Session variables! Almost. Not quite.

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.