Thread: Using libpq to access a repote database from a c trigger function breaks.

Andrew Fritz (afritz@teamdev.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Using libpq to access a repote database from a c trigger function breaks.

Long Description
The database is running on x86 (PenIII-650,256meg). The 7.0.2 source was compiled on RH 7.0 on the machine the backend
runson. The client (psql) is also being run localy. 

The C function (installed in the database from a shared object: "CREATE FUNCTION update_mirror() RETURNS OPAQUE AS
/usr/local/postgres/user_lib/update_mirror.so'LANGUAGE 'c'") is called by a trigger  ("create trigger sync_table1_trig
afterinsert on table1 for each row execute procedure update_mirror ()").  

It then opens a connection to another database via libpq (currently -during development-, this database resides on the
samebackend as the database that caused the trigger).  

Two database exist: test and mirror. Both have only one user table "table" which has the following definition "create
table'table' ('field1' varchar(100))". "test" has the previously mention trigger and c function installed on it.
"mirror"has no funcitons or triggers installed.  

The trigger is fired by an insert into table1 on "test".

After succesffully connecting to "mirror", res = PQexec (conn,"INSERT INTO table1 (field1) VALUES ('blablabla')")
completes.Checking the results yields PGRES_TUPLES_OK, instead of the expected (per the documentation)
PGRES_COMMAND_OKthat should be returned for a statment that retreives no values.  

Upon PQclear (res); the backend crashes. If the the PQclear is omited, the code runs without crashing the backend the
firsttime (although the INSERT does not actually occur). The second time the trigger fires, the backend crashes. 

The output from the server during the insert is:
NOTICE:  Called as trigger.
NOTICE:  Connection succeeded.
NOTICE:  About to execute "BEGIN"
NOTICE:  tuples ok
NOTICE:  About to clear results.
Invalid arguments to portal functions (135865632) [Function not implemented]
Server process (pid 2662) exited with status 134 at Wed Mar  7 11:06:26 2001
Terminating any active server processes...
The Data Base System is in recovery mode
NOTICE:  Message from PostgreSQL backend:
    The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory.
    I have rolled back the current transaction and am going to terminate your database system connection and exit.
    Please reconnect to the database system and repeat your query.
pq_flush: send() failed: Broken pipe
Server processes were terminated at Wed Mar  7 11:06:26 2001
Reinitializing shared memory and semaphores
DEBUG:  Data Base System is starting up at Wed Mar  7 11:06:26 2001
DEBUG:  Data Base System was interrupted being in production at Wed Mar  7 11:06:18 2001
DEBUG:  Data Base System is in production state at Wed Mar  7 11:06:26 2001



Sample Code
Here is the code for the c function:
#include "postgres.h"
#include "fmgr.h"
#include "commands/trigger.h"

#include "libpq-fe.h"

#include <stdio.h>

PG_FUNCTION_INFO_V1(update_mirror);

Datum update_mirror (PG_FUNCTION_ARGS) {

  HeapTuple rettuple;
  PGresult *res;
  PGconn *conn;
  FILE *log_file;
  const char *sql = "INSERT INTO table1 (field1) VALUES ('blablabla')";

  //get the current trigger data if we are being called as a trigger
  if (CurrentTriggerData != NULL) {
    CurrentTriggerData = NULL;

    elog (NOTICE,"Called as trigger.");
  } else {
    elog (NOTICE,"Not called as trigger.");
  }

  conn = PQsetdbLogin (NULL,NULL,NULL,NULL,"mirror","me","mypassword");

  if (PQstatus(conn) == CONNECTION_BAD)
    {
      elog (NOTICE,"Connection failed.");
    } else {
      elog (NOTICE,"Connection succeeded.");
    }

  log_file = fopen ("/usr/local/postgres/log/mirror.log","w");

  PQtrace(conn,log_file);

  elog (NOTICE,"About to execute \"%s\"",sql);

  res = PQexec(conn, sql);

  switch (PQresultStatus(res)) {
    case PGRES_EMPTY_QUERY: {
      elog (NOTICE,"empty query");
    } break;
    case PGRES_COMMAND_OK: {
      elog (NOTICE,"command ok");
    } break;
    case PGRES_TUPLES_OK: {
      elog (NOTICE,"tuples ok");
    } break;
    case PGRES_COPY_OUT: {
      elog (NOTICE,"copy out");
    } break;
    case PGRES_COPY_IN: {
      elog (NOTICE,"copy in");
    } break;
    case PGRES_BAD_RESPONSE: {
      elog (NOTICE,"bad responce");
    } break;
    case PGRES_NONFATAL_ERROR: {
      elog (NOTICE,"nonfatal error");
    } break;
    case PGRES_FATAL_ERROR: {
      elog (NOTICE,"fatal error");
    } break;
    default: {
      elog (NOTICE,"unknown status");
    }
  };

  elog (NOTICE,"About to clear results.");

  PQclear (res);

  elog (NOTICE,"Cleared result.");

  PQfinish (conn);

  elog (NOTICE,"Conn closed.");

  return PointerGetDatum(NULL);
}


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> Using libpq to access a repote database from a c trigger function breaks.

Try linking libpq.a into your .so file.  I think your references to
PQexec and so forth are being resolved to the
similarly-named-but-completely-different functions that exist in the
backend (see src/backend/libpq).  There is no copy of client libpq in
the standard backend.

            regards, tom lane

Tom Lane writes:

> pgsql-bugs@postgresql.org writes:
> > Using libpq to access a repote database from a c trigger function breaks.
>
> Try linking libpq.a into your .so file.  I think your references to
> PQexec and so forth are being resolved to the
> similarly-named-but-completely-different functions that exist in the
> backend (see src/backend/libpq).  There is no copy of client libpq in
> the standard backend.

You might also want to try to sneak in a -Bsymbolic option (or
-Wl,-Bsymbolic, respectively) on your link command line.  On ELF systems,
symbols in the executable override symbols in the shared library unless
you use this option.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/