Opened 5 years ago

Closed 4 years ago

#2708 closed patch (fixed)

updategeometrysrid doesn't update srid check in some cases

Reported by: igorberman Owned by: robe
Priority: medium Milestone: PostGIS 2.1.4
Component: postgis Version: 2.1.x
Keywords: history Cc:

Description

Hi, after upgrading to 9.3, and to postgis2.1 as extention(full version details at the bottom) I've noticed behaviour that updategeometrysrid doesn't update constraint if schema isn't specified

steps to reproduce: 1. CREATE TABLE test (

geom_utm geometry, CONSTRAINT enforce_srid_geom_utm CHECK (st_srid(geom_utm) = 0)

)

when I try to update srid to something <> 0, without schema parameter

select updategeometrysrid('test','geom_utm', 32630); it doesn't change the check definition, instead it inserts the check in column definition: \d test CREATE TABLE test (

geom_utm geometry(Geometry,32630), CONSTRAINT enforce_srid_geom_utm CHECK (st_srid(geom_utm) = 0)

) while 3. select updategeometrysrid('public', 'test','geom_utm', 32630); does updates the constraint \d test CREATE TABLE test (

geom_utm geometry, CONSTRAINT enforce_srid_geom_utm CHECK (st_srid(geom_utm) = 32630)

)

in both cases geometry_columns view is updated to have new srid and in both cases I get "public.test.geom_utm SRID changed to 32630" message

my default schema is 'public'(verified by 'select current_schema')

version details: "PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro? 4.8.1-10ubuntu8) 4.8.1, 64-bit POSTGIS="2.1.2 r12389" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.9.1" LIBJSON="U (...)"

Change History (4)

comment:1 Changed 5 years ago by robe

Milestone: PostGIS 2.1.3
Owner: changed from pramsey to robe

comment:2 Changed 4 years ago by marcjansen

Hi everybody,

I took a stab at this: https://github.com/postgis/postgis/pull/26

I think the original bug is a result of using schema_name (the raw passed argument) instead of the verified variable real_schema.

This is the diff

diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 3b8b40b..835f629 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -2389,7 +2389,7 @@ BEGIN
                END IF;
        END IF;
 
-       IF postgis_constraint_srid(schema_name, table_name, column_name) IS NOT NULL THEN 
+       IF postgis_constraint_srid(real_schema, table_name, column_name) IS NOT NULL THEN 
        -- srid was enforced with constraints before, keep it that way.
         -- Make up constraint name
         cname = 'enforce_srid_'  || column_name;

comment:3 Changed 4 years ago by robe

Type: defectpatch

Marc,

Thanks I'll try to take a look at this later today.

comment:4 Changed 4 years ago by robe

Keywords: history added
Resolution: fixed
Status: newclosed

Commited for 2.2 (trunk) at r12902 and for 2.1 at r12903

Note: See TracTickets for help on using tickets.