[ADMIN] pg_dump: schema with OID 2200 does not exist

Discussion:

(too old to reply)

Chander Ganesan

2012-05-09 13:58:45 UTC

Hi All,

I'm running into a weird issue with PostgreSQL 9.1.3 and PostGIS 2.0
when trying to dump a table - no matter what table I try to dump in this
database, I find that I get the same error, as evidenced below (scroll
down for relevant data/error output.)

Any ideas as to what might be the root cause of it ?

The error, and some output from relevant queries is below. In this
case, those things that are "supposed" to live in 2200 seem to be some
PostGIS related tables and views - which were moved after installation
(IIRC) using the alter extension statement (moved into the PostGIS
schema.) Though the original public schema still exists...

I've got another database (also with postgis 2.0 installed) where it's
not possible for some reason to dump the postgis.spatial_ref_sys table
(dumps return nothing, though I can query the table directly.)

Thanks

Chander

(erma)***@ermadev2:/data/dwhresponder/ERMA/logs$ pg_dump -t
arctic.data_layer erma

pg_dump: schema with OID 2200 does not exist

(erma)***@ermadev2:/data/dwhresponder/ERMA/logs$ psql erma

psql (9.1.3)

Type "help" for help.

erma=# \pset pager

erma=# select oid,* from pg_namespace ;

oid | nspname | nspowner |

nspacl

---------+--------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

11125 | pg_toast_temp_1 | 10 | {postgres=UC/postgres}

11 | pg_catalog | 10 |
{postgres=UC/postgres,=U/postgres}

11394 | information_schema | 10 |
{postgres=UC/postgres,=U/postgres}

99 | pg_toast | 10 | {postgres=UC/postgres}

19061 | postgis | 16384 |
{chander=UC/chander,erma_test_arctic=U/chander,erma_arctic=U/chander,erma_atlantic=U/chander,erma_caribbean=U/chander,erma_fireresponse=U/chander,erma_gomex=U/chander,erma_newengland=U/chander,erma_southwest=U/chander,erma_pacific=U/chander,erma_northwest=U/chander,erma_gulfofmexico=U/chander}

11124 | pg_temp_1 | 10 | {postgres=UC/postgres}

6887848 | pacific | 16384 |
{chander=UC/chander,erma_pacific=UC/chander}

7163349 | gulfofmexico | 16384 |
{chander=UC/chander,erma_gomex=UC/chander,erma_gulfofmexico=UC/chander}

6292634 | arctic | 16384 |
{chander=UC/chander,erma_test_arctic=U/chander,erma_arctic=UC/chander}

6834227 | newengland | 16384 |
{chander=UC/chander,erma_newengland=UC/chander}

7114095 | southwest | 16384 |
{chander=UC/chander,erma_southwest=UC/chander}

5973178 | public | 16384 |
{chander=UC/chander,=U/chander,erma_test_arctic=U/chander,erma_arctic=U/chander,erma_atlantic=U/chander,erma_caribbean=U/chander,erma_fireresponse=U/chander,erma_gomex=U/chander,erma_newengland=U/chander,erma_southwest=U/chander,erma_pacific=U/chander,erma_northwest=U/chander,erma_gulfofmexico=U/chander}

6982864 | northwest | 16384 |
{chander=UC/chander,erma_northwest=UC/chander}

6708470 | caribbean | 16384 |
{chander=UC/chander,erma_caribbean=UC/chander}

6795143 | fireresponse | 16384 |
{chander=UC/chander,erma_fireresponse=UC/chander}

18916 | topology | 16384 |
{chander=UC/chander,erma_test_arctic=U/chander,erma_arctic=U/chander,erma_atlantic=U/chander,erma_caribbean=U/chander,erma_fireresponse=U/chander,erma_gomex=U/chander,erma_newengland=U/chander,erma_southwest=U/chander,erma_pacific=U/chander,erma_northwest=U/chander,erma_gulfofmexico=U/chander}

6785026 | atlantic | 16384 |
{chander=UC/chander,erma_atlantic=UC/chander}

(17 rows)

erma=# SELECT tableoid, oid, typname, typnamespace, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = typowner) AS rolname, typinput::oid AS
typinput, typoutput::oid AS typoutput, typelem, typrelid, CASE WHEN
typrelid = 0 THEN ' '::"char" ELSE (SELECT relkind FROM pg_class WHERE
oid = typrelid) END AS typrelkind, typtype, typisdefined, typname[0] =
'_' AND typelem != 0 AND (SELECT typarray FROM pg_type te WHERE oid =
pg_type.typelem) = oid AS isarray FROM pg_type where typnamespace=2200
;
tableoid | oid | typname | typnamespace | rolname |
typinput | typoutput | typelem | typrelid | typrelkind | typtype |
typisdefined | isarray
----------+-------+--------------------+--------------+---------+----------+-----------+---------+----------+------------+---------+--------------+---------
1247 | 18045 | spatial_ref_sys | 2200 | chander |
2290 | 2291 | 0 | 18043 | r | c | t
| f
1247 | 18044 | _spatial_ref_sys | 2200 | chander |
750 | 751 | 18045 | 0 | | b | t
| t
1247 | 18351 | geography_columns | 2200 | chander |
2290 | 2291 | 0 | 18349 | v | c | t
| f
1247 | 18350 | _geography_columns | 2200 | chander |
750 | 751 | 18351 | 0 | | b | t
| t
1247 | 18455 | geometry_columns | 2200 | chander |
2290 | 2291 | 0 | 18453 | v | c | t
| f
1247 | 18454 | _geometry_columns | 2200 | chander |
750 | 751 | 18455 | 0 | | b | t
| t
1247 | 18897 | raster_columns | 2200 | chander |
2290 | 2291 | 0 | 18895 | v | c | t
| f
1247 | 18896 | _raster_columns | 2200 | chander |
750 | 751 | 18897 | 0 | | b | t
| t
1247 | 18906 | raster_overviews | 2200 | chander |
2290 | 2291 | 0 | 18904 | v | c | t
| f
1247 | 18905 | _raster_overviews | 2200 | chander |
750 | 751 | 18906 | 0 | | b | t
| t
(10 rows)

--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Tom Lane

2012-05-09 14:20:21 UTC

Permalink

Post by Chander Ganesan
I'm running into a weird issue with PostgreSQL 9.1.3 and PostGIS 2.0
when trying to dump a table - no matter what table I try to dump in this
database, I find that I get the same error, as evidenced below (scroll
down for relevant data/error output.)

2200 would normally be the "public" schema. Since you show it having a
different OID, I suppose you dropped and recreated it at some point.
The question is, how did you manage to do that if there were still
objects in it? Can you reproduce the sequence of events that led to
this state of affairs? Since you mention moving things with ALTER
EXTENSION, I wonder if there could be a bug in that, but this report
is way too vague for somebody else to investigate.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Elizandro Gallegos

2012-05-09 14:49:12 UTC

Permalink

Hello

Please can I be removed from the mailing list, and I receive many emails like this

thanks

ŽLIZANDñO GALLEGOS V.

Date: Wed, 9 May 2012 09:58:45 -0400
Subject: [ADMIN] pg_dump: schema with OID 2200 does not exist
Hi All,
I'm running into a weird issue with PostgreSQL 9.1.3 and PostGIS 2.0
when trying to dump a table - no matter what table I try to dump in this
database, I find that I get the same error, as evidenced below (scroll
down for relevant data/error output.)
Any ideas as to what might be the root cause of it ?
The error, and some output from relevant queries is below. In this
case, those things that are "supposed" to live in 2200 seem to be some
PostGIS related tables and views - which were moved after installation
(IIRC) using the alter extension statement (moved into the PostGIS
schema.) Though the original public schema still exists...
I've got another database (also with postgis 2.0 installed) where it's
not possible for some reason to dump the postgis.spatial_ref_sys table
(dumps return nothing, though I can query the table directly.)
Thanks
Chander
arctic.data_layer erma
pg_dump: schema with OID 2200 does not exist
psql (9.1.3)
Type "help" for help.
erma=# \pset pager
erma=# select oid,* from pg_namespace ;
oid | nspname | nspowner |
nspacl
---------+--------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11125 | pg_toast_temp_1 | 10 | {postgres=UC/postgres}
11 | pg_catalog | 10 |
{postgres=UC/postgres,=U/postgres}
11394 | information_schema | 10 |
{postgres=UC/postgres,=U/postgres}
99 | pg_toast | 10 | {postgres=UC/postgres}
19061 | postgis | 16384 |
{chander=UC/chander,erma_test_arctic=U/chander,erma_arctic=U/chander,erma_atlantic=U/chander,erma_caribbean=U/chander,erma_fireresponse=U/chander,erma_gomex=U/chander,erma_newengland=U/chander,erma_southwest=U/chander,erma_pacific=U/chander,erma_northwest=U/chander,erma_gulfofmexico=U/chander}
11124 | pg_temp_1 | 10 | {postgres=UC/postgres}
6887848 | pacific | 16384 |
{chander=UC/chander,erma_pacific=UC/chander}
7163349 | gulfofmexico | 16384 |
{chander=UC/chander,erma_gomex=UC/chander,erma_gulfofmexico=UC/chander}
6292634 | arctic | 16384 |
{chander=UC/chander,erma_test_arctic=U/chander,erma_arctic=UC/chander}
6834227 | newengland | 16384 |
{chander=UC/chander,erma_newengland=UC/chander}
7114095 | southwest | 16384 |
{chander=UC/chander,erma_southwest=UC/chander}
5973178 | public | 16384 |
{chander=UC/chander,=U/chander,erma_test_arctic=U/chander,erma_arctic=U/chander,erma_atlantic=U/chander,erma_caribbean=U/chander,erma_fireresponse=U/chander,erma_gomex=U/chander,erma_newengland=U/chander,erma_southwest=U/chander,erma_pacific=U/chander,erma_northwest=U/chander,erma_gulfofmexico=U/chander}
6982864 | northwest | 16384 |
{chander=UC/chander,erma_northwest=UC/chander}
6708470 | caribbean | 16384 |
{chander=UC/chander,erma_caribbean=UC/chander}
6795143 | fireresponse | 16384 |
{chander=UC/chander,erma_fireresponse=UC/chander}
18916 | topology | 16384 |
{chander=UC/chander,erma_test_arctic=U/chander,erma_arctic=U/chander,erma_atlantic=U/chander,erma_caribbean=U/chander,erma_fireresponse=U/chander,erma_gomex=U/chander,erma_newengland=U/chander,erma_southwest=U/chander,erma_pacific=U/chander,erma_northwest=U/chander,erma_gulfofmexico=U/chander}
6785026 | atlantic | 16384 |
{chander=UC/chander,erma_atlantic=UC/chander}
(17 rows)
erma=# SELECT tableoid, oid, typname, typnamespace, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = typowner) AS rolname, typinput::oid AS
typinput, typoutput::oid AS typoutput, typelem, typrelid, CASE WHEN
typrelid = 0 THEN ' '::"char" ELSE (SELECT relkind FROM pg_class WHERE
oid = typrelid) END AS typrelkind, typtype, typisdefined, typname[0] =
'_' AND typelem != 0 AND (SELECT typarray FROM pg_type te WHERE oid =
pg_type.typelem) = oid AS isarray FROM pg_type where typnamespace=2200
;
tableoid | oid | typname | typnamespace | rolname |
typinput | typoutput | typelem | typrelid | typrelkind | typtype |
typisdefined | isarray
----------+-------+--------------------+--------------+---------+----------+-----------+---------+----------+------------+---------+--------------+---------
1247 | 18045 | spatial_ref_sys | 2200 | chander |
2290 | 2291 | 0 | 18043 | r | c | t
| f
1247 | 18044 | _spatial_ref_sys | 2200 | chander |
750 | 751 | 18045 | 0 | | b | t
| t
1247 | 18351 | geography_columns | 2200 | chander |
2290 | 2291 | 0 | 18349 | v | c | t
| f
1247 | 18350 | _geography_columns | 2200 | chander |
750 | 751 | 18351 | 0 | | b | t
| t
1247 | 18455 | geometry_columns | 2200 | chander |
2290 | 2291 | 0 | 18453 | v | c | t
| f
1247 | 18454 | _geometry_columns | 2200 | chander |
750 | 751 | 18455 | 0 | | b | t
| t
1247 | 18897 | raster_columns | 2200 | chander |
2290 | 2291 | 0 | 18895 | v | c | t
| f
1247 | 18896 | _raster_columns | 2200 | chander |
750 | 751 | 18897 | 0 | | b | t
| t
1247 | 18906 | raster_overviews | 2200 | chander |
2290 | 2291 | 0 | 18904 | v | c | t
| f
1247 | 18905 | _raster_overviews | 2200 | chander |
750 | 751 | 18906 | 0 | | b | t
| t
(10 rows)
--
http://www.postgresql.org/mailpref/pgsql-admin

Kevin Grittner

2012-05-09 15:03:53 UTC

Permalink

Post by Elizandro Gallegos
Please can I be removed from the mailing list

The answer was in the email to which you responded. Did you have
trouble using the referenced page?

Post by Elizandro Gallegos

Post by Chander Ganesan
http://www.postgresql.org/mailpref/pgsql-admin

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Elizandro Gallegos

2012-05-14 13:52:32 UTC

Permalink

Hello

Please can I be removed from the mailing list, and I receive many emails like this

thanks

ŽLIZANDñO GALLEGOS V.