BUG #5716: Regression joining tables in UPDATE with composite types
The following bug has been logged online:
Bug reference: 5716
Logged by: Andrew Tipton
Email address: andrew@adioso.com
PostgreSQL version: 9.0.1
Operating system: Ubuntu 10.04
Description: Regression joining tables in UPDATE with composite types
Details:
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".
Interestingly, even trying to EXPLAIN the query fails with the same error,
leading me to believe that the issue lies in the query planner and/or
type-checking code.
Steps to reproduce:
$ createdb test
$ psql -f testcase.sql test
The attached testcase.sql script works fine on 8.4.4, and fails on 9.0.1 --
both Ubuntu 10.04 machines; the one running 8.4.4 is the official Ubuntu
packages, while 9.0.1 is using Martin Pitt's 9.0 packages.
Unfortunately I don't have access to a machine running a vanilla 9.0.1
compiled from the official sources, though I'd be pretty surprised if this
bug was introduced during the packaging process.
Interestingly, this issue only happens in an UPDATE (not in SELECT) and only
when the join condition is a composite-type-returning function. If the
composite type is present as a column in the base tables, the UPDATE
proceeds without issue.
-- testcase.sql
BEGIN;
CREATE TABLE price (
id SERIAL PRIMARY KEY,
active BOOLEAN NOT NULL,
price NUMERIC
);
CREATE TYPE price_input AS (
id INTEGER,
price NUMERIC
);
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
SET active=TRUE, price=input_prices.price
FROM unnest(ARRAY[(10, 123.00), (11, 99.99)]::price_input[])
input_prices
WHERE price_key_from_table(price.*) =
price_key_from_input(input_prices.*);
COMMIT;
"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!
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.
regards, tom lane
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