一、什么是咨询锁¶

1.1 简介¶

PostgreSQL允许创建由应用定义其含义与数据库本身没有关系的锁，这种锁被称为咨询锁，英文为Advisory Lock。

“select * from table where pid= <id> for update”也可以实现相同的锁功能有什么区别这是因为咨询锁与具体的数据没有关系，可以提供更好的性能

1.2 使用场景¶

在很多应用中，多个进程访问同一个数据库，这时如果想协调这些进程对一些非数据库资源的并发访问，就可以使用这种咨询锁。

1.3 咨询锁的函数及使用¶

咨询锁用一个64bit的数字或两个32bit的数字来表示，并提供了一些函数来实现加锁和释放锁的操作

1.4 咨询锁的函数¶

名称中有“lock”的是加锁函数，有“unlock”的是释放锁函数
名称中有“xact”的是事务级别的咨询锁，而名称中没有“xact”的是session级别的咨询锁
函数名称中有“try”的表示试图加锁，这样的函数不管是否能获得锁，都立即返回，
事务级别的咨询锁与session级别的咨询锁，实际上指向同一把锁，也就是说，当事务级别锁住一把锁时，试图获得session级别的这

把锁也会被阻塞

session1
获得session级别的锁（key=1）
select pg_advisory_lock(1);

session2
begin;
select pg_advisory_xact_lock(1);

结论：想获得一个事务级别的锁（key也是1）时，会发现被session锁阻塞住了，sesion1 释放锁之后 session2 获取到锁

select pg_advisory_unlock(1);
再重新获取时会被柱塞

案例场景：

session1

CREATE TABLE test (id INT PRIMARY KEY);
INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (2);
begin;
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1);
commit;

session2

-- session1 未提交前执行
begin;
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1);
commit;

session2 会报错

解决办法：

select pg_try_advisory_xact_lock(1);
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1);
end;

问题：

当连接中断后，数据库中持有的咨询锁是否会释

-- 会的。当连接中断后，数据库的会话就会被中止，其持有的咨询锁也会被释放

当事务回滚或提交后，持有的session级别的咨询锁是否会被释放？

-- 不会。session级别的咨询锁与事务没有任何关系

如果第一个session持有一把事务级别的key为1的锁，另一个session能否同时持有一把session级别的key 为1的锁

-- 不能。因为不管是session级别还是事务级别的，它们都代表同一把锁

二、SQL/MED¶

2.1 介绍¶

SQL/MED是SQL语言中管理外部数据的一个扩展标准通过SQL/MED可以连接到各种异构数据库或其他PostgreSQL数据库无 sql/med框架

SQL/MED框架

使用SQL/MED后，PostgreSQL通过SQL/MED 访问其他各种异构数据库或其他PostgreSQL数据库，应用程序只需要与这一台PostgreSQL数据库交互

2.2 创建步骤¶

2.2.1 外部数据包装器对象创建¶

创建FDW时需要指定一个函数，该函数定义了PostgreSQL数据库如何从外部数据源取得数据 CREATE EXTENSION命令会自动创建外部数据包装器比如：

create extension file_fdw;

CREATE EXTENSION postgres_fdw;

2.2.2 创建外部服务器对象¶

Server对象是把FDW与连接外部数据源的连接参数关联起来的对象，主要定义如何连接外部数据源。创建Server对象的语法格式如下

创建一个指向另一台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;

2.2.3 创建用户映射对象¶

用户映射主要解决PostgreSQL用户与外部服务器的用户之间的映射关系

#用户“user01”是本地数据库中的一个用户，而用户“user02”则是远程数据库中的用户
CREATE USER MAPPING FOR user01 SERVER postgres_fdw_server OPTIONS ( user 'user02', password 'okuser02')

2.2.4 外部表对象¶

SQL/MED就是把外部数据源中的数据对象映射成一张外部表，然后就可以像访问普通表一样访问这张外部表了

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

2.3 file_fdw使用实例¶

file_fdw插件为PostgreSQL数据库提供了访问外部文件数据的能力。该插件是内置在PostgreSQL的源码的 contri中的，使用这个包可以很方便地把外部文本文件映射成一张外部表

案例：把Linux下的/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的日志映射到postgresql外部表中

需要设置：

log_destination = 'csvlog'
logging_collector = on

2.4 postgres_fdw使用实例¶

访问服务其他PostgreSQL数据库的外部数据包装器，它提供了与原先已有的dblink模块相同的功能，但使用 postgres_fdw更符合SQL标准，在某些场景下比dblink有更好的性能

案例

远程数据库中建测试表“testtab01”

create table testtab01(id int, note text); insert into testtab01 select generate_series(1,10000), 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

在本地数据库中安装postgres_fdw插件，建外部数据表

CREATE EXTENSION postgres_fdw;

如果报错需要按照报

yum install -y postgresql15-contrib

建外部数据服务器

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;

2.5 oracle_fdw使用实例¶

oracle_fdw是一个PostgreSQL扩展，它提供了一个外部数据包装器，可以轻松高效地访问Oracle数据库，包括WHERE的下推条件和所需列以及全面的EXPLAIN支持

案例：

创建外部表扩展
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 osdba_fdw OPTIONS (user 'test', password 'cstech');

建映射表：
CREATE FOREIGN TABLE "test_tab" (id int,name text SERVER osdba_fdw OPTIONS (table 'TEST_TAB');

2.5.1 odbc_fdw¶

这个PostgreSQL扩展实现了一个外部数据包装器，使用开放式数据库连接（ODBC）的远程数据库，是专门用来访问SQL-Sever数据库中数据的

2.5.2 mysql_fdw¶

mysql_fdw是一个PostgreSQL扩展，它提供了一个外部数据包装器的远程数据库，是专门用来访问mysql数据库中数据的

PostgreSQL 咨询锁与 SQL MED 实战