Re: Functionally dependent columns in SELECT DISTINCT

Willow Chargin schrieb am 13.09.2024 um 07:20: > Hello! Postgres lets us omit columns from a GROUP BY clause if they are > functionally dependent on a grouped key, which is a nice quality-of-life > feature. I'm wondering if a similar relaxation could be permitted for > the SELECT DISTINCT list? > >

Functionally dependent columns in SELECT DISTINCT

Hello! Postgres lets us omit columns from a GROUP BY clause if they are functionally dependent on a grouped key, which is a nice quality-of-life feature. I'm wondering if a similar relaxation could be permitted for the SELECT DISTINCT list? I have a query where I want to find the most recent few i

Re: Better way to process records in bash?

You maybe even could use plpythonu.

Re: DDL issue

On 9/12/24 16:01, Rich Shepard wrote: I have one name in the people table who owns 5 different dairies with three different phone numbers, but all 5 have the the same email address. The five dairies each has its own name and location while the people table has five rows with the same last and fi

DDL issue

I have one name in the people table who owns 5 different dairies with three different phone numbers, but all 5 have the the same email address. The five dairies each has its own name and location while the people table has five rows with the same last and first names and email address. Is there

Re: Backward compat issue with v16 around ROLEs

On 13.09.2024 00:11, Robert Haas wrote: The prohibition against circular grants is really annoying in your use case. If dd_owner creates dd_user, then dd_user is granted to dd_owner, which means that dd_owner cannot be granted (directly or indirectly) to dd_user. In search of workaround... So

Re: Error:could not extend file " with FileFallocate(): No space left on device

On Thu, Sep 12, 2024 at 8:54 PM Pecsök Ján wrote: > In link you provided there is mention, that in PostgreSQL 16 data is not being > compressed for PostgreSQL 16 server. Does it mean, that PosgreSQL 16 use much > more space while computing queries? > If that is the case, it can be our problem, be

Re: Backward compat issue with v16 around ROLEs

On Thursday, September 12, 2024, Robert Haas wrote: > On Thu, Sep 12, 2024 at 3:40 PM Dominique Devienne > wrote: > > > > Any existing ROLE graph which had "back-edges" (GRANTs) from a ROLE > > back to the ROLE that created it, valid in pre-v16, becomes invalid in > v16+. > > And there's no work

Re: Backward compat issue with v16 around ROLEs

On Thu, Sep 12, 2024 at 3:40 PM Dominique Devienne wrote: > Another way to look at it is this: > > === v14 === > ddevienne=> create role dd_child; > CREATE ROLE > ddevienne=> select pg_has_role(current_role, 'dd_child', 'MEMBER'); > pg_has_role > - > f > (1 row) > > === v16 === > dde

pglogical selective child replication between different partition interval tables

Hi Respected Team Here source side tables are created with 7 days partition interval here we have data within them changelog_event_p20240830 changelog_event_p20240906 Target side tables are created with 3 days partition interval Structure of tables at both side is same Would it be possible to r

Recommendations on Improving the debezium performance even on medium workload

Hi Team How do we improve the debezium performance? Recommendations at kafka configuration side Agenda is to minimize the lag during moderate or high work load on db default > poll.interval.ms = 500ms Recommended value for balanced performance > 1000ms Recommended value for high throughput > 1000

Re: Better way to process records in bash?

On Thu, Sep 12, 2024 at 6:08 PM Ron Johnson wrote: > (This might be a bash question instead of a PG question, or it might be an > A/B question.) > > I need to process table records in a bash script. Currently, I read them > using a while loop and redirection. The table isn't that big (30ish > t

Re: Better way to process records in bash?

On Thu, Sep 12, 2024 at 3:30 PM Christoph Moench-Tegeder wrote: > ## Ron Johnson (ronljohnso...@gmail.com): > > > I need to process table records in a bash script. Currently, I read them > > using a while loop and redirection. The table isn't that big (30ish > > thousand rows), and performance

Re: Better way to process records in bash?

## Ron Johnson (ronljohnso...@gmail.com): > I need to process table records in a bash script. Currently, I read them > using a while loop and redirection. The table isn't that big (30ish > thousand rows), and performance is adequate, but am always looking for > "better". Use python, or any othe

Re: Logical replication without direct link between publisher and subscriber?

I've considered it, but it sounds like a lot of work and failure prone. Even projects like Debezium seem like it's a ton to set up. Thanks for the suggestions. Regards, Koen On Wed, Sep 11, 2024 at 3:20 PM Greg Sabino Mullane wrote: > Dumping changes periodically, sending them directly or uplo

Re: post-bootstrap init : permission denied pg_description

