\echo Creation of a persistent table (not temp)
begin;
create table paul(x int);
insert into paul values(1);
prepare transaction 'persistentTableShouldSucceed';
commit prepared 'persistentTableShouldSucceed';

\echo Drop of a persistent table (not temp)
begin;
drop table paul;
prepare transaction 'dropPersistentTableShouldSucceed';
commit prepared 'dropPersistentTableShouldSucceed';

\echo 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';

\echo Session-scope temp table use case
create temp table foo(x int);
begin;
insert into foo values(1);
delete from foo;
prepare transaction 'dropTempTableShouldSucceed';
commit prepared 'dropTempTableShouldSucceed';
drop table foo;

\echo 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';

\echo 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';
commit prepared 'onCommitDeleteRowsTempTableShouldSucceed';
drop table foo;

\echo 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';
commit prepared 'onCommitDeleteRowsTempTableShouldSucceed';
drop table foo;

\echo Rollback to savepoint test case
BEGIN;
SAVEPOINT sp;
CREATE TEMP TABLE foo(bar int4);
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';

\echo Dirty buffer check
begin;
create temp table foo(a int, b int, c int) on commit drop;
select relname, relfilenode from pg_class where relname='foo';
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;
select relname, relfilenode from pg_class where relname='bar';
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;

\echo 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';
commit prepared 'existingTempTableShouldFail';