Re: Python client + select = locked resources???

Hi!

2009.06.29. 18:26 keltezéssel, Craig Ringer írta:

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

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

InterBase / FireBird ? 

Yes, sorry for short descriptions.

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. 

Aha... So if I'm getting out from normal transactions I moved into implicit autocommit way.
Hmmm... Then that is meaning that every statement is in new transaction context which can makes inconsistency in the views...
For example (pseudo):
select item_head, count(items)
select items

Possible: count(items) <> len(fetchall(items)) if someone committed a new record into "items" table...

Am I thinking good?

So I need:

begin;
select item_head, count(items)
select items
rollback;

to get full consistent data-sets?

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 

The locks meaning in my "dictionary" that DB will prevent some functions on the table to avoid the consistency and other errors.

For example DBISAM is working in that way. We can make record modifications, etc, but for "altering table" I need to shut down all of the clients!
Because DBISAM put a "file lock" to this table while it altered.

But in FireBird is allowed to add a new field to table when it is used. Because FB is makes a new record version, and this version used for the next queries.
And I can add a new field without shutting down all of the clients.

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

Ok.

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. 

Thanks. So this was I ask from this mailing list before I started to use PGDB.

But they are said to me that PGDB is working in same mode like FB.

And now I know from your mail that isn't true - it will be easier if I shut down the webserver, make the modifications on PGDB and after that I restart them all.

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. 

Hmmm... Thanks for your info!!!

        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. 

Yes. If I can make a rollback on it, all of resources released.
Now I search for a way to "force dbutils to it must make a rollback before it re-move the connection into it's pool", or a way to I can do this easily from the webserver...

Thanks for your help and please suggest me a transaction mode to consistent views (see above).

    dd