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
(6 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
(2 rows)

-- 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 posint
(5 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 posint
(5 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 posint
(3 rows)

-- dropping it should still work
drop extension test_ext8;
-- 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:  PREPARE TRANSACTION is not yet supported in Apache Cloudberry
ROLLBACK;
-- Clean up
DROP TABLE test_ext4_tab;
DROP FUNCTION create_extension_with_temp_schema();
RESET client_min_messages;
\unset SHOW_CONTEXT
-- 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.
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.
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.
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.
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 test_ext_type
 view ext_cor_view
(4 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.
DROP COLLATION ext_cine_coll;
CREATE MATERIALIZED VIEW ext_cine_mv AS SELECT 11 AS f1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
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.
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.
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.
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.
DROP SEQUENCE ext_cine_seq;
CREATE TABLE ext_cine_tab1 (x int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
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.
DROP TABLE ext_cine_tab1;
CREATE TABLE ext_cine_tab2 AS SELECT 42 AS y;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
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.
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
(8 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
(9 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";