Cannot connect to pgbouncer database

[daniellionel01]

Hello!

I'm using https://www.pgbouncer.org/ for connection pooling. You can connect to a special "pgbouncer" database to get insights into how many clients are connected to your pool like this:

$ psql "postgresql://daniel:glue@localhost:5432/pgbouncer" -c "SHOW POOLS;"

However trying to use this connection string with pgo does not work. Other databases work normally though, it is only the pgbouncer database acting up.

I setup a repo to reproduce this: https://github.com/daniellionel01/pgo-pgbouncer-issue

And this is the output you're going to get:

$ rebar3 shell

===> Verifying dependencies...
===> Analyzing applications...
===> Compiling pgbouncer_test
Erlang/OTP 27 [erts-15.2.7] [source] [64-bit] [smp:10:10] [ds:10:10:10] [async-threads:1] [jit] [dtrace]

Eshell V15.2.7 (press Ctrl+G to abort, type help(). for help)
===> Booted backoff
===> Booted opentelemetry_api
===> Booted pg_types
===> Booted pgo
===> Booted pgbouncer_test
=ERROR REPORT==== 27-May-2025::09:30:46.143229 ===
** State machine <0.224.0> terminating
** Last event = {internal,load}
** When server state  = {ready,{data,default,
                                     #{port => 5432,user => "daniel",
                                       ssl => false,host => "localhost",
                                       password => "glue",pool_size => 5,
                                       database => "pgbouncer",
                                       queue_target => 50,
                                       queue_interval => 1000,
                                       idle_interval => 1000},
                                     undefined}}
** Reason for termination = error:{bad_generator,failed}
** Callback modules = [pgo_type_server]
** Callback mode = state_functions
** Stacktrace =
**  [{lists,'-filter/2-lc$^0/1-0-',2,[{file,"lists.erl"},{line,2200}]},
     {pg_types,'-update_for_typesends/5-fun-1-',5,
               [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl"},
                {line,121}]},
     {lists,foldl,3,[{file,"lists.erl"},{line,2146}]},
     {pg_types,update_map,3,
               [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl"},
                {line,88}]},
     {pg_types,update,3,
               [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl"},
                {line,78}]},
     {pgo_type_server,load,4,
                      [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pgo/src/pgo_type_server.erl"},
                       {line,66}]},
     {pgo_type_server,ready,3,
                      [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pgo/src/pgo_type_server.erl"},
                       {line,37}]},
     {gen_statem,loop_state_callback,11,
                 [{file,"gen_statem.erl"},{line,3735}]}]

=CRASH REPORT==== 27-May-2025::09:30:46.143425 ===
  crasher:
    initial call: pgo_type_server:init/1
    pid: <0.224.0>
    registered_name: []
    exception error: bad generator failed
      in function  lists:'-filter/2-lc$^0/1-0-'/2 (lists.erl, line 2200)
      in call from pg_types:'-update_for_typesends/5-fun-1-'/5 (/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl, line 121)
      in call from lists:foldl/3 (lists.erl, line 2146)
      in call from pg_types:update_map/3 (/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl, line 88)
      in call from pg_types:update/3 (/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl, line 78)
      in call from pgo_type_server:load/4 (/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pgo/src/pgo_type_server.erl, line 66)
      in call from pgo_type_server:ready/3 (/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pgo/src/pgo_type_server.erl, line 37)
      in call from gen_statem:loop_state_callback/11 (gen_statem.erl, line 3735)
    ancestors: [<0.219.0>,default,pgo_sup,<0.215.0>]
    message_queue_len: 1
    messages: [{'$gen_call',{<0.221.0>,
                                #Ref<0.2065119341.3271294979.102864>},
                               {reload,-576460751759007750}}]
    links: [<0.219.0>]
    dictionary: []
    trap_exit: false
    status: running
    heap_size: 10958
    stack_size: 29
    reductions: 94181
  neighbours:

=SUPERVISOR REPORT==== 27-May-2025::09:30:46.145799 ===
    supervisor: {<0.219.0>,pgo_pool_sup}
    errorContext: child_terminated
    reason: {{bad_generator,failed},
             [{lists,'-filter/2-lc$^0/1-0-',2,
                     [{file,"lists.erl"},{line,2200}]},
              {pg_types,'-update_for_typesends/5-fun-1-',5,
                        [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl"},
                         {line,121}]},
              {lists,foldl,3,[{file,"lists.erl"},{line,2146}]},
              {pg_types,update_map,3,
                        [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl"},
                         {line,88}]},
              {pg_types,update,3,
                        [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl"},
                         {line,78}]},
              {pgo_type_server,load,4,
                               [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pgo/src/pgo_type_server.erl"},
                                {line,66}]},
              {pgo_type_server,ready,3,
                               [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pgo/src/pgo_type_server.erl"},
                                {line,37}]},
              {gen_statem,loop_state_callback,11,
                          [{file,"gen_statem.erl"},{line,3735}]}]}
    offender: [{pid,<0.224.0>},
               {id,type_server},
               {mfargs,
                   {pgo_type_server,start_link,
                       [default,
                        #{port => 5432,user => "daniel",ssl => false,
                          host => "localhost",password => "glue",
                          pool_size => 5,database => "pgbouncer",
                          queue_target => 50,queue_interval => 1000,
                          idle_interval => 1000}]}},
               {restart_type,permanent},
               {significant,false},
               {shutdown,5000},
               {child_type,worker}]

=ERROR REPORT==== 27-May-2025::09:30:46.145808 ===
** State machine <0.221.0> terminating
** Last event = {internal,start_connections}
** When server state  = {connect,{data,<0.219.0>,<0.218.0>,5}}
** Reason for termination = exit:{{{bad_generator,failed},
                                   [{lists,'-filter/2-lc$^0/1-0-',2,
                                     [{file,"lists.erl"},{line,2200}]},
                                    {pg_types,
                                     '-update_for_typesends/5-fun-1-',5,
                                     [{file,
                                       "/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl"},
                                      {line,121}]},
                                    {lists,foldl,3,
                                     [{file,"lists.erl"},{line,2146}]},
                                    {pg_types,update_map,3,
                                     [{file,
                                       "/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl"},
                                      {line,88}]},
                                    {pg_types,update,3,
                                     [{file,
                                       "/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pg_types/src/pg_types.erl"},
                                      {line,78}]},
                                    {pgo_type_server,load,4,
                                     [{file,
                                       "/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pgo/src/pgo_type_server.erl"},
                                      {line,66}]},
                                    {pgo_type_server,ready,3,
                                     [{file,
                                       "/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pgo/src/pgo_type_server.erl"},
                                      {line,37}]},
                                    {gen_statem,loop_state_callback,11,
                                     [{file,"gen_statem.erl"},{line,3735}]}]},
                                  {gen_statem,call,
                                   [<0.224.0>,
                                    {reload,-576460751759007750},
                                    infinity]}}
** Callback modules = [pgo_connection_starter]
** Callback mode = state_functions
** Stacktrace =
**  [{gen,do_call,4,[{file,"gen.erl"},{line,247}]},
     {gen_statem,call,3,[{file,"gen_statem.erl"},{line,3250}]},
     {pgo_connection_starter,connect,3,
                             [{file,"/Users/daniellionel/Downloads/pgo_bouncer/_build/default/lib/pgo/src/pgo_connection_starter.erl"},
                              {line,28}]},
     {gen_statem,loop_state_callback,11,[{file,"gen_statem.erl"},{line,3735}]},
     {proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,329}]}]

[tsloughter]

Sorry I missed this issue! Thank you for opening it.

I see when this would happen. I need to run this project through eqwalizer and fix bugs like this..

This would happen if the type bootstrap query fails for whatever reason. But is weird since it would have had to been able to get a connection to the database before it runs the query that fails. So its not like a race or something. And you say it only happens with pgbouncer.

It is this query it must be failing on https://github.com/erleans/pgo/blob/main/src/pgo_type_server.erl#L78-L85

Other possibility is the connection closes and that is why the query fails.

I'll add better logging so a failure like this gives a stacktrace of the actual failure.

[tsloughter]

Do you by chance have disable_pqexec = 1 set in your pgbouncer config?

Pgo uses simple queries to bootstrap the types because the extended query handling functions try to read type information from the type server. To get around that circular dependency I just send a simple query and decode the columns from binary in the type server.