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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Aleksandr Dushein | 2010-10-20 09:44:09 | BUG #5718: Cannot start postgres (FATAL: invalid cache id: 19) |
Previous Message | Tom Lane | 2010-10-19 22:22:29 | Re: BUG #5705: btree_gist: Index on inet changes query result |