PostgreSQL and PL/Pythonu

Beside SQL PostgreSQL has support for creating functions in many other so called procedural languages. We just need to install them and they are available within database. Standard distribution includes those four languages PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python. There are other externally maintained languages like Java, Scheme, PHP, Unix shell and so on. If that is not enough it is possible to write procedural language handler, it is open source and consequently fully customizable.
Hosted Python is very interesting option. It is quite powerful, versatile language and code is very concise. If it is executed within PostgreSQL it enjoys very fast communication avoiding overhead of DB calls which external programs suffer. Both Python 2 and Python 3 are supported. If you are using, for example, Ubuntu then postgresql-plpython-9.x is Python 2 and postgresql-plpython3-9.x is Python 3. It needs to be installed, server restarted and language created.

createlang plpythonu [dbname]

If we do not specify db, then it will be created on db to which are we currently connected. That u suffix have meaning that we are dealing with unrestricted/untrusted language. Power is given to us and it is our responsibility to use it to our benefit without causing damage. Once everything is in place we can write Hello World function in Python.

CREATE OR REPLACE FUNCTION lumberjack() RETURNS text AS $BODY$ a = "I'm a lumberjack, and I'm okay. " return a + "I sleep all night and I work all day." $BODY$ LANGUAGE plpythonu

From psql we try it

testpgdb=# select lumberjack(); lumberjack ----------------------------------------------------------------------- I'm a lumberjack, and I'm okay. I sleep all night and I work all day. (1 row)

Or if you prefer GUI, then use pgAdmin III start Query tool and execute it there.
To enjoy full comfort of Python one need to import modules. How do we use modules in PL/Pythonu? It is no different to ordinary Python code:

create or replace function get_response() returns text as $BODY$ import httplib2 h = httplib2.Http(".cache") resp, content = h.request("http://example.org/", "GET") return resp $BODY$ language 'plpythonu'; select get_response();

we should see response. If we see:

ERROR: ImportError: No module named httplib2 CONTEXT: Traceback (most recent call last): PL/Python function "get_response", line 2, in <module> import httplib2 PL/Python function "get_response"

That is sign that httplib2 is not in path, we can place it in path or if we do not want to reload server we can append to function:

from sys import path path.append('/path to httplib2')

There is significant quantity of examples on Internet to help you further. With some knowledge of Python one can do really huge amount of work directly from PostgreSQL without overhead of DB connection. In role of mailer of monthly report PL/Pythonu function beats Java Application Server and even C/C++ application connecting from outside to DB.

GCC Quad-Precision Math Library example

There is small PDF on GCC website with libquadmath documentation. It contains explanation and example how to convert string to __float128 and how to convert __float128 back to string. It took me some googling to find out how to compile example and to figure out what is width parameter. To compile it we need -lquadmath linker switch and width, fourth argument to quadmath_snprintf, is overall width of output, right anchored. Here is my version of mentioned example:

#include <quadmath.h> #include <stdlib.h> #include <stdio.h> int main(){ __float128 pi = strtoflt128 ("3.1415926535897932384626433832795029", NULL); __float128 dpi = 3.1415926535897932384626433832795; __float128 qpi = 3.1415926535897932384626433832795029Q; char buf[128]; int n = quadmath_snprintf (buf, sizeof buf, "%*.34Qf",10, pi); if ((size_t) n < sizeof buf) printf ("%s\n", buf); n = quadmath_snprintf (buf, sizeof buf, "%*.34Qf", 40, dpi); if ((size_t) n < sizeof buf) printf ("%s\n", buf); n = quadmath_snprintf (buf, sizeof buf, "%*.34Qf", 45, qpi); if ((size_t) n < sizeof buf) printf ("%s\n", buf); return 0; }

If we try to assign number without Q suffix then we will have ordinary double inside quad storage. The fourth argument to quadmath_snprintf will be ignored if it is smaller than length of string representation, no spaces will be added in front of number.
If we need to examine layout in memory, we can use modified example from last article. We do not need here -lquadmath linker switch to compile.

#include <stdio.h> #include <quadmath.h> #include <stdlib.h> typedef struct quad_storage { unsigned long mant_low:64; unsigned long mant_high:48; unsigned int exponent:15; unsigned int sign:1; } Quad; typedef union quad_helper { __float128 number; Quad storage; } Helper; int main(){ Helper quad = {3.1415926535897932384626433832795029Q}; printf("sign = %u, exponent = %u, mantissa_h = %lu, mantissa_l = %lu\n", quad.storage.sign, quad.storage.exponent, quad.storage.mant_high, quad.storage.mant_low); printf("sign = %X, exponent = %X, mantissa_h = %lX, mantissa_l = %lX\n", quad.storage.sign, quad.storage.exponent, quad.storage.mant_high, quad.storage.mant_low); return 0; }

Interesting part from GDB session:

(gdb) print &pi $1 = (__float128 *) 0x7fffffffe650 (gdb) x/4 0x7fffffffe650 0x7fffffffe650: 0xc51701b8 0x8469898c 0xb54442d1 0x4000921f

We compare it with output of program to see what we are looking at.
Examining tests from GCC source, we find out that all operators which are supported for other floats are supported on __float128, so no funny function calls to add or compare two numbers. Comfort of normal syntax and 34 significant digits. For example we do square root round trip:

dpi = 2.0; qpi = sqrtq(dpi); n = quadmath_snprintf (buf, sizeof buf, "%*.34Qe", 45, dpi-qpi*qpi); if ((size_t) n < sizeof buf) printf ("%s\n", buf);

That code produced -3.8518598887744717061119558851698546e-34 error on my box.

Double precision floats and GDB

They are not human readable in memory like integers, so if you are examining memory in GDB session it is not very likely that you will recognize what number is it. Their layout in memory is specified by IEEE 754 standard. They occupy 64 bits and there is one bit for sign, 11 bits for exponent and 52 bits significand. There is really plenty of read about floats on the web and I will not bother you with theory, significance of their discrete spectrum, rounding and similar. Just want to show you how to handle them during debug session.
I have seen in some lecture, can't remember exactly where, solution to examine memory layout via struct and union. Here is my interpretation of it:

#include <stdio.h> typedef struct double_storage { unsigned long mantissa:52; unsigned int exponent:11; unsigned int sign:1; } Double; typedef union double_helper { double number; Double storage; } Helper; int main(){ Helper dbl = {3.141592653589793}; printf("number = %16.15lf sign = %u, exponent = %u, mantissa = %lu\n", dbl.number, dbl.storage.sign, dbl.storage.exponent, dbl.storage.mantissa); printf("number = %16.15g sign = %X, exponent = %X, mantissa = %lX\n", dbl.number, dbl.storage.sign, dbl.storage.exponent, dbl.storage.mantissa); return 0; }

In struct we have specified layout and union is helper so that we can see number in human readable representation. We compile it using GCC -g switch and we start GDB session to examine how layout looks like. Before that we execute program once and save output so that we can recognize our number, the second printf is of interest.

number = 3.14159265358979 sign = 0, exponent = 400, mantissa = 921FB54442D18

Here is debug session, rather most interesting parts:

(gdb) print &dbl $1 = (Helper *) 0x7fffffffe700 (gdb) x/2x 0x7fffffffe700 0x7fffffffe700: 0x54442d18 0x400921fb

After variable is initialized we queried address of it and then we asked for two units to be printed in hex format. Just to be on the safe side, let us print them 32bit by 32bit and 4 bytes by 4 bytes as well

(gdb) x/x 0x7fffffffe704 0x7fffffffe704: 0x400921fb (gdb) x/4b 0x7fffffffe704 0x7fffffffe704: 0xfb 0x21 0x09 0x40 (gdb) x/x 0x7fffffffe700 0x7fffffffe700: 0x54442d18 (gdb) x/4b 0x7fffffffe700 0x7fffffffe700: 0x18 0x2d 0x44 0x54

Where we clearly have little-endian layout in memory, least significant byte at lowest address. The same number in register

(gdb) print/x $rax $4 = 0x400921fb54442d18 (gdb) print/x $eax $5 = 0x54442d18 (gdb) print/x $ax $6 = 0x2d18 (gdb) print/x $al $7 = 0x18

Here we do not really have address so no endianness either. Maybe we could just use f format?

(gdb) x/f 0x7fffffffe700 0x7fffffffe700: 3.1415926535897931

OpenMP, file tree walk and map-reduce example

