postgresql server crash on windows 7 when using plpython

On Tue, Aug 9, 2011 at 7:54 AM, c k <shreeseva.learning@gmail.com> wrote:

Dear Friends,
For client requirements, I am testing postgresql 9.0.4 on windows 7 with
plpython. First I installed python 2.7 and then added python installation
path to windows PATH variable. Then installed postgresql from one-click
installer. Server started and every thing was ok. Then I go to create
plpythonu in a database. But it failed. After searching, I found that it
needs python 2.6. So I again installed python 2.6 and then dropped plpython
from database and created again. It was successful. Normal python functions
returning text etc. are working but when conatining

import sys
from uuid import getnode as get_mac
mac = get_mac()
return mac

fails. What will be the reason?

What would be the error message(s)?

The actual function code would also be helpful, to put things into context.

Advance thanks for your help.

Chaitanya Kulkarni

--
Adrian Klaver
adrian.klaver@gmail.com

Here is the actual function.
create or replace function software.python_test() returns text as
$body$
import sys
from uuid import getnode as get_mac
mac = get_mac()
return mac
$body$
language plpythonu volatile security definer;

When running the same code from python prompt, it run correctly without any
error.

Chaitanya Kulkarni
On Wed, Aug 10, 2011 at 12:57 AM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On 9/08/2011 10:54 PM, c k wrote:

Normal python functions returning text etc. are working but when conatining

import sys
from uuid import getnode as get_mac
mac = get_mac()
return mac

fails. What will be the reason?

You still haven't supplied the error message you get when you run this.

