Re: recovery_command has precedence over phisical slots?

[Laurenz Albe]

On Wed, 2022-08-24 at 14:18 +0900, Kyotaro Horiguchi wrote:
> At Fri, 19 Aug 2022 18:37:53 +0200, Laurenz Albe  
> wrote in 
> > On Fri, 2022-08-19 at 16:54 +0200, Giovanni Biscontini wrote:
> > > Hello everyone, 
> > > I'm experiencing a behaviour I don't really understand if is a 
> > > misconfiguration or a wanted behaviour:
> > > 1) I set up a primary server (a.k.a. db1) with and archive_command to a 
> > > storage
> > > 2) I set up a replica (a.k.a. db2) that created a slot named as slot_2 
> > > and that has the recovery_command
> > >set to read archived wal on the storage.
> > > If I shutdown replica db2 during a pgbench I see the safe_wal_size 
> > > queried from pg_replication_slots
> > > on the primary decrease to a certain amount but still in the 
> > > max_slot_wal_kepp_size window: even
> > > if I restart the replica db2 before the slot_state changes to unreserved 
> > > or lost I see that the
> > > replica gets needed wals from the storage using recovery_command but 
> > > doesn't use slot on primary.
> > > Only if I comment the recovery command on the .conf of the replica then 
> > > it uses slot.
> > > If this is a wanted behaviour I can't understand the need of slots on 
> > > primary.
> > 
> > This is normal behavior and is no problem.
> > 
> > After the standby has caught up using "restore_command", it will connection 
> > to
> > the primary as defined in "primary_conninfo" and stream WAL from there.
> 
> The reason that db2 ran recovery beyond the slot LSN is the db2's
> restore_command (I guess) points to db1's archive.  If db2 had its own
> archive directory or no archive (that is, restore_command is empty),
> archive recovery stops at (approximately) the slot LSN and replication
> will start from there (from the beginning of the segment, to be
> exact).

Is it a problem if archive recovery proceeds past the replication slot's LSN?

I guess I don't see the problem.

Yours,
Laurenz Albe

RE: Unable to start replica after failover

[Lahnov, Igor]

Hi,
Yes, the *patial* from the *new leader* is restored to *last leader* and 
renamed to 0002054E00FB, without *partial* postfix.

>>Postgres asks for file 0002054E00FB but somehow gets
>>0002054E00FB.partial instead. Why?

Yes, Postgres asks for 0002054E00FB and gets renamed 
0002054E00FB.partial (without *partial* postfix).

And, finally, the *last leader* has a local 0002054E00FB.partial, 
with the segments needed for recovery, and 0002054E00FB ( renamed 
0002054E00FB.partial from new leader). But 0002054E00FB 
has a higher priority than 0002054E00FB.partial
Also, the *new leader* archives full 0003054E00FB of the new 
timeline. And this file contains the same as 0002054E00FB data.

Re: Setting up a server with previous day data

[Ron]

On 8/24/22 01:42, Peter J. Holzer wrote:

On 2022-08-23 19:15:58 -0500, Ron wrote:

That was before someone developed a utility to convert the roll-forward logs
into INSERT, UPDATE and DELETE statements.

Such a utility for PostgreSQL that would convert yesterday's WAL files into SQL
would really solve your problem.

Isn't that what logical replication basically does?


In a more asynchronous manner.  :D

--
Angular momentum makes the world go 'round.

Re: Question regarding failover behavior

[Koen De Groote]

Updating to say I tested and ran into the issue where the timeline switched
from 0C to 0D.

Trying to bring the old primary back up as standby fails. It recovers upto
a point and then gets stuck asked for a next file that doesn't exist.

Regardless of taking the existing data directory or restoring a new
basebackup.

Taking a new basebackup, with the new primary, and restoring that, works.
Standby gets in sync and everything is as expected.

Kind regards,
Koen De Groote



On Wed, Aug 24, 2022 at 1:09 AM Koen De Groote  wrote:

