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 > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
