pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMAX() of 0 records.
18+ messages / 7 participants
[nested] [flat]
* MAX() of 0 records.
@ 2000-07-07 01:44 Paul McGarry <paulm@opentec.com.au>
0 siblings, 3 replies; 18+ messages in thread
From: Paul McGarry @ 2000-07-07 01:44 UTC (permalink / raw)
To: pgsql-sql
Hello,
I wish to perform the following query in a plsql function upon an
update/insert/delete trigger:
UPDATE entry_stats
SET entry_maxprice=MAX(item_price)
FROM item
WHERE item.item_entry_id=NEW.item_entry_id
AND item.item_live = 't';
However there will be situations where there are no records for
a given item_entry_id with item_live='t'. Currently when I try
do update/insert a record so that this occurs I get the following
error 'ERROR: ExecutePlan: (junk) 'ctid' is NULL!' and the
insert/update attempt is rolled back.
In this scenario I want entry_stats.entry_maxprice to be set to zero
(which is also the default value for that column if it's any help).
Is there a good way of going about this or should I just be wrapping
the whole thing up in an
====
IF (COUNT(*) FROM item
WHERE item.item_entry_id=NEW.item_entry_id
AND item.item_live = 't')>0
THEN
UPDATE ... =MAX() ...
ELSE
UPDATE SET ... =0 ...
END IF
====
?
Thanks
--
Paul McGarry mailto:paulm@opentec.com.au
Systems Integrator http://www.opentec.com.au
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park Road Phone: (02) 9878 1744
North Ryde NSW 2113 Fax: (02) 9878 1755
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: MAX() of 0 records.
@ 2000-07-07 03:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paul McGarry <paulm@opentec.com.au>
2 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2000-07-07 03:29 UTC (permalink / raw)
To: Paul McGarry <paulm@opentec.com.au>; +Cc: pgsql-sql
Paul McGarry <paulm@opentec.com.au> writes:
> However there will be situations where there are no records for
> a given item_entry_id with item_live='t'. Currently when I try
> do update/insert a record so that this occurs I get the following
> error 'ERROR: ExecutePlan: (junk) 'ctid' is NULL!' and the
> insert/update attempt is rolled back.
This seems like a backend bug to me, but being an overworked hacker
I'm too lazy to try to reconstruct the scenario from your sketch.
Could I trouble you to submit a formal bug report with a specific,
hopefully compact script that triggers the problem?
> Is there a good way of going about this or should I just be wrapping
> the whole thing up in an
Until I've isolated the bug I don't want to speculate about whether
it'll be reasonable to try to back-patch a fix into 7.0.*. Usually
we don't risk back-patching complex fixes into stable releases, but
the fix might be simple once we know the cause.
regards, tom lane
PS: I trust you're using 7.0.* ?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] MAX() of 0 records.
@ 2000-07-07 05:34 Paul McGarry <paulm@opentec.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Paul McGarry @ 2000-07-07 05:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@postgresql.org
Tom Lane wrote:
> This seems like a backend bug to me, but being an overworked hacker
> I'm too lazy to try to reconstruct the scenario from your sketch.
> Could I trouble you to submit a formal bug report with a specific,
> hopefully compact script that triggers the problem?
I've attached it here, along with the output I see. I am running 7.0.2
and the problem occurs on both my x86 Linux and Sparc Solaris
installations.
In addition to the output attached the postmaster console adds:
====
DEBUG: Last error occured while executing PL/pgSQL function
setentrystats
DEBUG: line 4 at SQL statement
====
Thanks,
--
Paul McGarry mailto:paulm@opentec.com.au
Systems Integrator http://www.opentec.com.au
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park Road Phone: (02) 9878 1744
North Ryde NSW 2113 Fax: (02) 9878 1755
Attachments:
ctidnulltest.out (application/x-unknown-content-type-out_auto_file, 706B) download
ctidnulltest.sql (application/x-unknown-content-type-sql_auto_file, 1.5K) download
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] MAX() of 0 records.
@ 2000-07-07 07:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paul McGarry <paulm@opentec.com.au>
0 siblings, 5 replies; 18+ messages in thread
From: Tom Lane @ 2000-07-07 07:06 UTC (permalink / raw)
To: Paul McGarry <paulm@opentec.com.au>; +Cc: pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
Paul McGarry <paulm@opentec.com.au> writes:
> CREATE TABLE entry_stats
> (
> entry_id INT4 NOT NULL REFERENCES entry ON DELETE CASCADE,
> entry_minprice INT4 NOT NULL DEFAULT 0
> );
>
> CREATE TABLE item(
> item_id INT4 PRIMARY KEY,
> item_entry_id INT4 NOT NULL REFERENCES entry ON DELETE NO ACTION,
> item_price INT4 NOT NULL,
> item_live bool NOT NULL DEFAULT 'n'
> );
>
> [trigger using]
>
> UPDATE entry_stats
> SET entry_minprice=min(item_price)
> FROM item where item_entry_id=NEW.item_entry_id AND item_live='t';
>
> ERROR: ExecutePlan: (junk) `ctid' is NULL!
Hmm. There are several things going on here, but one thing that needs
clarification is whether this UPDATE is written correctly. Since it
has no constraint on entry_stats, it seems to me that *every* row of
entry_stats will have entry_minprice set to the same value, namely
the minimum item_price over those item rows that satisfy the WHERE
condition. Surely that wasn't what you wanted? Shouldn't there be an
additional WHERE clause like entry_id = item_entry_id?
Anyway, the proximate cause of the error message is as follows.
A cross-table UPDATE like this is actually implemented as if it were
a SELECT:
SELECT entry_stats.ctid, min(item_price)
FROM entry_stats, item WHERE ...;
For each row emitted by this underlying SELECT, the executor takes
the ctid result column (which identifies the particular target tuple
in the target table) and updates that tuple by stuffing the additional
SELECT result column(s) into the specified fields of that tuple.
Now, if you try a SELECT like the above in a situation where there are
no tuples matching the WHERE clause, what you get out is a row of all
NULLs --- because that's what you get from SELECT if there's an
aggregate function with no GROUP BY and no input rows. The executor
gets this dummy row, tries to do a tuple update using it, and chokes
because the ctid is NULL. So that explains why the error message is
what it is. Next question is what if anything should be done
differently. We could just have the executor ignore result rows where
ctid is NULL, but that seems like patching around the problem not fixing
it.
The thing that jumps out at me is that if you actually try the SELECT
illustrated above, you do not get any row, null or otherwise; you get
ERROR: Attribute entry_stats.ctid must be GROUPed or used in an
aggregate function
which is a mighty valid complaint. If you are aggregating rows to get
the MIN() then you don't have a unique ctid to deliver, so which row
ought to be updated? This is the system's way of expressing the same
concern I started with: this query doesn't seem to be well-posed.
You don't see this complaint when you try the UPDATE, because ctid
is added to the implicit select result in a back-door way that doesn't
get checked for GROUP-BY validity. I wonder whether that is the bug.
If so, we'd basically be saying that no query like this is valid
(since UPDATE doesn't have a GROUP BY option, there'd be no way to
pass the grouping check).
Another way to look at it is that perhaps an UPDATE involving aggregate
functions ought to be implicitly treated as GROUP BY targetTable.ctid.
In other words, the MIN() or other aggregate function is implicitly
evaluated over only those join tuples that are formed for a single
target tuple. Intuitively that seems to make sense, and it solves the
problem you're complaining of, because no matching tuples = no groups =
no result tuples = update does nothing = no problem. But I have a
sneaking suspicion that I'm missing some nasty problem with this idea
too.
Comments anyone? What exactly *should* be the behavior of an UPDATE
that uses an aggregate function and a join to another table? Over what
set of tuples should the aggregate be evaluated?
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: MAX() of 0 records.
@ 2000-07-07 08:32 benoit@cyberdeck.net
parent: Paul McGarry <paulm@opentec.com.au>
2 siblings, 1 reply; 18+ messages in thread
From: benoit@cyberdeck.net @ 2000-07-07 08:32 UTC (permalink / raw)
To: Paul McGarry <paulm@opentec.com.au>; +Cc: pgsql-sql
> Hello,
>
> I wish to perform the following query in a plsql function upon an
> update/insert/delete trigger:
>
> UPDATE entry_stats
> SET entry_maxprice=MAX(item_price)
> FROM item
> WHERE item.item_entry_id=NEW.item_entry_id
> AND item.item_live = 't';
>
> However there will be situations where there are no records for
> a given item_entry_id with item_live='t'. Currently when I try
> do update/insert a record so that this occurs I get the following
> error 'ERROR: ExecutePlan: (junk) 'ctid' is NULL!' and the
> insert/update attempt is rolled back.
>
> In this scenario I want entry_stats.entry_maxprice to be set to zero
> (which is also the default value for that column if it's any help).
>
> Is there a good way of going about this or should I just be wrapping
> the whole thing up in an
> ====
> IF (COUNT(*) FROM item
> WHERE item.item_entry_id=NEW.item_entry_id
> AND item.item_live = 't')>0
> THEN
> UPDATE ... =MAX() ...
> ELSE
> UPDATE SET ... =0 ...
> END IF
> ====
> ?
>
> Thanks
>
> --
> Paul McGarry mailto:paulm@opentec.com.au
> Systems Integrator http://www.opentec.com.au
> Opentec Pty Ltd http://www.iebusiness.com.au
> 6 Lyon Park Road Phone: (02) 9878 1744
> North Ryde NSW 2113 Fax: (02) 9878 1755
I once had the same problem with an int4 column and solved it by using the
function below :
CREATE FUNCTION "nulliszero" (int4 )
RETURNS int4 AS
'SELECT 0 WHERE $1 IS NULL
UNION SELECT $1 WHERE $1 IS NOT NULL' LANGUAGE 'SQL';
Your request then would look like :
(...)
SET entry_maxprice=nulliszero(MAX(item_price));
(...)
-Benoit
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] MAX() of 0 records.
@ 2000-07-07 08:54 Paul McGarry <paulm@opentec.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
4 siblings, 1 reply; 18+ messages in thread
From: Paul McGarry @ 2000-07-07 08:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
Hi Tom,
> Hmm. There are several things going on here, but one thing that needs
> clarification is whether this UPDATE is written correctly. Since it
My goofup (you said you wanted a compact script!).
You are correct there should have been an extra where condition in
the triggered function.
====
UPDATE entry_stats
SET entry_minprice=min(item_price)
FROM item where item_entry_id=NEW.item_entry_id AND item_live='f';
====
Should really have been:
====
UPDATE entry_stats
SET entry_minprice=min(item_price)
FROM item where item_entry_id=NEW.item_entry_id
AND entry_stats.entry_id=item_entry_id
AND item_live='f';
====
which still generates the same error message (as the 'problem' is
caused by the where clause, not what is being updated).
FWIW I've attached the real function that I've implemented to get
around the error message. In all probability the way I'm handling
it is the right way:
1. Check I'm going to get a valid response from my aggregate
2a. If so perform the update with the aggregate
2b. If not perform the update with zeros(default value)
Originally I was just wondering if I could do it all in one go,
Try to perform the update and automatically get the aggregate
result if it were 'available' and default to zeros if not.
If I forget about aggregate functions for a moment and just
consider an update where nothing matches the where criterion
then I'd still use the same logic above to reset the values
to their default. The only differences between using the
aggregate function and not is that one throws an error and
the other just updates 0 rows.
> The thing that jumps out at me is that if you actually try the SELECT
> illustrated above, you do not get any row, null or otherwise; you get
> ERROR: Attribute entry_stats.ctid must be GROUPed or used in an
> aggregate function
> which is a mighty valid complaint. If you are aggregating rows to get
> the MIN() then you don't have a unique ctid to deliver, so which row
> ought to be updated? This is the system's way of expressing the same
> concern I started with: this query doesn't seem to be well-posed.
>
> You don't see this complaint when you try the UPDATE, because ctid
> is added to the implicit select result in a back-door way that doesn't
> get checked for GROUP-BY validity. I wonder whether that is the bug.
> If so, we'd basically be saying that no query like this is valid
> (since UPDATE doesn't have a GROUP BY option, there'd be no way to
> pass the grouping check).
Would that mean that any update that used an aggregate function
would be invalid? That would be a bit scary seeing as I am doing
this in part to get around using aggregate functions in a view.
> Another way to look at it is that perhaps an UPDATE involving aggregate
> functions ought to be implicitly treated as GROUP BY targetTable.ctid.
What exactly is a ctid?
Thanks for your response Tom, it has been enlightening. I feel I'm
getting a better understanding of what's going inside pgsql by the
day from yourself and other peoples posts on the various lists.
--
Paul McGarry mailto:paulm@opentec.com.au
Systems Integrator http://www.opentec.com.au
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park Road Phone: (02) 9878 1744
North Ryde NSW 2113 Fax: (02) 9878 1755
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] MAX() of 0 records.
@ 2000-07-07 09:00 Paul McGarry <paulm@opentec.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
4 siblings, 0 replies; 18+ messages in thread
From: Paul McGarry @ 2000-07-07 09:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
Here's the attachment I said I was going to attach to the last message.
TFIF!
--
Paul McGarry mailto:paulm@opentec.com.au
Systems Integrator http://www.opentec.com.au
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park Road Phone: (02) 9878 1744
North Ryde NSW 2113 Fax: (02) 9878 1755
Attachments:
function.sql (application/x-unknown-content-type-sql_auto_file, 2.6K) download
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: MAX() of 0 records.
@ 2000-07-07 09:37 Paul McGarry <paulm@opentec.com.au>
parent: benoit@cyberdeck.net
0 siblings, 0 replies; 18+ messages in thread
From: Paul McGarry @ 2000-07-07 09:37 UTC (permalink / raw)
To: benoit@cyberdeck.net; +Cc: pgsql-sql
Hi Benoit,
> I once had the same problem with an int4 column and solved it by
> using the function below :
>
> CREATE FUNCTION "nulliszero" (int4 )
> RETURNS int4 AS
> 'SELECT 0 WHERE $1 IS NULL
> UNION SELECT $1 WHERE $1 IS NOT NULL' LANGUAGE 'SQL';
>
> Your request then would look like :
> (...)
> SET entry_maxprice=nulliszero(MAX(item_price));
> (...)
Thanks, I'm afraid that doesn't help in this context (see attached
sql). I'm sure it will come in handy some time though.
The problem isn't that MAX(item_price) returns null, it's that it
causes an error. From what I have distilled from the bugs/hackers
list where Tom has kindly written a lengthy response/discussion
of the problem it seems that in this situation (when no rows
match the where condition) the MAX (or min or count or any
aggregate function?) would cause an implicit grouping to
occur around a null field which the backend doesn't like.
I'd read Tom's post if you are interested though, it probably
makes more sense!
--
Paul McGarry mailto:paulm@opentec.com.au
Systems Integrator http://www.opentec.com.au
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park Road Phone: (02) 9878 1744
North Ryde NSW 2113 Fax: (02) 9878 1755
Attachments:
benoit.sql (application/x-unknown-content-type-sql_auto_file, 1.6K) download
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: MAX() of 0 records.
@ 2000-07-07 12:44 DalTech - Continuing Technical Education <CTE@Dal.Ca>
parent: Paul McGarry <paulm@opentec.com.au>
2 siblings, 0 replies; 18+ messages in thread
From: DalTech - Continuing Technical Education @ 2000-07-07 12:44 UTC (permalink / raw)
To: Paul McGarry <paulm@opentec.com.au>; +Cc: pgsql-sql
> UPDATE entry_stats
> SET entry_maxprice=MAX(item_price)
> FROM item
> WHERE item.item_entry_id=NEW.item_entry_id
> AND item.item_live = 't';
Try
COALESCE(MAX(item_price),0)
Christopher J.D. Currie
Computer Technician
Dalhousie: DalTech - CTE
_____________________________________________
Lord, deliver me from the man who never makes a mistake,
and also from the man who makes the same mistake twice.
-William James Mayo
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [HACKERS] Re: [SQL] MAX() of 0 records.
@ 2000-07-07 15:35 Chris Bitmead <chris@bitmead.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
4 siblings, 1 reply; 18+ messages in thread
From: Chris Bitmead @ 2000-07-07 15:35 UTC (permalink / raw)
To: ; +Cc: pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
Another observation is that if the WHERE clause is successful, it seems
to update the first record in the target relation that it finds which is
a pretty random result.
pghack=# create table e(ee text, eee integer);
CREATE
pghack=# create table f(ff text, fff integer);
CREATE
pghack=# insert into e values('e', 1);
INSERT 18871 1
pghack=# insert into e values('ee', 2);
INSERT 18872 1
pghack=# insert into e values('eee', 3);
INSERT 18873 1
pghack=# insert into f values('fff', 3);
INSERT 18874 1
pghack=# insert into f values('ff', 2);
INSERT 18875 1
pghack=# insert into f values('f', 1);
INSERT 18876 1
pghack=# update e set eee=min(f.fff) from f;
UPDATE 1
pghack=# select * from e;
ee | eee
-----+-----
ee | 2
eee | 3
e | 1
(3 rows)
pghack=# select min(f.fff) from f;
min
-----
1
(1 row)
pghack=# update e set eee=min(f.fff) from f;
UPDATE 1
pghack=# select min(f.fff) from f;
min
-----
1
(1 row)
pghack=# select * from e;
ee | eee
-----+-----
eee | 3
e | 1
ee | 1
(3 rows)
pghack=# update e set eee=min(f.fff) from f;
UPDATE 1
pghack=# select * from e;
ee | eee
-----+-----
e | 1
ee | 1
eee | 1
(3 rows)
Tom Lane wrote:
>
> Paul McGarry <paulm@opentec.com.au> writes:
> > CREATE TABLE entry_stats
> > (
> > entry_id INT4 NOT NULL REFERENCES entry ON DELETE CASCADE,
> > entry_minprice INT4 NOT NULL DEFAULT 0
> > );
> >
> > CREATE TABLE item(
> > item_id INT4 PRIMARY KEY,
> > item_entry_id INT4 NOT NULL REFERENCES entry ON DELETE NO ACTION,
> > item_price INT4 NOT NULL,
> > item_live bool NOT NULL DEFAULT 'n'
> > );
> >
> > [trigger using]
> >
> > UPDATE entry_stats
> > SET entry_minprice=min(item_price)
> > FROM item where item_entry_id=NEW.item_entry_id AND item_live='t';
> >
> > ERROR: ExecutePlan: (junk) `ctid' is NULL!
>
> Hmm. There are several things going on here, but one thing that needs
> clarification is whether this UPDATE is written correctly. Since it
> has no constraint on entry_stats, it seems to me that *every* row of
> entry_stats will have entry_minprice set to the same value, namely
> the minimum item_price over those item rows that satisfy the WHERE
> condition. Surely that wasn't what you wanted? Shouldn't there be an
> additional WHERE clause like entry_id = item_entry_id?
>
> Anyway, the proximate cause of the error message is as follows.
> A cross-table UPDATE like this is actually implemented as if it were
> a SELECT:
> SELECT entry_stats.ctid, min(item_price)
> FROM entry_stats, item WHERE ...;
> For each row emitted by this underlying SELECT, the executor takes
> the ctid result column (which identifies the particular target tuple
> in the target table) and updates that tuple by stuffing the additional
> SELECT result column(s) into the specified fields of that tuple.
>
> Now, if you try a SELECT like the above in a situation where there are
> no tuples matching the WHERE clause, what you get out is a row of all
> NULLs --- because that's what you get from SELECT if there's an
> aggregate function with no GROUP BY and no input rows. The executor
> gets this dummy row, tries to do a tuple update using it, and chokes
> because the ctid is NULL. So that explains why the error message is
> what it is. Next question is what if anything should be done
> differently. We could just have the executor ignore result rows where
> ctid is NULL, but that seems like patching around the problem not fixing
> it.
>
> The thing that jumps out at me is that if you actually try the SELECT
> illustrated above, you do not get any row, null or otherwise; you get
> ERROR: Attribute entry_stats.ctid must be GROUPed or used in an
> aggregate function
> which is a mighty valid complaint. If you are aggregating rows to get
> the MIN() then you don't have a unique ctid to deliver, so which row
> ought to be updated? This is the system's way of expressing the same
> concern I started with: this query doesn't seem to be well-posed.
>
> You don't see this complaint when you try the UPDATE, because ctid
> is added to the implicit select result in a back-door way that doesn't
> get checked for GROUP-BY validity. I wonder whether that is the bug.
> If so, we'd basically be saying that no query like this is valid
> (since UPDATE doesn't have a GROUP BY option, there'd be no way to
> pass the grouping check).
>
> Another way to look at it is that perhaps an UPDATE involving aggregate
> functions ought to be implicitly treated as GROUP BY targetTable.ctid.
> In other words, the MIN() or other aggregate function is implicitly
> evaluated over only those join tuples that are formed for a single
> target tuple. Intuitively that seems to make sense, and it solves the
> problem you're complaining of, because no matching tuples = no groups =
> no result tuples = update does nothing = no problem. But I have a
> sneaking suspicion that I'm missing some nasty problem with this idea
> too.
>
> Comments anyone? What exactly *should* be the behavior of an UPDATE
> that uses an aggregate function and a join to another table? Over what
> set of tuples should the aggregate be evaluated?
>
> regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Re: [HACKERS] Re: [SQL] MAX() of 0 records.
@ 2000-07-07 16:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Bitmead <chris@bitmead.com>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2000-07-07 16:35 UTC (permalink / raw)
To: Chris Bitmead <chris@bitmead.com>; +Cc: pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
Chris Bitmead <chris@bitmead.com> writes:
> Another observation is that if the WHERE clause is successful, it seems
> to update the first record in the target relation that it finds which is
> a pretty random result.
Wouldn't surprise me --- leastwise, you will get a random one of the
input ctid values emitted into the aggregated SELECT row. Offhand I'd
have expected the last-scanned one, not the first-scanned, but the
point is that the behavior is dependent on the implementation's choice
of scanning order. This is exactly the uncertainty that the check for
"attribute must be GROUPed or used in an aggregate function" is designed
to protect you from. But ctid is (currently) escaping that check.
It seems to me that we have two reasonable ways to proceed:
1. Forbid aggregates at the top level of UPDATE. Then you'd need to do
a subselect, perhaps something like
UPDATE foo
SET bar = (SELECT min(f1) FROM othertab
WHERE othertab.keycol = foo.keycol)
WHERE condition-determining-which-foo-rows-to-update
if you wanted to use an aggregate. This is pretty ugly, especially so
if the outer WHERE condition is itself dependent on scanning othertab
to see if there are matches to the foo row.
2. Do an implicit GROUP BY ctid as I suggested last night. I still
don't see any holes in that idea, but I am still worried that there
might be one.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Re: [HACKERS] Re: [SQL] MAX() of 0 records.
@ 2000-07-07 16:53 Robert B. Easter <reaster@comptechnews.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Robert B. Easter @ 2000-07-07 16:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Chris Bitmead <chris@bitmead.com>; +Cc: pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
On Fri, 07 Jul 2000, Tom Lane wrote:
> Chris Bitmead <chris@bitmead.com> writes:
> UPDATE foo
> SET bar = (SELECT min(f1) FROM othertab
> WHERE othertab.keycol = foo.keycol)
> WHERE condition-determining-which-foo-rows-to-update
> if you wanted to use an aggregate. This is pretty ugly, especially so
If you use min(x) or max(x) frequently, isn't it best to make a trigger that
intercepts x on insert and update, then check it and store it somewhere rather
than scanning for it everytime? (not that this fixes any db problem thats being
discussed here)
--
Robert
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Re: [SQL] MAX() of 0 records.
@ 2000-07-07 17:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paul McGarry <paulm@opentec.com.au>
0 siblings, 0 replies; 18+ messages in thread
From: Tom Lane @ 2000-07-07 17:12 UTC (permalink / raw)
To: Paul McGarry <paulm@opentec.com.au>; +Cc: pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
Paul McGarry <paulm@opentec.com.au> writes:
> Would that mean that any update that used an aggregate function
> would be invalid? That would be a bit scary seeing as I am doing
> this in part to get around using aggregate functions in a view.
You'd have to embed the aggregate in a sub-select if we did things
that way. I'd rather not have such a restriction, but only if we can
understand clearly what it means to put an aggregate directly into
UPDATE. The executive summary of what I said before is "exactly what
SHOULD this query do, anyway?" I think it's not well-defined without
some additional assumptions.
>> Another way to look at it is that perhaps an UPDATE involving aggregate
>> functions ought to be implicitly treated as GROUP BY targetTable.ctid.
> What exactly is a ctid?
Physical location of the tuple, expressed as block# and tuple# within
the file. Try "select ctid,* from sometable" ...
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Re: [HACKERS] Re: [SQL] MAX() of 0 records.
@ 2000-07-07 18:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert B. Easter <reaster@comptechnews.com>
0 siblings, 0 replies; 18+ messages in thread
From: Tom Lane @ 2000-07-07 18:12 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: Chris Bitmead <chris@bitmead.com>; pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
"Robert B. Easter" <reaster@comptechnews.com> writes:
> If you use min(x) or max(x) frequently, isn't it best to make a
> trigger that intercepts x on insert and update, then check it and
> store it somewhere rather than scanning for it everytime?
I believe that's exactly what the original questioner is trying to do...
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] MAX() of 0 records.
@ 2000-07-09 18:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
4 siblings, 0 replies; 18+ messages in thread
From: Tom Lane @ 2000-07-09 18:35 UTC (permalink / raw)
To: Paul McGarry <paulm@opentec.com.au>; pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
I wrote:
> Comments anyone? What exactly *should* be the behavior of an UPDATE
> that uses an aggregate function and a join to another table? Over what
> set of tuples should the aggregate be evaluated?
Further note on this: SQL99 specifies:
<update statement: searched> ::=
UPDATE <target table>
SET <set clause list>
[ WHERE <search condition> ]
...
5) A <value expression> in a <set clause> shall not directly
contain a <set function specification>.
so the construct is definitely not SQL-compliant. Maybe we should just
forbid it. However, if you are joining against another table (which
itself is not an SQL feature) then it seems like there is some potential
use in it. What do people think of my implicit-GROUP-BY-ctid idea?
That would basically say that the aggregate is computed over all the
tuples that join to a single target tuple.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [HACKERS] Re: [SQL] MAX() of 0 records.
@ 2000-07-10 00:24 Philip Warner <pjw@rhyme.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
4 siblings, 2 replies; 18+ messages in thread
From: Philip Warner @ 2000-07-10 00:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Paul McGarry <paulm@opentec.com.au>; pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
At 14:35 9/07/00 -0400, Tom Lane wrote:
>
>so the construct is definitely not SQL-compliant. Maybe we should just
>forbid it. However, if you are joining against another table (which
>itself is not an SQL feature) then it seems like there is some potential
>use in it. What do people think of my implicit-GROUP-BY-ctid idea?
>That would basically say that the aggregate is computed over all the
>tuples that join to a single target tuple.
Sounds perfect to me...
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [HACKERS] Re: [SQL] MAX() of 0 records.
@ 2000-07-10 01:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Philip Warner <pjw@rhyme.com.au>
1 sibling, 0 replies; 18+ messages in thread
From: Tom Lane @ 2000-07-10 01:21 UTC (permalink / raw)
To: Philip Warner <pjw@rhyme.com.au>; +Cc: Paul McGarry <paulm@opentec.com.au>; pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
Philip Warner <pjw@rhyme.com.au> writes:
>> What do people think of my implicit-GROUP-BY-ctid idea?
>> That would basically say that the aggregate is computed over all the
>> tuples that join to a single target tuple.
> Sounds perfect to me...
Note that it would not meet your expectation that
update t1 set f2=count(*) from t2 where t1.f1=2 and t2.f1=t1.f1 ;
means the same as
update t1 set f2=(Select Count(*) from t2 where t2.f1=t1.f1) where
t1.f1 = 2
... at least not without some kind of outer-join support too. With
an inner join, t1 tuples not matching any t2 tuple wouldn't be modified
at all.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [HACKERS] Re: [SQL] MAX() of 0 records.
@ 2000-07-10 01:43 Philip Warner <pjw@rhyme.com.au>
parent: Philip Warner <pjw@rhyme.com.au>
1 sibling, 0 replies; 18+ messages in thread
From: Philip Warner @ 2000-07-10 01:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Paul McGarry <paulm@opentec.com.au>; pgsql-bugs@postgresql.org; pgsql-hackers@postgresql.org
At 21:21 9/07/00 -0400, Tom Lane wrote:
>
>> Sounds perfect to me...
>
>Note that it would not meet your expectation that
This seems OK; the 'update...from' syntax does also seemingly implies that
the rows affected will only be those rows that match the predicate, so your
interpretation is probably more in keeping with intuitive expectation.
>
>... at least not without some kind of outer-join support too. With
>an inner join, t1 tuples not matching any t2 tuple wouldn't be modified
>at all.
This sounds good, but even when OJ come along, I can't see how I would get
the same behaviour as:
update t1 set f2=(Select Count(*) from t2 where t2.f1=t1.f1)
where t1.f1 = 2
since in an OJ, count(*) will, I think, always be at least 1.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2000-07-10 01:43 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-07 01:44 MAX() of 0 records. Paul McGarry <paulm@opentec.com.au>
2000-07-07 03:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-07 05:34 ` Paul McGarry <paulm@opentec.com.au>
2000-07-07 07:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-07 08:54 ` Paul McGarry <paulm@opentec.com.au>
2000-07-07 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-07 09:00 ` Paul McGarry <paulm@opentec.com.au>
2000-07-07 15:35 ` Chris Bitmead <chris@bitmead.com>
2000-07-07 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-07 16:53 ` Robert B. Easter <reaster@comptechnews.com>
2000-07-07 18:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-09 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-10 00:24 ` Philip Warner <pjw@rhyme.com.au>
2000-07-10 01:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-10 01:43 ` Philip Warner <pjw@rhyme.com.au>
2000-07-07 08:32 ` benoit@cyberdeck.net
2000-07-07 09:37 ` Paul McGarry <paulm@opentec.com.au>
2000-07-07 12:44 ` DalTech - Continuing Technical Education <CTE@Dal.Ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox