Thread: FOR UPDATE versus WITH --- change 8.4 too?
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
On Tue, Oct 27, 2009 at 10:50 AM, Tom Lane <tgl@sss.pgh.pa.us> 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
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.
regards, tom lane
On Tuesday 27 October 2009 18:02:53 Robert Haas wrote: > On Tue, Oct 27, 2009 at 10:50 AM, Tom Lane <tgl@sss.pgh.pa.us> 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
On Tue, Oct 27, 2009 at 1:14 PM, Tom Lane <tgl@sss.pgh.pa.us> 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