Re: Time zone offset in to_char()

[Jim Nasby]

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
(1 row)



You end up with string that does not the correct offset as the AT TIME
ZONE outputs a timestamp not timestamptz value.


Yeah.  to_char() does not have any source for the TZ/TZH/TZM fields
other than the prevailing value of the timezone parameter, so you
really have to set that the way you want if you desire to use these
format fields.  As noted upthread, SET LOCAL together with a (dummy)
"SET timezone" clause in the function definition can be used to get
the effect of a function-local setting of the parameter.  I don't
know of another way to achieve that result above the C-code level.

    regards, tom lane


Sorry, I was implying that you could use the generated timestamp without 
timezone as a string and supply the necessary timezone:


select to_char(timestamptz(timezone('UTC',tstz) || ' CST6CDT'), 
'-MM-DD HH24:MI:SS.US0 TZH:TZM') from tstz ;

   to_char

  2024-01-11 23:29:00.0493300 -06:00
(1 row)


NEVERMIND... I see now that doesn't actually work.

Perhaps we should add a variant of timezone() that handles this use-case...
--
Jim Nasby, Data Architect, Austin TX

Re: Time zone offset in to_char()

[Jim Nasby]

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 that does not the correct offset as the AT TIME
ZONE outputs a timestamp not timestamptz value.


Yeah.  to_char() does not have any source for the TZ/TZH/TZM fields
other than the prevailing value of the timezone parameter, so you
really have to set that the way you want if you desire to use these
format fields.  As noted upthread, SET LOCAL together with a (dummy)
"SET timezone" clause in the function definition can be used to get
the effect of a function-local setting of the parameter.  I don't
know of another way to achieve that result above the C-code level.

regards, tom lane


Sorry, I was implying that you could use the generated timestamp without 
timezone as a string and supply the necessary timezone:


select to_char(timestamptz(timezone('UTC',tstz) || ' CST6CDT'), 
'-MM-DD HH24:MI:SS.US0 TZH:TZM') from tstz ;

  to_char

 2024-01-11 23:29:00.0493300 -06:00
(1 row)

--
Jim Nasby, Data Architect, Austin TX

What should I expect when creating many logical replication slots?

[Antonin Bas]

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
working pretty well so far based on some PoC code I have written. Postgres
is creating a walsender process for each replication slot, as expected, and
the memory footprint of each one is around 4MB.

So I am quite happy with the way things are working, but I am a bit uneasy
about increasing these configuration values by 10-20x compared to their
defaults (both max_wal_senders and max_replication_slots default to 10).

Is there anything I should be looking out for specifically? Is it
considered an anti-pattern to use that many replication slots and walsender
processes? And, when my database comes under heavy write load, will
walsender processes start consuming a large amount of CPU / memory (I
recognize that this is a vague question, I am still working on some
empirical testing).

Finally, I am currently using Postgres 14. Should I consider upgrading to
Postgres 15 or 16 based on my use case?

Thanks in advance for any insight on this.

Antonin

Re: Refresh Materialized View Issue

[Jim Nasby]

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 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 IF NOT EXISTS idx_large_table_id1

[snip]

CREATE INDEX IF NOT EXISTS idx_large_table_id2

[snip]

CREATE INDEX IF NOT EXISTS idx_large_table_id3

[snip]

CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3

[snip]
I'd strongly think about creating such an index, since the current 
indices don't help much.


That'd be a band-aid at best, because we know that the query used to 
define the materialized view runs in a reasonable amount of time on it's 
own, as does a CTAS. So either the REFRESH is doing something odd when 
writing into the new relation (which looking at the code seems very 
unlikely), or REFRESH is getting a different query plan for some reason. 
Unfortunately, I don't know of any easy way to get the query plan for 
the REFRESH (it might be possible via gdb, but I'm not sure). We do at 
least know that the REFRESH is using parallel workers.


Can you post the output of EXPLAIN ANALYZE for the SELECT? That might 
provide some clues.

--
Jim Nasby, Data Architect, Austin TX

Re: Time zone offset in to_char()

[Tom Lane]

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 offset as the AT TIME 
> ZONE outputs a timestamp not timestamptz value.

Yeah.  to_char() does not have any source for the TZ/TZH/TZM fields
other than the prevailing value of the timezone parameter, so you
really have to set that the way you want if you desire to use these
format fields.  As noted upthread, SET LOCAL together with a (dummy)
"SET timezone" clause in the function definition can be used to get
the effect of a function-local setting of the parameter.  I don't
know of another way to achieve that result above the C-code level.

regards, tom lane

Re: Time zone offset in to_char()

[Adrian Klaver]

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 almost there with to_char(ts, '-MM-DD HH24:MI:SS.US0 
TZH:TZM'). Unfortunately(?), the server lives at time zone UTC, while 
we need to convert to both UTC and Europe/Amsterdam zones. The above 
always gives me +00 for the TZH output, while it should be +01 now and 
+02 in the summer...


The issue here is that timestamptz doesn't store the original timezone; 
it always converts whatever is passed in to UTC and stores that. When 
you read the timezone back, by default it will be in the timezone 
specified in the TimeZone GUC. While there's a bunch of ways you can set 
that, for what you're looking to do I don't think any of them are 
appropriate; instead you want to use either AT TIME ZONE or timezone():


create table tstz(tstz timestamptz);
insert into tstz values(now());
SHOW timezone;
  TimeZone
--
  CST6CDT
(1 row)

select * from tstz ;
  tstz
--
  2024-01-11 17:29:00.04933-06
(1 row)

select timezone('UTC',tstz) from tstz ;
  timezone
---
  2024-01-11 23:29:00.04933
(1 row)

select tstz AT TIME ZONE 'UTC' from tstz ;
  timezone
---
  2024-01-11 23:29:00.04933
(1 row)


The problem with this is as mentioned here:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT


"timestamp with time zone AT TIME ZONE zone → timestamp without time zone

Converts given time stamp with time zone to time stamp without time 
zone, as the time would appear in that zone."


So when you do something like:

test=# set timezone = 'UTC';
SET
test=# select now() AT TIME ZONE 'UTC' ;
  timezone

 2024-01-11 23:44:46.021986
(1 row)

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 offset as the AT TIME 
ZONE outputs a timestamp not timestamptz value.




--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Keaney, Will]

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 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".
>>
>> I've tried using both .pgpass and passing the password explicitly in the 
>> myrecovery.conf primary_conninfo string. Debug logs on the primary show the 
>> initial connection. but no user provided and no authentication attempt.
>>
>> pg_hba.conf on the primary:
>> hostall,replication replprimary-database-server  
>>  scram-sha-256
>> hostall,replication replstandby-database-server  
>>  scram-sha-256
>>
>> myrecovery.conf on the standby:
>> primary_conninfo = 'host=primary-database-server port=5432 user=repl 
>> application_name=standby-server-name'
>> recovery_target_timeline = 'latest'
>> primary_slot_name = 'standby_replication_slot'
>>
>> .pgpass on the standby:
>> # hostname:port:database:username:password
>> *:*:replication:repl:repl_user_password
>>
>> I've triple-checked that the password in .pgpass matches the password set 
>> for the repl user in the database, and the repl user has REPLICATION access.
>> I'm able to connect to the primary server using the repl user and the psql 
>> client, both via .pgpass and providing the password directly.
>> I can't figure out why the standby postgres server is skipping the provided 
>> credentials when connecting to the primary.
>>
> Let pg_basebackup do all the work for you:
>
> $ cat ~postgres/.pg_service.conf
> [basebackup]
> host=
> port=5432
> user=replicator
> passfile=/var/lib/pgsql/.pgpass
>
> $ pg_basebackup \
> --pgdata=$PGDATA \
> --dbname=service=basebackup \
> --verbose --progress \
> --checkpoint=fast \
> --write-recovery-conf \
> --wal-method=stream \
> --create-slot --slot=pgstandby1

Thank you! That's both extremely useful and helped me identify the source of my 
problem.
A previous experiment with repmgr had left a stale primary_conninfo string in 
postgresql.auto.conf, which was somehow interfering with my other recovery 
settings.
After cleaning that up, my standby can connect to my primary without issue.

Best,
Will



This e-mail message, including any attachments, is for the sole use of the 
intended recipient(s) and may contain information that is confidential and 
protected by law from unauthorized disclosure. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply e-mail and destroy all copies of 
the original message.

Re: Time zone offset in to_char()

[Jim Nasby]

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, '-MM-DD HH24:MI:SS.US0 TZH:TZM'). 
Unfortunately(?), the server lives at time zone UTC, while we need to 
convert to both UTC and Europe/Amsterdam zones. The above always gives 
me +00 for the TZH output, while it should be +01 now and +02 in the 
summer...


The issue here is that timestamptz doesn't store the original timezone; 
it always converts whatever is passed in to UTC and stores that. When 
you read the timezone back, by default it will be in the timezone 
specified in the TimeZone GUC. While there's a bunch of ways you can set 
that, for what you're looking to do I don't think any of them are 
appropriate; instead you want to use either AT TIME ZONE or timezone():


create table tstz(tstz timestamptz);
insert into tstz values(now());
SHOW timezone;
 TimeZone
--
 CST6CDT
(1 row)

select * from tstz ;
 tstz
--
 2024-01-11 17:29:00.04933-06
(1 row)

select timezone('UTC',tstz) from tstz ;
 timezone
---
 2024-01-11 23:29:00.04933
(1 row)

select tstz AT TIME ZONE 'UTC' from tstz ;
 timezone
---
 2024-01-11 23:29:00.04933
(1 row)

--
Jim Nasby, Data Architect, Austin TX

Re: undefined symbol when installing pgcrypto on 16.1

[Tom Lane]

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
> "/home/postgres/src/postgresql-16.1/tmp_install/usr/local/pgsql/lib/
> pgcrypto.so": 
> /home/postgres/src/postgresql-16.1/tmp_install/usr/local/pgsql/lib/pgcrypto.so:
> undefined symbol: EVP_cast5_cbc

That should be supplied by OpenSSL.  Are you using some weird
version of openssl?  Did you get any warnings during build?

regards, tom lane

undefined symbol when installing pgcrypto on 16.1

[Michael Nolan]

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
"/home/postgres/src/postgresql-16.1/tmp_install/usr/local/pgsql/lib/
pgcrypto.so": 
/home/postgres/src/postgresql-16.1/tmp_install/usr/local/pgsql/lib/pgcrypto.so:
undefi
ned symbol: EVP_cast5_cbc


Mike Nolan
htf...@gmail.com

Re: Refresh Materialized View Issue

[Ron Johnson]

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 creating an index on all three of
> the columns?
>
> CREATE INDEX IF NOT EXISTS idx_large_table_id1
>
[snip]

> CREATE INDEX IF NOT EXISTS idx_large_table_id2
>
[snip]

> CREATE INDEX IF NOT EXISTS idx_large_table_id3
>
[snip]

> CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3
>
[snip]

I'd strongly think about creating such an index, since the current indices
don't help much.

Adding id1 to the end of idx_large_table_id2_id3 *might* be a better
option, since it'll reuse much of the existing disk space.


> A materialized view isn't too different from an unlogged table.
>
> So an unlogged table would also be an appropriate solution?
>

Sure, since materialized views are effectively tables with a SELECT
statement bound to it, and are logged.  Thus, unlogged tables are faster to
create.  Of course, being unlogged is a two-edged sword: any unclean
shutdown makes the data go away; you'd have to rebuild the table.

Re: Time zone offset in to_char()

[Adrian Klaver]

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 public.tz_fnc2()
 RETURNS void
 LANGUAGE plpgsql
 SET "TimeZone" TO 'UTC'
AS $function$
BEGIN
RAISE NOTICE '%', to_char(now(), 'OF');
END;
$function$;


test=# begin ;
BEGIN
test=*# select public.tz_fnc2();
NOTICE:  +00
 tz_fnc2
-

(1 row)

test=*# show timezone;
 TimeZone
--
 UTC
(1 row)

test=*# commit ;
COMMIT
test=# show timezone;
 TimeZone
--
 UTC
(1 row)




Frankly, I do hope that you’re right here, that would make my work easier.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Time zone offset in to_char()

[Adrian Klaver]

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$
BEGIN
SET LOCAL  TIMEZONE = 'UTC';
RAISE NOTICE '%', to_char(now(), 'OF');
END;
$function$


test=# begin ;
BEGIN
test=*# select public.tz_fnc();
NOTICE:  +00
 tz_fnc


(1 row)

test=*# show timezone;
 TimeZone
--
 UTC
(1 row)

test=*# commit;
COMMIT
test=# show timezone;
  TimeZone
-
 America/Los_Angeles
(1 row)







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.

Frankly, I do hope that you’re right here, that would make my work easier.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Refresh Materialized View Issue

[Jeremiah Bauer]

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 IF NOT EXISTS idx_large_table_id1
ON public.large_table USING btree
(id1 ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_large_table_id2
ON public.large_table USING btree
(id2 ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_large_table_id3
ON public.large_table USING btree
(id3 ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3
ON public.large_table USING btree
(id2 ASC NULLS LAST, id3 ASC NULLS LAST)
TABLESPACE pg_default;

A materialized view isn't too different from an unlogged table.
So an unlogged table would also be an appropriate solution?

--


Jeremiah

CONFIDENTIALITY NOTICE: The information contained in this email (and any 
attachments) is privileged and confidential and protected from disclosure. If 
you are not the intended recipient of this email or the attachments, be aware 
that any disclosure, copying, distribution or use of this email or any 
attachment is strictly prohibited and you should not read the message or read 
or open any attachment. If you have received this email by mistake, please 
immediately notify the sender and delete it permanently from your system. Agri 
Stats, Inc. and its subsidiaries will not be held liable to any person or 
entity resulting from the unintended or unauthorized use of any information 
contained in this email.

Re: Refresh Materialized View Issue

[Ron Johnson]

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 consuming CPU and IO.
>
> PostgreSQL Version: 15.5
>
> Due to some design decisions, we have a large table that we need a
> distinct list of associated id's out of for an application interface.  This
> table has approximately 1,650,000,000 rows with a table size of 175GB and
> 250GB of indexes.
>
> I anonymized the column names in the table structure, but this is the
> table structure of our large table.
>
> create table public.large_table(
>large_table_id bigint NOT NULL DEFAULT
> nextval(public.large_table_id_seq'::regclass),
> nmrc numeric(30,5),
> id1 bigint NOT NULL,
> id2 bigint NOT NULL,
> id3 bigint NOT NULL,
> id4 bigint NOT NULL,
> id5 bigint NOT NULL,
> last_updt_ts timestamp without time zone,
> last_db_updt_ts timestamp without time zone,
> charval character varying(30)
>
> )
>
> The materialized view is defined as:
>
> create materialized view public.vw_distinct_list_of_ids as (
>
> select distinct id1, id2, id3 from public.large_table
>
> ) with no data;
>
> When refreshed the materialized view will contain approximately 59,000,000
> rows.
>
> It takes approximately 12 minutes to run the select statement from the
> view definition.  When you refresh the materialized view for the first
> time, it will spawn several parallel workers that run at 100% CPU and
> produce some I/O for about 12 or 13 minutes, then they finish their work
> and terminate.  The parent worker will continue to run at 100% CPU until I
> terminate the process.  I've let it run for over 24 hours.
>
> What I find interesting is that if I do this:
>
> create table public.table_of_distinct_list_of_ids as (
>
> select distinct id1, id2, id3 from public.large_table
>
> );
>
> It will complete in 12 or 13 minutes, the same as the select statement.  I
> have three questions.
>

My question is: what indexes are on public.large_table?  Hopefully there's
a compound b-tree index on id1, id2, id3.

>
>1. Why is the materialized view refresh stalling and never completing?
>2. Is there a setting I am missing that affects materialized view
>refreshes?
>3. Should I just go with a table that is dropped and recreated
>instead?  I was hoping to avoid this.
>
> A materialized view isn't too different from an unlogged table.

Refresh Materialized View Issue

[Jeremiah Bauer]

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 design decisions, we have a large table that we need a distinct 
list of associated id's out of for an application interface.  This table has 
approximately 1,650,000,000 rows with a table size of 175GB and 250GB of 
indexes.

I anonymized the column names in the table structure, but this is the table 
structure of our large table.

create table public.large_table(
   large_table_id bigint NOT NULL DEFAULT 
nextval(public.large_table_id_seq'::regclass),
nmrc numeric(30,5),
id1 bigint NOT NULL,
id2 bigint NOT NULL,
id3 bigint NOT NULL,
id4 bigint NOT NULL,
id5 bigint NOT NULL,
last_updt_ts timestamp without time zone,
last_db_updt_ts timestamp without time zone,
charval character varying(30)

)

The materialized view is defined as:

create materialized view public.vw_distinct_list_of_ids as (

select distinct id1, id2, id3 from public.large_table

) with no data;

When refreshed the materialized view will contain approximately 59,000,000 rows.

It takes approximately 12 minutes to run the select statement from the view 
definition.  When you refresh the materialized view for the first time, it will 
spawn several parallel workers that run at 100% CPU and produce some I/O for 
about 12 or 13 minutes, then they finish their work and terminate.  The parent 
worker will continue to run at 100% CPU until I terminate the process.  I've 
let it run for over 24 hours.

What I find interesting is that if I do this:

create table public.table_of_distinct_list_of_ids as (

  select distinct id1, id2, id3 from public.large_table

);

It will complete in 12 or 13 minutes, the same as the select statement.  I have 
three questions.


  1.  Why is the materialized view refresh stalling and never completing?
  2.  Is there a setting I am missing that affects materialized view refreshes?
  3.  Should I just go with a table that is dropped and recreated instead?  I 
was hoping to avoid this.


--

Sincerely,


Jeremiah Bauer


Agri Stats, Inc.

CONFIDENTIALITY NOTICE: The information contained in this email (and any 
attachments) is privileged and confidential and protected from disclosure. If 
you are not the intended recipient of this email or the attachments, be aware 
that any disclosure, copying, distribution or use of this email or any 
attachment is strictly prohibited and you should not read the message or read 
or open any attachment. If you have received this email by mistake, please 
immediately notify the sender and delete it permanently from your system. Agri 
Stats, Inc. and its subsidiaries will not be held liable to any person or 
entity resulting from the unintended or unauthorized use of any information 
contained in this email.

Re: Time zone offset in to_char()

[Alban Hertroys]

> 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) because 
>>> we need to customise both the TDV side and the PostgreSQL side in that 
>>> case. Our preferred solution would be to just add a few entries to the TDV 
>>> database-specific capabilities file (as described in my initial message)
>> Are you referring to?:
>> "It currently have this:
>> ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
>> ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)
>> "
> 
> It finally dawned on me, you want to replace the user defined functions above 
> with Postgres builtins only. Try as I might I could not come with that 
> solution.

Exactly. I was having the same problem of finding a solution, quite to my 
surprise.

>> I thought the issue there was maintaining the two Postgres functions?

Yup, those two functions in fact.

There will be at least 3 separate deployments, while maintenance of the 
database(-schema) contents is the responsibility of the 3rd party application 
(TDV). PG is used as a caching DB here, we therefore intend to treat the data 
in it as volatile; it shouldn’t hurt if we decide to recreate the caches from 
scratch from source data. Having custom code in there not under control of the 
3rd party application breaks that guideline.

If they’re necessary, then so be it, but I can’t shake the feeling that we can 
achieve this without custom code in the database.

Regards,

Alban Hertroys
--
There is always an exception to always.

Re: Time zone offset in to_char()

[Alban Hertroys]

> 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 solution to my problem:
>> 1). The first function has as a drawback that it changes the time zone for 
>> the entire transaction (not sufficiently isolated to my tastes), while
>> 2). The second function has the benefit that it doesn't leak the time zone 
>> change, but has as drawback that the time zone is now hardcoded into the 
>> function definition, while
> 
> I don't think the set_config and SET are acting the way you think they are:
> 
> set_config(https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET)
> 
> "
> set_config ( setting_name text, new_value text, is_local boolean ) → text
> 
> Sets the parameter setting_name to new_value, and returns that value. If 
> is_local is true, the new value will only apply during the current 
> transaction. If you want the new value to apply for the rest of the current 
> session, use false instead. This function corresponds to the SQL command SET.
> 
> set_config('log_statement_stats', 'off', false) → off"
> "

I tried this like so:

select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), 
to_char(current_timestamp, ‘-MM-DD HH24:MI:SS.SU0 TZH:TZM’).

The result of the second call was based on time zone ‘Europe/Amsterdam’, where 
it wasn’t when called outside the transaction (when it was based on UTC 
corresponding to the server time zone).
So the time zone set with set_config(…, …, true) appeared to leak out of 
function scope and applied to transaction scope (as described in the quoted 
text).
For brevity I could run that query tomorrow when I’m back at work.

> SET(https://www.postgresql.org/docs/current/sql-set.html)
> 
> "If SET (or equivalently SET SESSION) is issued within a transaction that is 
> later aborted, the effects of the SET command disappear when the transaction 
> is rolled back. Once the surrounding transaction is committed, the effects 
> will persist until the end of the session, unless overridden by another SET.
> 
> The effects of SET LOCAL last only till the end of the current transaction, 
> whether committed or not. A special case is SET followed by SET LOCAL within 
> a single transaction: the SET LOCAL value will be seen until the end of the 
> transaction, but afterwards (if the transaction is committed) the SET value 
> will take effect.

It says transaction again here.

> The effects of SET or SET LOCAL are also canceled by rolling back to a 
> savepoint that is earlier than the command.
> 
> If SET LOCAL is used within a function that has a SET option for the same 
> variable (see CREATE FUNCTION), the effects of the SET LOCAL command 
> disappear at function exit; that is, the value in effect when the function 
> was called is restored anyway. This allows SET LOCAL to be used for dynamic 
> or repeated changes of a parameter within a function, while still having the 
> convenience of using the SET option to save and restore the caller's value. 
> However, a regular SET command overrides any surrounding function's SET 
> option; its effects will persist unless rolled back.
> "

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().

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.

Frankly, I do hope that you’re right here, that would make my work easier.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Time zone offset in to_char()

[Daniel Verite]

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
  SET timezone FROM CURRENT
doesn't that solve this problem?

Because as the doc says

  If a SET clause is attached to a function, then the effects of a SET
  LOCAL command executed inside the function for the same variable are
  restricted to the function: the configuration parameter's prior
  value is still restored at function exit

The actual value to which the timezone is set through this statement
does not matter, as the first instruction in the function overwrites
it:
   perform set_config('timezone', tz_, true /* local */); 

The point is that the prior value of timezone being restored
automatically at function exit, the temporary setting will not
leak out of the function.


Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

Re: Time zone offset in to_char()

[Adrian Klaver]

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 side in that case. Our preferred solution would be to just 
add a few entries to the TDV database-specific capabilities file (as 
described in my initial message)


Are you referring to?:

"It currently have this:
ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)
"


It finally dawned on me, you want to replace the user defined functions 
above with Postgres builtins only. Try as I might I could not come with 
that solution.




I thought the issue there was maintaining the two Postgres functions?


Provided that such a solution is possible, that is. If not, my current 
approach may have to suffice.


The reason I decided to ask on the ML is that I'm finding it hard to 
believe that this transformation would be this difficult, so I expect 
that I must be missing something.


Regards,
Alban Hertroys




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Time zone offset in to_char()

[Adrian Klaver]

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 drawback that it changes the time
zone for the entire transaction (not sufficiently isolated to my
tastes), while
 > 2). The second function has the benefit that it doesn't leak the
time zone change, but has as drawback that the time zone is now
hardcoded into the function definition, while
 > 3). Both functions need to be created in the caching database
before we can use them, while we have several environments where
they would apply (DEV, pre-PROD, PROD).

Would a function that dispatches its calls to a suitable array of
hard-coded functions based on an IN parameter help any ?

Karsten

Well, probably, but we don't have many time zones that are relevant to 
us. For that, the current functions would be sufficient.


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 
side in that case. Our preferred solution would be to just add a few 
entries to the TDV database-specific capabilities file (as described in 
my initial message)


Are you referring to?:

"It currently have this:
ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)
"

I thought the issue there was maintaining the two Postgres functions?


Provided that such a solution is possible, that is. If not, my current 
approach may have to suffice.


The reason I decided to ask on the ML is that I'm finding it hard to 
believe that this transformation would be this difficult, so I expect 
that I must be missing something.


Regards,
Alban Hertroys


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Time zone offset in to_char()

[Adrian Klaver]

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 that it changes the time zone 
for the entire transaction (not sufficiently isolated to my tastes), while
2). The second function has the benefit that it doesn't leak the time 
zone change, but has as drawback that the time zone is now hardcoded 
into the function definition, while


I don't think the set_config and SET are acting the way you think they are:

set_config(https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET)

"
set_config ( setting_name text, new_value text, is_local boolean ) → text

Sets the parameter setting_name to new_value, and returns that value. If 
is_local is true, the new value will only apply during the current 
transaction. If you want the new value to apply for the rest of the 
current session, use false instead. This function corresponds to the SQL 
command SET.


set_config('log_statement_stats', 'off', false) → off"
"

SET(https://www.postgresql.org/docs/current/sql-set.html)

"If SET (or equivalently SET SESSION) is issued within a transaction 
that is later aborted, the effects of the SET command disappear when the 
transaction is rolled back. Once the surrounding transaction is 
committed, the effects will persist until the end of the session, unless 
overridden by another SET.


The effects of SET LOCAL last only till the end of the current 
transaction, whether committed or not. A special case is SET followed by 
SET LOCAL within a single transaction: the SET LOCAL value will be seen 
until the end of the transaction, but afterwards (if the transaction is 
committed) the SET value will take effect.


The effects of SET or SET LOCAL are also canceled by rolling back to a 
savepoint that is earlier than the command.


If SET LOCAL is used within a function that has a SET option for the 
same variable (see CREATE FUNCTION), the effects of the SET LOCAL 
command disappear at function exit; that is, the value in effect when 
the function was called is restored anyway. This allows SET LOCAL to be 
used for dynamic or repeated changes of a parameter within a function, 
while still having the convenience of using the SET option to save and 
restore the caller's value. However, a regular SET command overrides any 
surrounding function's SET option; its effects will persist unless 
rolled back.

"

3). Both functions need to be created in the caching database before we 
can use them, while we have several environments where they would apply 
(DEV, pre-PROD, PROD).



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Time zone offset in to_char()

[Alban Hertroijs]

> 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 transaction (not sufficiently isolated to my tastes), while
> 2). The second function has the benefit that it doesn't leak the time zone 
> change, but has as drawback that the time zone is now hardcoded into the 
> function definition, while
> 3). Both functions need to be created in the caching database before we can 
> use them, while we have several environments where they would apply (DEV, 
> pre-PROD, PROD).

Would a function that dispatches its calls to a suitable array of hard-coded 
functions based on an IN parameter help any ?

Karsten
Well, probably, but we don't have many time zones that are relevant to us. For 
that, the current functions would be sufficient.

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 side in that case. Our 
preferred solution would be to just add a few entries to the TDV 
database-specific capabilities file (as described in my initial message).
Provided that such a solution is possible, that is. If not, my current approach 
may have to suffice.

The reason I decided to ask on the ML is that I'm finding it hard to believe 
that this transformation would be this difficult, so I expect that I must be 
missing something.

Regards,
Alban Hertroys

Re: Time zone offset in to_char()

[Alban Hertroijs]

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 
to_char(ts, '-MM-DD HH24:MI:SS.US0 TZH:TZM'). Unfortunately(?), the server 
lives at time zone UTC, while we need to convert to both UTC and 
Europe/Amsterdam zones. The above always gives me +00 for the TZH output, while 
it should be +01 now and +02 in the summer...

have you tried to use the proper time zone before you pass it to the to_char() 
function?

Time: 2,095 ms
mkrueger=# select to_char(now() at time zone 'Europe/Amsterdam', '-MM-DD 
HH24:MI:SS.US0 TZH:TZM');
  to_char

 2024-01-11 16:24:52.8736860 +00:00
(1 row)


At least it seems to do what you need.
That is indeed what I started out with, but that output is not correct for my 
purposes. That TZH field should read '+01' instead of '+00', so:
 2024-01-11 16:24:52.8736860 +01:00

For analytical purposes, I tacked a 'TZ' at the end of that format string, and 
it kept coming out either empty or giving 'UTC' instead of 'CET'. And according 
to the Internet, that is because PG timestamps don't actually store the time 
zone information (it's stored relative to UTC) and thus there is no information 
to base the output of TZ, TZH and TZM on other than the current scope's time 
zone.

This is in fact exactly the problem that I tried to work around using those 
functions.

Regards,
Alban Hertroys.

Re: Time zone offset in to_char()

[michael]

> 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 to_char(ts, '-MM-DD HH24:MI:SS.US0 TZH:TZM'). 
> Unfortunately(?), the server lives at time zone UTC, while we need to convert 
> to both UTC and Europe/Amsterdam zones. The above always gives me +00 for the 
> TZH output, while it should be +01 now and +02 in the summer...

have you tried to use the proper time zone before you pass it to the to_char() 
function?

mkrueger=# select to_char(now(), '-MM-DD HH24:MI:SS.US0 TZH:TZM');
  to_char

 2024-01-11 16:24:21.9154740 +01:00
(1 row)

Time: 12,351 ms
mkrueger=# select to_char(now() at time zone 'UTC', '-MM-DD HH24:MI:SS.US0 
TZH:TZM');
  to_char

 2024-01-11 15:24:38.1619810 +00:00
(1 row)

Time: 2,095 ms
mkrueger=# select to_char(now() at time zone 'Europe/Amsterdam', '-MM-DD 
HH24:MI:SS.US0 TZH:TZM');
  to_char

 2024-01-11 16:24:52.8736860 +00:00
(1 row)


At least it seems to do what you need.

Regards,
Michael

> 
> I'm dealing with a data virtualisation system (TIBCO TDV) here that connects 
> different types of data-sources, among which is an MS SQL database with said 
> type. The virtualisation software uses PostgreSQL (14.10 on Ubuntu Linux 
> 22.04) for caching data. TDV doesn't understand this datetimeoffset type and 
> treats it internally as a VARCHAR(34) - hence the string output - which is 
> obviously kind of hard to work with for aggregations and such.
> 
> However, in TDV we can create a translation between TDV functions that accept 
> a timestamp type and a time zone name with a translation to native PostgreSQL 
> functions, operands and whatnot. That's what I'm looking for.
> It currently have this:
> ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
> ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)
> 
> 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 transaction (not sufficiently isolated to my tastes), while
> 2). The second function has the benefit that it doesn't leak the time zone 
> change, but has as drawback that the time zone is now hardcoded into the 
> function definition, while
> 3). Both functions need to be created in the caching database before we can 
> use them, while we have several environments where they would apply (DEV, 
> pre-PROD, PROD).
> 
> /* Based this one on a stackoverflow post */
> create or replace function ciscache.ToDatetimeOffset(ts_ timestamptz, tz_ 
> text)
> returns varchar(34)
> language plpgsql
> as $$
> begin
>   perform set_config('timezone', tz_, true /* local */);
>   return to_char(ts_, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
> end;
> $$;
> 
> create or replace function ciscache.ToDatetimeOffsetNL(ts_ timestamptz)
> returns varchar(34)
> language plpgsql
> set timezone to 'Europe/Amsterdam'
> as $$
> begin
>   return to_char(ts_, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
> end;
> $$;
> 
> Is there a way to do this without functions, or if not, at least without 
> having to hard-code the time zone or leaking the time zone change to other 
> calls within the same transaction?
> 
> Any suggestions much appreciated.
>  
> Groet,
>  
> Alban Hertroijs
> Data engineer ∙ NieuweStroom
> aanwezig ma t/m vr, di tot 13:30 uur
>  
>  
> www.nieuwestroom.nl 
> Kijk gratis terug: webinar Dynamische energie is de toekomst 
> 
> 
> PS We hebben een nieuwe huisstijl en website! Met ons 10 jarige bestaan, 
> trokken we een nieuwe jas aan.

Aw: Time zone offset in to_char()

[Karsten Hilbert]

> > 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 transaction (not sufficiently isolated to my tastes), while
> > 2). The second function has the benefit that it doesn't leak the time zone 
> > change, but has as drawback that the time zone is now hardcoded into the 
> > function definition, while
> > 3). Both functions need to be created in the caching database before we can 
> > use them, while we have several environments where they would apply (DEV, 
> > pre-PROD, PROD).
>
> Would a function that dispatches its calls to a suitable array of hard-coded 
> functions based on an IN parameter help any ?

Without testing the idea - could it even generate the hardcoded function as 
needed, based on the parameter, and then run it ?

Karsten

Aw: Time zone offset in to_char()

[Karsten Hilbert]

> 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 transaction (not sufficiently isolated to my tastes), while
> 2). The second function has the benefit that it doesn't leak the time zone 
> change, but has as drawback that the time zone is now hardcoded into the 
> function definition, while
> 3). Both functions need to be created in the caching database before we can 
> use them, while we have several environments where they would apply (DEV, 
> pre-PROD, PROD).

Would a function that dispatches its calls to a suitable array of hard-coded 
functions based on an IN parameter help any ?

Karsten

Time zone offset in to_char()

[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 
to_char(ts, '-MM-DD HH24:MI:SS.US0 TZH:TZM'). Unfortunately(?), the server 
lives at time zone UTC, while we need to convert to both UTC and 
Europe/Amsterdam zones. The above always gives me +00 for the TZH output, while 
it should be +01 now and +02 in the summer...

I'm dealing with a data virtualisation system (TIBCO TDV) here that connects 
different types of data-sources, among which is an MS SQL database with said 
type. The virtualisation software uses PostgreSQL (14.10 on Ubuntu Linux 22.04) 
for caching data. TDV doesn't understand this datetimeoffset type and treats it 
internally as a VARCHAR(34) - hence the string output - which is obviously kind 
of hard to work with for aggregations and such.

However, in TDV we can create a translation between TDV functions that accept a 
timestamp type and a time zone name with a translation to native PostgreSQL 
functions, operands and whatnot. That's what I'm looking for.
It currently have this:
ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)

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 transaction (not sufficiently isolated to my tastes), while
2). The second function has the benefit that it doesn't leak the time zone 
change, but has as drawback that the time zone is now hardcoded into the 
function definition, while
3). Both functions need to be created in the caching database before we can use 
them, while we have several environments where they would apply (DEV, pre-PROD, 
PROD).

/* Based this one on a stackoverflow post */
create or replace function ciscache.ToDatetimeOffset(ts_ timestamptz, tz_ text)
returns varchar(34)
language plpgsql
as $$
begin
  perform set_config('timezone', tz_, true /* local */);
  return to_char(ts_, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$$;

create or replace function ciscache.ToDatetimeOffsetNL(ts_ timestamptz)
returns varchar(34)
language plpgsql
set timezone to 'Europe/Amsterdam'
as $$
begin
  return to_char(ts_, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$$;

Is there a way to do this without functions, or if not, at least without having 
to hard-code the time zone or leaking the time zone change to other calls 
within the same transaction?

Any suggestions much appreciated.



Groet,



Alban Hertroijs

Data engineer ∙ NieuweStroom
aanwezig ma t/m vr, di tot 13:30 uur



[Afbeelding met tekst, buiten, teken  Automatisch gegenereerde beschrijving]



www.nieuwestroom.nl

Kijk gratis terug: webinar Dynamische energie is de 
toekomst

PS We hebben een nieuwe huisstijl en website! Met ons 10 jarige bestaan, 
trokken we een nieuwe jas aan.

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

[Laurenz Albe]

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:
> hostall,replication replprimary-database-server   
> scram-sha-256
> hostall,replication replstandby-database-server   
> scram-sha-256
> 
> myrecovery.conf on the standby:
> primary_conninfo = 'host=primary-database-server port=5432 user=repl 
> application_name=standby-server-name'
> recovery_target_timeline = 'latest'
> primary_slot_name = 'standby_replication_slot'
> 
> .pgpass on the standby:
> # hostname:port:database:username:password
> *:*:replication:repl:repl_user_password

make sure that .pgpass is in the home directory of the PostgreSQL user and has
restrictive permissions (0600).  Try using it with a manual connection attempt
via "psql".

Yours,
Laurenz Albe