Issue 1:
Describe the bug

Using SQLAlchemy with AWS Redshift (1.0.47357).

After upgrading to:

SQLAlchemy==2.0.6
psycopg2-binary==2.9.5

I am getting:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) unrecognized configuration parameter "standard_conforming_strings"

Tried connecting with:

connect_args = {'options': '-c standard_conforming_strings=off'}
create_engine(self.connection_string, pool_pre_ping=True, connect_args=connect_args)

as ChatGPT suggested, getting a different, but similar error message:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "<redshift-host>" (<redshift-ip>), port 5439 failed: FATAL: unrecognized configuration parameter "standard_conforming_strings"

python --version
Python 3.9.16

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

SQLAlchemy==2.0.6

DBAPI (i.e. the database driver)

psycopg2-binary==2.9.5

Database Vendor and Major Version

Redshift 1.0.47357

Python Version

Python 3.9.16

Operating system

OSX

To Reproduce

create_engine(connection_string, pool_pre_ping=True)

# or

connect_args = {'options': '-cstandard_conforming_strings=off'}
# or
# connect_args = {'options': '-c standard_conforming_strings=off'}
create_engine(connection_string, pool_pre_ping=True, connect_args=connect_args)

Error

Traceback (most recent call last):
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 748, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedObject: unrecognized configuration parameter "standard_conforming_strings"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/opt/homebrew/Cellar/python@3.9/3.9.16/Frameworks/Python.framework/Versions/3.9/lib/python3.9/runpy.py", line 197, in _run_module_as_main
return _run_code(code, main_globals, None,
File "/opt/homebrew/Cellar/python@3.9/3.9.16/Frameworks/Python.framework/Versions/3.9/lib/python3.9/runpy.py", line 87, in _run_code
exec(code, run_globals)
File "/src/.ve/lib/python3.9/site-packages/lru/decorators.py", line 132, in _lazy_cache_wrapper
result = function(*args, **kwargs)
File "/src/mypackage/redshift.py", line 164, in tier1_partners
rs.connect()
File "/src/.ve/lib/python3.9/site-packages/maa/db.py", line 69, in connect
self._connection = self._engine.connect()
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3251, in connect
return self._connection_cls(self)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3275, in raw_connection
return self.pool.connect()
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 455, in connect
return _ConnectionFairy._checkout(self)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 1271, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 719, in checkout
rec = pool._do_get()
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 169, in _do_get
self._dec_overflow()
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
return self._create_connection()
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 396, in _create_connection
return _ConnectionRecord(self)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 681, in __init__
self.__connect()
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 917, in __connect
pool.dispatch.connect.for_modify(
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 473, in _exec_w_sync_on_first_run
self(*args, **kw)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
fn(*args, **kw)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 1912, in go
return once_fn(*arg, **kw)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 747, in first_connect
dialect.initialize(c)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 654, in initialize
super().initialize(connection)
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3033, in initialize
std_string = connection.exec_driver_sql(
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1772, in exec_driver_sql
ret = self._execute_context(
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
return self._exec_single_context(
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
self._handle_dbapi_exception(
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2326, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/src/.ve/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 748, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) unrecognized configuration parameter "standard_conforming_strings"

Additional context

thx for checking

Issue 2:
Describe the bug

I believe there is a regression of #5698 in 2.0.0

I get the below error after updating from 1.4.46 and have confirmed it present in 2.0.0 - 2.0.5.post1

UndefinedObject: unrecognized configuration parameter "standard_conforming_strings"

The logic added in 415ddce

does not appear to be present in 2.0.0

It appears to have been removed in db08a69

as seen in

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.5post1

DBAPI (i.e. the database driver)

psycopg2

Database Vendor and Major Version

Redshift 1.0.46987

Python Version

3.10

Operating system

OSX

To Reproduce

import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy import text

connection_string = 'postgresql+psycopg2://<redacted username>:<redacted url encoded password>@<redacted db hostname>:<redacted port>/<redacted database>'

redshift_engine = sqlalchemy.create_engine(connection_string, echo=False, pool_recycle=120, pool_pre_ping=True)

redshift_session = sessionmaker(bind=redshift_engine)

tmp_session = redshift_session()
tmp_session.query(text("SELECT * from my_schema.my_table limit 10")).all()

Error

Traceback (most recent call last):
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 747, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedObject: unrecognized configuration parameter "standard_conforming_strings"
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/username/Downloads/pycharm-2022.3.2/plugins/python/helpers/pydev/pydevconsole.py", line 364, in runcode
coro = func()
File "<input>", line 1, in <module>
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2715, in all
return self._iter().all() # type: ignore
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2875, in _iter
result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2229, in execute
return self._execute_internal(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2114, in _execute_internal
conn = self._connection_for_bind(bind)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1981, in _connection_for_bind
return trans._connection_for_bind(engine, execution_options)
File "<string>", line 2, in _connection_for_bind
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/state_changes.py", line 137, in _go
ret_value = fn(self, *arg, **kw)
File "/home/username/.local/share/virtualenvs/Traceback (most recent call last):
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 747, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedObject: unrecognized configuration parameter "standard_conforming_strings"
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/username/Downloads/pycharm-2022.3.2/plugins/python/helpers/pydev/pydevconsole.py", line 364, in runcode
coro = func()
File "<input>", line 1, in <module>
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2715, in all
return self._iter().all() # type: ignore
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2875, in _iter
result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2229, in execute
return self._execute_internal(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2114, in _execute_internal
conn = self._connection_for_bind(bind)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1981, in _connection_for_bind
return trans._connection_for_bind(engine, execution_options)
File "<string>", line 2, in _connection_for_bind
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/state_changes.py", line 137, in _go
ret_value = fn(self, *arg, **kw)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1108, in _connection_for_bind
conn = bind.connect()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3245, in connect
return self._connection_cls(self)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3269, in raw_connection
return self.pool.connect()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1255, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
rec = pool._do_get()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
with util.safe_reraise():
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
return self._create_connection()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
return _ConnectionRecord(self)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
self.__connect()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 916, in __connect
)._exec_w_sync_on_first_run(self.dbapi_connection, self)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 473, in _exec_w_sync_on_first_run
self(*args, **kw)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
fn(*args, **kw)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 1912, in go
return once_fn(*arg, **kw)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 746, in first_connect
dialect.initialize(c)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 654, in initialize
super().initialize(connection)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3028, in initialize
std_string = connection.exec_driver_sql(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1772, in exec_driver_sql
ret = self._execute_context(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
return self._exec_single_context(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
self._handle_dbapi_exception(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2325, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 747, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) unrecognized configuration parameter "standard_conforming_strings"
[SQL: show standard_conforming_strings]
(Background on this error at: https://sqlalche.me/e/20/f405)-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1108, in _connection_for_bind
conn = bind.connect()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3245, in connect
return self._connection_cls(self)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3269, in raw_connection
return self.pool.connect()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1255, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
rec = pool._do_get()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
with util.safe_reraise():
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
return self._create_connection()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
return _ConnectionRecord(self)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
self.__connect()
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 916, in __connect
)._exec_w_sync_on_first_run(self.dbapi_connection, self)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 473, in _exec_w_sync_on_first_run
self(*args, **kw)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
fn(*args, **kw)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 1912, in go
return once_fn(*arg, **kw)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 746, in first_connect
dialect.initialize(c)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 654, in initialize
super().initialize(connection)
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3028, in initialize
std_string = connection.exec_driver_sql(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1772, in exec_driver_sql
ret = self._execute_context(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
return self._exec_single_context(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
self._handle_dbapi_exception(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2325, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/home/username/.local/share/virtualenvs/project01-Z0F7Zsx7/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 747, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) unrecognized configuration parameter "standard_conforming_strings"
[SQL: show standard_conforming_strings]
(Background on this error at: https://sqlalche.me/e/20/f405)

Additional context

Downgrading to 1.4.46 has provided a workaround