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 <