All happens on Linux and we will use GCC and built in OpenMP support and also thread local storage. We will see how easy is to spread workload across threads using OpenMP.
In man pages we find complete example for nftw or file tree walk. That example will print to standard output about everything what it finds while walking directories. Instead of having it printing everything I made it count files:

#define _XOPEN_SOURCE 500 #include <ftw.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <stdint.h> int counter = 0; int count_files(const char *fpath, const struct stat *sb, int tflag, struct FTW *ftwbuf) { if(tflag == FTW_F){ counter++; } return 0; /* To tell nftw() to continue */ } int main(int argc, char *argv[]) { if (nftw((argc < 2) ? "." : argv[1], count_files, 20, 0) == -1) { perror("nftw"); exit(EXIT_FAILURE); } printf("There was %d files in %s\n", counter, (argc > 1)?argv[1]:"."); exit(EXIT_SUCCESS); }

To compile it I used:

gcc -O1 -o simpleftw simpleftw.c

In order to count files across multiple directories we will pass few directories in argument list to program and also turn our static variable counter into thread local storage. We will use pragma OpenMP parallel for directive combined with reduction which is in our case simple addition:

#define _XOPEN_SOURCE 500 #include <ftw.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <stdint.h> __thread int counter = 0; int count_files(const char *fpath, const struct stat *sb, int tflag, struct FTW *ftwbuf) { if(tflag == FTW_F){ counter++; } return 0; /* To tell nftw() to continue */ } int main(int argc, char *argv[]) { int total_fc = 0; #pragma omp parallel for reduction(+:total_fc) for (int i=1; i< argc; i++){ if (nftw((argc < 2) ? "." : argv[i], count_files, 20, 0) == -1) { perror("nftw"); exit(EXIT_FAILURE); } total_fc += counter; printf("Thread %d %d\n", i, counter); } printf("There was %d files in ",total_fc); for (int i=1; i< argc; i++) printf("%s ", argv[i]); printf("\n"); exit(EXIT_SUCCESS); }

That print inside for loop should demonstrate that thread local storage works as expected. C99 switch is required, or we will have to specify that variable i is private. Also switch for OpenMP is required. To compile it I used:

gcc -O1 -fopenmp -o dwalker dwalker.c -std=c99

Execution produces on my box:

./dwalker ~/Documents/ ~/Downloads/
Thread 1 3894
Thread 2 7784
There was 11678 files in ...

Everything works as expected.

C pointers, assembler and GDB

Idea here is to do some multitasking, find out how C pointer looks like in assembler and use Linux system calls, GCC and GDB. We start with Hello World:

int main() { char *c = "Hello World!\n"; write(1, c, 13); return 0; }

Execute man 2 write in terminal to find out more about write, 1 is standard output and 12 is number of bytes to be written. I saved it as hello.c and this is how it was compiled:

gcc -O0 -m64 -save-temps -o hlo hello.c

GCC will emit assembly code as temp, I like 64 bit version and no optimizations. Content of temp file is:

.file "hello.c" .section .rodata .LC0: .string "Hello World!\n" .text .globl main .type main, @function main: .LFB0: .cfi_startproc pushq %rbp .cfi_def_cfa_offset 16 .cfi_offset 6, -16 movq %rsp, %rbp .cfi_def_cfa_register 6 subq $16, %rsp movq $.LC0, -8(%rbp) movq -8(%rbp), %rax movl $13, %edx movq %rax, %rsi movl $1, %edi movl $0, %eax call write movl $0, %eax leave .cfi_def_cfa 7, 8 ret .cfi_endproc .LFE0: .size main, .-main .ident "GCC: (Debian 4.7.2-5) 4.7.2" .section .note.GNU-stack,"",@progbits

It pushes base pointer onto stack, there is q at the end of the push and bp is called rbp, so it must be 64 bit code. Loads string into memory, later into rax, prepares call to write and so on. Now we take that temp file and compile it with -g switch so that we can use GDB.

gcc -g -O0 -o hlo hello.s
gdb hlo

We type in start at prompt and here is the GDB session:

