Thread: libpq++ in Apache Problem.

Good day,

I'm experiencing a truly strange issue with the libpq++ interface when I
run it within an Apache process, and I was hoping that perhaps someone
else has run into this.

Inside of an Apache module I am developing, I need to make calls to
PostgreSQL, so I am building it with the libpq++ interface. It's worked
fine thus far, because the module is designed to keep a persistent
connection to PostgreSQL by having a PgDatabase* object that lives and
dies with each httpd process.

However, if I try to instantiate a PgDatabase* connection temporarily
within the httpd process, even though I delete the pointer to call the
destructor of the Object, the postmaster connection *continues to hang
around*, well after the request has finished processing, and well after
the call to its destructor is made.

I thought this might have something to do with the kind of convoluted way
that I'm linking my Apache module (which is written in C++, while Apache
is compiled in C) so I wrote a test case of a simple console application
that is written in C, but calls to object code compiled in C++ with
libpq++, and it behaved correctly. I was able to call destructors fine,
and memory was instantly returned and the connection closed.

So... I'm really at a loss here on what's going on, and wondering if
anyone has some insight? I can give more technical details if necessary,
that's just the gist of the problem.


Thanks, and Regards,
Jw.
--
John Worsley - Command Prompt, Inc.
jlx@commandprompt.com By way of pgsql-interfaces@commandprompt.com

Hello all,

Inspired by others who have recently gotten PostgreSQL functions to return
sets, I set out to create my own. I have on more than one occasion wished I
could run a query across databases or servers (similar to a dblink in Oracle
or a linked db in MSSQL). Attached is a C file which is my attempt. It
exports two functions:

dblink(text connect_string, text sql, text field_separator)
dblink_tok(text delimited_text, text field_separator, int ord_position)

The functions are used as shown in the following example:

select dblink_tok(t1.f1,'~',0)::int as vl_id,dblink_tok(t1.f1,'~',1)::text as vl_guid,dblink_tok(t1.f1,'~',2)::text as
vl_pri_email,dblink_tok(t1.f1,'~',3)::textas vl_acct_pass_phrase,dblink_tok(t1.f1,'~',4)::text as
vl_email_relname,dblink_tok(t1.f1,'~',5)::textas vl_hwsn_relname,dblink_tok(t1.f1,'~',6)::timestamp as
vl_mod_dt,dblink_tok(t1.f1,'~',7)::intas vl_status
 
from(select dblink('host=192.168.5.150 port=5432 dbname=vsreg_001 user=postgres
password=postgres','select * from vs_lkup','~') as f1) as t1

By doing "create view vs_lkup_rm as . . ." with the above query, from a
database on another server, I can then write:
"select * from vs_lkup" and get results just as if I were on 192.168.5.150
(sort of -- see problem below).

I have one question, and one problem regarding this.

First the question: is there any way to get the dblink function to return
setof composite -- i.e. return tuples instead of scalar values? The
documentation indicates that a function can return a composite type, but my
attempts all seemed to produce only pointer values (to the tuples?)

Now the problem: as I stated above, "select * from vs_lkup" returns results
just as if I were on 192.168.5.150 -- but if I try "select * from vs_lkup
WHERE vl_id = 1" or "select * from vs_lkup WHERE vl_pri_email in
('email1@foo.com')" I get the following error message: "ERROR:  Set-valued
function called in context that cannot accept a set". Any ideas how to work
around this?

Thanks,

Joe Conway

On Mon, 28 May 2001, J. T. Vermeulen wrote:
>On Sun, 27 May 2001, pgsql-interfaces@commandprompt.com wrote:
>>So... I'm really at a loss here on what's going on, and wondering if
>>anyone has some insight? I can give more technical details if necessary,
>>that's just the gist of the problem.
>Just a thought: You're not using exit(), are you?

Heh, nope. ;) I wish it were that simple.

The httpd process itself doesn't end, in fact. It just keeps on running,
which is the problem, because it accumulates postmaster connections with
each request until it's killed.

What is perplexing is why the destructor doesn't seem to be doing its job
when called from inside httpd, inexplicably. It is definitely reaching
that point in the code, but it just doesn't disconnect.

Further frustrating the matter, I tried not dynamically creating the
PgDatabase on the heap, and just made it a local variable on the stack.
Eg, PgDatabase foo(); instead of PgDatabase *foo = new PgDatabase.

Guess what? It still keeps the connection after leaving its scope. Grr.

This is easily the most frustrating programming problem I have experienced
in using PostgreSQL.


Jw.
--
John Worsley, Command Prompt, Inc.
jlx@commandprompt.com by way of pgsql-interfaces@commandprompt.com.

On Tue, 29 May 2001, jtv wrote:
>On Mon, May 28, 2001 at 10:25:31AM -0700, pgsql-interfaces@commandprompt.com wrote:
>>What is perplexing is why the destructor doesn't seem to be doing its job
>>when called from inside httpd, inexplicably. It is definitely reaching
>>that point in the code, but it just doesn't disconnect.
>Well, that's me stumped.  The only other thing I can think of is that some
>query/command execution code forgets to clear up its result set structure
>pointer prior to replacing it with a new result set, thereby confusing the
>underlying C library with invalid pointers.  Or something.

I thought that might be the case too, so I commented out all the code that
actually uses the PgDatabase *sqlDB variable, and it still happens. You
can imagine how frustrating this would be. ;)

