Procedural language definitions (was Re: 8.1 and syntax checking at create time)

I wrote:
> We've had repeated problems with PL languages stemming from the fact
> that pg_dump dumps them at a pretty low semantic level. Aside from this
> problem with adding a validator, we used to have issues with hardwired
> paths to the shared libraries in the CREATE FUNCTION commands. And in
> 8.1, whether the functions are in "public" or "pg_catalog" is going to
> vary across installations depending on whether the language was restored
> from a dump or not.

> I wonder if we could change the dump representation to abstract out the
> knowledge encapsulated in "createlang". I don't suppose this would
> work:
> \! createlang plpgsql <dbname>
> but it'd be nice if the dump didn't know any more about the language
> than its name, and didn't mention the implementation functions at all.

I thought some more about this and came up with a sketch of a solution.
This would solve the problem of loading subtly-bogus language
definitions from existing dump files, and it also offers a possibility
of relaxing the rule that only superusers can create PLs.

The basic idea is to create a shared catalog that contains "procedural
language templates". This catalog would essentially replace the
knowledge that's now hardwired in the createlang program. It's shared
because we need it to be already available in a new database; and
anyway, the information in it need not vary across databases of an
installation. I'm envisioning a schema like

pg_pltemplate:
lanname name name of PL
lantrusted boolean trusted?
lanhandler text name of its call handler function
lanvalidator text name of its validator function, or NULL
lanlibrary text path of shared library, eg $libdir/plpgsql
lanacl acl[] see below

This could be filled in at initdb time with information about all the
languages available in the standard distribution (whether or not they've
actually been built) --- heck, we could include entries for all the PLs
we know of, whether shipped in the core or not.

Then we would change CREATE LANGUAGE so that it first takes the given
PL name and looks to see if there is an entry by that name in
pg_pltemplate. If so, it *ignores the given parameters* (if any) and
uses what's in pg_pltemplate. The logic would be identical to what
createlang does now: look to see if the functions already exist in the
current database, create them if not, then create the language entry.
(If the specified shared library does not actually exist in the
installation, we'd fail at the "create functions" step --- this is why
it's OK to have entries for languages not built in the distribution.)

The bit about ignoring the given parameters is needed to be able to have
the right things happen when loading an existing dump script from an
older PG version with different support functions for the language.
However, we would also simplify pg_dump to never dump the implementation
functions of a language in future, and to emit CREATE LANGUAGE as just
CREATE LANGUAGE plpgsql;
without decoration. (createlang would reduce to that too.)

For languages that do not have a template in pg_pltemplate, CREATE
LANGUAGE would operate the same as now. This case supports languages
that we don't know of. It might also be worthwhile to create a command
like
CREATE LANGUAGE TEMPLATE ...
to simplify making new entries in pg_pltemplate. (However, we could not
ask pg_dump to dump templates, else we've merely moved the obsolete-dump
problem over one space. Not sure if anyone would see that as a fatal
objection to the scheme. I think it's a pretty minor point as long as
we are liberal about including template entries in the standard distro,
so that you'd seldom need to add one by hand.)

Finally, you noticed I stuck an ACL column in there. I am imagining
that the superuser could grant USAGE rights on a template to designated
people (eg, admins of individual databases), who could then issue CREATE
LANGUAGE using that template in their databases, without needing
superuser rights. You'd still have to be superuser to muck with the
templates of course, but given a known-good template there's no reason
why a non-superuser shouldn't be allowed to instantiate the language
within his database. (This might need a little more thought when it
comes to untrusted PLs, but the idea seems sound.)

It's a shame that we didn't think about this before feature freeze,
as the recent changes to create PL support functions in pg_catalog
have made both pg_dump and createlang noticeably uglier than before.
We could have dispensed with those hacks. Oh well.

Comments?

regards, tom lane