Re: Inheritance problem when restoring db

From: "Sebastjan Trepca" <trepca(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Postgres General" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Inheritance problem when restoring db
Date: 2007-10-28 08:53:03
Message-ID: cd329af80710280153j38d0bb67hbe0832fef10689e1@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)emikandi ~]$ pg_dump -c inh_test > inh_test.sql
[postgres(at)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(at)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Sebastjan Trepca" <trepca(at)gmail(dot)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
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Finneid 2007-10-28 09:17:58 Re: select count() out of memory
Previous Message Guy Rouillier 2007-10-28 05:59:43 Re: I want to search my project source code