Thread: 7.3b2 strangeness

Hi, i recently installed 7.3b2 on my machine to start looking at the
changes and to make sure my code works with the new system in
anticipation of 7.3.  I dumped my 7.2 database and reloaded in into
7.3.  The dump and resore went fine, and most everything seems to work
great.  I did run into a strange thing, where I cannot update one of my
tables.  I get "Relation 0 does not exist"  Does anyone have any ideas
why this would be happing?

When i shut down 7.3 and startup 7.2 the update works just fine,
datasets are identical.

[root@tex moby]# /usr/pg-7.3/bin/psql basement
Welcome to psql 7.3b2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

basement=# update game_review set approved = 5 where moby_user_id = 10;
ERROR:  Relation 0 does not exist
basement=# select approved, count(*) from game_review where moby_user_id
= 10 group by approved;
 approved | count
----------+-------
        1 |    10
(1 row)

basement=# \d game_review
                  Table "public.game_review"
       Column       |           Type           |   Modifiers
--------------------+--------------------------+---------------
 game_id            | integer                  |
 moby_user_id       | integer                  |
 summary            | character varying(255)   |
 favorite           | text                     |
 least_favorite     | text                     |
 review             | text                     |
 approved           | smallint                 |
 review_timestamp   | timestamp with time zone | default now()
 platform_id        | integer                  |
 points             | smallint                 |
 approver_id        | integer                  |
 approval_timestamp | timestamp with time zone |
 approval_record_id | integer                  |
Indexes: game_review_primarykey unique btree (game_id, moby_user_id,
platform_id),
         game_review_approved btree (approved),
         game_review_moby_user_idx btree (moby_user_id)
Triggers: RI_ConstraintTrigger_17133907,
          RI_ConstraintTrigger_17133908,
          RI_ConstraintTrigger_17133909,
          RI_ConstraintTrigger_17134250,
          delete_approval_history

basement=#

--
Brian Hirt <bhirt@mobygames.com>

On 24 Oct 2002, Brian Hirt wrote:

> Hi, i recently installed 7.3b2 on my machine to start looking at the
> changes and to make sure my code works with the new system in
> anticipation of 7.3.  I dumped my 7.2 database and reloaded in into
> 7.3.  The dump and resore went fine, and most everything seems to work
> great.  I did run into a strange thing, where I cannot update one of my
> tables.  I get "Relation 0 does not exist"  Does anyone have any ideas
> why this would be happing?

Has this dataset been dumped and restored in the past?  This looks like
the symptom from having had an older pg_dump drop a piece of the foreign
key information.  Current sources should actually work for this case,
but if you want you can set tgconstrrelid manually for the affected
pg_trigger rows or drop the triggers and re-add the foreign key
constraints.  The information was somewhat optional in the past but
became used in 7.3

Stephan, Thanks for the info.  This dataset started life off with 6.4
and has been dumped and restored many times over the last 4 years.

One of the things i'm looking forward to is more managable foreign keys
in 7.3 and I am currently writing a conversion script to drop the old
keys and recreate them using the 7.3 syntax.

On Thu, 2002-10-24 at 09:36, Stephan Szabo wrote:
> On 24 Oct 2002, Brian Hirt wrote:
>
> > Hi, i recently installed 7.3b2 on my machine to start looking at the
> > changes and to make sure my code works with the new system in
> > anticipation of 7.3.  I dumped my 7.2 database and reloaded in into
> > 7.3.  The dump and resore went fine, and most everything seems to work
> > great.  I did run into a strange thing, where I cannot update one of my
> > tables.  I get "Relation 0 does not exist"  Does anyone have any ideas
> > why this would be happing?
>
> Has this dataset been dumped and restored in the past?  This looks like
> the symptom from having had an older pg_dump drop a piece of the foreign
> key information.  Current sources should actually work for this case,
> but if you want you can set tgconstrrelid manually for the affected
> pg_trigger rows or drop the triggers and re-add the foreign key
> constraints.  The information was somewhat optional in the past but
> became used in 7.3
--
Brian Hirt <bhirt@mobygames.com>