Display the updated dependencies when a dependent port updates

A great feature of FreshPorts is the ability to see all the ports which are dependent upon a given port. For example, it allows you to see what ports depend upon security/polarssl. This list is stored in a the port_dependencies table, which looks like this:

freshports.org=# \d port_dependencies
         Table "public.port_dependencies"
         Column         |     Type     | Modifiers 
------------------------+--------------+-----------
 port_id                | integer      | not null
 port_id_dependent_upon | integer      | not null
 dependency_type        | character(1) | not null
Indexes:
    "port_dependencies_pkey" PRIMARY KEY, btree (port_id, port_id_dependent_upon, dependency_type)
Foreign-key constraints:
    "port_dependencies_port_id_dependent_upon_fkey" FOREIGN KEY (port_id_dependent_upon) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    "port_dependencies_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE

freshports.org=# 

This information is gathered when a port is updated. The data is used by both ports (i.e. port_id and port_id_dependent_upon) and is shown on the page for both ports. One port lists the other either under This port is required by: or under Dependencies (e.g. Build dependencies).

There is a bug where the dependent-upon port does not correctly display all dependent ports. The root cause is a caching issue. The page for a given port is cached until that port is updated, thus a dependent-upon-port will not refresh its list if a new dependency arises.

For example, consider security/polarssl on 14 Jul 2015 where you would have seen:

This port is required by:

for Libraries
emulators/dolphin-emu
security/openvpn-polarssl

On 5 Jul 2015, a new port was created security/openvpn-polarssl which was dependent upon security/polarssl. In this case, security/openvpn-polarssl would correctly display its dependent ports, however security/polarssl did not.

It was Avengence and mandree who pointed this problem out and led to the issue in question.

It was clearly a caching issue as demonstrated by clearing the cache for security/polarssl which then correctly included the new port.

The solution

The solution centered around stored procedures (SP). I created a new SP based upon an existing one.

CREATE OR REPLACE FUNCTION port_dependencies_clear_cache() RETURNS TRIGGER AS $$
   DECLARE
      l_cache_clearing_ports_id   int8;
      l_port      text;
      l_category  text;
   BEGIN
      IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
         INSERT INTO cache_clearing_ports (port_id, category, port)
            SELECT distinct id, category, name
              FROM port_dependencies PD, ports_all PA
             WHERE PD.port_id                = NEW.id
               AND PD.port_id_dependent_upon = PA.id
               AND NOT EXISTS (SELECT port_id 
                                 FROM cache_clearing_ports
                                WHERE port_id = PD.port_id_dependent_upon);

          NOTIFY port_updated;
      END IF;

      IF TG_OP = 'DELETE' THEN
         INSERT INTO cache_clearing_ports (port_id, category, port)
            SELECT distinct id, category, name
              FROM port_dependencies PD, ports_all PA
             WHERE PD.port_id                = OLD.id
               AND PD.port_id_dependent_upon = PA.id
               AND NOT EXISTS (SELECT port_id 
                                 FROM cache_clearing_ports
                                WHERE port_id = PD.port_id_dependent_upon);

          NOTIFY port_updated;
      END IF;

      -- when a port changes, add an entry to the cache clearing table
      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';

  DROP TRIGGER port_dependencies_clear_cache ON ports;
CREATE TRIGGER port_dependencies_clear_cache
    AFTER UPDATE OR INSERT OR DELETE on ports
    FOR EACH ROW
    EXECUTE PROCEDURE port_dependencies_clear_cache();

This is a trigger which will be invoked each time there is an update, insert, or delete on the ports table (see line 40). The SP will queue up a cache clear for all ports which the given port is dependent upon. This queueing is accomplished by the highlighting code, one being slightly different from the other because of the source of the port id for a DELETE (i.e. you get it off the OLD record).

The test

Here is the test I ran to confirm the correct action:

freshports.org=# begin;
BEGIN
freshports.org=# select * from cache_clearing_ports;
 id | port_id | category | port | date_added 
----+---------+----------+------+------------
(0 rows)

freshports.org=# update ports set patch_depends = '***' where id = 36926;
UPDATE 1
freshports.org=# select * from cache_clearing_ports;
   id    | port_id | category  |       port       |         date_added         
---------+---------+-----------+------------------+----------------------------
 2151680 |   15203 | archivers | lzo2             | 2015-08-09 10:35:03.292856
 2151681 |   32213 | security  | easy-rsa         | 2015-08-09 10:35:03.292856
 2151682 |   31511 | devel     | pkgconf          | 2015-08-09 10:35:03.292856
 2151683 |   25927 | security  | polarssl         | 2015-08-09 10:35:03.292856
 2151684 |   36926 | security  | openvpn-polarssl | 2015-08-09 10:35:03.292856
(5 rows)

freshports.org=# rollback;
ROLLBACK
freshports.org=# 

In this test, I do a fake update to port.id = 36926 (security/openvpn-polarssl). After the update, you can see that the cache_clearing_ports now lists all the dependent ports and itself. These cache entries will be removed when the fp-list wakes up and processes the table.

I had planned to work on the above issue during my flight home from DefCon, but that is tomorrow.