Postgres Pro
Downloads
Home   >   mailing lists

Thread: Inheritance problem when restoring db

Inheritance problem when restoring db

From
"Sebastjan Trepca"
Date:
Hi,

I noticed a small bug/problem when restoring a database that uses inheritance.

Lets say you have a table B that inherits from table A.

Current state:

Table B has a primary key with sequence b_seq. Table A also has a
primary key with sequence a_seq.

Now we create a backup and restore the database.

New state:

Table B has a primary key with sequence a_seq. Table A is the same as before.

Is this wrong or normal functionality? Do I have to set some extra
flags when doing the backup?

Thanks, Sebastjan

Re: Inheritance problem when restoring db

From
Tom Lane
Date:
"Sebastjan Trepca" <trepca@gmail.com> writes:
> Current state:

> Table B has a primary key with sequence b_seq. Table A also has a
> primary key with sequence a_seq.

In view of the fact that primary keys aren't inherited, and do not
"have sequences", this description is uselessly imprecise.  Please show
exactly how you created these two tables.  And which PG version is this?

            regards, tom lane

Re: Inheritance problem when restoring db

From
"Sebastjan Trepca"
Date:
Hi,

sorry for late response and lack of details. Postgresql version is 8.2.5 .

This is how to reproduce this issue:

inh_test=# CREATE TABLE cities (
inh_test(#     id serial,
inh_test(#     name            text,
inh_test(#     population      float,
inh_test(#     altitude        int     -- in feet
inh_test(# );
CREATE TABLE
inh_test=#
inh_test=# CREATE TABLE capitals (
inh_test(#   id serial,
inh_test(#     name            text,
inh_test(#     population      float,
inh_test(#     altitude        int ,    -- in feet
inh_test(# state           char(2)
inh_test(# ) ;
CREATE TABLE
inh_test=#
inh_test=# alter table capitals inherit cities;
ALTER TABLE
inh_test=# \d cities
                                Table "public.cities"
   Column   |       Type       |                      Modifiers
------------+------------------+-----------------------------------------------------
 id         | integer          | not null default
nextval('cities_id_seq'::regclass)
 name       | text             |
 population | double precision |
 altitude   | integer          |

inh_test=# \d capitals
                                Table "public.capitals"
   Column   |       Type       |                       Modifiers
------------+------------------+-------------------------------------------------------
 id         | integer          | not null default
nextval('capitals_id_seq'::regclass)
 name       | text             |
 population | double precision |
 altitude   | integer          |
 state      | character(2)     |
Inherits: cities


[postgres@emikandi ~]$ pg_dump -c inh_test > inh_test.sql
[postgres@emikandi ~]$ psql -d inh_test < inh_test.sql
SET
SET
SET
SET
SET
SET
ALTER TABLE
ALTER TABLE
DROP SEQUENCE
DROP SEQUENCE
DROP TABLE
DROP TABLE
DROP SCHEMA
CREATE SCHEMA
ALTER SCHEMA
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval
--------
      1
(1 row)

CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval
--------
      1
(1 row)

ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
[postgres@emikandi ~]$ psql -d inh_test
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.

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

inh_test=# \d cities
                                Table "public.cities"
   Column   |       Type       |                      Modifiers
------------+------------------+-----------------------------------------------------
 id         | integer          | not null default
nextval('cities_id_seq'::regclass)
 name       | text             |
 population | double precision |
 altitude   | integer          |

inh_test=# \d capitals
                               Table "public.capitals"
   Column   |       Type       |                      Modifiers
------------+------------------+-----------------------------------------------------
 id         | integer          | not null default
nextval('cities_id_seq'::regclass)
 name       | text             |
 population | double precision |
 altitude   | integer          |
 state      | character(2)     |
Inherits: cities

inh_test=#


Capitals loses its own sequence in the second case.

Regards, Sebastjan

On 10/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Sebastjan Trepca" <trepca@gmail.com> writes:
> > Current state:
>
> > Table B has a primary key with sequence b_seq. Table A also has a
> > primary key with sequence a_seq.
>
> In view of the fact that primary keys aren't inherited, and do not
> "have sequences", this description is uselessly imprecise.  Please show
> exactly how you created these two tables.  And which PG version is this?
>
>                         regards, tom lane
>

Re: Inheritance problem when restoring db

From
Tom Lane
Date:
"Sebastjan Trepca" <trepca@gmail.com> writes:
> This is how to reproduce this issue:
> ...
> inh_test=# alter table capitals inherit cities;

Fascinating.  pg_dump is almost smart enough to get this right, except
that what it spits out is

ALTER TABLE capitals ALTER COLUMN id SET DEFAULT nextval('capitals_id_seq'::regclass);
...
ALTER TABLE cities ALTER COLUMN id SET DEFAULT nextval('cities_id_seq'::regclass);

and since it already declared capitals as inheriting from cities, the
second command descends the inheritance tree and replaces the local
default for capitals.

What we apparently must do is add a dependency relation within pg_dump
to cause these two commands to be emitted in the other order.  I briefly
considered making these sorts of ALTERs be ALTER TABLE ONLY, but if we
go that route we will be unable to correctly restore the inherited vs
not-inherited property of default expressions.  (Not that the system
tracks that currently, but I think there were proposals on the table
to do so.)

            regards, tom lane

Re: Inheritance problem when restoring db

From
Tom Lane
Date:
I wrote:
> "Sebastjan Trepca" <trepca@gmail.com> writes:
>> This is how to reproduce this issue:
>> ...
>> inh_test=# alter table capitals inherit cities;

> Fascinating.  pg_dump is almost smart enough to get this right, ...

I've fixed this --- if you need a patch right away, see here:
http://archives.postgresql.org/pgsql-committers/2007-10/msg00448.php

            regards, tom lane

Re: Inheritance problem when restoring db

From
"Sebastjan Trepca"
Date:
Great, super thanks!

Sebastjan

On 10/29/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> > "Sebastjan Trepca" <trepca@gmail.com> writes:
> >> This is how to reproduce this issue:
> >> ...
> >> inh_test=# alter table capitals inherit cities;
>
> > Fascinating.  pg_dump is almost smart enough to get this right, ...
>
> I've fixed this --- if you need a patch right away, see here:
> http://archives.postgresql.org/pgsql-committers/2007-10/msg00448.php
>
>                         regards, tom lane
>