transaction and insert

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:

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.

E-MAIL CONFIDENTIALITY NOTICE:
The contents of this e-mail message and 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.