Re: [Postgres-xc-general] Uncommited transaction? Weird issues with DDL and missing database

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

In PGXC, DDL operation is not serialized, I mean, the order of the node
where each peace of DDL executed is not defined.   This may cause some race
conditions and could cause local errors, resulting in inconsistent status.

Recetly, I found CREATE TABLE in parallel schedule of regression test cause
similar problems.   I think this is common to XC and XL.

There are a couple of ways to fix this:

1. Use coordinator primary node to go first, as done in replicated tables,
2. Serialize DDL execution, possibly by using advisory lock internally.

Anyway, this is a bug and we need a fix.   Solution 1 is bit more
complicated than 2.

Best;

---
Koichi Suzuki
https://www.linkedin.com/in/koichidbms


2015-07-09 23:22 GMT+09:00 Artur Graniszewski <aa...@bo...>:

> Hi,
>
> I just finished the multi-master configuration of Postgres XC on my test
> environment (three virtual machines):
>
> * VM1 - with GTM only
> * VM2, VM2 - two masters with one data-node and one coordinator on each
> machine
>
> I tried to create some databases on each master to check if everything
> works ok, but I found a strange issue with one database that had been
> created only on one of the masters (while other databases I created and
> deleted afterwards were ok):
>
> postgres=# \l
>                                   List of databases
>    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
> privileges
>
> -----------+----------+----------+-------------+-------------+-----------------------
>  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres          +
>            |          |          |             |             |
> postgres=CTc/postgres
>  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres          +
>            |          |          |             |             |
> postgres=CTc/postgres
>  test2     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>  whatnot   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>
> vs
>
> postgres=# \l
>                                   List of databases
>    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
> privileges
>
> -----------+----------+----------+-------------+-------------+-----------------------
>  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres          +
>            |          |          |             |             |
> postgres=CTc/postgres
>  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres          +
>            |          |          |             |             |
> postgres=CTc/postgres
>  whatnot   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>
> Unfortunately, when I try to perform any DDL command on a server that sees
> "test2" database I receive the following errors:
>
> postgres=# drop database test2;
> ERROR:  database "test2" does not exist
> postgres=# create database test2;
> ERROR:  database "test2" already exists
>
> Also, when I try to remove another database called "whatnot", drop
> operation never finishes. It looks like a transaction that hangs some DDL
> operations:
>
> postgres=# select * from pgxc_prepared_xacts;
>  pgxc_prepared_xact
> --------------------
>  T74589
> (1 row)
>
> postgres=# select * from pg_prepared_xacts;
>  transaction |  gid   |           prepared            |  owner   |
> database
>
> -------------+--------+-------------------------------+----------+----------
>        73716 | T73716 | 2015-07-09 12:58:25.823385+02 | postgres | postgres
> (1 row)
>
> postgres=#
>
> When I try to rollback the transaction, Postgres says that given GID does
> not exist.
>
> Is it a bug in Postgres XC? Is there a way to synchronize the masters? (eg
> delete "test2" on one master and "whatnot" on both of them). Please keep in
> mind that I created/dropped more databases without any issues, so I'm
> unsure what went wrong in case of these two specific databases.
>
> Cheers,
>
> Artur
>
>
>
>
>
>
>
>
>
>
> ------------------------------------------------------------------------------
> Don't Limit Your Business. Reach for the Cloud.
> GigeNET's Cloud Solutions provide you with the tools and support that
> you need to offload your IT needs and focus on growing your business.
> Configured For All Businesses. Start Your Cloud Today.
> https://www.gigenetcloud.com/
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>