=?iso-8859-1?Q?Fran=E7ois?= SIMON writes: > So the problem seems to come from xlc, and only at initdb step. > I can see that initdb runs a backend postgres in single user mode. > And this is this backend, when compiled with xlc, that shows : > FATAL: permission denied for table pg_description Ye

Re: post-bootstrap init : permission denied pg_description

Le Thu, Sep 12, 2024 at 09:52:57AM -0700, Adrian Klaver a écrit : > On 9/12/24 09:03, François SIMON wrote: > > $HOME is on a NFS volume. > > Have you read this?: > > https://www.postgresql.org/docs/16/creating-cluster.html#CREATING-CLUSTER-FILESYSTEM Yes. I understand NFS is OK, but yes I see s

Re: post-bootstrap init : permission denied pg_description

On 9/12/24 10:06, François SIMON wrote: Le Thu, Sep 12, 2024 at 06:03:47PM +0200, François SIMON a écrit : Le Thu, Sep 12, 2024 at 08:20:59AM -0700, Adrian Klaver a écrit : On 9/12/24 03:58, François SIMON wrote: Hello All, So the problem seems to come from xlc, and only at initdb step. I c

Re: post-bootstrap init : permission denied pg_description

Le Thu, Sep 12, 2024 at 06:03:47PM +0200, François SIMON a écrit : > Le Thu, Sep 12, 2024 at 08:20:59AM -0700, Adrian Klaver a écrit : > > On 9/12/24 03:58, François SIMON wrote: > > > Hello All, > > > > > > I am trying to install PostgreSQL 16.4 on AIX 7.1. > > > > > > I get an error at initdb s

Re: Recommendations on improving the insert on conflict do nothing performance

Hi Muhammad Usman Khan I have already set required values of params.Here issue was about triggers.I have resolved this issue Regards Durga Mahesh On Thu, Sep 12, 2024 at 10:05 AM Muhammad Usman Khan wrote: > Hi, > You can use the following approaches for optimization: > >- Instead of inser

Re: post-bootstrap init : permission denied pg_description

On 9/12/24 09:03, François SIMON wrote: Le Thu, Sep 12, 2024 at 08:20:59AM -0700, Adrian Klaver a écrit : On 9/12/24 03:58, François SIMON wrote: Hello All, A normal user account. Is that the account you did the make install as? PGDATA is set to a subdirectory of $HOME for this user. $H

Re: Better way to process records in bash?

On Thu, Sep 12, 2024 at 11:43 AM Thiemo Kellner wrote: > Hi > > What is this "something" that it cannot be calculated within the dB? > It's an external program that can't read a csv input file. -- Death to America, and butter sauce. Iraq lobster!

Re: post-bootstrap init : permission denied pg_description

Le Thu, Sep 12, 2024 at 08:20:59AM -0700, Adrian Klaver a écrit : > On 9/12/24 03:58, François SIMON wrote: > > Hello All, > > > > I am trying to install PostgreSQL 16.4 on AIX 7.1. > > > > I get an error at initdb step : > > > > performing post-bootstrap initialization ... 2024-09-12 12:09:07.0

Better way to process records in bash?

Hi What is this "something" that it cannot be calculated within the dB?

Re: Connection between PostgreSQL and SAP HANA database

On 9/9/24 08:35, Thürmann, Andreas wrote: Adrian Klaver (adrian.kla...@aklaver.com ) asked: Postgres version? PostgreSQL 16 with pgAdmin 16 4.2.2 Have you tried tried this using psql? Have you looked at Postgres log for errors?     No erro

Re: post-bootstrap init : permission denied pg_description

On 9/12/24 03:58, François SIMON wrote: Hello All, I am trying to install PostgreSQL 16.4 on AIX 7.1. I get an error at initdb step : performing post-bootstrap initialization ... 2024-09-12 12:09:07.075 CEST [14745748] FATAL: permission denied for table pg_description That looks like an is

Better way to process records in bash?

(This might be a bash question instead of a PG question, or it might be an A/B question.) I need to process table records in a bash script. Currently, I read them using a while loop and redirection. The table isn't that big (30ish thousand rows), and performance is adequate, but am always lookin

Re: Performance degrade on insert on conflict do nothing

Hi Greg Great response from you this worked Regards Durga Mahesh On Wed, Sep 11, 2024 at 7:12 PM Greg Sabino Mullane wrote: > On Wed, Sep 11, 2024 at 1:02 AM Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > >> Hi >> createdat | timestamp with time zone | | not null | n

Re: RLS and Table Inheritance

Sanjay Minni writes: > Do RLS policies defined at the parent, work on the child (in Table > inheritance). > At the parent I have a column 'site_id' with an RLS policy that only rows > with site_id = current_setting(curr_site_id) would be accessible. > However the policy defined at the parent does

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

På torsdag 12. september 2024 kl. 16:10:26, skrev Christophe Pettus < x...@thebuild.com >: > On Sep 12, 2024, at 06:58, Greg Sabino Mullane wrote: > > But if it works for you, go ahead. As Tom said, it will work 95% of the time. But it will break things that should

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

> On Sep 12, 2024, at 06:58, Greg Sabino Mullane wrote: > > But if it works for you, go ahead. As Tom said, it will work 95% of the time. > But it will break things that should work, and it will not prevent the > ability to get the information in other ways. To be clear, we never recommend

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

On Thu, Sep 12, 2024 at 3:53 PM Greg Sabino Mullane wrote: > On Thu, Sep 12, 2024 at 9:12 AM Dominique Devienne > wrote: >> On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane >> wrote: >> > (Also note that determining if a database or user exists does not even >> > require a successful login

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

On Thu, Sep 12, 2024 at 9:21 AM Andreas Joseph Krogh wrote: > Yes, it *is* theater, but that doesn't prevent “compliance people” to > care about it. We have to take measures to prevent “information leaks”. > *shrug* Then the compliance people are not good at their jobs, frankly. But if it works

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

On Thu, Sep 12, 2024 at 9:12 AM Dominique Devienne wrote: > On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane > wrote: > > (Also note that determining if a database or user exists does not even > require a successful login to the cluster.) > > Hi. How so? I was not aware of such possibilities.

Re: Backward compat issue with v16 around ROLEs

On Thu, Sep 12, 2024 at 2:40 PM Dominique Devienne wrote: > Basically the above explain why we have that > dd_user (INHERIT) > `-> member-of dd_admin (NOINHERIT) > `-> member-of dd_owner (INHERIT). > > In pre-v16, once again, this was fine. > Because v16+ adds that dd_owner member-of dd_user

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

På torsdag 12. september 2024 kl. 15:05:48, skrev Greg Sabino Mullane < htamf...@gmail.com >: On Thu, Sep 12, 2024 at 12:52 AM Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: I know PG is not designed for this, but I have this requirement nonetheless… I think

RLS and Table Inheritance

Hi, Do RLS policies defined at the parent, work on the child (in Table inheritance). At the parent I have a column 'site_id' with an RLS policy that only rows with site_id = current_setting(curr_site_id) would be accessible. However the policy defined at the parent does not work for me in inherite

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane wrote: > (Also note that determining if a database or user exists does not even > require a successful login to the cluster.) Hi. How so? I was not aware of such possibilities. Can you please give pointers (docs, examples) of this? Thanks, --D

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

On Thu, Sep 12, 2024 at 12:52 AM Andreas Joseph Krogh wrote: > I know PG is not designed for this, but I have this requirement > nonetheless… > I think preventing “most users and tools" from seeing/presenting this > information is “good enough”. > As pointed out, there are very many workarounds.

Re: Removing duplicate rows in table

On Thu, 12 Sep 2024, Muhammad Usman Khan wrote: You can try the following CTE which removes all the identical rows and only leave single row Thank you, Muhammed. Rich

Re: Backward compat issue with v16 around ROLEs

On Wed, Sep 11, 2024 at 10:20 PM Pavel Luzanov wrote: > On 11.09.2024 22:21, David G. Johnston wrote: >> I think this needs to be the other way around: > [...]. In any case fixing the with admin error is the correct approach. > > Unfortunately, it won't work. > Dominique is right. This will lead

Re: Backward compat issue with v16 around ROLEs

On Wed, Sep 11, 2024 at 11:39 PM Tom Lane wrote: > Dominique Devienne writes: > > Hi. I'm going around in circles trying to solve an issue with our > > system when running against a PostgreSQL v16 server. Which is linked > > to the weakening of CREATEROLE to have more granular permissions. > > I'

post-bootstrap init : permission denied pg_description

Hello All, I am trying to install PostgreSQL 16.4 on AIX 7.1. I get an error at initdb step : performing post-bootstrap initialization ... 2024-09-12 12:09:07.075 CEST [14745748] FATAL: permission denied for table pg_description This version of PostgreSQL was build on this machine with IBM XL

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

Could you use FDW's in another completely separate db for them to access so they have no direct access to the source data (or database), only the linked tables which have no local data, other users, etc, present at all? Which is sort of what was suggested: "Put some kind of restrictive app in f

RE: Error:could not extend file " with FileFallocate(): No space left on device

In link you provided there is mention, that in PostgreSQL 16 data is not being compressed for PostgreSQL 16 server. Does it mean, that PosgreSQL 16 use much more space while computing queries? If that is the case, it can be our problem, because our queries use sometimes several TB of disk space f