SEO

Hi,

Is it OK to use PREPARE TRANSACTION and COMMIT PREPARED in order to have
transactions that last longer than just a single web request?

Previously it was usually a bad idea to keep database connections alive
just to keep a transaction pending.

Now I'm thinking that we could keep transactions around for as long as the
relevant web _session_ is valid. That will be quite nice for many things.

Would it be easier to support X "prepared transactions" than X database
connections for increasing values of X?

Thanks,

Link.


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Is it OK to use PREPARE TRANSACTION and COMMIT PREPARED in order to have
> transactions that last longer than just a single web request?

> Previously it was usually a bad idea to keep database connections alive
> just to keep a transaction pending.

A prepared transaction eats just about the same resources (other than an
active connection) as a live one. In particular it still holds its
locks, which makes leaving it around for a long time just as evil as
simply sitting on it in an un-prepared state.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

At 04:11 PM 11/10/2005 -0500, Tom Lane wrote:

>Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> > Is it OK to use PREPARE TRANSACTION and COMMIT PREPARED in order to have
> > transactions that last longer than just a single web request?
>
> > Previously it was usually a bad idea to keep database connections alive
> > just to keep a transaction pending.
>
>A prepared transaction eats just about the same resources (other than an
>active connection) as a live one. In particular it still holds its
>locks, which makes leaving it around for a long time just as evil as
>simply sitting on it in an un-prepared state.

Assuming the transactions don't explicitly do any locks (lock table, select
for update - just selects, inserts and normal updates), would it be
possible to have say 10000 pending prepared transactions? What would the
main limiters be?

It will be very nice if that sort of thing is viable. Previously if you
want to do transactional stuff with webapps, you'd have to simulate it at
the application layer (or leave db connections open[1]). Doing transaction
stuff at the application level seems rather MySQL-ish (OK MySQL 3-ish ).
Having to have tables with transactionid columns, transaction table etc.

Leaving transactions pending will affect vacuuming, but perhaps we can just
put the web transaction stuff in a separate database, so it doesn't affect
vacuuming of other normal transactions. If you do such stuff at the
application layer, you will still have to keep those rows around anyway.

Last but not least, is this a silly thing to do? Are people already doing
such stuff on other databases, or they do such things in other ways for
good reasons (which are?)?

Best regards,
Link.

[1] Which I'd consider viable only in a controlled environment- internal
web app for internal users.

Hmm. I wonder if it would be possible to simulate 20K concurrent database
connections, using many db proxies (e.g. pgpool), and prepared transactions
(just prepare all transactions, but only process a manageable number of
transactions at a time).




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, Nov 11, 2005 at 05:45:28PM +0800, Lincoln Yeoh wrote:
> At 04:11 PM 11/10/2005 -0500, Tom Lane wrote:
> >A prepared transaction eats just about the same resources (other than an
> >active connection) as a live one. In particular it still holds its
> >locks, which makes leaving it around for a long time just as evil as
> >simply sitting on it in an un-prepared state.
>
> Assuming the transactions don't explicitly do any locks (lock table, select
> for update - just selects, inserts and normal updates), would it be
> possible to have say 10000 pending prepared transactions? What would the
> main limiters be?

Every transaction takes locks, on every table it accesses. Shared lock,
but locks anyway. UPDATEs take stronger locks, so any UPDATE may cause
other queries to wait until you COMMIT or ABORT.

See also:
: The state of each prepared transaction is kept in a so called "2PC
: state file" in the pg_twophase directory. There is one state file for
: each prepared transaction, and the filename is the xid of the
: transaction.
:
: The state file is created and populated when the transaction is
: prepared, and it's used in commit/rollback to finish the transaction
: on behalf of the original backend. It's also used on database
: recovery to recover any in-memory state the transaction must have,
: like locks held.
http://users.tkk.fi/~hlinnaka/pgsql/

> It will be very nice if that sort of thing is viable. Previously if you
> want to do transactional stuff with webapps, you'd have to simulate it at
> the application layer (or leave db connections open[1]). Doing transaction
> stuff at the application level seems rather MySQL-ish (OK MySQL 3-ish ).
> Having to have tables with transactionid columns, transaction table etc.

But once you've prepared a transaction, you can't reopen it, all you
can do is either commit it or abort it. I don't see how prepared
transaction relate to webapps at all.

See also the docs:
http://www.postgresql.org/docs/8.1/s...ansaction.html

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDdJr2IB7bNG8LQkwRAqyoAJ42+6Z/BjqQFJSJeFNFAv2pR8KS8wCgkld4
whpSWNRrJgv/t7qubJfzz7M=
=XsLl
-----END PGP SIGNATURE-----

On Fri, Nov 11, 2005 at 02:22:05PM +0100, Martijn van Oosterhout wrote:
> Every transaction takes locks, on every table it accesses. Shared lock,
> but locks anyway. UPDATEs take stronger locks, so any UPDATE may cause
> other queries to wait until you COMMIT or ABORT.

Note also, you don't want to use prepare transactions until you know
exactly what you're doing. Take for example (in a single session):

