Re: can't drop table due to reference from orphaned temp function

Hi,

On 2022-02-18 18:27:37 +1100, Miles Delahunty wrote:
> You will note that in my example below, I've appended a thousand
> commented-out lines to the end of the function definition. If I remove
> these, the problem doesn't appear. So somehow the length of the function is
> a factor. Similarly, creating a temp table is also seemingly required to
> elicit the bug.

Huh. There's a very relevant error message in the log:

2022-02-19 09:39:12.628 PST [3731015][client backend][2/11:0] LOG:  statement: select pg_temp.mytempfunc();
2022-02-19 09:39:12.642 PST [3731015][client backend][2/12:730] FATAL:  cannot fetch toast data without an active
snapshot
2022-02-19 09:39:12.642 PST [3731015][client backend][:0] LOG:  disconnection: session time: 0:00:00.165 user=andres
database=mydbhost=[local]
 
(middle line)

See backtrace below [1]. The same problem does *not* exist when starting to
use the same temp schema in a new session (which drops the old contents
first), which kind of explains why we've not previously noticed this.

But even so, I'm surprised we haven't noticed this before.

It's pretty easy to fix, we just need a
PushActiveSnapshot(GetTransactionSnapshot());
...
PopActiveSnapshot();

around the RemoveTempRelations().


A naive test for this wouldn't be reliable, because the client doesn't wait
for the disconnect to finish. Thus verifying that the temp catalog entries are
gone would race against the removal.

But I think we can make it reliable by using a session level advisory lock?
RemoveTempRelationsCallback() is registered after ShutdownPostgres(), so
session level advisory locks aren't released before the temp relations cleanup
is complete.

Greetings,

Andres Freund

[1]
#0  init_toast_snapshot (toast_snapshot=0x7fffce35c9a0) at
/home/andres/src/postgresql/src/backend/access/common/toast_internals.c:661
#1  0x00007fdbfaef6ce5 in toast_delete_datum (rel=0x7fdbf768f768, value=140582582660640, is_speculative=false)
    at /home/andres/src/postgresql/src/backend/access/common/toast_internals.c:429
#2  0x00007fdbfafcd7b3 in toast_delete_external (rel=0x7fdbf768f768, values=0x7fffce35d170, isnull=0x7fffce35cb30,
is_speculative=false)
    at /home/andres/src/postgresql/src/backend/access/table/toast_helper.c:334
#3  0x00007fdbfaf6c312 in heap_toast_delete (rel=0x7fdbf768f768, oldtup=0x7fffce3603e0, is_speculative=false)
    at /home/andres/src/postgresql/src/backend/access/heap/heaptoast.c:73
#4  0x00007fdbfaf52f3d in heap_delete (relation=0x7fdbf768f768, tid=0x7fdbee3942c4, cid=3, crosscheck=0x0, wait=true,
tmfd=0x7fffce360480,changingPart=false)
 
    at /home/andres/src/postgresql/src/backend/access/heap/heapam.c:3074
#5  0x00007fdbfaf53002 in simple_heap_delete (relation=0x7fdbf768f768, tid=0x7fdbee3942c4)
    at /home/andres/src/postgresql/src/backend/access/heap/heapam.c:3114
#6  0x00007fdbfb034236 in CatalogTupleDelete (heapRel=0x7fdbf768f768, tid=0x7fdbee3942c4) at
/home/andres/src/postgresql/src/backend/catalog/indexing.c:352
#7  0x00007fdbfb123a0b in RemoveFunctionById (funcOid=16401) at
/home/andres/src/postgresql/src/backend/commands/functioncmds.c:1322
#8  0x00007fdbfb022248 in doDeletion (object=0x7fdbfbe3223c, flags=29) at
/home/andres/src/postgresql/src/backend/catalog/dependency.c:1423
#9  0x00007fdbfb021f5e in deleteOneObject (object=0x7fdbfbe3223c, depRel=0x7fffce3606d0, flags=29)
    at /home/andres/src/postgresql/src/backend/catalog/dependency.c:1311
#10 0x00007fdbfb0207e2 in deleteObjectsInList (targetObjects=0x7fdbfbe321e0, depRel=0x7fffce3606d0, flags=29)
    at /home/andres/src/postgresql/src/backend/catalog/dependency.c:271
#11 0x00007fdbfb020897 in performDeletion (object=0x7fffce360704, behavior=DROP_CASCADE, flags=29)
    at /home/andres/src/postgresql/src/backend/catalog/dependency.c:356
#12 0x00007fdbfb03a7b8 in RemoveTempRelations (tempNamespaceId=16399) at
/home/andres/src/postgresql/src/backend/catalog/namespace.c:4277
#13 0x00007fdbfb03a7eb in RemoveTempRelationsCallback (code=0, arg=0) at
/home/andres/src/postgresql/src/backend/catalog/namespace.c:4296
#14 0x00007fdbfb3ee16c in shmem_exit (code=0) at /home/andres/src/postgresql/src/backend/storage/ipc/ipc.c:239