Re: Transactions and temp tables - Mailing list pgsql-hackers
| From | Emmanuel Cecchet |
|---|---|
| Subject | Re: Transactions and temp tables |
| Date | |
| Msg-id | 4914CD14.2060608@frogthinker.org Whole thread Raw |
| In response to | Re: Transactions and temp tables (Emmanuel Cecchet <manu@frogthinker.org>) |
| Responses |
Re: Transactions and temp tables
(Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>)
|
| List | pgsql-hackers |
Hi, As I have not found yet an elegant solution to deal with the DROP CASCADE issue, here is a simpler patch that handles temp tables that are dropped at commit time. This is a good first step and we will try to elaborate further to support ON COMMIT DELETE ROWS. I have also added a compilation of the tests I have even if some are not really relevant anymore without the support for empty temp tables but we will probably reuse them later. Thanks in advance for the feedback, Emmanuel Emmanuel Cecchet wrote: > Heikki Linnakangas wrote: >>> Yes, I was trying to allow also ON COMMIT DROP and ON COMMIT DELETE >>> ROW. An empty temp table at PREPARE time would be similar to an ON >>> COMMIT DELETE ROW table. >> I think you'll want to check explicitly that the table is defined >> with ON COMMIT DELETE ROWS, instead of checking that it's empty. > Where can I find the field containing the CREATE options for the temp > table? >> Yeah, thanks to MVCC, it's possible that the table looks empty to the >> transaction being prepared, using SnapshotNow, but there's some >> tuples that are still visible to other transactions. For example: >> >> CREATE TEMPORARY TABLE foo (id int4); >> INSERT INTO foo VALUES (1); >> begin; >> DELETE FROM foo; >> PREPARE TRANSACTION 'foo'; -- doesn't error, because the table is >> empty, according to SnapshotNow >> SELECT * FROM foo; -- Still shows the one row, because the deleting >> transaction hasn't committed yet. > Is that a problem? If your transaction isolation level is not > serializable the SELECT will not block and return the current > snapshot. From the transaction standpoint, it is fine that the > transaction can prepare or am I missing something? > Actually, I did a test and if the temp table is created with 'on > commit delete rows' option, the select blocks until the transaction is > committed. This seems a normal behavior to me. >>>> I don't think you can just ignore "prepared temp relations" in >>>> findDependentObjects to avoid the lockup at backend exit. It's also >>>> used >>>> for DROP CASCADE, for example. >>> Do you mean that it will break the DROP CASCADE behavior in general, >>> or that would break the behavior for master/child temp tables? >> >> For temp tables, I suppose. > I confirm that doing a drop cascade on a master temp table after a > prepared transaction committed from another backend will not drop the > children for now. >> >> The hack in findDependentObjects still isn't enough, anyway. If you >> have a prepared transaction that created a temp table, the database >> doesn't shut down: >> >> $ bin/pg_ctl -D data start >> server starting >> $ LOG: database system was shut down at 2008-11-04 10:27:27 EST >> LOG: autovacuum launcher started >> LOG: database system is ready to accept connections >> >> $ bin/psql postgres -c "begin; CREATE TEMPORARY TABLE temp (id >> integer); PREPARE TRANSACTION 'foo';" >> PREPARE TRANSACTION >> hlinnaka@heikkilaptop:~/pgsql.fsmfork$ bin/pg_ctl -D data stop >> LOG: received smart shutdown request >> LOG: autovacuum launcher shutting down >> waiting for server to shut >> down............................................................... >> failed >> pg_ctl: server does not shut down > Interesting case, if the table is created but not accessed it is not > enlisted and then the shutdown does not catch this dependency. The > table should be enlisted at CREATE time as well. > > The bookkeeping of prepared commit tables is just for the shutdown > case right now. If you think it is a bad idea altogether to have > session temp tables (even with delete rows on commit) that can cross > commit boundaries, then we can remove that second bookkeeping and only > allow temp tables that have been created withing the scope of the > transaction. > > I fixed the hash_freeze problem but this drop cascade on temp table > seems to be an issue (if anyone uses that feature). > > Emmanuel > -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development & Consulting -- Web: http://www.frogthinker.org email: manu@frogthinker.org Skype: emmanuel_cecchet ### Eclipse Workspace Patch 1.0 #P Postgres-HEAD Index: src/backend/access/heap/heapam.c =================================================================== RCS file: /root/cvsrepo/pgsql/src/backend/access/heap/heapam.c,v retrieving revision 1.268 diff -u -r1.268 heapam.c --- src/backend/access/heap/heapam.c 31 Oct 2008 19:40:26 -0000 1.268 +++ src/backend/access/heap/heapam.c 7 Nov 2008 23:09:11 -0000 @@ -878,7 +878,7 @@ /* Make note that we've accessed a temporary relation */ if (r->rd_istemp) - MyXactAccessedTempRel = true; + enlistRelationIdFor2PCChecks(relationId); pgstat_initstats(r); @@ -926,7 +926,7 @@ /* Make note that we've accessed a temporary relation */ if (r->rd_istemp) - MyXactAccessedTempRel = true; + enlistRelationIdFor2PCChecks(relationId); pgstat_initstats(r); @@ -976,7 +976,7 @@ /* Make note that we've accessed a temporary relation */ if (r->rd_istemp) - MyXactAccessedTempRel = true; + enlistRelationIdFor2PCChecks(relationId); pgstat_initstats(r); Index: src/backend/access/transam/xact.c =================================================================== RCS file: /root/cvsrepo/pgsql/src/backend/access/transam/xact.c,v retrieving revision 1.266 diff -u -r1.266 xact.c --- src/backend/access/transam/xact.c 20 Oct 2008 19:18:18 -0000 1.266 +++ src/backend/access/transam/xact.c 7 Nov 2008 23:09:11 -0000 @@ -65,13 +65,6 @@ int CommitDelay = 0; /* precommit delay in microseconds */ int CommitSiblings = 5; /* # concurrent xacts needed to sleep */ -/* - * MyXactAccessedTempRel is set when a temporary relation is accessed. - * We don't allow PREPARE TRANSACTION in that case. (This is global - * so that it can be set from heapam.c.) - */ -bool MyXactAccessedTempRel = false; - /* * transaction states - transaction state from server perspective @@ -209,6 +202,9 @@ */ static MemoryContext TransactionAbortContext = NULL; +/* Hash table containing Oids of accessed temporary relations */ +HTAB *accessedTempRel; + /* * List of add-on start- and end-of-xact callbacks */ @@ -250,6 +246,7 @@ SubTransactionId mySubid, SubTransactionId parentSubid); static void CleanupTransaction(void); +static void CleanupAccessedTempRel(void); static void CommitTransaction(void); static TransactionId RecordTransactionAbort(bool isSubXact); static void StartTransaction(void); @@ -623,7 +620,7 @@ /* Propagate new command ID into static snapshots */ SnapshotSetCommandId(currentCommandId); - + /* * Make any catalog changes done by the just-completed command * visible in the local syscache. We obviously don't need to do @@ -1110,10 +1107,10 @@ */ if (s->parent->childXids == NULL) new_childXids = - MemoryContextAlloc(TopTransactionContext, + MemoryContextAlloc(TopTransactionContext, new_maxChildXids * sizeof(TransactionId)); else - new_childXids = repalloc(s->parent->childXids, + new_childXids = repalloc(s->parent->childXids, new_maxChildXids * sizeof(TransactionId)); s->parent->childXids = new_childXids; @@ -1466,7 +1463,6 @@ XactIsoLevel = DefaultXactIsoLevel; XactReadOnly = DefaultXactReadOnly; forceSyncCommit = false; - MyXactAccessedTempRel = false; /* * reinitialize within-transaction counters @@ -1715,6 +1711,8 @@ AtCommit_Memory(); + CleanupAccessedTempRel(); + s->transactionId = InvalidTransactionId; s->subTransactionId = InvalidSubTransactionId; s->nestingLevel = 0; @@ -1732,6 +1730,50 @@ RESUME_INTERRUPTS(); } +/* ---------------- + * enlistRelationIdFor2PCChecks - enlist a relation in the list of + * resources to check at PREPARE COMMIT time if we are part of + * a 2PC transaction. The resource will be removed from the list + * if the table is dropped before commit. + * ---------------- + */ +void +enlistRelationIdFor2PCChecks(Oid relationId) +{ + Oid *tid; + + /* + * Each time a temporary relation is accessed, it is added to the + * accessedTempRel list. PREPARE TRANSACTION will fail if any + * of the accessed relation is still valid (not dropped). (This is + * called from from heapam.c.) + */ + if (accessedTempRel == NULL) + { // Allocate the list on-demand + HASHCTL ctl; + + ctl.keysize = sizeof(Oid); + ctl.entrysize = sizeof(Oid); + accessedTempRel = hash_create("accessed temp tables", 4, &ctl, + HASH_ELEM); + } + + // Add to the hash list if missing + tid = hash_search(accessedTempRel, &relationId, HASH_ENTER, NULL); + *tid = relationId; +} + +/* + * Cleanup the list of prepared temp tables that were accessed during this transaction. + */ +static void CleanupAccessedTempRel(void) +{ + if (accessedTempRel != NULL) + { + hash_destroy(accessedTempRel); + accessedTempRel = NULL; + } +} /* * PrepareTransaction @@ -1808,14 +1850,37 @@ * We must check this after executing any ON COMMIT actions, because * they might still access a temp relation. * - * XXX In principle this could be relaxed to allow some useful special - * cases, such as a temp table created and dropped all within the - * transaction. That seems to require much more bookkeeping though. + * We only allow to proceed further if the accessed temp tables have + * been dropped before PREPARE COMMIT. */ - if (MyXactAccessedTempRel) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot PREPARE a transaction that has operated on temporary tables"))); + if (accessedTempRel != NULL) + { + HASH_SEQ_STATUS status; + Oid* tempTableOid; + + /* Prevent further updates to the list as recommended in hash_seq_init doc. */ + hash_freeze(accessedTempRel); + hash_seq_init(&status, accessedTempRel); + while ((tempTableOid = (Oid *) hash_seq_search(&status)) != NULL) + { /* Check all accessed temp tables. If the table has been dropped, + * try_relation_open will fail and we can safely continue. */ + Relation tempTable = try_relation_open(*tempTableOid, NoLock); + + if (tempTable != NULL) + { // We have an open temp table at PREPARE COMMIT time throw an error + relation_close(tempTable, NoLock); + hash_seq_term(&status); + hash_destroy(accessedTempRel); + accessedTempRel = NULL; + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot PREPARE a transaction that has operated on temporary tables that are notdropped at commit time"))); + } + } + + hash_destroy(accessedTempRel); + accessedTempRel = NULL; + } /* Prevent cancel/die interrupt while cleaning up */ HOLD_INTERRUPTS(); @@ -2106,6 +2171,8 @@ elog(FATAL, "CleanupTransaction: unexpected state %s", TransStateAsString(s->state)); + CleanupAccessedTempRel(); + /* * do abort cleanup processing */ Index: src/include/access/xact.h =================================================================== RCS file: /root/cvsrepo/pgsql/src/include/access/xact.h,v retrieving revision 1.95 diff -u -r1.95 xact.h --- src/include/access/xact.h 11 Aug 2008 11:05:11 -0000 1.95 +++ src/include/access/xact.h 7 Nov 2008 23:09:11 -0000 @@ -18,6 +18,7 @@ #include "nodes/pg_list.h" #include "storage/relfilenode.h" #include "utils/timestamp.h" +#include "postgres_ext.h" /* @@ -44,8 +45,8 @@ /* Asynchronous commits */ extern bool XactSyncCommit; -/* Kluge for 2PC support */ -extern bool MyXactAccessedTempRel; +/* List of temp tables accessed during a transaction for 2PC support */ +extern void enlistRelationIdFor2PCChecks(Oid relationId); /* * start- and end-of-transaction callbacks for dynamically loaded modules ### Eclipse Workspace Patch 1.0 #P Postgres-HEAD Index: src/test/regress/parallel_schedule =================================================================== RCS file: /root/cvsrepo/pgsql/src/test/regress/parallel_schedule,v retrieving revision 1.50 diff -u -r1.50 parallel_schedule --- src/test/regress/parallel_schedule 31 Oct 2008 09:17:16 -0000 1.50 +++ src/test/regress/parallel_schedule 7 Nov 2008 23:13:53 -0000 @@ -90,3 +90,19 @@ # run tablespace by itself test: tablespace + +# -------- +# 2PC related tests +# -------- +test: 2pc_persistent_table 2pc_on_delete_rows_transaction 2pc_on_commit_drop 2pc_temp_table_transaction 2pc_temp_table_rollback2pc_temp_table_savepoint 2pc_temp_table_failure +test: 2pc_dirty_buffer_check +test: 2pc_temp_table_session +test: 2pc_on_delete_rows_session +# The following tests must be executing in sequence, +# do not alter the order nor try to execute them in parallel +test: 2pc_temp_table_prepared_not_committed +test: 2pc_temp_table_commit_prepared +# This test must be run last to check if the database properly +# shutdowns with a prepared transaction that is not committed +test: 2pc_temp_table_prepared_not_committed + \ No newline at end of file Index: src/test/regress/serial_schedule =================================================================== RCS file: /root/cvsrepo/pgsql/src/test/regress/serial_schedule,v retrieving revision 1.47 diff -u -r1.47 serial_schedule --- src/test/regress/serial_schedule 31 Oct 2008 09:17:16 -0000 1.47 +++ src/test/regress/serial_schedule 7 Nov 2008 23:13:53 -0000 @@ -119,3 +119,20 @@ test: xml test: stats test: tablespace +test: 2pc_persistent_table +test: 2pc_on_delete_rows_transaction +test: 2pc_on_commit_drop +test: 2pc_temp_table_transaction +test: 2pc_temp_table_rollback +test: 2pc_temp_table_savepoint +test: 2pc_dirty_buffer_check +test: 2pc_temp_table_session +test: 2pc_on_delete_rows_session +test: 2pc_temp_table_failure +# The following tests must be executing in sequence, +# do not alter the order nor try to execute them in parallel +test: 2pc_temp_table_prepared_not_committed +test: 2pc_temp_table_commit_prepared +# This test must be run last to check if the database properly +# shutdowns with a prepared transaction that is not committed +test: 2pc_temp_table_prepared_not_committed Index: src/test/regress/expected/temp.out =================================================================== RCS file: /root/cvsrepo/pgsql/src/test/regress/expected/temp.out,v retrieving revision 1.15 diff -u -r1.15 temp.out --- src/test/regress/expected/temp.out 1 Sep 2008 20:42:46 -0000 1.15 +++ src/test/regress/expected/temp.out 7 Nov 2008 23:13:53 -0000 @@ -1,3 +1,13 @@ +create temp table master (x int); +create temp table child () inherits (master); +insert into master values (1); +insert into child values (2); +drop table master cascade; +NOTICE: drop cascades to table child +select * from child; +ERROR: relation "child" does not exist +LINE 1: select * from child; + ^ -- -- TEMP -- Test temp relations and indexes Index: src/test/regress/results/2pc_temp_table_transaction.out =================================================================== RCS file: src/test/regress/results/2pc_temp_table_transaction.out diff -N src/test/regress/results/2pc_temp_table_transaction.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_temp_table_transaction.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,7 @@ +-- Transaction-scope dropped temp table use case +begin; +create temp table foo(x int); +insert into foo values(1); +drop table foo; +prepare transaction 'dropTempTableShouldSucceed'; +commit prepared 'dropTempTableShouldSucceed'; Index: src/test/regress/results/2pc_temp_table_prepared_not_committed.out =================================================================== RCS file: src/test/regress/results/2pc_temp_table_prepared_not_committed.out diff -N src/test/regress/results/2pc_temp_table_prepared_not_committed.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_temp_table_prepared_not_committed.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,23 @@ +-- The purpose of this test is to test the proper termination of +-- a session with a prepared transaction that has accessed a temp table +-- +-- Session-scope temp table use case but exit after prepared +-- see 2pc_temp_table_commit_prepared.sql for committing that transaction +create temp table foo1(x int); +insert into foo1 values(1); +begin; +delete from foo1; +prepare transaction 'preparedNonCommittedFoo1'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +select * from foo1; + x +--- + 1 +(1 row) + +create temp table foo2(x int); +begin; +insert into foo2 values(1); +delete from foo2; +prepare transaction 'preparedNonCommittedFoo2'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time Index: src/test/regress/expected/2pc_temp_table_savepoint.out =================================================================== RCS file: src/test/regress/expected/2pc_temp_table_savepoint.out diff -N src/test/regress/expected/2pc_temp_table_savepoint.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_temp_table_savepoint.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,8 @@ +-- Rollback to savepoint test case +BEGIN; +SAVEPOINT sp; +CREATE TEMP TABLE foo(bar int4); +INSERT INTO foo VALUES (1); +ROLLBACK TO sp; +PREPARE TRANSACTION 'savepointTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php'; +COMMIT PREPARED 'savepointTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php'; Index: src/test/regress/expected/2pc_temp_table_transaction.out =================================================================== RCS file: src/test/regress/expected/2pc_temp_table_transaction.out diff -N src/test/regress/expected/2pc_temp_table_transaction.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_temp_table_transaction.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,7 @@ +-- Transaction-scope dropped temp table use case +begin; +create temp table foo(x int); +insert into foo values(1); +drop table foo; +prepare transaction 'dropTempTableShouldSucceed'; +commit prepared 'dropTempTableShouldSucceed'; Index: src/test/regress/results/2pc_temp_table_commit_prepared.out =================================================================== RCS file: src/test/regress/results/2pc_temp_table_commit_prepared.out diff -N src/test/regress/results/2pc_temp_table_commit_prepared.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_temp_table_commit_prepared.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,14 @@ +-- The purpose of this test is to test the proper termination of +-- a session with a prepared transaction that has accessed a temp table +-- +-- This test has to be called in sequence after 2pc_temp_table_prepared_not_committed.sql +commit prepared 'preparedNonCommittedFoo1'; +ERROR: prepared transaction with identifier "preparedNonCommittedFoo1" does not exist +-- The table should not exist anymore in this session +drop table foo1; +ERROR: table "foo1" does not exist +commit prepared 'preparedNonCommittedFoo2'; +ERROR: prepared transaction with identifier "preparedNonCommittedFoo2" does not exist +-- The table should not exist anymore in this session +drop table foo2; +ERROR: table "foo2" does not exist Index: src/test/regress/expected/2pc_on_delete_rows_transaction.out =================================================================== RCS file: src/test/regress/expected/2pc_on_delete_rows_transaction.out diff -N src/test/regress/expected/2pc_on_delete_rows_transaction.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_on_delete_rows_transaction.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,10 @@ +-- Temp table with ON DELETE ROWS option (transaction scope) +begin; +create temp table foo(x int) on commit delete rows; +insert into foo values(1); +prepare transaction 'onCommitDeleteRowsTempTableShouldSucceed'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +commit prepared 'onCommitDeleteRowsTempTableShouldSucceed'; +ERROR: prepared transaction with identifier "onCommitDeleteRowsTempTableShouldSucceed" does not exist +drop table foo; +ERROR: table "foo" does not exist Index: src/test/regress/results/2pc_on_delete_rows_transaction.out =================================================================== RCS file: src/test/regress/results/2pc_on_delete_rows_transaction.out diff -N src/test/regress/results/2pc_on_delete_rows_transaction.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_on_delete_rows_transaction.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,10 @@ +-- Temp table with ON DELETE ROWS option (transaction scope) +begin; +create temp table foo(x int) on commit delete rows; +insert into foo values(1); +prepare transaction 'onCommitDeleteRowsTempTableShouldSucceed'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +commit prepared 'onCommitDeleteRowsTempTableShouldSucceed'; +ERROR: prepared transaction with identifier "onCommitDeleteRowsTempTableShouldSucceed" does not exist +drop table foo; +ERROR: table "foo" does not exist Index: src/test/regress/expected/2pc_persistent_table.out =================================================================== RCS file: src/test/regress/expected/2pc_persistent_table.out diff -N src/test/regress/expected/2pc_persistent_table.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_persistent_table.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,11 @@ +-- Creation of a persistent table (not temp) +begin; +create table paul(x int); +insert into paul values(1); +prepare transaction 'persistentTableShouldSucceed'; +commit prepared 'persistentTableShouldSucceed'; +-- Drop of a persistent table (not temp) +begin; +drop table paul; +prepare transaction 'dropPersistentTableShouldSucceed'; +commit prepared 'dropPersistentTableShouldSucceed'; Index: src/test/regress/expected/2pc_temp_table_session.out =================================================================== RCS file: src/test/regress/expected/2pc_temp_table_session.out diff -N src/test/regress/expected/2pc_temp_table_session.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_temp_table_session.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,10 @@ +-- Session-scope temp table use case +create temp table foo(x int); +begin; +insert into foo values(1); +delete from foo; +prepare transaction 'dropTempTableShouldSucceed'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +commit prepared 'dropTempTableShouldSucceed'; +ERROR: prepared transaction with identifier "dropTempTableShouldSucceed" does not exist +drop table foo; Index: src/test/regress/results/2pc_on_commit_drop.out =================================================================== RCS file: src/test/regress/results/2pc_on_commit_drop.out diff -N src/test/regress/results/2pc_on_commit_drop.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_on_commit_drop.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,6 @@ +-- Temp table with ON COMMIT DROP option +begin; +create temp table foo(x int) on commit drop; +insert into foo values(1); +prepare transaction 'onCommitDropTempTableShouldSucceed'; +commit prepared 'onCommitDropTempTableShouldSucceed'; Index: src/test/regress/expected/2pc_temp_table_commit_prepared.out =================================================================== RCS file: src/test/regress/expected/2pc_temp_table_commit_prepared.out diff -N src/test/regress/expected/2pc_temp_table_commit_prepared.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_temp_table_commit_prepared.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,14 @@ +-- The purpose of this test is to test the proper termination of +-- a session with a prepared transaction that has accessed a temp table +-- +-- This test has to be called in sequence after 2pc_temp_table_prepared_not_committed.sql +commit prepared 'preparedNonCommittedFoo1'; +ERROR: prepared transaction with identifier "preparedNonCommittedFoo1" does not exist +-- The table should not exist anymore in this session +drop table foo1; +ERROR: table "foo1" does not exist +commit prepared 'preparedNonCommittedFoo2'; +ERROR: prepared transaction with identifier "preparedNonCommittedFoo2" does not exist +-- The table should not exist anymore in this session +drop table foo2; +ERROR: table "foo2" does not exist Index: src/test/regress/expected/2pc_temp_table_failure.out =================================================================== RCS file: src/test/regress/expected/2pc_temp_table_failure.out diff -N src/test/regress/expected/2pc_temp_table_failure.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_temp_table_failure.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,8 @@ +-- Existing non-empty temp table at commit time should still fail +begin; +create temp table foo(x int); +insert into foo values(1); +prepare transaction 'existingTempTableShouldFail'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +commit prepared 'existingTempTableShouldFail'; +ERROR: prepared transaction with identifier "existingTempTableShouldFail" does not exist Index: src/test/regress/expected/2pc_on_delete_rows_session.out =================================================================== RCS file: src/test/regress/expected/2pc_on_delete_rows_session.out diff -N src/test/regress/expected/2pc_on_delete_rows_session.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_on_delete_rows_session.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,9 @@ +-- Temp table with ON DELETE ROWS option (session scope) +create temp table foo(x int) on commit delete rows; +begin; +insert into foo values(1); +prepare transaction 'onCommitDeleteRowsTempTableShouldSucceed'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +commit prepared 'onCommitDeleteRowsTempTableShouldSucceed'; +ERROR: prepared transaction with identifier "onCommitDeleteRowsTempTableShouldSucceed" does not exist +drop table foo; Index: src/test/regress/results/2pc_dirty_buffer_check.out =================================================================== RCS file: src/test/regress/results/2pc_dirty_buffer_check.out diff -N src/test/regress/results/2pc_dirty_buffer_check.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_dirty_buffer_check.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,52 @@ +-- 2PC Dirty buffer check +begin; +create temp table foo(a int, b int, c int) on commit drop; +insert into foo values(1,1,1); +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +prepare transaction 'bcd'; +commit prepared 'bcd'; +begin; +create temp table bar(a int, b int, c int) on commit drop; +insert into bar values(1,1,1); +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +commit; Index: src/test/regress/results/2pc_temp_table_rollback.out =================================================================== RCS file: src/test/regress/results/2pc_temp_table_rollback.out diff -N src/test/regress/results/2pc_temp_table_rollback.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_temp_table_rollback.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,7 @@ +-- Rollback prepared +BEGIN; +CREATE TEMP TABLE foo(bar int4); +PREPARE TRANSACTION 'rollbackTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +ROLLBACK PREPARED 'rollbackTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php'; +ERROR: prepared transaction with identifier "rollbackTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php"does not exist Index: src/test/regress/results/temp.out =================================================================== RCS file: src/test/regress/results/temp.out diff -N src/test/regress/results/temp.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/temp.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,213 @@ +create temp table master (x int); +create temp table child () inherits (master); +insert into master values (1); +insert into child values (2); +drop table master cascade; +NOTICE: drop cascades to table child +select * from child; +ERROR: relation "child" does not exist +LINE 1: select * from child; + ^ +-- +-- TEMP +-- Test temp relations and indexes +-- +-- test temp table/index masking +CREATE TABLE temptest(col int); +CREATE INDEX i_temptest ON temptest(col); +CREATE TEMP TABLE temptest(tcol int); +CREATE INDEX i_temptest ON temptest(tcol); +SELECT * FROM temptest; + tcol +------ +(0 rows) + +DROP INDEX i_temptest; +DROP TABLE temptest; +SELECT * FROM temptest; + col +----- +(0 rows) + +DROP INDEX i_temptest; +DROP TABLE temptest; +-- test temp table selects +CREATE TABLE temptest(col int); +INSERT INTO temptest VALUES (1); +CREATE TEMP TABLE temptest(tcol float); +INSERT INTO temptest VALUES (2.1); +SELECT * FROM temptest; + tcol +------ + 2.1 +(1 row) + +DROP TABLE temptest; +SELECT * FROM temptest; + col +----- + 1 +(1 row) + +DROP TABLE temptest; +-- test temp table deletion +CREATE TEMP TABLE temptest(col int); +\c +SELECT * FROM temptest; +ERROR: relation "temptest" does not exist +LINE 1: SELECT * FROM temptest; + ^ +-- Test ON COMMIT DELETE ROWS +CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS; +BEGIN; +INSERT INTO temptest VALUES (1); +INSERT INTO temptest VALUES (2); +SELECT * FROM temptest; + col +----- + 1 + 2 +(2 rows) + +COMMIT; +SELECT * FROM temptest; + col +----- +(0 rows) + +DROP TABLE temptest; +BEGIN; +CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; +SELECT * FROM temptest; + col +----- + 1 +(1 row) + +COMMIT; +SELECT * FROM temptest; + col +----- +(0 rows) + +DROP TABLE temptest; +-- Test ON COMMIT DROP +BEGIN; +CREATE TEMP TABLE temptest(col int) ON COMMIT DROP; +INSERT INTO temptest VALUES (1); +INSERT INTO temptest VALUES (2); +SELECT * FROM temptest; + col +----- + 1 + 2 +(2 rows) + +COMMIT; +SELECT * FROM temptest; +ERROR: relation "temptest" does not exist +LINE 1: SELECT * FROM temptest; + ^ +BEGIN; +CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1; +SELECT * FROM temptest; + col +----- + 1 +(1 row) + +COMMIT; +SELECT * FROM temptest; +ERROR: relation "temptest" does not exist +LINE 1: SELECT * FROM temptest; + ^ +-- ON COMMIT is only allowed for TEMP +CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS; +ERROR: ON COMMIT can only be used on temporary tables +CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; +ERROR: ON COMMIT can only be used on temporary tables +-- Test foreign keys +BEGIN; +CREATE TEMP TABLE temptest1(col int PRIMARY KEY); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "temptest1_pkey" for table "temptest1" +CREATE TEMP TABLE temptest2(col int REFERENCES temptest1) + ON COMMIT DELETE ROWS; +INSERT INTO temptest1 VALUES (1); +INSERT INTO temptest2 VALUES (1); +COMMIT; +SELECT * FROM temptest1; + col +----- + 1 +(1 row) + +SELECT * FROM temptest2; + col +----- +(0 rows) + +BEGIN; +CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "temptest3_pkey" for table "temptest3" +CREATE TEMP TABLE temptest4(col int REFERENCES temptest3); +COMMIT; +ERROR: unsupported ON COMMIT and foreign key combination +DETAIL: Table "temptest4" references "temptest3", but they do not have the same ON COMMIT setting. +-- Test manipulation of temp schema's placement in search path +create table public.whereami (f1 text); +insert into public.whereami values ('public'); +create temp table whereami (f1 text); +insert into whereami values ('temp'); +create function public.whoami() returns text + as $$select 'public'::text$$ language sql; +create function pg_temp.whoami() returns text + as $$select 'temp'::text$$ language sql; +-- default should have pg_temp implicitly first, but only for tables +select * from whereami; + f1 +------ + temp +(1 row) + +select whoami(); + whoami +-------- + public +(1 row) + +-- can list temp first explicitly, but it still doesn't affect functions +set search_path = pg_temp, public; +select * from whereami; + f1 +------ + temp +(1 row) + +select whoami(); + whoami +-------- + public +(1 row) + +-- or put it last for security +set search_path = public, pg_temp; +select * from whereami; + f1 +-------- + public +(1 row) + +select whoami(); + whoami +-------- + public +(1 row) + +-- you can invoke a temp function explicitly, though +select pg_temp.whoami(); + whoami +-------- + temp +(1 row) + +drop table public.whereami; Index: src/test/regress/results/2pc_on_delete_rows_session.out =================================================================== RCS file: src/test/regress/results/2pc_on_delete_rows_session.out diff -N src/test/regress/results/2pc_on_delete_rows_session.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_on_delete_rows_session.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,9 @@ +-- Temp table with ON DELETE ROWS option (session scope) +create temp table foo(x int) on commit delete rows; +begin; +insert into foo values(1); +prepare transaction 'onCommitDeleteRowsTempTableShouldSucceed'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +commit prepared 'onCommitDeleteRowsTempTableShouldSucceed'; +ERROR: prepared transaction with identifier "onCommitDeleteRowsTempTableShouldSucceed" does not exist +drop table foo; Index: src/test/regress/expected/2pc_temp_table_prepared_not_committed.out =================================================================== RCS file: src/test/regress/expected/2pc_temp_table_prepared_not_committed.out diff -N src/test/regress/expected/2pc_temp_table_prepared_not_committed.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_temp_table_prepared_not_committed.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,23 @@ +-- The purpose of this test is to test the proper termination of +-- a session with a prepared transaction that has accessed a temp table +-- +-- Session-scope temp table use case but exit after prepared +-- see 2pc_temp_table_commit_prepared.sql for committing that transaction +create temp table foo1(x int); +insert into foo1 values(1); +begin; +delete from foo1; +prepare transaction 'preparedNonCommittedFoo1'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +select * from foo1; + x +--- + 1 +(1 row) + +create temp table foo2(x int); +begin; +insert into foo2 values(1); +delete from foo2; +prepare transaction 'preparedNonCommittedFoo2'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time Index: src/test/regress/results/2pc_temp_table_savepoint.out =================================================================== RCS file: src/test/regress/results/2pc_temp_table_savepoint.out diff -N src/test/regress/results/2pc_temp_table_savepoint.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_temp_table_savepoint.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,8 @@ +-- Rollback to savepoint test case +BEGIN; +SAVEPOINT sp; +CREATE TEMP TABLE foo(bar int4); +INSERT INTO foo VALUES (1); +ROLLBACK TO sp; +PREPARE TRANSACTION 'savepointTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php'; +COMMIT PREPARED 'savepointTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php'; Index: src/test/regress/expected/2pc_on_commit_drop.out =================================================================== RCS file: src/test/regress/expected/2pc_on_commit_drop.out diff -N src/test/regress/expected/2pc_on_commit_drop.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_on_commit_drop.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,6 @@ +-- Temp table with ON COMMIT DROP option +begin; +create temp table foo(x int) on commit drop; +insert into foo values(1); +prepare transaction 'onCommitDropTempTableShouldSucceed'; +commit prepared 'onCommitDropTempTableShouldSucceed'; Index: src/test/regress/expected/2pc_dirty_buffer_check.out =================================================================== RCS file: src/test/regress/expected/2pc_dirty_buffer_check.out diff -N src/test/regress/expected/2pc_dirty_buffer_check.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_dirty_buffer_check.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,52 @@ +-- 2PC Dirty buffer check +begin; +create temp table foo(a int, b int, c int) on commit drop; +insert into foo values(1,1,1); +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +insert into foo select * from foo; +prepare transaction 'bcd'; +commit prepared 'bcd'; +begin; +create temp table bar(a int, b int, c int) on commit drop; +insert into bar values(1,1,1); +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +insert into bar select * from bar; +commit; Index: src/test/regress/results/2pc_persistent_table.out =================================================================== RCS file: src/test/regress/results/2pc_persistent_table.out diff -N src/test/regress/results/2pc_persistent_table.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_persistent_table.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,11 @@ +-- Creation of a persistent table (not temp) +begin; +create table paul(x int); +insert into paul values(1); +prepare transaction 'persistentTableShouldSucceed'; +commit prepared 'persistentTableShouldSucceed'; +-- Drop of a persistent table (not temp) +begin; +drop table paul; +prepare transaction 'dropPersistentTableShouldSucceed'; +commit prepared 'dropPersistentTableShouldSucceed'; Index: src/test/regress/results/2pc_temp_table_session.out =================================================================== RCS file: src/test/regress/results/2pc_temp_table_session.out diff -N src/test/regress/results/2pc_temp_table_session.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_temp_table_session.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,10 @@ +-- Session-scope temp table use case +create temp table foo(x int); +begin; +insert into foo values(1); +delete from foo; +prepare transaction 'dropTempTableShouldSucceed'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +commit prepared 'dropTempTableShouldSucceed'; +ERROR: prepared transaction with identifier "dropTempTableShouldSucceed" does not exist +drop table foo; Index: src/test/regress/results/2pc_temp_table_failure.out =================================================================== RCS file: src/test/regress/results/2pc_temp_table_failure.out diff -N src/test/regress/results/2pc_temp_table_failure.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/results/2pc_temp_table_failure.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,8 @@ +-- Existing non-empty temp table at commit time should still fail +begin; +create temp table foo(x int); +insert into foo values(1); +prepare transaction 'existingTempTableShouldFail'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +commit prepared 'existingTempTableShouldFail'; +ERROR: prepared transaction with identifier "existingTempTableShouldFail" does not exist Index: src/test/regress/expected/2pc_temp_table_rollback.out =================================================================== RCS file: src/test/regress/expected/2pc_temp_table_rollback.out diff -N src/test/regress/expected/2pc_temp_table_rollback.out --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/test/regress/expected/2pc_temp_table_rollback.out 1 Jan 1970 00:00:00 -0000 @@ -0,0 +1,7 @@ +-- Rollback prepared +BEGIN; +CREATE TEMP TABLE foo(bar int4); +PREPARE TRANSACTION 'rollbackTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php'; +ERROR: cannot PREPARE a transaction that has operated on temporary tables that are not dropped at commit time +ROLLBACK PREPARED 'rollbackTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php'; +ERROR: prepared transaction with identifier "rollbackTestCase_archives.postgresql.org_pgsql-hackers_2008-03_msg00017.php"does not exist
pgsql-hackers by date: