postgresql plpython bug

[Mage]

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

 

[Jonathan Ellis]

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.

First, you don't give enough context to see where your python code
generates a timestamp, but in any case it's more of a limitation than a
bug that plpython doesn't try to autoconvert certain datatypes. (Are
you even returning a datetime class, or a string?)

You could play around with strftime to try to get something postgresql
will recognize, but it's probably easier to just return an epoch value
which you can turn into a postgresql timestamp with the abstime
function.

-Jonathan

 

[Mage]

First, you don't give enough context to see where your python code
generates a timestamp, but in any case it's more of a limitation than a
bug that plpython doesn't try to autoconvert certain datatypes. (Are
you even returning a datetime class, or a string?)

I gave (almost) all the required information because my function doesn't
generates any timestamp.
I did not make any work with the timestamp field, it was only sent back
with return 'MODIFY'

Finally I found the source of the bug. It's not in plypython but it is
in plperl. I call a plperl stored procedure in my plpython stored
procedure. I won't do it anymore.

Below is the sample script which produces the bug. I have to set the
locale in plperl same to the server locales because buggy plperl
doesn't see the database server locales (which plpsql and plpython
does). I sent this issue to the general pgsql mailing list earlier.

The funny thing that this plperl bug was one of the reasons of my first
python adventures.

--------

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"