From: | Mark Roberts <mailing_lists(at)pandapocket(dot)com> |
---|---|
To: | Matthew Wakeling <matthew(at)flymine(dot)org> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Database size Vs performance degradation |
Date: | 2008-07-30 16:49:38 |
Message-ID: | 1217436578.6288.44.camel@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-07-30 17:18:25 | Re: Database size Vs performance degradation |
Previous Message | Greg Smith | 2008-07-30 16:48:14 | Re: Database size Vs performance degradation |