Re: strange behavior of pg_hba.conf file

On Wed, 2023-11-22 at 23:33 +0530, Atul Kumar wrote: > Please can you share any command  for due diligence whether ip is resolved to > ipv6 ?. Not a lot of diligence is due to figure out that you can use ping localhost Yours, Laurenz Albe

Re: strange behavior of pg_hba.conf file

On 11/22/23 10:01 AM, Adrian Klaver wrote: On 11/22/23 9:55 AM, Andreas Kretschmer wrote: Am 22.11.23 um 18:44 schrieb Atul Kumar: I am giving this command psql -d postgres -U postgres -p 5432 -h localhost Then only I get that error. so localhost resolved to an IPv6 - address ... Yeah

Re: strange behavior of pg_hba.conf file

On 11/22/23 10:03 AM, Atul Kumar wrote: Please can you share any command  for due diligence whether ip is resolved to ipv6 ?. This: psql -d postgres -U postgres -p 5432 -h localhost where pretty sure /etc/hosts is resolving localhost --> ::1 On Wed, Nov 22, 2023 at 11:25 PM Andreas Kre

Re: strange behavior of pg_hba.conf file

Please can you share any command for due diligence whether ip is resolved to ipv6 ?. On Wed, Nov 22, 2023 at 11:25 PM Andreas Kretschmer wrote: > > > Am 22.11.23 um 18:44 schrieb Atul Kumar: > > I am giving this command > > psql -d postgres -U postgres -p 5432 -h localhost > > Then only I get t

Re: strange behavior of pg_hba.conf file

On 11/22/23 9:55 AM, Andreas Kretschmer wrote: Am 22.11.23 um 18:44 schrieb Atul Kumar: I am giving this command psql -d postgres -U postgres -p 5432 -h localhost Then only I get that error. so localhost resolved to an IPv6 - address ... Yeah, you should take a look at: /etc/hosts In

Re: strange behavior of pg_hba.conf file

Am 22.11.23 um 18:44 schrieb Atul Kumar: I am giving this command psql -d postgres -U postgres -p 5432 -h localhost Then only I get that error. so localhost resolved to an IPv6 - address ... but when I  pass ip or hostname of the local server then I don't get such error message 1. psql

Re: strange behavior of pg_hba.conf file

I am giving this command psql -d postgres -U postgres -p 5432 -h localhost Then only I get that error. but when I pass ip or hostname of the local server then I don't get such error message 1. psql -d postgres -U postgres -p 5432 -h 2. psql -d postgres -U postgres -p 5432 -h I don;t get that

Re: strange behavior of pg_hba.conf file

On 11/22/23 09:03, Atul Kumar wrote: The entries that I changed were to replace the md5 with scram-sha-256 and remove unnecessary remote IPs. FYI from: https://www.postgresql.org/docs/current/auth-password.html md5 The method md5 uses a custom less secure challenge-response mechanism. I

Re: strange behavior of pg_hba.conf file

The error message is EXPLICIT, and DOES NOT LIE. Either someone removed the ::1 entry, or you're now using IPv6. On Wed, Nov 22, 2023 at 12:03 PM Atul Kumar wrote: > The entries that I changed were to replace the md5 with scram-sha-256 and > remove unnecessary remote IPs. > > But it has nothing

Re: strange behavior of pg_hba.conf file

The entries that I changed were to replace the md5 with scram-sha-256 and remove unnecessary remote IPs. But it has nothing to do with connecting the server locally with "psql -d postgres -U postgres -h localhost" But when I try to connect it locally I get this error. So it is related to local co

Re: strange behavior of pg_hba.conf file

On Wed, Nov 22, 2023 at 11:22 AM Atul Kumar wrote: > Hi, > > I have postgres 12 running in centos 7, recently I changed the > authentication of entries of pg_hba.conf to scram-sh-256 for localhost. > > I think you changed something else, at the same time. > Since then I have started getting the

Re: strange behavior of pg_hba.conf file

On 11/22/23 08:21, Atul Kumar wrote: Hi, I have postgres 12 running in centos 7, recently I changed the authentication of entries of pg_hba.conf to scram-sh-256 for localhost. Since then I have started getting the below error: no pg_hba.conf entry for host "::1", user "postgres", database "p

Re: strange behavior of pg_hba.conf file

Am 22.11.23 um 17:21 schrieb Atul Kumar: Since then I have started getting the below error: no pg_hba.conf entry for host "::1", user "postgres", database "postgres What I am missing here, please suggest. that's sounds like an issue with IPv6. Do you use it? Disable it or add an en

Re: strange behavior of .pgpass file

On Wed, 2023-06-21 at 00:29 +0530, Atul Kumar wrote: > I found some strange behaviour of postgres superuser password in my existing > cluster, below is the basic outline of the setup. > 1. version - postgres 12 > 2. replication - streaming replication async > 3. OS- centos7 > 4. One Master, One St

Re: strange behavior of .pgpass file

On 6/20/23 13:32, Atul Kumar wrote: Please reply to list also. Ccing list. Th both pgpass files contains details as below: *:5432:*:postgres: There are no other lines in the file? More information below. I couldn't find anything wrong there in pgpass. and if the issue would have been wi

