Hi!
I wanna ask something. I came from IB/FB world.
In this world I was everytime in transaction, because of reads are also working under transactions.
In the FB world the transactions without any writes/updates are not locking the database, so another clients can makes a transactions on any records.
And also can add new fields to the tables.
Now I used Pylons webserver (Python) with PyGRESQL, and DBUtils for cached database connections/cursors.
Today I saw a locking situation in many times.
0.) I started Pylons web server, and in the browser I request for a simple view (without modify anything).
1.) I opened PGAdmin.
2.) I move the focus to the table "X".
3.) I opened an SQL editor and try to make two column adds:
alter table X add test_a date;
alter table X add test_b date;
4.) After the the PGAdmin's Query Execution (F5) nothing happened. I see this menu is disabled, and PGAdmin is locked for new operations.
5.) When I simply close "Pylons web server", the PGAdmin quickly finished with this table restructure without problems...
The problem can repeatable in any times.
This is very hateable thing, because in this view I don't modify anything, I use only "selects", nothing other things.
And I wanna solve this problem, because if I must do some modifications in the online database (for example: add a new field), I don't want to shut down the webserver with all online clients...
I simplified this "bug" to see this without web server, dbutils, and other layers.
I wrote this python code:
import os, sys, pgdb
fmtstring = '%s:%s:%s:%s'
fmtstring = fmtstring % ('127.0.0.1',
'anydb',
'anyuser', 'what?')
db = pgdb.connect (fmtstring)
print "ok"
cur = db.cursor()
cur.execute('select * from testtable')
rek = cur.fetchone()
cur.close()
while 1:
pass
db.close()
After start this I tried to add a new field to the testtable from PGAdmin's Query GUI:
alter table testtable add test_001 date;
With the cur.execute("select * from testtable") I got lock "error", the PGAdmin query is running and running and running... :-(
Without this cur.execute the alter table finished without locks.
When I put a "db.rollback()" before while the lock vanished...
So pg is hold all records I fetched? They are not useable freely in a simple, readonly select?
Please help me SOS if possible, I must find a way to get out from these locks!
And please help me: how to check that I'm in transaction or not?
Thanks for your help:
dd