Re: Inheritance problem when restoring db - Mailing list pgsql-general

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
>