Ошибка 42P05 (PostgreSQL, Npgsql) Prepare

[t0pdevice]

Добрый день.

Используется коннектор Npgsql. Обнаружена следующая проблема.
Если на сервере с PostgreSQL используется pgBouncer, в нем Prepare Statesments не работает:
https://pgbouncer.github.io/faq.html#ho ... on-pooling

How to use prepared statements with transaction pooling?
To make prepared statements work in this mode would need PgBouncer to keep track of them internally, which it does not do. So only way to keep using PgBouncer in this mode is to disable prepared statements in the client.

Если в DataSource тип выбран "Хранимая процедура", то в Stimulsoft, как раз возникает эта проблема:

npgsql_1.png (24.11 КБ) 12550 просмотров

npgsql_2.png (14.19 КБ) 12550 просмотров

Код: Выделить всё

   в Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   в Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   в Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   в Npgsql.NpgsqlConnector.<ReadExpecting>d__154`1.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   в System.Threading.Tasks.ValueTask`1.get_Result()
   в Npgsql.NpgsqlConnector.ReadExpecting[T]()
   в Npgsql.NpgsqlCommand.Prepare()
   в Stimulsoft.Report.Dictionary.StiSqlSource.RetrieveData(Boolean schemaOnly)
   в Stimulsoft.Report.Dictionary.StiSqlAdapterService.ConnectDataSourceToData(StiDictionary dictionary, StiDataSource dataSource, Boolean loadData)
   в Stimulsoft.Report.Dictionary.StiDataSource.Connect(StiDataCollection datas, Boolean loadData)
   в Stimulsoft.Report.Dictionary.StiDataSource.Connect(Boolean loadData)
   в Stimulsoft.Report.Dictionary.StiDataSource.Connect()
   в Reports.Report.Report_BeginRender(Object sender, EventArgs e)
   в Stimulsoft.Report.StiReport.InvokeBeginRender()
   в Stimulsoft.Report.Engine.StiRenderProviderV2.Render(StiReport report, StiRenderState state)
   в Stimulsoft.Report.Engine.StiReportV2Builder.RenderSingleReport(StiReport masterReport, StiRenderState renderState)
   в Stimulsoft.Report.StiReport.RenderReport(StiRenderState renderState)
   в Stimulsoft.Report.StiReport.Render(StiRenderState renderState, StiGuiMode guiMode)
   в Stimulsoft.Report.StiReport.Render(StiRenderState renderState)
   в Stimulsoft.Report.StiReport.Render(Boolean showProgress, Int32 fromPage, Int32 toPage)
   в Stimulsoft.Report.StiReport.Render(Boolean showProgress)
   в Stimulsoft.Report.Design.Controls.StiDesignerPreviewControl.btRefresh_Click(Object sender, EventArgs e)

Как можно отключить Prepare Statements для соединения в Stimulsoft? Есть ли для этого где-то параметр? Если нету, то можете добавить опцию, чтобы запросы делались без prepare?

[Edward]

Здравствуйте,

Проблема ясна. Вы не могли бы, пожалуйста, выслать пример базы данных и пример конфигурации для pgBouncer на сервере для быстрого воспроизведения проблемы на support[at]stimulsoft.com ? Попробуем модифицировать адаптер для поддержки опции без prepare для этого случая.

Спасибо,
Эд

[t0pdevice]

База PostgreSQL обычная с любой структурой.
Конфиг pgBouncer тоже в принципе не важен, можно и стандартный, как пример ниже. Все остальное описано в первом посте.

Код: Выделить всё

;; database name = connect string
;;
;; connect string params:
;;   dbname= host= port= user= password=
;;   client_encoding= datestyle= timezone=
;;   pool_size= connect_query=
[databases]

postgres = host=127.0.0.1 port=6432 dbname=postgres
production = host=127.0.0.1 port=6432 dbname=production

; foodb over unix socket
;foodb =

; redirect bardb to bazdb on localhost
;bardb = host=localhost dbname=bazdb

; acceess to dest database will go with single user
;forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

; use custom pool sizes
;nondefaultdb = pool_size=50 reserve_pool=10

; fallback connect string
;* = host=testserver

;; Configuration section
[pgbouncer]

;;;
;;; Administrative settings
;;;

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

;;;
;;; Where to wait for clients
;;;

; ip address or * which means all ip-s
listen_addr = *
listen_port = 5432

; unix socket is also used for -R.
; On debian it should be /var/run/postgresql
;unix_socket_dir = /tmp
;unix_socket_mode = 0777
;unix_socket_group =
unix_socket_dir = /var/run/postgresql

;;;
;;; Authentication settings
;;;

