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 :)

C doesn't have any concept of a Go interface{}. You need to return a C type from your exported function. (If that doesn't work, you need to get dome more debugging info from the server as to why it crashed) — Mr_Pink
– Mr_Pink, Commented Jul 26, 2016 at 13:19
That didn't work, how can i add some debugging/log to that code? import "log" and than printing to file doesn't work ... — microo8
– microo8, Commented Jul 26, 2016 at 13:33
I would guess that you're segfaulting in the ret function, since cstring_to_text returns a *text, and you palloc only the size of that pointer, then copy the text struct into that location. I would make a proof of concept in C first to ensure you can get that working before expanding to Go/cgo. — Mr_Pink
– Mr_Pink, Commented Jul 26, 2016 at 13:54

David Budworth · Accepted Answer · 2016-07-27 21:31:56Z

9

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;

answered Jul 27, 2016 at 21:31

David Budworth

11.7k1 gold badge38 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

microo8 Over a year ago

Looks good, I'we worked the version 1 around and now there is an complicated mess on my github: github.com/microo8/plgo There must not be the all the CFLAGS and LDFLAGS options? just build c-shared?

David Budworth Over a year ago

yeah, that's why I stuck with version 0. I wanted stored procs in go. Not write a bunch of C code to connect it all together. For sure you dont need all the flags taking my approach, maybe it's required for the v1 macros

Collectives™ on Stack Overflow

Golang procedural language for Postgresql

1 Answer 1

Step 1

Step 2

Step 3

Step 4

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Step 1

Step 2

Step 3

Step 4

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related