From a65911df1815df3b98c247446fa39c8dc70ffa9c Mon Sep 17 00:00:00 2001 From: Nikolay Shaplov Date: Tue, 3 Jun 2025 20:23:19 +0300 Subject: [PATCH] =?utf8?q?=D0=9C=D0=B5=D1=81=D1=82=D0=BE=20=D0=B4=D0=BB?= =?utf8?q?=D1=8F=20=D1=85=D1=80=D0=B0=D0=BD=D0=B5=D0=BD=D0=B8=D1=8F=20?= =?utf8?q?=D0=BF=D1=80=D0=B5=D0=B7=D0=B5=D0=BD=D1=82=D0=B0=D1=86=D0=B8?= =?utf8?q?=D0=B9?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../.placeholder | 0 .../code/log.log | 361 ++++++++++++++++++ .../code/my_first_extension/Makefile | 13 + .../expected/my_first_extension-main-test.out | 13 + .../my_first_extension--0.0.1--0.0.2.sql | 10 + .../my_first_extension--0.0.2--0.0.3.sql | 5 + .../my_first_extension--0.0.3.sql | 24 ++ .../my_first_extension/my_first_extension.c | 12 + .../my_first_extension.control | 8 + .../sql/my_first_extension-main-test.sql | 9 + 10 files changed, 455 insertions(+) create mode 100644 slides/2025 - Creating own extension/2025 - Creating own extension, Nepal/.placeholder create mode 100644 slides/2025 - Creating own extension/code/log.log create mode 100644 slides/2025 - Creating own extension/code/my_first_extension/Makefile create mode 100644 slides/2025 - Creating own extension/code/my_first_extension/expected/my_first_extension-main-test.out create mode 100644 slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.1--0.0.2.sql create mode 100644 slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.2--0.0.3.sql create mode 100644 slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.3.sql create mode 100644 slides/2025 - Creating own extension/code/my_first_extension/my_first_extension.c create mode 100644 slides/2025 - Creating own extension/code/my_first_extension/my_first_extension.control create mode 100644 slides/2025 - Creating own extension/code/my_first_extension/sql/my_first_extension-main-test.sql diff --git a/slides/2025 - Creating own extension/2025 - Creating own extension, Nepal/.placeholder b/slides/2025 - Creating own extension/2025 - Creating own extension, Nepal/.placeholder new file mode 100644 index 0000000..e69de29 diff --git a/slides/2025 - Creating own extension/code/log.log b/slides/2025 - Creating own extension/code/log.log new file mode 100644 index 0000000..5832a3c --- /dev/null +++ b/slides/2025 - Creating own extension/code/log.log @@ -0,0 +1,361 @@ +Extension is a set of PostgreSQL objects that can be `CREATE`ed. +Like `CREATE FUNCTION`, `CREATE TYPE` or `CREATE OPERATOR`. +This set is created, updated and deleted as a bundle. +When you do `CREATE EXTENSION` you it will create all object included into extension. +When you do `DROP EXTENSION` all that objects will be automatically removed. + +So extension is just a set of SQL commands to create or update extension schema. +(You even do not need to delete it, `DROP EXTENSION` will delete them automatically) + +Functions may be written in `PL/pgSQL` or other `PL/*` or you can load binary function from shared `.so` library. +. +You can register a function as a hook handler to change behavior if PostgreSQL. + + + +See https://github.com/IshaanAdarsh/Postgres-extension-tutorial/blob/main/SGML/Main.md for more info + +``` + +$ whoami +nataraj + +$ sudo -u postgres createuser -s nataraj +``` + +Make sure that `-dev` files for postgres are installed. In Debian-based distributives it wil +be like this + +``` +sudo apt-get install postgresql-server-dev-all + +``` + + +``` +$ mkdir my_first_extension + +$ cd my_first_extension +``` + +``` +touch my_first_extension.control +``` + +``` +# my_first_extension.control +# Comment line to provide additional information about the extension +comment = 'My Very First Extension' + +# Specifies the default version of the extension +default_version = '0.0.1' +``` + +https://www.postgresql.org/docs/17/extend-extensions.html#EXTEND-EXTENSIONS-FILES + + +--- + +``` +touch my_first_extension--0.0.1.sql +``` + +``` +CREATE TABLE my_first_extension_table ( + id SERIAL PRIMARY KEY, + my_string VARCHAR NOT NULL +); + +INSERT INTO my_first_extension_table (my_string) VALUES('Hello, World'); +``` + +--- + +``` +touch Makefile +``` + +``` +EXTENSION = my_first_extension # Name of the extension +DATA = my_first_extension--0.0.1.sql # SQL file containing extension objects and functions + +PG_CONFIG ?= pg_config # Path to the pg_config executable +PGXS := $(shell $(PG_CONFIG) --pgxs) # Get the PostgreSQL Extension Makefile from pg_config +include $(PGXS) # Include the PostgreSQL Extension Makefile +``` + +--- + +``` +make +sudo make install +``` + +``` +sudo -u postgres psql + +# CREATE EXTENSION my_first_extension; +CREATE EXTENSION + +# SELECT * FROM my_first_extension_table; + id | my_string +----+-------------- + 1 | Hello, World +(1 row) + +# DROP EXTENSION my_first_extension; +DROP EXTENSION + +# SELECT * FROM my_first_extension_table; +ERROR: relation "my_first_extension_table" does not exist +СТРОКА 1: SELECT * FROM my_first_extension_table; + ^ +``` + +------------ + +# Add tests + +``` +mkdir sql +``` + +edit `sql/my_first_extension-main-test.sql` + +``` +CREATE EXTENSION my_first_extension; + +SELECT * FROM my_first_extension_table; + +DROP EXTENSION + +-- There should be no my_first_extension_table anymore +SELECT * FROM my_first_extension_table; +``` + +---- + +change Makefile + +``` +EXTENSION = my_first_extension # Name of the extension +DATA = my_first_extension--0.0.1.sql # SQL file containing extension objects and functions +REGRESS = my_first_extension-main-test + + +PG_CONFIG ?= pg_config # Path to the pg_config executable +PGXS := $(shell $(PG_CONFIG) --pgxs) # Get the PostgreSQL Extension Makefile from pg_config +include $(PGXS) # Include the PostgreSQL Extension Makefile +``` + +run `make installcheck` + + +and get an error: `my_first_extension/expected/my_first_extension-main-test.out: No such file or directory` + +and a `results` dir. + +---- + +Check that `results/my_first_extension-main-test.out` is what you expect + +``` +$ cat results/my_first_extension-main-test.out +CREATE EXTENSION my_first_extension; +SELECT * FROM my_first_extension_table; + id | my_string +----+-------------- + 1 | Hello, World +(1 row) + +DROP EXTENSION +-- There should be no my_first_extension_table anymore +SELECT * FROM my_first_extension_table; +ERROR: syntax error at or near "SELECT" +LINE 3: SELECT * FROM my_first_extension_table; + ^ +``` + +---- + +If everything as you expected copy `my_first_extension-main-test.out` from `results` dir to `expected` + +``` +mkdir expected +cp results/my_first_extension-main-test.out expected +``` + +---- + +Now tests should pass + +``` +n$ make installcheck +echo "+++ regress install-check in +++" && /usr/lib/postgresql/15/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/15/bin' --dbname=contrib_regression my_first_extension-main-test ++++ regress install-check in +++ +(using postmaster on Unix socket, default port) +============== dropping database "contrib_regression" ============== +SET +DROP DATABASE +============== creating database "contrib_regression" ============== +CREATE DATABASE +ALTER DATABASE +ALTER DATABASE +ALTER DATABASE +ALTER DATABASE +ALTER DATABASE +ALTER DATABASE +============== running regression test queries ============== +test my_first_extension-main-test ... ok 54 ms + +===================== + All 1 tests passed. +===================== +``` + +----- + +Now let's create create a new version of extension, with a plpsql function in it + +* Change version from `0.0.1` to `0.0.2` in `my_first_extension.control`: + +``` +default_version = '0.0.2' +``` + +* rename schema creation file from `my_first_extension--0.0.1.sql` to `my_first_extension--0.0.2.sql` +* create an schema update file `my_first_extension--0.0.1--0.0.2.sql` + +Add to both of them function creation code + +``` +CREATE FUNCTION my_one() +returns int +language plpgsql +as +$$ +begin + return 1; +end; +$$; + +``` + +* Update `Makefile` adding new sql file names to `DATA` variable + +``` +DATA = my_first_extension--0.0.2.sql my_first_extension--0.0.1--0.0.2.sql +``` + +* install extension + +``` +sudo make install + +``` + +* check everything works + +``` +$ sudo -u postgres psql + +=# CREATE EXTENSION my_first_extension; +CREATE EXTENSION + +s=# SELECT my_one(); + my_one +-------- + 1 +(1 row) +``` + +---- + +Write test for `my_one` function yourself + +---- + +https://www.highgo.ca/2020/01/10/how-to-create-test-and-debug-an-extension-written-in-c-for-postgresql/ + + +Creating C-function + +// Do not forget to install `postgresql-server-dev-all` + +create `my_first_extension.c` file + +``` +#include "postgres.h" +#include "fmgr.h" + +PG_MODULE_MAGIC; + +PG_FUNCTION_INFO_V1(my_get_two); + +Datum +my_get_two(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT32(2); +} +``` + +add following lines to `Makefile` + +``` +MODULE_big = my_first_extension +OBJS = my_first_extension.o + +``` +and to the `my_first_extension.control` + + +``` +module_pathname = '$libdir/my_first_extension' + +``` + + +Check everything works well by running + +``` +make +``` + +add to schema +``` +CREATE FUNCTION my_two() +RETURNS INT +AS 'MODULE_PATHNAME', 'my_get_two' +LANGUAGE C; +``` + + +bump version in *.sql files, Makefile and control file + + +``` +make +sudo make install +``` + +Check everything works: + +``` +$ sudo -u postgres psql + +=# DROP EXTENSION my_first_extension; +DROP EXTENSION + +=# CREATE EXTENSION my_first_extension; +CREATE EXTENSION +postgres=# SELECT my_two(); + my_two +-------- + 2 +(1 row) + +``` + +Do not forget to write tests! + + diff --git a/slides/2025 - Creating own extension/code/my_first_extension/Makefile b/slides/2025 - Creating own extension/code/my_first_extension/Makefile new file mode 100644 index 0000000..4b14455 --- /dev/null +++ b/slides/2025 - Creating own extension/code/my_first_extension/Makefile @@ -0,0 +1,13 @@ +MODULE_big = my_first_extension +EXTENSION = my_first_extension # Name of the extension +DATA = my_first_extension--0.0.3.sql my_first_extension--0.0.1--0.0.2.sql my_first_extension--0.0.2--0.0.3.sql +REGRESS = my_first_extension-main-test + +OBJS = my_first_extension.o + + +PG_CONFIG ?= pg_config # Path to the pg_config executable +PGXS := $(shell $(PG_CONFIG) --pgxs) # Get the PostgreSQL Extension Makefile from pg_config +include $(PGXS) # Include the PostgreSQL Extension Makefile + + diff --git a/slides/2025 - Creating own extension/code/my_first_extension/expected/my_first_extension-main-test.out b/slides/2025 - Creating own extension/code/my_first_extension/expected/my_first_extension-main-test.out new file mode 100644 index 0000000..a0b8cc9 --- /dev/null +++ b/slides/2025 - Creating own extension/code/my_first_extension/expected/my_first_extension-main-test.out @@ -0,0 +1,13 @@ +CREATE EXTENSION my_first_extension; +SELECT * FROM my_first_extension_table; + id | my_string +----+-------------- + 1 | Hello, World +(1 row) + +DROP EXTENSION my_first_extension; +-- There should be no my_first_extension_table anymore +SELECT * FROM my_first_extension_table; +ERROR: relation "my_first_extension_table" does not exist +LINE 1: SELECT * FROM my_first_extension_table; + ^ diff --git a/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.1--0.0.2.sql b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.1--0.0.2.sql new file mode 100644 index 0000000..9c107ac --- /dev/null +++ b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.1--0.0.2.sql @@ -0,0 +1,10 @@ +CREATE FUNCTION my_one() +returns int +language plpgsql +as +$$ +begin + return 1; +end; +$$; + diff --git a/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.2--0.0.3.sql b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.2--0.0.3.sql new file mode 100644 index 0000000..7b9adcd --- /dev/null +++ b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.2--0.0.3.sql @@ -0,0 +1,5 @@ +CREATE FUNCTION my_two() +RETURNS INT +AS 'MODULE_PATHNAME', 'my_get_two' +LANGUAGE C; + diff --git a/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.3.sql b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.3.sql new file mode 100644 index 0000000..b25f223 --- /dev/null +++ b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension--0.0.3.sql @@ -0,0 +1,24 @@ +CREATE TABLE my_first_extension_table ( + id SERIAL PRIMARY KEY, + my_string VARCHAR NOT NULL +); + +INSERT INTO my_first_extension_table (my_string) VALUES('Hello, World'); + + +CREATE FUNCTION my_one() +returns int +language plpgsql +as +$$ +begin + return 1; +end; +$$; + + +CREATE FUNCTION my_two() +RETURNS INT +AS 'MODULE_PATHNAME', 'my_get_two' +LANGUAGE C; + diff --git a/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension.c b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension.c new file mode 100644 index 0000000..a126494 --- /dev/null +++ b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension.c @@ -0,0 +1,12 @@ +#include "postgres.h" +#include "fmgr.h" + +PG_MODULE_MAGIC; + +PG_FUNCTION_INFO_V1(my_get_two); + +Datum +my_get_two(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT32(2); +} diff --git a/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension.control b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension.control new file mode 100644 index 0000000..68688a0 --- /dev/null +++ b/slides/2025 - Creating own extension/code/my_first_extension/my_first_extension.control @@ -0,0 +1,8 @@ +# my_first_extension.control +# Comment line to provide additional information about the extension +comment = 'My Very First Extension' + +# Specifies the default version of the extension +default_version = '0.0.3' +module_pathname = '$libdir/my_first_extension' + diff --git a/slides/2025 - Creating own extension/code/my_first_extension/sql/my_first_extension-main-test.sql b/slides/2025 - Creating own extension/code/my_first_extension/sql/my_first_extension-main-test.sql new file mode 100644 index 0000000..8992652 --- /dev/null +++ b/slides/2025 - Creating own extension/code/my_first_extension/sql/my_first_extension-main-test.sql @@ -0,0 +1,9 @@ +CREATE EXTENSION my_first_extension; + +SELECT * FROM my_first_extension_table; + +DROP EXTENSION my_first_extension; + +-- There should be no my_first_extension_table anymore +SELECT * FROM my_first_extension_table; + -- 2.47.3