Re: Python client + select = locked resources??? - Mailing list pgsql-general

On Mon, 2009-06-29 at 13:36 +0200, durumdara wrote:

> I wanna ask something. I came from IB/FB world.

InterBase / FireBird ?

> In this world I was everytime in transaction, because of reads are
> also working under transactions.

Just like PostgreSQL. You can't run a query without a transaction in
PostgreSQL; if you don't issue an explicit BEGIN, it'll do an implicit
BEGIN/COMMIT around the statement.

> 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.

PostgreSQL doesn't "lock the database" for reads or writes. Transactions
do take out various levels of lock on tables when you do things with
those tables. See the locking documentation:

http://www.postgresql.org/docs/8.3/static/explicit-locking.html

Additionally, PostgreSQL can take out share and update locks against
rows, as the documentation mentions.

> 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;

ALTER TABLE does take out an exclusive lock on the table. See the
manual:

http://www.postgresql.org/docs/8.3/static/explicit-locking.html

If there's other work in progress, it can't get the exclusive lock until
that work completes.

> 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...

You should not have to. If you can't get a lock on the table, then most
likely the web app is holding transactions open instead of opening them,
doing work, and promptly committing / rolling back.

Try connecting to the database with psql and running
  "select * from pg_stat_activity"
while the web app is running. You should see only "IDLE" or working
connections, never idle in transaction. If you have anything idle in a
transaction for more than a few moments you WILL have problems, because
if those transactions have SELECTed from the table you're trying to
alter they'll hold share locks that will prevent ALTER TABLE from
grabbing an exclusive lock on the table.


>         cur = db.cursor()
>         cur.execute('select * from testtable')
>         rek = cur.fetchone()
>         cur.close()
>         while 1:
>             pass

Here you're holding a transaction open and idle. Wrong move. Close the
transaction (dispose the cursor) and then open a new transaction to do
more work.

--
Craig Ringer