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

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;