By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

Online system & transactions

P: n/a
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.

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

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ra*@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.
Nov 23 '05 #2

P: n/a
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" <cb******@acm.org>
To: <pg***********@postgresql.org>
Sent: Monday, October 18, 2004 7:22 PM
Subject: Re: [GENERAL] Online system & transactions

ra*@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3

P: n/a
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 | aj*@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.