From: "Joel Burton" <jburton@scw.org>
Organization: Support Center of Washington
To: pgsql-docs@postgresql.org
Date: Sun, 3 Dec 2000 17:29:20 -0500
MIME-Version: 1.0
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Subject: Proposed tutorial or chapter on RI/hacking RI in catalog
Message-ID: <3A2A82F0.24487.D98117@localhost>

[This is a report, since the original post never appeared. My 
apologies if it's a duplicate for you.]


I've written the start of a tutorial on how to use the RI features of
Pgsql, and how to alter the system catalogs to change actions,
deferrability, etc. Much of the information is already in the docs, but
spread around different SQL reference sections, etc., so I thought it
would be useful in one place.

It's not in Docbook; I want to see first what structural changes 
people
can recommend, etc., before it gets marked up.

It's still quite bit rough, so I'd hold off on grammar/spelling/etc., but 
I would be interested if people have (a) tips not in it, (b) factual 
errors, or (c) ideas about where it might fit into the docs.

I do read this group, but I always like a cc: to make sure I don't miss
anything: jburton@scw.org.

Thanks!


Referential Integrity Tutorial & Hacking Referential 
Integrity:


Referential integrity is the feature of a database that 
ensures that
implied relationships in a database are enforced. It is 
a feature of
most database systems, and protects users from 
accidentally (or
intentially!) creating discrepencies in their database.

In this chapter, we will work with a sample set of data 
involving
people, tasks they are responsible for, and appointments 
you have
scheduled with them. As you fire employees, you delete 
them from your
person table, but want to be certain that you can't 
leave tasks that
no one is in charge of, or leave appointments where the 
persons name
cannot be located.

(If your not familiar with the concepts of Primary Keys 
and Foreign
Keys, it might be helpful to review a basic database 
primer, as this
tutorial assumes basic knowledge of these concepts.)


First, because we're going playing with the system 
tables, it's best to do
this in a practice database:

# CREATE DATABASE RI_test;
# \c RI_test

Now, let's create practice tables. We'll have one parent 
table, Pers, and
two child tables, Tasks and Appts.

The parent table is straightforward:

# CREATE TABLE Pers (pid INT NOT NULL PRIMARY KEY, 
                     pname TEXT NOT NULL);

The first child table, which will hold tasks for which 
this person is
responsible. 

# CREATE TABLE Tasks (taskid SERIAL NOT NULL PRIMARY KEY,
                      pid INT NOT NULL
                        CONSTRAINT tasks__ref_p
                          REFERENCES Pers,
		      task TEXT NOT NULL);

A few notes:

* foreign key references are handled by PostgreSQL as a 
type of
CONSTRAINT. Therefore, we can begin the referential 
declaration with
CONSTRAINT <name of constraint>. If this is done, the 
error messages
generated by PostgreSQL will return this error name 
(which the user
could helpfully see, or which we could parse and handle 
in a front-end
system.) By wrapping it in system identifiers ("), we 
could even make
it look like an error message (eg CONSTRAINT "Table 
tasks refers to table
Pers"), but we are limited in the length of this 
name/message, and
therefore it's usefulness. CONSTRAINT names are not 
neccessary, and we
could leave this off: ...pid INT NOT NULL REFERENCES 
pers..., in which
case everything works the same, except our constraint is 
unnamed, and
therefore the error messages are more generic.

* after REFERENCES comes the name of the table we are 
referring to,
and (optionally), the name of the field in parentheses. 
If the field
is the primary key, the fieldname is optional. If we had 
not made pid
table Pers's primary key, we would have to say 
REFERENCES Pers(pid).

* optionally, we can inform PostgreSQL how to handle 
deletes/updates
on table Pers by adding ON DELETE and ON UPDATE 
declarations. If these
are not made specific, then PostgreSQL defaults to "no 
action", which
(at the time of this writing, 12/2000) means the same 
thing as
"restrict"

[Ed: my digging into the source code shows that they're 
treated the
same, though there is a comment about SQL3 requiring 
that it be
trapped before... I'm not sure what this is referring 
to, exactly, but
it still seems true that the mean the same. Is this 
accurate?]

, which means that we could neither delete a parent nor
update the parent pid field if a child existed that 
depended on that
parent. In our other child table, we'll see another way 
to handle
this.

* optionally, we could tell PostgreSQL if this is 
"deferrable", that
is, if we are allowed to violate referential integrity 
while inside of
some transaction as long as we have fixed our integrity 
problem at the
end of the transaction. We'll see examples of this 
below, in
DEFERRING.

So, let's add some sample data:

INSERT INTO Pers VALUES (1, 'Jeff Brown');
INSERT INTO Pers VALUES (2, 'Maria Lane');

INSERT INTO Tasks (pid, task) VALUES (1, 'Write 
contract');
INSERT INTO Tasks (pid, task) VALUES (1, 'Upgrade 
database');

So far so good.

If we try to insert a child that has no parent, eg

INSERT INTO Tasks (pid, task) VALUES (3, 'Install 
Linux');

we'll get a referential integrity error. This error will 
refer to our
constraint name (if any), and will block the entering of 
this data
into Tasks. (In addition, if this is part of a 
transaction, it will
rollback the entire transaction, as always happens in 
PostgreSQL).

Also, if we try to change a parent that has children:

DELETE FROM Pers WHERE pname = 'Jeff Brown';

We'll get the same problem, as we cannot delete Jeff as 
long
as he has his two tasks assigned.

DELETE FROM Pers WHERE pname = 'Maria Lane';

(if you actually deleted Maria, add her back in for our 
later
examples.)

because there are currently no child tasks for Maria.

Default (NO ACTION) (or RESTRICT, which means the same 
thing to
PostgreSQL) ON UPDATE and ON DELETE rules make sense for 
many
situations like this. You wouldn't want to be able to 
delete a staff
person from your database if they had certain 
resposibilities,
otherwise you would never know who was in charge of 
different tasks.


A More Permissive Child Table: Appointments

Let's create a second child, Appts (for Appointments):

# CREATE TABLE Appts (apptid SERIAL NOT NULL PRIMARY KEY,
                      pid INT NOT NULL
                        CONSTRAINT appt__ref_pers
                          REFERENCES Pers
                            ON UPDATE CASCADE
                            ON DELETE CASCADE
                            DEFERRABLE,
                      apptsubj TEXT NOT NULL,
                      apptdate DATE NOT NULL);

and insert some data into it:

INSERT INTO Appts (pid, apptsubj, apptdate) 
  VALUES (1, 'Discuss raise', '2001-01-15');
INSERT INTO Appts (pid, apptsubj, apptdate)
  VALUES (2, 'Plan project', '2001-01-20');

This works just fine. Also, just as before,

INSERT INTO Appts (pid, apptsubj, apptdate)
  VALUES (3, 'Termination', '2001-01-20');

does not work, as there is no Pers with pid=3.

However, unlike the Tasks table, Appts is set to CASCADE 
deletes and
updates. This means that if we update a person's pid, or 
delete a
person entirely, PostgreSQL will allow this by first 
deleting any
Tasks that exist for this person.

So:

UPDATE Pers SET pid=14 WHERE pid=2;

This will change Maria's pid to 14, first changing the 
connecting pid
in the Appts table.

DELETE FROM Pers WHERE pid=14;

This will delete Maria's appointment, then delete Maria.

Other Relationships:

Note that CASCADE in this class only refers to the 
CASCADE between the
Pers and Appt tables. Trying to DELETE Jeff would still 
fail because,
although the Pers-Appt relationship would CASCADE, the 
Pers-Tasks
relationship would fail, and PostgreSQL would report 
this DELETE
attempt as a violation of that referential integrity.

Possible actions:

NO ACTION: (the default). Stop the action if an 
update/delete would
fail referential integrity checking.

RESTRICT: currently means same thing as NO ACTION.

CASCADE: delete child data, then delete parent data 
(subject to other
relationships, as noted above)

SET NULL: set foreign key field to NULL, then 
update/delete parent.

SET DEFAULT: set foreign key field to DEFAULT value, then
update/delete parent.


SET NULL and SET DEFAULT can be useful options 
(especially for ON
DELETE). For example, we might have a table, Offices, 
that kept track
of which office a worker used. It might contain fields 
for officeid,
officelocation, and persid. If we wanted to delete a 
person, we
shouldn't be stopped just because that person has a 
related office,
but similarly, we don't want to delete an office just 
because this
person is being deleted. In some cases, the best option 
might be to
set the persid field for Offices to NULL (or DEFAULT), 
leaving the
office in place, but making it clear that this office is 
now unused.

Note that ON UPDATE and ON DELETE can have different 
rules. It's very
common, for instance to ON UPDATE CASCADE but ON DELETE
RESTRICT--allow people to change their person IDs, but 
not allow
deleting of Tasks if a related person exists.

It is possible to change the actions for a relationship, 
but requires
a little hacking in the system catalog tables. See 
Hacking the
Relationships, below.


Deferring

By default, referential integrity is checked for every 
single
relationship, for every single insert, delete, update 
that could
affect this relationship. This means that

INSERT INTO Tasks (pid, task) VALUES (5, 'Open sales 
office');
INSERT INTO Person (pid, name) VALUES (5, 'Helen Kim');

would fail, because at the time of the first attempted 
insert, there
is no person with pid=5. Much of the time, this is the 
most intuitive
setting.

Sometimes, however, you may not be able to predict the 
exact order
data arrives. Perhaps you are receiving data loaded from 
a text file,
or across the web. It's possible that the data may 
arrive in the order
above (task, then associated person). When this happens, 
you can
choose to defer the transaction checking.

In order to defer a transaction, two things must happen:

1) the referential integrity relationship must have been 
defined as
DEFERRABLE. This is not the default (NOT DEFERRABLE is), 
so it must be
declared explicity, as we did for Appts, above.

2) you must be in an explicit transaction.

3) you must either have DEFERRED be the initial default 
for this
relationship, or have SET the relationship constraint to 
DEFERRED for
this transaction.

For our Appts table relationship to Pers, we have 
declared this is as
deferred. So, if we do

BEGIN;
SET CONSTRAINTS ALL DEFERRABLE;
INSERT INTO Appts (pid, apptsubj, apptdate)
  VALUES (5, 'Negotiation', '2001-01-20');
INSERT INTO Pers VALUES (5, 'Helen Kim');
COMMIT;

works just fine. Note that having the relationship 
declared as
DEFERRABLE is not enough--we must also use SET to 
explicity set
CONSTRAINTS to deferred. In this example, we set all 
relationships to
DEFERRED; instead we could set only a single constraint 
to deferred,
as in

SET CONSTRAINTS appts__ref_pers DEFERRABLE;

It may be convenient to have a relationship already set, 
rather than
having to set this for every transaction. To do this, 
add "INITIALLY
DEFERRED" to the CONSTRAINT ... REFERENCES declaration 
for the table,
eg

# CREATE TABLE Appts (apptid SERIAL NOT NULL PRIMARY KEY,
                      pid INT NOT NULL
                        CONSTRAINT appt__ref_pers
                          REFERENCES Pers
                            ON UPDATE CASCADE
                            ON DELETE CASCADE
                            DEFERRABLE
			    INITALLY DEFERRABLE,
                      apptsubj TEXT NOT NULL,
                      apptdate DATE NOT NULL);

Now, we could simply

BEGIN;
INSERT INTO Appts (pid, apptsubj, apptdate)
  VALUES (5, 'Negotiation', '2001-01-20');
INSERT INTO Pers VALUES (5, 'Helen Kim');
COMMIT;

With no explicit SET command. This is especially 
convenient if you
work in a programming setting that abstracts SQL 
commands and makes it
difficult to execute an arbitrary, nonstandard SQL 
standard such as
"SET...".

[Ed: I assume that deferred could be a bit faster for 
large inserts,
too, since the subroutine would be exited out of quickly 
until the
very last check. Is there enough time difference/can 
anyone verify
this?]


Hacking Referential Integrity

Referential integrity works great in PostgreSQL. 
However, PostgreSQL
does not (yet) have SQL-synax commands to change 
actions, turn on/off
RI, etc. However, as this information is stored in the 
system catalog
tables, it can be performed by editing these tables 
directly.

WARNING: to edit the system catalog tables, you must be 
a superuser in
PostgreSQL. In addition, you should be VERY CAREFULLY 
when editing
these tables, and make sure that you have a backup first 
(via
pg_dump). An accidental table-wide UPDATE or DELETE 
could delete all
of your tables, ruins your indexes, corrupt your 
database,
etc. Practice this by working in a test database, 
preferrably even on
a machine without any other critical databases.

There are several system catalog tables of interest to 
us:

pg_class  all "classses", included tables, views, 
sequences, etc.
pg_trigger    all triggers. PostgreSQL handles 
referential integrity
using behind-the-scenes triggers, so this is where all 
of your RI
controls are stored.
pg_proc	 all PostgreSQL procedures. We won't need to 
make any
changes to this, but will use this to show what the RI 
procedures used
actually are.

To see (most) of the system tables, you can use the 
command \dS in
psql.

For example, for our test tables, let's collect 
information from
pg_class about our tables.

# SELECT oid, relname FROM pg_class WHERE relname IN
('pers','appts','tasks');

Note that PostgreSQL usually downcases non-quoted system 
identifiers
like tables for us automatically (that is, I can CREATE 
TABLE foo but
SELECT * FROM FOO.) However, when examining pg_class, 
you must work in
a case-sensitive manner, or use case-insensitive 
operators.

This query returns

  oid | relname
--------+---------
 9100 | pers
 9110 | tasks
 9120 | appts
(3 rows)

(Your OIDs will be different. Don't worry, just notice 
what they are.)

Now, if we look in pg_trigger, we can find the triggers 
that are used
by our tables.

test2=# SELECT * from pg_trigger WHERE tgrelid in
(9100, 9110, 9120);

 tgrelid |           tgname            | tgfoid | tgtype 
| tgenabled |
tgisconstraint |  tgconstrname  | tgconstrrelid | 
tgdeferrable |
tginitdeferred | tgnargs | tgattr |
tgargs
---------+-----------------------------+--------+--------
+-----------+----------------+----------------+----------
-----+--------------+----------------+---------+--------
+--------------------------------------------------------
----------
  263721 | RI_ConstraintTrigger_263752 |   1644 |     21 
| t         |
t              | tasks__ref_p   |        263674 | f
      | f              |       6 |        |
tasks__ref_p\000tasks\000pers\000UNSPECIFIED\000pid\000pi
d\000
  263674 | RI_ConstraintTrigger_263754 |   1654 |      9 
| t         |
t              | tasks__ref_p   |        263721 | f
      | f              |       6 |        |
tasks__ref_p\000tasks\000pers\000UNSPECIFIED\000pid\000pi
d\000
  263674 | RI_ConstraintTrigger_263756 |   1655 |     17 
| t         |
t              | tasks__ref_p   |        263721 | f
      | f              |       6 |        |
tasks__ref_p\000tasks\000pers\000UNSPECIFIED\000pid\000pi
d\000
  263776 | RI_ConstraintTrigger_263808 |   1644 |     21 
| t         |
t              | appt__ref_pers |        263674 | t
      | f              |       6 |        |
appt__ref_pers\000appts\000pers\000UNSPECIFIED\000pid\000
pid\000
  263674 | RI_ConstraintTrigger_263810 |   1646 |      9 
| t         |
t              | appt__ref_pers |        263776 | t
      | f              |       6 |        |
appt__ref_pers\000appts\000pers\000UNSPECIFIED\000pid\000
pid\000
  263674 | RI_ConstraintTrigger_263812 |   1647 |     17 
| t         |
t              | appt__ref_pers |        263776 | t
      | f              |       6 |        |
appt__ref_pers\000appts\000pers\000UNSPECIFIED\000pid\000
pid\000
(6 rows)

The columns in pg_trigger are:

tgrelid	    What table is this trigger on. That is, 
which table is
inserted/updated/deleted that calls that this trigger.
tgname			 Name of this trigger. Trigger names are
generated by the referential integrity feature of 
PostgreSQL are are
unimportant, except that they are unique.
tgfoid	     OID of the function that is called. This is 
an important
column--by changing the function called, we can change 
which action
(CASCADE, etc.) is performed. (See below).
tgtype	  What kind of trigger is this (is it UPDATE, 
DELETE, etc.)
tgenabled Is this trigger enabled?
tgisconstraint	  Is this trigger part of a constraint. 
Non-RI
triggers that are user-declared by CREATE TRIGGER may be 
FALSE for
this, but RI triggers will always be true.
tgconstrname Name of constraint that calls this trigger. 
If you named
your constraints (as we did, eg pers__ref_tasks), this 
will be the
constraint name, otherwise it will be unnamed.
tgconstrrelid	 OID of table that which had the constraint 
set.
tgdeferrable	 Can this constraint be deferred? 
(Equivalent to
DEFERRABLE. See above.)
tginitdeferred	Is this constraint initially deferred? 
(Equivalent to
INITIALLY DEFERRED. See above.)
tgnargs	  Number of arguments for the RI function. As 
of PostgreSQL
7.1, this always seems to be 6, and should not be edited.
tgattr	  ???
tgargs	  The actual arguments to the RI function. 

To help make this more understandable and usable, a view 
is helpful:

CREATE VIEW dev_ri
AS 
SELECT   t.oid as trigoid,
          c.relname as trig_tbl,
          t.tgfoid, 
          f.proname as trigfunc,
          t.tgenabled,
          t.tgconstrname,
          c2.relname as const_tbl,
          t.tgdeferrable,
          t.tginitdeferred
FROM      pg_trigger t,
          pg_class c, 
          pg_class c2, 
          pg_proc f 
WHERE     t.tgrelid=c.oid 
 AND      t.tgconstrrelid=c2.oid 
 AND      tgfoid=f.oid
 AND      tgname ~ '^RI_'
ORDER BY  t.oid;

(This view requires PostgreSQL 7.1 because of the ORDER 
BY. For
versions earlier than 7.1, you can remove the ORDER BY.)

# select * from dev_ri;

 trigoid | trig_tbl | tgfoid |       trigfunc       | 
tgenabled |
tgconstrname  | const_tbl | tgdeferrable | tginitdeferred
---------+----------+--------+----------------------+----
-------+----------------+-----------+--------------+-----
-----------
  263753 | tasks    |   1644 | RI_FKey_check_ins    | t  
       |
tasks__ref_p   | pers      | f            | f
  263755 | pers     |   1654 | RI_FKey_noaction_del | t  
       |
tasks__ref_p   | tasks     | f            | f
  263757 | pers     |   1655 | RI_FKey_noaction_upd | t  
       |
tasks__ref_p   | tasks     | f            | f
  263809 | appts    |   1644 | RI_FKey_check_ins    | t  
       |
appt__ref_pers | pers      | t            | f
  263811 | pers     |   1646 | RI_FKey_cascade_del  | t  
       |
appt__ref_pers | appts     | t            | f
  263813 | pers     |   1647 | RI_FKey_cascade_upd  | t  
       |
appt__ref_pers | appts     | t            | f
(6 rows)

Now, it's much easier to understand what's happening. 
For example,
from trigger oid=263753, we can see that on inserts to 
the Tasks
table, RI_FKey_check_ins is called, which checks the 
Pers table.