sequences and RULEs

Hi All!

I've seen sometning unexpected here. I'd apreciate it if someone could
give me a hint of why this have happened and may be a sugesstion of a
workaround.

I'm writing "Building Access Control System" (BACS). My environment is
"Debian testing" with "their current" postgresql version: 8.3.7.

I my BACS, there are:
1. dors/gates, which register in/out events
2. ... of personal badges
3. ... and the always is current head-count status record for the
building.

So I:

CREATE TABLE badges (
id int not null unique,
employee int references people(id)
);

CREATE TABLE gates (
id int not null unique,
location int references locations(id),
);

CREATE TABLE eventlog (
id serial unique,
pass timestamp not null default current_timestamp,
in_out bool not null,
gate int not null references gates(id),
badge int not null references badges(id),
hc_before int -- STATUS.HEAD_COUNT before this event
);

CREATE TABLE status (
head_count int,
recent int references eventlog(id),
);

Now I'd like to write a RULE, that automatically updates references
between EVENTLOG and STATUS:

CREATE RULE hit AS ON INSERT TO eventlog do (update status set
head_count = head_count+ (case when new.in_out then -1 else 1
end )::integer, recent=new.id );

Now, testing it all I get:
--------------------------------------------------
hc=# SELECT * from eventlog;
id | pass | in_out | gate | badge
----+------+--------+------+-------
(0 rows)

hc=# INSERT INTO eventlog (gate,badge,in_out) VALUES (1,2,true);
ERROR: insert or update on table "eventlog" violates foreign key
constraint "eventlog_badge_fkey"
DETAIL: Key (badge)=(2) is not present in table "badges".
hc=# INSERT INTO eventlog (gate,badge,in_out) VALUES (1,1,true);
ERROR: insert or update on table "status" violates foreign key
constraint "status_recent_fkey"
DETAIL: Key (recent)=(3) is not present in table "eventlog".
hc=# SELECT * from eventlog;
id | pass | in_out | gate | badge
----+------+--------+------+-------
(0 rows)

hc=# \d status
Table "public.status"
Column | Type | Modifiers
------------+---------+-----------
head_count | integer |
recent | integer |
Foreign-key constraints:
"status_recent_fkey" FOREIGN KEY (recent) REFERENCES eventlog(id)

hc=# ALTER TABLE status drop CONSTRAINT status_recent_fkey;
ALTER TABLE
hc=# INSERT INTO eventlog (gate,badge,in_out) VALUES (1,1,true);
INSERT 0 1
hc=# SELECT * from eventlog;
id | pass | in_out | gate | badge
----+----------------------------+--------+------+-------
4 | 2009-06-05 11:03:09.918096 | t | 1 | 1
(1 row)

hc=# SELECT * from status;
head_count | recent
------------+--------
-1 | 5
(1 row)
--------------------------------------------------

which is surprising and unexpected.

The NEW tuple of the table EVENTLOG, in its ID field at the moment of
RULE execution has a value of 5! But after everything is finished, the
actual value deposited in that record is 4.

Naturaly this trancient relation makes it impossible to store a
reference to newly created EVENT inside of a STATUS table as
CURRENT_STATUS record..... while this is my ultimate goal.

So comes my questions:
1. Is this a feature or a bug, that such inconsistent NEW tuple show up
during RULE execution?

2. It looks like I'd have to use TRIGGER FOR EACH ROW in this case. But
may be there is some othar way, that I could achieve this with the RULE
system?

Thenx,

-R

Rafal Pietrak wrote:
> Now I'd like to write a RULE, that automatically updates references
> between EVENTLOG and STATUS:

> The NEW tuple of the table EVENTLOG, in its ID field at the moment of
> RULE execution has a value of 5! But after everything is finished, the
> actual value deposited in that record is 4.

A rule rewrites the query, so you can end up with parts being evaluated
twice. For functions with side-effects like nextval() that causes
problems. There are other issues too.

> So comes my questions:
> 1. Is this a feature or a bug, that such inconsistent NEW tuple show up
> during RULE execution?

It's a (confusing) feature.

> 2. It looks like I'd have to use TRIGGER FOR EACH ROW in this case. But
> may be there is some othar way, that I could achieve this with the RULE
> system?

Triggers for keeping count of things. See the mailing list archives for
details.

--
Richard Huxton
Archonet Ltd

On Fri, 2009-06-05 at 14:32 +0100, Richard Huxton wrote:
> Rafal Pietrak wrote:
> > The NEW tuple of the table EVENTLOG, in its ID field at the moment of
> > RULE execution has a value of 5! But after everything is finished, the
> > actual value deposited in that record is 4.
>
> A rule rewrites the query, so you can end up with parts being evaluated
> twice. For functions with side-effects like nextval() that causes
> problems. There are other issues too.

I'd sort of understood it, if nextval() looked like actually evaluated
twice ... which should show up as EVENTLOG getting gaps on SERIAL ID.

But there are no gaps in eventlog ID sequence.

Which is really puzzling.

> > So comes my questions:
> > 1. Is this a feature or a bug, that such inconsistent NEW tuple show up
> > during RULE execution?
>
> It's a (confusing) feature.

OK. If that's a feature I'd just have to learn it.

Thenx for the explanations,

-R