Skip site navigation (1) Skip section navigation (2)

bug? rules fail to cascade after NOT IN

From: Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: bug? rules fail to cascade after NOT IN
Date: 2003-03-03 19:43:30
Message-ID: w665r0utgd.fsf@guinness.ts.gatech.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
We have verified this problem under both 7.3.2 and the CVS tip.

The attached example is far simpler than the actual code in our
application, but may nevertheless benefit from some explanation.  We
have several tables with two ON INSERT rules:

    [TABLE policy_accounts]
              |
              | ON INSERT
              V
      RULE executor_active  <-  [TABLE accounts_on_hold]
              |
              V
 [TABLE policy_accounts_active]
              |
              | ON INSERT
              V
      RULE executor_hamlet  <-  [TABLE policy_hamlet_atoms]
              |
              V
   [TABLE account_instances]

The accounts_on_hold lists user accounts for which no changes or
updates should currently be made; so the the executor_active rule
copies new rows from policy_accounts to policy_accounts_active only
for accounts which are not listed in accounts_on_hold.

Our system manages computer accounts across a university campus.
Since a given account might exist on several machines, we have given
the name `account instance' to the idea of an account on a particular
system - so if the account `tgl' were given access to both a Linux
interactive machine and a web email server, we would say that two
`instances' of the account currently existed.

The policy_hamlet_atoms table lists, for each policy, which systems
the policy grants access to.  So when a new row appears in
policy_accounts_active, linking an account to a new policy, the
executor_hamlet rule examines the policy_hamlet_atoms table and
creates any new account_instances that are necessary.

(Thus hamlet decides which account instances are to be, or not to be.)

Our problem is that if we add a NOT IN clause to the executor_active
rule to prevent duplicate inserts into the policy_accounts_active
table, then the executor_hamlet rule will not fire for rows inserted
by the executor_active rule.  While we are beginning to learn how to
read parse trees, we are not yet proficient enough to see what is
going on here.

Two test cases are attached.

The `broken' test case has the NOT IN clause, and incorrectly does
*not* create an account_instances row when executor_active does an
INSERT to policy_accounts_active.  The test code then goes on to
delete and re-insert the row by hand, showing that executor_hamlet
*will* fire if the INSERT to policy_accounts_active is not done
through executor_active.

The `working' test case omits the

    AND (account, policy) NOT IN
     (SELECT account, policy FROM policy_accounts_active)

condition from the end of executor_active, which magically makes the
executor_hamlet rule start firing as it should.

Any help or correction of our techniques will be appreciated!


Attachment: test-working.sql
Description: application/octet-stream (1.5 KB)
Attachment: test-broken.sql
Description: application/octet-stream (1.8 KB)

Responses

pgsql-hackers by date

Next:From: Neil ConwayDate: 2003-03-03 20:29:55
Subject: Re: Yet another open-source benchmark
Previous:From: scott.marloweDate: 2003-03-03 18:17:01
Subject: Re: Yet another open-source benchmark

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group