2-4.pg特性功能之咨询锁，sql med介绍和使用

PostgreSQL 特性功能：咨询锁与 SQL/MED 介绍

什么是咨询锁（Advisory Lock）

简介

PostgreSQL 允许创建由应用定义其含义且与数据库本身无关的锁，这种锁被称为咨询锁（Advisory Lock）。

与使用 SELECT ... FOR UPDATE 这样的行级锁不同，咨询锁与具体的数据无关，因此能提供更好的性能。

使用场景

当多个进程访问同一个数据库，且需要协调这些进程对非数据库资源的并发访问时，可以使用咨询锁。

咨询锁的函数及使用

咨询锁可以用一个 64 位数字或两个 32 位数字表示，并提供了一系列函数实现加锁和释放锁。

常用咨询锁函数

说明：

• 名称中有 lock 的是加锁函数，有 unlock 的是释放锁函数。
• 名称中有 xact 的是事务级别的咨询锁，否则为会话级别。
• 名称中有 try 的函数会尝试加锁并立即返回结果，不会阻塞。

事务级与会话级咨询锁的关系

事务级与会话级咨询锁实际上指向同一把锁。例如，当一个事务锁住某个键时，另一个会话试图获取同一键的会话级锁也会被阻塞。

示例：

Session 1：

SELECT pg_advisory_lock(1);  -- 获取会话级锁

Session 2：

BEGIN;
SELECT pg_advisory_xact_lock(1);  -- 被阻塞，直到 Session 1 释放锁

并发控制案例

Session 1：

CREATE TABLE test (id INT PRIMARY KEY);
INSERT INTO test VALUES (1), (2);

BEGIN;
DELETE FROM test WHERE id = 1;
INSERT INTO test VALUES (1);
COMMIT;

Session 2（在 Session 1 未提交时执行）：

BEGIN;
DELETE FROM test WHERE id = 1;  -- 可能因锁冲突报错
INSERT INTO test VALUES (1);
COMMIT;

解决方案：使用 pg_try_advisory_xact_lock

SELECT pg_try_advisory_xact_lock(1);  -- 尝试获取锁
DELETE FROM test WHERE id = 1;
INSERT INTO test VALUES (1);
END;

常见问题

1. 连接中断后，咨询锁是否释放？
   会。连接中断后会话结束，所有持有的咨询锁会被释放。

2. 事务回滚或提交后，会话级咨询锁是否释放？
   不会。会话级咨询锁与事务无关。

3. 一个会话持有事务级锁时，另一个会话能否同时持有同一键的会话级锁？
   不能。两者代表同一把锁，会互相阻塞。

SQL/MED（管理外部数据）

介绍

SQL/MED 是 SQL 语言中管理外部数据的一个扩展标准，通过它可以将多种异构数据库或其他 PostgreSQL 数据库连接起来，实现跨数据源查询。

框架示意图：

应用程序 → PostgreSQL → SQL/MED → 外部数据源（如 MySQL、Oracle、文件等）

创建步骤

1. 创建外部数据包装器（FDW）

FDW 定义了如何从外部数据源获取数据。

CREATE EXTENSION postgres_fdw;    -- 用于连接 PostgreSQL
CREATE EXTENSION file_fdw;        -- 用于连接文件
CREATE EXTENSION oracle_fdw;      -- 用于连接 Oracle
CREATE EXTENSION mysql_fdw;       -- 用于连接 MySQL

2. 创建外部服务器对象

定义如何连接外部数据源。

-- 连接另一台 PostgreSQL
CREATE SERVER postgres_fdw_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.10.90', dbname 'center', port '5432');

-- 连接 MySQL
CREATE SERVER mysql_fdw_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '10.0.3.236', port '3306');

权限分配：

GRANT USAGE ON FOREIGN SERVER mysql_fdw_server TO user02;

3. 创建用户映射

定义本地用户与远程用户的映射关系。

CREATE USER MAPPING FOR user01
SERVER postgres_fdw_server
OPTIONS (user 'user02', password 'okuser02');

4. 创建外部表

将外部数据映射为本地表。

CREATE FOREIGN TABLE fttest01 (
    id INT,
    note TEXT
) SERVER postgres_fdw_server
OPTIONS (table_name 'testtab01');

file_fdw 使用实例

将 /etc/passwd 文件映射为外部表。

CREATE EXTENSION file_fdw;

CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE passwd (
    username TEXT,
    pass TEXT,
    uid INT4,
    gid INT4,
    gecos TEXT,
    home TEXT,
    shell TEXT
) SERVER file_fdw_server
OPTIONS (format 'text', filename '/etc/passwd', delimiter ':', null '');

-- 查询示例
SELECT * FROM passwd LIMIT 5;
SELECT * FROM passwd ORDER BY uid ASC LIMIT 10;

日志文件映射（需在 postgresql.conf 中设置）：

log_destination = 'csvlog'
logging_collector = on

postgres_fdw 使用实例

连接远程 PostgreSQL 数据库。

远程数据库准备

CREATE TABLE testtab01 (id INT, note TEXT);
INSERT INTO testtab01 
SELECT generate_series(1,10000), 'a' || repeat('a', 200);

本地数据库配置

-- 安装扩展
CREATE EXTENSION postgres_fdw;

-- 创建外部服务器
CREATE SERVER postgres_fdw_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '10.0.3.236', dbname 'user01', port '5432');

-- 用户映射
CREATE USER MAPPING FOR CURRENT_USER
SERVER postgres_fdw_server
OPTIONS (user 'user01', password 'okuser01');

-- 创建外部表
CREATE FOREIGN TABLE fttest01 (
    id INT,
    note TEXT
) SERVER postgres_fdw_server
OPTIONS (table_name 'testtab01');

-- 查询
SELECT * FROM fttest01 LIMIT 2;
EXPLAIN SELECT * FROM fttest01 LIMIT 2;

oracle_fdw 使用实例

连接 Oracle 数据库。

-- 创建扩展
CREATE EXTENSION oracle_fdw;

-- 创建外部服务器
CREATE SERVER oradb
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//192.168.56.8:1521/orcl');

-- 用户映射
CREATE USER MAPPING FOR postgres
SERVER oradb
OPTIONS (user 'test', password 'cstech');

-- 创建外部表
CREATE FOREIGN TABLE "test_tab" (
    id INT,
    name TEXT
) SERVER oradb
OPTIONS (table 'TEST_TAB');

mysql_fdw 使用实例

连接 MySQL 数据库。

-- 创建扩展
CREATE EXTENSION mysql_fdw;

-- 创建外部服务器
CREATE SERVER mysql_fdw_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '10.0.3.236', port '3306');

-- 用户映射
CREATE USER MAPPING FOR CURRENT_USER
SERVER mysql_fdw_server
OPTIONS (username 'mysql_user', password 'mysql_pass');

-- 创建外部表
CREATE FOREIGN TABLE mysql_table (
    id INT,
    content TEXT
) SERVER mysql_fdw_server
OPTIONS (dbname 'testdb', table_name 'source_table');

总结