PostgreSQL 10.0 preview 功能增强 - 匿名、自治事务(Oracle 兼容性)-阿里云开发者社区

开发者社区> 德哥> 正文

PostgreSQL 10.0 preview 功能增强 - 匿名、自治事务(Oracle 兼容性)

简介:
+关注继续查看

标签

PostgreSQL , 10.0 , 匿名事务 , 自治事务


背景

PostgreSQL 10.0 通过session backendground实现了匿名事务,从此可以愉快的支持Oracle存储过程的自治事务了。

此前,我们需要通过dblink实现,或者通过匿名块+exception来实现,比较繁琐。

《PostgreSQL Oracle 兼容性之 - plpgsql 自治事务(autonomous_transaction)补丁》

《PostgreSQL Oracle 兼容性之 - 函数 自治事务 的写法和实现》

I would like to propose the attached patch implementing autonomous  
transactions for discussion and review.  
  
This work was mostly inspired by the discussion about pg_background a  
while back [0].  It seemed that most people liked the idea of having  
something like that, but couldn't perhaps agree on the final interface.  
Most if not all of the preliminary patches in that thread were  
committed, but the user interface portions were then abandoned in favor  
of other work.  (I'm aware that rebased versions of pg_background  
existing.  I have one, too.)  
  
The main use case, in a nutshell, is to be able to commit certain things  
independently without having it affected by what happens later to the  
current transaction, for example for audit logging.  
  
My patch consists of three major pieces.  (I didn't make them three  
separate patches because it will be clear where the boundaries are.)  
  
- A API interface to open a "connection" to a background worker, run  
queries, get results: AutonomousSessionStart(), AutonomousSessionEnd(),  
AutonomousSessionExecute(), etc.  The communication happens using the  
client/server protocol.  
  
- Patches to PL/pgSQL to implement Oracle-style autonomous transaction  
blocks:  
  
AS $$  
DECLARE  
  PRAGMA AUTONOMOUS_TRANSACTION;  
BEGIN  
  FOR i IN 0..9 LOOP  
    START TRANSACTION;  
    INSERT INTO test1 VALUES (i);  
    IF i % 2 = 0 THEN  
        COMMIT;  
    ELSE  
        ROLLBACK;  
    END IF;  
  END LOOP;  
  
  RETURN 42;  
END;  
$$;  
  
This is very incomplete and has some open technical issues that I will  
discuss below.  But those are all issues of PL/pgSQL, not really issues  
of how autonomous sessions work.  
  
Basically, a block that is declared with that pragma uses the autonomous  
C API instead of SPI to do its things.  
  
- Patches to PL/Python to implement a context manager for autonomous  
sessions (similar to how subtransactions work there):  
  
with plpy.autonomous() as a:  
    for i in range(0, 10):  
        a.execute("BEGIN")  
        a.execute("INSERT INTO test1 (a) VALUES (%d)" % i)  
        if i % 2 == 0:  
            a.execute("COMMIT")  
        else:  
            a.execute("ROLLBACK")  
  
This works quite well, except perhaps some tuning with memory management  
and some caching and some refactoring.  
  
While the PL/pgSQL work is more of a top-level goal, I added the  
PL/Python implementation because it is easier to map the C API straight  
out to something more accessible, so testing it out is much easier.  
  
  
The main technical problem I had with PL/pgSQL is how to parse named  
parameters.  If you're in PL/Python, say, you do  
  
    plan = a.prepare("INSERT INTO test1 (a, b) VALUES ($1, $2)",  
                     ["int4", "text"])  
  
and that works fine, because it maps straight to the client/server  
protocol.  But in PL/pgSQL, you will want something like  
  
    DECLARE  
      x, y ...  
    BEGIN  
      INSERT INTO test1 (a, b) VALUES (x, y)  
  
When running in-process (SPI), we install parser hooks that allow the  
parser to check back into PL/pgSQL about whether x, y are variables and  
what they mean.  When we run in an autonomous session, we don't have  
that available.  So my idea was to extend the protocol Parse message to  
allow sending a symbol table instead of parameter types.  So instead of  
saying, there are two parameters and here are their types, I would send  
a list of symbols and types, and the server would respond to the Parse  
message with some kind of information about which symbols it found.  I  
think that would work, but I got lost in the weeds and didn't get very  
far.  But you can see some of that in the code.  If anyone has other  
ideas, I'd be very interested.  
  
  
Other than that, I think there are also other bits and pieces that are  
worth looking at, and perhaps have some overlap with other efforts, such as:  
  
