Re: ALTER OBJECT any_name SET SCHEMA name

Hi,

Thanks for your review!

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
>> ALTER EXTENSION ext SET SCHEMA name;
>> ALTER EXTENSION ext SET SCHEMA foo TO bar;
>
>> I think that would end the open debate about search_path vs extension,
>> because each user would be able to relocate his local extensions easily,
>> wherever the main script has installed them (often enough, public).
>
> I'm not sure whether that really fixes anything, or just provides people
> with a larger-caliber foot-gun. See for example recent complaints about
> citext misbehaving if it's not in the public schema (or more generally,
> any schema not in the search path). I think we'd need to think a bit
> harder about the behavior of objects that aren't in the search path
> before creating a facility like this, since it seems to be tantamount
> to promising that extensions won't break when pushed around to different
> schemas.

Well AFAIK we have only two choices here. Either we impose the schema
where to find any extension's object or we offer more facilities for
users to support their choices.

In the former case, we have problems with upgrades and hosting several
versions of the same extension at the same time (think PostGIS 1.4 and
1.5 e.g.). Not being able to choose a schema where to host an
extension's objects only makes sense when the user can't set the
search_path, which is what we do with pg_catalog.

In the latter case, following your example, all it would take the user
to fix his setup would be a SET SCHEMA command on the citext extension.
The only goal of this proposal is not to have to rethink object
visibility, by offering the tools users need to manage the situation.

All in all, I don't think it'll be ever possible to both support
search_path flexibility and extension robustness when search_path
changes.

What we could do is offer extension's author a way to find their
operator or functions or whatever dynamically in SQL, so that writing
robust pure-SQL functions is possible. What comes to mind now would be a
way to call a function/operator/... by OID at the SQL level. Not pretty
but with the pg_extension_objects() SRF and maybe a layer atop that,
that would do the trick. Brain dumping still.

> I'm also a bit less than enthused about the implementation approach.
> If we're going to have a policy that every object type must support
> ALTER SET SCHEMA, I think it might be time to refactor, rather than
> copying-and-pasting similar boilerplate code for every one.

I've begun the effort with the CheckSetNamespace() function, about half
of the rest of the code could receive some abstraction too (syscache
searches, tuple access and editing, dependency changing, but ACL checks
looks harder. How much easier would it be with defmacro...

I'll have a try at it soon, happy to hear what you have in mind here
before I start, so that I follow your guidance.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support