Re: Prepare Transaction support for ON COMMIT DROP temporary tables

On Sat, Jan 19, 2019 at 10:39:43AM +0900, Michael Paquier wrote:
> I have not looked at the patch in details, but we should really be
> careful that if we do that the namespace does not remain behind when
> performing such transactions so as it cannot be dropped. On my very
> recent lookups of this class of problems you can easily finish by
> blocking a backend from shutting down when dropping its temporary
> schema, with the client, say psql, already able to disconnect. So as
> long as the 2PC transaction is not COMMIT PREPARED the backend-side
> wait will not be able to complete, blocking a backend slot in shared
> memory. PREPARE TRANSACTION is very close to a simple commit in terms
> of its semantics, while COMMIT PREPARED is just here to finish
> releasing resources.

I have been looking at this patch, which conflicts on HEAD by the way
(Sorry!) still it is easy enough to get rid of the conflict, and from
what I can see it does not completely do its job. Simply take the
following example:
=# begin;
BEGIN
=# create temp table aa (a int ) on commit drop;
CREATE TABLE
=# prepare transaction 'ad';
PREPARE TRANSACTION
=# \q

This causes the client to think that the session is finished, but if
you look closer at the backend it is still pending to close until the
transaction is COMMIT PREPARED:
michael 22126 0.0 0.0 218172 15788 ? Ss 15:59 0:00
postgres: michael michael [local] idle waiting

Here is a backtrace:
#7 0x00005616900d0462 in LockAcquireExtended (locktag=0x7ffdd6bd5390,
lockmode=8, sessionLock=false, dontWait=false,
reportMemoryError=true, locallockp=0x0)
at lock.c:1050
#8 0x00005616900cf9ab in LockAcquire (locktag=0x7ffdd6bd5390,
lockmode=8, sessionLock=false, dontWait=false) at lock.c:713
#9 0x00005616900ced07 in LockDatabaseObject (classid=2615,
objid=16385, objsubid=0, lockmode=8) at lmgr.c:934
#10 0x000056168fd8cace in AcquireDeletionLock (object=0x7ffdd6bd5414,
flags=0) at dependency.c:1389
#11 0x000056168fd8b398 in performDeletion (object=0x7ffdd6bd5414,
behavior=DROP_CASCADE, flags=29) at dependency.c:325
#12 0x000056168fda103a in RemoveTempRelations (tempNamespaceId=16385)
at namespace.c:4142
#13 0x000056168fda106d in RemoveTempRelationsCallback (code=0, arg=0)
at namespace.c:4161

If you really intend to drop any trace of the objects at PREPARE
phase, that does not seem completely impossible to me, still you would
also need handling for the case where the temp table created also
creates the temporary schema for the session.
--
Michael