postgresql 自治事务(autonomous_transaction)的介绍之一 pg_background

postgresql extension 同时被 2 个专栏收录
65 篇文章 1 订阅

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

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:代码科技 设计师:Amelia_0503 返回首页

打赏作者

数据库人生

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值