Re: replication strange behavior

Hi, In the master pg_hba.conf file, standby server hostname name is not being read even with the domain name, but server ip is working fine. output of /etc/hosts is given below 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdom

Re: replication strange behavior

On Thu, Nov 30, 2023 at 3:41 PM Atul Kumar wrote: > Hi, > > I have postgres 12 running in centos 7. > > I have configured streaming replication between one master and one standby > server. > > In the pg_hba.conf file of the master server, I have put the standby > server's hostname instead of IP a

replication strange behavior

Hi, I have postgres 12 running in centos 7. I have configured streaming replication between one master and one standby server. In the pg_hba.conf file of the master server, I have put the standby server's hostname instead of IP and due to which replication got broken and I started getting below

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

strange behavior of pg_hba.conf file

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 "postgres The entry of pg_hba.conf is l

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

strange behavior of .pgpass file

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 identical pgpass files on both server postgres

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(

Strange behavior between timestamp and date comparison

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 00:22:06.974000'); insert into ts(t) values

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

00136CF00A3 > -rw--- 1 enterprisedb enterprisedb 16777216 Jun 4 08:23 > 0001000036CF00A4 > > > > the file names ending with A3 and A4 are the files that got generated > with same name with the latest timestamp. > > > > So that's why I

Re: strange behavior of WAL files

enterprisedb enterprisedb 16777216 Jun 4 08:23 000136CF00A4 the file names ending with A3 and A4 are the files that got generated with same name with the latest timestamp. So that's why I called it strange behavior, please suggest your opinion. Regards, Atul On 6/4/21

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

y is this happening ? >> >> >> >> please help me with your suggestions. >> >> >> Regards. >> >> >> >> >> On 6/4/21, Jehan-Guillaume de Rorthais wrote: >> > On Fri, 4 Jun 2021 15:39:30 +0530 >> > Atul Kumar wrote:

Re: strange behavior of WAL files

> my query is Why is this happening ? > > > > please help me with your suggestions. > > > Regards. > > > > > On 6/4/21, Jehan-Guillaume de Rorthais wrote: > > On Fri, 4 Jun 2021 15:39:30 +0530 > > Atul Kumar wrote: > > > >> HI, > &

Re: strange behavior of WAL files

my query is Why is this happening ? please help me with your suggestions. Regards. On 6/4/21, Jehan-Guillaume de Rorthais wrote: > On Fri, 4 Jun 2021 15:39:30 +0530 > Atul Kumar wrote: > >> HI, >> >> We have a centos 6 enviornment where postgres 9.6 is runni

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 differe

strange behavior of WAL files

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_command is working fine. So strange behavior is : We have a WAL file say

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

Strange behavior of function date_trunc

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. EXPLAIN (ANALYZE) SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 s'::interval) AS g(x) WHERE g.x

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

Strange behavior

Hi, I’m surprised by this behavior I noticed in pgadmin3 and postgresql 9.6 Suppose those two tables create table test1 (v1 text) create table test2 (v2 text) insert into test1 (v1) values ('A') insert into test2 (v2) values ('B') query select v1 from test2 return v2 column doesn’t exi

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 ge

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 ord

Strange behavior of the random() function

Hello, Can anybody explain me that strange behavior? select random() as "rnd", random() as "rnd1", random() as "rnd2" from generate_series( 1, 5 ); --- 0.948556384071708 0.769186236895621 0.381390900816768 0.684488877654076 0.594888080

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;

Strange behavior with missing column in SQL function

Here's the code that reproduces the behavior: http://paste.debian.net/1035412/ I have already discussed this in the IRC channel but there doesn't seem to be a consensus on whether this is a bug here's a brief transcript of RhodiumToad's opinion: > this isn't new, goes back to 9.1 at least > basic