Re: SELECT FOR UPDATE and LIMIT 1 behave oddly

[Josh Berkus]

Bruce,

Ah, yes, which reminds me I need to generate that doc patch.

> I am wondering if a documentation warning about the use of FOR UPDATE
> and LIMIT is a good idea. If we can't be sure the LIMIT will return a
> guaranteed number of rows, should we just disallow that combination? I
> realize such a case is rare. Should we emit a warning when it happens?

Well, limit+for update can be useful under some circumstances, as long as you
understand its limitations. We found a workaround. So I'd oppose
disallowing it.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Josh Berkus]

Andrea,

> i'm sorry for the curiosity.... but
> could you share, if it's possible, this workaround? ;)
> (if it's not the one you describe at the beginning thread
> e.g. don't use LIMIT 1)

Well, we actually roped in the pg_locks view to do a "SELECT the first row not
already locked for update". Then added some code on the client end for
error handling, like race conditions and no rows being returned, both of
which happen in production.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[andrea suisani]

hi!

Josh Berkus wrote:
> Bruce,
>

[snip]

>
>
> Well, limit+for update can be useful under some circumstances, as long as you
> understand its limitations. We found a workaround.

i'm sorry for the curiosity.... but

could you share, if it's possible, this workaround? ;)

andrea

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Andrea Suisani]

tnks for the hint ;)
I'll try something similar here.

Andrea

Josh Berkus wrote:
> Andrea,
>
>
>>i'm sorry for the curiosity.... but
>>could you share, if it's possible, this workaround? ;)
>>(if it's not the one you describe at the beginning thread
>> e.g. don't use LIMIT 1)
>
>
> Well, we actually roped in the pg_locks view to do a "SELECT the first row not
> already locked for update". Then added some code on the client end for
> error handling, like race conditions and no rows being returned, both of
> which happen in production.
>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match