SELECT FOR SHARE and FOR UPDATE

On Fri, Oct 07, 2005 at 01:18:03PM +0400, Ilja Golshtein wrote:

Hello!

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.
Any hints?

Thanks.

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.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Sat, Oct 08, 2005 at 11:16:08AM +0400, Ilja Golshtein wrote:

I started to believe SELECT ... FOR SHARE is the remedy for my
problems. Unfortunately it is not till I cannot combine share and
exclusive locks for different tables in one query.

I wonder if this limitation is fundamental or such a mixing of
lock modes could be allowed in future releases? I badly need this
feature.

The documentation says "It is currently not allowed," which suggests
that perhaps it could be allowed in a future version. Don't expect
to see it in 8.1, however, since that version is long past feature
freeze. I don't recall how much, if any, discussion there was on
this; search the pgsql-hackers archives to find out.

Could you tell us more about what you're doing? Maybe there's
another way to achieve it.

--
Michael Fuhr