4 ms·

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 Record

by cjauvin 11y ago

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

eeZi 11y ago
Why would I use it instead of SQLAlchemy? Simplicity?
_paulc 11y ago
Wow - this looks really really similar to my wrapper library (pgwrap [1]). See comment below [2] [1] https://github.com/paulchakravarti/pgwrap https://github.com/paulchakravarti/pgwrap [2] https://news.ycombinator.com/item?id=11054944 https://news.ycombinator.com/item?id=11054944
aschampion 11y ago
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.
- approaching 11y ago
  > 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.
  - aschampion 11y ago
    Good point -- I was talking in the context of bulk upserts but didn't make that explicit.

by cjauvin 11y ago

eeZi 11y ago

Why would I use it instead of SQLAlchemy? Simplicity?

_paulc 11y ago

Wow - this looks really really similar to my wrapper library (pgwrap [1]). See comment below [2] [1] https://github.com/paulchakravarti/pgwrap https://github.com/paulchakravarti/pgwrap [2] https://news.ycombinator.com/item?id=11054944 https://news.ycombinator.com/item?id=11054944

aschampion 11y ago

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.

approaching 11y ago
> 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.
- aschampion 11y ago
  Good point -- I was talking in the context of bulk upserts but didn't make that explicit.