On 20 October 2010 06:15, Tom Lane <t...@sss.pgh.pa.us> wrote: > "Andrew Tipton" <and...@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
