INSERT-colision/MERGE in postgresql

From: Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: INSERT-colision/MERGE in postgresql
Date: 2011-08-14 07:06:04
Message-ID: 1313305564.11685.24.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Uwe Schroeder 2011-08-14 08:02:51 Re: Using Postgresql as application server
Previous Message Greg Williamson 2011-08-13 23:45:51 Re: Using Postgresql as application server

Browse pgsql-general by date

  From Date Subject
Next Message Uwe Schroeder 2011-08-14 08:02:51 Re: Using Postgresql as application server
Previous Message Scott Marlowe 2011-08-14 05:29:45 Re: backup-strategies for large databases