6 ms·

Show HN: PL/Swift – PostgreSQL Functions in Swift

84 points by helge5 9y ago

cyberon 9y ago
Looks interesting PoC. I have written multiple extensions for PostgreSQL in Go. The hardest part is not "glueing" together the tools to make it work. Most of the time it's much more easier and faster to just write in C (C++ with wrapper). There are many cases you should consider, like returning rows (composite types) and implementing trigger functions.
- helge5 9y ago
  I disagree that it is easier or faster to just write it in C. But C still makes a lot more sense for many other reasons. And yes, this demos only the very basics, nothing fancy. Anyways, a nice feature of Swift in this context is that Swift can directly access C APIs w/o any "wrapping". So while PL/Swift can add convenience APIs (like the ones it has to convert String's and Int's into Datum's), it can already do almost everything C can do due to that.
  - saagarjha 9y ago
    > Anyways, a nice feature of Swift in this context is that Swift can directly access C APIs w/o any "wrapping". Well, you have to write a modulemap, but I see what you mean.
alexnewman 9y ago
A different approach I wrote in rust https://github.com/posix4e/rpgffi https://github.com/posix4e/rpgffi With some example consumers: https://github.com/durch/google-bigtable-postgres-fdw https://github.com/durch/google-bigtable-postgres-fdw https://github.com/posix4e/jsoncdc https://github.com/posix4e/jsoncdc
stevefan1999 9y ago
I’d suggest the authors to change the name, I thought it was about PL/I at the first sight.
- saurik 9y ago
  This is the naming scheme for all PostgreSQL programming language extensions. https://www.postgresql.org/docs/9.1/static/server-programming.html https://www.postgresql.org/docs/9.1/static/server-programmin...
  - DrJokepu 9y ago
    Also I believe this naming pattern originated from Oracle’s PL/SQL, on which PostgreSQL’s own PL/pgSQL was based originally.
solidsnack9000 9y ago
Being able to push some application logic into the database server can be very powerful for batch operations. Often you need to make real decisions in application logic at several steps along the way. If you use an ORM-like coding style, you get orderly code but performance is terrible because (a) every row is handled in its own transaction and (b) even if it wasn’t there are multiple network round trips per row. One is thus compelled to write a kind of hybrid: insert a thousand rows, do some join, for each row that is returned, add a row to the pile of stuff to be inserted next, do that insert, and so on and so forth, for many steps. Instead of having a loop like: for data in input { step1(); step2(); ... } ...you get code that reads like: do step1 for a thousand items, insert, do step2() for a thouand items, insert... With the code running directly in the database, you can write it in a more naive, ORM like style, because calling into the database for an INSERT or SELECT is much cheaper than a network round trip.
- vosper 9y ago
  > If you use an ORM-like coding style, you get orderly code but performance is terrible because (a) every row is handled in its own transaction and (b) even if it wasn’t there are multiple network round trips per row. What ORM are you using? I've only really used SQLAlchemy, and I'm pretty sure it doesn't end up working like that. I've seen the SQL it outputs and it almost always is what I would have written (I haven't done really complicated stuff, though)
  - oneweekwonder 9y ago
    > If you use an ORM-like coding style Not the op but for me the defining word here is "coding style". Would be interesting to see if their is any serious ORM frameworks that recommends ops steps. That kind of steps you normally see in novice code, that does not understand sql or the orm framework. So for a novice a fetchall into memory, loop over each row in memory, and after each modification commit, not a issue, ignorance is bliss. And it will most probably stay a non issue until performance hits are taken, then optimization can take place. This time maybe with a senior that will look at the sql logs and show the junior the error in his ways. At least with the junior code you could move the product forward... but maybe it a case of one step forward, two step back.

84 points by helge5 9y ago

Looks interesting PoC. I have written multiple extensions for PostgreSQL in Go. The hardest part is not "glueing" together the tools to make it work. Most of the time it's much more easier and faster to just write in C (C++ with wrapper). There are many cases you should consider, like returning rows (composite types) and implementing trigger functions.

helge5 9y ago
I disagree that it is easier or faster to just write it in C. But C still makes a lot more sense for many other reasons. And yes, this demos only the very basics, nothing fancy. Anyways, a nice feature of Swift in this context is that Swift can directly access C APIs w/o any "wrapping". So while PL/Swift can add convenience APIs (like the ones it has to convert String's and Int's into Datum's), it can already do almost everything C can do due to that.
- saagarjha 9y ago
  > Anyways, a nice feature of Swift in this context is that Swift can directly access C APIs w/o any "wrapping". Well, you have to write a modulemap, but I see what you mean.

alexnewman 9y ago

A different approach I wrote in rust https://github.com/posix4e/rpgffi https://github.com/posix4e/rpgffi With some example consumers: https://github.com/durch/google-bigtable-postgres-fdw https://github.com/durch/google-bigtable-postgres-fdw https://github.com/posix4e/jsoncdc https://github.com/posix4e/jsoncdc

stevefan1999 9y ago

I’d suggest the authors to change the name, I thought it was about PL/I at the first sight.

saurik 9y ago
This is the naming scheme for all PostgreSQL programming language extensions. https://www.postgresql.org/docs/9.1/static/server-programming.html https://www.postgresql.org/docs/9.1/static/server-programmin...
- DrJokepu 9y ago
  Also I believe this naming pattern originated from Oracle’s PL/SQL, on which PostgreSQL’s own PL/pgSQL was based originally.

solidsnack9000 9y ago

Being able to push some application logic into the database server can be very powerful for batch operations. Often you need to make real decisions in application logic at several steps along the way. If you use an ORM-like coding style, you get orderly code but performance is terrible because (a) every row is handled in its own transaction and (b) even if it wasn’t there are multiple network round trips per row. One is thus compelled to write a kind of hybrid: insert a thousand rows, do some join, for each row that is returned, add a row to the pile of stuff to be inserted next, do that insert, and so on and so forth, for many steps. Instead of having a loop like: for data in input { step1(); step2(); ... } ...you get code that reads like: do step1 for a thousand items, insert, do step2() for a thouand items, insert... With the code running directly in the database, you can write it in a more naive, ORM like style, because calling into the database for an INSERT or SELECT is much cheaper than a network round trip.

vosper 9y ago
> If you use an ORM-like coding style, you get orderly code but performance is terrible because (a) every row is handled in its own transaction and (b) even if it wasn’t there are multiple network round trips per row. What ORM are you using? I've only really used SQLAlchemy, and I'm pretty sure it doesn't end up working like that. I've seen the SQL it outputs and it almost always is what I would have written (I haven't done really complicated stuff, though)
- oneweekwonder 9y ago
  > If you use an ORM-like coding style Not the op but for me the defining word here is "coding style". Would be interesting to see if their is any serious ORM frameworks that recommends ops steps. That kind of steps you normally see in novice code, that does not understand sql or the orm framework. So for a novice a fetchall into memory, loop over each row in memory, and after each modification commit, not a issue, ignorance is bliss. And it will most probably stay a non issue until performance hits are taken, then optimization can take place. This time maybe with a senior that will look at the sql logs and show the junior the error in his ways. At least with the junior code you could move the product forward... but maybe it a case of one step forward, two step back.