sequences and RULEs - Mailing list pgsql-general

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