Re: CALL and named parameters

On Fri, Aug 8, 2025 at 1:33 AM Tom Lane wrote: > Christoph Moench-Tegeder writes: > > ## Dominique Devienne (ddevie...@gmail.com): > >> dd_v185=> call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'); > >> ERROR: procedure Epos-DBA.db_grant_connect_to(grantee_role => > >> unknown) does n

Re: Questions about the continuity of WAL archiving

Thank you for your reply. The archived files can be used for PITR (Point-In-Time Recovery), allowing recovery to any point between WAL 80 and 100 on timeline 1. Additionally, if there's a backup taken during timeline 1 and a switchover to a new primary has occurred without taking a new full backup

Re: Questions about the continuity of WAL archiving

On 8/7/25 20:20, px shi wrote: Hi, There is a scenario: the current timeline of the PostgreSQL primary node is 1, and the latest WAL file is 100. The standby node has also received up to WAL file 100. However, the latest WAL file archived is only file 80. If the primary node crashes at this po

Re: CALL and named parameters

Christoph Moench-Tegeder writes: > ## Dominique Devienne (ddevie...@gmail.com): >> dd_v185=> call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'); >> ERROR: procedure Epos-DBA.db_grant_connect_to(grantee_role => >> unknown) does not exist >> LINE 1: call "Epos-DBA".db_grant_connect_to(gr

Re: CALL and named parameters

## Dominique Devienne (ddevie...@gmail.com): > dd_v185=> call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'); > ERROR: procedure Epos-DBA.db_grant_connect_to(grantee_role => > unknown) does not exist > LINE 1: call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'... There's the

Re: Libpq.dll

On 8/7/25 05:14, suport...@unitsistemas.com.br wrote: When I say "my Postgres", I mean the Postgres 15.3 that I have installed. How about 32-bit lib.dlls? I downloaded them from Google, placed them in my executable folder and in the Bin and Lib folders of my Postgres 15.3, and then extracted t

Re: CALL and named parameters

Dominique Devienne writes: > But still, arg names are not part of the signature. > So they should be checked after the fact. No, that's not how it works. David's comment about signature reflects the fact that the primary key of pg_proc is name + schema + input argument types. Arg names are inde

Re: CALL and named parameters

On Thu, Aug 7, 2025 at 4:36 PM Tom Lane wrote: > Dominique Devienne writes: > > I was expecting an error telling me the procedure exists, but the > > argument name used in the call didn't. Then it's obvious to me what > > mistake I made. If argument names don't participate in the function's > > s

Re: CALL and named parameters

Dominique Devienne writes: > I was expecting an error telling me the procedure exists, but the > argument name used in the call didn't. Then it's obvious to me what > mistake I made. If argument names don't participate in the function's > signature, why should they participate in the lookup? Do th

Re: CALL and named parameters

On Thu, Aug 7, 2025 at 4:21 PM Tom Lane wrote: > The message presumably was like > > ERROR: procedure ... does not exist > HINT: No procedure matches the given name and argument types. You might need > to add explicit type casts. Hi Tom. Indeed it was. I shared my psql session showing that.

Re: CALL and named parameters

"David G. Johnston" writes: > On Thursday, August 7, 2025, Dominique Devienne wrote: >> What's not nice is in the way it failed IMHO. I guess I persist it's >> not a user friendly message :) > Then write the error message you would have liked to see. The message presumably was like ERROR: pro

Re: CALL and named parameters

On Thu, Aug 7, 2025 at 3:30 PM David G. Johnston wrote: > On Thursday, August 7, 2025, Dominique Devienne wrote: >> Can you overload a function solely by changing an argument name? > No, the signature is only the name and input argument types. Thanks for confirming. >> So the function did exist

Re: CALL and named parameters

Hi čt 7. 8. 2025 v 15:30 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal: > On Thursday, August 7, 2025, Dominique Devienne > wrote: >> >> >> What's not nice is in the way it failed IMHO. I guess I persist it's >> not a user friendly message :) > > > Then write the error messag

Re: CALL and named parameters

On Thursday, August 7, 2025, Dominique Devienne wrote: > > > What's not nice is in the way it failed IMHO. I guess I persist it's > not a user friendly message :) Then write the error message you would have liked to see. > > Can you overload a function solely by changing an argument name? No

Re: CALL and named parameters

p me, in a way :). So mea culpa. Apologies for the misguided rant (and smaller re-rant above :)). PS: below's my psql session that led me to the wrong conclusion. dd_v185=> call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'); ERROR: procedure Epos-DBA.

Re: Libpq.dll

When I say "my Postgres", I mean the Postgres 15.3 that I have installed.How about 32-bit lib.dlls? I downloaded them from Google, placed them in my executable folder and in the Bin and Lib folders of my Postgres 15.3, and then extracted the package into the folders.The link where I downloaded the

Re: Libpq.dll

2)a) Windows 10 Home Single Language, Version 22H2b) The settings and steps I followed were: first, I opened a project that already existed in my Delphi 10.2, then started editing it. I changed one of the project columns, and the message appeared:"None of the dynamic libraries can be found or is no

Re: Approach for DB migration

On Thu, Aug 7, 2025 at 12:21 AM Siraj G wrote: > Yes Ron, database migration service. But it works better if we have to > migrate all the DBs in one shot > Is that really a problem? But if there's a problem with DMS, then logical replication should do the trick. > since it converts the target

Re: Approach for DB migration

Yes Ron, database migration service. But it works better if we have to migrate all the DBs in one shot since it converts the target DB into a read replica during the migration. Regards Siraj On Thu, Aug 7, 2025 at 9:33 AM Ron Johnson wrote: > On Wed, Aug 6, 2025 at 9:30 PM Siraj G wrote: > >>

Re: Approach for DB migration

On Wed, Aug 6, 2025 at 9:30 PM Siraj G wrote: > Hello Experts! > > I have this environment with 100+ DBs and would like to migrate to GCP's > cloud SQL for Postgres. > > Primary: 48 CPUs, 48GB memory > Secondary/Read Replica: 80 CPUs, 128GB memory > PG version: 12.22 (we have already started the

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

On 8/6/25 14:39, Rumpi Gravenstein wrote: That having a table and function with similar names causes this problem is a bug. Actually it is that a type and function have the same name. The connection is the table as that where the type gets it's name. Further complicated by you doing _type_nam

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

On Wed, Aug 6, 2025 at 2:39 PM Rumpi Gravenstein wrote: > That having a table and function with similar names causes this problem is > a bug. > > Arguably. But it seems likely it's one we are not going to fix. David J.

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

"David G. Johnston" writes: > On Wednesday, August 6, 2025, Rumpi Gravenstein wrote: >> Here's a reproducible test case that causes the problem in different >> schemas. The issue appears to be related to creating a table and a >> function that has the same name as the table with a prepended unde

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

That having a table and function with similar names causes this problem is a bug. Going forward I'll be sure to avoid the problem. On Wed, Aug 6, 2025 at 5:35 PM David G. Johnston wrote: > On Wednesday, August 6, 2025, Rumpi Gravenstein > wrote: > >> Here's a reproducible test case that causes

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

On Wednesday, August 6, 2025, Rumpi Gravenstein wrote: > Here's a reproducible test case that causes the problem in different > schemas. The issue appears to be related to creating a table and a > function that has the same name as the table with a prepended underscore. > Don’t do that. Naming

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

Here's a reproducible test case that causes the problem in different schemas. The issue appears to be related to creating a table and a function that has the same name as the table with a prepended underscore. rumpi_test -- table name _rumpi_test -- function name Here's the test case; SELECT v

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

On 8/6/25 1:17 PM, Rumpi Gravenstein wrote: Just coming back to this.  Don't know how to interpret this: xxx_pub_dev_2_db=# select proname, pronamespace, oid from pg_proc where proname like '%sa_setup%';     proname     | pronamespace |   oid +--+- _sa_s

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

Just coming back to this. Don't know how to interpret this: xxx_pub_dev_2_db=# select proname, pronamespace, oid from pg_proc where proname like '%sa_setup%'; proname | pronamespace | oid +--+- _sa_setup_role | 7038406 | 7869125 (1 row) xxx_pub_

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

Puzzling. I'd do: pg_dump --schema-only xxx_pub_dev_2_db | grep -i -A5 _sa_setup_role Note the -i. That _might_ be important. On Wed, Aug 6, 2025 at 4:18 PM Rumpi Gravenstein wrote: > Just coming back to this. Don't know how to interpret this: > > xxx_pub_dev_2_db=# select proname, pronamesp

Re: Libpq.dll

On 8/6/25 11:24, suport...@unitsistemas.com.br wrote: The executable's libpq is 32-bit, and its dependencies are correct and on the same architecture. In my Postgres, libpq was on a 64-bit You need to give more details on what '... my Postgres ...' is? Did you download it as package or did

Re: CALL and named parameters

## Dominique Devienne (ddevie...@gmail.com): > Turns out, thanks to ChatGPT for clueing me in, CALL does NOT support > named parameters. Turns out, ChatGPT is once again very wrong. As per https://www.postgresql.org/docs/14/sql-call.html , "Arguments can include parameter names, using the syntax

Re: Libpq.dll

On Wed, Aug 6, 2025 at 11:46 AM Adrian Klaver wrote: > > On 8/6/25 06:17, suport...@unitsistemas.com.br wrote: > > Hello, how are you? I'm asking for help. When I try to compile a 32-bit > > executable, in my 32-bit Delphi as well, in my 64-bit operating system, > > an error appears about libpq.dl

Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID

On 8/6/25 05:37, Charles Qi wrote: And if we do the updates in multiple subtransactions, multixact will be created, which is not created when the BEFORE ROW UPDATE trigger is absent. Is this behavior by design? If so, what is the purpose for the behavior? I would say this is by design. My rea

Re: CALL and named parameters

On 8/6/25 10:48, Dominique Devienne wrote: (sorry, this is a rant...). Was getting an error calling a procedure ERROR: procedure ... does not exist HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. I verify USAGE on the SCHEMA of the pro

Re: CALL and named parameters

Hi st 6. 8. 2025 v 19:49 odesílatel Dominique Devienne napsal: > (sorry, this is a rant...). > > Was getting an error calling a procedure > > ERROR: procedure ... does not exist > HINT: No procedure matches the given name and argument types. You > might need to add explicit type casts. > > I v

Re: Libpq.dll

On 8/6/25 06:17, suport...@unitsistemas.com.br wrote: Hello, how are you? I'm asking for help. When I try to compile a 32-bit executable, in my 32-bit Delphi as well, in my 64-bit operating system, an error appears about libpq.dll, saying that it was found, but not loaded. Can you help me with

Re: Stored procedures or raw queries

> On Aug 6, 2025, at 8:03 AM, Simon Connah wrote: > > Hi, > > I'm pretty new to PostgreSQL and am building a simple website with it. > > My main question is whether I should use stored procedures / functions > or whether I should embed raw SQL queries in my backend? I understand > that proce

Re: Stored procedures or raw queries

In my experience, starting with store procedures in a project might seem overwhelming. Still, as time passes, and your deliverables output grows, it becomes easier to maintain and improve your products. It is just a different paradigm that allows you to focus on improving your code everywhere. Rem

Re: Stored procedures or raw queries

On Wed, Aug 6, 2025 at 2:04 PM Simon Connah wrote: > My main question is whether I should use stored procedures / functions > or whether I should embed raw SQL queries in my backend? I understand > that procedures are faster as it cuts down on the round trip speed and > the database can optimise i

Re: Stored procedures or raw queries

Thank you all for your help. It is much appreciated. I'll have a play around and see how things work out. Simon. On Wed, 6 Aug 2025 at 14:17, Philip Semanchuk wrote: > > > > > On Aug 6, 2025, at 8:03 AM, Simon Connah wrote: > > > > Hi, > > > > I'm pretty new to PostgreSQL and am building a simp

Re: Stored procedures or raw queries

Generally you should use stored procedures when it will reduce the number of round trips to the database. Wrapping simple SELECT statements in a stored proc just adds friction for updating the application especially as the number of developers grows. On Wed, Aug 6, 2025, 8:04 AM Simon Connah wro

Re: Stored procedures or raw queries

Hi st 6. 8. 2025 v 14:04 odesílatel Simon Connah napsal: > Hi, > > I'm pretty new to PostgreSQL and am building a simple website with it. > > My main question is whether I should use stored procedures / functions > or whether I should embed raw SQL queries in my backend? I understand > that proc

Re: PGDG16 repository error - repomd.xml.asc is empty

Thx, Created an issue ( https://github.com/pgdg-packaging/pgdg-rpms/issues/67 ) for the debuginfo repository; time correlation is there as you mentioned. Kind regards Ales Zeleny út 5. 8. 2025 v 17:04 odesílatel Adrian Klaver napsal: > On 8/5/25 01:05, Aleš Zelený wrote: > > The pgdg16-debugin

Re: PGDG16 repository error - repomd.xml.asc is empty

On 8/5/25 01:05, Aleš Zelený wrote: The pgdg16-debuginfo repository suffers from another issue (yesterday it worked): You should be bringing this up here: https://github.com/pgdg-packaging/pgdg-rpms/issues/ Failed to download metadata for repo 'pgdg16-debuginfo': repomd.xml GPG signature v

Re: Notices not printed by PQtrace

On Tue, Aug 5, 2025 at 3:20 PM Álvaro Herrera wrote: > On 2025-Aug-05, Dominique Devienne wrote: > Yeah, I think that makes sense. Feel free to propose a patch. OK, fair enough. If I ever do (fat chances), I'd add another opt-in flag to make the trace machine readable (see my older thread about

Re: Notices not printed by PQtrace

On 2025-Aug-05, Dominique Devienne wrote: > Hi. I'm using PQtrace for client-side tracing with PQtrace, > which I thought was showing protocol messages in details. > > But turns out that RAISE notices from PL/pgSQL are not there. Hmm, don't they show up as NOTICE messages? I think they should.

Re: PGDG16 repository error - repomd.xml.asc is empty

The pgdg16-debuginfo repository suffers from another issue (yesterday it worked): Failed to download metadata for repo 'pgdg16-debuginfo': repomd.xml GPG signature verification error: Bad GPG signature Kind regards Ales Zeleny po 4. 8. 2025 v 17:20 odesílatel Aleš Zelený napsal: > Probably du

Re: PGDG16 repository error - repomd.xml.asc is empty

Probably duplicate for BUG #19009 / BUG #19010. po 4. 8. 2025 v 17:01 odesílatel Aleš Zelený napsal: > Hello, > I've found that PGDG16 repository access is failing (while last Thursday > it worked): > > Example - pgdg 17 works, pgdg16 doesn't work: > > $ yum repolist pgdg16 pgdg17 > repo id

Re: What is the specific release date of PG18?

On Monday, August 4, 2025, David G. Johnston wrote: > On Sunday, August 3, 2025, 矫顺田 wrote: > >> What is the specific release date of PG18? >> > > Here is some data to let you make an informed decision. Ignore anything > before 9.0 I suppose. IIRC it will be almost certainly be a Thursday. > >

Re: What is the specific release date of PG18?

On Sunday, August 3, 2025, 矫顺田 wrote: > What is the specific release date of PG18? > Here is some data to let you make an informed decision. Ignore anything before 9.0 I suppose. IIRC it will be almost certainly be a Thursday. David J.

Re: What is the specific release date of PG18?

On Mon, 4 Aug 2025 at 18:59, 矫顺田 wrote: > What is the specific release date of PG18? The exact date is not yet determined. Per [1], the approximate time is "September/October 2025". You might also look at the date we released previous versions, but there's absolutely no guarantee that it'll end

Re: Get info about the index

On 8/1/25 20:50, Igor Korot wrote: Adrian, If I read the docs correctly, this field indicates whether the WHERE condition is actually present. Am I right? No, it refers to: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION Thx for clarification. Bu

Re: Get info about the index

Adrian, On Fri, Aug 1, 2025 at 8:13 PM Adrian Klaver wrote: > > On 8/1/25 17:06, Igor Korot wrote: > > Hi, Laurenz, > > > > > I'm looking at the pg_index table and I see it has: > > > > [quote] > > indisexclusion bool > > > > If true, this index supports an exclusion constraint > > [/quote] > > >

Re: Get info about the index

On Fri, Aug 1, 2025 at 8:06 PM Igor Korot wrote: > > If I read the docs correctly, this field indicates whether the > WHERE condition is actually present. > Are you referring to the condition on a partial index? You can get that using pg_get_expr(): select pg_get_expr(indpred, indrelid) from pg_

Re: Get info about the index

On 8/1/25 17:06, Igor Korot wrote: Hi, Laurenz, I'm looking at the pg_index table and I see it has: [quote] indisexclusion bool If true, this index supports an exclusion constraint [/quote] If I read the docs correctly, this field indicates whether the WHERE condition is actually present.

Re: Get info about the index

Hi, Laurenz, On Tue, Jul 29, 2025 at 7:07 AM Laurenz Albe wrote: > > On Tue, 2025-07-29 at 06:46 -0500, Igor Korot wrote: > > SELECT > > t.relname AS table_name, > > i.relname AS index_name, > > a.attname AS column_name > > FROM > > pg_class t, > > pg_class i, > > pg_index

Re: Failing to allocate memory when I think it shouldn't

## Siraj G (tosira...@gmail.com): > I am getting the same error in postgres 12 (sorry that our version upgrade > sucks). In all likelyhood, this is a somewhat different situation, as nothing here points to JIT. > I see that hash_mem_multiplier is available from version 13. What > could we do in

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On 2025-Jul-31, Dominique Devienne wrote: > But also, it's weird DELETE allows you to delete all rows. > Yet prevents you from deleting just one, i.e. a subset. But you don't know what you deleted, so you cannot exfiltrate useful info by repeatedly deleting with varying WHERE values. I suspect t

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On Thu, Jul 31, 2025 at 4:11 PM Tom Lane wrote: > Dominique Devienne writes: > > On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge > > wrote: > >> It doesn't lie. The role has DELETE privilege. I guess what it lacks is > >> the SELECT privilege. If you do a "DELETE FROM ... WHERE ...", you need

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On Thu, Jul 31, 2025 at 4:13 PM Adrian Klaver wrote: > On 7/31/25 04:37, Dominique Devienne wrote: > So the below from the original post was not correct: > > "My setup ensures that the role I SET LOCAL ROLE to, has (indirectly) > been granted DMLs on that table." Not so. DML is Data Modification

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On 7/31/25 08:06, David G. Johnston wrote: On Thursday, July 31, 2025, Adrian Klaver > wrote: So the below from the original post was not correct: "My setup ensures that the role I SET LOCAL ROLE to, has (indirectly) been granted DMLs on that table

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On Thursday, July 31, 2025, Adrian Klaver wrote: > On 7/31/25 04:37, Dominique Devienne wrote: > >> On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge >> wrote: >> >>> On 31/07/2025 10:41, Dominique Devienne wrote: >>> On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver < adrian.kla...@aklav

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On 7/31/25 04:37, Dominique Devienne wrote: On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge wrote: On 31/07/2025 10:41, Dominique Devienne wrote: On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver wrote: how can has_table_privilege() "lie" like this? It doesn't lie. The role has DELETE privil

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

Dominique Devienne writes: > On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge > wrote: >> It doesn't lie. The role has DELETE privilege. I guess what it lacks is >> the SELECT privilege. If you do a "DELETE FROM ... WHERE ...", you need >> the SELECT privilege to perform the WHERE. Without "WHE

Re: Yet more ROLE changes in v18 beta1???

On Thu, Jun 5, 2025 at 4:57 AM Tom Lane wrote: > Dominique Devienne writes: > > Unfortunately, digging into this is not something I can do right away. > > v18 is still a few months out, I do hope I can investigate before that. > > It'd be good if you could prioritize that a bit more. If there is

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge wrote: > On 31/07/2025 10:41, Dominique Devienne wrote: > > On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver > > wrote: > > how can has_table_privilege() "lie" like this? > > It doesn't lie. The role has DELETE privilege. I guess what it lacks is >

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On 31/07/2025 10:41, Dominique Devienne wrote: On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver wrote: My suspicion is that there is a missing piece in your chain of roles. But my point Adrian is that, in my case, has_table_privilege() returns true (t) yet the delete fails. Contrary to your exam

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver wrote: > My suspicion is that there is a missing piece in your chain of roles. But my point Adrian is that, in my case, has_table_privilege() returns true (t) yet the delete fails. Contrary to your example above. I can easily accept that the problem l

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On 7/30/25 09:21, Adrian Klaver wrote: On 7/30/25 08:47, Dominique Devienne wrote: On Wed, Jul 30, 2025 at 5:23 PM Adrian Klaver wrote: On 7/30/25 04:37, Dominique Devienne wrote: Are there special consideration I'm unaware of, regarding SET ROLE inside routines? What is the ROLE that defi

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On 7/30/25 08:47, Dominique Devienne wrote: On Wed, Jul 30, 2025 at 5:23 PM Adrian Klaver wrote: On 7/30/25 04:37, Dominique Devienne wrote: Are there special consideration I'm unaware of, regarding SET ROLE inside routines? What is the ROLE that defined the function? A 3rd role. But does

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On Wed, Jul 30, 2025 at 5:23 PM Adrian Klaver wrote: > On 7/30/25 04:37, Dominique Devienne wrote: > > Are there special consideration I'm unaware of, regarding SET ROLE > > inside routines? > What is the ROLE that defined the function? A 3rd role. But does it matter? Given that this is in SECUR

Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On 7/30/25 04:37, Dominique Devienne wrote: Hi. PostgreSQL v17 here. I'm stumped by something, and would like pointers to sort something out. Inside my function, I do: EXECUTE format('SET LOCAL ROLE %I', schema_owner); raise notice 'CURRENT_USER = %, can DELETE = %', CURRE

Re: Failing to allocate memory when I think it shouldn't

Hello Christoph I am getting the same error in postgres 12 (sorry that our version upgrade sucks). I see that hash_mem_multiplier is available from version 13. What could we do in version 12? The error is: Timezones: 104120 total in 2 blocks; 2624 free (0 chunks); 101496 usedindex info: 102

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

On Tue, 2025-07-29 at 15:00 +0800, yexiu-glory wrote: > The approach of adding unique indexes is not suitable for our business. Well, then I guess that logical replication is not suitable for your business. Yours, Laurenz Albe

Re: Regarding logical replication issues with PostgreSQL versions 16and 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 logica

Re: Get info about the index

On Tue, Jul 29, 2025, 9:24 AM Laurenz Albe wrote: > On Tue, 2025-07-29 at 07:18 -0500, Igor Korot wrote: > > If I have a database for some financial data for the year 2024 in the > > mydb_2024 and I have current year financial data in the mydb_2025 how > > can I compare the data? > > You connect

Re: Get info about the index

On Tue, 2025-07-29 at 07:18 -0500, Igor Korot wrote: > If I have a database for some financial data for the year 2024 in the > mydb_2024 and I have current year financial data in the mydb_2025 how > can I compare the data? You connect to both and query them both. Alternatively, you use postgres_f

Re: Get info about the index

On Tue, Jul 29, 2025 at 7:07 AM Laurenz Albe wrote: > > On Tue, 2025-07-29 at 06:46 -0500, Igor Korot wrote: > > SELECT > > t.relname AS table_name, > > i.relname AS index_name, > > a.attname AS column_name > > FROM > > pg_class t, > > pg_class i, > > pg_index ix, > > p

Re: Get info about the index

On Tue, 2025-07-29 at 06:46 -0500, Igor Korot wrote: > SELECT >     t.relname AS table_name, >     i.relname AS index_name, >     a.attname AS column_name > FROM >     pg_class t, >     pg_class i, >     pg_index ix, >     pg_attribute a > WHERE >     t.oid = ix.indrelid AND >     i.oid = ix.indexr

Re: Get info about the index

Hi, guys, On Mon, Jul 28, 2025 at 10:13 AM Laurenz Albe wrote: > > 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. I finally formulate my google request and got this: https:

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

  1   2   3   4   5   6   7   8   9   10   >