Database with Elephant

From the PostgreSQL documentation¹: "A window function performs a calculation across a set of table rows that are somehow related to the current row. … A window function call always contains an OVER clause directly following the window function's name and argument(s)." Examples could be found online^{2, 3}, they are also available through postgres-src/src/tutorial/advanced.sql⁴

When building scripts in src/tutorial/, Fedora complains that pg_config is missing. In this case, it's available through package postgresql-server-devel^{5, 6}.

sudo dnf install postgresql-server-devel

In the case of following error during compilation process: "gcc: fatal error: cannot read spec file ‘/usr/lib/rpm/redhat/redhat-hardened-cc1’: No such file or director", install package redhat-rpm-config⁷ and try again.

sudo dnf install redhat-rpm-config

If somehow the following error message shows up:

gcc -Wall … -specs=/usr/lib/rpm/redhat/redhat-hardened-ld -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -shared -o complex.so complex.o: file not recognized: file format not recognized collect2: error: ld returned 1 exit status

It's likely caused by intermediate objects generated by earlier failed compilation process. Now run make clean before compile again.

The CREATE FUNCTION statement requires following permissions to be ready:

• the database user has SUPERUSER privilege
• the shared library .so file is readable and executable to current user, e.g. chmod +rw
• the share library has proper SELinux context configured, e.g. postgresql_exec_t

Typical error messages include:

• ERROR: permission denied for language c
• ERROR: could not access file "/path/to/libfile": Permission denied
• ERROR: could not load library "/path/to/libfile.so": /path/to/libfile.so: cannot open shared object file: Permission denied
• ERROR: could not load library "/path/to/libfile.so": /path/to/libfile.so: failed to map segment from shared object

The first error indicates the case when current user has no SUPERUSER privilege. To resolve this issue, either switch to the default postgres super user, or grant proper privilege to current user first by ALTER USER user_name WITH SUPERUSER;.

The second error is caused by the venerable file and directory permission control. Ensure that the database user has read access to the file, and execute permission on directories along the way.

The third and fourth error are likely caused by violation of SELinux policy, whose details should be enlisted in /var/log/audit/audit.log. The third error could be resolved when a policy like postgresql_db_t is applied, and the fourth error could be resolved by applying postgresql_exec_t to the library .so file. e.g.

# if we choose to add the postgresql_db_t type
sudo semanage fcontext -a -t postgresql_db_t "/path/to/lib_dir(/.*)?"
sudo restorecon -Rv /path/to/lib_dir

# another way to add the postgresql_exec_t type to *.so files
sudo chcon -Rt postgresql_exec_t /path/to/lib_dir/*.so