PostgreSQL programming environment

Some databases rely heavily on their programming environment to deliver an imperative programming environment to where a developer can use high-level programming concepts such as control selection, iteration and flow.

Oracle supports PL/SQL, Sql Server supports T-SQL. PostgreSQL supports PL/pgSQL.

Prior to running through some top-level topics on the programming environment, some very important links:

• PL/pgSQL Documentation
• PL/pgSQL on the Postgres Wiki
• Writing PostgreSQL Functions with PL/pgSQL

Anonymous Code Blocks

Executing an anonymous code block is achieved with the DO keyword. It emulates setting up any other function, with no parameters and no return value. Because you have a function body, you are afforded the ability to use a DECLARE section to house variables.

DO $$
BEGIN

END $$;

Adding variables to this block:

DO $$
DECLARE age INT;
BEGIN

  age := 24;

  IF age < 18 THEN
    -- handle the below 18 case
  END IF;
END$$;

Because of the parameter and return value constraints on anonymous code blocks, getting information out can be a little tricky. There are some ways that we can get some information out though.

RAISE NOTICE allows us to present information to the console. This is considerably handy when we’re testing a block of code.

DO $$
DECLARE age INT;
BEGIN

  age := 17;

  IF age < 18 THEN
    RAISE NOTICE 'Underage!';
  END IF;
END$$;

Of course, if your code is a re-usable block you should be creating a full function for it. This takes care of any input/output parameter issues as you take full control.

Looking elsewhere

If this isn’t to your liking, you can try any of the other operating environments/languages available:

• PL/Tcl
• PL/Perl
• PL/Python

Monitoring PostgreSQL Databases

Understanding what your database is doing and when is essential to runtime administration, maintenance, monitoring and reporting. Gaining insight into how your system responds to different workloads can also tell you how your current deployment is or isn’t serving your purpose.

There are many great articles on this particular topic already. In today’s post, I’m going to walk through a couple of the simple things you can do to check your system’s runtime status.

Unix Tools

When your database is being hosted in a unix-like environment, you’re given the greatest tools at your disposal to understand what’s happening.

ps can show you running processes on your system. Paired with grep, you can focus ps to look at postgres processes only:

ps -ef | grep postgres

postgres     1     0  0 11:05 ?        00:00:00 postgres
postgres    17     1  0 11:05 ?        00:00:00 postgres: checkpointer process  
postgres    18     1  0 11:05 ?        00:00:00 postgres: writer process  
postgres    19     1  0 11:05 ?        00:00:00 postgres: wal writer process  
postgres    20     1  0 11:05 ?        00:00:00 postgres: autovacuum launcher process  
postgres    21     1  0 11:05 ?        00:00:00 postgres: stats collector process  

iostat and vmstat will also give you some operating system level insight to how your database application is performing.

Statistics Collector

An important, integrated piece of the Postgres architecture is the statistics collector. Using this, you can query to a very low level many pieces of information surrounding your system’s performance.

The following except is just a small sample of all of the views offered by the statistics collector; which are made available to the developer.

The full list can be found here.

Scala type construction

Scala gives the developer flexibility when reasoning about and designing type systems for applications. By using classes and traits, a developer can quickly build a complex hierarchy that can assist in describing constraint and relationship information.

In today’s post, I’m going to walk through a useless but demonstrative example of a type hierarchy and some of the constraint features available to the developer.

Vehicles

We’re going to model some different vehicles. Cars, planes, trucks, skateboards, whatever.

abstract class Vehicle

We could start case-classing this base out or directly adding derivatives that specialise down to the exact vehicle types that we want, but we’re going to reason about some attributes that these vehicles might have. Wheels and Jets.

trait HasWheels extends Vehicle {
  def numberOfWheels: Int
}

trait HasJets extends Vehicle {
  def numberOfJets: Int
}

When a vehicle HasWheels, the type is going to require us to specify numberOfWheels. Likewise numberOfJets for HasJets. These traits are extending our abstract Vehicle class.

When we have wheels, we should be able to set how fast they’re spinning.

trait WheelPropulsion {
  this: HasWheels =>

  def setWheelSpin(velocity: Double) {
    println("Wheel spin: " + velocity)
  }
}

Our WheelPropulsion trait says that this needs to be HasWheels. Makes sense. We can’t spin wheels if we don’t have wheels.

Likewise, we’d want to set the turbine intensity if we have jets.

trait JetPropoulsion {
  this: HasJets =>

  def setTurbine(strength: Double) {
    println("Setting turbine strength to " + strength)
  }
}

