Thread: Background worker plus language handler for Andl: OK?

Background worker plus language handler for Andl: OK?

From
Date:
It seems to me that it should be possible to create a language front end for
Andl by creating a background worker and a language handler. The BGW will
need to implement the Andl language and a listener, and submit generated SQL
to SPI. The PL will get called by query functions and pass them to the BGW
(via a callback) for execution. AFAIK the BGW and the PL run in the same
process, and this should work.

Apart from reading the documentation (I have), reading source code
(worker_spi.c) and heeding warnings about the risk of breaking the server,
is there any reason why this would not work? There is a fair bit of work,
and it would be nice to know what to watch out for.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org




Re: Background worker plus language handler for Andl: OK?

From
David Wilson
Date:
On Mon, Apr 25, 2016 at 03:18:30PM +1000, david@andl.org wrote:

> It seems to me that it should be possible to create a language front
> end for Andl by creating a background worker and a language handler.

I've been reading your posts over the past few days and while I find it
fun to follow, I can't help but wonder why there there is urgency in
reimplementing a protocol within PG itself.

It seems to me this is a much larger undertaking than you realize, for
example, you would at least need to reinvent PG's existing
authentication and authorization mechanisms, or perhaps patch PG somehow
to expose them usefully to your own code.

Is there a hard requirement that this stuff be in-process? Most of the
cost of a SQL query will be lost in planning and execution, the actual
time spent copying some strings around and context switching will be
pretty minimal for a query of any significance.

If I were you I'd start with building a robust proxy server first,
serving up your custom protocol and rewriting it to a PG client
connection internally, and only then look at how that might be merged
in-proess if indeed there was a real need for it.


David


Re: Background worker plus language handler for Andl: OK?

From
Date:
> owner@postgresql.org] On Behalf Of David Wilson

> I've been reading your posts over the past few days and while I find it
fun
> to follow, I can't help but wonder why there there is urgency in
> reimplementing a protocol within PG itself.

I think it's an interesting problem -- glad you find it so.

No, I don't plan to implement any more protocols. The problem here is
callbacks, and probably transaction boundaries.

Andl is designed to be a relational language filling a similar niche to SQL
with PLSQL or SQL/PSM. It contains a full implementation of the relational
algebra, but is also a general purpose programming language. [The code is
all compiled and the front end is RPC, nothing like libpq or ODBC.]

A query is a relational expression and may evaluate arbitrary expressions.
Example:

// Q8. Get all shipments where the quantity is in the range 300 to 750
inclusive.
// SQL> select spj.* from spj where spj.QTY>=300 and spj.QTY<=750;
Andl: SPJ .where(QTY>=300 and QTY<=750)

The JOIN can be generated as SQL but the where predicate requires a callback
into the Andl runtime. I would be quite happy to run queries through libql,
but I can see no way to handle callbacks without running in-process.

[Yes, in some cases the query planner will replace this by an operation on
an index, but this is about the general case.]

I have it working as a PL extension, but then the entire query has to be
embedded inside a PL function call which is (a) messy (b) cannot manage
transaction boundaries.

> It seems to me this is a much larger undertaking than you realize, for
> example, you would at least need to reinvent PG's existing authentication
and
> authorization mechanisms, or perhaps patch PG somehow to expose them
usefully
> to your own code.

It's a useful point, but I'm not sure it applies. Andl is not intended to
have an SQL-like execution or security model. Perhaps this is something that
needs some more thought, but it's unlikely to be a critical factor.

> Is there a hard requirement that this stuff be in-process? Most of the
cost
> of a SQL query will be lost in planning and execution, the actual time
spent
> copying some strings around and context switching will be pretty minimal
for
> a query of any significance.

See above. The cost of setting up the query is trivial compared to the cost
of a callback every time an expression is evaluated, perhaps many times per
row.
>
> If I were you I'd start with building a robust proxy server first, serving
up
> your custom protocol and rewriting it to a PG client connection
internally,
> and only then look at how that might be merged in-proess if indeed there
was
> a real need for it.

If there really is another way to go, I'm happy to hear about it. I think
this is not a job for a proxy server -- the external interface for Andl is
RPC, not shipping query text around. That's all working -- it's the backend
I need.


Regards
David M Bennett FACS

Andl - A New Database Language - andl.org