Connecting to an existing transaction state.

Alex Gen wrote:

Hello,

I’m in the process of creating a set of scripts for testing certain locking features in an application.
What I would like to do:
1. Start a connection from machine-01 through the m01-s1.sql script.
2.While (1) is running, start another transaction on the same database from machine-02 using m02-s1.sql.

At this point in time, there are two open transactions on certain tables in the same database.

3. Using m01-s2.sql I would like to execute a certain SQL statement – BUT within the scope of the transaction begun by m01-s1.sql.
4. Current situation: Since there are several .sql scripts, each getting its own connection and executing sql stmts – they are not aware of activities of the other scripts (i.e. the open transactions).
5. What I’d like to do: After a transaction has been started from a machine, I should be able to save the transaction reference (id?) temporarily somewhere.
6. The next statement (new .sql file) that wishes to execute within the scope of the above transaction – should be able to get the transaction reference (id) and latch onto it in its current state. This way it continues to perform as part of a whole – rather than only executing the statements that it had.

You cannot do this directly by simply using SQL files being fed to psql.
What you can do is open one psql process reading from a named pipe, and
write SQL commands to this pipe.

I was able to do this with some shell tricks; it read a single SQL file
which had commands prefixed by a "session number", and executed each
command in a different session by echoing them to the pipe attached to
each session. (You must have a separate process keeping the pipe open
for writing in between; otherwise psql gets a SIGPIPE and dies after the
first "echo" finishes).

I don't have the script around anymore though :-(

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

Alex Gen wrote:

Hello,

I’m in the process of creating a set of scripts for testing certain locking features in an application.
What I would like to do:
1. Start a connection from machine-01 through the m01-s1.sql script.
2.While (1) is running, start another transaction on the same database from machine-02 using m02-s1.sql.

At this point in time, there are two open transactions on certain tables in the same database.

3. Using m01-s2.sql I would like to execute a certain SQL statement – BUT within the scope of the transaction begun by m01-s1.sql.
4. Current situation: Since there are several .sql scripts, each getting its own connection and executing sql stmts – they are not aware of activities of the other scripts (i.e. the open transactions).
5. What I’d like to do: After a transaction has been started from a machine, I should be able to save the transaction reference (id?) temporarily somewhere.
6. The next statement (new .sql file) that wishes to execute within the scope of the above transaction – should be able to get the transaction reference (id) and latch onto it in its current state. This way it continues to perform as part of a whole – rather than only executing the statements that it had.

You cannot do this directly by simply using SQL files being fed to psql.
What you can do is open one psql process reading from a named pipe, and
write SQL commands to this pipe.

Yep. You can also write a file that sources the others, and use psql to
load that. Eg:

BEGIN;
\i m01-s1.sql
\i m01-s2.sql
COMMIT;

Another option is to write a wrapper program using one of the scripting
languages with PostgreSQL interfaces (Python, Perl, etc) that
establishes a connection then loads a sequence of snippets and sends
them. That's a little more flexible, but not much more complicated.

--
Craig Ringer