Re: btree_gin, bigint and number literals

Hello Tom,

I see that you have commited a change (e2b64fcef35f70f96fa92db56fbfa9ac2da136c7) which addresses this issue. Thank
you!

I looked into this issue recently and still don't understand why this would not work for other hardware variants. Will
ityield the wrong plan, or will the plan's execution yield wrong results?
 

I'm surprised because the SQL changes I proposed seemed relatively aligned with the existing extension source code
whichreferences support functions defined in code related to btree indexes (e.g. btint2cmp). These functions are
alreadyhardware-independent. Aren't these functions the ones called by the engine when executing a query and going
throughthe index - as explained here (https://www.postgresql.org/docs/18/xindex.html#XINDEX-OPFAMILY)?
 

Also there a specific reason the integer-related operator classes defined in btree_gin (int2_ops, int4_ops, int8_ops)
don'tbelong to the same operator family? That seems to be the direction suggested by the documentation I linked to
above.

Finally, what is your approach to testing on 32-bit or big-endian hardware?

Thank you for your guidance,

Quentin


On Fri, Jan 31, 2025, at 21:42, Tom Lane wrote:
> "Quentin de Metz" <quentin@de.me.tz> writes:
>> On a multi-column GIN index over a bigint column and a text column, the query planner does not filter the index on
thebigint column when a condition on this column is specified with a number literal.
 
>
> Yeah, because "owner_id = 12" will use int84eq, which as you observe
> is not supported by btree_gin's opclass.
>
>> Would you be open to considering a patch to include the ALTER OPERATOR snippet in the btree_gin install script, so
thatthis works out of the box?
 
>
> I'd be quite surprised if that "just works" without any corresponding
> changes in the C code, because btree_gin.c only knows about applying
> same-type-on-both-sides comparison functions.  (int8 vs int4 might
> appear to work as long as you don't try very hard, but for example
> it'd fail on 32-bit or big-endian hardware.)  If you feel like writing
> a patch that actually takes care of the matter fully, step right up.
>
>             regards, tom lane