DROP and re-CREATE a table, and ERROR: could not open relation with OID xyz

Поиск
Список
Период
Сортировка
Искать

DROP and re-CREATE a table, and ERROR: could not open relation with OID xyz

От:
Filip Rembiałkowski <filip.rembialkowski@gmail.com>
Дата:
Hi all,

I have a program which replaces (DROP + CREATE) some frequently-used table.

Sometimes, just after the table is replaced, I get "ERROR:  could not
open relation with OID xyz" from concurrent clients accessing the
table.

One could say this behaviour breaks transaction isolation... Does it?

I understand this is caused by system catalogs being cached by
postgres backend (which is normally a good thing). Am I right on it?
 Is there any way to force a backend to "forget" table OIDs (DISCARD
does not help)?

I know it will help if I switch to "DELETE and INSERT" pattern instead
of "DROP and CREATE" pattern.
But this is some legacy program and we do not want to change it unless
absolutely needed.

I will be grateful for any suggestions how to get rid of this problem.

The goal is to rotate the table transparently for other clients.


Here is the minimal test case that I reduced the problem to:

A. This is the client (multiple clients will run this SELECT in parallel).
$ echo "SELECT num FROM x;" > pgbench.minimal.sql

B. This is the "rotator".
$ cat > rotate.x.sql <
FAQ