Re: Lock Modes (Documentation)

Thomas F. O'Connell wrote:
> I thought about posting to pgsql-docs, but since this might require
> comment from developers, I thought -general might be a better
> starting point.
>
> Anyway, I've occasionally run into monitoring situations where it
> would be immediately helpful to know the built-in SQL statements that
> generate given table-lock modes.
>
> For instance, doesn't UPDATE implicitly mean that an ACCESS SHARE
> lock will be taken if there are foreign keys involved (at least in
> versions prior to 8.1)? Are there any other scenarios where a given
> SQL command might take a lock of one of these forms as a result of
> what it does under the hood? Maybe UPDATE is the only one since it's
> implicitly a SELECT, DELETE, and INSERT all rolled into one.
>
> I'd love to see 12.3 <http://www.postgresql.org/docs/8.0/static/
> explicit-locking.html> document this more thoroughly, but I don't
> know enough about the underlying locking requirements of each step of
> each SQL command to know when locks might implicitly be acquired.
> Even if UPDATE is the only special case, it seems like it'd be worth
> mentioning.

Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
have ROW SHARE until 8.1. I actually can't find out how we are doing
that in the code, however. Analyzing the code is probably the only way
to get this detailed lock information.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073