[GENERAL] plpython timestamp without time zone, showing up as text instead of timestamp
My full function works fine as a standalone python script, but I was having
trouble getting it to work in Posgres.
Outside of Postgres it worked fine (because I was feeding in lists of dates
to test on).
The I did the script with psycopg2 calling in the below table and it worked
fine. (in the docs for psycopg2 it says it converts the timestamps into
python dates, this would explain why it is working in this scenario)
I finally figured out what the issue is, when using plpython and pulling a
date out of Postgres, it is treating it as text - is that intended or am I
doing something wrong?
Here is a quick/simple example to demonstrate:
--##
CREATE OR REPLACE FUNCTION some_test(subject_a timestamp without time
zone[], bt_len integer)
RETURNS timestamp without time zone AS
$BODY$
from datetime import datetime, timedelta
defined_period = timedelta(days=bt_len)
return subject_a[0] + defined_period
$BODY$
LANGUAGE plpythonu;
create table hold_dates
(
initials timestamp without time zone[]
);
insert into hold_dates values('{2014-01-09 10:10:03, 2014-02-18 10:10:03}');
select
some_test(initials, 2)
from
hold_dates;
--##
The result of the above is:
ERROR: TypeError: cannot concatenate 'str' and 'datetime.timedelta' objects
CONTEXT: Traceback (most recent call last):
PL/Python function some_test, line 5, in module
return subject_a[0] + defined_period
PL/Python function some_test
So the question is - why is plpython returning subject_a[0] as text rather
than an actual date that python can operate on?, is that intended? (trying
to learn how to think about this is a Postgres way)
Granted,
There is a workaround, I can just change my function to read:
return datetime.strptime(subject_a[0], '%Y-%m-%d %H:%M:%S') + defined_period
instead of:
return subject_a[0] + defined_period
However,
I am just trying to figure out why if postgres knows it is a day, it is not
telling python that in the function - like it would when I use psycopg2?
Re: [GENERAL] plpython timestamp without time zone, showing up as text instead of timestamp
On 03/23/2014 08:19 AM, jared wrote: My full function works fine as a standalone python script, but I was having trouble getting it to work in Posgres. Outside of Postgres it worked fine (because I was feeding in lists of dates to test on). The I did the script with psycopg2 calling in the below table and it worked fine. (in the docs for psycopg2 it says it converts the timestamps into python dates, this would explain why it is working in this scenario) I finally figured out what the issue is, when using plpython and pulling a date out of Postgres, it is treating it as text - is that intended or am I doing something wrong? Intended: http://www.postgresql.org/docs/9.3/interactive/plpython-data.html FYI, it would be a good idea to include the Postgres version. plpython has been undergoing a lot of changes recently, so it would help to peg where you are at in that sequence. So the question is - why is plpython returning subject_a[0] as text rather than an actual date that python can operate on?, is that intended? (trying to learn how to think about this is a Postgres way) See above. Granted, There is a workaround, I can just change my function to read: return datetime.strptime(subject_a[0], '%Y-%m-%d %H:%M:%S') + defined_period instead of: return subject_a[0] + defined_period However, I am just trying to figure out why if postgres knows it is a day, it is not telling python that in the function - like it would when I use psycopg2? Because psycopg2 has an additional typecasting/adaptation code: http://initd.org/psycopg/docs/usage.html#adaptation-of-python-values-to-sql-types I find dateutil(https://labix.org/python-dateutil) a handy package to have in this situation. Use parse() from the parser module to do the heavy lifting of converting from a string into a date/datetime object. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython timestamp without time zone, showing up as text instead of timestamp
On Sun, Mar 23, 2014 at 12:11 PM, Adrian Klaver adrian.kla...@aklaver.comwrote: Intended: http://www.postgresql.org/docs/9.3/interactive/plpython-data.html FYI, it would be a good idea to include the Postgres version. plpython has been undergoing a lot of changes recently, so it would help to peg where you are at in that sequence. Ah, I see it now: All other data types, including the PostgreSQL character string types, are converted to a Python str. Thanks (as an aside Postgres 9.3 and python 2.7) I find dateutil(https://labix.org/python-dateutil) a handy package to have in this situation. Use parse() from the parser module to do the heavy lifting of converting from a string into a date/datetime object. I will look into that - thanks again.
Re: [GENERAL] plpython timestamp without time zone, showing up as text instead of timestamp
On 03/23/2014 09:47 AM, jared wrote: On Sun, Mar 23, 2014 at 12:11 PM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: Intended: http://www.postgresql.org/__docs/9.3/interactive/plpython-__data.html http://www.postgresql.org/docs/9.3/interactive/plpython-data.html FYI, it would be a good idea to include the Postgres version. plpython has been undergoing a lot of changes recently, so it would help to peg where you are at in that sequence. Ah, I see it now: All other data types, including the PostgreSQL character string types, are converted to a Python str. Thanks (as an aside Postgres 9.3 and python 2.7) I find dateutil(https://labix.org/__python-dateutil https://labix.org/python-dateutil) a handy package to have in this situation. Use parse() from the parser module to do the heavy lifting of converting from a string into a date/datetime object. I will look into that - thanks again. Should have added previously, if you use dateutil you can dispense with: from datetime import datetime, timedelta and use the relativedelta module from dateutil to do your offsets -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.
I try install plpython on PostgreSQL 9.3, Ubuntu 12.04 64bit but it has
error when CREATE EXTENSION plpython3u;
2013-09-27 22:09:26 ICT LOG: database system was shut down at 2013-09-27
22:09:20 ICT
2013-09-27 22:09:26 ICT LOG: database system is ready to accept connections
2013-09-27 22:09:26 ICT LOG: autovacuum launcher started
Traceback (most recent call last):
File /usr/lib/python3.2/sysconfig.py, line 354, in _init_posix
_parse_makefile(makefile, vars)
File /usr/lib/python3.2/sysconfig.py, line 240, in _parse_makefile
with open(filename, errors=surrogateescape) as f:
IOError: [Errno 2] No such file or directory:
'/usr/lib/python3.2/config-3.2m/Makefile'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File /usr/lib/python3.2/site.py, line 549, in module
main()
File /usr/lib/python3.2/site.py, line 537, in main
known_paths = addusersitepackages(known_paths)
File /usr/lib/python3.2/site.py, line 269, in addusersitepackages
user_site = getusersitepackages()
File /usr/lib/python3.2/site.py, line 244, in getusersitepackages
user_base = getuserbase() # this will also set USER_BASE
File /usr/lib/python3.2/site.py, line 234, in getuserbase
USER_BASE = get_config_var('userbase')
File /usr/lib/python3.2/sysconfig.py, line 597, in get_config_var
return get_config_vars().get(name)
File /usr/lib/python3.2/sysconfig.py, line 494, in get_config_vars
_init_posix(_CONFIG_VARS)
File /usr/lib/python3.2/sysconfig.py, line 359, in _init_posix
raise IOError(msg)
IOError: invalid Python installation: unable to open
/usr/lib/python3.2/config-3.2m/Makefile (No such file or directory)
My python 3.2 is
tuan@tuan-VirtualBox:/$ python3.2 --version
Python 3.2.3
tuan@tuan-VirtualBox:/$ python3.2 --version
Python 3.2.3
tuan@tuan-VirtualBox:/$ python3.2
Python 3.2.3 (default, Apr 10 2013, 06:11:55)
[GCC 4.6.3] on linux2
Type help, copyright, credits or license for more information.
exit()
tuan@tuan-VirtualBox:/$
On Fri, Sep 27, 2013 at 11:53 AM, tuanhoanganh hatua...@gmail.com wrote:
If i run import pyodbc from python command and run pyodbc sql command,
everything work well.
Please help me.
Tuan Hoang Anh
On Fri, Sep 27, 2013 at 10:05 AM, John R Pierce pie...@hogranch.comwrote:
On 9/26/2013 8:00 PM, tuanhoanganh wrote:
I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64
bit. But my plpython function have ERROR: ImportError: DLL load failed: A
dynamic link library (DLL) initialization routine failed. after import
pyodbc
My Python version is ActivePython-3.2.2.3-win64-**x64, pyodbc version
is pyodbc-3.0.2.win-amd64-py3.2.
On windows 7 SP1 64bit everything run fine.
Please help me. Thanks in advance.
I just heard from a developer friend that a recent windows update broke
all KINDA dll-related things with Windows. maybe thats what is happening
here. maybe not. he hasn't gotten to the bottom of it yet.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.
On 09/27/2013 08:55 AM, tuanhoanganh wrote: I try install plpython on PostgreSQL 9.3, Ubuntu 12.04 64bit but it has error when CREATE EXTENSION plpython3u; 2013-09-27 22:09:26 ICT LOG: database system was shut down at 2013-09-27 22:09:20 ICT 2013-09-27 22:09:26 ICT LOG: database system is ready to accept connections 2013-09-27 22:09:26 ICT LOG: autovacuum launcher started Traceback (most recent call last): File /usr/lib/python3.2/sysconfig.py, line 354, in _init_posix _parse_makefile(makefile, vars) File /usr/lib/python3.2/sysconfig.py, line 240, in _parse_makefile with open(filename, errors=surrogateescape) as f: IOError: [Errno 2] No such file or directory: '/usr/lib/python3.2/config-3.2m/Makefile' During handling of the above exception, another exception occurred: At a guess you do not have the python3.2 devel package installed. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.
Hello all. I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64 bit. But my plpython function have ERROR: ImportError: DLL load failed: A dynamic link library (DLL) initialization routine failed. after import pyodbc My Python version is ActivePython-3.2.2.3-win64-x64, pyodbc version is pyodbc-3.0.2.win-amd64-py3.2. On windows 7 SP1 64bit everything run fine. Please help me. Thanks in advance. Tuan Hoang Anh
Re: [GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.
On 9/26/2013 8:00 PM, tuanhoanganh wrote: I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64 bit. But my plpython function have ERROR: ImportError: DLL load failed: A dynamic link library (DLL) initialization routine failed. after import pyodbc My Python version is ActivePython-3.2.2.3-win64-x64, pyodbc version is pyodbc-3.0.2.win-amd64-py3.2. On windows 7 SP1 64bit everything run fine. Please help me. Thanks in advance. I just heard from a developer friend that a recent windows update broke all KINDA dll-related things with Windows. maybe thats what is happening here. maybe not. he hasn't gotten to the bottom of it yet. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.
If i run import pyodbc from python command and run pyodbc sql command, everything work well. Please help me. Tuan Hoang Anh On Fri, Sep 27, 2013 at 10:05 AM, John R Pierce pie...@hogranch.com wrote: On 9/26/2013 8:00 PM, tuanhoanganh wrote: I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64 bit. But my plpython function have ERROR: ImportError: DLL load failed: A dynamic link library (DLL) initialization routine failed. after import pyodbc My Python version is ActivePython-3.2.2.3-win64-**x64, pyodbc version is pyodbc-3.0.2.win-amd64-py3.2. On windows 7 SP1 64bit everything run fine. Please help me. Thanks in advance. I just heard from a developer friend that a recent windows update broke all KINDA dll-related things with Windows. maybe thats what is happening here. maybe not. he hasn't gotten to the bottom of it yet. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors
On Mon, Jan 14, 2013 at 11:30 PM, Brian Sutherland br...@vanguardistas.net wrote: Hi, I have a plpython stored procedure which sometimes fails when I run my applications automated test suite. The procedure is called hundreds of times during the tests but only fails a few times, often with the following ImportError: Traceback (most recent call last): File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 73, in module __boot() File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 2, in __boot import sys, imp, os, os.path File /Users/jinty/src/mp/lib/python2.7/os.py, line 49, in module import posixpath as path File /Users/jinty/src/mp/lib/python2.7/posixpath.py, line 15, in module import stat ImportError: No module named stat The first thing that pops out here is that the paths contain references to buildout. Can you confirm that the exception is actually being received from PostgreSQL, or is the error coming from your test harness? Is your plpython stored procedure supposed to be using the environment constructed by buildout, or the system default environment? (we use buildout for our Python code, but our plpythonu stored procedures use the stock standard Python environment, as provided by the Ubuntu packages). If this is the correct environment, it sounds like you are triggering some sort of race condition in the buildout generated .py files. You might be able to confirm and/or work around the issue by getting your own stanza added to the top of the generated site.py, explicitly importing the problematic modules right at the top before any buildout magic happens. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors
On Thu, Jan 17, 2013 at 03:18:09PM +0700, Stuart Bishop wrote: On Mon, Jan 14, 2013 at 11:30 PM, Brian Sutherland br...@vanguardistas.net wrote: Hi, I have a plpython stored procedure which sometimes fails when I run my applications automated test suite. The procedure is called hundreds of times during the tests but only fails a few times, often with the following ImportError: Traceback (most recent call last): File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 73, in module __boot() File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 2, in __boot import sys, imp, os, os.path File /Users/jinty/src/mp/lib/python2.7/os.py, line 49, in module import posixpath as path File /Users/jinty/src/mp/lib/python2.7/posixpath.py, line 15, in module import stat ImportError: No module named stat The first thing that pops out here is that the paths contain references to buildout. Yeah, my test environment is undeniably just too complex :( Can you confirm that the exception is actually being received from PostgreSQL, or is the error coming from your test harness? It was definitely from PostgreSQL. Is your plpython stored procedure supposed to be using the environment constructed by buildout, or the system default environment? I set PYTHONPATH and PYTHONHOME so that postgres uses my buildout environment (which is inside a virtualenv :(:(). The hack I use is undeniably ugly, but does work... (we use buildout for our Python code, but our plpythonu stored procedures use the stock standard Python environment, as provided by the Ubuntu packages). Sadly, I need to get this running on OSX as that's what our developers use. On Ubuntu/Debian, I would have definitely used the system python environment. If this is the correct environment, it sounds like you are triggering some sort of race condition in the buildout generated .py files. You might be able to confirm and/or work around the issue by getting your own stanza added to the top of the generated site.py, explicitly importing the problematic modules right at the top before any buildout magic happens. I mentioned in a different post, but I did manage to resolve the issue. OSX has insanely low limits for max open files. Python hit that limit during the import and hid the real error. Increasing the open file limit with ulimit before starting postgres resolved the issue. I reported a bug about it here: http://bugs.python.org/issue16981 Many thanks for having a look anyway. -- Brian Sutherland -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors
On 17 January 2013 12:30, Brian Sutherland br...@vanguardistas.net wrote: (we use buildout for our Python code, but our plpythonu stored procedures use the stock standard Python environment, as provided by the Ubuntu packages). Sadly, I need to get this running on OSX as that's what our developers use. On Ubuntu/Debian, I would have definitely used the system python environment. Why wouldn't you do the same on OSX? It has Python 2.7 in the base system, doesn't it?
Re: [GENERAL] plpython intermittent ImportErrors
On Thu, Jan 17, 2013 at 01:25:54PM +0100, Alban Hertroys wrote: On 17 January 2013 12:30, Brian Sutherland br...@vanguardistas.net wrote: (we use buildout for our Python code, but our plpythonu stored procedures use the stock standard Python environment, as provided by the Ubuntu packages). Sadly, I need to get this running on OSX as that's what our developers use. On Ubuntu/Debian, I would have definitely used the system python environment. Why wouldn't you do the same on OSX? It has Python 2.7 in the base system, doesn't it? But the base system doesn't have many Python libraries, so you need a packaging system. Ubuntu/Debian has APT which I am personally very confortable with. -- Brian Sutherland -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors
On Wed, Jan 16, 2013 at 08:10:26AM +1100, Chris Angelico wrote: On Tue, Jan 15, 2013 at 4:55 AM, Brian Sutherland br...@vanguardistas.net wrote: I'm guessing that it's some kind of race condition, but I wouldn't know where to start looking. Look for a recursive import (A imports B, B imports A) I've always seen circular imports as deterministic. But I don't think it's this, because at least some of the tracebacks occur when importing standard library code during import site at interpreter startup. It's very unlikely there's a circular import there. or multiple threads trying to import simultaneously - Python sometimes has issues with that. Quite a few of those issues were sorted out in recent 3.x versions, but you're using 2.7. I thought Python, even in 2.7, had an import lock to prevent multiple threads importing simultaneously: http://docs.python.org/2/library/imp.html#imp.lock_held But yes, that could be a lead onto the issue, if the import lock were broken that could result in the behaviour I see. Hmm, but checking in the various modules shows that the import lock is being correctly acquired. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Brian Sutherland -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors RESOLVED
On Mon, Jan 14, 2013 at 09:05:09AM -0800, Adrian Klaver wrote:
On 01/14/2013 08:30 AM, Brian Sutherland wrote:
Hi,
I have a plpython stored procedure which sometimes fails when I run my
applications automated test suite. The procedure is called hundreds of
times during the tests but only fails a few times, often with the
following ImportError:
Traceback (most recent call last):
File
/Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 73,
in module
__boot()
File
/Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 2,
in __boot
import sys, imp, os, os.path
File /Users/jinty/src/mp/lib/python2.7/os.py, line 49, in module
import posixpath as path
File /Users/jinty/src/mp/lib/python2.7/posixpath.py, line 15, in
module
import stat
ImportError: No module named stat
Changing the order in which the tests are run, or running tests
individually makes the error move/change or disappear. The behaviour is
the same with PostgreSQL versions 9.2.2 and 9.1.7.
I have tried (but failed) to reproduce this error in a simple .sql
script. Outside of the tests, it always seems to work.
Having run into a brick wall debugging this, I'm hoping there's someone
here who can help?
Since order seems to be important what test is run prior to the
function failing versus the test run when it succeeds?
I finally got out the big hammer. I applied the attached patch to
Python/import.c and started postgres with PYTHONVERBOSE set. I
discovered that the import was failing because the fopen() call on the
module fails with:
# trying /Users/jinty/src/mp/lib/python2.7/linecache.py
Error opening file: Too many open files
So there's at least one bug here, Python should probably raise an
intelligent error message if an import fails because of too many open
files. Reported that here: http://bugs.python.org/issue16981
I had a look at the files open by the process, there were not that many,
so no leaks or anything. Just an utterly insane OSX default maximum open
file descriptors.
Running:
ulimit -n 4096
before starting PostgreSQL resolved my issue completely.
Many thanks to all who helped out!
--
Brian Sutherland
--- ./Python/import.c.orig 2013-01-16 13:37:49.0 +0100
+++ ./Python/import.c 2013-01-16 14:03:04.0 +0100
@@ -7,6 +7,7 @@
#undef Yield /* undefine macro conflicting with winbase.h */
#include pyarena.h
#include pythonrun.h
+#include errno.h
#include errcode.h
#include marshal.h
#include code.h
@@ -1478,7 +1479,13 @@
if (filemode[0] == 'U')
filemode = r PY_STDIOTEXTMODE;
fp = fopen(buf, filemode);
+if (fp == NULL Py_VerboseFlag 1) {
+PySys_WriteStderr(Error opening file: %s\n, strerror( errno
));
+}
if (fp != NULL) {
+if (Py_VerboseFlag 1) {
+PySys_WriteStderr(# OPENED\n);
+}
if (case_ok(buf, len, namelen, name))
break;
else { /* continue search */
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors RESOLVED
On 01/16/2013 08:20 AM, Brian Sutherland wrote: On Mon, Jan 14, 2013 at 09:05:09AM -0800, Adrian Klaver wrote: On 01/14/2013 08:30 AM, Brian Sutherland wrote: I had a look at the files open by the process, there were not that many, so no leaks or anything. Just an utterly insane OSX default maximum open file descriptors. Running: ulimit -n 4096 before starting PostgreSQL resolved my issue completely. Many thanks to all who helped out! Well that came from left field. That was some detective work. Thanks for the follow up. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors
On Tue, Jan 15, 2013 at 4:55 AM, Brian Sutherland br...@vanguardistas.net wrote: I'm guessing that it's some kind of race condition, but I wouldn't know where to start looking. Look for a recursive import (A imports B, B imports A) or multiple threads trying to import simultaneously - Python sometimes has issues with that. Quite a few of those issues were sorted out in recent 3.x versions, but you're using 2.7. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpython intermittent ImportErrors
Hi, I have a plpython stored procedure which sometimes fails when I run my applications automated test suite. The procedure is called hundreds of times during the tests but only fails a few times, often with the following ImportError: Traceback (most recent call last): File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 73, in module __boot() File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 2, in __boot import sys, imp, os, os.path File /Users/jinty/src/mp/lib/python2.7/os.py, line 49, in module import posixpath as path File /Users/jinty/src/mp/lib/python2.7/posixpath.py, line 15, in module import stat ImportError: No module named stat Changing the order in which the tests are run, or running tests individually makes the error move/change or disappear. The behaviour is the same with PostgreSQL versions 9.2.2 and 9.1.7. I have tried (but failed) to reproduce this error in a simple .sql script. Outside of the tests, it always seems to work. Having run into a brick wall debugging this, I'm hoping there's someone here who can help? -- Brian Sutherland -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors
On 01/14/2013 08:30 AM, Brian Sutherland wrote: Hi, I have a plpython stored procedure which sometimes fails when I run my applications automated test suite. The procedure is called hundreds of times during the tests but only fails a few times, often with the following ImportError: Traceback (most recent call last): File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 73, in module __boot() File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 2, in __boot import sys, imp, os, os.path File /Users/jinty/src/mp/lib/python2.7/os.py, line 49, in module import posixpath as path File /Users/jinty/src/mp/lib/python2.7/posixpath.py, line 15, in module import stat ImportError: No module named stat Changing the order in which the tests are run, or running tests individually makes the error move/change or disappear. The behaviour is the same with PostgreSQL versions 9.2.2 and 9.1.7. I have tried (but failed) to reproduce this error in a simple .sql script. Outside of the tests, it always seems to work. Having run into a brick wall debugging this, I'm hoping there's someone here who can help? Since order seems to be important what test is run prior to the function failing versus the test run when it succeeds? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors
On Mon, Jan 14, 2013 at 09:05:09AM -0800, Adrian Klaver wrote: On 01/14/2013 08:30 AM, Brian Sutherland wrote: Hi, I have a plpython stored procedure which sometimes fails when I run my applications automated test suite. The procedure is called hundreds of times during the tests but only fails a few times, often with the following ImportError: Traceback (most recent call last): File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 73, in module __boot() File /Users/jinty/.buildout/eggs/setuptools-0.6c11-py2.7.egg/site.py, line 2, in __boot import sys, imp, os, os.path File /Users/jinty/src/mp/lib/python2.7/os.py, line 49, in module import posixpath as path File /Users/jinty/src/mp/lib/python2.7/posixpath.py, line 15, in module import stat ImportError: No module named stat Changing the order in which the tests are run, or running tests individually makes the error move/change or disappear. The behaviour is the same with PostgreSQL versions 9.2.2 and 9.1.7. I have tried (but failed) to reproduce this error in a simple .sql script. Outside of the tests, it always seems to work. Having run into a brick wall debugging this, I'm hoping there's someone here who can help? Since order seems to be important what test is run prior to the function failing versus the test run when it succeeds? Experimenting, I can get it down to about 3 tests. At that point it succeeds about 80% of the time and the errors start being more random (i.e. different modules are unimportable). It also starts erroring inside the stored procedure itself rather than at import site time. The database backend stops closing the connection immediately. The 2 preceding tests, in this case, do not call the stored procedure (or any plpython code) at all. I'm guessing that it's some kind of race condition, but I wouldn't know where to start looking. -- Brian Sutherland -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython intermittent ImportErrors
On 01/14/2013 09:55 AM, Brian Sutherland wrote: Changing the order in which the tests are run, or running tests individually makes the error move/change or disappear. The behaviour is the same with PostgreSQL versions 9.2.2 and 9.1.7. I have tried (but failed) to reproduce this error in a simple .sql script. Outside of the tests, it always seems to work. Having run into a brick wall debugging this, I'm hoping there's someone here who can help? Since order seems to be important what test is run prior to the function failing versus the test run when it succeeds? Experimenting, I can get it down to about 3 tests. At that point it succeeds about 80% of the time and the errors start being more random (i.e. different modules are unimportable). It also starts erroring inside the stored procedure itself rather than at import site time. The database backend stops closing the connection immediately. What are the stored procedure errors? The 2 preceding tests, in this case, do not call the stored procedure (or any plpython code) at all. I'm guessing that it's some kind of race condition, but I wouldn't know where to start looking. Just a guess, something is screwing around with sys.path. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpython on postgresql 9.1
Hello, I have installed postgresql on a mac using the 'one click' installer (postgresql-9.1.1-1-osx.dmg). I then tried to import the python language but I get the error: create language plpythonu; ERROR: could not access file $libdir/plpython2: No such file or directory Can you help me in figuring out what is wrong? (I.e. where can I get plpython2?) These are some settings that might be relevant: Mac OS X 10.6.8 PostgreSQL 9.1.1 python 2.6.1 pg_config --pkglibdir /Library/PostgreSQL/9.1/lib/postgresql pg_config --libdir /Library/PostgreSQL/9.1/lib Many thanks! Dario
[GENERAL] plpython module import errors
Hello, I have installed postgresql 9 on fedora 14 having python 2.7. Now created plpythonu language in my database and created a simple function to calculate sum of two variables. while importing math libbrary and executing the function i got the error PL/Python: ImportError: No module named cmath tried to search help for this error on net and tried various things but nothing solved this. What can I do to solve this error. I can still work from python interpreter directly. Thanks in advcance. C P Kulkarni -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython module import errors
On 04/28/2011 02:19 PM, c k wrote: Hello, I have installed postgresql 9 on fedora 14 having python 2.7. Now created plpythonu language in my database and created a simple function to calculate sum of two variables. while importing math libbrary and executing the function i got the error PL/Python: ImportError: No module named cmath tried to search help for this error on net and tried various things but nothing solved this. What can I do to solve this error. I can still work from python interpreter directly. Thanks in advcance. C P Kulkarni This can happen if you have more then one version of python on the server and the postgresql is using a different one then you do on the command line. This is becuase modules are installed per version of python. The easiest way to check this is: create or replace function pyver() returns text as $$ import sys return sys.version $$ language 'plpythonu'; select pyver() and see if the version it returns is the same one as when you open the interpreter. Sim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython module import errors
you will have to compensate for python's version-i*i*t*c behaviour by naming the binary to the exact version of python you are calling e.g. mv python python5 (for python version 5 binary) mv python python6 (for python version 6 binary) then in each of the bash scripts you are calling reference python5 or python6 the correct version you can also setup an alias in the .bashrc to point to the 'right version' *friends dont let friends code in python* Martin __ Note de déni et de confidentialité Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Thu, 28 Apr 2011 15:49:14 +0300 From: s...@compulab.co.il To: shreeseva.learn...@gmail.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] plpython module import errors On 04/28/2011 02:19 PM, c k wrote: Hello, I have installed postgresql 9 on fedora 14 having python 2.7. Now created plpythonu language in my database and created a simple function to calculate sum of two variables. while importing math libbrary and executing the function i got the error PL/Python: ImportError: No module named cmath tried to search help for this error on net and tried various things but nothing solved this. What can I do to solve this error. I can still work from python interpreter directly. Thanks in advcance. C P Kulkarni This can happen if you have more then one version of python on the server and the postgresql is using a different one then you do on the command line. This is becuase modules are installed per version of python. The easiest way to check this is: create or replace function pyver() returns text as $$ import sys return sys.version $$ language 'plpythonu'; select pyver() and see if the version it returns is the same one as when you open the interpreter. Sim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython module import errors
On Thu, Apr 28, 2011 at 09:15:06AM -0400, Martin Gainty wrote: mv python python5 (for python version 5 binary) mv python python6 (for python version 6 binary) Do you happen to mean 2.5 and 2.6 ? Given that, say, our Electronic Medical Record solution happily runs on Python 2.5, 2.6, and 2.7 I wonder what the version problems are that you are referring to ? Wether any Python is a wise choice for use as a procedural language may be another matter. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython module import errors
Yes, there are three version (now). I am aware of only 2.7 installed by default in /usr/lib directory and 3.2 which I have installed externally. But the function given above shows version 2.6.4. Now the question is how to change the version postresql is calling for function execution? I have also complied the postgresql source using python option, but nothing happens. Is there is variable or like where we can set the python deirectory and version postresql have to use? Because as per manual, after installing 3.2, I have complied source, but still it does not support to python 3.2! Kindly give the details. Thanks and regards, C P Kulkarni On 4/28/11, Karsten Hilbert karsten.hilb...@gmx.net wrote: On Thu, Apr 28, 2011 at 09:15:06AM -0400, Martin Gainty wrote: mv python python5 (for python version 5 binary) mv python python6 (for python version 6 binary) Do you happen to mean 2.5 and 2.6 ? Given that, say, our Electronic Medical Record solution happily runs on Python 2.5, 2.6, and 2.7 I wonder what the version problems are that you are referring to ? Wether any Python is a wise choice for use as a procedural language may be another matter. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython module import errors
On Thursday, April 28, 2011 7:11:50 am c k wrote: Yes, there are three version (now). I am aware of only 2.7 installed by default in /usr/lib directory and 3.2 which I have installed externally. But the function given above shows version 2.6.4. Now the question is how to change the version postresql is calling for function execution? I have also complied the postgresql source using python option, but nothing happens. Is there is variable or like where we can set the python deirectory and version postresql have to use? Because as per manual, after installing 3.2, I have complied source, but still it does not support to python 3.2! It can only compile against one and by default that will be the 'system ' version. To determine what that is , at the system command line enter: python -V If you want to compile against 3.2 you will have to tell Postgres that. See here for more detail: http://www.postgresql.org/docs/9.0/interactive/install-procedure.html PYTHON Full path to the Python interpreter. This will be used to determine the dependencies for building PL/Python. Also, whether Python 2 or 3 is specified here (or otherwise implicitly chosen) determines which variant of the PL/Python language becomes available. See Section 42.1 for more information. Kindly give the details. Thanks and regards, C P Kulkarni -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] plpython module import errors
Now, I found that python version postresql is using is 2.6 and path to it is ['/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python26.zip', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python2.6/', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python2.6/plat-linux2', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python2.6/lib-tk', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python2.6/lib-old', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/lib-dynload'] what this means? It seems that this path is of the python installation which is used to compile default plpython.so. If I will compile the source with python 2.7, and change the old plpython.so with new, will my problem solved? Thanks and regards, CPKulkarni On Thu, Apr 28, 2011 at 7:59 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On Thursday, April 28, 2011 7:11:50 am c k wrote: Yes, there are three version (now). I am aware of only 2.7 installed by default in /usr/lib directory and 3.2 which I have installed externally. But the function given above shows version 2.6.4. Now the question is how to change the version postresql is calling for function execution? I have also complied the postgresql source using python option, but nothing happens. Is there is variable or like where we can set the python deirectory and version postresql have to use? Because as per manual, after installing 3.2, I have complied source, but still it does not support to python 3.2! It can only compile against one and by default that will be the 'system ' version. To determine what that is , at the system command line enter: python -V If you want to compile against 3.2 you will have to tell Postgres that. See here for more detail: http://www.postgresql.org/docs/9.0/interactive/install-procedure.html PYTHON Full path to the Python interpreter. This will be used to determine the dependencies for building PL/Python. Also, whether Python 2 or 3 is specified here (or otherwise implicitly chosen) determines which variant of the PL/Python language becomes available. See Section 42.1http://www.postgresql.org/docs/9.0/interactive/plpython-python23.htmlfor more information. Kindly give the details. Thanks and regards, C P Kulkarni -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] plpython module import errors
Cheers! Solved. What I did is complied source with python option (it failed even giving correct python 3.2 as per instruction given in the manual page you have shown) for python 2.7. From build and installed postgresql, copied plpython2.so and plpython.so to the developement server and restarted it. and it get solved. Thanks to all of you. Regards, CPKulkarni On Thu, Apr 28, 2011 at 9:57 PM, c k shreeseva.learn...@gmail.com wrote: Now, I found that python version postresql is using is 2.6 and path to it is ['/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python26.zip', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python2.6/', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python2.6/plat-linux2', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python2.6/lib-tk', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/python2.6/lib-old', '/home/apy/rrun/build/activepython-svn-trunk/build/py2_6_4-linux-x86-apy26-rrun/CoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIxCoReAcTiVePyThOnPrEfIx/lib/lib-dynload'] what this means? It seems that this path is of the python installation which is used to compile default plpython.so. If I will compile the source with python 2.7, and change the old plpython.so with new, will my problem solved? Thanks and regards, CPKulkarni On Thu, Apr 28, 2011 at 7:59 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On Thursday, April 28, 2011 7:11:50 am c k wrote: Yes, there are three version (now). I am aware of only 2.7 installed by default in /usr/lib directory and 3.2 which I have installed externally. But the function given above shows version 2.6.4. Now the question is how to change the version postresql is calling for function execution? I have also complied the postgresql source using python option, but nothing happens. Is there is variable or like where we can set the python deirectory and version postresql have to use? Because as per manual, after installing 3.2, I have complied source, but still it does not support to python 3.2! It can only compile against one and by default that will be the 'system ' version. To determine what that is , at the system command line enter: python -V If you want to compile against 3.2 you will have to tell Postgres that. See here for more detail: http://www.postgresql.org/docs/9.0/interactive/install-procedure.html PYTHON Full path to the Python interpreter. This will be used to determine the dependencies for building PL/Python. Also, whether Python 2 or 3 is specified here (or otherwise implicitly chosen) determines which variant of the PL/Python language becomes available. See Section 42.1http://www.postgresql.org/docs/9.0/interactive/plpython-python23.htmlfor more information. Kindly give the details. Thanks and regards, C P Kulkarni -- Adrian Klaver adrian.kla...@gmail.com
[GENERAL] plpython returns integer[] fails for multi-dimensional array
In postgresql-9.0.1 I have to modify my plpython functions that return arrays. It seems one dimesional arrays are handled properly, but not 2-dimensional arrays. create or replace function atest() returns integer[] as $eopy$ a = list() a.append(1) a.append(2) a.append(3) #return a works fine b = list() b.append(a) b.append(a) # error return b $eopy$ language plpythonu select atest() gives obtest=# select atest(); ERROR: invalid input syntax for integer: [1, 2, 3] CONTEXT: while creating return value PL/Python function atest How can I return multi-dimensional arrays in plpython? TJ O'Donnell -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython returns integer[] fails for multi-dimensional array
On 21 December 2010 22:48, TJ O'Donnell t...@acm.org wrote: In postgresql-9.0.1 I have to modify my plpython functions that return arrays. It seems one dimesional arrays are handled properly, but not 2-dimensional arrays. create or replace function atest() returns integer[] as $eopy$ a = list() a.append(1) a.append(2) a.append(3) #return a works fine b = list() b.append(a) b.append(a) # error return b $eopy$ language plpythonu select atest() gives obtest=# select atest(); ERROR: invalid input syntax for integer: [1, 2, 3] CONTEXT: while creating return value PL/Python function atest How can I return multi-dimensional arrays in plpython? Are you sure that a returns okay in that scenario. You're using a list. Shouldn't you be using an array? Like: a = [] -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython returns integer[] fails for multi-dimensional array
On 21 December 2010 23:17, Thom Brown t...@linux.com wrote: Are you sure that a returns okay in that scenario. You're using a list. Shouldn't you be using an array? Like: a = [] a =[] actually declares an empty list in Python. You can return a list or a tuple from a pl/python function in 9.0 and it will be interpreted as an array at the SQL call site. You cannot in prior versions. -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython returns integer[] fails for multi-dimensional array
On Tuesday 21 December 2010 2:48:16 pm TJ O'Donnell wrote: In postgresql-9.0.1 I have to modify my plpython functions that return arrays. It seems one dimesional arrays are handled properly, but not 2-dimensional arrays. create or replace function atest() returns integer[] as $eopy$ a = list() a.append(1) a.append(2) a.append(3) #return a works fine b = list() b.append(a) b.append(a) # error return b $eopy$ language plpythonu select atest() gives obtest=# select atest(); ERROR: invalid input syntax for integer: [1, 2, 3] CONTEXT: while creating return value PL/Python function atest How can I return multi-dimensional arrays in plpython? TJ O'Donnell Maybe: create or replace function atest() returns integer[][] -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython returns integer[] fails for multi-dimensional array
On Tuesday 21 December 2010 3:25:48 pm Peter Geoghegan wrote: On 21 December 2010 23:17, Thom Brown t...@linux.com wrote: Are you sure that a returns okay in that scenario. You're using a list. Shouldn't you be using an array? Like: a = [] a =[] actually declares an empty list in Python. You can return a list or a tuple from a pl/python function in 9.0 and it will be interpreted as an array at the SQL call site. You cannot in prior versions. -- Regards, Peter Geoghegan Digging into the source for plpython seems to show it only supports one dimensional arrays. When I tried my previous example on a 9.0.1 instance it kept changing integer[][] to integer[]. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython returns integer[] fails for multi-dimensional array
In previous versions (8.x) for plpython fn returning integer[]
I created (had to create) a string in the proper SQL format {
{1,2,3}, {4,5,6} }
and returned that. It worked fine.
I LIKE the ability to not have to do that in 9.0
but I CAN'T return and string like { {1,2,3}, {4,5,6} } for a fn that
returns integer[]
AND I can't return a two-dimensional array. Not a happy 9.0 camper.
Anyone know of any plans to 9.0 plpython to support multi-dimensional arrays?
TJ O'Donnell
On Tue, Dec 21, 2010 at 4:02 PM, Adrian Klaver adrian.kla...@gmail.com wrote:
On Tuesday 21 December 2010 3:25:48 pm Peter Geoghegan wrote:
On 21 December 2010 23:17, Thom Brown t...@linux.com wrote:
Are you sure that a returns okay in that scenario. You're using a
list. Shouldn't you be using an array? Like: a = []
a =[] actually declares an empty list in Python. You can return a list
or a tuple from a pl/python function in 9.0 and it will be interpreted
as an array at the SQL call site. You cannot in prior versions.
--
Regards,
Peter Geoghegan
Digging into the source for plpython seems to show it only supports one
dimensional arrays. When I tried my previous example on a 9.0.1 instance it
kept changing integer[][] to integer[].
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpython feature idea: an option to return row results as lists
With result rows in plpython returned as dicts rather than lists, we ran
into issues with a need to preserve the column order in the resultset.
Of course, dicts in python have an arbitrary, non-random order. It's
consistent in the result but does not match the order in the query. Our
use case was a third party to a customer and ourselves who required a
specific order of columns in CSVs sent to them.
Has there ever been any interest in adding a keyword option for
returning row lists rather than dicts? I couldn't find any, so I
experimented a little and came up with the attached patch. I tested this
a little...managed to make it not segfault at the very least. :) I'm
not even close to a guru so there's probably at least one mistake.
As an example:
test=# do language plpythonu $$
a = plpy.execute(
SELECT 1 as a, 2 as b, NULL as c, ARRAY[1,2,3] as d;
, return_list=True)
for row in a:
plpy.notice(repr(row))
$$;
NOTICE: [1, 2, None, [1, 2, 3]]
CONTEXT: PL/Python anonymous code block
DO
I didn't test with Python 3/plpython3u.
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 972b205..bda4181 100644
--- a/src/pl/plpython/plpython.c
+++ b/src/pl/plpython/plpython.c
@@ -344,6 +344,7 @@ static PyObject *PLyBytes_FromBytea(PLyDatumToOb *arg, Datum d);
static PyObject *PLyString_FromDatum(PLyDatumToOb *arg, Datum d);
static PyObject *PLyList_FromArray(PLyDatumToOb *arg, Datum d);
+static PyObject *PLyList_FromTuple(PLyTypeInfo *, HeapTuple, TupleDesc);
static PyObject *PLyDict_FromTuple(PLyTypeInfo *, HeapTuple, TupleDesc);
static Datum PLyObject_ToBool(PLyTypeInfo *, PLyObToDatum *,
@@ -2039,6 +2040,52 @@ PLyList_FromArray(PLyDatumToOb *arg, Datum d)
}
static PyObject *
+PLyList_FromTuple(PLyTypeInfo *info, HeapTuple tuple, TupleDesc desc)
+{
+ PyObject*volatile list;
+ int i;
+
+ if (info-is_rowtype != 1)
+ elog(ERROR, PLyTypeInfo structure describes a datam);
+
+ list = PyList_New(0);
+ if (list == NULL)
+ PLy_elog(ERROR, could not create new list);
+
+ PG_TRY();
+ {
+ for (i = 0; i info-in.r.natts; i++)
+ {
+ Datum vattr;
+ bool is_null;
+ PyObject *value;
+
+ if (desc-attrs[i]-attisdropped)
+continue;
+
+ vattr = heap_getattr(tuple, (i + 1), desc, is_null);
+
+ if (is_null || info-in.r.atts[i].func == NULL)
+PyList_Append(list, Py_None);
+ else
+ {
+value = (info-in.r.atts[i].func) (info-in.r.atts[i], vattr);
+PyList_Append(list, value);
+Py_DECREF(value);
+ }
+ }
+ }
+ PG_CATCH();
+ {
+ Py_DECREF(list);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ return list;
+}
+
+static PyObject *
PLyDict_FromTuple(PLyTypeInfo *info, HeapTuple tuple, TupleDesc desc)
{
PyObject *volatile dict;
@@ -2491,10 +2538,10 @@ static int PLy_result_ass_slice(PyObject *, Py_ssize_t, Py_ssize_t, PyObject *);
static PyObject *PLy_spi_prepare(PyObject *, PyObject *);
-static PyObject *PLy_spi_execute(PyObject *, PyObject *);
-static PyObject *PLy_spi_execute_query(char *query, long limit);
-static PyObject *PLy_spi_execute_plan(PyObject *, PyObject *, long);
-static PyObject *PLy_spi_execute_fetch_result(SPITupleTable *, int, int);
+static PyObject *PLy_spi_execute(PyObject *, PyObject *, PyObject *);
+static PyObject *PLy_spi_execute_query(char *query, long limit, PyObject *);
+static PyObject *PLy_spi_execute_plan(PyObject *, PyObject *, long, PyObject *);
+static PyObject *PLy_spi_execute_fetch_result(SPITupleTable *, int, int, PyObject *);
static PyMethodDef PLy_plan_methods[] = {
@@ -2608,7 +2655,7 @@ static PyMethodDef PLy_methods[] = {
/*
* execute a plan or query
*/
- {execute, PLy_spi_execute, METH_VARARGS, NULL},
+ {execute, (PyCFunction)PLy_spi_execute, METH_VARARGS | METH_KEYWORDS, NULL},
{NULL, NULL, 0, NULL}
};
@@ -2929,12 +2976,15 @@ PLy_spi_prepare(PyObject *self, PyObject *args)
* execute(plan=plan, values=(foo, bar), limit=5)
*/
static PyObject *
-PLy_spi_execute(PyObject *self, PyObject *args)
+PLy_spi_execute(PyObject *self, PyObject *args, PyObject *keywds)
{
- char *query;
- PyObject *plan;
- PyObject *list = NULL;
- long limit = 0;
+ char *query;
+ PyObject *plan;
+ PyObject *list = NULL;
+ long limit = 0;
+ PyObject *return_list = Py_False;
+ static char*query_kwlist[] = {query, limit, return_list, NULL};
+static char*plan_kwlist[] = {plan, list, limit, return_list, NULL};
/* Can't execute more if we have an unhandled error */
if (PLy_error_in_progress)
@@ -2943,21 +2993,21 @@ PLy_spi_execute(PyObject *self, PyObject *args)
return NULL;
}
- if (PyArg_ParseTuple(args, s|l, query, limit))
- return PLy_spi_execute_query(query, limit);
+ if (PyArg_ParseTupleAndKeywords(args, keywds, s|lO, query_kwlist, query, limit, return_list))
+ return PLy_spi_execute_query(query, limit, return_list);
PyErr_Clear();
- if (PyArg_ParseTuple(args, O|Ol, plan, list, limit)
+ if (PyArg_ParseTupleAndKeywords(args, keywds, O|OlO,
Re: [GENERAL] plpython feature idea: an option to return row results as lists
On Fri, Jul 30, 2010 at 15:45, Derek Arnold
derek.arn...@dealerbuilt.com wrote:
With result rows in plpython returned as dicts rather than lists, we ran
into issues with a need to preserve the column order in the resultset.
Interesting, +1 for the idea.
plpy.execute(
SELECT 1 as a, 2 as b, NULL as c, ARRAY[1,2,3] as d;
, return_list=True)
Hrm... Maybe a separate function is better. I dont think that will
translate to other languages very well. For instance pl/perl, you
would end up doing something like spi_exec_query(select 1;,
undefined, {return_list=1}); ick. Yes we could make spi_exec_query()
say oh the 2nd arg is a hash? well then its the options. Seems
fragile, and i dunno about other pls.
FYI, I did look at pl/perl and pl/tcl and they seem to be able to only
return dictionaries as well.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython feature idea: an option to return row results as lists
On fre, 2010-07-30 at 16:45 -0500, Derek Arnold wrote: Has there ever been any interest in adding a keyword option for returning row lists rather than dicts? I don't think so, but it sounds like a reasonable idea. Other possible approaches are - Using a factory class like psycopg (http://initd.org/psycopg/docs/extras.html) - Using an OrderedDict -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython return setof and yield
On Sunday 16 August 2009 5:13:51 pm Nuno Mota wrote:
Hi,
I am kind of new into python, and I have been trying to port some plperl
functions into plpython, but I've run up into a problem.
Imagine the following plpython code.
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
rv = plpy.execute(SELECT 1)
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
When executing the function on the psql console I always the this error.
netbo-dev=# select * from greet('Nuno');
ERROR: error fetching next item from iterator
If I comment the first line:
rv = plpy.execute(select 1)
Turning the code into this:
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
#rv = plpy.execute(SELECT 1)
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
The code works:
netbo-dev=# select * from greet('Nuno');
how | who
-+-
0 | 0
0 | 2
0 | 3
0 | 4
0 | 5
0 | 6
I know the example code is not the best, but What I was tryng to do is
execute some SQL then process it and return it back.
I also know I could just generate the rows and place them in a list and
then return it, but I would like to know why the yield function does'nt
work in this case.
This was tried on with:
PostgreSQL 8.3.7 and Python 2.5.1
Thanks,
Nuno Mota
As a guess, in the first example you have the function creating two result sets
without declaring which one to return. In the second example you are using the
generator alone.
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython return setof and yield
Thank you very much,
Can i ask one more question ?
How do I declare the correct result set to return, this in first example
code, is it possible ?
Thank you again,
Nuno Mota
On Mon, Aug 17, 2009 at 3:21 PM, Adrian Klaver akla...@comcast.net wrote:
On Sunday 16 August 2009 5:13:51 pm Nuno Mota wrote:
Hi,
I am kind of new into python, and I have been trying to port some plperl
functions into plpython, but I've run up into a problem.
Imagine the following plpython code.
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
rv = plpy.execute(SELECT 1)
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
When executing the function on the psql console I always the this error.
netbo-dev=# select * from greet('Nuno');
ERROR: error fetching next item from iterator
If I comment the first line:
rv = plpy.execute(select 1)
Turning the code into this:
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
#rv = plpy.execute(SELECT 1)
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
The code works:
netbo-dev=# select * from greet('Nuno');
how | who
-+-
0 | 0
0 | 2
0 | 3
0 | 4
0 | 5
0 | 6
I know the example code is not the best, but What I was tryng to do is
execute some SQL then process it and return it back.
I also know I could just generate the rows and place them in a list and
then return it, but I would like to know why the yield function does'nt
work in this case.
This was tried on with:
PostgreSQL 8.3.7 and Python 2.5.1
Thanks,
Nuno Mota
As a guess, in the first example you have the function creating two result
sets
without declaring which one to return. In the second example you are using
the
generator alone.
--
Adrian Klaver
akla...@comcast.net
--
Nuno Mota nm...@net-bo.com
Management Solutions for POS
Tlf: 351 225322000
Mob: 351 936272176
Fax: 351 226177662
http://www.net-bo.com
Re: [GENERAL] plpython return setof and yield
- Nuno Mota nm...@net-bo.com wrote: Thank you very much, Can i ask one more question ? How do I declare the correct result set to return, this in first example code, is it possible ? Thank you again, Nuno Mota From here: http://www.postgresql.org/docs/8.3/interactive/plpython-funcs.html The result is returned from the Python code in the usual way, with return or yield (in case of a result-set statement). Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython return setof and yield
I have fixed the problem populating a list and returning it. Well I guess I have to learn a litte bit more of python to understand how to use yield when using multiple result sets. Thank you anyway On Mon, Aug 17, 2009 at 7:23 PM, Adrian Klaver akla...@comcast.net wrote: - Nuno Mota nm...@net-bo.com wrote: Thank you very much, Can i ask one more question ? How do I declare the correct result set to return, this in first example code, is it possible ? Thank you again, Nuno Mota From here: http://www.postgresql.org/docs/8.3/interactive/plpython-funcs.html The result is returned from the Python code in the usual way, with return or yield (in case of a result-set statement). Adrian Klaver akla...@comcast.net -- Nuno Mota nm...@net-bo.com Management Solutions for POS Tlf: 351 225322000 Mob: 351 936272176 Fax: 351 226177662 http://www.net-bo.com
Re: [GENERAL] plpython return setof and yield
Ok, now I think I understand since I already have a result set from the query I cannot use yield. The other example works because I am generating only one result set. On Mon, Aug 17, 2009 at 7:55 PM, Nuno Mota nm...@net-bo.com wrote: I have fixed the problem populating a list and returning it. Well I guess I have to learn a litte bit more of python to understand how to use yield when using multiple result sets. Thank you anyway On Mon, Aug 17, 2009 at 7:23 PM, Adrian Klaver akla...@comcast.netwrote: - Nuno Mota nm...@net-bo.com wrote: Thank you very much, Can i ask one more question ? How do I declare the correct result set to return, this in first example code, is it possible ? Thank you again, Nuno Mota From here: http://www.postgresql.org/docs/8.3/interactive/plpython-funcs.html The result is returned from the Python code in the usual way, with return or yield (in case of a result-set statement). Adrian Klaver akla...@comcast.net -- Nuno Mota nm...@net-bo.com Management Solutions for POS Tlf: 351 225322000 Mob: 351 936272176 Fax: 351 226177662 http://www.net-bo.com -- Nuno Mota nm...@net-bo.com Management Solutions for POS Tlf: 351 225322000 Mob: 351 936272176 Fax: 351 226177662 http://www.net-bo.com
[GENERAL] plpython return setof and yield
Hi,
I am kind of new into python, and I have been trying to port some plperl
functions into plpython, but I've run up into a problem.
Imagine the following plpython code.
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
rv = plpy.execute(SELECT 1)
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
When executing the function on the psql console I always the this error.
netbo-dev=# select * from greet('Nuno');
ERROR: error fetching next item from iterator
If I comment the first line:
rv = plpy.execute(select 1)
Turning the code into this:
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
#rv = plpy.execute(SELECT 1)
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
The code works:
netbo-dev=# select * from greet('Nuno');
how | who
-+-
0 | 0
0 | 2
0 | 3
0 | 4
0 | 5
0 | 6
I know the example code is not the best, but What I was tryng to do is
execute some SQL then process it and return it back.
I also know I could just generate the rows and place them in a list and then
return it, but I would like to know why the yield function does'nt work in
this case.
This was tried on with:
PostgreSQL 8.3.7 and Python 2.5.1
Thanks,
Nuno Mota
Re: [GENERAL] PLPython function and multiple line insert
Andi Klapper wrote:
sql = (INSERT INTO table1 (field1, field2, field3)
VALUES ('abc', 'abc', TRUE),
('def', 'def', FALSE),
('ghi', 'ghi', TRUE);
pypl.execute(sql)
.
.
$$
LANGUAGE 'plpythonu' VOLATILE
I ran into trouble with quoting this SQL statement in PLPython
(PostgreSQL 8.3.7). Any ideas/suggestions how to quote it either with
pypl.execute() or pypl.plan()?
The statement looks incorrect from the python point of view (not just
plpython). You should quote multi-lined strings, or use an escape
character (\) like:
line 1
line 2
'''line 1
line2'''
'line 1\n\
line2'
So I would try to rewrite the statement with:
sql = INSERT INTO table1 (field1, field2, field3)
VALUES ('abc', 'abc', TRUE),
('def', 'def', FALSE),
('ghi', 'ghi', TRUE);
pypl.execute(sql)
Thanks for any suggestions and help in advance,
Andi
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PLPython function and multiple line insert
Hi everyone,
I hope, I am on the right list. If not my apology.
I'd like to insert multiple lines as following with PLPython:
SQL statement:
INSET INTO table1 (field1, field2, field3)
VALES ('abc', 'abc', TRUE),
('def', 'def', FALSE),
('ghi', 'ghi', TRUE);
Here the proposed function:
CREATE OR REPLACE FUNCTION insert_rows()
RETURNS boolean AS
$$
.
.
.
sql = (INSERT INTO table1 (field1, field2, field3)
VALUES ('abc', 'abc', TRUE),
('def', 'def', FALSE),
('ghi', 'ghi', TRUE);
pypl.execute(sql)
.
.
$$
LANGUAGE 'plpythonu' VOLATILE
I ran into trouble with quoting this SQL statement in PLPython (PostgreSQL
8.3.7). Any ideas/suggestions how to quote it either with pypl.execute()
or pypl.plan()?
Thanks for any suggestions and help in advance,
Andi
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpython large result set
Hello, I am hoping to use plpython to perform various transforms on query results of very large size. The documentation in the official 8.3 manual makes it appear as if the results of plpy.execute are read in at once (e.g., they appear to have random access and are mutable) rather than in the hidden cursor fashion of looping through a PgSql query result set. If this correct? If so does it mean that I need to avoid plpy.execute for very large queries? If so, a cursor/generator interface would seem to be a substantial improvement for the future. If I cannot use plpy.execute, is there some way to declare and use a standard cursor from within plpython? I can find nothing on this on the web, and my own experimentation has been fruitless. Any quick example would be hugely appreciated. If none of the above works, my fallback will be to execute the query in PgSql, then within the fetch loop call a plpython procedure. This works as far as my testing has gone, but seems unfortunate. Thanks, Kevin
[GENERAL] plpython infinite loop
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm using posgresql 8.2.4 on Gentoo Linux. Is there anything that can be done about an infinite loop in plpython? I tired stopping the query (with PG Admin), but the process kept going on the server. I tried pg_cancel_backend but it kept running. I tried kill -INT and it ignored that as well. I found a response by Tom Lane from Oct. 3 2007, subject: Re: [GENERAL] pg_cancel_backend() does not work with buzz queries where he suggested the solution: Short answer: don't write infinite loops in plpython The query was a simple loop that was programmed by accident. It is using 100% of CPU. Is there a better way then restarting the server? Is this fixed in a more current version? Thanks Sim -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkl+53MACgkQjDX6szCBa+owmQCfWxyVBZdnMW3zSUqbatl83wrD 3vcAoJLrYJE8cjCkcWTJEgmkxL7U33sO =qHrx -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpython array support
Sean Davis wrote: On Nov 19, 2007 9:08 PM, Jorge Godoy [EMAIL PROTECTED] wrote: Em Monday 19 November 2007 19:29:51 Sean Davis escreveu: Just a simple question--does plpythonu (8.3beta) have support for arrays? I don't see a specific mention of it in the docs, so I suppose not. Arrays work for a long time now. I've been using them since 8.1, for sure, but I think that on 7.4 I already had code with arrays inside the database... Thanks, Jorge. I know that postgresql supports arrays, but I still don't see how plpython supports arrays. I noticed this post: http://archives.postgresql.org/pgsql-general/2007-01/msg01417.php from January, 2007 that suggests how to use the string representation of an array to convert to/from python arrays. This will work, but I was wondering if there is now a true conversion from postgresql arrays to python and vice-versa. With 8.2, no there is not. Beyond the simple number and string times everything gets cast to a string (arrays, tsvectors, etc.). I have no idea if anyone is working on this for 8.3 or later. -- Stuart Bishop [EMAIL PROTECTED] http://www.stuartbishop.net/ signature.asc Description: OpenPGP digital signature
[GENERAL] plpython and error catching
What is the expected behavior of a construct like this:
def insert_xml(elem):
id=int(elem.findtext('PMID'))
try:
plpy.execute(plan,[unicode(ET.tostring(elem)),id])
except:
plpy.execute(plan2,[unicode(ET.tostring(elem)),id])
id is a primary key on the table into which I am inserting. plan is
the execute plan for inserting new data. plan2 is for updating data
already in the database. When I run this, I am not able to catch
errors of this type:
WARNING: plpython: in function insert_medline_file:
DETAIL: plpy.Error: Unknown error in PLy_spi_execute_plan
ERROR: duplicate key value violates unique constraint medlinexml_pkey
CONTEXT: SQL statement insert into medlinexml(content,id) values
(xmlparse(CONTENT $1),$2)
Why am I not able to catch this error and execute the update plan?
The manual (8.3beta2) implies that errors generated in functions are
genuine python errors that I should be able to catch.
Thanks,
Sean
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] plpython array support
Just a simple question--does plpythonu (8.3beta) have support for arrays? I don't see a specific mention of it in the docs, so I suppose not. Thanks, Sean ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpython array support
Em Monday 19 November 2007 19:29:51 Sean Davis escreveu: Just a simple question--does plpythonu (8.3beta) have support for arrays? I don't see a specific mention of it in the docs, so I suppose not. Arrays work for a long time now. I've been using them since 8.1, for sure, but I think that on 7.4 I already had code with arrays inside the database... -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] plpython and error catching
On Monday 19 November 2007 10:37 am, Sean Davis wrote:
What is the expected behavior of a construct like this:
def insert_xml(elem):
id=int(elem.findtext('PMID'))
try:
plpy.execute(plan,[unicode(ET.tostring(elem)),id])
except:
plpy.execute(plan2,[unicode(ET.tostring(elem)),id])
id is a primary key on the table into which I am inserting. plan is
the execute plan for inserting new data. plan2 is for updating data
already in the database. When I run this, I am not able to catch
errors of this type:
WARNING: plpython: in function insert_medline_file:
DETAIL: plpy.Error: Unknown error in PLy_spi_execute_plan
ERROR: duplicate key value violates unique constraint medlinexml_pkey
CONTEXT: SQL statement insert into medlinexml(content,id) values
(xmlparse(CONTENT $1),$2)
Why am I not able to catch this error and execute the update plan?
The manual (8.3beta2) implies that errors generated in functions are
genuine python errors that I should be able to catch.
Thanks,
Sean
You might want to take another look at the manual. It says an exception can be
raised from within the function and passed to the calling query by, unless
caught, by using plpy.error or plpy.fatal. In other words the opposite of
what you are counting on.
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plpython array support
On Nov 19, 2007 9:08 PM, Jorge Godoy [EMAIL PROTECTED] wrote: Em Monday 19 November 2007 19:29:51 Sean Davis escreveu: Just a simple question--does plpythonu (8.3beta) have support for arrays? I don't see a specific mention of it in the docs, so I suppose not. Arrays work for a long time now. I've been using them since 8.1, for sure, but I think that on 7.4 I already had code with arrays inside the database... Thanks, Jorge. I know that postgresql supports arrays, but I still don't see how plpython supports arrays. I noticed this post: http://archives.postgresql.org/pgsql-general/2007-01/msg01417.php from January, 2007 that suggests how to use the string representation of an array to convert to/from python arrays. This will work, but I was wondering if there is now a true conversion from postgresql arrays to python and vice-versa. Thanks, Sean ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] plpython
Hi all, Can someone hint on resources for using plpython for writing stored procedures ? I have gone thru official docs for 8.1.5 for plpythonu but its not in detail/with examples. When is plpython going to be considered safe ? any targeted version ? regards, KM ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] plpython
km [EMAIL PROTECTED] writes: Can someone hint on resources for using plpython for writing stored procedures ? Sure. But it depends a lot on what you're willing to do. The docs have the details. This one I did just to learn it: CREATE FUNCTION f_v_fechamento(p_cliente_id integer, p_data date, p_pago boolean, OUT retorno record) RETURNS record AS $$ p_cliente_id = args[0] p_data = args[1] p_pago = args[2] w_total = 0 w_amostra = 0 w_final_do_mes = plpy.execute(SELECT f_v_final_do_mes(%s::date) % p_data) w_inicio_do_mes = plpy.execute(SELECT f_v_inicio_mes(%s::date) % p_data) retorno = dict() retorno['w_inicio_do_mes'] = w_inicio_do_mes retorno['w_final_do_mes'] = w_final_do_mes return retorno $$ LANGUAGE plpythonu STABLE; It can be rewritten in a better way but was the handier example I had here that had queries and used a bit more of PostgreSQL 8.1 :-) I have gone thru official docs for 8.1.5 for plpythonu but its not in detail/with examples. It's just pure Python. And you use plpy for querying the database. When is plpython going to be considered safe ? any targeted version ? I hope never, otherwise we loose some facilities. You understood the concept of 'safe' and 'unsafe' in PostgreSQL's docs, right? It might not be what you're thinking. Be seeing you, -- Jorge Godoy [EMAIL PROTECTED] ---(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
Re: [GENERAL] plpython
KM,Can someone hint on resources for using plpython for writing stored procedures ? I have gone thru official docs for8.1.5 for plpythonu but its not in detail/with examples.CREATE OR REPLACE FUNCTION myfunc(text) RETURNS text AS$BODY$eingabe=args[0]hello = Good Morning %s % (eingabe,) return hello$BODY$ LANGUAGE 'plpythonu' VOLATILE;ALTER FUNCTION myfunc(text) OWNER TO postgres;that as a first example to get you started. The other one I could give you is using Pyro, which brings in rather uncommon challenges. When is plpython going to be considered safe ? any targeted version ? Hey, you fell into the same safe and unsafe trap than me!unsafe does not have the information: it is possibly errorprone to use this language unsafe simply says: it is impossible for the database to guarantee, that a bad minded programmer can do harmfull thins with this language For example: with plpython you can read and delete files on the server. There is no way for PostgreSQL to stop plpython from doing harm; so it is considered unsafe.(Btw: there is no restricted mode of execution for Python, google the Python Mailingslists for it; sandbox is a helpfull keyword) So: plpython is only recommended to be used by trustworthy programmers. Not by the general public.Harald-- GHUM Harald Massapersuadere et programmareHarald Armin Massa Reinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
Re: [GENERAL] plpython
Sure. But it depends a lot on what you're willing to do. The docs have the details. This one I did just to learn it: CREATE FUNCTION f_v_fechamento(p_cliente_id integer, p_data date, p_pago boolean, OUT retorno record) RETURNS record AS $$ p_cliente_id = args[0] p_data = args[1] p_pago = args[2] w_total = 0 w_amostra = 0 w_final_do_mes = plpy.execute(SELECT f_v_final_do_mes(%s::date) % p_data) w_inicio_do_mes = plpy.execute(SELECT f_v_inicio_mes(%s::date) % p_data) retorno = dict() retorno['w_inicio_do_mes'] = w_inicio_do_mes retorno['w_final_do_mes'] = w_final_do_mes return retorno $$ LANGUAGE plpythonu STABLE; It can be rewritten in a better way but was the handier example I had here that had queries and used a bit more of PostgreSQL 8.1 :-) Thanks for that snippet. why is that 'STABLE' at the end of the function ? i am stuck at createlang for plpythonu! with postgres user error reads: $createlang plpythonu template1; createlang: language installation failed: ERROR: could not load library /usr/local/pgsql/lib/plpython.so: /usr/local/pgsql/lib/plpython.so: undefined symbol: Py_InitModule4_64 i am on a x86_64 linux box. couldnt comprehend the error. plpython.so is in /usr/local/pgsql/lib whats wrong? regards, KM ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpython
2006/10/27, km [EMAIL PROTECTED]: i am stuck at createlang for plpythonu! with postgres user error reads: $createlang plpythonu template1; createlang: language installation failed: ERROR: could not load library /usr/local/pgsql/lib/plpython.so: /usr/local/pgsql/lib/plpython.so: undefined symbol: Py_InitModule4_64 i am on a x86_64 linux box. couldnt comprehend the error. plpython.so is in /usr/local/pgsql/lib whats wrong? Read the thread named plpython.so Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] plpython error logs
Hey all When using the Fedora Core 4 rpms for plpython, I find that when an error is raised, the error logger does not report the line number of the python function where the error was raised which makes debugging the functions very difficult. Using the native Windows installer, the line number is reported. I have tried changing the log level to debug5 and raising the verbosity which gives the line number of the plpython.c module that reports the error, but still does not give the line number of the actual plpython code. Does anyone know how to enable this feature? Even better would be to get the entire traceback to be logged. Thanks, Scott ---(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
Re: [GENERAL] plpython error logs
P. Scott DeVos [EMAIL PROTECTED] writes: When using the Fedora Core 4 rpms for plpython, I find that when an error is raised, the error logger does not report the line number of the python function where the error was raised which makes debugging the functions very difficult. Using the native Windows installer, the line number is reported. It hardly seems likely that the Windows installer is what makes that work. Are these two installations the same version of Postgres? The same version of Python? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] plpython error logs
Tom Lane wrote: P. Scott DeVos [EMAIL PROTECTED] writes: When using the Fedora Core 4 rpms for plpython, I find that when an error is raised, the error logger does not report the line number of the python function where the error was raised which makes debugging the functions very difficult. Using the native Windows installer, the line number is reported. It hardly seems likely that the Windows installer is what makes that work. I'm sure the installer is not the key. I was thinking more along the lines of different compile-time options or different default configuration files. But I can't find anything point the way to what I am looking for. It isn't even that easy to compare the two--what, for example, is the equivalent to a spec file on Windows? Are these two installations the same version of Postgres? The same version of Python? regards, tom lane They are pretty close to the same versions, currently my Fedora version is a little newer, but they used to be the same version and it worked the same. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] plpython function with dictionary as function argument?
Hi all. I need to know if it is possible to call a plpython stored funcion
with an dictionary as single argument:
Suppose this python structure:
someDict = {
'field1': 'Foo',
'creepyfield': 'Bar'
}
The data base connection
CONN = pg.connect()
I want some pypgslq function
CREATE myfunction ( __dictionary__) returns void
AS
(process)
LANGUAGE pythonu
and (the desired goal) be able to execute
CONN.execute(select * from myfunction (someDict))
It this possible?
Thanks!!
--
Gerardo Herzig
Direccion General de Organizacion y Sistemas
Facultad de Medicina
U.B.A.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] plpython function with dictionary as function argument?
[EMAIL PROTECTED] wrote:
Hi all. I need to know if it is possible to call a plpython stored funcion
with an dictionary as single argument:
Suppose this python structure:
someDict = {
'field1': 'Foo',
'creepyfield': 'Bar'
}
The data base connection
CONN = pg.connect()
I want some pypgslq function
CREATE myfunction ( __dictionary__) returns void
AS
(process)
LANGUAGE pythonu
and (the desired goal) be able to execute
CONN.execute(select * from myfunction (someDict))
Marshal someDict and unmarshal it in your function. Marshal is *very* fast.
--
Peter Fein [EMAIL PROTECTED] 773-575-0694
Basically, if you're not a utopianist, you're a schmuck. -J. Feldman
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] plpython error since upgrading from 7.x to 8.x
I had a plpythonu function that worked in 7.x but since upgrading to 8.x it's giving this error: ERROR: plpython: function notify failed DETAIL: exceptions.TypeError: unsubscriptable object plpythonu IS installed in the database in which I'm trying to use the function. Any idea what the problem is? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plpython error since upgrading from 7.x to 8.x
On Wed, May 25, 2005 at 02:04:22AM -0700, CSN wrote: I had a plpythonu function that worked in 7.x but since upgrading to 8.x it's giving this error: ERROR: plpython: function notify failed DETAIL: exceptions.TypeError: unsubscriptable object Could you post a simple, self-contained example that exhibits this problem? Debugging would be easier if we could see what you're doing. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plpython error since upgrading from 7.x to 8.x
It happens when I try to insert rows: insert into table1 (col1, col2, col3) values (val1, val2, val3); I have an insert/update trigger on that table, and the plpythonu function just sends a notification email. Here's the function: if TD[new][active] != TD[old][active]: import smtplib import string fromaddr = [EMAIL PROTECTED] toaddr = [EMAIL PROTECTED] if TD[new][active] == 1: msg = From: %s\r\nTo: %s\r\nSubject: Approved\r\n\r\n % (fromaddr, toaddr) msg = msg + Greetings, ... else: msg = From: %s\r\nTo: %s\r\nSubject: New Record\r\n\r\n % (fromaddr, toaddr) msg = msg + Greetings, ... server = smtplib.SMTP(localhost) server.set_debuglevel(1) server.sendmail(fromaddr, toaddr, msg) server.quit() # I also recently switched to Dbmail, so maybe that caused a problem (but mail works fine with everything else). CSN --- Michael Fuhr [EMAIL PROTECTED] wrote: On Wed, May 25, 2005 at 02:04:22AM -0700, CSN wrote: I had a plpythonu function that worked in 7.x but since upgrading to 8.x it's giving this error: ERROR: plpython: function notify failed DETAIL: exceptions.TypeError: unsubscriptable object Could you post a simple, self-contained example that exhibits this problem? Debugging would be easier if we could see what you're doing. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ __ Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plpython error since upgrading from 7.x to 8.x
On Wed, May 25, 2005 at 12:02:22PM -0700, CSN wrote: It happens when I try to insert rows: insert into table1 (col1, col2, col3) values (val1, val2, val3); I have an insert/update trigger on that table, and the plpythonu function just sends a notification email. Here's the function: if TD[new][active] != TD[old][active]: Without a complete, self-contained example that succeeds in one version of PostgreSQL and fails in another, we still have to guess at what's going on. By complete, self-contained example I mean a sequence of SQL statements that anybody could load into an empty database and get the same results that you get. When putting together such an example, it's a good idea to reduce it as much as possible, i.e., keep removing things until you can't possibly make the example smaller and still get the behavior in question. Aside from making it easier for others to focus on the problem, sometimes the act of reducing the problem can help you find the problem on your own. My first guess would be that you're getting an error because when the event is INSERT, TD[old] is None and thus unsubscriptable. But if that's the case then I'm not sure why the version of PostgreSQL would matter, and without seeing a complete example I'm not convinced that it does. Do you still get the error if you check if TD[event] is UPDATE before trying to use TD[old]? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpython error since upgrading from 7.x to 8.x
Ah, you're right. The function appears to only cause an error on inserts and not updates. Thanks for pointing that out (I'm really green at Python). CSN --- Michael Fuhr [EMAIL PROTECTED] wrote: On Wed, May 25, 2005 at 12:02:22PM -0700, CSN wrote: It happens when I try to insert rows: insert into table1 (col1, col2, col3) values (val1, val2, val3); I have an insert/update trigger on that table, and the plpythonu function just sends a notification email. Here's the function: if TD[new][active] != TD[old][active]: Without a complete, self-contained example that succeeds in one version of PostgreSQL and fails in another, we still have to guess at what's going on. By complete, self-contained example I mean a sequence of SQL statements that anybody could load into an empty database and get the same results that you get. When putting together such an example, it's a good idea to reduce it as much as possible, i.e., keep removing things until you can't possibly make the example smaller and still get the behavior in question. Aside from making it easier for others to focus on the problem, sometimes the act of reducing the problem can help you find the problem on your own. My first guess would be that you're getting an error because when the event is INSERT, TD[old] is None and thus unsubscriptable. But if that's the case then I'm not sure why the version of PostgreSQL would matter, and without seeing a complete example I'm not convinced that it does. Do you still get the error if you check if TD[event] is UPDATE before trying to use TD[old]? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ __ Do you Yahoo!? Yahoo! Small Business - Try our new Resources site http://smallbusiness.yahoo.com/resources/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] plpython multi row return workaround
I've seen other people with this question and today it hit me full force because I need to expand a string and then return all the results. the problem is that string parsing is best done in python, but as far as I could figure out, python only allows 1 result returned. The problem: I have data in my system such as - Am29LV320M[T|B][120|90]EI(T|V) this is shortcut data, with [] meaning one of the following is required and ( ) meaning that any of those are optional. That datum Am29LV320M[T|B][120|90]EI(T|V) is therefore equal to the following: Am29LV320MT120EIT Am29LV320MT120EIV Am29LV320MT120EI Am29LV320MB120EIT Am29LV320MB120EIV Am29LV320MB120EI Am29LV320MT90EIT Am29LV320MT90EIV Am29LV320MT90EI Am29LV320MB90EIT Am29LV320MB90EIV Am29LV320MB90EI and obviously it can get much larger because you can have x number of options and choices in the options. This kind of parsing can't be easily done by plpgsql, so I wrote it in plPython. Python breaks it up very nicely, the problem is now I have a resultset and nothing to do with it. Answer: Temporary Tables. While I don't know if this is a feature by design or not (I can hear the argument both ways), if you create a temp table in the python function, it is accessible to the entire session, including any functions that called it. therefore my solution in this case is to create a temp table and populate it in the python function and return a useless value. The calling function can then access the temp table and return any kind of resultset it wants. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plpython setof row
As far as I can tell it can't be done. Mage [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello, how can I return setof rows in plpython language? I read the manual and couldn't find. Mage ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] plpython setof row
Hello, how can I return setof rows in plpython language? I read the manual and couldn't find. Mage ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plpython bug
Update:
it might be not plpython, but similar to the plperl bug I found last time.
The script which can produce the bug:
create table test (id int, date timestamp);
create or replace function trigger_test() returns trigger as $$
plpy.info(TD['new'])
return 'MODIFY'
$$ language plpythonu;
create trigger test_update before update on test for each row execute
procedure trigger_test();
insert into test values (1, now());
insert into test values (2, now());
update test set id = 3;
create or replace function test_perl() returns boolean as $$
use locale;
use POSIX qw(locale_h);
setlocale(LC_COLLATE,'hu_HU');
setlocale(LC_CTYPE,'hu_HU');
setlocale(LC_NUMERIC,'hu_HU');
return True
$$ language plperlu;
create or replace function trigger_test() returns trigger as $$
plpy.info(TD['new'])
plpy.execute('select * from test_perl()')
return 'MODIFY'
$$ language plpythonu;
update test set id = 4;
-
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 9138862 1
INSERT 9138863 1
INFO: ({'date': '2005-05-05 13:20:43.793551', 'id': 3},)
INFO: ({'date': '2005-05-05 13:20:43.794401', 'id': 3},)
UPDATE 2
CREATE FUNCTION
CREATE FUNCTION
INFO: ({'date': '2005-05-05 13:20:43.793551', 'id': 4},)
ERROR: invalid input syntax for type timestamp: 2005-05-05
13:20:43.793551
---
I don't think that plperl or plperlu with locales should be used in
production environment.
Mage
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] plpython bug
Hello! create or replace function trigger_keywords_maintain() returns trigger as $$ return 'MODIFY' $$ language plpythonu; update table set id = id where id = 7 ERROR: invalid input syntax for type timestamp: 2005-05-03 14:07:33,279213 I see that Python's timestamp format is not accepted by postgresql. Mage ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] plpython function problem workaround
The only ?issue? that I have found with it is similar to an issue I
posted about multiline in general, which does not seem to be
considered a bug.
I would say if it is documented that any newlines in a python
function, including embedded newlines, are *NIX newlines no matter
what operating system the function is created on, that would suffice.
As an example - Windows PGAdmin client. Linux Server:
create table test1(f1 varchar(50));
insert into test1 values('this is a multi line string
line2
line3
')
select * from test1 where f1='this is a multi line string
line2
line3
'
--returns 1 row
create or replace function testnewlines() returns int as
$$
x=plpy.execute(select f1 from test1 where f1='this is a multi line
string\r\nline2\r\nline3\r\n')
return x.nrows()
$$ language 'plpythonu'
--returns 1
create or replace function testnewlines() returns int as
$$
x=plpy.execute(select f1 from test1 where f1='this is a multi line string
line2
line3
')
return x.nrows()
$$ language 'plpythonu'
--returns 0
Thank You
Sim Zacks
On Fri, Mar 18, 2005 at 10:12:05PM -0700, Michael Fuhr wrote:
I just submitted a small patch to convert CRLF = LF, CR = LF.
This patch is in 8.0.2beta1, so PL/Python users might want to test
it before 8.0.2 is released. See the recent 8.0.2 Beta Available
announcement:
http://archives.postgresql.org/pgsql-general/2005-03/msg01311.php
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plpython function problem workaround
On Tue, 29 Mar 2005, Sim Zacks wrote:
The only ?issue? that I have found with it is similar to an issue I
posted about multiline in general, which does not seem to be
considered a bug.
I've posted similar concerns in the past. The whole point is that
there are two possible approaches:
1) treat text as binary - as we do now;
2) do on the wire conversion - like FTP ASCII mode.
Both have disadvantages, and both lead to unexpected results.
As I wrote before, 2) is more problematic. You'll have to reject
any file with a bare \n from a Windows, otherwise you won't be able
to process it correclty.
I think if you do:
insert into test (sometext) values ('Line one\nLine two\r\n');
-- with the literal chars, not the escape sequences
you're expecting exaclty the same on output. If the server
converts it in the Unix form:
'Line one\nLine two\n'
for storing and the converts back to the Windows form, when you do:
select sometext from test; -- from a Windows client
you get:
Line one\r\nLine two\r\n
which is not the same you entered.
I doubt FTP ASCII mode handles this correctly.
As for the examples you made (the python functions), it's a problem
with python string literals (just don't use them). Let's try this:
-- CUT HERE 8
#!/usr/bin/env python
import pgdb
db = pgdb.connect()
curs = db.cursor()
# this is only to emulate PGAdmin under Windows (I don't have it)
# (I ran the script with these uncommented on Linux)
#q = create temp table test1(f1 varchar(50));
#curs.execute(q)
#q = insert into test1 values('this is a multi line
string\r\nline2\r\nline3\r\n');
#curs.execute(q)
real test
# embedded in string literal
q1 = select count(f1) from test1 where f1 = 'this is a multi line string
line2
line3
'
# escapes (expanded by python)
q2 = select count(f1) from test1 where f1 = 'this is a multi line
string\r\nline2\r\nline3\r\n'
# escapes (expanded by PostgreSQL)
q3 = rselect count(f1) from test1 where f1 = 'this is a multi line
string\r\nline2\r\nline3\r\n'
curs.execute(q3)
# stating the obvious
print Comparisons:
print %-10s%-10s%-10s % (q1 == q2, q1 == q3, q2 == q3)
print %-10s%-10s%-10s % (q1 == q2,q1 == q3, q2 == q3)
print \nRunning tests...
curs.execute(q1)
print Test 1 (string literal):, curs.fetchone()[0]
curs.execute(q2)
print Test 2 (Python escapes):, curs.fetchone()[0]
curs.execute(q3)
print Test 3 (PG escapes):, curs.fetchone()[0]
# in case someone wonders, let's try using query parameters
astring = this is a multi line string
line2
line3
q = select count(f1) from test1 where f1 = %(mystr)s
curs.execute(q, { mystr: astring })
print Test 4 (parameters):, curs.fetchone()[0]
-- 8 CUT HERE
This is the output (on Linux):
Comparisons:
q1 == q2 q1 == q3 q2 == q3
False False False
Running tests...
Test 1 (string literal): 0
Test 2 (Python escapes): 1
Test 3 (PG escapes): 1
Test 4 (parameters): 0
Which is consistent with your examples, that is, it works _only_
with explicit escapes (either at Python level or at PostgreSQL level).
If client-side python works this way, why are you expecting server-side
python to work differently?
.TM.
--
/ / /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_/ _/ _/ [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plpython function problem workaround
On Tue, 29 Mar 2005, Marco Colombo wrote: # escapes (expanded by PostgreSQL) q3 = rselect count(f1) from test1 where f1 = 'this is a multi line string\r\nline2\r\nline3\r\n' curs.execute(q3) ^^ This line (no. 28) is useless (but harmless), please ignore it (just a cutpaste error). .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plpython function problem workaround
On Fri, Mar 18, 2005 at 10:12:05PM -0700, Michael Fuhr wrote: I just submitted a small patch to convert CRLF = LF, CR = LF. This patch is in 8.0.2beta1, so PL/Python users might want to test it before 8.0.2 is released. See the recent 8.0.2 Beta Available announcement: http://archives.postgresql.org/pgsql-general/2005-03/msg01311.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plpython function problem workaround
Sorry it took me so long to respond. I've been out for a couple days. While certain things may be permissible in a language, I think it is also important to look at the context at which the language is applied and make a determination if it will practically turn up in relevant code. If the answer is no, then I think it would be acceptable to add a note that the postgresql python implementation does not accept implicit newlines and any newlines must be explicit (the text \r\n) That being said, the only place I can see wanting to use a newline is for a situation where you would want to either do a select statement with a multiline where such as results = plpy.execute(...where textfield= 'multi line string'...) or if you returned the results and wanted to do the same type of thing. if results[0][textfield]==multi line string: ... However, this example assumes that the client writing the functions and the client inserting the data are always using the same OS, because if a linux client inserted the data and a windows client had written the function, it wouldn't receive any results, because postgresql sees the newline as valid characters. hat being the case I would say that while it is entirely possible to put multiline quoted text in python code, it would be inappropriate in a postgresql environment. Sim Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sim Zacks [EMAIL PROTECTED] writes: I've been looking at the possibility of having a planned CR in the source code and I don't see a case where it would happen. Does python actually disallow newlines in string literals? That is x = 'foo bar' Whether you think this is good style is not the question --- is it allowed by the language? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] plpython function problem workaround
On Fri, 18 Mar 2005, Tom Lane wrote: Marco Colombo [EMAIL PROTECTED] writes: Right now I'm parsing the string first, changing the resulting parse tree adding missing nodes (def, INDENT, DEINDENT) and then compiling it. Hmmm ... is this really going to be simpler or more robust than lexing the string carefully enough to insert tabs at the right places? The impression I had so far was that you'd only need to understand about Python's string-literal conventions to get that right ... and that's something that's not likely to change. I'm not so sure that parse trees can be regarded as an immutable API. regards, tom lane I've completed a proof of concept, I think I can answer: - simpler? not at all. It requires understanding of how the parser works. The whole thing is about 50 lines long, but quite a bit of parser magic is going on. And I'm far from the point I can be confident about it doing always the right thing. I still have to handle (de)allocations correctly. - more robust - yes. The only way to make sure we're lexing the string the same way python does is to use its lexer. Every single difference however subtle would call for a bug. And it's re-invening the wheel. But there's no way you can work at lexer level that I'm aware of. That is, to add tokens before sending them to the parser. So you have to work on the parser output. - I have no idea if the node API is immutable at all. For sure, the interface I'm using is one or two levels below the current one, and yes, it's more likely to change. I share your concerns here. If our problem is only string literals, maybe we can handle them with a dedicated lexer. Python string literals are quite complex (compared to other languages): http://docs.python.org/ref/strings.html but not that hard. Still, my first concern is that one day we find another corner case in python syntax that makes our blind tab adding fail. And we're back to square one. BTW, I'm not preparing a patch for now, I'm working with a test program. As soon as I finish it, either I'll post it or prepare a patch against plpython.c, for consideration. I won't say it is ready for inclusion until someone else more knowledgeable than me on both PostgreSQL and python embedding looks at it, anyway. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plpython function problem workaround
On Thu, 17 Mar 2005, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: On Thu, Mar 17, 2005 at 01:03:36PM +0100, Marco Colombo wrote: OMG! It's indenting the funtion body. I think you can't do that w/o being syntax-aware. I'm not familiar with the code, why is it adding a 'def' in front of it at all? I undestand that once you do it you'll have to shift the code by an indentation level. Presumbly because it wants to create a function, which can later be called. Since python is sensetive to whitespace it has to indent the code to make it work. Seems like we have to upgrade that thing to have a complete understanding of Python lexical rules --- at least enough to know where the line boundaries are. Which is pretty much exactly the same as knowing which CRs to strip out. So I guess we have a candidate place for a solution. Anyone want to code it up? I don't know enough Python to do it ... I'm no expert but I'll look into it. Unless someone else already tried it, I want to investigate first if it's possible to create a callable object w/o using 'def', which alters the name space and (the thing we're interested to) needs an extra identation level. At first sight, what we do now (at function creation time) is: 1) execute a function definition; 2) compile a function call, and save the resulting code object for later use. I'm wondering if we can save one step, and use a python callable object. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plpython function problem workaround
On Thu, 17 Mar 2005, Michael Fuhr wrote: On Thu, Mar 17, 2005 at 09:48:51PM -0500, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: Line-ending CRs stripped, even inside quotes; mid-line CRs converted to LF. Tests done with Python 2.4 on FreeBSD 4.11-STABLE; I wonder what Python on Windows would do. Unfortunately, I don't think that proves anything, because according to earlier discussion Python will do newline-munging when it reads a file (including a script file). The question that we have to deal with is what are the rules for a string fed to PyRun_String ... and it seems those rules are not the same. Marco, you've stated that you're against munging the code because it's not our job to 'fix' data coming from the client. But I'm suggesting that we think about the code in a different way than the current implementation does: not as a literal that we pass untouched to the Python interpreter, but rather as code that Python would munge anyway if it had read that code from a file. We could still store the code exactly as received and have the language handler munge it on the fly, as we've discovered it's already doing. Comments? Have I overlooked anything? Could munging CRs have effects that a Python programmer wouldn't expect if the same code had been read from a file? Since it mimics Python's own behavior with code read from a file, can anybody justify not doing it? If you put it that way, I'm 100% with you. Just let me suggest not to mimic its behaviour, but to use the Real Thing if we manage to. That is, directly use the Universal Line Support code provided by python itself, so that we don't even have to think about being compatible. Unfortunately, I'm new to python embedding. I think I'll ask on python lists about the function definition code. Actually, we are kind of a corner case: we ask the user to provide the function body w/o the initial def declaration. We're treating partial, incomplete python code and not a well-formed program, so we have to munge it anyway. I have no idea if and how the python C API lets you control such low level details. I think what we really want is to create a callable (anonymous) object from the source of its body. I'm experimenting a bit, trying to write a PLy_procedure_compile() that does not require source munging. I'm aiming at removing the need for extra indentation. The \r\n thing is another beast, and I'm not sure it belongs to the same place in our code. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] plpython function problem workaround
On Fri, Mar 18, 2005 at 11:34:46AM +0100, Marco Colombo wrote: Just let me suggest not to mimic its behaviour, but to use the Real Thing if we manage to. That is, directly use the Universal Line Support code provided by python itself, so that we don't even have to think about being compatible. Sounds good if the Python API provides the hooks for doing so. I had started looking into that but didn't spent much time on it. I'm experimenting a bit, trying to write a PLy_procedure_compile() that does not require source munging. I'm aiming at removing the need for extra indentation. Sounds good too, if that'll work. Looking forward to seeing what you find out. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plpython function problem workaround
Michael Fuhr [EMAIL PROTECTED] writes: On Fri, Mar 18, 2005 at 12:35:07AM -0500, Tom Lane wrote: So that part of it can be solved fairly easily. Should I submit a patch? It should be only a few additional lines in PLy_procedure_munge_source(). Would you apply it only to HEAD, or would it be considered a bug fix that REL8_0_STABLE could get as well? It might be nice to have it in 8.0.2, whenever that comes out. I think it would be reasonable to back-patch a small fix to convert CRLF. The sort of rewrite Marco is considering, I wouldn't back-patch. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] plpython function problem workaround
On Fri, 18 Mar 2005, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: On Fri, Mar 18, 2005 at 12:35:07AM -0500, Tom Lane wrote: So that part of it can be solved fairly easily. Should I submit a patch? It should be only a few additional lines in PLy_procedure_munge_source(). Would you apply it only to HEAD, or would it be considered a bug fix that REL8_0_STABLE could get as well? It might be nice to have it in 8.0.2, whenever that comes out. I think it would be reasonable to back-patch a small fix to convert CRLF. The sort of rewrite Marco is considering, I wouldn't back-patch. regards, tom lane Short update: it isn't possible to get a callable object directly from the source provided by the user, since 'return' is not valid outside function definitions in Python. Right now I'm parsing the string first, changing the resulting parse tree adding missing nodes (def, INDENT, DEINDENT) and then compiling it. Grammar definitions for a python function is: funcdef: [decorators] 'def' NAME parameters ':' suite suite: simple_stmt | NEWLINE INDENT stmt+ DEDENT What we get from the users is stmt+ (a sequence of stmt). The INDENT and DEDENT tokens are generated by the parser only when indentation level _changes_. My plan is to generate two parse trees, one from this code (with the right fname of course): def fname(): pass and one from the function definition. Then, we attach the root of the second tree where the pass node is in the first tree. We should get a parse tree ready for compilation. I wish I could push the right tokens in the right places, but it seems it's not possible. Stay tuned. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plpython function problem workaround
Marco Colombo [EMAIL PROTECTED] writes: Right now I'm parsing the string first, changing the resulting parse tree adding missing nodes (def, INDENT, DEINDENT) and then compiling it. Hmmm ... is this really going to be simpler or more robust than lexing the string carefully enough to insert tabs at the right places? The impression I had so far was that you'd only need to understand about Python's string-literal conventions to get that right ... and that's something that's not likely to change. I'm not so sure that parse trees can be regarded as an immutable API. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] plpython function problem workaround
On Fri, Mar 18, 2005 at 10:33:01AM -0500, Tom Lane wrote: I think it would be reasonable to back-patch a small fix to convert CRLF. The sort of rewrite Marco is considering, I wouldn't back-patch. I just submitted a small patch to convert CRLF = LF, CR = LF. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plpython function problem workaround
On Wed, 16 Mar 2005, Michael Fuhr wrote:
[I've changed the Subject back to the thread that started this
discussion.]
On Wed, Mar 16, 2005 at 05:52:02PM +0100, Marco Colombo wrote:
I'm against to any on-the-fly conversion, now.
I don't like the idea of PostgreSQL accepting input in one form
(\r\n) and providing output in a different form (\n). Also think of
a function definition with mixed \r\n and \n lines: we'd have no way
to reconstruct the original input.
Yeah, that's a reasonable argument against modifying the function
source code before storing it in pg_proc. But I expect this problem
will come up again, and some people might not care about being able
to reconstruct the original input if it's just a matter of stripped
carriage returns, especially if the function logic doesn't use
literal carriage return characters that would be missed. For those
people, the validator hack might be an acceptable way to deal with
a client interface that inserts carriage returns that the programmer
didn't intend anyway. Not necessarily as part of the core PostgreSQL
code or even distributed with PostgreSQL, but as something they
could install if they wanted to.
Agreed.
I think we should just state that text used for function definitions
is \n-delimited. Some languages may accept \r\n as well, but that's
undocumented side effect, and bad practice.
Whether it's an undocumented side effect depends on the language,
and whether it's bad practice is a matter of opinion.
Sure. I mean, we may just state that, per spec. Program data
should be \n-delimeted, full stop. It sounds sensible to me.
Just put it somewhere in the docs, problem solved. We're loosing
nothing. I'm just proposing to add that to the docs/specs.
In any case,
that's the language's concern and not something PostgreSQL should
judge or enforce. PostgreSQL shouldn't have to know or care about a
procedural language's syntax -- a function's source code should be an
opaque object that PostgreSQL stores and passes to the language's
handler without caring about its contents. Syntax enforcement should
be in the language's validator or handler according to the language's
own rules.
That's what we do now. My point being it's not our job to fix data
coming from the client. If a client app creates a plpython function
the wrong way, fix it. Why should we place a paperbag on a client bug?
Speaking of code munging and syntax enforcement, have a look at this:
CREATE FUNCTION foo() RETURNS text AS $$
return line 1
line 2
line 3
$$ LANGUAGE plpythonu;
SELECT foo();
foo
--
line 1
line 2
line 3
(1 row)
Eh? Where'd those leading tabs come from? Why, they came from
PLy_procedure_munge_source() in src/pl/plpython/plpython.c:
mrc = PLy_malloc(mlen);
plen = snprintf(mrc, mlen, def %s():\n\t, name);
Assert(plen = 0 plen mlen);
sp = src;
mp = mrc + plen;
while (*sp != '\0')
{
if (*sp == '\n')
{
*mp++ = *sp++;
*mp++ = '\t';
}
else
*mp++ = *sp++;
}
*mp++ = '\n';
*mp++ = '\n';
*mp = '\0';
How about them apples? The PL/Python handler is already doing some
fixup behind the scenes (and potentially causing problems, as the
example illustrates).
OMG! It's indenting the funtion body. I think you can't do that
w/o being syntax-aware. I'm not familiar with the code, why is it
adding a 'def' in front of it at all? I undestand that once you do
it you'll have to shift the code by an indentation level.
.TM.
--
/ / /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_/ _/ _/ [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] plpython function problem workaround
On Thu, Mar 17, 2005 at 01:03:36PM +0100, Marco Colombo wrote: OMG! It's indenting the funtion body. I think you can't do that w/o being syntax-aware. I'm not familiar with the code, why is it adding a 'def' in front of it at all? I undestand that once you do it you'll have to shift the code by an indentation level. Presumbly because it wants to create a function, which can later be called. Since python is sensetive to whitespace it has to indent the code to make it work. There was an example on the web somewhere (the link has been posted to this list) of a peice of python which you can load into the interpreter which will allow it to accept \r\n terminated lines. I don't recall if anyone actually tried it out or not... Won't fix the indenting problem though... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpMaQLR9wCfh.pgp Description: PGP signature
Re: [GENERAL] plpython function problem workaround
Martijn van Oosterhout kleptog@svana.org writes: On Thu, Mar 17, 2005 at 01:03:36PM +0100, Marco Colombo wrote: OMG! It's indenting the funtion body. I think you can't do that w/o being syntax-aware. I'm not familiar with the code, why is it adding a 'def' in front of it at all? I undestand that once you do it you'll have to shift the code by an indentation level. Presumbly because it wants to create a function, which can later be called. Since python is sensetive to whitespace it has to indent the code to make it work. Seems like we have to upgrade that thing to have a complete understanding of Python lexical rules --- at least enough to know where the line boundaries are. Which is pretty much exactly the same as knowing which CRs to strip out. So I guess we have a candidate place for a solution. Anyone want to code it up? I don't know enough Python to do it ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plpython function problem workaround
On Thu, Mar 17, 2005 at 10:49:24AM -0500, Tom Lane wrote: Seems like we have to upgrade that thing to have a complete understanding of Python lexical rules --- at least enough to know where the line boundaries are. Which is pretty much exactly the same as knowing which CRs to strip out. So I guess we have a candidate place for a solution. Anyone want to code it up? I don't know enough Python to do it ... [Sound of crickets] More pabulum for pondering: % cat -v foo.py print '''line 1^M line^M2^M line 3^M '''^M % python foo.py | cat -v line 1 line 2 line 3 % cat -v bar.py print 'line 1^M' % python bar.py File bar.py, line 1 print 'line 1 ^ SyntaxError: EOL while scanning single-quoted string Line-ending CRs stripped, even inside quotes; mid-line CRs converted to LF. Tests done with Python 2.4 on FreeBSD 4.11-STABLE; I wonder what Python on Windows would do. If it behaves the same way, then a munging algorithm might be CRLF = LF, otherwise CR = LF. Or we could take Marco's suggestion and do nothing, putting the burden on the client to send the right thing. That doesn't address the indentation munging, though. That appears to be a matter of knowing whether you're inside a quote or not when a LF appears. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] plpython function problem workaround
Michael Fuhr [EMAIL PROTECTED] writes: Line-ending CRs stripped, even inside quotes; mid-line CRs converted to LF. Tests done with Python 2.4 on FreeBSD 4.11-STABLE; I wonder what Python on Windows would do. Unfortunately, I don't think that proves anything, because according to earlier discussion Python will do newline-munging when it reads a file (including a script file). The question that we have to deal with is what are the rules for a string fed to PyRun_String ... and it seems those rules are not the same. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plpython function problem workaround
On Thu, Mar 17, 2005 at 09:48:51PM -0500, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: Line-ending CRs stripped, even inside quotes; mid-line CRs converted to LF. Tests done with Python 2.4 on FreeBSD 4.11-STABLE; I wonder what Python on Windows would do. Unfortunately, I don't think that proves anything, because according to earlier discussion Python will do newline-munging when it reads a file (including a script file). The question that we have to deal with is what are the rules for a string fed to PyRun_String ... and it seems those rules are not the same. I was curious about how Python's munging works with quotes that span lines, i.e., when the CRs and LFs might be considered part of a quoted string. Apparently any CR or LF is considered a line ending in an ordinary Python script, with CR and CRLF normalized to LF before being passed to the interpreter, so I'm thinking that a Python programmer wouldn't expect to be able to embed CRs in a string literal and have them remain unchanged. If that's the case, then concerns about CR conversions potentially messing up a user's strings might be unfounded. PL/Python currently treats the function source as a string that's passed unchanged (except for the added def and indentation) to PyRun_String. But that's an implementation detail that the user shouldn't have to care about: I'm wondering if, instead, PL/Python should treat the function source as Python would treat a file and do the same conversions that Python would, namely CRLF = LF and lone CR = LF. That should solve the complaints, and it should be justifiable as more than just a hack: PL/Python would simply be doing the same thing that Python would do if it had read the source from a file. That might even be less surprising than the current behavior. Marco, you've stated that you're against munging the code because it's not our job to 'fix' data coming from the client. But I'm suggesting that we think about the code in a different way than the current implementation does: not as a literal that we pass untouched to the Python interpreter, but rather as code that Python would munge anyway if it had read that code from a file. We could still store the code exactly as received and have the language handler munge it on the fly, as we've discovered it's already doing. Comments? Have I overlooked anything? Could munging CRs have effects that a Python programmer wouldn't expect if the same code had been read from a file? Since it mimics Python's own behavior with code read from a file, can anybody justify not doing it? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpython function problem workaround
Michael Fuhr [EMAIL PROTECTED] writes: Apparently any CR or LF is considered a line ending in an ordinary Python script, with CR and CRLF normalized to LF before being passed to the interpreter, so I'm thinking that a Python programmer wouldn't expect to be able to embed CRs in a string literal and have them remain unchanged. If that's the case, then concerns about CR conversions potentially messing up a user's strings might be unfounded. Yeah, it looks like you are right: http://cvs.sourceforge.net/viewcvs.py/python/python/nondist/peps/pep-0278.txt?rev=1.2 So that part of it can be solved fairly easily. Unfortunately we are still left with the issue of having to lex Python programs enough to know how to indent them ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plpython function problem workaround
On Fri, Mar 18, 2005 at 12:35:07AM -0500, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: then concerns about CR conversions potentially messing up a user's strings might be unfounded. Yeah, it looks like you are right: http://cvs.sourceforge.net/viewcvs.py/python/python/nondist/peps/pep-0278.txt?rev=1.2 So that part of it can be solved fairly easily. Should I submit a patch? It should be only a few additional lines in PLy_procedure_munge_source(). Would you apply it only to HEAD, or would it be considered a bug fix that REL8_0_STABLE could get as well? It might be nice to have it in 8.0.2, whenever that comes out. Unfortunately we are still left with the issue of having to lex Python programs enough to know how to indent them ... Yeah, that one's a little extra work, but I don't think anybody's complained about it yet. So far I'm thinking it only affects multi-line strings as in the example I posted, and even then it might not matter unless you use that string as a column value or return it to the caller. If you're doing something else like building a query statement, then the bogus indentation is probably irrelevant. It ought to be fixed, but it doesn't seem to be squeaking very loudly. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 02:50:06PM -0700, Michael Fuhr wrote: On Tue, Mar 15, 2005 at 03:41:37PM +, Ragnar Hafstað wrote: actually, perl scripts with \r\n line endings will run just fine in unix/linux. Indeed, and PL/Perl doesn't care. I just tested several PLs with PostgreSQL 8.0.1 on Solaris 9 and here are the results: PL/pgSQL CRLF ok PL/PerlCRLF ok PL/RubyCRLF ok PL/Tcl CRLF ok PL/Python CRLF fails PL/R CRLF fails Details: Thanks for the detailed test you ran. This is something I should have done before spouting off. So, really, AIUI, one of the selling points for each of the above-mentioned languages is their portability. It would appear to me that this newline incompatibility ought to be considered a major bug in python. (Maybe I'm spouting off half-cocked again, though). ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 03:41:37PM +, Ragnar Hafstað wrote: On Tue, 2005-03-15 at 07:33 -0600, David wrote: [about the line-termination problem in plpython] I'd like to insert one note here. While I'm not particularly familiar with either perl or python, when I encountered the referred-to thread regarding Windows/Unix newline incompatibilities in Python, I ran some tests on my Linux system. I wrote some very simple scripts in Perl and Python and tested them. They ran OK when I saved them in native Linux newlines, they ran, but if I saved them in DOS mode (vim), neither the Python nor the Perl scripts would run. actually, perl scripts with \r\n line endings will run just fine in unix/linux. Yes, I believe Michael Fuhr demonstrated that quite admirably in another reply to this message. what you might have been experiencing, is the fact that the unix shell is expecting \n lineendings, and the #! line could have been failing to run the perl executable. Yes, I'm now certain you are correct. On retesting, I caught something I didn't when I first ran them - the error message was identical for each language. The message was : bad interpreter: No such file or directory If the error had been internal to the language, I'm sure the messages from each would have been different. I guess this shows what happens when you shoot off your mouth when you don't know what you're talking about. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] plpython function problem workaround
[I've changed the Subject back to the thread that started this
discussion.]
On Wed, Mar 16, 2005 at 05:52:02PM +0100, Marco Colombo wrote:
I'm against to any on-the-fly conversion, now.
I don't like the idea of PostgreSQL accepting input in one form
(\r\n) and providing output in a different form (\n). Also think of
a function definition with mixed \r\n and \n lines: we'd have no way
to reconstruct the original input.
Yeah, that's a reasonable argument against modifying the function
source code before storing it in pg_proc. But I expect this problem
will come up again, and some people might not care about being able
to reconstruct the original input if it's just a matter of stripped
carriage returns, especially if the function logic doesn't use
literal carriage return characters that would be missed. For those
people, the validator hack might be an acceptable way to deal with
a client interface that inserts carriage returns that the programmer
didn't intend anyway. Not necessarily as part of the core PostgreSQL
code or even distributed with PostgreSQL, but as something they
could install if they wanted to.
I think we should just state that text used for function definitions
is \n-delimited. Some languages may accept \r\n as well, but that's
undocumented side effect, and bad practice.
Whether it's an undocumented side effect depends on the language,
and whether it's bad practice is a matter of opinion. In any case,
that's the language's concern and not something PostgreSQL should
judge or enforce. PostgreSQL shouldn't have to know or care about a
procedural language's syntax -- a function's source code should be an
opaque object that PostgreSQL stores and passes to the language's
handler without caring about its contents. Syntax enforcement should
be in the language's validator or handler according to the language's
own rules.
Speaking of code munging and syntax enforcement, have a look at this:
CREATE FUNCTION foo() RETURNS text AS $$
return line 1
line 2
line 3
$$ LANGUAGE plpythonu;
SELECT foo();
foo
--
line 1
line 2
line 3
(1 row)
Eh? Where'd those leading tabs come from? Why, they came from
PLy_procedure_munge_source() in src/pl/plpython/plpython.c:
mrc = PLy_malloc(mlen);
plen = snprintf(mrc, mlen, def %s():\n\t, name);
Assert(plen = 0 plen mlen);
sp = src;
mp = mrc + plen;
while (*sp != '\0')
{
if (*sp == '\n')
{
*mp++ = *sp++;
*mp++ = '\t';
}
else
*mp++ = *sp++;
}
*mp++ = '\n';
*mp++ = '\n';
*mp = '\0';
How about them apples? The PL/Python handler is already doing some
fixup behind the scenes (and potentially causing problems, as the
example illustrates).
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] plpython function problem workaround
I've been looking at the possibility of having a planned CR in the source code and I don't see a case where it would happen. I tried this function, as an example: create or replace function crtest() returns int as $$ x=plpy.execute(select * from pg_proc where prosrc like '%\r%') return x.nrows() $$ language 'plpythonu' and it considered the \r to be text and not a CR. Therefore the update function removed the CR at the end of the line but ignored the \r that was specifically mentioned. Is there any case that someone can think of where an actual CR, not a textual representation of it, would purposefully be put into the code? Sim Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sim Zacks [EMAIL PROTECTED] writes: I don't think that this type of solution should be discussed as an official patch. If it was, I would recommend solving the problem in source code when the function is passed to the translator. Indeed, but first we need to work out what the necessary translation is. A hack using a VALIDATOR function isn't an unreasonable way to prototype the conversion logic. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