Temporary breakpoint 1, main () at hello.s:16 16 subq $16, %rsp (gdb) n 17 movq $.LC0, -8(%rbp) (gdb) i r rbp rbp 0x7fffffffe360 0x7fffffffe360 (gdb) x 0x7fffffffe358 0x7fffffffe358: 0x00000000 (gdb) n 18 movq -8(%rbp), %rax (gdb) x 0x7fffffffe358 0x7fffffffe358: 0x004005ec (gdb) x/13c 0x004005ec 0x4005ec: 72 'H' 101 'e' 108 'l' 108 'l' 111 'o' 32 ' ' 87 'W' 111 'o' 0x4005f4: 114 'r' 108 'l' 100 'd' 33 '!' 10 '\n'

Label .LCO is our string, -8(%rbp) is where pointer is. After info registers in short form, I also restricted info to rbp, I find out that rbp points to  0x7fffffffe360 and at  0x7fffffffe360 - 8 = 0x7fffffffe358 is our pointer. No, 0x60 - 8 is not 0x52. After we send next instruction and line 17 is executed we can see our string in memory, slash 13 c is we want 13 characters from that address.
Since write is Linux system call, we can use strace to see what is going on.

strace -tt ./hlo 08:13:04.407694 execve("./hlo", ["./hlo"], [/* 37 vars */]) = 0 ... 08:13:04.412892 write(1, "Hello World!\n", 13Hello World! ) = 13 08:13:04.413108 exit_group(0) = ?

After quite few lines were replaced with three consecutive dots we see that we managed to write all 13 characters to standard output.
Using system calls is so easy that one may even attempt writing to real file:

#include <sys/types.h> #include <sys/stat.h> #include <fcntl.h> int main() { char *c = "Hello World!\n"; int fd; fd = open("deleteme.blog", O_WRONLY | O_CREAT | O_TRUNC | O_APPEND, S_IRUSR | S_IWUSR); if (fd == -1){ write(1, "Open failed!", 12); return 1; } if(write(fd, c, 13)!=13) write(1, "Write failed!", 13); if(close(fd)==-1){ write(1, "Close failed!", 13); return 1; } return 0; }

We added more system calls. We applied common flags and modes in open, checked for all possible errors, after writing string to file we have closed file. If we compile it using

gcc -O0 -m64 -save-temps -o hello2 hello.c

We have binary and also assembly code. With help of strace

strace -tt ./hello2 09:24:49.906151 execve("./hello2", ["./hello2"], [/* 37 vars */]) = 0 ... 09:24:49.911601 open("deleteme.blog", O_WRONLY|O_CREAT|O_TRUNC|O_APPEND, 0600) = 3 09:24:49.911842 write(3, "Hello World!\n", 13) = 13 09:24:49.912018 close(3) = 0 09:24:49.912386 exit_group(0) = ?

Executing cat deleteme.blog we see that write really worked and strace didn't cheat. Examining assembly code will be interesting to those who are curious how if works on low level. Finally every project manager will take a note that from 08:13:04.407694 to 09:24:49.906151 I have written only 21 line of code.

Extending PostgreSQL with own C functions

Power of open source, if you are not happy with what PostgreSQL currently offers, you write own extension in C. Compile code with your functions into shared library, install it and they will become available from PostgreSQL. OK there are some rules and procedures. Once we are inside PostgreSQL we are using its types, interfaces and utilities. Let us do Hello World example. To build example you will need postgresql-server-dev-9.1 or whatever version you are using, installed on your Linux box.

#include "postgres.h" #include <string.h> #include "fmgr.h" #include "utils/builtins.h" #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif PG_FUNCTION_INFO_V1(say_hello_n); Datum say_hello_n(PG_FUNCTION_ARGS) { text *arg1 = cstring_to_text("Hello "); text *arg2 = PG_GETARG_TEXT_P(0); int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ; text *new_text = (text *) palloc(new_text_size); SET_VARSIZE(new_text, new_text_size); memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ); memcpy(VARDATA(new_text) + (VARSIZE(arg1) - VARHDRSZ), VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ); PG_RETURN_TEXT_P(new_text); }

As we can see we are using new V1 spec. That is modified concat_text example from PostgreSQL documentation, look for 35.9.4. Version 1 Calling Conventions. We have load of different VAR macros. For example:

VARHDRSZ is the same as sizeof(int4), but it's considered good style to use the macro VARHDRSZ to refer to the size of the overhead for a variable-length type.

That is from PostgreSQL documentation. SET_VARSIZE we can find in includes postgres.h

