Re: preserving data after updates

Greg Patnude wrote:

> Yeah… this is where the inheritance model gets a little funky… What do
> you have “SQL_INEHERITANCE” set to when you dump the database ? I’ve
> never tested this so I don’t know if it makes a difference being on or
> off when you dump a table…. You might try it and compare the two
> versions of the DDL for your inherited tables…
>

I set SQL_INEHERITANCE to OFF because I have lots of existing queries in
an application that do not include the "ONLY" option. I did try setting
it back on the default ON, and the problem remained..

> Note: postgreSQL recommends leaving SQL_INHERITANCE at “ON” and using
> the keyword “ONLY”
>
> I’ve seen that before… The problem is that pg_dump creates the
> person_history table as a standalone table (look at the DDL) with the
> keyword “INHERITS” – My gut feeling is that this is probably a bug in
> pg_dump – I don’t think pg_dump really knows how to dump just the
> additional fields specified in an inherited table so it dumps the
> actual definition it finds in the system catalogs…
>
> If you poke around in pg_catalog, you’ll find that the catalog
> definition is a combination of pointers to the parent table and any
> additional fields, constraints, rules, etc you defined when you
> created the inherited table.
>
> My work-around has been to drop and recreate the history tables using
> the “original” SQL I used to create the inherited table in the first
> place…
>