Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 5:56 PM Adrian Klaver wrote: > On 4/22/24 14:35, Ron Johnson wrote: > > > > > > > On Stack Exchange, I've got a question on how to determine when > > to run > > > CLUSTER. It ties in strongly with this thread.. > > > > > > > And the link is? >

Re: issue with reading hostname

On Mon, Apr 22, 2024 at 2:54 PM Atul Kumar wrote: > I mean, Once I change the hostname then how will the socket read the new > hostname ? Does it require a postgres service restart ? > >> >> -h doesn't strictly mean hostname (the wording choice here does seem problematic), rather it is simply a

Re: issue with reading hostname

On 4/22/24 14:54, Atul Kumar wrote: I mean, Once I change the hostname then how will the socket read the new hostname ? Does it require a postgres service restart ? The host name of the machine? On Tue, Apr 23, 2024 at 3:19 AM Adrian Klaver > wrote:

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 14:35, Ron Johnson wrote: > > On Stack Exchange, I've got a question on how to determine when to run > CLUSTER.  It ties in strongly with this thread.. > And the link is? It should have been the initial question of this thread and it explains what you

Re: issue with reading hostname

On Mon, Apr 22, 2024 at 1:14 PM Atul Kumar wrote: > > so below are my questions: > > 1. Is the psql client reading the socket file that resides in the /tmp > directory to fetch the hostname ? > > 2. I saw the socket file in /tmp and it is empty. Then how is the psql > client still reading the

Re: issue with reading hostname

I mean, Once I change the hostname then how will the socket read the new hostname ? Does it require a postgres service restart ? On Tue, Apr 23, 2024 at 3:19 AM Adrian Klaver wrote: > On 4/22/24 14:37, Atul Kumar wrote: > > Can we edit the socket to change the hostname in it ? > > On Ubuntu

Re: issue with reading hostname

On 4/22/24 14:37, Atul Kumar wrote: Can we edit the socket to change the hostname in it ? On Ubuntu 22.04 install, given: srwxrwxrwx 1 postgres postgres0 Apr 22 14:01 .s.PGSQL.5432= -rw--- 1 postgres postgres 68 Apr 22 14:01 .s.PGSQL.5432.lock The contents of

Re: issue with reading hostname

There's no hostname in a socket directory. Do you mean :"change the socket name from /tmp or /var/run/postgresql to something else?" If so, then https://www.postgresql.org/docs/16/runtime-config-connection.html mentions sockets. On Mon, Apr 22, 2024 at 5:37 PM Atul Kumar wrote: > Can we edit

Re: Performance degradation after upgrading from 9.5 to 14

On Tue, 23 Apr 2024 at 07:01, Олександр Янін wrote: > Try setting enable_memoize to off. > Our practice has shown that enabling this parameter by default often resulted > in less than optimal query plans in the cache. It would be good to see a thread opened with details on this. I understand

Re: issue with reading hostname

Can we edit the socket to change the hostname in it ? Regards. On Tue, Apr 23, 2024 at 2:41 AM Ron Johnson wrote: > On Mon, Apr 22, 2024 at 4:14 PM Atul Kumar wrote: > >> Hi, >> >> I have postgresql version 15 running on centos7. >> >> I have below query that reads hostname from /tmp

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 5:03 PM Adrian Klaver wrote: > On 4/22/24 13:59, Ron Johnson wrote: > > On Mon, Apr 22, 2024 at 4:21 PM Adrian Klaver > > wrote: > > [snip] > > > > Which gets us back to your comment upstream: > > > > "What the VACUUM docs

Re: issue with reading hostname

On Mon, Apr 22, 2024 at 4:14 PM Atul Kumar wrote: > Hi, > > I have postgresql version 15 running on centos7. > > I have below query that reads hostname from /tmp directory: > > psql postgres -A -t -p 5432 -h /tmp/ -c 'SELECT pg_is_in_recovery();' > If you installed from the PGDG repository

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 13:59, Ron Johnson wrote: On Mon, Apr 22, 2024 at 4:21 PM Adrian Klaver > wrote: [snip] Which gets us back to your comment upstream: "What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the PK, if the PK is a sequence

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 4:21 PM Adrian Klaver wrote: [snip] > Which gets us back to your comment upstream: > > "What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the > PK, if the PK is a sequence (whether that be an actual sequence, or a > timestamp or something else that grows

Re: issue with reading hostname

On 4/22/24 13:13, Atul Kumar wrote: Hi, I have postgresql  version 15 running on centos7. I have below query that reads hostname from /tmp directory: psql postgres -A -t -p 5432 -h /tmp/ -c 'SELECT pg_is_in_recovery();' so below are my questions: 1. Is the psql client reading the socket

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 12:51, Ron Johnson wrote: On Mon, Apr 22, 2024 at 3:14 PM Adrian Klaver > wrote: 1) If they are already in enough of a PK order that the CLUSTER time vs VACUUM FULL time would not be material as there is not much or any sorting to

issue with reading hostname

Hi, I have postgresql version 15 running on centos7. I have below query that reads hostname from /tmp directory: psql postgres -A -t -p 5432 -h /tmp/ -c 'SELECT pg_is_in_recovery();' so below are my questions: 1. Is the psql client reading the socket file that resides in the /tmp directory

Re: altering a column to to make it generated

"David G. Johnston" writes: > On Mon, Apr 22, 2024 at 12:42 PM Celia McInnis > wrote: >> Can I alter a table column to now make it generated? I tried this >> unsuccessfully: > I looked at all of the "alter table ... alter column" commands listed here: >

Re: altering a column to to make it generated

