Window Function COUNT

This article is an extension of the window function, to follow up on each of the window functions.

The COUNT function will counts each row that contains non-NULL values for the given expression.

An example usage over the test data set would be:

SELECT sale_date, salesperson,
	   COUNT(*) OVER (
         PARTITION BY sale_date
         ORDER BY sale_id
         ROWS UNBOUNDED PRECEDING
       ) as sale_number
FROM public.sales
ORDER BY sale_date;

We list the sale_date and salesperson out for every sale on record. The COUNT partitions by the date of the sale made. This makes the sale_number tell us what number the sale was for that given date.

Taking 2018-05-27, we can see that John got the first sale, June got the second and third.

Window Function AVG

This article is an extension of the window function, to follow up on each of the window functions.

The AVG function takes the arithmetic mean of the input that it sees.

An example usage over the test data set would be:

SELECT salesperson, sale_date, (quantity * unit_cost),
	   avg(quantity * unit_cost) OVER (
         PARTITION BY salesperson
       ) as month_avg
FROM public.sales
ORDER BY salesperson, sale_date DESC;

This takes the average cost value (quantity * unit_cost), but personalises the value by salesperson. Each salesperson is going to have their average calculated in isolation because of PARTITION BY salesperson.

Range generation in Redshift

A little while ago, I’d written an article on Range generation in PostgreSQL, and whilst Redshift and PostgreSQL are similar database beasts, they are quite different in terms of their query capabilities due to their different strengths.

In today’s article, we’ll walk through a couple of different solutions to range generation.

generate_series

The generate_series is a pretty serious tool in terms of achieving exactly what this article is talking about. Unfortunately, there is no analog for this function within the Redshift database management system. Who knows; maybe one day.

Until then though, we need to create our own.

The query

with digit as (
    select 0 as d union all
    select 1 union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9
),
seq as (
    select a.d + (10 * b.d) + (100 * c.d) + (1000 * d.d) as num
    from digit a
        cross join
        digit b
        cross join
        digit c
        cross join
        digit d
    order by 1
),
drange as (
    SELECT (getdate()::date - seq.num)::date       as start_date
    FROM seq
    LIMIT 20
)
SELECT start_date
FROM drange;

There’s a bit going on in here.

The process starts with the digit CTE. Is does nothing more than counts from 0, up to 9.

select 0 as d union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9

The next CTE seq uses the digits CTE, 4 times. Each time its treated as an increasing power of 10. a handles the 10^0, b handles the 10^1, etc. This catersian effect quickly multiplies our 0-9 range up to 0-9999. If more numbers are required, we simple add another CROSS JOIN, and treat the next power of 10.

select a.d + (10 * b.d) + (100 * c.d) + (1000 * d.d) as num
from digit a
    cross join
    digit b
    cross join
    digit c
    cross join
    digit d
order by 1

That’s it as far a number generation. It’s pretty easy.

I’ve seen other developers materialise this information into a physical table, and simply join to that table also.

Extending into time

Applying this into some date ranges now, we use the seq CTE and a little date arithmatic. My example here just uses the numbers within seq to enumerate dates. Adding LIMIT to this query also prevents us from utilising all 10000 values produced by seq.

SELECT (getdate()::date - seq.num)::date       as start_date
FROM seq
LIMIT 20

And that it.

Daemon development

In today’s article, I’ll take you through some basic daemon development for POSIX based systems. This article should give you enough information to get started on your own project to add in the bits that your program does.

Getting started

The largest amout of effort into writing any daemon is validation. Validation of incoming data is one thing; but we’re not even there yet. We’re just talking about validating the current system state, environment, and any configuration values.

fork

First job is to use the fork system call, to duplicate the current process.

fork() creates a new process by duplicating the calling process. The new process is referred to as the child process. The calling process is referred to as the parent process.

Directly after forking from the parent process, the parent is terminated; leaving the child to own the running of the daemon. This allows the daemon to now be independent of any other process running. Removing this dependency means that no one (or nothing) can kill your daemon by killing another (parent) process.

A secondary fork is then invoked; usually after a call to setsid. This starts a new session with no terminal.

setsid() creates a new session if the calling process is not a process group leader. The calling process is the leader of the new session (i.e., its session ID is made the same as its process ID). The calling process also becomes the process group leader of a new process group in the session (i.e., its process group ID is made the same as its process ID).

The side-effect that we’re looking for here.

Initially, the new session has no controlling terminal.

The child is then exited, removing any reliance on the terminal.

The grandchild is now the daemon.

pid_t pid;

/* the initial fork */
pid = fork();

/* terminate the parent */
if (pid == -1) {
  die("Failed to fork (errno=%d)", errno);
} else if (pid != 0) {
  exit(EXIT_SUCCESS);
}

/* make the child to session-leader */
setsid();

/* SIGHUP now gets ignore, and we re-fork */
signal(SIGHUP, SIG_IGN);
pid = fork();

/* terminate the child */
if (pid == -1) {
  die("Failed to fork (errno=%d)", errno);
else if (pid != 0) {
  exit(EXIT_SUCCESS);
}

/* we are now the grandparent
   this is the daemon */

Working directory

Now that we have a safe running process, it’s time to change to a location that we know is safe. We know that the root directory exists. We’ll use it for our example.

if (chdir("/") == -1) {
  die("Unable to change working directory (errno=%d)", errno);
}

This puts us in a sane place, where we know where we are. Doesn’t need to be the root directory; but at least we know where we are.

File mask

Next up, we’ll set the umask for the process to 0. It it standard practice for a daemon to operate with a umask of 0. At the operating system level, this forces new file objects created to have permission of 0666 (world-writable); and directories as 0777.

umask() sets the calling process’s file mode creation mask (umask) to mask & 0777 (i.e., only the file permission bits of mask are used), and returns the previous value of the mask.

In short, umask of 0 means objects are created with privileges initially revoked.

umask(0);

Standard File Descriptors

Daemons now close and re-open the standard file handles. STDIN, STDOUT, and STDERR all get closed. It’s assumed that /dev/null is provided on any POSIX system, and as a result it’s used to re-open these handles.

close(STDIN_FILENO);
close(STDOUT_FILENO);
close(STDERR_FILENO);

if (open("/dev/null", O_RDONLY) == -1) {
  die("Unable to re-open STDIN (errno=%d)", errno);
}

if (open("/dev/null", O_WRONLY) == -1) {
  die("Unalb to re-open STDOUT (errno=%d)", errno);
}

if (open("/dev/null", O_RDWR) == -1) {
  die("Unable to re-open STDERR (errno=%d)", errno);
}

All together

Your daemonize process might now look something like this:

#include <errno.h>
#include <signal.h>
#include <fcntl.h>
#include <unistd.h>

void daemonise() {
  /* 1. Initial fork */
  pid_t pid = fork();
  if (pid == -1) {
    die("Unable to fork (errno=%d)", errno);
  } else if (pid != 0) {
    _exit(0);
  }

  /* 2. Start a new session */
  if (setsid()==-1) {
    die("Unable to become a session leader (errno=%d)", errno);
  }

  /* 3. Fork again */
  signal(SIGHUP, SIG_IGN);
  pid = fork();

  if (pid == -1) {
    die("Unable to fork (errno=%d)",errno);
  } else if (pid != 0) {
    _exit(0);
  }

  /* 4. Set the working directory */
  if (chdir("/") == -1) {
    die("Unable to change the working directory (errno=%d)",errno);
  }

  /* 5. Set the umaask */
  umask(0);

  /* 6. Deal with the file descriptors */
  close(STDIN_FILENO);
  close(STDOUT_FILENO);
  close(STDERR_FILENO);

  if (open("/dev/null", O_RDONLY) == -1) {
    die("Unable to re-open stdin (errno=%d)", errno);
  }
  if (open("/dev/null", O_WRONLY) == -1) {
    die("Unable to re-open stdout (errno=%d)",errno);
  }
  if (open("/dev/null", O_RDWR) == -1) {
    die("Unable to re-open stderr (errno=%d)",errno);
  }
}

Closing up

You’re now daemonised, and ready to start actually writing your daemon process.

Some points to note might be installing some signal handlers?

signal(SIGCHLD, SIG_IGN);
signal(SIGTSTP, SIG_IGN);
signal(SIGTTOU, SIG_IGN);
signal(SIGTTIN, SIG_IGN);
signal(SIGHUP, daemon_signalhandler);
signal(SIGTERM, daemon_signalhandler);

Redshift variable hack

Being a data warehouse, Redshift from AWS doesn’t provide the same level of programming platform that you may have had the luxury of using.

The concept of variables in Redshift is non existant however with this clever hack you can fake your way into some variable based information.

WITH vars AS (
  SELECT 'John Smith' AS username,
         '2019-01-01T00:00:00.000Z'::timestamp AS process_date
)
SELECT *
FROM user_processsing
WHERE user == (SELECT username from vars) AND
      date_of_process = (SELECT process_date from vars);

The same trick can be achieved using CREATE TEMP TABLE also. The temporary table version will allow you to use your variables outside of the context of that one query. As above, the variables are bound to the CTE feeding the query.