Menu
▾
▴
[Postgres-xc-bugs] ON COMMIT PRESERVE/DELETE ROWS do not work
|
From: Hitoshi H. <hem...@la...> - 2012-11-13 08:06:27
|
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
|
