I would like to refresh the topic and add another report about the issue that just happened to me. I'm sure it's the toast table that cannot be opened inside the function. I have added following RAISE NOTICE clauses to it and run analyze inside of the function:
analyze verbose temp_table;
raise notice 'oid temp_table %', ( SELECT array_agg(relname::TEXT|| relfilenode::TEXT|| 'relpages:'||relpages::TEXT|| 'reltuples:' || reltuples::TEXT|| 'relallvisible:' ||relallvisible::TEXT||'reltoastrelid:'|| reltoastrelid::TEXT) FROM pg_class where relname= 'temp_table');
raise notice 'rel size %', (select pg_total_relation_size('temp_table'));
It's pointing to the toast table:
1 live rows and 1 dead rows; 1 rows in sample, 1 estimated total rows
psql:/tmp/gg:23: NOTICE: oid temp_table {temp_table106538relpages:1reltuples:1relallvisible:0reltoastrelid:106541}
psql:/tmp/gg:23: NOTICE: rel size 32768
psql:/tmp/gg:23: ERROR: could not open relation with OID 106541 Thank you for the advice about ON COMMIT DROP - it's working. When the table size is smaller, about 16k this issue simply disappears.
Sent from the
PostgreSQL - general mailing list archive at Nabble.com.
