Re: search_path vs extensions

On Mon, May 25, 2009 at 11:16 AM, Dimitri Fontaine
<dfontaine(at)hi-media(dot)com> wrote:
> Hi,
>
>  Preliminary note: I'm using the term "extension" as if it's what we
>  already agree to call them, feel free to ignore this and use whatever
>  term you see fit. We'll have the naming issue tackled, please not now
>  though.
>
[...]

Few thoughts about the ideas:

Basically I sort of don't like the idea of playing with search_path.
In past I have set up a system where each extension had a separate
schema. Maintaining per user search_path wasn't a very nice experience.
And trying to alter it later on for whatever reason, especially from
command line was even worse. :) I tend to avoid such designs now. :)

I think it is much better to store objects in one schema (like public)
and maintain access rights via roles. Like GRANT ltree_pkg TO userfoo;
...and build upon this idea.
One of advantages of roles here is that you can DROP OWNED BY ltree_pkg;
just as well as you did DROP SCHEMA ltree_pkg;
And they take effect immediately, not requiring all sessions to restart
to take up new search_path.

Furthermore, I think it would be nice to have a cluster-wide pg_extension
table which would list all the available (installed) packages available in
the system (much like pg_database lists all databases present).
This pg_extension should be used to "rewrite" extension objects into
given schema using given role (which would be either fixed or user defined).

The idea is that whenever user installs a RPM, DEB or whatever package
the system registers the extension. Or she compiles from source and registers
extension. Or we get a CPAN style utility which installs source, compiles and
register the extension. Then administrator can copy over given extension
into specific database, into specific schema.

Simplest implementation would be that the pg_extension would contain
a package name, package version (we can have multiple versions of
the same package installed), install script (series of CREATE FUNCTION
or whatever), uninstall script (may not be present) and some upgrade
path would be needed as well.

The installation would CREATE ROLE <packagename>_pkg and execute
all CREATE FUNCTION inside schema PUBLIC. Then GRANT access.
If administrator instructs so it might CREATE ROLE <packagename>_<schema>_pkg
and execute all CREATE FUNCTION in schema <schema>.

Uninstall would mean DROP OWNED BY <packagename>_pkg;

OK, enough of my proposal. :-)

Coming back to the pre_search_path -- it sounds somewhat like Oracle's
PACKAGEs, only different (completely parallel hierarchy, but similar to
schemas). I like the Oracle approach better though -- no messing with
search_paths please...

Best regards,
Dawid
--
.................. ``The essence of real creativity is a certain
: *Dawid Kuroczko* : playfulness, a flitting from idea to idea
: qnex42(at)gmail(dot)com : without getting bogged down by fixated demands.''
`..................' Sherkaner Underhill, A Deepness in the Sky, V. Vinge