使用 Go 编写 PostgreSQL 触发器 已翻译 100%

oschina 投递于 2017/04/20 22:10 (共 7 段, 翻译完成于 05-03)
阅读 5478
收藏 38
0
加载中

How to implement PostgreSQL functions and triggers in Golang.

Triggers in PostgreSQL are a simple yet powerful mechanism to react to changes happening in tables.

Read on to find out how to write PostgreSQL triggers in Go.

POSTGRESQL FUNCTIONS AND TRIGGERS

PostgreSQL lets you create user-defined functions using the CREATE FUNCTION SQL statement. Functions are essentially how PostgreSQL can manage user-defined pieces of logic.

Functions can be written in various languages – the most common one is probably PL/pgSQL, which is what you use when you write “stored procedures”. You can also write them in other languages, like Python and Perl.

They can also be written in C. For this, the C code has to be compiled into a dynamically loadable shared library (*.so). PostgreSQL can be told that a function lives as a certain symbol name in a certain *.so file. This is somewhat similar to how modules in Apache or Nginx work.

Functions can be used as triggers, which is what we’re interested in.

已有 1 人翻译此段
我来翻译

TRIGGERS

Triggers are a form of event handlers – they are pieces of logic that can be executed when certain events happen to specified objects. Typically, the objects involved are tables, but they can also be views or foreign tables.

The events, not surprisingly, are:

  • insert (rows)

  • update (rows)

  • delete (rows)

  • truncate (table)

PostgreSQL triggers are versatile:

  • They can be invoked once per row or once per statement. For example, if a statement updates 5 rows, the trigger can be invoked once for the statement or 5 times, one for each row.

  • They can be invoked before or after the actual change happens.

  • The “before” triggers have a change to modify the values or cancel the change.

  • Triggers can be used to impose any arbitrary constraint on a table.

The most popular use of triggers is probably creating audit logs (or more specifically, change logs). You can read more about triggers here and here.

已有 1 人翻译此段
我来翻译

DYNAMICALLY LOADABLE MODULES IN GO

Starting with version 1.5, Go has the ability to create C-style shared libraries. Using this, you can export an arbitrary Go function that can be invoked by other language runtimes – like dlopen/dlysm in C, ctypes in Python or JNI in Java.

You can build a C-style shared library in Go like this:

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

Here myso.go is a Go main package, which looks like this:

package main

import "C"

//export MyName
func MyName(x int) int {
	return 42 + x
}

func main() {
	// empty
}

Note the “decorator” comment just above the exported function. The import "C" statement is also required for the export to happen.

已有 1 人翻译此段
我来翻译

WRITING POSTGRESQL FUNCTIONS IN GO

With this feature, we can build a *.so file contains an exported method that can be invoked as a PostgreSQL function.

There are some conventions that must be adhered to when writing this function – they are detailed here.

Let’s start off by defining the “module”, and listing an exported function called mytrigger.

// file module.go

package main

/*
#include "postgres.h"
#include "fmgr.h"

#cgo LDFLAGS: -Wl,--unresolved-symbols=ignore-all

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(mytrigger);
*/
import "C"

func main() {
}

Note the LDFLAGS declaration. This lets us build the so file without the linker complaining about unresolved symbols. For PostgreSQL, there are no libraries to link against, and the symbols that are needed by our shared library can be verified only when the so file is loaded by PostgreSQL.

已有 1 人翻译此段
我来翻译

Next, let’s flesh out the trigger function itself in another file mytrigger.go:

// file mytrigger.go

package main

/*
#include "postgres.h"
#include "commands/trigger.h"

//...

*/
import "C"
import (
	"fmt"
	"unsafe"
)

//export mytrigger
func mytrigger(fcInfo *C.FunctionCallInfoData) C.Datum {
	trigdata := (*C.TriggerData)(unsafe.Pointer(fcInfo.context))

	//...
}

The signature of the exported Go function, mytrigger, is mandated by the PostgreSQL function manager convention. In case of triggers, this function is passed the row itself, which it can possibly modify (in case of “before” triggers), and return back.

For now, we’ll create a simple function that will be triggered after INSERTs and UPDATEs. It will not modify the data, and will return it back unchanged. Let’s also assume that the first column in the row will of type “text”, which we’ll read and print.

Now would be a good time to look at how the trigger would look like in C. Here is the example from the PostgreSQL docs.

