Re: JDBC behaviour

Please see http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html
You would need something like an "instead of trigger" for insert doing what
you want. If you are not forced to use "insert", you can directly call the
procedure that handles errors.
It has nothing to do with JDBC, it's how PostgreSQL transactions works.

Best regards, Vitalii Tymchyshyn

Сб, 20 лют. 2016 о 21:20 Sridhar N Bamandlapally <sridhar(dot)bn1(at)gmail(dot)com>
пише:

> Hi
>
> My expectation is simple, please refer below
>
> create table employee(empid numeric(4) primary key, ename varchar(20));
>
> from Java/jdbc code, conn.setAutoCommit(false)
>
> insert into employee values(1, 'K1');
> insert into employee values(1, 'K1');
> insert into employee values(2, 'K2');
>
> by looking at exceptions i may rollback or commit, i.e. conn.rollback() or
> conn.commit()
> if I rollback table should be empty,
> if I commit table should have 2 rows
>
> is there any way is possible ?
>
> Thanks
> Sridhar
>
>
>
>
> On Sat, Feb 20, 2016 at 11:12 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
>> We do have a patch https://github.com/pgjdbc/pgjdbc/pull/477 that will
>> address this but not in batch update.
>>
>> In my mind batches are used primarily for performance. Adding the
>> overhead of savepoints makes them much slower, which is something we are
>> unlikely to accommodate
>>
>> Dave
>>
>> Dave Cramer
>>
>> davec(at)postgresintl(dot)com
>> www.postgresintl.com
>>
>> On 20 February 2016 at 12:34, Vitalii Tymchyshyn <vit(at)tym(dot)im> wrote:
>>
>>> Well, often in ETL it's not MY crappy data. It's something received from
>>> upstream system and my task is to perform a load, not stop on the first
>>> error and produce at least meaninful report on data crappyness statistics.
>>> Extended cases may involve something like manual data fixing for error rows.
>>>
>>> Just a real world example: in financial transactions sometimes you can
>>> receive a transaction on a financial instrument that is brand new and is
>>> not in your db yet. You don't want to fail the whole batch.
>>>
>>> And yes, globally it's a move from "getting data" to "filtering this
>>> [crappy] data ocean" going on.
>>>
>>> Best regards, Vitalii Tymchyshyn
>>>
>>> Сб, 20 лют. 2016 12:09 Bill Moran <wmoran(at)potentialtech(dot)com> пише:
>>>
>>>> On Sat, 20 Feb 2016 16:29:09 +0000
>>>> Vitalii Tymchyshyn <vit(at)tym(dot)im> wrote:
>>>>
>>>> > Well, I suppose replacing simple copy with procedural per-row function
>>>> > would give huge performance hit. Also what method do you propose to
>>>> use in
>>>> > the code? Savepoints?
>>>>
>>>> Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
>>>> savepoints.
>>>>
>>>> > I'd say this would also add a more slowdown.
>>>>
>>>> What? The savepoints? Well, you don't need them. The stored
>>>> procedure is going to incur a bit of a hit, though.
>>>>
>>>> > Also quite a bit of boilerplate code would be needed. It's similar to
>>>> merge
>>>> > statement. Yes, it can be achieved in pure SQL, but having clean merge
>>>> > statement saves you a lot of headache and is usually much more
>>>> efficient.
>>>> > Basically, it's not that what OP needs is not doable at all, it's that
>>>> > other RDBMs often has this functionality in much more convenient and
>>>> > performance optimized way.
>>>>
>>>> True. I don't think "clean up my crappy data" has ever been a
>>>> priority for PostgreSQL. Although, "allow the user to build whatever
>>>> is needed" has been.
>>>>
>>>> I find it curious that those of us who become stewards of other people's
>>>> data find ourselves bending over backwards to try to clean up their
>>>> garbage data. It's an interesting social commentary on how software
>>>> design has changed since the term GIGO was in common use.
>>>>
>>>> > Best regards, Vitalii Tymchyshyn
>>>> >
>>>> > ??, 20 ???. 2016 11:16 Bill Moran <wmoran(at)potentialtech(dot)com> ????:
>>>> >
>>>> > > On Sat, 20 Feb 2016 16:01:04 +0000
>>>> > > Vitalii Tymchyshyn <vit(at)tym(dot)im> wrote:
>>>> > >
>>>> > > > Well, it OT here and belongs to -hackers, but as for me main use
>>>> case
>>>> > > here
>>>> > > > is ETL or ELT process getting a lot of unvalidated external data.
>>>> > > > And a good option to solve this problem is not to change
>>>> transaction
>>>> > > > semantics or slow down processing by adding tons of savepoints,
>>>> but add
>>>> > > "on
>>>> > > > error" clause to insert/copy statement.
>>>> > > >
>>>> > > > This clause should allow to save records that can't fit into
>>>> destination
>>>> > > > table because of type, check of referential constaints into error
>>>> table.
>>>> > > > Oracle has similar functionality and we are using it in our
>>>> project. No
>>>> > > > error is generated - no transaction rollback, batch abort or
>>>> similar.
>>>> > > >
>>>> > > > As for me it would cover 90% of use cases and would be really
>>>> usefull.
>>>> > > The
>>>> > > > one problem I can see is with inserting into partition parent.
>>>> > >
>>>> > > PL/PGSQL provides this functionality. It requires the creation of a
>>>> server
>>>> > > side function and using that function to insert data, but it can do
>>>> exactly
>>>> > > what you're describing.
>>>> > >
>>>> > > While adding other mechanisms to make it "easier" or "more like
>>>> some other
>>>> > > software" might be valuable; the simple fact is that Postgres _does_
>>>> > > support
>>>> > > what you want. The fact that you're not aware of it doesn't change
>>>> that.
>>>> > >
>>>> > > --
>>>> > > Bill Moran
>>>> > >
>>>>
>>>>
>>>> --
>>>> Bill Moran
>>>>
>>>
>>
>