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

[Artur G. <aa...@bo...>]

Hi,

does it mean that CREATE TABLE command may fail the same way? Is there any
possibility to reduce the risk of such situation by for example setting
preferred or primary node in Postgre XC configuration? (ALTER NODE
command).

Also, how often are the new versions (with bugfixes) of XC released? I
know that fixing such bug could take a while, but I wonder if this fix
won't be hindered by a release plan too..

On Fri, 10 Jul 2015 13:13:30 +0900, Koichi Suzuki <koi...@gm...>
wrote:
> 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
>>
>>