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

On Wed, Aug 31, 2005 at 05:56:52PM -0400, Tom Lane wrote:
> 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?

This idea appears to me to be sound. It may be worth adding the
feature during beta anyway to simplify the ugliness of pg_dump
with createlang problems. The large number of weird configurations
"out there" could use the beta testing of this release. I
ran into this issue a lot with non-standard installations.

--elein

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>