I need to replace a table with a new one.
Example..
I create a script that continously does selects like
select count(*) from tmp_deleteme_francisco;
.... enough selects to last the duration of second script
select count(*) from tmp_deleteme_francisco;
Another script then does
begin;
select * into tmp_deleteme_francisco_2 from xxx;
alter table tmp_deleteme_francisco rename to tmp_deleteme_francisco_old;
alter table tmp_deleteme_francisco_2 rename to tmp_deleteme_francisco;
drop table tmp_deleteme_francisco_old;
commit;
That results in the script doing the selects getting
could not open relation with OID ####.
I thought using an explicit access exclusive lock would do the trick and
tried a few variations including
begin;
select * into tmp_deleteme_francisco_2 from xxx;
lock tmp_deleteme_francisco in access exclusive mode;
alter table tmp_deleteme_francisco rename to tmp_deleteme_francisco_old;
alter table tmp_deleteme_francisco_2 rename to tmp_deleteme_francisco;
drop table tmp_deleteme_francisco_old;
commit;
If I get the access exclusive lock I thought nothing else could be
accessing the table after the lock is obtained.
Any ideas?
Postgresql 8.4.1 on CentOS 5.3
