Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#2511 closed defect (fixed)

geometry_columns doesn't support materialized views or foreign tables

Reported by: pramsey Owned by: pramsey
Priority: medium Milestone: PostGIS 2.0.5
Component: postgis Version: 2.0.x
Keywords: history Cc:

Change History (9)

comment:1 Changed 8 years ago by pramsey

Looks like 9.3 adds a new relkind for materialized views, 'm', so we need to add "OR c.relkind = 'm'::"char"" to the geometry columns view generator.

comment:2 Changed 8 years ago by pramsey

Resolution: fixed
Status: newclosed

Fixed in 2.1 at r12039

Fixed in trunk at r12040

Fixed in 2.0 at r12041

comment:3 Changed 8 years ago by robe

You actually didn't need to do it in 2.0 since we don't support 9.3 in 2.0 anyway, but I guess it doesn't hurt anything.

comment:4 Changed 8 years ago by robe

Do we support Foreign Tables. I didn't check. Now that PostgreSQL 9.3 has packaged a PostgreSQL foreign data wrapper, it's quite possible we'll be seeing postgresql foreign tables with postgis geometry columns

comment:5 Changed 8 years ago by robe

Resolution: fixed
Status: closedreopened
Summary: geometry_columns doesn't support materialized viewsgeometry_columns doesn't support materialized views or foreign tables

It appears we don't. I just tried this exercise http://www.postgresonline.com/journal/archives/294-PostgreSQL-9.3-postgres_fdw-a-Test-Drive.html

and didn't see my geometry table appear in geometry_columns.

comment:6 Changed 8 years ago by robe

For the foreign table case, I think that we SHOULD backport to 2.0 since a 9.2 backport of postgres_fdw extension by Command Prompt (I believe) and besides FDWs have existed since 9.1

comment:7 Changed 8 years ago by robe

CREATE OR REPLACE VIEW geometry_columns AS 
  SELECT current_database()::varchar(256) AS f_table_catalog, 
    n.nspname::varchar(256) AS f_table_schema, 
    c.relname::varchar(256) AS f_table_name, 
    a.attname::varchar(256) AS f_geometry_column, 
    COALESCE(NULLIF(postgis_typmod_dims(a.atttypmod),2),
             postgis_constraint_dims(n.nspname, c.relname, a.attname),
             2) AS coord_dimension, 
    COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod),0),
             postgis_constraint_srid(n.nspname, c.relname, a.attname),
             0) AS srid, 
    -- force to be uppercase with no ZM so is backwards compatible
    -- with old geometry_columns
    replace(
      replace(
        COALESCE(
          NULLIF(upper(postgis_typmod_type(a.atttypmod)::text), 'GEOMETRY'),
          postgis_constraint_type(n.nspname, c.relname, a.attname),
          'GEOMETRY'
        ), 'ZM', ''
      ), 'Z', ''
    )::varchar(30) AS type
  FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
  WHERE t.typname = 'geometry'::name 
    AND a.attisdropped = false 
    AND a.atttypid = t.oid 
    AND a.attrelid = c.oid 
    AND c.relnamespace = n.oid 
    AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" OR c.relkind = 'm'::"char" OR c.relkind = 'f'::"char" )
    AND NOT pg_is_other_temp_schema(c.relnamespace)
    AND NOT ( n.nspname = 'public' AND c.relname = 'raster_columns' )
    AND has_table_privilege( c.oid, 'SELECT'::text );

above is the revised to handle foreign table wrappers. My FDW geometry column shows up correctly with that. I would commit but not near my dev environment and strk says I can't commit if I can't test and no counting on bots to do my dirty work. We should probably also get rid of the n.nspname = 'public' while we are at it, because if you install postgis in its own schema it's slightly annoying that raster_columns appears in geometry_columns list.

comment:8 Changed 8 years ago by pramsey

Resolution: fixed
Status: reopenedclosed

FDW support added in 2.0 at r12046, 2.1 at r12047, trunk at r12048

comment:9 Changed 8 years ago by robe

Keywords: history added
Note: See TracTickets for help on using tickets.