Thread: UPDATE using query; per-row function calling problem
I'm doing an UPDATE something like this:
UPDATE
slots
SET
a = 'a'
,b = (SELECT uuid_generate_v1())
WHERE
c = TRUE;
Each updated row in slots is getting the same value for b. Is there a
way of getting a per-row value from uuid_generate_v1() without doing a
PL loop?
Regards
Rory
Rory Campbell-Lange <rory@campbell-lange.net> writes:
> I'm doing an UPDATE something like this:
> UPDATE
> slots
> SET
> a = 'a'
> ,b = (SELECT uuid_generate_v1())
> WHERE
> c = TRUE;
> Each updated row in slots is getting the same value for b.
That's Postgres' interpretation of an uncorrelated sub-SELECT: there's
no reason to do it more than once, so it doesn't.
> Is there a way of getting a per-row value from uuid_generate_v1()
> without doing a PL loop?
Drop the word "SELECT". Why did you put that in in the first place?
regards, tom lane
On 02/09/11, Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Rory Campbell-Lange <rory@campbell-lange.net> writes:
> > I'm doing an UPDATE something like this:
> > UPDATE
> > slots
> > SET
> > a = 'a'
> > ,b = (SELECT uuid_generate_v1())
> > WHERE
> > c = TRUE;
>
> > Each updated row in slots is getting the same value for b.
> That's Postgres' interpretation of an uncorrelated sub-SELECT: there's
> no reason to do it more than once, so it doesn't.
>
> > Is there a way of getting a per-row value from uuid_generate_v1()
> > without doing a PL loop?
>
> Drop the word "SELECT". Why did you put that in in the first place?
Hi Tom
Good question to which I don't know the answer. Thanks very much for the
advice.
I was able to force a per-row call to uuid_generate_v1 by using this
pattern
UPDATE
r_slots
SET b = (SELECT
y.x
FROM
(select -1 as n, uuid_generate_v1() as x )y
WHERE
y.n != r_slots.id)
...
But
b = uuid_generate_v1()
is a lot simpler!
In my "-1" example, am I right in assuming that I created a correlated
subquery rather than an correlated one? I'm confused about the
difference.
Many thanks
Rory
> > In my "-1" example, am I right in assuming that I created a correlated > subquery rather than an correlated one? I'm confused about the > difference. > > Correlated: has a where clause that references the outer query Un-correlated: not correlated Because of the where clause a correlated sub-query will return a different record for each row whereas an un-correlated sub-querywill return the same record for all rows since the where clause (if any) is constant. David J.
That's interpretation of subselect is ok, when it contains only stable functions. Maybe add a warning when subselect contains volatile function. 2011/9/2, Rory Campbell-Lange <rory@campbell-lange.net>: > On 02/09/11, Tom Lane (tgl@sss.pgh.pa.us) wrote: >> Rory Campbell-Lange <rory@campbell-lange.net> writes: >> > I'm doing an UPDATE something like this: >> > UPDATE >> > slots >> > SET >> > a = 'a' >> > ,b = (SELECT uuid_generate_v1()) >> > WHERE >> > c = TRUE; >> >> > Each updated row in slots is getting the same value for b. > >> That's Postgres' interpretation of an uncorrelated sub-SELECT: there's >> no reason to do it more than once, so it doesn't. >> >> > Is there a way of getting a per-row value from uuid_generate_v1() >> > without doing a PL loop? >> >> Drop the word "SELECT". Why did you put that in in the first place? > > Hi Tom > > Good question to which I don't know the answer. Thanks very much for the > advice. > > I was able to force a per-row call to uuid_generate_v1 by using this > pattern > > UPDATE > r_slots > SET b = (SELECT > y.x > FROM > (select -1 as n, uuid_generate_v1() as x )y > WHERE > y.n != r_slots.id) > ... > > But > b = uuid_generate_v1() > is a lot simpler! > > In my "-1" example, am I right in assuming that I created a correlated > subquery rather than an correlated one? I'm confused about the > difference. > > Many thanks > Rory > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- ------------ pasman
=?ISO-8859-2?Q?pasman_pasma=F1ski?= <pasman.p@gmail.com> writes:
> That's interpretation of subselect is ok, when it contains only stable
> functions.
> Maybe add a warning when subselect contains volatile function.
We're not likely to do that, because this sort of notation is actually
fairly commonly used to hide the volatility of non-stable functions.
regards, tom lane
On 02/09/11, David Johnston (polobo@yahoo.com) wrote:
> > In my "-1" example, am I right in assuming that I created a correlated
> > subquery rather than an correlated one? I'm confused about the
> > difference.
> >
> Correlated: has a where clause that references the outer query
> Un-correlated: not correlated
>
> Because of the where clause a correlated sub-query will return a
> different record for each row whereas an un-correlated sub-query will
> return the same record for all rows since the where clause (if any) is
> constant.
Hi David -- thanks for the clarification. However I'm still a little
confused. As I understand it the following is a un-correlated sub-query:
UPDATE
slots
SET
a = 'a'
,b = (SELECT uuid_generate_v1())
WHERE
c = TRUE;
and the following, without a 'WHERE', is a correlated sub-query:
UPDATE
slots
SET
a = 'a'
,b = uuid_generate_v1()
WHERE
c = TRUE;
Is the point that the lower is not a sub-query at all?
Regards
Rory
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rory Campbell-Lange
Sent: Monday, September 05, 2011 4:55 PM
To: David Johnston
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] UPDATE using query; per-row function calling problem
On 02/09/11, David Johnston (polobo@yahoo.com) wrote:
> > In my "-1" example, am I right in assuming that I created a
> > correlated subquery rather than an correlated one? I'm confused
> > about the difference.
> >
> Correlated: has a where clause that references the outer query
> Un-correlated: not correlated
>
> Because of the where clause a correlated sub-query will return a
> different record for each row whereas an un-correlated sub-query will
> return the same record for all rows since the where clause (if any) is
> constant.
Hi David -- thanks for the clarification. However I'm still a little
confused. As I understand it the following is a un-correlated sub-query:
UPDATE
slots
SET
a = 'a'
,b = (SELECT uuid_generate_v1())
WHERE
c = TRUE;
and the following, without a 'WHERE', is a correlated sub-query:
UPDATE
slots
SET
a = 'a'
,b = uuid_generate_v1()
WHERE
c = TRUE;
Is the point that the lower is not a sub-query at all?
----------------------------------------------------------------------------
--------------------------
Correct, the second query uses a simple function call to set the value of
"b"; Using your example you would need to do something like:
UPDATE
slots
SET
a = 'a'
,b = (SELECT something FROM somewhere WHERE somewhere.a = slots.a)
WHERE
c = TRUE;
to use a correlated sub-query. Since "uuid_generate_v1()" doesn't naturally
link to slots (or anything else) there isn't any meaningful way to use a
correlated sub-query in this situation. Since you are using a function (as
opposed to a direct TABLE/VIEW) the use of a sub-query is pointless and,
apparently, results in optimizations that are undesirable.
David J.