Re: Connecting to an existing transaction state.

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