- Refining the internal APIs for running queries, with more flexibility  
than SPI.  There have recently been discussions about that.  I just used  
whatever was in tcop/postgres.c directly, like pg_background does, and  
that seems mostly fine, but if there are other ideas, they would be  
useful for this, too.  
  
- An exception to the "mostly fine" is that the desirable handling of  
log_statement, log_duration, log_min_duration_statement for  
non-top-level execution is unclear.  
  
- The autonomous session API could also be useful for other things, such  
as perhaps implementing a variant of pg_background on top of them, or  
doing other asynchronous or background execution schemes.  So input on  
that is welcome.  
  
- There is some overlap with the protocol handling for parallel query,  
including things like error propagation, notify handling, encoding  
handling.  I suspect that other background workers will need similar  
facilities, so we could simplify some of that.  
  
- Client encoding in particular was recently discussed for parallel  
query.  The problem with the existing solution is that it makes  
assign_client_encoding() require hardcoded knowledge of all relevant  
background worker types.  So I tried a more general solution, with a hook.  
  
- I added new test files in the plpgsql directory.  The main test for  
plpgsql runs as part of the main test suite.  Maybe we want to move that  
to the plpgsql directory as well.  
  
- More guidance for using some of the background worker and shared  
memory queue facilities.  For example, I don't know what a good queue  
size would be.  
  
- Both PL/pgSQL and PL/Python expose some details of SPI in ways that  
make it difficult to run some things not through SPI.  For example,  
return codes are exposed directly by PL/Python.  PL/pgSQL is heavily  
tied to the API flow of SPI.  It's fixable, but it will be some work.  I  
had originally wanted to hide the autonomous session API inside SPI or  
make it fully compatible with SPI, but that was quickly thrown out.  
PL/Python now contains some ugly code to make certain things match up so  
that existing code can be used.  It's not always pretty.  
  
- The patch "Set log_line_prefix and application name in test drivers"  
(https://commitfest.postgresql.org/10/717/) is helpful in testing and  
debugging this.  
  
  
[0]:  
https://www.postgresql.org/message-id/flat/CA+Tgmoam66dTzCP8N2cRcS6S6dBMFX+JMba+mDf68H=KAkNjPQ(at)mail(dot)gmail(dot)com  
  
--   
Peter Eisentraut              http://www.2ndQuadrant.com/  
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services  

这个patch的讨论,详见邮件组,本文末尾URL。

PostgreSQL社区的作风非常严谨,一个patch可能在邮件组中讨论几个月甚至几年,根据大家的意见反复的修正,patch合并到master已经非常成熟,所以PostgreSQL的稳定性也是远近闻名的。

参考

https://commitfest.postgresql.org/13/873/

https://www.postgresql.org/message-id/flat/659a2fce-b6ee-06de-05c0-c8ed6a01979e@2ndquadrant.com#659a2fce-b6ee-06de-05c0-c8ed6a01979e@2ndquadrant.com

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
duilib 修复CTreeViewUI复选功能判断不准确的bug
转载请说明出处,谢谢~~:http://blog.csdn.net/zhuhongshu/article/details/42265209         CTreeViewUI里面自带了复选的功能,但是复选功能存在bug:       ...
802 0
spring事物配置,声明式事务管理和基于@Transactional注解的使用
spring事物配置,声明式事务管理和基于@Transactional注解的使用 事物管理对于企业应用来说是至关重要的,好使出现异常情况,它也可以保证数据的一致性。 spring支持编程式事务管理和声明式事务管理两种方式。
1472 0
Sharding-Proxy的基本功能使用
Sharding-Proxy是一个分布式数据库中间件,定位为透明化的数据库代理端。作为开发人员可以完全把它当成数据库,而它具体的分片规则在Sharding-Proxy中配置。
866 0
Android 禁止Viewpager左右滑动功能
首先自定义一个 继承自 ViewPager的自定义 类 package com.yourcompany; import android.content.Context; import android.
831 0
TensorFlow新功能:TensorFlow Probability概率编程工具箱介绍
2018年,tensorflow开发者峰会上,tensorflow管理人员发布了:TensorFlow Probability——一种概率编程工具箱,用于机器学习研究人员和从业人员快速可靠地构建利用最先进硬件的复杂模型。快来学习一下吧~
3337 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载