Mar 112018
 

Databases use relational integrity to enforce expected situations. A common scenario is duplicates. Case in point, I present the port_dependencies table:

freshports.org=# \d port_dependencies
                    Table "public.port_dependencies"
         Column         |     Type     | Collation | Nullable | Default 
------------------------+--------------+-----------+----------+---------
 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
Triggers:
    port_dependencies_delete_clear_cache AFTER DELETE ON port_dependencies FOR EACH ROW EXECUTE PROCEDURE port_dependencies_delete_clear_cache()
    port_dependencies_insert_clear_cache AFTER INSERT ON port_dependencies FOR EACH ROW EXECUTE PROCEDURE port_dependencies_insert_clear_cache()

freshports.org=# 

For those not familiar with FreeBSD ports, each port (you could also refer to them as a package or application) can have zero or more dependencies. The FreshPorts database extracts and lists these dependencies for convenient viewing.

As you can see in the above table definition, for a specific (port_id), a given dependency type (port_id_dependent_upon, dependency_type) can only occur once. There is no reason for a port to be dependent upon something twice.

Or so you, or I, might think.

Then this happened:

Could not execute SQL SELECT PortsDependenciesAdd( 'net/ceph', 'devel/boost-python-libs', 'L' ) as result ... 
maybe invalid? ERROR:  duplicate key value violates unique constraint "port_dependencies_pkey"

It was part of this commit on net/ceph. Let’s find out where FreshPorts went wrong.

Clearly, something tried to record a duplicate dependency. The code doesn’t look for it, and did not detect the violation.

The log file

FreshPorts logs a lot of stuff, because it can. It has always been useful for situations such as this.

Here is what I found:

depends with this: 'libboost_python.so:devel/boost-python-libs@py27 libboost_python.so:devel/boost-python-libs
libboost_thread.so:devel/boost-libs libleveldb.so:databases/leveldb libnss3.so:security/nss
libcryptopp.so:security/cryptopp libsnappy.so:archivers/snappy libcurl.so:ftp/curl libxml2.so:textproc/libxml2
libexpat.so:textproc/expat2 liblz4.so:archivers/liblz4 libplds4.so:devel/nspr libtcmalloc.so:devel/google-perftools
libfuse.so:sysutils/fusefs-libs libintl.so:devel/gettext-runtime libldap-2.4.so.2:net/openldap24-client'

The next entry referred to:

The 'L' depends are: devel/boost-python-libs@py27 - devel/boost-python-libs - devel/boost-libs - 
databases/leveldb - security/nss - security/cryptopp - archivers/snappy - ftp/curl - textproc/libxml2 - 
textproc/expat2 - archivers/liblz4 - devel/nspr - devel/google-perftools - sysutils/fusefs-libs -
devel/gettext-runtime - net/openldap24-client

Then the code starts processing them, one by one:

adding in devel/boost-python-libs@py27 which converts to 'devel/boost-python-libs'
sql is SELECT PortsDependenciesAdd( 'net/ceph', 'devel/boost-python-libs', 'L' ) as result
result is 1

As you can see, it strips the flavor (@py27) from the dependency. I think I know where this is going.

And then the next one:

adding in devel/boost-python-libs which converts to 'devel/boost-python-libs'
sql is SELECT PortsDependenciesAdd( 'net/ceph', 'devel/boost-python-libs', 'L' ) as result
Could not execute SQL SELECT PortsDependenciesAdd( 'net/ceph', 'devel/boost-python-libs', 'L' ) as result ...
maybe invalid? ERROR:  duplicate key value violates unique constraint "port_dependencies_pkey"
DETAIL:  Key (port_id, port_id_dependent_upon, dependency_type)=(45100, 25346, L) already exists.
CONTEXT:  SQL statement "INSERT INTO port_dependencies (port_id, port_id_dependent_upon, dependency_type)
            VALUES (l_PortID, l_PortIDDependency, p_DependencyType)"
PL/pgSQL function portsdependenciesadd(text,text,text) line 19 at SQL statement
that failed

Ahh, OK, that’s why. I see now.

Let’s go to the source.

The source

This isn’t the source, it’s the Makefile from that commit. Looking at lines 25-28 we see:

25	LIB_DEPENDS=    \
26	        ${PY_BOOST} \
27	        libboost_python.so:devel/boost-python-libs \
28	        libboost_thread.so:devel/boost-libs \

I bet it’s coming from here:

$ make -V PY_BOOST
libboost_python.so:devel/boost-python-libs@py27

Yes, that’s it. Now we know where and why the duplicates are arising.

How to fix this

I could take one of two approaches:

  1. Detect and eliminate duplicates within the code.
  2. Within the database, ignore attempts to add duplicates.

The database is wonderful at doing this. I could write code, and it might be buggy. I trust the database for this, much more than I trust my code. Plus, I’ll have less code to maintain.

I’ll take option #2.

The code

Here is the statement which inserts the tuple into the database:

INSERT INTO port_dependencies (port_id, port_id_dependent_upon, dependency_type)
      VALUES (l_PortID, l_PortIDDependency, p_DependencyType);

There is no detection of duplicate rows.

In different situations, I have used this approach to detect duplicates during insertion:

INSERT INTO port_dependencies (port_id, port_id_dependent_upon, dependency_type)
  SELECT l_PortID, l_PortIDDependency, p_DependencyType
   WHERE NOT EXISTS (SELECT port_id, port_id_dependent_upon, dependency_type
                       FROM port_dependencies
                      WHERE port_id                = l_PortID
                        AND port_id_dependent_upon = l_PortIDDependency
                        AND dependency_type        = p_DependencyType);

The above may be easier to follow if I do the variable substitution:

 INSERT INTO port_dependencies (port_id, port_id_dependent_upon, dependency_type)
  SELECT 45100, 25346, 'L'
   WHERE NOT EXISTS (SELECT port_id, port_id_dependent_upon, dependency_type
                       FROM port_dependencies
                      WHERE port_id                = 45100
                        AND port_id_dependent_upon = 25346
                        AND dependency_type        = 'L');

That works well, exactly as expected. It does what I need.

But there is a better way

Then I posted on Twitter about this, with the above example. My cousin, Scott Walsh, mentioned ON CONFLICT. This feature arrived with PostgreSQL 9.5 (see docs), which was released in early 2016. I haven’t kept up. FreshPorts added dependency support in 2011.

I immediately tried this approach:

INSERT INTO port_dependencies (port_id, port_id_dependent_upon, dependency_type)
freshports.org-#   SELECT 45100, 25346, 'L'
freshports.org-# ON CONFLICT DO NOTHING;
INSERT 0 0

That’s great.

Then I got to thinking… there are other conflicts which could occur, errors which should be brought to my attention. Foreign key violations come to mind.

Reading more of the documentation came up with this solution:

freshports.org=# INSERT INTO port_dependencies (port_id, port_id_dependent_upon, dependency_type)
freshports.org-#   SELECT 45100, 25346, 'L'
freshports.org-# ON CONFLICT ON CONSTRAINT port_dependencies_pkey DO NOTHING;
INSERT 0 0

EDIT 2017.03.17

I see I’ve left my SELECT in that INSERT. The following is what I will use in production:

INSERT INTO port_dependencies (port_id, port_id_dependent_upon, dependency_type)
    VALUES (l_PortID, l_PortIDDependency, p_DependencyType)
    ON CONFLICT ON CONSTRAINT port_dependencies_pkey DO NOTHING;

When running my first test of that code, this is what was found in the logs (two duplicate inserts, no failures):