> Hello all,
>
> I have a system that was originally set up on 9.3, a few years ago moved
> to 11.2
>
> A shared mount is used to host the basebackup and wal archives.
>
> The failover procedure was basically manual and as follow:
>
> 1/ Take out the primary, remove the IP from the primary machine/VM
> 2/ Create the trigger_file on the standby, add the IP to the new primary
> machine/VM
> 3/ Create a basebackup from the new primary
> 4/ Redeploy the new standby, which will unpack this basebackup from step 3
> 5/ The new standby will have a restore_command to get synced back up.
>
> I see there's more recent tools like pg_rewind these days. Haven't had
> time to check that out and probably won't for a while.
>
> My question is: is the basebackup actually necessary? Can I not simply
> modify the configuration old the old primary, set it up like a standby, and
> then start the process/container, and have it pick up the necessary
> wal_archives with the restore_command?
>
> Or is it more complicated than that? Something like a timeline jump or
> something else that makes it impossible to simply set the old primary to
> standby and start it again?
>
> Kind regards,
> Koen De Groote
>

Re: recovery_command has precedence over phisical slots?

[Giovanni Biscontini]

Il giorno mer 24 ago 2022 alle ore 13:00 Laurenz Albe <
laurenz.a...@cybertec.at> ha scritto:

> On Wed, 2022-08-24 at 14:18 +0900, Kyotaro Horiguchi wrote:
> > At Fri, 19 Aug 2022 18:37:53 +0200, Laurenz Albe <
> laurenz.a...@cybertec.at> wrote in
> > > On Fri, 2022-08-19 at 16:54 +0200, Giovanni Biscontini wrote:
> > > > Hello everyone,
> > > > I'm experiencing a behaviour I don't really understand if is a
> misconfiguration or a wanted behaviour:
> > > > 1) I set up a primary server (a.k.a. db1) with and archive_command
> to a storage
> > > > 2) I set up a replica (a.k.a. db2) that created a slot named as
> slot_2 and that has the recovery_command
> > > >set to read archived wal on the storage.
> > > > If I shutdown replica db2 during a pgbench I see the safe_wal_size
> queried from pg_replication_slots
> > > > on the primary decrease to a certain amount but still in the
> max_slot_wal_kepp_size window: even
> > > > if I restart the replica db2 before the slot_state changes to
> unreserved or lost I see that the
> > > > replica gets needed wals from the storage using recovery_command but
> doesn't use slot on primary.
> > > > Only if I comment the recovery command on the .conf of the replica
> then it uses slot.
> > > > If this is a wanted behaviour I can't understand the need of
> slots on primary.
> > >
> > > This is normal behavior and is no problem.
> > >
> > > After the standby has caught up using "restore_command", it will
> connection to
> > > the primary as defined in "primary_conninfo" and stream WAL from there.
> >
> > The reason that db2 ran recovery beyond the slot LSN is the db2's
> > restore_command (I guess) points to db1's archive.  If db2 had its own
> > archive directory or no archive (that is, restore_command is empty),
> > archive recovery stops at (approximately) the slot LSN and replication
> > will start from there (from the beginning of the segment, to be
> > exact).
>
> Is it a problem if archive recovery proceeds past the replication slot's
> LSN?
>
> I guess I don't see the problem.
>
> Yours,
> Laurenz Albe
>

Hi and thanks all, my thoughts:
a) if I set up a slot I thought it would be useful for 2 reason:
a.1) it has a "per replica" reference on the wal to keep,
a.2) after a disconnection in replica (db2) when it reconnects I think
it can be quicker to get missing WALs referenced in slot from the primary
pg_wal than recover them from archived, especially if archived are on an a
S3 bucket (so yes db2 recovery points to the same archive of db1)
b) Archive and consequently the recovery command in my thoughts are "the
safety" if replica falls behind the wal_keep_size or (in this case) behind
the max_slot_wal_keep_size
c) I understand that, maybe, the idea behind giving the precedence to to
recovery_command is "recovery is present, so don't even give a try to slot
because it can be lost so go to "safety" with recovery that is intended to
be.
but... in this case if I set a slot+a recovery_command the usage and
subsequently the risk of filling the disk space, is useless: it uses always
the recovery.

So if I can say the problem is: I configure a slot that in every case
produces more time to set it up, more disk usage, more configuration, but
is useless...

thanks in advance and best regard, Giovanni

p.s. I forgot to specify before: the pg version is 14.5

Re: Corrupted Postgresql Microsoft Binaries

[Hillary Masha]

Thank you Adrian, I was able to unzip the files using powershell, my OS is
Windows 10 and I had been using Windows' 'Extract All' to try to get the
files. As well, simply double clicking to open the files showed an error
stating that windows cannot open this file. I had download the zip files
from  https://www.enterprisedb.com/downloads/postgres-postgresql-downloads .

