Rdkit: PostgreSQL Cartridge raise an error 'syntax error at or near "@" at character 32237'

[darkcircle]

Description:
I have compiled RDKit by following the way exactly same as previous versions,
but I stucked on 'create extension' after I compiled PostgreSQL Cartridge,
and installed into /usr/share/postgresql/extension and /usr/lib64/postgresql-9.5/lib64

• RDKit Version: 2018.03.1.dev1
• Platform: Gentoo Linux(gcc-6.4.0-r1)

I cannot proceed to the next step on 'create extension rdkit'; on postgresql console;

[darkcircle]

+PostgreSQL version is 9.5.11

[darkcircle]

on postgresql 9.5.5 occurs the same problem... why?

I had no met problem until 2017 Q1. so weird.

[greglandrum]

Can you please paste in:

• the command you used to install the cartridge
• the full error message that you get from pgsql

These would help track down what's going on.

[darkcircle]

Preparation

first of all, I already compiled below packages into my system
(postgresql should be configured by using 'emerge --config =dev-db/postgresql-9.5.11')

sys-devel/gcc-6.4-r1
sys-devel/binutils-2.29.1
dev-libs/boost-1.65
dev-cpp/eigen-3.3.4
app-arch/unzip-6.0_p21
dev-db/postgresql-9.5.11
dev-lang/python-3.5.4-r1
dev-python/pip-9.0.1-r2

and additional python libraries has been installed as below

numpy==1.14.1
scipy==1.0.0
urllib3==1.22
...

as we all know, another python library can be prepared via pip if rdkit asks to install into the system.

I cloned rdkit git source tree into my computer

# cd /opt;git clone https://github.com/rdkit/rdkit RDKit;

and I have set below environment variable. (PYTHONPATH has ignored during cmake configuration)

export RDBASE=/opt/RDKit
export LD_LIBRARY_PATH=$RDBASE/lib:$LD_LIBRARY_PATH
export PYTHONPATH=$RDBASE:$PYTHONPATH:/root/.local/lib64/python3.5/site-packages

Download INCHI-API

I downloaded INCHI-API by using simple script,

# cd $RDBASE/External/INCHI-API;./download-inchi.sh;

Compilation

and ... I started parallelized-compilation to build main RDKit library. I considered that I'm using Quad-core processor without Hyper-thread feature only provided by Intel processors.

# mkdir -p $RDBASE/build;cd $RDBASE/build;
# cmake -D EIGEN3_INCLUDE_DIR=/usr/include/eigen3 \
        -D PYTHON_LIBRARY=/usr/lib64/libpython3.5m.so.1.0 \
        -D PYTHON_INCLUDE=/usr/include/python3.5m \
        -D PYTHON_EXECUTABLE=/usr/bin/python \
        -DRDK_BUILD_INCHI_SUPPORT=ON ..;
# make -j5 -l8 && make install;

Copy Library

I found that there is RDKit-Library-missing problem, so, I copied library components manually,

# cp -a $RDBASE/lib/libRDKit* /usr/lib64/.

Build PostgreSQL Cartridge

We need to modify Makefile being on $RDBASE/Code/PgSQL/rdkit/, before we try to do "make", because library reference problem, so, I modified RDKLIBS assignment line as below

RDKLIBS = ${AVALONLIBS} ${INCHILIBS} -lRDKitMolDraw2D -lRDKitMolTransforms 
-lRDKitMolHash -lRDKitFMCS -lRDKitChemReactions -lRDKitChemTransforms 
-lRDKitFileParsers -lRDKitSmilesParse -lRDKitFingerprints -lRDKitSubgraphs 
-lRDKitDescriptors -lRDKitPartialCharges -lRDKitSubstructMatch -lRDKitGraphMol
-lRDKitEigenSolvers -lRDKitDataStructs -lRDKitDepictor -lRDKitRDGeometryLib 
-lRDKitRDGeneral

I love to use boost, so I applied USE_THREADS=1 on the head of line.

cd $RDBASE/Code/PgSQL/rdkit;USE_THREADS=1 make -j5 -l8;make install;

Then we will can see where copied file is.

