implement upserts (INSERT ... ON CONFLICT)

[wuputah]

INSERT ... ON CONFLICT ... is not currently implemented, even DO NOTHING if you have a primary key defined, e.g.:

postgres=# create table testy (id int PRIMARY KEY, v1 text, v2 text) using columnar;
CREATE TABLE
postgres=# insert into testy values (1, 'a', 'b') ON CONFLICT DO NOTHING;
ERROR:  columnar_tuple_insert_speculative not implemented

Presumably DO NOTHING would be easier to implement first.

A bit about insert_speculative from https://github.com/postgres/postgres/blob/master/src/include/access/heapam.h :

/*
 * Perform a "speculative insertion". These can be backed out afterwards
 * without aborting the whole transaction.  Other sessions can wait for the
 * speculative insertion to be confirmed, turning it into a regular tuple, or
 * aborted, as if it never existed.  Speculatively inserted tuples behave as
 * "value locks" of short duration, used to implement INSERT .. ON CONFLICT.
 *
 * A transaction having performed a speculative insertion has to either abort,
 * or finish the speculative insertion with
 * table_tuple_complete_speculative(succeeded = ...).
 */
static inline void
table_tuple_insert_speculative(Relation rel, TupleTableSlot *slot,
							   CommandId cid, int options,
							   struct BulkInsertStateData *bistate,
							   uint32 specToken)
{
	rel->rd_tableam->tuple_insert_speculative(rel, slot, cid, options, bistate, specToken);
}

How this is done in heap tables is available at https://github.com/postgres/postgres/blob/master/src/backend/access/heap/heapam_handler.c#L259-L280 though it largely relies on setting a flag then calling heap_insert ( https://github.com/postgres/postgres/blob/master/src/backend/access/heap/heapam.c#L2007-L2205 ).

[muntdan]

For me this is working but is a bit slow on large batches