Thread: transaction and insert
Hi,
Can anyone help me? It looks like that it is real bug. When I use the PostgreSQL 10, it happen to me. After that, I have upgraded my PostgreSQL to version 11 with new JDBC. It is the same as before, eg. I have a transaction block with sql select and insert statements. At the beginning, eg. it is working fine at first 30 or 50 tests. After that, I have unusual error, eg. when I comment out the transaction statements (begin and commit), it works fine again. Can anyone tell me how to debug this? Or how to output more error information? Or anyone can give a clue what is the problem. Is it memory not enough or cache not enough? I also have run vacuum and restart the PostgreSQL, the error still exists.
The error is : ERROR: invalid input syntax for integer:
Actually
I use Linux cento 7.
Thank you very much in advance.
Regards,
Jack
aOn Wed, Jul 17, 2019 at 11:24 AM jack chen <jackchen88@gmail.com> wrote:
Can anyone help me? It looks like that it is real bug.
Mostly likely but its your application's bug, not PostgreSQL's.
When I use the PostgreSQL 10, it happen to me. After that, I have upgraded my PostgreSQL to version 11 with new JDBC. It is the same as before,
Expected...
eg. I have a transaction block with sql select and insert statements. At the beginning, eg. it is working fine at first 30 or 50 tests. After that, I have unusual error, eg. when I comment out the transaction statements (begin and commit), it works fine again.
Doubtful, you probably still have an error you are probably just ignoring it when you forgo transactions.
Can anyone tell me how to debug this? Or how to output more error information?
Focus on debugging and instrumenting your application until it sees the error and then dump out the data the application is sending to the server.
Or anyone can give a clue what is the problem. Is it memory not enough or cache not enough? I also have run vacuum and restart the PostgreSQL, the error still exists.
Given the error message below your problem is happening during data ingestion and does not likely pertain to any data already existing in tables or indexes. Vaccum/memory/cache have nothing to do with it.
The error is : ERROR: invalid input syntax for integer:
Server log for PostgreSQL or, probably more usefully, the application log of the problem that sending what appears to be an empty string and expecting the server to convert that into an integer. The empty string cannot be cast to integer, only numbers and NULL can be.
David J.
Thanks for your advice.
But question is that it is working in first 30 or 50 tests. After that, I have this problem. I have also increased the memory but it is not working.
It is the same as PostgreSQL version 10.
Regards,
Jack
On Thu, Jul 18, 2019 at 4:37 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
aOn Wed, Jul 17, 2019 at 11:24 AM jack chen <jackchen88@gmail.com> wrote:Can anyone help me? It looks like that it is real bug.Mostly likely but its your application's bug, not PostgreSQL's.When I use the PostgreSQL 10, it happen to me. After that, I have upgraded my PostgreSQL to version 11 with new JDBC. It is the same as before,Expected...eg. I have a transaction block with sql select and insert statements. At the beginning, eg. it is working fine at first 30 or 50 tests. After that, I have unusual error, eg. when I comment out the transaction statements (begin and commit), it works fine again.Doubtful, you probably still have an error you are probably just ignoring it when you forgo transactions.Can anyone tell me how to debug this? Or how to output more error information?Focus on debugging and instrumenting your application until it sees the error and then dump out the data the application is sending to the server.Or anyone can give a clue what is the problem. Is it memory not enough or cache not enough? I also have run vacuum and restart the PostgreSQL, the error still exists.Given the error message below your problem is happening during data ingestion and does not likely pertain to any data already existing in tables or indexes. Vaccum/memory/cache have nothing to do with it.The error is : ERROR: invalid input syntax for integer:Server log for PostgreSQL or, probably more usefully, the application log of the problem that sending what appears to be an empty string and expecting the server to convert that into an integer. The empty string cannot be cast to integer, only numbers and NULL can be.David J.
On Wed, Jul 17, 2019 at 11:44 AM jack chen <jackchen88@gmail.com> wrote:
But question is that it is working in first 30 or 50 tests. After that, I have this problem. I have also increased the memory but it is not working
Given that we know nothing about these "first 30 or 50 tests" its impossible to explain or provide more explicit advice. You need to instrument your code and figure out what it is sending to the server when the error occurs.
David J.
The error is : ERROR: invalid input syntax for integer:
Server log for PostgreSQL or, probably more usefully, the application log of the problem that sending what appears to be an empty string and expecting the server to convert that into an integer. The empty string cannot be cast to integer, only numbers and NULL can be.
This can occur when bulk-uploading data from with decimal numbers, too. In other words ‘0’ can be cast as an integer, but ‘0.0’, when found in the same column, cannot.
any attachments are intended solely for the
addressee(s) and may contain confidential
and/or legally privileged information. If you
are not the intended recipient of this message
or if this message has been addressed to you
in error, please immediately alert the sender
by reply e-mail and then delete this message
and any attachments. If you are not the
intended recipient, you are notified that
any use, dissemination, distribution, copying,
or storage of this message or any attachment
is strictly prohibited.
Adding back -bugs to the email chain........
On Wed, Jul 17, 2019 at 12:15 PM jack chen <jackchen88@gmail.com> wrote:
Thank you very much. The following is my code and output.
try{
System.err.println(" I am here -------------------------------- 111111111111111 --------------------------------------- : ");
System.err.println(" 11111111111 : " + sqlUpdateAddSubTotal + sqlInsertAddRecord);
rows = stmt.executeUpdate(sqlUpdateAddSubTotal + sqlInsertAddRecord);
System.err.println(" I am here -------------------------------- 2222222222222222 --------------------------------------- : ");
}
}
catch(SQLException e)
{
errMessage = e.getMessage();
System.err.println(" errMessage 99999999999999999999999999 : " + errMessage);}
output :
I am here -------------------------------- 111111111111111 --------------------------------------- :
11111111111 : update points set points = 4091028, balance = 4065108, record_time = '2019-7-18 5:3:2 +10:00', ip_address = '10.10.10.10' where user_id = 5105 and point_type = 2 and sequence = 2 and language = 'en' and locality = 'au';
insert into points values(5105, 2, 340, 'en', 'au', 26013, 25920, 4091028, 5186, 'Renew Own Domain With Web Only Hosting', '2019-7-18 5:3:2 +10:00', '110.110.110.110', null, null);
I am here -------------------------------- 2222222222222222 --------------------------------------- :
errMessage 99999999999999999999999999 : ERROR: invalid input syntax for integer: "Renew Own Domain With Web Only Hosting"
Position: 75
Awesome, now we know that the 10th column of the points table is an integer but you are inserting some sort of textual name into it.
Its not clear that this ever actually worked but in any case you either need to rearrange the VALUES clause of the INSERT to match the column order of the points table or add the column names to the command.
INSERT INTO points (col1, col2, col3....) VALUES ('','',''....);
David J.
On Wed, Jul 17, 2019 at 12:24 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
rows = stmt.executeUpdate(sqlUpdateAddSubTotal + sqlInsertAddRecord);
Also, after solving your immediate concern you should learn about SQL Injection risks and using preparedStatement in Java to mitigate that risk. The style you are using here is simply bad code.
That fact that you don't re-throw the SQLException is also bad (can't tell if that is mock-up code or live production, but given other observations I'm going to assume the later).
David J.