主页 / dba / 问题 / 13518
Accepted
tscho
Asked: 2012-02-18 12:55:14 +0800 CST

跨越多个连续 psql 会话的单个事务

  • 772

我在Ubuntu 10.04 Lucid Lynx 上运行PostgreSQL 8.4数据库服务器和psql终端前端，并希望将单个事务跨越多个顺序 psql 会话。

当我使用 psql 连接到我的数据库时，会建立一个新连接并为此连接创建一个服务器后端进程。当我断开连接时，连接被释放并且后端进程终止。（非 XA*）事务绑定到连接范围，因此显然没有直接的方法可以跨多个 psql 会话跨越单个事务。

我想要实现的是以下命令序列可以在单个事务中运行，因此在每次调用时返回相同的事务时间戳now()：

tscho@test:~$ sudo -u postgres psql -p 5433 --no-align --tuples-only -c "select now()"
2012-02-17 21:25:07.027056+01
tscho@test:~$ sudo -u postgres psql -p 5433 --no-align --tuples-only -c "select now()"
2012-02-17 21:25:09.487601+01

数据库日志：

2012-02-17 21:25:07 CET 0- LOG:  connection received: host=[local]
2012-02-17 21:25:07 CET 0- LOG:  connection authorized: user=postgres database=postgres
2012-02-17 21:25:07 CET 0-2/0 LOG:  duration: 0.366 ms  statement: select now()
2012-02-17 21:25:07 CET 0- LOG:  disconnection: session time: 0:00:00.002 user=postgres database=postgres host=[local]
2012-02-17 21:25:09 CET 0- LOG:  connection received: host=[local]
2012-02-17 21:25:09 CET 0- LOG:  connection authorized: user=postgres database=postgres
2012-02-17 21:25:09 CET 0-2/0 LOG:  duration: 0.347 ms  statement: select now()
2012-02-17 21:25:09 CET 0- LOG:  disconnection: session time: 0:00:00.002 user=postgres database=postgres host=[local]

显然这不是我真正想做的。我希望能够执行几个连接到数据库的 bash 脚本，并在单个事务中使用 psql 执行 SQL 语句和脚本。

* Afaik XA 协议将允许 BEGIN TRANSACTION 和 PREPARE TRANSACTION 在不同的连接上，但 PostgreSQL 不支持这一点。

我解决这个问题的第一个方法是设置PgBouncer 1.5连接池并将其配置为一个简单的代理，与目标数据库只有一个连接（会话池模式）。我的理由是 PgBouncer 会在启动时建立这个连接，然后我可以在与数据库的连接保持打开的同时使用 psql 连接/断开代理。

tscho@test:~$ sudo -u postgres psql pgproxy -U pgbouncer -p 6432 --no-align --tuples-only -c "select now()"
2012-02-17 21:25:23.517019+01
tscho@test:~$ sudo -u postgres psql pgproxy -U pgbouncer -p 6432 --no-align --tuples-only -c "select now()"
2012-02-17 21:25:26.943172+01

正如数据库日志显示的那样，这实际上效果很好：

2012-02-17 21:25:17 CET 0- LOG:  connection received: host=[local]
2012-02-17 21:25:17 CET 0- LOG:  connection authorized: user=postgres database=postgres
2012-02-17 21:25:23 CET 0-1/0 LOG:  duration: 0.110 ms  statement: select 1
2012-02-17 21:25:23 CET 0-1/0 LOG:  duration: 0.053 ms  statement: select now()
2012-02-17 21:25:23 CET 0-1/0 LOG:  duration: 0.046 ms  statement: DISCARD ALL
2012-02-17 21:25:26 CET 0-1/0 LOG:  duration: 0.126 ms  statement: select now()
2012-02-17 21:25:26 CET 0-1/0 LOG:  duration: 0.043 ms  statement: DISCARD ALL

但是这种方法有一个小问题，只要我在代理连接上开始事务并断开连接......

tscho@test:~$ sudo -u postgres psql pgproxy -U pgbouncer -p 6432 --no-align --tuples-only -c "start transaction"
START TRANSACTION

...连接由 PgBouncer 释放。

2012-02-17 21:32:47 CET 0-1/2178 LOG:  duration: 0.025 ms  statement: start transaction
2012-02-17 21:32:47 CET 0- LOG:  disconnection: session time: 0:07:20.737 user=postgres database=postgres host=[local]

当然，这对于连接池来说非常有意义。它的工作是为多个客户端提供（a）共享连接，但隔离这些客户端的事务。但是对于我的用例，共享事务正是我所需要的......

所以我现在的问题是，有没有办法配置 PgBouncer（或另一个连接池）在 BEGIN/START TRANSACTION 后断开连接时不释放连接，或者有另一种方法来实现我想做的事情吗？

对这篇文章的所有其他问题、评论和答案当然表示赞赏！

postgresql psql

2 个回答

  1. Best Answer

    也许在后台运行 psql 是最简单的，它设置为执行标准输入，并将其标准输入连接到命名管道。然后您可以不断地将数据推送到该管道中，最后推送“end; \quit”。就像是：

    #!/bin/sh

psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!

echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
  kill $psql_pid
  rm -f $psql_pipe
' EXIT

echo "begin;" >&3

echo "select now();" >&3

sleep 2

echo "select now();" >&3

sleep 2

echo "end; \quit" >&3

wait $psql_pid

    请注意，您不能简单地这样做，echo "sql" >$psql_pipe因为 EOF 将被传输到 psql，然后它会提前退出——shell 脚本必须保持其 fd 处于打开状态。

    • 6

  2. 我通常以不同的方式解决这个问题：我收集我想要运行的 SQL 文件/片段，然后执行一个发出 BEGIN 的单个 psql 会话，然后使用 \i 依次包含每个文件，最后发出一个 COMMIT。例如（未经测试，但总体思路）：

    psql -1 <<__END__
\i script1.sql
\i script2.sql
\i script3.sql
__END__

errstatus=$?
if ! $errstatus; then
  echo "psql failed with $errstatus"
  exit 1
fi

    编辑：或者，如果您的脚本和 PostgreSQL 会话需要双向交互，例如，如果您正在生成动态 sql，那么您可以使用协同进程或使用比 sh 更复杂的脚本语言，它有自己的内置PostgreSQL 接口。请参阅我不久前写给一个非常相似的问题的答案。

    我大部分时间都使用 Python 和 psycopg 来处理这类事情，但如果你被 bash 困住，协进程会很有用。

    如果您在 Windows 上使用，cmd.exe那么 (a) 对不起， (b) 您将不得不使用真正的脚本语言，或者，如果 PowerShell 支持诸如协同进程之类的任何东西，则可能使用 PowerShell 和 psql。

    • 5

相关问题