Re: Proposing WITH ITERATIVE

On Mon, Apr 27, 2020 at 8:50 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> Hi,
>

Hey, Jeff. Long time no talk. Good to see you're still on here.

You might get better feedback in a month or so; right now we just got
> into feature freeze.
>

Yep. No hurry. I've just been playing with this and wanted to start getting
feedback. It's a side-project for me anyway, so time is limited.

> Can you illustrate with some examples? I get that one is appending and
> the other is modifying in-place, but how does this end up looking in
> the query language?
>

I'm putting together a few concrete real-world examples.

> Rather than stopping when no new tuples are generated, WITH ITERATIVE
> > stops when a user-defined predicate evaluates to true.
>
> Why stop when it evaluates to true, and not false?
>

It's how they implemented it. A few other databases have implemented
similar functionality but, as it's not standard, it's kinda just up to each
implementor. I'm not married to that idea, but it has worked well for me so
far.

It seems like the benefit comes from carrying information along within
> tuples (by adding to scores or counters) rather than appending tuples.
> Is it possible to achieve this in other ways? The recursive CTE
> implementation is a very direct implementation of the standard, perhaps
> there are smarter approaches?
>

Yeah, in that specific case, one of the other implementations seems to
carry the counters along in the executor itself. But, as not all uses of
this functionality are iteration-count-based, I think that's a little
limiting. Using a terminator expression (of some kind) seems most
adaptable, I think. I'll give some examples of both types of cases.

--
Jonah H. Harris