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 <<EOF
BEGIN;
DROP TABLE x;
CREATE TABLE x ( num integer );
COMMIT;
EOF
C. create empty database and table x.
$ createdb dev
$ psql dev -c "CREATE TABLE x ( num integer )"
D. Start 30 clients.
$ pgbench -c 30 dev -T 60 -n -f pgbench.minimal.sql
E. (in other terminal) Run the "rotator".
$ psql dev -f rotate.x.sql
F. observe what happens to the clients (just after the "rotator" COMMITs).
Client 4 aborted in state 1: ERROR: could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
^
Client 0 aborted in state 1: ERROR: could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
^
(...and so on - every client fails)
What's interesting - even if the clients work in "new connection for
each transaction" mode (add "-C" option to pgbench), same error
occurs. Not always, and not for every client, but it does.
Thanks,
Filip
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
