663
魔獸
PostgreSQL Oracle兼容性之 session_id
標簽
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兼容性文檔：
1、https://github.com/orafce/orafce
最後更新：2017-09-24 16:33:23