"Transaction over connections"

[Lada 'Ray' Lostak]

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.



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

http://www.postgresql.org/docs/faqs/FAQ.html

[Karsten Hilbert]

Re: "Transacti on over connections&quo t;

> So, my direct question: can I invoke automatically some function in PgSql to[color=blue]
> make condition to EVERY sql statement ?[/color]
You can add an attribute "work-in-progress" to your tables. Then
set up views that filter out rows with work-in-progress=true.
Your production client then needs to be modified to look at
those views instead of the tables.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Lada 'Ray' Lostak]

Re: "Transacti on over connections&quo t;

> You can add an attribute "work-in-progress" to your tables. Then[color=blue]
> set up views that filter out rows with work-in-progress=true.
> Your production client then needs to be modified to look at
> those views instead of the tables.[/color]
I also though about this, but what about performance ? I have hundreds
tables. Anyone have experimence withs TONS of views ?

Is it possible to setup 'view' on whole database ? Or a 'part' of DB ? Same
expression for all/setof tables.

It is not problem to modify SQL statement 'table' name (it is in reality
easier than modify expression :). So, client doesn't need to be changed -
just 'SQL' exec handler... So, base condition is OK.

Thanks,
R.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Shridhar Daithankar]

Re: "Transacti on over connections&quo t;

Lada 'Ray' Lostak wrote:[color=blue][color=green]
>>You can add an attribute "work-in-progress" to your tables. Then
>>set up views that filter out rows with work-in-progress=true.
>>Your production client then needs to be modified to look at
>>those views instead of the tables.[/color]
>
> I also though about this, but what about performance ? I have hundreds
> tables. Anyone have experimence withs TONS of views ?
>
> Is it possible to setup 'view' on whole database ? Or a 'part' of DB ? Same
> expression for all/setof tables.[/color]

how about all non-approved product goes to a in development schema and all
approved products are moved to production schema when approved.

Sure it means lot more than mainening single column but development schema would
be as slim as productio and further more it is totally invisible from client side.

Just set the schema search pats correctly.

HTH..

Shridhar


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match