#define SET_VARSIZE_4B(PTR,len) \ (((varattrib_4b *) (PTR))->va_4byte.va_header = (((uint32) (len)) << 2))

Unless you are on big endian. Going through header file one also can read in comments more about Datum and varlena datatypes. Then we got palloc which corresponds to malloc, memcpy you already know and GET and RETURN macros. It is obvious that for writing extensions one needs to familiarize himself with PostgreSQL internals. Power without knowledge and responsibility exists only in fery tails told by “software evangelists” at annual developers developers developers meetings.
Variables passed around by PostgreSQL may be on the disc, do not change them.
To build shared library I used the following Makefile:

MODULES = say_hello PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS)

Rather long story to get location of pgxs. That pgxs is location of makefiles for building extensions. It is not trivial build and using provided mk files is right way to do it. After that we can copy say_hello.so to some reasonable location or give full path to it in create function declaration.

CREATE FUNCTION say_hello(text) RETURNS text AS '/opt/pg/ext/say_hello', 'say_hello_n' LANGUAGE C STRICT; ------- select say_hello('Filip');

PostgreSQL already allows Python through untrusted language PL/Python. One can utilize power of Python for functions or triggers without learning much about PostgreSQL internals. But again if you need power and speed, you can use what PostgreSQL speaks internally and that is C.

SQLite parameterized query in C

Still very angry at Discovery Health but that is not reason to stop using C. To execute parameterized query we prepare SQL statement with one or more placeholders. Placeholder could be question mark, alone or followed by number, column, dollar sign or at sign followed by alphanumeric. For example:

select title, full_name from jane where id = @id;

We prepare such statement using sqlite3_prepare_v2, later we bind our parameter and finally execute query. To do binding we will use appropriate function, there is few of them:

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*)); int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*); int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

All bind functions will return SQLITE_OK if binding is successful or error code if it fails. The first argument is handle to prepared statement. The second argument is index of parameter. The third argument is value to be set. For blob, text we have the fourth argument, size in bytes and the fifth – destructor function. Instead of destructor function we can pass constants SQLITE_STATIC - do nothing or SQLITE_TRANSIENT – make local copy and free it when done. To find out what is index of parameter we are using this function:

int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName);

We pass prepared statement and parameter name it returns zero if there is no such parameter or parameter index if it exists. Even if we know that our parameter must have index one, we will still look for it to demonstrate how it is done. Here is the code:

#include <sqlite3.h> #include <stdio.h> #include <string.h> int main(int argc, char **argv) { int rc, i, v, col; sqlite3 *db; sqlite3_stmt *stmt; char *sql; rc = sqlite3_open_v2("surogat.db", &db, SQLITE_OPEN_READWRITE, NULL); if(rc != SQLITE_OK) { printf("Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); return 1; } sql = "select title, full_name from jane where id = @id"; rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0); if(rc != SQLITE_OK) { printf("SQL error: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); return 1; } i = sqlite3_bind_parameter_index(stmt, "@id"); v = 3; rc = sqlite3_bind_int(stmt, i, v); if(rc != SQLITE_OK) { printf("SQL error: %s\n", sqlite3_errmsg(db)); sqlite3_finalize(stmt); sqlite3_close(db); return 1; } rc = sqlite3_step(stmt); col = sqlite3_column_count(stmt); for(i=0; i < col; i++) { printf("%s\t",sqlite3_column_name(stmt, i)); } printf("\n-----------------\n"); while(rc == SQLITE_ROW) { for(i=0; i < col; i++) { printf("%s\t", sqlite3_column_text(stmt, i)); } printf("\n"); rc = sqlite3_step(stmt); } sqlite3_finalize(stmt); sqlite3_close(db); return 0; }

Database should be loaded with required values in previous examples, if not here is sql to create it:

CREATE TABLE jane ( id INTEGER PRIMARY KEY NOT NULL, title TEXT, full_name TEXT NOT NULL )
INSERT INTO jane VALUES(1,'Mr','Smith');
INSERT INTO jane VALUES(2,'Mrs','Doe');
INSERT INTO jane VALUES(3,'Mr','Doe');

After we build it using:

gcc -g test.c -lsqlite3 -o test

We execute test and see the following output:

title full_name ----------------- Mr Doe

We could also misspell parameter name and rebuild to check is error handling working.