"SET CLIENT_ENCODING TO" called even though 'encoding' is not defined in database.yml

[FrailWords]

I am using the following Rails setup -

rails 5.2.4
activerecord/activesupport 5.2.4.4
pg 1.2.3

In the postgres_adapter.rb file, if the encoding is not set, the set_client_encoding method is not called and I have verified this by stepping through the code base - https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L776

and I have searched across the code base in rails or application and i do not see any other place where this method might be called from.

But still, when I try to run the application, there's a SET CLIENT_ENCODING TO query that happens on the initialization of every connection and since this is connection related, thats why I am posting it here for some guidance.

[larskanis]

PG gem executes set client_encoding to 'UTF8' (in lowercase) if Encoding.default_internal is set (which is not the ruby default). That's so since pg was ported to ruby-1.9 and is done here:

ruby-pg/ext/pg_connection.c

Lines 3937 to 3939 in 3cc3020

	if (( enc = rb_default_internal_encoding() )) {
	encname = pg_get_rb_encoding_as_pg_encoding( enc );
	if ( pgconn_set_client_encoding_async(self, encname) != 0 )

[FrailWords]

@larskanis I did check this condition as well and i've checked the value for Encoding.default_internal and it is not set. I am not sure where else I can look to find this issue. I have checked the SQL logs as well and at the application level, I cannot seem to find where it is coming from. If only I could either - do a detailed log OR I could patch the connection adapter to see who might be executing this query...

[FrailWords]

@larskanis Ignore my earlier comment - the Encoding.default_internal was indeed set to "UTF8" and so the method was being called. Since this is a Ruby level encoding setting, I am afraid of the consequences of setting this to nil in the initialization of the app just to avoid this call. Would you be able to suggest an alternative on how to avoid this during connection init ? Thank you for your advice and help.

[larskanis]

You could use the async API of ruby-pg as demonstrated in async_api.rb. That would require some monkey patching of ActiveRecord.

On the other hand it's good practise to keep Encoding.default_internal untouched hence nil. Especially in a rails context.

Can you describe why you try to get rid of this statement?

[FrailWords]

@larskanis thank you for the suggestion and help. I'll check out the example for async API and also see how it can be patched and tested to see it doesn't have any side-effects on the encoding of the various language strings.

Here's the context for why we are trying to get rid of this statement -

We are using an AWS service called RDS Proxy, which is a database connection pooling proxy that takes away the responsibility of connection re-use from the application.

As per their documentation, statements which involve a SET SQL statement get 'pinned' to the session of the connection and even if the connection becomes idle after the statement finishes, it doesn't allow for re-use of the connection until it is closed by the originator. This makes the connections go up faster than the speed at which they get cleaned up due to idle timeout from the application.

Ideally, they have an option to 'EXCLUDE_VARIABLE_SETS' from the pinning behaviour but unfortunately, setting this option has no effect and thus we are having to solve a problem of an external service in our application. Its not ideal but its something that we have to fix on our side, at least in the short-term until AWS comes back to us with a resolution.

Thank you again for your reply.

[FrailWords]

There's another issue in Rails that I thought I'd mention here - rails/rails#40207 - which is also tied to the 'pinning' behaviour where non-prepared statements are also being executed in an 'extended query' format.

This is also something we are having to patch in our app to make sure the queries are 'simple' queries as we are not using prepared statements in our app.

Thought this might be relevant to the discussion if anyone comes across here.

[arkirchner]

@FrailWords Did you solve this issue? I am still stuck with the pinned session. How is the simple format related to removing the set encoding.

The client session was pinned to the database connection [dbConnection=1189232136] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query. Digest: "set client_encoding to $1".

[FrailWords]

@FrailWords Did you solve this issue? I am still stuck with the pinned session. How is the simple format related to removing the set encoding.

The client session was pinned to the database connection [dbConnection=1189232136] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query. Digest: "set client_encoding to $1".

@arkirchner I worked around this by monkey patching all activerecord functions that lead to a SET statement, mostly removing them and moving them to the proxy's init section - depends on if the proxy you are using has such a place - idea being to do the SET statements only once per connection. Also, I set Encoding.default_internal to nil - this didn't have any side-effects and will fix this issues from the pg gem perspective.

[arkirchner]

@FrailWords Thank you very much sharing. I wanted to use RDS Proxy as well. I not feeling to confident with monkey patching ActiveRecord and setting Encoding.default_internal = nil. 🤔 I will give up on RDS Proxy and use pgbouncer instead.

It looks like pgbouncer is more sensible in this area. It can keep track of some set statement and raises errors for others.

By default, PgBouncer allows only parameters it can keep track of in startup packets: client_encoding, datestyle, timezone and standard_conforming_strings. All others parameters will raise an error.
https://www.pgbouncer.org/config.html

[FrailWords]

@FrailWords Thank you very much sharing. I wanted to use RDS Proxy as well. I not feeling to confident with monkey patching ActiveRecord and setting Encoding.default_internal = nil. 🤔 I will give up on RDS Proxy and use pgbouncer instead.

It looks like pgbouncer is more sensible in this area. It can keep track of some set statement and raises errors for others.

By default, PgBouncer allows only parameters it can keep track of in startup packets: client_encoding, datestyle, timezone and standard_conforming_strings. All others parameters will raise an error.
https://www.pgbouncer.org/config.html

@arkirchner in my experience, I agree with your decision about using pgbouncer over RDS proxy - it offers much more control and has more documentation and more importantly, works as you'd expect it to without surprises.

In my case, we completely removed the proxy altogether because ActiveRecord also does connection pooling and since my app was running on k8s, we could control number of connections via number of instances (internally then managed by ActiveRecord). Adding a proxy meant introducing another connection pool, the only benefit that comes from it is some 'headroom' in terms of 'idle' connections, which is also available at the ActiveRecord level by tweaking the 'idle timeout' parameter.

Either way, be sure to evaluate whether you actually need a proxy or not as well.

[renan-zippi]

I worked around this by monkey patching all activerecord functions that lead to a SET statement...

@FrailWords do you remember which functions that lead to the SET statement? I was tying to figure out how to solve this issue with RDS Proxy, and see how it behaves before moving out to other solution

[i7an]

Ideally, they have an option to EXCLUDE_VARIABLE_SETS from the pinning behaviour but unfortunately, setting this option has no effect and thus we are having to solve a problem of an external service in our application.

That is because:

This setting is only supported for MySQL engine family databases.
Read more

[oehlschl]

It looks like this issue was effectively resolved in #542 / #543 and released in ruby-pg v1.5.4. With that change, the pg lib will not set the client encoding if it matches what is already set on the connection, which means this query can be prevented if the encoding is set upstream by the proxy (in RDS Proxy's "Initialization query" or pgbouncer's "connect_query").