Re: [pgpool-general: 9132] Pgpool delegate IP is not reachable from the remote host

[Bo Peng]

Hi,

The default value of "if_up_cmd" is:

  /usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0

If the network interface "eth0" exists on Pgpool-II servers,
the command may run successfully.

I think the default value of "if_up_cmd" was executed to bring up the VIP.

> Hello everyone.
> 
> We have a pgpool & postgres setup made as per the given example
> 
> 
> We haven't configured the *if_up_cmd, if_down_cmd & **arping_cmd* in
> pgpool.conf
> (we commented it out on purpose). We enabled the *delegate_ip* and assigned
> a unused IP.
> 
> While we bringing up the pgpool the log shows VIP is successfully UP via
> if_up_cmd, how is this happening? any default way is considering the
> if_up_cmd to bring the VIP?
> 
> 
> 
> *2024-06-18 08:27:02.735: watchdog_utility pid 102493: LOG:  successfully
> acquired the delegate IP:"10.127.1.20"2024-06-18 08:27:02.735:
> watchdog_utility pid 102493: DETAIL:  'if_up_cmd' returned with
> success2024-06-18 08:27:02.735: watchdog pid 102378: LOG:  watchdog
> escalation process with pid: 102493 exit with SUCCESS.*
> 
> NOTE: We run into some situation so that we disabled the if_* commands in
> the configuration and started pgpool, but VIP is getting acquired by the
> leader pgpool node even the if_* commands are not used in pgpool.conf file.
> 
> Can anyone explain how the VIP works in pgpool watchdog
> 
> Thank you
> Regards
> Mukesh Tanuku


-- 
Bo Peng 
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/

Pgpool delegate IP is not reachable from the remote host

[Mukesh Tanuku]

Hello everyone.

We have a pgpool & postgres setup made as per the given example


We haven't configured the *if_up_cmd, if_down_cmd & **arping_cmd* in
pgpool.conf
(we commented it out on purpose). We enabled the *delegate_ip* and assigned
a unused IP.

While we bringing up the pgpool the log shows VIP is successfully UP via
if_up_cmd, how is this happening? any default way is considering the
if_up_cmd to bring the VIP?



*2024-06-18 08:27:02.735: watchdog_utility pid 102493: LOG:  successfully
acquired the delegate IP:"10.127.1.20"2024-06-18 08:27:02.735:
watchdog_utility pid 102493: DETAIL:  'if_up_cmd' returned with
success2024-06-18 08:27:02.735: watchdog pid 102378: LOG:  watchdog
escalation process with pid: 102493 exit with SUCCESS.*

NOTE: We run into some situation so that we disabled the if_* commands in
the configuration and started pgpool, but VIP is getting acquired by the
leader pgpool node even the if_* commands are not used in pgpool.conf file.

Can anyone explain how the VIP works in pgpool watchdog

Thank you
Regards
Mukesh Tanuku

Re: Restore of a reference database kills the auto analyze processing.

[Peter J. Holzer]

On 2024-06-18 14:59:16 +, HORDER Philip wrote:
> Classified as: {OPEN}
[...]
> {OPEN}
> The information contained in this e-mail is confidential. It is
> intended only for the stated addressee(s) and access to it by any
> other person is unauthorised.
[...]

This is an interesting definition of "OPEN".

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Tom Lane]

"David G. Johnston"  writes:
> On Tuesday, June 18, 2024, Ron Johnson  wrote:
>> What's the purpose?  Legacy of not having procedures?

> So people can have a style guide that says always specify a returns clause
> on function definitions.

To my mind, the reason we allow RETURNS together with OUT parameter(s)
is so there's a place to write SETOF if you want that.

Yes, the RETURNS TABLE syntax is somewhat redundant with RETURNS
SETOF.  Blame the SQL standard for that.

regards, tom lane

Re: fail to install postgresql15 on Alma9

[Adrian Klaver]

On 6/18/24 10:48, Dmitry O Litvintsev wrote:

Yes I did. Sorry did not mention that. I ran the

dnf -qy module sisable postgresql

prior to running.

dnf install postgresql15-server

(so that did not help)



Have you retried the install in case it was just a repo refresh issue?

If that does not work then the only other suggestion I have is to visit:

https://yum.postgresql.org/contact/



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

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[David G. Johnston]

On Tuesday, June 18, 2024, Ron Johnson  wrote:

> On Tue, Jun 18, 2024 at 2:37 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tuesday, June 18, 2024, Ron Johnson  wrote:
>>
>>> On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <
>>> david.g.johns...@gmail.com> wrote:
>>>
 On Tuesday, June 18, 2024, Ron Johnson  wrote:

>
> But I stand by returning OUT params and records at the same time.
>

 You mean you dislike adding the optional returns clause when output
 parameters exist?

>>>
>>> Correct.  It breaks the distinction between function and procedure.
>>>
>>
>> How so?
>>
>> The two distinctions are functions can produce sets while procedures get
>> transaction control.
>>
>> They both can produce a single multi-column output record.  The presence
>> or absence of the optional return clause on a function definition doesn’t
>> change that fact.
>>
>
> "A function returns a value*, but a procedure does not."
>
> *In the case of SQL, "value" might be a set.
>
>
Notably it’s the use of output arguments in create function that violate
the consistency, but using them is the only way to define an adhoc
multi-column result.

I’ll accept the narrow definition of “return value” being something that be
incorporated into an expression.  Procedures do not have that.  Hence they
don;y have a return clause.  Since the output arguments for a function are
return values specifying “returns record” just makes is perfectly clear
what is happening and that it is different than a procedure with the same
output arguments.

David J.

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Ron Johnson]

On Tue, Jun 18, 2024 at 2:37 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tuesday, June 18, 2024, Ron Johnson  wrote:
>
>> On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Tuesday, June 18, 2024, Ron Johnson  wrote:
>>>

 But I stand by returning OUT params and records at the same time.

>>>
>>> You mean you dislike adding the optional returns clause when output
>>> parameters exist?
>>>
>>
>> Correct.  It breaks the distinction between function and procedure.
>>
>
> How so?
>
> The two distinctions are functions can produce sets while procedures get
> transaction control.
>
> They both can produce a single multi-column output record.  The presence
> or absence of the optional return clause on a function definition doesn’t
> change that fact.
>

"A function returns a value*, but a procedure does not."

*In the case of SQL, "value" might be a set.

Re: HISTIGNORE in psql

[Daniel Gustafsson]

> On 18 Jun 2024, at 19:28, Wiwwo Staff  wrote:

> being able to tell psql to exclude some patterns from being stored in the 
> .psql_history file

Off the cuff that sounds like a pretty useful thing to have.

--
Daniel Gustafsson

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[David G. Johnston]

On Tuesday, June 18, 2024, Ron Johnson  wrote:

> On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tuesday, June 18, 2024, Ron Johnson  wrote:
>>
>>>
>>> But I stand by returning OUT params and records at the same time.
>>>
>>
>> You mean you dislike adding the optional returns clause when output
>> parameters exist?
>>
>
> Correct.  It breaks the distinction between function and procedure.
>

How so?

The two distinctions are functions can produce sets while procedures get
transaction control.

They both can produce a single multi-column output record.  The presence or
absence of the optional return clause on a function definition doesn’t
change that fact.


>
>
>> Because the out parameters and the “record” represent the exact same
>> thing.
>>
>
> What's the purpose?  Legacy of not having procedures?
>

So people can have a style guide that says always specify a returns clause
on function definitions.

David J.

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Ron Johnson]

On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tuesday, June 18, 2024, Ron Johnson  wrote:
>
>>
>> But I stand by returning OUT params and records at the same time.
>>
>
> You mean you dislike adding the optional returns clause when output
> parameters exist?
>

Correct.  It breaks the distinction between function and procedure.


> Because the out parameters and the “record” represent the exact same thing.
>

What's the purpose?  Legacy of not having procedures?

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[David G. Johnston]

On Tuesday, June 18, 2024, Ron Johnson  wrote:

>
> But I stand by returning OUT params and records at the same time.
>

You mean you dislike adding the optional returns clause when output
parameters exist?  Because the out parameters and the “record” represent
the exact same thing.

David J.

Re: fail to install postgresql15 on Alma9

[Dmitry O Litvintsev]

dnf -qy module sisable postgresql -> "dnf -qy module disable postgresql". 
Proper syntax was used. :)


From: Dmitry O Litvintsev 
Sent: Tuesday, June 18, 2024 12:48 PM
To: Adrian Klaver; pgsql-generallists.postgresql.org
Subject: Re: fail to install postgresql15 on Alma9

[EXTERNAL] – This message is from an external sender

Yes I did. Sorry did not mention that. I ran the

dnf -qy module sisable postgresql

prior to running.

dnf install postgresql15-server

(so that did not help)



From: Adrian Klaver 
Sent: Tuesday, June 18, 2024 12:44 PM
To: Dmitry O Litvintsev; pgsql-generallists.postgresql.org
Subject: Re: fail to install postgresql15 on Alma9

[EXTERNAL] – This message is from an external sender

On 6/18/24 10:40 AM, Dmitry O Litvintsev wrote:
> Hello,
>
> I am foillowing instructions on
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e=
>
> I select version "15"
> I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9"
>
> I get this command to run:
>
> "dnf install -y 
> https://urldefense.proofpoint.com/v2/url?u=https-3A__download.postgresql.org_pub_repos_yum_reporpms_EL-2D9-2Dx86-5F64_pgdg-2Dredhat-2Drepo-2Dlatest.noarch.rpm&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=pe18CD7xIVa4f2Npl1shD05CkZ2fTQSJQEWbhzzB8lI&e=
>  "
>
> But then I fail to install postgersql15:
>
> # yum install postgresql15-server
> Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM 
> CDT.
> No match for argument: postgresql15-server
> Error: Unable to find a match: postgresql15-server
>
> I can install postgresql16-server sussessfully but not 15.
> I need 15. What am I doing wrong?

I don't use RH, still the instructions here:

https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e=

Mention doing:

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

Did you do the above?

Then:

# Install PostgreSQL:
sudo dnf install -y postgresql15-server


