Re: Bug list? - Mailing list pgsql-hackers

At 13:57 9/07/00 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> Is there an official bug list?
>
>There's the TODO list, but things usually only get on there if they're
>not going to be fixed quickly.  Active discussion threads in pghackers
>don't normally get reflected into TODO ...

The only reason I brought this up was because it'd be good to know it was
on a 'known issues' list, possibly with with your "it's a feature"
response. Then when/if people actually work on the code, they could
*consider* seeing if any of these items could be addressed. Then again,
maybe I should have spotted the 'Max() of no records' discussion sooner...


>> 1. Temp tables preventing permanent table creation:
>
>Not a bug IMHO, since temp tables mask permanent tables.  Drop
>or rename the temp table if you want to make a permanent table.

Don't mind them masking them on select, update, drop etc, but why mask it
on create?


>> update t1 set f2=count(*) from t2 where t1.f1=2 and t2.f1=t1.f1 ;
>> ERROR:  ExecutePlan: (junk) `ctid' is NULL!
>
>This is a bug, but it's not clear what the behavior should be; maybe
>the bug is accepting an ill-defined command in the first place.  See
>"MAX() of 0 records" thread nearby.

In the case up the above query, I expected it to be the same as:
   update t1 set f2=(Select Count(*) from t2 where t2.f1=t1.f1) where
t1.f1 = 2

and I would have expected Count(*) to return 0 with no matches, and Max(*)
to return NULL with no matches; then in the case of Max(*) one can use
Coalesce if one wants a non-null value.

The big advantage I see about the 'update ... from...' syntax is it helps
the planner (at least in theory) in the case where multiple attrs are being
updated:
   update t1 set        f1=(Select Count(t2.f2) from t2 where t2.f1=t1.f1),        f2=(Select Max(t2.f2) from t2 where
t2.f1=t1.f1),       f3=(Select Min(t2.f2) from t2 where t2.f1=t1.f1)    where t1.f1=2;
 

seems more clumsy and perhaps harder to optimize than:
   update t1 set       f1=Count(t2.f2),       f2=Max(t2.f2),       f3=Min(t2.f2)   From       t2    where t1.f1=2 and
t2.f1=t1.f1

But I agree it's a little unclear as to how it should be interpreted!


----------------------------------------------------------------
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   |/