Обсуждение: Remote troubleshooting session connection?

I'm working on an ETL system that is driven from Java/JDBC but is implemented mostly as SQL queries against a
PostgreSQLdatabase.   

An ETL "job" runs inside its own transaction and consists of a series of queries that transform the data from staging
tablesto the destination tables.  If a failure occurs, the transaction rolls back so there's no "debris" left over --
whichmakes troubleshooting very difficult.   

In the Java world we have JPDA, which allows us to connect to a running JVM to examine the state of variables and set
breakpoints,etc. 

Assuming I can pause the driving Java code between queries in a job, is there any way to connect from PGAdmin (or
anothertool) and view the state of tables in the in-progress transaction?  If this is not currently possible, how
difficultwould it be to do? 

On 04/04/2014 03:41 PM, Jim Garrison wrote:
> I'm working on an ETL system that is driven from Java/JDBC but is implemented mostly as SQL queries against a
PostgreSQLdatabase. 
>
> An ETL "job" runs inside its own transaction and consists of a series of queries that transform the data from staging
tablesto the destination tables.  If a failure occurs, the transaction rolls back so there's no "debris" left over --
whichmakes troubleshooting very difficult. 

Why not crank up the logging in postgresql.conf and then look at the
postgres logs?

>
> In the Java world we have JPDA, which allows us to connect to a running JVM to examine the state of variables and set
breakpoints,etc. 
>
> Assuming I can pause the driving Java code between queries in a job, is there any way to connect from PGAdmin (or
anothertool) and view the state of tables in the in-progress transaction?  If this is not currently possible, how
difficultwould it be to do? 
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 4/4/2014 3:41 PM, Jim Garrison wrote:
> Assuming I can pause the driving Java code between queries in a job, is there any way to connect from PGAdmin (or
anothertool) and view the state of tables in the in-progress transaction?  If this is not currently possible, how
difficultwould it be to do? 

very difficult, as that data only exists in the context of the socket
the JDBC connection is using.

you maybe could do that with a JDBC tool that could 'pirate' on the same
JDBC connection your app is using, obviously when its not already in a
call.    perhaps add a telnet or whatever UI to instrument your java ETL
tool to allow you to pause and run manual SQL queries on the same interface?



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Jim Garrison wrote
> Assuming I can pause the driving Java code between queries in a job, is
> there any way to connect from PGAdmin (or another tool) and view the state
> of tables in the in-progress transaction?  If this is not currently
> possible, how difficult would it be to do?

What you want is "dirty read" semantics which none of the implemented
transaction isolation levels permit - so no you cannot go make an
uncommitted change and read the results from another session.

http://www.postgresql.org/docs/9.1/static/transaction-iso.html

That said I thought there was a project out there, probably related to data
recovery, that allows one to examine the system without respecting any
transactional boundaries.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Remote-troubleshooting-session-connection-tp5798810p5798823.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.