Re: GSoC 2017: Foreign Key Arrays

On Mon, Jun 26, 2017 at 2:26 AM, Mark Rofail <markm(dot)rofail(at)gmail(dot)com> wrote:

> *What I did:*
>
>
>
> - read into the old patch but couldn't apply it since it's quite old.
> It needs to be rebased and that's what I am working on. It's a lot of
> work.
> - incomplete patch can be found attached here
>
> Have you met any particular problem here? Or is it just a lot of
mechanical work?

*Bugs*
>
> - problem with the @>(anyarray, anyelement) opertator: if for example,
> you apply the operator as follows '{AAAAAAAAAA646'}' @> 'AAAAAAAAAA646' it
> maps to @>(anyarray, anyarray) since 'AAAAAAAAAA646' is interpreted as
> char[] instead of Text
>
> I don't think it is bug. When types are not specified explicitly, then
optimizer do its best on guessing them. Sometimes results are
counterintuitive to user. But that is not bug, it's probably a room for
improvement. And I don't think this improvement should be subject of this
GSoC. Anyway, array FK code should use explicit type cast, and then you
wouldn't meet this problem.

On the other hand, you could just choose another operator name for
arraycontainselem.
Then such problem probably wouldn't occur.

*Suggestion:*
>
> - since I needed to check if the Datum was null and its type, I had to
> do it in the arraycontainselem and pass it as a parameter to the underlying
> function array_contains_elem. I'm proposing to introduce a new struct like
> ArrayType, but ElementType along all with brand new MACROs to make dealing
> with anyelement easier in any polymorphic context.
>
> You don't need to do explicit check for nulls, because arraycontainselem
is marked as strict function. Executor never pass null inputs to your
function if its declared as strict. See evaluate_function().
Also, during query planning it's checked that all polymorphic are
consistent between each other. See
https://www.postgresql.org/docs/devel/static/extend-type-system.html#extend-types-polymorphic
and check_generic_type_consistency() for details.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company