Обсуждение: How do we support FULL JOIN on PostGIS types?
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.
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?
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.
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 outERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditionsWe 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
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