Re: BUG #5716: Regression joining tables in UPDATE with composite types - Mailing list pgsql-bugs

From Andrew Tipton
Subject Re: BUG #5716: Regression joining tables in UPDATE with composite types
Date
Msg-id AANLkTim-=5y2d5-nKJu8puL1p4HA1zB1ma2=F+YRaBn=@mail.gmail.com
Whole thread Raw
In response to Re: BUG #5716: Regression joining tables in UPDATE with composite types  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
On 20 October 2010 06:15, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Andrew Tipton" <andrew@adioso.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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #5705: btree_gist: Index on inet changes query result
Next
From: "Aleksandr Dushein"
Date:
Subject: BUG #5718: Cannot start postgres (FATAL: invalid cache id: 19)