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
constraintsfor 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
forenamesIS 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
ID32B8FAA1 ======================================== "For by grace are ye saved through faith; and
thatnot of yourselves. It is the gift of God; not of works, lest any man should boast." Ephesians 2:8,9