Golang procedural language for Postgresql

Question

I'm trying to compile and run go code as Postgresql stored procedure. My motivation is because postgresql can have excensions written in C and golang can be compiled as c-shared

So I have to files, pl.go:

package main

/*
#cgo CFLAGS: -Wall -Wpointer-arith -Wno-declaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -march=x86-64 -mtune=generic -O2 -pipe -fstack-protector-strong -fpic -I. -I./ -I/usr/include/postgresql/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2
#cgo LDFLAGS: -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -march=x86-64 -mtune=generic -O2 -pipe -fstack-protector-strong -fpic -L/usr/lib -Wl,-O1,--sort-common,--as-needed,-z,relro  -Wl,--as-needed -Wl,-rpath,'/usr/lib',--enable-new-dtags -shared

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

//the return value must be allocated trough palloc
void* ret(void *val, uint64 *size) {
    void *retDatum = palloc(*size);
    memcpy(retDatum, val, *size);
    return retDatum;
}

PG_FUNCTION_INFO_V1(plgo_func);
*/
import "C"
import "unsafe"

func main() {}

//PGVal returns the Postgresql C type from Golang type (currently implements just stringtotext)
func PGVal(val interface{}) (ret interface{}) {
    var size uintptr
    switch v := val.(type) {
    case string:
        ret = C.cstring_to_text(C.CString(v))
        size = unsafe.Sizeof(ret)
    default:
        ret = val
        size = unsafe.Sizeof(ret)
    }
    return C.ret(ret, (*C.uint64)(unsafe.Pointer(size)))
}

the CFLAGS and LDFLAGS i'we got from pg_config

and the file where I create the function to call, plgo.go:

package main

/*
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
*/
import "C"

//export plgo_func
func plgo_func(fcinfo *C.FunctionCallInfoData) interface{} {
    return PGVal("meh")
}

the shared library is created with: go build -buildmode=c-shared -o plgo.so plgo.go pl.go && sudo cp plgo.so /usr/lib/postgresql

the function in postgresql is created with:

CREATE OR REPLACE FUNCTION public.plgo_func(integer)
  RETURNS text AS
'$libdir/plgo', 'plgo_func'
  LANGUAGE c IMMUTABLE STRICT
  COST 1;

but when I run: psql -U root -d meh -c "select plgo_func(0)"

the server crashes with:

server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
connection to server was lost

EDIT: I've successfully created an golang "library" for creating stored procedures and triggers in golang plgo :)

Answer 1

The trick is to use version 0 calling conventions as those allow calling simple C functions without using all the fancy macros they added to version 1 calling.

You also need a single C file to invoke the PG_MODULE_MAGIC macro.

I have a working example project that does all this, might be easiest if you just copied that as a starting point.

Not sure it will let you do exactly what you want, but it does indeed work for some use cases.

https://github.com/dbudworth/gopgfuncs

Will also outline the same stuff that repo tells you...

Step 1

create a .go file with you functions and the CGO includes

package main

//#include "postgres.h"
//#include "fmgr.h"
//#include <string.h>
import "C"
import (
    "log"
    "sync/atomic"
)

// Functions are scoped to the db session
var counter int64

//Inc atomically increments a session local counter by a given delta
//export Inc
func Inc(delta C.int64) C.int64 {
    log.Printf("Inc(%v) called", delta)
    return C.int64(atomic.AddInt64(&counter, int64(delta)))
}

//AddOne adds one to the given arg and retuns it
//export AddOne
func AddOne(i C.int) C.int {
    log.Printf("AddOne(%v) called", i)
    return i + 1
}

func main() {
}

Step 2

create a .c file in the same directory that invokes the PG_MODULE_MAGIC macro from postgres.h, this is a requirement of all extension libraries. Go will automatically include the C file while compiling, no special instructions are needed.

#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

Step 3

Build your so file, trick here is to use -buildmode=c-shared

go build -buildmode=c-shared -o libMyMod.so

Step 4

Execute SQL to register your functions. Since registing an extension requires absolute paths, I prefer to pass the module on the command line to avoid making the "install.sql" script have hard coded paths in it.

PGMOD=`pwd`/libMyMod.so
psql $(DBNAME) --set=MOD=\'$(PGMOD)\' -f install.sql

Install script contains one of these statements per function you export.

It's absolutely critical you get the input / output types right as there's nothing the system can do to verify them and you may end up stomping on memory, storage, whatever. You can see the translation of pg types to c types here

create or replace function add_one(integer)
  returns integer as :MOD,'AddOne'
  LANGUAGE C STRICT;