Re: Bloated toast table with empty associated table

On Thu, Mar 20, 2025 at 7:40 AM Paul Allen wrote: > Hello. > > Preconditions. > > I have some empty table and constantly try to execute `insert ... on > conflict do update ...` on it. My data in row which I try to insert is > invalid by violation of foreing key constraint, so I am getting error >

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

"David G. Johnston" writes: > On Thu, Mar 20, 2025 at 11:54 AM Tom Lane wrote: >> I think it's a mistake to suppose that pg_type_d.h is the only >> place where there's a risk of confusion. We should be thinking >> about this more generally: genbki.pl is taking zero thought to >> make what it emi

Re: [EXTERNAL] RDS IO Read time

On 3/31/25 11:23 AM, Christophe Pettus wrote: On Mar 31, 2025, at 10:54, Eden Aharoni wrote: So you believe it's strictly an EBS issue? You are asking about disk I/O, that is the purview of the OS and hardware. In your case both are created and managed by AWS, only they know what is g

Re: Postgres incremental database updates thru CI/CD

There are no built in tools for this in Postgres. There are other tools like the one mentioned that you can use instead. I've used Liquibase for migrations for multiple companies now and it works well enough. If you have to support rollbacks for your deployments, that is a pretty manual process fo

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

=?utf-8?Q?=C3=81lvaro?= Herrera writes: > That said, we could add a comment that makes this more obvious: > ... > This looks a tad redundant in pg_type.h itself, but makes the generated > pg_type_d.h file more obvious: I think it's a mistake to suppose that pg_type_d.h is the only place where the

Re: Experience and feedback on pg_restore --data-only

On 3/24/25 07:24, Dimitrios Apostolou wrote: On Sun, 23 Mar 2025, Laurenz Albe wrote: On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote: Performance issues: (important as my db size is >5TB) * WAL writes: I didn't manage to avoid writing to the WAL, despite having    setting wal_

Re: Issue installing postgis on RHEL9

Hi, On Wed, 2025-04-02 at 11:29 -0500, Danny Im wrote: > I am having issues trying to install the postgis30_13 package on a > RHEL9 > host. Attempting to install the package gives me this error: > > $ sudo dnf install postgis30_13 > Updating Subscription Management repositories. > Error: >  Pr

Re: Experience and feedback on pg_restore --data-only

Dimitrios Apostolou schrieb am 20.03.2025 um 23:48: > Things that made my life hard: > > * plenty of permission denials for both ALTER OWNER or SET SESSION >   AUTHORIZATION (depending on command line switches).  Both of these >   require superuser privilege, but in my case this is not really neede

Re: Experience and feedback on pg_restore --data-only

On 3/20/25 15:48, Dimitrios Apostolou wrote: Rationale: When restoring a backup in an emergency situation, it's fine to run pg_restore as superuser and get an exact replica of the dumped db. AFAICT pg_restore (without --data-only) is optimised for such case. But pg_dump/restore can be used as a

Re: Querying one partition in a function takes locks on all partitions

Renan Alves Fonseca writes: > There is a Note that says: "The entire body of an SQL function is > parsed before any of it is executed. While an SQL function can contain > commands that alter ..." Yup. > If I understood well [2], then both notes may be discarded together in > the next version. N

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

On Thu, Mar 20, 2025 at 2:56 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Thu, Mar 20, 2025 at 11:54 AM Tom Lane wrote: > >> I think it's a mistake to suppose that pg_type_d.h is the only > >> place where there's a risk of confusion. We should be thinking > >> about this more genera

Replication slot WAL reservation

I am trying to understand how logical replication slots work, specifically in regard to how WAL is reserved and freed by a replication slot. My current understanding of the WAL (set to wal_level logical) is that: 1. Every DML operation (insert, update, delete, truncate) will have a row in the WAL

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

On 3/20/25 00:17, Sebastien Flaesch wrote: Let's not deviate from my request: I have implemented various DB client modules using the C client APIs, for Oracle DB, SQL Server, IBM DB2, MySQL/MariaDB, SQLite and PostgreSQL. While I like PostgreSQL a lot, this is the only case where I have to d

Re: Any industry best practise to overcome this specific malware "pg_mem"

On Wed, Apr 2, 2025 at 11:31 AM Adrian Klaver wrote: > On 4/2/25 08:18, Bharani SV-forum wrote: > > Hello MVP's > > Good Morning > > Any industry best practise to overcome this specific malware "pg_mem". > > > > url = > > > https://www.aquasec.com/blog/pg_mem-a-malware-hidden-in-the-postgres-proc

Re: Postgres Query Plan using wrong index

Thanks Tom. Since you mentioned the planner not knowing about the correlation between the columns, I’m curious, why doesn’t making a multivariate statistic make a difference? CREATE STATISTICS col_a_col_b_stats (dependencies) ON col_a, col_b FROM test_table; ANALYZE test_table; And the result

Re: Export operation efficiency in read replica

On 3/20/25 05:58, Siraj G wrote: Hello Laurenz As per my understanding coming to a proper conclusion wrt RPO You still have not defined what RPO is. -- Adrian Klaver adrian.kla...@aklaver.com

Re: Querying one partition in a function takes locks on all partitions

On Sat, 29 Mar 2025 at 06:00, Evgeny Morozov wrote: > > On 23/03/2025 2:35 pm, David Rowley wrote: > >> alter table entity_2 add column new_column text; > > Is this just an example command? You can't add a column to a > > partition directly. > > Yes, it was just the simplest way I could think of

Re: Best way to check if a table is empty

On Sunday, March 23, 2025, David G. Johnston wrote: > > Maybe IOS helps though I do wonder whether a sequential scan skips over > known all-dead pages making that relative benefit go away. > Well, no, since it tracks known visible, not known non-visible, though for something like a fast exists t

Re: Experience and feedback on pg_restore --data-only

Hi Laurenz, On Sun, 23 Mar 2025, Laurenz Albe wrote: On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote: Performance issues: (important as my db size is >5TB) * WAL writes: I didn't manage to avoid writing to the WAL, despite having    setting wal_level=minimal. I even wrote my own

Re: Determine server version from psql script

On 3/22/25 17:31, Igor Korot wrote: Hi, All, I have a big script that populated the DB for me. The language used for the script? Is it actually running in psql? I made it based on the latest available version. However,  i have version 13 installed on my Linux box and so trying to execute

Re: Postgres Query Plan using wrong index

Thanks so much for your help, Tom. Sorry, I didn’t quite understand the answer — I have a few follow-up questions. Sorry, I'm new to Postgres so I am a bit ignorant here and would appreciate any tips on the query planner you could give. 1) Why is the query currently picking the poorly perform

Re: Trying to dynamically create a procedure

On Wed, 2025-03-26 at 20:27 +, Dirschel, Steve wrote: > DO $$ >   > BEGIN >   > EXECUTE 'create or replace procedure junk.test_proc() ' || >   'LANGUAGE plpgsql  '  || >   'AS $$ '    || >   'declare  '  || >   '  v_cnt

Re: Q on SELECT column list pushdown from view to table

Karsten Hilbert writes: > Am Tue, Mar 25, 2025 at 06:55:34PM -0400 schrieb Tom Lane: >> Works fine if you don't mess with the view's security_invoker >> status. > I know but doing so was kind of the point. [ shrug... ] It's not going to work. When the view is inlined into the calling query, you

Re: Best way to check if a table is empty

On Sunday, March 23, 2025, Marcelo Fernandes wrote: > Hi folks, > > I came up with three strategies to verify whether a table is empty. 3 is strictly terrible worse to answer “is live row count > 0”. Using an index likely serves no/negative benefit since it contains no tuple liveness informati

Re: [EXTERNAL] RDS IO Read time

> On Mar 31, 2025, at 12:36, Eden Aharoni wrote: > Hope you’ll have any clue 😊 Based on that, I'd take it up with AWS. It does seem that the EBS mount is under-performing.

Re: Need help understanding has_function_privilege

Hi, and thanks (both of you!) Shouldn't the create role my_user NOINHERIT; avoid this? And since not, why? :-) On Thu, 20 Mar 2025 at 15:07, David G. Johnston wrote: > On Wednesday, March 19, 2025, Cosimo Simeone > wrote: >> >> >> true? >> Well... Ok, "whatever"... I revoke it: >> =# revoke

Best way to check if a table is empty

Hi folks, I came up with three strategies to verify whether a table is empty. I wanted to sound the community to check whether my assumptions are correct for each of these strategies, and to also discuss which strategy is best. ## Strategy 1 [possibly best?] SELECT EXISTS (SELECT 1 FROM foo LIM

Re: Performance issues during pg_restore -j with big partitioned table

On 4/2/25 10:39 AM, Adrian Klaver wrote: --clean will drop the object entirely not TRUNCATE. I'm guessing that this is being done by you per: https://www.postgresql.org/message-id/53760c70-4a87-a453-9e02-57abc9cb2e54%40gmx.net "After each failed attempt, I need to issue a TRUNCATE table

Create replication slot options

I am testing out some streaming logical replication commands and am having trouble specifying options when calling CREATE_REPLICATION_SLOT . I connect to the database with psql "dbname=

Re: Experience and feedback on pg_restore --data-only

On 3/24/25 08:51, Dimitrios Apostolou wrote: On Mon, 24 Mar 2025, Adrian Klaver wrote: On 3/24/25 07:24, Dimitrios Apostolou wrote:  On Sun, 23 Mar 2025, Laurenz Albe wrote:  On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:  Performance issues: (important as my db size is >5TB)