Skip site navigation (1) Skip section navigation (2)

Re: machine-readable pg_controldata?

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: machine-readable pg_controldata?
Date: 2010-03-05 13:31:14
Message-ID: 4B9107A2.6070209@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-03-05 13:39:38
Subject: Re: Explicit psqlrc
Previous:From: Stephen FrostDate: 2010-03-05 13:18:33
Subject: Re: Repetition of warning message while REVOKE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group