Thread: PyGreSQL and transactions

PyGreSQL and transactions

From
"Nagy Laszlo Zsolt , KLTE TTK pm1"
Date:
Hi all.

I'm writting a text-based server program with PyGreSQL.
My first problem follows:

>>> import pg
>>> db = pg.DB('gandalf','localhost',5432,None,None,'gandalf','')
>>> db.query('BEGIN')
>>> db.query("INSERT INTO tbl(s) VALUES('value1')")
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
>>> db.query('commit')
>>> 

The notice was sent to stderr, it seems that I have no way to catch it.
There is getnotify(), but there is no getnotice(). I must catch it,
because my server must know if the operation was successful. (It is
in a multi-tier application's middle, and sends back a message about
the operation.)

Second problem:

>>> lo = db.locreate(pg.INV_WRITE)
>>> lo
Closed large object, oid 18863
>>> lo.open(pg.INV_WRITE)
Traceback (innermost last): File "<stdin>", line 1, in ?
IOError: can't open large object.
>>> 

I have compiled the PyGreSQL module without -DNO_LARGE.

Thank in advance:
 Laszlo Nagy nagylzs@delfin.klte.hu




Re: PyGreSQL and transactions

From
"Billy G. Allie"
Date:
"Nagy Laszlo Zsolt , KLTE TTK pm1" wrote:
> 
>  Hi all.
> 
> I'm writting a text-based server program with PyGreSQL.
> My first problem follows:
> 
> >>> import pg
> >>> db = pg.DB('gandalf','localhost',5432,None,None,'gandalf','')
> >>> db.query('BEGIN')
> >>> db.query("INSERT INTO tbl(s) VALUES('value1')")
> NOTICE:  current transaction is aborted, queries ignored until end of transaction block
> >>> db.query('commit')
> >>> 
> 
> The notice was sent to stderr, it seems that I have no way to catch it.
> There is getnotify(), but there is no getnotice(). I must catch it,
> because my server must know if the operation was successful. (It is
> in a multi-tier application's middle, and sends back a message about
> the operation.)
> 

I have a Python to PostgreSQL interface program that I am preparing to release.
It has the following features:

1.  Support for cursors using 'DECLARE ... ' systax.
2.  Support the following PostgreSQL typs as Python objects:PG_BOOL, PG_INT2, PG_INT4, PG_INT8, PG_FLOAT4 PG_FLOAT8,
PG_CHAR,PG_VARCHAR, PG_TEXT,  PG_BLOB (Large Objects), PG_NUMERIC   The PG_NUMERIC support include *, /, +,  and -
operators.  The PG_BLOB fully emulates a Python File Object except for truncate().
 
3.  Support for PostgreSQL arrays.  They are converted to/from Python lists on 
input/output.
4.  PostgreSQL notices are made available via the notices attribute of a 
connection object.  The    notices attribute is implemented as a list of notices such that 
connection.notices.pop() will    return the first notice recieved from the back-end.  Coalling pop() will 
return the next    notice received, and so on.
5.  Traps attempts to drop tables/indices, and unlinking large objects within 
a transasction.    These actions are dangerous, since they can not be successfully rolled 
back with the current    version of PostgreSQL.  If the transactions were aborted or rolled back, 
the database will be    left in a state where accessing the table, index, or large object that 
was dropped will result    in an error because PostgreSQL can not access the file associated with 
the table/index/LO.

I have been using the interface for about a week now without problems., but 
have only been using it on UnixWare 7.0.   If you are interested in using the 
interface, I can send it to you and provide some assistance in porting it to 
your environement (if needed).

> Second problem:
> 
> >>> lo = db.locreate(pg.INV_WRITE)
> >>> lo
> Closed large object, oid 18863
> >>> lo.open(pg.INV_WRITE)
> Traceback (innermost last):
>   File "<stdin>", line 1, in ?
> IOError: can't open large object.
> >>> 

Large Objects can only be opened and used in a transaction.
-- 
____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/  |LLIE  | (313) 582-1540    | 



Re: PyGreSQL and transactions

From
Jason Earl
Date:
"Nagy Laszlo Zsolt , KLTE TTK pm1"
<nagylzs@dragon.klte.hu> writes:

>  Hi all.
>
> I'm writting a text-based server program with
PyGreSQL.
> My first problem follows:
>
> >>> import pg
> >>> db =
pg.DB('gandalf','localhost',5432,None,None,'gandalf','')
> >>> db.query('BEGIN')
> >>> db.query("INSERT INTO tbl(s) VALUES('value1')")
> NOTICE:  current transaction is aborted, queries
ignored until end of transaction block
> >>> db.query('commit')
> >>>
>
> The notice was sent to stderr, it seems that I have
no way to catch
> it.  There is getnotify(), but there is no
getnotice(). I must catch
> it, because my server must know if the operation was
successful. (It
> is in a multi-tier application's middle, and sends
back a message
> about the operation.)

Using both the older 2.4 version of PyGreSQL and the
newer 3.1 version
that I am using you can catch these errors with a
try/except block:

db.query('BEGIN')
try:   db.query("INSERT INTO tbl(s) VALUES('value1')")
except (pg.error,), detail:   print "The transaction has been aborted\n %s" %
(detail,)   db.query('ABORT') #not necessary

> Second problem:
>
> >>> lo = db.locreate(pg.INV_WRITE)
> >>> lo
> Closed large object, oid 18863
> >>> lo.open(pg.INV_WRITE)
> Traceback (innermost last):
>   File "<stdin>", line 1, in ?
> IOError: can't open large object.
> >>>
>
> I have compiled the PyGreSQL module without
-DNO_LARGE.

You need to encapsulate your large object access in a
transaction try
starting with a:

db.query('BEGIN')

and ending it with:

db.query('COMMIT')

> Thank in advance:
>
>   Laszlo Nagy
>   nagylzs@delfin.klte.hu

I hope this helps.

Jason

--
The single most useful thing about the Internet is
that it facilitates
using Linux. To use Linux, you need so much goddamn
technical
information that if you don't have a really good
source of technical
support, you're just screwed. -- Neal Stephenson



__________________________________________________
Do You Yahoo!?
Get Yahoo! Mail � Free email you can access from anywhere!
http://mail.yahoo.com/


Re: PyGreSQL and transactions

From
"Nagy Laszlo Zsolt , KLTE TTK pm1"
Date:
> > I'm writting a text-based server program with PyGreSQL.
> > My first problem follows:
...

> I have a Python to PostgreSQL interface program that I am preparing to release.

...

> 
> I have been using the interface for about a week now without problems., but 
> have only been using it on UnixWare 7.0.   If you are interested in using the 
> interface, I can send it to you and provide some assistance in porting it to 
> your environement (if needed).
> 

Yes, I would be very pleased. :-)
 Laci 1.0