"Transaction over connections" - Mailing list pgsql-general

Hi !

    We are moving to PgSql application, with 'typical' style of work. Thin
client, huge database. From database is (also) generated whole website. I
will use it for explain my current todo. Right now, I am before solving
following problem:

    Someone starts (let's say) adding 'new product'. It mean, he have to add
records to many tables, gfxer have to create proper images and some XML/HTML
code, ppl translating to other languages have to translate, etc.... Before
the new product is finished and later accepted, user "www" (the one
generating public web) have to 'ignore' all these new records (ofcourse,
many foreign keys, stored procedures are used). When new products will be
tested (on web :) and everything fixed, all records in all tables related to
this product should become visible to all. Basically, something like
"database transaction" which can be re-used accross various DB connections
and 'commit' at some point. I saw some ppl solving this (typical) problem
with local copy of DB and 'udpate', but I don't like that... It is possible
to do 'export' at local copy and 'import' on server. But....

I am asking for your thoughts how to do this with PgSql - the best for
performance. I am sure, many ppl had similar thing to do, but I didn't find
any experimence results. If there are some articles around, I would be happy
for links.

So, I think we need to add 'system' colum to EVERY table in the system (for
performance reasons) - something like

    transaction_id

(or something like that, details not important, it is about general 'how
to')

One of possible ways (I don't think the best) is to improve all SQL's, to
reflect these columns. Something, what will do simple test - like
"transaction_id=0 OR transaction_id=$attached$"...

So, my direct question: can I invoke automatically some function in PgSql to
make condition to EVERY sql statement ? Or what way will be the best for
performace ? Does PgSql allready include some support for this thing ? I
have done something for Oracle, but it will not work at PgSql world.I am
novice to PgSql at all.

The whole process need to be 'transparent' to all part of system, except
parts, where these 'transactions' will be managed - like "create",
"roolback" and "commit". My imagination is something like:

    Admin will create global transaction - e.g. "New product"

Users having proper access rigth can 'connect' to this transaction and
EVERYTHING what they will do, will be visible only 'inside' this
transaction. All users, NOT connected will not see any records - it means,
all SQL will not returns related rows. Next required operation is to connect
to MORE transactions - READ ONLY access (write is not possible ofcourse
then)... One day, transaction and all records can be rollback/commin -
together by 'id', or move to another transaction (glued). Thats' all :)

Thank you for opinions/ideas/hints,
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.