Adjacency List & total item counts - Mailing list pgsql-general

Hi

This question is not specific to PostgreSQL but I would like to know
what is the best way to count the number of items in each node from
the leaf to the root? Something like this:

                             Computers (100)
                                    /\
                                   /  \
                     CPU (15)      Memory (85)

I have the following SQL schema:

Tree (
     treeId int,
     parentId int,
     name varchar(250),
)

Item (
    itemId int,
    treeId int,
    expiryDate date
)

Note that the count for the total number of items in each node depends
on the item expiry date, i.e. ignore the item if the expiry date is
older than now().

I have come up with the following solutions but not happy with any one of them:

1) Do a batch count, i.e. count the number of items every 30 minutes.
Using this method defeats the purpose of having the count next to each
node since the number might not be the same as the actual count.

2) Use trigger but this can be slow since it has to recurse the tree
and do the sum every time new item is added.

Thanks
Ben