How do we support FULL JOIN on PostGIS types?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

How do we support FULL JOIN on PostGIS types?

Darafei "Komяpa" Praliaskouski
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


We already have a btree opclass with equality: 
 
We also have hash equality opclass:

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
Reply | Threaded
Open this post in threaded view
|

Re: How do we support FULL JOIN on PostGIS types?

Darafei "Komяpa" Praliaskouski
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 <[hidden email]> 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


We already have a btree opclass with equality: 
 
We also have hash equality opclass:

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


--
Darafei Praliaskouski
Reply | Threaded
Open this post in threaded view
|

Re: How do we support FULL JOIN on PostGIS types?

Tom Lane-2
=?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?= <[hidden email]> 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