Re: machine-readable pg_controldata?

Magnus Hagander wrote:<br /><blockquote cite="mid:9837222c1003041354v19e7b451xd823e4a8a7489d@mail.gmail.com"
type="cite"><prewrap="">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 :-) </pre></blockquote><br /> I do find it a
bithard to imagine that any program capable of shelling out to call pg_controldata and doing something with the output
wouldhit a major hurdle parsing the format that's already there.  Moving toward single fields I could see as being
betterfor 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.<br /><br /><blockquote
cite="mid:9837222c1003041354v19e7b451xd823e4a8a7489d@mail.gmail.com"type="cite"><blockquote type="cite"><pre
wrap="">Evenbetter 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.   </pre></blockquote><pre wrap="">
+1 for having all the information available from inside the backend,
if that's technically possible (which I assume it should be) </pre></blockquote><br /> I revisit this every time I
writeyet another user-space parser and ask myself why I haven't exposed it in the server yet.  The primary answer so
farhas always been "because you can't execute a query on the standby while it's in recovery", making half the stuff I
wantedthe data far (e.g. standby lag monitoring like <a class="moz-txt-link-freetext"
href="http://www.kennygorman.com/wordpress/?p=249">http://www.kennygorman.com/wordpress/?p=249</a>) unable to use that
interfaceanyway.  Now that Hot Standby cracks that objection, it's worth talking about for a minute.<br /><br />
pg_controldataitself just reads the file in directly and dumps the data.  There is a copy of it kept around all the
timein shared memory though (ControlFile in xlog.c), protected by a LWLock.  At a high level you can imagine a new
functionin xlog.c that acquires that lock, copies the block into a space the backend allocated for saving it, releases
thelock, and then returns the whole structure.  Then just wrap some number of superuser-only UDFs around it (I'd guess
nobodywants regular ones able to hold a lock on ControlFile) and you've exposed the results to user-space.<br /><br />
Twoquestions before I'd volunteer to write that:<br /><br /> 1) How do you handle the situation where the
pg_controldatais invalid?  "Not read in yet" and "CRC is bad" are the two most obvious ones that can happen.  Return a
nullfor 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
willhave to be structured.<br /><br /> 2) While it's easy to say "I only want one or two of these values" and expose a
wholeset of UDFs to grab them individually (perhaps wrapping into a system view via that popular approach), I am
concernedthat people are going to call any single-value versions provided one at a time and get an inconsistent set.  I
thinkthe only reasonable interface to this would not return a single field, it would pop out all of them so you got a
matchingset 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?<br/><br /> 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'ssuggestion it's just unworkable to keep going via that model forever has merit though.  I find it hard to mark
this9.0 territory though, given the data is not actually difficult to grab--and that trail is already well blazed,
nothingnew in this version.<br /><br /> In short:  I'd vote for TODO item and would happily write myself for 9.1 given
reasonableagreement on the questions raised above, -1 for doing anything about it right now though.  Given both the
existenceof completely reasonable workarounds and the existence of much more serious blocker problems sitting on the
roadmapto release, can't get real excited about this as the thing to worry about right now.  Same reason I ignored the
ideawhen Joshua Tolley brought it up last month:  <a class="moz-txt-link-freetext"
href="http://archives.postgresql.org/message-id/4b69caeb.9513f30a.731a.3427@mx.google.com">http://archives.postgresql.org/message-id/4b69caeb.9513f30a.731a.3427@mx.google.com</a><br
/><br/><pre class="moz-signature" cols="72">-- 
 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
<a class="moz-txt-link-abbreviated" href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a>   <a
class="moz-txt-link-abbreviated"href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
 

</pre>