From: Rafal Pietrak <rafal@zorro.isa-geek.com>
To: pgsql-general@postgresql.org
Subject: INSERT-colision/MERGE in postgresql
Date: Sun, 14 Aug 2011 09:06:04 +0200
Message-ID: <1313305564.11685.24.camel@localhost.localdomain> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rafal@zorro.isa-geek.com, pgsql-general@postgresql.org
Subject: Re: INSERT-colision/MERGE in postgresql
In-Reply-To: <1313305564.11685.24.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox