Ah. So it does. Testing with two psql sessions locks as you said, and moving the DROP INDEX to a separate transaction give the results I was looking for.
Thanks, Mike __________________________________________________________________________________ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com <http://www.rrdonnelley.com/> * <mike.blackw...@rrd.com>* On Thu, Nov 29, 2012 at 10:54 AM, Scott Marlowe <scott.marl...@gmail.com>wrote: > On Thu, Nov 29, 2012 at 9:38 AM, Mike Blackwell <mike.blackw...@rrd.com> > wrote: > > On Wed, Nov 28, 2012 at 9:48 PM, Claudio Freire <klaussfre...@gmail.com> > > wrote: > >> > >> On Wed, Nov 28, 2012 at 8:28 PM, Jeff Davis <pg...@j-davis.com> wrote: > >> > > >> > The main problem with a long-running delete or update transaction is > >> > that the dead tuples (deleted tuples or the old version of an updated > >> > tuple) can't be removed until the transaction finishes. That can cause > >> > temporary "bloat", but 1.5M records shouldn't be noticeable. > >> > >> Not really that fast if you have indices (and who doesn't have a PK or > >> two). > >> > >> I've never been able to update (update) 2M rows in one transaction in > >> reasonable times (read: less than several hours) without dropping > >> indices. Doing it in batches is way faster if you can't drop the > >> indices, and if you can leverage HOT updates. > > > > > > What I'm trying at this point is: > > > > BEGIN; > > DROP INDEX -- only one unique index exists > > DELETE FROM table; > > COPY table FROM STDIN; > > COMMIT; > > CREATE INDEX CONCURRENTLY; > > > > Do I understand correctly that DROP/CREATE index are not transactional, > and > > thus the index will disappear immediately for other transactions? Am I > > better off in that case moving the DROP INDEX outside the transaction? > > > > The access pattern for the table is such that I can afford the occasional > > stray hit without an index during the reload time. It's been pretty > quick > > using the above. > > Drop / create index ARE transactional, like most other things in > postgresql (only drop / create database and drop / create tablespace > are non-transactional). Your current sequence will result in the > table you are dropping the index on being locked for other > transactions until commit or rollback. Run two psql sessions and test > it to see. >
