Seeking help understanding Postgres rules and why my rule doesn't
work.

I have a table for 'person' and a person can have one or more email
addresses.

CREATE TABLE person
( id SERIAL PRIMARY KEY,
mtime TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE email_address
( id SERIAL PRIMARY KEY,
email_address VARCHAR(128) NOT NULL,
end_at TIMESTAMP WITH TIME ZONE,
person_id INTEGER NOT NULL REFERENCES person (id),
start_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

A view shows a person's current email address.

CREATE VIEW person_email (id, contact_email) AS
SELECT p.id, c.email_address
FROM person p
JOIN email_address c ON c.person_id = p.id AND c.end_at IS NULL;

I want to make this view update-able such that on update the new email
address will replace its predecessor in the view row but the
predecessor is preserved in the email_address table. This is what I
came up with for insert.

CREATE RULE person_email_insert AS ON INSERT TO person_email
DO INSTEAD
(
INSERT INTO person (id) VALUES (DEFAULT)
;
INSERT INTO email_address (email_address, person_id)
VALUES (NEW.contact_email, currval('person_id_seq'))
;
);

This insert rule works fine, e.g.

km=> insert into person_email (contact_email) values ('one');
INSERT 0 1
km=> select * from person;
id | mtime
----+-------------------------------
1 | 2010-04-21 04:40:24.566566+00
(1 row)

km=> select * from email_address;
id | email_address | end_at | person_id | start_at
----+---------------+--------+-----------+--------------------
1 | one | | 1 | 2010-04-21 04:40:24
(1 row)

km=> select * from person_email;
id | contact_email
----+---------------
1 | one
(1 row)

For update, I tried

CREATE RULE person_email_update AS ON UPDATE TO person_email
DO INSTEAD
(
UPDATE email_address SET end_at = CURRENT_TIMESTAMP
WHERE NEW.contact_email <> OLD.contact_email
AND person_id = OLD.id AND end_at IS NULL
;
INSERT INTO email_address (email_address, person_id)
SELECT NEW.contact_email, OLD.id
WHERE NEW.contact_email <> OLD.contact_email
;
--
UPDATE person SET mtime = CURRENT_TIMESTAMP
WHERE id = OLD.id
;
);

The update rule doesn't work.

km=> update person_email set contact_email = 'two';
UPDATE 0
km=> select * from person;
id | mtime
----+-------------------------------
1 | 2010-04-21 04:40:24.566566+00
(1 row)

The person row wasn't touched.

km=> select * from email_address;
id | email_address | end_at | person_id | start_at
----+---------------+---------------------+-----------+---------------------
1 | one | 2010-04-21 04:40:42 | 1 | 2010-04-21 04:40:24
(1 row)

The rule's first UPDATE did what I expected by setting end_at, but the
new row for email_address 'two' did not appear. It's as if the rule
stopped at that UPDATE and discarded the INSERT and the update of
person.mtime.

What went wrong? This is on 8.4.2 on Debian.
Thanks
--
KM

On Wed, 21 Apr 2010 05:07:35 +0000, KM wrote:

What does this condition mean, exactly? If I am not mistaken, neither of
these two identifiers (NEW.contact_email, OLD.contact_email) identifies a
column in the email_address table?

Where are you selecting this from? The rule creates but I am not at all
clear as to what should it do?

I tried modifying things myself but it didn't make much sense:

CREATE RULE person_email_update AS ON UPDATE TO person_email
DO INSTEAD
(
UPDATE email_address SET end_at = CURRENT_TIMESTAMP
WHERE email_address <> NEW.contact_email
AND person_id = OLD.id AND end_at IS NULL
;
INSERT INTO email_address (email_address, person_id)
VALUES(NEW.contact_email,OLD.id);
--
UPDATE person SET mtime = CURRENT_TIMESTAMP
WHERE id = OLD.id
;
);
CREATE RULE
mgogala=#
mgogala=# select * from person_email
;
id | contact_email
----+---------------
3 | one
(1 row)

mgogala=#
mgogala=# update person_email set contact_email = 'two';
UPDATE 0
mgogala=# select * from person_email
;
id | contact_email
----+---------------
(0 rows)

mgogala=# select * from email_address;
id | email_address | end_at | person_id
----+---------------+-------------------------------+-----------
+---------------
----------------
3 | one | 2010-04-21 23:38:46.798049-04 | 3 |
2010-04-21 23:
13:58.835275-04
(1 row)

mgogala=# select * from person;
id | mtime
----+-------------------------------
3 | 2010-04-21 23:13:58.835275-04
(1 row)

mgogala=# \dv
List of relations
Schema | Name | Type | Owner
--------+--------------+------+---------
public | person_email | view | mgogala
(1 row)


--
http://mgogala.byethost5.com

On 2010-04-22, Mladen Gogala <gogala.mladen (AT) gmail (DOT) com> wrote:
That the new contact_email differs from the old.

Right, the column is on the view person_email.

From 'thin air', same as if you "SELECT 2 + 2 WHERE TRUE;" at a psql
prompt.

My intention was that it inserts a new row in email_address with the
old row marked with a value in end_at to show that it is no longer
current. I think your modification suffers from the same problem as
my attempt, which I think I understand now. I'm going to follow up
with what I think is the source of the trouble. Thanks for the reply.
--
KM

On 2010-04-21, KM <km (AT) xacrasis (DOT) netx> wrote:
The version below fixes the update problem. What I think happens is
that the NEW values evolve as the rule is executed.

The first command in the update rule retired the old email address,
leaving the person at that point with no current email address. The
view person_email no longer had a row to provide any NEW values and
the 2nd and 3rd commands therefore affected no rows.


CREATE TABLE person
( id SERIAL PRIMARY KEY,
mtime TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE email_address
( id SERIAL PRIMARY KEY,
email_address VARCHAR(128) NOT NULL,
end_at TIMESTAMP WITH TIME ZONE,
person_id INTEGER NOT NULL REFERENCES person (id),
start_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE VIEW person_email (id, contact_email) AS
WITH current_email AS
( SELECT DISTINCT ON (person_id)
person_id, email_address
FROM
email_address
WHERE
end_at IS NULL
ORDER BY
person_id, start_at DESC
)
SELECT p.id, c.email_address
FROM person p
JOIN current_email c ON c.person_id = p.id;

CREATE RULE person_email_insert AS ON INSERT TO person_email
DO INSTEAD
(
INSERT INTO person (id) VALUES (DEFAULT)
;
INSERT INTO email_address (email_address, person_id)
VALUES (NEW.contact_email, currval('person_id_seq'))
;
);

CREATE RULE person_email_update AS ON UPDATE TO person_email
DO INSTEAD
(
-- Add the new email before removing the old, so the person
-- is never without a current email.
INSERT INTO email_address (email_address, person_id)
SELECT NEW.contact_email, NEW.id
WHERE NEW.contact_email IS NULL OR NEW.contact_email <> OLD.contact_email
;
UPDATE email_address SET end_at = CURRENT_TIMESTAMP
WHERE person_id = OLD.id AND end_at IS NULL
AND id < currval('email_address_id_seq')
;
--
UPDATE person SET mtime = CURRENT_TIMESTAMP
WHERE id = NEW.id
;
);

--
KM

On Thu, 22 Apr 2010 04:42:03 +0000, KM wrote:

With rewriting, you should exercise more care. Pulling things out
of..thin air is usually not a good solution.



--
http://mgogala.byethost5.com