Re: Efficient use of ON CONFLICT DO UPDATE with the PostgreSQL JDBC driver

Christopher Brown wrote:
> ...
> I'm wondering if there's any performance or resource-usage side effects (or workarounds) when doing something like this:
>
> INSERT INTO foo (c_uuid, file_data, file_name) VALUES (?, ?, ?)
> ON CONFLICT (c_uuid) DO UPDATE SET file_data = ?, file_name = ?
>
> On the Java side, that means invoking PreparedStatement::setBinaryStream TWICE for the same data. ...

Yes, there are lots of resource usage side effects.

pgjdbc transmits the byte stream over the network once for every time the stream is bound.

You will also need to provide independent InputStream values (or use mark()/reset()) because setBinaryStream() consumes the stream.
The nice thing with pgjdbc is that it consumes the stream at the time you make the setBinaryStream() call. "small" streams (50 KB or less)
are materialized in memory while larger streams are written to a temp file. The mark()/reset() trick doesn't work for JDBC drivers that
consume the stream lazily.

If your query uses simple protocol, the query is materialized as a StringBuilder/String in its entirety (including parameter replacement)
before the query is transmitted (when you execute the statement). Which, if you have large blob/clob values, can result in
OutOfMemoryError or running into the internal JVM limits on String/array size (normally Integer.MAX_VALUE).