postgresql 自治事务(autonomous_transaction)的介绍之一 pg_background版本下载安装 pg_background

84 阅读 0 评论 56 点赞

我是靠谱客的博主诚心嚓茶，最近开发中收集的这篇文章主要介绍postgresql 自治事务(autonomous_transaction)的介绍之一 pg_background版本下载安装 pg_background，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

os: centos 7.4
db: postgresql 10.10

自治事务，它将记录独立于数据库主线事务运行，并且不关心主线事务的最终结果。

oracle 通过添加 pragma autonomous_transaction 实现自治事务，可以很安全的实现写日志的安全性。参考
<<Oracle 的自治事务 AUTONOMOUS TRANSACTION>>

那么 postgresql 如何处理自治事务了？

目前有如下三种方式：
pg_background
dblink
pragma autonomous_transaction

先介绍下pg_background 的实现方式.

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# su - postgres
Last login: Sat Oct 26 22:55:25 CST 2019 on pts/0
$
$ psql -c "select version();"
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

下载安装 pg_background

默认的 extension 是没有 pg_backgroud ,这个 extension 有点类似 linux 的 nohup 命令

postgres=# dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# select * from pg_available_extensions where name like '%pg_backgroud%' order by name;
 name | default_version | installed_version | comment 
------+-----------------+-------------------+---------
(0 rows)

需要额外下载安装

# su - postgres
$ git clone https://github.com/vibhorkum/pg_background.git
$ cd pg_background
$ more README.md

$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin
$ export PATH=/usr/pgsql-10/bin:${PATH}
$ echo $PATH
/usr/pgsql-10/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin

$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-10/include/server -I/usr/pgsql-10/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pg_background.o pg_background.c
pg_background.c: In function ‘pg_background_result’:
pg_background.c:333:57: error: request for member ‘atttypid’ in something not a structure or union
     getTypeBinaryInputInfo(funcctx->tuple_desc->attrs[i].atttypid,
                                                         ^
pg_background.c:440:89: error: request for member ‘atttypid’ in something not a structure or union
                                                         if (type_id != tupdesc->attrs[i].atttypid)
                                                                                         ^
pg_background.c:446:74: error: request for member ‘atttypid’ in something not a structure or union
                                                 else if(tupdesc->attrs[i].atttypid != TEXTOID)
                                                                          ^
pg_background.c:510:47: error: request for member ‘atttypid’ in something not a structure or union
   if (tupdesc->natts != 1 || tupdesc->attrs[0].atttypid != TEXTOID)
                                               ^
pg_background.c: In function ‘form_result_tuple’:
pg_background.c:569:29: error: request for member ‘atttypmod’ in something not a structure or union
            tupdesc->attrs[i].atttypmod);
                             ^
pg_background.c:579:29: error: request for member ‘atttypmod’ in something not a structure or union
            tupdesc->attrs[i].atttypmod);
                             ^
pg_background.c: In function ‘pg_background_worker_main’:
pg_background.c:808:82: error: too many arguments to function ‘BackgroundWorkerInitializeConnection’
                                                                                  NameStr(fdata->authenticated_user), 0);
                                                                                  ^
In file included from /usr/pgsql-10/include/server/storage/shm_mq.h:16:0,
                 from /usr/pgsql-10/include/server/libpq/pqmq.h:17,
                 from pg_background.c:27:
/usr/pgsql-10/include/server/postmaster/bgworker.h:141:13: note: declared here
 extern void BackgroundWorkerInitializeConnection(char *dbname, char *username);
             ^
make: *** [pg_background.o] Error 1

编译有错误，看 github 上的 issues ，貌似作者已经移情别恋，不再更新这个了。

pg_background 先这样吧。
参考:
https://github.com/vibhorkum/pg_background
http://blog.dalibo.com/2016/08/19/Autonoumous_transactions_support_in_PostgreSQL.html