Thread: attislocal value changed with dump
The problem is that after a dump and reload of
a table hierarchy there are different values in
pg_attribute.attislocal.
A quick grep shows few references to attislocal.
But I cannot say for sure it is unused since it is
documented. However, I'm looking at a db diff
tool and there it does matter.
This is the setup:
I've got an inheritance hierarchy, answer_values
with a bunch of answer_[type] tables inheriting
from it.
tiny=# \d answer_values
Table "public.answer_values"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------------------
orid | integer | not null
qid | integer | not null
avid | integer | not null default nextval('public.answer_values_avid_seq'::text)
atype | text |
ncols | integer |
Indexes:
"answer_values_pkey" PRIMARY KEY, btree (orid, qid, avid)
tiny=# \d answer_text
Table "public.answer_text"
Column | Type | Modifiers
--------+---------+----------------------------------------------------------
orid | integer | not null
qid | integer | not null
avid | integer | not null default nextval('answer_values_avid_seq'::text)
atype | text |
ncols | integer |
avalue | text |
Indexes:
"answer_text_pk" UNIQUE, btree (avid)
Inherits: answer_values
* In 8.0.4,
* created a clean db (tiny) and loaded the SQL to define the hierarchy.
* pg_dump tiny > tiny.dat
* createdb tiny2
* psql < tiny.dat
After this, looking at the 'avid' attribute in the
pg_attribute table all of tables loaded in the second
db have 't' for attislocal. This is different from
the original definition.
tiny=# select r.relname, attname, attislocal, attinhcount from pg_attribute a join pg_class r ON (a.attrelid = r.oid)
whereattname = 'avid' order by relname;
relname | attname | attislocal | attinhcount
----------------------+---------+------------+-------------
answer_addr | avid | f | 1
answer_addr_pk | avid | t | 0
answer_bool_pk | avid | t | 0
answer_boolean | avid | f | 1
answer_date | avid | f | 1
answer_date_pk | avid | t | 0
answer_date_range | avid | f | 1
answer_dater_pk | avid | t | 0
answer_float | avid | f | 1
answer_flt_pk | avid | t | 0
answer_num_pk | avid | t | 0
answer_numeric | avid | f | 1
answer_numeric_range | avid | f | 1
answer_numr_pk | avid | t | 0
answer_text | avid | f | 1
answer_text_pk | avid | t | 0
answer_values | avid | t | 0
answer_values_pkey | avid | t | 0
av_v | avid | t | 0
(19 rows)
tiny2=# select r.relname, attname, attislocal, attinhcount from pg_attribute a join pg_class r ON (a.attrelid = r.oid)
whereattname = 'avid' order by relname;
relname | attname | attislocal | attinhcount
----------------------+---------+------------+-------------
answer_addr | avid | t | 1
answer_addr_pk | avid | t | 0
answer_bool_pk | avid | t | 0
answer_boolean | avid | t | 1
answer_date | avid | t | 1
answer_date_pk | avid | t | 0
answer_date_range | avid | t | 1
answer_dater_pk | avid | t | 0
answer_float | avid | t | 1
answer_flt_pk | avid | t | 0
answer_num_pk | avid | t | 0
answer_numeric | avid | t | 1
answer_numeric_range | avid | t | 1
answer_numr_pk | avid | t | 0
answer_text | avid | t | 1
answer_text_pk | avid | t | 0
answer_values | avid | t | 0
answer_values_pkey | avid | t | 0
av_v | avid | t | 0
(19 rows)
----- End forwarded message -----
Hi Elein, elein wrote: > The problem is that after a dump and reload of > a table hierarchy there are different values in > pg_attribute.attislocal. > > A quick grep shows few references to attislocal. > But I cannot say for sure it is unused since it is > documented. However, I'm looking at a db diff > tool and there it does matter. It's not unused, though it's not a hot spot (it's only used to prevent you from dropping an inherited column). I can't reproduce your problem here though -- the dump comes out just like the tables I produced. Care to provide the original SQL script? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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);
elein <elein@varlena.com> writes:
> This is the repro with the sql file below.
FWIW, this seems to work as expected with 8.1. Don't have time to try
it with 8.0 right now, but it may be an already-solved issue ...
regards, tom lane
elein <elein@varlena.com> writes:
> This is the repro with the sql file below.
I looked into this, and the answer is you're doing it to yourself;
you shouldn't be explicitly re-specifying the defaults for the child
columns.
> create table answer_numeric (
> avalue numeric
> ) inherits (answer_values) ;
> alter table answer_numeric alter column avid set default nextval('answer_values_avid_seq');
It's unnecessary to have that "alter column set default" command,
because avid will have inherited the default expression from the parent
anyway. The reason that setting it changes pg_dump's output is that
what you are setting is not quite right: the actual default expression
in the parent is
nextval('public.answer_values_avid_seq')
Since that's different, pg_dump concludes that the child's default is
non-inherited and emits a redefinition of the column.
The reason I didn't see the same behavior in CVS tip is that now that
we use regclass literals for nextval() arguments, the changed default
still lists out the same way as the parent's default, and so pg_dump
thinks it's an inherited default.
It strikes me that there is still a risk here, which is that because
listing of regclass values is search-path-sensitive, pg_dump could
come to the wrong conclusion about the inheritance of a default when
the child is in a different schema than the parent. We could probably
fix that by comparing adbin strings instead of the reverse-compiled
expressions to decide if a child default matches its parent or not.
Alternatively, maybe we should add explicit inheritance information
to pg_attrdef. There's already a proposal to do that for constraints...
regards, tom lane
Is there a TODO here?
---------------------------------------------------------------------------
Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > This is the repro with the sql file below.
>
> I looked into this, and the answer is you're doing it to yourself;
> you shouldn't be explicitly re-specifying the defaults for the child
> columns.
>
> > create table answer_numeric (
> > avalue numeric
> > ) inherits (answer_values) ;
> > alter table answer_numeric alter column avid set default nextval('answer_values_avid_seq');
>
> It's unnecessary to have that "alter column set default" command,
> because avid will have inherited the default expression from the parent
> anyway. The reason that setting it changes pg_dump's output is that
> what you are setting is not quite right: the actual default expression
> in the parent is
> nextval('public.answer_values_avid_seq')
> Since that's different, pg_dump concludes that the child's default is
> non-inherited and emits a redefinition of the column.
>
> The reason I didn't see the same behavior in CVS tip is that now that
> we use regclass literals for nextval() arguments, the changed default
> still lists out the same way as the parent's default, and so pg_dump
> thinks it's an inherited default.
>
> It strikes me that there is still a risk here, which is that because
> listing of regclass values is search-path-sensitive, pg_dump could
> come to the wrong conclusion about the inheritance of a default when
> the child is in a different schema than the parent. We could probably
> fix that by comparing adbin strings instead of the reverse-compiled
> expressions to decide if a child default matches its parent or not.
>
> Alternatively, maybe we should add explicit inheritance information
> to pg_attrdef. There's already a proposal to do that for constraints...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073