Changing database connection options?

RT Users
1

Hi,
another question: is there any way to change database connections
options from config?
I.e. I want to change what’s being passed as 4th argument to
DBI->connect().

Reason for this is pretty simple: I’m using PostgreSQL, and DBD::Pg is
choosing by default to use server side prepared statements. I don’t like
them, and would like to disable them, but I’d prefer not to modify RT
source itself, so I was hoping for some way to make it happen from
config.

To show some example.

When $dbh is created like this;

$dbh = DBI->connect( ‘dbi:Pg:dbname=xxx’, ‘yy’, ‘zz’ );

DBD::Pg will use server side prepared statements.

To disable this “feature”, one have to either:

$dbh->{pg_server_prepare} = 0;

or:

$dbh = DBI->connect( ‘dbi:Pg:dbname=xxx’, ‘yy’, ‘zz’, { pg_server_prepare => 0 } );

In case you need to know why i want to disable server side prepared
statements:

  1. Prepared statements gotcha – select * from depesz;
  2. it breaks connection pooling with pgbouncer (and I know I can use
    different pooling mode, but “session pooling” is pretty much useless)

Best regards,

depesz

Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

2

Hi,
another question: is there any way to change database connections
options from config?
I.e. I want to change what’s being passed as 4th argument to
DBI->connect().

You’d need to patch DBIx::SearchBuilder to provide an option for RT to
use and then patch RT to use that option.

-kevin

3

Hi,
another question: is there any way to change database connections
options from config?
I.e. I want to change what’s being passed as 4th argument to
DBI->connect().

You’d need to patch DBIx::SearchBuilder to provide an option for RT to
use and then patch RT to use that option.

Hmm. This looks like a very complicated task. Wouldn’t it be possible,
to just call:

$dbh->{‘pg_server_prepare’} = 0;
from rt?

Reason - server side prepared statements are rarely useful, usually they
do not speed things up, and they break some setups.

Of course I can modify my own copy of rt, but patching it again and
again after every upgrade seems to be suboptimal.

Best regards,

depesz

Linkedin: Hubert Lubaczewski - Instructure, Inc. | LinkedIn / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

4

Hi,
another question: is there any way to change database connections
options from config?
I.e. I want to change what’s being passed as 4th argument to
DBI->connect().

You’d need to patch DBIx::SearchBuilder to provide an option for RT to
use and then patch RT to use that option.

Hmm. This looks like a very complicated task. Wouldn’t it be possible,
to just call:

I’m delineating the right way to do it.

$dbh->{‘pg_server_prepare’} = 0;
from rt?

Reason - server side prepared statements are rarely useful, usually they
do not speed things up, and they break some setups.

Of course I can modify my own copy of rt, but patching it again and
again after every upgrade seems to be suboptimal.

I’m leery to make a change like that for RT without knowing much more
about why I should be making it.

-kevin

5

I’m delineating the right way to do it.

$dbh->{‘pg_server_prepare’} = 0;
from rt?

Reason - server side prepared statements are rarely useful, usually they
do not speed things up, and they break some setups.

Of course I can modify my own copy of rt, but patching it again and
again after every upgrade seems to be suboptimal.

I’m leery to make a change like that for RT without knowing much more
about why I should be making it.

I am willing to provide all kind of information if you’d need.

the biggest problem with server side prepared statements is described
with examples in here:
http://www.depesz.com/index.php/2008/05/10/prepared-statements-gotcha/

they (server side prepared statements) make sense if you:

  1. use complicated queries with many joins
  2. in the time of single db connection you run given query many times
  3. if you change parameters to the query - you don’t change the ones
    that can influence execution plan.

all in all - (in my opinion) chances of this are very slim, and in rt -
i haven’t seen anything even resembling such queries.

on the other hand - there is program called pgbouncer. which is
connection pooler with great ability to reuse connection as soon as it
finishes transaction.

this is great from performance and resources-consumption standpoint, but
breaks if you use server side prepared statements - because they are
being used across many transactions.

finally - when using short, simple queries - prepared statements can
actually work worse than normal queries, because they need more
roundtrips client<->server to do the same task.

Best regards,

depesz

Linkedin: Hubert Lubaczewski - Instructure, Inc. | LinkedIn / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

6

finally - when using short, simple queries - prepared statements can
actually work worse than normal queries, because they need more
roundtrips client<->server to do the same task.

I suspect we’d need to see benchmarks of RT on versions of postgres
from 8.1 → 9 to be able to really evaluate that. I’d hate to make
someone else’s performance worse.

Adding a way for you to frob it to RT from the Connect code might be
possible, but it’d need to be a config option.

-kevin