Thread: CREATE EXTENSION BLOCKS
Hackers, I am working on scripts to copy data from Oracle via oracle_fdw. They each do something like this: CREATE SCHEMA migrate_stuff; SET search_path TO migrate_stuff,public; CREATE EXTENSION oracle_fdw SCHEMA migrate_rules; CREATE SERVER oracle_stuff FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver :'oracle_uri'); CREATE USER MAPPING FOR postgres SERVER oracle_stuff OPTIONS (user :'oracle_user', password :'oracle_pass'); CREATE FOREIGN TABLE migrate_stuff ( stuff_id integer, name text ) SERVER oracle_rules OPTIONS(table 'STUFF'); INSERT INTO my.stuff SELECT * FROM migrate_stuff; DROP SCHEMA migrate_stuff CASCADE; COMMIT; Then I run them in parallel: for file in migrate*.sql; do psql -d foo -f $file & done wait This works fine except for one thing: the first CREATE EXTENSION statement blocks all the others. Even when I create theextension in separate schemas in each script! I have to remove the CREATE EXTENSION statement, create it in public beforeany of the scripts run, then drop it when they're done. I'm okay with this workaround, but wasn't sure if the blockingof CREATE EXTENSION was intentional or a known issue (id did not see it documented in http://www.postgresql.org/docs/current/static/sql-createextension.html). Thanks, David
David E. Wheeler wrote: > I am working on scripts to copy data from Oracle via oracle_fdw. They each do something like this: > > CREATE SCHEMA migrate_stuff; > SET search_path TO migrate_stuff,public; > CREATE EXTENSION oracle_fdw SCHEMA migrate_rules; > [...] > > Then I run them in parallel: > > for file in migrate*.sql; do > psql -d foo -f $file & > done > wait > > This works fine except for one thing: the first CREATE EXTENSION statement blocks all the others. Even > when I create the extension in separate schemas in each script! I have to remove the CREATE EXTENSION > statement, create it in public before any of the scripts run, then drop it when they're done. I'm okay > with this workaround, but wasn't sure if the blocking of CREATE EXTENSION was intentional or a known > issue (id did not see it documented in http://www.postgresql.org/docs/current/static/sql- > createextension.html). I'd expect one of the CREATE EXTENSION commands to succeed and the others to block until the transaction is committed, then to fail with 'extension "oracle_fdw" already exists'. If that is what happens, it's what I'd expect since extension names are unique (see the unique constraint on pg_extension). Yours, Laurenz Albe
On Apr 3, 2013, at 2:37 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > I'd expect one of the CREATE EXTENSION commands to succeed > and the others to block until the transaction is committed, > then to fail with 'extension "oracle_fdw" already exists'. > > If that is what happens, it's what I'd expect since > extension names are unique (see the unique constraint on > pg_extension). Oh, they are not unique per-schema? I guess they are global to the database but then their objects are in the specified schema,then. Thanks, David
David E. Wheeler wrote: > On Apr 3, 2013, at 2:37 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > > > I'd expect one of the CREATE EXTENSION commands to succeed > > and the others to block until the transaction is committed, > > then to fail with 'extension "oracle_fdw" already exists'. > > > > If that is what happens, it's what I'd expect since > > extension names are unique (see the unique constraint on > > pg_extension). > > Oh, they are not unique per-schema? I guess they are global to the database but then their objects are in the specifiedschema, then. Right -- an extension is not considered to live within a schema, they are database-global. The objects might live in a particular schema (if it is "relocatable"), and there's support to move those to a different schema, but this doesn't affect the extension itself. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Apr 3, 2013, at 11:41 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: >> Oh, they are not unique per-schema? I guess they are global to the database but then their objects are in the specifiedschema, then. > > Right -- an extension is not considered to live within a schema, they > are database-global. The objects might live in a particular schema (if > it is "relocatable"), and there's support to move those to a different > schema, but this doesn't affect the extension itself. Thanks. I humbly submit this patch to help prevent silly questions like this in the future. diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml index 4f3b9a5..4ab3dff 100644 --- a/doc/src/sgml/ref/create_extension.sgml +++ b/doc/src/sgml/ref/create_extension.sgml @@ -93,6 +93,8 @@ CREATE EXTENSION [ IF NOT EXISTS ] <replaceable class="parameter">extension_name relocated. Thenamed schema must already exist. If not specified, and the extension's control file does not specify a schemaeither, the current default object creation schema is used. + Note that only the extension objects will be placed into the named + schema; the extension itself is a database-global object. </para> </listitem> </varlistentry> Best, David
Hi, I though we were more specific about an extension's object itself not living in a schema in our documentation, but I agree we still have room for progress here. "David E. Wheeler" <david@justatheory.com> writes: > + Note that only the extension objects will be placed into the named > + schema; the extension itself is a database-global object. I think you're patching the right place, but I'm not sure about the term "database-global object", that I can't find by grepping in sgml/ref. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Apr 4, 2013, at 5:16 AM, Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote: > "David E. Wheeler" <david@justatheory.com> writes: >> + Note that only the extension objects will be placed into the named >> + schema; the extension itself is a database-global object. > > I think you're patching the right place, but I'm not sure about the term > "database-global object", that I can't find by grepping in sgml/ref. Yeah, I wasn't sure, either, but figured someone here would know what to call those sorts of things. Thanks, David
"David E. Wheeler" <david@justatheory.com> writes:
> On Apr 3, 2013, at 11:41 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> Right -- an extension is not considered to live within a schema, they
>> are database-global. The objects might live in a particular schema (if
>> it is "relocatable"), and there's support to move those to a different
>> schema, but this doesn't affect the extension itself.
> Thanks. I humbly submit this patch to help prevent silly questions like this in the future.
I think this should be addressed in extend.sgml not only on the CREATE
EXTENSION reference page. After thinking awhile I came up with the
attached wording. Further wordsmithing anyone?
regards, tom lane
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 672d0df..bc1cd59 100644
*** a/doc/src/sgml/extend.sgml
--- b/doc/src/sgml/extend.sgml
***************
*** 359,364 ****
--- 359,370 ----
extension.) Also notice that while a table can be a member of an
extension, its subsidiary objects such as indexes are not directly
considered members of the extension.
+ Another important point is that schemas can belong to extensions, but not
+ vice versa: an extension as such has an unqualified name and does not
+ exist <quote>within</> any schema. The extension's member objects,
+ however, will belong to schemas whenever appropriate for their object
+ types. It may or may not be appropriate for an extension to own the
+ schema(s) its member objects are within.
</para>
<sect2>
diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml
index 4f3b9a5..9c9bf6f 100644
*** a/doc/src/sgml/ref/create_extension.sgml
--- b/doc/src/sgml/ref/create_extension.sgml
*************** CREATE EXTENSION [ IF NOT EXISTS ] <repl
*** 94,99 ****
--- 94,105 ----
If not specified, and the extension's control file does not specify a
schema either, the current default object creation schema is used.
</para>
+ <para>
+ Remember that the extension itself is not considered to be within any
+ schema: extensions have unqualified names that must be unique
+ database-wide. But objects belonging to the extension can be within
+ schemas.
+ </para>
</listitem>
</varlistentry>
Tom Lane <tgl@sss.pgh.pa.us> writes: > I think this should be addressed in extend.sgml not only on the CREATE > EXTENSION reference page. After thinking awhile I came up with the > attached wording. Further wordsmithing anyone? Thanks! -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Apr 4, 2013, at 2:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I think this should be addressed in extend.sgml not only on the CREATE > EXTENSION reference page. After thinking awhile I came up with the > attached wording. Further wordsmithing anyone? Works for me, though I think it would be useful to have a term for objects that are unique in/global to a database. Likeschemas (duh) and extensions. David