On Tue, Aug 23, 2022 at 3:27 PM Adrian Klaver 
wrote:

> On 8/23/22 11:58, Hillary Masha wrote:
> > Hello,
> >
> > I downloaded the microsoft postgresql binaries for versions 14.5, 13.8,
> > 12.12, 11.17, 10.22 from
> > https://www.enterprisedb.com/download-postgresql-binaries
> >  and found
> > that there was an error with opening the zip files. Does anyone else use
>
> I could unzip the file on Linux with no errors.
>
> What OS are you on Windows or MacOS and what version?
>
> How did you download?
>
> What software are you using to unzip the file?
>
> > these files and found that they ran into the same issue? If so, where
> > else can I get the files?
> >
>
> There are the installers:
>
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Corrupted Postgresql Microsoft Binaries

[Adrian Klaver]

On 8/24/22 09:17, Hillary Masha wrote:
Thank you Adrian, I was able to unzip the files using powershell, my OS 
is Windows 10 and I had been using Windows' 'Extract All' to try to get 
the files. As well, simply double clicking to open the files showed an 
error stating that windows cannot open this file. I had download the zip 
files from 
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 


The files you where trying to open where unzipped with PowerShell or 
'Extract All'(assuming this means from the right click context menu for 
the zip file)?


Show commands/steps used in each case.

Which file(s) exactly?

What is the complete error message?

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

Re: Corrupted Postgresql Microsoft Binaries

[Hillary Masha]

It looks like Windows 10 is unable to open the zip files for windows
versions of the binaries 14.5, 13.8, 12.12, 11.17, 10.22. I get this error
message when I double click the zip file or when I right click and select
'Extract All..'.
[image: image.png]

I was able to successfully unzip files in powershell using the command
below.
[image: image.png]


On Wed, Aug 24, 2022 at 2:40 PM Adrian Klaver 
wrote:

> On 8/24/22 09:17, Hillary Masha wrote:
> > Thank you Adrian, I was able to unzip the files using powershell, my OS
> > is Windows 10 and I had been using Windows' 'Extract All' to try to get
> > the files. As well, simply double clicking to open the files showed an
> > error stating that windows cannot open this file. I had download the zip
> > files from
> > https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
>
> The files you where trying to open where unzipped with PowerShell or
> 'Extract All'(assuming this means from the right click context menu for
> the zip file)?
>
> Show commands/steps used in each case.
>
> Which file(s) exactly?
>
> What is the complete error message?
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Corrupted Postgresql Microsoft Binaries

[Adrian Klaver]

On 8/24/22 12:22, Hillary Masha wrote:
It looks like Windows 10 is unable to open the zip files for windows 
versions of the binaries 14.5, 13.8, 12.12, 11.17, 10.22. I get this 
error message when I double click the zip file or when I right click and 
select 'Extract All..'.

image.png


I cranked  up a Windows 10 machine and I see the same thing.

Using 7-Zip:

https://www.7-zip.org/

for the extraction worked.

Must be something with the Windows built-in unzip code.

I generally keep 7-Zip on Windows machines because it can handle a 
variety of compressed formats. When you install it will show up in the 
context menu when you right click on a compressed file.




I was able to successfully unzip files in powershell using the command 
below.

image.png


On Wed, Aug 24, 2022 at 2:40 PM Adrian Klaver > wrote:


On 8/24/22 09:17, Hillary Masha wrote:
 > Thank you Adrian, I was able to unzip the files using powershell,
my OS
 > is Windows 10 and I had been using Windows' 'Extract All' to try
to get
 > the files. As well, simply double clicking to open the files
showed an
 > error stating that windows cannot open this file. I had
download the zip
 > files from
 >
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads


The files you where trying to open where unzipped with PowerShell or
'Extract All'(assuming this means from the right click context menu for
the zip file)?

Show commands/steps used in each case.

Which file(s) exactly?

What is the complete error message?

-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Corrupted Postgresql Microsoft Binaries

[Adrian Klaver]

On 8/24/22 12:22, Hillary Masha wrote:
It looks like Windows 10 is unable to open the zip files for windows 
versions of the binaries 14.5, 13.8, 12.12, 11.17, 10.22. I get this 
error message when I double click the zip file or when I right click and 
select 'Extract All..'.

image.png

I was able to successfully unzip files in powershell using the command 
below.

image.png



I sent an email to:

webmas...@enterprisedb.com

