Garry Saddington | 3 Sep 18:10 2010

transaction commit

We are using the Psycopg2 DA for zope. Is there a way to force auto 
commit on a transaction or does the use of COMMIT in a ZSQL method have 
the desired effect?
Imre Horvath | 29 Aug 15:35 2010

composite types


How can I use composite type array function parameters with psycopg2?

The situation is:

create type mytype as
        i integer;
        t character varying;

create function myfunc(in param mytype[]) as

How can I call this function form psycopg2?

Thanks in advance:
Imre Horvath
Horváth Imre | 29 Aug 15:09 2010

composite types


How can I use composite type array function parameters with psycopg2?

The situation is:

create type mytype as
	i integer;
	t character varying;

create function myfunc(in param mytype[]) as

How can I call this function form psycopg2?

Thanks in advance:
Imre Horvath
Marcelo Fernandez | 28 Aug 18:31 2010

Notify examples

Hello there,

I was trying to make a basic example of notify/listen and I've found a
big difference in the select() call between the examples directory [1]
and the official docs version [2].

The first one say:[curs],[],[],5)==([],[],[]):

but the docs say:[conn],[],[],5) == ([],[],[]):

Notice the difference in waiting input from cursor and from the
connection objects; which one is correct?

I'm using psycopg2 *v2.0* in Ubuntu 10.04, and if I try the docs
version (select([conn]...) it doesn't work:

marcelo <at> marcelo-laptop:~/desarrollo/pg_notify_example$ python

Waiting for 'NOTIFY test'
Traceback (most recent call last):
  File "", line 13, in <module>
    if[conn],[],[],5) == ([],[],[]):
TypeError: argument must be an int, or have a fileno() method.

At first I tought there was a change in the psycopg2 *v2.2* version
(which will be available in Ubuntu 10.10), but the trunk/examples
(Continue reading)

Garry Saddington | 28 Aug 15:52 2010


Psycopg2 when used in Zope via the zope adapter seems to return booleans 
as True or False. Is there any way to make it return 1 or 0 as psycopg1 did?
A.M. | 25 Aug 21:39 2010

NOTIFY payload patch

Here is a trivial patch to add support for NOTIFY payloads in 9.0b4:

(tg2env)[15:33:32][agentm <at> RD07:~/Dev/psycopg2]> git diff
diff --git a/psycopg/connection_int.c b/psycopg/connection_int.c
index 902fdbb..7177e75 100644
--- a/psycopg/connection_int.c
+++ b/psycopg/connection_int.c
 <at>  <at>  -141,9 +141,10  <at>  <at>  conn_notifies_process(connectionObject *self)
         Dprintf("conn_notifies_process: got NOTIFY from pid %d, msg = %s",
                 (int) pgn->be_pid, pgn->relname);

-        notify = PyTuple_New(2);
+        notify = PyTuple_New(3);
         PyTuple_SET_ITEM(notify, 0, PyInt_FromLong((long)pgn->be_pid));
         PyTuple_SET_ITEM(notify, 1, PyString_FromString(pgn->relname));
+       PyTuple_SET_ITEM(notify, 2, PyString_FromString(pgn->extra));
         PyList_Append(self->notifies, notify);

Note that this will break code which unpacks the tuple in an assignment:

(pid,name)  = dbconn.notifies.pop()

must become

(pid,name,payload) = dbconn.notifies.pop()

but as long as pop() doesn't return a Notification object, I don't see a way around this.

(Continue reading)

A.M. | 25 Aug 20:26 2010

official git repo really old


It looks like the git repo at git:// linked from the psycopg2 homepage
hasn't been updated since May, 2009. By googling, I found git:// . Is this now
the "official" psycopg2 repo?

A.M. | 23 Aug 21:44 2010

green polling to wait for notify


I am using the great new coroutine feature to integrate eventlet with psycopg2. I wrote the following code
to run a greenlet thread which merely waits for a NOTIFY event.

def postgresql_listen(connect_string):
    dbconn = psycopg2.connect(connect_string)
    #issue listen for events                                                                                                 
    dbcursor = dbconn.cursor()
    dbcursor.execute('LISTEN test;');
    while True:
        print 'poll in'
        state = dbconn.poll()
        print 'poll out'
        if state == psycopg2.extensions.POLL_OK:
            while dbconn.notifies:
                notification = dbconn.notifies.pop()
                print notification
  [dbconn.fileno()], [], []) #delete this line to cause infinite loop- I had to add this line
to prevent it
        elif state == psycopg2.extensions.POLL_READ:
  [dbconn.fileno()], [], [])
        elif state == psycopg2.extensions.POLL_WRITE:
  [], [dbconn.fileno()], [])
            raise OperationalError("bad state from poll: %s" % state)

I am mimicking the documentation's approach to asynchronous polling- why does poll() always return
(Continue reading)

Richard Harley | 23 Aug 16:55 2010

commit problems

We have had a problem with postgresql 8.3 on a Windows server 2008. I 
wonder whether there has been any other experiences like this. We have 
connected to Potsgres from Zope via a Psycopg adapter and noticed that 
sometimes when data is inserted via a form in Zope, the data is 
available in Zope but doesn't get committed to the database. This has 
come to a head over the weekend where at least one month's worth of data 
has disappeared and this includes changes to the actual database 
structure - luckily only on a testing machine, but concerning nevertheless.
The data seemed to disappear after we upgraded to the most recent 
version of Zpsycopg2 database adapter, previously we were using version 
1 (I know it's old, we are only testing after all) and have never had 
these sorts of problem before. It's almost as if all the data and 
changes to the db were held in memory, and Postgres continued to work 
fine from there, but when the db adapter was changed and Zope restarted 
everything was cleared from memory.
Has anyone had similar experinces or can anyone cast any light on where 
the problem may lie. To us it seems the db adapter is the most likely 
candidate but we can't understand how this could happen, explanations 
also welcome.
Rob Brown-Bayliss | 16 Aug 03:57 2010

fetchall() never returns


I have a problem with what appears to be fetchall() never returning or
not timing out.

I am fetching a set of results from a db continuously, that is get the
results, process, wait and then get the results again.

The problem is that randomly the fetch loop seems to just stop.  No
errors, nothing at all. Some times it will run for a day or more,
other times it will stop after a couple of minutes.   The database is
on the other side of the world, so there is a large round trip (600ms
or more), so I was wondering if that was the cause.

Attached is a simple test I have been using.  I set the time out on
the server to 1 second, is there any way to set the time out on the
client?  Or is there something else happening here.   Once or twice I
have had  an InternalError exception thrown, but 99% of the time it
just seems to freeze.


from __future__ import print_function

import psycopg2 as postgres
import psycopg2.extensions
import time

conn = postgres.connect(host=" ", port="5432", user="", password=" ",
database=" ", sslmode="prefer")
(Continue reading)

Imre Horvath | 10 Aug 18:52 2010

callproc type casts

I've got a plpython function, with a character varying param.
I can call it from sql.

But when i try to call it with psycopg2.callproc('testfunc', ['test']),
i've got the error:
function testfunc(unknown) does not exist
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.

I'm using postgres 8.4.4 and psycopg2 2.0.14

Thanks in advance:
Imre Horvath