I essentially have a block of code that creates a connection, does nothing
with it, SHOULD delete it when it hits the destructor, but either doesn't
destruct at all, or doesn't properly call CloseConnection, or something
more primitive goes wrong. At any rate, the connection, even unused, WILL
NOT GO AWAY! It's mind-boggling.

>>Guess what? It still keeps the connection after leaving its scope. Grr.
>How long-lived are these connections?  I've been seeing my problems after
>leaving them idle for perhaps several seconds.  Shortening their lifetimes
>happened to help in my case <shudder>.

Weird! Well, in my case, I'm just creating an open connection, and it just
hangs out indefinitely. I let it run a full day once just to check. They
hung around for a day or so before I re-started apache to clean up the
processes.

The next step in trouble-shooting this I suppose is to build the dummy
Apache module with libpq++ and see if it the same behavior is exhibited.
If not, it's got to be something deeper in the core of my module.

I'm not sure I like either outcome of that test.


Jw.
--
John Worsley at Command Prompt, Inc.
jlx@commandprompt.com by way of pgsql-interfaces@commandprompt.com

On Tue, May 29, 2001 at 07:42:46AM -0700, pgsql-interfaces@commandprompt.com wrote:
> 
> The next step in trouble-shooting this I suppose is to build the dummy
> Apache module with libpq++ and see if it the same behavior is exhibited.
> If not, it's got to be something deeper in the core of my module.

Have you tried using just libpq, and just "inlining" the code that libpq++
*should* be executing based on your invocations?  Sounds like you don't even
need the PQresult structure to reproduce the problem, so it should be doable.


Jeroen

On Thu, 31 May 2001, jtv wrote:
>On Tue, May 29, 2001 at 07:42:46AM -0700, pgsql-interfaces@commandprompt.com wrote:
>>The next step in trouble-shooting this I suppose is to build the dummy
>>Apache module with libpq++ and see if it the same behavior is exhibited.
>>If not, it's got to be something deeper in the core of my module.
>Have you tried using just libpq, and just "inlining" the code that libpq++
>*should* be executing based on your invocations?  Sounds like you don't even
>need the PQresult structure to reproduce the problem, so it should be doable.

Unfortunately, this is a kind of at a deep level in an application
utilizing libpq++, and it'd be somewhat nightmarish to refactor all the
libpq++ code into procedural libpq.

Theoretically I could use libpq for this one function, since my other
libpq++ objects live and die correctly with the httpd process, and have
the two different APIs co-existing, it's just not ideal.

Moreover, I want to know WHY this is happening! ;) This is truly bizarre.


Jw.
--
jlx@commandprompt.com by way of pgsql-interfaces@commandprompt.com