Thread: PyGreSQL and transactions
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
"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 |
"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/
> > 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