Re: Savepoints in transactions for speed?

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(dot)Blackwell(at)rrd(dot)com
http://www.rrdonnelley.com

<http://www.rrdonnelley.com/>
* <Mike(dot)Blackwell(at)rrd(dot)com>*

On Thu, Nov 29, 2012 at 10:54 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Thu, Nov 29, 2012 at 9:38 AM, Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com>
> wrote:
> > On Wed, Nov 28, 2012 at 9:48 PM, Claudio Freire <klaussfreire(at)gmail(dot)com>
> > wrote:
> >>
> >> On Wed, Nov 28, 2012 at 8:28 PM, Jeff Davis <pgsql(at)j-davis(dot)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.
>