Logical Replication - When to Enable Disabled Publication and When to Create a New One

[Avi Weinberg]

Hi All,

If I find a subscription in subenabled = false state.  Is it safe to run ALTER 
subscription and put it in ENABLE state or it is better to drop and create it 
from scratch?

I'm trying to figure our what can cause subscription to be in subenabled = 
false state.  If we know all the scenarios that can cause it to be disabled 
maybe we can determine when to enable the subscription and when to recreate it.

Thanks!
IMPORTANT - This email and any attachments is intended for the above named 
addressee(s), and may contain information which is confidential or privileged. 
If you are not the intended recipient, please inform the sender immediately and 
delete this email: you should not copy or use this e-mail for any purpose nor 
disclose its contents to any person.

Logical Replication - When to Enable Disabled Subscription and When to Create a New One

[Avi Weinberg]

Sorry, my previous mail had in the title Publication instead of Subscription
Hi All,

If I find a subscription in subenabled = false state.  Is it safe to run ALTER 
subscription and put it in ENABLE state or it is better to drop and create it 
from scratch?

I'm trying to figure our what can cause subscription to be in subenabled = 
false state.  If we know all the scenarios that can cause it to be disabled 
maybe we can determine when to enable the subscription and when to recreate it.

Thanks!
IMPORTANT - This email and any attachments is intended for the above named 
addressee(s), and may contain information which is confidential or privileged. 
If you are not the intended recipient, please inform the sender immediately and 
delete this email: you should not copy or use this e-mail for any purpose nor 
disclose its contents to any person.
IMPORTANT - This email and any attachments is intended for the above named 
addressee(s), and may contain information which is confidential or privileged. 
If you are not the intended recipient, please inform the sender immediately and 
delete this email: you should not copy or use this e-mail for any purpose nor 
disclose its contents to any person.

Re: case insensitive collation of Greek's sigma

[Jakub Jedelsky]

On Wed, Dec 1, 2021 at 8:49 PM Tom Lane  wrote:

