Skip site navigation (1) Skip section navigation (2)

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

From: Andrew Tipton <andrew(at)adioso(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5716: Regression joining tables in UPDATE with composite types
Date: 2010-10-19 22:43:53
Message-ID: AANLkTim-=5y2d5-nKJu8puL1p4HA1zB1ma2=F+YRaBn=@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Aleksandr DusheinDate: 2010-10-20 09:44:09
Subject: BUG #5718: Cannot start postgres (FATAL: invalid cache id: 19)
Previous:From: Tom LaneDate: 2010-10-19 22:22:29
Subject: Re: BUG #5705: btree_gist: Index on inet changes query result

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group