On December 6, 2002 02:10 pm, Bruce Momjian wrote:
> It should lock only the rows you retrieved, but I have no idea how FOR
> UPDATE and INTO TEMP behave. My guess is that it should work fine, but
> I have never seen those two used together before.
Turns out that it wasn't the SELECT ... FOR UPDATE that was causing the
problem. I did a test like this.
> > SELECT certificate_id
> > INTO TEMP TABLE x_certs
> > FROM certificate
> > WHERE cert_status = 0 AND
> > certificate_id BETWEEN 1111 AND 2222 AND
> > client_id IN (1, 2, 3)
> > ORDER BY certificate_id
> > FOR UPDATE;
Basically this query after a BEGIN TRANSACTION except without the INTO part.
Then I went to another window and tried to update two certificates, one
inside and one outside the range. The first failed and the second succeeded
as expected. I then updated one of the certs in the range. After that I
could not update any certificates until I closed the transaction. Very weird.
I then built a new database and repeated the experiment with fresh, simple
tables and was able to confirm that normally PostgreSQL does NOT have this
behaviour so then I started thinking about differences between the simple
setup and our real production setup. One thing that I thought of was that
the real database has this trigger on certificate.
CREATE TRIGGER mk_cardnum BEFORE INSERT OR UPDATE ON certificate FOR EACH ROW EXECUTE PROCEDURE mk_cardnum
(cardnum,certificate_id, validation);
mk_cardnum is a C function that reads certificate_id and validation and
writes something into cardnum. My understanding is that this only affects
the row(s) being updated. It must since this table has over seven million
records and we would notice if it took minutes to do a simple update.
I couldn't find anything in the docs or web specifically about this. Does
anyone have any ideas?
Adding hackers as this may be an internal issue.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.