Extending the Database – the Extension Ecosystem

Extensions are a powerful way of packaging together related database objects, such as functions, routines, and tables, making the management of the objects as a single unit easier. Extensions allow you and other developers to extend the already rich PostgreSQL set of features by providing a clear, concise, and accurate way of installing, upgrading, and removing features and objects. In this chapter, you will see what extensions are and how they can be installed, upgraded, or removed with different tools. Moreover, you will learn how to build your own extension from scratch so that you will be immediately productive in packaging your own scripts and tools to distribute across other databases and PostgreSQL instances.

The chapter consists of the following topics:

• Introducing extensions
• Managing extensions
• Exploring the PGXN client
• Installing extensions
• Creating your own extension

...

Technical requirements

The chapter examples can be run on the chapter_12 Docker image that you can find in the book’s GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL-Second-Edition. For installation and usage instructions for the Docker images for this book, please refer to Chapter 1, Introduction to PostgreSQL.

Introducing extensions

SQL is a declarative language that allows you to create and manipulate objects, as well as data. You can group SQL statements into scripts so that you can run the scripts in a more predictable and reproducible way. However, such scripts are seen by PostgreSQL as a sequence of unrelated commands, that is, you are responsible for correlating such commands into appropriate scripts. Things get even worse when you have to deal with foreign programming languages (e.g., PL/Perl and other not SQL-based languages) or binary libraries; the cluster knows nothing about your aims or how the objects are related to each other. Luckily, extensions help you get order out of chaos.

An extension is a packaged set of files that can be installed in the cluster in order to provide more functionalities, that is, to “extend” the current cluster set of features. Unlike scripts, extensions are managed strictly through specific commands that install, deploy, load, and...

Managing extensions

Every extension is managed at a database level, meaning that every database that needs an extension must manage such an extension life cycle. In other words, there is no per-cluster way of managing an extension and applying it to every database within the cluster.

Extensions are mainly managed by three SQL statements: CREATE EXTENSION, DROP EXTENSION, and ALTER EXTENSION, to respectively install an extension in a database, remove the extension from the database, and modify extension attributes or upgrade them.

Every extension is specified by a mnemonic and a version; if a version is not specified, PostgreSQL assumes you want to deal with the latest available version or the one that is already installed.

In the following subsections, each of the three management statements will be explained.

Creating an extension

The CREATE EXTENSION statement allows you to install an existing extension in the current database.

The synopsis of the statement...

Exploring the PGXN client

The PGXN client is an external application, written in Python, that works as a command-line interface for PGXN. The application, named pgxnclient, works by means of commands, which are actions such as install, download, uninstall, and so on, allowing a database administrator to interact with PGXN and extensions.

To some extent, pgxnclient works the same as the cpan (or cpanm) command for Perl, zef for Raku, pip for Python, and so on.

Being an external application means that pgxnclient is not distributed with PostgreSQL, and therefore you need to install it on your machine before you can use it. Installing pgxnclient is not mandatory in order to use PostgreSQL extensions, but it can make your life a lot easier.

In the following subsections, you will see how to install pgxnclient on main Unix and Unix-like operating systems, but before that, it is important to let you know that, once it is installed, you will find two executables on...

Installing extensions

Usually, the workflow for getting an extension up and running involves a few steps. First, you need to find out which extension to use, which version, and the compatibility with your cluster. Once you have found out the extension you need, you have to install it in the cluster.

Installing it in the cluster really means deploying it in the PostgreSQL directories, that is, moving all the extension-related files and libraries into the shared directory of the cluster so that PostgreSQL can seek the code required to run the extension.

Lastly, you need to create the extension in every single database that needs it. Creating an extension is like enabling the usage of the extension within a specific database.

In order to demonstrate the usage of an extension, we will install orafce, the Oracle compatibility functions extension. Describing the whole extension is not the aim of this section, so let’s just say that this extension provides a set of functions...

Creating your own extension

In this section, we will build an extension from scratch so that you will better understand how they are made up. The idea is to let you know how to convert even your own SQL scripts into an extension, with all the advantages that an extension can provide in terms of manageability.

Defining an example extension

In order to demonstrate how to build your own extension, we are going to create a simple set of capabilities that apply to the forum database, providing some more features. In particular, we are going to define an extension named tagext that will provide a utility function that, given a particular tag within the tag table, will return the full path to that tag with all ancestors.

For example, the Linux tag is a child of the Operating Systems tag, and therefore the path to the Linux tag is Operating System > Linux.

In particular, we want our extension to provide us with a function named tag_path that, given a tag, provides the...

Summary

This chapter has introduced you to the extension ecosystem, a very rich and powerful system to package-related objects and manage them as a single unit. Extensions provide a way to add new features to your cluster and your databases and most notably provide a clear and concise way of building updates and repeatable installations, therefore easing the distribution of the features to other clusters and databases.

PostgreSQL ships with useful extensions provided within the contrib package; these extensions are developed directly by the PostgreSQL developers and therefore are very well integrated with the current PostgreSQL version. On the other hand, the PGXN network provides third-party extensions that can improve your cluster with new functionalities.

Thanks to the PGXS building infrastructure, creating an extension from scratch is comprehensive and quite easy, while thanks to tools such as pgxnclient, managing a lot of extensions can be automated.

In the next chapter...

Verify your knowledge

• What is an extension?

  An extension is a collection of related database objects that can be installed, upgraded, or removed as a single unit. See the Introducing extensions section for more details.

• What is the pgxnclient command?

  pgxnclient is a command that eases the usage of the PGXN by downloading, installing, and removing extensions. See the Exploring the PGXN client section for more details.

• What is an extension control file?

  A control file is a text file that defines the main properties of an extension, like the name, the version, and the other dependencies. See the Extension components section for more details.

• How can you inspect which extensions have been created in a database?

  The special pg_extension catalog provides information about installed extensions; in psql, the special \dx command shows a summary of installed extensions. See the Viewing installed extensions section...

References

• PostgreSQL official documentation about extensions: https://www.postgresql.org/docs/current/extend-extensions.html
• PostgreSQL official documentation about the extension build system (PGXS): https://www.postgresql.org/docs/current/extend-pgxs.html
• The pgxnclient official repository: https://pypi.org/project/pgxnclient/
• The pgxnclient official documentation: https://pgxn.github.io/pgxnclient/
• PostgreSQL 11 Server Side Programming – Quick Start Guide, Packt Publishing

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/jYWCjF6Tku