PostgreSQL 9.5 Upsert Capability

Our data permanence relies on PostgreSQL for its failure resistance.  So, far this week has been spent upgrading our Amazon Web Services relational database system to the new PostgreSQL 9.5.2 to include the Upsert capability.

Upsert, you say?

Upsert is a technical term for an insert or update of a data row rolled into a single transaction.  Prior to this I was jury rigging a solution in Java on the layer above the database, but now I can use the upsert to avoid all of the excess computational power required to juggle things in Java.

Why did take so long for PostgreSQL to implement, doesn't Oracle and MySQL do it already? 

Yes, MySQL has an INSERT OR UPDATE capability, but it doesn't care about concurrency issues, namely race-conditions and thus data integrity is not preserved in a MySQL INSERT OR UPDATE transaction when you have multiple threads interacting with the database at once.  The problems surrounding the insert or update are described very playfully in this post.  Oracle does atomically, thread-safe upsert already, but it is not open source, so to have an enterprise-level open source database include upsert is a big deal.

However, translating the upsert from Java to PostgreSQL has had an annoying and unforeseen consequence.  Heretofore the primary key assignment responsibilities lay in the PostgreSQL's 'bigserial' data-type which causes PostgreSQL to automatically create a sequence to increment the primary key.  However, the upsert statement in PostgresSQL does a conflict check that requires the potential key to be input into the statement like:

where the Primary Key field has to be included in the INSERT statement, otherwise you end up inserting ad infinitum because there is no conflict when you don't have a primary key that you want to insert.

So this precipitated a change in how keys are assigned on the server-side software, namely that primary key assignment now falls to the data cache rather than the bigserial of PostgreSQL.  It is annoying, but necessary to gain the atomic, thread-safe upsert that is completed in a single transaction.

á na márië,
gumshoe, out.