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.

Laurenz Albe · Accepted Answer · 2019-09-10 13:08:50Z

3

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.

answered Sep 10, 2019 at 13:08

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gaëtan Over a year ago

Thanks a lot, the makefile works ! I will also take a look at the Server Programming Interface. I never heard of it before but it looks to be a better solution for what I want to do.

Collectives™ on Stack Overflow

PostgreSQL C function: libpq doesn't link?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related