Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to use DBLink to achieve autonomous affairs in PostgreSQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Install the dblink plug-in and verify that dblink can connect to the local PG

[local]: 5432 pg12@testdb=# create extension dblink;ERROR: extension "dblink" already existsTime: 1.128 ms [local]: 5432 pg12@testdb=# select T1 1.* from dblink ('host=/tmp port=5432 dbname=testdb user=pg12','select * from T1') as T1 (id int,c1 int,c2 int) limit 1; id | C1 | c2-- +-4 | 4 | 4 (1 row) Time: 1413.943 ms (00Rule 01.414)

Create data tables and stored procedures

[local]: 5432 pg12@testdb=# drop table if exists log;DROP TABLETime: 38.531 ms [local]: 5432 pg12@testdb=# CREATE TABLE log (pg12@testdb (# msg text not null,pg12@testdb (# local_ts timestamptz default now () pg12@testdb) CREATE TABLETime: 8.686 ms [local]: 5432 pg12@testdb=# CREATE OR REPLACE FUNCTION log_dblink (msg text) pg12@testdb-# RETURNS voidpg12@testdb-# LANGUAGE sqlpg12@testdb-# AS $function$pg12@testdb$# select dblink ('host=/tmp port=5432 user=pg12 dbname=testdb',pg12@testdb$# format (' insert into log select% L,% msg, clock_timestamp ():: text)) pg12@testdb$# $function$;CREATE FUNCTIONTime: 3.657 ms

Test validation, start the transaction, call the function, and then rollback

[local]: 5432 pg12@testdb=# begin;BEGINTime: 1.293 ms [local]: 5432 pg12@testdb=#* select log_dblink ('log message'); log_dblink-(1 row) Time: 16.394 ms [local]: 5432 pg12@testdb=#* rollback;ROLLBACKTime: 0.845 ms

SQL main transaction isolation performed by dblink, commit

[local]: 5432 pg12@testdb=# select * from log Msg | sender_ts | local_ts-+-log message | 2019-08-05 14 : 23ms 30.4591831508 | 2019-08-05 14VAND 2330.4680478.08 (1 row) Time: above 0.695 ms is how to use DBLink to achieve autonomous transactions in PostgreSQL The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.