Re: The missing pg_get_*def functions - Mailing list pgsql-hackers

From Noah Misch
Subject Re: The missing pg_get_*def functions
Date
Msg-id 20130430223146.GA27134@tornado.leadboat.com
Whole thread Raw
In response to Re: The missing pg_get_*def functions  (Joel Jacobson <joel@trustly.com>)
List pgsql-hackers
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@leadboat.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



pgsql-hackers by date:

Previous
From: Dmitry Koterov
Date:
Subject: Incomplete description of pg_start_backup?
Next
From: Greg Smith
Date:
Subject: Re: Substituting Checksum Algorithm (was: Enabling Checksums)