Обсуждение: JDBC squirrely transaction behavior??

dumb JDBC/SQL question:

I have just moved some code over from pg 6.5.2 to pg 7.0 and am starting to modify an existing code base
to use transactions.

I need to add some records to masterfile tables in a particular order (due to foreign key dependencies) and
want to use a transaction to allow aborting the whole shebang in the event any of the inserts fails.

Below is a simple sample of code which shows the problem.

---------------------------------------------

create table foos (foo_id serial primary key, foo_name varchar(64) unique);

create table bars(bar_id serial primary key, foo_id int4 references foos, bar_len int4, bar_width int4);

...

connection.setAutoCommit(false);

while (true)
    {
    try
        {
        try
            {
            insert into foos (foo_name) values ('foo test value #1');
            }
        catch (SQLException e)
            {
            system.out.println("attempt to insert duplicate foo ... not a problem.");
            }
        // get the foo_id for this foo;
        rs = select foo_id from foos where foo_name='foo test value #1');
        if (rs.next()
            {
            int foo_id = rs.getInt(1);
            insert into bars (foo_id, bar_len, bar_width) values (foo_id, 25, 99);
            connection.Commit();
            }
        else
            {
            connection.Rollback();
            System.out.println("error: unable to lookup foo_id");
            throw new Exception("internal error...");
            }
        }
    catch (SQLException e)
        {
        System.out.println("bad things a-happenin");
        e.printStackTrace();
        }
    }

This code will run for a number of insertions, then fail because the select of the foo_id
returns no records.  This should be impossible, because either (1) the foo_id was
just added to the foos table, or (2) it had previously been added.  Either way,
there should be a record with a matching namefield.

I then commented out the transaction stuff, and verified that the code ran properly.

My first thought was that statement #2 was unable to see changes effected by
statement #1, but because they are within the scope of the same transaction,
they should be able to....strangely, this works for some records, but not others.

Is the pg or jdbc support for transactions broken??

Finally, the API does not seem to offer support for nested transactions, which
sometime come in handy...  But even the JavaSoft-approved JDBC book
written by the authors of JDBC does a lame job of covering transactions
(and many other things...)

Thanks and best regards,
Mark Dzmura

----------------------------------------------------

On a related note,

Any guesses as to when the postgresql jdbc driver will support the JDBC 2.x extensions
for record insert/update/delete via the special record in a result set ??

Regards,
Mark Dzmura

--
)))) This email routed via a wireless gateway!! ((((

Mark Dzmura <mdz@digital-mission.com> writes:
>         try
>             {
>             insert into foos (foo_name) values ('foo test value #1');
>             }
>         catch (SQLException e)
>             {
>             system.out.println("attempt to insert duplicate foo ... not a problem.");
>             }

Can't do it that way inside a transaction: the insert failure forces
the whole transaction to be aborted.  The fact that you caught the
exception doesn't affect the fact that the server believes the
transaction must now be aborted; it won't process any more statements
until COMMIT or ROLLBACK.

I'd suggest something like
       rs = select foo_id from foos where foo_name='foo test value #1';       if (rs is empty)       {
insertinto foos (foo_name) values ('foo test value #1');            rs = select foo_id from foos where foo_name='foo
testvalue #1';            Assert(rs is not empty);       }
 

This will be a little slower if the common case is that an insert is
needed, but faster if the common case is that no insert is needed.

Another possibility is to automate the sequence with a rule or trigger
on the database side.  For example I think you could define a view
"foos_magic" with a rule such that you can just unconditionally do an
"select from foos_magic where ..." and an insert will be done for you
iff there's no matching record.
        regards, tom lane

On Wed, 31 May 2000, Mark Dzmura wrote:

> dumb JDBC/SQL question:
> 
> I have just moved some code over from pg 6.5.2 to pg 7.0 and am starting to modify an existing code base
> to use transactions.
> 
> I need to add some records to masterfile tables in a particular order (due to foreign key dependencies) and
> want to use a transaction to allow aborting the whole shebang in the event any of the inserts fails.
> 
> Below is a simple sample of code which shows the problem.
> 
> ---------------------------------------------
> 
> create table foos (foo_id serial primary key, foo_name varchar(64) unique);
> 
> create table bars(bar_id serial primary key, foo_id int4 references foos, bar_len int4, bar_width int4);
> 
> ...
> 
> connection.setAutoCommit(false);
> 
> while (true)
>     {
>     try
>         {
>         try
>             {
>             insert into foos (foo_name) values ('foo test value #1');
>             }
>         catch (SQLException e)
>             {
>             system.out.println("attempt to insert duplicate foo ... not a problem.");
>             }
>         // get the foo_id for this foo;
>         rs = select foo_id from foos where foo_name='foo test value #1');
>         if (rs.next()
>             {
>             int foo_id = rs.getInt(1);
>             insert into bars (foo_id, bar_len, bar_width) values (foo_id, 25, 99);
>             connection.Commit();
>             }
>         else
>             {
>             connection.Rollback();
>             System.out.println("error: unable to lookup foo_id");
>             throw new Exception("internal error...");
>             }
>         }
>     catch (SQLException e)
>         {
>         System.out.println("bad things a-happenin");
>         e.printStackTrace();
>         }
>     }
> 

> This code will run for a number of insertions, then fail because the
> select of the foo_id returns no records.  This should be impossible,
> because either (1) the foo_id was just added to the foos table, or (2)
> it had previously been added.  Either way, there should be a record
> with a matching namefield.

I can see a problem here. Your first insert inserts foo_id, and fails if
foo_id already exists. In your code, you ignore this which is fine outside
a Transaction. However, because you are using a transaction, the
transaction is now in a failed state, so everything else done in that
transaction will be ignored.

So, I'd add in the catch clause of that block a connection.rollback()
call. That should then start a new transaction, which will then work for
the rest of the code.

The alternative is to but a connection.commit() after the insert, which
may work in this case, but you have to think about the integrity of your
tables.

> I then commented out the transaction stuff, and verified that the code ran properly.
> 
> My first thought was that statement #2 was unable to see changes effected by
> statement #1, but because they are within the scope of the same transaction,
> they should be able to....strangely, this works for some records, but not others.
> 
> Is the pg or jdbc support for transactions broken??

No, this looks like normal behaviour. Because foo_id already exists, the
first insert fails, and marks that transaction as void. PG will ignore
everything until the transaction ends, rolls back, or autocommit is
enabled.

> Finally, the API does not seem to offer support for nested transactions, which
> sometime come in handy...  But even the JavaSoft-approved JDBC book
> written by the authors of JDBC does a lame job of covering transactions
> (and many other things...)

This is because Postgres doesn't support nested transactions. There's a
lot of JDBC internals that could be implemented easier if we had nested
transactions.

> Any guesses as to when the postgresql jdbc driver will support the JDBC 2.x extensions
> for record insert/update/delete via the special record in a result set ??

Possibly 7.1. It depends on if I get time between now and then. Time may
be tight for the next couple of months, as I may be moving home shortly,
and obviously I'll need to give that some priority ;-)

Peter

--      Peter T Mount peter@retep.org.uk     Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgresJava PDF Generator: http://www.retep.org.uk/pdf

Peter Mount wrote:
> 
....
> 
> > This code will run for a number of insertions, then fail because the
> > select of the foo_id returns no records.  This should be impossible,
> > because either (1) the foo_id was just added to the foos table, or (2)
> > it had previously been added.  Either way, there should be a record
> > with a matching namefield.
> 
> I can see a problem here. Your first insert inserts foo_id, and fails if
> foo_id already exists. In your code, you ignore this which is fine outside
> a Transaction. However, because you are using a transaction, the
> transaction is now in a failed state, so everything else done in that
> transaction will be ignored.

Hmmm, that may cause me a problem in a similar situation - I want to
do an update whenever an insert fails.  Further, for performance reasons
the insertions are done in large transactions (thousands to tens of
thousands of inserts in each transaction).  Does the above comment mean
that this ("update after failed insert") won't work as I'd like because
it's inside a transaction?  Is there a way to force the state of the
transaction back to success after the failed insert, without losing the
insertions that have been made previously in the transaction [or those
that are made subsequently...]?

> The alternative is to but a connection.commit() after the insert, which
> may work in this case, but you have to think about the integrity of your
> tables.

This sounds like it would work in my case - does anyone see why it wouldn't
or have a more appropriate approach?  (Through JDBC, of course.)

Thanks!




--
Steve Wampler-  SOLIS Project, National Solar Observatory
swampler@noao.edu

Today, in a message to postgres-interfaces, Steve Wampler wrote:
> 
> Hmmm, that may cause me a problem in a similar situation - I want to
> do an update whenever an insert fails.

There was a discussion about this in several Postgresql mailing lists a
few weeks ago. Postgresql currently doesn't support statement-level abort
and instead aborts the whole transaction. Apparently, there is a consensus
among the developers that statement-level abort is a good thing to
have. Perhaps we will see it some day...

Even now you can do this trick the other way around: do an update first,
and when the row count is zero insert the row instead. This works because
updating zero rows is not an error in SQL. Not sure whether this helps in
your situation.

Joachim

-- 
work:     joachima@realtimeint.com  (http://www.realtimeint.com)
private:  joachim@kraut.bc.ca       (http://www.kraut.bc.ca)

Tom Lane wrote:
> 
> Mark Dzmura <mdz@digital-mission.com> writes:
> >         try
> >             {
> >             insert into foos (foo_name) values ('foo test value #1');
> >             }
> >         catch (SQLException e)
> >             {
> >             system.out.println("attempt to insert duplicate foo ... not a problem.");
> >             }
> 
> Can't do it that way inside a transaction: the insert failure forces
> the whole transaction to be aborted.  The fact that you caught the
> exception doesn't affect the fact that the server believes the
> transaction must now be aborted; it won't process any more statements
> until COMMIT or ROLLBACK.
> 
> I'd suggest something like
> 
>         rs = select foo_id from foos where foo_name='foo test value #1';
>         if (rs is empty)
>         {
>              insert into foos (foo_name) values ('foo test value #1');
>              rs = select foo_id from foos where foo_name='foo test value #1';
>              Assert(rs is not empty);
>         }
> 
> This will be a little slower if the common case is that an insert is
> needed, but faster if the common case is that no insert is needed.
> 
> Another possibility is to automate the sequence with a rule or trigger
> on the database side.  For example I think you could define a view
> "foos_magic" with a rule such that you can just unconditionally do an
> "select from foos_magic where ..." and an insert will be done for you
> iff there's no matching record.

In my case, the common case is way far and away (>90% of the time) tilted
toward insert (where if insert isn't needed, then an update is...), so a
question:

There seem to be two general approaches here (and in other related postings):
   (a) Try an update and see if it works before trying an insert.  Similar to   the first approach Tom suggests here.
   (b) Attach a trigger to another table or view to catch the insert and   then query the real table to see if it
reallyshould be an insert or   an update.
 

Does anyone know which approach is likely to be faster?  My guess is that
(b) has an advantage, being a server-side operation.

Of course, given my context, I would really like to try the insert first
and only fall back to the update for the exception, but the lack of a
statement level abort prevents that approach currently.

Incidently, I'm leaning towards inserting into a temporary table with
a trigger that catches inserts to the temp table and converts it (appropriately)
into an insert or update into the 'real' table, instead of using a view.
My (limited) understanding is that the view would have to be rebuilt after
every insertion to keep it consistent, whereas the temp table would stay
empty - it's really just used as a place to attach the trigger to an
insert without having to worry about recursion...  Please correct me
if that's not correct!

Thanks!
--
Steve Wampler-  SOLIS Project, National Solar Observatory
swampler@noao.edu

Today, in a message to postgres-interfaces, Steve Wampler wrote:
>
> My (limited) understanding is that the view would have to be rebuilt
> after every insertion to keep it consistent,

In postgresql a view is in fact a relation just like a table is a
relation, except that the view has an "on select do instead .." trigger
attached to it (don't take the syntax literally, I'm writing this from
memory).

> whereas the temp table would stay empty - it's really just used as a
> place to attach the trigger to an insert without having to worry about
> recursion...

Same effect as using a view, except with a view you would also have a
trigger associated with select so you can select and insert into the same
relation.

Joachim

-- 
work:     joachima@realtimeint.com  (http://www.realtimeint.com)
private:  joachim@kraut.bc.ca       (http://www.kraut.bc.ca)