sql is SELECT PortsDependenciesAdd( 'net/ceph', 'devel/boost-python-libs', 'L' ) as result
NOTICE:   branch is head
NOTICE:   GetPort pathname is=/ports/head/net/ceph
NOTICE:   branch is head
NOTICE:   GetPort pathname is=/ports/head/devel/boost-python-libs
result is 1
adding in devel/boost-python-libs which converts to 'devel/boost-python-libs'
sql is SELECT PortsDependenciesAdd( 'net/ceph', 'devel/boost-python-libs', 'L' ) as result
NOTICE:   branch is head
NOTICE:   GetPort pathname is=/ports/head/net/ceph
NOTICE:   branch is head
NOTICE:   GetPort pathname is=/ports/head/devel/boost-python-libs
result is 1

That’s exactly what I need. Phew.


This code deals specifically with one constraint, port_dependencies_pkey. If that constraint is violated, we have a duplicate key: do nothing, ignore it, as if nothing happened. Please move along, nothing to see here, thank you.

This, for my situation, is exactly what I want. Instead of removing duplicates in the front end code, I can let the database silently handle it. Score.

Thanks to those who helped me find this solution. I hope it helps you too. This is not a one-size-fits-all solution, but it might be applicable to your needs.

Jul 252016
 

FreshPorts uses a cache for every port page (e.g. https://www.freshports.org/sysutils/bacula-server/). When an update to a port occurs, we must clear the cache for that port. This sounds simple, and it is. The various ways in which updates occur complicates the situation.

This post is all about fixing one edge case: the addition or removal of a port dependency (e.g. RUN_DEPENDS, LIB_DEPENDS).

When port A depends on port B, this fact is listed on both pages, but adding/removing a dependency is not properly handled (see this bug). Tonight, I have coded a fix. I’d like it to be less complex, but it has been fixed.

The table

This is the table in question:

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=# 

Using the terminology from the first sentence, port A and port B are represented by port_id and port_id_dependent_upon respectively. Any new row or any deleted row must invalidate the cache for both port A and port B.

Keeping track of cache to clear

This is the table we use to clear the ports cache:

freshports.org=# \d cache_clearing_ports
                                     Table "public.cache_clearing_ports"
   Column   |            Type             |                             Modifiers                             
------------+-----------------------------+-------------------------------------------------------------------
 id         | integer                     | not null default nextval('cache_clearing_ports_id_seq'::regclass)
 port_id    | integer                     | not null
 category   | text                        | not null
 port       | text                        | not null
 date_added | timestamp without time zone | not null default now()
Indexes:
    "cache_clearing_ports_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "cache_clearing_ports_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE

freshports.org=# 

We add rows to this table to indicate that a given port must be cleared from the cache.

On INSERT

On an insert to the port_dependencies table, let’s do this:

CREATE OR REPLACE FUNCTION port_dependencies_insert_clear_cache() RETURNS TRIGGER AS $$
   DECLARE
      l_cache_clearing_ports_id   int8;
      l_port      text;
      l_category  text;
   BEGIN
        --
        -- This function handles the addition of a new dependency.
        -- yes, we need to clear the cache for both port_id and port_id_dependent_upon
        -- from the cache_clearing_ports.  I figure there is a shorter way to this but
        -- I cannot think it through right now.
        --

        IF TG_OP = 'INSERT' THEN
         -- handle port A (port_id)
         SELECT port_id
           INTO l_cache_clearing_ports_id
           FROM cache_clearing_ports
          WHERE port_id = NEW.port_id;

          IF NOT FOUND THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = NEW.port_id;
            INSERT INTO cache_clearing_ports (port_id, category, port)
            VALUES (NEW.port_id, l_category, l_port);
          END IF;

         -- handle port B (port_id_dependent_upon)
         SELECT port_id
           INTO l_cache_clearing_ports_id
           FROM cache_clearing_ports
          WHERE port_id = NEW.port_id_dependent_upon;

          IF NOT FOUND THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = NEW.port_id_dependent_upon;
            INSERT INTO cache_clearing_ports (port_id, category, port)
            VALUES (NEW.port_id_dependent_upon, l_category, l_port);
          END IF;

          NOTIFY port_updated;
      END IF;

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

  DROP TRIGGER IF EXISTS port_dependencies_insert_clear_cache ON port_dependencies;
CREATE TRIGGER port_dependencies_insert_clear_cache
    AFTER INSERT on port_dependencies
    FOR EACH ROW
    EXECUTE PROCEDURE port_dependencies_insert_clear_cache();

That function is rather long, and I know it can be simplified, but that’s not on tonight’s agenda. If you are so inclined, I am happy to hear your suggestions. Specifically, I think I can do an INSERT INTO … WHERE NOT EXISTS and do the SELECT & INSERT in one step, without the need for IF FOUND.

On DELETE

Here is the code for the DELETE.

CREATE OR REPLACE FUNCTION port_dependencies_delete_clear_cache() RETURNS TRIGGER AS $$
   DECLARE
      l_cache_clearing_ports_id   int8;
      l_port      text;
      l_category  text;
   BEGIN
        --
        -- This function handles the deletion of a existing dependency.
        -- yes, we need to clear the cache for both port_id and port_id_dependent_upon
        -- from the cache_clearing_ports.  I figure there is a shorter way to this but
        -- I cannot think it through right now.
        --

        IF TG_OP = 'DELETE' THEN
         SELECT port_id
           INTO l_cache_clearing_ports_id
           FROM cache_clearing_ports
          WHERE port_id = OLD.port_id;

          IF NOT FOUND THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = OLD.port_id;
            INSERT INTO cache_clearing_ports (port_id, category, port)
            VALUES (OLD.port_id, l_category, l_port);
          END IF;

         SELECT port_id
           INTO l_cache_clearing_ports_id
           FROM cache_clearing_ports
          WHERE port_id = OLD.port_id_dependent_upon;

          IF NOT FOUND THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = OLD.port_id_dependent_upon;
            INSERT INTO cache_clearing_ports (port_id, category, port)
            VALUES (OLD.port_id_dependent_upon, l_category, l_port);
          END IF;

          NOTIFY port_updated;
      END IF;

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

  DROP TRIGGER IF EXISTS port_dependencies_delete_clear_cache ON port_dependencies;
CREATE TRIGGER port_dependencies_delete_clear_cache
    AFTER DELETE on port_dependencies
    FOR EACH ROW
    EXECUTE PROCEDURE port_dependencies_delete_clear_cache();

It is nearly identical to the first function. Again, those of you who want to suggest improvements there, feel free.

I will run this in dev for a while and see how it goes. Initial testing has been promising.

NOTE: there are no updates to this table, by design. Instead when we see a new commit to a port, we do a DELETE from port_dependencies WHERE port_id = :a.

Jan 292014
 

Yesterday, I wrote about some great progress on getting FreshPorts to work with multiple repositories to cater for the branches of the FreeBSD ports tree. At the end of that post, I talked briefly about wanting global settings or variables for my stored procedures. Shortly after posting that, I found a solution in the form of a post by depesz.

A bit about the problem

The problem I encountered was one of context. Sometimes, you want to use trunk (or head) and sometimes you want to use a branch (e.g. RELENG_9_1_0). A function such as the one shown below, has no content. It has ‘head’ hardcoded (see line 8) because the FreeBSD ports tree had no branches when the function was first modified to cater for SVN (FreeBSD previously used CVS).

CREATE OR REPLACE FUNCTION GetPort(text) RETURNS int4 AS $$
   DECLARE
      category_port ALIAS for $1;
      pathname         text;
      port_element_id  int4;
      port_id          int4;
   BEGIN
      pathname        := '/ports/head/' || category_port;
      port_element_id := Pathname_ID(pathname);
      if port_element_id IS NOT NULL THEN
         select id
           into port_id
           from ports
          where element_id = port_element_id;
      END IF;
      return port_id;
   END;
$$ LANGUAGE 'plpgsql';

If you look at yesterday’s post, you’ll see this output:

freshports.org=# select id, name, category, element_id, element_pathname(element_id) from ports_active where name = 'spellathon';
  id   |    name    | category | element_id |               element_pathname                
-------+------------+----------+------------+-----------------------------------------------
 24964 | spellathon | games    |     324899 | /ports/head/games/spellathon
 34159 | spellathon | games    |     559872 | /ports/branches/RELENG_9_1_0/games/spellathon
(2 rows)
 
freshports.org=# 

We need some way to tell this function which branch we are using.

The solution

This is the amended function, which is working just fine, thank you depesz.

CREATE OR REPLACE FUNCTION GetPort(text) RETURNS int4 AS $$
   DECLARE
      category_port ALIAS for $1;
      pathname         text;
      port_element_id  int4;
      port_id          int4;
      l_branch         text;
   BEGIN
      l_branch := freshports_branch_get();

      IF l_branch = 'head' THEN
          pathname := '/ports/'          || l_branch || '/' || category_port;
      ELSE
          pathname := '/ports/branches/' || l_branch || '/' || category_port;
      END IF;

      port_element_id := Pathname_ID(pathname);
      IF port_element_id IS NOT NULL THEN
         SELECT id
           INTO port_id
           FROM ports  
          WHERE element_id = port_element_id;
      END IF;

      RETURN port_id;
   END;
$$ LANGUAGE 'plpgsql';

The new magic code appears on lines 9-15.

  • Line 9-10 calls a new function (shown later) which pulls back the branch.
  • The IF statement constructs the correct path, based on the differences between trunk and branches in the FreeBSD ports repository.

Show me it working!

I have coded the function to assume head if no branch is specified. That is the default in the current code. Here is what you get when you invoke GetPort without specifying a branch:

freshports.org=# select GetPort('games/spellathon');
 getport 
---------
   24964
(1 row)

freshports.org=# 

Next, I set the branch, and call the same function again:

freshports.org=# select freshports_branch_set('RELENG_9_1_0');
 freshports_branch_set 
-----------------------
 
(1 row)

freshports.org=# select GetPort('games/spellathon');
 getport 
---------
   34159
(1 row)

freshports.org=# 

And here we swap back again:

freshports.org=# select freshports_branch_set('head');
 freshports_branch_set 
-----------------------
 
(1 row)

freshports.org=# select GetPort('games/spellathon');
 getport 
---------
   24964
(1 row)

freshports.org=# 

I was pretty chuffed when I was able to get this working with a minimum of fuss. This bodes well for the rest of this project.

The rest of the code

In addition to the code provided in depesz’s post, I added the following helper functions, specific to FreshPorts.

CREATE OR REPLACE FUNCTION freshports_branch_set( TEXT ) RETURNS void as $$

   SELECT session_variables.set_value('branch', $1);

$$ language sql;


CREATE OR REPLACE FUNCTION freshports_branch_get() RETURNS TEXT as $$

DECLARE
    reply TEXT;

BEGIN

   reply := session_variables.get_value( 'branch' );

   IF reply IS NULL THEN
      reply := 'head';
   END IF;

   RETURN reply;

END;

$$ language plpgsql;

The first function lets my code set the branch. You saw me use that in the examples above. The second function is used by the GetPort() function to retrieve the branch it should use. By default, it will return ‘head’.

Let’s go!

This session variable stuff is pretty much what I was looking for last night. I’m very pleased that it has been so easy to add to my particular application. Hope it helps you. I’m looking forward to the rest of this upgrade.

Jan 252014
 

Earlier today, I wrote about a fix which broke search. At the end of that post, I mentioned a few things which needed to be done to fix up the broken relationships.

  1. clean up the database
  2. add a foreign key, on delete set null
  3. trigger to find the right value when set null

Let’s get started.

clean up the database

How many rows are we talking about?

SELECT count(*)
  FROM ports P
 WHERE NOT EXISTS
   (SELECT CL.id 
      FROM commit_log CL
     WHERE CL.id = P.last_commit_id);
 count 
-------
 13023

Ouch. That’s about half the ports tree.

Let’s set them all NULL.

BEGIN;
UPDATE ports P
   set last_commit_id = NULL
 WHERE NOT EXISTS
   (SELECT CL.id 
      FROM commit_log CL
     WHERE CL.id = P.last_commit_id);
UPDATE 13023

-- that number matches up
-- now let's run that first query again:
SELECT count(*)
  FROM ports P
 WHERE NOT EXISTS
   (SELECT CL.id 
      FROM commit_log CL
     WHERE CL.id = P.last_commit_id);
 count 
-------
 13023
(1 row)

-- oh, of course.  Let's exclude what we just fixed

SELECT count(*)
  FROM ports P
 WHERE P.last_commit_id IS NOT NULL
   AND NOT EXISTS
   (SELECT CL.id 
      FROM commit_log CL
     WHERE CL.id = P.last_commit_id);
 count 
-------
     0
(1 row)

-- this looks good; commit;

commit;
COMMIT

Now, let’s fix up all the NULL keys. Fortunately, I have a script which fixes this exact situation.

#!/usr/bin/perl -w
#
# $Id: set-last-commit-id.pl,v 1.2 2006-12-17 12:04:06 dan Exp $
#
# Copyright (c) 1999-2004 DVL Software
#

#
# I found that several (about 800-900) ports did not yet have last_commit_id set.
# this script sets that.
#

use strict;
use lib "$ENV{HOME}/scripts";
use port;
use DBI;
use database;
use utilities;

my $dbh;

my $porttorefresh;
my @PORTS;
my $sql;
my $sth;
my @row;

FreshPorts::Utilities::InitSyslog();

$dbh = FreshPorts::Database::GetDBHandle();

#
# get a list of ports to update
#

$sql = "
  SELECT id
    FROM ports
   WHERE last_commit_id IS NULL
";

print "sql = $sql\n";

$sth = $dbh->prepare($sql);
$sth->execute ||
		FreshPorts::Utilities::ReportError('warning', "Could not execute SQL $sql ... maybe invalid?", 1);

while (@row=$sth->fetchrow_array) {
	print "now reading @row\n";
	push @PORTS, "$row[0]"
}

my $port = FreshPorts::Port->new($dbh);

foreach $porttorefresh (@PORTS) {
	$sql = "
SELECT max(CL.commit_date)
  FROM commit_log CL, commit_log_ports CLP
 WHERE CLP.port_id = $porttorefresh
   AND CL.id       = CLP.commit_log_id
";

	print "sql = $sql\n";

	$sth = $dbh->prepare($sql);
	$sth->execute ||
		FreshPorts::Utilities::ReportError('warning', "Could not execute SQL $sql ... maybe invalid?", 1);

	@row =$sth->fetchrow_array;

	my $CommitLogDate = $row[0];

	$sql = "
SELECT CL.id
  FROM commit_log CL, commit_log_ports CLP
 WHERE CLP.port_id = $porttorefresh
   AND CL.id       = CLP.commit_log_id
   AND CL.commit_date = '$CommitLogDate'
";

	$sth = $dbh->prepare($sql);
	$sth->execute ||
		FreshPorts::Utilities::ReportError('warning', "Could not execute SQL $sql ... maybe invalid?", 1);

	@row =$sth->fetchrow_array;

	my $CommitLogID = $row[0];

	$sql = "
UPDATE ports
   SET last_commit_id = $CommitLogID
 WHERE id             = $porttorefresh
";

	print "sql = $sql\n";

	$sth = $dbh->prepare($sql);
	$sth->execute ||
		FreshPorts::Utilities::ReportError('warning', "Could not execute SQL $sql ... maybe invalid?", 1);

}

$sth->finish();

$dbh->commit();
$dbh->disconnect();

After running that script, I ran one of the queries from above to confirm all was well:

SELECT count(*)
  FROM ports P
 WHERE NOT EXISTS
   (SELECT CL.id 
      FROM commit_log CL
     WHERE CL.id = P.last_commit_id);
 count 
-------
     0
(1 row)

That’s all the ports correct.

Let’s try running the problem query mentioned in a previous post:

SELECT count(*)
  FROM ports P LEFT OUTER JOIN ports_vulnerable PV on PV.port_id = P.id JOIN commit_log CL on P.last_commit_id = CL.id, categories C, element E
 WHERE P.category_id   = C.id
   AND P.element_id    = E.id  
   AND lower(E.name) = lower(E'bird');
 count 
-------
     1

That query correctly retrieves one row.

add a foreign key, on delete set null

The ports table is simple, with a lot of referential integrity (RI). I’m about to add more RI to it.

freshports.org=> \d  ports
                                       Table "public.ports"
      Column       |           Type           |                     Modifiers                      
-------------------+--------------------------+----------------------------------------------------
 id                | integer                  | not null default nextval('ports_id_seq'::regclass)
 element_id        | integer                  | not null
 category_id       | integer                  | not null
 short_description | text                     | 
 long_description  | text                     | 
 version           | text                     | 
 revision          | text                     | 
 maintainer        | text                     | 
 homepage          | text                     | 
 master_sites      | text                     | 
 extract_suffix    | text                     | 
 package_exists    | boolean                  | 
 depends_build     | text                     | 
 depends_run       | text                     | 
 last_commit_id    | integer                  | 
 found_in_index    | boolean                  | 
 forbidden         | text                     | 
 broken            | text                     | 
 date_added        | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
 categories        | text                     | 
 deprecated        | text                     | 
 ignore            | text                     | 
 master_port       | text                     | 
 latest_link       | text                     | 
 depends_lib       | text                     | 
 no_latest_link    | text                     | 
 no_package        | text                     | 
 package_name      | text                     | 
 portepoch         | text                     | 
 no_cdrom          | text                     | 
 restricted        | text                     | 
 expiration_date   | date                     | 
 is_interactive    | text                     | 
 only_for_archs    | text                     | 
 not_for_archs     | text                     | 
 status            | character(1)             | not null
 showconfig        | text                     | 
 license           | text                     | 
Indexes:
    "ports_pkey" PRIMARY KEY, btree (id)
    "ports_active_idx" btree (status) WHERE status = 'A'::bpchar
    "ports_broken" btree (broken) WHERE broken <> ''::text
    "ports_category_id_idx" btree (category_id)
    "ports_deleted" btree (status) WHERE status = 'D'::bpchar
    "ports_element_id" btree (element_id)
    "ports_expiration_date" btree (expiration_date) WHERE expiration_date IS NOT NULL
    "ports_ignore" btree (ignore) WHERE ignore <> ''::text
    "ports_is_interactive" btree (is_interactive) WHERE is_interactive IS NOT NULL
    "ports_package_name" btree (package_name)
    "ports_ports_expiration_date" btree (expiration_date) WHERE expiration_date IS NOT NULL
Foreign-key constraints:
    "$1" FOREIGN KEY (category_id) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE CASCADE
    "$2" FOREIGN KEY (element_id) REFERENCES element(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "ports_categories" CONSTRAINT "$1" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "commit_log_ports_ignore" CONSTRAINT "$1" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ports_moved" CONSTRAINT "$1" FOREIGN KEY (from_port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ports_updating_ports_xref" CONSTRAINT "$1" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ports_vulnerable" CONSTRAINT "$1" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE RESTRICT ON DELETE CASCADE
    TABLE "commit_log_ports" CONSTRAINT "$2" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ports_moved" CONSTRAINT "$2" FOREIGN KEY (to_port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "commit_log_ports_vuxml" CONSTRAINT "$2" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "commit_log_port_elements" CONSTRAINT "$3" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "cache_clearing_ports" CONSTRAINT "cache_clearing_ports_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "port_dependencies" CONSTRAINT "port_dependencies_port_id_dependent_upon_fkey" FOREIGN KEY (port_id_dependent_upon) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "port_dependencies" CONSTRAINT "port_dependencies_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    ports_clear_cache AFTER UPDATE ON ports FOR EACH ROW EXECUTE PROCEDURE ports_clear_cache()
    ports_ports_categories AFTER INSERT OR UPDATE ON ports FOR EACH ROW EXECUTE PROCEDURE ports_categories_set()
    ports_status BEFORE INSERT OR UPDATE ON ports FOR EACH ROW EXECUTE PROCEDURE ports_status()

As you can see from the above, last_commit_id is not involved in any foreign key constraints. The following statement will ensure that the field always contains a valid key, or null:

ALTER TABLE ports                                                     
    ADD FOREIGN KEY (last_commit_id)
       REFERENCES commit_log (id) ON UPDATE CASCADE ON DELETE SET NULL;

Doing another \d ports will display the following addition to the table:

   "ports_last_commit_id_fkey" FOREIGN KEY (last_commit_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE SET NULL

trigger to find the right value when set null

Here is the trigger I just created. It is based upon the script (mentioned above) which I used to fix up the values.

CREATE OR REPLACE FUNCTION check_last_commit_id() RETURNS TRIGGER as '
   declare
      l_max_commit_date  timestamp with time zone;
      l_commit_log_id    integer;
begin

  if new.last_commit_id is null then
    SELECT max(CL.commit_date)
      INTO l_max_commit_date
      FROM commit_log CL, commit_log_ports CLP
     WHERE CLP.port_id = NEW.id
       AND CL.id       = CLP.commit_log_id;

    IF FOUND THEN
      SELECT CL.id
        INTO l_commit_log_id
        FROM commit_log CL, commit_log_ports CLP
       WHERE CLP.port_id    = NEW.id
         AND CL.id          = CLP.commit_log_id
         AND CL.commit_date = l_max_commit_date;

      IF FOUND THEN
        NEW.last_commit_id := l_commit_log_id;
      END IF;
    END IF;

  end if;

  RETURN NEW;

end;
' LANGUAGE 'plpgsql';


  DROP TRIGGER check_last_commit_id on ports;
CREATE TRIGGER check_last_commit_id
BEFORE update on ports
FOR EACH ROW
EXECUTE PROCEDURE check_last_commit_id();

Here is my test of that code:

freshports.org=# begin;
BEGIN
freshports.org=# select id, last_commit_id from ports where id = 234;
 id  | last_commit_id 
-----+----------------
 234 |         237699
(1 row)

freshports.org=# update ports set last_commit_id = null where id = 234;
UPDATE 1
freshports.org=# select id, last_commit_id from ports where id = 234;
 id  | last_commit_id 
-----+----------------
 234 |         237699
(1 row)

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

Yes, I just set the last_commit_id to null, and it bounced right back. But for the killer test, let’s delete a commit and see what happens.

Testing via commit delete

Let’s start with this commit against chinese/bg5ps. I will check the existing values, delete the commit, then check the new values.

freshports.org=# SELECT id, last_commit_id FROM ports_active where name = 'bg5ps';
  id  | last_commit_id 
------+----------------
 2973 |         504539
(1 row)

freshports.org=# begin;
BEGIN
freshports.org=# delete from commit_log where id = 504539 and message_id = '201401251811.s0PIBg6R031537@svn.freebsd.org';
DELETE 1
freshports.org=# SELECT id, last_commit_id FROM ports_active where name = 'bg5ps';
  id  | last_commit_id 
------+----------------
 2973 |         504087
(1 row)

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

Yes, that seems to have worked as planned. I did a rollback at the end, just because the delete was not required.

Verifying the fix

But let’s check that new value. Is it correct?

freshports.org=# select message_id from commit_log where id = 504087;
                 message_id                  
---------------------------------------------
 201401221552.s0MFqBK6007511@svn.freebsd.org
(1 row)

freshports.org=# 

Looking at the webpage, yes, that is the expected value for that message_id, which can be found by hovering over the commit email icon or the file set icon.

I’ll run this on my dev server for a while before pushing it through to production.