Thread: unexpected output of rule

unexpected output of rule

From
Jaap Remerie
Date:
Platform used    : Redhat Linux 7.2 kernel 2.4.20-28.7
Version Postgresql: postgresql-7.3.4-2PGDG

Problem:

--The following rule was created:--

create rule tbl_notities_project_id as
on insert to tbl_project_1
do
insert into tbl_notities (project_id) values (new.project_id);



--tbl_project_1 is the following table--

         Column        |      Type      |                           Modifiers

----------------------+----------------+----------------------------------------

  project_id           | integer        | not null default
nextval('tbl_project_1
_project_id_seq'::text)
  projectnaam          | character(50)  |
  zoeknaam             | character(12)  |
  projectomschrijving  | character(50)  |
  adres_project        | character(40)  |
  postcode_project     | character(7)   |
  plaatsnaam           | character(50)  |
  datum_invoer_project | date           |
  relatie_id           | integer        |
  memo_bak             | character(254) |
  projectnummer_klant  | character(32)  |
  projectstatus        | character(8)   |
  memo                 | text           |
  bvo                  | real           |
Indexes: tbl_project_1_pkey primary key btree (project_id)
Rules: tbl_notities_project_id

--tbl_notities is the following table--

    Column   |           Type           |
Modifiers
------------+--------------------------+---------------------------------------------------------------
  notitie_id | integer                  | not null default
nextval('tbl_notities_notitie_id_seq'::text)
  project_id | bigint                   |
  datum      | timestamp with time zone |
  notitie    | text                     |
Indexes: tbl_notities_pkey primary key btree (notitie_id)
Rules: wijzig_notitie

When a new record is inserted in tbl_project_1, the value of project_id in
tbl_notities is raised by 1.
The expected value "389" appeared in tbl_notities as "390". It seemed as if
"tbl_project_1_project_id_seq" was used in the created rule for the value
of "new.project_id".

The suspected behavior was confirmed when a new record in tbl_project_1 was
inserted. The new project_id was 391 instead of the expected 390.

Yours Sincerely,

Jaap Remerie


Remerie Bouw
Da Costastraat 111 huis
1053 ZJ AMSTERDAM
T. 020-6125072
F. 020-6125119
http://www.remerie.com