From: | Steven Schlansker <steven(at)likeness(dot)com> |
---|---|
To: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: upsert functionality |
Date: | 2013-05-16 17:27:12 |
Message-ID: | EC31D0A3-3B8A-4136-BBA8-0D5E720C754D@likeness.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On May 15, 2013, at 11:52 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> Sajeev Mayandi, 16.05.2013 07:01:
>> Hi,
>>
>> Our company is planning to move to postreSQL. We were initially using
>> sybase where upsert functionality was available using "insert on
>> existing update" clause. I know there multiple ways to fix this
>> using RULE or separate function in postgresql. But I would like to
>> know which version of postgresql has support for upsert planned using
>> an official syntax. I have postgresql 9.2 which does not have this
>> feature, if its planned in near future, I would rather wait to
>> migrate to PostgreSQL.
>>
>
> You can use writeable CTEs for this purpose.
>
> There are several examples out there:
>
> http://www.xzilla.net/blog/2011/Mar/Upserting-via-Writeable-CTE.html
> http://www.depesz.com/2011/03/16/waiting-for-9-1-writable-cte/
> http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/
>
> http://stackoverflow.com/a/8702291/330315
One thing I didn't see mentioned in two of the links -- they mention race
conditions, where multiple writers can still cause the faked UPSERT to fail.
This can be avoided using SERIALIZABLE transactions, now that Postgres has
SSI. http://wiki.postgresql.org/wiki/SSI
I can vouch that we use writable CTEs and SERIALIZABLE to implement UPSERT
in production with no issues thus far.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-05-16 17:36:12 | Re: 9.3 beta and materialized views |
Previous Message | Thomas Kellerer | 2013-05-16 17:23:30 | 9.3 beta and materialized views |