> Peter Eisentraut  writes:
> > Running lower() like this is really the wrong thing to do.  We should be
> > doing "case folding" instead, which normalizes these differences for the
> > purpose of case-insensitive comparisons.
>
> That just begs the question: if tolower (or towlower) isn't the
> appropriate API, what is?  Perhaps ICU has something for a more
> generalized notion of case-similarity, but I'm not aware of any such
> thing in the POSIX API.
>
> BTW, I think it's only accidental that the regex example shown upthread
> gets the right answer.  In that example, what's happening is that we
> consider a letter in a case-insensitive regex to match itself, or
> tolower() of itself, or toupper() of itself.  Both σ and ς have Σ
> as toupper() so they both work.  But if you'd written Σ in the regex,
> only one of σ and ς would match that as a data character.  (Haven't
> actually tested this, but given the way the code works I'm pretty
> sure it's so.)  Again, it's hard to see how to do better atop a POSIX
> locale library.
>

Thanks for digging into the issue.

Based on GNU docs [1] the POSIX APIs are not ready for that. Anyway, is it
possible to keep current behaviour with lowercase in POSIX as a fallback
and have the correct solution for ICU? I think (not an expert though) there
should be already working code for case folding for some time already.

[1] https://www.gnu.org/software/libunistring/
"""
Text files are nowadays usually encoded in Unicode, and may consist of very
different scripts – from Latin letters to Chinese Hanzi –, with many kinds
of special characters – accents, right-to-left writing marks, hyphens,
Roman numbers, and much more. But the POSIX platform APIs for text do not
contain adequate functions for dealing with particular properties of many
Unicode characters. In fact, the POSIX APIs for text have several
assumptions at their base which don't hold for Unicode text.
"""

Re: case insensitive collation of Greek's sigma

[Gianni Ceccarelli]

I realise this may not be applicable to the original problem, but
non-deterministic collations seems to offer a solution::


  dakkar@[local] dakkar=> create collation "en-US-ins-icu" (
 provider=icu,
 locale='en-US-u-ks-level2',
 deterministic=false
 );

  dakkar@[local] dakkar=> select 'ΣΣ' = 'σσ' collate "en-US-ins-icu";
  ┌──┐
  │ ?column? │
  ├──┤
  │ t│
  └──┘
  (1 row)

  dakkar@[local] dakkar=> select 'ΣΣ' = 'σς' collate "en-US-ins-icu";
  ┌──┐
  │ ?column? │
  ├──┤
  │ t│
  └──┘
  (1 row)

  dakkar@[local] dakkar=> select 'ΣΣ' = 'α' collate "en-US-ins-icu";
  ┌──┐
  │ ?column? │
  ├──┤
  │ f│
  └──┘
  (1 row)

Notice, though:

* I don't understand what that ``-u-`` is doing in ``locale``, but
  it's necessary
* as the docs
  https://www.postgresql.org/docs/13/collation.html#COLLATION-NONDETERMINISTIC
  say:

  - B-tree cannot use deduplication with indexes that use a
nondeterministic collation
  - certain operations are not possible with nondeterministic
collations, such as pattern matching operations (this means you
can't use ``LIKE``)

-- 
Dakkar - 
GPG public key fingerprint = A071 E618 DD2C 5901 9574
 6FE2 40EA 9883 7519 3F88
key id = 0x75193F88

pg_upgrade question

[Alan Stange]

Hello all,

We're running a 13.x installation and looking to upgrade to 14.1.  This
is all on Linux servers.   We have a main instance running with a number
of hot standby replicas configured.   In the past, we have done a
dump/restore on a slow evening and then rsynced all the bits around so
that the main server and all replicas were identical at the start.   The
database has gotten larger now (~1TB) so that is a less desirable option.

So I am thinking to do a pg_upgrade --link on each of the main and the
hot standby replicas, and then restarting all the processes.   I'm sure
this will work fine on the main server, but I have not seen any mention
of this working in the expected way on the hot standby replicas.   Can
someone confirm that the steps I described here will work in the way I
am optimistically expecting it will?

I should mention that we are running on ZFS, and will take a snapshot
prior to the upgrade, so that if something goes sideways with our use of
--link  we can revert back instantly. 

Thank you,

Alan

Re: pg_upgrade question

[Adrian Klaver]

On 12/2/21 08:05, Alan Stange wrote:

Hello all,

We're running a 13.x installation and looking to upgrade to 14.1.  This
is all on Linux servers.   We have a main instance running with a number
of hot standby replicas configured.   In the past, we have done a
dump/restore on a slow evening and then rsynced all the bits around so
that the main server and all replicas were identical at the start.   The
database has gotten larger now (~1TB) so that is a less desirable option.

So I am thinking to do a pg_upgrade --link on each of the main and the
hot standby replicas, and then restarting all the processes.   I'm sure
this will work fine on the main server, but I have not seen any mention
of this working in the expected way on the hot standby replicas.   Can
someone confirm that the steps I described here will work in the way I
am optimistically expecting it will?


Have you looked at steps 9 & 11 here?:

https://www.postgresql.org/docs/current/pgupgrade.html



I should mention that we are running on ZFS, and will take a snapshot
prior to the upgrade, so that if something goes sideways with our use of
--link  we can revert back instantly.

Thank you,

Alan





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

Re: pg_upgrade question

[Alan Stange]

Of course that would be in the manual ;-)   Thank you for pointing this
out.   We've been doing upgrades the reliable old school way for so long
that I wasn't aware that something better was already well documented.

Thank you,

Alan

On 12/2/21 11:10, Adrian Klaver wrote:
> On 12/2/21 08:05, Alan Stange wrote:
>> Hello all,
>>
>> We're running a 13.x installation and looking to upgrade to 14.1.  This
>> is all on Linux servers.   We have a main instance running with a number
>> of hot standby replicas configured.   In the past, we have done a
>> dump/restore on a slow evening and then rsynced all the bits around so
>> that the main server and all replicas were identical at the start.   The
>> database has gotten larger now (~1TB) so that is a less desirable option.
>>
>> So I am thinking to do a pg_upgrade --link on each of the main and the
>> hot standby replicas, and then restarting all the processes.   I'm sure
>> this will work fine on the main server, but I have not seen any mention
>> of this working in the expected way on the hot standby replicas.   Can
>> someone confirm that the steps I described here will work in the way I
>> am optimistically expecting it will?
> Have you looked at steps 9 & 11 here?:
>
> https://www.postgresql.org/docs/current/pgupgrade.html
>
>> I should mention that we are running on ZFS, and will take a snapshot
>> prior to the upgrade, so that if something goes sideways with our use of
>> --link  we can revert back instantly.
>>
>> Thank you,
>>
>> Alan
>>
>>
>

Re: Issues cross-compiling libpq 14.x to MacOS armv8

[Vincas Dargis]

Thanks Tom!

Should we expect this fix in the next 14 patch release, or only in 15.x?

If latter, I would add this patch into Conan package itself, to make it work 
earlier.

On 2021-11-30 23:33, Tom Lane wrote:

I wrote:

It seems like a useful test when *not* cross compiling, which is most
of the time.  I'd just wrap that bit in
 if test "$cross_compiling" = no; then


Or actually, since we should print something, it looks like this will do:

diff --git a/configure.ac b/configure.ac
index a5c10b8d56..7257afda20 100644
--- a/configure.ac
+++ b/configure.ac
@@ -2287,6 +2287,8 @@ if test x"$with_ssl" = x"openssl" ; then
AC_MSG_RESULT([OpenSSL])
  elif test x"$PORTNAME" = x"win32" ; then
AC_MSG_RESULT([Windows native])
+elif test x"$cross_compiling" = x"yes"; then
+  AC_MSG_RESULT([assuming /dev/urandom])
  else
AC_MSG_RESULT([/dev/urandom])
AC_CHECK_FILE([/dev/urandom], [], [])

Off to see if I can verify that before pushing.

regards, tom lane

Re: Issues cross-compiling libpq 14.x to MacOS armv8

[Tom Lane]

Vincas Dargis  writes:
> Should we expect this fix in the next 14 patch release, or only in 15.x?

I did push it into v14:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=175edafd1f30a78643359b56c5545b5e7aabfb50

regards, tom lane

Re: Require details that how to find user creation date in postgresql Database

[Sonai muthu raja M]

Dear Team,

Kindly help us to provide the details that how to find User creation date in 
postgresql Database.

Note: we know that we can find the create user query from postgresql log. Apart 
from that how to find it from inside the Database

Warm regards,

M Sonai Muthu Raja
Managed Delivery Services - DBA Support
M: +919003132734
II Floor, TIDEL Park, 4 Rajiv Gandhi Salai,
Taramani, Chennai – 600 113

[cid:01b1fe56-bb1a-41e1-958c-289acbc17f49]

www.sifytechnologies.com



From: Sonai muthu raja M 
Sent: Thursday, December 2, 2021 2:54 PM
To: secur...@postgresql.org 
Cc: Sonai muthu raja M 
Subject: Require details that how to find user creation date in postgresql 
Database

Dear Team,

Kindly help us to provide the details that how to find User creation date in 
postgresql Database.

Note: we know that we can find the create user query from postgresql log. Apart 
from that how to find it from inside the Database.

Warm regards,

M Sonai Muthu Raja
Managed Delivery Services - DBA Support
M: +919003132734
II Floor, TIDEL Park, 4 Rajiv Gandhi Salai,
Taramani, Chennai – 600 113

[cid:b274d046-b74a-4a8f-9bfd-b145ae0303c9]

www.sifytechnologies.com


DISCLAIMER: The information contained in this electronic message and any 
attachments to this message are intended for the exclusive use of the 
addressee(s) and may contain proprietary, confidential or privileged 
information. If you are not the intended recipient, you should not disseminate, 
distribute or copy this e-mail. Please notify the sender immediately and 
destroy all copies of this message and any attachments. Please note that 
intercepting or any unauthorized use of this message or any attachment can be 
treated as infringement of person’s right of privacy under the Human Rights Act 
1993 and also is a Criminal Offence under the Information and Technology Act, 
2008.

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. Thank you for your cooperation.

CTE Materialization

[Paul van der Linden]

Hi,

when switching to postgres 14 (from 11) I'm having some slow queries
because of inlining of CTE's.
I know I can get the same result as with PG11 when adding MATERIALIZED to
the cte, but the same application also needs to be able to run on older
postgres versions, so that is a no-go.
Is there any other way that I can have materialized cte's in PG14 while
still be compatible with older PG versions?
Much appreciated,

Paul

PS please cc me when answering

Re: Require details that how to find user creation date in postgresql Database

[Tom Lane]

"Sonai muthu raja  M"  writes:
> Kindly help us to provide the details that how to find User creation date in 
> postgresql Database.

This information is not stored by Postgres, so you can't.

regards, tom lane

Re: Require details that how to find user creation date in postgresql Database

[Adrian Klaver]

On 12/2/21 01:51, Sonai muthu raja M wrote:

Dear Team,

Kindly help us to provide the details that how to find User creation 
date in postgresql Database.


Note: we know that we can find the create user query from postgresql 
log. Apart from that how to find it from inside the Database


You can't.



*/Warm regards,/**/

M Sonai Muthu Raja
Managed Delivery Services - DBA Support


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

Re: CTE Materialization

[David G. Johnston]

On Thursday, December 2, 2021, Paul van der Linden <
paul.doskabou...@gmail.com> wrote:

> Hi,
>
> when switching to postgres 14 (from 11) I'm having some slow queries
> because of inlining of CTE's.
> I know I can get the same result as with PG11 when adding MATERIALIZED to
> the cte, but the same application also needs to be able to run on older
> postgres versions, so that is a no-go.
> Is there any other way that I can have materialized cte's in PG14 while
> still be compatible with older PG versions?
> Much appreciated,
>

The usual anti-inlining hack is to add an “offset 0” to the query.  Haven’t
tried it in 14 myself though.

David J.

Re: Max connections reached without max connections reached

[James Sewell]

>
> I expect my 'vote' counts for naught, but I fully expect seeing these show
> up in the logs would have helped me much more quickly have insight into
> what was going on during times of very high concurrency and extreme
> slowness with many processes showing as waiting on LwLocks.
>

Is there any mechanism I could use from a C extension and a hook to warn
about >64 overflows?

- James

-- 
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

[Yi Sun]

Hi Kyotaro,

Thank you for your explanation, after putting the crl file to client, it
works now, thanks.

Kyotaro Horiguchi  于2021年12月2日周四 下午12:46写道：

> Hi.
>
> At Thu, 2 Dec 2021 11:31:26 +0800, Yi Sun  wrote in
> > Hi Kyotaro
> >
> > From the description, seems  ~/.postgresql/root.crl  is store client
> > revoked certificate
>
> No.  Revocation is checked on the peer. There's no point for a server
> to check for revocation of its own certificate, and actually that
> doesn't happen. Revocation of a client certificate is checked on
> server side referencing server.crl. Revocation of a server certificate
> is checked on client side referencing postgresql.crl. For example,
> some web browsers make use of CRL of web *servers*, which is
> automatically maintained in background.
>
> You will see it work if you duped the server.crl as
> ~/.postgresql/root.crl on the client.  (I spelled this wrongly in the
> previous message..)
>
> > https://www.postgresql.org/docs/11/libpq-ssl.html
> > ~/.postgresql/root.crl certificates revoked by certificate authorities
> server
> > certificate must not be on this list
> > Just don't know why server parameter ssl_crl_file parameter configured
> but
> > don't take affect
>
> As explained above, it is because the CRL specified by ssl_crl_file
> can only be used to verify client certificates.
>
> >
> https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-SSL-CRL-FILE
> >
> > ssl_crl_file (string)
> >
> > Specifies the name of the file containing the SSL server certificate
> > revocation list (CRL). Relative paths are relative to the data directory.
> > This parameter can only be set in the postgresql.conf file or on the
> server
> > command line. The default is empty, meaning no CRL file is loaded.
>
> Ah, the "server" in "SSL server certificate revocation list" looks
> like a noise word, rather misleading, or plain wrong, I'm not sure
> which one it actually is.
>
>
> Anyway I propose change the rephrase as "SSL client certification
> revocation list" as attached.
>
> regards.
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>