E-Maj Could use a Makefile

[theory]

To support automated installers like the pgxn client, add a Makefile that uses PGXS to install the extension. Something like this has bits to automate various things, but even just something as simple as this would help:

EXTENSION    = E-Maj
DATA         = $(wildcard sql/emaj--*)
PG_CONFIG   ?= pg_config

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

This would allow one to do

make
make install

[beaud76]

Thank You very much, David, for your proposal.

I think it would be great if the next version could allow pgxn to install, load, unload and uninstall the extension. And this needs a proper Makefile to be created.

I already tried to do this in the past, but unsuccessfully (I am not a system expert). That's the reason for I finally described the installation as currently stated in the documentation.

There are several specificities for the E-Maj project:

• the E-Maj project not only contains the emaj extension, but also some CLI clients (as well as a web client but that is not located in the same repo and is not concerned by this topic);
• the sql scripts not only contains the scripts needed for the extension management (creation and upgrades) but also some other scripts that the user can execute;
• dropping the extension is encapsulated into a dedicated sql script that performs preliminary tasks (that may reject the action in some cases);
• a postgres version upgrade must keep the same emaj version.

The approach I currently have in mind could:

• save the global file tree once unzipped into a /usr/share/emaj/x.y.z directory
• install : copy the emaj.control file into the regular prefix/extension directory, including a 'directory' directive to point to /usr/share/emaj/x.y.z/sql
• load : the regular 'CREATE EXTENSION emaj CASCADE' SQL command;
• let the user call the CLI clients or access the extra documentation files from /usr/share/emaj/x.y.z/client/ or doc/ sub-directory
• unload : call the proper /usr/share/emaj/x.y.z/sql/emaj_uninstall.sql script to unload the extension from a database
• uninstall : remove the emaj.control file and the /usr/share/emaj/x.y.z directory

Regarding the load and unload functions, I presume that pgxn should be able to handle them (once an uninstall_emaj.sql link will be created, pointing towards emaj_uninstall.sql).

Regarding the install and uninstall functions, my current feeling is that PGXS would not really help and I should rather try to write a Makefile without it.

Please, feel free to comment.

[beaud76]

Here is a first Makefile draft:

PG_CONFIG   ?= pg_config
PG_SHAREDIR := $(shell $(PG_CONFIG) --sharedir)

# Variables about E-Maj
# Extract the first "version": "..." tag from the META.json file, removing < and >, if any
EMAJ_VERS	:= $(shell grep -m 1 '"version":' META.json | sed -n -r -e 's/^.*:\s*"(.*?)".*/\1/' -e 's/[<>]//gp')
EMAJ_DIR	:= /usr/share/emaj/$(EMAJ_VERS)

all:

install:
	mkdir -p $(EMAJ_DIR)
	cp -r * $(EMAJ_DIR)/.
	sed -r "s|^#directory\s+= .*$$|directory = '$(EMAJ_DIR)/sql/'|" $(EMAJ_DIR)/emaj.control >$(PG_SHAREDIR)/extension/emaj.control

uninstall:
	rm $(PG_SHAREDIR)/extension/emaj.control
	rm -r $(EMAJ_DIR)

[theory]

Shouldn't EMAJ_DIR be under PG_SHAREDIR?

[theory]

the E-Maj project not only contains the emaj extension, but also some CLI clients (as well as a web client but that is not located in the same repo and is not concerned by this topic);

I think ideally it would be installed in pg_config --bindir

the sql scripts not only contains the scripts needed for the extension management (creation and upgrades) but also some other scripts that the user can execute;

I think it's reasonable that they'd live with the other scripts in PG_SHAREDIR. To keep them all in a subdirectory, set MODULEDIR to emaj and in emaj.control set directory = 'emaj'. More details here.

dropping the extension is encapsulated into a dedicated sql script that performs preliminary tasks (that may reject the action in some cases);

If it's an extension using a control file, then DROP EXTENSION emaj should handle all that.

a postgres version upgrade must keep the same emaj version.

You can include SQL scripts to cover all versions, then the user just has to make sure they have the same release installed (that is, default_version in emaj.control is the same). Details here.

