Thread: Using a lock to avoid: could not open relation with OID

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

On 12/01/2010 2:04 AM, Francisco Reyes wrote:
> 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 ####.

Possible workaround: Instead of your table creation, renaming and
dropping, use TRUNCATE.

--
Craig Ringer

Craig Ringer writes:

> Possible workaround: Instead of your table creation, renaming and
> dropping, use TRUNCATE.

Have to support both 8.1 and 8.4.
If I recall correctly 8.1 did not support truncate inside of a transaction.

We are in the process of upgrading everything to 8.4, but until then.. have
to deal with all the 8.1 and 8.2 machines.