>
> Thank you,
> Dmitry
>
>

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

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Ron Johnson]

On Tue, Jun 18, 2024 at 1:16 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, Jun 18, 2024 at 10:07 AM Ron Johnson 
> wrote:
>
>> fun2 puzzles me.  Why would you return parameters AND *a single record* 
>> (unless
>> it's an error status).
>>
>
> You mis-understand what 2 is doing.  You should go re-read the docs for
> create function again.  Especially the description of rettype.
>

That's true.  I've even used "RETURNS SETOF record" before.

But I stand by returning OUT params and records at the same time.

Re: fail to install postgresql15 on Alma9

[Dmitry O Litvintsev]

Yes I did. Sorry did not mention that. I ran the

dnf -qy module sisable postgresql

prior to running.

dnf install postgresql15-server

(so that did not help)



From: Adrian Klaver 
Sent: Tuesday, June 18, 2024 12:44 PM
To: Dmitry O Litvintsev; pgsql-generallists.postgresql.org
Subject: Re: fail to install postgresql15 on Alma9

[EXTERNAL] – This message is from an external sender

On 6/18/24 10:40 AM, Dmitry O Litvintsev wrote:
> Hello,
>
> I am foillowing instructions on
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e=
>
> I select version "15"
> I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9"
>
> I get this command to run:
>
> "dnf install -y 
> https://urldefense.proofpoint.com/v2/url?u=https-3A__download.postgresql.org_pub_repos_yum_reporpms_EL-2D9-2Dx86-5F64_pgdg-2Dredhat-2Drepo-2Dlatest.noarch.rpm&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=pe18CD7xIVa4f2Npl1shD05CkZ2fTQSJQEWbhzzB8lI&e=
>  "
>
> But then I fail to install postgersql15:
>
> # yum install postgresql15-server
> Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM 
> CDT.
> No match for argument: postgresql15-server
> Error: Unable to find a match: postgresql15-server
>
> I can install postgresql16-server sussessfully but not 15.
> I need 15. What am I doing wrong?

I don't use RH, still the instructions here:

https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e=

Mention doing:

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

Did you do the above?

Then:

# Install PostgreSQL:
sudo dnf install -y postgresql15-server


>
> Thank you,
> Dmitry
>
>

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

Re: fail to install postgresql15 on Alma9

[Adrian Klaver]

On 6/18/24 10:40 AM, Dmitry O Litvintsev wrote:

Hello,

I am foillowing instructions on

https://www.postgresql.org/download/linux/redhat/

I select version "15"
I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9"

I get this command to run:

"dnf install -y 
https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm";

But then I fail to install postgersql15:

# yum install postgresql15-server
Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM CDT.
No match for argument: postgresql15-server
Error: Unable to find a match: postgresql15-server

I can install postgresql16-server sussessfully but not 15.
I need 15. What am I doing wrong?


I don't use RH, still the instructions here:

https://www.postgresql.org/download/linux/redhat/

Mention doing:

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

Did you do the above?

Then:

# Install PostgreSQL:
sudo dnf install -y postgresql15-server




Thank you,
Dmitry




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

fail to install postgresql15 on Alma9

[Dmitry O Litvintsev]

Hello, 

I am foillowing instructions on 

https://www.postgresql.org/download/linux/redhat/

I select version "15"
I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9"

I get this command to run:

"dnf install -y 
https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm";

But then I fail to install postgersql15:

# yum install postgresql15-server 
Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM CDT.
No match for argument: postgresql15-server
Error: Unable to find a match: postgresql15-server

I can install postgresql16-server sussessfully but not 15. 
I need 15. What am I doing wrong?

Thank you, 
Dmitry

Re: HISTIGNORE in psql

[Wiwwo Staff]

Hi Adrian,
yes, of course, that is what I meant: being able to tell psql to exclude
some patterns from being stored in the .psql_history file

On Tue, 18 Jun 2024 at 16:12, Adrian Klaver 
wrote:

> On 6/17/24 23:45, Wiwwo Staff wrote:
> > Hi!
> > As a big history lover, it would be great to have the HISTIGNORE
> > functionality added to psql.
>
> HISTIGNORE is a BASH feature and psql runs in more then the BASH shell.
>
> Do you mean you would like a clone of that feature added to psql?
>
> Also psql uses either readline or libedit so it would need to work with
> those.
>
> >
> > Have a great day!
> > Wiwwo
> >
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[David G. Johnston]

On Tue, Jun 18, 2024 at 10:07 AM Ron Johnson 
wrote:

> fun2 puzzles me.  Why would you return parameters AND *a single record* 
> (unless
> it's an error status).
>

You mis-understand what 2 is doing.  You should go re-read the docs for
create function again.  Especially the description of rettype.

David J.

>

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[David G. Johnston]

On Tue, Jun 18, 2024 at 9:50 AM Ayush Vatsa 
wrote:

> 1/ I wanted to know what's the difference between the above three
> definitions.
> As per my understanding, "fun1" and "fun2" look the same, taking one
> integer and returning two columns with multiple rows.
>

Yes.


>
> Can the above definition (used for fun1 and fun2) cover both single and
> multiple row scenarios.
>

In so far as one is a valid number of rows to return from a function that
returns zero or more rows, yes.  But if the function is incapable of
returning more than one result it should not be marked with table/setof on
semantic grounds.


> 2/ How does someone decide which type of definition is to be used?
>
>
Between 1 and 2 is a style choice.  I prefer TABLE.  Using setof is more
useful when the returned type is predefined.  Or a true record where the
caller has to specify the shape.

For 3, having a non-set-returning-function that outputs multiple columns is
just odd, IMO.  Personally I'd go for pre-defining a composite type, then
return that type.

David J.

Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Ron Johnson]

fun1 returns a table set just like any other SELECT statement.
fun2 puzzles me.  Why would you return parameters AND *a single record* (unless
it's an error status).
fun3 just returns two parameters.  Why isn't it a procedure?

fun2, returning parameters AND a function value, would have made my Comp
Sci professors very, very angry.  Definitely Bad Practice.

You choose which to use based on how much data you want to return.

On Tue, Jun 18, 2024 at 12:50 PM Ayush Vatsa 
wrote:

> Hi PostgreSQL community,
> Recently I am exploring extensions in postgres and got a little confused
> regarding the function definition present in SQL file. For example consider
> below three functions:
>
> CREATE FUNCTION fun1(integer)
> RETURNS TABLE(
> col1 integer,
> col2 text
> )
> AS 'MODULE_PATHNAME', 'fun1'
> LANGUAGE C;
>
> CREATE FUNCTION fun2(
> IN  input integer,
> OUT col1 integer,
> OUT col2 text
> )
> RETURNS SETOF record
> AS 'MODULE_PATHNAME', 'fun2'
> LANGUAGE C;
>
> CREATE FUNCTION fun3(
> IN  input integer,
> OUT col1 integer,
> OUT col2 text
> )
> AS 'MODULE_PATHNAME', 'fun3'
> LANGUAGE C;
>
> 1/ I wanted to know what's the difference between the above three
> definitions.
> As per my understanding, "fun1" and "fun2" look the same, taking one
> integer and returning two columns with multiple rows.
> Whereas "fun3" is used when we are returning only one row, but my doubt
> for "fun3" is that, Can the above definition (used for fun1 and fun2) cover
> both single and multiple row scenarios.
>
> 2/ How does someone decide which type of definition is to be used?
>
> Regards
> Ayush Vatsa
>

Seeking Clarification on Function Definitions in PostgreSQL Extensions

[Ayush Vatsa]

Hi PostgreSQL community,
Recently I am exploring extensions in postgres and got a little confused
regarding the function definition present in SQL file. For example consider
below three functions:

CREATE FUNCTION fun1(integer)
RETURNS TABLE(
col1 integer,
col2 text
)
AS 'MODULE_PATHNAME', 'fun1'
LANGUAGE C;

CREATE FUNCTION fun2(
IN  input integer,
OUT col1 integer,
OUT col2 text
)
RETURNS SETOF record
AS 'MODULE_PATHNAME', 'fun2'
LANGUAGE C;

CREATE FUNCTION fun3(
IN  input integer,
OUT col1 integer,
OUT col2 text
)
AS 'MODULE_PATHNAME', 'fun3'
LANGUAGE C;

1/ I wanted to know what's the difference between the above three
definitions.
As per my understanding, "fun1" and "fun2" look the same, taking one
integer and returning two columns with multiple rows.
Whereas "fun3" is used when we are returning only one row, but my doubt for
"fun3" is that, Can the above definition (used for fun1 and fun2) cover
both single and multiple row scenarios.

2/ How does someone decide which type of definition is to be used?

Regards
Ayush Vatsa

Re: set search_path "$owner". And name versus literal for schemas.

[Adrian Klaver]

On 6/18/24 01:35, Dominique Devienne wrote:

Hi. Two things related to the search_path.

First, [the doc][1] mentions one can use a variable like "$user" for
the search_path. But setting the search_path is also for FUNCTIONs and
PROCEDUREs, and there what I really REALLY would like, is the ability
to use "$owner", to limit the search_path to the OWNER schema of that
func/proc, instead of having to explicitly spell it out. When I want
to *clone* a schema, having to "patch" the search_path of all those
funcs/procs (to replace the old schema with the new one), is a real
PITA.

Has this ever been considered? And if so, why was it refused?
It would simplify my life so much, I wonder why this doesn't already exist.


I could see this. I would choose something other then $owner as you are 
not really concerned with the func/proc owner but it's location. 
Something like $home would seem more on point.




Second, and related to the first point, when I introspect a schema,
the search_path
of functions/procedures seems to be rewritten with literals, instead of names.
Even the doc uses names, so why is it rewritten as literals? Or
accepts both in fact.

To actually simplify schema cloning, the introspected proc/func
search_path should remain "$owner" (or '$owner' I guess...) and not be
expanded. Otherwise we'd back to "manual" patching of the search_path,
which again is a PITA.

I'm curious to hear/read what PostgreSQL experts have to say on this subject.

Thanks, --DD

[1]: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH




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

Re: HISTIGNORE in psql

[Adrian Klaver]

On 6/17/24 23:45, Wiwwo Staff wrote:

Hi!
As a big history lover, it would be great to have the HISTIGNORE 
functionality added to psql.


HISTIGNORE is a BASH feature and psql runs in more then the BASH shell.

Do you mean you would like a clone of that feature added to psql?

Also psql uses either readline or libedit so it would need to work with 
those.




Have a great day!
Wiwwo




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

RE: Restore of a reference database kills the auto analyze processing.

[HORDER Philip]

Classified as: {OPEN}

Installing 15.7 has indeed fixed the problem.

Phil Horder
Database Mechanic

Thales
Land & Air Systems


{OPEN}
The information contained in this e-mail is confidential. It is intended only 
for the stated addressee(s) and access to it by any other person is 
unauthorised. If you are not an addressee, you must not disclose, copy, 
circulate or in any other way use or rely on the information contained in this 
e-mail. Such unauthorised use may be unlawful. If you have received this e-mail 
in error, please inform the originator immediately and delete it and all copies 
from your system.

Thales UK Limited. A company registered in England and Wales. Registered 
Office: 350 Longwater Avenue, Green Park, Reading, Berks RG2 6GF. Registered 
Number: 868273

Please consider the environment before printing a hard copy of this e-mail.

Re: DROP COLLATION vs pg_collation question

[Adrian Klaver]

On 6/18/24 06:32, Karsten Hilbert wrote:

Am Tue, Jun 18, 2024 at 03:02:56PM +0200 schrieb Karsten Hilbert:


I see, and since any database can be used as a template for
more databases, which can be create with an encoding
different from the template,


Proving myself wrong:


root@hermes:~/tmp# sudo -u postgres psql -e -f /tmp/db.sql
Output format is wrapped.
drop database if exists new_tmpl;
DROP DATABASE
create database new_tmpl with encoding = 'utf8';
CREATE DATABASE
You are now connected to database "new_tmpl" as user "postgres".
select current_database();
  current_database
--
  new_tmpl
(1 row)

drop database if exists new_latin1;
psql:/tmp/db.sql:8: HINWEIS:  Datenbank »new_latin1« existiert nicht, wird 
übersprungen
DROP DATABASE
create database new_latin1 with template = new_tmpl encoding = 'latin1' locale 
= 'de_DE@latin1';
psql:/tmp/db.sql:9: FEHLER:  neue Kodierung (LATIN1) ist inkompatibel mit der 
Kodierung der Template-Datenbank (UTF8)
TIP:  Verwenden Sie die gleiche Kodierung wie die Template-Datenbank oder 
verwenden Sie template0 als Template.


That is covered here:

https://www.postgresql.org/docs/16/manage-ag-templatedbs.html

"Another common reason for copying template0 instead of template1 is 
that new encoding and locale settings can be specified when copying 
template0, whereas a copy of template1 must use the same settings it 
does. This is because template1 might contain encoding-specific or 
locale-specific data, while template0 is known not to."


Substitute  for template1 and you can get 
the error you received.




Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B




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

Re: How to attach partition with primary key

[Alvaro Herrera]

On 2024-Jun-18, Philipp Faster wrote:

> I've done a bad job describing my issue in the first place: I left out a
> key definition statement that I thought doesn't play any role in the issue:
> another unique index on the same columns as PK.

I see.  That unique index seems quite useless. Why not just drop it?
That would also solve your problem.

> Interesting thing is that if I define PK before the unique index and then
> drop it and redefine after the unique index, then the code still doesn't
> work. This behaviour smells like a bug on the PostgreSQL side...

Yeah, it sounds like we have a bug in the index-matching code, though
it depends on having a useless, duplicative index.

-- 
Álvaro Herrera PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Selbst das größte Genie würde nicht weit kommen, wenn es
alles seinem eigenen Innern verdanken wollte." (Johann Wolfgang von Goethe)
   Ni aún el genio más grande llegaría muy lejos si
quisiera sacarlo todo de su propio interior.

Re: How to attach partition with primary key

[Philipp Faster]

Hey Alvaro and everyone,

Thank you for replying! I've checked `pg_dump -s`, but it didn't help - the
definition here was exactly the same (except order, but I didn't notice it
since in my actual schema there are thousands of rows).

I've done a bad job describing my issue in the first place: I left out a
key definition statement that I thought doesn't play any role in the issue:
another unique index on the same columns as PK.

My actual script was:

```sql
CREATE TABLE "Transactions_202405" (LIKE "Transactions" INCLUDING DEFAULTS);
INSERT INTO "Transactions_202405" SELECT * FROM
"Transactions_202405_parted";
alter table "Transactions_202405" add primary key (id, "createdAt");
alter table "Transactions_202405" add foreign key (uid) references "User"
on update cascade on delete restrict;
create index "Transactions_202405_createdAt_idx" on "Transactions_202405"
("createdAt" desc);
create index "Transactions_202405_type_idx" on "Transactions_202405" (type);
create index "Transactions_202405_uid_idx" on "Transactions_202405" (uid);
create unique index "Transactions_202405_id_createdAt_idx" on
"Transactions_202405" (id desc, "createdAt" desc);
alter table "Transactions_202405" add constraint
"Transactions_202405_check" check ("createdAt">='2024-05-01' and
"createdAt"<'2024-06-01');
alter table "Transactions" attach partition "Transactions_202405" for
values from ('2024-05-01') TO ('2024-06-01');
alter table "Transactions_202405" drop constraint
"Transactions_202405_check";
```

So as you can see on the 8th line, there is a definition of another unique
index. I left it out since I wanted to provide the minimal code for the
problem. This index is defined on all partitions and the main partitioned
table.

The solution I found is the following: to move `alter table ... add primary
key ...` statement after the unique index definition. After that it starts
to work like a charm. I tried all combinations of row order, but only when
I move this line after the unique index - it starts working.

Interesting thing is that if I define PK before the unique index and then
drop it and redefine after the unique index, then the code still doesn't
work. This behaviour smells like a bug on the PostgreSQL side...

I've found a minimal reproducible example:

```sql
create table "Transactions"
(
id  bigserial
not null,
uid uuid
 not null,
typevarchar(255)
 not null,
amount  numeric(26, 10)
not null,
"createdAt" timestamp(3) default CURRENT_TIMESTAMP
 not null
) partition by RANGE ("createdAt");
create unique index "Transactions_id_createdAt_idx" on "Transactions" (id
desc, "createdAt" desc);
alter table "Transactions" add primary key (id, "createdAt");

create table "Transactions_202403" (LIKE "Transactions" INCLUDING DEFAULTS);
alter table "Transactions_202403" add primary key (id, "createdAt");
create unique index "Transactions_202403_id_createdAt_idx" on
"Transactions_202403" (id desc, "createdAt" desc);
alter table "Transactions" attach partition "Transactions_202403" for
values from ('2024-03-01') to ('2024-04-01');
```

If I change the order of PK and unique index statements either in the first
block or second - the script breaks. Seems like PostgreSQL requires you to
define constraints and indexes in exactly the same order as the partitioned
table. Sounds buggy, but kinda logical.

Thank you and sorry for the incorrect question definition.

On Tue, Jun 18, 2024 at 4:46 PM Alvaro Herrera 
wrote:

> On 2024-Jun-18, Philipp Faster wrote:
>
> > As I understand, PostgreSQL refuses to use existing primary key for
> > some reason and tries to create its own as a children of
> > "Transactions" table's primary key.
>
> Yeah.  Your case sounds like the primary key in the partitioned table
> has some slight definition difference from the stock one, which makes
> the one you create in the partition not an exact match.  I'd suggest to
> look at what "pg_dump -s" emits as definition of the table with a
> magnifying glass to search for such differences.
>
> --
> Álvaro Herrera PostgreSQL Developer  —
> https://www.EnterpriseDB.com/
>

Re: Monitoring logical replication

[Ron Johnson]

On Tue, Jun 18, 2024 at 5:03 AM Shaheed Haque 
wrote:

> Hi all,
>
> Is there an "official" pairing of LSN values on the publication and
> subscription sides that should be used to track the delta between the two
> systems? I ask because Google is full of different pairs being used. I
> tried to identify the highest level interface points exposed, i.e. what is
> documented on
> https://www.postgresql.org/docs/current/replication-origins.html, the
> pg_stat_subscription table, the pg_stat_publication table and the
> pg_current_wal_lsn() function on the publisher, but these seem to be barely
> used.
>

The attached scripts (whose guts I took from a Stack Exchange post) might
be a good starting point.  It certainly works for physical replication!


> P.S. On a related note, I see a (stalled?) discussion on providing LSN ->
> timestamp conversion
> ,
> I'd just like to say that something like that would be very useful.
>

Out of curiosity, how does that work?  Is an instance's initial LSN really
based on Epoch?
#!/bin/bash

readonly Prime=foo1.example.com
readonly Replica=foo2.example.com

wal_location_to_64bits()
{
local id="${1%%/*}"
local offset="${1##*/}"
echo $((0xFF00 * 0x$id + 0x$offset))
}

declare Prime_SSE Prime_LSN
declare Second_SSE Second_Recv_LSN Second_Repl_LSN

IFS=$'\t' read -r Prime_LSN \
<<<$(psql --host=$Prime -XAt -F$'\t' \
-c "select pg_current_wal_lsn();")
IFS=$'\t' read -r Second_Timestamp Second_Recv_LSN Second_Repl_LSN \
<<<$(psql --host=$Replica -XAt -F$'\t' \
-c "select pg_last_wal_receive_lsn()
, pg_last_wal_replay_lsn();")

Prime_SSE=$(date +"%s.%N" -d "$Prime_Timestamp")
Second_SSE=$(date +"%s.%N" -d "$Second_Timestamp")
declare Query_Lag=$(echo "$Second_SSE - $Prime_SSE" | bc -l)

printf "Query Lag: %f\n" $Query_Lag

echo "LSN:"
printf "  Prime:= %s\n" $Prime_LSN
printf "  Replica Received: = %s\n" $Second_Recv_LSN
printf "  Replica Replayed: = %s\n" $Second_Repl_LSN

declare -i Prime_Bytes=$(wal_location_to_64bits $Prime_LSN)
declare -i Second_Recv_Bytes=$(wal_location_to_64bits $Second_Recv_LSN)
declare -i Second_Repl_Bytes=$(wal_location_to_64bits $Second_Repl_LSN)

echo "Backlog Bytes:"
printf "  Received = %'18d\n" $(echo "($Prime_Bytes - $Second_Recv_Bytes)" | bc -l)
printf "  Replayed = %'18d\n" $(echo "($Prime_Bytes - $Second_Repl_Bytes)" | bc -l)

Re: DROP COLLATION vs pg_collation question

[Karsten Hilbert]

Am Tue, Jun 18, 2024 at 03:02:56PM +0200 schrieb Karsten Hilbert:

> I see, and since any database can be used as a template for
> more databases, which can be create with an encoding
> different from the template,

Proving myself wrong:


root@hermes:~/tmp# sudo -u postgres psql -e -f /tmp/db.sql
Output format is wrapped.
drop database if exists new_tmpl;
DROP DATABASE
create database new_tmpl with encoding = 'utf8';
CREATE DATABASE
You are now connected to database "new_tmpl" as user "postgres".
select current_database();
 current_database
--
 new_tmpl
(1 row)

drop database if exists new_latin1;
psql:/tmp/db.sql:8: HINWEIS:  Datenbank »new_latin1« existiert nicht, wird 
übersprungen
DROP DATABASE
create database new_latin1 with template = new_tmpl encoding = 'latin1' locale 
= 'de_DE@latin1';
psql:/tmp/db.sql:9: FEHLER:  neue Kodierung (LATIN1) ist inkompatibel mit der 
Kodierung der Template-Datenbank (UTF8)
TIP:  Verwenden Sie die gleiche Kodierung wie die Template-Datenbank oder 
verwenden Sie template0 als Template.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: DROP COLLATION vs pg_collation question

[Karsten Hilbert]

Am Sun, Jun 16, 2024 at 04:38:49PM -0400 schrieb Tom Lane:

> It's really kind of moot, since you can't change the encoding
> of an existing database.  So any pg_collation entries that are
> for an incompatible encoding cannot be used for anything in that
> database, and they might as well not be there.  The reason they
> are there is merely an implementation detail: CREATE DATABASE clones
> those catalogs from the single copy of pg_collation in template0,
> which therefore had better include all collations that might be
> needed.

I see, and since any database can be used as a template for
more databases, which can be create with an encoding
different from the template, it doesn't really make too much
sense to be able to remove even pg_collation entries.

So, DROP COLLATION is somewhat of a smoking gun pointed at my
foot :-)

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: How to attach partition with primary key

[Alvaro Herrera]

On 2024-Jun-18, Philipp Faster wrote:

> As I understand, PostgreSQL refuses to use existing primary key for
> some reason and tries to create its own as a children of
> "Transactions" table's primary key.

Yeah.  Your case sounds like the primary key in the partitioned table
has some slight definition difference from the stock one, which makes
the one you create in the partition not an exact match.  I'd suggest to
look at what "pg_dump -s" emits as definition of the table with a
magnifying glass to search for such differences.

-- 
Álvaro Herrera PostgreSQL Developer  —  https://www.EnterpriseDB.com/

Re: Monitoring logical replication

[Shaheed Haque]

Hi all,

Is there an "official" pairing of LSN values on the publication and
subscription sides that should be used to track the delta between the two
systems? I ask because Google is full of different pairs being used. I
tried to identify the highest level interface points exposed, i.e. what is
documented on
https://www.postgresql.org/docs/current/replication-origins.html, the
pg_stat_subscription table, the pg_stat_publication table and the
pg_current_wal_lsn() function on the publisher, but these seem to be barely
used.

Am I missing something?

Thanks, Shaheed

P.S. On a related note, I see a (stalled?) discussion on providing LSN ->
timestamp conversion
,
I'd just like to say that something like that would be very useful.



On Tue, 30 Jan 2024 at 11:27, Shaheed Haque  wrote:

> This is great, thank you for posting. I'm currently a subcontinent or two
> away from my dev env, but will compare your approach with mine (you are
> using some facilities of psql I'm not familiar with). At least you have
> confirmed that LSNs are the place to start.
>
> Thanks again, Shaheed
>
>
> On Tue, 30 Jan 2024, 05:15 Klaus Darilion, 
> wrote:
>
>> Hi Saheed!
>>
>> I monitor our replication this way:
>>
>> 1. Every 10 seconds i fetch the current LSN and write it into a table,
>> next with the current timestamp. Further I fetch confirmend LSNs from
>> the replication slots and delete old entries in lsn2data table.
>>
>> calculate_logical_replication_lag.php:
>>
>> >
>> $path = realpath(dirname(__FILE__) . "/../inc");
>> set_include_path($path . PATH_SEPARATOR . get_include_path());
>>
>> require_once('config.php');
>> $config_int['syslogprefix'] = basename(__FILE__);
>> require_once('logging.php');
>>
>> $dbuser="replication_lag_user";
>> $dbpass="";
>> if (!$dbconn = pg_pconnect('host='.$config_int['dbhost'].'
>> dbname='.$config_int['dbname'].' user='.$dbuser.' password='.$dbpass)) {
>>  print "Sorry, database connection failed";
>>  exit;
>> }
>>
>> $accuracy = 10; // in seconds
>>
>> //
>> // Preparations:
>> //
>> // CREATE TABLE lsn2data(
>> //lsn pg_lsn PRIMARY KEY,
>> //seen timestamp NOT NULL DEFAULT NOW()
>> // );
>> // CREATE ROLE replication_lag_user WITH LOGIN PASSWORD
>> 'XXX';
>> // GRANT ALL ON TABLE lsn2data TO replication_lag_user;
>> //
>> // CREATE OR REPLACE FUNCTION get_replication_lag() RETURNS TABLE
>> (subscriber name, lag bigint) AS
>> // $BODY$
>> // DECLARE
>> // subscriber name;
>> // BEGIN
>> // FOR subscriber IN
>> // SELECT slot_name FROM pg_replication_slots
>> // LOOP
>> // RETURN QUERY SELECT slot_name, EXTRACT(EPOCH FROM
>> NOW()-seen)::bigint lag from lsn2data,pg_replication_slots WHERE
>> slot_name=subscriber AND lsn < confirmed_flush_lsn ORDER BY lsn DESC
>> LIMIT 1;
>> // END LOOP;
>> // RETURN;
>> // END
>> // $BODY$
>> // LANGUAGE plpgsql;
>> //
>> while (1) {
>>  $dbq = pg_query("INSERT INTO lsn2data (lsn) VALUES
>> (pg_current_wal_lsn())");
>>  if ($dbq === FALSE) {
>>  mylog(LOG_ERROR, "SQL query error:
>> ".pg_last_error()."\n");
>>  exit(1);
>>  }
>>
>>  $dbq = pg_query("DELETE FROM lsn2data WHERE lsn < (".
>>"SELECT lsn FROM lsn2data WHERE lsn < (".
>>  "SELECT confirmed_flush_lsn FROM
>> pg_replication_slots ORDER BY confirmed_flush_lsn ASC LIMIT 1".
>>") ORDER BY lsn DESC LIMIT 1".
>>  ")"
>>  );
>>  if ($dbq === FALSE) {
>>  mylog(LOG_ERROR, "SQL query error:
>> ".pg_last_error()."\n");
>>  exit(1);
>>  }
>>  sleep($accuracy);
>> }
>>
>> 2. I graph the replications lags (converted from LSN to seconds) in my
>> check_mk monitoring:
>>
>> #!/bin/bash
>>
>> #
>> # Managed by Puppet:
>> modules/base/files/monitoring/check_logical_replication_lag.sh
>> #
>> # Check the logical replication lag and export performance data for each
>> subscriber
>> #
>>
>> # exit on error
>> #set -e
>>
>> #Make sure this script only runs one at a time
>> (
>>
>>ME=$0
>>MEBASE=`basename $0`
>>
>>mylog () {
>>  echo "$MEBASE: $1"
>>  logger -t "$MEBASE" "$1"
>>}
>>
>>flock -x -w 1 200
>>if [ $? != "0" ]; then
>>  #echo "ERROR: $0 is already running ... exit"
>>  logger -t "$MEBASE" "ERROR: $0 is already running ... exit"
>>  exit 1
>>fi
>>
>># Do stuff
>>
>> # Variablen fuer Monitoring
>> CMK_SPOOLDIR=/var/lib/check_mk_agent/spool
>> CMK_NAME=$MEBASE
>> CMK_SPOOLFILE=600_`basename ${CMK_NAME}`.txt
>> CMK_HEADER="<<>>"
>> TMP_FILE="/tmp/logical_replication_lag.csv"
>>
>> # Schwellwerte
>> warn=300
>> crit=600
>>
>> final_output="$CMK_HEADER\nP $CMK_NAME "
>>
>> # move to a dir

set search_path "$owner". And name versus literal for schemas.

[Dominique Devienne]

Hi. Two things related to the search_path.

First, [the doc][1] mentions one can use a variable like "$user" for
the search_path. But setting the search_path is also for FUNCTIONs and
PROCEDUREs, and there what I really REALLY would like, is the ability
to use "$owner", to limit the search_path to the OWNER schema of that
func/proc, instead of having to explicitly spell it out. When I want
to *clone* a schema, having to "patch" the search_path of all those
funcs/procs (to replace the old schema with the new one), is a real
PITA.

Has this ever been considered? And if so, why was it refused?
It would simplify my life so much, I wonder why this doesn't already exist.

Second, and related to the first point, when I introspect a schema,
the search_path
of functions/procedures seems to be rewritten with literals, instead of names.
Even the doc uses names, so why is it rewritten as literals? Or
accepts both in fact.

To actually simplify schema cloning, the introspected proc/func
search_path should remain "$owner" (or '$owner' I guess...) and not be
expanded. Otherwise we'd back to "manual" patching of the search_path,
which again is a PITA.

I'm curious to hear/read what PostgreSQL experts have to say on this subject.

Thanks, --DD

[1]: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH