Skip site navigation (1) Skip section navigation (2)

Re: Database size Vs performance degradation

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Database size Vs performance degradation
Date: 2008-07-30 16:16:11
Message-ID: Pine.LNX.4.64.0807301656020.4250@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2008-07-30 16:48:14
Subject: Re: Database size Vs performance degradation
Previous:From: Mark RobertsDate: 2008-07-30 15:46:16
Subject: Re: Database size Vs performance degradation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group