OIDs and AddGeometryColumn

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

OIDs and AddGeometryColumn

Dylan Keon-3
Hi all,

I upgraded a server to PostgreSQL 8.1.0 and PostGIS 1.0.5 (coming from
7.4.5/0.8.2 - ancient, I know :).  I can't do a SELECT
AddGeometryColumn, apparently because there are no OID columns in the
restored tables.  The default behavior in 8.1 is to not create OIDs.

  tsunami=# SELECT AddGeometryColumn('temp','geom',-1,'POINT',2);
  ERROR:  column reference "oid" is ambiguous

Do I have to force OID creation just so that I can use
AddGeometryColumn?  Or am I missing something?

Thanks much,
Dylan


Full output:

tsunami=# SELECT AddGeometryColumn('temp','geom',-1,'POINT',2);
ERROR:  column reference "oid" is ambiguous
CONTEXT:  SQL statement "UPDATE geometry_columns SET f_table_schema =
n.nspname FROM pg_namespace n, pg_class c, pg_attribute a,
pg_constraint sridcheck, pg_constraint typecheck WHERE (
f_table_schema is NULL OR f_table_schema = '' OR f_table_schema NOT IN
( SELECT nspname::varchar FROM pg_namespace nn, pg_class cc,
pg_attribute aa WHERE cc.relnamespace = nn.oid AND cc.relname =
f_table_name::name AND aa.attrelid = cc.oid AND aa.attname =
f_geometry_column::name)) AND f_table_name::name = c.relname AND c.oid
= a.attrelid AND c.relnamespace = n.oid AND f_geometry_column::name =
a.attname AND sridcheck.conrelid = c.oid AND sridcheck.consrc LIKE
'(srid(% = %)' AND sridcheck.consrc ~ textcat(' = ', srid::text) AND
typecheck.conrelid = c.oid AND typecheck.consrc LIKE
'((geometrytype(%) = ''%''::text) OR (% IS NULL))' AND
typecheck.consrc ~ textcat(' = ''', type::text) AND NOT EXISTS (
SELECT oid FROM geometry_columns gc WHERE c.relname::varchar =
gc.f_table_name AND n.nspname::varchar = gc.f_table_schema AND
a.attname::varchar = gc.f_geometry_column )"
PL/pgSQL function "fix_geometry_columns" line 17 at SQL statement
SQL statement "SELECT  fix_geometry_columns()"
PL/pgSQL function "addgeometrycolumn" line 142 at select into variables
SQL statement "SELECT  AddGeometryColumn('','', $1 , $2 , $3 , $4 , $5 )"
PL/pgSQL function "addgeometrycolumn" line 4 at select into variables
tsunami=#

Reply | Threaded
Open this post in threaded view
|

Re: OIDs and AddGeometryColumn

strk-2
On Tue, Dec 06, 2005 at 04:43:24PM -0800, Dylan Keon wrote:

> Hi all,
>
> I upgraded a server to PostgreSQL 8.1.0 and PostGIS 1.0.5 (coming from
> 7.4.5/0.8.2 - ancient, I know :).  I can't do a SELECT
> AddGeometryColumn, apparently because there are no OID columns in the
> restored tables.  The default behavior in 8.1 is to not create OIDs.
>
>   tsunami=# SELECT AddGeometryColumn('temp','geom',-1,'POINT',2);
>   ERROR:  column reference "oid" is ambiguous
>
> Do I have to force OID creation just so that I can use
> AddGeometryColumn?  Or am I missing something?

You need OID just in the geometry_columns table.
Neither pg_restore nor postgis_restore.pl will do this
for you, unfortunately.

I suggest you backup your geometry_columns table, recreate
it using the CREATE TABLE found in lwpostgis.sql and finally
populate the new one from the backup.

Maybe this could be encoded into the postgis_restore.pl script.

--strk;


>
> Thanks much,
> Dylan
>
>
> Full output:
>
> tsunami=# SELECT AddGeometryColumn('temp','geom',-1,'POINT',2);
> ERROR:  column reference "oid" is ambiguous
> CONTEXT:  SQL statement "UPDATE geometry_columns SET f_table_schema =
> n.nspname FROM pg_namespace n, pg_class c, pg_attribute a,
> pg_constraint sridcheck, pg_constraint typecheck WHERE (
> f_table_schema is NULL OR f_table_schema = '' OR f_table_schema NOT IN
> ( SELECT nspname::varchar FROM pg_namespace nn, pg_class cc,
> pg_attribute aa WHERE cc.relnamespace = nn.oid AND cc.relname =
> f_table_name::name AND aa.attrelid = cc.oid AND aa.attname =
> f_geometry_column::name)) AND f_table_name::name = c.relname AND c.oid
> = a.attrelid AND c.relnamespace = n.oid AND f_geometry_column::name =
> a.attname AND sridcheck.conrelid = c.oid AND sridcheck.consrc LIKE
> '(srid(% = %)' AND sridcheck.consrc ~ textcat(' = ', srid::text) AND
> typecheck.conrelid = c.oid AND typecheck.consrc LIKE
> '((geometrytype(%) = ''%''::text) OR (% IS NULL))' AND
> typecheck.consrc ~ textcat(' = ''', type::text) AND NOT EXISTS (
> SELECT oid FROM geometry_columns gc WHERE c.relname::varchar =
> gc.f_table_name AND n.nspname::varchar = gc.f_table_schema AND
> a.attname::varchar = gc.f_geometry_column )"
> PL/pgSQL function "fix_geometry_columns" line 17 at SQL statement
> SQL statement "SELECT  fix_geometry_columns()"
> PL/pgSQL function "addgeometrycolumn" line 142 at select into variables
> SQL statement "SELECT  AddGeometryColumn('','', $1 , $2 , $3 , $4 , $5 )"
> PL/pgSQL function "addgeometrycolumn" line 4 at select into variables
> tsunami=#
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

--

 /"\    ASCII Ribbon Campaign
 \ /    Respect for low technology.
  X     Keep e-mail messages readable by any computer system.
 / \    Keep it ASCII.


Reply | Threaded
Open this post in threaded view
|

Re: OIDs and AddGeometryColumn

Dylan Keon-3
On 12/7/05, [hidden email] <[hidden email]> wrote:

> On Tue, Dec 06, 2005 at 04:43:24PM -0800, Dylan Keon wrote:
> > Hi all,
> >
> > I upgraded a server to PostgreSQL 8.1.0 and PostGIS 1.0.5 (coming from
> > 7.4.5/0.8.2 - ancient, I know :).  I can't do a SELECT
> > AddGeometryColumn, apparently because there are no OID columns in the
> > restored tables.  The default behavior in 8.1 is to not create OIDs.
> >
> >   tsunami=# SELECT AddGeometryColumn('temp','geom',-1,'POINT',2);
> >   ERROR:  column reference "oid" is ambiguous
> >
> > Do I have to force OID creation just so that I can use
> > AddGeometryColumn?  Or am I missing something?
>
> You need OID just in the geometry_columns table.
> Neither pg_restore nor postgis_restore.pl will do this
> for you, unfortunately.
>
> I suggest you backup your geometry_columns table, recreate
> it using the CREATE TABLE found in lwpostgis.sql and finally
> populate the new one from the backup.

strk,

That worked.  Thanks!

Dylan