; any, trust, plain, crypt, md5
auth_type = md5
;auth_file = /8.0/main/global/pg_auth
auth_file = /etc/pgbouncer/userlist.txt
;auth_query = SELECT * FROM public.fn_pgbouncer_user_search($1)
;;;
;;; Users allowed into database 'pgbouncer'
;;;

; comma-separated list of users, who are allowed to change settings
admin_users = postgres

; comma-separated list of users who are just allowed to use SHOW command
stats_users = stats, root

;;;
;;; Pooler personality questions
;;;

; When server connection is released back to pool:
;   session      - after client disconnects
;   transaction  - after transaction finishes
;   statement    - after statement finishes
pool_mode = transaction

;
; Query for cleaning connection immediately after releasing from client.
; No need to put ROLLBACK here, pgbouncer does not reuse connections
; where transaction is left open.
;
; Query for 8.3+:
;   DISCARD ALL;
;
; Older versions:
;   RESET ALL; SET SESSION AUTHORIZATION DEFAULT
;
; Empty if transaction pooling is in use.
;
server_reset_query = DISCARD ALL

;
; Comma-separated list of parameters to ignore when given
; in startup packet.  Newer JDBC versions require the
; extra_float_digits here.
;
ignore_startup_parameters = extra_float_digits, lc_monetary

;
; When taking idle server into use, this query is ran first.
;   SELECT 1
;
;server_check_query = select 1

; If server was used more recently that this many seconds ago,
; skip the check query.  Value 0 may or may not run in immediately.
;server_check_delay = 30

;;;
;;; Connection limits
;;;

; total number of clients that can connect
max_client_conn = 30000

; default pool size.  20 is good number when transaction pooling
; is in use, in session pooling it needs to be the number of
; max clients you want to handle at any moment
default_pool_size = 50

; how many additional connection to allow in case of trouble
;reserve_pool_size = 5

; if a clients needs to wait more than this many seconds, use reserve pool
;reserve_pool_timeout = 3

; log if client connects or server connection is made
log_connections = 1

; log if and why connection was closed
log_disconnections = 0

; log error messages pooler sends to clients
log_pooler_errors = 1


; If off, then server connections are reused in LIFO manner
;server_round_robin = 0

;;;
;;; Timeouts
;;;

;; Close server connection if its been connected longer.
server_lifetime = 1200

;; Close server connection if its not been used in this time.
;; Allows to clean unnecessary connections from pool after peak.
server_idle_timeout = 60

;; Cancel connection attempt if server does not answer takes longer.
server_connect_timeout = 30

;; If server login failed (server_connect_timeout or auth failure)
;; then wait this many second.
server_login_retry = 30

;; Dangerous.  Server connection is closed if query does not return
;; in this time.  Should be used to survive network problems,
;; _not_ as statement_timeout. (default: 0)
;query_timeout = 0

;; Dangerous.  Client connection is closed if the query is not assigned
;; to a server in this time.  Should be used to limit the number of queued
;; queries in case of a database or network failure. (default: 0)
;query_wait_timeout = 0

;; Dangerous.  Client connection is closed if no activity in this time.
;; Should be used to survive network problems. (default: 0)
;client_idle_timeout = 0

;; Disconnect clients who have not managed to log in after connecting
;; in this many seconds.
;client_login_timeout = 60

;; Clean automatically created database entries (via "*") if they
;; stay unused in this many seconds.
; autodb_idle_timeout = 3600

;;;
;;; Low-level tuning options
;;;

;; buffer for streaming packets
;pkt_buf = 2048

;; man 2 listen
;listen_backlog = 128

;; networking options, for info: man 7 tcp

;; Linux: notify program about new connection only if there
;; is also data received.  (Seconds to wait.)
;; On Linux the default is 45, on other OS'es 0.
;tcp_defer_accept = 0

;; In-kernel buffer size (Linux default: 4096)
;tcp_socket_buffer = 0

;; whether tcp keepalive should be turned on (0/1)
;tcp_keepalive = 1

;; following options are Linux-specific.
;; they also require tcp_keepalive=1

;; count of keepaliva packets
;tcp_keepcnt = 0

;; how long the connection can be idle,
;; before sending keepalive packets
;tcp_keepidle = 0

;; The time between individual keepalive probes.
;tcp_keepintvl = 0

;; DNS lookup caching time
;dns_max_ttl = 15

;; DNS zone SOA lookup period
;dns_zone_check_period = 0

[Aleksey]

Здравствуйте,

Необходимо некоторое время для анализа проблемы.

Спасибо.

[t0pdevice]

Есть новости?

[Aleksey]

Здравствуйте,

Пока что никаких. Постараемся в ближайшее время найти решение.

Спасибо.