Thread: Postgres Crash Running PLPGSQL Function on 8.2.3
Has anyone experienced any problems with the Postgres 8.2.x database crashing when running PLPGSQL functions?
When I try to run my plpgsql function it causes Postgres to restart unexpectedly. The code was working with prior versions of Postgres, now it seems to be crashing the postmaster. Here are my log entries...
2007-03-27 10:20:35 LOG: statement:
SELECT "2007-3".getopencharge('822512', '2007-3-31', '2007-3', 'f')
2007-03-27 10:20:35 LOG: server process (PID 3516) exited with exit code -1073741819
2007-03-27 10:20:35 LOG: terminating any other active server processes
2007-03-27 10:20:35 WARNING: terminating connection because of crash of another server process
2007-03-27 10:20:35 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2007-03-27 10:20:35 HINT: In a moment you should be able to reconnect to the database and repeat your command.
2007-03-27 10:20:35 WARNING: terminating connection because of crash of another server process
2007-03-27 10:20:35 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2007-03-27 10:20:35 HINT: In a moment you should be able to reconnect to the database and repeat your command.
2007-03-27 10:20:35 LOG: all server processes terminated; reinitializing
2007-03-27 10:20:35 LOG: database system was interrupted at 2007-03-27 10:18:52 Eastern Daylight Time
When I run the function, it runs ok the first time. The second time right after that, the crash occurs. This is consistent every time I run this test.
This problem seems to be exactly the same symptoms as a bug I reported already #3158/#3166, but have not seen much dialogue on it. I am not sure how to correct this problem as it was already working fine in a prior version of Postgres.
I am running this on a Windows 2003 Standard Server SP1. The server is an Intel P4 XEON with 1GB RAM and Ultra 320 RAID 10 Configuration. My PostgresSQL version is 8.2.3.
Can any one help with this?
Gary
"Gary Winslow" <gary.winslow@atfs.com> writes:
> Has anyone experienced any problems with the Postgres 8.2.x database
> crashing when running PLPGSQL functions?
Show us the function please --- a self-contained test case would be
best.
> This problem seems to be exactly the same symptoms as a bug I reported
> already #3158/#3166, but have not seen much dialogue on it.
I see no such bug numbers in the pgsql-bugs archives ...
regards, tom lane
Tom Lane,
I would be happy to provide a “self-contained” test case. I would have to send to you a pg_dump of it which would be rather large to send via email. Some of the code includes confidential business specific calc rules that I cannot release to the general public.
How can I provide this to you more directly?
Gary Winslow
"Gary Winslow" <gary.winslow@atfs.com> writes:
> I would be happy to provide a "self-contained" test case. I would have
> to send to you a pg_dump of it which would be rather large to send via
> email. Some of the code includes confidential business specific calc
> rules that I cannot release to the general public.
Understood; it won't go anywhere. But how large is "rather large"?
Can you duplicate the problem with no (or less) data?
regards, tom lane
I need to extract the necessary database components to repro the problem
and find out how much data it will require. I will need some time to
prepare that for sending. I should be able to prepare something today,
or tomorrow.
My Thanks for giving this problem attention. Until this is fixed, I
can't upgrade past this point (v8.1.8).
As said previously, my code works perfectly on 8.1.8 on Windows 2003
SP1. A colleague of mine tested my code on a Postgres 8.2.3 running on
FreeBSD, and it too ran without incident.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Monday, April 09, 2007 4:48 PM
To: Gary Winslow
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres Crash Running PLPGSQL Function on 8.2.3
"Gary Winslow" <gary.winslow@atfs.com> writes:
> I would be happy to provide a "self-contained" test case. I would
have
> to send to you a pg_dump of it which would be rather large to send via
> email. Some of the code includes confidential business specific calc
> rules that I cannot release to the general public.
Understood; it won't go anywhere. But how large is "rather large"?
Can you duplicate the problem with no (or less) data?
regards, tom lane
---------------------------(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
Sorry, it took so long to build a test case, but I think I have
something reproducible that won't be a burden to transport.
What made this problem even more strange was that if my tables only
contained the record I was calculating with, then the problem would not
manifest.
So, I have stripped down the database a lot and its ready for posting.
Tom, how would you like me to get this to you?
Gary
gwinn7
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gary Winslow
Sent: Tuesday, April 10, 2007 8:46 AM
To: Tom Lane; Gary Winslow
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres Crash Running PLPGSQL Function on 8.2.3
I need to extract the necessary database components to repro the problem
and find out how much data it will require. I will need some time to
prepare that for sending. I should be able to prepare something today,
or tomorrow.
My Thanks for giving this problem attention. Until this is fixed, I
can't upgrade past this point (v8.1.8).
As said previously, my code works perfectly on 8.1.8 on Windows 2003
SP1. A colleague of mine tested my code on a Postgres 8.2.3 running on
FreeBSD, and it too ran without incident.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Monday, April 09, 2007 4:48 PM
To: Gary Winslow
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres Crash Running PLPGSQL Function on 8.2.3
"Gary Winslow" <gary.winslow@atfs.com> writes:
> I would be happy to provide a "self-contained" test case. I would
have
> to send to you a pg_dump of it which would be rather large to send via
> email. Some of the code includes confidential business specific calc
> rules that I cannot release to the general public.
Understood; it won't go anywhere. But how large is "rather large"?
Can you duplicate the problem with no (or less) data?
regards, tom lane
---------------------------(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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq