I dug a bit further into this and found that the code was also creating
and releasing a savepoint for each insert (and there were over 10,000 of
them). When I removed that the delay at the end disappeared.
Regards,
Evan
On 11/05/2012 8:26 PM, Simon Riggs wrote:
On 11 May 2012 11:16, Evan Martin<postgresql@xxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
