15 Lesson 15: Administration / Managing Extensions

Page 1: Administration / Managing Extensions

Copyright

© Postgres Professional, 2016–2026 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)

Use of Course Materials

Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials.

Feedback

Please send your feedback, comments and suggestions to: edu@postgrespro.ru

Disclaimer

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications.

Page 2: Topics

• Extensions in PostgreSQL
• Creation and Management of Extensions
• Update of Extensions
• Features of the pg_dump

Page 3: Extensibility of PostgreSQL

Features

• functions and programming languages
• data types, operators, access methods
• foreign data wrappers (FDW)
• Mechanisms
  • modifiable system catalog
  • API for external handlers
  • loading and execution of user code

Speaker notes

Extensibility is the the core feature of PostgreSQL. It allows to add new functionality on the fly without changing the server code.

Thus, we can add programming languages and develop functions using them, define new types of data and operators to work with them, create new access methods for data types, develop foreign data wrappers to connect to external sources.

To make this possible, the PostgreSQL system catalog stores a large amount of information about database objects. This information is not hardcoded in the server source code. Users can modify the contents of the system catalog tables, thereby adding new objects and related functionality.

In addition, PostgreSQL source code has a large number of hooks and various APIs for connecting user functions. This enables the development of extensions such as pg_stat_statements, auto_explain, pldebugger, and many others.

The final key mechanism is the ability to load user code into server processes. For example, we can develop a shared library and incorporate it during operation.

https://postgrespro.com/docs/postgresql/16/extend-how

Note that the execution of poorly written user code by server processes can lead to catastrophic consequences. Only verified code from reliable sources should be trusted.

Page 4: Extensions

Group of related database objects

• creating all objects with a single command
• individual objects cannot be deleted
• pg_dump maintains relationships between objects
• tools for updating to a new version
• Sources
  • core distribution (contrib)
  • external extensions
  • ability to create your own extensions

Speaker notes

There are situations when several database objects are logically related. For example, several data types, functions and operators, operator classes. Such a relationship can be made explicit using the extension mechanism.

This makes it easier to manage objects:

● All objects are created with a single command.

● Individual objects cannot be deleted, an extension can only be deleted completely.

● The relationships between objects are also maintained when creating a backup with the pg_dump utility.

● There are tools for managing extension versions.

PostgreSQL includes a significant number of useful extensions, some of which we have already used.

https://postgrespro.com/docs/postgresql/16/contrib

https://postgrespro.com/docs/postgresql/16/contrib-prog

Another source is PostgreSQL Extension Network (PGXN), an extension

network similar to CPAN for Perl: https://pgxn.org/

Extensions can also be distributed in other ways, including via package repositories of OS distributions.

Page 5: Creating an Extension

