typmod geometry type not changing in before trigger

[robe]

This is the report posted on: ​http://www.postgis.org/pipermail/postgis-users/2011-November/031493.html

I can reproduce on 8.4 and 9.1 running latest build of PostGIS 2.0.

To reproduce:

CREATE TABLE A(gid serial primary key, geom geometry(MultiPolygon, 23030));

CREATE OR REPLACE FUNCTION public.triga() RETURNS trigger AS
$$
BEGIN
NEW.geom = ST_GeometryN(New.geom,1);
RETURN NEW;
END;
$$ language plpgsql
VOLATILE;


CREATE TRIGGER triga_before
  BEFORE INSERT
  ON public.a
  FOR EACH ROW
  EXECUTE PROCEDURE public.triga();

-- this allows  a polygon which should not be legal --
INSERT INTO a(geom) VALUES('SRID=23030;MULTIPOLYGON (((0 0, 10 0, 10 10, 0 0)))'::geometry);

-- st_AsText POLYGON((0 0,10 0,10 10,0 0))

Geography works as expected -see next 2 notes.

[pramsey]

How about w/ varchar?

[robe]

Paul,

Sorry I've been testing two things. One is a bonafied bug in PostGIS because it works fine for geography. I got my tests a bit mixed up. The other is another demonstration of a use case that typmod doesn't work for.

So here is the geography equivalent and it does as expected.

CREATE TABLE b(gid serial primary key, geog geography(MultiPolygon, 4326));
CREATE OR REPLACE FUNCTION public.trigb() RETURNS trigger AS
$$
BEGIN
NEW.geog = ST_GeometryN(New.geog::geometry,1)::geography;
RETURN NEW;
END;
$$ language plpgsql
VOLATILE;


CREATE TRIGGER trigb_before
  BEFORE INSERT
  ON public.b
  FOR EACH ROW
  EXECUTE PROCEDURE public.trigb();

INSERT INTO b(geog) VALUES('MULTIPOLYGON (((0 0, 10 0, 10 10, 0 0)))'::geography);

throws this error — which is the right thing to do:

ERROR:  Geometry type (Polygon) does not match column type (MultiPolygon)
CONTEXT:  PL/pgSQL function "trigb" line 2 at assignment

geometry typmod seems to be missing this secondary check. The other issue which I think is the one bothering the poster is something there by design. I'll test with varchar on that to confirm and that is that PostgreSQL does a signature check to make sure what you are trying to stuff in a table can go there before it even gets to the trigger.

[strk]

It looks like the big difference here is the use of a cast. See debugging lines. Here's the geometry case:

NOTICE:  [gserialized_typmod.c:postgis_valid_typmod:124] Entered function
NOTICE:  [gserialized_typmod.c:postgis_valid_typmod:129] Got lwgeom(type = 6, srid = 4326, hasz = 0, hasm = 0)
NOTICE:  [gserialized_typmod.c:postgis_valid_typmod:130] Got typmod(type = 6, srid = 4326, hasz = 0, hasm = 0)

Here's the geography case:

OTICE:  [gserialized_typmod.c:postgis_valid_typmod:124] Entered function
NOTICE:  [gserialized_typmod.c:postgis_valid_typmod:129] Got lwgeom(type = 6, srid = 4326, hasz = 0, hasm = 0)
NOTICE:  [gserialized_typmod.c:postgis_valid_typmod:130] Got typmod(type = 6, srid = 4326, hasz = 0, hasm = 0)
NOTICE:  [gserialized_typmod.c:postgis_valid_typmod:124] Entered function
NOTICE:  [gserialized_typmod.c:postgis_valid_typmod:129] Got lwgeom(type = 3, srid = 4326, hasz = 0, hasm = 0)
NOTICE:  [gserialized_typmod.c:postgis_valid_typmod:130] Got typmod(type = 6, srid = 4326, hasz = 0, hasm = 0)

So the first part is the same for both (no mismatch) while for geography there's a second step going on in which lwgeom becomes visibly different from expected typmod.

[strk]

Found: LWGEOM_in is missing typmod handling (geography_in has it). Will commit the fix togheter with regression testing.

[strk]

Interesting enough the input function is not always called with 3 arguments, but sometimes only with 1 (typmod is 3rd, when given). Since I discovered this I'm also fixing it for geography which is assuming it'll always get a 3rd typmod.

[strk]

r8431 fixes this case.

I've found the culprit for calls to LWGEOM_in w/out 3rd argument:

lwgeom_inout.c:450:     result = DirectFunctionCall1(LWGEOM_in, CStringGetDatum(wkt));

which is Datum parse_WKT_lwgeom(PG_FUNCTION_ARGS)

It would be hard to get a similar situation for geography, anyway better safe than sorry, so the geography_in support for such calls is in r8432