UPDATE using query; per-row function calling problem

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-query will 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.