Hacker News Reader

Comment by hosay123 | Hacker News Reader

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;

View on HN

This 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.

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.

2. IIRC the rule method does not work correctly for multiple-rows inserts including duplicates

> 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.

The MERGE itself is atomic, but it doesn't prevent duplicate insertions without a lock: http://stackoverflow.com/questions/9871644/is-merge-an-atomi...

If 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.

There 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

Rules also break RETURNING.

Yep. The moment you have a condition, you can't have a returning, and it's just annoying.

> 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.

Reply on news.ycombinator.com