Thread: MAX() of 0 records.
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 THENUPDATE ... =MAX() ... ELSEUPDATE 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
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.* ?
> 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
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
Attachment
> 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