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.Blackwell@rrd.com http://www.rrdonnelley.com
On Thu, Nov 29, 2012 at 9:38 AM, Mike Blackwell <mike.blackwell@rrd.com> wrote: > On Wed, Nov 28, 2012 at 9:48 PM, Claudio Freire <klaussfreire@gmail.com> > wrote: >> >> On Wed, Nov 28, 2012 at 8:28 PM, Jeff Davis <pgsql@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.
