Re: [SQL] SELECT FOR UPDATE locks whole table

[D'Arcy J.M. Cain]

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  AND  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|  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.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Accent-insensitive

[Joel Burton]

On Sat, Dec 07, 2002 at 07:06:45PM -0300, Pedro Igor wrote:
> Thanks, you know if some possible release would have some internal to deal
> with this ?
> 
> Abraços
> Pedro Igor
> >
> > CREATE FUNCTION lower_ascii (text) RETURNS text AS '
> >   BEGIN
> > RETURN lower(to_ascii($1));
> >   END
> > ' language 'plpgsql';
> >
> > CREATE INDEX table_lower_ascii ON table(lower_ascii(field));

Pedro --

Please keep conversations on the list -- other people may know things I
don't (actually, they certainly will!), and it allows other people to
follow the conversation.

As for this being internal, I have no idea -- you could submit it as a
suggestion.

Given how easy it is to implement in plpgsql, I suspect this kind of thing will
stay out of the internals. Keep in mind that if you define this function
in your template1 database, you can have it created automatically in all
new databases you create, so it's one less thing to worry about.

HTH.

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster