Thread: Preserving data after updates
A few months ago, a question by Scott Frankel produced a suggestion from
Greg Patnude which I found very exciting that had to do with using pg
table inheritance to maintain an audit or row change history table. I've
been testing Patnude's idea and ran into a problem, described below, and
wanted to ask about work-around suggestions.
Testing had so far entailed occasionally dumping the production data
base, restoring to DEV, and then modifying DEV to include several
"history" tables, using a script similar to that which I documented on
the PG web site. So today, I tried for the first time dumping DEV after
making the history table additions and then testing the restore from the
dump script so produced. The restore failed.
The problem is that one of my parent tables has table constraints:
CREATE TABLE person
(
person_pk int4 NOT NULL DEFAULT nextval('"person_person_pk_seq"'::text),
last_name varchar(24),
first_name varchar(24),
middle_name varchar(24),
e_mail_address name,
social_security_no varchar(11),
CONSTRAINT person_e_mail_address CHECK
(check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::character varying)),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'::character
varying))
)
WITHOUT OIDS;
I create the history table with
CREATE TABLE person_change_history(
action VARCHAR(6),
update_date TIMESTAMP NOT NULL DEFAULT NOW(),
update_user NAME NOT NULL DEFAULT CURRENT_USER
) INHERITS (person) WITHOUT OIDS;
CREATE RULE person_ru AS ON UPDATE TO person
DO INSERT INTO person_change_history
SELECT *, 'UPDATE' FROM ONLY person WHERE person_pk = old.person_pk;
CREATE RULE person_rd AS ON DELETE TO person
DO INSERT INTO person_change_history
SELECT *, 'DELETE' FROM ONLY person WHERE person_pk = old.person_pk;
But after doing a dump of the modified data base, the script created by
pg dump wants to recreate the history table as
CREATE TABLE person_change_history
(
person_pk int4 NOT NULL DEFAULT nextval('"person_person_pk_seq"'::text),
last_name varchar(24),
first_name varchar(24),
middle_name varchar(24),
e_mail_address name,
social_security_no varchar(11),
"action" varchar(6),
update_date timestamp NOT NULL DEFAULT now(),
update_user name NOT NULL DEFAULT "current_user"(),
CONSTRAINT person_e_mail_address CHECK
(check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::character varying)),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'::character
varying))
) INHERITS (person)
WITHOUT OIDS;
When I run the script to restore the dumped, modified, data base, psql
raises an error when creating the history table because the table
constraints already exist"
psql:paid-5434.sql:7678: ERROR: constraint "person_e_mail_address"
already exists for relation "person_change_history"
Any suggestion on how to get around this problem?
I don't want to have to manually modified the pg_dump output script so
as to delete the constraint definitions from the history table
definition, because that sort of manual intervention really gets in the
way of good administrative procedures for disaster recovery if this
scheme were to be implemented in the production data base.
-- BMT
Berend Tober <btober@seaworthysys.com> writes:
> But after doing a dump of the modified data base, the script created by
> pg dump wants to recreate the history table as
> ...
> CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
> (first_name IS NOT NULL))),
Hmm, it shouldn't do that ... and in a quick test here I couldn't
reproduce any such bug. What version of pg_dump are you using?
regards, tom lane
Tom Lane wrote: >Berend Tober <btober@seaworthysys.com> writes: > > >>But after doing a dump of the modified data base, the script created by >>pg dump wants to recreate the history table as >>... >> CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR >>(first_name IS NOT NULL))), >> >> > >Hmm, it shouldn't do that ... and in a quick test here I couldn't >reproduce any such bug. What version of pg_dump are you using? > Sorry I failed to specify. Production version is 7.3.1 (change is hard!), although I origianally worked out the implementation on version 8. I bet that is the problem.
Berend Tober <btober@seaworthysys.com> writes:
> Tom Lane wrote:
>> Hmm, it shouldn't do that ... and in a quick test here I couldn't
>> reproduce any such bug. What version of pg_dump are you using?
> Sorry I failed to specify. Production version is 7.3.1 (change is
> hard!), although I origianally worked out the implementation on version
> 8. I bet that is the problem.
The case I tested seems to work in 7.3 as well:
CREATE TABLE person (last_name varchar(24),
first_name varchar(24),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))));
CREATE TABLE person_change_history(
action VARCHAR(6),
update_date TIMESTAMP NOT NULL DEFAULT NOW(),
update_user NAME NOT NULL DEFAULT CURRENT_USER
) INHERITS (person);
pg_dump puts the CONSTRAINT only on person, as it should. I'm testing
7.3.10 but I don't see any changes in the 7.3 CVS log that look related.
Can you put together a reproducible test case?
regards, tom lane
Tom Lane wrote:
>The case I tested seems to work in 7.3 as well:
>
>CREATE TABLE person (last_name varchar(24),
> first_name varchar(24),
> CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
>(first_name IS NOT NULL))));
>
>CREATE TABLE person_change_history(
> action VARCHAR(6),
> update_date TIMESTAMP NOT NULL DEFAULT NOW(),
> update_user NAME NOT NULL DEFAULT CURRENT_USER
> ) INHERITS (person);
>
>pg_dump puts the CONSTRAINT only on person, as it should. I'm testing
>7.3.10 but I don't see any changes in the 7.3 CVS log that look related.
>Can you put together a reproducible test case?
>
>
I tried a simpler example than my original, as you have, and the problem
bahavior didn't manifest, but it still happens in my dev copy of my
production database. The immediately obvious difference between the
simpler example, like yours, and the actual case in which the problem
manifests is that the problem case to of the table constraints call a
user-defined function "check_pattern()" (which tests the column value
against a regular expression), i.e.
CREATE OR REPLACE FUNCTION public.check_pattern("varchar", "varchar")
RETURNS bool AS
'
DECLARE
l_value ALIAS FOR $1;
l_pattern ALIAS FOR $2;
l_row RECORD;
BEGIN
IF (l_value IS NOT NULL) AND (LENGTH(l_value) > 0) THEN
IF EXISTS(SELECT 1 FROM public.regular_expression WHERE
UPPER(description) = UPPER(l_pattern)) THEN
SELECT INTO l_row regular_expression, user_message FROM
public.regular_expression WHERE UPPER(description) = UPPER(l_pattern);
IF NOT (l_value ~ l_row.regular_expression) THEN
RAISE EXCEPTION \'Invalid %. %\', l_pattern, l_row.user_message;
END IF;
END IF;
END IF;
RETURN TRUE;
END;' LANGUAGE 'plpgsql' VOLATILE;
in the definition:
CREATE TABLE person
(
person_pk int4 NOT NULL DEFAULT nextval('"person_person_pk_seq"'::text),
last_name varchar(24),
first_name varchar(24),
middle_name varchar(24),
e_mail_address name,
social_security_no varchar(11),
CONSTRAINT person_pkey PRIMARY KEY (person_pk),
CONSTRAINT person_e_mail_address CHECK
(check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::character varying)),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'::character
varying))
)
WITHOUT OIDS;
Berend Tober <btober@seaworthysys.com> writes:
> I tried a simpler example than my original, as you have, and the problem
> bahavior didn't manifest, but it still happens in my dev copy of my
> production database. The immediately obvious difference between the
> simpler example, like yours, and the actual case in which the problem
> manifests is that the problem case to of the table constraints call a
> user-defined function "check_pattern()" (which tests the column value
> against a regular expression), i.e.
Nope, that's not it. Still works fine here.
What do you get from
select conname, consrc from pg_catalog.pg_constraint
where contype = 'c' and conrelid = 'person'::regclass;
select conname, consrc from pg_catalog.pg_constraint
where contype = 'c' and conrelid = 'person_change_history'::regclass;
AFAICS from looking at the 7.3 pg_dump source, it should suppress any
constraint on person_change_history that looks identical to one of the
parent table's constraints in this query.
regards, tom lane
Tom Lane wrote:
>What do you get from
>
>select conname, consrc from pg_catalog.pg_constraint
>where contype = 'c' and conrelid = 'person'::regclass;
>
>
>
conname | consrc
---------------------------+---------------------------------------------------------------------------------------------------------
person_e_mail_address | public.check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::charactervarying)
person_name_check | ((last_name IS NOT NULL) OR (first_name IS NOT NULL))
person_social_security_no | public.check_pattern(social_security_no, 'Social Security Number'::character varying)
(3 rows)
>select conname, consrc from pg_catalog.pg_constraint
>where contype = 'c' and conrelid = 'person_change_history'::regclass;
>
>
conname | consrc
---------------------------+--------------------------------------------------------------------------------------------------
person_social_security_no | check_pattern(social_security_no, 'Social Security Number'::character varying)
person_name_check | ((last_name IS NOT NULL) OR (first_name IS NOT NULL))
person_e_mail_address | check_pattern((e_mail_address)::character varying, 'Internet E-Mail Address'::character
varying)
(3 rows)
>AFAICS from looking at the 7.3 pg_dump source, it should suppress any
>constraint on person_change_history that looks identical to one of the
>parent table's constraints in this query.
>
>
Interesting. The consrc column values differ in that the explicit schema
qualification on the function calls is missing for the descendent table.
So, you think maybe if I remove the explicit schema qualification from
the function calls in the constraint declarations on the person table
that that might fix it? Yup! That does it!
Thanks for your help.
Tom Lane wrote:
> What do you get from
>
> select conname, consrc from pg_catalog.pg_constraint
> where contype = 'c' and conrelid = 'person'::regclass;
>
>
>
conname
|
consrc
---------------------------+---------------------------------------------------------------------------------------------------------
person_e_mail_address |
public.check_pattern((e_mail_address)::character varying, 'Internet
E-Mail Address'::character varying)
person_name_check | ((last_name IS NOT NULL) OR (first_name IS
NOT NULL))
person_social_security_no | public.check_pattern(social_security_no,
'Social Security Number'::character varying)
(3 rows)
> select conname, consrc from pg_catalog.pg_constraint
> where contype = 'c' and conrelid = 'person_change_history'::regclass;
>
>
conname |
consrc
---------------------------+--------------------------------------------------------------------------------------------------
person_social_security_no | check_pattern(social_security_no, 'Social
Security Number'::character varying)
person_name_check | ((last_name IS NOT NULL) OR (first_name IS
NOT NULL))
person_e_mail_address | check_pattern((e_mail_address)::character
varying, 'Internet E-Mail Address'::character varying)
(3 rows)
> AFAICS from looking at the 7.3 pg_dump source, it should suppress any
> constraint on person_change_history that looks identical to one of the
> parent table's constraints in this query.
>
>
Interesting. The consrc column values differ in that the explicit schema
qualification on the function calls is missing for the descendent table.
So, you think maybe if I remove the explicit schema qualification from
the function calls in the constraint declarations on the person table
that that might fix it?
Yup! That does it! Thanks for your help!
But now, however, when restoring from the pg_dump output the script gets
hung up over the fact that when the CREATE TABLE statements are executed
the raw script can't find the check_pattern function, since it is
declared in the public schema and these application-specific tables are
(being tried to be) declared in a different schema. That is, the pg_dump
output has lots of
SET search_path = public, pg_catalog;
and
SET search_path = paid, pg_catalog;
statements sprinkled throughout, and when a table is declared having the
check_pattern function call constraint after the latter statement, then
the function can't be found. I had to manually edit the pg_dump output
script search path statements to read
SET search_path = paid, public, pg_catalog;
in order to make this all work right. Again, too much manual editing to
tolerate for disaster recovery and for my frequent refresh of DEV and
QAT from PRD for development and testing purposes.
Now what, oh most wise one?
Berend Tober <btober@computer.org> writes:
> Interesting. The consrc column values differ in that the explicit schema
> qualification on the function calls is missing for the descendent table.
> So, you think maybe if I remove the explicit schema qualification from
> the function calls in the constraint declarations on the person table
> that that might fix it? Yup! That does it!
OK. This is a variant of the old problems that we had with relying on
consrc to dump constraints. pg_dump hasn't done that for awhile, so
I expect the problem is gone in more recent releases.
regards, tom lane
Berend Tober <btober@seaworthysys.com> writes:
> Now what, oh most wise one?
OK, now I finally get the point: you are creating child tables in
different schemas than their parents live in. This creates a problem
because reverse-listing of the constraints varies depending on what
the search path is.
An example in CVS tip is:
create function foo(text) returns bool as 'select true' language sql;
create table t1(f1 text constraint c1 check (foo(f1)));
create schema s1;
create table s1.t2() inherits(public.t1);
pg_dump yields this:
SET search_path = public, pg_catalog;
CREATE TABLE t1 (
f1 text,
CONSTRAINT c1 CHECK (foo(f1))
);
...
SET search_path = s1, pg_catalog;
CREATE TABLE t2 (CONSTRAINT c1 CHECK (public.foo(f1))
)
INHERITS (public.t1);
It's the same constraint, but the different reverse-listing fools
pg_dump into assuming that it's different.
At the moment I'm not seeing any really nice way to fix this.
A short-term workaround is to hack pg_dump so that it doesn't compare
the constraint expressions at all, but just assumes that a child table's
constraint is the same as the parent's if the constraint name matches.
You can of course break this by manually dropping the child constraint
and creating a different one of the same name --- but does anyone do
that in practice? (Note: the code in pg_dump seems to think that there
is something special about constraint names beginning with '$', but
in quick tests I don't see the system generating constraint names of
that kind as far back as 7.0, which is the oldest server version pg_dump
now claims to support. So I think that is long-dead code, and that a
comparison of constraint names is probably sufficient in practice.)
It can be argued that we should actually prohibit dropping inherited
constraints, which'd eliminate that problem. I seem to recall that this
has come up before and we explicitly decided against making such a
restriction ... but given that a dump/restore will cause the inherited
constraint to come back anyway, it can hardly be claimed that we really
support dropping them.
Comments anyone?
regards, tom lane
Tom Lane wrote: > Berend Tober <btober@seaworthysys.com> writes: > > >> Now what, oh most wise one? >> > > > OK, now I finally get the point: you are creating child tables in > different schemas than their parents live in. This creates a problem > because reverse-listing of the constraints varies depending on what > the search path is. > > Close but not exactly. In my case the child tables are in the same schema as the parent, but it is the function call referenced in the check constraint that lives in a different schema than the tables. However, as an alternative in developing this idea, I did consider the possibility of defining a separate schema where all the child tables would live so that the child tables could have the same name as the parent tables, since this particular implementation is such that the child tables represent change histories of the parent tables. > An example in CVS tip is:... > > It's the same constraint, but the different reverse-listing fools > pg_dump into assuming that it's different. > > At the moment I'm not seeing any really nice way to fix this. > > If the pg_dump output produced "SET search_path" statement with the complete actual path required to find all objects in subsequent DDL statements, my world would be at peace. (But I have no idea how complicated it would be to implement that.) > It can be argued that we should actually prohibit dropping inherited > constraints, which'd eliminate that problem. I seem to recall that this > has come up before and we explicitly decided against making such a > restriction ... but given that a dump/restore will cause the inherited > constraint to come back anyway, it can hardly be claimed that we really > support dropping them. > > Comments anyone? > > I like that arguement to prohibit dropping inherited constraints.
On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote: > Berend Tober <btober@seaworthysys.com> writes: > > Now what, oh most wise one? > > OK, now I finally get the point: you are creating child tables in > different schemas than their parents live in. ... > Comments anyone? Best thing to do is to prevent people from creating child tables in different schemas. Or at least advise against it. Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. My partitioning efforts will eventually distinguish between inherited and non-inherited constraints, since the former are fairly useless for partition elimination. So I can't see a reason to care whether they are there or not, if the user knows better. Best Regards, Simon Riggs
Simon Riggs wrote: >On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote: > > >>Berend Tober <btober@seaworthysys.com> writes: >> >> >>>Now what, oh most wise one? >>> >>> >>OK, now I finally get the point: you are creating child tables in >>different schemas than their parents live in. >> >> > >... > > >>Comments anyone? >> >> > >Best thing to do is to prevent people from creating child tables in >different schemas. Or at least advise against it. > >Doing anything to restrict dropping of inherited constraints seems like >wasted effort and potentially annoying anyhow. > >My partitioning efforts will eventually distinguish between inherited >and non-inherited constraints, since the former are fairly useless for >partition elimination. So I can't see a reason to care whether they are >there or not, if the user knows better. > > The case in question was not one of the child table being in a different partition (do you mean schema?), although that arrangement was considered and rejected for other reasons during data base design. In this implementation, a function called for a table constraint was in a different schema. The function so called was defined in the public scheme because it is a generic function that can be used by different applications, and some tables are relevant only to specific applications and so have there own, application-specific schema -- but they still can make use of shared definitions, i.e., this particular function, which are defined in the public schema.
Re: [HACKERS] Inherited constraints and search paths (was Re: Preserving data after updates)
From
Tom Lane
Date:
Berend Tober <btober@seaworthysys.com> writes:
>> On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote:
>>> OK, now I finally get the point: you are creating child tables in
>>> different schemas than their parents live in.
>>
> The case in question was not one of the child table being in a different
> partition (do you mean schema?), although that arrangement was
> considered and rejected for other reasons during data base design.
I should clarify: the version of the pg_dump bug that still exists in
HEAD is triggered by putting the child table in a different schema than
the parent. 7.3 has different behavior --- offhand I think that in 7.3
the problem can occur if the child table is created while search_path is
set differently than it was when the parent was created. (Of course,
across multiple pg_dump and reload cycles this may boil down to the same
thing. But there are more ways to burn yourself given the 7.3
implementation.)
regards, tom lane
Berend Tober <btober@seaworthysys.com> writes:
> If the pg_dump output produced "SET search_path" statement with the
> complete actual path required to find all objects in subsequent DDL
> statements, my world would be at peace.
We're not doing that, because it's demonstrably impossible :-(.
You can't guarantee that every object in a random collection can be
referenced with an unqualified name. Consider
... CHECK (a.foo(f1) AND b.foo(f1))
ie identically named functions in different schemas.
Even in the cases where it is possible, the infrastructure for it isn't
available to pg_dump, which means there's no hope of a solution like
that being back-ported to 7.3.
regards, tom lane
Simon Riggs <simon@2ndquadrant.com> writes:
> Doing anything to restrict dropping of inherited constraints seems like
> wasted effort and potentially annoying anyhow.
Uh, why? Arguably the constraints are as much part of the parent table
definition as the columns themselves. If you had "check (f1 > 0)" in
the definition of a table, wouldn't you be pretty surprised to select
from it and find rows with f1 < 0?
regression=# create table parent(f1 int check (f1 > 0));
CREATE TABLE
regression=# create table child() inherits(parent);
CREATE TABLE
regression=# alter table child drop constraint parent_f1_check;
ALTER TABLE
regression=# insert into child values(-1);
INSERT 0 1
regression=# select * from parent;
f1
----
-1
(1 row)
I think a good argument can be made that the above behavior is a bug,
and that the ALTER command should have been rejected. We've gone to
great lengths to make sure you can't ALTER a child table to make it
incompatible with the parent in terms of the column names and types;
shouldn't this be true of check constraints as well?
regards, tom lane
On Fri, 2005-05-20 at 11:51 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > Doing anything to restrict dropping of inherited constraints seems like > > wasted effort and potentially annoying anyhow. > > Uh, why? Arguably the constraints are as much part of the parent table > definition as the columns themselves. If you had "check (f1 > 0)" in > the definition of a table, wouldn't you be pretty surprised to select > from it and find rows with f1 < 0? > > regression=# create table parent(f1 int check (f1 > 0)); > CREATE TABLE > regression=# create table child() inherits(parent); > CREATE TABLE > regression=# alter table child drop constraint parent_f1_check; > ALTER TABLE > regression=# insert into child values(-1); > INSERT 0 1 > regression=# select * from parent; > f1 > ---- > -1 > (1 row) > > I think a good argument can be made that the above behavior is a bug, > and that the ALTER command should have been rejected. We've gone to > great lengths to make sure you can't ALTER a child table to make it > incompatible with the parent in terms of the column names and types; > shouldn't this be true of check constraints as well? Thats a good case. I retract my comment on potentially annoying. If you were going to fix that by adding a column that allows me to tell the difference between inherited and non-inherited relations, that would be a very useful piece of info for partition elimination. In that case I would also retract my comment on wasted effort. :-) If you're looking for other inheritance wierdies, you may also be interested in this one. When you create a table that inherits from a master, it copies across constraints with exactly matching names. If a constraint is then added to the master, the constraint is copied across to the child but does not have the same name. So the name of inherited constraints differs depending upon whether CREATE or ALTER puts them there. FWIW, fixing either of those won't get in my way on partitioning... Best Regards, Simon Riggs
Simon Riggs <simon@2ndquadrant.com> writes:
> If you were going to fix that by adding a column that allows me to tell
> the difference between inherited and non-inherited relations, that would
> be a very useful piece of info for partition elimination.
Inherited and non-inherited constraints you mean? Probably. I hadn't
thought through the details, but certainly there would need to be some
better way of telling whether a constraint was inherited.
> If you're looking for other inheritance wierdies, you may also be
> interested in this one. When you create a table that inherits from a
> master, it copies across constraints with exactly matching names. If a
> constraint is then added to the master, the constraint is copied across
> to the child but does not have the same name.
Hmm, that's weird: if you give an explicit name ("add constraint foo")
then it's used, but if you let the system generate the name it's not
the same. I agree that was probably unintentional. Does anyone want to
argue for keeping it this way?
regression=# create table parent(f1 int check (f1 > 0));
CREATE TABLE
regression=# create table child() inherits(parent);
CREATE TABLE
regression=# alter table parent add check (f1 > 100);
ALTER TABLE
regression=# \d parent
Table "public.parent"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
Check constraints:
"parent_f1_check" CHECK (f1 > 0)
"parent_f1_check1" CHECK (f1 > 100)
regression=# \d child
Table "public.child"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
Check constraints:
"parent_f1_check" CHECK (f1 > 0)
"child_f1_check" CHECK (f1 > 100)
Inherits: parent
(This is, btw, another case that would break the current code for
identifying inherited constraints in pg_dump. Given a positive marker
for an inherited constraint, however, we wouldn't care. So I don't
think we need to consider pg_dump in debating which behavior we like.)
regards, tom lane
Tom Lane wrote:
...
I just ran into another inheritance-related oddness. Well maybe it is
not really an oddness -- you tell me.
The problem stems from the fact that I did not originally plan on using
inhertiance and so did not include the ONLY keyword in the FROM clause
of queries coded into my user interface application. To get around
having to modify lots of queries in the application so as to include
ONLY, I instead switched the configuration parameter SQL_INHERITANCE to
OFF. This works fine for cases where I select from tables directly,
i.e., the query correctly returns only the rows from the parent table.
However, when I do a select from a view, which itself does a select from
a parent table, the query result does include the child table rows,
i.e., the SQL_INHERITANCE setting is ignored in this situation. Should
the SQL_INHERITANCE setting still rule?
TEST.SQL:
\set ON_ERROR_STOP ON
\connect - postgres
--DROP DATABASE test;
CREATE DATABASE test WITH TEMPLATE = template1;
\connect test postgres
SET search_path = public, pg_catalog;
CREATE TABLE person (
person_pk serial NOT NULL,
last_name character varying(24),
first_name character varying(24),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR (first_name IS NOT NULL)))
) WITHOUT OIDS;
CREATE TABLE person_change_history (
"action" character varying(6),
update_date timestamp without time zone DEFAULT now() NOT NULL,
update_user name DEFAULT "current_user"() NOT NULL
)INHERITS (person) WITHOUT OIDS;
CREATE OR REPLACE RULE person_ru AS ON UPDATE TO person DO
INSERT INTO person_change_history
SELECT *, 'UPDATE' FROM ONLY person WHERE (person.person_pk = old.person_pk );
/*
My views were originally created with the default SQL_INHERITANCE setting,
which results in PG not automagically inserting the ONLY keyword.
*/
SET SQL_INHERITANCE TO ON;
CREATE VIEW persons AS SELECT * FROM person;
/*
I set it to OFF so that I do not have to go back and do major
modifications to the application.
*/
SET SQL_INHERITANCE TO OFF;
INSERT INTO person (first_name, last_name) VALUES ('Doug', 'Funny');
INSERT INTO person (first_name, last_name) VALUES ('Patty', 'Mayonaise');
SELECT * FROM person;
/*
person_pk | last_name | first_name
-----------+-----------+------------
1 | Funny | Doug
2 | Mayonaise | Patty
(2 rows)
*/
SELECT * FROM person_change_history;
/*
person_pk | last_name | first_name | action | update_date | update_user
-----------+-----------+------------+--------+-------------+-------------
(0 rows)
*/
SELECT * FROM persons;
/*
person_pk | last_name | first_name
-----------+-----------+------------
1 | Funny | Doug
2 | Mayonaise | Patty
(2 rows)
*/
-- A.O.K. so far.
UPDATE person SET last_name = 'Mayonnaise' WHERE last_name = 'Mayonaise';
/*
UPDATE 1
*/
SELECT * FROM person;
/*
person_pk | last_name | first_name
-----------+------------+------------
1 | Funny | Doug
2 | Mayonnaise | Patty
(2 rows)
*/
-- Still O.K.
SELECT * FROM person_change_history;
/*
person_pk | last_name | first_name | action | update_date | update_user
-----------+-----------+------------+--------+---------------------------+-------------
2 | Mayonaise | Patty | UPDATE | 2005-05-20 17:10:53.81593 | postgres
(1 row)
*/
-- Still O.K.
SELECT * FROM persons;
/*
person_pk | last_name | first_name
-----------+------------+------------
1 | Funny | Doug
2 | Mayonnaise | Patty
2 | Mayonaise | Patty
(3 rows)
*/
--Zing...ouch!
Berend Tober <btober@seaworthysys.com> writes:
> However, when I do a select from a view, which itself does a select from
> a parent table, the query result does include the child table rows,
> i.e., the SQL_INHERITANCE setting is ignored in this situation. Should
> the SQL_INHERITANCE setting still rule?
I believe what matters is the sql_inheritance setting that was in force
when the view was created. You should be able to replace the view and
get it to do what you want.
(Hmm ... which suggests that we have still another pg_dump issue,
because views will be dumped using ONLY or no marker, and so reloading
them into a server with SQL_INHERITANCE off would result in a change in
the view behavior. Probably we need to put "SET SQL_INHERITANCE = ON"
into the standard prologue of pg_dump scripts.)
regards, tom lane
Added to TODO:
* Prevent child tables from altering constraints like CHECK that were
inherited from the parent table
---------------------------------------------------------------------------
Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Doing anything to restrict dropping of inherited constraints seems like
> > wasted effort and potentially annoying anyhow.
>
> Uh, why? Arguably the constraints are as much part of the parent table
> definition as the columns themselves. If you had "check (f1 > 0)" in
> the definition of a table, wouldn't you be pretty surprised to select
> from it and find rows with f1 < 0?
>
> regression=# create table parent(f1 int check (f1 > 0));
> CREATE TABLE
> regression=# create table child() inherits(parent);
> CREATE TABLE
> regression=# alter table child drop constraint parent_f1_check;
> ALTER TABLE
> regression=# insert into child values(-1);
> INSERT 0 1
> regression=# select * from parent;
> f1
> ----
> -1
> (1 row)
>
> I think a good argument can be made that the above behavior is a bug,
> and that the ALTER command should have been rejected. We've gone to
> great lengths to make sure you can't ALTER a child table to make it
> incompatible with the parent in terms of the column names and types;
> shouldn't this be true of check constraints as well?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
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