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

On Wed, 2009-07-01 at 09:23 +0200, durumdara wrote:

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

Correct.

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

You are. However, this is ALSO true in a transaction by default.
PostgreSQL defaults to the READ COMMITTED isolation level, which means
that statements may see data that was committed by another transaction
after the start of the transaction in which the statement is run, but
before the start of the statement.

If you want to avoid that, you may use the SERIALIZABLE isolation level.
That has its own complications and costs, though, including the need to
be prepared to retry any transaction after a serialization failure.

(Of course, your app should be prepared to retry a transaction ANYWAY
unless you're incredibly sure your code is perfectly free from lock
conflicts etc).

See:

http://www.postgresql.org/docs/8.3/static/transaction-iso.html

Once again, I VERY strongly recommend reading the whole PostgreSQL
manual. It'll teach you a lot about SQL and relational databases in
general as well as PostgreSQL in particular, and is very well written.

> So I need:
>
>         begin;
>         select item_head, count(items)
>         select items
>         rollback;
>
> to get full consistent data-sets?

That won't protect you from:

TRANSACTION 1                     TRANSACTION 2
begin;
                                  begin;
select item_head, count(items)
                                  INSERT INTO items(...)
                                  commit;
select items

"select items" in transaction 1 can see the changes made by transaction
2 after transaction 2 commits in the default READ COMMITTED isolation
level.

See the documentation I linked to above.

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

You can do that, but YOU SHOULD NOT HAVE TO.

If you have to, your web server or the code running in your web server
is buggy and is holding transactions open longer than it needs them.
Your web server and web app should be able to remain running and retain
connections to the database (so long as they're not idle in a
transaction that's done work). There will be a brief pause while the
ALTER TABLE executes, but that's all.

Make sure there are no connections that're idle in transaction:

   select * from pg_stat_activity

If there are, you may need to alter settings in your web server,
connection pooler, or web app.

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

Yes, that's what you need to do.

--
Craig Ringer