Re: The missing pg_get_*def functions

On Tue, Apr 30, 2013 at 04:47:58AM +0100, Joel Jacobson wrote:
> On Tue, Apr 30, 2013 at 12:46 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > Those existing functions give a mostly-SnapshotNow picture of their objects,
> > but an sql-language implementation would give a normally-snapshotted picture.
>
> I assume "normally" is better than "mostly"?

Inconsistent snapshot usage ("mostly"-anything) is bad, especially within the
confines of a single function. pg_get_viewdef() grabs pg_rewrite.ev_action
using an MVCC snapshot, then interprets it relative to SnapshotNow. That's a
paradox waiting to happen. (Granted, the same could be said for *any* use of
SnapshotNow in the absence of locking.)

Whether all-normally-snapshotted (all-MVCC) beats all-SnapshotNow is less
clear-cut, but I tentatively think it would.

> > That status quo is perhaps more an implementation accident than a designed
> > behavior. Before proliferating functions like this, we should pick a snapshot
> > policy and stick to it. See the block comment at the top of pg_dump.c.
>
> I didn't think there would be any reason to migrate the existing
> functions from C to SQL, but this snapshot problem seems like a good
> motive to do it. If they would all be written in SQL, the snapshot
> problem would be solved, right?

Nominally yes, but not because of difficulty using a normal MVCC snapshot from
C. It's just that the sql PL uses nothing but normal MVCC snapshots. So,
this isn't a sound reason to translate C to SQL. In any case, I can't fathom
a prudent 100% sql implementation of pg_get_viewdef(), which needs to deparse
arbitrary queries.

> > Note also that minor releases can readily fix bugs in C-language functions,
> > but we have no infrastructure to update sql-language functions after initdb.
> > That flexibility is unfortunate to lose, particularly for something that
> > pg_dump depends on. Now, the right thing is probably to design a mechanism
> > for applying simple catalog updates in concert with a minor release. In the
> > mean time, its absence puts the sql PL at a nontrivial disadvantage here.
>
> What do you mean with "infrastructure"? Isn't it as simple as CREATE
> OR REPLACE FUNCTION? As long as the interface the pg_get_*def
> functions don't change, I cannot see how simply replacing the existing
> functions in a minor release upgrade could do any harm.

Stephen described the sort of infrastructure I had in mind.

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com