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
rowspsql:/tmp/gg:23: NOTICE: oid temp_table
{temp_table106538relpages:1reltuples:1relallvisible:0reltoastrelid:*106541*}psql:/tmp/gg:23:
NOTICE: rel size 32768psql:/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: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
