Re: Transactions and temp tables - Mailing list pgsql-hackers

Emmanuel Cecchet wrote:
>> What's the purpose of checking that a table is empty on prepare? I think
>> I'd feel more comfortable with the approach of only accepting PREPARE
>> TRANSACTIOn if the accessed temp tables have been created and destroyed
>> in the same transaction, to avoid possibly surprising behavior when a
>> temp table is kept locked by a prepared transaction and you try to drop
>> it later in the sesssion, but the patch allows more than that. I guess
>> accessing an existing ON COMMIT DELETE ROWS temp table would also be OK,
> 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.

>>  but checking that there's no visible rows in the table doesn't achieve
>> that.
> If the relation exist but contains no row, is it possible that the table 
> is not empty? What would I need to do to ensure that the table is empty?

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.

>> 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.

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


> By the way, 
> does Postgres support child temp tables?

Yes.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com