Re: BUG #5716: Regression joining tables in UPDATE with composite types

On 20 October 2010 06:15, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Andrew Tipton" <andrew(at)adioso(dot)com> writes:
> > Attempting to execute an UPDATE that joins to another table where the
> join
> > condition is comparing a composite type fails with the (presumably
> internal)
> > error message "psql:testcase.sql:29: ERROR: could not find pathkey item
> to
> > sort".
>
> Fixed, thanks for the report!
>

Thanks for the amazingly fast response! Yet another reason why Postgres
(and the dev team behind it) continue to be my database of choice.

> BTW ... while this is unrelated to the cause of the problem, I think
> this is quite an inefficient coding technique:
>
> > CREATE TYPE price_key AS (
> > id INTEGER
> > );
>
> > CREATE FUNCTION price_key_from_table(price) RETURNS price_key AS $$
> > SELECT $1.id
> > $$ LANGUAGE SQL IMMUTABLE;
>
> > CREATE FUNCTION price_key_from_input(price_input) RETURNS price_key AS $$
> > SELECT $1.id
> > $$ LANGUAGE SQL IMMUTABLE;
>
> > UPDATE price ...
> > WHERE price_key_from_table(price.*) =
> price_key_from_input(input_prices.*);
>
> Comparing composite types is probably a good two orders of magnitude
> slower than comparing plain ints would be. I'm sure that coding
> technique looks cute, but you're paying through the nose for it.
> Consider making price_key a simple domain over int.
>

Ah, I probably should have mentioned that the actual design is quite a bit
more complicated. I took some time to distill things down to the simplest
possible testcase that still triggered the bug, but the result is certainly
a bit nonsensical. :)

Cheers!

Andrew Tipton
Co-founder
Adioso Inc
www.adioso.com