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 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).
Thanks,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
waitThis 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 specified schema, 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 specified schema, 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. The named schema must already exist.
If not specified, and the extension's control file does not specify a
schema either, 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
"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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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. Like schemas (duh) and extensions.
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers