Rotate a table in PostgreSQL

Question

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;

• xenoterracide: 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.
• user1822: Why use drop & create? A simple TRUNCATE has the same effect.
• filiprem: xeno, I heard that content duplication is a Bad Thing (tm) ;-)
• filiprem: a_horse, two reasons (1) TRUNCATE is not mvcc safe, (2) it's a "legacy app", ie. expensive to change
• filiprem: 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!

Answer 1

What about ALTER TABLE RENAME followed by CREATE TABLE?

• Ghislain Leveque: what about relations (foreign keys from and to the table), how are they handled in this case ?
• gilad905: not only keys, also e.g views that select from this table will deny dropping the old table, because they depend on it

Answer 2

Based on what I have found here:

This is the most optimized way:

CREATE TABLE parent ... ;
CREATE TABLE child_1() INHERITS (parent);
CREATE TABLE child_2() INHERITS (parent);

INSERT commands are done always into child_1.

SELECT commands are done on parent.

On your priod of choice, run the follwing sql for rotating child_1 with child_2:

TRUNCATE TABLE child_2;
BEGIN;
ALTER TABLE child_1 RENAME TO child_tmp;
ALTER TABLE child_2 RENAME TO child_1;
ALTER TABLE child_tmp RENAME TO child_2;
COMMIT;

This is much faster comparing to

DELETE FROM parent WHERE time < now() - interval 'given period'

This also avoids table fragmentation, so SELECT queries work faster on the table.