save the global file tree once unzipped into a /usr/share/emaj/x.y.z directory

This should at least be configurable, and ideally use PostgreSQL SHAREDIR. I don't think you need the version in the directory name.

install : copy the emaj.control file into the regular prefix/extension directory, including a 'directory' directive to point to /usr/share/emaj/x.y.z/sql

I don't think that will work. Postgres requires that its SQL files be in its SHAREDIR. Maybe the directory setting can be a full path, but I'm not aware of anyone doing that.

let the user call the CLI clients or access the extra documentation files from /usr/share/emaj/x.y.z/client/ or doc/ sub-directory

Postgres extension docs ideally should go into the cluster's docs or man directories. If you set DOCS to the list of files in the Makefile then PGXS will put them in the right place.

unload : call the proper /usr/share/emaj/x.y.z/sql/emaj_uninstall.sql script to unload the extension from a database

Is there something about DROP EXTENSION that doesn't work for emaj?

Regarding the load and unload functions, I presume that pgxn should be able to handle them (once an uninstall_emaj.sql link will be created, pointing towards emaj_uninstall.sql).

The PGXN client currently expects everything to use either PGXS-based Makefile stuff or ./Configure && make && make install a la GNU automate. I think it will also run cmake before configure if it needs to. But for loading and unloading I believe it just uses CREATE EXTENSION and DROP EXTENSION.

Regarding the install and uninstall functions, my current feeling is that PGXS would not really help and I should rather try to write a Makefile without it.

I think that's true if you have requirements for file system locations that PGXS isn't aware of, but I encourage using the Postgres standard locations for everything to do with an extension (and not the other stuff you have in this repo, which I assume is not included in the zip file released on PGXN). It keeps things much more predictable for DBAs.

Hope that's helpful!

[beaud76]

Thanks a lot for your comments. It's very helpful :-)

Regarding the files location, it's good to know where the DBAs use to find components.

Is there something about DROP EXTENSION that doesn't work for emaj?

E-Maj goal is to capture changes on tables. And this is done using triggers created on application tables. When dropping the extension, we have to be sure that no such triggers are left on these tables. The uninstall SQL script checks this and refuses to drop the extension if it's not the case. I am not aware of a parameter in the control file or any other trick that would allow to avoid a SQL script.

install : copy the emaj.control file into the regular prefix/extension directory, including a 'directory' directive to point to /usr/share/emaj/x.y.z/sql

I don't think that will work. Postgres requires that its SQL files be in its SHAREDIR. Maybe the directory setting can be a full path, but I'm not aware of anyone doing that.

In fact, it's possible to use a full path, as stated in the documentation. And I use this in my tests environments that uses all PG versions from 11 to 16.

I will try to write another Makefile taking into account your advice.

[beaud76]

To be more precise about the emaj_uninstall.sql script:

• It checks that the emaj schema exists, the current role is superuser or is the emaj schema owner, and no non E-Maj objects are located into all E-Maj schemas;
• It disables the event trigger that blocks any attempt to directly DROP EXTENSION emaj;
• It executes 2 cleanup functions that may have been left by demo or test scripts executed by the user;
• It drops the remaining E-Maj tables groups, if any, in order to be sure that no log triggers are left on application tables;
• It drops the extension and the emaj schema;
• It drops the event trigger function that is kept outside the extension (in order to let it work);
• It revokes grant given to the emaj_adm role used for E-Maj administration;
• It drops emaj_adm and emaj_viewer roles if they are not used by any other databases in the cluster.

The same script processes emaj installed as an EXTENSION and installed with a psql script (for cloud environments).

[beaud76]

Hi David,

Right now, I am facing 3 issues. The first concerns PGXS, more precisely the uninstall target. I reported it yesterday in the pgsql-general mailing list. (https://www.postgresql.org/message-id/f2645de1-b3b0-43b2-b467-bc3e83bb4898%40free.fr)
The two others are related to pgxn.

While issuing the load command, I get a message saying that required extensions are missing. It looks like the pgxn client doesn't use the CASCADE clause of the CREATE EXTENSION statement. This is not a big deal as once the required extensions are created, the command works fine. I have not found any parameter asking for the CASCADE clause. Could you confirm that I have not missed something and that it is the expected behavior ?

The last issue deals with the unload command. I have not been able to let the pgxn client use an uninstall script instead of the regular DROP EXTENSION statement. I tried to copy the uninstall script into the $PGSHARE/$MODULEDIR directory or directly into the $PGSHARE/extension directory but unsuccessfully. May be I misread the documentation. It says in particular:

For every extension specified in the ‘provides’ section of the distribution META.json, the command will look for a file called uninstall_file.sql where file.sql is the file specified. If no file is specified, extension.sql is assumed.

Actually, I don't understand where this file could be specified. I don't see a file option in the pgxn unload command and in the META.json the "file" property of the "provides" section is the file containing the extension and is not optional.

Thanks by advance for your help.

[theory]

To be more precise about the emaj_uninstall.sql script:

Yes, most extensions that let the use add and remove objects can leave detritus. It's not mutually-exclusive to DROP EXTENSION, but an extra step to recommend.

The first concerns PGXS, more precisely the uninstall target. I reported it yesterday in the pgsql-general mailing list

Yeah, PGXS does not remove those files, it just leaves them. They're mostly harmless though.

It looks like the pgxn client doesn't use the CASCADE clause of the CREATE EXTENSION statement.

I don't thin you've missed anything; I don't see it in the source. Might be worth opening a feature request issue in the client project.

The last issue deals with the unload command. I have not been able to let the pgxn client use an uninstall script instead of the regular DROP EXTENSION statement.

Yeah that's not a supported pattern of DROP EXTENSION AFAIK. The same problem manifests for anyone issuing DROP EXTENSION themselves. I think the best you can do is recommend that the script be run before uninstalling. I wonder if you could add a cleanup function of some kind, so that one can call the function without having to access the physical file system of the server to find the uninstall script.

For every extension specified in the ‘provides’ section of the distribution META.json, the command will look for a file called uninstall_file.sql where file.sql is the file specified. If no file is specified, extension.sql is assumed.

I didn't know about that! It's specific to the pgxn client (I honestly don't use it much myself). Might be a bug if it doesn't find it.

Actually, I don't understand where this file could be specified. I don't see a file option in the pgxn unload command and in the META.json the "file" property of the "provides" section is the file containing the extension and is not optional.

I don't know either. I would assume it would need to be installed with all of your SQL scripts, so should be included in the DATA variable in the Makefile. But @ dvarrazzo would know best.

[beaud76]

I have created 2 issues in the pgxn client project:

• CREATE EXTENSION CASCADE at pgxn load time pgxn/pgxnclient#46
• Uninstall script pgxn/pgxnclient#47 to get help on the uninstall script at pgxn unload time and clarify the documentation.

[beaud76]

The commit b367d0a adds a Makefile.

[theory]

Looks good, although I think the SQL files need to go into $(PG_SHAREDIR)/extension/emaj. This is because directory = emaj in the control file as set on line 28, means "The emaj subdirectory of $(PG_SHAREDIR)/extension."

[beaud76]

Thanks for the feedback.
In fact it works well as is, and as described in the PG documentation:

The directory containing the extension's SQL script file(s). Unless an absolute path is given, the name is relative to the installation's SHAREDIR directory. The default behavior is equivalent to specifying directory = 'extension'.

FYI, I am currently working on the move of the uninstall script towards a SQL function, to help the user dropping the extension, as you suggested in an earlier message. This is a good idea and ... it seems to work fine.
I would have liked to automatically call the function at SQL DROP EXTENSION time but an event trigger of type DROP fires too late, after the extension is already dropped. So I keep the existing event trigger to prevent from a direct SQL DROP EXTENSION statement and now hint for using the new function.

[theory]

Oh I see, I forget it doesn't go into the extension subdirectory. :-/

I think documentation is the only way to encourage people to run an uninstall procedure before actually uninstalling, alas. Nice to have it in the database, though, will be more convenient, I think — as long as no one calls it accidentally. :-)