Início / dba / Perguntas / 13518
Accepted
tscho
Asked: 2012-02-18 12:55:14 +0800 CST

Amplie uma única transação em várias sessões psql sequenciais

  • 772

Estou executando o servidor de banco de dados PostgreSQL 8.4 e o front-end do terminal psql no Ubuntu 10.04 Lucid Lynx e gostaria de abranger uma única transação em várias sessões psql sequenciais.

Quando eu me conecto ao meu banco de dados com o psql, uma nova conexão é estabelecida e um processo de back-end do servidor para essa conexão é criado. Quando desconecto, a conexão é liberada e o processo de back-end é encerrado. Uma transação (não-XA*) está vinculada ao escopo de uma conexão, portanto, obviamente, não há uma maneira direta de abranger uma única transação em várias sessões do psql.

O que eu gostaria de conseguir é que a seguinte sequência de comandos possa ser executada em uma única transação e, portanto, retornar o mesmo registro de data e hora da transação em cada chamada de 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

registro do banco de dados:

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]

Claramente, isso não é o que eu realmente quero fazer. Quero poder executar vários scripts bash que se conectam ao banco de dados e executar instruções e scripts SQL com psql em uma única transação.

* Embora o protocolo XA permita BEGIN TRANSACTION e PREPARE TRANSACTION em conexões diferentes, mas o PostgreSQL não oferece suporte a isso.

Minha primeira tentativa de resolver esse problema foi configurar o pool de conexões do PgBouncer 1.5 e configurá-lo como um proxy simples com exatamente uma conexão com o banco de dados de destino (modo de pooling de sessão). Meu raciocínio era que o PgBouncer estabeleceria essa conexão na inicialização e que eu poderia conectar/desconectar de/para o proxy com psql enquanto a conexão com o banco de dados permanece aberta.

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

Na verdade, isso funciona muito bem, como mostra o log do banco de dados:

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

Mas há um pequeno problema com essa abordagem, assim que eu começo uma transação na conexão do proxy e desconecto...

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

...a conexão é liberada pelo 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]

Claro que isso faz todo o sentido para um pool de conexão. Sua função é fornecer (uma) conexão(ões) compartilhada(s) para vários clientes, mas isolar as transações desses clientes. Mas para o meu caso de uso, uma transação compartilhada é exatamente o que eu precisaria...

Então, minha pergunta agora é: existe uma maneira de configurar o PgBouncer (ou outro pool de conexão) para não liberar a conexão após a desconexão após BEGIN/START TRANSACTION ou existe outra maneira de conseguir o que eu gostaria de fazer?

Todas as perguntas adicionais a este post, comentários e, claro, respostas apreciadas!

postgresql psql

2 respostas

  1. Best Answer

    Talvez seja mais fácil executar um psql em segundo plano, com ele configurado para executar stdin e conectar seu stdin a um pipe nomeado. Em seguida, você pode enviar dados continuamente para esse canal e, finalmente, enviar "end; \ quit". Algo como:

    #!/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

    Observe que você não pode simplesmente fazer isso, echo "sql" >$psql_pipepois o EOF seria transmitido para o psql, que sairia mais cedo - o script de shell deve manter seu fd aberto.

    • 6

  2. Eu costumo lidar com isso de maneira um pouco diferente: eu coleto os arquivos/snippets SQL que desejo executar e, em seguida, executo uma única sessão psql que emite um BEGIN, em seguida, uso \i para incluir cada arquivo por vez, finalmente emitindo um COMMIT. Por exemplo (não testado, mas a ideia geral):

    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

    Editar: Como alternativa, se o seu script e a sessão do PostgreSQL precisarem de interação bidirecional, digamos, se você estiver gerando sql dinâmico, poderá usar um coprocesso ou usar uma linguagem de script mais sofisticada do que sh que possui seu próprio integrado Interface do PostgreSQL. Veja esta resposta que escrevi para uma pergunta muito semelhante há algum tempo.

    Eu uso Python e psycopg para esse tipo de coisa na maioria das vezes, mas um coprocesso pode ser útil se você estiver preso ao bash.

    Se você estiver no Windows e estiver usando cmd.exe, (a) desculpe e (b) você terá que usar uma linguagem de script real ou, se o PowerShell oferecer suporte a algo como coprocessos, talvez use o PowerShell e o psql.

    • 5

relate perguntas