postgres-xc-bugs

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

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

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

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

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