Replication with Drop: could not open relation with OID

From: salah jubeh <s_jubeh(at)yahoo(dot)com>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Replication with Drop: could not open relation with OID
Date: 2013-06-20 15:58:35
Message-ID: 1371743915.74923.YahooMailNeo@web122205.mail.ne1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I have a database server which do a complex  views calculation,  the result of those views are shipped to another database servers via a simple  replication tool which have a high  client loads.

The tool  is creating a  table, and indexes based on predefined conf.,   then drop the table that needs to be synched then rename the temporary tables.  i.e.

BEGIN;
DROP TABLE IF EXISTS y;  -- the table I want to replace it
ALTER TABLE x RENAME TO y;  -- x contains the data which synched from server  (already created)
ALTER INDEX ..... RENAME TO .....; -- rename indexes
COMMIT;

In version 8.3 , 8.4, and 9.1, I get errors could not open relation with OID; However with version 9.2 every thing works fine, I tried to lock the table in access exclusive mode  before dropping it   i.e
 
BEGIN;
LOCK TABLE y IN ACCESS EXCLUSIVE MODE;
DROP TABLE IF EXISTS y;  -- the table I want to replace
ALTER TABLE x RENAME TO y;  -- x is the temporay table
ALTER INDEX x_x_name_idx RENAME TO y_x_name_idx; -- rename indexes
COMMIT;

But I still get the same errors. 

I have seen this post 

http://dba.stackexchange.com/questions/16909/rotate-a-table-in-postgresql and I used the same strategy for testing. In version 9.2 I was not able at all to generate the error. In 8.3, 8.4, 9.1 I was able to generate the errors.

Since the tables, I am creating are quite big (several millions of record) , I am using drop and rename  to speed the creation. For small table sizes,  this problem does not appear often, but in my case it pops up often because of the table size.

Is there any way to solve this for the mensioned versions

Regards

Responses

Browse pgsql-general by date

  From Date Subject
Next Message itishree sukla 2013-06-20 16:15:02 coalesce function
Previous Message Dmitriy Igrishin 2013-06-20 15:08:35 Frontend/backend protocol improvements proposal (request).