Re: Fwd: problems changing postgres jdbc driver - Mailing list pgsql-jdbc

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,rhost=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,rhost=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,rhost=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,rhost=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,rhost=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,rhost=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,rhost=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,rhost=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,rhost=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,rhost=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 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