Re: [Postgres-xc-bugs] Incomplete transaction clean up during pgxc_lock_for_backup violation

[Koichi S. <koi...@gm...>]

If you would like to cleanup outstanding 2PCs, you should do the following:

1. Visit all the nodes (both coordinators and datanodes) and get what nodes
are involved in such 2PC TXN.   You can do this using EXECUTE DIRECT and
issue SELECT command to pg_prepared_xacts.   The information you've got is
the status at the coordinator you connected.

2. Connect direct to each of the node using psql as a superuser.   Isuue
"set xc_maintenance_mode to on" and then issue "rollback prepared".   This
is how to clean up such transactions.

If you're interested in, contrib includes pgxc_clean module, which will do
similar cleanup, although I've not tested it in your situation.   The above
method is more direct and could be helpful.

Regards;

---
Koichi Suzuki


2013/11/12 Víctor Daniel M. O. <vda...@gm...>

> Greetings to everyone, I'm new to the mail list,
>
> I was testing master branch of postgres-xc, and I think exist a strange
> behavior during transaction redo when Postgres-XC is violated trying to
> create a database while pgxc_lock_for_backup is active.
>
> GIT commit:
>
> $ git log | tail | grep ^commit
> commit d31084e9d1118b25fd16580d9d8c2924b5740dff
> $ git branch -vv
> * master a50bdd3 [origin/master] Replace strndup calls by strdup in
> makesgml
>
> $ uname -a
> Linux eva-01-nerv-seele 3.11.7-200.fc19.x86_64 #1 SMP Mon Nov 4 14:09:03
> UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
>
>  ~/.opt/pgxc/bin/initgtm -D ~/.opt/pgxc/var/lib/pgsql/data_gtm
>  ~/.opt/pgxc/bin/initdb -D ~/.opt/pgxc/var/lib/pgsql/data_datanode2
> --nodename datanode2
>  ~/.opt/pgxc/bin/initdb -D ~/.opt/pgxc/var/lib/pgsql/data_datanode1
> --nodename datanode1
>  ~/.opt/pgxc/bin/initdb -D ~/.opt/pgxc/var/lib/pgsql/data_coord1
> --nodename coord1
>
> ~/.opt/pgxc/bin/gtm -D ~/.opt/pgxc/var/lib/pgsql/data_gtm
> ~/.opt/pgxc/bin/postgres --coordinator -D
> ~/.opt/pgxc/var/lib/pgsql/data_coord1
> ~/.opt/pgxc/bin/postgres --datanode -D
> ~/.opt/pgxc/var/lib/pgsql/data_datanode1 -p 15432
>
>
> $ ~/.opt/pgxc/bin/psql postgres
> psql (PGXC 1.1devel, based on PG 9.2beta2)
> Type "help" for help.
>
> postgres=# CREATE NODE datanode1 WITH (TYPE = 'datanode', PORT = 15432);
> CREATE NODE
> postgres=# select pgxc_pool_reload();
>  pgxc_pool_reload
> ------------------
>  t
> (1 row)
>
> postgres=# select * from pg_prepared_xacts ;
>  transaction | gid | prepared | owner | database
> -------------+-----+----------+-------+----------
> (0 rows)
>
> postgres=# select pgxc_lock_for_backup();
> INFO:  please do not close this session until you are done adding the new
> node
>  pgxc_lock_for_backup
> ----------------------
>  t
> (1 row)
>
> postgres=# select txid_current();
>  txid_current
> --------------
>         10011
> (1 row)
>
> postgres=# create database test;
> PANIC:  we seem to have dropped a bit somewhere
> PANIC:  we seem to have dropped a bit somewhere
> The connection to the server was lost. Attempting reset: Failed.
> !> \q
>
> --- LOGS IN DIRECTOR ---
> LOG:  server process (PID 23684) was terminated by signal 6: Aborted
> DETAIL:  Failed process was running: create database test;
> LOG:  terminating any other active server processes
> WARNING:  terminating connection because of crash of another server process
> DETAIL:  The postmaster has commanded this server process to roll back the
> current transaction and exit, because another server process exited
> abnormally and possibly corrupted shared memory.
> HINT:  In a moment you should be able to reconnect to the database and
> repeat your command.
> FATAL:  the database system is in recovery mode
> LOG:  all server processes terminated; reinitializing
> LOG:  database system was interrupted; last known up at 2013-11-11
> 21:38:21 COT
> LOG:  database system was not properly shut down; automatic recovery in
> progress
> LOG:  redo starts at 0/189C940
> LOG:  record with zero length at 0/189CB18
> LOG:  redo done at 0/189C940
> LOG:  recovering prepared transaction 10012
> LOG:  database system is ready to accept connections
> LOG:  autovacuum launcher started
> ---
>
> $ ~/.opt/pgxc/bin/psql postgres
> psql (PGXC 1.1devel, based on PG 9.2beta2)
> Type "help" for help.
>
> postgres=# select * from pg_prepared_xacts ;
>  transaction |  gid   |           prepared            | owner | database
> -------------+--------+-------------------------------+-------+----------
>        10012 | T10012 | 2013-11-11 21:38:22.246206-05 | vdmo  | postgres
> (1 row)
>
> postgres=# rollback prepared 'T10012';
> ERROR:  prepared transaction with identifier "T10012" does not exist
>
> Unfortunately from here not is possible to rollback or commit the
> transaction, and not allow run again pgxc_lock_for_backup because exists a
> transaction in prepare stage.
>
> Excuse my ignorance but is this a problem or normal expected behavior?.
>
> Thks,
>
> Daniel.
>
>
> ------------------------------------------------------------------------------
> November Webinars for C, C++, Fortran Developers
> Accelerate application performance with scalable programming models.
> Explore
> techniques for threading, error checking, porting, and tuning. Get the most
> from the latest Intel processors and coprocessors. See abstracts and
> register
> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-bugs mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>
>