[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] Python client + select = locked resources???
From: durumdara <durumdara () gmail ! com>
Date: 2009-06-30 8:21:26
Message-ID: 4A49CB06.8080701 () gmail ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-2"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi!<br>
<br>
2009.06.29. 18:26 keltezéssel, Craig Ringer írta:
<blockquote cite="mid:1246292784.11346.38.camel@ayaki" type="cite">
<pre wrap="">On Mon, 2009-06-29 at 13:36 +0200, durumdara wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I wanna ask something. I came from IB/FB world.
</pre>
</blockquote>
<pre wrap=""><!---->
InterBase / FireBird ?
</pre>
</blockquote>
Yes, sorry for short descriptions.<br>
<br>
<blockquote cite="mid:1246292784.11346.38.camel@ayaki" type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">In this world I was everytime in transaction, because of reads are
also working under transactions.
</pre>
</blockquote>
<pre wrap=""><!---->
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.
</pre>
</blockquote>
Aha... So if I'm getting out from normal transactions I moved into
implicit autocommit way.<br>
Hmmm... Then that is meaning that every statement is in new transaction
context which can makes inconsistency in the views...<br>
For example (pseudo):<br>
select item_head, count(items)<br>
select items<br>
<br>
Possible: count(items) <> len(fetchall(items)) if someone
committed a new record into "items" table...<br>
<br>
Am I thinking good?<br>
<br>
So I need:<br>
<br>
<blockquote>begin;<br>
select item_head, count(items)<br>
select items<br>
rollback;<br>
</blockquote>
<br>
to get full consistent data-sets?<br>
<blockquote cite="mid:1246292784.11346.38.camel@ayaki" type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<pre wrap=""><!---->
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:
<a class="moz-txt-link-freetext" \
href="http://www.postgresql.org/docs/8.3/static/explicit-locking.html">http://www.postgresql.org/docs/8.3/static/explicit-locking.html</a>
</pre>
</blockquote>
The locks meaning in my "dictionary" that DB will prevent some
functions on the table to avoid the consistency and other errors.<br>
<br>
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! <br>
Because DBISAM put a "file lock" to this table while it altered.<br>
<br>
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.<br>
And I can add a new field without shutting down all of the clients.<br>
<br>
<blockquote cite="mid:1246292784.11346.38.camel@ayaki" type="cite">
<pre wrap="">
Additionally, PostgreSQL can take out share and update locks against
rows, as the documentation mentions.
</pre>
</blockquote>
Ok.<br>
<blockquote cite="mid:1246292784.11346.38.camel@ayaki" type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">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;
</pre>
</blockquote>
<pre wrap=""><!---->
ALTER TABLE does take out an exclusive lock on the table. See the
manual:
<a class="moz-txt-link-freetext" \
href="http://www.postgresql.org/docs/8.3/static/explicit-locking.html">http://www.postgresql.org/docs/8.3/static/explicit-locking.html</a>
If there's other work in progress, it can't get the exclusive lock until
that work completes.
</pre>
</blockquote>
Thanks. So this was I ask from this mailing list before I started to
use PGDB. <br>
<br>
But they are said to me that PGDB is working in same mode like FB.<br>
<br>
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.<br>
<br>
<blockquote cite="mid:1246292784.11346.38.camel@ayaki" type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">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...
</pre>
</blockquote>
<pre wrap=""><!---->
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.
</pre>
</blockquote>
Hmmm... Thanks for your info!!!<br>
<br>
<blockquote cite="mid:1246292784.11346.38.camel@ayaki" type="cite">
<blockquote type="cite">
<pre wrap=""> cur.close()
while 1:
pass
</pre>
</blockquote>
<pre wrap=""><!---->
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.
</pre>
</blockquote>
Yes. If I can make a rollback on it, all of resources released.<br>
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...<br>
<br>
Thanks for your help and please suggest me a transaction mode to
consistent views (see above).<br>
<br>
dd<br>
<br>
<br>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic