Обсуждение: Connecting to an existing transaction state.
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 – theyare 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 toget the transaction reference (id) and latch onto it in its current state. This way it continues to perform as part ofa whole – rather than only executing the statements that it had. Any guidance in this will help. Cheers! AlexiG __________________________________________________________ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html
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 bym01-s1.sql. > 4. Current situation: Since there are several .sql scripts, each getting its own connection and executing sql stmts – theyare 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 ableto get the transaction reference (id) and latch onto it in its current state. This way it continues to perform as partof 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 bym01-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 transactionreference (id?) temporarily somewhere. >> 6. The next statement (new .sql file) that wishes to execute within the scope of the above transaction – should be ableto get the transaction reference (id) and latch onto it in its current state. This way it continues to perform as partof 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