pg-dump bug (at 6.4)
pg_dump has improved a lot since 6.3.2, but here is one bug that relates
to inheritance, where a parent table has constraints.
These are the original table definitions:
create table individual
(
gender char(1) check (gender = 'M' or gender = 'F' or
gender is null),
born datetime check ((born >= '1 Jan 1880' and born
<= 'today') or born is null),
surname text,
forenames text,
title text,
old_surname text,
mobile text,
ni_no text,
constraint is_named check (not (surname isnull and forenames isnull))
)
inherits (person)
;
create table outworker
(
started datetime not null,
finish datetime
)
inherits (individual)
;
This is the output from trying to reload the pg_dump output:
CREATE TABLE "individual" ("gender" char(1), "born" "datetime", "surname"
"text", "forenames" "text", "title" "text", "old_surname" "text", "mobile"
"text", "ni_no" "text", CONSTRAINT is_named CHECK (NOT ( surname IS NULL AND
forenames IS NULL )), CONSTRAINT individual_born CHECK (( born >= '1 Jan
1880' AND born <= 'today' ) OR born IS NULL), CONSTRAINT individual_gender
CHECK (gender = 'M' OR gender = 'F' OR gender IS NULL)) inherits ( "person");
CREATE
CREATE TABLE "outworker" ("started" "datetime" NOT NULL, "finish" "datetime",
CONSTRAINT individual_gender CHECK (gender = 'M' OR gender = 'F' OR gender IS
NULL), CONSTRAINT individual_born CHECK (( born >= '1 Jan 1880' AND born <=
'today' ) OR born IS NULL), CONSTRAINT is_named CHECK (NOT ( surname IS NULL
AND forenames IS NULL ))) inherits ( "individual");
ERROR: DefineRelation: name (individual_gender) of CHECK constraint duplicated
The problem is that pg_dump is unnecessarily restating the constraints for
the parent table in its descendants.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Therefore being justified by faith, we have peace
with God through our Lord Jesus Christ." Romans 5:1
I have investigated further the bug in pg_dump relating to inherited
check constraints. This arises in src/bin/pg_dump/pg_dump.c in getTables(), where the query recovers all the constraints for a table, whether or not
they are inherited:
1477 sprintf(query, "SELECT rcname, rcsrc from pg_relcheck "
1478 "where rcrelid = '%s'::oid ",
1479 tblinfo[i].oid);
In the following example, a constraint is inherited from the
table `individual':
bray=> select oid, relname from pg_class
where oid in
(select rcrelid from pg_relcheck
where rcname = 'is_named')
order by oid desc;
oid|relname
-----+----------
67552|staff
67436|outworker
67111|individual
(3 rows)
bray=> select rcrelid, rcname, rcsrc from pg_relcheck
where rcname = 'is_named'
order by rcrelid desc;
rcrelid|rcname |rcsrc
-------+--------+---------------------------------------------
67552|is_named|NOT ( surname IS NULL AND forenames IS NULL )
67436|is_named|NOT ( surname IS NULL AND forenames IS NULL )
67111|is_named|NOT ( surname IS NULL AND forenames IS NULL )
(3 rows)
pg_dump writes all three constraints into its output, which causes the
table creation to fail on the inherited tables when the database is
restored.
We actually need to select a check constraint only if, for each constraint,
tblinfo[i].oid = min(rcrelid). However, I cannot work out how
to write the query (not least because there is no min()
function for oids).
Can anyone take this further, please?
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"For by grace are ye saved through faith; and that not
of yourselves. It is the gift of God; not of works,
lest any man should boast." Ephesians 2:8,9