4 ms·
2226 words and not a single mention of CREATE RULE. Postgres has had something far more general than "upserts" for over a decade. CREATE RULE Pages_Upsert AS
by hosay123 13y ago
2226 words and not a single mention of CREATE RULE. Postgres has had something far more general than "upserts" for over a decade.
CREATE RULE Pages_Upsert AS ON INSERT TO Pages
WHERE EXISTS (SELECT 1 from Pages P where NEW.Url = P.Url)
DO INSTEAD
UPDATE Pages SET LastCrawled = NOW(), Html = NEW.Html WHERE Url = NEW.Url;
- masklinn 13y ago1. Note that there are no plans for postgres to implement a specialised UPSERT. The plan was/is to implement MERGE, which is a fair bit more generic (especially with the 2008 and 2011 extensions). In fact, TFA very specifically disagrees with this route in his conclusion. 2. IIRC the rule method does not work correctly for multiple-rows inserts including duplicates
- the_mitsuhiko 13y ago> 1. Note that there are no plans for postgres to implement a specialised UPSERT. The plan was/is to implement MERGE, which is a fair bit more generic (especially with the 2008 and 2011 extensions). In fact, TFA very specifically disagrees with this route in his conclusion. Does the SQL standard actually guarantee that a MERGE is atomic? After all the MERGE statement involves a subselect which seems to be about as concurrency safe as a SQLite replace insert which a join.
- mjn 13y agoThe MERGE itself is atomic, but it doesn't prevent duplicate insertions without a lock: http://stackoverflow.com/questions/9871644/is-merge-an-atomic-statement-in-sql2008 http://stackoverflow.com/questions/9871644/is-merge-an-atomi...
- jeltz 13y agoIf PostgreSQL goes with an implementation of MERGE like that then it would not be a replacement for UPSERT. With UPSERT you can make sure to never get any constraint violations.
- jeltz 13y agoThere is a patch for specialized UPSERT which was being actively developed very recently. I would not be surprised if it makes it into PostgreSQL 9.5. On the other hand there is currently no working being done on MERGE. https://commitfest.postgresql.org/action/patch_view?id=1201 https://commitfest.postgresql.org/action/patch_view?id=1201
- chrisfarms 13y agoThis does not solve the fundamental problem of an upsert. This rule allows the possibility of raising an exception on INSERT if two Pages with the same Url are "upserted" in parallel. You would still require a table level lock to ensure you don't get an exception within the statement as a whole. Also: Rules are easy to get wrong. Try not to reach for them unless absolutely necessary.
- tobltobs 13y agoRules also break RETURNING.
- Terretta 13y agoYep. The moment you have a condition, you can't have a returning, and it's just annoying.
- the_mitsuhiko 13y ago> 2226 words and not a single mention of CREATE RULE. Two reasons: a.) same concurrency problems. b.) defined globally for the table. Completely useless for the kind of things I need an upsert for.