Re: Creating table and indexes for new application

On Fri, 23 Feb, 2024, 1:20 pm sud, wrote: > > > On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, > wrote: > >> On Fri, 2024-02-23 at 02:05 +0530, yudhi s >> >> > 2)Should we be creating composite indexes on each foreign key for >> table2 and table3, because >> > any update or delete on parent is g

Re: Creating table and indexes for new application

On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, wrote: > On Fri, 2024-02-23 at 02:05 +0530, yudhi s > > > 2)Should we be creating composite indexes on each foreign key for table2 > and table3, because > > any update or delete on parent is going to take lock on all child > tables? > > Every foreign

Re: Creating table and indexes for new application

On Fri, 2024-02-23 at 02:05 +0530, yudhi s wrote: > postgres version 15+ database. And it would be ~400million transactions/rows > per day in the > main transaction table and almost double in the multiple child tables and > some child tables > will hold lesser records too. > > We are considering

Re: Question on Table creation

Thank you so much. This really helped. Regards Sud >

Re: Creating table and indexes for new application

My 2 cents... Foreign key indexes are required for avoiding locking when deleting and updating the pk in parent. But insert only table may not have any issue. And we used to do this in other relational databases like oracle but not sure how different is this in PG. However considering your high tra

Re: Postgresql assistance needed

Thanks, I'll check it out. Regards, Sasmit Utkarsh +91-7674022625 On Thu, 22 Feb, 2024, 21:40 Laurenz Albe, wrote: > On Thu, 2024-02-22 at 19:35 +0530, Sasmit Utkarsh wrote: > > Is there any configuration/query that can be checked to verify if > "shc_uadmin" has the correct path set? > > The S

walreceiver fails on asynchronous replica [SEC=UNOFFICIAL]

UNOFFICIAL Hello, We have the following setup in our DEV environment - - Primary/Replica using asynchronous streaming replication - Servers run Ubuntu Linux 5.15.0-88-generic #98-Ubuntu SMP Mon Oct 2 15:18:56 UTC 2023 x86_64 x86_64 GNU/Linux - Postgres version postgr

Re: Postgres 16 missing from apt repo?

On 2/22/24 15:18, Tamal Saha wrote: Hi Adrian, Thanks for the response. I am trying to build the postgres:16.1 docker image and it fails. This is the docker file (I am not associated with Docker Inc.). https://github.com/docker-library/postgres/blob/d416768b1a7f03919b9cf0fef6adc9dcad937888/16/

Re: Postgres 16 missing from apt repo?

Hi Adrian, Thanks for the response. I am trying to build the postgres:16.1 docker image and it fails. This is the docker file (I am not associated with Docker Inc.). https://github.com/docker-library/postgres/blob/d416768b1a7f03919b9cf0fef6adc9dcad937888/16/bookworm/Dockerfile#L93

Re: Postgres 16 missing from apt repo?

On 2/22/24 13:29, Tamal Saha wrote: Hi, I can't find the Postgres 16 folder in https://apt.postgresql.org/pub/repos/apt/pool/

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

On 2024-02-22 22:14 +0100, Vick Khera wrote: > On Wed, Feb 21, 2024 at 4:27 PM Tom Lane wrote: > > > For the moment, I think the only feasible solution is for your trigger > > function to set the search path it needs by adding a "SET search_path > > = whatever" clause to the function's CREATE com

Postgres 16 missing from apt repo?

Hi, I can't find the Postgres 16 folder in https://apt.postgresql.org/pub/repos/apt/pool/ for the last 4-5 days. Is this a known

Re: Performance issue debugging

On Thu, Feb 22, 2024 at 4:03 PM veem v wrote: > Hi All, > As i understand we have pg_stats_activity which shows the real time > activity of sessions currently running in the database. And the > pg_stats_statement provides the aggregated information of the historical > execution of all the queries

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

On Wed, Feb 21, 2024 at 4:27 PM Tom Lane wrote: > For the moment, I think the only feasible solution is for your trigger > function to set the search path it needs by adding a "SET search_path > = whatever" clause to the function's CREATE command. The error is not in the function, it is the WHE

Re: How do you optimize the disk IO when you cannot assume a file will start at a boundary then?

On Thu, Feb 22, 2024 at 2:23 PM Siddharth Jain wrote: > I understand the storage layer in databases goes to great lengths to ensure: > - a row does not cross a block boundary > - read/writes/allocation happen in units of blocks > etc. The motivation is that at the OS level, it reads and writes pag

Performance issue debugging

Hi All, As i understand we have pg_stats_activity which shows the real time activity of sessions currently running in the database. And the pg_stats_statement provides the aggregated information of the historical execution of all the queries in the database. But I don't see any sampling or timing i

Creating table and indexes for new application

Hello Friends, We are newly creating tables for a system which is going to consume transaction data from customers and store in postgres version 15+ database. And it would be ~400million transactions/rows per day in the main transaction table and almost double in the multiple child tables and some

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

> > I have a table that is capturing what is, basically, time series data Time series data usually is concerned with "recent" data, and has a subsequent drop off date. This is ideal for partitioning by timestamp - not only do your queries only need to hit a few of the total tables, but you can si

Re: [SPAM] Re: Partial table duplication via triggger

On 22/02/24 17:49, Erik Wienhold wrote: On 2024-02-22 15:14 +0100, Moreno Andreo wrote: suppose I have 2 tables [snip] What am I missing? The parameters you pass in with USING have to be referenced as $1, $2, and so on. For example: DECLARE fieldlist text := (

Re: pg_dump performance issues

On 2/22/24 06:40, Dominique Devienne wrote: On Thu, Feb 22, 2024 at 12:20 PM Dominique Devienne > wrote: On Thu, Feb 22, 2024 at 11:41 AM Francisco Olarte mailto:fola...@peoplecall.com>> wrote: Dominique: > in the 10-12MB/s throughput ra

Re: How do you optimize the disk IO when you cannot assume a file will start at a boundary then?

Thanks Tomas On Thu, Feb 22, 2024 at 3:05 AM Tomas Vondra wrote: > On 2/22/24 02:22, Siddharth Jain wrote: > > Hi All, > > > > I understand the storage layer in databases goes to great lengths to > ensure: > > - a row does not cross a block boundary > > - read/writes/allocation happen in units o

Re: Partial table duplication via triggger

On 2024-02-22 15:14 +0100, Moreno Andreo wrote: > suppose I have 2 tables > > CREATE TABLE t1( >     id uuid, >     name text, >     surname text, >     ... >     PRIMARY KEY(id) > ) > > CREATE TABLE t2( >     id uuid, >     master_id uuid, >     op_ts timestamp with time zone, >     name text, >

Re: Postgresql assistance needed

On Thu, 2024-02-22 at 19:35 +0530, Sasmit Utkarsh wrote: >  Is there any configuration/query that can be checked to verify if  > "shc_uadmin" has the correct path set? The SQL statement "SHOW search_path" would return the current setting. But look at the error message first. Yours, Laurenz Albe

Re: Postgresql assistance needed

On the surface, it looks as though it *should* work if the only thing changing is the username. Those other more serious errors should get fixed, but putting those aside for now... We don't know what your program does. Write a smaller one that just does a PQexec and calls nextval, then returns a pr

Re: pg_dump performance issues

On Thu, Feb 22, 2024 at 12:20 PM Dominique Devienne wrote: > On Thu, Feb 22, 2024 at 11:41 AM Francisco Olarte > wrote: > >> Dominique: >> > in the 10-12MB/s throughput range. >> >> This has the faint smell of a saturated 100Mbps link in the middle >> (12*8=96Mbps) >> > > So indeed, I'm

Partial table duplication via triggger

Hi *,     suppose I have 2 tables CREATE TABLE t1(     id uuid,     name text,     surname text,     ...     PRIMARY KEY(id) ) CREATE TABLE t2(     id uuid,     master_id uuid,     op_ts timestamp with time zone,     name text,     surname text,     ...     PRIMARY KEY(id) ) I need to write an

Re: Postgresql assistance needed

Thanks Laurenz ->To debug that, get the actual error message using PQerrorMessage(). That should tell you what is going on. -- will try to add the PQerrorMessage() in the logs ->Perhaps the sequence is not on your "search_path", and you should qualify the name with the schema. -- I have given t

Re: Postgresql assistance needed

On Thu, 2024-02-22 at 17:18 +0530, Sasmit Utkarsh wrote: > Perhaps, the issue I wanted to highlight here is that I get the same entry > working > when I switch user to "pgddb_admin" and not when change user with same > privileges > as PGUSER "shc_uadmin" I get the message in the error log like >

Re: Postgresql assistance needed

Hi Laurenz/Postgresql Team, Perhaps, the issue I wanted to highlight here is that I get the same entry working when I switch user to *"pgddb_admin" *and not when change user with same privileges as PGUSER "*shc_uadmin" *I get the message in the error log like* "<3>3343433-[ERROR] SELECT nextval F

Re: pg_dump performance issues

On Thu, Feb 22, 2024 at 11:41 AM Francisco Olarte wrote: > Dominique: > > in the 10-12MB/s throughput range. > > This has the faint smell of a saturated 100Mbps link in the middle > (12*8=96Mbps) > Very good call Francisco! Thanks a lot. Below is connecting to the v14 PostgreSQL host,

Re: How do you optimize the disk IO when you cannot assume a file will start at a boundary then?

On 2/22/24 02:22, Siddharth Jain wrote: > Hi All, > > I understand the storage layer in databases goes to great lengths to ensure: > - a row does not cross a block boundary > - read/writes/allocation happen in units of blocks > etc. The motivation is that at the OS level, it reads and writes pages

Re: Postgresql assistance needed

On Thu, 2024-02-22 at 16:13 +0530, Sasmit Utkarsh wrote: > On Thu, Feb 22, 2024 at 2:20 PM Laurenz Albe wrote: > > On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote: > > > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address > > > 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp

Re: Postgresql assistance needed

Hi Laurenz, Sorry but are you talking about the export variables in the result? Regards, Sasmit Utkarsh +91-7674022625 On Thu, Feb 22, 2024 at 2:20 PM Laurenz Albe wrote: > On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote: > > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on

Re: pg_dump performance issues

Dominique: On Thu, 22 Feb 2024 at 10:50, Dominique Devienne wrote: > In the past, I've read [this post][1] from Marc Millas that reports `pg_dump > ... | psql` at throughput around 500MB/s (5Gb/s) on a 10Gb/s network. That is half the theoretical bandwidth,more or less. > I tried against a v12

pg_dump performance issues

In the past, I've read [this post][1] from Marc Millas that reports `pg_dump ... | psql` at throughput around 500MB/s (5Gb/s) on a 10Gb/s network. Today, I've tried pg_dump on a single schema of 25 tables, 88K rows, 70MB total (as reported by pg total relation sizes). 1 table of 225 rows contains

Re: Postgresql assistance needed

On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote: > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address > 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp 0xff8a70b0 > READ of size 4096 at 0xf337ba80 thread T0 >     #0 0xf795fcdc in __interceptor_memcpy > (/nix/store/3blqv6wzmw

Postgresql assistance needed

Hi Postgresql Team, I'm facing a weird issue which testing the application code using libpq in C. when i run a test case with PGUSER as *"pgddb_admin" * i get the expected result (more details attached in success_log doc) whereas when i run the same test case using another user "*shc_uadmin" *whic