Re: PL/PGSQL function problem

Hi there,

Can someone tell me if there's a fairly obvious reason why when I run a
function I've written it runs twice?

Here's the code (replete with debug - sorry). The basic structure has a
number of nested 'for' loops. The trace debug
reveals that it's running twice through yet I'm only issueing "select
generateshiftcandidatedata();" x 1.

Any help would be great.

Mark

create function generateShiftCandidateData() returns integer as '
DECLARE
rolesRec RECORD;
skillsRec RECORD;
availRec RECORD;
shiftRec RECORD;
conditionRec RECORD;
conditionsToMeet BOOLEAN := TRUE;
rowcount integer := 0;
availCount integer := 0;
counter integer := 0;
available boolean := FALSE;
done boolean := FALSE;

begin
/* find all roles */
--for rolesRec in select * from role loop
for rolesRec in select * from role where role_id = 20 loop
raise notice ''Checking for role %... '',rolesRec.role_id;

for skillsRec in select * from employee_skill where "RoleID" =
rolesRec.role_id loop
if not done then
get diagnostics rowcount = ROW_COUNT;
if rowcount > 0 then
raise notice '' No. of employees for Role % = %'',
rolesRec.role_id, rowcount;
end if;
done := not done;
end if;

/* find all availabilities for employees found */
raise notice ''checking availability for
employee(%)...'',skillsRec.EmployeeID;
select into availCount count(*) from availability where
"EmployeeID" = skillsRec.EmployeeID;
raise notice ''... available for % days'', availCount;
for availRec in select * from availability where
"EmployeeID" = skillsRec.EmployeeID loop

/* find all shifts for this role (does ordering matter?) */
for shiftRec in select * from shift where "RoleID" =
rolesRec.role_id order by "StartTime" loop

/*
for conditionRec in select * from shift_condition
where "ShiftID" = shiftRec.ShiftID loop

if conditionsToMeet then

end if;

end loop;
*/

/* now, find candidates */
available := isAvailable(shiftRec.StartTime,
shiftRec.StopTime, availRec.FromTime, availRec.ToTime);
if available then
raise notice '' employee % '', availRec.EmployeeID;
raise notice '' is available to perform shift
%'', shiftRec.ShiftID;
perform recordCandidate(availRec.EmployeeID,
shiftRec.RoleID, shiftRec.ShiftID, shiftRec.Duration);
else
--raise notice '' Employee % was not available for
shift %'',availRec.EmployeeID, shiftRec.ShiftID;
end if;
end loop;
end loop;
end loop;
counter := counter + 1;
raise notice ''No of roles processed = %'',counter;
end loop;

/* all was fine... */
RETURN 0;
end;
' language 'plpgsql';

Mark,

Whoa! Based on this, you are ready to join the pgsql-sql list. This is not a
novice question!

> Can someone tell me if there's a fairly obvious reason why when I run a
> function I've written it runs twice?
>
> Here's the code (replete with debug - sorry). The basic structure has a
> number of nested 'for' loops. The trace debug
> reveals that it's running twice through yet I'm only issueing "select
> generateshiftcandidatedata();" x 1.

Um ... how are you calling the function? From psql? And when you say "it runs
twice", what do you mean, exactly? Myabe you could paste the relevant portion
of the debug output?

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

Josh Berkus wrote:

>Mark,
>
>Whoa! Based on this, you are ready to join the pgsql-sql list. This is not a
> novice question!
>
>>Can someone tell me if there's a fairly obvious reason why when I run a
>> function I've written it runs twice?
>>
>>Here's the code (replete with debug - sorry). The basic structure has a
>> number of nested 'for' loops. The trace debug
>>reveals that it's running twice through yet I'm only issueing "select
>> generateshiftcandidatedata();" x 1.
>>
>
>Um ... how are you calling the function? From psql? And when you say "it runs
> twice", what do you mean, exactly? Myabe you could paste the relevant portion
> of the debug output?
>
>-Josh
>
Thanks for the reply Josh (x2!). Errm... i've change the script slightly
- improved the quality of the debug output - the code is the same and
yes, i'm still seeing it run twice: witness the "STARTING FUNCTION"
output x 2. This is printed once at the start of the function - or
rather it's supposed to be... ;)

Thanks for your help.

Here's the function:

create function generateShiftCandidateData() returns integer as '
DECLARE
rolesRec RECORD;
skillsRec RECORD;
availRec RECORD;
shiftRec RECORD;
conditionRec RECORD;
conditionsToMeet BOOLEAN := TRUE;
rowcount integer := 0;
availCount integer := 0;
counter integer := 0;
available boolean := FALSE;
done boolean := FALSE;

begin
/* find all roles */
--for rolesRec in select * from role loop

raise notice ''STARTING FUNCTION...'';

/*if counter >= 1 then
RETURN 0;
end if;*/

for rolesRec in select * from role where role_id = 20 loop
raise notice ''Checking for role %... '',rolesRec.role_id;

for skillsRec in select * from employee_skill where "RoleID" =
rolesRec.role_id loop
if not done then
get diagnostics rowcount = ROW_COUNT;
if rowcount > 0 then
raise notice '' No. of employees for Role % = %'',
rolesRec.role_id, rowcount;
end if;
done := not done;
end if;

/* find all availabilities for employees found */
raise notice ''checking availability for
employee(%)...'',skillsRec.EmployeeID;
select into availCount count(*) from availability where
"EmployeeID" = skillsRec.EmployeeID;
raise notice ''... available for % days'', availCount;
for availRec in select * from availability where
"EmployeeID" = skillsRec.EmployeeID loop

/* find all shifts for this role (does ordering matter?) */
for shiftRec in select * from shift where "RoleID" =
rolesRec.role_id order by "StartTime" loop

