Hologres：GUC參數

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class HologresQueryIdDemo {

    // 匹配NOTICE中的 "QueryID: <QUERY_ID>"
    private static final Pattern QUERY_ID_PATTERN = Pattern.compile(
            "query[_ ]?id\\s*(?:is\\b|[=:])?\\s*([0-9a-zA-Z_\\-]{6,})", Pattern.CASE_INSENSITIVE);

    public static void main(String[] args) throws Exception {
        // Endpoint請選擇與運行環境匹配的網路地址（公網或VPC），在控制台的執行個體詳情頁擷取
        String url = "jdbc:postgresql://<ENDPOINT>:80/<DB_NAME>";
        String user = "<ACCESS_KEY_ID>";
        String password = "<ACCESS_KEY_SECRET>";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement()) {

            // 開啟Query ID回顯，該參數預設為off且僅在當前session生效
            stmt.execute("set hg_experimental_display_query_id = on;");

            String sql = "select count(*), sum(id) from holo_query_id_demo;";

            // 執行前清空上一條SQL殘留的Warning，確保Query ID與SQL一一對應
            stmt.clearWarnings();
            boolean hasResultSet = stmt.execute(sql);

            // 讀取結果集，Query ID不在結果集中
            if (hasResultSet) {
                try (ResultSet rs = stmt.getResultSet()) {
                    while (rs.next()) {
                        System.out.println("rows      : (" + rs.getLong(1) + ", " + rs.getLong(2) + ")");
                    }
                }
            }

            // NOTICE以SQLWarning鏈的形式返回，遍曆並解析出Query ID
            String queryId = null;
            List<String> notices = new ArrayList<>();
            for (SQLWarning w = stmt.getWarnings(); w != null; w = w.getNextWarning()) {
                notices.add(w.getMessage());
                Matcher m = QUERY_ID_PATTERN.matcher(w.getMessage());
                if (m.find()) {
                    queryId = m.group(1);
                }
            }

            System.out.println("query_id  : " + queryId);
            System.out.println("notices   : " + notices);
        }
    }
}

rows      : (1000, 500500)
query_id  : 1002002606817139331
notices   : [One or more columns in the following table(s) do not have statistics: holo_query_id_demo, QueryID: 1002002606817139331]

import re
import psycopg

NOTICES = []
QUERY_IDS = []

QUERY_ID_PATTERN = re.compile(
    r"query[_ ]?id\s*(?:is\b|[=:])?\s*([0-9a-zA-Z_\-]{6,})", re.IGNORECASE
)

def notice_handler(diag):
    """服務端每發送一條NOTICE都會回調一次，從中解析Query ID。"""
    text = diag.message_primary or ""
    NOTICES.append(text)
    match = QUERY_ID_PATTERN.search(text)
    if match:
        QUERY_IDS.append(match.group(1))

conn = psycopg.connect(
    host="<ENDPOINT>",          # 在控制台的執行個體詳情頁擷取
    port=80,
    dbname="<DB_NAME>",
    user="<ACCESS_KEY_ID>",
    password="<ACCESS_KEY_SECRET>",
)
conn.autocommit = True
conn.add_notice_handler(notice_handler)   # 註冊NOTICE回調

cur = conn.cursor()
# 開啟Query ID回顯，該參數預設為off且僅在當前session生效
cur.execute("set hg_experimental_display_query_id = on;")

def run_sql(sql, params=None, fetch=True):
    """執行SQL並返回 (結果集, Query ID, 原始NOTICE列表)。無結果集時結果集為None。"""
    NOTICES.clear()
    QUERY_IDS.clear()
    cur.execute(sql, params)
    rows = None
    if fetch and cur.description is not None:
        rows = cur.fetchall()
    query_id = QUERY_IDS[-1] if QUERY_IDS else None
    return rows, query_id, list(NOTICES)

rows, query_id, notices = run_sql("select count(*) from holo_query_id_demo;")
print("rows:", rows)
print("query_id:", query_id)

SQL       : insert into holo_query_id_demo select i, 'v' || i from generate_series(1, 1000) i;
query_id  : 1002002606817130830
notices   : ['QueryID: 1002002606817130830']

SQL       : select count(*), sum(id) from holo_query_id_demo;
query_id  : 1002002606817139331
rows      : [(1000, 500500)]
notices   : ['One or more columns in the following table(s) do not have statistics: holo_query_id_demo', 'QueryID: 1002002606817139331']

# 需先執行 pip install psycopg_pool
from psycopg_pool import ConnectionPool

def configure(conn):
    conn.autocommit = True
    conn.add_notice_handler(notice_handler)
    conn.execute("set hg_experimental_display_query_id = on;")

pool = ConnectionPool(kwargs=CONN_INFO, configure=configure, min_size=1, max_size=4)

with pool.connection() as conn:
    conn.execute("select 1;").fetchall()

// HikariCP：connectionInitSql在每條物理串連建立時執行
HikariConfig config = new HikariConfig();
config.setJdbcUrl(url);
config.setUsername(user);
config.setPassword(password);
config.setConnectionInitSql("set hg_experimental_display_query_id = on;");
HikariDataSource dataSource = new HikariDataSource(config);

// Druid：connectionInitSqls支援配置多條初始化SQL
DruidDataSource druid = new DruidDataSource();
druid.setUrl(url);
druid.setUsername(user);
druid.setPassword(password);
druid.setConnectionInitSqls(Collections.singletonList("set hg_experimental_display_query_id = on;"));