Re: strange behavior of .pgpass file

Please suggest. On Wed, 21 Jun 2023, 02:02 Atul Kumar, wrote: > Th both pgpass files contains details as below: > > *:5432:*:postgres: > > I couldn't find anything wrong there in pgpass. > > and if the issue would have been with .pgpass file only then I would not > have got the same error with -

Re: strange behavior of .pgpass file

On 6/20/23 11:59, Atul Kumar wrote: Hi, I found some strange behaviour of postgres superuser password in my existing cluster, below is the basic outline of the setup. 1. version - postgres 12 2. replication - streaming replication async 3. OS- centos7 4. One Master, One Standby I have identic

Re: Strange behavior between timestamp and date comparison

On 7/23/22 03:04, Ludwig Isaac Lim wrote: Hello: Below is a sample case that exhibits a behavior that I can't explain: -- create the table create table ts (t timestamp without time zone); -- populate insert into ts(t) values ('2022-07-16 00:22:06.974000'); insert into ts(t) values ('2022-07-16

Re: Strange behavior between timestamp and date comparison

On 23/07/22, Ludwig Isaac Lim (ludz_...@yahoo.com) wrote: > Below is a sample case that exhibits a behavior that I can't explain: > > -- create the table > create table ts (t timestamp without time zone); > > -- populate > insert into ts(t) values ('2022-07-16 00:22:06.974000'); > insert into ts(

Re: strange behavior of WAL files

this is a very interesting case.  Atul keep us posted.

Re: strange behavior of WAL files

Atul Kumar writes: > Please check my findings below > older > -rw--- 1 enterprisedb enterprisedb 16777216 Jun 2 02:47 > 000136CF00A4 > -rw--- 1 enterprisedb enterprisedb 16777216 Jun 2 02:45 > 000136CF00A3 > -rw--- 1 enterprisedb enterprisedb 16777216 Jun 2

Re: strange behavior of WAL files

it gets cleaned up for me. turn archiving on, simulate success using /bin/true turn archiving off, simulate success using /bin/false generate wals by some DMLS. postgres@go:~/pgsql/data/pg_wal$ grep wal_size ../postgresql.conf max_wal_size = 100MB min_wal_size = 80MB postgres@go:~/pgsql/data/pg_

Re: strange behavior of WAL files

hi Tom, Please check my findings below older -rw--- 1 enterprisedb enterprisedb 16777216 Jun 2 02:47 000136CF00A4 -rw--- 1 enterprisedb enterprisedb 16777216 Jun 2 02:45 000136CF00A3 -rw--- 1 enterprisedb enterprisedb 16777216 Jun 2 02:44 000136CF000

Re: strange behavior of WAL files

Hi Jehan, Just to add little more info about this issue is : We have set value 4000 for parameter wal_keep_segments. So is there any chance that after a certain number of WAL files, postgres will start recycling the WAL with same name ? Please share your valuable suggestion. Regards. Atul

Re: strange behavior of WAL files

Atul Kumar writes: > once old WAL files of pg_xlog directory are archived in > '/nfslogs/wal/' directory then these WAL files are getting generated > with the same name in pg_xlog directory. Are you sure you are describing the behavior accurately? What I would expect to happen, once an old WAL f

Re: strange behavior of WAL files

I will try to simulate this and see if i can reproduce it, currently in between difficult interviews where i have little hope :) PostgreSQL WAL Retention and Clean Up: pg_archivecleanup - Percona Database Performance Blog

Re: strange behavior of WAL files

I have not seen this, so cannot comment, but when I am trying to simulate i do not see issues. One thing to note, It seems your wal is on nfs mount , can you rule out any nfs errors if it is nfs. On Fri, Jun 4, 2021, 6:24 PM Atul Kumar wrote: > Hi, > > > archive_command is 'cp %p /nfslogs/wal/%

Re: strange behavior of WAL files

Hi, archive_command is 'cp %p /nfslogs/wal/%f' and no, we are not removing anything from pg_xlog directory. once old WAL files of pg_xlog directory are archived in '/nfslogs/wal/' directory then these WAL files are getting generated with the same name in pg_xlog directory. my query is Why is t

Re: strange behavior of WAL files

On Fri, 4 Jun 2021 15:39:30 +0530 Atul Kumar wrote: > HI, > > We have a centos 6 enviornment where postgres 9.6 is running on it. > > We have strange behavior of WAL files of pg_xlog directory > > As we have set archive_command to archive WAL files at different > location and the archive_comma

Re: Strange behavior of function date_trunc

David, On 06.05.2021 17:28, David G. Johnston wrote: On Thu, May 6, 2021 at 6:44 AM Tom Lane > wrote: This case is the reason we invented the "stable" attribute to begin with.  People have since misinterpreted it as authorizing caching of function results

Re: Strange behavior of function date_trunc

I will try to summarize what was said before. We have discussed the details of executing STABLE functions in queries of the form: SELECT * FROM t WHERE col oper stable_func(); * Checking STABLE does not guarantee that the function will be executed only once. If the table is scanned sequential

