Connect from background worker thread to database

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

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

If I remove comment from BackgroundWorkerInitializeConnection  https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L179  and comment InitPostgres  https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L98 , I have the same errors in log:

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>:

Воскресенье, 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.

--
Alexey Vasiliev

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

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

Понедельник, 25 ноября 2013, 8:31 +09:00 от Michael Paquier <michael.paquier@gmail.com>:

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

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.

Thanks again.

--
Alexey Vasiliev

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

Понедельник, 25 ноября 2013, 13:31 +09:00 от Michael Paquier <michael.paquier@gmail.com>:

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

Thanks. I will look how to do this in the best way by your suggestions.

--
Alexey Vasiliev