Thread

1. "Transaction over connections"

   Lada 'Ray' Lostak <ray@unreal64.net> — 2003-11-20T07:52:15Z

   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.
   
   
   
   

2. Re: "Transaction over connections"

   Karsten Hilbert <karsten.hilbert@gmx.net> — 2003-11-20T08:19:30Z

   > So, my direct question: can I invoke automatically some function in PgSql to
   > make condition to EVERY sql statement ?
   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
   
   

3. Re: "Transaction over connections"

   Lada 'Ray' Lostak <ray@unreal64.net> — 2003-11-20T08:47:50Z

   > 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.
   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.
   
   
   

4. Re: "Transaction over connections"

   Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-11-20T08:54:31Z

   On Thursday 20 November 2003 13:22, Lada 'Ray' Lostak wrote:
   > 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.
   
   Instead of transaction id use a boolean column which is isProduction and 
   default it to false.
   
   When you want to make it visible, just update relevant table in a single 
   transaction and expose it thr. a view.
   
   I think this is already suggested.
   
    Shridhar
   
   
   

5. Re: "Transaction over connections"

   Shridhar Daithankar <shridhar_daithankar@myrealbox.com> — 2003-11-20T09:23:15Z

   Lada 'Ray' Lostak wrote:
   >>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.
   > 
   > 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.
   
   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