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

Thanks for info. 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”. Maybe not revoking access to all the tables in the schemas, but the “most obvious ones”, like pg_use

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

Andreas Joseph Krogh writes: > Motivation: I have PowerBI users, with a separate ‘reporting’-role, accessing > a database and I want to prevent them from listing all tables, users, > databases > and view-definitions (to not see the underlying query). Postgres is not designed to support this re

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

Hi, You can use the following approaches for optimization: - Instead of inserting one row at a time, perform bulk inserts, which will reduce the overhead of each individual transaction - Partitioning can improve write performance by splitting the data into smaller, more manageable chun

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

Hei all. Motivation: I have PowerBI users, with a separate ‘reporting’-role, accessing a database and I want to prevent them from listing all tables, users, databases and view-definitions (to not see the underlying query). I'm evaluating this: REVOKE SELECT ON ALL TABLES IN SCHEMA pg_cata

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

I don't understand what ENOSPC has to do with the file descriptor limits, but this person reported: # touch test touch: cannot touch ‘test’: No space left on device https://serverfault.com/questions/746032/rsync-and-scp-failing-with-no-space-left-on-xfs-device ... with plenty of free space, and

Re: Backward compat issue with v16 around ROLEs

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'm not entirely sure, but I think the relevant v16 change is t

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

On Thu, Sep 12, 2024 at 12:39 AM Alvaro Herrera wrote: >> On 2024-Sep-11, Pecsök Ján wrote: > > In our case: > > Kernel: Linux version 4.18.0-513.18.1.el8_9.ppc64le > > (mockbu...@ppc-hv-13.build.eng.rdu2.redhat.com) (gcc version 8.5.0 20210514 > > (Red Hat 8.5.0-20) (GCC)) #1 SMP Thu Feb 1 02:5

Re: Backward compat issue with v16 around ROLEs

On 11.09.2024 22:21, David G. Johnston wrote: > ddevienne=> grant dd_owner to dd_admin with admin option; -- I think this needs to be the other way around:    grant dd_admin to dd_owner with admin option; Best, Wolfgang Probably, intend to get those reverse

Re: Backward compat issue with v16 around ROLEs

On Wed, Sep 11, 2024, 12:17 Wolfgang Walther wrote: > Dominique Devienne: > > Hi David. I did as you suggested, and it fails the same way. Did I > > misunderstand you? --DD > > > > [..] > > > > ddevienne=> grant dd_owner to dd_admin with admin option; -- > > I think this needs to be the

Re: Backward compat issue with v16 around ROLEs

Dominique Devienne: Hi David. I did as you suggested, and it fails the same way. Did I misunderstand you? --DD [..] ddevienne=> grant dd_owner to dd_admin with admin option; -- I think this needs to be the other way around: grant dd_admin to dd_owner with admin option; Best, Wol

Re: Backward compat issue with v16 around ROLEs

On Wed, Sep 11, 2024 at 5:09 PM Adrian Klaver wrote: > What user did you do the above as? My own user, which lacks SUPERUSER (I have CREATEROLE and CREATEDB only, and LOGIN of course). > On my Postgres 16.4 instance logged in as postgres: > test=# create role dd_owner createrole; > CREATE ROLE

Re: Backward compat issue with v16 around ROLEs

On Wed, Sep 11, 2024 at 5:06 PM David G. Johnston wrote: > As the error indicates, this grant needs to be done with admin option. Hi David. I did as you suggested, and it fails the same way. Did I misunderstand you? --DD D:\pdgm\trunk\psc2>psql service=pau16 psql (17beta3, server 16.1) Type "hel

Re: Backward compat issue with v16 around ROLEs

On 9/11/24 07:41, Dominique Devienne wrote: Hi. I'm going around in circles trying to solve an issue with our on v16: D:\pdgm\trunk\psc2>psql service=pau16 psql (17beta3, server 16.1) Type "help" for help. ddevienne=> create role dd_owner createrole; CREATE ROLE ddevienne=> create role dd_ad

Re: Backward compat issue with v16 around ROLEs

On Wednesday, September 11, 2024, Dominique Devienne wrote: > > on v16: > > D:\pdgm\trunk\psc2>psql service=pau16 > psql (17beta3, server 16.1) > Type "help" for help. > > ddevienne=> create role dd_owner createrole; > CREATE ROLE > ddevienne=> create role dd_admin noinherit; > CREATE ROLE > ddev

Backward compat issue with v16 around ROLEs

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've distilled it down to a simple workflow, as shown below on both v14 (OK) and v16 (KO). In our

Re: Performance degrade on insert on conflict do nothing

On Wed, Sep 11, 2024 at 1:02 AM Durgamahesh Manne wrote: > Hi > createdat | timestamp with time zone | | not null | now() >| plain| | | > modified | timestamp with time zone | | not null | now() >| plain| |

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

Output of xfs_info: []# xfs_info /data/aisgamp1/pgdata_system meta-data=/dev/mapper/dataamp1vg-lv_aisgamp1_pgsys isize=512agcount=118, agsize=134217720 blks = sectsz=512 attr=2, projid32bit=1 = crc=1finobt=1, sparse=1, r

Re: Database schema for "custom fields"

I'd go with option 2 (jsonb), as it's likely already well supported by your applications, while the other approaches will require a good bit of customization. JSONB can be indexed, so performance should be on par with "traditional" tables. Cheers, Greg

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

> > Dumping changes periodically, sending them directly or uploading to cloud > storage and then downloading and applying them on the subscriber side. > But maybe there's a simpler option someone here knows about? How about using WAL shipping to populate a replica, and either query that directly

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

In our case: Kernel: Linux version 4.18.0-513.18.1.el8_9.ppc64le (mockbu...@ppc-hv-13.build.eng.rdu2.redhat.com) (gcc version 8.5.0 20210514 (Red Hat 8.5.0-20) (GCC)) #1 SMP Thu Feb 1 02:52:53 EST 2024 File systém type:xfs -Original Message- From: Thomas Munro Sent: Wednesday, Septem

Re: Ghost data from failed FDW transactions?

Any updates on this? A few replies from me inline: On Wed, Aug 28, 2024 at 12:18 PM Jacob Biesinger wrote: > There aren't many details in the docs around failure modes... is there > anything there that could cause this issue? > Nothing that I know of, but it's possible there is some sort of we

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

On 2024-Sep-11, Pecsök Ján wrote: > In our case: > Kernel: Linux version 4.18.0-513.18.1.el8_9.ppc64le > (mockbu...@ppc-hv-13.build.eng.rdu2.redhat.com) (gcc version 8.5.0 20210514 > (Red Hat 8.5.0-20) (GCC)) #1 SMP Thu Feb 1 02:52:53 EST 2024 > File systém type:xfs Can you please share the out

Re: infinite loop in an update statement

On Wed, Sep 11, 2024 at 6:14 AM Fabrice Chapuis wrote: > status = active >> wait event = NULL >> wait event type = NULL >> > That seems quite unlikely. Perhaps you are seeing the pg_stat_activity query itself? Try this: select state, now()-state_change, wait_event_type, wait_event, query from pg

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

On Wed, Sep 11, 2024 at 9:56 PM Alvaro Herrera wrote: > On 2024-Sep-10, Pecsök Ján wrote: > > After upgrade of Posgres from version 13.5 to 16.2 we experience following > > error: > > could not extend file "pg_tblspc/16401/PG_16_202307071/17820/3968302971" > > with FileFallocate(): No space left

Re: infinite loop in an update statement

On Tue, Sep 10, 2024 at 4:49 PM Fabrice Chapuis wrote: > no lock, in view pg_stat_activity > > status = active > wait event = NULL > wait event type = NULL > > On Mon, Sep 9, 2024 at 5:00 PM Tom Lane wrote: > >> Fabrice Chapuis writes: >> > why this update does not return instantly? >> >> > UPD

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

Hello Ján, On 2024-Sep-10, Pecsök Ján wrote: > After upgrade of Posgres from version 13.5 to 16.2 we experience following > error: > could not extend file "pg_tblspc/16401/PG_16_202307071/17820/3968302971" with > FileFallocate(): No space left on device > > We cannot easily replicate problem.

Re: Overlapping values (?) in multi-column partitioned tables

I'm not sure of the less than vs less than or equals in this one, so it may be my ignorance... but which partition gets records with a date of '24-02-01", it seems that without a more explicit definition there is ambiguity on dates at the partition limits when those dates are common to multiple

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

Dear community, After upgrade of Posgres from version 13.5 to 16.2 we experience following error: could not extend file "pg_tblspc/16401/PG_16_202307071/17820/3968302971" with FileFallocate(): No space left on device We cannot easily replicate problem. It happens at randomly every 1-2 weeks of

Connection between PostgreSQL and SAP HANA database

Ron Johnson (ronljohnso...@gmail.com) asked me the following. Answers below: 1. What error did you get? No error, the query runns into infinity (>30 min.) without a result. 2. Which command caused the error? Test-query like SELECT * FROM hana_table LIMIT 10; 3. Wha

Recommendations on improving the insert on conflict do nothing performance

Hi insert into dictionary(lang,tid,sportid,brandid,translatedtext,objecttype,basetid) values ($1,$2,$3,$4,$5,$6,$7) on conflict do nothing *8vcpus and 32gb ram Number of calls per sec 1600 at this time 42% of cpu utilized Max in ms 33.62 per call Avg in ms