PostGIS dropgeometrycolumn function (Was: Re: [7.4] "permissions problem" with pl/pgsql function )

On Mon, 19 Jan 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> > Just had a client point this out to me, and am wondering if its supposed
> > to happen:
>
> > 420_test=> select
> > dropgeometrycolumn('420_test','lroadline61','roads61_geom');
> > ERROR: permission denied for relation pg_attribute
> > CONTEXT: PL/pgSQL function "dropgeometrycolumn" line 19 at execute statement
>
> Can't tell much about this without seeing the contents of the function ...
> in particular, what SQL command is it trying to execute when it chokes?

The function is executing:

EXECUTE ''update pg_attribute set attnotnull = false from pg_class where
pg_attribute.attrelid = pg_class.oid and pg_class.relname = '' ||
quote_literal(table_name) ||'' and pg_attribute.attname = '' ||
quote_literal(column_name);

I'm going to hit up the PostGis folks, since right at the top of the
function it stats:

-- There is no ALTER TABLE DROP COLUMN command in postgresql
-- There is no ALTER TABLE DROP CONSTRAINT command in postgresql
-- So, we:
-- 1. remove the unwanted geom column reference from the
-- geometry_columns table
-- 2. update the table so that the geometry column is all NULLS
-- This is okay since the CHECK srid(geometry) = <srid> is not
-- checked if geometry is NULL (the isstrict attribute on srid())
-- 3. add another constraint that the geometry column must be NULL
-- This, effectively kills the geometry column
-- (a) its not in the geometry_column table
-- (b) it only has nulls in it
-- (c) you cannot add anything to the geom column because it must be NULL
--
-- This will screw up if you put a NOT NULL constraint on the geometry
-- column, so the first thing we must do is remove this constraint (its a
-- modification of the pg_attribute system table)
--
-- We also check to see if the table/column exists in the geometry_columns
-- table

Anyone on this list working with the PostGis development team?

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664