greenplumpg-trgm

Install pg_trgm on postgres 9.4.24

I'm trying to use the similarity function on a Greemplum system using postgres 9.4.24 version. The Greenplum System is running on a CentOS 7 cluster (CentOS Linux release 7.9.2009 (Core))

I've managed to install the postgresql-contrib package by running this:

sudo yum install postgresql-contrib.x86_64

However, when I run this command on a DB: create extension pg_trgm(); I get the following:;

SQL Error [58P01]: ERROR: could not open extension control file "/usr/local/greenplum-db-6.13.0/share/postgresql/extension/pg_trgm.control": No such file or directory

I looked in the directory and the pg_trgm.control wasn't there but I was expecting it since I installed the contrib package.

Is there anything I missed?

I found the contrib repository for Greenplum and downloaded the code here: https://github.com/greenplum-db/gpdb/tree/master which includes the contrib directory with all the extensions..

however, when I try to make && make install, I get an error:

[gpadmin@mdw pg_trgm]$ sudo make
Makefile:22: ../../src/Makefile.global: No such file or directory
Makefile:23: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target `/contrib/contrib-global.mk'.  Stop.

Looking at the directory structure and files, Makefile.global doesn't exists and contrib-global.mk does but not sure if the path is correct, I can try changing the path in the Makefile but not sure.

These are the contents of the Makefile file:

# contrib/pg_trgm/Makefile

MODULE_big = pg_trgm
OBJS = trgm_op.o trgm_gist.o trgm_gin.o trgm_regexp.o $(WIN32RES)

EXTENSION = pg_trgm
DATA = pg_trgm--1.3--1.4.sql \
        pg_trgm--1.3.sql pg_trgm--1.2--1.3.sql pg_trgm--1.1--1.2.sql \
        pg_trgm--1.0--1.1.sql pg_trgm--unpackaged--1.0.sql
PGFILEDESC = "pg_trgm - trigram matching"

REGRESS = pg_trgm pg_word_trgm pg_strict_word_trgm
REGRESS_OPTS += --init-file=$(top_srcdir)/src/test/regress/init_file

ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/pg_trgm
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

I have checked out the code in my /tmp folder and it created the structure under /tmp/gpdb

UPDATE: I did follow Bradford suggestions and was able to compile and install, however, I'm getting this when I run: create extension pg_trgm;

ERROR: could not open extension control file "/usr/local/greenplum-db-6.13.0/share/postgresql/extension/pg_trgm.control": No such file or directory (seg57 172.28.8.8:6001 pid=146663)

I did run manually once I ran the make and make install:

/usr/bin/install -c -m 755  pg_trgm.so '/usr/local/greenplum-db-6.13.0/lib/postgresql/pg_trgm.so'
/usr/bin/install -c -m 644 pg_trgm.control '/usr/local/greenplum-db-6.13.0/share/postgresql/extension/'
/usr/bin/install -c -m 644 pg_trgm--1.1.sql pg_trgm--1.0--1.1.sql pg_trgm--unpackaged--1.0.sql '/usr/local/greenplum-db-6.13.0/share/postgresql/extension/'

I can see the .control file in there so not sure what's going on, it seems it was copied only to the master but not the segment hosts, would that be an issue? Do I need to compile this on every segment host?

UPDATE: I did install on each segment and now I'm getting this:

SQL Error [0A000]: ERROR: query plan with multiple segworker groups is not supported Hint: likely caused by a function that reads or modifies data in a distributed table

would this extension not be supported by greenplum?

Solution

  • At a high level, you will want to download the source for the release of GPDB that you are running. You can do this either by downloading the tarball from the GitHub release page or cloning the repository and checking-out the release tag.

    Once you have done that, source greenplum_path.sh from your installation of GPDB, change into the the contrib/pg_trgm directory and run

    USE_PGXS=true make
USE_PGXS=true make install

    Setting USE_PGXS will pull in the missing Makefiles from the installation of GPDB.

    If the install location of GPDB is not owned by gpadmin user, then the make install will fail for permission issues; using sudo make install will most likely fail because pg_config won't be found on the path. In that case, I just run make -n install and then manually run the commands prefixed with sudo.

    Here are all the steps in a psuedo-shell session:

    git clone https://github.com/greenplum-db/gpdb.git
cd gpdb

# use git checkout 6.13.0 for older versions of git
git switch --detach 6.13.0
git submodule update --init --recursive

source /usr/local/greenplum-db-6.13.0/greenplum_path.sh
cd contrib/pg_trgm
USE_PGXS=true make
USE_PGXS=true make -n install

    Edit: I should clarify that I haven't tested if pg_trgm works with GPDB, only that it builds and compiles.