Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    Pratyush Agarwal
    @PratyushAgarwa7_twitter
    Hi, I am getting this intermittent issue while updating bulk records "ERROR: cannot execute UPDATE in a read-only transaction" with pg bouncer. I am using transaction pooling mode. I do not have master/slave arch here. i am just using single node of postgres db. Can anyone help me out to identify what is the problem here?
    Pratyush Agarwal
    @PratyushAgarwa7_twitter
    Application connecting to pg bouncer is spring boot application.
    Alicja Kucharczyk
    @StiepanTrofimo_twitter
    hey Pratyush, maybe you set somewhere default_transaction_read_only = on ? on a connection or user level?
    Pratyush Agarwal
    @PratyushAgarwa7_twitter
    Hi Alicja, Thanks for the response. I did not set default_transaction_read_only flag anywhere because by default it will be off. Any other suggestions?
    Alicja Kucharczyk
    @StiepanTrofimo_twitter
    maybe you do SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY somewhere? and since you are using transaction mode then this connection is reused by update?
    dt108
    @dt108
    Is there a possible way to figure out which queries (actual sql) are timing out from pgbouncer logs? I have upped the verbosity to 10 and i see debug statements in the pgbouncer log with sql queries. I also notice something like this in the logs 'pooler error: query timeout'. How do we connect this error to actual sql statement that is causing the timeout
    1 reply
    yinan8128
    @yinan8128
    Hi, as checking pgbouncer transaction mode not support "Protocol-level prepared plans" http://www.pgbouncer.org/features.html
    What is "Protocol-level prepared plans" meaning please
    1 reply
    google can not get it, thanks
    yinan8128
    @yinan8128
    Hi guys
    As the doc show "the user name pgbouncer is allowed to log in without password, if the login comes via the Unix socket and the client has same Unix user UID as the running process"
    so I su to pgbouncer linux user for console login, works well, just once exit the console, shows that "could not save history to file "//.psql_history": No such file or directory"
    4 replies
    image.png
    Can check the attached image,
    thanks
    dt108
    @dt108
    Hi, we are using pgbouncer rr for query routing (load balance read queries to reader endpoints and write transactions to primary endpoint). Our rules for routing are trivial, if the query has a 'select' keyword, route to a specific pool defined in the pgbouncer ini. There are some errors in pgbouncer logs and pgbouncer crashes after this error. Any help would be appreciated. 2020-10-27 23:48:23.180 UTC [32214] FATAL @src/objects.c:312 in function put_in_order(): put_in_order: found existing elem
    1 reply
    dt108
    @dt108
    Reg the errors above (2020-10-27 23:48:23.180 UTC [32214] FATAL @src/objects.c:312 in function put_in_order(): put_in_order: found existing elem), we are on PgBouncer 1.12.0
    John Firebaugh
    @jfirebaugh
    Does PgBouncer support single-shot unnamed "prepared statements" (really just parameterized statements) in transaction pooling mode, e.g. what PQexecParams generates? I know it doesn't support the full prepared statement protocol, but I'm wondering about this specific subset. pgbouncer/pgbouncer#37 seems to suggest it does, but the documentation is inconclusive.
    awlamb
    @awlamb
    @jfirebaugh It does. We use a cluster of pgbouncer instances with Ruby on Rails, which exclusively uses execParams
    (we also use it in transaction pooling mode)
    What you're referring to is the pg extended protocol, where it parameterizes the query into what is essentially an anonymous prepared statement
    All sent in a Parse/Bind/Describe/Execute request
    awlamb
    @awlamb
    Hope that helps!
    John Firebaugh
    @jfirebaugh
    @awlamb Great, thanks for sharing your experience! I came across rails/rails#40207 today as well, which was helpful because I was also considering RDS Proxy, but it seems that's a no-go even with prepared_statements: false. So thanks for that as well! :)
    I wish there was a way to get Rails to use parameterized statements without caching the prepared statement. I rely on pg_stat_statements for performance monitoring and prepared_statements: false makes pg_stat_statements useless because virtually every query is unique.
    Peter Eisentraut
    @petere
    @jfirebaugh pg_stat_statements should unify similar queries. Unless you are using a very old version.
    John Firebaugh
    @jfirebaugh
    @petere So I've read, but in practice it does not. (On 11.9)
    Alexey Pikin
    @notmaxx_gitlab
    hi, I have AWS RDS behind pgbouncer. I need to do minor upgrade AWS RDS, it is actually reboot. When I do upgrade of AWS RDS it happens not exactly at the moment when I press the button but at some random time (~4 min after I press the button to upgrade). So I cannot run commands like PAUSE/RESUME properly to hold the connections because I am not sure when to issue those commands. Is there way to tell pgbouncer to hold connections say for 1 min and keep trying to re-connect to upstream DB while DB is being restarted during upgrade. Any other recommendations you may give to me? Thank you
    Alexey Pikin
    @notmaxx_gitlab
    Also it should not drop any connected client connections. Basically work as PAUSE when PG connection drops
    yinan8128
    @yinan8128
    Hi, if want to disconnect all connections and processid no change, if "pgbouncer -R pgbouncer.ini" will achieve it please? checked the description, not very sure, so ask to double check, thanks
    Peter Eisentraut
    @petere
    @notmaxx_gitlab Not built-in, but I think you could script something like that.
    1 reply
    @yinan8128 pgbouncer -R will create a new process with a new pid. Perhaps you want the KILL command.
    2 replies
    David Fetter
    @davidfetter
    I noticed that when I put a %p in my PROMPT1 in psql, when I connect to a pgbouncer, I get gibberish, but not the same gibberish every time, i.e. it shows a negative number that's kinda large. Could psql be seeing things it shouldn't be seeing by looking at a location that's neither zeroed out nor populated correctly with the backend pid? I haven't yet figured out what would populate that bit in the pgbouncer code.
    Peter Eisentraut
    @petere
    @davidfetter The data for %p is sent from the backend as part of the cancel key. The cancel key is notionally the 4-byte backend PID and a 4-byte random value. But pgbouncer, when it acts as the server to your psql, just sends a random 8 byte value.
    Alexey Bashtanov
    @bashtanov
    Hi, I'm looking for to restart pgbouncer (1.14) seamlessly on Debian (9). init.d script looks correct, but systemd (232) interrupts and does not call its restart). If I run pgbouncer -R manually, systemd goes crazy about the main PID changed: drops the pidfile and doesn't recognize the new one if I recreate it. Systemd service file is generated, type=forking. For this type I don't think there is a way to notify systemd when PID changes. I'm thinking of writing a wrapper script that would appear as the daemon for systemd, will restart pgbouncer when it receives certain signal and will propagate other signals pgbouncer. It looks ugly and also I have a feeling of reinventing the wheel. I don't suppose anyone knows a better or just ready solution?
    David Fetter
    @davidfetter
    petere, thanks for the explanation!
    Bryan Patton
    @bpatton00_twitter
    uh oh I can't find any documentation on this -- so I am bumping up from 1.6 to 1.15 and all I'm getting blowback that statement timeout cannot equal 5000 -- I see nothing in the changelog about statement_timeout changing? Help? Maybe it's something as simple as it got converted to seconds from MS but it should be in the changelog if that's the case, etc.
    Bryan Patton
    @bpatton00_twitter

    also, is there a memory leak that's been introduced since 1.6? I'm seeing climbing memory patterns here and that's not what I've seen at all historically ( we did change from ubuntu to alpine but I doubt that's it)

    flat one is previous, new one is climbing

    image.png
    image.png
    Casuallynoted
    @Casuallynoted
    I'm having a really hard time getting pgbouncer to work on my machine. It's continually turning down authentication and saying the password is wrong, but I can sign into my pgsql server directly with the exact same credentials
    3 replies
    Peter Eisentraut
    @petere
    @bashtanov Online reboot doesn't work under systemd. If you need it, you should stick with the init.d script. Fixing this is on the todo list.
    @bpatton00_twitter statement_timeout is a PostgreSQL setting, not something PgBouncer deals with.
    1 reply
    @bpatton00_twitter I don't know of any memory leaks, and I haven't heard any reports about it. Of course, 1.15 is pretty new, so if there is one, maybe you're the first one to find it. But the test suite is run under valgrind, so if there was a leak in a well-trodden code path, we'd know about it. I wouldn't exclude the Ubuntu/Alpine change as a cause.
    17 replies
    Peter Eisentraut
    @petere
    @Casuallynoted You're going to have to give some more details than that.
    Enggar Ranu Hariawan
    @enggarranu_twitter
    image.png
    hello i wana ask,
    i have pgbouncer configuration like this (max_client_connection = 4096) , but i got no more connections allowed (max_client_conn) on 800 connections
    I need your help and your insight why only 800 connections I got max client connection error?
    12 replies
    Bryan Patton
    @bpatton00_twitter
    image.png
    FleZzz
    @FleZzz

    Hello, i want to ask
    Is it any chance to hide outputting db credentials when starting pg as a docker image?
    docker run -ti -e DATABASES_HOST=testhost -e DATABASES_DBNAME=testdb -e DATABASES_USER=user -e DATABASES_PASSWORD=pass pgbouncer/pgbouncer

    there exist QUIET option but it also hide logs from pg service