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.

--
Best regards
Ilja Golshtein

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/i...l-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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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/i...l-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...R-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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly