All database connections being consumed up and not released with ORM and postgres

dev google-group-archive
1

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without any
success. I’ve tried increasing (as well as decreasing) the max connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t get
the above error message but rather Lucee just times out (which makes sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am
using a combination of ORM as well as non-ORM SQL statements. I use two
data sources.

A “ps auxwww|grep postgres” shows that there are lots of idle connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or
solve?

Thanks,
Cage

postgres 8336 0.0 0.2 220396 7828 ? Ss 02:34 0:00 postgres:
username dbname 127.0.0.1(36112) idle

postgres 8337 0.0 0.3 220396 10292 ? Ss 02:34 0:00
postgres: username dbname 127.0.0.1(36113) idle

postgres 8339 0.0 0.1 219268 3484 ? Ss 02:34 0:00 postgres:
username dbname 127.0.0.1(36115) idle

postgres 8359 0.0 0.1 219268 3484 ? Ss 02:34 0:00 postgres:
username dbname 127.0.0.1(36120) idle

postgres 8364 0.0 0.1 219268 3484 ? Ss 02:34 0:00 postgres:
username dbname 127.0.0.1(36121) idle

postgres 8410 0.0 1.2 220940 38620 ? Ss 02:35 0:00 postgres:
username dbname 127.0.0.1(36125) idle

postgres 8415 0.0 0.3 220220 9736 ? Ss 02:35 0:00 postgres:
username dbname 127.0.0.1(36127) idle

postgres 8428 0.0 0.1 219264 3500 ? Ss 02:35 0:00 postgres:
username dbname 127.0.0.1(36130) idle

postgres 8446 0.2 2.0 220540 61960 ? Ss 02:36 0:04 postgres:
username dbname 127.0.0.1(36132) idle

postgres 8457 0.0 0.1 219264 3508 ? Ss 02:37 0:00 postgres:
username dbname 127.0.0.1(36136) idle

postgres 8539 0.3 2.7 220564 83256 ? Ss 02:42 0:04 postgres:
username dbname 127.0.0.1(36145) idle

postgres 8628 0.4 2.7 220200 83288 ? Ss 02:47 0:04 postgres:
username dbname 127.0.0.1(36158) idle

postgres 8664 0.5 2.7 221480 84672 ? Ss 02:49 0:04 postgres:
username dbname 127.0.0.1(36163) idle

postgres 8674 0.0 0.5 220216 17320 ? Ss 02:49 0:00 postgres:
username dbname 127.0.0.1(36165) idle

postgres 8677 0.0 0.2 220084 8756 ? Ss 02:49 0:00 postgres:
username dbname 127.0.0.1(36168) idle

postgres 8744 0.0 0.1 219264 3512 ? Ss 02:52 0:00 postgres:
username dbname 127.0.0.1(36176) idle

postgres 8750 0.6 2.0 220540 62004 ? Ss 02:52 0:03 postgres:
username dbname 127.0.0.1(36178) idle

postgres 8752 0.1 0.5 220216 18372 ? Ss 02:52 0:00 postgres:
username dbname 127.0.0.1(36179) idle

postgres 8759 0.1 1.5 221516 47096 ? Ss 02:52 0:00 postgres:
username dbname 127.0.0.1(36180) idle

postgres 8760 0.6 2.0 220156 63316 ? Ss 02:52 0:04 postgres:
username dbname 127.0.0.1(36181) idle

postgres 8785 0.0 0.1 219264 3512 ? Ss 02:53 0:00 postgres:
username dbname 127.0.0.1(36183) idle

postgres 8789 0.0 0.3 220220 10172 ? Ss 02:53 0:00 postgres:
username dbname 127.0.0.1(36185) idle

2

Hi Cage

Sounds like it might be related to this bug:

https://luceeserver.atlassian.net/browse/LDEV-119

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.On 29 November 2015 at 08:13, Cage Sarin <@Cage_Sarin> wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without any
success. I’ve tried increasing (as well as decreasing) the max connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t get
the above error message but rather Lucee just times out (which makes sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or solve?

3

Not seeing the issue when using event handling either. Sorry I can’t help more. My connection re-usage is rock solid with every type of query I can think of, aside from the issues I noted before.

Is there anything you’re doing inside of your pre/post create/update/delete event handlers (or pre/postFlush(), pre/postLoad(), etc ) that might be causing the issue?

Jon

I am doing all of the settings via admin and no connection timeout settings or creation of data sources programmatically.

Driver info is below.

Any chance you’d be able to test with a query in an ORM event handler? Just want to make sure that’s not it since the problem started soon after I implemented those methods (or at least I think it did - maybe coincidence!).

Thanks very much.
Cage

Database (Name/Version) PostgreSQL 9.4.1
Driver (Name/Version) PostgreSQL Native Driver PostgreSQL 8.3 JDBC4 with SSL (build 607) (JDBC 4.0)

Cage,

I ran some tests an found that there was one setting that will append connections if it is changed. If the connection timeout is changed or set programmatically, it will append new connections and the other ones will hang out there until they expire. I’m not sure if that value is being set anywhere within the application, but it will produce the problem you’re seeing.

Also, programically creating the DSN repeatedly will orphan connections. Are you creating the DSN within the application startup in a way that might be re-creating that datasource when you reload your application?

Other than that, I used both of your query methods and wasn’t able to duplicate the problem (my standard development timeout is set to 1 minute but changing it didn’t seem to .

What is your PostgreSQL driver version ( In the admin under Driver Name/Version)? That might give a clue as well.

Jon

Including examples below. In addition, I use standard entityLoad / entityNew / entitySave ORM functions as well.

example ORM query:

deletedCases = ormExecuteQuery(“update table1 set deleted = true,
datetimemodified = :datetimemodified
where mydate = :mydate
and location = :location
and id not in (:idsProcessed)”, {mydate=workDT, location=siteName, idsProcessed=idsProcessed, datetimemodified=Now()});

example SQL query:

public void function postInsert( any entity )

{

var id = this.getID();
var something = this.getSomething();
var surgdate = this.getSurgdate();

var sql = "INSERT INTO messages(type,target,creator,value,workdate) VALUES(:type,:target,:creator,:value,:workdate);";
var params = {type='messagetype1',
                target='{"id":#id#, "something":#something#}',
                creator='#Session.cfid#',
                value="somevalue",
                workdate={value = surgdate, cfsqltype="CF_SQL_DATE"}};

var message = queryExecute(
                sql,
                params,
                {datasource="datasourcename"});

}

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit and have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or using the standard query() ). There may be something in the arguments passed to your query method that’s causing the issue.

Jon

I’ll double check but I’m pretty sure I’m not doing any ormReload() (except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

Hi Cage

Sounds like it might be related to this bug:

https://luceeserver.atlassian.net/browse/LDEV-119

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.On November 29, 2015 at 5:14:42 PM, Cage Sarin (@Cage_Sarin) wrote:
On Sunday, November 29, 2015 at 5:07:23 PM UTC-5, Jon Clausen wrote:
On November 29, 2015 at 12:28:43 PM, Cage Sarin (warp...@gmail.com) wrote:
On Sunday, November 29, 2015 at 12:15:00 PM UTC-5, Jon Clausen wrote:
On November 29, 2015 at 8:58:16 AM, Cage Sarin (warp...@gmail.com) wrote:
On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without any
success. I’ve tried increasing (as well as decreasing) the max connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t get
the above error message but rather Lucee just times out (which makes sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or solve?
–
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/1977ab64-c30e-46f9-838b-89221527f70e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/eecb10f9-3aed-42df-bd27-4c03333717b4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

4

Also, I’m using ORM event handling to insert data into a messages table
using executeQuery…not sure if that could be a source of problems…

5

Including examples below. In addition, I use standard entityLoad /
entityNew / entitySave ORM functions as well.

example ORM query:

deletedCases = ormExecuteQuery(“update table1 set deleted = true,
datetimemodified = :datetimemodified
where mydate = :mydate
and location = :location
and id not in (:idsProcessed)”, {mydate=workDT, location=siteName, idsProcessed=idsProcessed, datetimemodified=Now()});

example SQL query:

public void function postInsert( any entity )

{

var id = this.getID();
var something = this.getSomething();
var surgdate = this.getSurgdate();

var sql = "INSERT INTO messages(type,target,creator,value,workdate) VALUES(:type,:target,:creator,:value,:workdate);";
var params = {type='messagetype1',
                target='{"id":#id#, "something":#something#}',
                creator='#Session.cfid#',
                value="somevalue",
                workdate={value = surgdate, cfsqltype="CF_SQL_DATE"}};

var message = queryExecute(
                sql,
                params,
                {datasource="datasourcename"});

}On Sunday, November 29, 2015 at 12:15:00 PM UTC-5, Jon Clausen wrote:

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit and
have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or
using the standard query() ). There may be something in the arguments
passed to your query method that’s causing the issue.

Jon

On November 29, 2015 at 8:58:16 AM, Cage Sarin (warp...@gmail.com <javascript:>) wrote:

I’ll double check but I’m pretty sure I’m not doing any ormReload()
(except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

Hi Cage

Sounds like it might be related to this bug:

[LDEV-119] - Lucee

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without any
success. I’ve tried increasing (as well as decreasing) the max
connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t
get
the above error message but rather Lucee just times out (which makes
sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am
using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle
connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or
solve?

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+un...@googlegroups.com <javascript:>.
To post to this group, send email to lu...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

6

I’ll double check but I’m pretty sure I’m not doing any ormReload() (except
when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

Hi Cage

Sounds like it might be related to this bug:

[LDEV-119] - Lucee

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.

On 29 November 2015 at 08:13, Cage Sarin <warp...@gmail.com <javascript:>> wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without any
success. I’ve tried increasing (as well as decreasing) the max
connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t
get
the above error message but rather Lucee just times out (which makes
sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am
using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle
connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or
solve?

7

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit and have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or using the standard query() ). There may be something in the arguments passed to your query method that’s causing the issue.

Jon

I’ll double check but I’m pretty sure I’m not doing any ormReload() (except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

Hi Cage

Sounds like it might be related to this bug:

https://luceeserver.atlassian.net/browse/LDEV-119

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.On November 29, 2015 at 8:58:16 AM, Cage Sarin (@Cage_Sarin) wrote:
On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without any
success. I’ve tried increasing (as well as decreasing) the max connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t get
the above error message but rather Lucee just times out (which makes sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or solve?
–
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

8

Cage,

I ran some tests an found that there was one setting that will append connections if it is changed. If the connection timeout is changed or set programmatically, it will append new connections and the other ones will hang out there until they expire. I’m not sure if that value is being set anywhere within the application, but it will produce the problem you’re seeing.

Also, programically creating the DSN repeatedly will orphan connections. Are you creating the DSN within the application startup in a way that might be re-creating that datasource when you reload your application?

Other than that, I used both of your query methods and wasn’t able to duplicate the problem (my standard development timeout is set to 1 minute but changing it didn’t seem to .

What is your PostgreSQL driver version ( In the admin under Driver Name/Version)? That might give a clue as well.

Jon

Including examples below. In addition, I use standard entityLoad / entityNew / entitySave ORM functions as well.

example ORM query:

deletedCases = ormExecuteQuery(“update table1 set deleted = true,
datetimemodified = :datetimemodified
where mydate = :mydate
and location = :location
and id not in (:idsProcessed)”, {mydate=workDT, location=siteName, idsProcessed=idsProcessed, datetimemodified=Now()});

example SQL query:

public void function postInsert( any entity )

{

var id = this.getID();
var something = this.getSomething();
var surgdate = this.getSurgdate();

var sql = "INSERT INTO messages(type,target,creator,value,workdate) VALUES(:type,:target,:creator,:value,:workdate);";
var params = {type='messagetype1',
                target='{"id":#id#, "something":#something#}',
                creator='#Session.cfid#',
                value="somevalue",
                workdate={value = surgdate, cfsqltype="CF_SQL_DATE"}};

var message = queryExecute(
                sql,
                params,
                {datasource="datasourcename"});

}

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit and have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or using the standard query() ). There may be something in the arguments passed to your query method that’s causing the issue.

Jon

I’ll double check but I’m pretty sure I’m not doing any ormReload() (except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

Hi Cage

Sounds like it might be related to this bug:

https://luceeserver.atlassian.net/browse/LDEV-119

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.On November 29, 2015 at 12:28:43 PM, Cage Sarin (@Cage_Sarin) wrote:
On Sunday, November 29, 2015 at 12:15:00 PM UTC-5, Jon Clausen wrote:
On November 29, 2015 at 8:58:16 AM, Cage Sarin (warp...@gmail.com) wrote:
On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without any
success. I’ve tried increasing (as well as decreasing) the max connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t get
the above error message but rather Lucee just times out (which makes sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or solve?
–
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/1977ab64-c30e-46f9-838b-89221527f70e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

9

I am doing all of the settings via admin and no connection timeout settings
or creation of data sources programmatically.

Driver info is below.

Any chance you’d be able to test with a query in an ORM event handler? Just
want to make sure that’s not it since the problem started soon after I
implemented those methods (or at least I think it did - maybe coincidence!).

Thanks very much.
Cage

Database (Name/Version)PostgreSQL 9.4.1Driver (Name/Version)PostgreSQL
Native Driver PostgreSQL 8.3 JDBC4 with SSL (build 607) (JDBC 4.0)On Sunday, November 29, 2015 at 5:07:23 PM UTC-5, Jon Clausen wrote:

Cage,

I ran some tests an found that there was one setting that will append
connections if it is changed. If the connection timeout is changed or set
programmatically, it will append new connections and the other ones will
hang out there until they expire. I’m not sure if that value is being set
anywhere within the application, but it will produce the problem you’re
seeing.

Also, programically creating the DSN repeatedly will orphan connections.
Are you creating the DSN within the application startup in a way that
might be re-creating that datasource when you reload your application?

Other than that, I used both of your query methods and wasn’t able to
duplicate the problem (my standard development timeout is set to 1 minute
but changing it didn’t seem to .

What is your PostgreSQL driver version ( In the admin under Driver
Name/Version)? That might give a clue as well.

Jon

On November 29, 2015 at 12:28:43 PM, Cage Sarin (warp...@gmail.com <javascript:>) wrote:

Including examples below. In addition, I use standard entityLoad /
entityNew / entitySave ORM functions as well.

example ORM query:

deletedCases = ormExecuteQuery(“update table1 set deleted = true,
datetimemodified = :datetimemodified
where mydate = :mydate
and location = :location
and id not in (:idsProcessed)”, {mydate=workDT, location=siteName, idsProcessed=idsProcessed, datetimemodified=Now()});

example SQL query:

public void function postInsert( any entity )

{

var id = this.getID();
var something = this.getSomething();
var surgdate = this.getSurgdate();

var sql = "INSERT INTO messages(type,target,creator,value,workdate) VALUES(:type,:target,:creator,:value,:workdate);";
var params = {type='messagetype1',
                target='{"id":#id#, "something":#something#}',
                creator='#Session.cfid#',
                value="somevalue",
                workdate={value = surgdate, cfsqltype="CF_SQL_DATE"}};

var message = queryExecute(
                sql,
                params,
                {datasource="datasourcename"});

}

On Sunday, November 29, 2015 at 12:15:00 PM UTC-5, Jon Clausen wrote:

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit
and have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or
using the standard query() ). There may be something in the arguments
passed to your query method that’s causing the issue.

Jon

On November 29, 2015 at 8:58:16 AM, Cage Sarin (warp...@gmail.com) wrote:

I’ll double check but I’m pretty sure I’m not doing any ormReload()
(except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

Hi Cage

Sounds like it might be related to this bug:

[LDEV-119] - Lucee

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for
non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and
then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without
any
success. I’ve tried increasing (as well as decreasing) the max
connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t
get
the above error message but rather Lucee just times out (which makes
sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am
using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle
connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or
solve?

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+un...@googlegroups.com <javascript:>.
To post to this group, send email to lu...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/1977ab64-c30e-46f9-838b-89221527f70e%40googlegroups.com
https://groups.google.com/d/msgid/lucee/1977ab64-c30e-46f9-838b-89221527f70e%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

10

Can’t think of anything unusual. Just the SQL queries in event handlers.

And in one persistent CFC, I’m doing some encrypt/decrypt for data (see
below) using Lucee encrypt functions.

I am using Taffy with REST API resources in a subfolder of my main web app.
It has a Application.cfc that extends the taffy.core.api and in it, I
specify some of the same settings as the Application.cfc in my main app
(i.e., datasource, eventhandling, etc are specified a second time) but the
app name is the same in both Application.cfc’s. These settings duplicated
include:

this.name = “myappname”;

this.ormEnabled = true;
this.ormsettings = {datasource = “datasourcename”, cfclocation = “…/models”, eventhandling = true, logSQL = false};

public void function preInsert( any entity )
{
this.encryptProperties();
}

public void function preUpdate( any entity )
{
this.encryptProperties();
}

public void function postLoad( any entity )
{
this.decryptProperties();
}

public void function encryptProperties()
{
var props = getMetaData( this ).properties;

for (var prop in props){
  if (structKeyExists(  prop, "encrypted" ) AND prop.encrypted){
    var n = prop.name;
    if (!isNull(variables[n])){
        variables[ n ] = encrypt( variables[ n ], Application.stcMagic.key, Application.stcMagic.type, Application.stcMagic.block );
    }
  }
}

}On Sunday, November 29, 2015 at 5:41:09 PM UTC-5, Jon Clausen wrote:

Not seeing the issue when using event handling either. Sorry I can’t help
more. My connection re-usage is rock solid with every type of query I can
think of, aside from the issues I noted before.

Is there anything you’re doing inside of your pre/post
create/update/delete event handlers (or pre/postFlush(), pre/postLoad(),
etc ) that might be causing the issue?

Jon

On November 29, 2015 at 5:14:42 PM, Cage Sarin (warp...@gmail.com <javascript:>) wrote:

I am doing all of the settings via admin and no connection timeout
settings or creation of data sources programmatically.

Driver info is below.

Any chance you’d be able to test with a query in an ORM event handler?
Just want to make sure that’s not it since the problem started soon after I
implemented those methods (or at least I think it did - maybe coincidence!).

Thanks very much.
Cage

Database (Name/Version) PostgreSQL 9.4.1 Driver (Name/Version) PostgreSQL
Native Driver PostgreSQL 8.3 JDBC4 with SSL (build 607) (JDBC 4.0)

On Sunday, November 29, 2015 at 5:07:23 PM UTC-5, Jon Clausen wrote:

Cage,

I ran some tests an found that there was one setting that will append
connections if it is changed. If the connection timeout is changed or set
programmatically, it will append new connections and the other ones will
hang out there until they expire. I’m not sure if that value is being set
anywhere within the application, but it will produce the problem you’re
seeing.

Also, programically creating the DSN repeatedly will orphan connections.
Are you creating the DSN within the application startup in a way that
might be re-creating that datasource when you reload your application?

Other than that, I used both of your query methods and wasn’t able to
duplicate the problem (my standard development timeout is set to 1 minute
but changing it didn’t seem to .

What is your PostgreSQL driver version ( In the admin under Driver
Name/Version)? That might give a clue as well.

Jon

On November 29, 2015 at 12:28:43 PM, Cage Sarin (warp...@gmail.com) wrote:

Including examples below. In addition, I use standard entityLoad /
entityNew / entitySave ORM functions as well.

example ORM query:

deletedCases = ormExecuteQuery(“update table1 set deleted = true,
datetimemodified = :datetimemodified
where mydate = :mydate
and location = :location
and id not in (:idsProcessed)”, {mydate=workDT, location=siteName, idsProcessed=idsProcessed, datetimemodified=Now()});

example SQL query:

public void function postInsert( any entity )

{

var id = this.getID();
var something = this.getSomething();
var surgdate = this.getSurgdate();

var sql = "INSERT INTO messages(type,target,creator,value,workdate) VALUES(:type,:target,:creator,:value,:workdate);";
var params = {type='messagetype1',
                target='{"id":#id#, "something":#something#}',
                creator='#Session.cfid#',
                value="somevalue",
                workdate={value = surgdate, cfsqltype="CF_SQL_DATE"}};

var message = queryExecute(
                sql,
                params,
                {datasource="datasourcename"});

}

On Sunday, November 29, 2015 at 12:15:00 PM UTC-5, Jon Clausen wrote:

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit
and have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or
using the standard query() ). There may be something in the arguments
passed to your query method that’s causing the issue.

Jon

On November 29, 2015 at 8:58:16 AM, Cage Sarin (warp...@gmail.com) wrote:

I’ll double check but I’m pretty sure I’m not doing any ormReload()
(except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

Hi Cage

Sounds like it might be related to this bug:

[LDEV-119] - Lucee

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for
non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and
then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without
any
success. I’ve tried increasing (as well as decreasing) the max
connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I
don’t get
the above error message but rather Lucee just times out (which makes
sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I
am using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle
connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or
solve?

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google
Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/1977ab64-c30e-46f9-838b-89221527f70e%40googlegroups.com
https://groups.google.com/d/msgid/lucee/1977ab64-c30e-46f9-838b-89221527f70e%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+un...@googlegroups.com <javascript:>.
To post to this group, send email to lu...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/eecb10f9-3aed-42df-bd27-4c03333717b4%40googlegroups.com
https://groups.google.com/d/msgid/lucee/eecb10f9-3aed-42df-bd27-4c03333717b4%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

11

As I say, I can replicate this at will by calling OrmReload() as
described in LDEV-119. I also use the default 1 minute connection
timeout for datasources and any connections made in the 60 seconds
prior to calling OrmReload() become detached and only die when killed
by the database. To work around this I’ve had to set my database’s
timeout for inactive connections to be much lower than the default -
which is 8 hours!On 29 November 2015 at 22:07, Jon Clausen <@Jon_Clausen> wrote:

I ran some tests an found that there was one setting that will append
connections if it is changed. If the connection timeout is changed or set
programmatically, it will append new connections and the other ones will
hang out there until they expire. I’m not sure if that value is being set
anywhere within the application, but it will produce the problem you’re
seeing.

12

You could also try setting autoManageSession=false. Coupled with
flushAtRequestEnd that will stop Hibernate from interfering in how
sessions are handled. You should then control them yourself by
wrapping changes that need persisting in transaction{} blocks (no need
for OrmFlush()).

I always use these settings and the only time I see the detached
connections are when I call OrmReload().

Julian.On 30 November 2015 at 04:49, Cage Sarin <@Cage_Sarin> wrote:

I tried one more thing, which is use flushatrequestend = false, and now I’m seeing less of a problem. It appears all the connections are being released. Any thoughts on why this is helping? And are there any unexpected consequences of doing this? I’m pretty sure I’m doing ormFlush() after making my changes to ORM data entities.

13

I tried one more thing, which is use flushatrequestend = false, and now I’m
seeing less of a problem. It appears all the connections are being
released. Any thoughts on why this is helping? And are there any unexpected
consequences of doing this? I’m pretty sure I’m doing ormFlush() after
making my changes to ORM data entities.On Sunday, November 29, 2015 at 6:10:13 PM UTC-5, Cage Sarin wrote:

Can’t think of anything unusual. Just the SQL queries in event handlers.

And in one persistent CFC, I’m doing some encrypt/decrypt for data (see
below) using Lucee encrypt functions.

I am using Taffy with REST API resources in a subfolder of my main web
app. It has a Application.cfc that extends the taffy.core.api and in it, I
specify some of the same settings as the Application.cfc in my main app
(i.e., datasource, eventhandling, etc are specified a second time) but the
app name is the same in both Application.cfc’s. These settings duplicated
include:

this.name = “myappname”;

this.ormEnabled = true;
this.ormsettings = {datasource = “datasourcename”, cfclocation = “…/models”, eventhandling = true, logSQL = false};

public void function preInsert( any entity )
{
this.encryptProperties();
}

public void function preUpdate( any entity )
{
this.encryptProperties();
}

public void function postLoad( any entity )
{
this.decryptProperties();
}

public void function encryptProperties()
{
var props = getMetaData( this ).properties;

for (var prop in props){
  if (structKeyExists(  prop, "encrypted" ) AND prop.encrypted){
    var n = prop.name;
    if (!isNull(variables[n])){
        variables[ n ] = encrypt( variables[ n ], Application.stcMagic.key, Application.stcMagic.type, Application.stcMagic.block );
    }
  }
}

}

On Sunday, November 29, 2015 at 5:41:09 PM UTC-5, Jon Clausen wrote:

Not seeing the issue when using event handling either. Sorry I can’t
help more. My connection re-usage is rock solid with every type of query I
can think of, aside from the issues I noted before.

Is there anything you’re doing inside of your pre/post
create/update/delete event handlers (or pre/postFlush(), pre/postLoad(),
etc ) that might be causing the issue?

Jon

On November 29, 2015 at 5:14:42 PM, Cage Sarin (warp...@gmail.com) wrote:

I am doing all of the settings via admin and no connection timeout
settings or creation of data sources programmatically.

Driver info is below.

Any chance you’d be able to test with a query in an ORM event handler?
Just want to make sure that’s not it since the problem started soon after I
implemented those methods (or at least I think it did - maybe coincidence!).

Thanks very much.
Cage

Database (Name/Version) PostgreSQL 9.4.1 Driver (Name/Version) PostgreSQL
Native Driver PostgreSQL 8.3 JDBC4 with SSL (build 607) (JDBC 4.0)

On Sunday, November 29, 2015 at 5:07:23 PM UTC-5, Jon Clausen wrote:

Cage,

I ran some tests an found that there was one setting that will append
connections if it is changed. If the connection timeout is changed or set
programmatically, it will append new connections and the other ones will
hang out there until they expire. I’m not sure if that value is being set
anywhere within the application, but it will produce the problem you’re
seeing.

Also, programically creating the DSN repeatedly will orphan connections.
Are you creating the DSN within the application startup in a way that
might be re-creating that datasource when you reload your application?

Other than that, I used both of your query methods and wasn’t able to
duplicate the problem (my standard development timeout is set to 1 minute
but changing it didn’t seem to .

What is your PostgreSQL driver version ( In the admin under Driver
Name/Version)? That might give a clue as well.

Jon

On November 29, 2015 at 12:28:43 PM, Cage Sarin (warp...@gmail.com) wrote:

Including examples below. In addition, I use standard entityLoad /
entityNew / entitySave ORM functions as well.

example ORM query:

deletedCases = ormExecuteQuery(“update table1 set deleted = true,
datetimemodified = :datetimemodified
where mydate = :mydate
and location = :location
and id not in (:idsProcessed)”, {mydate=workDT, location=siteName, idsProcessed=idsProcessed, datetimemodified=Now()});

example SQL query:

public void function postInsert( any entity )

{

var id = this.getID();
var something = this.getSomething();
var surgdate = this.getSurgdate();

var sql = "INSERT INTO messages(type,target,creator,value,workdate) VALUES(:type,:target,:creator,:value,:workdate);";
var params = {type='messagetype1',
                target='{"id":#id#, "something":#something#}',
                creator='#Session.cfid#',
                value="somevalue",
                workdate={value = surgdate, cfsqltype="CF_SQL_DATE"}};

var message = queryExecute(
                sql,
                params,
                {datasource="datasourcename"});

}

On Sunday, November 29, 2015 at 12:15:00 PM UTC-5, Jon Clausen wrote:

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit
and have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or
using the standard query() ). There may be something in the arguments
passed to your query method that’s causing the issue.

Jon

On November 29, 2015 at 8:58:16 AM, Cage Sarin (warp...@gmail.com) wrote:

I’ll double check but I’m pretty sure I’m not doing any ormReload()
(except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

Hi Cage

Sounds like it might be related to this bug:

[LDEV-119] - Lucee

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for
non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and
then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without
any
success. I’ve tried increasing (as well as decreasing) the max
connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I
don’t get
the above error message but rather Lucee just times out (which makes
sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I
am using
a combination of ORM as well as non-ORM SQL statements. I use two
data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle
connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot
and/or solve?

–
Love Lucee? Become a supporter and be part of the Lucee project today!

You received this message because you are subscribed to the Google
Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google
Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/1977ab64-c30e-46f9-838b-89221527f70e%40googlegroups.com
https://groups.google.com/d/msgid/lucee/1977ab64-c30e-46f9-838b-89221527f70e%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/eecb10f9-3aed-42df-bd27-4c03333717b4%40googlegroups.com
https://groups.google.com/d/msgid/lucee/eecb10f9-3aed-42df-bd27-4c03333717b4%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

14

Hi Bill,

No, sorry, that is the ACF name for the setting. I mis-named it in that reply. Lucee maintains connections via the “Timeout" setting. In Lucee, you can also enable the “Validate” setting ( the PostgreSQL driver is JDBC4 ) to validate the connection before using. Usually, though, that’s not necessary and the validation does add a tiny bit of overhead to each database transaction.

Are you using a proxy or balancer at all between your PostgreSQL server(s) and your Lucee Server(s)? Intermittent connection failure errors, in my experience, are usually caused by a keepalive “misunderstanding” between Lucee and the PostgreSQL server and I’ve seen the intermittent failures when using a proxy between the two, which is why I ask. If so, perchance, then I might suggest using PGBouncer instead of a standard proxy balancer. It’s much more reliable at connection pooling.

You might also want make sure your postgresql.conf tcp_keepalives_idle setting is equal to or higher than your server Timeout setting. If it’s a Windows, machine, you might also want to check your KeepAliveInterval and KeepAliveTime settings in the registry.

HTH,

Jon

Hi Jon,

I’m having an issue with maintaining connections with Lucee and Postgres. I intermittently get a ‘Connection Failure’, next click and everything is fine.

Where is the toggle to ‘Maintain Connections’ in Lucee or are you referring to Postgres. I can’t see any setting for that.

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit and have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or using the standard query() ). There may be something in the arguments passed to your query method that’s causing the issue.

Jon

I’ll double check but I’m pretty sure I’m not doing any ormReload() (except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

Hi Cage

Sounds like it might be related to this bug:

https://luceeserver.atlassian.net/browse/LDEV-119

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.On March 6, 2016 at 4:35:12 PM, Bill Tindal (@Bill_Tindal) wrote:
On Monday, 30 November 2015 04:15:00 UTC+11, Jon Clausen wrote:
On November 29, 2015 at 8:58:16 AM, Cage Sarin (warp...@gmail.com) wrote:
On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without any
success. I’ve tried increasing (as well as decreasing) the max connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t get
the above error message but rather Lucee just times out (which makes sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or solve?
–
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/edf8364d-73cb-4097-aa81-7c1c4a004790%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

15

Thanks Jon.

Not using a proxy or balancer between Lucee and the Postgres server.

I’m trying the ‘validate’ setting now that I understand what it’s doing.
The overhead added is OK for me at this time as I just want to ensure that
this doesn’t happen: The connection attempt failed.

PGBouncer is my next port of call once we get some added traffic.On Monday, 7 March 2016 09:22:07 UTC+11, Jon Clausen wrote:

Hi Bill,

No, sorry, that is the ACF name for the setting. I mis-named it in that
reply. Lucee maintains connections via the “Timeout" setting. In Lucee,
you can also enable the “Validate” setting ( the PostgreSQL driver is JDBC4
) to validate the connection before using. Usually, though, that’s not
necessary and the validation does add a tiny bit of overhead to each
database transaction.

Are you using a proxy or balancer at all between your PostgreSQL server(s)
and your Lucee Server(s)? Intermittent connection failure errors, in my
experience, are usually caused by a keepalive “misunderstanding” between
Lucee and the PostgreSQL server and I’ve seen the intermittent failures
when using a proxy between the two, which is why I ask. If so, perchance,
then I might suggest using PGBouncer instead of a standard proxy balancer.
It’s much more reliable at connection pooling.

You might also want make sure your postgresql.conf tcp_keepalives_idle
setting is equal to or higher than your server Timeout setting. If it’s a
Windows, machine, you might also want to check your KeepAliveInterval and
KeepAliveTime settings in the registry.

HTH,

Jon

On March 6, 2016 at 4:35:12 PM, Bill Tindal (mal...@gmail.com <javascript:>) wrote:

Hi Jon,

I’m having an issue with maintaining connections with Lucee and Postgres.
I intermittently get a ‘Connection Failure’, next click and everything is
fine.

Where is the toggle to ‘Maintain Connections’ in Lucee or are you
referring to Postgres. I can’t see any setting for that.

On Monday, 30 November 2015 04:15:00 UTC+11, Jon Clausen wrote:

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit
and have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or
using the standard query() ). There may be something in the arguments
passed to your query method that’s causing the issue.

Jon

On November 29, 2015 at 8:58:16 AM, Cage Sarin (warp...@gmail.com) wrote:

I’ll double check but I’m pretty sure I’m not doing any ormReload()
(except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

Hi Cage

Sounds like it might be related to this bug:

[LDEV-119] - Lucee

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for
non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and
then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without
any
success. I’ve tried increasing (as well as decreasing) the max
connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t
get
the above error message but rather Lucee just times out (which makes
sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am
using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle
connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or
solve?

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+un...@googlegroups.com <javascript:>.
To post to this group, send email to lu...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/edf8364d-73cb-4097-aa81-7c1c4a004790%40googlegroups.com
https://groups.google.com/d/msgid/lucee/edf8364d-73cb-4097-aa81-7c1c4a004790%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

16

Hi Jon,

I’m having an issue with maintaining connections with Lucee and Postgres. I
intermittently get a ‘Connection Failure’, next click and everything is
fine.

Where is the toggle to ‘Maintain Connections’ in Lucee or are you referring
to Postgres. I can’t see any setting for that.On Monday, 30 November 2015 04:15:00 UTC+11, Jon Clausen wrote:

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit and
have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or
using the standard query() ). There may be something in the arguments
passed to your query method that’s causing the issue.

Jon

On November 29, 2015 at 8:58:16 AM, Cage Sarin (warp...@gmail.com <javascript:>) wrote:

I’ll double check but I’m pretty sure I’m not doing any ormReload()
(except when first starting lucee and the application).
I do do some entityReload()'s though.
Any chance it could be related to using SQL in the ORM event handlers?

On Sunday, November 29, 2015 at 3:47:56 AM UTC-5, Julian Halliwell wrote:

Hi Cage

Sounds like it might be related to this bug:

[LDEV-119] - Lucee

Basically if you call OrmReload() any existing session connections
become “detached” and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.

On 29 November 2015 at 08:13, Cage Sarin warp...@gmail.com wrote:

Hi all. After reloading my web app that has a Lucee + Taffy back end
approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication
superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then
things are fine for a while and then the process repeats!

I’ve tried various datasource settings via the administrator without any
success. I’ve tried increasing (as well as decreasing) the max
connection
limit as well as setting the connection timeout to 0 vs 1 minute vs
something very long. When I decrease the max connection limit, I don’t
get
the above error message but rather Lucee just times out (which makes
sense
as here it is waiting to use a connection).

I’m using Postgresql 9.4 on the same server as Lucee (localhost). I am
using
a combination of ORM as well as non-ORM SQL statements. I use two data
sources.

A “ps auxwww|grep postgres” shows that there are lots of idle
connections
and these don’t go away, even after a long time (see below).

Anyone have any ideas what’s happening and how to troubleshoot and/or
solve?

–
Love Lucee? Become a supporter and be part of the Lucee project today! -
http://lucee.org/supporters/become-a-supporter.html

You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+un...@googlegroups.com <javascript:>.
To post to this group, send email to lu...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com
https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

17

@Jon

I was wondering if you had a ‘default’ config for a production datasource
that connects to PostgreSQL. I’m interested in comparing notes, I’m still
seeing a ‘connection failure’ every now and then and my PostgreSQL DB guys
are wondering why there’s only 1 connection coming from the Lucee server.