PL/Lua: Lua as a loadable procedural language for Postgres | Hacker News Reader

PL/Lua: Lua as a loadable procedural language for Postgres

github.com

119 pointstosh23 comments

Open articleView on HN

Except don’t use this one.

It’s buggy and forgotten.

Use:

https://github.com/pllua/pllua-ng

Rewritten, much better and actually updated.

If you want a modern version of this, you should checkout https://github.com/RhodiumToad/pllua-ng

A friend of mine benchmarked different pl languages and https://github.com/eugwne/pllj was basically the fastest, but it's not so actively maintained. pllua-ng with luajit 2.1 was also faster than plv8. But that was about two years ago, your milage may vary.

"Lua is faster - PL/V8 is lightly faster than Python, but Lua is about 30% faster than PL/V8" - Pavel Stěhule and that was without luajit, but 4 years ago.

Maybe someone should build a benchmark :D

These are both up to date at the moment, but it's not entirely clear which one of these will be used for future development:

https://github.com/RhodiumToad/pllua-ng https://github.com/pllua/pllua-ng

plv8 has sped up significantly over the last 4 years, but still has an expensive startup. I'd love to see some benchmarks.

Looking at these great PL* languages makes me wonder if it would be feasible to use something like GraalVM to write PL functions in R/Java/JS/etc.

Oracle DB supports this already (it seems, I've not tried it): https://www.graalvm.org/docs/examples/mle-oracle/ ) - and the advantage of Graal is it supports a wide range of languages and can output native binaries.

There's already pljava (https://github.com/tada/pljava) for java....we use it extensively where I work.

How much fun is that?

We use PLv8 with AWS RDS Postgres. It's super up-to-date amazingly and supports most of the latest ES syntax.

For interest, here a post that show a quick comparison of PLPGSQL, PLLua & PLV8 https://www.newfies-dialer.org/using-lua-in-postgresql-pllua...

I quickly redid the benchmarks: https://gist.github.com/xvaara/df0b802b86d2aa4f30b88a3fd6b69...

This looks really cool--reminds me of the capabilities of Redis with Lua scripts. Does anyone know how it compares to PLV8 (https://github.com/plv8/plv8)?

Or Tarantool.

This is neat. Anyone know of any functional languages that would integrate with Postgres?

Scala and Clojure should run under pljava. Probably not a lot of people doing that though!

On a game I worked on, we used little snippets of lua in our UI for both labels and property values (as well as our scripting language). It was very akin to working with something like HTML+JS. Super convenient.

That's interesting. For somebody who's not all that familiar with Lua, what are it's main selling points for something like this?

1. Lua is simple.

There is only one container type: the table. There is only one number type. (Since 5.3 Lua internally uses 2: ints and doubles, but that’s an implementation detail.) etc.

2. Lua is easy to integrate.

3. It’s easy to set up a sandboxed environment with Lua.

4. Lua is surprisingly fast, especially considering it is interpreted and dynamic.

The PLs (Procedural Languages) in PostgreSQL let you write stored procedures (i.e. functions) that can be called on their own or via a trigger (e.g. when a row is inserted or updated).

For instance, if you had a field that you wanted to be the output of some complex expression, algorithm, &c, you can write it as a function. For instance, something like Greatest Common Divisor (GCD) or geometry functions (e.g. intersection of two geometries, the union or intersection of two geometries, &c). Another example is custom aggregate functions.

This just lets you do it in Lua, if that's your thing. I think by default there is PL/pgSQL, PL/Tcl, PL/Perl , and PL/Python. There are plugins for a variety of other languages (e.g. ruby, java lua, R). I actually thought Lua became a default recently, but maybe they're still talking about it.

Yes. PL/pgSQL is the default AFAIK (been away from active use of Postgres for a few years). PL/pgSQL is basically the same as Oracle's PL/SQL, which has a very Pascal-like syntax.

I played around with PL/Lua 5 or 6 years ago, and liked it, but the project/idea got sidelined for other reasons so I never got very deep into it.

If you take this idea a lot farther - imagine your entire application — frontend, backend, web-service, views — can run as a stored procedure inside the database, and thus load data so fast that it opens up new architectural possibilities beyond client/server. That sounds like crazyland for people from the postgres/rest mindset, but it starts to become possible when you have a simple and portable language that runs in many places. http://www.dustingetz.com/:stu-halloway-datomic-ions-in-seve...

Stored Proc biz-logic architecture was a big thing in the 2000s. MSSQL with its T-SQL were very common (and its polymorphic AND multi-table return types, could make for some very interesting control flows sometimes). There was a lot of bitterness about it back in the late 00's and early 10's, and it drove a lot of ORM adoption.

I worked for a shop in the late 00's and early 10's that had Very Big Database Servers doing several hundred million transactions per second (100+ core, multiple RAMSan, etc. type bare metal servers), and stored procs were the way the go. They were basically the interface layer to the data, and it allowed us to keep the interface (or even version it) but change how access happened behind the scenes, like splitting a table into partitions, or replacing a bad performing query with a differently structured one that did better, which made it a non-concern for the frontend code that was responsible for getting data and formatting it, and didn't really care about _how_ that data was retrieved.

I have been pushing a pet idea that we should leverage our RDBMSes as platforms more than we do. Pretty frequently the model layer in MVCs end up with some functionality that would be much more robust inside the database. Validation ends up being done (at least) twice, query results end up being quite distant from objects, transactions which should be one are split, etc. Problems that could be mitigated with user defined procedures in the database.

I think it might be the current state of version control tooling that's in the way here. It can be a tricky and often fairly manual process to sync database changes (even just running the migrate command) when releases are tied to git repos.

That's not to say git repos aren't wonderful, but I see underutilization of database functionality as a subtle downside.

I don't have to imagine. I was paid for a while to work on Oracle Application Express apps.

There were things that were neat. But a lot of it came from the head-scratch-inducing genus of genius.

Reply on news.ycombinator.com