[postgis-devel] [postgis-tickets] r17049 - Replace pg_constraint.consrc for pg_get_constraintdef fix for topology

[Regina Obe]

Subqueries like this are fine.  They don't impact performance like CTEs do since the join filter I believe is applied first before the computation of the function call when the function call is not involved in the join.

This change was needed because consrc was taken out in PostgreSQL 12 since it's been long deprecated.  The new way is to use pg_get_constraintdef(oid) which has existed since at least PostgreSQL 9.4.  I have to double-check if it existed in 9.3, but probably doesn't matter since I think we took out support for 9.3 in 2.5, and I'm only going to back-port this to 2.5, since other versions don't have a chance in hell of supporting 12devel.

The reason I did it this way is I wanted it consistent with the other changes I did in other areas of postgis and raster 
which use consrc more than once.
Those would have required a ton more changes and WOULD impact performance if I didn't take the subquery approach.



> -----Original Message-----
> From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf
> Of Sandro Santilli
> Sent: Thursday, November 22, 2018 7:11 AM
> To: postgis-devel at lists.osgeo.org
> Subject: Re: [postgis-devel] [postgis-tickets] r17049 - Replace
> pg_constraint.consrc for pg_get_constraintdef fix for topology
> 
> On Wed, Nov 21, 2018 at 11:33:52AM +0000, Regina Obe wrote:
> > Author: robe
> > Date: 2018-11-21 23:33:52 -0800 (Wed, 21 Nov 2018) New Revision: 17049
> >
> > Modified:
> >    trunk/topology/topology.sql.in
> > Log:
> > Replace pg_constraint.consrc for pg_get_constraintdef  fix for
> > topology References #4231 for PostGIS 3.0.0
> >
> > Modified: trunk/topology/topology.sql.in
> >
> =================================================================
> ==
> > --- trunk/topology/topology.sql.in	2018-11-22 06:46:54 UTC (rev 17048)
> > +++ trunk/topology/topology.sql.in	2018-11-22 07:33:52 UTC (rev 17049)
> > @@ -829,7 +829,8 @@
> >      --c.consrc src,
> >      regexp_matches(c.consrc,
> >        '\.topology_id = (\d+).*\.layer_id = (\d+).*\.type = (\d+)')
> > inf
> > -  FROM pg_constraint c, pg_class r, pg_namespace n
> > +  FROM (SELECT conname, connamespace, conrelid, conkey,
> pg_get_constraintdef(oid) As consrc
> > +		    FROM pg_constraint) AS c, pg_class r, pg_namespace n
> >    WHERE c.conname LIKE 'check_topogeom_%'
> >      AND r.oid = c.conrelid
> >      AND n.oid = r.relnamespace
> 
> This change looks suspicious to me.
> I usually try to avoid subqueries, what's the rationale for that ?
> 
> --strk;
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel