Re: Imperative Query Languages

On Wed, Jul 5, 2017 at 7:22 AM, Jason Dusek <jason(dot)dusek(at)gmail(dot)com> wrote:

> Hi All,
>
> This more of a general interest than specifically Postgres question. Are
> there any “semi-imperative” query languages that have been tried in the
> past? I’m imagining a language where something like this:
>
> for employee in employees:
> for department in department:
> if employee.department == department.department and
> department.name == "infosec":
> yield employee.employee, employee.name, employee.location, employee.favorite_drink
>
> would be planned and executed like this:
>
> SELECT employee.employee, employee.name, employee.location, employee.favorite_drink
> FROM employee JOIN department USING (department)
> WHERE department.name == "infosec"
>
> The only language I can think of that is vaguely like this is Fortress, in
> that it attempts to emulate pseudocode and Fortran very closely while being
> fundamentally a dataflow language.
>
Having done a lot of SQL optimisation stuff I have doubts that this is
possible. The problem is that it is much easier to go from a declarative
to an imperative plan than it is to go the other way. In fact sometimes we
use SQL the way your first code works and then it is often a problem.

For example, consider the difference between an EXISTS and an IN query, or
between an INNER JOIN and a LATERAL JOIN. PostgreSQL's optimiser is
amazing at identifying cases where these are equivalent and planning
accordingly, but it is extremely easy to get just outside the envelope
where the optimiser gives up and has to default back to an imperative
interpretation of these. Proving that two imperative approaches are
equivalent is a lot harder than proving that two different imperative
approaches implement the same declarative request. In other words, going
imperative -> declarative strikes me as a far, far harder problem than the
other way.

Also I have done a little bit of work on Apache Spark and there it is
extremely important to understand the imperative side of the data flow in
that case (what is partitioned and what is not).

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more