In yesterday's discussions about FOR UPDATE there was some mention of
making it not propagate into WITH subqueries:
http://archives.postgresql.org/pgsql-hackers/2009-10/msg01540.php
That is, given
WITH w AS (SELECT * FROM foo) SELECT * FROM w, bar ... FOR UPDATE
should foo be locked FOR UPDATE or not? The current behavior is that
the code attempts to propagate FOR UPDATE into the WITH, and fails
(the parser rejects it in some cases, and the planner in others ---
AFAICT there is no case where it actually works). This is pretty
useless, and it's also at odds with the philosophy we adopted that WITH
queries execute independently of the primary query. So I think there
was consensus to change it to have FOR UPDATE ignore WITH references.

What I'm wondering at the moment is if there's any objection to
back-patching the change into 8.4. Given the lack of any way to have a
working query depend on this behavior, it doesn't seem that there could
be a problem, but can anyone think of an objection I missed?

regards, tom lane

Search Discussions

  • Robert Haas at Oct 27, 2009 at 5:03 pm

    On Tue, Oct 27, 2009 at 10:50 AM, Tom Lane wrote:
    In yesterday's discussions about FOR UPDATE there was some mention of
    making it not propagate into WITH subqueries:
    http://archives.postgresql.org/pgsql-hackers/2009-10/msg01540.php
    That is, given
    WITH w AS (SELECT * FROM foo) SELECT * FROM w, bar ... FOR UPDATE
    should foo be locked FOR UPDATE or not?  The current behavior is that
    the code attempts to propagate FOR UPDATE into the WITH, and fails
    (the parser rejects it in some cases, and the planner in others ---
    AFAICT there is no case where it actually works).  This is pretty
    useless, and it's also at odds with the philosophy we adopted that WITH
    queries execute independently of the primary query.  So I think there
    was consensus to change it to have FOR UPDATE ignore WITH references.

    What I'm wondering at the moment is if there's any objection to
    back-patching the change into 8.4.  Given the lack of any way to have a
    working query depend on this behavior, it doesn't seem that there could
    be a problem, but can anyone think of an objection I missed?
    If it doesn't have any effect anyway, what's the virtue of back-patching it?

    It seems like we might want to throw an error rather than silently
    ignoring it, but that obviously wouldn't be back-patchable.

    ...Robert
  • Tom Lane at Oct 27, 2009 at 5:14 pm

    Robert Haas writes:
    If it doesn't have any effect anyway, what's the virtue of back-patching it?
    Because 8.4 just fails in cases where we can easily allow it to work
    according to the new definition. Right now, if you want to use FOR
    UPDATE in a query that has WITHs, you have to carefully write
    FOR UPDATE OF x,y,z,... for all the non-WITH relations.

    regards, tom lane
  • Robert Haas at Oct 27, 2009 at 5:45 pm

    On Tue, Oct 27, 2009 at 1:14 PM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    If it doesn't have any effect anyway, what's the virtue of back-patching it?
    Because 8.4 just fails in cases where we can easily allow it to work
    according to the new definition.  Right now, if you want to use FOR
    UPDATE in a query that has WITHs, you have to carefully write
    FOR UPDATE OF x,y,z,... for all the non-WITH relations.
    Oh, I see. Well, that seems like it makes sense to me, then.

    ...Robert
  • Andres Freund at Oct 27, 2009 at 5:22 pm

    On Tuesday 27 October 2009 18:02:53 Robert Haas wrote:
    On Tue, Oct 27, 2009 at 10:50 AM, Tom Lane wrote:
    In yesterday's discussions about FOR UPDATE there was some mention of
    making it not propagate into WITH subqueries:
    http://archives.postgresql.org/pgsql-hackers/2009-10/msg01540.php
    That is, given
    WITH w AS (SELECT * FROM foo) SELECT * FROM w, bar ... FOR UPDATE
    should foo be locked FOR UPDATE or not? The current behavior is that
    the code attempts to propagate FOR UPDATE into the WITH, and fails
    (the parser rejects it in some cases, and the planner in others ---
    AFAICT there is no case where it actually works). This is pretty
    useless, and it's also at odds with the philosophy we adopted that WITH
    queries execute independently of the primary query. So I think there
    was consensus to change it to have FOR UPDATE ignore WITH references.

    What I'm wondering at the moment is if there's any objection to
    back-patching the change into 8.4. Given the lack of any way to have a
    working query depend on this behavior, it doesn't seem that there could
    be a problem, but can anyone think of an objection I missed?
    If it doesn't have any effect anyway, what's the virtue of back-patching
    it?

    It seems like we might want to throw an error rather than silently
    ignoring it, but that obviously wouldn't be back-patchable.
    Because it makes it impossible to use SELECT FOR UPDATE with a CTE atm? Which
    very well can be considered a bug.

    Andres

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 27, '09 at 2:50p
activeOct 27, '09 at 5:45p
posts5
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase