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