How do I shutdown the connection from Rose::DB to Postgres?

[Jeremy Begg]

Hi,

I've written a network service which implements a tunnel/proxy so that a program running on a user's PC can interact with a device connected via a remote device.

A network server process is created for each active user (i.e. as soon as the user starts the program on his or her PC).

When the network server process starts up, the user's PC sends it some credentials and the identity of the remote device to which the user wishes to connect.

The server process validates those details against a local Postgres database via Rose::DB.

My problem is, what do I need to do in my Perl to get it to fully disconnect from the Postgres database once the validation steps have been completed?

I need to do this because many more users are using this service than originally expected, and thereby causing the Postgres "max_connections" to be exceeded.

Once the user's request has been validated the process has no further need for the database, but it runs until the user terminates the TCP session.

As far as I can tell the Rose::DB->disconnect and Rose::DB->release_dbh don't have any effect on the underlying Postgres connection.

Thanks,

Jeremy Begg

[Darren Duncan]

Jeremy, is your service meant to keep running and serve multiple client
requests, or is it meant to quit as soon as it answered a request? If the
former, why not just reuse the same database connection for multiple client
queries. If the latter, then the connection should close when Perl quits if not
sooner. -- Darren Duncan

[Jeremy Begg]

Hi Darren,

The system creates a new server process for each incoming connection; each such process serves a single request and runs to completion then dies.

Ordinarily it would be fine to wait until the Perl script terminates - but these scripts can be running for many hours, and the database connection is only needed when the script is starting up.

Once the user's credentials etc have been verified, the script has no further need for the database.

Thanks,

Jeremy

[Darren Duncan]

Jeremy,

Have you tested what would happen if you use DBI directly and not by way of
Rose::DB? Does the Postgres connection close in a timely manner then?

Also, is your Perl a standalone process that is mainly just your code or is
there some larger application server at play? Is it possible that app server
framework is caching your database connections?

-- Darren Duncan

> --
> Source: https://github.com/siracusa/rose
> CPAN: http://search.cpan.org/dist/Rose-DB-Object
> ---
> You received this message because you are subscribed to the Google Groups
> "Rose::DB::Object" group.
> To unsubscribe from this group and stop receiving emails from it, send an email
> to rose-db-objec...@googlegroups.com
> <mailto:rose-db-objec...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/rose-db-object/4fe321c3-8618-46e3-9233-dbd6a83d6ae7%40googlegroups.com
> <https://groups.google.com/d/msgid/rose-db-object/4fe321c3-8618-46e3-9233-dbd6a83d6ae7%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Jeremy Begg]

Hi Darren

I haven't tried calling DBI directly.

I'm using Rose::DB because we already have a lot of Perl written using it (mostly as part of a Mojolicious web application, although this particular script is not part of that).

Specifically, we have Rose::DB-derived classes for accessing the database tables relating to users and their devices.

So it seemed natural to use those classes in this script.

There is no "app server framework" in use by this script other than the Rose::DB framework itself.

Jeremy

On Monday, May 25, 2020 at 4:59:26 PM UTC+9:30, (Darren Duncan) wrote:

Jeremy,

[Darren Duncan]

Sure, well I'm not suggesting to switch to using plain DBI, rather this is just
about debugging where the source of your disconnecting problem is. -- Darren Duncan

> --
> Source: https://github.com/siracusa/rose
> CPAN: http://search.cpan.org/dist/Rose-DB-Object
> ---
> You received this message because you are subscribed to the Google Groups
> "Rose::DB::Object" group.
> To unsubscribe from this group and stop receiving emails from it, send an email
> to rose-db-objec...@googlegroups.com
> <mailto:rose-db-objec...@googlegroups.com>.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/rose-db-object/88861b4a-6aa8-4fde-bf1c-915804d110ea%40googlegroups.com
> <https://groups.google.com/d/msgid/rose-db-object/88861b4a-6aa8-4fde-bf1c-915804d110ea%40googlegroups.com?utm_medium=email&utm_source=footer>.