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

[Hitoshi H. <hem...@la...>]

Sorry for my late responce again.
Thank you for your explanation. That resolved the problem.
Howerver, it should be very hard for a normal user to get the point by 
himself, we guess.

enforce_two_phase_commit is described in
http://postgres-xc.sourceforge.net/docs/1_0/pg-xc-specifics.html
There is no explicit pointer or suggestion to the above page in
http://postgres-xc.sourceforge.net/docs/1_0/sql-createtable.html
where CREATE TABLE syntax specified.

Any planned improvements?

-hemmi



Michael Paquier さんは書きました:
> 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... <mailto:hem...@la...>> wrote:
>
>     Hi,
>
>     ============================================================================
>     POSTGRES-XC BUG REPORT TEMPLATE
>     ============================================================================
>
>     Your name : Hitoshi Hemmi
>     Your email address : hem...@la...
>     <mailto: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 <http://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 <http://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... <mailto: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...
>     <mailto:Pos...@li...>
>     https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>
>
>
>
> -- 
> Michael Paquier
> http://michael.otacoo.com


-- 
Hitoshi HEMMI
NTT Open Source Software Center
hem...@la...
(Please note that my address has changed.)
Tel:(03)5860-5115
Fax:(03)5463-5490