Re: [GENERAL] Preserving data after updates

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] Preserving data after updates

Berend Tober
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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] Preserving data after updates

Berend Tober-2
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?



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] Preserving data after updates

Tom Lane-2
In reply to this post by Berend Tober
Berend Tober <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Inherited constraints and search paths (was Re: [GENERAL] Preserving

Berend Tober-2
In reply to this post by Berend Tober-2
Tom Lane wrote:

> Berend Tober <[hidden email]> 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.



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Inherited constraints and search paths (was Re: [GENERAL] Preserving

Tom Lane-2
Berend Tober <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was

Berend Tober-2
In reply to this post by Berend Tober-2
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!



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was Re:

Tom Lane-2
Berend Tober <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match