使用pgrx开发postgre插件
pg插件开发建议在linux环境下进行, windows可以采用虚拟机的方式开发.
安装虚拟机环境
- 准备虚拟机, 使用的是ubuntu22.04, 太新的版本会依赖拉取有问题
- 安装开发工具和依赖:
sudo apt-get update sudo apt-get install -y git clang gcc make build-essential libz-dev zlib1g-dev strace libssl-dev pkg-config libreadline-dev libpg-dev flex bison libxml2-dev libxslt-dev libxml2-utils xsltproc - 安装rust:
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh - 安装cargo:
sudo apt-get install cargo - 安装pgrx:
cargo install cargo-pgrx - 初始化pgrx:
cargo pgrx init 这会安装12 - 17版本的pg.
耐心等待所有步骤完成后, 就可以使用pgrx新建模板来写插件了
hello pgrx
执行命令新建pg扩展项目: cargo pgrx new <project_name>
使用生成的项目模板进行插件开发
调试
如果有需要, 可以修改自己要用的pg版本配置文件来方便调试, 例如要用pg16就改.pgrx/data-16
- pg_hba.conf和pg_ident.conf 支持外部工具连接pg
- postgresql.conf设置日志输出级别
修改log_min_messages和client_min_messages
运行默认模板插件的函数
进入pgrx新建的项目目录后, 在控制台执行cargo pgrx run pg16表示在postgre16版本运行该插件.
默认端口起始是28800, 因此pg16开放的端口是28816(pg17就是28817),如果开放允许外部连接可以使用这个端口进去(pgrx run命令会自动接入到pg控制台模式)
执行sql安装pg扩展:
create extension <project_name>;
SELECT extname AS "Extension Name", extversion AS "Version"
FROM pg_extension;
<project_name>的来源是自己建立的项目名称.
调试用的宏
例: use pgrx::{debug1, debug5}
按照pg级别使用, 有:
- debug5!
- debug4!
- debug3!
- debug2!
- debug1!
- info!
- notice!
- warning!
- error!
- log!
- FATAL!
- PANIC!
自定义类型
see: 自定义类型官方示例
自定义类型需要实现序列化和反序列化:
#[derive(PostgresType, Deserialize, Serialize)]
pub struct MyType {}
重载运算符
see: 重载运算符官方示例
#[pg_operator(immutable, parallel_safe)]
#[opname(||)]
fn mytype_concact(mut left: MyType, right: MyType) -> MyType {
left.0.push_str(&right.0);
left
}
处理panic
官方提供了一个叫做PgTryBuilder的实现, 用来捕获rust中的panic.
#[pg_extern]
fn maybe_panic(panic: bool, trap_it: bool, message: &str) {
PgTryBuilder::new(|| {
if panic {
panic!("panic says: {}", message)
// std::panic::panic_any(42)
}
})
.catch_rust_panic(|cause| {
// we can catch general Rust panics. The `error` argument is a pg_sys::panic::CaughtError::RustPanic
// enum variant with the payload from the originating panic
if trap_it {
if let CaughtError::RustPanic { ereport, payload } = &cause {
warning!("{:#?}", ereport);
if let Some(s) = payload.downcast_ref::<String>() {
// we have access to the panic!() message
warning!("{}", s);
return;
} else {
// this won't happen with this example, but say the `panic_any(42)` was used
// instead. Then we'd be here, and we can just raise another `panic!()`, which
// will be what's ultimately reported to Postgres.
//
// In this case, Postgres' LOCATION error slot will be this line, and the CONTEXT
// slot will show the line number of the original `panic_any(42)` above
panic!("panic payload not a `String`");
}
}
unreachable!("internal error: `CaughtError` not a `::RustPanic`");
} else {
cause.rethrow()
}
})
// finally block always runs after the catch handlers finish (even if they rethrow or raise
// their own panic, like in this case)
.finally(|| warning!("FINALLY!"))
.execute()
}
在插件中执行SQL
see: 官方spi示例
see: hook列表(没有试过)
执行SQL需要用到pgrx提供的spi接口
这是一个在hook中执行sql的示例:
#[pg_guard]
pub unsafe extern "C" fn _PG_init() {
PgTryBuilder::new(|| {
let vec = generate_rsa_key().unwrap();
Spi::connect(move |mut client| {
let init_schema = format!(
r#"
create schema if not exists {};
"#,
PROJECT_SCHEMA
);
match client.update(init_schema.as_str(), None, None) {
Ok(_) => {}
Err(_) => {
return;
}
};
let init_sql = format!(
r#"
create table if not exists {}.t_rsa_key(
id serial8 not null primary key,
public_key text,
private_key text
);
"#,
PROJECT_SCHEMA
);
client.update(init_sql.as_str(), None, None).unwrap();
let insert_sql = format!(
r#"
insert into {}.t_rsa_key (public_key,private_key) values ('{}','{}');
"#,
PROJECT_SCHEMA, vec[0], vec[1]
);
client.update(insert_sql.as_str(), None, None).unwrap();
})
})
.execute();
}
关于_PG_init被调用多次的解决方式
https://stackoverflow.com/questions/28037300/in-postgresql-why-is-pg-init-called-twice
https://github.com/pgcentralfoundation/pgrx/blob/develop/pgrx-examples/shmem/README.md
(TODO: 其实我也没去处理这个问题)