Re: Strange behavior of function date_trunc

Pavel Luzanov writes: > One thing remains unclear. > Why, if a scalar subquery is used to materialize the function value(even > constant), then an inefficient index scan is chosen: The scalar subquery prevents the planner from seeing the actual comparison value, so it falls back to a default sel

Re: Strange behavior of function date_trunc

Hello, On 06.05.2021 16:44, Tom Lane wrote: Pavel Luzanov writes: Does having an index allow the function value to be cached? For an indexscan, the comparison value is evaluated once and used to search the index. The point of the "stable" marking is actually to promise that this will give th

Re: Strange behavior of function date_trunc

On Thu, May 6, 2021 at 6:44 AM Tom Lane wrote: > This case is the reason we invented the "stable" attribute to begin > with. People have since misinterpreted it as authorizing caching of > function results, but that's not what it was intended for. > > This is a good paragraph...if something like

Re: Strange behavior of function date_trunc

Pavel Luzanov writes: > Does having an index allow the function value to be cached? For an indexscan, the comparison value is evaluated once and used to search the index. The point of the "stable" marking is actually to promise that this will give the same result as the naive interpretation of a

Re: Strange behavior of function date_trunc

Hello, On 05.05.2021 16:55, Tomas Vondra wrote: Well, it'd not like date_trunc is executed for each row while now() is executed only once. The functions are executed for each row in both cases... A couple more experiments. Since I can't to track usage of system functions, I decided to play wit

Re: Strange behavior of function date_trunc

On 05.05.2021 17:11, Tom Lane wrote: Tomas Vondra writes: On 5/5/21 3:23 PM, Pavel Luzanov wrote: It is very likely that the date_trunc function in the following example is executed for each line of the query. Although it marked as a STABLE and could only be called once. It could, but that's

Re: Strange behavior of function date_trunc

Hello, On 05.05.2021 16:55, Tomas Vondra wrote: Well, it'd not like date_trunc is executed for each row while now() is executed only once. The functions are executed for each row in both cases, but now() is simply much cheaper - it just returns a value that is already calculated, while date_tr

Re: Strange behavior of function date_trunc

Tomas Vondra writes: > On 5/5/21 3:23 PM, Pavel Luzanov wrote: >> It is very likely that the date_trunc function in the following example >> is executed for each line of the query. Although it marked as a STABLE >> and could only be called once. > It could, but that's just an option - the datab

Re: Strange behavior of function date_trunc

On 5/5/21 3:23 PM, Pavel Luzanov wrote: Hello, It is very likely that the date_trunc function in the following example is executed for each line of the query. Although it marked as a STABLE and could only be called once. It could, but that's just an option - the database may do that, bu

Re: Strange behavior

"David G. Johnston" writes: > I am pondering trying to get the FAQ entry incorporated into the actual > documentation. Not sure how much it'd help, but we have warnings against mistakes that are far less common than this one, so sure why not. Taking a quick gander at the docs, it seems like the

Re: Strange behavior

On Sat, Oct 10, 2020 at 9:13 AM Olivier Leprêtre wrote: This has nothing to do with pgAdmin, or any other client interface. In other words, a wrong query returns a valid result. This happens because > v1 is a column from test1, (select vx from test2) will return an error as > expected. > https:

Re: Strange behavior

Olivier: On Sat, Oct 10, 2020 at 6:13 PM Olivier Leprêtre wrote: > I’m surprised by this behavior I noticed in pgadmin3 and postgresql 9.6 ... > select v1 from test1 where v1 not in (select v1 from test2) This is called a correlated subquery ( google and search for it, it is even in wikipedia

Re: Strange behavior of the random() function

> 26 сент. 2018 г., в 6:35, Tom Lane написал(а): > > r.zhar...@postgrespro.ru writes: >> Can anybody explain me that strange behavior? > > It's a squishiness in the SQL language, if you ask me. Consider this > simplified query: > > select random() from generate_series(1, 3) order by random()

Re: Strange behavior of the random() function

r.zhar...@postgrespro.ru writes: > Can anybody explain me that strange behavior? It's a squishiness in the SQL language, if you ask me. Consider this simplified query: select random() from generate_series(1, 3) order by random(); Would you expect the output of this query to appear ordered, or n

Re: Strange behavior with missing column in SQL function

> CREATE OR REPLACE FUNCTION myfunction(myrow mytable) > RETURNS INTEGER AS $$ > SELECT myrow.c + myrow.b FROM myrow; > $$ LANGUAGE sql; > where "myrow" is a table with a different set of column names from > "mytable". The existing behavior for that is to seek the column name > in "myrow" (th

Re: Strange behavior with missing column in SQL function

Marcelo Lacerda writes: > Here's the code that reproduces the behavior: > http://paste.debian.net/1035412/ For the archives' sake, the issue of concern here is what error message to throw for CREATE OR REPLACE FUNCTION myfunction(myrow mytable) RETURNS INTEGER AS $$ SELECT myrow.c + myrow.b;