From: Florian Weimer <fweimer@bfk.de>
To: pgsql-sql@postgresql.org
Subject: INSERT/UPDATEs cycles and lack of phantom locking
Date: Wed, 19 Jul 2006 10:30:39 +0200
Message-ID: <82zmf60zf4.fsf@mid.bfk.de> (raw)
I've got several tables where I need to either insert new records, or
update existing ones (identified based on the primary key). For
performance reasons, I want to do this in batches, so I plan to use
something like this:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; -- SERIALIZABLE
...
CREATE TEMPORARY TABLE tmp
(key TEXT NOT NULL, new_val INTEGER NOT NULL);
COPY tmp (key, new_val) FROM STDIN;
...
\.
-- SAVEPOINT tmp_created; -- (see below)
CREATE TEMPORARY TABLE tmp2 AS SELECT tmp.key, new_val, real.val AS old_val
FROM tmp LEFT OUTER JOIN real ON tmp.key = real.key;
UPDATE real SET val = new_val + old_val FROM tmp2
WHERE old_val IS NOT NULL AND tmp2.key = real.key;
INSERT INTO real SELECT key, new_val FROM tmp2 WHERE old_val IS NULL;
If this is run concurrently, the INSERT may fail. In this case, I
rerun the transaction. Actually, I want to rollback to the
tmp_created checkpoint, but I don't think this will pick up the new
rows in the "real" table, and the INSERT will fail again.
Usually, the batch size is small enough that the necessary data is
still cached, and concurrent updates aren't the norm, so this approach
(complete transaction rollback) is not completely infeasible.
However, I still wonder if there is a more straightforward solution.
Serializing the updates isn't one, I think. Is there some form of
table-based advisory locking which I could use? This way, I wouldn't
lock out ordinary readers (which is crucial), but the reading part of
an updating transaction would be blocked. For bonus points, deadlocks
would be automatically detected by PostgreSQL (although I would order
the locks properly in the usual case, but I can't guarantee this for
all codepaths due to the modularity of the application).
Florian
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Durlacher Allee 47 tel: +49-721-96201-1
D-76131 Karlsruhe fax: +49-721-96201-99
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fweimer@bfk.de
Subject: Re: INSERT/UPDATEs cycles and lack of phantom locking
In-Reply-To: <82zmf60zf4.fsf@mid.bfk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox