On Mon, 19 Jan 2004, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@hub.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.oidand 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@hub.org Yahoo!: yscrappy ICQ: 7615664