Обсуждение: Connection.setReadOnly()
does Connection.setReadOnly(true) block pgjdbc from automatically issuing BEGIN;, similar to setAutoCommit(true) ? if not, why not?
On Thu, 10 Dec 2009, John R Pierce wrote: > does Connection.setReadOnly(true) block pgjdbc from automatically issuing > BEGIN;, similar to setAutoCommit(true) ? No. > if not, why not? > Just because your connection is readonly doesn't mean that everyone elses is as well. If you want a consistent view of the database you can get that with a serializable transaction. Kris Jurka
Kris Jurka wrote: > Just because your connection is readonly doesn't mean that everyone > elses is as well. If you want a consistent view of the database you > can get that with a serializable transaction. my oracle developers are screaming bloody murder over this. they have processes that do nothing but reads. jdbc tosses a BEGIN; out automatically. these processes never Commit(); on a read, why should they? so.... we have a fairly large database thats fed a constant stream of realtime data 24/7 from a factory doing both inserts and updates... many different sorts of things have to query this database, including mechanical/electrical engineering types doing adhoc queries for oddball reports and such who are not SQL experts. its way to easy for them to accidentally create a transaction that they never end, causing multiday blockage of VACUUM, massive table bloat, etc. per all my Oracle people this is totally unacceptable and they are ready to cancel any further postgres projects.
On Thu, 10 Dec 2009, John R Pierce wrote: > Kris Jurka wrote: >> Just because your connection is readonly doesn't mean that everyone elses >> is as well. If you want a consistent view of the database you can get that >> with a serializable transaction. > > my oracle developers are screaming bloody murder over this. > > they have processes that do nothing but reads. jdbc tosses a BEGIN; out > automatically. these processes never Commit(); on a read, why should they? So if they go to the trouble of doing a setReadOnly call, why not simply do a setAutoCommit(true) call as well? Kris Jurka
Kris Jurka wrote: > So if they go to the trouble of doing a setReadOnly call, why not > simply do a setAutoCommit(true) call as well? thats pretty much what they'll have to do. I guess setReadOnly() is pretty much a non-op in pg-jdbc? we're having such a problem with Idle in Transaction on this database that I'm looking into writing a process killer that queries pg_stat_activity every 10 minutes and does a pg_terminate_backend() on PIDs older than some arbitrary threshold, maybe an hour. we're still fixing oraclisms in the main application. the developers wanted a mutex on the various 'back end' processes, so they have a thread that connects, and did a lock on a row in a table (one row for each named app that has to be a 1-of), then leave that transaction going.... This works perfectly well on Oracle. On postgresql, 4-5 days later, its painful to see how much bloat is in this 70GB database, and in fact, the autovacuum processes start stacking up on each other and primariy application response time starts shooting up slower and slower til the factory is slowing down, not good.... I've shown them how to use pg_try_advisory_lock() to do the same thing.
John R Pierce wrote: > Kris Jurka wrote: >> Just because your connection is readonly doesn't mean that everyone >> elses is as well. If you want a consistent view of the database you >> can get that with a serializable transaction. > > my oracle developers are screaming bloody murder over this. > > they have processes that do nothing but reads. jdbc tosses a BEGIN; > out automatically. these processes never Commit(); on a read, why > should they? Because if they're operating on a connection with autocommit off, the JDBC spec is very specific about transaction boundaries, and it would actually be incorrect for the driver *not* to start a transaction. I have at least one application here that runs readonly queries inside a transaction specifically because it wants a consistent view. Does the Oracle driver not follow the JDBC spec here? Or is it just that long-term open transactions don't cause problems on Oracle? If your oracle developers don't want manually committed transactions, perhaps they should.. turn on autocommit. > per all my Oracle people this is totally unacceptable and they are ready to cancel any further postgres projects. It's somewhat telling that they identify themselves as "Oracle people". Postgres isn't identical to Oracle; if you want applications that are portable between RDBMSes you need to take some care, and presumably they didn't do this originally and are now paying the price. -O
John R Pierce wrote: > Kris Jurka wrote: >> So if they go to the trouble of doing a setReadOnly call, why not >> simply do a setAutoCommit(true) call as well? > > thats pretty much what they'll have to do. I guess setReadOnly() is > pretty much a non-op in pg-jdbc? It issues a "SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY" command. See http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java?rev=1.54&content-type=text/x-cvsweb-markup -O
Oliver Jowett wrote: > Because if they're operating on a connection with autocommit off, the > JDBC spec is very specific about transaction boundaries, and it would > actually be incorrect for the driver *not* to start a transaction. I > have at least one application here that runs readonly queries inside a > transaction specifically because it wants a consistent view. > > Does the Oracle driver not follow the JDBC spec here? Or is it just that > long-term open transactions don't cause problems on Oracle? > the latter. long term transactions are not a problem for Oracle. As I said, they've been using an open transaction with a row lock as a MUTEX for an app that runs for weeks or even months. > If your oracle developers don't want manually committed transactions, > perhaps they should.. turn on autocommit. > otherwise, an app thats doing purely read queries has to periodically issue a Commit(). I have to say, doing Commit() on read operations is NOT at all intuitive. > It's somewhat telling that they identify themselves as "Oracle people". > Postgres isn't identical to Oracle; if you want applications that are > portable between RDBMSes you need to take some care, and presumably they > didn't do this originally and are now paying the price. > These two SQL developers have been working on this same family of manufacturing databases in oracle for like 12-15 years. Its me thats calling them 'oracle people' :)
John R Pierce wrote: > Oliver Jowett wrote: >> If your oracle developers don't want manually committed transactions, >> perhaps they should.. turn on autocommit. >> > > otherwise, an app thats doing purely read queries has to periodically > issue a Commit(). I have to say, doing Commit() on read operations is > NOT at all intuitive. Maybe not, but it's a fairly well known quirk of Postgresql that long-running transactions don't work so well; the JDBC driver is no different to any other interface in this regard. If you don't care about transaction boundaries, why are you even turning off autocommit in the first place? It's a little unfair to explicitly turn off autocommit, then complain that you need to manually commit transactions! -O
John R Pierce wrote: > we have a fairly large database thats fed a > constant stream of realtime data 24/7 from a factory doing both inserts > and updates... many different sorts of things have to query this > database, including mechanical/electrical engineering types doing adhoc > queries for oddball reports and such who are not SQL experts. its way > to easy for them to accidentally create a transaction that they never > end, causing multiday blockage of VACUUM, massive table bloat, etc. Presumably they could also run a query that accidentally consumes all the CPU and I/O bandwidth on your server (hello missing WHERE clause!) It sounds like what you need is an intermediate layer that enforces transaction and query timeouts, rather than giving your mech/eng types direct access to the DB. -O
Oliver Jowett wrote: > John R Pierce wrote: > >> we have a fairly large database thats fed a >> constant stream of realtime data 24/7 from a factory doing both inserts >> and updates... many different sorts of things have to query this >> database, including mechanical/electrical engineering types doing adhoc >> queries for oddball reports and such who are not SQL experts. its way >> to easy for them to accidentally create a transaction that they never >> end, causing multiday blockage of VACUUM, massive table bloat, etc. >> > > Presumably they could also run a query that accidentally consumes all > the CPU and I/O bandwidth on your server (hello missing WHERE clause!) > on the typical server we use with 8 or 16 or more processors, and lots of fiberchannel bandwidth for storage, they can at most bung up a single core and some fraction of the total disk IO demand. the administrators are fairly used to run away reporting queries in the oracle space, and will kill them off without hesitation if they are impacting operational transactions.. > It sounds like what you need is an intermediate layer that enforces > transaction and query timeouts, rather than giving your mech/eng types > direct access to the DB. > thats kind of hard to do well when you need totally off the wall adhoc queries to answer complex engineering questions. on our larger scale systems we have a seperate dedicated database for reporting queries that runs on its own hardware, but this postgres system is meant to be a lower-cost version for a smaller/simpler operation, so the array of several back end servers we have traditionally used have all been folded onto this one server (currently the production evaluation server is on an 8 core Sun v490 with 16GB ram etc).
On Fri, Dec 11, 2009 at 12:57 AM, John R Pierce <pierce@hogranch.com> wrote: >> Does the Oracle driver not follow the JDBC spec here? Or is it just that >> long-term open transactions don't cause problems on Oracle? >> > > the latter Well eventually on Oracle you would get a "Snapshot too old" error. Postgres chooses not to have this unpredictable error and instead have unpredictable space usage -- it's an inevitable engineering tradeoff. However recent versions of Oracle have tried various strategies to reduce the frequency of this error -- likewise recent versions of Postgres have made efforts to reduce this issue as well. If you use 8.4 you should be much less likely to run into this problem. -- greg
Oliver Jowett wrote: > John R Pierce wrote: >> Oliver Jowett wrote: > >>> If your oracle developers don't want manually committed transactions, >>> perhaps they should.. turn on autocommit. >>> >> otherwise, an app thats doing purely read queries has to periodically >> issue a Commit(). I have to say, doing Commit() on read operations is >> NOT at all intuitive. > > Maybe not, but it's a fairly well known quirk of Postgresql that > long-running transactions don't work so well; the JDBC driver is no > different to any other interface in this regard. Does Pg have issues with long-running read-only transactions in read-committed mode? Or are they an exception to the usual "avoid long-running mostly-idle transactions" guideline? Pg doesn't have to worry about retaining old rows in vacuum with such a statement, because each new statement creates a new snapshot, so if it's idle it effectively has _no_ snapshot and vacuum is free to clear old rows. It doesn't need to keep track of anything to roll back, since the transaction is read only. So ... for READ COMMITTED, READ ONLY transactions, _are_ there any VACUUM issues? -- Craig Ringer
Greg Stark wrote: >>> Does the Oracle driver not follow the JDBC spec here? Or is it just that >>> long-term open transactions don't cause problems on Oracle? >> >> the latter > > Well eventually on Oracle you would get a "Snapshot too old" error. Only if the long reading transaction has isolation level SERIALIZABLE, because with READ COMMITTED it will not need very old rows from the UNDO tablespace. Yours, Laurenz Albe
Craig Ringer <craig@postnewspapers.com.au> writes:
> Does Pg have issues with long-running read-only transactions in
> read-committed mode? Or are they an exception to the usual "avoid
> long-running mostly-idle transactions" guideline?
> Pg doesn't have to worry about retaining old rows in vacuum with such a
> statement, because each new statement creates a new snapshot, so if it's
> idle it effectively has _no_ snapshot and vacuum is free to clear old
> rows. It doesn't need to keep track of anything to roll back, since the
> transaction is read only.
As of 8.4, I think the above statements are true. In prior releases,
transactions just advertised the age of their first snapshot, so an
open transaction created an issue for VACUUM regardless of whether
it was serializable or not. 8.4 has more bookkeeping that allows a
transaction to report that it currently has no live snapshots.
regards, tom lane
Tom Lane wrote: > As of 8.4, I think the above statements are true. In prior releases, > transactions just advertised the age of their first snapshot, so an > open transaction created an issue for VACUUM regardless of whether > it was serializable or not. 8.4 has more bookkeeping that allows a > transaction to report that it currently has no live snapshots. > I've been using this... pgadmin=# select datname,procpid,usename,now() - xact_start as "Age", current_query from pg_stat_activity where now()-xact_start> interval '30 min' order by xact_start; datname | procpid | usename | Age | current_query ---------+---------+---------+-----------------+------------------------------------------------ lss | 22528 | fds | 11:43:15.715747 | <IDLE> in transaction lss | 22979 | fds | 11:38:27.101715 | <IDLE> in transaction lss | 22980 | fds | 11:38:27.101676 | <IDLE> in transaction lss | 23150 | fds | 11:36:39.850232 | <IDLE> in transaction lss | 22987 | pgadmin | 03:38:57.974805 | autovacuum: VACUUM ANALYZE ts.disc_yield_daily lss | 24272 | pgadmin | 02:37:19.303353 | autovacuum: VACUUM ANALYZE ts.disc_yield_hour (6 rows) to detect old stale transactions and other long running operations in 8.4, there's a different high water mark I should be using instead?
John R Pierce <pierce@hogranch.com> writes:
> I've been using this...
> to detect old stale transactions and other long running operations
> in 8.4, there's a different high water mark I should be using instead?
No, the point of my comment was just that IDLE-in-transaction isn't as
dangerous as it used to be, from the perspective of whether VACUUM can
reclaim dead rows. There are still lots of other reasons to not want
transactions sitting open without need. They might be holding locks
for example.
regards, tom lane