Re: machine-readable pg_controldata?

Magnus Hagander wrote:
> Huh? It's fixed with, you don't need regexps for that. Just split the
> string apart.
>
> Taking options for single fields might have a better usecase, of course :-)
>

I do find it a bit hard to imagine that any program capable of shelling
out to call pg_controldata and doing something with the output would hit
a major hurdle parsing the format that's already there. Moving toward
single fields I could see as being better for some cases, but going all
the way to XML/JSON is a step backwards from the plain text format as
far as I'm concerned. Anything that can parse one of those complicated
formats should be able to trivially chew the existing one already.
Seriously: I have bash scripts that parse that thing.

>> Even better would be the ability to get everything which is in
>> pg_controldata currently as part of a system view in a running
>> PostgreSQL; I can get most of them, but certainly not all.
>>
>
> +1 for having all the information available from inside the backend,
> if that's technically possible (which I assume it should be)
>

I revisit this every time I write yet another user-space parser and ask
myself why I haven't exposed it in the server yet. The primary answer
so far has always been "because you can't execute a query on the standby
while it's in recovery", making half the stuff I wanted the data far
(e.g. standby lag monitoring like
http://www.kennygorman.com/wordpress/?p=249 ) unable to use that
interface anyway. Now that Hot Standby cracks that objection, it's
worth talking about for a minute.

pg_controldata itself just reads the file in directly and dumps the
data. There is a copy of it kept around all the time in shared memory
though (ControlFile in xlog.c), protected by a LWLock. At a high level
you can imagine a new function in xlog.c that acquires that lock, copies
the block into a space the backend allocated for saving it, releases the
lock, and then returns the whole structure. Then just wrap some number
of superuser-only UDFs around it (I'd guess nobody wants regular ones
able to hold a lock on ControlFile) and you've exposed the results to
user-space.

Two questions before I'd volunteer to write that:

1) How do you handle the situation where the pg_controldata is invalid?
"Not read in yet" and "CRC is bad" are the two most obvious ones that
can happen. Return a null for every field, try and guess (the way
pg_resetxlog does), don't return a row of output at all, or throw an
error? Each of these has slightly different implications for how admin
code that will do something with these values will have to be structured.

2) While it's easy to say "I only want one or two of these values" and
expose a whole set of UDFs to grab them individually (perhaps wrapping
into a system view via that popular approach), I am concerned that
people are going to call any single-value versions provided one at a
time and get an inconsistent set. I think the only reasonable interface
to this would not return a single field, it would pop out all of them so
you got a matching set from the point in time the lock was held. And if
that's the case, I'm not sure of the most reasonable UI is. Just return
a whole row with a column for each field in the file, and then people
can select out just the ones they want? (That's probably the right
one) Produce the mess as a series of rows with (name,value) pairs? Put
them into an array?

Have re-raised these concerns to myself, this is usually the point in
this exercise where I go "screw it, I'll just parse pg_controldata again
instead" and do that instead. This is happening so much lately that I
think Josh's suggestion it's just unworkable to keep going via that
model forever has merit though. I find it hard to mark this 9.0
territory though, given the data is not actually difficult to grab--and
that trail is already well blazed, nothing new in this version.

In short: I'd vote for TODO item and would happily write myself for 9.1
given reasonable agreement on the questions raised above, -1 for doing
anything about it right now though. Given both the existence of
completely reasonable workarounds and the existence of much more serious
blocker problems sitting on the roadmap to release, can't get real
excited about this as the thing to worry about right now. Same reason I
ignored the idea when Joshua Tolley brought it up last month:
http://archives.postgresql.org/message-id/4b69caeb.9513f30a.731a.3427@mx.google.com

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us