Re: WITH RECURSIVE ... CYCLE in vanilla SQL: issues with arrays of rows

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> On Tue, Oct 7, 2008 at 9:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> * Instead of the above, we could try to make
>> ROW(some columns) = ANY (array variable)
>> work. This is shorter than the above syntax and would presumably have
>> a lot less overhead too. But it doesn't work right now, not even for
>> named rowtypes much less anonymous ones.

> By extension, would this also mean things like
> select row(1,2,3)::foo = foo from foo;
> Would work (presumably as row-wise comparison does)?

Well, it turned out to be easier than I thought to get the base case
working --- all that's necessary is to define an array type for RECORD
and add generic comparison functions, and the cases that are needed for
recursive cycle detection Just Work! See attached WIP patch, and
particularly note the new test case in with.sql.

The part that actually seems to need some thought is the relationship
between this and operations on named rowtypes. In the patch I tweaked
parse_coerce.c to treat coercion from a named rowtype's array type to
record[] as an allowed binary-compatible case, but I didn't do the
other direction yet (I'm not fully convinced that it's necessary).

What I'm really not clear about is the extent to which record[] ought
to be treated like a polymorphic type --- should we consider that
it acts like an "anyrecordarray" type, or is that a distinct notion?
Do we even want that? record itself is not considered a polymorphic
type, though it has some similar qualities.

Another point worth making is that I made the comparisons work like
IS NOT DISTINCT, ie, they don't return NULL just because some field of
the row is NULL. This is *not* like SQL-spec row comparison, but we
can't build a btree opclass from these functions if they insist on
returning null for null fields. (Our array comparisons work like this,
too.) I'm not sure how big a deal that is either way, but I am pretty
sure that IS NOT DISTINCT is the semantics you need to have if you want
cycle detection to work reliably. (Hm, is that a bug in the spec?
They say to use = rather than DISTINCT in cycle checking ...)

Comments? Do we want to go forward with this?

regards, tom lane