Re: [GENERAL] Re: [HACKERS] TRANSACTIONS - Mailing list pgsql-general

kaiq@realtyideas.com wrote:
>
> On Fri, 25 Feb 2000, Karl DeBisschop wrote:
>
> >
> > >   From: <kaiq@realtyideas.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 ;

I guess like this (got rid of AutoCommit, because that looks funny
nested):

$dbh->RaiseError = 1;
$dbh->StartTransaction;
eval {
  $dbh->do("CREATE TABLE tmp (a int unique,b int)");
  while (blahblahblah) {
    $dbh->StartTransaction;
    eval {
      $dbh->do("INSERT INTO tmp VALUES ($1,$2)");
    };
    if ($@) {
    $dbh->Rollback;
          {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
    } else {
    $dbh->Commit;
    }
  }
}
if ($@) {
    $dbh->rollback;
} else {
    $dbh->commit;
}
$dbh->disconnect;

I.e., try the INSERT within the inner transaction; if it fails, roll it
back and do the UPDATE; if that fails, blow out the whole outer
transaction.

You could do the whole thing checking a return value as in the original
example, but the eval and RaiseError are canonical, according the the
docs.

>  2) on the other hand, can sql92's (plain) transaction be interpreted
> in the way that above behavior is legitimate?
>
Well, I'm not sure of the necessity of nested transactions in the case
of continuing a transaction after a single-row insert has failed, but
that's implementation details I'm not familiar with...  i.e., I'm not
having to code the danged thing!