{#source-figure-005 caption=“Source slide page 5: Creating an Extension”}

Speaker notes

The extension is installed into the database using the CREATE EXTENSION command. This requires two files:

● Control file “name.control” with extension parameters

● Script for creating extension objects “name-version.sql”

The version is typically in the format “1.0,” “1.1,” etc., but this is not necessary: it can consist of any characters (but it should not contain “–” and begin or end with “-”).

The version is usually not specified in the CREATE EXTENSION command, since the current version is set in the control file (the default_version parameter) and is used by default.

Other parameters in the control file specify whether the extension’s objects can be relocated between schemas (relocatable), list dependencies on other extensions (requires), restrict installation to superusers only (superuser), etc.

https://postgrespro.com/docs/postgresql/16/extend-extensions

https://postgrespro.com/docs/postgresql/16/sql-createextension

The extension is removed by the DROP EXTENSION command. A separate script for deleting objects is not required.

https://postgrespro.com/docs/postgresql/16/sql-dropextension

Page 6: Installation of the Extension

The source code files for the UOM (Units of Measurement) training extension are located in the uom directory within the student home directory:

student$ ls -l /home/student/uom

total 28
-rw-rw-r-- 1 student student  163 Jun 11 16:42 Makefile
-rw-rw-r-- 1 student student  725 Jun 11 16:42 README.md
-rw-rw-r-- 1 student student 1571 Jun 11 16:42 uom--1.0--1.1.sql
-rw-rw-r-- 1 student student  711 Jun 11 16:42 uom--1.0.sql
-rw-rw-r-- 1 student student  592 Jun 11 16:42 uom--1.1--1.2.sql
-rw-rw-r-- 1 student student 2392 Jun 11 16:42 uom--1.2.sql
-rw-rw-r-- 1 student student   93 Jun 11 16:42 uom.control

There is a control file with settings among the files:

student$ cat /home/student/uom/uom.control

default_version = '1.2'
relocatable = true
encoding = UTF8
comment = 'Units of Measurement'

default_version defines the default version; without this parameter, the version should be specified explicitly. relocatable indicates that the extension can be moved between schemas (we will discuss this later). encoding is required if non-ASCII characters are used. comment defines a comment for the extension. These are not all possible parameters, you can find the complete list in the documentation. Files shared across all database clusters of the same PostgreSQL version are located in the SHAREDIR directory:

student$ pg_config --sharedir

/usr/share/postgresql/16

In particular, files for installed extensions are stored in the SHAREDIR/extension directory. For example, the pg_buffercache extension files:

student$ ls $(pg_config --sharedir)/extension/pg_buffercache*

/usr/share/postgresql/16/extension/pg_buffercache--1.0--1.1.sql
/usr/share/postgresql/16/extension/pg_buffercache--1.1--1.2.sql
/usr/share/postgresql/16/extension/pg_buffercache--1.2--1.3.sql
/usr/share/postgresql/16/extension/pg_buffercache--1.2.sql
/usr/share/postgresql/16/extension/pg_buffercache--1.3--1.4.sql
/usr/share/postgresql/16/extension/pg_buffercache.control

Extensions are typically installed using the make utility. If an extension requires compilation and PostgreSQL was installed from a package, you may also need to install a development package, which typically has dev in its name, such as postgresql-server-dev-16. The package includes everything necessary for building, including the PostgreSQL header files. Since our extension contains only SQL functions and does not require compilation or building, the Makefile only needs to copy the extension files to SHAREDIR/extension:

student$ cat /home/student/uom/Makefile

EXTENSION = uom
DATA = uom--1.0.sql uom--1.2.sql uom--1.0--1.1.sql  uom--1.1--1.2.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

The EXTENSION variable specifies the extension name. The DATA variable lists the files to copy to SHAREDIR (excluding the control file). The last lines remain unchanged, they include the PGXS infrastructure for extension installation. The pg_config utility must be available, otherwise the PostgreSQL installation paths cannot be determined. Install the extension by running make install in its directory:

student$ sudo make install -C /home/student/uom

make: Entering directory '/home/student/uom'
/bin/mkdir -p '/usr/share/postgresql/16/extension'

Page 7: Page 7

/bin/mkdir -p '/usr/share/postgresql/16/extension'
/usr/bin/install -c -m 644 .//uom.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//uom--1.0.sql .//uom--1.2.sql .//uom--1.0--1.1.sql
.//uom--1.1--1.2.sql  '/usr/share/postgresql/16/extension/'
make: Leaving directory '/home/student/uom'

Extension installation is typically performed by a superuser whose environment may not include the PostgreSQL executable path. In this case, specify the pg_config location explicitly:

student$ sudo make install -C /home/student/uom PG_CONFIG=/usr/lib/postgresql/16/bin/pg_config

The list of extensions installed on the server and available for loading into the cluster databases is visible in the system catalog:

=> SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name;

        name        | default_version | installed_version
--------------------+-----------------+-------------------
 adminpack          | 2.1             |
 amcheck            | 1.3             |
 autoinc            | 1.0             |
 bloom              | 1.0             |
 btree_gin          | 1.3             |
 btree_gist         | 1.7             |
 citext             | 1.6             |
 cube               | 1.5             |
 dblink             | 1.2             |
 dict_int           | 1.0             |
 dict_xsyn          | 1.0             |
 earthdistance      | 1.1             |
 file_fdw           | 1.0             |
 fuzzystrmatch      | 1.2             |
 hstore             | 1.8             |
 insert_username    | 1.0             |
 intagg             | 1.1             |
 intarray           | 1.5             |
 isn                | 1.2             |
 lo                 | 1.1             |
 ltree              | 1.2             |
 moddatetime        | 1.0             |
 old_snapshot       | 1.0             |
 pageinspect        | 1.12            |
 pg_buffercache     | 1.4             |
 pg_freespacemap    | 1.2             |
 pg_prewarm         | 1.2             |
 pg_stat_statements | 1.10            |
 pg_surgery         | 1.0             |
 pg_trgm            | 1.6             |
 pg_visibility      | 1.2             |
 pg_wait_sampling   | 1.1             |
 pg_walinspect      | 1.1             |
 pgcrypto           | 1.3             |
 pgrowlocks         | 1.2             |
 pgstattuple        | 1.5             |
 plpgsql            | 1.0             | 1.0
 postgres_fdw       | 1.1             |
 refint             | 1.0             |
 seg                | 1.4             |
 sslinfo            | 1.2             |
 tablefunc          | 1.0             |
 tcn                | 1.0             |
 tsm_system_rows    | 1.0             |
 tsm_system_time    | 1.0             |
 unaccent           | 1.1             |
 uom                | 1.2             |
 uuid-ossp          | 1.1             |
 xml2               | 1.1             |
(49 rows)

Standard databases have only the plpgsql extension installed. Here is the list of versions for our extension:

=> SELECT name, version, installed
FROM pg_available_extension_versions
WHERE name = 'uom'
ORDER BY version;

Page 8: Page 8

 name | version | installed
------+---------+-----------
 uom  | 1.0     | f
 uom  | 1.1     | f
 uom  | 1.2     | f
(3 rows)

In PostgreSQL, extension versions are treated as names, not numbers. In other words, 1.1 and 1.2 are simply two different versions, their order is not defined. Let’s load version 1.0 of the uom extension into a new database:

=> CREATE DATABASE admin_extensions;

CREATE DATABASE

=> \c admin_extensions

You are now connected to database "admin_extensions" as user "student".

=> CREATE EXTENSION uom VERSION '1.0';

CREATE EXTENSION

=> \dx

                    List of installed extensions
  Name   | Version |   Schema   |            Description
---------+---------+------------+------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 uom     | 1.0     | public     | Units of Measurement. uom--1.0.sql
(2 rows)

The extension appears in the database. It contains a table and a function:

=> \dx+ uom

     Objects in extension "uom"
         Object description
-------------------------------------
 function uom2uom(numeric,text,text)
 table uom_ref
(2 rows)

The objects are installed in the schema where they would be created by default, in this case, public. We could explicitly specify the schema when creating the extension:

CREATE EXTENSION uom SCHEMA public;

The function converts length values between units:

=> SELECT uom2uom(1, 'inch', 'cm') AS "Centimeters in inch";

 Centimeters in inch
---------------------
  2.5400000000000000
(1 row)

The table stores a list of supported units of measurement:

=> SELECT * FROM uom_ref;

 name |   k
------+--------
 m    |      1
 km   |   1000
 cm   |   0.01
 foot | 0.3048
 inch | 0.0254
(5 rows)

The extension objects were created by the SQL script for version 1.0:

student$ cat /usr/share/postgresql/16/extension/uom--1.0.sql

\echo Use "CREATE EXTENSION uom" to load this file. \quit

COMMENT ON EXTENSION uom IS 'Units of Measurement. uom--1.0.sql';

-- Reference of measurement units

Page 9: Page 9

CREATE TABLE uom_ref (
    name       text,
    k          numeric
);
INSERT INTO uom_ref VALUES ('m',1), ('km',1000), ('cm',0.01),
    ('foot',0.3048), ('inch',0.0254);

-- Convert length from one unit to another
CREATE FUNCTION uom2uom (value numeric, name_from text, name_to text) RETURNS numeric
LANGUAGE sql
RETURN uom2uom.value *
          (SELECT k FROM uom_ref WHERE name = uom2uom.name_from) /
          (SELECT k FROM uom_ref WHERE name = uom2uom.name_to);

-- Required to execute the function by any user
GRANT SELECT ON uom_ref TO public;

The first line prevents accidental manual execution of the script; the script should run only when CREATE EXTENSION is executed. When creating an extension, all commands run within a single transaction, which means that transaction control commands (as well as utility commands such as VACUUM) cannot be used here. The search_path will be set to the single schema where the extension objects are created. Objects belonging to an extension cannot be deleted directly:

=> DROP FUNCTION uom2uom(numeric,text,text);

ERROR:  cannot drop function uom2uom(numeric,text,text) because extension uom requires it
HINT:  You can drop extension uom instead.

However, the extension mechanism does not track changes made to the objects themselves. For example, adding a column to a table or altering a function will not cause an error. You should avoid making such changes to extension objects. The extension and its objects can be moved to another schema (relocatable=true):

=> CREATE SCHEMA util;

CREATE SCHEMA

=> ALTER EXTENSION uom SET SCHEMA util;

ALTER EXTENSION

=> \dx uom

                 List of installed extensions
 Name | Version | Schema |            Description
------+---------+--------+------------------------------------
 uom  | 1.0     | util   | Units of Measurement. uom--1.0.sql
(1 row)

=> \df util.uom*

                                    List of functions
 Schema |  Name   | Result data type |             Argument data types             | Type
--------+---------+------------------+---------------------------------------------+------
 util   | uom2uom | numeric          | value numeric, name_from text, name_to text | func
(1 row)

Return it to public:

=> ALTER EXTENSION uom SET SCHEMA public;

ALTER EXTENSION

Page 10: Extension Update

{#source-figure-010 caption=“Source slide page 10: Extension Update”}

Speaker notes

The extension version is updated using the ALTER EXTENSION UPDATE command. In this case, there must be an update script “name-old-version– new-version.sql” containing the commands necessary for updating.

We can also change the control file “name.control,” updating the current version and, possibly, other parameters.

If needed, there can also be a separate control file bound to the version. For example, if version 1.2 introduces a dependency on another extension, then it is better to indicate this dependency not in the main control file, but in an additional file for version 1.2. The parameters specified in the additional control file have priority over those in the main file.

The example on the slide shows update scripts 1.0→1.1 and 1.1→1.2. While a 1.0→1.2 script is possible, it is typically not required: the extension mechanism automatically determines the update path based on the available transitions between versions. For example, if version 1.0 is installed, it can be updated directly to version 1.2: the 1.0→1.1 script is automatically applied, followed by the 1.1→1.2 script.

Similar to creation, the target version is usually not specified during an update. In this case, the extension is updated to the latest version set in the main control file.

https://postgrespro.com/docs/postgresql/16/sql-alterextension

Page 11: Extension Versions and Update

Version 1.1 of the uom extension introduces a data type for length units, comparison operators for values of this type, and an operator class that enables indexing table columns of this type. The update file:

student$ cat /usr/share/postgresql/16/extension/uom--1.0--1.1.sql

\echo Use "ALTER EXTENSION uom UPDATE TO '1.1'" to load this file. \quit

COMMENT ON EXTENSION uom IS 'Units of Measurement. uom--1.0--1.1.sql';

-- New data type
CREATE TYPE uom AS (
    value numeric,
    name text
);

-- Comparison operators for the type
CREATE FUNCTION uom_cmp (a uom, b uom) RETURNS int LANGUAGE SQL
AS 'SELECT CASE WHEN a2b.value > b.value THEN  1
                WHEN a2b.value < b.value THEN -1
                ELSE 0 END
    FROM (SELECT uom2uom(a.value, a.name, b.name)) AS a2b(value)';

CREATE FUNCTION uom_lt(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) = -1' LANGUAGE sql;

CREATE FUNCTION uom_le(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) IN (-1,0)' LANGUAGE sql;

CREATE FUNCTION uom_eq(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) = 0' LANGUAGE sql;

CREATE FUNCTION uom_ge(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) IN (0,1)' LANGUAGE sql;

CREATE FUNCTION uom_gt(a uom, b uom) RETURNS boolean
AS 'SELECT uom_cmp(a, b) = 1' LANGUAGE sql;

CREATE OPERATOR <  (PROCEDURE = uom_lt, LEFTARG = uom, RIGHTARG = uom);
CREATE OPERATOR <= (PROCEDURE = uom_le, LEFTARG = uom, RIGHTARG = uom);
CREATE OPERATOR =  (PROCEDURE = uom_eq, LEFTARG = uom, RIGHTARG = uom);
CREATE OPERATOR >= (PROCEDURE = uom_ge, LEFTARG = uom, RIGHTARG = uom);
CREATE OPERATOR >  (PROCEDURE = uom_gt, LEFTARG = uom, RIGHTARG = uom);

CREATE OPERATOR CLASS uom_ops DEFAULT FOR TYPE uom USING btree AS
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,
    FUNCTION 1 uom_cmp(uom,uom);

When creating a new version of an extension, two scenarios are possible: The database contains a previous version that needs to be updated. There is no previous version, so the later version needs to be created directly. Make sure that we can update from version 1.0 to 1.1. List available update paths:

=> SELECT *
FROM pg_extension_update_paths('uom')
WHERE path IS NOT NULL
ORDER BY source, target;

 source | target |     path
--------+--------+---------------
 1.0    | 1.1    | 1.0--1.1
 1.0    | 1.2    | 1.0--1.1--1.2
 1.1    | 1.2    | 1.1--1.2
(3 rows)

Perform the update:

Page 12: Page 12

=> ALTER EXTENSION uom UPDATE TO '1.1';

ALTER EXTENSION

=> \dx uom

                   List of installed extensions
 Name | Version | Schema |               Description
------+---------+--------+-----------------------------------------
 uom  | 1.1     | public | Units of Measurement. uom--1.0--1.1.sql
(1 row)

Now, let’s create version 1.1, first removing the extension, which deletes all its objects:

=> DROP EXTENSION uom;

DROP EXTENSION

=> \df uom*

                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

Create version 1.1 directly in a separate schema, ensuring object visibility:

=> CREATE EXTENSION uom VERSION '1.1' SCHEMA util;

CREATE EXTENSION

=> SET search_path = public, util;

SET

=> \dx uom

                   List of installed extensions
 Name | Version | Schema |               Description
------+---------+--------+-----------------------------------------
 uom  | 1.1     | util   | Units of Measurement. uom--1.0--1.1.sql
(1 row)

To create version 1.1, the extension mechanism will first look for the uom–1.1.sql file, which does not exist. However, version 1.1 can be reached via a chain: first create version 1.0, then update to 1.1. This is the shortest path to version 1.1, and the only one in this case. Examine the uom data type. Comparison operators for length units are defined for it:

=> SELECT (5, 'm'   )::uom <  ( 10,'foot')::uom,
          (1, 'foot')::uom <= ( 12,'inch')::uom,
          (4, 'inch')::uom =  ( 10,'cm'  )::uom,
          (1, 'km'  )::uom >= (300,'foot')::uom,
          (4, 'inch')::uom >  ( 10,'cm'  )::uom;

 ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------
 f        | t        | f        | t        | t
(1 row)

The new type can be used as a column data type:

=> CREATE TABLE t (len uom);

CREATE TABLE

=> INSERT INTO t VALUES
    ((1,'km')::uom), ((3,'foot')::uom), ((17,'m')::uom), ((90,'inch')::uom);

INSERT 0 4

Data in the table can be sorted:

=> SELECT * FROM t ORDER BY len;

Page 13: Page 13

    len
-----------
 (3,foot)
 (90,inch)
 (17,m)
 (1,km)
(4 rows)

You can create a B-tree index:

=> CREATE INDEX ON t USING btree (len);

CREATE INDEX

If we disable sequential scans for the planner, it will use the new index when executing the query:

=> SET enable_seqscan TO off;

SET

=> EXPLAIN (costs off)
SELECT * FROM t ORDER BY len;

              QUERY PLAN
--------------------------------------
 Index Only Scan using t_len_idx on t
(1 row)

pg\_dump Utility

The pg_dump utility exports only the CREATE EXTENSION command, not the commands that create the extension’s objects. Therefore, to restore from a backup, all extensions in use must be installed on the server. In the next version 1.2 of the extension a column named predefined is added to the table. This column is used to identify rows that were inserted into the table not by the extension script, but after the extension was created. Update file:

student$ cat /usr/share/postgresql/16/extension/uom--1.1--1.2.sql

\echo Use "ALTER EXTENSION uom UPDATE TO '1.2'" to load this file. \quit

COMMENT ON EXTENSION uom IS 'Units of Measurement. uom--1.1--1.2.sql';

-- Add a flag indicating that the row has been inserted during extension install.
ALTER TABLE uom_ref ADD COLUMN predefined boolean;

UPDATE uom_ref SET predefined = true;

ALTER TABLE uom_ref
    ALTER COLUMN predefined SET NOT NULL,
    ALTER COLUMN predefined SET DEFAULT false;

-- If the reference table will be extended,
-- pg_dump should export the new rows.
SELECT pg_extension_config_dump('uom_ref'::regclass, 'WHERE NOT predefined');

The script calls the pg_extension_config_dump function so that the utility exports only the table rows that meet the specified condition. We cannot simply delete and recreate the extension to update to version 1.2. The len column in table t prevents this:

=> DROP EXTENSION uom;

ERROR:  cannot drop extension uom because other objects depend on it
DETAIL:  column len of table t depends on type uom
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

If we are not willing to delete the table (or the len column), we should perform an update instead:

=> ALTER EXTENSION uom UPDATE;

ALTER EXTENSION

When no version is specified, the extension is updated to the default version set in the control file:

=> \dx uom

Page 14: Page 14

                   List of installed extensions
 Name | Version | Schema |               Description
------+---------+--------+-----------------------------------------
 uom  | 1.2     | util   | Units of Measurement. uom--1.1--1.2.sql
(1 row)

Now, besides CREATE EXTENSION, the logical backup file will also include data from the uom_ref table. If you back up the entire database, all installed extensions are included, which is usually acceptable. However, if you specify a schema during the export, extensions installed in that schema are not included:

student$ pg_dump -d admin_extensions --schema util | grep 'CREATE'

CREATE SCHEMA util;

Restoring the contents of the uom_ref table from such a backup will be impossible. To export specific extensions, specify their names using the –extension parameter. For this purpose, we should manage dependencies: if we export only the util schema in our case, the table public.t will not be included in the backup copy, despite its dependence on the extension:

student$ pg_dump -d admin_extensions --schema util --extension uom | grep 'CREATE'

CREATE SCHEMA util;
CREATE EXTENSION IF NOT EXISTS uom WITH SCHEMA util;

When an extension is created, the privileges on its objects are stored in the system catalog. If privileges have changed by the time the export is performed, pg_dump will add commands to the backup to set up the new privileges.

Page 15: Takeaways

Extensibility is one of the most important features of PostgreSQL

• An extension is a package of related database objects
• The extension mechanism includes tools for version updates and pg_dump support

Page 16: Practice

1. Install the uom extension and check if it appears in the list of available extensions.

• 2. Create the uom extension without specifying a version. Check the version and the scripts that were executed.
• 3. Add mile and yard to the reference table.
• 4. Change access to the reference table: the SELECT privilege should be granted only to a specific role, not to all users.
• 5. Check how the pg_dump exports extension objects: table, type, functions and operators, table contents, access privileges.

Speaker notes

1. Extension files are located in the uom subdirectory of the student home directory. The installation process is similar to the one used in the demonstration.

2. When adding the data, ensure that the value for new entries in the “predefined” column is set to false. Conversion factors:

1 mile = 1609.344 m 1 yard = 0.9144 m

Page 17: 1. Installation of the Extension

Let’s install the extension files:

student$ sudo make install -C /home/student/uom

make: Entering directory '/home/student/uom'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/usr/bin/install -c -m 644 .//uom.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//uom--1.0.sql .//uom--1.2.sql .//uom--1.0--1.1.sql
.//uom--1.1--1.2.sql  '/usr/share/postgresql/16/extension/'
make: Leaving directory '/home/student/uom'

Check that the extension is available for loading in the database:

=> SELECT *
FROM pg_available_extensions
WHERE name = 'uom';

 name | default_version | installed_version |       comment
------+-----------------+-------------------+----------------------
 uom  | 1.2             |                   | Units of Measurement
(1 row)

2. Loading the Extension into the Database

Load the uom extension into a new database:

=> CREATE DATABASE admin_extensions;

CREATE DATABASE

=> \c admin_extensions

You are now connected to database "admin_extensions" as user "student".

=> CREATE EXTENSION uom;

CREATE EXTENSION

The extension version matches the default_version parameter value:

=> \dx uom

                 List of installed extensions
 Name | Version | Schema |            Description
------+---------+--------+------------------------------------
 uom  | 1.2     | public | Units of Measurement. uom--1.2.sql
(1 row)

Since the uom–1.2.sql file exists for version 1.2, it is used to create the extension. For users updating from version 1.1 or 1.0, corresponding update paths are available.

3. Extending the Reference Table

Let’s add mile and yard. The predefined column is populated with the default value (false):

=> INSERT INTO uom_ref VALUES ('mile', 1609.344);

INSERT 0 1

=> INSERT INTO uom_ref VALUES ('yard', 0.9144);

INSERT 0 1

How many yards are in a mile?

=> SELECT uom2uom(1, 'mile', 'yard');

        uom2uom
-----------------------
 1760.0000000000000000
(1 row)

Page 18: 4. Changing Access Privileges

Create a dedicated role to access the uom_ref table:

=> CREATE ROLE util;

CREATE ROLE

Only the new role can read from the table:

=> GRANT SELECT ON uom_ref TO util;

GRANT

=> REVOKE SELECT ON uom_ref FROM public;

REVOKE

5. pg\_dump and Extension Objects

The list of tables to be exported by pg_dump is stored in pg_extension:

=> SELECT extname, extconfig::regclass[], extcondition
FROM pg_extension
WHERE extname = 'uom';

 extname | extconfig |       extcondition
---------+-----------+--------------------------
 uom     | {uom_ref} | {"WHERE NOT predefined"}
(1 row)

Run pg_dump:

student$ pg_dump -d admin_extensions

--
-- PostgreSQL database dump
--

-- Dumped from database version 16.2 (Ubuntu 16.2-2.pgdg24.04+1)
-- Dumped by pg_dump version 16.2 (Ubuntu 16.2-2.pgdg24.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: uom; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS uom WITH SCHEMA public;

--
-- Name: EXTENSION uom; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION uom IS 'Units of Measurement. uom--1.2.sql';

--
-- Data for Name: uom_ref; Type: TABLE DATA; Schema: public; Owner: student
--

COPY public.uom_ref (name, k, predefined) FROM stdin;
mile
1609.344
f
yard
0.9144 f
\.

--
-- Name: TABLE uom_ref; Type: ACL; Schema: public; Owner: student

Page 19: Page 19

--

REVOKE SELECT ON TABLE public.uom_ref FROM PUBLIC;
GRANT SELECT ON TABLE public.uom_ref TO util;

--
-- PostgreSQL database dump complete
--

Examine how pg_dump exports extension objects: The table, type, functions, and operators are not exported. They will be created by the CREATE EXTENSION command. The system where the restore is performed must have the same version of the extension that was during the export. The contents of newly added table rows are exported using a COPY command. Access privileges for the uom_ref table are generated as they were at the moment pg_dump was run. To properly export access privileges, the system catalog stores information about object privileges granted by extension creation scripts:

=> SELECT objoid::regclass, initprivs
FROM pg_init_privs
WHERE privtype = 'e';

 objoid  |              initprivs
---------+--------------------------------------
 uom_ref | {student=arwdDxt/student,=r/student}
(1 row)

Without this information, it would be impossible to generate the REVOKE command.