Re: Fwd: problems changing postgres jdbc driver

--

Silvio Brandani

good idea, we set up a test case.

we suspect something related to server prepared statement .

Thanks

Il 09/03/2016 14:18, Dave Cramer ha scritto:
> Oh, sorry for the mis-understanding.
>
> Is it possible to create a test case which creates this error ?
>
> Dave
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com <mailto:davec(at)postgresintl(dot)com>
> www.postgresintl.com <http://www.postgresintl.com>
>
> On 9 March 2016 at 08:16, Silvio Brandani <silvio(dot)brandani(at)tech(dot)sdb(dot)it
> <mailto:silvio(dot)brandani(at)tech(dot)sdb(dot)it>> wrote:
>
> I don't say the driver change the order of operations, I said that
> after changing the driver the operations executed in that order
> give the Foreign key violation error.
>
> But the record is inserted in ap_invoice_t (referenced by
> ap_invoice_m) one step before the insert on ap_invoice_m ( which
> raise the FK violaton error)
>
> are there options enable by default with the new driver they were
> not before ?
>
>
> --
>
> Silvio Brandani
>
>
>
> Il 09/03/2016 14:04, Dave Cramer ha scritto:
>> The driver does not change the order of operations.
>>
>> I'd be curious what else changed at the same time as the driver
>> upgrade.
>>
>> Either way if you could provide us with a test case we'd be happy
>> to look into it for you.
>>
>> Dave Cramer
>>
>> davec(at)postgresintl(dot)com <mailto:davec(at)postgresintl(dot)com>
>> www.postgresintl.com <http://www.postgresintl.com>
>>
>> On 9 March 2016 at 06:29, Silvio Brandani
>> <silvio(dot)brandani(at)tech(dot)sdb(dot)it
>> <mailto:silvio(dot)brandani(at)tech(dot)sdb(dot)it>> wrote:
>>
>> yes the,
>>
>> previous log is from server logging , the statements are
>> executed in that order and from same session, I clean up the
>> log to be readble.
>>
>> anyway the log is as follow ( session 22302)
>>
>> 2016-03-08 13:45:10 CET [22302]: [2038-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> LOG: execute S_11: INSERT INTO
>> ap_invoice_t (branch_cod, doc_year, orig_doc, amount,
>> az_remota, branch_ad2, branch_ad3, branch_ad4, branch_ad5,
>> branch_nam, cage1, cage2, cage3, cdes1, cdes2, cdes3, cfab1,
>> cfab2, cfab3, data_manut, data_par, dept, descr, doc_date,
>> doc_type, esito, file_ref, house_bl, lcl_fcl, loc_destin,
>> master_bl, mov_nr, notes, orig_ref, porto_car, porto_sca,
>> targa, valuta, vettore, azienda) values ($1, $2, $3, $4, $5,
>> $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18,
>> $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30,
>> $31, $32, $33, $34, $35, $36, $37, $38, $39, $40)
>> 2016-03-08 13:45:10 CET [22302]: [2039-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> DETAIL: parameters: $1 =
>> '2160100121', $2 = '2016', $3 = '0010044846', $4 =
>> '69.0999999999999943', $5 = '31', $6 = 'Via Del Botteghino,
>> 24/26', $7 = 'SCANDICCI - FLORENCE 50018', $8 = 'Tel. +39
>> 05552191', $9 = 'Fax. +39 055721288', $10 = 'SAVINO DEL BENE
>> SPA WW-HQ - ITALY', $11 = '', $12 = '', $13 = '', $14 = '',
>> $15 = '', $16 = ' ', $17 = '', $18 = '', $19 = ' ', $20 =
>> '2016-03-08 00:00:00', $21 = '2016-03-08', $22 = '05', $23 =
>> '', $24 = '2016-03-07', $25 = 'DB', $26 = 'Y', $27 =
>> 'XXXXCZ', $28 = '', $29 = '', $30 = '', $31 = '', $32 =
>> '000001733', $33 = '', $34 = '', $35 = '', $36 = '', $37 =
>> '', $38 = 'USD', $39 = '', $40 = 'CZ'
>> 2016-03-08 13:45:10 CET [22302]: [2040-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> LOG: duration: 2.661 ms
>> 2016-03-08 13:45:10 CET [22302]: [2041-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> LOG: duration: 0.176 ms
>> 2016-03-08 13:45:10 CET [22302]: [2042-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> LOG: duration: 0.206 ms
>> 2016-03-08 13:45:10 CET [22302]: [2043-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> LOG: execute <unnamed>: INSERT INTO
>> ap_invoice_m (branch_cod, cod_voce, doc_year, orig_doc,
>> progr, des_voce, imp_voce, azienda) values ($1, $2, $3, $4,
>> $5, $6, $7, $8)
>> 2016-03-08 13:45:10 CET [22302]: [2044-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> DETAIL: parameters: $1 =
>> '2160100121', $2 = 'c03', $3 = '2016', $4 = '0010044846', $5
>> = '1', $6 = 'AS PER AGREEMENT TOTAL TO YOUR DEBIT FOR
>> INTERNAL', $7 = '69.0999999999999943', $8 = 'CZ'
>> 2016-03-08 13:45:10 CET [22302]: [2045-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> ERROR: insert or update on table
>> "ap_invoice_m" violates foreign key constraint
>> "ap_invoice_m_fkey"
>> 2016-03-08 13:45:10 CET [22302]: [2046-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> DETAIL: Key (branch_cod, orig_doc,
>> doc_year, azienda)=(2160100121 , 0010044846 , 2016, CZ ) is
>> not present in table "ap_invoice_t".
>> 2016-03-08 13:45:10 CET [22302]: [2047-1]
>> db=tsdb,user=java,lhost=sdbfi-pg02ng.sdb.it
>> <http://sdbfi-pg02ng.sdb.it>,rhost=jblade01.sdb.it
>> <http://jblade01.sdb.it> STATEMENT: INSERT INTO ap_invoice_m
>> (branch_cod, cod_voce, doc_year, orig_doc, progr, des_voce,
>> imp_voce, azienda) values ($1, $2, $3, $4, $5, $6, $7, $8)
>>
>> Thanks
>>
>> --
>>
>> Silvio Brandani
>>
>>
>> Il 09/03/2016 12:14, Albe Laurenz ha scritto:
>>> Silvio Brandani wrote:
>>>> We have problems changing postgres jdbc driver from postgresql-9.3-1102.jdbc41.jar to postgresql-
>>>> 9.4.1208.jar
>>>>
>>>> we have table ap_invoice_t
>>>>
>>>> Referenced by:
>>>> TABLE "ap_invoice_m" CONSTRAINT "ap_invoice_m_fkey" FOREIGN KEY (branch_cod, orig_doc, doc_year,
>>>> azienda) REFERENCES "123".ap_invoice_t(branch_cod, orig_doc, doc_year, azienda) ON DELETE CASCADE
>>>>
>>>> the following operations are executed in order:
>>> [...]
>>>
>>>> INSERT INTO ap_invoice_t (branch_cod, doc_year, orig_doc, [...] azienda)
>>>> values
>>>> ($1, $2, $3, [...] $40)
>>>>
>>>> $1 = '2150100078', $2 = '2015', $3 = '0000079369', [...] $40 = 'CX'
>>>>
>>>>
>>>> INSERT INTO ap_invoice_m (branch_cod, cod_voce, doc_year, orig_doc, progr, des_voce, imp_voce,
>>>> azienda) values ($1, $2, $3, $4, $5, $6, $7, $8)
>>>> DETAIL: parameters: $1 = '2150100078', $2 = 'M01', $3 = '2015', $4 = '0000079369', $5 = '1', $6 =
>>>> 'OCEAN IMPORT FREIGHT', $7 = '150', $8 = 'CX'
>>>> ERROR: insert or update on table "ap_invoice_m" violates foreign key constraint "ap_invoice_m_fkey"
>>>> DETAIL: Key (branch_cod, orig_doc, doc_year, azienda)=(2150100078 , 0000079369 , 2015, CX ) is not
>>>> present in table "ap_invoice_t".
>>>> STATEMENT: INSERT INTO ap_invoice_m (branch_cod, cod_voce, doc_year, orig_doc, progr, des_voce,
>>>> imp_voce, azienda) values ($1, $2, $3, $4, $5, $6, $7, $8)
>>>>
>>>> as you se we get unexpectedly Foreing key violation
>>> Turn on server logging (log_statement=all) in postgresql.conf and examine the
>>> server log:
>>>
>>> - Are the two statements executed in the correct order?
>>> - Are statements executed on the same database connection (same session ID in the log)?
>>>
>>> It does not look like a JDBC driver problem at first glace.
>>>
>>> Yours,
>>> Laurenz Albe
>>
>>
>> This message is for the recipients only. If you receive it in
>> error, please notify the sender and delete it together with
>> any attachments. For any further information, including our
>> privacy policy please refer to
>> http://www.savinodelbene.com/privacy/
>>
>>
>
>
> This message is for the recipients only. If you receive it in
> error, please notify the sender and delete it together with any
> attachments. For any further information, including our privacy
> policy please refer to http://www.savinodelbene.com/privacy/
>
>

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

This message is for the recipients only. If you receive it in error, please notify the sender and delete it together with any attachments. For any further information, including our privacy policy please refer to http://www.savinodelbene.com/privacy/

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