
Show HN: PL/Swift – PostgreSQL Functions in Swift - helge5
http://www.alwaysrightinstitute.com/plswift/
======
alexnewman
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)

------
solidsnack9000
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
> 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)

~~~
kbenson
There are ways to mitigate this if your ORM is good. I made the poor decision
of moving to a much more powerful but geographically distant DB a while back,
and while some operations benefit greatly from the extra hardware and memory,
you _really_ learn where your inefficiencies are when there's a 50 millisecond
round trip involved and you find you're hitting the DB a few hundred or
thousand times for this task.

Pre-fetching relations is amazingly useful in that context, as are alternate
input methods that allow multiple inserts in a single glue opening (but be
prepared to divide.and conquer if one or more items might fail to insert, such
as when a unique key has a duplicate). Updates are generally the biggest
problem, but if you're lucky you can reduce those by grouping similar updates
into single update statements.

Many of these have more specific solutions depending on the particular RDBMS
you are using, but ORM support for those features is generally poor. If you're
lucky your ORM is extensible enough that you can hack some in without too much
effort and while being fairly clean.

~~~
AlphaSite
From what I remember batch operations are a part of the JDBC core.

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

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

~~~
saurik
This is the naming scheme for all PostgreSQL programming language extensions.

[https://www.postgresql.org/docs/9.1/static/server-
programmin...](https://www.postgresql.org/docs/9.1/static/server-
programming.html)

~~~
DrJokepu
Also I believe this naming pattern originated from Oracle’s PL/SQL, on which
PostgreSQL’s own PL/pgSQL was based originally.

