Thread: plpgsql FOR LOOP CTE problem ?
Hi,
I am working on a plpgsql function that is not acting as I would hope.
I am working with the Postgres 9.3 beta load and would like to
solicit some feedback.
Looking at the outermost for loop of the function below, If I run this CTE query from the psql command line
I am returned what I expect for values for translator_id and the
Aggregating MIN functions. I restore the experimental data and now run the function.
In the context of this function I get a valid translator_id ,
But the MINed columns are NULL ????
The function later bombs on the insert statement as tid_seq and ws_grp_seq columns
Have a not null requirement.
Any thoughts ?
Thanks
Dave
The function basics are:
CREATE OR REPLACE FUNCTION admin.activate_translator_user (ws_id integer)
RETURNS void AS
$BODY$
DECLARE
drow admin.translator_member%ROWTYPE; -- deleted row holder
wsrow RECORD;
patt CHARACTER VARYING;
BEGIN
-- Remove current input ws_id subsection of the translator but grab some
-- sequenceing information from it.
FOR drow IN
WITH drows AS ( -- Runs as expected from psql command line
DELETE FROM admin.translator_member
WHERE tu_id = ws_id RETURNING *
)
SELECT translator_id, MIN(tid_seq)
AS tid_seq, MIN(ws_grp_seq) AS ws_grp_seq
FROM drows GROUP BY translator_id
LOOP
Raise notice ' TID_seq % WS_seq % TID % ', drow.tid_seq, drow.ws_grp_seq, drow.translator_id;
-- Replace the removed subsection of relevant translation sets.
FOR wsrow IN
SELECT * FROM admin.translator_user_mbr
WHERE tu_id = ws_id
ORDER BY obj_seq ASC
LOOP
-- On activation refresh the pattern content from the pattern
-- table if pattern_id is not zero.
IF wsrow.pattern_id IS NOT NULL AND wsrow.pattern_id != 0
THEN
SELECT pattern INTO patt FROM admin.pattern
WHERE pattern_id = wsrow.pattern_id;
ELSE
patt = 'NULL';
END IF;
INSERT INTO admin.translator_member (
"name", "tu_id", "translator_id",
"tid_seq", "ws_grp_seq", "obj_seq", …..
) VALUES (
NULL, wsrow."tu_id", drow."translator_id",
drow."tid_seq", drow."ws_grp_seq", wsrow."obj_seq",….. _);
END LOOP; -- wsrow
END LOOP; -- drow
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
2013/8/9 Day, David <dday@redcom.com>: > Hi, > > > > I am working on a plpgsql function that is not acting as I would hope. > > I am working with the Postgres 9.3 beta load and would like to > > solicit some feedback. > > > > Looking at the outermost for loop of the function below, If I run this CTE > query from the psql command line > > I am returned what I expect for values for translator_id and the > > Aggregating MIN functions. I restore the experimental data and now run the > function. > > In the context of this function I get a valid translator_id , > > But the MINed columns are NULL ???? > > > > The function later bombs on the insert statement as tid_seq and ws_grp_seq > columns > > Have a not null requirement. > > > > Any thoughts ? > probably it is plpgsql bug, you can try FOR IN EXECUTE 'with ...' Regards Pavel Stehule > > > > > Thanks > > > > Dave > > > > > > > > > > The function basics are: > > > > CREATE OR REPLACE FUNCTION admin.activate_translator_user (ws_id integer) > > RETURNS void AS > > $BODY$ > > DECLARE > > drow admin.translator_member%ROWTYPE; -- deleted row holder > > wsrow RECORD; > > patt CHARACTER VARYING; > > > > BEGIN > > -- Remove current input ws_id subsection of the translator but grab some > > -- sequenceing information from it. > > > > FOR drow IN > > WITH drows AS ( > -- Runs as expected from psql command line > > DELETE FROM admin.translator_member > > WHERE tu_id = ws_id RETURNING * > > ) > > SELECT translator_id, MIN(tid_seq) > > AS tid_seq, MIN(ws_grp_seq) AS ws_grp_seq > > FROM drows GROUP BY translator_id > > LOOP > > Raise notice ' TID_seq % WS_seq % TID % ', drow.tid_seq, > drow.ws_grp_seq, drow.translator_id; > > > > -- Replace the removed subsection of relevant translation sets. > > FOR wsrow IN > > SELECT * FROM admin.translator_user_mbr > > WHERE tu_id = ws_id > > ORDER BY obj_seq ASC > > LOOP > > -- On activation refresh the pattern content from the pattern > > -- table if pattern_id is not zero. > > IF wsrow.pattern_id IS NOT NULL AND wsrow.pattern_id != 0 > > THEN > > SELECT pattern INTO patt FROM admin.pattern > > WHERE pattern_id = wsrow.pattern_id; > > ELSE > > patt = 'NULL'; > > END IF; > > > > INSERT INTO admin.translator_member ( > > "name", "tu_id", "translator_id", > > "tid_seq", "ws_grp_seq", "obj_seq", ….. > > ) VALUES ( > > NULL, wsrow."tu_id", drow."translator_id", > > drow."tid_seq", drow."ws_grp_seq", > wsrow."obj_seq",….. _); > > > > END LOOP; -- wsrow > > END LOOP; -- drow > > > > > > END > > $BODY$ > > LANGUAGE plpgsql VOLATILE > > COST 100; > >
"Day, David" <dday@redcom.com> writes:
> Looking at the outermost for loop of the function below, If I run this CTE query from the psql command line
> I am returned what I expect for values for translator_id and the
> Aggregating MIN functions. I restore the experimental data and now run the function.
> In the context of this function I get a valid translator_id ,
> But the MINed columns are NULL ????
Perhaps those column names duplicate variable names within the function,
and what's being returned is the values of the variables?
If you think there's a bug here, you'd need to provide a self-contained
test case.
regards, tom lane
Hi Tom,
That's a good thought on the duplicated variable names.
The two table involved do have many common column names
But the inputs are explicitly referencing tables on the inserts.
Would not seem to be possible to confuse them.
In trying to make a simplified test case, I came across
Something else I thought odd.
My observation is that if the inner loop references the
"drow" LOOP variable the actual deletion in table aaa by the outer loop
is not taking place as I would hope.
RE: the inner loop insert aaa table fails.
I get a duplicate primary key violation on the insert in my sample function below.
So far, for my test case below, I do not see the original reported error where the MINed
Column value is NULL. Still trying to replicate that in a simplified version.
The purpose of the function is to replace in a master table (aaa) a subsection that
A user has modify privileges on within a separate work space table (bbb) .
I am continuing to explore replicating the original problem. Thought this tangent
might suggest something to you. ( hopefully that I'm not a total idiot )
Thanks
Dave
CREATE TABLE admin.aaa (
translator_id integer,
tid_seq integer NOT NULL,
ws_grp_seq integer,
PRIMARY KEY (translator_id, tid_seq)
);
INSERT INTO admin.aaa (translator_id,tid_seq, ws_grp_seq ) VALUES
(1,1,1),
(1,2,1),
(1,3,1),
(2,1,1),
(2,2,1),
(2,3,1);
CREATE TABLE admin.bbb (
translator_id integer,
tid_seq integer,
ws_grp_seq integer,
PRIMARY KEY (translator_id, tid_seq)
);
INSERT INTO admin.bbb (translator_id,tid_seq, ws_grp_seq ) VALUES
(1,1,1),
(1,2,1),
(1,3,1);
CREATE OR REPLACE FUNCTION admin.activate_translator_user1 (ws_id integer)
RETURNS void AS
$BODY$
DECLARE
drow admin.aaa%ROWTYPE; -- deleted row holder
nrow admin.bbb%ROWTYPE; --
BEGIN
-- Remove current subsection of the translator but grab some
-- sequenceing information from it.
FOR drow IN
WITH xrows AS (
DELETE FROM admin.aaa
WHERE translator_id = ws_id RETURNING *
)
SELECT translator_id, MIN(tid_seq), MIN(ws_grp_seq)
FROM xrows GROUP BY translator_id
LOOP
Raise notice ' TID_seq % WS_seq % TID % ', drow.tid_seq, drow.ws_grp_seq, drow.translator_id; -- output is
correct(all 3 cases ? )
FOR nrow IN
SELECT * FROM admin.bbb WHERE translator_id = ws_id
LOOP
INSERT INTO admin.aaa ( translator_id, tid_seq, ws_grp_seq ) VALUES
( nrow.translator_id, nrow.tid_seq, nrow.tid_seq ); -- works correctly.
-- ( nrow.translator_id, drow.tid_seq, nrow.tid_seq ); -- duplicate primary key
error.
-- ( drow.translator_id, drow.tid_seq, drow.tid_seq ); -- duplicate prinary key
errors-
END LOOP; -- drow
END LOOP; -- drow
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
select admin.activate_translate_user1(1);
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, August 09, 2013 10:20 AM
To: Day, David
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ?
"Day, David" <dday@redcom.com> writes:
> Looking at the outermost for loop of the function below, If I run
> this CTE query from the psql command line I am returned what I expect
> for values for translator_id and the Aggregating MIN functions. I restore the experimental data and now run the
function.
> In the context of this function I get a valid translator_id , But the
> MINed columns are NULL ????
Perhaps those column names duplicate variable names within the function, and what's being returned is the values of the
variables?
If you think there's a bug here, you'd need to provide a self-contained test case.
regards, tom lane
A complete self contained test case: example of the problem with my FOR LOOP using a COMMON table expression.
Again this is version 9.3beta
Any comments
Thanks
CREATE SCHEMA test
CREATE TABLE test.tmm
(
name character varying,
tu_id integer NOT NULL DEFAULT 1,
translator_id integer NOT NULL,
tid_seq integer NOT NULL,
ws_grp_seq integer DEFAULT 0,
obj_seq integer NOT NULL,
entry_type integer DEFAULT 0,
pattern_key character varying,
pattern character varying,
pattern_match integer DEFAULT 0,
screen_class integer DEFAULT 0,
term_mode character varying,
port_id integer,
tag_id integer,
cause__q850 integer,
cause__redcom integer,
new_translator_id integer,
new_screen_class integer,
duration integer,
address character varying,
macro_type integer,
owner_usr_id integer,
owner_port_id integer,
trans_result integer NOT NULL DEFAULT 0,
sys_note character varying,
continue_translating character varying,
call_priority__level integer,
user_message character varying,
label character varying,
label_reference character varying,
-- row_type sys.rsrc_type_enum DEFAULT 'real'::sys.rsrc_type_enum,
CONSTRAINT translator_member_pkey PRIMARY KEY (translator_id, tu_id, obj_seq)
-- CONSTRAINT translator_member_call_priority_fkey FOREIGN KEY (call_priority__level)
-- REFERENCES admin.call_priority_level_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_member_entry_type_fkey FOREIGN KEY (entry_type)
-- REFERENCES admin.translator_entry_type_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_member_owner_port_id_fkey FOREIGN KEY (owner_port_id)
-- REFERENCES admin.port (port_id) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_member_pattern_match_fkey FOREIGN KEY (pattern_match)
-- REFERENCES admin.translator_pattern_match_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_member_trans_result_fkey FOREIGN KEY (trans_result)
-- REFERENCES admin.translator_result_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
CREATE TABLE test.tum
(
name character varying,
tu_id integer NOT NULL DEFAULT 1,
translator_id integer,
tid_seq integer,
ws_grp_seq integer DEFAULT 0,
obj_seq integer NOT NULL,
entry_type integer DEFAULT 0,
pattern_key character varying,
pattern_match integer DEFAULT 0,
screen_class integer DEFAULT 0,
term_mode character varying,
port_id integer,
tag_id integer,
cause__q850 integer,
cause__redcom integer,
new_translator_id integer,
new_screen_class integer,
duration integer,
address character varying,
macro_type integer,
pattern_id integer,
pattern_class_id integer,
owner_usr_id integer,
owner_port_id integer,
trans_result integer NOT NULL DEFAULT 0,
sys_note character varying,
continue_translating character varying,
call_priority__level integer,
user_message character varying,
label character varying,
label_reference character varying,
-- row_type sys.rsrc_type_enum DEFAULT 'real'::sys.rsrc_type_enum,
CONSTRAINT translator_user_mbr_pkey PRIMARY KEY (tu_id, obj_seq)
-- CONSTRAINT translator_mbr_owner_port_id_fkey FOREIGN KEY (owner_port_id)
-- REFERENCES admin.port (port_id) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_mbr_pattern_match_fkey FOREIGN KEY (pattern_match)
-- REFERENCES admin.translator_pattern_match_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_user_entry_type_fkey FOREIGN KEY (entry_type)
-- REFERENCES admin.translator_entry_type_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_user_mbr_call_priority_fkey FOREIGN KEY (call_priority__level)
-- REFERENCES admin.call_priority_level_enum (value) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_user_mbr_trans_result_fkey FOREIGN KEY (trans_result)
-- REFERENCES admin.translator_result_enum (value) MATCH Unknown1,
-- ON UPDATE NO ACTION ON DELETE NO ACTION,
-- CONSTRAINT translator_user_mbr_tu_id_fkey FOREIGN KEY (tu_id)
-- REFERENCES admin.translator_user (tu_id) MATCH Unknown
-- ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)
WITH (
OIDS=FALSE
);
INSERT INTO test.tmm ( translator_id, tid_seq, tu_id, obj_seq) VALUES
(1,1,1,1),
(1,1,1,2),
(1,1,1,3),
(1,1,1,4),
(2,1,1,1),
(2,1,1,2),
(2,1,1,3);
INSERT INTO test.tum ( translator_id, tu_id, obj_seq) VALUES
(1,1,1),
(1,1,2),
(1,1,3),
(1,1,4),
(2,2,1),
(2,2,2),
(2,2,3);
CREATE OR REPLACE FUNCTION test.activate_translator_user (ws_id integer)
RETURNS void AS
$BODY$
DECLARE
drow test.tmm%ROWTYPE; -- deleted row holder
wsrow test.tum%ROWTYPE;
patt CHARACTER VARYING;
BEGIN
-- Remove current subsection of the translator but grab some
-- sequenceing information from it.
-- ( Presumes that this is an update !!!!!! )
FOR drow IN
WITH xrows AS (
DELETE FROM test.tmm
WHERE tu_id = ws_id RETURNING *
)
SELECT translator_id, MIN(tid_seq), MIN(ws_grp_seq)
FROM xrows GROUP BY translator_id
LOOP
Raise notice ' INPUT % TID_seq % WS_seq % TID % ', ws_id, drow.tid_seq, drow.ws_grp_seq, drow.translator_id;
-- Replace the removed subsection of relevant translation sets.
FOR wsrow IN
SELECT * FROM test.tum a
WHERE a.tu_id = ws_id
ORDER BY obj_seq ASC
LOOP
INSERT INTO test.tmm (
"tu_id", "translator_id",
"tid_seq", "ws_grp_seq", "obj_seq",
"entry_type", "pattern_key", "pattern",
"pattern_match", "screen_class", "term_mode",
"trans_result", "port_id", "tag_id",
"cause__q850", "cause__redcom", "new_translator_id",
"new_screen_class", "duration", "call_priority__level",
"address", "macro_type", "owner_usr_id",
"owner_port_id", "sys_note", "continue_translating",
"user_message", "label", "label_reference"
) VALUES (
wsrow."tu_id", drow."translator_id",
drow."tid_seq", drow."ws_grp_seq", wsrow."obj_seq",
wsrow."entry_type", wsrow."pattern_key", patt,
wsrow."pattern_match", wsrow."screen_class", wsrow."term_mode",
wsrow."trans_result", wsrow."port_id", wsrow."tag_id",
wsrow."cause__q850", wsrow."cause__redcom", wsrow."new_translator_id",
wsrow."new_screen_class", wsrow."duration", wsrow."call_priority__level",
wsrow."address", NULL, wsrow."owner_usr_id",
wsrow."owner_port_id", wsrow."sys_note", wsrow."continue_translating",
wsrow."user_message", wsrow."label", wsrow."label_reference" );
END LOOP; -- wsrow
END LOOP; -- drow
END
$BODY$
LANGUAGE plpgsql VOLATILE;
--- Here is executing the function and bad result
select * from test.activate_translator_user(1);
NOTICE: INPUT 1 TID_seq <NULL> WS_seq <NULL> TID 0
ERROR: null value in column "tid_seq" violates not-null constraint
DETAIL: Failing row contains (null, 1, 0, null, null, 1, 0, null, null, 0, 0, null, null, null, null, null, null,
null,null, null, null, null, null, 0, null, null, null, null, null, null).
CONTEXT: SQL statement "INSERT INTO test.tmm (
"tu_id", "translator_id",
"tid_seq", "ws_grp_seq", "obj_seq",
"entry_type", "pattern_key", "pattern",
"pattern_match", "screen_class", "term_mode",
"trans_result", "port_id", "tag_id",
"cause__q850", "cause__redcom", "new_translator_id",
"new_screen_class", "duration", "call_priority__level",
"address", "macro_type", "owner_usr_id",
"owner_port_id", "sys_note", "continue_translating",
"user_message", "label", "label_reference"
) VALUES (
wsrow."tu_id", drow."translator_id",
drow."tid_seq", drow."ws_grp_seq", wsrow."obj_seq",
wsrow."entry_type", wsrow."pattern_key", patt,
wsrow."pattern_match", wsrow."screen_class", wsrow."term_mode",
wsrow."trans_result", wsrow."port_id", wsrow."tag_id",
wsrow."cause__q850", wsrow."cause__redcom", wsrow."new_translator_id",
wsrow."new_screen_class", wsrow."duration", wsrow."call_priority__level",
wsrow."address", NULL, wsrow."owner_usr_id",
wsrow."owner_port_id", wsrow."sys_note", wsrow."continue_translating",
wsrow."user_message", wsrow."label", wsrow."label_reference" )"
PL/pgSQL function test.activate_translator_user(integer) line 32 at SQL statement
-- ? why does the CTE from the command line get the right result ?
ace_db=# with xrows as(
ace_db(# select * from test.tmm where tu_id = 1
ace_db(# )
ace_db-# select translator_id, min(tid_seq), min(ws_grp_seq) from xrows group by translator_id;
translator_id | min | min
---------------+-----+-----
1 | 1 | 0
2 | 1 | 0
(2 rows)
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, August 09, 2013 10:20 AM
To: Day, David
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ?
"Day, David" <dday@redcom.com> writes:
> Looking at the outermost for loop of the function below, If I run
> this CTE query from the psql command line I am returned what I expect
> for values for translator_id and the Aggregating MIN functions. I restore the experimental data and now run the
function.
> In the context of this function I get a valid translator_id , But the
> MINed columns are NULL ????
Perhaps those column names duplicate variable names within the function, and what's being returned is the values of the
variables?
If you think there's a bug here, you'd need to provide a self-contained test case.
regards, tom lane
On 08/09/2013 02:18 PM, Day, David wrote:
> A complete self contained test case: example of the problem with my FOR LOOP using a COMMON table expression.
> Again this is version 9.3beta
>
> Any comments
Got it past the error by:
Changing:
drow test.tmm%ROWTYPE; -- deleted row holder
to:
drow record; -- deleted row holder
SELECT translator_id, MIN(tid_seq), MIN(ws_grp_seq)
FROM xrows GROUP BY translator_id
to:
SELECT translator_id, MIN(tid_seq) as tid_seq, MIN(ws_grp_seq) as
ws_grp_seq
FROM xrows GROUP BY translator_id
MIN() becomes a column min which is not in the %ROWTYPE for test.tmm
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
"Day, David" <dday@redcom.com> writes:
> A complete self contained test case: example of the problem with my FOR LOOP using a COMMON table expression.
Ah, I see the problem. It's got nothing particularly to do with CTEs;
rather, your temporary variable is of the wrong rowtype:
> drow test.tmm%ROWTYPE; -- deleted row holder
> FOR drow IN
> WITH xrows AS (
> DELETE FROM test.tmm
> WHERE tu_id = ws_id RETURNING *
> )
> SELECT translator_id, MIN(tid_seq), MIN(ws_grp_seq)
> FROM xrows GROUP BY translator_id
That SELECT returns three columns, translator_id, MIN(tid_seq),
MIN(ws_grp_seq) (all of type int). The FOR will attempt to stuff those
three values into the first three columns of "drow", which are
> name character varying,
> tu_id integer NOT NULL DEFAULT 1,
> translator_id integer NOT NULL,
All the rest are left NULL. It's bad luck that you don't get a type
mismatch error here, but there's an assignment coercion from int to
varchar, so the assignment of an int to the varchar name column doesn't
raise an error.
Personally I'd declare drow as RECORD so as to avoid the issue.
regards, tom lane
Tom,
I feel chagrined, Your absolutely right and your explanation is quite good..
I see my other test case suffered from a similar logic error.
Thanks for taking the time to look into it for me.
Dave
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, August 09, 2013 6:14 PM
To: Day, David
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ?
"Day, David" <dday@redcom.com> writes:
> A complete self contained test case: example of the problem with my FOR LOOP using a COMMON table expression.
Ah, I see the problem. It's got nothing particularly to do with CTEs; rather, your temporary variable is of the wrong
rowtype:
> drow test.tmm%ROWTYPE; -- deleted row holder
> FOR drow IN
> WITH xrows AS (
> DELETE FROM test.tmm
> WHERE tu_id = ws_id RETURNING *
> )
> SELECT translator_id, MIN(tid_seq), MIN(ws_grp_seq)
> FROM xrows GROUP BY translator_id
That SELECT returns three columns, translator_id, MIN(tid_seq),
MIN(ws_grp_seq) (all of type int). The FOR will attempt to stuff those three values into the first three columns of
"drow",which are
> name character varying,
> tu_id integer NOT NULL DEFAULT 1,
> translator_id integer NOT NULL,
All the rest are left NULL. It's bad luck that you don't get a type mismatch error here, but there's an assignment
coercionfrom int to varchar, so the assignment of an int to the varchar name column doesn't raise an error.
Personally I'd declare drow as RECORD so as to avoid the issue.
regards, tom lane