Re: Get info about the index

On Mon, 2025-07-28 at 08:19 -0500, David Barbour wrote: > Couple of suggestions.  You might try ChatGPT. Please don't be insulting. He asked for real information. Yours, Laurenz Albe

Re: Get info about the index

Couple of suggestions. You might try ChatGPT. I've had some success using this tool to uncover improvements to the use of indexes. The other would be to look at https://explain.depesz.com/. It's pretty self-explanatory. You run an explain plan and paste the results into the tool and it will run

Re: Regarding logical replication issues with PostgreSQL versions 16 and above

Hi , On Fri, 2025-07-18 at 19:08 +0800, yexiu-glory wrote: > I'm facing a problem here: our business requires logical data replication to > other > departments, but at the same time, sensitive fields need to be filtered out. > Therefore, we used the column filtering function when cre

Re: Get info about the index

On Sat, Jul 26, 2025 at 3:13 PM Igor Korot wrote: > > I didn't find the sorting for the field. > > Can you help? The pg_index_column_has_property() can provide this information. E.g., select pg_index_column_has_property('my_index'::regclass, 2, 'desc');

Re: Get info about the index

On Sat, 2025-07-26 at 15:13 -0400, Igor Korot wrote: > On Sat, Jul 26, 2025, 2:14 PM Christophe Pettus wrote: > > > I want to know all individual things: > > > - whether the index is unique. > > > - what type of index it is > > > - whether the field is ASC or DESC. > > > - all other individual ara

Re: Get info about the index

Hi, Christopher, On Sat, Jul 26, 2025, 2:14 PM Christophe Pettus wrote: > > > > I want to know all individual things: > > - whether the index is unique. > > - what type of index it is > > - whether the field is ASC or DESC. > > - all other individual arams > > pg_index is the source for those.

Re: Get info about the index

> I want to know all individual things: > - whether the index is unique. > - what type of index it is > - whether the field is ASC or DESC. > - all other individual arams pg_index is the source for those. The one exception is the access method for the index, which is in pg_class.

Re: Get info about the index

Adrian, On Sat, Jul 26, 2025 at 11:08 AM Adrian Klaver wrote: > > On 7/26/25 08:00, Igor Korot wrote: > > Hi, ALL, > > Is there a better place to get the info about the index other than > > https://www.postgresql.org/docs/current/view-pg-indexes.html > > What information do you need? This is the

Re: Possible causes of high_replay lag, given replication settings?

On Fri, Jul 25, 2025 at 7:13 PM Greg Sabino Mullane wrote: > > On Fri, Jul 25, 2025 at 9:57 AM Jon Zeppieri wrote: >> >> Thanks for the response, Nick. I'm curious why the situation you describe >> wouldn't also lead to the write_lag and flush_lag also being >> high. If the problem is simply kee

Re: Get info about the index

Adrian Klaver writes: > On 7/26/25 08:00, Igor Korot wrote: >> Is there a better place to get the info about the index other than >> https://www.postgresql.org/docs/current/view-pg-indexes.html > pg_class: > https://www.postgresql.org/docs/current/catalog-pg-class.html > and pg_attribute: > https

Re: Get info about the index

On 7/26/25 08:00, Igor Korot wrote: Hi, ALL, Is there a better place to get the info about the index other than https://www.postgresql.org/docs/current/view-pg-indexes.html That guy has a full blown CREATE INDEX command and I will need to parse it to get the info I need. FYI, pg_class: https

Re: Get info about the index

On Sat, Jul 26, 2025, 08:00 Igor Korot wrote: > Hi, ALL, > Is there a better place to get the info about the index other than > https://www.postgresql.org/docs/current/view-pg-indexes.html > > That guy has a full blown CREATE INDEX command and I will need to > parse it to get the info I need. >

Re: Get info about the index

On Sat, 26 Jul 2025, 17:00 Igor Korot, wrote: > Hi, ALL, > Is there a better place to get the info about the index other than > https://www.postgresql.org/docs/current/view-pg-indexes.html > > That guy has a full blown CREATE INDEX command and I will need to > parse it to get the info I need. > >

Re: Get info about the index

On 7/26/25 08:00, Igor Korot wrote: Hi, ALL, Is there a better place to get the info about the index other than https://www.postgresql.org/docs/current/view-pg-indexes.html What information do you need? That guy has a full blown CREATE INDEX command and I will need to parse it to get the inf

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Also, Neon [0] and Aurora [1] pricing is so high that it seems to make most use-cases impractical (well, if you want a managed offering...). Neon's top public tier is not even what a single modern dedicated server (or virtual machine) can provide. I would have thought decoupling compute and stor

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Ah, by "shared storage" I mean that each node can acquire exclusivity, not that they can both R/W to it at the same time. > Some pretty well-known cases of storage / compute separation (Aurora, Neon) > also share the storage between instances, That model is cool, but I think it's more of a solu

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Sorry, I was referring to this: > But when PostgreSQL instances share storage rather than replicate: > - Consistency seems maintained (same data) > - Availability seems maintained (client can always promote an accessible node) > - Partitions between PostgreSQL nodes don't prevent the system from

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

What you describe doesn’t look like something very useful for the vast majority of projects that needs a database. Why would you even want that if you can avoid it? If your “single node” can handle tens / hundreds of thousands requests per second, still have very durable and highly available s

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

A shared storage would require a lot of extra work. That's essentially what AWS Aurora does. You will have to have functionality to sync in-memory states between nodes, because all the instances will have cached data that can easily become stale on any write operation. That alone is not that simple

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

And finally, some read only benchmarks with the same postgres build. 9P: postgres@zerofs:/mnt_9p$ pgbench -vvv -c 100 -j 40 -t 1 bench -S pgbench (16.9 (Ubuntu 16.10-1)) starting vacuum...end. starting vacuum pgbench_accounts...end. transaction type: scaling factor: 50 query mode: simple num

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

I built postgres (same version, 16.9) but --with-block-size=32 (I'd really love if this would be a initdb time flag!) and did some more testing: synchronous_commit = off postgres@zerofs:~$ pgbench -vvv -c 100 -j 40 -t 1 bench pgbench (16.9 (Ubuntu 16.10-1)) starting vacuum...end. starting va

Re: Possible causes of high_replay lag, given replication settings?

On Fri, Jul 25, 2025 at 9:57 AM Jon Zeppieri wrote: > Thanks for the response, Nick. I'm curious why the situation you describe > wouldn't also lead to the write_lag and flush_lag also being > high. If the problem is simply keeping up with the primary, wouldn't you > expect all three lag times to

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

Laurenz Albe writes: > On Fri, 2025-07-25 at 14:10 -0400, Tom Lane wrote: >> So ... any chance you have a data type named _sa_setup_role? > ... it could also be a type "sa_setup_role", and "_sa_setup_role" > is interpreted as the corresponding array type: Oh, of course --- that's a good deal mor

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

On Fri, 2025-07-25 at 14:10 -0400, Tom Lane wrote: > After looking at the code I remembered that the parser might be taking > this as a type coercion request.  With that idea, I can duplicate the > observed behavior like so: > > regression=# select _sa_setup_role('af_repo_app'); > INFO:  af_repo_a

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

I wrote: > Well, there's got to be *something* different about that database. After looking at the code I remembered that the parser might be taking this as a type coercion request. With that idea, I can duplicate the observed behavior like so: regression=# select _sa_setup_role('af_repo_app');

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

Rumpi Gravenstein writes: > Our databases are deployed with automation tools. They should all be > created the same. They all have the same 17 extensions. I've asked a DBA > to confirm. Well, there's got to be *something* different about that database. > This issue only appears in the functio

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

Now I'm wondering about stray entries in pg_cast. Also, do you have any extensions loaded in that DB that aren't in your other ones? Our databases are deployed with automation tools. They should all be created the same. They all have the same 17 extensions. I've asked a DBA to confirm. This

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

Rumpi Gravenstein writes: > No ... just one version: D'oh, actually this would have complained if there was more than one match, so that theory is wrong: > _pub_dev_2_db=# DROP FUNCTION if exists _sa_setup_role; > DROP FUNCTION Now I'm wondering about stray entries in pg_cast. Also, do you

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

There is really only one function with this name. A rerun of my test script with the suggested change: _pub_dev_2_db=# SELECT version(); version ---

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

On 7/25/25 09:59, Rumpi Gravenstein wrote: No ... just one version: _pub_dev_2_db=# \df _sa_setup_role Do: \df *._sa_setup_role -- Rumpi Gravenstein -- Adrian Klaver adrian.kla...@aklaver.com

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

No ... just one version: _pub_dev_2_db=# SHOW server_version; server_version 16.9 (1 row) _pub_dev_2_db=# DROP FUNCTION if exists _sa_setup_role; DROP FUNCTION _pub_dev_2_db=# CREATE OR REPLACE FUNCTION _sa_setup_role( p_role_to_be_granted varchar) _pub_dev_2_d

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

Rumpi Gravenstein writes: > I've been confound by the following behavior that I see in one of our > PostgreSQL 16 instances. In this case I am running this script from psql. I'd bet there is another function named _sa_setup_role() that takes some kind of array, and the parser is resolving the am

Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

On 7/25/25 09:36, Rumpi Gravenstein wrote: PostgreSQL Experts, I've been confound by the following behavior that I see in one of our PostgreSQL 16 instances.  In this case I am running this script from psql. -

Re: Upper / lower case keys in JSON objects

On 7/25/25 05:26, Mark wrote: Hi all Apologies for any unexpected protocol exceptions, I do not post to mailing lists very often I have been trying to work with JSON objects and noticed I could not get the function json_to_record to produce results, unless I had the key values in lower case

Re: Sorting by respecting diacritics/accents

On Fri, 2025-07-25 at 13:05 +0300, JānisE wrote: > I seem to not be able to get PostgreSQL to sort rows by a string column > respecting the diacritics. > > I read [1] that it's possible to define a custom collation having collation > strength "ks" > set to "level2", which would mean that it's a

Re: Possible causes of high_replay lag, given replication settings?

On Wed, Jul 23, 2025 at 4:27 PM Nick Cleaton wrote: > > On Fri, 18 Jul 2025 at 21:29, Jon Zeppieri wrote: > > > > I just had a situation where physical replication fell far behind > > (hours). The write and flush lag times were 0, but replay_lag was > > high. The replica has hot_standby_feedback

Re: Upper / lower case keys in JSON objects

Sorry all, Figured it out, the following works: query: select * from json_to_record('{"secLvl": 13, "firBal": "somethi", "firLvl": "C", "thiLvl": "A", "fourLvl": 2}'::json) as x("secLvl" int, "firBal" text, "firLvl " text, "thiLvl" text, "fourLvl" int); result: secLvl | firBal | firLvl | thiLvl

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Hi, I went ahead and did that test. Here is the postgresql config I used for reference (note the wal options (recycle, init_zero) as well as full_page_writes = off, because ZeroFS cannot have torn writes by design). https://gist.github.com/Barre/8d68f0d00446389998a31f4e60f3276d Test was runni

Re: Is there any limit on the number of rows to import using copy command

On 7/24/25 16:59, sivapostg...@yahoo.com wrote: 1.  Testcase.  Created a new database, modified the triggers (split into three), populated required master data, lookup tables.  Then transferred 86420 records. Checked whether all the 86420 records inserted in table1 and also whether the trigger

Re: Is there any limit on the number of rows to import using copy command

On Thu, Jul 24, 2025 at 8:00 PM sivapostg...@yahoo.com < sivapostg...@yahoo.com> wrote: [snip] > 3. Regarding moving the logic to procedure. Won't the trigger work? > Will it be a burden for 86420 records? It's working, if we insert few > thousand records. After split of trigger function, it's

Re: Is there any limit on the number of rows to import using copy command

1.  Testcase.  Created a new database, modified the triggers (split into three), populated required master data, lookup tables.  Then transferred 86420 records. Checked whether all the 86420 records inserted in table1 and also whether the trigger created the required records in table2.   Yes, i

Re: Is there any limit on the number of rows to import using copy command

Those two columns have null values when the color turns into Red. And the status column shows 'active' only.   All those three column values are same (null, null, active) even after 2 hours! It works when I split the trigger function into three, one each for insert, delete and update call from t

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

> This then begs the obvious question of how fast is this with > synchronous_commit = on? Probably not awful, especially with commit_delay. I'll try that and report back. Best, Pierre On Fri, Jul 25, 2025, at 00:03, Jeff Ross wrote: > On 7/24/25 13:50, Pierre Barre wrote: > >> It’s not “safe”

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Hi Marco, Thanks for the kind words! > and potentially elaborate on other projects for an active/active cluster! I > applaud you. I wrote an argument there: https://github.com/Barre/ZeroFS?tab=readme-ov-file#cap-theorem I definitely want to write a proof of concept when I get some time. Best

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

My humble take on this project: well done! You are opening the doors to work on a much-needed endeavor, decouple compute from storage, and potentially elaborate on other projects for an active/active cluster! I applaud you. On Thu, Jul 17, 2025, 4:59 PM Pierre Barre wrote: > Hi everyone, > > I w

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

On 7/24/25 13:50, Pierre Barre wrote: It’s not “safe” or “unsafe”, there’s mountains of valid workloads which don’t require synchronous_commit. Synchronous_commit don’t make your system automatically safe either, and if that’s a requirement, there’s many workarounds, as you suggested, it cert

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

It’s not “safe” or “unsafe”, there’s mountains of valid workloads which don’t require synchronous_commit. Synchronous_commit don’t make your system automatically safe either, and if that’s a requirement, there’s many workarounds, as you suggested, it certainly doesn’t make the setup useless. Be

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

On Fri, Jul 18, 2025 at 12:57:39PM +0200, Pierre Barre wrote: > - Postgres configured accordingly memory-wise as well as with > synchronous_commit = off, wal_init_zero = off and wal_recycle = off. Bingo. That's why it's fast (synchronous_commit = off). It's also why it's not safe _unless_ you

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

On Fri, Jul 18, 2025 at 06:40:58AM +0200, Laurenz Albe wrote: > On Fri, 2025-07-18 at 00:57 +0200, Pierre Barre wrote: > > Looking forward to your feedback and questions! > > I think the biggest hurdle you will have to overcome is to > convince notoriously paranoid DBAs that this tall stack > prov

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

> "NFS" is a key word that does not inspire confidence in PostgreSQL circles... Coming back to this, I just implemented 9P, which should translates to proper semantics for FSYNC. mount -t 9p -o trans=tcp,port=5564,version=9p2000.L,msize=65536,access=user 127.0.0.1 /mnt/9p Best, Pierre On Fri,

Re: Is there any limit on the number of rows to import using copy command

You might also consider Backup/Restore. It appears you're not concerned with data being inserted into the source table after the backup is complete? If so, you can then easily sync the two post restore. On Wed, Jul 23, 2025 at 6:55 AM sivapostg...@yahoo.com < sivapostg...@yahoo.com> wrote: > Th

Re: Is there any limit on the number of rows to import using copy command

On 7/24/25 05:18, sivapostg...@yahoo.com wrote: Thanks Merlin, adrain, Laurenz As a testcase, I split the trigger function into three, one each for insert, update, delete, each called from a separate trigger. IT WORKS!. It worked before, it just slowed down as your cases got bigger. You nee

Re: Is there any limit on the number of rows to import using copy command

Thanks Merlin, adrain, Laurenz As a testcase, I split the trigger function into three, one each for insert, update, delete, each called from a separate trigger.   IT WORKS!. Shouldn't we have one trigger function for all the three trigger events?  Is it prohibited for bulk insert like this? I tr

Re: Possible causes of high_replay lag, given replication settings?

On Fri, 18 Jul 2025 at 21:29, Jon Zeppieri wrote: > > I just had a situation where physical replication fell far behind > (hours). The write and flush lag times were 0, but replay_lag was > high. The replica has hot_standby_feedback on, and both > max_standby_streaming_delay and max_standby_archiv

Re: Is there any limit on the number of rows to import using copy command

On Wed, Jul 23, 2025 at 2:51 AM sivapostg...@yahoo.com wrote: > > Tried in PostgreSQL 11.11 , PostgreSQL 15.2 in Windows 10 > > Here we try to transfer data from one database to another (remote) database. > > Tables do have records ranging from 85000 to 360 along with smaller sized > tables.

Re: Is there any limit on the number of rows to import using copy command

On 7/23/25 01:50, sivapostg...@yahoo.com wrote: Tried in PostgreSQL 11.11 , PostgreSQL 15.2 in Windows 10 The above command succeeds, when 1.  The trigger in Table1 is disabled with all other constraints on. 2.  The no. of rows is within 16000 or less, with Trigger enabled.  We haven't tried

Re: Is there any limit on the number of rows to import using copy command

On Wed, 2025-07-23 at 11:55 +, sivapostg...@yahoo.com wrote: > 1.  I tried running Copy From command from PGAdmin.   > 2.  I ran pg_stat_activity also in another tab [ PGAdmin ]. > > What I observed, > 1.  In about 2 min, in the Dashboard of PGAdmin, the colour changed to Orange > for that pa

Re: Is there any limit on the number of rows to import using copy command

Thanks Laurenz Albe, 1.  I tried running Copy From command from PGAdmin.  2.  I ran pg_stat_activity also in another tab [ PGAdmin ]. What I observed,1.  In about 2 min, in the Dashboard of PGAdmin, the colour changed to Orange for that particular pid.2.  After few seconds, the colour again cha

Re: Request for Feedback on PostgreSQL HA + Load Balancing Architecture

On Wed, 23 Jul 2025 12:13:26 +0100 Achilleas Mantzios wrote: … > Have you consider pgpool-ii ?  I know most support / service companies > push for patroni, and manually separating read / write traffic from > within the app, Indeed > but I still find pgpool's query routing very nice. Using Pgp

Re: Request for Feedback on PostgreSQL HA + Load Balancing Architecture

On 6/18/25 18:12, vijay patil wrote: Hi Team, I am planning to implement a PostgreSQL High Availability (HA) and Load Balancing setup and would greatly appreciate your feedback on the architecture we've designed (see attached diagram). image.png *Overview of the Setup:* * Two Postgr

Re: Is there any limit on the number of rows to import using copy command

On Wed, 2025-07-23 at 08:50 +, sivapostg...@yahoo.com wrote: > Tried in PostgreSQL 11.11 , PostgreSQL 15.2 in Windows 10 Both of these choices are unsavory. Don't use the unsupported v11, and use 15.13 with v15. > Here we try to transfer data from one database to another (remote) database. 

Re: Is there any limit on the number of rows to import using copy command

Tried in PostgreSQL 11.11 , PostgreSQL 15.2 in Windows 10 Here we try to transfer data from one database to another (remote) database.  Tables do have records ranging from 85000 to 360 along with smaller sized tables. No issues while transferring smaller sized tables. I here take one particula

Re: Wrapping a select in another select makes it slower

On 2025-07-23 10:08:31 +1200, David Rowley wrote: > On Wed, 23 Jul 2025 at 03:18, Peter J. Holzer wrote: > > > > PostgreSQL version 17.5 on Ubuntu 24.04 (PGDG build). > > > > -> Merge Left Join (cost=4613.25..7180.30 rows=8357 > > width=136) (actual time=222.037..292242.701 rows

Re: PgBouncer Prepared Statement ERROR

On 7/18/25 13:52, KK CHN wrote: Hi , I am getting error when using  PgBouncer(1.23.1 ) with Postgres 16  (RedHAT 9.4) 2025-07-18 00:00:00 IST ERROR:  prepared statement "S_243" does not exist 2025-07-18 00:00:03 IST ERROR:  prepared statement "S_205" does not exist 2025-07-18 00:00:03 IST ER

Re: Wrapping a select in another select makes it slower

On Wed, 23 Jul 2025 at 03:18, Peter J. Holzer wrote: > > PostgreSQL version 17.5 on Ubuntu 24.04 (PGDG build). > -> Merge Left Join (cost=4613.25..7180.30 rows=8357 > width=136) (actual time=222.037..292242.701 rows=40460 loops=1) > Merge Cond: (ns.nspname =

Re: Request for Feedback on PostgreSQL HA + Load Balancing Architecture

> > Is this architecture considered a best practice within the PostgreSQL > community? I would say Patroni is probably "best practice", but there are other people out there happy with, and expert with, repmgr as well. Are there any potential bottlenecks or failure points I should be aware of?

Re: Postgresql 16.9 fast shutdown hangs with walsenders eating 100% CPU

On Mon, 2025-07-21 at 10:47 +, Klaus Darilion wrote: > (Note: I have also attached the whole email for better readability of the > logs) Your mail looks good enough the way it is: https://postgr.es/m/DBAPR03MB6358854AD71C8ABA5CA10A8DF15DA%40DBAPR03MB6358.eurprd03.prod.outlook.com > Our setup

Re: Performance of JSON type in postgres

On 7/19/25 14:19, veem v wrote: On Sun, 20 Jul 2025 at 02:29, Adrian Klaver > wrote: On 7/19/25 13:39, veem v wrote: > I thought you are answered that with your tests above? At least for the Postgres end. As to the Snowflake end you will need

Re: Performance of JSON type in postgres

On Sat, Jul 19, 2025 at 5:19 PM veem v wrote: > > On Sun, 20 Jul 2025 at 02:29, Adrian Klaver > wrote: > >> On 7/19/25 13:39, veem v wrote: >> > >> >> I thought you are answered that with your tests above? At least for the >> Postgres end. As to the Snowflake end you will need to do comparable >

Re: Performance of JSON type in postgres

On Sun, 20 Jul 2025 at 02:29, Adrian Klaver wrote: > On 7/19/25 13:39, veem v wrote: > > > > I thought you are answered that with your tests above? At least for the > Postgres end. As to the Snowflake end you will need to do comparable > tests for fetching the data from Postgres and transforming

Re: Performance of JSON type in postgres

On 7/19/25 13:39, veem v wrote: Thank you. I tested below for sample data. I see loading or serialization seems a lot slower(twice as slower) in JSONB as compared to JSON. Whereas storage looks efficient in JSONB. and reading performance of nested fields are 7-8 times slower in JSON as co

Re: Performance of JSON type in postgres

On Tue, 15 Jul 2025 at 23:02, Merlin Moncure wrote: > On Mon, Jul 14, 2025 at 2:01 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Mon, Jul 14, 2025 at 12:54 PM Adrian Klaver >> wrote: >> >>> On 7/14/25 12:51, veem v wrote: >>> > So I want to >>> > understand the experts' opi

Re: Regarding logical replication issues with PostgreSQL versions 16 and above

On Fri, 2025-07-18 at 19:08 +0800, yexiu-glory wrote: > I'm facing a problem here: our business requires logical data replication to > other > departments, but at the same time, sensitive fields need to be filtered out. > Therefore, we used the column filtering function when creating logical > re

Re: PgBouncer Prepared Statement ERROR

On Fri, 2025-07-18 at 18:22 +0530, KK CHN wrote: > I am getting error when using  PgBouncer(1.23.1 ) with  Postgres 16  (RedHAT > 9.4) > > 2025-07-18 00:00:00 IST ERROR:  prepared statement "S_243" does not exist > 2025-07-18 00:00:03 IST ERROR:  prepared statement "S_205" does not exist > 2025-0

Re: PgBouncer Prepared Statement ERROR

Make sure max_prepared_statements is set to nonzero in your config. See: https://www.crunchydata.com/blog/prepared-statements-in-transaction-mode-for-pgbouncer Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

> The interesting thing is, a few searches about the performance return mostly > negative impressions about their object storage in comparison to the original > S3. I think they had a rough start, but it's quite good now from what I've experienced. It's also dirt-cheap, and they don't bill for

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Thanks, I learned something else: I didn't know Hetzner offered S3 compatible storage. The interesting thing is, a few searches about the performance return mostly negative impressions about their object storage in comparison to the original S3. Finding out what kind of performance your benchmark

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Now, I'm trying to understand how CAP theorem applies here. Traditional PostgreSQL replication has clear CAP trade-offs - you choose between consistency and availability during partitions. But when PostgreSQL instances share storage rather than replicate: - Consistency seems maintained (same dat

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Hi Seref, For the benchmarks, I used Hetzner's cloud service with the following setup: - A Hetzner s3 bucket in the FSN1 region - A virtual machine of type ccx63 48 vCPU 192 GB memory - 3 ZeroFS nbd devices (same s3 bucket) - A ZFS stripped pool with the 3 devices - 200GB zfs L2ARC - Postgres con

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Sorry, this was meant to go to the whole group: Very interesting!. Great work. Can you clarify how exactly you're running postgres in your tests? A specific AWS service? What's the test infrastructure that sits above the file system? On Thu, Jul 17, 2025 at 11:59 PM Pierre Barre wrote: > Hi eve

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

Hi Laurenz, > I think the biggest hurdle you will have to overcome is to > convince notoriously paranoid DBAs that this tall stack > provides reliable service, honors fsync() etc. Indeed, but that doesn't have to be "sudden." I think we need to gain confidence in the whole system gradually by st

Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance

On Fri, 2025-07-18 at 00:57 +0200, Pierre Barre wrote: > Looking forward to your feedback and questions! I think the biggest hurdle you will have to overcome is to convince notoriously paranoid DBAs that this tall stack provides reliable service, honors fsync() etc. Performance is great, but it i

Re: Should we document the cost of pg_database_size()? Alternatives?

On Thu, Jul 17, 2025 at 8:55 PM Craig Ringer wrote: [snip] > > FS-based sizing isn't really enough > > > Asking users to monitor at the filesystem level works, kind-of, but > it'll lead to confusion due to WAL and temp files in simple installs. > To get decent results they will n

Re: Should we document the cost of pg_database_size()? Alternatives?

On Fri, 18 Jul 2025 at 12:54, Craig Ringer wrote: > [...] > I recently ran into an issue where a monitoring component was calling > pg_catalog.pg_database_size() at a ~15s interval on a large > schema-sharded database. It took so long that the query was timing out > before the next 15s scrape inte

Re: Bypassing Directory Ownership Check in PostgreSQL 16.6 with Secure z/OS NFS (AT-TLS)

On Wed, Jul 16, 2025 at 8:42 PM Greg Sabino Mullane wrote: > On Wed, Jul 16, 2025 at 9:25 AM Amol Inamdar wrote: > >> >>1. NFS mount point is for /nfs-mount/postgres (and permissions locked >>down so that Postgres cannot create directories in here) >>2. Postgres data directory is /nf

Re: Query regarding support of test_decoding and PGReplicationStream with Standby Logical Replication

Hi Dinesh, Yes, that is correct. Regards, Kiran On Tue, Jul 15, 2025 at 11:43 PM DINESH NAIR wrote: > > Hi Kiran, > > Are we trying to perform logical replication from create replication slots > on a standby server and use logical decoding plugins(JDBC > PGReplicationStream API) to stream chan

Re: Bypassing Directory Ownership Check in PostgreSQL 16.6 with Secure z/OS NFS (AT-TLS)

that should have gone out with floppy disks. You need some extremely fundamental re-examination of your design decisions. At the moment I am content to say that Postgres does not support this storage mechanism and we do not intend to do so in the future. regards, tom lane

Re: Bypassing Directory Ownership Check in PostgreSQL 16.6 with Secure z/OS NFS (AT-TLS)

@Laurenz Albe If you pre-create the data directory with the appropriate permissions, what keeps you from giving ownership to the correct user too? Our NFS server is not a regular linux based server, it's on zOS (Mainframes) with AT-TLS security enabled, hence it doesn't allow changing of ownershi

Re: Bypassing Directory Ownership Check in PostgreSQL 16.6 with Secure z/OS NFS (AT-TLS)

On Wed, Jul 16, 2025 at 9:25 AM Amol Inamdar wrote: > >1. NFS mount point is for /nfs-mount/postgres (and permissions locked >down so that Postgres cannot create directories in here) >2. Postgres data directory is /nfs-mount/postgres/db >3. > >With secured NFS + AT-TLS setup P

Re: Bypassing Directory Ownership Check in PostgreSQL 16.6 with Secure z/OS NFS (AT-TLS)

On Wed, 2025-07-16 at 18:54 +0530, Amol Inamdar wrote: > I would like to rephrase the question a little bit, below is how our setup > going to be  >    1. NFS mount point is for /nfs-mount/postgres (and permissions locked down > so > that Postgres cannot create directories in here) >    2.

Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

On 7/16/25 00:40, Achilleas Mantzios wrote: On 7/16/25 08:22, KK CHN wrote: How? I am getting , connecting directly to postgresql, or pgpool, or pgbouncer : achill@smadevnu:~ % psql "postgresql://localhost:5432?statement_timeout=10" psql: error: invalid URI query parameter: "statement_tim

Re: Bypassing Directory Ownership Check in PostgreSQL 16.6 with Secure z/OS NFS (AT-TLS)

Quoting Tom's earlier email: "(But I too *would not use Postgres-over-NFS for any critical data*. Too many moving parts. It's tough enough to ensure crash safety with local storage.)" You're going through a lot of security effort to implement a Worst Practice. On Wed, Jul 16, 2025 at 9:25 AM Amo

Re: Bypassing Directory Ownership Check in PostgreSQL 16.6 with Secure z/OS NFS (AT-TLS)

Hi All, I would like to rephrase the question a little bit, below is how our setup going to be 1. NFS mount point is for /nfs-mount/postgres (and permissions locked down so that Postgres cannot create directories in here) 2. Postgres data directory is /nfs-mount/postgres/db 3. Wit

Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

On 7/16/25 08:40, Achilleas Mantzios wrote: On 7/16/25 08:22, KK CHN wrote: Hi, I am facing an issue with PgBouncer-Postgres setup, *PgBouncer (PgBouncer 1.23.1 running on VM instance FreeBSD 14.2) and PostgreSQL DB cluster on a virtual machine (PG version 16.0,  RHEL 9.4) *. My applica

Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

There's an option to ignore parameters in pgbouncer's config. Here's an SO answer that gives an example: https://stackoverflow.com/a/36911794 On Wed, 16 Jul 2025, 09:19 KK CHN, wrote: > Hi, > > I am facing an issue with PgBouncer-Postgres setup, > > *PgBouncer (PgBouncer 1.23.1 running on VM ins

Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

On 7/16/25 08:22, KK CHN wrote: Hi, I am facing an issue with PgBouncer-Postgres setup, *PgBouncer (PgBouncer 1.23.1 running on VM instance FreeBSD 14.2) and PostgreSQL DB cluster on a virtual machine (PG version 16.0,  RHEL 9.4) *. My application backend is nodeJS which throws the follow

Re: Removing terminal period from varchar string in table column

On Tue, 15 Jul 2025, Thom Brown wrote: UPDATE companies SET company_name = rtrim(company_name, '.') WHERE company_name != rtrim(company_name, '.'); Thom, That makes sense. The web pages I read assumed I knew to use the UPDATE command. As this was the first time I needed to clean column conten

Re: Removing terminal period from varchar string in table column

On Tue, 15 Jul 2025, 18:59 Rich Shepard, wrote: > On Tue, 15 Jul 2025, Thom Brown wrote: > > > There are various options, but perhaps just use rtrim. > > rtrim(company_name, '.') > > Thom, > > I looked at rtrim() but didn't see where to specify the table name. Would > it > be `select * from table

Re: Query regarding support of test_decoding and PGReplicationStream with Standby Logical Replication

Hi Kiran, Are we trying to perform logical replication from create replication slots on a standby server and use logical decoding plugins(JDBC PGReplicationStream API) to stream changes from the standby Is my understanding correct? Hi, I have a question regarding the new featu

Re: Removing terminal period from varchar string in table column

On Tue, 15 Jul 2025, Jeff Ross wrote: How about test:     select company_name, replace(company_name,'.','') from companies; update:     update companies set company_name = replace(company_name,'.','') where company_name like '%.'; Jeff, These contain the table and column names I didn't see

  1   2   3   4   5   6   7   8   9   10   >