From: | "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | PostGIS dropgeometrycolumn function (Was: Re: [7.4] "permissions problem" with pl/pgsql function ) |
Date: | 2004-01-20 16:13:23 |
Message-ID: | 20040120120838.G15422@ganymede.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2004-01-20 16:14:40 | Re: VACUUM delay (was Re: What's planned for 7.5?) |
Previous Message | Tom Lane | 2004-01-20 15:36:34 | Re: Allow backend to output result sets in XML |