Re: [HACKERS] Locking when concurrent updated of foreign references

2011-04-11 Thread Jesper Krogh

On 2011-04-11 20:18, Jesper Krogh wrote:

Hi.

This seem a bit strange to me. In short:

Not any more I.. I guess what made me a bit confused was that
a update table set key = value would acually block out changes
on tables referencing this tuple even if the referenced column wasn't
effected by the update.

But when the locking is done row-level then it is correct
to do it that way. It would allthough be nice with a weaker
locklevel for that kind of updates (I have no clue if that is
a hard problem).

--
Jesper

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Locking when concurrent updated of foreign references

2011-04-11 Thread Alvaro Herrera
Excerpts from Jesper Krogh's message of lun abr 11 17:07:33 -0300 2011:

 But when the locking is done row-level then it is correct
 to do it that way. It would allthough be nice with a weaker
 locklevel for that kind of updates (I have no clue if that is
 a hard problem).

http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Locking when concurrent updated of foreign references

2011-04-11 Thread Jesper Krogh

On 2011-04-11 23:30, Alvaro Herrera wrote:

Excerpts from Jesper Krogh's message of lun abr 11 17:07:33 -0300 2011:


But when the locking is done row-level then it is correct
to do it that way. It would allthough be nice with a weaker
locklevel for that kind of updates (I have no clue if that is
a hard problem).

http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/


That looks exactly what I have been seeing.

Naive suggestion (at least to part of the problem):
Would it be possible to identify updates that never
can violate any constraints and not do any verification
of foreign keys on the update and only pick a lock
that block concurrent updates of the same tuple?

UPDATE table set something which is neither referenced or a reference;
would all be of that type.

Would allthough require the database to examine
the UPDATE statement and in comparison with the
table definition figure out which of the column are
safe to update.

There might actually be a potential speedup since the update
would require to go visit the foreign table at all.

Jesper
--
Jesper


--
Jesper

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers