pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: codingsemen (@codingsemen) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: Re: [pgjdbc/pgjdbc] issue #194: PgJDBC can experience client/server deadlocks during batch execution
Date: Tue, 04 Aug 2020 11:17:18 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
I've encountered the same issue in our application aswell.
Using batchSize of 5000 always reliably reproduces the issue on a table with 54 columns.
Setting the batchSize to 2000 solves the problem but I suppose only until an even bigger data set comes around with more columns and longer textfields. (Insert batching)
I don't see how to change the output buffer size. Could you elaborate?
```Thread [main] (Suspended)
owns: BufferedOutputStream (id=60)
owns: QueryExecutorImpl (id=61)
SocketOutputStream.socketWrite0(FileDescriptor, byte[], int, int) line: not available [native method] [local variables unavailable]
SocketOutputStream.socketWrite(byte[], int, int) line: 111
SocketOutputStream.write(byte[], int, int) line: 155
BufferedOutputStream.flushBuffer() line: 82
BufferedOutputStream.write(byte[], int, int) line: 126
BufferedOutputStream(FilterOutputStream).write(byte[]) line: 97
PGStream.sendInteger4(int) line: 264
QueryExecutorImpl.sendParse(SimpleQuery, SimpleParameterList, boolean) line: 1537
QueryExecutorImpl.sendOneQuery(SimpleQuery, SimpleParameterList, int, int, int) line: 1858
QueryExecutorImpl.sendQuery(Query, V3ParameterList, int, int, int, ResultHandler, BatchResultHandler) line: 1421
QueryExecutorImpl.execute(Query[], ParameterList[], BatchResultHandler, int, int, int) line: 496
PgPreparedStatement(PgStatement).internalExecuteBatch() line: 851
PgPreparedStatement(PgStatement).executeBatch() line: 874
PgPreparedStatement.executeBatch() line: 1563
Postgres(Database).executeBatchedInsert(Connection, PreparedStatement, String[], int, CsvRow[], String) line: 99
DatabaseHandler.prepareAndExecuteBatchedInsert(Connection, List<CsvRow>, int, String[], String[], String) line: 305
ImportManager.importTable(String[], String[], String) line: 238
ImportManager.importTable(String) line: 126
Tester.main(String[]) line: 27
```
Using driverversion 42.2.14
```java
private void setBufferSize(Connection con) {
Field queryExecutorField = null;
try {
queryExecutorField = PgConnection.class.getDeclaredField("queryExecutor");
} catch (NoSuchFieldException | SecurityException e) {
e.printStackTrace();
}
queryExecutorField.setAccessible(true);
QueryExecutorImpl pc = null;
try {
pc = (QueryExecutorImpl)queryExecutorField.get(con);
} catch (IllegalArgumentException | IllegalAccessException e) {
e.printStackTrace();
}
Field pgstreamField = null;
try {
pgstreamField = QueryExecutorBase.class.getDeclaredField("pgStream");
} catch (NoSuchFieldException | SecurityException e) {
e.printStackTrace();
}
pgstreamField.setAccessible(true);
PGStream pgs = null;
try {
pgs = (PGStream) pgstreamField.get(pc);
} catch (IllegalArgumentException | IllegalAccessException e) {
e.printStackTrace();
}
Socket s = pgs.getSocket();
try {
System.err.println("PgJDBC send buffer size is: " + s.getSendBufferSize());
s.setSendBufferSize((int)byteSize+5000000);
} catch (SocketException e) {
e.printStackTrace();
}
```
Apparently I'm somehow calculating my byteSize for the buffer wrong.
```java
switch(dataType) {
case CHAR:
case NCHAR:
case VARCHAR:
case NVARCHAR:
byteSize += columnValue.getBytes().length;
prepStatement.setString(columnIndex, columnValue);
break;
case TIMESTAMP:
Timestamp timestamp = null;
try {
Long timeStamp = Long.parseLong(columnValue);
timestamp = new Timestamp(timeStamp);
} catch(NumberFormatException e) {
LOG.error("couldn't parse timestamp from csv", e);
}
byteSize += 10;
prepStatement.setTimestamp(columnIndex, timestamp);
break;
case NUMBER:
byteSize += 8;
long number = Long.parseLong(columnValue);
prepStatement.setLong(columnIndex, number);
break;
case BLOB:
case CLOB:
byteSize += columnValue.getBytes().length;
ByteArrayInputStream is = new ByteArrayInputStream(columnValue.getBytes());
prepStatement.setBinaryStream(columnIndex, is);
break;
default:
break;
}
```
Only after I add 5000000 it works as intended.
view thread (36+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: github://pgjdbc/pgjdbc
Cc: [email protected], [email protected]
Subject: Re: [pgjdbc/pgjdbc] issue #194: PgJDBC can experience client/server deadlocks during batch execution
In-Reply-To: <<[email protected]>>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox