Subject: Re: Database size Vs performance degradation
From: Mark Roberts <mailing_lists@pandapocket.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <Pine.LNX.4.64.0807301656020.4250@aragorn.flymine.org>
References: 
 <35FABCF85D99464FB00BC5123DC2A70A051D0FB8@s228130hz1ew09.apptix-01.savvis.net>
	<276541.4189.qm@web25805.mail.ukl.yahoo.com>
	<35FABCF85D99464FB00BC5123DC2A70A051D11E1@s228130hz1ew09.apptix-01.savvis.net>
	<48908988.7020204@emolecules.com>
	<Pine.LNX.4.64.0807301656020.4250@aragorn.flymine.org>
Content-Type: text/plain
Date: Wed, 30 Jul 2008 09:49:38 -0700
Message-Id: <1217436578.6288.44.camel@localhost>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


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