/bin/mkdir -p '/usr/lib64/postgresql-9.5/lib64'
/bin/mkdir -p '/usr/share/postgresql-9.5/extension'
install -c -m 755 rdkit.so '/usr/lib64/postgresql-9.5/lib64/rdkit.so'
install -c -m 644 rdkit.control '/usr/share/postgresql-9.5/extension/'
install -c -m 644 rdkit--3.5.sql '/usr/share/postgresql-9.5/extension/'

Setting postgresql via postgresql.conf

postgresql.conf file is being in /var/lib/postgresql/9.5/data, but this looks fake(and It's symbolic link in real), real file is in /etc/postgresql-9.5/ .

I used below command to insert variable and value on the last of postgresql.conf.

echo 'shared_preload_libraries=\'rdkit\'' >> /etc/postgresql-9.5/postgresql.conf

and also I have set below option to see the problem

log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = 0
log_line_prefix = '[%m %p %d:%u] '

and, I restarted(or start for the first time after installation) postgresql-9.5 server,
(I did not change init system to the systemd).

# /etc/init.d/postgresql-9.5 restart

Occoured Problem

in the postgresql console, I could see available extension list as below

db=# select * from pg_available_extension;
        name        | default_version | installed_version |                               comment                                
--------------------+-----------------+-------------------+----------------------------------------------------------------------
 intarray           | 1.0             |                   | functions, operators, and index support for 1-D arrays of integers
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
 chkpass            | 1.0             |                   | data type for auto-encrypted passwords
 dblink             | 1.1             |                   | connect to other PostgreSQL databases from within a database
 pgstattuple        | 1.3             |                   | show tuple-level statistics
 rdkit              | 3.5             |                   | Cheminformatics functionality for PostgreSQL.
 tsm_system_rows    | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 ltree_plpython2u   | 1.0             |                   | transform between ltree and plpython2u
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

but, when I tried to run query,

chemdb=# create extension rdkit;
ERROR:  syntax error at or near "@"

????????

so, I checked over postgresql log file, /var/lib/postgresql/9.5/data/postmaster.log,

[2018-02-28 14:33:54.975 KST 19019 chemdb:postgres] STATEMENT:  create extension rdkit;
[2018-02-28 14:33:54.979 KST 19019 chemdb:postgres] ERROR:  syntax error at or near "@" at character 32237

This two lines are all of error message.

I assume that this problem came from rdkit--3.5.sql file, but I want someone give me little bit simple hint to find cause of this bug.

ok. This is all of my story to find a problem.

Thanks for advance :)

[greglandrum]

Building the cartridge is an area where the documentation really needs help.
You can now build the cartridge with the rest of the RDKit using cmake. I would really suggest that you do that.
To do so, add -DRDK_BUILD_PGSQL=ON to the cmake arguments for the RDKit. As cmake runs it will produce instructions for how to complete the installation:

=====================================================================
After building, check
/scratch/RDKit_git/build/Code/PgSQL/rdkit/pgsql_install.sh
for correctness of installation paths. If everything is OK, gain 
administrator privileges, stop the PostgreSQL service, run
/scratch/RDKit_git/build/Code/PgSQL/rdkit/pgsql_install.sh
to install the PostgreSQL RDKit cartridge, then start again
the PostgreSQL service
=====================================================================

Once you've built and completed that step, you can then run ctest as you normally would to test your build. ctest will also test the cartridge install.

[darkcircle]

I have try again from a scratch with adding -DRDK_BUILD_PGSQL=ON to the cmake arguments for the RDKit. and I tried to run pgsql_install.sh after RDKit build process, without additional doing compile process for the Code/PgSQL/rdkit.

I saw below message! when I enter command in the postgresql console.

db=# create extension rdkit; -- no error
db=# \dx+rdkit
                      Objects in extension "rdkit"
                            Object Description
---------------------------------------------------------------------------
 function add(sfp,sfp)
 function all_values_gt(sfp,integer)
 function all_values_lt(sfp,integer)
 function atompair_fp(mol)
 function atompairbv_fp(mol)
 function avalon_fp(mol,boolean,integer)
 function bfp_cmp(bfp,bfp)
.... !!!

All is done(HOOOOO!!)!

I don't know why separated compile process causes an error what i said ...... was it my mistake? but, I knew, the way that you told me is more simple and handy.

Thank you so much! my problem is cleanly resolved!