Обсуждение: BUG #14289: Potential bug: "invalid attribute number" when dblink result is assigned in PL/PGSQL

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI4OQpMb2dnZWQgYnk6ICAg
ICAgICAgIE1pY2hhZWwgT3Zlcm1leWVyCkVtYWlsIGFkZHJlc3M6ICAgICAg
bS5vdmVybWV5ZXJAeWFob28uY2EKUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUu
NApPcGVyYXRpbmcgc3lzdGVtOiAgIENlbnRPUyA3LCBhbHNvIERvY2tlcgpE
ZXNjcmlwdGlvbjogICAgICAgIAoKSSBiZWxpZXZlIEkgbWF5IGhhdmUgZm91
bmQgYSBidWcgaW4gUEwvUEdTUUwgb3IgZGJsaW5rLiBXaGVuIHVzaW5nIHRo
ZQphc3NpZ25tZW50IG9wZXJhdG9yIHRvIGFzc2lnbiB0aGUgcmVzdWx0cyBv
ZiBkYmxpbmtfYnVpbGRfc3FsX2RlbGV0ZSB0byBhCnZhcmlhYmxlLCBpdCBn
aXZlcyBtZToNCg0KICAgIHBzcWw6cGdfYnVnLnNxbDozNzogRVJST1I6ICAy
MjAyMzogaW52YWxpZCBhdHRyaWJ1dGUgbnVtYmVyIC0xNTk4DQogICAgQ09O
VEVYVDogIFBML3BnU1FMIGZ1bmN0aW9uIHRlc3RfYXNzaWdubWVudChpbnQy
dmVjdG9yKSBsaW5lIDYgYXQKYXNzaWdubWVudA0KICAgIExPQ0FUSU9OOiAg
dmFsaWRhdGVfcGthdHRudW1zLCBkYmxpbmsuYzoyODUxDQoNClRoaXMgZG9l
cyBub3Qgb2NjdXIgaWYgSSBkbyBub3QgcGFzcyB0aGUgaW50MnZlY3RvciBh
cyBhIHBhcmFtZXRlciwgbm9yIHdoZW4KSSB1c2UgdGhlIFNFTEVDVC4uLklO
VE8gc3ludGF4Lg0KDQpTdGVwcyB0byByZXByb2R1Y2U6DQoxLiBEb3dubG9h
ZApodHRwczovL2dpc3QuZ2l0aHVidXNlcmNvbnRlbnQuY29tL21vdmVybWV5
ZXIvY2RjOWM5OTc3NDRkOTdhNWNmOTQ3MTgzN2RmZjZiNGEvcmF3L2I0ZTdm
MzM4ODM0ZWQzNWE4ZjdjYjA2NTNmODQzZjVlZmFlYzBmZWYvcGdfYnVnLnNx
bAp0byAicGdfYnVnLnNxbCINCjEuIFN0YXJ0IGEgUG9zdGdyZXMgaW5zdGFu
Y2U6IGBkb2NrZXIgcnVuIC1pIC10IC0tcm09dHJ1ZSAtZQpQT1NUR1JFU19Q
QVNTV09SRD1wb3N0Z3JlcyAtcCA1NDMyOjU0MzIgcG9zdGdyZXNgDQoyLiBS
dW4gcGdfYnVnLnNxbDogYFBHUEFTU1dPUkQ9cG9zdGdyZXMgcHNxbCAtVSBw
b3N0Z3JlcyAtaCAxMjcuMC4wLjEgLXAKNTQzMiAtZiBwZ19idWcuc3FsYA0K
DQpTYW1wbGUgb3V0cHV0Og0KDQogICAgQ1JFQVRFIERBVEFCQVNFDQogICAg
WW91IGFyZSBub3cgY29ubmVjdGVkIHRvIGRhdGFiYXNlICJ0ZXN0X2RiIiBh
cyB1c2VyICJwb3N0Z3JlcyIuDQogICAgQ1JFQVRFIEVYVEVOU0lPTg0KICAg
IENSRUFURSBUQUJMRQ0KICAgIElOU0VSVCAwIDENCiAgICBDUkVBVEUgRlVO
Q1RJT04NCiAgICBwc3FsOnBnX2J1Zy5zcWw6Mzc6IEVSUk9SOiAgMjIwMjM6
IGludmFsaWQgYXR0cmlidXRlIG51bWJlciAtMTU5OA0KICAgIENPTlRFWFQ6
ICBQTC9wZ1NRTCBmdW5jdGlvbiB0ZXN0X2Fzc2lnbm1lbnQoaW50MnZlY3Rv
cikgbGluZSA2IGF0CmFzc2lnbm1lbnQNCiAgICBMT0NBVElPTjogIHZhbGlk
YXRlX3BrYXR0bnVtcywgZGJsaW5rLmM6Mjg1MQ0KICAgIA0KV29ya2Fyb3Vu
ZDoNCg0KICAgIFVzZSB0aGUgU0VMRUNULi4uSU5UTyBzeW50YXggaW5zdGVh
ZC4KCg==

On 08/18/2016 01:44 PM, m.overmeyer@yahoo.ca wrote:
> The following bug has been logged on the website:
>=20
> 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:=20=20=20=20=20=20=20=20
>=20
> 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:
>=20
>     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


--=20
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