geometry_columns doesn't support materialized views or foreign tables

[pramsey]

​http://gis.stackexchange.com/questions/74588/materialized-view-does-not-appear-in-geometry-columns-view

[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.

[pramsey]

Fixed in 2.1 at r12039

Fixed in trunk at r12040

Fixed in 2.0 at r12041

[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.

[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

[robe]

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.

[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

[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.

[pramsey]

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