Storing a chain - Mailing list pgsql-general

From Johan Fredrik Øhman
Subject Storing a chain
Date
Msg-id E1AMESg-0007HY-Ed@mail-mx1.uio.no
Whole thread Raw
List pgsql-general
Hi,
I'm seeking some opinion on the following problem

For those familiar with "iptables/netfilter", I am actually storing firewall
rules in the database. The order of these rules is critical, so there has to
be some kind of system to how they are ordered. As I see it there are at
least 2 approaches.

1) Use a Sequence number in the table. This way it is possible to use ORDER
BY sequencenumber to retrive the correct list.  This works, but you have to
do some massive updating when you what to insert a rule between i.e "12" and
"13".


CREATE TABLE FW_CHAIN (
         FW_CHAIN_ID          INT4                 not null,
         PARENT_ID            INT4                 null,
          ....  fields ..............
         constraint PK_FW_CHAIN primary key (FW_CHAIN_ID)
);

2) Use a Parent_Id, as described intended in the table above.  I feel that
this is a little prettier, even if it might be somewhat less efficient.
Unfortunately, I'm really not sure how to perform the queries.  As I see it
some kind of recursive select statement is needed. Cursors ?  Stored
Procedure?

If somebody has the time, I'd really appreciate some links to relevant
examples, documentation or advice on how to solve this.

--
Johan Fredrik Øhman



pgsql-general by date:

Previous
From: Lynn.Tilby@asu.edu
Date:
Subject: Re: Better Unilization of Memory
Next
From: "Randolf Richardson, DevNet SysOp 29"
Date:
Subject: Re: Good open source mailing list system PHP / Postgresql