In case, anyone is curious what that might look like, I have a 30-line Python code here (TOTP generation only, no verification): https://github.com/susam/mintotp. Indeed the core function (the HOTP function) contains only 6 lines, thanks to Python's extensive standard library.
Why? Implementation-wise, it's far better to have a pure plpgsql function than a Python UDF if you have a choice between the two. A Python UDF would be useful if you need to do more Python stuff in the db in general.
agree, this is exactly why the code was written. I had originally tried to use plv8 or others, but as my postgres experience matured and I built testing harnesses, I decided to throw away any non-standard code (plv8, etc).
I think often times folks who come to postgres often don't know PL/pgsql (as I also didn't) and prefer to user their language of choice. I truly think if our postgres dev environments were better, we'd find more people writing code this way, so I can empathize with why people would prefer python or JS in the db, but at this stage I can't get myself to use those extensions.
FWIW, at one point, AWS didn't support a lot of these (sometimes unsafe) language extensions. So going pure PL/pgsql was the only true path forward. I think now for AWS users, they do support plv8 now, but as you pointed out somewhere in this thread, they are much slower... also I remember plv8 specifically had memory leaks.
Maybe this is the case in general, but a while back we did something that required flipping an array and when we did it with python inside Postgres it was several magnitudes faster.
This would make for a super interesting blog post / case study IMO, you should write about it even if it's just to showcase the two algorithms / comparisons.
Sorry, but late back but I’ve just found the code.
It’s a bit of a weird case where I needed to reverse arrays inside a big json blob (millions of big json blobs). Trying to explode the structures out and rebuild them was a bit of a no-go. Instead I did a nasty little regex replace on the json string:
There might be a way of doing this in postgres these days (this was 3 or 4 years ago), but I'm not sure. At the time this was the best I could come up with and I was pretty surprised it worked as well as it did.
Since then I've always installed the python extension whenever I set up a new postgres, just in case.
for sure there are all sorts of postgres regex tools... You can likely do everything. The main limitations I found in plpgsql were in base encodings (that were missing), which is why I had to implement base32 from scratch. Besides that, it's pretty much a super powerful language
Comments
The author's main SQL code seems to be in this file: https://github.com/pyramation/totp/blob/master/packages/totp...
For comparison, these are my relatively short TOTP implementations in {TypeScript, Python, Java, Rust, C++}: https://www.nayuki.io/page/time-based-one-time-password-tool... . I even have a 6-line Python function.
The file you're pointing to is not the full extension, here it is:
https://github.com/pyramation/totp/blob/master/packages/totp...
In case, anyone is curious what that might look like, I have a 30-line Python code here (TOTP generation only, no verification): https://github.com/susam/mintotp. Indeed the core function (the HOTP function) contains only 6 lines, thanks to Python's extensive standard library.
Cool. If I had to choose I'd wrap the simple py implementation in a PL/Python UDF.
Why? Implementation-wise, it's far better to have a pure plpgsql function than a Python UDF if you have a choice between the two. A Python UDF would be useful if you need to do more Python stuff in the db in general.
agree, this is exactly why the code was written. I had originally tried to use plv8 or others, but as my postgres experience matured and I built testing harnesses, I decided to throw away any non-standard code (plv8, etc).
I think often times folks who come to postgres often don't know PL/pgsql (as I also didn't) and prefer to user their language of choice. I truly think if our postgres dev environments were better, we'd find more people writing code this way, so I can empathize with why people would prefer python or JS in the db, but at this stage I can't get myself to use those extensions.
FWIW, at one point, AWS didn't support a lot of these (sometimes unsafe) language extensions. So going pure PL/pgsql was the only true path forward. I think now for AWS users, they do support plv8 now, but as you pointed out somewhere in this thread, they are much slower... also I remember plv8 specifically had memory leaks.
I mean, why though? I haven't played around much with other languages within Postgres, and mainly stick to pl/pgsql, so real question.
Is it performance related? Not requiring another language to be installed?
Yes and yes; all other things equal, performance will be a lot worse when using Python. And you'll need Python installed.
Maybe this is the case in general, but a while back we did something that required flipping an array and when we did it with python inside Postgres it was several magnitudes faster.
This would make for a super interesting blog post / case study IMO, you should write about it even if it's just to showcase the two algorithms / comparisons.
Sorry, but late back but I’ve just found the code.
It’s a bit of a weird case where I needed to reverse arrays inside a big json blob (millions of big json blobs). Trying to explode the structures out and rebuild them was a bit of a no-go. Instead I did a nasty little regex replace on the json string:
There might be a way of doing this in postgres these days (this was 3 or 4 years ago), but I'm not sure. At the time this was the best I could come up with and I was pretty surprised it worked as well as it did.Since then I've always installed the python extension whenever I set up a new postgres, just in case.
for sure there are all sorts of postgres regex tools... You can likely do everything. The main limitations I found in plpgsql were in base encodings (that were missing), which is why I had to implement base32 from scratch. Besides that, it's pretty much a super powerful language