Database

LP supports Oracle and Postgres databases for Faces service and Postgres database for Events service.

Multiple hosts

The dsn parameter of service database config should be specified to enable multihost option for Postgres.

DSN may be specified in the following format: luna:luna@postgres01:5432,postgres02:5432/luna_faces?some_option=some_value. It is possible to partially fill in the DSN string (e.g. postgres01,postgres02/luna_faces), and then the missing parameters will be filled in from proprietary parameter values (or defaults).

At startup, the service will create a pool of connections to one of DSN hosts available. In case of problems with establishing a connection after several unsuccessful attempts, the service will again try to set up a connection pool to any of DSN hosts available.

DB matching installation

To allow matching in db, one needs to:

1. Compile a library with a VLMatch function using appropriate database development libraries.

2. Import the library in the running database and check it availability.

---

See luna_python_matcher/db/vlmatch/postgres/readme.md for Postgres Database:

Instruction for PostgreSQL database

Note: The following instruction describes installation for PostgreSQL 16.

Standard matching option

You can find all the required files for the VLMatch user-defined extension (UDx) compilation in the following directory:

/var/lib/luna/current/extras/VLMatch/postgres/

For VLMatch UDx function compilation one needs to:

1. Install RPM repository:

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. Install PostgreSQL 16:

dnf install postgresql16-server

3. Install the development environment:

dnf install postgresql16-devel

4. Install the gcc-c++ package. The package version 4.8 or higher is required.

dnf install gcc-c++

5. Install CMAKE. The version 3.5 or higher is required.

6. Open the make.sh script using a text editor. It includes paths to the currently used PostgreSQL version. Change the following values (if necessary):

   • SDK_HOME specifies the path to PostgreSQL home directory. The default value is /usr/pgsql-16/include/server;

   • LIB_ROOT specifies the path to PostgreSQL library root directory. The default value is /usr/pgsql-16/lib.

7. Go to the make.sh script directory and run it:

cd /var/lib/luna/current/extras/VLMatch/postgres/

chmod +x make.sh

./make.sh

8. Define the function inside the service database:

sudo -u postgres -h 127.0.0.1 -- psql -d luna_events -c "CREATE FUNCTION VLMatch(bytea, bytea, int) RETURNS float8 AS 'VLMatchSource.so', 'VLMatch' LANGUAGE C PARALLEL SAFE;"

9. Test function by sending re following request to the service database:

sudo -u postgres -h 127.0.0.1 -- psql -d luna_events -c "SELECT VLMatch('\x1234567890123456789012345678901234567890123456789012345678901234'::bytea, '\x0123456789012345678901234567890123456789012345678901234567890123'::bytea, 32);"

The result returned by the database must be “0.4765625”.

Alternative matching options

When it is not possible to use postgres C-extensions for some reason, an alternative SQL user defined function can be used for matching. Define the SQL-VLMatch function inside the service database:

CREATE OR REPLACE FUNCTION vlmatch(bytea, bytea, int) RETURNS float AS
-- original `vlmatch` function compatible interface
$BODY$
DECLARE
    acc float = 0;
BEGIN
    FOR i IN 0..$3-1 LOOP
        acc = acc + (get_byte($1, i) - get_byte($2, i)) ^ 2.0;
    END LOOP;
    RETURN acc / (128 * 128);
END;
$BODY$
LANGUAGE plpgsql PARALLEL SAFE IMMUTABLE;

Test the function by the following query to the database:

SELECT * FROM vlmatch('\x1234567890123456789012345678901234567890123456789012345678901234'::bytea, '\x0123456789012345678901234567890123456789012345678901234567890123'::bytea, 32)

The result returned by the database must be “0.4765625”.

⚠️Beware the performance penalty. When using the SQL-VlMatch, the query execution time is about two orders of magnitude slower than when using the standard C-VLMatch. Also make sure your database has query parallelization configured.

---

See also luna_python_matcher/db/vlmatch/oracle/readme.md for Oracle Database:

Instruction for Oracle database

Note: The following instruction describes installation for Oracle 21c.

You can find all the required files for the VLMatch user-defined extension (UDx) compilation in the following directory:

/var/lib/luna/current/extras/VLMatch/postgres/

For VLMatch UDx function compilation one needs to:

1. Install required environment, see requirements:

sudo yum install gcc g++

2. Change SDK_HOME variable - oracle sdk root (default is $ORACLE_HOME/bin, check $ORACLE_HOME environment variable is set) in the makefile and run make from base_scripts/database_matching/oracle directory:

chmod +x make.sh

./make.sh

3. Define the library and the function inside the database (from database console):

CREATE OR REPLACE LIBRARY VLMatchSource AS '$ORACLE_HOME/bin/VLMatchSource.so';
CREATE OR REPLACE FUNCTION VLMatch(descriptorFst IN RAW, descriptorSnd IN RAW, length IN BINARY_INTEGER)
   RETURN BINARY_FLOAT
AS
   LANGUAGE C
   LIBRARY VLMatchSource
   NAME "VLMatch"
   PARAMETERS (descriptorFst BY REFERENCE, descriptorSnd BY REFERENCE, length UNSIGNED SHORT, RETURN FLOAT);

4. Test function within call (from database console):

SELECT VLMatch(HEXTORAW('1234567890123456789012345678901234567890123456789012345678901234'), HEXTORAW('0123456789012345678901234567890123456789012345678901234567890123'), 32) FROM DUAL;

The result returned by the database must be “0.4765625”.