PostgreSQL C function: libpq doesn't link?

Question

I want to write a C function for PostgreSQL. For this function I will need to query some data using libpq, so I started by writing a dummy function to test this part:

#define _XOPEN_SOURCE

#include <libpq-fe.h>
#include <postgres.h>
#include <fmgr.h>
#include <funcapi.h>
#include <executor/executor.h>

#include "test.h"

PG_FUNCTION_INFO_V1(getAnnotation);
Datum getAnnotation(PG_FUNCTION_ARGS) {
    // Connection to the database
    PGconn *conn = PQsetdbLogin("localhost",
                         "5432",
                         "",
                         "",
                         "postgres",
                         "postgres",
                         "password");

    // Databases names
    PGresult *res = PQexec (conn, "SELECT user FROM activity LIMIT 1;");
    VarChar* i = PQgetvalue(res, 0, 0);
    PG_RETURN_VARCHAR_P(i);
}

It is just supposed to return the first column of the first line of one of my tables. Pretty simple right ? Well it doesn't work.

When I try to use it within psql, it says :

ERROR:  could not load library "/usr/local/lib/postgresql/test.so": Error relocating /usr/local/lib/postgresql/test.so: PQexec: symbol not found

The weird part is that both PQsetdbLogin and PQexec are in the libpq-fe.h file, but only the second one causes an error. If I comment the PQexec line, then PQsetdbLogin raises an error as well.

Here is the Makefile I use to build the code:

PG_CPPFLAGS = -I$(libpq_srcdir)
LDFLAGS_INTERNAL = -L$(libdir)
SHLIB_LINK_INTERNAL = $(libpq)
SHLIB_PREREQS = submake-libpq

EXTENSION = test
DATA = test--0.1.sql
MODULES = test

# REGRESS = ... # Script for tests

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

As you can see, I linked libpq therem so everything should work... But it doesn't and I don't know why.

I am using PostgreSQL 9.6 in a docker container.

Answer 1

If you want to link with external libraries, you need SHLIB_LINK, but that only works if you use MODULE_big instead of MODULES.

A working Makefile would be

PG_CPPFLAGS = -I$(libpq_srcdir)
SHLIB_LINK = $(libpq)

EXTENSION = test
DATA = test--0.1.sql
MODULE_big = test
OBJS = test.o

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

But there are other problems with your function:

• VarChar is a varlena, but PQgetvalue returns a char *.

  You'd have to convert the value to a varlena with code similar to this:

  VarChar *result;
  char *c = PQgetvalue(res, 0, 0);
  result = (VarChar *) palloc(strlen(c) + VARHDRSZ);
  strncpy(VARDATA(result), c, strlen(c));
  SET_VARSIZE(result, strlen(c) + VARHDRSZ);
  

• It is usually the wrong idea to write client code in a server function.

  If all you need is to run a query inside the current session, use the Server Programming Interface.

• I guess your function is only sample code, but you need to close the connection before the backend terminates, else you have a connection leak.