Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

From: <kaiq(at)realtyideas(dot)com>
To: Karl DeBisschop <kdebisschop(at)range(dot)infoplease(dot)com>
Cc: lylyeoh(at)mecomb(dot)com, pgsql-general(at)postgreSQL(dot)org
Subject: Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Date: 2000-02-25 23:40:09
Message-ID: Pine.LNX.4.10.10002251726100.20593-100000@picasso.realtyideas.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers

On Fri, 25 Feb 2000, Karl DeBisschop wrote:

> 
> >   From: <kaiq(at)realtyideas(dot)com>
> >   On Fri, 25 Feb 2000, Karl DeBisschop wrote:
> >
> >   > 
> >   > >>To summarize, I stated that the following does not work with
> >   > >>postgresql:
> >   > >>
> >   > >>> $dbh->{AutoCommit} = 0;
> >   > >>> $dbh->do("CREATE TABLE tmp (a int unique,b int)");
> >   > >>>         $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)");
> >   > >>>         if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
> >   > >>> $dbh->commit;
> >   > >>> $dbh->disconnect;
> >   > >>
> >   > 
> >   > The usefulness of the idion is that in a mutli-user environment, this
> >   > is a basic way to update data that may or may not already have a key
> >   > in the table.  You can't do a "SELECT COUNT" because in the time
> >   > between when you SELECT and INSERT (assuming the key is not already
> >   > there) someone may have done a separate insert.  The only other way I
> >   > know to do this is to lock the entire table against INSERTs which has
> >   > obvious performance effects.
> 
> >   sounds right, but ;-) why you use the transaction in the first place? 
> 
> Rememeber that this is just an example to illustrate what sort of
> behaviour one user would find useful in tranasctions, so it is a
> little simplistic.  Not overly simplistic, though, I think.
> 
> I'd want a transaction because I'm doing a bulk insert into this live
> database - say syncing in a bunch of data from a slave server while
> the master is still running.  If one (or more) insert(s) fail, I want
> to revert back to the starting pint so I can fix the cause of the
> failed insert and try again with the database in a known state.
> (there may, for instance, be relationships beteewn the b field such
> that if only part of the bulk insert suceeds, the database is rendered
> corrupt).
> 
thanks. I'm on your side now ;-)  -- it is a useful senario. 
the question are: 1) can nested transaction be typically interpreted 
to handle this situation? If is is, then, it should be handled by that
"advanced feature", not plain transaction ;
 2) on the other hand, can sql92's (plain) transaction be interpreted 
in the way that above behavior is legitimate?


In response to

Responses

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2000-02-26 01:02:22
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Previous:From: Karl DeBisschopDate: 2000-02-25 22:24:57
Subject: Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

pgsql-general by date

Next:From: Ross J. ReedstromDate: 2000-02-26 00:02:20
Subject: Re: timestamp ?(RE: [GENERAL] scheduling table design)
Previous:From: BarnesDate: 2000-02-25 23:25:56
Subject: RE: [GENERAL] scheduling table design

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group