Re: Relation 0 does not exist - Mailing list pgsql-general

On Thu, Sep 26, 2002 at 09:59:32AM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> > then dumped with the v7.3 pg_dumpall which generated:
>
> > CREATE CONSTRAINT TRIGGER "<unnamed>"
> >     AFTER INSERT OR UPDATE ON trans
> > NOT DEFERRABLE INITIALLY IMMEDIATE
> >     FOR EACH ROW
> >     EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'trans', 'meter', 'UNSPE
> > CIFIED', 'meter_id', 'id');
>
> Yeek.  The lack of a FROM <table> clause in that trigger definition is
> why it's not working.  IIRC, the FROM was optional in pre-7.3 releases,
> but it is *required* now.  (We probably should adjust the syntax
> accordingly.)
>
> 7.3 pg_dump is not working hard enough to regenerate the appropriate
> info, which we can fix, but I'm wondering how it got that way in the
> first place.  The bug that could originally cause tgconstrrelid to be
> forgotten was a pg_dump bug that existed up to about 7.0.  Is it
> possible that these tables have a continuous history of being dumped
> and reloaded back to 7.0 or before?

I wrote the system last year and it started running for real
  Thu 29 Mar 22:41:25 2001
I think that is after 7.0? It has gone through a number of dump/restore
cycles.

> Anyway the quickest fix seems to be to manually drop the triggers
> and reconstruct the FK relationships with ALTER TABLE ADD FOREIGN KEY
> commands.  If that seems too messy to do by hand, you can wait till
> I've got a pg_dump patch to do it for you.

Just a note on output. Before I had

\d trans...
Indexes: firsttimei_idx btree (firsttimei),
         srcpeername_idx btree (sourcepeername)
Triggers: RI_ConstraintTrigger_14413070,
          RI_ConstraintTrigger_14413073
\d meter...
Indexes: meter_pkey primary key btree (id)
Triggers: RI_ConstraintTrigger_14413071,
          RI_ConstraintTrigger_14413072
\d stats...
Indexes: stats_pkey primary key btree (id)
Triggers: RI_ConstraintTrigger_14413074,
          RI_ConstraintTrigger_14413075

after drop trigger/alter table add foreign key:

\d trans
Indexes: firsttimei_idx btree (firsttimei),
         srcpeername_idx btree (sourcepeername)
Foreign Key constraints: $1 FOREIGN KEY (meter_id) REFERENCES meter(id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO
ACTION,
                         $2 FOREIGN KEY (stats_id) REFERENCES stats(id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO
ACTION
\d meter...
Indexes: meter_pkey primary key btree (id)
\d stats...
Indexes: stats_pkey primary key btree (id)


I take it the difference is because before tgconstrrelid was zero, and now
it isn't? (Apart from pg_sync_pg_pwd and pg_sync_pg_group)

Thank you for the help! (Working now :-) .. now to see what's up with libpq++)

Patrick