test=# begin;
BEGIN
test=# update test set value=1 where value=1;
UPDATE 1
test=# prepare transaction 'test';
PREPARE TRANSACTION
test=# begin;
BEGIN
test=# update test set value=1 where value=1;

and you've deadlocked yourself. Until you create another connection and
commit or rollback the prepared transaction, this query will never end.
This is at the lowest isolation level. You do not want to keep
transactions open longer than absolutly necessary.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDdKA1IB7bNG8LQkwRAoyeAJ9Wg4C/LwlinggSSvFMRHME73/JNwCfREhR
Ktn2SHGO7myavj8QbideTj0=
=4J/d
-----END PGP SIGNATURE-----

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Fri, Nov 11, 2005 at 05:45:28PM +0800, Lincoln Yeoh wrote:
>> Assuming the transactions don't explicitly do any locks ...

> Every transaction takes locks, on every table it accesses. Shared lock,
> but locks anyway. UPDATEs take stronger locks, so any UPDATE may cause
> other queries to wait until you COMMIT or ABORT.

Also, the mere existence of an old open transaction restricts VACUUM's
ability to reclaim dead rows.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

At 02:22 PM 11/11/2005 +0100, Martijn van Oosterhout wrote:

>But once you've prepared a transaction, you can't reopen it, all you
>can do is either commit it or abort it. I don't see how prepared
>transaction relate to webapps at all.
>
>See also the docs:
>http://www.postgresql.org/docs/8.1/s...ansaction.html

Oh. I thought one could reopen transactions. Why can't we do that?

Would it be reasonably possible to add a postgresql feature to save
transactions to disk, disconnect from the database, reconnect to the
database, reopen and continue a desired transaction? e.g. CONTINUE
TRANSACTION transaction_id

I think I asked for such a feature in postgresql years ago, but didn't get
a positive reply, so I figured it was not possible, but with the recent
announcement of the "prepare transaction" feature, I'm getting a bit more
hopeful .

Is there a reason why transactions should be so tightly linked to database
connections? Being able to decoupling transactions from database
connections could make a lot of tasks easier.

Vacuum not being able to reclaim dead rows isn't a big issue. Unless I'm
mistaken, doing such a thing at the application level will by necessity
result in a similar situation. It's all a necessary cost of supporting that
many concurrent outstanding _transactions_ (in contrast with the cost of
supporting "real" concurrent DB connections).

Deadlocking is an issue of course. But are there any differences in the
locking situation? Wouldn't it be the same as having a normal transaction
that takes a long time to complete? We already have users with transactions
that are open for days at least.

I think it's easier to use NOWAIT than to reimplement MVCC at a
webapplication level .

If a CONTINUE TRANSACTION feature is possible, it could also allow
postgresql systems to "pretend" to support many more concurrent open
"database connections" .

Regards,

Link.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Hi,

Can we have a reconnect and "reopen prepared/saved transactions" feature?

Please?

I'm sure there'll be uses for it. e.g. the stuff I mentioned.

Maybe we can also use it to help migrate queries to a different node.

At 11:54 AM 11/12/2005 +0800, Lincoln Yeoh wrote:

>At 02:22 PM 11/11/2005 +0100, Martijn van Oosterhout wrote:
>
>>But once you've prepared a transaction, you can't reopen it, all you
>>can do is either commit it or abort it. I don't see how prepared
>>transaction relate to webapps at all.
>>
>>See also the docs:
>>http://www.postgresql.org/docs/8.1/s...ansaction.html
>
>Oh. I thought one could reopen transactions. Why can't we do that?



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, 15 Nov 2005, Lincoln Yeoh wrote:

> Can we have a reconnect and "reopen prepared/saved transactions" feature?
>
> Please?
>

Note that this (transaction suspend/resume) is also required for a full
implementation of XA. Our current 2PC only supports the basics. There's
a bunch of other complicated features, like transaction interleaving[1]
and multiple threads of control participating in the same backend
transaction[2] that we currently don't support either. Now some of these
may be worked around and faked on the driver side, but it won't be able to
do these well. For example you could implement suspend/resume by simply
holding the backend connection open or you could implement interleaved
transactions by opening multiple connections, but both have a serious cost
in the number of open connections. It would be better to implement this
functionality in the backend, but I'm not sure how important these
situations are in the real world. Some on the jdbc list have shown ways
to configure transaction managers to avoid using these exotic features.

Also I think that trying to use 2PC without a real transaction manager is
just asking for trouble. Normal XA usage is two serverside resources held
open the time it takes to service a single request, not wait for user
input. A random webapp leaving suspended or prepared transactions around
is going to lock things up in a hurry.

Kris Jurka

[1] http://archives.postgresql.org/pgsql...6/msg00165.php
[2] http://archives.postgresql.org/pgsql...6/msg00171.php

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Can we have a reconnect and "reopen prepared/saved transactions" feature?
> Please?

No. A prepared transaction has already run its end-of-transaction
operations, eg at-commit triggers. Reopening it would be a fundamental
semantics violation.

That said, it seems to me that the prepared-xacts infrastructure could
possibly support a separate "suspend transaction" and "resume
transaction" facility, if anyone wants to do the legwork to make it
happen. What this would actually be useful for is a fair question
though --- what's it do that you don't have now?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend