Thread: Connect from background worker thread to database

2013-11-24 02:57:43 UTC ERROR:  stack depth limit exceeded

2013-11-24 02:57:43 UTC HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

2013-11-24 02:57:43 UTC CONTEXT:  SQL statement "SELECT COUNT(*) FROM pg_class;"

Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading  so far helped me to this point.


Thanks in advance, Alexey

Hi,

On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote:
> This is part where I try to connect to database:  https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132
,but SPI functions give error in log (it is commented):
 
> 
> 2013-11-24 02:57:43 UTC ERROR:  stack depth limit exceeded
> 2013-11-24 02:57:43 UTC HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after
ensuringthe platform's stack depth limit is adequate.
 
> 2013-11-24 02:57:43 UTC CONTEXT:  SQL statement "SELECT COUNT(*) FROM pg_class;"
> 
> Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how
todo it, just a tip, pull request - anything. Google search and PostgreSQL sources reading  so far helped me to this
point.

At the very least you're calling InitPostgres() instead of
BackgroundWorkerInitializeConnection() which you have commented out. And
the latter should only be called once in every worker.

Greetings,

Andres Freund

2013-11-24 13:35:24 UTC ERROR:  stack depth limit exceeded

2013-11-24 13:35:24 UTC HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

2013-11-24 13:35:24 UTC CONTEXT:  SQL statement "SELECT COUNT(*) FROM pg_class;"

2013-11-24 13:35:25 UTC LOG:  worker process: pg_web (PID 1957) exited with exit code 1

2013-11-24 13:35:26 UTC LOG:  starting background worker process "pg_web"

2013-11-24 13:35:26 UTC INFO:  Start web server on port 8080
If I change InitPostgres to BackgroundWorkerInitializeConnection and leave BackgroundWorkerInitializeConnection in bgw_main, I will have this error:

2013-11-24 13:39:58 UTC ERROR:  invalid processing mode in background worker

2013-11-24 13:39:58 UTC LOG:  worker process: pg_web (PID 2719) exited with exit code 1

2013-11-24 13:39:59 UTC LOG:  starting background worker process "pg_web"
Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund <andres@anarazel.de>:

On Mon, Nov 25, 2013 at 2:10 AM, Alexey Vasiliev <leopard_ne@inbox.ru> wrote:
> Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund
> <andres@anarazel.de>:
>
> Hi,
>
>
> On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote:
>> This is part where I try to connect to database:
>> https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but
>> SPI functions give error in log (it is commented):
>>
>> 2013-11-24 02:57:43 UTC ERROR:  stack depth limit exceeded
>> 2013-11-24 02:57:43 UTC HINT:  Increase the configuration parameter
>> "max_stack_depth" (currently 2048kB), after ensuring the platform's stack
>> depth limit is adequate.
>> 2013-11-24 02:57:43 UTC CONTEXT:  SQL statement "SELECT COUNT(*) FROM
>> pg_class;"
>>
>> Because I doing something in wrong way. I will appreciate for any help:
>> where I doing wrong, link to the article how to do it, just a tip, pull
>> request - anything. Google search and PostgreSQL sources reading  so far
>> helped me to this point.
>
> At the very least you're calling InitPostgres() instead of
> BackgroundWorkerInitializeConnection() which you have commented out. And
> the latter should only be called once in every worker.
>
> Greetings,
>
> Andres Freund
>
>
> Fixed by using PQconnectdb from "libpq-fe.h". Thanks.
You should not need an extra PQconnectdb to connect to a database
using a background worker for your case as far as I understood. By
using that you are requiring a connection to database without using
the internal infrastructure in place, meaning that your bgworker is
not connected to the database server from the inside but from the
outside, like a normal client would do. Aren't to trying to have a
background worker connected to a database when it is initialized with
BgWorkerStart_PostmasterStart? Bgworkers using this start-up mode are
not eligible to initialize database connections. Please use either
BgWorkerStart_ConsistentState or BgWorkerStart_RecoveryFinished and
BackgroundWorkerInitializeConnection will allow a connection to
database without any extra work.
--
Michael

On Mon, Nov 25, 2013 at 1:02 PM, Alexey Vasiliev <leopard_ne@inbox.ru> wrote:
> Thanks, I just try this. This work if I working with database in loop inside
> bgw_main function. But if I create threads inside bgw_main and try to
> connect to database - I have errors "stack depth limit exceeded" . The code:
>
> https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L195 - connect to
> database
> https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L100-L131 -
> "http_event_handler" function execute in threads, because it handle http
> requests. And this code not work. BTW, I need connect to database depend
> from request url, so execute BackgroundWorkerInitializeConnection at the
> beginning not enough.
There is a design problem with your application when trying to create
threads with mg_start in order to grab events. Note that I am not
familiar with mongoose, but with bgworkers you cannot simply create
new threads that would be able to connect to server concurrently. A
model that would be more suited with bgworkers would be something
like:
- Handle event messages by for example opening a port or monitoring
the events on a single bgworker launched by bgw_main, with for example
a message queue model (possible with 9.3). Connection to database
would be done with a single connection, managed within the loop of
bgw_main.
- Create a new bgworker dynamically each time a new event comes in
(possible only with 9.4~). The top work would be done by a central
bgworker initialized with server, which would treat events and kick
new slave workers when necessary.

Regards,
-- 
Michael

On Mon, Nov 25, 2013 at 1:02 PM, Alexey Vasiliev <leopard_ne@inbox.ru> wrote:
> Thanks, I just try this. This work if I working with database in loop inside
> bgw_main function. But if I create threads inside bgw_main and try to
> connect to database - I have errors "stack depth limit exceeded" . The code:
>
> https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L195 - connect to
> database
> https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L100-L131 -
> "http_event_handler" function execute in threads, because it handle http
> requests. And this code not work. BTW, I need connect to database depend
> from request url, so execute BackgroundWorkerInitializeConnection at the
> beginning not enough.
There is a design problem with your application when trying to create
threads with mg_start in order to grab events. Note that I am not
familiar with mongoose, but with bgworkers you cannot simply create
new threads that would be able to connect to server concurrently. A
model that would be more suited with bgworkers would be something
like:
- Handle event messages by for example opening a port or monitoring
the events on a single bgworker launched by bgw_main, with for example
a message queue model (possible with 9.3). Connection to database
would be done with a single connection, managed within the loop of
bgw_main.
- Create a new bgworker dynamically each time a new event comes in
(possible only with 9.4~). The top work would be done by a central
bgworker initialized with server, which would treat events and kick
new slave workers when necessary.

Regards,
--
Michael


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers