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

> 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