Re: Connection.setReadOnly()

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).