Re: SELECT FOR SHARE and FOR UPDATE

On Fri, Oct 07, 2005 at 09:18:00PM -0500, Jim C. Nasby wrote:
> On Fri, Oct 07, 2005 at 01:18:03PM +0400, Ilja Golshtein wrote:
> > I want to select data from two tables obtaining
> > exclusive lock for records of the first table and
> > nonexclusive lock for records of the second one.
> >
> > In other words, I need something like
> > select a.f, b.f from a,b for update of a for share of b.
>
> From http://www.postgresql.org/docs/8.0/interactive/sql-select.html:
> FOR UPDATE [ OF table_name [, ...] ]
>
> I'm assuming that the syntax is the same for FOR SHARE.

It sounds like Ilja wants to do both FOR UPDATE and FOR SHARE in
the same SELECT statement.  According to the 8.1 documentation
that's not allowed:

http://developer.postgresql.org/docs/postgres/sql-select.html#SQL-FOR-UPDATE-SHARE

"It is currently not allowed for a single SELECT statement to include
both FOR UPDATE and FOR SHARE, nor can different parts of the statement
use both NOWAIT and normal waiting mode."

--
Michael Fuhr