How do we support FULL JOIN on PostGIS types?

Hi,

Thanks a lot RhodiumToad on IRC for suggestion of setting HASHES, MERGES on
OPERATOR =.

Now we have other problem: how do we set these flags on upgrade to new
version of extension? Dropping an OPERATOR = will drop all indexes an views
depending on it so isn't really an option.

Also, if someone can sneak "ERROR: FULL JOIN is only supported with
merge-joinable or hash-joinable join conditions" keywords into
https://www.postgresql.org/docs/current/xoper-optimization.html#id-1.8.3.17.8
it would greatly help future extension writers - it's not possible to
google this page out by the error message.

On Thu, May 16, 2019 at 7:05 PM Darafei "Komяpa" Praliaskouski <
me@komzpa.net> wrote:

Hi!

Greetings from OSGeo Code sprint in Minneapolis :)

We're trying to make FULL JOIN on equality of geometry and can't figure
out why it doesn't work.

Here's reproducer, it works on bytea but not on PostGIS geometry throwing
out

ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable
join conditions

https://trac.osgeo.org/postgis/ticket/4394

We already have a btree opclass with equality:

https://github.com/postgis/postgis/blob/svn-trunk/postgis/postgis.sql.in#L420

We also have hash equality opclass:

https://github.com/postgis/postgis/blob/svn-trunk/postgis/postgis.sql.in#L440

Reading through Postgres documentation I can't figure out what else shall
we do for this join to work. How do we make it work?

--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa

--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa

=?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?= <me@komzpa.net> writes:

Thanks a lot RhodiumToad on IRC for suggestion of setting HASHES, MERGES on
OPERATOR =.
Now we have other problem: how do we set these flags on upgrade to new
version of extension? Dropping an OPERATOR = will drop all indexes an views
depending on it so isn't really an option.

I think you're going to have to use a direct UPDATE on pg_operator in
the extension update script :-(. Perhaps ALTER OPERATOR should be able
to handle changing these flags, but for now it can't.

regards, tom lane