explaining the issue.

Is there a reason you can't use the installer?

Can you do what you need with the files unzipped in PowerShell?


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

Two questions about "pg_constraint"

[Bryn Llewellyn]

*Question 1: why does "pg_constraint" have a "connamespace" column?*

I created this temporary view (using PG 14.4):

create temporary view all_constraints(t_owner, t_schema, t_name, c_name, same) 
as
select
  r.rolname,
  s.nspname,
  c.relname,
  x.conname,
  (x.connamespace = c.relnamespace)
from
  pg_class c
  inner join
  pg_roles r
  on c.relowner = r.oid
  inner join
  pg_namespace s
  on c.relnamespace = s.oid
  inner join pg_constraint x
  on c.oid = x.conrelid
where c.relkind = 'r';

I created three tables, each with a user-created constraint. The tables also 
have implicitly created primary key constraints.

Then I did this:

select count(*) from all_constraints;

It said that the count is over a hundred. (All but the rows for my three tables 
are for rows for tables in the "pg_catalog" schema.)

Then I did this:

select exists(select 1 from all_constraints where not same)::text;

It said "false".

Over one hundred seems to be a fair sample size. So it seems to be reasonable 
to assume that "pg_constraint.connamespace = pg_class.relnamespace" is always 
true. Ordinary common-sense analysis of the query suggests this too. If the 
hypothesis is right, then "connamespace" is simply a derived value. And this 
would be a departure from usual table design practice.

What do you think?

*Question 2: what happened to the column "consrc"?*

The PG 11 account of "pg_constraint"
https://www.postgresql.org/docs/11/catalog-pg-constraint.html

describes "consrc" (text) thus:

« If a check constraint, a human-readable representation of the expression »

Ad hoc queries in my PG 11.9 env show results like « (v = lower(v)) » in this 
column for my tables. This is useful information. But the PG 14 version of 
"pg_constraint" has no such column (and nor does the doc mention it). Is this 
information now exposed somewhere else?

Re: Two questions about "pg_constraint"

[Adrian Klaver]

On 8/24/22 13:11, Bryn Llewellyn wrote:

*Question 1: why does "pg_constraint" have a "connamespace" column?*




What do you think?

*Question 2: what happened to the column "consrc"?*

The PG 11 account of "pg_constraint"
https://www.postgresql.org/docs/11/catalog-pg-constraint.html 



describes "consrc" (text) thus:

« If a check constraint, a human-readable representation of the expression »

Ad hoc queries in my PG 11.9 env show results like « (v = lower(v)) » in 
this column for my tables. This is useful information. But the PG 14 
version of "pg_constraint" has no such column (and nor does the doc 
mention it). Is this information now exposed somewhere else?




It was in 11 but not later, so lets look at release notes from 12 --> 14

Here in 12:

"

Remove obsolete pg_constraint.consrc column (Peter Eisentraut)

This column has been deprecated for a long time, because it did not 
update in response to other catalog changes (such as column renamings). 
The recommended way to get a text version of a check constraint's 
expression from pg_constraint is pg_get_expr(conbin, conrelid). 
pg_get_constraintdef() is also a useful alternative.

"

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

Re: Two questions about "pg_constraint"

[Adrian Klaver]

On 8/24/22 13:17, Adrian Klaver wrote:

On 8/24/22 13:11, Bryn Llewellyn wrote:


Ad hoc queries in my PG 11.9 env show results like « (v = lower(v)) » 
in this column for my tables. This is useful information. But the PG 
14 version of "pg_constraint" has no such column (and nor does the doc 
mention it). Is this information now exposed somewhere else?




It was in 11 but not later, so lets look at release notes from 12 --> 14


Should have been ... not 14.




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

Re: Two questions about "pg_constraint"

[Adrian Klaver]

On 8/24/22 13:11, Bryn Llewellyn wrote:

*Question 1: why does "pg_constraint" have a "connamespace" column?*

I created this temporary view (using PG 14.4):

*create temporary view all_constraints(t_owner, t_schema, t_name, 
c_name, same) as

select
   r.rolname,
   s.nspname,
   c.relname,
   x.conname,
   (x.connamespace = c.relnamespace)
from
   pg_class c
   inner join
   pg_roles r
   on c.relowner = r.oid
   inner join
   pg_namespace s
   on c.relnamespace = s.oid
   inner join pg_constraint x
   on c.oid = x.conrelid
where c.relkind = 'r';
*

I created three tables, each with a user-created constraint. The tables 
also have implicitly created primary key constraints.


Then I did this:

*select count(*) from all_constraints;
*
It said that the count is over a hundred. (All but the rows for my three 
tables are for rows for tables in the "pg_catalog" schema.)


Then I did this:

*select exists(select 1 from all_constraints where not same)::text;
*
It said "false".

Over one hundred seems to be a fair sample size. So it seems to be 
reasonable to assume that "pg_constraint.connamespace = 
pg_class.relnamespace" is always true. Ordinary common-sense analysis of 
the query suggests this too. If the hypothesis is right, then 
"connamespace" is simply a derived value. And this would be a departure 
from usual table design practice.


What do you think?



create table c1 (id integer, constraint pk1 primary key(id));
CREATE TABLE

create table c2 (id integer, constraint pk1 primary key(id));
ERROR:  relation "pk1" already exists


create table test.c2 (id integer, constraint pk1 primary key(id));
CREATE TABLE

 select conname, connamespace from pg_constraint where conname = 'pk1';
 conname | connamespace
-+--
 pk1 | 2200
 pk1 |59706


From:

https://www.postgresql.org/docs/current/catalog-pg-constraint.html

conname name

Constraint name (not necessarily unique!)

So connamespace makes it unique.

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

Re: Two questions about "pg_constraint"

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Question 2: what happened to the column "consrc"?
> 
> It was in 11 but not later, so let's look at release notes...

HERE:
https://www.postgresql.org/docs/12/release-12.html

> «
> Remove obsolete pg_constraint.consrc column (Peter Eisentraut)
> 
> This column has been deprecated for a long time, because it did not update in 
> response to other catalog changes (such as column renamings). The recommended 
> way to get a text version of a check constraint's expression from 
> pg_constraint is pg_get_expr(conbin, conrelid). pg_get_constraintdef() is 
> also a useful alternative.
> »

Thanks, Adrian. I should have tried Google for "remove pg_constraint consrc". 
This finds the quote as the top hit.

I confirmed that "pg_get_expr(conbin, conrelid)" shows « (v = lower(v)) » for 
my example table.

Re: Two questions about "pg_constraint"

[Tom Lane]

Bryn Llewellyn  writes:
> *Question 1: why does "pg_constraint" have a "connamespace" column?*

You appear to be assuming that every pg_constraint entry is tied to
a table.  This isn't so.

(1) That catalog also carries check constraints for domains, which
are tied to types instead.  Yeah, you could imagine some rule like
"look in either pg_class or pg_type to find the schema", but it'd
be really painful.

(2) The SQL standard describes "assertions", which are global
check constraints that can affect multiple tables.  We don't
support those, and very possibly never will, but the pg_constraint
catalog is set up to support them.  Presumably they'd be stored
with conrelid and contypid both zero, so there would be no other
place to find out the assertion's schema.

I'm not entirely convinced that putting these two (or three) sorts
of objects in the same catalog was a great design.  However, that's
what we've got and changing it seems like more trouble than it'd be
worth.

regards, tom lane

Re: Corrupted Postgresql Microsoft Binaries

[Hillary Masha]

Thank you for sending the email to them, i had sent an email to
techsupp...@enterprisedb.com last week but haven't heard back.
I need the zip files as my team uses a script that installs the postgres
versions to multiple machines using the binaries.

On Wed, Aug 24, 2022 at 4:09 PM Adrian Klaver 
wrote:

> On 8/24/22 12:22, Hillary Masha wrote:
> > It looks like Windows 10 is unable to open the zip files for windows
> > versions of the binaries 14.5, 13.8, 12.12, 11.17, 10.22. I get this
> > error message when I double click the zip file or when I right click and
> > select 'Extract All..'.
> > image.png
> >
> > I was able to successfully unzip files in powershell using the command
> > below.
> > image.png
> >
>
> I sent an email to:
>
> webmas...@enterprisedb.com
>
> explaining the issue.
>
> Is there a reason you can't use the installer?
>
> Can you do what you need with the files unzipped in PowerShell?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Corrupted Postgresql Microsoft Binaries

[Thomas Kellerer]

Hillary Masha schrieb am 23.08.2022 um 20:58:

I downloaded the microsoft postgresql binaries for versions 14.5,
13.8, 12.12, 11.17, 10.22 from
https://www.enterprisedb.com/download-postgresql-binaries and found
that there was an error with opening the zip files. Does anyone else
use these files and found that they ran into the same issue? If so,
where else can I get the files?


I have no problems unzipping them on Windows 10 using TotalCommander
or the Info-ZIP "unzip" tool

Re: Corrupted Postgresql Microsoft Binaries

[Adrian Klaver]

On 8/24/22 2:01 PM, Hillary Masha wrote:
Thank you for sending the email to them, i had sent an email to 
techsupp...@enterprisedb.com  last 
week but haven't heard back.
I need the zip files as my team uses a script that installs the postgres 
versions to multiple machines using the binaries.


As I understand it you can unzip them and the issue is just with the 
'Extract All' process. In other words they are valid except for the one 
use case.




On Wed, Aug 24, 2022 at 4:09 PM Adrian Klaver > wrote:


On 8/24/22 12:22, Hillary Masha wrote:
 > It looks like Windows 10 is unable to open the zip files for windows
 > versions of the binaries 14.5, 13.8, 12.12, 11.17, 10.22. I get this
 > error message when I double click the zip file or when I right
click and
 > select 'Extract All..'.
 > image.png
 >
 > I was able to successfully unzip files in powershell using the
command
 > below.
 > image.png
 >

I sent an email to:

webmas...@enterprisedb.com 

explaining the issue.

Is there a reason you can't use the installer?

Can you do what you need with the files unzipped in PowerShell?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Corrupted Postgresql Microsoft Binaries

[Adrian Klaver]

On 8/24/22 2:23 PM, Thomas Kellerer wrote:

Hillary Masha schrieb am 23.08.2022 um 20:58:

I downloaded the microsoft postgresql binaries for versions 14.5,
13.8, 12.12, 11.17, 10.22 from
https://www.enterprisedb.com/download-postgresql-binaries and found
that there was an error with opening the zip files. Does anyone else
use these files and found that they ran into the same issue? If so,
where else can I get the files?


I have no problems unzipping them on Windows 10 using TotalCommander
or the Info-ZIP "unzip" tool



I did some searching to see if there was a fix and the results where:

From MS the usual:
a) It's your problem
b) Upgrade
c) Reboot.

From others:

a) The builtin unzip program is buggy don't use.
b) Use just about any other program.

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

Re: Two questions about "pg_constraint"

[Adrian Klaver]

On 8/24/22 13:43, Bryn Llewellyn wrote:

/adrian.kla...@aklaver.com  wrote:/





Thanks, Adrian. I should have tried Google for "remove pg_constraint 
consrc". This finds the quote as the top hit.


To see all release notes together go here:

https://bucardo.org/postgres_all_versions

Then you can search on the page.



I confirmed that "pg_get_expr(conbin, conrelid)" shows « (v = lower(v)) 
» for my example table.



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

Greg Sabino Mullane ? Re: Two questions about "pg_constraint"

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Thanks, Adrian. I should have tried Google for "remove pg_constraint 
>> consrc". This finds the quote as the top hit.
> 
> To see all release notes together go here:
> 
> https://bucardo.org/postgres_all_versions
> 
> Then you can search on the page.
> 
>> I confirmed that "pg_get_expr(conbin, conrelid)" shows « (v = lower(v)) » 
>> for my example table.

Thanks again, Andrian. That's gold-dust. I see that the page says:

« This page was generated on August 11, 2022 by a script (version 1.30) by Greg 
Sabino Mullane, and contains information for 461 versions of Postgres. »

And I went on to find this:

https://postgresql.life/post/greg_sabino_mullane/ 


Do you see posts to this list, Greg? If so, then thank you very much!

pg_dump without setting search_path

[gzh]

When I use pg_dump to export schema from a database, it adds the following line 
at the beginning:




SELECT pg_catalog.set_config('search_path', '', false);




Is it possible set an option where pg_dump will not add this line? 

It is causing issues later when I try to execute other SQL commands, without 
the schema qualifier.




This is the pg_dump command I am using right now:




pg_dump -O -x -h  -p  -U  -d  --schema 
public --schema-only > public-schema.sql

Re: pg_dump without setting search_path

[Tom Lane]

gzh  writes:
> When I use pg_dump to export schema from a database, it adds the following 
> line at the beginning:
> SELECT pg_catalog.set_config('search_path', '', false);
> Is it possible set an option where pg_dump will not add this line? 

No.  It's a security precaution.

