From: Rafal Pietrak <rafal@zorro.isa-geek.com>
To: pgsql-general@postgresql.org
In-Reply-To: <alpine.LNX.2.00.1108131159430.1426@salmo.appl-ecosys.com>
References: 
 <CAN2Y=uPjF14F5A9JXj02_vaPw4TMR0WGQid_w80xt4sC8o4qGg@mail.gmail.com>
	<4E46B33B.7090801@bestmx.ru>
	<CAN2Y=uM+jKpK7DGxTvfXE=wqR4__17zw+eW7NTA6AL17aGAMww@mail.gmail.com>
	<alpine.LNX.2.00.1108131159430.1426@salmo.appl-ecosys.com>
Content-Type: text/plain; charset="UTF-8"
Date: Sun, 14 Aug 2011 09:06:04 +0200
Message-ID: <1313305564.11685.24.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: INSERT-colision/MERGE in postgresql

Hi,

I've recently looked into the problem of my INSERTs throwing an ROW
error, when a new row hits an already present one, by unique constraint.
It triggers an expensive rollback, and I'd like to have it sort of
"optimised". In my case, duplicates can be discarded on an attempt
INSERT, but an UPDATE instead would also do.

When I was looking for a solution, I found this:
http://wiki.postgresql.org/wiki/SQL_MERGE

Which would do nicely, but I understand postgresql does not have it,
yet.

On the other hand, I think that providing the OLD.* table for RULES and
TRIGGERS on INSERT, for an application level programmer (like myself),
could provide a simple way to overcome the missing feature, until it's
fully implemented as MERGE statement according to SQL:2003. 

Such OLD.* table on INSERTS should contain a row from current table
content, that matches unique constraints of a currently inserted row or
nothing, if there is no collision. This way I could make a conditional
RULE with "... WHERE exists(OLD.someting)..." instead of doing an
explicit SELECT in that WHERE clausure, which I think is more expensive
then referring a column already fetched by the engine.

Possibly, this may pave the way to MERGE implementation??

As of today, no application level code can possibly expect a valid OLD.*
table within ROLE/TRIGGER on INSERT - so no current code will be broken
by this.

Would it be possible to add this to a whishlist for 9.2 or something?

Regards,

-R