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 PostgreSQL database.
An ETL "job" runs inside its own transaction and consists of a series of queries that transform the data from staging tables to the destination tables. If a failure occurs, the transaction rolls back so there's no "debris" left over -- which makes 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 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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 PostgreSQL database.
An ETL "job" runs inside its own transaction and consists of a series of queries that transform the data from staging tables to the destination tables. If a failure occurs, the transaction rolls back so there's no "debris" left over -- which makes 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 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?
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 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?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sat, Apr 5, 2014 at 6:41 AM, Jim Garrison <jim.garrison@nwea.org> wrote:
An ETL "job" runs inside its own transaction and consists of a series of
queries that transform the data from staging tables to the destination
tables. If a failure occurs, the transaction rolls back so there's no
"debris" left over -- which makes troubleshooting very difficult.
If you are loading huge amount of data then:
1) Committing every 10000 (or so) rows might make sense
2) Have you considered using COPY API in Postgres' JDBC?
3) Which version of PostgreSQL are you using? I guess 9.3 has a freeze
option which might help you. I am not sure if the API supports it.
Best Regards,
*Sameer Kumar | Database Consultant*
*ASHNIK PTE. LTD.*
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M: *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
*[image: icons]*
[image: Email patch] <http://www.ashnik.com/>
This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).