In the absence of better information, my guess would be that python's
uuid module uses ossp-uuid, same as PostgreSQL does, but a different
version or one compiled differently. The PostgreSQL `bin' dir with the
postgresql version of the DLL will be in the path before the Python one,
so Python is calling into a different version of the DLL than it expects
and probably crashing as a result.

That would be consistent with it working from the python command line.

To work around this, I'd either use the ossp-uuid functions via the SPI
rather than using the Python UUID module, or I'd remove the OSSP-UUID
dll from the postgresql directory. You can certainly try that to see if
it helps.

This is one of the joys you get with Windows software not being managed
by a central packaging system. Everyone bundles their own versions of
all the dependencies, leaving messes like this where two DLLs with the
same name aren't quite compatible. Yay!

--
Craig Ringer

I didn't get any error message. When calling the function from PGAdmin I get
********error**********.
When I try to re-execute it, it says 'no connection to the server'. When
checked the logs I found

2011-08-09 19:46:00 IST LOG: database system was interrupted; last known up
at 2011-08-09 19:45:17 IST
2011-08-09 19:46:00 IST LOG: database system was not properly shut down;
automatic recovery in progress
2011-08-09 19:46:00 IST FATAL: the database system is starting up
2011-08-09 19:46:00 IST LOG: consistent recovery state reached at
0/420B8C00
2011-08-09 19:46:00 IST LOG: record with zero length at 0/420B8C00
2011-08-09 19:46:00 IST LOG: redo is not required
2011-08-09 19:46:00 IST LOG: database system is ready to accept connections
2011-08-09 19:46:01 IST LOG: autovacuum launcher started
Fatal Python error: PyThreadState_Get: no current thread

This application has requested the Runtime to terminate it in an unusual
way.
Please contact the application's support team for more information.
2011-08-09 19:49:39 IST LOG: server process (PID 2596) exited with exit
code 3
2011-08-09 19:49:39 IST LOG: terminating any other active server processes
2011-08-09 19:49:39 IST WARNING: terminating connection because of crash of
another server process
2011-08-09 19:49:39 IST 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.
2011-08-09 19:49:39 IST HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2011-08-09 19:49:39 IST LOG: all server processes terminated;
reinitializing
2011-08-09 19:49:50 IST FATAL: pre-existing shared memory block is still in
use
2011-08-09 19:49:50 IST HINT: Check if there are any old server processes
still running, and terminate them.

It only crashes when calling the first stated get_mac. If I use any other
logic inside the function it works fine. May be problem occurs when using
hardware related methods.

When I restart the server manually, there is nothing about the problem.

Chaitanya Kulkarni

On Wed, Aug 10, 2011 at 12:07 PM, Craig Ringer <ringerc@ringerc.id.au>wrote:

On Wed, Aug 10, 2011 at 1:23 AM, c k <shreeseva.learning@gmail.com> wrote:

When I try to re-execute it, it says 'no connection to the server'. When
checked the logs I found

2011-08-09 19:46:00 IST LOG:  database system was interrupted; last known up
at 2011-08-09 19:45:17 IST
2011-08-09 19:46:00 IST LOG:  database system was not properly shut down;
automatic recovery in progress
2011-08-09 19:46:00 IST FATAL:  the database system is starting up
2011-08-09 19:46:00 IST LOG:  consistent recovery state reached at
0/420B8C00
2011-08-09 19:46:00 IST LOG:  record with zero length at 0/420B8C00
2011-08-09 19:46:00 IST LOG:  redo is not required
2011-08-09 19:46:00 IST LOG:  database system is ready to accept connections
2011-08-09 19:46:01 IST LOG:  autovacuum launcher started
Fatal Python error: PyThreadState_Get: no current thread

There should be an error before that that caused the database to not
shut down properly etc.

On Tuesday, August 09, 2011 10:13:17 pm c k wrote:

Here is the actual function.
create or replace function software.python_test() returns text as
$body$
import sys
from uuid import getnode as get_mac
mac = get_mac()
return mac
$body$
language plpythonu volatile security definer;

When running the same code from python prompt, it run correctly without any
error.

The above function runs correctly in Postgres 9.0.3 on my Linux machine. I would
say Craigs post about multiple versions of uuid causing the problem is the
answer to your problem.

Chaitanya Kulkarni

--
Adrian Klaver
adrian.klaver@gmail.com

I have renamed uuid-ossp.dll to 0uuid-ossp.dll in postgresql's lib
directory. But it is also found that postgresql's bin directory is not
included in the path.
Then started the postgresql again, called the same plpython function again
and again server crashed without any details in the log.
Also searched for the above dll or similar in python's installation but not
founf, one available was not dll but was .py file.
Recent log contains following lines.

2011-08-10 22:25:38 IST LOG: database system was shut down at 2011-08-10
14:40:36 IST
2011-08-10 22:25:38 IST FATAL: the database system is starting up
2011-08-10 22:25:38 IST LOG: database system is ready to accept connections
2011-08-10 22:25:38 IST LOG: autovacuum launcher started
*Fatal Python error: PyThreadState_Get: no current thread*

This application has requested the Runtime to terminate it in an unusual
way.
Please contact the application's support team for more information.
2011-08-10 22:27:49 IST LOG: server process (PID 2584) exited with exit
code 3
2011-08-10 22:27:49 IST LOG: terminating any other active server processes
2011-08-10 22:27:49 IST WARNING: terminating connection because of crash of
another server process
2011-08-10 22:27:49 IST 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.
2011-08-10 22:27:49 IST HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2011-08-10 22:27:49 IST WARNING: terminating connection because of crash of
another server process
2011-08-10 22:27:49 IST 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.
2011-08-10 22:27:49 IST HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2011-08-10 22:27:49 IST WARNING: terminating connection because of crash of
another server process
2011-08-10 22:27:49 IST 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.
2011-08-10 22:27:49 IST HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2011-08-10 22:27:49 IST WARNING: terminating connection because of crash of
another server process
2011-08-10 22:27:49 IST 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.
2011-08-10 22:27:49 IST HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2011-08-10 22:27:49 IST LOG: all server processes terminated;
reinitializing
2011-08-10 22:27:58 IST FATAL: pre-existing shared memory block is still in
use
2011-08-10 22:27:58 IST HINT: Check if there are any old server processes
still running, and terminate them.

Is it related with python threads?

Regards,
Chaitanya Kulkarni

On Wed, Aug 10, 2011 at 7:34 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On 11/08/2011 1:00 AM, c k wrote:

I have renamed uuid-ossp.dll to 0uuid-ossp.dll in postgresql's lib
directory. But it is also found that postgresql's bin directory is not
included in the path.

It doesn't have to be. On Windows, the directory containing the current
executable is implicitly the first path entry. Since postgres.exe lives
in the same directory as uuid-ossp.dll, that's the copy of the DLL
that'll be called.

Then started the postgresql again, called the same plpython function
again and again server crashed without any details in the log.
Also searched for the above dll or similar in python's installation but
not founf, one available was not dll but was .py file.

OK, so maybe Python doesn't use uuid-ossp but its own implementation in
pure Python. I'll pull out a Windows box and check.

Recent log contains following lines.

*Fatal Python error: PyThreadState_Get: no current thread*

Hmm, that's interesting. Thanks for supplying the error message; looks
like I was probably on entirely the wrong track because I was forced to
guess with not enough information.

If I get a chance I'll have a play with the function you posted and see
if I can reproduce the crash on my Win7 box.

In the mean time, if you want you can try to collect some more
information about the crash according to these instructions:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows

--
Craig Ringer

I am not going to do stack trace right now, because I am using wireless
broadband and is much costlier to download.
But as other functions are working, may be it is related with other thing.
I have forgotten to told that I am using virtual machine for windows. For
even this the normal python interpreter does not have any problems. So it
seems that the problem is occurring due to crash in python package.

Chaitanya Kulkarni

On Thu, Aug 11, 2011 at 5:37 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:

On Saturday, August 13, 2011 8:35:08 am c k wrote:

I am not going to do stack trace right now, because I am using wireless
broadband and is much costlier to download.
But as other functions are working, may be it is related with other thing.
I have forgotten to told that I am using virtual machine for windows. For
even this the normal python interpreter does not have any problems. So it
seems that the problem is occurring due to crash in python package.

My suspicion is it has to do with this from your original post:

"First I installed python 2.7 and then added python installation
path to windows PATH variable. ..."

" Then I go to create plpythonu in a database. But it failed.
After searching, I found that it needs python 2.6. So I again
installed python 2.6 and then dropped plpython from database
and created again."

Unmentioned is whether you uninstalled Python 2.7 or removed the PATH reference
to Python 2.7

Chaitanya Kulkarni

--
Adrian Klaver
adrian.klaver@gmail.com

No i didn't removed any thing. Only I have installed python 2.6. And then
tried to create plpythonu. It got created, but when tries to execute already
mentioned function server crashes.

I didn't have any clue.

Thanks and regards.

Chaitany Kulkarni

On Sun, Aug 14, 2011 at 12:37 AM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On Monday, August 22, 2011 7:24:30 am c k wrote:

No i didn't removed any thing. Only I have installed python 2.6. And then
tried to create plpythonu. It got created, but when tries to execute
already mentioned function server crashes.

I didn't have any clue.

My guess is this is the clue:

"First I installed python 2.7 and then added python installation
path to windows PATH variable. ..."

It is very possible you have a version cross reference going on. In other words
pl/pythonu is being compiled against one version of Python, but run against
another. If it is possible I would remove the Python 2.7 installation or at
least the references to it and then recompile pl/pythonu.

Thanks and regards.

Chaitany Kulkarni

--
Adrian Klaver
adrian.klaver@gmail.com

Yes,
Now I have removed python 2.7. Restarted the postgresql. When I call the
above mentioned function, now server doesn't crash. It is running, but I get
the error 'No connection to the server'. I am using PgAdmin to work with
Postgresql. I tried to execute a simple function to show python version or
path, but still I am getting the same error. I tried to execute other
plpython function but still the same problem. I can use other plsql and sql
functions correctly.
When checked the log file it have entries like this:

2011-08-24 11:19:57 IST LOG: database system was interrupted; last known up
at 2011-08-10 22:25:38 IST
2011-08-24 11:19:57 IST LOG: database system was not properly shut down;
automatic recovery in progress
2011-08-24 11:19:57 IST FATAL: the database system is starting up
2011-08-24 11:19:57 IST LOG: consistent recovery state reached at
0/424E9800
2011-08-24 11:19:57 IST LOG: redo starts at 0/424E9800
2011-08-24 11:19:57 IST LOG: record with zero length at 0/424EFC70
2011-08-24 11:19:57 IST LOG: redo done at 0/424EFC30
2011-08-24 11:19:57 IST LOG: last completed transaction was at log time
2011-08-10 22:27:35.06+05:30
2011-08-24 11:19:57 IST LOG: database system is ready to accept connections
2011-08-24 11:19:58 IST LOG: autovacuum launcher started
ImportError: No module named site
ImportError: No module named site
ImportError: No module named site
ImportError: No module named site
ImportError: No module named site
ImportError: No module named site
ImportError: No module named site

This last line is added every time I call any plpython function. Here is the
simple plpython function.

CREATE OR REPLACE FUNCTION software.pyver()
RETURNS text AS
$BODY$
import sys
#return sys.version
return sys.path
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;

What is the problem?

Chaitany Kulkarni

On Mon, Aug 22, 2011 at 8:08 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On Tuesday, August 23, 2011 11:10:19 pm c k wrote:

Yes,

ImportError: No module named site
ImportError: No module named site
ImportError: No module named site

This last line is added every time I call any plpython function. Here is
the simple plpython function.

CREATE OR REPLACE FUNCTION software.pyver()
RETURNS text AS
$BODY$
import sys
#return sys.version
return sys.path
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;

What is the problem?

plpythonu cannot find the Python installation. site is a module imported by
default by the Python interpreter. When you removed 2.7 you probably also
removed the PATH entries for Python. So to recap, previously you where running
plpythonu compiled with 2.6 using a PATH leading to 2.7, hence the crashes. Now
you have 2.7 out of the way, but no PATH to 2.6.

Chaitany Kulkarni

--
Adrian Klaver
adrian.klaver@gmail.com

No,
I have added the python directory in the PATH. Another thing is I created
another language plpython2u and succeeded. Still I will try to figure out
the problem.

Regards,

Chaitanya Kulkarni

On Wed, Aug 24, 2011 at 7:11 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On 08/24/11 10:06 AM, c k wrote:

I have added the python directory in the PATH. Another thing is I
created another language plpython2u and succeeded. Still I will try to
figure out the problem.

is it in the PATH that the server is using? the server doesn't know or
care anything about the client's PATH.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

Server and client both are a single machine.

Chaitanya Kulkarni

On Wed, Aug 24, 2011 at 10:47 PM, John R Pierce <pierce@hogranch.com> wrote:

On 08/24/11 10:33 AM, c k wrote:

Server and client both are a single machine.

that doesn't answer my question. PATH, like other environment
variables, is specific to the process. just because you change the
default path of your login account via $HOME/.profile or whatever,
doesn't have any effect on a service daemon like postgresql which is
running in an entirely seperate process context.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On 08/24/2011 10:06 AM, c k wrote:

No,
I have added the python directory in the PATH. Another thing is I
created another language plpython2u and succeeded. Still I will try to
figure out the problem.

How did you create plpythonu2?

Define succeed; the language was created or the language was created and
functions written with it ran successfully?

Regards,

Chaitanya Kulkarni

--
Adrian Klaver
adrian.klaver@gmail.com