> It is causing issues later when I try to execute other SQL commands, without 
> the schema qualifier.

That probably means you have some user-defined functions that are
not sufficiently careful about search_path.  You want to either
schema-qualify every reference in the function body, or attach a
"SET search_path" clause to remove the function's context dependency.

(If you're using SQL-language functions in v14 or later, another
option is to use the SQL-standard syntax instead of writing the
body as a string literal.  Then the object references in the function
are parsed at definition time.)

regards, tom lane

Re:Re: pg_dump without setting search_path

[gzh]

Hi Tom,

Thank you for your prompt response.

When I use pg_dump to export schema from an older version of PostgreSQL 8.2.3 , 
it adds the following line at the beginning:

SET search_path = public, pg_catalog;

Is it possible set an option where pg_dump will add this line in PostgreSQL 
12.5?



At 2022-08-25 11:07:46, "Tom Lane"  wrote:
>gzh  writes:
>> When I use pg_dump to export schema from a database, it adds the following 
>> line at the beginning:
>> SELECT pg_catalog.set_config('search_path', '', false);
>> Is it possible set an option where pg_dump will not add this line? 
>
>No.  It's a security precaution.
>
>> It is causing issues later when I try to execute other SQL commands, without 
>> the schema qualifier.
>
>That probably means you have some user-defined functions that are
>not sufficiently careful about search_path.  You want to either
>schema-qualify every reference in the function body, or attach a
>"SET search_path" clause to remove the function's context dependency.
>
>(If you're using SQL-language functions in v14 or later, another
>option is to use the SQL-standard syntax instead of writing the
>body as a string literal.  Then the object references in the function
>are parsed at definition time.)
>
>   regards, tom lane

Re: pg_dump without setting search_path

[David G. Johnston]

On Wednesday, August 24, 2022, gzh  wrote:
>
> When I use pg_dump to export schema from an older version of PostgreSQL
> 8.2.3 , it adds the following line at the beginning:
>
> SET search_path = public, pg_catalog;
>
> Is it possible set an option where pg_dump will add this line in
> PostgreSQL 12.5?
>
Your observation about the past isn’t going to change the answer
(especially if you choose to appeal to 8.2 behavior).  It is a policy
choice, not a technical limitation.

David J.

Re: pg_dump without setting search_path

[Adrian Klaver]

On 8/24/22 20:39, gzh wrote:

Hi Tom,

Thank you for your prompt response.

When I use pg_dump to export schema from an older version of PostgreSQL 
8.2.3 , it adds the following line at the beginning:


SET search_path = public, pg_catalog;

Is it possible set an option where pg_dump will add this line in 
PostgreSQL 12.5?




No for this reason:

https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path


regards, tom lane



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

Re: Unable to start replica after failover

[Alexander Kukushkin]

Hi,


On Wed, 24 Aug 2022 at 13:37, Lahnov, Igor  wrote:

>
>
> Yes, Postgres asks for 0002054E00FB and gets renamed
> 0002054E00FB.partial (without *partial* postfix).
>

But why? This is totally weird and unexpected behavior. Why pg_probackup is
doing this?

Regards,
--
Alexander Kukushkin

Re:Re: pg_dump without setting search_path

[gzh]

Dear all,
 
Thank you so much for your response to my request for information.
I appreciated you taking the time to provide some answers and for getting back 
to me so promptly.
It made a lot of sense and was exactly what I needed to know.


At 2022-08-25 12:19:03, "Adrian Klaver"  wrote:
>On 8/24/22 20:39, gzh wrote:
>> Hi Tom,
>> 
>> Thank you for your prompt response.
>> 
>> When I use pg_dump to export schema from an older version of PostgreSQL 
>> 8.2.3 , it adds the following line at the beginning:
>> 
>> SET search_path = public, pg_catalog;
>> 
>> Is it possible set an option where pg_dump will add this line in 
>> PostgreSQL 12.5?
>> 
>
>No for this reason:
>
>https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
>
>>> regards, tom lane
>
>
>-- 
>Adrian Klaver
>adrian.kla...@aklaver.com

Re: Two questions about "pg_constraint"

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> b...@yugabyte.com writes:
>> 
>> Question 1: why does "pg_constraint" have a "connamespace" column?
> 
> You appear to be assuming that every pg_constraint entry is tied to a table. 
> This isn't so.

I see how this might have appeared to be the case. But I did, very much, know 
about the possibility to define a constraint on a domain. (It's a very valuable 
feature and I've used it a lot.)

> (1) That catalog also carries check constraints for domains, which are tied 
> to types instead. Yeah, you could imagine some rule like "look in either 
> pg_class or pg_type to find the schema", but it'd be really painful.

Yes, I knew that too. The documentation of the catalog tables is terse to a 
fault—and I, at least, have to do empirical tests to try to discern the proper 
mental model. I'm probably misunderstanding some things. Having said this, the 
results from these query seem to be informative. I did it in a "play" database 
where I'd created both a table with a column check constraint and a domain with 
a value constraint.

select count(*) from pg_constraint where (conrelid <> 0 and contypid =   0); 
-->> 107 rows

select count(*) from pg_constraint where (conrelid =  0 and contypid <>  0); 
-->> 3 rows

select exists(
select 1 from pg_constraint
where not (
(conrelid =  0 and contypid <> 0) or
(conrelid <> 0 and contypid =  0)
  )
  )::text as "conrelid and contypid both non-zero"; -->> false

My hypothesis (of which I've been unable to find a statement in the doc) is 
that this shows a reliable invariant. In other words, it seems that a 
constraint has an arc-FK relationship to its parent EITHER in "pg_class" OR in 
"pg_type". I think that you imply this with your "look in either pg_class or 
pg_type to find the schema". However, I don't feel this lookup to be painful. I 
anyway want a view that lists all the schema objects that implement my database 
app.

I've copied my definition of my "schema_objects" view at the end. It unions 
these (aliased) columns:

oid, name, schema_oid, owner_oid, kind

from "pg_class", "pg_type", and "pg_proc". (These, so far, are sufficient for 
my present purpose.) It also adds the column "namespace" to materialize which 
catalog table is the source:

pg_class → 'relations', pg_type → 'types', and pg_proc → 'subprograms'

Then it joins this union with "pg_namespace" and "pg_roles" to get the 
human-readable names for these.

The upshot is that my "schema_objects" view has a surrogate PK, "oid", and the 
unique business key "(name, schema, namespace)".

I don't know what better name to use for my "namespace" column. It's taken up 
by the name of the view for schemas (which should, in my book, be called 
"pg_schema"—in the singular or plural according to the usual coin-toss. My name 
captures the meaning and accords with the fact that I can have an object whose 
"name" is 'x' three times in "schema" called 's1' distinguished from each other 
by the fact that each has a different "namespace" thus: 'relations', 'types' 
and 'subprograms'.

I'm confused by these outcomes:

- when you create a composite type manually, you automatically get an entry 
with its name and schema BOTH in "pg_class" AND in "pg_type".

- when you create a table or a view manually, you again automatically get an 
entry with its name and schema BOTH in "pg_class" AND in "pg_type".

Where can I read about the mental model for this?

Anyway, with my "schema objects" in place, I can create a "constrants" view 
thus:

create view a.constraints(c_name, c_kind, c_expr, t_name, t_schema, 
t_namespace, t_kind) as
with c(conname, contype, expr, sch_obj_id) as (
  select
conname,
contype,
pg_get_expr(conbin, conrelid),
case contypid
  when 0 then conrelid
  elsecontypid
end
  from pg_constraint)
select
  c.conname,
  c.contype,
  c.expr,
  s.name,
  s.schema,
  s.namespace,
  s.kind
from
  a.schema_objects s
  inner join
  c
  on sch_obj_id = s.oid;

Then this query:

select c_name, c_kind, c_expr, t_name, t_schema, t_namespace, t_kind from 
a.constraints
where t_schema = any(array['s1', 's2', 's3'])
order by c_name, t_name, t_schema, t_namespace;

shows this result:

  c_name  | c_kind | c_expr | t_name | t_schema | t_namespace | 
t_kind 
--++++--+-+
 d_chk| c  | s2.fnc(VALUE)  | dom| s3   | types   | domain
 itm_pkey | p  || itm| s1   | relations   | 
ordinary-table
 t_chk| c  | (v = lower(v)) | tab| s1   | relations   | 
ordinary-table
 tab_pkey | p  || tab| s1   | relations   | 
ordinary-table

for the test objects that I created. So (from the user P.o.V.) a constraint is 
uniquely identified by its name, its kind, and the identity of the schema 
object that it hangs off. This seems to me to be just what I want.

> (2) The SQL standa