Re: Time zone offset in to_char()

On 1/11/24 6:20 PM, Jim Nasby wrote: On 1/11/24 5:53 PM, Tom Lane wrote: Adrian Klaver writes: test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', '-MM-DD HH24:MI:SS.US0 TZH:TZM') ;     to_char    2024-01-12 00:44:57.5421420 +00:00

Re: Time zone offset in to_char()

On 1/11/24 5:53 PM, Tom Lane wrote: Adrian Klaver writes: test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', '-MM-DD HH24:MI:SS.US0 TZH:TZM') ; to_char 2024-01-12 00:44:57.5421420 +00:00 (1 row) You end up with string

What should I expect when creating many logical replication slots?

Hi all, I have a use case for which I am considering using Postgres Logical Replication, but I would like to scale up to 100 or even 200 replication slots. I have increased max_wal_senders and max_replication_slots to 100 (also making sure that max_connections is large enough). Things seem to be

Re: Refresh Materialized View Issue

On 1/11/24 3:40 PM, Ron Johnson wrote: On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer > wrote: My question is: what indexes are on public.large_table? Hopefully there's a compound b-tree index on id1, id2, id3. There is not, after further

Re: Time zone offset in to_char()

Adrian Klaver writes: > test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', '-MM-DD > HH24:MI:SS.US0 TZH:TZM') ; >to_char > > 2024-01-12 00:44:57.5421420 +00:00 > (1 row) > You end up with string that does not the correct

Re: Time zone offset in to_char()

On 1/11/24 15:32, Jim Nasby wrote: On 1/11/24 9:06 AM, Alban Hertroijs wrote: I'm basically looking for a one-liner to convert a timestamptz (or a timestamp w/o time zone if that turns out to be more convenient) to a string format equal to what MS uses for their datetimeoffset type. I got

RE: Postgres 13 streaming replication standby not sending password, 'fe_sendauth: no password supplied'

On Wed, Jan 10, 2024 at 23:22 Ron Johnson wrote: >> On Wed, Jan 10, 2024 at 5:51 PM Keaney, Will >> wrote: >> Hello, >> >> I'm building a new 2-node Postgreql 13 streaming replication cluster. I'm >> able to clone the primary to the standby using pg_basebackup. >> However, the standby is

Re: Time zone offset in to_char()

On 1/11/24 9:06 AM, Alban Hertroijs wrote: I'm basically looking for a one-liner to convert a timestamptz (or a timestamp w/o time zone if that turns out to be more convenient) to a string format equal to what MS uses for their datetimeoffset type. I got almost there with to_char(ts,

Re: undefined symbol when installing pgcrypto on 16.1

Michael Nolan writes: > This is on AlmaLinux 9.3, installing postgresql from source code. > In PG 16.1 when I try to install pgcrypto, the modules compile but I > get this error when running checks: > CREATE EXTENSION pgcrypto; > +ERROR: could not load library >

undefined symbol when installing pgcrypto on 16.1

This is on AlmaLinux 9.3, installing postgresql from source code. In PG 16.1 when I try to install pgcrypto, the modules compile but I get this error when running checks: CREATE EXTENSION pgcrypto; +ERROR: could not load library

Re: Refresh Materialized View Issue

On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer wrote: > My question is: what indexes are on public.large_table? Hopefully > there's a compound b-tree index on id1, id2, id3. > > There is not, after further investigation. There are these 4 indexes that > involve id1, id2, and id3. Should I try

Re: Time zone offset in to_char()

On 1/11/24 11:04, Alban Hertroys wrote: I did manage to apply it to the second function header, which I think behaves such that the time zone change stays within function scope. Right now I’m not 100% sure that I verified that. More to check tomorrow. CREATE OR REPLACE FUNCTION

Re: Time zone offset in to_char()

On 1/11/24 11:04, Alban Hertroys wrote: I didn’t succeed in calling SET LOCAL TIMEZONE from within the function. Could be I missed something, then Google (stackoverflow) pointed me to set_config(). CREATE OR REPLACE FUNCTION public.tz_fnc() RETURNS void LANGUAGE plpgsql AS $function$

Re: Refresh Materialized View Issue

My question is: what indexes are on public.large_table? Hopefully there's a compound b-tree index on id1, id2, id3. There is not, after further investigation. There are these 4 indexes that involve id1, id2, and id3. Should I try creating an index on all three of the columns? CREATE INDEX

Re: Refresh Materialized View Issue

