Message-ID: <gh-pgjdbc-pgjdbc-194-c668536155@github.com>
From: "codingsemen (@codingsemen)" <noreply+codingsemen@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Tue, 04 Aug 2020 11:17:18 +0000
Subject: Re: [pgjdbc/pgjdbc] issue #194: PgJDBC can experience client/server deadlocks during batch execution
In-Reply-To: <gh-pgjdbc-pgjdbc-194@github.com>
References: <gh-pgjdbc-pgjdbc-194@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: codingsemen
X-GitHub-Comment-Id: 668536155
X-GitHub-Comment-Type: issue_comment
X-GitHub-Edited-At: 2020-08-05T07:31:09Z
X-GitHub-Issue: 194
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/194#issuecomment-668536155
Content-Type: text/plain; charset=utf-8

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.