Re: "too many trigger records found for relation xyz"

["Mikko Partio" <mpartio@xxxxxxxxx>]

On Tue, Apr 8, 2008 at 12:20 PM, Mikko Partio <mpartio@xxxxxxxxx> wrote:

On Tue, Apr 8, 2008 at 11:27 AM, Zdenek Kotala <Zdenek.Kotala@xxxxxxx> 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.

Can you provide self contained reproduction scenario?

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