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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
