Re: The missing pg_get_*def functions

* Joel Jacobson (joel@trustly.com) wrote:
> On Tue, Apr 30, 2013 at 12:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > That alone would probably be sufficient reason why we would never allow
> > pg_dump to depend on any such thing (not that I see a compelling
> > argument for it to do so anyway...).
>
> It would be better to find a way to update sql-language functions in
> minor upgrades, instead of shutting that door entirely for all future
> implementation ideas involving sql-language functions in the
> pg_catalog.

Go for it? :)  I don't think you'll find much disagreement here, but
it's no trivial thing to do either..  Perhaps some kind of one-time
startup "script" that gets run?  Or maybe an internal identifier that
tracks the version of the catalog and runs a script which updates it on
first start when there are changes to the catalog?  But what about any
dependencies which exist?  Are we confident enough in the existing
infrastructure for 'create-or-replace' that, if we don't change the API,
it'll just replace the contents and we won't hurt user objects?

> > The long and the short of it here is that there isn't any very good
> > reason to migrate any of the existing pg_dump-side functionality into
> > server-side functions, and especially not server-side functions that
> > aren't in C.  One of the things that we frequently recommend when doing
> > upgrades is that you do the dump with the newer version's pg_dump, so
> > as to get the benefits of any bug fixes that are in it.  The more
> > dump functionality is on the server side, the less opportunity we have
> > to repair things that way.
>
> There are two very good reasons in particular of doing exactly that:

[... good reasons to have pg_dump-like capability in the server ...]

There's a difference between 'migrate' and 'implement' that I'm not sure
you saw..  I agree w/ Tom that there's probably no really good reason to
*remove* the existing code in pg_dump.  Adding code which is similar to
the server is quite a different thing and, as Tom commented later, might
be useful for other tools to use.

> b) Reduce the amount of hard-coded sql queries in pg_dump.c.

Unless you're hacking on pg_dump quite a bit (which, if you are, you
probably need to understand/see the queries that it's making anyway), I
don't see why this is actually a problem..?  I'm thinking that you've
simply had to be in the guts of pg_dump enough, as some others of us
have also, that you grow tired of having to extract out the useful bits
from it.  If those bits are replicated as clean functions in the
backend, you won't need to be groking around in pg_dump anymore.. :)

> Won't
> help for previous versions, but perhaps the sql queries to support
> older versions can be hidden in some separately included file at least
> so you don't have to see it, which would make sense since the sql for
> the old versions won't change and won't need the same amount of active
> development as the code for the latest version. Makes no sense of
> keeping the sql for all versions at the same place.

I'd be all for a rework of pg_dump which more cleanly sets up the SQL
for each server version- feel free to work on such. :)

> > It may be that the functions Joel proposes are worth having for other
> > tools to use, but I'm not in favor of making pg_dump use them.
>
> I don't agree with you as I don't think it's a very hard problem to
> deal with updates of sql-language functions in the pg_catalog schema.

The above doesn't really seem like a response to the piece quoted, so
I'll respond to just what you wrote- I agree.  Let's figure out a way to
update sql-language functions.  Once we have that, it'll certainly open
up the door to having more of them.  I don't think we can punt on this
as a "well, we'll do it when we need it"; we've needed it in the past
(as I recall, we've had to tell people in the release notes to hack the
catalog for some issue or another at least once in most major revs..)
and it'd be great to have the problem solved.

> If we fix that first, this and a whole range of problems, where the
> solution involves taking in-data from pg_catalog, and producing
> out-data based on that, becomes super easy to achieve by writing
> simple sql-language functions with direct and simple access to the
> pg_catalog, instead of writing C functions with inline hard-coded sql.

Sounds fantastic.
Thanks,
    Stephen