Within the function, we want to first get the correct row data, since the function can be invoked via an INSERT or an UPDATE:

	var rettuple *C.HeapTupleData
	if C.trigger_fired_by_update(trigdata.tg_event) != 0 {
		rettuple = (*C.HeapTupleData)(trigdata.tg_newtuple)
	} else {
		rettuple = (*C.HeapTupleData)(trigdata.tg_trigtuple)
	}

And then we’ll extract the first column data (indices start from 1), assuming it is a “text” data type (with no embedded NULs):

	url := C.GoString(C.getarg_text(trigdata, rettuple, 1))

We’ll just print it out for now, rather than actually processing it:

	C.elog_info(C.CString(fmt.Sprintf("got url=%s", url)))
	fmt.Println(url)

And finally return the original, unmodified data:

	return C.pointer_get_datum(rettuple)

The full file can be seen here. See below for the github repo link and build instructions.

已有 1 人翻译此段
我来翻译

RUNNING THE TRIGGER

To see the trigger in action, first let’s create a table:

$ sudo -u postgres psql -d test
psql (9.6.2)
Type "help" for help.

test=# CREATE TABLE urls ( url TEXT );
CREATE TABLE
test=#

And then our function (you’ll need the USAGE privilege on language C for this):

test=# CREATE FUNCTION mytrigger()
test-# RETURNS TRIGGER AS '/home/alice/ptgo/ptgo.so'
test-# LANGUAGE C;
CREATE FUNCTION
test=#

Next let’s create a trigger on INSERT and UPDATE on table urls, that invokes our function:

test=# CREATE TRIGGER trig_1
test-# AFTER INSERT OR UPDATE
test-# ON urls
test-# FOR EACH ROW
test-# EXECUTE PROCEDURE mytrigger();
CREATE TRIGGER
test=#

Now let’s insert a couple of rows. The “got url=” lines are printed by our function:

test=# INSERT INTO urls VALUES ('http://example.com/');
INFO:  got url=http://example.com/
INSERT 0 1
test=#
test=# INSERT INTO urls VALUES ('http://mydomain.com/');
INFO:  got url=http://mydomain.com/
INSERT 0 1
test=#

And when the rows are updated, the function receives the post-change values because it is an AFTER trigger:

test=# UPDATE urls SET url='http://www.test.com/';
INFO:  got url=http://www.test.com/
INFO:  got url=http://www.test.com/
UPDATE 2
test=#

And that’s it! We have our very own PostgreSQL trigger written in Go!

已有 1 人翻译此段
我来翻译

THE CODE

The entire code is available on GitHub here: github.com/rapidloop/ptgo. Feel free to fork it and modify it to implement your own triggers. It has been tested only on Linux. To get started, do:

git clone https://github.com/rapidloop/ptgo
cd ptgo
make

You might need to install the development package for Postgres first. For Debian-based systems, this can be done with:

sudo apt-get install postgresql-server-dev-9.6
已有 1 人翻译此段
我来翻译
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接。
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。
加载中

评论(6)

曾摩西爷爷
模组是港台词汇,不伦不类。应该翻译成为模块。
不要看我签名
不要看我签名

引用来自“kchr”的评论

用 python, perl, go 写触发器,这都什么破事。

写一个触发器,操控一个 3D 打印机,把数据打印出来,然后旁边的摄像头加以识别,并通过高能激光发射到月球,通过预先放置在月球的一个镜子,反射到北极,然后俄罗斯核潜艇穿破北极冰盖,接收到数据,存到 U 盘,去美国友好访问的时候,交给特朗普,特朗普发到推特,NSA 看到推特,攻入 PostgreSQL 服务器,激活隐藏在硬盘固件内的木马,直接在硬盘的区块上写入结果。

这他妈的不是更酷?
我只服你,👿
三石头123
三石头123
k
kchr
用 python, perl, go 写触发器,这都什么破事。

写一个触发器,操控一个 3D 打印机,把数据打印出来,然后旁边的摄像头加以识别,并通过高能激光发射到月球,通过预先放置在月球的一个镜子,反射到北极,然后俄罗斯核潜艇穿破北极冰盖,接收到数据,存到 U 盘,去美国友好访问的时候,交给特朗普,特朗普发到推特,NSA 看到推特,攻入 PostgreSQL 服务器,激活隐藏在硬盘固件内的木马,直接在硬盘的区块上写入结果。

这他妈的不是更酷?
阿福Chris
阿福Chris
是不是有点多此一举的意思
RaphaelShin
RaphaelShin
好东西
返回顶部
顶部