标签
PostgreSQL , session id
背景
数据库有会话的概念，用于区分每一个会话。会话ID是会话的唯一标识。
PostgreSQL的基于进程的模型，每个连接会分配一个backend process，因此使用process id也可以作为会话ID来使用。
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
110508
(1 row)
但是process id是会变的（在同一时间点唯一，但是会话退出后，其他会话新建的process 可能ID会与之前断开的会话的process id一致），实际上就是进程ID。
为了得到更真切的在整个时间跨度上全局唯一的session id，PostgreSQL是怎么做的呢？
启动时间+pid，得到 集群级 会话ID
同一时刻是不可能出现两个一样的process id的，因此使用“会话启动时间+PROCESS ID”可以作为一个集群唯一的session id。
实际上PostgreSQL的log中也是这么来表示session id的。
https://www.postgresql.org/docs/9.6/static/file-fdw.html
CREATE FOREIGN TABLE pglog (
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
) SERVER pglog
OPTIONS ( filename '/home/josh/9.1/data/pg_log/pglog.csv', format 'csv' );
LOG的例子
2017-09-20 00:05:41.191 CST,,,123569,,59c0b9c0.1e2b1,17,,2017-09-19 14:31:28 CST,,0,LOG,00000,"worker process: parallel worker for PID 116356 (PID 116569) exited with exit code 1",,,,,,,,,""
其中"59c0b9c0.1e2b1"就是session id。
对应的代码如下：
src/backend/utils/error/elog.c
/* session id */
appendStringInfo(&buf, "%lx.%x", (long) MyStartTime, MyProcPid);
我们可以通过这种方法，定义一个获取会话ID的函数
postgres=# create or replace function session_id(int default pg_backend_pid()) returns text as $$
select to_hex(extract('epoch' from backend_start)::int8)||'.'||to_hex(pid) from pg_stat_activity where pid=$1 limit 1;
$$ language sql strict;
CREATE FUNCTION
postgres=# select session_id();
session_id
----------------
59c4ffa6.1654d
(1 row)
注意以上方法依旧有可能在时间跨度上出现重复的SESSION ID，比如在同一秒（新建、断开过若干个连接），因为前面只精确到秒，所以还可能重复。
那么可以改成这样，就精确了。
postgres=# create or replace function session_id(int default pg_backend_pid()) returns text as $$
select extract('epoch' from backend_start)||'.'||to_hex(pid) from pg_stat_activity where pid=$1 limit 1;
$$ language sql strict;
CREATE FUNCTION
postgres=# select session_id();
session_id
------------------------
1506085350.16757.1afac
(1 row)
如果你希望得到一个INT类型的SESSION ID，可以使用如下方法。
sequence id，得到 库级 会话ID
例子：
postgres=# create sequence session_id_seq;
CREATE SEQUENCE
postgres=# \set VERBOSITY verbose
postgres=# select currval('session_id_seq');
ERROR: 55000: currval of sequence "session_id_seq" is not yet defined in this session
LOCATION: currval_oid, sequence.c:841
创建一个序列，获取序列值，作为SESSION ID。因为序列值绝对不会重复，因此是可行的方法。
create or replace function get_session_id() returns int8 as $$
declare
res int8;
begin
-- 老版本
select currval('pg_session_id_sequence_oracle_comp') into res;
return res;
exception
when sqlstate '55000' then
select nextval('pg_session_id_sequence_oracle_comp') into res;
return res;
when sqlstate '42P01' then
create sequence pg_session_id_sequence_oracle_comp;
select nextval('pg_session_id_sequence_oracle_comp') into res;
return res;
end;
$$ language plpgsql strict set client_min_messages to error;
或
create or replace function get_session_id() returns int8 as $$
declare
res int8;
begin
-- 新版本
create sequence IF NOT EXISTS pg_session_id_sequence_oracle_comp;
select currval('pg_session_id_sequence_oracle_comp') into res;
return res;
exception when sqlstate '55000' then
create sequence IF NOT EXISTS pg_session_id_sequence_oracle_comp;
select nextval('pg_session_id_sequence_oracle_comp') into res;
return res;
end;
$$ language plpgsql strict set client_min_messages to error;
如下
postgres=# select get_session_id();
get_session_id
----------------
5
(1 row)
这种方法获得的session id，是库级唯一的session id，如果你需要获取整个集群唯一的SESSION ID，请使用第一种方法。
参考
PostgreSQL的其他Oracle兼容性文档：
