Thread: BUG #3882: unexpected PARAM_SUBLINK ID

The following bug has been logged online:

Bug reference:      3882
Logged by:          Jan Mate
Email address:      mate@yeea.eu
PostgreSQL version: 8.2.6
Operating system:   Linux and Mac OS X
Description:        unexpected PARAM_SUBLINK ID
Details:

I am trying to create a row versioning table using view and rules.

The problem occur when I try to insert a new row to view using:

INSERT INTO "table" (number, level) VALUES(1, 1);

I get the following error:
ERROR:  unexpected PARAM_SUBLINK ID: 3

BUT, the same INSERT RULE (see the dump below) works fine when I delete the
"limited" column from the table (and view) and modify the rules on the
view:

Dump of my DB is:

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

DROP RULE "_INSERT" ON public."table";
DROP INDEX public."r_table#_id#key";
ALTER TABLE ONLY public."r_table" DROP CONSTRAINT "r_table#pkey";
DROP SEQUENCE public."r_table#__id_entry#seq";
DROP VIEW public."table";
DROP TABLE public."r_table";
DROP SEQUENCE public."r_table#_id#seq";

--
-- Name: r_table#_id#seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE "r_table#_id#seq"
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1
    CYCLE;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: r_table; Type: TABLE; Schema: public; Owner: -; Tablespace:
--

CREATE TABLE "r_table" (
    _id bigint DEFAULT nextval('"r_table#_id#seq"'::regclass) NOT NULL,
    __version bigint NOT NULL,
    __latest boolean DEFAULT false NOT NULL,
    __op_type text NOT NULL,
    __timestamp timestamp without time zone NOT NULL,
    __id_entry bigint NOT NULL,
    number bigint NOT NULL,
    limited timestamp without time zone,
    "level" bigint DEFAULT 1 NOT NULL
);


--
-- Name: table; Type: VIEW; Schema: public; Owner: -
--
-- Select only the latest version of rows if they are not DELETED

CREATE VIEW "table" AS
    SELECT "r_table"._id, "r_table".number, "r_table".limited,
"r_table"."level" FROM "r_table" WHERE (((("r_table".__id_entry,
"r_table".__version) IN (SELECT "r_table".__id_entry,
max("r_table".__version) AS __version FROM "r_table" GROUP BY
"r_table".__id_entry)) AND ("r_table".__op_type <> 'DELETE'::text)) AND
("r_table".__latest = true));


--
-- Name: r_table#__id_entry#seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE "r_table#__id_entry#seq"
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: r_table#pkey; Type: CONSTRAINT; Schema: public; Owner: -;
Tablespace:
--

ALTER TABLE ONLY "r_table"
    ADD CONSTRAINT "r_table#pkey" PRIMARY KEY (_id);


--
-- Name: r_table#_id#key; Type: INDEX; Schema: public; Owner: -; Tablespace:

--

CREATE UNIQUE INDEX "r_table#_id#key" ON "r_table" USING btree (_id);


--
-- Name: _INSERT; Type: RULE; Schema: public; Owner: -
--
-- Insert the new row only if there is not duplicate row in view

CREATE RULE "_INSERT" AS ON INSERT TO "table" DO INSTEAD INSERT INTO
"r_table" (__version, __latest, __op_type, __timestamp, __id_entry, number,
limited, "level") VALUES (0, true, 'INSERT'::text, now(),
nextval('"r_table#__id_entry#seq"'::regclass), (SELECT new.number WHERE (NOT
((new.number, new.limited, new."level") IN (SELECT "table".number,
"table".limited, "table"."level" FROM "table")))), new.limited,
new."level");

"Jan Mate" <mate@yeea.eu> writes:
> I get the following error:
> ERROR:  unexpected PARAM_SUBLINK ID: 3

This is an interesting test case.  The problem is coming from the part
of the rule that has

    (new.number, new.limited, new."level") IN (SELECT ...)

The parser transforms this to a SubLink with a "testexpr" that
looks like

    V1 = P1 AND V2 = P2 AND V3 = P3

where the V's are Vars representing the new.* fields and the P's
are PARAM_SUBLINK Params representing the output columns of the
sub-SELECT.

The planner's subselect.c assumes that the testexpr will still
look like that, at least to the extent of referencing the same
Params in the same order, by the time it sees the SubLink.
This assumption was always a bit shaky, as noted in the code,
but I had not seen a case that breaks it.  What happens is that
for an ON INSERT rule with INSERT / VALUES, the rewriter substitutes
the VALUES-list items for the new.* Vars, which in this case
yields

    1 = P1 AND null = P2 AND 1 = P3

and then const-simplification reasons that timestamp equality is a
strict operator and therefore cannot succeed on constant-null input,
so the result of eval_const_expressions is just

    1 = P1 AND 1 = P3

causing subselect.c to choke because there's no Param corresponding
to the second subselect output column.

I thought for a bit about a band-aid fix involving doing sublink
expansion before const-simplification, but really the right answer
is to get rid of the shaky assumption.  Instead of relying on
one-for-one matching of Param uses, subselect.c should scan the
subselect's output targetlist for itself to determine the number
and types of the output columns.  This involves duplicating some
code from the parser's transformSubLink, but only about a dozen
lines worth.  (It was trying to avoid duplicating that logic that
led me down the garden path to this error :-()  The substitution
for the PARAM_SUBLINK Params still needs to happen, but that
should be driven off list_nth() selection from the result of this
scan, instead of assuming that the Params appear in any particular
ordering.

Will fix.

            regards, tom lane

"Jan Mate" <mate@yeea.eu> writes:
> Description:        unexpected PARAM_SUBLINK ID

If convenient, please try the 8.2 patch seen here:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/subselect.c

            regards, tom lane