Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Problem description

I need to rotate (with DROP and CREATE) a table which is heavily used by other clients.

At present, I have a program which replaces (DROP + CREATE) this 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.

The test case

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). We will use pgbench for stressing the database.

$ 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)

Comments and ideas

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.

I asked this question some time ago on mailing list: http://archives.postgresql.org/pgsql-general/2012-04/msg00431.php - this SO post is just a copy.

For more audience: Same thing applies to rotating a partition. So it might be interesting for all people who use partitioning.

This is all on PostgreSQL 9.0, Linux.

Solution

(thanks to Chris Travers)

filip@srv:~$ cat rotate.x.sql
BEGIN;
DROP TABLE IF EXISTS xold;
ALTER TABLE x RENAME TO xold;
CREATE TABLE x ( num integer );
COMMIT;
share|improve this question
    
please do not expect people to go read a thread on a mailing list to get the details... all the details should be here. Linking is fine. –  xenoterracide Apr 23 '12 at 19:23
2  
Why use drop & create? A simple TRUNCATE has the same effect. –  a_horse_with_no_name Apr 23 '12 at 20:17
    
xeno, I heard that content duplication is a Bad Thing (tm) ;-) –  filiprem Apr 24 '12 at 0:10
1  
a_horse, two reasons (1) TRUNCATE is not mvcc safe, (2) it's a "legacy app", ie. expensive to change –  filiprem Apr 24 '12 at 0:11
    
People, I have improved the question, so I would be thankful if you review it once more and could you please remove the mod-downs. Thanks! –  filiprem Apr 25 '12 at 16:56

1 Answer 1

up vote 3 down vote accepted

What about ALTER TABLE RENAME followed by CREATE TABLE?

share|improve this answer
    
thanks! simple and unexpected :-) –  filiprem Sep 4 '12 at 16:21
    
what about relations (foreign keys from and to the table), how are they handled in this case ? –  Ghislain Leveque Nov 27 at 9:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.