Re: Alignment check

On 27.06.24 18:07, Marthin Laubscher wrote: I don’t intend dissing or plugging anyone’s efforts or start a flame war, but I’d like to get a sense of how the PostgreSQL community feels about: a) YugabyteDB, and b) PostgreSQL on Kubernetes. For my application I’m deeply vested in Kubernetes as

Re: Can a long running procedure detect when smart shutdown is pending?

so 6. 7. 2024 v 8:19 odesílatel Pavel Stehule napsal: > > > so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios < > a.mantz...@cloud.gatewaynet.com> napsal: > >> Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε: >> >> >> >> so 6. 7. 2024 v 1:36 odesílatel Dennis White >> napsal: >> >>> Thanks for resp

Re: Can a long running procedure detect when smart shutdown is pending?

so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios < a.mantz...@cloud.gatewaynet.com> napsal: > Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε: > > > > so 6. 7. 2024 v 1:36 odesílatel Dennis White > napsal: > >> Thanks for responding. >> This will be a stored procedure written in plpgsql that's kick

Re: Can a long running procedure detect when smart shutdown is pending?

Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε: so 6. 7. 2024 v 1:36 odesílatel Dennis White napsal: Thanks for responding. This will be a stored procedure written in plpgsql that's kicked off by pg_cron. I wasn't sure a normal smart shutdown would stop it. shutdown try to ca

Re: Can a long running procedure detect when smart shutdown is pending?

On Fri, 2024-07-05 at 14:12 -0400, Dennis White wrote: > My project's DB has a mutli-step stored procedure using Transaction Control > that may take 30 minutes or more to complete. > I am curious if there is a way to make it more smart shutdown friendly so it > can stop between steps? I don't th

Re: Can a long running procedure detect when smart shutdown is pending?

so 6. 7. 2024 v 1:36 odesílatel Dennis White napsal: > Thanks for responding. > This will be a stored procedure written in plpgsql that's kicked off by > pg_cron. > I wasn't sure a normal smart shutdown would stop it. > shutdown try to cancel any query. The plpgsql routine should be canceled wit

Re: confused about material view locks please explain

On 7/5/24 21:10, Peter J. Holzer wrote: If I understand https://github.com/sraoss/pg_ivm correctly, the materialized view will be updated within the same transaction. So it's just the same as any other change in the database: Neither client will wait for the other. The first client will see ei

Re: Can a long running procedure detect when smart shutdown is pending?

Thanks for responding. This will be a stored procedure written in plpgsql that's kicked off by pg_cron. I wasn't sure a normal smart shutdown would stop it. Thanks On Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios < a.mantz...@cloud.gatewaynet.com> wrote: > Στις 5/7/24 21:12, ο/η Dennis White έγ

Re: Planning of sub partitions

On Sat, 6 Jul 2024 at 03:24, Matt Hughes wrote: > -- 4. uses all partitions; should exclude event_closed_y2024_m02 > explain select * from event > where > cleared is false OR > (cleared is true and date_raised > '2024-01-01' AND date_raised < > '2024-01-02'); >

Re: Can a long running procedure detect when smart shutdown is pending?

Στις 5/7/24 21:12, ο/η Dennis White έγραψε: My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete. I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps? We are using both PG 14 and

Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC

"Tefft, Michael J" writes: > I apologize, that was sloppy. > I was using the acldefault() function with pg_roles, like this: > => select rolname, acldefault('f',oid) from pg_roles where rolname like > 'mjt%' order by 1; Ah, yeah, that always shows the *built in* default privileges for a given o

RE: Removing the default grant of EXECUTE on functions/procedures to PUBLIC

I apologize, that was sloppy. I was using the acldefault() function with pg_roles, like this: => select rolname, acldefault('f',oid) from pg_roles where rolname like 'mjt%' order by 1; rolname | acldefault ---+-- mjt_test1 | {=X/mjt_test

Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC

"Tefft, Michael J" writes: > I was checking pg_roles.acl_default to see if my role-level ALTER DEFAULT > PRIVILEGES had been effective. But I see the same content both before and > after the ALTEr. Er, what? There's no column named acl_default in pg_roles, nor any other standard PG view. psql

Can a long running procedure detect when smart shutdown is pending?

My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete. I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps? We are using both PG 14 and PG 16 on Rhel 8. Pardon me if it's obvious but

RE: Removing the default grant of EXECUTE on functions/procedures to PUBLIC

I was checking pg_roles.acl_default to see if my role-level ALTER DEFAULT PRIVILEGES had been effective. But I see the same content both before and after the ALTEr. You mention that this needs to be done in each database. Is there a database-level version of pg_roles.acl_default that I should b

Re: apt-archive.postgresql.org a bit broken?

On 05.07.24 18:27, Magnus Hagander wrote: On Fri, Jul 5, 2024 at 6:07 PM Tomas Pospisek > wrote: Hello, I get this when I try to access the archive of focal packages: ``` # apt-get update [...] Err:6 https://apt-archive.postgresql.org/pub/r

Re: apt-archive.postgresql.org a bit broken?

On Fri, Jul 5, 2024 at 6:07 PM Tomas Pospisek wrote: > Hello, > > I get this when I try to access the archive of focal packages: > > ``` > # apt-get update > [...] > Err:6 https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg > InRelease >403 Forbidden [IP: 2600:9000:2761:4600:b:5965:3

apt-archive.postgresql.org a bit broken?

Hello, I get this when I try to access the archive of focal packages: ``` # apt-get update [...] Err:6 https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg InRelease 403 Forbidden [IP: 2600:9000:2761:4600:b:5965:3380:93a1 443] [...] E: Failed to fetch https://apt-archive.postgresql.

Re: Planning of sub partitions

One workaround seems to be using a CTE with a union: with openAndRecentlyRaisedEvents as ( select * from event where cleared is false union all select * from event where cleared is true and date_raised > '2024-01-01' AND date_raised < '2024-01-02'; ) select * from openAndREcentlyRaisedEven

Re: confused about material view locks please explain

On 2024-07-05 18:58:21 +0530, Krishnakant Mane wrote: > I have decided to use materialised views (more precisely IVM ). [...] > Given this setup I have a very specific questionh. > > if client 1 has asked for his balance sheet and the view is being queried, > then what will happen if client 2 happ

Planning of sub partitions

I am trying to model a stateful `event` table using partitions. An event has date_raised (not null) and date_cleared (nullable) columns. An event is in the "open" state when it has a null date_cleared; it is in the "closed" state when date_cleared is set. Once date_cleared is set, it won't chang

Re: Load a csv or a avro?

On 7/5/24 02:08, sud wrote: Hello all, Its postgres database. We have option of getting files in csv and/or in avro format messages from another system to load it into our postgres database. The volume will be 300million messages per day across many files in batches. Are dumping the entire

Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC

"Tefft, Michael J" writes: > I am trying to remove the default grant of EXECUTE on all > functions/procedures to PUBLIC. >> From my reading, there is no straightforward way to do this. For example, > ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; > Does not apply this across th

Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC

On Friday, July 5, 2024, Tefft, Michael J wrote: > I am trying to remove the default grant of EXECUTE on all > functions/procedures to PUBLIC. > > From my reading, there is no straightforward way to do this. For example, > > ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; > > Do

Removing the default grant of EXECUTE on functions/procedures to PUBLIC

I am trying to remove the default grant of EXECUTE on all functions/procedures to PUBLIC. >From my reading, there is no straightforward way to do this. For example, ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; Does not apply this across the entire cluster (or database) but onl

confused about material view locks please explain

Hello all. I am an old timer postgresql user for last 17 years. I have used it for my open source as well as enterprise cloud services. IN my current fintech solution, I need to do some performance optimisations. I have decided to use materialised views (more precisely IVM ). So on my postgre

Re: Load a csv or a avro?

On Fri, Jul 5, 2024 at 5:08 AM sud wrote: > Hello all, > > Its postgres database. We have option of getting files in csv and/or in > avro format messages from another system to load it into our postgres > database. The volume will be 300million messages per day across many files > in batches. > >

RE: psql help

-Original Message- From: dep...@depesz.com Sent: Friday, July 5, 2024 6:05 AM To: Murthy Nunna Cc: pgsql-general@lists.postgresql.org Subject: Re: psql help [EXTERNAL] – This message is from an external sender On Fri, Jul 05, 2024 at 03:54:56AM +, Murthy Nunna wrote: > Sorry, t

Re: psql help

On Fri, Jul 05, 2024 at 03:54:56AM +, Murthy Nunna wrote: > Sorry, there is no problem with the following statement and the environment > variable. It works fine. But it terminates only one PID due to LIMIT 1. I > want to terminate all pids that meet this criteria. If I remove LIMIT 1, > pg_

Re: Load a csv or a avro?

Hi, Performance Considerations Avro files are smaller due to compression so needing less I/O time. whereas CSV files are simpler but larger in size so read/write will need more time. COPY command works very well with CSV files whereas ETL process is required for handling Avro. Regards, Mu

Re: Load a csv or a avro?

pá 5. 7. 2024 v 11:08 odesílatel sud napsal: > > Hello all, > > Its postgres database. We have option of getting files in csv and/or in avro > format messages from another system to load it into our postgres database. > The volume will be 300million messages per day across many files in batches.

Re: Load a csv or a avro?

Hi There are different data formats available, following are few points for there performance implications 1. CSV : It's easy to use and widely supported but it can be slower due to parsing overload. 2. Binary : Its faster to load but not human understandable. Hope this helps. Regards Kashif Ze

Load a csv or a avro?

Hello all, Its postgres database. We have option of getting files in csv and/or in avro format messages from another system to load it into our postgres database. The volume will be 300million messages per day across many files in batches. My question was, which format should we chose in regards

Re: Design strategy for table with many attributes

On Fri, 5 Jul 2024 at 20:53, Lok P wrote: > However out of curiosity, if the roasted/compressed component or column which > is JSON itself goes beyond 8k post compression, will it break then? No. The size limit of a varlena field such as TEXT, JSON or JSONB is 1GB. See "field size" in [1]. Ple

Re: Design strategy for table with many attributes

On Fri, 5 Jul, 2024, 1:44 pm David Rowley, wrote: > On Fri, 5 Jul 2024 at 19:53, Lok P wrote: > > As David suggested it breaks if a row exceeds the 8k limit I. E a single > page size , will that still holds true if we have a column with JSON in it? > > You wouldn't be at risk of the same tuple l

Re: Design strategy for table with many attributes

On Fri, 5 Jul 2024 at 19:53, Lok P wrote: > As David suggested it breaks if a row exceeds the 8k limit I. E a single page > size , will that still holds true if we have a column with JSON in it? You wouldn't be at risk of the same tuple length problem if you reduced the column count and stored t

Re: Design strategy for table with many attributes

Some folks in the team suggested to have key business attributes or say frequently queried attributes in individual columns and others in a column in same table clubbed in JSON format. Is that advisable or any issues can occur with this approach? Also not sure how effectively postgres processes JSO