Re: attislocal value changed with the dump - Mailing list pgsql-bugs

This is the repro with the sql file below.

createdb bug1
psql bug1 < qna.sql
pg_dump bug1 > bug1.sql
createdb bug2
psql bug2 < bug1.sql

psql bug1
=# select r.relname, attname, attislocal, attinhcount from pg_attribute a join pg_class r ON (a.attrelid = r.oid) where
attname= 'avid' order by relname; 
...output omitted...
=# \c bug2
=# select r.relname, attname, attislocal, attinhcount from pg_attribute a join pg_class r ON (a.attrelid = r.oid) where
attname= 'avid' order by relname; 
...output omitted but shows all columns as local...


I'm running 8.0.4 by the way.

The problem may be with the addition of
default values and indexes on the inherited
columns.  It may assume that because we
are altering the column and/or adding an
index on the inherited columns then the
column is local.

--elein

---- =====
--
-- == CLEAN UP ==
--
drop table askers cascade;
drop table answerers cascade;
drop table questions cascade;
drop table asker_questions cascade;
drop table answer_values cascade;
drop table answer_numeric cascade;
drop table answer_date cascade;
drop table answer_boolean cascade;
drop table answer_float cascade;
drop table answer_text cascade;
drop table answer_date_2 cascade;
drop table answer_numeric_2 cascade;
drop table answers cascade;
drop table atypes cascade;

--
-- == TABLES ==
--
create table askers (
    fid    SERIAL PRIMARY KEY,
    fname    text
);

create table answerers (
    orid    SERIAL PRIMARY KEY,
    orname    text
);

create table atypes (
    atype    text ,
    ncols    smallint,
    PRIMARY KEY (atype, ncols)
);

create table questions (
    qid        SERIAL PRIMARY KEY,
    question    text,
    atype        text,
    ncols        integer,
    FOREIGN KEY (atype, ncols) references atypes (atype, ncols)
);


create table asker_questions (
    fid    integer REFERENCES askers (fid),
    qid    integer REFERENCES questions (qid),
    fset    text,
    PRIMARY KEY (fid, qid, fset)
);

create table answer_values (
    orid    integer REFERENCES answerers (orid),
    qid    integer REFERENCES questions (qid),
    avid    SERIAL,
    atype    text,
    ncols    integer,
    PRIMARY KEY (orid, qid, avid),
    FOREIGN KEY (atype, ncols) references atypes (atype,ncols)
);

--
-- == CHILDREN ANSWER TABLES ==
--
create table answer_numeric (
    avalue    numeric
) inherits (answer_values) ;
alter table answer_numeric alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_num_pk on answer_numeric (avid);

create table answer_date (
    avalue    date
) inherits (answer_values) ;
alter table answer_date alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_date_pk on answer_date (avid);

create table answer_boolean (
    avalue    boolean
) inherits (answer_values) ;
alter table answer_boolean alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_bool_pk on answer_boolean (avid);

create table answer_float (
    avalue    float
) inherits (answer_values) ;
alter table answer_float alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_flt_pk on answer_float (avid);

create table answer_text (
    avalue    text
) inherits (answer_values) ;
alter table answer_text alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_text_pk on answer_text (avid);

create table answer_date_2 (
    astart    date,
    aend    date
) inherits (answer_values) ;
alter table answer_date_2 alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_dater_pk on answer_date_2 (avid);

create table answer_numeric_2 (
    astart    numeric,
    aend    numeric
) inherits (answer_values) ;
create unique index answer_numr_pk on answer_numeric_2 (avid);
alter table answer_numeric_2 alter column avid  set default nextval('answer_values_avid_seq');

create table answer_addr (
    addr    text,
    city    text,
    state    char(2),
    zip    text
) inherits (answer_values) ;
alter table answer_addr alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_addr_pk on answer_addr (avid);