Re: Prevent extension creation in temporary schemas - Mailing list pgsql-hackers
| From | Chris Travers |
|---|---|
| Subject | Re: Prevent extension creation in temporary schemas |
| Date | |
| Msg-id | CAN-RpxBrgOY6MFL=0HJLh4gmj6w2NiEvAUhw+p46LRXU1d2ORw@mail.gmail.com Whole thread Raw |
| In response to | Re: Prevent extension creation in temporary schemas (Michael Paquier <michael@paquier.xyz>) |
| Responses |
Re: Prevent extension creation in temporary schemas
Re: Prevent extension creation in temporary schemas |
| List | pgsql-hackers |
On Wed, Mar 6, 2019 at 3:19 AM Michael Paquier <michael@paquier.xyz> wrote:
On Tue, Mar 05, 2019 at 12:47:54PM +0000, Chris Travers wrote:
> I tried installing a test extension into a temp schema. I found
> this was remarkably difficult to do because pg_temp did not work (I
> had to create a temporary table and then locate the actual table it
> was created in). While that might also be a bug it is not in the
> scope of this patch so mostly noting in terms of future work.
pgcrypto works in this case.
So the issue here is in finding the pg temp schema to install into. The extension is less of an issue.
The point of my note above is that there are other sharp corners that have to be rounded off in order to make this work really well.
> After creating the extension I did as follows:
> \dx in the current session shows the extension
> \dx in a stock psql shows the extension in a separate session
> \dx with a patched psql in a separate session does not show the
> extension.
>
> In terms of the scope of this patch, I think this correctly and
> fully solves the problem at hand.
I was just looking at this patch this morning with fresh eyes, and I
think that I have found one argument to *not* apply it. Imagine the
following in one session:
=# create extension pgcrypto with schema pg_temp_3;
CREATE EXTENSION
=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+------------------------------
pgcrypto | 1.3 | pg_temp_3 | cryptographic functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
That's all good, we see that the session which created this extension
has it listed. Now let's use in parallel a second session:
=# create extension pgcrypto with schema pg_temp_4;
ERROR: 42710: extension "pgcrypto" already exists
LOCATION: CreateExtension, extension.c:1664
=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
This is actually also good, because the extension of the temporary
schema of the first session does not show up. Now I think that this
can bring some confusion to the user actually, because the extension
becomes not listed via \dx, but trying to create it with a different
schema fails.
Ok so at present I see three distinct issues here, where maybe three different patches over time might be needed.
The issues are:
1. create extension pgcrypto with schema pg_temp; fails because there is no schema actually named pg_temp.
2. If you work around this, the \dx shows temporary extensions in other sessions. This is probably the most minor issue of the three.
3. You cannot create the same extension in two different schemas.
My expectation is that this may be a situation where other sharp corners are discovered over time. My experience is that where things are difficult to do in PostgreSQL and hence not common, these sharp corners exist (domains vs constraints in table-based composite types for example, multiple inheritance being another).
It is much easier to review patches if they make small, well defined changes to the code. I don't really have an opinion on whether this should be applied as is, or moved to next commitfest in the hope we can fix issue #3 there too. But I would recommend not fixing the pg_temp naming (#1 above) until at least the other two are fixed. There is no sense in making this easy yet. But I would prefer to review or write patches that address these issues one at a time rather than try to get them all reviewed and included together.
But I don't think there is likely to be a lot of user confusion here. It is hard enough to install extensions in temporary schemas that those who do can be expected to know more that \dx commands.
Thoughts?
--
Michael
Best Regards,
Chris Travers
Head of Database
Saarbrücker Straße 37a, 10405 Berlin
pgsql-hackers by date: