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