Thread: Replication with Drop: could not open relation with OID
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;
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;
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
Hello,
I have just found this post
http://grokbase.com/t/postgresql/pgsql-hackers/10ces3w9kz/alter-table-replace-with/nested/page/2#responses_tab_top
and it helped a lot
From: salah jubeh <s_jubeh@yahoo.com>
To: pgsql <pgsql-general@postgresql.org>
Sent: Thursday, June 20, 2013 5:58 PM
Subject: [GENERAL] Replication with Drop: could not open relation with OID
But I still get the same errors.
I have seen this post
I have just found this post
http://grokbase.com/t/postgresql/pgsql-hackers/10ces3w9kz/alter-table-replace-with/nested/page/2#responses_tab_top
and it helped a lot
Thanks
From: salah jubeh <s_jubeh@yahoo.com>
To: pgsql <pgsql-general@postgresql.org>
Sent: Thursday, June 20, 2013 5:58 PM
Subject: [GENERAL] Replication with Drop: could not open relation with OID
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;
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;
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
On Thu, 20 Jun 2013 08:58:35 -0700 (PDT) salah jubeh <s_jubeh@yahoo.com> wrote: > Hello, > > > I have a database server which do a complex views calculation, the result of those views are shipped to another databaseservers 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 thenrename 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 worksfine, 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. Inversion 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 tablesize. > > > Is there any way to solve this for the mensioned versions In sqlite, which don't have the wonderful features Postgres has, you can do that using a column with the data version. Createa View to the table that enforces current data version. Add new data using a new data version number. When you wantto switch, update the view and delete old version data. You must add version column as the first index entry on the indexesyou create. HTH > > Regards --- --- Eduardo Morras <emorrasg@yahoo.es>