Re: Why is RELEASE SAVEPOINT sometimes slow?

On 11 May 2012 11:16, Evan Martin <postgresql(at)realityexists(dot)net> wrote:
> I'm running a bulk import application against PostgreSQL 9.1.3, which has
> several stages and each stage follows the same general pattern:
>
> BEGIN TRANSACTION
> DELETE (many rows)
>
> CREATE SAVEPOINT
> INSERT
> ...
> RELEASE SAVEPOINT
>
> CREATE SAVEPOINT
> INSERT
> INSERT
> ... half an hour of inserts later ...
> RELEASE SAVEPOINT
>
> COMMIT TRANSACTION
>
> I find that for one particular stage of the import the RELEASE SAVEPOINT
> command consistently takes about 6 minutes, while for the rest of them it's
> very quick. COMMIT TRANSACTION is always very quick.
>
> At first I thought the discrepancy may be because that particular stage has
> many INSERT statements inside one savepoint, while other stages create many
> savepoints with a small amount of work in each. However, if I take out the
> savepoints entirely then the COMMIT TRANSACTION statement for that stage
> takes 6 minutes, while for the rest of them it's still very quick.
>
> Could anyone explain what may be happening here? What is PostgreSQL doing
> when I call RELEASE SAVEPOINT that it seems to otherwise do in COMMIT
> TRANSACTION?

Sounds interesting.

Please can you produce a test case that demonstrates this, then post
the SQL file and an output of a run that shows the negative timing?

Thanks

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services