Skip site navigation (1) Skip section navigation (2)

CREATE EXTENSION BLOCKS

From: "David E(dot) Wheeler" <david(at)justatheory(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: CREATE EXTENSION BLOCKS
Date: 2013-04-02 22:19:33
Message-ID: 8B7A41E9-D3FB-4DC8-B66B-10D04F09083E@justatheory.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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



Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2013-04-02 22:59:39
Subject: Re: spoonbill vs. -HEAD
Previous:From: Tom LaneDate: 2013-04-02 22:01:50
Subject: Re: spoonbill vs. -HEAD

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group