Different results in a loop with RECORD vs ROWTYPE... - Mailing list pgsql-bugs

I'm looping through a single column from a set of results.  If I store
the result in a ROWTYPE of the table, I get NULL values back.  If I
store them in a RECORD type, I get the proper values and life is
happy.  I'm not sure why there'd be any difference, but it screams bug
to me and I haven't been able to reproduce it outside of my production
schema.

I haven't been able to bottle up a test sequence that'd allow others
to repeat this out in the wild, unfortunately, but simply changing
between s.t%ROWTYPE and RECORD fixes the problem... and it shouldn't
as far as I can tell.  Here is a munged version of the
environment/function I'm using:

CREATE TABLE s.t (
        a BIGINT NOT NULL,
        b BIGINT NOT NULL,
        c INT NOT NULL DEFAULT 1::INT,
        d BIGINT NOT NULL
);

CREATE FUNCTION s.t_ins()
        RETURNS TRIGGER
        EXTERNAL SECURITY DEFINER
        AS '
DECLARE
        r_t RECORD; -- s.t%ROWTYPE;
BEGIN
        SELECT CURRVAL(''s1.seq1'') INTO NEW.d;
        FOR r_t IN SELECT z.b FROM s.t z WHERE z.c =3D NEW.c LOOP
                RAISE INFO ''b: %, c: %'', r_t.b, NEW.c;
                PERFORM s.f(r_t.b,NEW.c);
        END LOOP;

        RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER t_ins BEFORE INSERT ON s.t FOR EACH ROW
        EXECUTE PROCEDURE s.t_ins();

I suppose the specifics don't matter, but, here's the difference in
output if I change between RECORD and ROWTYPE:

Using RECORD:

psql:file.sql:1429: INFO:  b: 1, c: 4
psql:file.sql:1429: INFO:  b: 2, c: 4
psql:file.sql:1429: INFO:  b: 3, c: 4
INSERT 2164848 1

And using ROWTYPE:

psql:file.sql:1429: INFO:  b: <NULL>, c: 4
psql:file.sql:1429: ERROR:  ExecInsert: Fail to add null value in not null =
attribute b
CONTEXT:  PL/pgSQL function f line 13 at SQL statement
PL/pgSQL function t_ins line 7 at unknown

???  The _only_ difference between the two is changing things from
RECORD to ROWTYPE.  Inbetween each test run, I'm doing a drop database
and recreating everything from scratch so the test should be clean.

Like I've said, I've tried bottling up a test case that someone else
can use to debug this, but I haven't been able to find anything
reproducible or anything in the code that'd suggest what the problem
is.  I'm hoping that someone who's familiar with pl/pgsql's guts can
look at this and go, "hrm...  It's a problem with assigning a single
value into a rowtype or SPI's memory handling is being overly
aggressive in cleaning this up because of ____," or something, but I
haven't been able to reproduce it with simpler test cases.  :(

This is using a snapshot from HEAD on 2003.05.08.  Here's the test
case I've been trying to use to reproduce this, for those interested
(this test proves nothing, only is a more complete version of what's
listed above that can be run verbatim).  Because the below test case
doesn't work, I'm worried that this is a memory issue and not related
to a specific action, which concerns me a great deal.


\c template1
DROP DATABASE test;
CREATE DATABASE test;
\c test pgsql
CREATE FUNCTION public.plpgsql_call_handler () RETURNS language_handler
        AS '$libdir/plpgsql', 'plpgsql_call_handler'
        LANGUAGE c;

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER public.plpgsql_call_hand=
ler;
\c test
CREATE SCHEMA s;
CREATE TABLE s.t (i INT, j INT);
CREATE SEQUENCE s.seq;
INSERT INTO s.t (i) VALUES (1);
INSERT INTO s.t (i) VALUES (2);
INSERT INTO s.t (i) VALUES (3);
CREATE OR REPLACE FUNCTION s.t_ins()
       RETURNS TRIGGER
       EXTERNAL SECURITY DEFINER
       AS '
DECLARE
    r_t s.t%ROWTYPE; -- RECORD;
BEGIN
    SELECT NEXTVAL(''s.seq'') INTO NEW.j;
    FOR r_t IN SELECT a.i FROM s.t a WHERE a.i IS NOT NULL LOOP
        RAISE INFO ''i: %\tj: %'', r_t.i, NEW.j;
        END LOOP;

        RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER t_ins BEFORE INSERT ON s.t FOR EACH ROW EXECUTE PROCEDURE s.=
t_ins();
INSERT INTO s.t (i) VALUES (4);
INSERT INTO s.t (i) VALUES (5);
SELECT * FROM s.t;



Any hints are invaluable.  -sc

--=20
Sean Chittenden