Use Go to write PostgreSQL triggers "translated 100%"

This is a creation in Article, where the information may have evolved or changed.

How do I apply the PostgreSQL function and the trigger in Golang?

The trigger in PostgreSQL is a simple but powerful mechanism that reacts to the changes that are taking place in the table.

The following describes how to write a PostgreSQL trigger in Go.

PostgreSQL functions and Triggers

By using the CREATE FUNCTION SQL statement, PostgreSQL allows you to create user-defined functions. The function is essentially the user-defined part of how PostgreSQL manages the logic.

Functions can be written in multiple languages – most likely the most common is pl/pgsql, which is the function used when writing "stored Procedures". You can also write in other languages, such as Python and Perl.

You can also write them in C code. The C code must therefore be compiled into a shared library (*.so) that can be dynamically loaded. PostgreSQL is told that in a *.so file, a function will exist with a symbolic name. This approach is somewhat similar to how the module works in Apache or Nginx.

The function can be used as a trigger, and that is the part of our interest.

Janetrans translated 2 months ago 0 People top Top Well translated!

Trigger

Triggers are a form of event handlers-they are logical blocks that can be executed when a specific event occurs for a specified object. Typically, they involve tables, but they can also be views or external tables.

These events are usually:

• Insert (rows)

• Update (rows)

• Delete (rows)

• Truncate (table)

PostgreSQL triggers are useful for a wide range of purposes:

• They can be called once per line or once per statement. For example, if a statement updates 5 rows, the statement can be executed one time, or every 5 lines, or each row is called.

• They can be called before or after the actual change occurs.

• The "before" trigger can change the modified value or cancel the change.

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

The most popular triggers may be used to create an audit log (or, more specifically, to modify the log). You can read more about triggers here and here.

total length translated 1 months ago 0 People top Top Well translated!

Dynamic loading of modules using the Go language

The Go language supports the creation of a C language-style shared library starting with version 1.5. In this way, any Go function can be exported to other languages, similar to other languages such as Dlopen/dlysm for C, Python ctypes, and Java JNI.

You can use the following instructions to create a C-style shared library:

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

The myso.go here is a main package written with Go, with the following code:

Package Mainimport "C"//export mynamefunc MyName (x int) int {return + X}func main () {//Not implemented}

Note that the export function MyName has a cosmetic comment above it. In addition, if you want to export, you have to write import "C" in the code.

Azuresora translated 1 months ago 0 People top Top Well translated!

Using the Go language to write PostgreSQL functions

You can create a *.so file that contains all the methods, and then write the PostgreSQL function that you want to export.

When writing such a function, you must follow certain rules, please click here for details.

So now, let's define our module and write an export function called Mytrigger.

File Module.gopackage main/* #include "postgres.h" #include "fmgr.h" #cgo ldflags:-wl,--unresolved-symbols= Ignore-all#ifdef pg_module_magicpg_module_magic; #endifPG_FUNCTION_INFO_V1 (Mytrigger); */import "C" Func Main () {}

Note the Ldflags declaration in the code. When it is used, the linker will not be prompted to encounter an unresolved symbol when it generates the so file. Because we are using PostgreSQL now, there is no external library that needs to be linked, and those symbols are only checked for the presence of PostgreSQL when the so file is loaded.

Azuresora translated 1 months ago 0 People top Top Well translated!

We then refine the trigger function in file Mytrigger.go:

File Mytrigger.gopackage main/* #include "postgres.h" #include "commands/trigger.h"//...*/import "C" Import ("FMT" " unsafe ")//export mytriggerfunc Mytrigger (fcinfo *c.functioncallinfodata) c.datum {trigdata: = (*c.triggerdata) (unsafe . Pointer (Fcinfo.context))//...}

The exported Go function name Mytrigger is guaranteed to be unique by the PostgreSQL functions management mechanism. In a trigger, the row data is passed in, it is possible to modify the row data (for example, in the "before" trigger), and then pass the modified data through.

Here we write a simple function body, which is triggered when an INSERT and UPDATE operation is performed, and the function will not modify the data, only read and print it, assuming that the first column of the row data is of type "text".

Now it's time to look at how the code for the trigger function is in C, see this instance in the PostgreSQL document URL.

In the function body, we take the row data first, because the function is called by INSERT and UPDATE:

var rettuple *c.heaptupledataif c.trigger_fired_by_update (trigdata.tg_event)! = 0 {rettuple = (*C.HeapTupleData) ( Trigdata.tg_newtuple)} else {rettuple = (*c.heaptupledata) (Trigdata.tg_trigtuple)}

We then take the first column of data (in order starting at 1), assuming the data is of type "text" (No NULL column):

URL: = c.gostring (C.getarg_text (Trigdata, Rettuple, 1))

We only print it out and do not modify the processing:

C.elog_info (C.cstring (FMT). Sprintf ("Got url=%s", url))) fmt. Println (URL)

Finally, the original unmodified data is returned:

Return C.pointer_get_datum (Rettuple)

The complete code can be seen here. The following sections are links to GitHub libraries and process guidance on how to generate target programs.

s Zhang Limin z translated 1 months ago 0 People top Top Well translated!

Running triggers

Before the trigger runs, create the table:

$ sudo-u postgres psql-d testpsql (9.6.2) Type ' help ' for help.test=# CREATE TABLE URLs (url TEXT); CREATE tabletest=#

After that, it is to create our function (you need to have the C language USAGE permission, like this):

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

Next, let's create an INSERT and UPDATE trigger on the table path and let it call our function:

test=# CREATE TRIGGER trig_1test-# after INSERT OR updatetest-# on urlstest-# for each rowtest-# EXECUTE PROCEDURE Mytrigg ER (); CREATE triggertest=#

Now, let's insert two lines. "Got Url=" is printed through our function:

test=# INSERT INTO URLs VALUES (' http://example.com/'); info:got url=http://example.com/insert 0 1test= #test =# INSERT INT O URLs VALUES (' http://mydomain.com/'); info:got Url=http://mydomain.com/insert 0 1test=#

When the row is updated, the function receives the updated value 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 2test=#

That's it! We have our own PostgreSQL triggers written with Go!

no translation at the top of 0 people 1 months ago Top Well translated!

Code

All code can be accessed on GitHub with the following links: Github.com/rapidloop/ptgo. You can fork and modify to implement your own triggers. It has been tested on Linux only. Please follow the steps below to obtain the source code:

git clone https://github.com/rapidloop/ptgocd Ptgomake

You may need to install the Postgres development package first. For Debian-based systems, you can use the following command:

sudo apt-get install postgresql-server-dev-9.6

tocy translated 2 months ago 0 People top Top Well translated!