Thread: postgresql server crash on windows 7 when using plpython

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?

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 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 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 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 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 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 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 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 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