Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres

This is the toy with the shape of data that will be seen in the application. The final trick was to use to_jsonb to allow the timestamptz to be put back into the jsonb. WITH replace AS ( SELECT jsonb($$[ {"start": "2023-06-12T19:54:39.248859996+10:00", "end": "2023-06-12T19:54:59.2488

Re: How batch processing works

Hi, > On 19 Sep 2024, at 07:30, Lok P wrote: > [snip] > > Method-4 > > INSERT INTO parent_table VALUES (1, 'a'), (2, 'a'); > INSERT INTO child_table VALUES (1,1, 'a'), (1,2, 'a'); > commit; I’ve done some batch processing of JSON messages from Kafka in Java. By far the most performant way

Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres

Thank you both. This has been extremely helpful. I still have more work to do but this has made it possible to start playing with something, and reading about it when it doesn't work. On Sun, 2024-09-15 at 10:13 -0700, Willow Chargin wrote: > On Sun, Sep 15, 2024 at 4:23 AM Alban Hertroys > wrote

Re: glibc updarte 2.31 to 2.38

On 2024-09-19 20:12:13 +0200, Paul Foerster wrote: > Hi Peter, > > On 19 Sep 2024, at 19:43, Peter J. Holzer wrote: > > > > I wrote a small script[1] which prints all unicode code points and a few > > selected[2] longer strings in order. If you run that before and after > > the upgrade and the ou

Re: IO related waits

On 2024-09-20 14:11:38 -0700, Adrian Klaver wrote: > On 9/20/24 1:01 PM, veem v wrote: > > Able to reproduce this deadlock graph as below.  Now my question is , > > this is a legitimate scenario in which the same ID can get inserted from > > multiple sessions and in such cases it's expected to skip

Re: IO related waits

On 9/20/24 1:01 PM, veem v wrote: On Thu, 19 Sept, 2024, 8:40 pm Adrian Klaver, > wrote: On 9/19/24 05:24, Greg Sabino Mullane wrote: > On Thu, Sep 19, 2024 at 5:17 AM veem v mailto:veema0...@gmail.com> > This is really difficult to diagnose

Re: IO related waits

On Fri, Sep 20, 2024 at 4:47 PM Tom Lane wrote: > veem v writes: > > Able to reproduce this deadlock graph as below. Now my question is , > this > > is a legitimate scenario in which the same ID can get inserted from > > multiple sessions and in such cases it's expected to skip that (thus "On >

Re: IO related waits

veem v writes: > Able to reproduce this deadlock graph as below. Now my question is , this > is a legitimate scenario in which the same ID can get inserted from > multiple sessions and in such cases it's expected to skip that (thus "On > conflict Do nothing" is used) row. But as we see it's break

Re: IO related waits

On Thu, 19 Sept, 2024, 8:40 pm Adrian Klaver, wrote: > On 9/19/24 05:24, Greg Sabino Mullane wrote: > > On Thu, Sep 19, 2024 at 5:17 AM veem v > > This is really difficult to diagnose from afar with only snippets of > > logs and half-complete descriptions of your business logic. Pull > > everyon

Re: Why no pg_has_role(..., 'ADMIN')?

Robert Haas writes: > On Fri, Sep 20, 2024 at 2:34 PM Tom Lane wrote: >> I'm now inclined to add wording within the pg_has_role entry, along >> the lines of >> >> WITH ADMIN OPTION or WITH GRANT OPTION can be added to any of >> these privilege types to test whether ADMIN privilege is held >> (al

Re: Why no pg_has_role(..., 'ADMIN')?

On Fri, Sep 20, 2024 at 2:34 PM Tom Lane wrote: > I'm now inclined to add wording within the pg_has_role entry, along > the lines of > > WITH ADMIN OPTION or WITH GRANT OPTION can be added to any of > these privilege types to test whether ADMIN privilege is held > (all six spellings te

Re: Why no pg_has_role(..., 'ADMIN')?

I wrote: > Robert Haas writes: >> I think this already exists. The full list of modes supported by >> pg_has_role() is listed in convert_role_priv_string(). You can do >> something like pg_has_role('alice', 'USAGE WITH ADMIN OPTION'). This >> is not new: it worked in older releases too, but AFAIK

Re: Why no pg_has_role(..., 'ADMIN')?

Robert Haas writes: > I think this already exists. The full list of modes supported by > pg_has_role() is listed in convert_role_priv_string(). You can do > something like pg_has_role('alice', 'USAGE WITH ADMIN OPTION'). This > is not new: it worked in older releases too, but AFAIK it's never been

Re: Why no pg_has_role(..., 'ADMIN')?

On Fri, Sep 20, 2024 at 12:37 PM Laurenz Albe wrote: > > But knowing whether DROP ROLE will work, > > w/o invalidating the current transaction, > > seems like something quite useful to know now, no? > > > > I can query pg_auth_members for admin_option, > > but only easily for direct membership. Ta

Re: Why no pg_has_role(..., 'ADMIN')?

On Fri, 2024-09-20 at 17:26 +0200, Dominique Devienne wrote: > To find out whether a ROLE can DROP another in v16+. > Prior to v16, just having CREATEROLE was enough, > so it didn't really seem necessary. > > But knowing whether DROP ROLE will work, > w/o invalidating the current transaction, > se

Why no pg_has_role(..., 'ADMIN')?

To find out whether a ROLE can DROP another in v16+. Prior to v16, just having CREATEROLE was enough, so it didn't really seem necessary. But knowing whether DROP ROLE will work, w/o invalidating the current transaction, seems like something quite useful to know now, no? I can query pg_auth_membe

Re: Dependencies on the system view

On Fri, Sep 20, 2024 at 7:32 AM Olleg wrote: > Hi all. > > One of our programmer created a view based on the system view. I tried > to explain him, that he created a dependency from the ordinary database > to the system object and this is a bad idea. But he is not smart enough. > So I need a guru

Re: pg_locks in production

On Thu, 2024-09-19 at 15:08 -0400, Wizard Brony wrote: > If performance is not an issue, is it valid to use the pg_locks > system view in production code? Or are there other concerns > besides performance that would make its use inappropriate for production? There are no concerns if you use pg_loc

pg_locks in production

If performance is not an issue, is it valid to use the pg_locks system view in production code? Or are there other concerns besides performance that would make its use inappropriate for production?

Dependencies on the system view

Hi all. One of our programmer created a view based on the system view. I tried to explain him, that he created a dependency from the ordinary database to the system object and this is a bad idea. But he is not smart enough. So I need a guru opinion. Is this permissible or here will be a trouble

Dependencies on the system view

Hi all. One of our programmer created a view based on the system view. I tried to explain him, that he created a dependency from the ordinary database to the system object and this is a bad idea. But he is not smart enough. So I need a guru opinion. Is this permissible or will here be a troubl

question on timezone with pgBackRest PITR

The Postgres instance is running on timezone CEST (parameter timezone = 'Europe/Zurich') which is UTC+2. The underlying Linux OS (K8s container) is running on timezone UTC. Backups are done with pgBackRest. With PITR we will have to use target timezone CEST (--target=2024-09-19 17:00:00+02") a