INSERT-colision/MERGE in postgresql

Поиск
Список
Период
Сортировка
От Rafal Pietrak
Тема INSERT-colision/MERGE in postgresql
Дата
Msg-id 1313305564.11685.24.camel@localhost.localdomain
обсуждение исходный текст
Ответ на Re: [ADMIN] Using Postgresql as application server  (Rich Shepard <rshepard@appl-ecosys.com>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: backup-strategies for large databases
Следующее
От: k_b
Дата:
Сообщение: Where to start, graphs and routing.