/*
for conditionRec in select * from shift_condition
where "ShiftID" = shiftRec.ShiftID loop

if conditionsToMeet then

end if;

end loop;
*/

/* now, find candidates */
available := isAvailable(shiftRec.StartTime,
shiftRec.StopTime, availRec.FromTime, availRec.ToTime);
if available then
raise notice '' employee % '', availRec.EmployeeID;
raise notice '' is available to perform shift
%'', shiftRec.ShiftID;
perform recordCandidate(availRec.EmployeeID,
shiftRec.RoleID, shiftRec.ShiftID, shiftRec.Duration);
else
--raise notice '' Employee % was not available for
shift %'',availRec.EmployeeID, shiftRec.ShiftID;
end if;
end loop;
end loop;
end loop;
counter := counter + 1;
raise notice ''No of roles processed = %'',counter;
end loop;

/* all was fine... */
RETURN 0;
end;
' language 'plpgsql';

Here's the output:

>
NOTICE: STARTING FUNCTION...
NOTICE: STARTING FUNCTION...
NOTICE: Checking for role 20...
NOTICE: Checking for role 20...
NOTICE: checking availability for employee(3)...
NOTICE: checking availability for employee(3)...
NOTICE: ... available for 5 days
NOTICE: ... available for 5 days
NOTICE: 10 lies before shift start of 7
NOTICE: 10 lies before shift start of 8
NOTICE: 10 lies before shift start of 8.5
NOTICE: employee 3
NOTICE: is available to perform shift 9
NOTICE: 10 lies before shift start of 7
NOTICE: 10 lies before shift start of 8
NOTICE: 10 lies before shift start of 8.5
NOTICE: employee 3
NOTICE: is available to perform shift 9
NOTICE: employee 3
NOTICE: is available to perform shift 8
NOTICE: 16 lies beyond shift boundary of 19.5
NOTICE: 16 lies beyond shift boundary of 20
NOTICE: 16 lies beyond shift boundary of 20.5
NOTICE: 16 lies beyond shift boundary of 23
NOTICE: 16 lies beyond shift boundary of 23
NOTICE: 12 lies before shift start of 7
NOTICE: 12 lies before shift start of 8
NOTICE: 12 lies before shift start of 8.5
NOTICE: employee 3
NOTICE: is available to perform shift 9
ERROR: Cannot insert a duplicate key into unique index sc_uk
DEBUG: Last error occured while executing PL/pgSQL function recordcandidate
DEBUG: line 9 at SQL statement
NOTICE: employee 3
NOTICE: is available to perform shift 8
NOTICE: 16 lies beyond shift boundary of 19.5
NOTICE: 16 lies beyond shift boundary of 20
NOTICE: 16 lies beyond shift boundary of 20.5
NOTICE: 16 lies beyond shift boundary of 23
NOTICE: 16 lies beyond shift boundary of 23
NOTICE: 12 lies before shift start of 7
NOTICE: 12 lies before shift start of 8
NOTICE: 12 lies before shift start of 8.5
NOTICE: employee 3
NOTICE: is available to perform shift 9
ERROR: Cannot insert a duplicate key into unique index sc_uk
schmick_dev_db=>

Mark Hesketh <renmark(at)iprimus(dot)com(dot)au> writes:
> [ very strange-looking log trace ]

I'd almost think that that trace is a mixture of output-to-the-client
with output-to-the-postmaster-log. What are you showing us, exactly?
And how did you start the postmaster?

regards, tom lane

Tom Lane wrote:

>Mark Hesketh <renmark(at)iprimus(dot)com(dot)au> writes:
>
>>[ very strange-looking log trace ]
>>
>
>I'd almost think that that trace is a mixture of output-to-the-client
>with output-to-the-postmaster-log. What are you showing us, exactly?
>And how did you start the postmaster?
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
Problem solved... it was a simple bug in my function... confusing tho... ;)

Thanks for the interest.

Mark

Hi,

There is a 16k limit on how large an SQL-query can be. How do I increase
this and is the default larger i 7.2?

Regards,

Torbjörn Andersson
---------------------------------------------------
Embryo Communication phone: +46 (0) 31-774 39 11(00)
Kungsgatan 7a fax: +46 (0)31 774 07 80
S-411 19 Göteborg mobile: 0708-30 70 04
Sweden home: http://www.embryo.se/
mail: torbjorn(dot)andersson(at)embryo(dot)se
---------------------------------------------------
"Att idag tänka annorlunda än igår skiljer den vise från den envise." — John
Steinbeck

Torbj=?ISO-8859-1?B?9g==?=rn Andersson <tobbe(at)embryo(dot)se> writes:
> There is a 16k limit on how large an SQL-query can be.

That limit went away in 7.0.

regards, tom lane

02-01-30 17.00 Tom Lane tgl(at)sss(dot)pgh(dot)pa(dot)us

> Torbj=?ISO-8859-1?B?9g==?=rn Andersson <tobbe(at)embryo(dot)se> writes:
>> There is a 16k limit on how large an SQL-query can be.
>
> That limit went away in 7.0.

I found it in 7.1.1 but it is gone in 7.1.3 which means my problem is
solved.

Regards

Torbjörn Andersson
---------------------------------------------------
Embryo Communication phone: +46 (0) 31-774 39 11(00)
Kungsgatan 7a fax: +46 (0)31 774 07 80
S-411 19 Göteborg mobile: 0708-30 70 04
Sweden home: http://www.embryo.se/
mail: torbjorn(dot)andersson(at)embryo(dot)se
---------------------------------------------------
"Att idag tänka annorlunda än igår skiljer den vise från den envise." — John
Steinbeck