On Tue, Apr 8, 2008 at 12:20 PM, Mikko Partio <
mpartio@gmail.com> wrote:
On Tue, Apr 8, 2008 at 11:27 AM, Zdenek Kotala <
Zdenek.Kotala@sun.com> wrote:
Mikko Partio napsal(a): Hello all
Postgres version 8.3.1
I just created a bunch of tables (~10) with identical structure; all tables
have 6 foreign key references to other tables and a primary key. To my
surprise, some of the tables were created ok, some missed primary key and
some didn't get created at all.
Unfortunately not -- if I try to dump one the referenced tables I get this error:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: more than one row returned by a subquery used as an expression
pg_dump: The command was: 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
I little investigation showed that there is duplicate row in pg_class:
# select oid from pg_class group by oid having count(*) > 1 ;
oid
--------
294397
(1 row)
# \x
Expanded display is on.
# select * from pg_class where oid = 294397;
-[ RECORD 1 ]--+-----------------------------------------
relname | abc
relnamespace | 2200
reltype | 294399
relowner | 16385
relam | 0
relfilenode | 294397
reltablespace | 0
relpages | 1
reltuples | 3
reltoastrelid | 294400
reltoastidxid | 0
relhasindex | t
relisshared | f
relkind | r
relnatts | 4
relchecks | 0
reltriggers | 6
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | f
relhaspkey | t
relhasrules | f
relhassubclass | f
relfrozenxid | 3840767676
relacl | {removed}
reloptions |
-[ RECORD 2 ]--+-----------------------------------------
relname | abc
relnamespace | 2200
reltype | 294399
relowner | 16385
relam | 0
relfilenode | 294397
reltablespace | 0
relpages | 1
reltuples | 3
reltoastrelid | 294400
reltoastidxid | 0
relhasindex | t
relisshared | f
relkind | r
relnatts | 4
relchecks | 0
reltriggers | 12
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | f
relhaspkey | t
relhasrules | f
relhassubclass | f
relfrozenxid | 3840767676
relacl | {removed}
reloptions |
Regards
Mikko