Hacker News Reader

Comment by cjauvin | Hacker News Reader

I had planned to "show it to HN" soon, but the occasion is too good now: I have also created a thin "psycopg2 wrapper", pretty much in the same spirit as Records (but different in purpose), which allows you to perform SQL operations with plain data structures:

https://github.com/cjauvin/little_pger

I have been using this for myself for quite some time, but feedback would be very appreciated!

Edit: A cool thing about it (I believe) is that it provides an "upsert wrapper" which makes use of either the new PG 9.5 "on conclict" mechanism if it's available, or an "update if exists, insert if not" two-step fallback, for previous versions.

View on HN

Neat library. Just to note that this approach to fake upsert is not safe to concurrent sessions. There were exactly two concurrency-safe ways to implement upsert prior to 9.5:

1. An exclusive lock on the table, which is slow as hell 2. A PGPSQL function that attempts to INSERT, catches the duplicate key exception and instead UPDATEs (also handling the case that the row vanishes), which is slower than hell

This is why ON CONFLICT is such a huge deal. If it were as simple as checking keys with a SELECT then sending some to INSERT and some to UPDATE it would just be nice syntax sugar.

> 2. A PGPSQL function that attempts to INSERT, catches the duplicate key exception and instead UPDATEs (also handling the case that the row vanishes), which is slower than hell

That's not true, at least not when you're operating on a single row at a time. According to my benchmarks the function approach was usually within 10% of the native upsert -- even outperforming it by around 5% depending on data distribution. For insert-or-select (instead of update) the function consistently outperformed INSERT ... IGNORE.

Good point -- I was talking in the context of bulk upserts but didn't make that explicit.

Wow - this looks really really similar to my wrapper library (pgwrap [1]). See comment below [2]

[1] https://github.com/paulchakravarti/pgwrap [2] https://news.ycombinator.com/item?id=11054944

Why would I use it instead of SQLAlchemy? Simplicity?

Reply on news.ycombinator.com