Even with this very basic level of type description we can start to make some basic vehicles.

class Motorcycle extends Vehicle with HasWheels
                                 with WheelPropulsion {
  def numberOfWheels = 2                                  
}

class MotorVehicle extends Vehicle with HasWheels
                                   with WheelPropulsion {
  def numberOfWheels = 4
}

class TwinJetPlane extends Vehicle with HasJets
                                   with JetPropoulsion {
  def numberOfJets = 2                                  
}

Mixing in

When you’re assembling a variable of your own, there’s no reason you can’t mix in when creating your own types:

val toyota = new Vehicle() with HasWheels 
                           with WheelPropulsion { 
  def numberOfWheels = 4
}

Of course, we could have just constructed toyota as a MotorVehicle for the same effect. This just demonstrates the instance construction flexibility.

Constraints

Finally, when you’re writing functions that work with your types you can specify rich constraint rules so that you can target functionality with as much precision as you require:

// everything can be painted
def paint(v: Vehicle) = { }

// only a vehicle with wheels can burnout
def doBurnout(v: Vehicle with HasWheels) = { }

As you can see, you not only use the with keyword to define your types; this keyword is also used for variable construction and function signature definition.

Covariance and contravariance in your types

When creating parameterised types, you have control on how those types can be passed. These nuances are referred to as variance and scala allows you to explicitly nominate how this works in your own classes.

An excellent explanation on these terms can be found here. I’ve reproduced the three main points for this article though:

That is, if A and B are types, f is a type transformation, and ≤ the subtype relation (i.e. A ≤ B means that A is a subtype of B), we have:

• f is covariant if A ≤ B implies that f(A) ≤ f(B)
• f is contravariant if A ≤ B implies that f(B) ≤ f(A)
• f is invariant if neither of the above holds

Invariant

Invariant parameter types are what ensures that you can only pass MyContainer[Int] to def fn(x: MyContainer[Int]). The guarantee is that the type that you’re containing (when it’s being accessed) is done so as the correct type.

class MyInvariant[T](var value: T)

This guarantees the type of T when we go to work on it.

def double(a: MyInvariant[Int]) = { 
  a.value *= 2
}

You can see here that a good case for invariant is for mutable data.

To show the error case here, we define a show function specialising to MyInvariant[Any]

def show(a: MyInvariant[Any]) = { 
  println("Here is: " + a.value) 
}

Trying to use this function:

scala> show(new MyInvariant[Int](5))
<console>:13: error: type mismatch;
 found   : MyInvariant[Int]
 required: MyInvariant[Any]
Note: Int <: Any, but class MyInvariant is invariant in type T.
You may wish to define T as +T instead. (SLS 4.5)
       show(new MyInvariant[Int](5))
            ^

Covariant

Covariant parameter type is specific. You pass these sorts of types to functions that generalise their inner type access. You need to decorate the type parameter with a +.

class CovariantContainer[+T](var value: T)

Then your function to generalise over this type:

def show(a: CovariantContainer[Any]) = { 
  println("The value is " + a.value)
}

Covariance is a good case for read-only scenarios.

Contravariant

Contravariance is defined by decorating the type parameter with a -. It’s useful in write-only situations.

class ContravariantContainer[-T](var value: T)

We write specialised functions for the type, but that are write-only cases:

def write(a: ContravariantContainer[String]) = {
  println("Writing " + a)
}

Rules

When designing types, the following rules are very important when dealing with parameterization of types.

• Mutable containers should be invariant
• Immutable containers should be covariant
• Transformation inputs should be contravariant
• Transformation outputs should be covariant

Modeling a function call

Armed with this information, we can generalise function execution into the following type:

trait Fn[-In, +Out] {
  def apply(i: In): Out
}

Defining this trait, allows us to generalise the computation of an input to an output like the following:

val anyToInt = new Fn[Any, Int] {
  def apply(i: Any) = i.toString.toInt
}

DROP DATABASE and other users

From time to time, when I’ve gone and issued DROP DATABASE on my postgres server, I’m returned with the following error:

ERROR:  database "xyz" is being accessed by other users
DETAIL:  There is 1 other session using the database.

All this is telling us is that we need to be the only user/connection on the database before performing such an operation.

First of all, we need to prevent other users from making a connection to the database from this point. To do this, we’ll use REVOKE CONNECT like so:

REVOKE CONNECT ON DATABASE "xyz" FROM public;

Next, we kill off every connection:

SELECT pg_stat_activity.pid, pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'xyz';

Now you can issue your DROP DATABASE.