On Wed, 2008-07-30 at 17:16 +0100, Matthew Wakeling wrote: > > 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.
It could indeed cause data loss. > 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? Yes, that would resolve the issue. It would also block the application's writes for however long the process takes (this could be unacceptable). > 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? The lock will follow the table itself (rather than the table name). > Anyway, surely it's much safer to just run VACUUM manually? Generally, you would think so. The problem comes from Vacuum blocking the application process' writes. -Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
