BUG #14289: Potential bug: "invalid attribute number" when dblink result is assigned in PL/PGSQL

On 08/18/2016 01:44 PM, m.overmeyer@yahoo.ca wrote:

The following bug has been logged on the website:

Bug reference: 14289
Logged by: Michael Overmeyer
Email address: m.overmeyer@yahoo.ca
PostgreSQL version: 9.5.4
Operating system: CentOS 7, also Docker
Description:

I believe I may have found a bug in PL/PGSQL or dblink. When using the
assignment operator to assign the results of dblink_build_sql_delete to a
variable, it gives me:

psql:pg_bug.sql:37: ERROR: 22023: invalid attribute number -1598
CONTEXT: PL/pgSQL function test_assignment(int2vector) line 6 at
assignment
LOCATION: validate_pkattnums, dblink.c:2851

This does not have anything specific to do with dblink -- but it does
seem to be specific to plpgsql. Here is a minimal test case:

SELECT version();
version
--------------------------------------------------------------------
PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
(1 row)

CREATE OR REPLACE FUNCTION test_int2vector(pka int2vector)
RETURNS int2vector AS $$
BEGIN
return pka;
END;
$$ LANGUAGE plpgsql;

SELECT test_int2vector('1'::int2vector);
-- garbage result

CREATE OR REPLACE FUNCTION test_int2v(pka int2vector)
RETURNS int2vector AS
$$ select pka $$ LANGUAGE sql;

SELECT test_int2v('1'::int2vector);
-- works fine

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

m.overmeyer@yahoo.ca writes:

I believe I may have found a bug in PL/PGSQL or dblink.

So you did. Fixed, thanks.

For the archives' sake, the test case looked like

------
create extension dblink;

create table test_table
(
username text primary key
);

INSERT INTO test_table VALUES ('test_user');

CREATE OR REPLACE FUNCTION test_assignment(primary_key_attnums int2vector)
RETURNS text AS
$BODY$
DECLARE
results text;
BEGIN
-- This doesn't work:
results := dblink_build_sql_delete('test_table', primary_key_attnums, 1, ARRAY['test_user']::text[]);

-- But this one (use value instead of parameter) does work:
--results := dblink_build_sql_delete('test_table', '1'::int2vector, 1, ARRAY['test_user']::text[]);

--And so does this one:
-- SELECT dblink_build_sql_delete('test_table', primary_key_attnums, 1, ARRAY['test_user']::text[]) INTO results;

return results;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

SELECT test_assignment('1'::int2vector);
-----

and the problem was that plpgsql thought it could convert the int2vector
argument into an "expanded array". But int2vector doesn't allow toasting
so a fortiori it can't handle being expanded.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs