path: root/src/test/modules/test_extensions/expected/test_extensions.out

CREATE SCHEMA has$dollar;
-- test some errors
CREATE EXTENSION test_ext1;
ERROR:  required extension "test_ext2" is not installed
HINT:  Use CREATE EXTENSION ... CASCADE to install required extensions too.
CREATE EXTENSION test_ext1 SCHEMA test_ext1;
ERROR:  schema "test_ext1" does not exist
CREATE EXTENSION test_ext1 SCHEMA test_ext;
ERROR:  schema "test_ext" does not exist
CREATE EXTENSION test_ext1 SCHEMA has$dollar;
ERROR:  extension "test_ext1" must be installed in schema "test_ext1"
-- finally success
CREATE EXTENSION test_ext1 SCHEMA has$dollar CASCADE;
NOTICE:  installing required extension "test_ext2"
NOTICE:  installing required extension "test_ext3"
NOTICE:  installing required extension "test_ext5"
NOTICE:  installing required extension "test_ext4"
SELECT extname, nspname, extversion, extrelocatable FROM pg_extension e, pg_namespace n WHERE extname LIKE 'test_ext%' AND e.extnamespace = n.oid ORDER BY 1;
  extname  |  nspname   | extversion | extrelocatable 
-----------+------------+------------+----------------
 test_ext1 | test_ext1  | 1.0        | f
 test_ext2 | has$dollar | 1.0        | t
 test_ext3 | has$dollar | 1.0        | t
 test_ext4 | has$dollar | 1.0        | t
 test_ext5 | has$dollar | 1.0        | t
(5 rows)

CREATE EXTENSION test_ext_cyclic1 CASCADE;
NOTICE:  installing required extension "test_ext_cyclic2"
ERROR:  cyclic dependency detected between extensions "test_ext_cyclic1" and "test_ext_cyclic2"
DROP SCHEMA has$dollar CASCADE;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to extension test_ext3
drop cascades to extension test_ext5
drop cascades to extension test_ext2
drop cascades to extension test_ext4
drop cascades to extension test_ext1
CREATE SCHEMA has$dollar;
CREATE EXTENSION test_ext6;
DROP EXTENSION test_ext6;
CREATE EXTENSION test_ext6;
-- test dropping of member tables that own extensions:
-- this table will be absorbed into test_ext7
create table old_table1 (col1 serial primary key);
create extension test_ext7;
\dx+ test_ext7
Objects in extension "test_ext7"
      Object description       
-------------------------------
 sequence ext7_table1_col1_seq
 sequence ext7_table2_col2_seq
 sequence old_table1_col1_seq
 table ext7_table1
 table ext7_table2
 table old_table1
 type ext7_table1
 type ext7_table1[]
 type ext7_table2
 type ext7_table2[]
 type old_table1
 type old_table1[]
(12 rows)

alter extension test_ext7 update to '2.0';
\dx+ test_ext7
Objects in extension "test_ext7"
      Object description       
-------------------------------
 sequence ext7_table2_col2_seq
 table ext7_table2
 type ext7_table2
 type ext7_table2[]
(4 rows)

-- test reporting of errors in extension scripts
alter extension test_ext7 update to '2.1bad';
ERROR:  syntax error at or near "FUNCTIN"
LINE 1: CREATE FUNCTIN my_erroneous_func(int) RETURNS int LANGUAGE S...
               ^
QUERY:  CREATE FUNCTIN my_erroneous_func(int) RETURNS int LANGUAGE SQL
AS $$ SELECT $1 + 1 $$;
CONTEXT:  extension script file "test_ext7--2.0--2.1bad.sql", near line 10
alter extension test_ext7 update to '2.2bad';
ERROR:  syntax error at or near ","
LINE 1:  SELECT $1 + , 1 
                     ^
QUERY:   SELECT $1 + , 1 
CONTEXT:  SQL statement "CREATE FUNCTION my_erroneous_func(int) RETURNS int LANGUAGE SQL
AS $$ SELECT $1 + , 1 $$"
extension script file "test_ext7--2.0--2.2bad.sql", near line 9
-- test handling of temp objects created by extensions
create extension test_ext8;
-- \dx+ would expose a variable pg_temp_nn schema name, so we can't use it here
select regexp_replace(pg_describe_object(classid, objid, objsubid),
                      'pg_temp_\d+', 'pg_temp', 'g') as "Object description"
from pg_depend
where refclassid = 'pg_extension'::regclass and deptype = 'e' and
  refobjid = (select oid from pg_extension where extname = 'test_ext8')
order by 1;
           Object description            
-----------------------------------------
 function ext8_even(posint)
 function pg_temp.ext8_temp_even(posint)
 table ext8_table1
 table ext8_temp_table1
 type ext8_table1
 type ext8_table1[]
 type ext8_temp_table1
 type ext8_temp_table1[]
 type posint
 type posint[]
(10 rows)

-- Should be possible to drop and recreate this extension
drop extension test_ext8;
create extension test_ext8;
select regexp_replace(pg_describe_object(classid, objid, objsubid),
                      'pg_temp_\d+', 'pg_temp', 'g') as "Object description"
from pg_depend
where refclassid = 'pg_extension'::regclass and deptype = 'e' and
  refobjid = (select oid from pg_extension where extname = 'test_ext8')
order by 1;
           Object description            
-----------------------------------------
 function ext8_even(posint)
 function pg_temp.ext8_temp_even(posint)
 table ext8_table1
 table ext8_temp_table1
 type ext8_table1
 type ext8_table1[]
 type ext8_temp_table1
 type ext8_temp_table1[]
 type posint
 type posint[]
(10 rows)

-- here we want to start a new session and wait till old one is gone
select pg_backend_pid() as oldpid \gset
\c -
do 'declare c int = 0;
begin
  while (select count(*) from pg_stat_activity where pid = '
    :'oldpid'
  ') > 0 loop c := c + 1; perform pg_stat_clear_snapshot(); end loop;
  raise log ''test_extensions looped % times'', c;
end';
-- extension should now contain no temp objects
\dx+ test_ext8
Objects in extension "test_ext8"
     Object description     
----------------------------
 function ext8_even(posint)
 table ext8_table1
 type ext8_table1
 type ext8_table1[]
 type posint
 type posint[]
(6 rows)

-- dropping it should still work
drop extension test_ext8;
-- check handling of types as extension members
create extension test_ext9;
\dx+ test_ext9
          Objects in extension "test_ext9"
                 Object description                 
----------------------------------------------------
 cast from varbitrange to varbitmultirange
 function varbitmultirange()
 function varbitmultirange(varbitrange)
 function varbitmultirange(varbitrange[])
 function varbitrange(bit varying,bit varying)
 function varbitrange(bit varying,bit varying,text)
 table sometable
 type somecomposite
 type somecomposite[]
 type sometable
 type sometable[]
 type varbitmultirange
 type varbitmultirange[]
 type varbitrange
 type varbitrange[]
(15 rows)

alter extension test_ext9 drop type varbitrange;
\dx+ test_ext9
          Objects in extension "test_ext9"
                 Object description                 
----------------------------------------------------
 cast from varbitrange to varbitmultirange
 function varbitmultirange()
 function varbitmultirange(varbitrange)
 function varbitmultirange(varbitrange[])
 function varbitrange(bit varying,bit varying)
 function varbitrange(bit varying,bit varying,text)
 table sometable
 type somecomposite
 type somecomposite[]
 type sometable
 type sometable[]
(11 rows)

alter extension test_ext9 add type varbitrange;
\dx+ test_ext9
          Objects in extension "test_ext9"
                 Object description                 
----------------------------------------------------
 cast from varbitrange to varbitmultirange
 function varbitmultirange()
 function varbitmultirange(varbitrange)
 function varbitmultirange(varbitrange[])
 function varbitrange(bit varying,bit varying)
 function varbitrange(bit varying,bit varying,text)
 table sometable
 type somecomposite
 type somecomposite[]
 type sometable
 type sometable[]
 type varbitmultirange
 type varbitmultirange[]
 type varbitrange
 type varbitrange[]
(15 rows)

alter extension test_ext9 drop table sometable;
\dx+ test_ext9
          Objects in extension "test_ext9"
                 Object description                 
----------------------------------------------------
 cast from varbitrange to varbitmultirange
 function varbitmultirange()
 function varbitmultirange(varbitrange)
 function varbitmultirange(varbitrange[])
 function varbitrange(bit varying,bit varying)
 function varbitrange(bit varying,bit varying,text)
 type somecomposite
 type somecomposite[]
 type varbitmultirange
 type varbitmultirange[]
 type varbitrange
 type varbitrange[]
(12 rows)

alter extension test_ext9 add table sometable;
\dx+ test_ext9
          Objects in extension "test_ext9"
                 Object description                 
----------------------------------------------------
 cast from varbitrange to varbitmultirange
 function varbitmultirange()
 function varbitmultirange(varbitrange)
 function varbitmultirange(varbitrange[])
 function varbitrange(bit varying,bit varying)
 function varbitrange(bit varying,bit varying,text)
 table sometable
 type somecomposite
 type somecomposite[]
 type sometable
 type sometable[]
 type varbitmultirange
 type varbitmultirange[]
 type varbitrange
 type varbitrange[]
(15 rows)

drop extension test_ext9;
-- Test creation of extension in temporary schema with two-phase commit,
-- which should not work.  This function wrapper is useful for portability.
-- Avoid noise caused by CONTEXT and NOTICE messages including the temporary
-- schema name.
\set SHOW_CONTEXT never
SET client_min_messages TO 'warning';
-- First enforce presence of temporary schema.
CREATE TEMP TABLE test_ext4_tab ();
CREATE OR REPLACE FUNCTION create_extension_with_temp_schema()
  RETURNS VOID AS $$
  DECLARE
    tmpschema text;
    query text;
  BEGIN
    SELECT INTO tmpschema pg_my_temp_schema()::regnamespace;
    query := 'CREATE EXTENSION test_ext4 SCHEMA ' || tmpschema || ' CASCADE;';
    RAISE NOTICE 'query %', query;
    EXECUTE query;
  END; $$ LANGUAGE plpgsql;
BEGIN;
SELECT create_extension_with_temp_schema();
 create_extension_with_temp_schema 
-----------------------------------
 
(1 row)

PREPARE TRANSACTION 'twophase_extension';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
-- Clean up
DROP TABLE test_ext4_tab;
DROP FUNCTION create_extension_with_temp_schema();
RESET client_min_messages;
\unset SHOW_CONTEXT
-- Test case of an event trigger run in an extension upgrade script.
-- See: https://postgr.es/m/20200902193715.6e0269d4@firost
CREATE EXTENSION test_ext_evttrig;
ALTER EXTENSION test_ext_evttrig UPDATE TO '2.0';
DROP EXTENSION test_ext_evttrig;
-- It's generally bad style to use CREATE OR REPLACE unnecessarily.
-- Test what happens if an extension does it anyway.
-- Replacing a shell type or operator is sort of like CREATE OR REPLACE;
-- check that too.
CREATE FUNCTION ext_cor_func() RETURNS text
  AS $$ SELECT 'ext_cor_func: original'::text $$ LANGUAGE sql;
CREATE EXTENSION test_ext_cor;  -- fail
ERROR:  function ext_cor_func() is not a member of extension "test_ext_cor"
DETAIL:  An extension is not allowed to replace an object that it does not own.
CONTEXT:  SQL statement "CREATE OR REPLACE FUNCTION ext_cor_func() RETURNS text
  AS $$ SELECT 'ext_cor_func: from extension'::text $$ LANGUAGE sql"
extension script file "test_ext_cor--1.0.sql", near line 8
SELECT ext_cor_func();
      ext_cor_func      
------------------------
 ext_cor_func: original
(1 row)

DROP FUNCTION ext_cor_func();
CREATE VIEW ext_cor_view AS
  SELECT 'ext_cor_view: original'::text AS col;
CREATE EXTENSION test_ext_cor;  -- fail
ERROR:  view ext_cor_view is not a member of extension "test_ext_cor"
DETAIL:  An extension is not allowed to replace an object that it does not own.
CONTEXT:  SQL statement "CREATE OR REPLACE VIEW ext_cor_view AS
  SELECT 'ext_cor_view: from extension'::text AS col"
extension script file "test_ext_cor--1.0.sql", near line 11
SELECT ext_cor_func();
ERROR:  function ext_cor_func() does not exist
LINE 1: SELECT ext_cor_func();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM ext_cor_view;
          col           
------------------------
 ext_cor_view: original
(1 row)

DROP VIEW ext_cor_view;
CREATE TYPE test_ext_type;
CREATE EXTENSION test_ext_cor;  -- fail
ERROR:  type test_ext_type is not a member of extension "test_ext_cor"
DETAIL:  An extension is not allowed to replace an object that it does not own.
CONTEXT:  SQL statement "CREATE TYPE test_ext_type AS ENUM('x', 'y')"
extension script file "test_ext_cor--1.0.sql", near line 17
DROP TYPE test_ext_type;
-- this makes a shell "point <<@@ polygon" operator too
CREATE OPERATOR @@>> ( PROCEDURE = poly_contain_pt,
  LEFTARG = polygon, RIGHTARG = point,
  COMMUTATOR = <<@@ );
CREATE EXTENSION test_ext_cor;  -- fail
ERROR:  operator <<@@(point,polygon) is not a member of extension "test_ext_cor"
DETAIL:  An extension is not allowed to replace an object that it does not own.
CONTEXT:  SQL statement "CREATE OPERATOR <<@@ ( PROCEDURE = pt_contained_poly,
  LEFTARG = point, RIGHTARG = polygon )"
extension script file "test_ext_cor--1.0.sql", near line 19
DROP OPERATOR <<@@ (point, polygon);
CREATE EXTENSION test_ext_cor;  -- now it should work
SELECT ext_cor_func();
         ext_cor_func         
------------------------------
 ext_cor_func: from extension
(1 row)

SELECT * FROM ext_cor_view;
             col              
------------------------------
 ext_cor_view: from extension
(1 row)

SELECT 'x'::test_ext_type;
 test_ext_type 
---------------
 x
(1 row)

SELECT point(0,0) <<@@ polygon(circle(point(0,0),1));
 ?column? 
----------
 t
(1 row)

\dx+ test_ext_cor
Objects in extension "test_ext_cor"
      Object description      
------------------------------
 function ext_cor_func()
 operator <<@@(point,polygon)
 type ext_cor_view
 type ext_cor_view[]
 type test_ext_type
 type test_ext_type[]
 view ext_cor_view
(7 rows)

--
-- CREATE IF NOT EXISTS is an entirely unsound thing for an extension
-- to be doing, but let's at least plug the major security hole in it.
--
CREATE COLLATION ext_cine_coll
  ( LC_COLLATE = "C", LC_CTYPE = "C" );
CREATE EXTENSION test_ext_cine;  -- fail
ERROR:  collation ext_cine_coll is not a member of extension "test_ext_cine"
DETAIL:  An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
CONTEXT:  SQL statement "CREATE COLLATION IF NOT EXISTS ext_cine_coll
  ( LC_COLLATE = "POSIX", LC_CTYPE = "POSIX" )"
extension script file "test_ext_cine--1.0.sql", near line 10
DROP COLLATION ext_cine_coll;
CREATE MATERIALIZED VIEW ext_cine_mv AS SELECT 11 AS f1;
CREATE EXTENSION test_ext_cine;  -- fail
ERROR:  materialized view ext_cine_mv is not a member of extension "test_ext_cine"
DETAIL:  An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
CONTEXT:  SQL statement "CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1"
extension script file "test_ext_cine--1.0.sql", near line 13
DROP MATERIALIZED VIEW ext_cine_mv;
CREATE FOREIGN DATA WRAPPER dummy;
CREATE SERVER ext_cine_srv FOREIGN DATA WRAPPER dummy;
CREATE EXTENSION test_ext_cine;  -- fail
ERROR:  server ext_cine_srv is not a member of extension "test_ext_cine"
DETAIL:  An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
CONTEXT:  SQL statement "CREATE SERVER IF NOT EXISTS ext_cine_srv FOREIGN DATA WRAPPER ext_cine_fdw"
extension script file "test_ext_cine--1.0.sql", near line 17
DROP SERVER ext_cine_srv;
CREATE SCHEMA ext_cine_schema;
CREATE EXTENSION test_ext_cine;  -- fail
ERROR:  schema ext_cine_schema is not a member of extension "test_ext_cine"
DETAIL:  An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
CONTEXT:  SQL statement "CREATE SCHEMA IF NOT EXISTS ext_cine_schema"
extension script file "test_ext_cine--1.0.sql", near line 19
DROP SCHEMA ext_cine_schema;
CREATE SEQUENCE ext_cine_seq;
CREATE EXTENSION test_ext_cine;  -- fail
ERROR:  sequence ext_cine_seq is not a member of extension "test_ext_cine"
DETAIL:  An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
CONTEXT:  SQL statement "CREATE SEQUENCE IF NOT EXISTS ext_cine_seq"
extension script file "test_ext_cine--1.0.sql", near line 21
DROP SEQUENCE ext_cine_seq;
CREATE TABLE ext_cine_tab1 (x int);
CREATE EXTENSION test_ext_cine;  -- fail
ERROR:  table ext_cine_tab1 is not a member of extension "test_ext_cine"
DETAIL:  An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
CONTEXT:  SQL statement "CREATE TABLE IF NOT EXISTS ext_cine_tab1 (x int)"
extension script file "test_ext_cine--1.0.sql", near line 23
DROP TABLE ext_cine_tab1;
CREATE TABLE ext_cine_tab2 AS SELECT 42 AS y;
CREATE EXTENSION test_ext_cine;  -- fail
ERROR:  table ext_cine_tab2 is not a member of extension "test_ext_cine"
DETAIL:  An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
CONTEXT:  SQL statement "CREATE TABLE IF NOT EXISTS ext_cine_tab2 AS SELECT 42 AS y"
extension script file "test_ext_cine--1.0.sql", near line 25
DROP TABLE ext_cine_tab2;
CREATE EXTENSION test_ext_cine;
\dx+ test_ext_cine
Objects in extension "test_ext_cine"
        Object description         
-----------------------------------
 collation ext_cine_coll
 foreign-data wrapper ext_cine_fdw
 materialized view ext_cine_mv
 schema ext_cine_schema
 sequence ext_cine_seq
 server ext_cine_srv
 table ext_cine_tab1
 table ext_cine_tab2
 type ext_cine_mv
 type ext_cine_mv[]
 type ext_cine_tab1
 type ext_cine_tab1[]
 type ext_cine_tab2
 type ext_cine_tab2[]
(14 rows)

ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
\dx+ test_ext_cine
Objects in extension "test_ext_cine"
        Object description         
-----------------------------------
 collation ext_cine_coll
 foreign-data wrapper ext_cine_fdw
 materialized view ext_cine_mv
 schema ext_cine_schema
 sequence ext_cine_seq
 server ext_cine_srv
 table ext_cine_tab1
 table ext_cine_tab2
 table ext_cine_tab3
 type ext_cine_mv
 type ext_cine_mv[]
 type ext_cine_tab1
 type ext_cine_tab1[]
 type ext_cine_tab2
 type ext_cine_tab2[]
 type ext_cine_tab3
 type ext_cine_tab3[]
(17 rows)

--
-- Test @extschema@ syntax.
--
CREATE SCHEMA "has space";
CREATE EXTENSION test_ext_extschema SCHEMA has$dollar;
ERROR:  invalid character in extension "test_ext_extschema" schema: must not contain any of ""$'\"
CREATE EXTENSION test_ext_extschema SCHEMA "has space";
--
-- Test basic SET SCHEMA handling.
--
CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE EXTENSION test_ext_set_schema SCHEMA s1;
ALTER EXTENSION test_ext_set_schema SET SCHEMA s2;
\dx+ test_ext_set_schema
      Objects in extension "test_ext_set_schema"
                  Object description                   
-------------------------------------------------------
 cast from s2.ess_range_type to s2.ess_multirange_type
 function s2.ess_func(integer)
 function s2.ess_multirange_type()
 function s2.ess_multirange_type(s2.ess_range_type)
 function s2.ess_multirange_type(s2.ess_range_type[])
 function s2.ess_range_type(text,text)
 function s2.ess_range_type(text,text,text)
 table s2.ess_table
 type s2.ess_composite_type
 type s2.ess_composite_type[]
 type s2.ess_multirange_type
 type s2.ess_multirange_type[]
 type s2.ess_range_type
 type s2.ess_range_type[]
 type s2.ess_table
 type s2.ess_table[]
(16 rows)

\sf s2.ess_func(int)
CREATE OR REPLACE FUNCTION s2.ess_func(integer)
 RETURNS text
 LANGUAGE sql
BEGIN ATOMIC
 SELECT ess_table.f3
    FROM s2.ess_table
   WHERE (ess_table.f1 = $1);
END
--
-- Test extension with objects outside the extension's schema.
--
CREATE SCHEMA test_func_dep1;
CREATE SCHEMA test_func_dep2;
CREATE SCHEMA test_func_dep3;
CREATE EXTENSION test_ext_req_schema1 SCHEMA test_func_dep1;
ALTER FUNCTION test_func_dep1.dep_req1() SET SCHEMA test_func_dep2;
SELECT pg_describe_object(classid, objid, objsubid) as obj,
       pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
       deptype
  FROM pg_depend
  WHERE classid = 'pg_extension'::regclass AND
        objid = (SELECT oid FROM pg_extension WHERE extname = 'test_ext_req_schema1')
  ORDER BY 1, 2;
              obj               |        objref         | deptype 
--------------------------------+-----------------------+---------
 extension test_ext_req_schema1 | schema test_func_dep1 | n
(1 row)

-- fails, as function dep_req1 is not in the same schema as the extension.
ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_func_dep3;
ERROR:  extension "test_ext_req_schema1" does not support SET SCHEMA
DETAIL:  function test_func_dep2.dep_req1() is not in the extension's schema "test_func_dep1"
-- Move back the function, and the extension can be moved.
ALTER FUNCTION test_func_dep2.dep_req1() SET SCHEMA test_func_dep1;
ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_func_dep3;
SELECT pg_describe_object(classid, objid, objsubid) as obj,
       pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
       deptype
  FROM pg_depend
  WHERE classid = 'pg_extension'::regclass AND
        objid = (SELECT oid FROM pg_extension WHERE extname = 'test_ext_req_schema1')
  ORDER BY 1, 2;
              obj               |        objref         | deptype 
--------------------------------+-----------------------+---------
 extension test_ext_req_schema1 | schema test_func_dep3 | n
(1 row)

DROP EXTENSION test_ext_req_schema1 CASCADE;
DROP SCHEMA test_func_dep1;
DROP SCHEMA test_func_dep2;
DROP SCHEMA test_func_dep3;
--
-- Test @extschema:extname@ syntax and no_relocate option
--
CREATE EXTENSION test_ext_req_schema1 SCHEMA has$dollar;
CREATE EXTENSION test_ext_req_schema3 CASCADE;
NOTICE:  installing required extension "test_ext_req_schema2"
ERROR:  invalid character in extension "test_ext_req_schema1" schema: must not contain any of ""$'\"
DROP EXTENSION test_ext_req_schema1;
CREATE SCHEMA test_s_dep;
CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
CREATE EXTENSION test_ext_req_schema3 CASCADE;
NOTICE:  installing required extension "test_ext_req_schema2"
SELECT test_s_dep.dep_req1();
 dep_req1 
----------
 req1
(1 row)

SELECT dep_req2();
 dep_req2  
-----------
 req1 req2
(1 row)

SELECT dep_req3();
 dep_req3  
-----------
 req1 req3
(1 row)

SELECT dep_req3b();
    dep_req3b    
-----------------
 req1 req2 req3b
(1 row)

CREATE SCHEMA test_s_dep2;
ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;  -- fails
ERROR:  cannot SET SCHEMA of extension "test_ext_req_schema1" because other extensions prevent it
DETAIL:  Extension "test_ext_req_schema3" requests no relocation of extension "test_ext_req_schema1".
ALTER EXTENSION test_ext_req_schema2 SET SCHEMA test_s_dep;  -- allowed
SELECT test_s_dep.dep_req1();
 dep_req1 
----------
 req1
(1 row)

SELECT test_s_dep.dep_req2();
 dep_req2  
-----------
 req1 req2
(1 row)

SELECT dep_req3();
 dep_req3  
-----------
 req1 req3
(1 row)

SELECT dep_req3b();  -- fails
ERROR:  function public.dep_req2() does not exist
LINE 1:  SELECT public.dep_req2() || ' req3b' 
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:   SELECT public.dep_req2() || ' req3b' 
CONTEXT:  SQL function "dep_req3b" statement 1
DROP EXTENSION test_ext_req_schema3;
ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;  -- now ok
SELECT test_s_dep2.dep_req1();
 dep_req1 
----------
 req1
(1 row)

SELECT test_s_dep.dep_req2();
 dep_req2  
-----------
 req1 req2
(1 row)

DROP EXTENSION test_ext_req_schema1 CASCADE;
NOTICE:  drop cascades to extension test_ext_req_schema2