On Thu, Jan 11, 2024 at 3:31 PM Jeremiah Bauer wrote: > Hello all, > > We are having an issue with a materialized view refresh never finishing, > any help is appreciated. It will run at 100% CPU and no IO traffic > indefinitely after about 15 minutes of parallel workers and the parent > worker

Refresh Materialized View Issue

Hello all, We are having an issue with a materialized view refresh never finishing, any help is appreciated. It will run at 100% CPU and no IO traffic indefinitely after about 15 minutes of parallel workers and the parent worker consuming CPU and IO. PostgreSQL Version: 15.5 Due to some

Re: Time zone offset in to_char()

> On 11 Jan 2024, at 18:27, Adrian Klaver wrote: > > On 1/11/24 08:48, Adrian Klaver wrote: >> On 1/11/24 08:04, Alban Hertroijs wrote: > >>> The drawback, as mentioned, being that we need to maintain those functions >>> in each deployment, which is a bit of a hassle (albeit a minor one)

Re: Time zone offset in to_char()

> On 11 Jan 2024, at 17:43, Adrian Klaver wrote: > > On 1/11/24 07:06, Alban Hertroijs wrote: >> Hi all, > >> In the above, I worked around the issue using a couple of user-defined >> functions in PG. That should give a reasonable idea of the desired >> functionality, but it's not an ideal

Re: Time zone offset in to_char()

Alban Hertroijs wrote: > 1). The first function has as a drawback that it changes the time zone for > the entire transaction (not sufficiently isolated to my tastes) But if you add in the function declaration SET timezone TO 'Europe/Amsterdam' like in your 2nd function, or simply

Re: Time zone offset in to_char()

On 1/11/24 08:48, Adrian Klaver wrote: On 1/11/24 08:04, Alban Hertroijs wrote: The drawback, as mentioned, being that we need to maintain those functions in each deployment, which is a bit of a hassle (albeit a minor one) because we need to customise both the TDV side and the PostgreSQL

Re: Time zone offset in to_char()

On 1/11/24 08:04, Alban Hertroijs wrote: > In the above, I worked around the issue using a couple of user-defined functions in PG. That should give a reasonable idea of the desired functionality, but it's not an ideal solution to my problem: > 1). The first function has as a

Re: Time zone offset in to_char()

On 1/11/24 07:06, Alban Hertroijs wrote: Hi all, In the above, I worked around the issue using a couple of user-defined functions in PG. That should give a reasonable idea of the desired functionality, but it's not an ideal solution to my problem: 1). The first function has as a drawback

Re: Time zone offset in to_char()

> In the above, I worked around the issue using a couple of user-defined > functions in PG. That should give a reasonable idea of the desired > functionality, but it's not an ideal solution to my problem: > 1). The first function has as a drawback that it changes the time zone for > the entire

Re: Time zone offset in to_char()

Am 11.01.2024 um 16:06 schrieb Alban Hertroijs : Hi all, I'm basically looking for a one-liner to convert a timestamptz (or a timestamp w/o time zone if that turns out to be more convenient) to a string format equal to what MS uses for their datetimeoffset type. I got almost there with

Re: Time zone offset in to_char()

> Am 11.01.2024 um 16:06 schrieb Alban Hertroijs : > > Hi all, > > I'm basically looking for a one-liner to convert a timestamptz (or a > timestamp w/o time zone if that turns out to be more convenient) to a string > format equal to what MS uses for their datetimeoffset type. I got almost >

Aw: Time zone offset in to_char()

> > In the above, I worked around the issue using a couple of user-defined > > functions in PG. That should give a reasonable idea of the desired > > functionality, but it's not an ideal solution to my problem: > > 1). The first function has as a drawback that it changes the time zone for > >

Aw: Time zone offset in to_char()

> In the above, I worked around the issue using a couple of user-defined > functions in PG. That should give a reasonable idea of the desired > functionality, but it's not an ideal solution to my problem: > 1). The first function has as a drawback that it changes the time zone for > the entire

Time zone offset in to_char()

Hi all, I'm basically looking for a one-liner to convert a timestamptz (or a timestamp w/o time zone if that turns out to be more convenient) to a string format equal to what MS uses for their datetimeoffset type. I got almost there with to_char(ts, '-MM-DD HH24:MI:SS.US0 TZH:TZM').

Re: Postgres 13 streaming replication standby not sending password, 'fe_sendauth: no password supplied'

On Wed, 2024-01-10 at 22:51 +, Keaney, Will wrote: > However, the standby is unable to authenticate to the primary to begin > recovery during startup. > It logs an error, "FATAL: could not connect to the primary server: > fe_sendauth: no password supplied". > > pg_hba.conf on the primary: