Re: Database size Vs performance degradation

On Wed, 30 Jul 2008, Craig James wrote:
> You don't have to change the application. One of the great advantages of
> Postgres is that even table creation, dropping and renaming are
> transactional. So you can do the select / drop / rename as a transaction by
> an external app, and your main application will be none the wiser. In
> pseudo-SQL:
>
> begin
> create table new_table as (select * from old_table);
> create index ... on new_table ... (as needed)
> drop table old_table
> alter table new_table rename to old_table
> commit

I believe this SQL snippet could cause data loss, because there is a
period during which writes can be made to the old table that will not be
copied to the new table.

On a side note, I would be interested to know what happens with locks when
renaming tables. For example, if we were to alter the above SQL, and add a
"LOCK TABLE old_table IN ACCESS EXCLUSIVE" line, would this fix the
problem? What I mean is, if the application tries to run "INSERT INTO
old_table ...", and blocks on the lock, when the old_table is dropped,
will it resume trying to insert into the dropped table and fail, or will
it redirect its attentions to the new table that has been renamed into
place?

Also, if a lock is taken on a table, and the table is renamed, does the
lock follow the table, or does it stay attached to the table name?

Anyway, surely it's much safer to just run VACUUM manually?

Matthew

--
Change is inevitable, except from vending machines.