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);
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
