Re: Replacing a table

Francisco Reyes <lists@natserv.com> writes:
> I thought that given that the queries for these report tables take 20 to
> 30 minutes I would try something like
> begin
> drop
> select ... into table
> commit

> However once I ran a test case I was unable to connect to the table.

Yeah, because the DROP will acquire exclusive lock on the table;
this approach is rollback-safe but not transaction-friendly.
Consider

begin
select into new_table
drop table
alter table new_table rename to table
commit

which does not hold the exclusive lock as long.

> What would be the way to replace tables?
> The output is fairly small so I was thinking about something like:
> Create data to cursor
> drop table
> select from cursor into table

If you don't mind copying the data then this is probably the best
bet:

begin
select ... into temp table ttable
lock table table
delete from table
insert into table select * from ttable
drop table ttable
commit

(A vacuum or truncate would be nice here to actually remove the deleted
rows, but you can't put either inside a transaction block at present.
A "vacuum table" outside the transaction block will have to do instead.)

The reason this is better is that it doesn't DROP the table, which means
you do not lose indexes, foreign keys, views, etc that reference the
table.  Also it eliminates a race condition: someone trying to access
the old table just after you drop it would get an error, even though
there is a new table of the same name by the time he gets to proceed.

            regards, tom lane