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

From: Christopher BROWN <brown(at)reflexe(dot)fr>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Efficient use of ON CONFLICT DO UPDATE with the PostgreSQL JDBC driver
Date: 2017-01-13 14:01:53
Message-ID: CAHL_zcOaWb2wuTX0eVnVXie0LhDjdZgvX0xR1PZ1GX2sM-8Ovw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hello,

I've at last had an opportunity to use PostgreSQL 9.6 (instead of 9.4) for
a project, and have been able to use the ON CONFLICT DO UPDATE clause. As
it can involve repeating parameter values (including, as is my case, large
binary files), 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.

I'm not noticing side effects on localhost (but haven't profiled either).
Is this fine, or a potential problem? Is there a better approach?

Thanks,
Christopher

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2017-01-13 14:20:09 Re: Efficient use of ON CONFLICT DO UPDATE with the PostgreSQL JDBC driver
Previous Message Rich Shepard 2016-12-28 23:16:57 Re: Questions on use