Different results in a loop with RECORD vs ROWTYPE...

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: pgsql-bugs(at)PostgreSQL(dot)org
Subject: Different results in a loop with RECORD vs ROWTYPE...
Date: 2003-05-22 08:13:02
Message-ID: 20030522081302.GG71079@perrin.int.nxad.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 = 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_handler;
\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

--
Sean Chittenden

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sean Chittenden 2003-05-22 10:56:54 Re: Different results in a loop with RECORD vs ROWTYPE...
Previous Message Gaetano Mendola 2003-05-21 11:20:27 Re: Primary key duplicated: NUMBER OF INDEX' TUPLES (21539) IS NOT THE SAME AS HEAP (18607)'