PostgreSQL Oracle兼容性之 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

2、PostgreSQL Oracle兼容性文档

digoal’s 大量PostgreSQL文章入口