Re: is there an immutable function to switch from date to character?
Sorry I pasted in the wrong code, I had wanted a column with the character version of the date (ie., -Mon-DD). Steve Baldwin's hack pointed me in the right direction. Here is the example: create temporary table junk as select now()::date as evtdate; SELECT 1 alter table junk add column chardate text GENERATED ALWAYS AS (cmm_date_to_char(evtdate)) STORED; select * from junk; evtdate | chardate +- 2024-04-24 | 2024-Apr-24 (1 row) where cmm_date_to_char is defined as: create or replace function cmm_date_to_char(i_date in date) returns text immutable language sql as $$ select to _char(i_date, '-Mon-DD') $$; Thanks! On Wed, Apr 24, 2024 at 5:54 PM Tom Lane wrote: > Celia McInnis writes: > > create temporary table junk as select now()::date as evtdate; > > alter table junk add column chardate text GENERATED ALWAYS AS > > (to_char(evtdate,'-Mon-DD')) STORED; > > > ERROR: generation expression is not immutable > > Probably not; I think all the available conversion functions > respond to some combination of datestyle, lc_time, and timezone > settings. (Type date doesn't depend on timezone, but that keeps you > from using anything that shares functionality with timestamptz ... > and your to_char call promotes the date to timestamptz.) > > I find your example not terribly compelling. Why expend storage > space on such a column? > > If you're bound and determined to do it, writing a wrapper > function that's labeled immutable should work: > > =# create function mytochar(date) returns text > strict immutable parallel safe > as $$ begin return to_char($1::timestamp, '-Mon-DD'); end $$ > language plpgsql; > CREATE FUNCTION > =# alter table junk add column chardate text GENERATED ALWAYS AS > (mytochar(evtdate)) STORED; > ALTER TABLE > > It's on you to be sure that the function actually is immutable, > or at least immutable enough for your use-case. I believe my > example is pretty safe: neither datestyle nor timezone should > affect the timestamp-without-timezone variant of to_char(), > and this particular format string doesn't depend on lc_time. > > regards, tom lane >
Re: is there an immutable function to switch from date to character?
Celia McInnis writes: > create temporary table junk as select now()::date as evtdate; > alter table junk add column chardate text GENERATED ALWAYS AS > (to_char(evtdate,'-Mon-DD')) STORED; > ERROR: generation expression is not immutable Probably not; I think all the available conversion functions respond to some combination of datestyle, lc_time, and timezone settings. (Type date doesn't depend on timezone, but that keeps you from using anything that shares functionality with timestamptz ... and your to_char call promotes the date to timestamptz.) I find your example not terribly compelling. Why expend storage space on such a column? If you're bound and determined to do it, writing a wrapper function that's labeled immutable should work: =# create function mytochar(date) returns text strict immutable parallel safe as $$ begin return to_char($1::timestamp, '-Mon-DD'); end $$ language plpgsql; CREATE FUNCTION =# alter table junk add column chardate text GENERATED ALWAYS AS (mytochar(evtdate)) STORED; ALTER TABLE It's on you to be sure that the function actually is immutable, or at least immutable enough for your use-case. I believe my example is pretty safe: neither datestyle nor timezone should affect the timestamp-without-timezone variant of to_char(), and this particular format string doesn't depend on lc_time. regards, tom lane
Sql scripts execution
Hi All, What is the generally used open source solution for deploying dml and ddl scripts for monthly release on postgres rds? Can we use github actions to perform the same Thanks, Arun
Re: is there an immutable function to switch from date to character?
On Thu, Apr 25, 2024 at 7:31 AM Celia McInnis wrote: > create temporary table junk as select now()::date as evtdate; > > alter table junk add column chardate text GENERATED ALWAYS AS > (to_char(evtdate,'-Mon-DD')) STORED; > > ERROR: generation expression is not immutable > > Maybe this is a hack but.. b2bcreditonline=# create temporary table junk as select now()::date as evtdate; SELECT 1 b2bcreditonline=# alter table junk add column chardate text GENERATED ALWAYS AS (to_char(evtdate,'-Mon-DD')) STORED; ERROR: generation expression is not immutable b2bcreditonline=# create or replace function date_to_text(i_date in date) returns text immutable language sql as $$ select to_char(i_date, '-MM-DD') $$; CREATE FUNCTION b2bcreditonline=# alter table junk add column chardate text GENERATED ALWAYS AS (date_to_text(evtdate)) STORED; ALTER TABLE b2bcreditonline=# select * from junk; evtdate | chardate + 2024-04-24 | 2024-04-24 (1 row)
is there an immutable function to switch from date to character?
create temporary table junk as select now()::date as evtdate; alter table junk add column chardate text GENERATED ALWAYS AS (to_char(evtdate,'-Mon-DD')) STORED; ERROR: generation expression is not immutable
Re: Backup_Long Running
On 4/24/24 00:03, jaya kumar wrote: Hi Team, Production database Backup is running very long hours. Any option to reduce backup time? Kindly advise me. Hardware specifications? Network specifications? The actual pg_basebackup command used? Server(s) user load? DB size: 793 GB We are taking pg_basebackup backup. Backup File size=613G Backup Running Duration: 8 Hours -- Thanks & Regards, Jayakumar.S +91-9840864439. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Backup_Long Running
PgBackRest is in the PGDG repositories (RHEL & Debian). The documentation is thorough, and discoverable via Google, On Wed, Apr 24, 2024 at 8:30 AM jaya kumar wrote: > Thanks for your update. Can you have any link or document to configure L0 > & L1 backup using pgbackrest tool. Also share the pgbackrest installation > method. > > On Wed, Apr 24, 2024 at 2:50 PM Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: > >> >> On Wed, Apr 24, 2024, 12:33 PM jaya kumar wrote: >> >>> Hi Team, >>> >>> >>> >>> Production database Backup is running very long hours. Any option to >>> reduce backup time? Kindly advise me. >>> >>> >>> >>> DB size: 793 GB >>> >>> >>> >>> We are taking pg_basebackup backup. >>> >>> >> do you see network saturation, io saturation ? >> generally faster hardware i.e striped and or nvme disks along with a >> robust network link and capacity should help get the backup done quickly. >> where are you taking the backup from? is the server busy doing other work >> or it is a dedicated machine for backups ? >> basically monitor for resource saturation, if all looks good, we could >> take basebackup of a 10tb db in 8 hours, and in another case on a slow >> remote storage, backup of 2tb took 1 day. >> >> now, pgbackrest can speedup backup processes by spawning more workers for >> archiving and stuff. we have taken backup on nvme disks striped of 28tb in >> 3 hours, bare metals servers with powerful cpu. >> >> so , it's hardware else switch to pgbackrest which can take >> incremental/differential/full backups. >> there are other tools too, I used only these two. >> >>> >>> > > -- > Thanks & Regards, > Jayakumar.S > +91-9840864439. >
Re: Backup_Long Running
Thanks for your update. Can you have any link or document to configure L0 & L1 backup using pgbackrest tool. Also share the pgbackrest installation method. On Wed, Apr 24, 2024 at 2:50 PM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > On Wed, Apr 24, 2024, 12:33 PM jaya kumar wrote: > >> Hi Team, >> >> >> >> Production database Backup is running very long hours. Any option to >> reduce backup time? Kindly advise me. >> >> >> >> DB size: 793 GB >> >> >> >> We are taking pg_basebackup backup. >> >> > do you see network saturation, io saturation ? > generally faster hardware i.e striped and or nvme disks along with a > robust network link and capacity should help get the backup done quickly. > where are you taking the backup from? is the server busy doing other work > or it is a dedicated machine for backups ? > basically monitor for resource saturation, if all looks good, we could > take basebackup of a 10tb db in 8 hours, and in another case on a slow > remote storage, backup of 2tb took 1 day. > > now, pgbackrest can speedup backup processes by spawning more workers for > archiving and stuff. we have taken backup on nvme disks striped of 28tb in > 3 hours, bare metals servers with powerful cpu. > > so , it's hardware else switch to pgbackrest which can take > incremental/differential/full backups. > there are other tools too, I used only these two. > >> >> -- Thanks & Regards, Jayakumar.S +91-9840864439.
Re: Backup_Long Running
On Wed, Apr 24, 2024, 12:33 PM jaya kumar wrote: > Hi Team, > > > > Production database Backup is running very long hours. Any option to > reduce backup time? Kindly advise me. > > > > DB size: 793 GB > > > > We are taking pg_basebackup backup. > > do you see network saturation, io saturation ? generally faster hardware i.e striped and or nvme disks along with a robust network link and capacity should help get the backup done quickly. where are you taking the backup from? is the server busy doing other work or it is a dedicated machine for backups ? basically monitor for resource saturation, if all looks good, we could take basebackup of a 10tb db in 8 hours, and in another case on a slow remote storage, backup of 2tb took 1 day. now, pgbackrest can speedup backup processes by spawning more workers for archiving and stuff. we have taken backup on nvme disks striped of 28tb in 3 hours, bare metals servers with powerful cpu. so , it's hardware else switch to pgbackrest which can take incremental/differential/full backups. there are other tools too, I used only these two. > >
Backup_Long Running
Hi Team, Production database Backup is running very long hours. Any option to reduce backup time? Kindly advise me. DB size: 793 GB We are taking pg_basebackup backup. Backup File size=613G Backup Running Duration: 8 Hours -- Thanks & Regards, Jayakumar.S +91-9840864439.