On 4/22/24 12:42, Celia McInnis wrote: Can I alter a table column to now make it generated? I tried this unsuccessfully: create temp table tmp1 as select 15::numeric(6,1) as distance,'24:30'::interval,0::numeric(7,3) as avgspd; alter table tmp1 alter column avgspd type numeric(7,3) generated

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 3:14 PM Adrian Klaver wrote: > > > On 4/22/24 11:45 AM, Ron Johnson wrote: > > On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston > > mailto:david.g.johns...@gmail.com>> wrote: > > > > > > > > On Mon, Apr 22, 2024, 08:37 Ron Johnson >

Re: altering a column to to make it generated

On Mon, Apr 22, 2024 at 12:42 PM Celia McInnis wrote: > Can I alter a table column to now make it generated? I tried this > unsuccessfully: > I looked at all of the "alter table ... alter column" commands listed here: https://www.postgresql.org/docs/current/sql-altertable.html And none seem

altering a column to to make it generated

Can I alter a table column to now make it generated? I tried this unsuccessfully: create temp table tmp1 as select 15::numeric(6,1) as distance,'24:30'::interval,0::numeric(7,3) as avgspd; alter table tmp1 alter column avgspd type numeric(7,3) generated always as ((3600.*distance)/EXTRACT (EPOCH

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 11:45 AM, Ron Johnson wrote: On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Apr 22, 2024, 08:37 Ron Johnson mailto:ronljohnso...@gmail.com>> wrote: On Mon, Apr 22, 2024 at 10:25 AM Tom Lane

Re: Performance degradation after upgrading from 9.5 to 14

Try setting enable_memoize to off. Our practice has shown that enabling this parameter by default often resulted in less than optimal query plans in the cache. ср, 17 апр. 2024 г. в 20:13, Johnathan Tiamoh : > Hello, > > > I performed an upgrade from postgresql-9.5 to postgresql-14 and the >

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > > > On Mon, Apr 22, 2024, 08:37 Ron Johnson wrote: > >> On Mon, Apr 22, 2024 at 10:25 AM Tom Lane wrote: >> >>> Marcos Pegoraro writes: >>> > But wouldn't it be good that VACUUM FULL uses that index

Re: CLUSTER vs. VACUUM FULL

Em seg., 22 de abr. de 2024 às 11:25, Tom Lane escreveu: > No ... what would be the difference then Well, I think if a cluster index was defined sometime in a table, it should be respected for next commands, including VACUUM FULL. If I want to come back to PK or any other index I would use

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024, 08:37 Ron Johnson wrote: > On Mon, Apr 22, 2024 at 10:25 AM Tom Lane wrote: > >> Marcos Pegoraro writes: >> > But wouldn't it be good that VACUUM FULL uses that index defined by >> > Cluster, if it exists ? >> >> No ... what would be the difference then? >> > > What the

Re: adding a generated column to a table?

On 4/22/24 09:05, Celia McInnis wrote: If I have a table containing a date field, say: create temporary table tmp1 as select now()::date as evtdate; SELECT 1 select DATE_PART('year', evtdate)::integer as year from tmp1;  year --  2024 (1 row) Is there some way of doing something like the

adding a generated column to a table?

If I have a table containing a date field, say: create temporary table tmp1 as select now()::date as evtdate; SELECT 1 select DATE_PART('year', evtdate)::integer as year from tmp1; year -- 2024 (1 row) Is there some way of doing something like the following?: alter table tmp1 add column

Re: CLUSTER vs. VACUUM FULL

On 4/22/24 08:37, Ron Johnson wrote: On Mon, Apr 22, 2024 at 10:25 AM Tom Lane > wrote: Marcos Pegoraro mailto:mar...@f10.com.br>> writes: > But wouldn't it be good that VACUUM FULL uses that index defined by > Cluster, if it exists ? No ... what

Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 10:25 AM Tom Lane wrote: > Marcos Pegoraro writes: > > But wouldn't it be good that VACUUM FULL uses that index defined by > > Cluster, if it exists ? > > No ... what would be the difference then? > What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the

Re: CLUSTER vs. VACUUM FULL

Marcos Pegoraro writes: > But wouldn't it be good that VACUUM FULL uses that index defined by > Cluster, if it exists ? No ... what would be the difference then? regards, tom lane

Re: Failing streaming replication on PostgreSQL 14

Hi, facing the same situation again, but this time, analyzing the wal with xxd shows a different pattern. I had no blocks of . The output of pg_waldump is: pg_waldump: fatal: error in WAL record at 11C/93F9FF70: invalid magic number in log segment 0001011C0093, offset

Re: CLUSTER vs. VACUUM FULL

Em dom., 21 de abr. de 2024 às 22:35, David Rowley escreveu: > > Both VACUUM FULL and CLUSTER go through a very similar code path. Both > use cluster_rel(). VACUUM FULL just won't make use of an existing > index to provide presorted input or perform a sort, whereas CLUSTER > will attempt to

Re: Not able to grant access on pg_signal_backend on azure flexible server

No luck. On Mon, 22 Apr, 2024, 11:42 am David G. Johnston, < david.g.johns...@gmail.com> wrote: > On Sun, Apr 21, 2024 at 10:49 PM Saksham Joshi > wrote: > >> We have tried granting it with our admin user also but no avail. >> > > This is how v16+ works in a community installation: > >

Re: Not able to grant access on pg_signal_backend on azure flexible server

On Sun, Apr 21, 2024 at 10:49 PM Saksham Joshi wrote: > We have tried granting it with our admin user also but no avail. > This is how v16+ works in a community installation: postgres=1 # create role cr createrole; CREATE ROLE postgres 2=# set role cr; SET postgres 2=> create role otherrole;