Hi,

Tom:
Sorry to email you directly but the mailing lists seem to be down
and you fixed a similar problem I reported back in October.

I just upgraded to PostgreSQL 8.2 and have a function
which crashes PostgreSQL 8.2 while logging these messages:

server process exited with exit code -1073741819
terminating any other active server processes

It crashes under Windows XP Service Pack 2 and Windows Server 2003.
Note that it worked fine with PostgreSQL 8.1

Strangely, Yesterday I got it working a few hours by calling it with
select * from UDFActualPerformanceVsStandard($1,$2::CHAR) from within
another plpgsql function but then it got back to crashing.

Thanks,
Jean-Pierre Pelletier
e-djuster

To Reproduce:

CREATE TABLE Claim (
ClaimId INTEGER NOT NULL,
AssociatePersonId INTEGER NULL,
IsOnSite BOOLEAN NOT NULL
);

CREATE TABLE SubTask (
TaskCode VARCHAR(3) NOT NULL,
subTaskId SMALLINT NOT NULL,
ReportTaskCode VARCHAR(2) NULL
);

CREATE TABLE WorkEntry (
DurationHour INTERVAL(0) NULL,
TaskCode VARCHAR(3) NULL,
SubTaskId SMALLINT NULL,
PersonId INTEGER NOT NULL,
ClaimId INTEGER NULL,
ExtensionNo CHAR(1) NULL
);

INSERT INTO Claim values (1,0,false);

CREATE TYPE UDTActualPerformanceVsStandard AS (
ClaimId INTEGER,
ExtensionNo CHAR,
IsStandard BOOLEAN,
StandardRoleId SMALLINT,
PersonId INTEGER,
ReportTaskCode VARCHAR,
PersonOrStandardRoleTaskCountItem BIGINT,
PersonOrStandardRoleTaskDistanceKm DECIMAL,
PersonOrStandardRoleTaskDurationHour INTERVAL
);

CREATE OR REPLACE FUNCTION UDFActualPerformanceVsStandard(
PClaimId INTEGER,
PExtensionNo CHAR
) RETURNS SETOF UDTActualPerformanceVsStandard AS $$
DECLARE
isOnSite BOOLEAN;
associatePersonId INTEGER;
ResultRow UDTActualPerformanceVsStandard%ROWTYPE;
BEGIN
SELECT INTO isOnSite, associatePersonId C.IsOnSite,
C.AssociatePersonId FROM Claim C WHERE PClaimId = C.ClaimId;

FOR resultRow IN
SELECT
PClaimId AS ClaimId,
PExtensionNo AS ExtensionNo,
IsStandard,
NULL AS StandardRoleId,
PersonId,
ReportTaskCode,
SUM(PersonOrStandardRoleTaskCountItem),
SUM(PersonOrStandardRoleTaskDistanceKm),
SUM(PersonOrStandardRoleTaskDurationHour)
FROM
(SELECT
FALSE AS IsStandard,
WE.PersonId,
ST.ReportTaskCode,
CAST(NULL AS BIGINT) AS PersonOrStandardRoleTaskCountItem,
CAST(NULL AS DECIMAL) AS PersonOrStandardRoleTaskDistanceKm,
SUM(WE.DurationHour) AS PersonOrStandardRoleTaskDurationHour
FROM
WorkEntry WE

INNER JOIN SubTask ST
ON WE.TaskCode = ST.TaskCode
AND WE.SubTaskId = ST.SubTaskId
WHERE
WE.ClaimId = PClaimId
AND WE.ExtensionNo = PExtensionNo
GROUP BY
WE.PersonId,

ST.ReportTaskCode
UNION ALL
SELECT
FALSE,
associatePersonId,
'DE',
NULL,
NULL,
NULL
) NamedSubselect
WHERE
PersonOrStandardRoleTaskCountItem IS NOT NULL
OR PersonOrStandardRoleTaskDistanceKm IS NOT NULL
OR PersonOrStandardRoleTaskDurationHour IS NOT NULL
OR (associatePersonId = personId AND 'DE' = ReportTaskCode)
GROUP BY
IsStandard,
PersonId,
ReportTaskCode
LOOP
RETURN NEXT resultRow;
END LOOP;

RETURN;
END;
$$ LANGUAGE PLPGSQL STABLE;

select * from UDFActualPerformanceVsStandard(1,'A');



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

JEAN-PIERRE PELLETIER wrote:

There was an SPI bug which may explain your problem, fixed after 8.2 was
released. This is the fix:

http://archives.postgresql.org/pgsql...2/msg00063.php

Not sure how you could get a patched version short of compiling it
yourself. Or you could wait for 8.2.1.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

I've compiled the 8.2.0 sources from
http://www.postgresql.org/ftp/source/v8.2/
and when run, it crash by logging the same messages.

I'll compile from the nightly snapshot tarball (which is probably 8.3 ?) and
will post
the result.

Jean-Pierre Pelletier



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

I've compiled 8.3 devel from
the latest nightly snapshot tarball, did an initdb
and when run, it crash by logging the same messages.

Jean-Pierre Pelletier



---------------------------(end of broadcast)---------------------------
TIP 1: 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

JEAN-PIERRE PELLETIER wrote:

Ok, I took the latest from the 8.2 branch and tried your function. My
server doesn't crash, but shows this error:

alvherre=# select * from UDFActualPerformanceVsStandard(1,'A');
ERROR: tupdesc reference 0xa0e148 is not owned by resource owner Portal
CONTEXTO: PL/pgSQL function "udfactualperformancevsstandard" line 9 at for over select rows

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"JEAN-PIERRE PELLETIER" <pelletier_32 (AT) sympatico (DOT) ca> writes:
Fixed for 8.2.1, thanks for the report.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate