Thread: Online system & transactions
Hello ppl,
Imagine some online system, based on PgSql. There are 'many' parts which
depends on each other. I will give small example, but instead of simple
table imagine changing tenths various tables (editing). So, 'change table A'
can be work for hours.
There are tables tableA and tableB. Changes are made by thin client. If
you change tableA, you have to also change tableB to 'finish' operation.
Between starting changing tableA until tableB is finished, data are 'not
valid'. Record may be changes, deleted, inserted, whatever.
Is there any simple way, how to create 'transactions' over database
connections ? These relations are really complex in real live and doesn't
fit table foundaries, so, it is basically not realistic to make it on
application layer and need to be done somehow on DB layer....
The best solution should be possibility to 'begin' transaction, 'store'
somehow on server, and next time, instead of begin new transaction return to
stored one. Transactions solves all these problems, but they don't work
across various/multiple connections....
Any ideas/references please ?
Thank you,
Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net
--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.
ray@unreal64.net ("Lada 'Ray' Lostak") writes:
> Imagine some online system, based on PgSql. There are 'many' parts which
> depends on each other. I will give small example, but instead of simple
> table imagine changing tenths various tables (editing). So, 'change table A'
> can be work for hours.
>
> There are tables tableA and tableB. Changes are made by thin client. If
> you change tableA, you have to also change tableB to 'finish' operation.
> Between starting changing tableA until tableB is finished, data are 'not
> valid'. Record may be changes, deleted, inserted, whatever.
>
> Is there any simple way, how to create 'transactions' over database
> connections ? These relations are really complex in real live and doesn't
> fit table foundaries, so, it is basically not realistic to make it on
> application layer and need to be done somehow on DB layer....
>
> The best solution should be possibility to 'begin' transaction, 'store'
> somehow on server, and next time, instead of begin new transaction return to
> stored one. Transactions solves all these problems, but they don't work
> across various/multiple connections....
>
> Any ideas/references please ?
You probably should look at the documentation for this.
<http://www.postgresql.org/docs/current/interactive/sql-begin.html>
PostgreSQL supports _exactly_ this notion, in exactly the way you
describe.
If you need to have multiple transactions active at once, you will
just need to establish multiple connections.
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.
Hello, > You probably should look at the documentation for this. > > <http://www.postgresql.org/docs/current/interactive/sql-begin.html> > > PostgreSQL supports _exactly_ this notion, in exactly the way you > describe. But this is normal 'transaction' - and I didn't find way, how can I 'store' transaction to disc storage and 'reopen' later. So, begin/commin works within ONE connection and doesn't help in my general problem. Or is there some way to do that with normal transactions ? My problem is that related data changes are done in 'longer' time - e.g. hour or so (it take hour to change all datas together) and within various database connections. Thx Best regards, Lada 'Ray' Lostak Unreal64 Develop group http://www.orcave.com http://www.unreal64.net -------------------------------------------------------------------------- In the 1960s you needed the power of two C64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI. ----- Original Message ----- From: "Chris Browne" <cbbrowne@acm.org> To: <pgsql-general@postgresql.org> Sent: Monday, October 18, 2004 7:22 PM Subject: Re: [GENERAL] Online system & transactions > ray@unreal64.net ("Lada 'Ray' Lostak") writes: > > Imagine some online system, based on PgSql. There are 'many' parts which > > depends on each other. I will give small example, but instead of simple > > table imagine changing tenths various tables (editing). So, 'change table A' > > can be work for hours. > > > > There are tables tableA and tableB. Changes are made by thin client. If > > you change tableA, you have to also change tableB to 'finish' operation. > > Between starting changing tableA until tableB is finished, data are 'not > > valid'. Record may be changes, deleted, inserted, whatever. > > > > Is there any simple way, how to create 'transactions' over database > > connections ? These relations are really complex in real live and doesn't > > fit table foundaries, so, it is basically not realistic to make it on > > application layer and need to be done somehow on DB layer.... > > > > The best solution should be possibility to 'begin' transaction, 'store' > > somehow on server, and next time, instead of begin new transaction return to > > stored one. Transactions solves all these problems, but they don't work > > across various/multiple connections.... > > > > Any ideas/references please ? > > You probably should look at the documentation for this. > > <http://www.postgresql.org/docs/current/interactive/sql-begin.html> > > PostgreSQL supports _exactly_ this notion, in exactly the way you > describe. > > If you need to have multiple transactions active at once, you will > just need to establish multiple connections. > -- > let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; > http://www.ntlug.org/~cbbrowne/linuxxian.html > A VAX is virtually a computer, but not quite. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
On Tue, Oct 19, 2004 at 01:29:41PM +0200, Lada 'Ray' Lostak wrote:
> But this is normal 'transaction' - and I didn't find way, how can I 'store'
> transaction to disc storage and 'reopen' later.
That you can't do. The usual recipe for this is something like
optimistic locking -- for instance, set an int8 column to
nextval() of some sequence, and then later, when you're ready to
commit, check to make sure the value hasn't changed and do all your
work in an SQL transaction. If the value has changed anywhere, you
know you have to throw away the work you did (or present the
intermediate changes to the user, or whatever).
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland