Re: [Postgres-xc-bugs] ON COMMIT PRESERVE/DELETE ROWS do not work

[Michael P. <mic...@gm...>]

This is not a bug.
The transaction that creates the temporary table is performed on multiple
nodes at the same time, so it needs to use 2PC. The table is replicated,
and PostgreSQL core cannot use 2PC on temporary objects. So what you need
to do is set enforce_two_phase_commit to off to enforce the use of
autocommit for such transactions involving temporary objects.

On Tue, Nov 13, 2012 at 5:06 PM, Hitoshi HEMMI
<hem...@la...>wrote:

> Hi,
>
>
> ============================================================================
> POSTGRES-XC BUG REPORT TEMPLATE
>
> ============================================================================
>
> Your name : Hitoshi Hemmi
> Your email address : hem...@la...
>
> System Configuration:
> ---------------------
> Architecture : x86_64
>
> Operating Systems : CentOS release 6.2 x86_64
>
> Postgres-XC version : Postgres-XC 1.0.1dev
>
> Description of problems:
> ==============================================
>
>
> a) ON COMMIT PRESERVE ROWS doesn't work
>
>
> -----------------------------------------------------------------------------------
> psql (PGXC 1.0.0, based on PG 9.1.4)
> Type "help" for help.
>
> mydb=# DROP TABLE temp;
> ERROR: table "temp" does not exist
> mydb=# \d
> List of relations
> Schema | Name | Type | Owner
> --------+--------------------+-------+----------
> public | area_digital | table | postgres
> public | number | table | postgres
> public | people | table | postgres
> public | some_table | table | postgres
> public | tenk1 | table | postgres
> public | tenk1_with_oids | table | postgres
> public | tenk1_without_oids | table | postgres
> (7 rows)
>
> mydb=# BEGIN;
> BEGIN
> mydb=# CREATE GLOBAL TEMPORARY TABLE temp ( id SERIAL , text VARCHAR(10)
> ) ON COMMIT PRESERVE ROWS;
> NOTICE: CREATE TABLE will create implicit sequence "temp_id_seq" for
> serial column "temp.id"
> INSERT INTO temp(text) VALUES('test');
> \d
> CREATE TABLE
> mydb=# INSERT INTO temp(text) VALUES('test');
> INSERT 0 1
> mydb=# INSERT INTO temp(text) VALUES('test');
> INSERT 0 1
> mydb=# INSERT INTO temp(text) VALUES('test');
> INSERT 0 1
> mydb=# \d
> List of relations
> Schema | Name | Type | Owner
> -----------+--------------------+----------+----------
> pg_temp_2 | temp | table | postgres
> pg_temp_2 | temp_id_seq | sequence | postgres
> public | area_digital | table | postgres
> public | number | table | postgres
> public | people | table | postgres
> public | some_table | table | postgres
> public | tenk1 | table | postgres
> public | tenk1_with_oids | table | postgres
> public | tenk1_without_oids | table | postgres
> (9 rows)
>
> mydb=# COMMIT;
> ERROR: cannot PREPARE a transaction that has operated on temporary tables
> DETAIL: Disabling enforce_two_phase_commit is recommended to enforce COMMIT
> mydb=# \d
> List of relations
> Schema | Name | Type | Owner
> --------+--------------------+-------+----------
> public | area_digital | table | postgres
> public | number | table | postgres
> public | people | table | postgres
> public | some_table | table | postgres
> public | tenk1 | table | postgres
> public | tenk1_with_oids | table | postgres
> public | tenk1_without_oids | table | postgres
> (7 rows)
>
> mydb=# SELECT * FROM temp;
> ERROR: relation "temp" does not exist
> LINE 1: SELECT * FROM temp;
> ^
>
> -----------------------------------------------------------------------------------
>
>
>
> b) ON COMMIT DELETE ROWS doesn't work
>
>
> -----------------------------------------------------------------------------------
> psql (PGXC 1.0.0, based on PG 9.1.4)
> Type "help" for help.
>
> mydb=#
> mydb=#
> mydb=#
> mydb=# DROP TABLE temp;
> ERROR: table "temp" does not exist
> mydb=# \d
> List of relations
> Schema | Name | Type | Owner
> --------+--------------------+-------+----------
> public | area_digital | table | postgres
> public | number | table | postgres
> public | people | table | postgres
> public | some_table | table | postgres
> public | tenk1 | table | postgres
> public | tenk1_with_oids | table | postgres
> public | tenk1_without_oids | table | postgres
> (7 rows)
>
> mydb=# BEGIN;
> BEGIN
> mydb=# CREATE GLOBAL TEMPORARY TABLE temp ( id SERIAL , text VARCHAR(10)
> ) ON COMMIT DELETE ROWS;
> NOTICE: CREATE TABLE will create implicit sequence "temp_id_seq" for
> serial column "temp.id"
> INSERT INTO temp(text) VALUES('test');
> INSERT INTO temp(text) VALUES('test');
> CREATE TABLE
> mydb=# INSERT INTO temp(text) VALUES('test');
> INSERT 0 1
> mydb=# INSERT INTO temp(text) VALUES('test');
> INSERT 0 1
> mydb=# INSERT INTO temp(text) VALUES('test');
> INSERT 0 1
> mydb=# \d
> List of relations
> Schema | Name | Type | Owner
> -----------+--------------------+----------+----------
> pg_temp_2 | temp | table | postgres
> pg_temp_2 | temp_id_seq | sequence | postgres
> public | area_digital | table | postgres
> public | number | table | postgres
> public | people | table | postgres
> public | some_table | table | postgres
> public | tenk1 | table | postgres
> public | tenk1_with_oids | table | postgres
> public | tenk1_without_oids | table | postgres
> (9 rows)
>
> mydb=# COMMIT;
> ERROR: cannot PREPARE a transaction that has operated on temporary tables
> DETAIL: Disabling enforce_two_phase_commit is recommended to enforce COMMIT
> mydb=# \d
> List of relations
> Schema | Name | Type | Owner
> --------+--------------------+-------+----------
> public | area_digital | table | postgres
> public | number | table | postgres
> public | people | table | postgres
> public | some_table | table | postgres
> public | tenk1 | table | postgres
> public | tenk1_with_oids | table | postgres
> public | tenk1_without_oids | table | postgres
> (7 rows)
>
> mydb=# SELECT * FROM temp;
> ERROR: relation "temp" does not exist
> LINE 1: SELECT * FROM temp;
> ^
>
> -----------------------------------------------------------------------------------
>
> --
> Hitoshi HEMMI
> NTT Open Source Software Center
> hem...@la...
> (Please note that my address has changed.)
> Tel:(03)5860-5115
> Fax:(03)5463-5490
>
>
>
> ------------------------------------------------------------------------------
> Monitor your physical, virtual and cloud infrastructure from a single
> web console. Get in-depth insight into apps, servers, databases, vmware,
> SAP, cloud infrastructure, etc. Download 30-day Free Trial.
> Pricing starts from $795 for 25 servers or applications!
> http://p.sf.net/sfu/zoho_dev2dev_nov
> _______________________________________________
> Postgres-xc-bugs mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>



-- 
Michael Paquier
http://michael.otacoo.com