PgCompute: New PostgreSQL Client-Side Extension for Database Functions(yugabyte.com)
yugabyte.com
PgCompute: New PostgreSQL Client-Side Extension for Database Functions
https://www.yugabyte.com/blog/pgcompute-postgresql-extension-database-functions/
11 comments
Thank you for feedback. Sharing a bit more details to make things clearer.
Upon a function invocation, PgCompute does the following:
1. Constructs a function signature (name + arguments) and checks if the function was invoked before. (there is a deploymentTable object that tracks functions that were already sent and executed on the database: https://github.com/dmagda/pg-compute-node/blob/main/compute/...)
2. If the function already exists on the database and its implementation is NOT changed, then PgCompute just sends the function signature to the database for the execution using the `select + schema + "." + funcName + "(" + argsStr + ") statement`.
3. If the function has never been executed or its implementation is changed, then the function is first deployed/created on the database end and then executed. JS allows you to capture the function body as text and send it over whenever you like. PgCompute uses the `create or replace function " + this.#schema + "." + funcName + "()...` statemet to deploy the function.
The transitively invoked functions should work if you execute functionA first via PgCompute and then execute functionB that calls functionA. But I still need to check this.
As for the scope of supported JS APIs, presently the functions have to use the APIs that are allowed/supported by plv8.
Upon a function invocation, PgCompute does the following:
1. Constructs a function signature (name + arguments) and checks if the function was invoked before. (there is a deploymentTable object that tracks functions that were already sent and executed on the database: https://github.com/dmagda/pg-compute-node/blob/main/compute/...)
2. If the function already exists on the database and its implementation is NOT changed, then PgCompute just sends the function signature to the database for the execution using the `select + schema + "." + funcName + "(" + argsStr + ") statement`.
3. If the function has never been executed or its implementation is changed, then the function is first deployed/created on the database end and then executed. JS allows you to capture the function body as text and send it over whenever you like. PgCompute uses the `create or replace function " + this.#schema + "." + funcName + "()...` statemet to deploy the function.
The transitively invoked functions should work if you execute functionA first via PgCompute and then execute functionB that calls functionA. But I still need to check this.
As for the scope of supported JS APIs, presently the functions have to use the APIs that are allowed/supported by plv8.
Vulnerable to SQL injection since it’s wrapping string args with single. https://github.com/dmagda/pg-compute-node/blob/main/compute/...
Personally, I’d prefer to create plv8 functions out of band (in a migration) and call them explicitly using a normal DB connection. Much less to go wrong.
Personally, I’d prefer to create plv8 functions out of band (in a migration) and call them explicitly using a normal DB connection. Much less to go wrong.
Nice, thanks for catching the issue! https://github.com/dmagda/pg-compute-node/issues/5
This extension is primaraly for those who avoid using database functions at all because of not-the-best dev experience. Once your plv8 function is ready & tested, then it's totally fine to create it once and execute using a DB driver. However, while in development you might need to change the function implementation several times (or you might need to update it after going to prod) and, personally, it's easier to do this from within your IDE.
Anyway, those who prefer create functions manually can take advantage of the MANUAL deployment mode: https://github.com/dmagda/pg-compute-node/blob/main/compute/...
This extension is primaraly for those who avoid using database functions at all because of not-the-best dev experience. Once your plv8 function is ready & tested, then it's totally fine to create it once and execute using a DB driver. However, while in development you might need to change the function implementation several times (or you might need to update it after going to prod) and, personally, it's easier to do this from within your IDE.
Anyway, those who prefer create functions manually can take advantage of the MANUAL deployment mode: https://github.com/dmagda/pg-compute-node/blob/main/compute/...
You might also consider hooking into the TypeScript compiler (or just using basic codegen) and outputting .sql files containing the migration scripts.
This is incredibly awesome. When Oracle started to talk about Javascript in the database (around 2016), with GraalVM and MLE, it was clear for me that the goal was not to deploy stored procedures in Javascript but, at some point have the JS function called in the application and transparently run close to data. They will probably go there but that will take time.
I'm so happy to see this idea in Open Source with PostgreSQL! The best of both worlds: defined the business logic in the app but run it close to data for performance.
I'm so happy to see this idea in Open Source with PostgreSQL! The best of both worlds: defined the business logic in the app but run it close to data for performance.
Yeap, that was the main reason for building this client-side extension/module - so that we, developers, can execute the functions from the application source and our preferred IDEs.
[deleted]
Hey folks, I'm the one who created this extension. Looking forward to your feedback, thoughts and suggestions.
Very cool!
Are there any performance trade-offs?
Has anyone run in it production environments?
It would be fantastic to see a sample repo showing how a small web app shares part of the business logic with PG.
Are there any performance trade-offs?
Has anyone run in it production environments?
It would be fantastic to see a sample repo showing how a small web app shares part of the business logic with PG.
PgCompute uses the plv8 extension on the database end. Basically, PgCompute will be transferring the function logic to the database where plv8 will get it executed. This function transfer happens only once and then PgCompute just passes the method signature for the execution.
I think that the perf impact should be negligable but you're right that the next reasonable step is to do some perf testing.
Nope, nobody uses it in prod yet. I've just released the first version.
I think that the perf impact should be negligable but you're right that the next reasonable step is to do some perf testing.
Nope, nobody uses it in prod yet. I've just released the first version.
How does the local JS code get retrieved to be sent across the wire? I’m not a JS expert but I’m not aware of a way to get the source backing a Function object. (On mobile so can’t easily access the source)
Also, does this work with transitively invoked functions? If I pass function a to pg-compute, but that a function itself calls other functions or goes on to instantiate complex classes potentially in other modules and use large npm dependencies - is pg-compute able to transfer that entire dependency tree correctly? If so, how?
Edit: Answering my own question: it calls toString on the function and some minor string manipulation to adjust the format. So no, you can’t call any other functions or local modules (the plv8 module is presumably implicitly global server side). Very neat trick.
One suggestion I might have is to memorize whether or not you’ve registered the function so you only ever send it once. Ie register it with the hash of the source code and after the first invocation never send the function again. There’s some care that has to be done to make sure that you don’t have an ever accumulating amount of garbage of functions you’ve ever run against your database, so it’s probably not a good default but something that should be possible with guidance of how to properly set things up (eg update the timestamp for that function access once a day and then have a cleanup job that deletes functions older than some date could be one approach)