Skip site navigation (1) Skip section navigation (2)

Re: UPDATE using sub selects

From: NikhilS <nikkhils(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: UPDATE using sub selects
Date: 2008-03-18 07:48:19
Message-ID: d3c4af540803180048o291124b1n863e454b188c96a3@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-patches
Hi Tom,

> ... eg backend/nodes/, optimizer/util/clauses.c, ruleutils.c...)
>
> Actually, on further thought ruleutils.c represents one of the biggest
> stumbling blocks here.  We have to be able to reverse-compile the parse
> tree into something that's at least semantically equivalent to the
> original query.  The existing kluge for UPDATE SET (columns) = ... can
> ignore this because it rearranges the query into a sematically
> equivalent update with independent SET targets, but the whole problem
> with sub-select updates is that there *is* no semantically equivalent
> command with a flat targetlist.  So one way or another there will have
> to be more information in the parse tree than there is now.
>
> If we use Params that can be traced back to specific SubLink list
> entries, it might be okay to finesse this by having ruleutils.c check
> for successive targetlist entries that reference outputs of the same
> SubLink.  That's pretty ugly but it might be better than changing the
> representation of targetlists, which is something that's understood
> by one heck of a lot of code.
>

Thanks a lot for taking a look at this patch. As mentioned and detailed out
by you, this patch *does* need a lot more amount of work and is certainly
not a simpler effort that I had envisioned it to be earlier. Another issue
with the patch as it stands today is that it does not work with correlated
subqueries. This will require some more work too. So for example, something
of this sort does not work yet:
UPDATE update_tbl set (a, b) = (select a, b from other_tbl where c =
update_tbl.c) where a = 10;

I will try to have another shot at it if I can, before the next commit fest.


Thanks and Regards,
Nikhils

-- 
EnterpriseDB http://www.enterprisedb.com

In response to

pgsql-patches by date

Next:From: Bruce MomjianDate: 2008-03-18 14:58:05
Subject: Re: UPDATE using sub selects
Previous:From: ITAGAKI TakahiroDate: 2008-03-18 07:09:20
Subject: Patch for testing query modes on pgbench

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group