Logical replication issue after Postgresql upgrade from 13 to 14

[Sergey Belyashov]

I have an issue with logical replication after Postgresql upgrade from
13 to 14 (upgraded subscriber only using pg_upgrade_cluster -m link 13
main). After upgrade all subscriptions were disabled so I have enabled
them and replication workers successfully started.
pg_stat_subscription contains list of all subscriptions. All times in
this table is near current time (replication workers receives data
from servers). But no changes in destination table since cluster
upgrade (on publishers tables are changed). What I'm doing wrong and
how to fix issue?

Sergey Belyashov

ssl_crl_file Certificate Revocation List doesn't work for postgresql 11

[Yi Sun]

Hi All,

OS: CentOS 7.6
PG: 11.11

Our env already configured ssl
--server postgresql.conf
ssl = 'on'
ssl_ca_file = '/var/lib/pgsql/tls/root.crt'
ssl_cert_file = '/var/lib/pgsql/tls/server.crt'
ssl_key_file = '/var/lib/pgsql/tls/server.key'

--client configuration
$ ls -alrt /var/lib/pgsql/.postgresql
total 20
-rw-r--r-- 1 postgres postgres  688 Nov 30 06:46 root.crt
-rw-r--r-- 1 postgres postgres  778 Nov 30 06:46 postgresql.crt
-rw--- 1 postgres postgres 1708 Nov 30 06:47 postgresql.key

--From client to connect ssl works
$ psql "host=master.pgcluster11.service.consul port=5432 dbname=testdb
user=test sslmode=verify-full"
Password:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)

--Now we want to configure the ssl_crl_file and generated the root.crl file
as below redhat doc

https://access.redhat.com/documentation/en-us/red_hat_update_infrastructure/2.1/html/administration_guide/chap-red_hat_update_infrastructure-administration_guide-certification_revocation_list_crl

--Use openssl to verify, shows "certificate revoked"
# cat /home/sunyi/tls/root.crt /home/sunyi/tls/1/root.crl > /tmp/test_1.pem
# openssl verify -extended_crl -verbose -CAfile /tmp/test_1.pem -crl_check
/home/sunyi/tls/1/server.crt
/home/sunyi/tls/1/server.crt: O = Acronis, OU = DBS, CN =
s12345y-patroni_cluster-507460701
error 23 at 0 depth lookup:certificate revoked

--copy root.crl file to /var/lib/pgsql/tls
-bash-4.2$ ls -alrt /var/lib/pgsql/tls
total 20
drwx-- 4 postgres postgres 4096 Nov 30 04:20 ..
-rw-r- 1 postgres postgres 1164 Nov 30 04:20 server.crt
-rw--- 1 postgres postgres 1679 Nov 30 04:20 server.key
-rw-r- 1 postgres postgres  688 Nov 30 04:20 root.crt
drwx-- 2 postgres postgres 4096 Nov 30 04:20 .

--Configure /var/lib/pgsql/11/data/postgresql.conf
ssl_crl_file = '/var/lib/pgsql/tls/root.crl'

--Reload postgresql
$ psql
psql (11.11)
Type "help" for help.

postgres=# select pg_reload_conf();
 pg_reload_conf

 t
(1 row)
postgres=# show ssl_crl_file;
ssl_crl_file
-
 /var/lib/pgsql/tls/root.crl
(1 row)

--From client to connect still can connect not as expect
$ psql "host=master.pgcluster11.service.consul port=5432 dbname=testdb
user=test sslmode=verify-full"
Password:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)

Could you please advise if need any more configuration? Thanks

Thanks and best regards
Sun Yi

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

[Gabriel Cabillon]

  
  
El 30/11/2021 a las 10:53, Yi Sun escribió:

  
  Hi All,


OS: CentOS 7.6
PG: 11.11


Our env already configured ssl
--server postgresql.conf
ssl = 'on'
  ssl_ca_file = '/var/lib/pgsql/tls/root.crt'
  ssl_cert_file = '/var/lib/pgsql/tls/server.crt'
  ssl_key_file = '/var/lib/pgsql/tls/server.key'



--client configuration
$ ls -alrt /var/lib/pgsql/.postgresql
  total 20
  -rw-r--r-- 1 postgres postgres  688 Nov 30 06:46 root.crt
  -rw-r--r-- 1 postgres postgres  778 Nov 30 06:46
  postgresql.crt
  -rw--- 1 postgres postgres 1708 Nov 30 06:47
  postgresql.key



--From client to connect ssl works
$ psql "host=master.pgcluster11.service.consul port=5432
  dbname=testdb user=test sslmode=verify-full"
  Password:
  SSL connection (protocol: TLSv1.2, cipher:
  ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
  

--Now we want to configure the ssl_crl_file and generated
  the root.crl file as below redhat doc


https://access.redhat.com/documentation/en-us/red_hat_update_infrastructure/2.1/html/administration_guide/chap-red_hat_update_infrastructure-administration_guide-certification_revocation_list_crl



--Use openssl to verify, shows "certificate revoked"
# cat /home/sunyi/tls/root.crt /home/sunyi/tls/1/root.crl
  > /tmp/test_1.pem

# openssl verify -extended_crl -verbose -CAfile
  /tmp/test_1.pem -crl_check /home/sunyi/tls/1/server.crt
  /home/sunyi/tls/1/server.crt: O = Acronis, OU = DBS, CN =
  s12345y-patroni_cluster-507460701
  error 23 at 0 depth lookup:certificate revoked



--copy root.crl file to /var/lib/pgsql/tls 
-bash-4.2$ ls -alrt /var/lib/pgsql/tls
  total 20
  drwx-- 4 postgres postgres 4096 Nov 30 04:20 ..
  -rw-r- 1 postgres postgres 1164 Nov 30 04:20 server.crt
  -rw--- 1 postgres postgres 1679 Nov 30 04:20 server.key
  -rw-r- 1 postgres postgres  688 Nov 30 04:20 root.crt
  drwx-- 2 postgres postgres 4096 Nov 30 04:20 .



--Configure /var/lib/pgsql/11/data/postgresql.conf
  ssl_crl_file = '/var/lib/pgsql/tls/root.crl'



--Reload postgresql
$ psql
  psql (11.11)
  Type "help" for help.
  
  postgres=# select pg_reload_conf();
   pg_reload_conf
  
   t
  (1 row)

postgres=# show ssl_crl_file;
          ssl_crl_file
  -
   /var/lib/pgsql/tls/root.crl
  (1 row)



--From client to connect still can connect not as expect
$ psql "host=master.pgcluster11.service.consul port=5432
  dbname=testdb user=test sslmode=verify-full"
  Password:
  SSL connection (protocol: TLSv1.2, cipher:
  ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)



Could you please advise if need any more configuration?
  Thanks


Thanks and best regards
Sun Yi
  


Hi, 

according to the ls command it
  seems you copied root.crt instead of
root.crl

Yours,
Gabriel
  

How to reveal the codes of functions properly?

[Shaozhong SHI]

Any one can shed the light on this?

Regards,

David

Re: How to reveal the codes of functions properly?

[Rob Sargent]

On 11/30/21 9:30 AM, Shaozhong SHI wrote:

Any one can shed the light on this?

Regards,

David

\sf+ function_name

Re: Database Scalability

[Ben Chobot]

Saurav Sarkar wrote on 11/29/21 10:13 PM:

Hi All,

We have some multi-tenant solutions which are separating the tenant 
data in Postgresql mainly in the following manner.


1. Using different schemas
2. Using different tables for different tenants.


Without more details it's impossible to give you a detailed answer, so, 
in general if you are breaking out your client data by schema, you 
will likely be fine. We have used this method with great success to 
scale our customer workload - each customer gets their own schema, which 
we can then rebalance between databases as those clients drive more load 
over time. For those tables that we want partitioned, we simply 
partition them in every schema. That's arguably inefficient but we find 
the schema consistency to be an overall win.


(For context, when I say we have used this method with great success, we 
have over 13k customers, almost a PB of data, peak around 1.5M iops, and 
it's all painless to operate.)

Issues cross-compiling libpq 14.x to MacOS armv8

[Vincas Dargis]

Hi list,

I wanted to update [0] Conan package for building libpq 14.1. Usually it's enough to add new tarball and declare it's 
hash, but it seems that since 14.0 cross-compiling to armv8 MacOS now fails, and I *guess* it's due to removed 
`--disable-strong-random` option.


Here's some snippets from build log: [1]

```
...
Cross-build from 'Macos:x86_64' to 'Macos:armv8'
...
checking which random number source to use... /dev/urandom
checking for /dev/urandom... libpq/14.1:
libpq/14.1: WARN: Build folder is dirty, removing it: 
/Users/jenkins/w/BuildSingleReference@2/.conan/data/libpq/14.1/_/_/build/30acef53c04f36d5f9412c84a1b3a7434a1f10fb

configure: WARNING: unrecognized options: --disable-strong-random
configure: WARNING: using cross tools not prefixed with host triplet
configure: error: cannot check for file existence when cross compiling
libpq/14.1: ERROR: Package '30acef53c04f36d5f9412c84a1b3a7434a1f10fb' build 
failed
```

Could this mean that building on armv8 Macos cannot work with "strong random", or at least in the way PostgreSQL 
configure script expect that to be detected to work?


Thanks!

P.S. there was earlier attempt by another contributor to update Conan package to 14.0, which also failed in the same 
manner [2].


[0] https://github.com/conan-io/conan-center-index/pull/8109
[1] 
https://c3i.jfrog.io/c3i/misc/logs/pr/8109/2-configs/macos-m1-clang/libpq/14.1//30acef53c04f36d5f9412c84a1b3a7434a1f10fb-build.txt

[2] https://github.com/conan-io/conan-center-index/pull/7676

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

[Tom Lane]

Vincas Dargis  writes:
> checking which random number source to use... /dev/urandom
> checking for /dev/urandom... 
> configure: error: cannot check for file existence when cross compiling

Hmm ... this evidently stems from 16f96c74d.

AFAICS this is the only test in our configure script that is a hard
fail when cross-compiling, and I don't see a reason for it to be that.
We could just assume that /dev/urandom will be available --- that's no
worse than a lot of the other optimistic assumptions that configure
makes in that mode.

regards, tom lane

Re: How to reveal the codes of functions properly?

[Shaozhong SHI]

In what environment, that did not work.


On Tuesday, 30 November 2021, Rob Sargent  wrote:

> On 11/30/21 9:30 AM, Shaozhong SHI wrote:
>
> Any one can shed the light on this?
>
> Regards,
>
> David
>
> \sf+ function_name
>

Re: How to reveal the codes of functions properly?

[Rob Sargent]

On 11/30/21 1:17 PM, Shaozhong SHI wrote:

In what environment, that did not work.


On Tuesday, 30 November 2021, Rob Sargent > wrote:


On 11/30/21 9:30 AM, Shaozhong SHI wrote:

Any one can shed the light on this?

Regards,

David

\sf+ function_name


psql

Re: How to reveal the codes of functions properly?

[Wicher]

On Tue, 30 Nov 2021 20:17:32 +
Shaozhong SHI  wrote:

> In what environment, that did not work.
> 
> 
> On Tuesday, 30 November 2021, Rob Sargent  wrote:
> 
> > On 11/30/21 9:30 AM, Shaozhong SHI wrote:
> >
> > Any one can shed the light on this?
> >
> > Regards,
> >
> > David
> >
> > \sf+ function_name
> >  

If you run that in a psql shell when psql has been passed `-E`, you'll see that 
under the
hood it runs:

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END as "Type",
 CASE
  WHEN p.provolatile = 'i' THEN 'immutable'
  WHEN p.provolatile = 's' THEN 'stable'
  WHEN p.provolatile = 'v' THEN 'volatile'
 END as "Volatility",
 CASE
  WHEN p.proparallel = 'r' THEN 'restricted'
  WHEN p.proparallel = 's' THEN 'safe'
  WHEN p.proparallel = 'u' THEN 'unsafe'
 END as "Parallel",
 pg_catalog.pg_get_userbyid(p.proowner) as "Owner",
 CASE WHEN prosecdef THEN 'definer' ELSE 'invoker' END AS "Security",
 pg_catalog.array_to_string(p.proacl, E'\n') AS "Access privileges",
 l.lanname as "Language",
 p.prosrc as "Source code",
 pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
FROM pg_catalog.pg_proc p
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE pg_catalog.pg_function_is_visible(p.oid)
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

Wildcarding json keys in json query

[David Gauthier]

PG 11.5 on linux

Let's say I store a jsonb in a column called test_results that looks like
this...

{
ports : {
port_abc:{min: 5, max: 7, mean: 6},
port_def:{min: 5, max: 9, mean: 7},
port_ghi:{min: 6, max: 10, mean: 8}
}
}

And I want to to get all the port names where the mean is >= 7.

This works...

select 1 from mytbl where cast(test_results#>'{ports,*port_abc*,mean}' as
float) >= 7 ;

But I want *all *the ports that have mean >= 7.
Something akin to...

select 1 from mytbl where cast(test_results#>'{ports,***,mean}' as float)
>= 7 ;

But the "*" doesn't work :-(

Any ideas ?
Thanks!

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

[Daniel Gustafsson]

> On 30 Nov 2021, at 20:59, Tom Lane  wrote:
> 
> Vincas Dargis  writes:
>> checking which random number source to use... /dev/urandom
>> checking for /dev/urandom... 
>> configure: error: cannot check for file existence when cross compiling
> 
> Hmm ... this evidently stems from 16f96c74d.
> 
> AFAICS this is the only test in our configure script that is a hard
> fail when cross-compiling, and I don't see a reason for it to be that.
> We could just assume that /dev/urandom will be available --- that's no
> worse than a lot of the other optimistic assumptions that configure
> makes in that mode.

Agreed, I don't see a problem with that.  I'm not terribly familiar with
supporting cross compilation in autoconf, would a reasonable approach be to
just remove the check altogether like the below sketch?

diff --git a/configure.ac b/configure.ac
index a5c10b8d56..80fe0de38d 100644
--- a/configure.ac
+++ b/configure.ac
@@ -2289,13 +2289,6 @@ elif test x"$PORTNAME" = x"win32" ; then
   AC_MSG_RESULT([Windows native])
 else
   AC_MSG_RESULT([/dev/urandom])
-  AC_CHECK_FILE([/dev/urandom], [], [])
-
-  if test x"$ac_cv_file__dev_urandom" = x"no" ; then
-AC_MSG_ERROR([
-no source of strong random numbers was found
-PostgreSQL can use OpenSSL, native Windows API or /dev/urandom as a source of 
random numbers.])
-  fi
 fi

--
Daniel Gustafsson   https://vmware.com/

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

[Tom Lane]

Daniel Gustafsson  writes:
>> On 30 Nov 2021, at 20:59, Tom Lane  wrote:
>> AFAICS this is the only test in our configure script that is a hard
>> fail when cross-compiling, and I don't see a reason for it to be that.
>> We could just assume that /dev/urandom will be available --- that's no
>> worse than a lot of the other optimistic assumptions that configure
>> makes in that mode.

> Agreed, I don't see a problem with that.  I'm not terribly familiar with
> supporting cross compilation in autoconf, would a reasonable approach be to
> just remove the check altogether like the below sketch?

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
...
fi

(I'm a bit surprised that the AC_CHECK_FILE macro doesn't provide
an action-if-cross-compiling option, but it apparently doesn't.)

regards, tom lane

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

[Tom Lane]

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

[Daniel Gustafsson]

> On 30 Nov 2021, at 22: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:

+1, looks reasonable.

> +elif test x"$cross_compiling" = x"yes"; then

I noticed that we test without the x"foo" = x"yes" construction for zic (line
1135), should we change that while at it and be consistent for all
$cross_compiling uses?

--
Daniel Gustafsson   https://vmware.com/

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

[Tom Lane]

Daniel Gustafsson  writes:
> I noticed that we test without the x"foo" = x"yes" construction for zic (line
> 1135), should we change that while at it and be consistent for all
> $cross_compiling uses?

Probably.  $cross_compiling should theoretically always be set, but
there's no harm in being bulletproof.

I had thought I could crank up a cross-compile test painlessly, but
at least on my available systems it doesn't seem to be painless :-(.
So I'm just going to push the change without having really tested
that end of it.  I wonder if it is at all feasible to maintain a
cross-compiling buildfarm member ...

regards, tom lane

Re: How to reveal the codes of functions properly?

[David G. Johnston]

On Tue, Nov 30, 2021 at 1:17 PM Shaozhong SHI 
wrote:

> In what environment, that did not work.
>
>>
>>
In what environment did you wish the answer to be given?

David J.

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

[Yi Sun]

Hi Gabriel,

Thank you.

I copied root.crl again and reload postgresql

-bash-4.2$ ls -alrt /var/lib/pgsql/tls
total 24
-rw-r- 1 postgres postgres 1168 Nov 30 04:20 server.crt
-rw--- 1 postgres postgres 1679 Nov 30 04:20 server.key
-rw-r- 1 postgres postgres  688 Nov 30 04:20 root.crt
-rw-r- 1 postgres postgres  410 Nov 30 07:42 root.crl
drwx-- 4 postgres postgres 4096 Nov 30 08:02 ..
drwx-- 2 postgres postgres 4096 Nov 30 23:36 .

-bash-4.2$ psql
psql (11.11)
Type "help" for help.

postgres=# select pg_reload_conf();
 pg_reload_conf

 t
(1 row)

postgres=# show ssl_crl_file;
ssl_crl_file
-
 /var/lib/pgsql/tls/root.crl
(1 row)

--From client to connect still can connect not as expect
-bash-4.2$ psql "host=master.pgcluster11.service.consul port=5432
dbname=testdb user=test sslmode=verify-full"
Password:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)

Is there any more configuration need to do please? Thanks

Thanks and best regards
Sun Yi



Gabriel Cabillon  于2021年11月30日周二 下午10:03写道：

> El 30/11/2021 a las 10:53, Yi Sun escribió:
>
> Hi All,
>
> OS: CentOS 7.6
> PG: 11.11
>
> Our env already configured ssl
> --server postgresql.conf
> ssl = 'on'
> ssl_ca_file = '/var/lib/pgsql/tls/root.crt'
> ssl_cert_file = '/var/lib/pgsql/tls/server.crt'
> ssl_key_file = '/var/lib/pgsql/tls/server.key'
>
> --client configuration
> $ ls -alrt /var/lib/pgsql/.postgresql
> total 20
> -rw-r--r-- 1 postgres postgres  688 Nov 30 06:46 root.crt
> -rw-r--r-- 1 postgres postgres  778 Nov 30 06:46 postgresql.crt
> -rw--- 1 postgres postgres 1708 Nov 30 06:47 postgresql.key
>
> --From client to connect ssl works
> $ psql "host=master.pgcluster11.service.consul port=5432 dbname=testdb
> user=test sslmode=verify-full"
> Password:
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> bits: 256, compression: off)
>
> --Now we want to configure the ssl_crl_file and generated the root.crl
> file as below redhat doc
>
>
> https://access.redhat.com/documentation/en-us/red_hat_update_infrastructure/2.1/html/administration_guide/chap-red_hat_update_infrastructure-administration_guide-certification_revocation_list_crl
>
> --Use openssl to verify, shows "certificate revoked"
> # cat /home/sunyi/tls/root.crt /home/sunyi/tls/1/root.crl > /tmp/test_1.pem
> # openssl verify -extended_crl -verbose -CAfile /tmp/test_1.pem -crl_check
> /home/sunyi/tls/1/server.crt
> /home/sunyi/tls/1/server.crt: O = Acronis, OU = DBS, CN =
> s12345y-patroni_cluster-507460701
> error 23 at 0 depth lookup:certificate revoked
>
> --copy root.crl file to /var/lib/pgsql/tls
> -bash-4.2$ ls -alrt /var/lib/pgsql/tls
> total 20
> drwx-- 4 postgres postgres 4096 Nov 30 04:20 ..
> -rw-r- 1 postgres postgres 1164 Nov 30 04:20 server.crt
> -rw--- 1 postgres postgres 1679 Nov 30 04:20 server.key
> -rw-r- 1 postgres postgres  688 Nov 30 04:20 root.crt
> drwx-- 2 postgres postgres 4096 Nov 30 04:20 .
>
> --Configure /var/lib/pgsql/11/data/postgresql.conf
> ssl_crl_file = '/var/lib/pgsql/tls/root.crl'
>
> --Reload postgresql
> $ psql
> psql (11.11)
> Type "help" for help.
>
> postgres=# select pg_reload_conf();
>  pg_reload_conf
> 
>  t
> (1 row)
> postgres=# show ssl_crl_file;
> ssl_crl_file
> -
>  /var/lib/pgsql/tls/root.crl
> (1 row)
>
> --From client to connect still can connect not as expect
> $ psql "host=master.pgcluster11.service.consul port=5432 dbname=testdb
> user=test sslmode=verify-full"
> Password:
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> bits: 256, compression: off)
>
> Could you please advise if need any more configuration? Thanks
>
> Thanks and best regards
> Sun Yi
>
>
> Hi,
>
> according to the ls command it seems you copied root.crt instead of
> root.crl
>
> Yours,
> Gabriel
>

Re: Wildcarding json keys in json query

[David G. Johnston]

On Tue, Nov 30, 2021 at 1:40 PM David Gauthier 
wrote:

> {
> ports : {
> port_abc:{min: 5, max: 7, mean: 6},
> port_def:{min: 5, max: 9, mean: 7},
> port_ghi:{min: 6, max: 10, mean: 8}
> }
> }
>
> select 1 from mytbl where cast(test_results#>'{ports,***,mean}' as float)
> >= 7 ;
>
> But the "*" doesn't work :-(
>
> Any ideas ?
>
>
If you have the option to not use data values in json field identifiers
(i.e., object keys) you will avoid fighting against the system's underlying
design choices.  i.e., IMO, ports should contain an array, not an object.

I was under the impression that SQL/JSON Path (jsonpath)
functions/operators solve some of these kinds of problems so try using that
instead of the "fixed structure" access-based functions and operators.

David J.

Re: Database Scalability

[Saurav Sarkar]

Hi Ben,

Thanks a lot for your reply.

So are all the schemas on one DB or are distributed/sharded across multiple
DBs ?

Best Regards,
Saurav

On Tue, Nov 30, 2021 at 11:43 PM Ben Chobot  wrote:

> Saurav Sarkar wrote on 11/29/21 10:13 PM:
>
> Hi All,
>
> We have some multi-tenant solutions which are separating the tenant data
> in Postgresql mainly in the following manner.
>
> 1. Using different schemas
> 2. Using different tables for different tenants.
>
>
> Without more details it's impossible to give you a detailed answer, so, in
> general if you are breaking out your client data by schema, you will
> likely be fine. We have used this method with great success to scale our
> customer workload - each customer gets their own schema, which we can then
> rebalance between databases as those clients drive more load over time. For
> those tables that we want partitioned, we simply partition them in every
> schema. That's arguably inefficient but we find the schema consistency to
> be an overall win.
>
> (For context, when I say we have used this method with great success, we
> have over 13k customers, almost a PB of data, peak around 1.5M iops, and
> it's all painless to operate.)
>

Re: Database Scalability

[Mladen Gogala]

On 11/30/21 22:08, Saurav Sarkar wrote:

Hi Ben,

Thanks a lot for your reply.

So are all the schemas on one DB or are distributed/sharded across 
multiple DBs ?


Best Regards,
Saurav


To my knowledge PostgreSQL doesn't support sharding, which is well and 
good because sharding is mostly useless, at least in my opinion.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Database Scalability

[Michael Stephenson]

Store a connection string for each tenant or the metadata to build one on 
demand.  Then each tenant is in its own schema on whatever database instance 
the connection string points at.  Then it doesn’t really matter how you spread 
your tenants across one database or many; just do whatever works best and 
adjust as needed.

~ Mike

Re: [EXT] Re: XQuery/XPath 2.0+ support

[Garfield Lewis]

Thx, Tom...

But isn't the libxml2 library not sufficient for this purpose? Note that I have 
not tried it yet, I am still just investigating possible solutions.

-- 
Regards,
Garfield A. Lewis

On 2021-11-29, 12:08 PM, "Tom Lane"  wrote:

Garfield Lewis  writes:
> I am investigating whether there are any plans to have XPath 2.0 support 
for Postgres XML natively or are there any available extension packages that 
includes this support?

I don't know of anyone actively working on that.  The difficulty is
that we don't especially want to write/maintain such logic ourselves,
but there don't seem to be any suitable libraries available that
(a) can be called from C and (b) have a compatible license.

regards, tom lane

Re: [EXT] Re: XQuery/XPath 2.0+ support

[Pavel Stehule]

Hi

st 1. 12. 2021 v 6:28 odesílatel Garfield Lewis 
napsal:

> Thx, Tom...
>
> But isn't the libxml2 library not sufficient for this purpose? Note that I
> have not tried it yet, I am still just investigating possible solutions.
>

Unfortunately, no, or it is not about Postgres. The development of libxml2
is frozen :-/ and there are only bugfix releases. What I know, there are
only some experimental enhancements, but nobody has finalized it.

https://stackoverflow.com/questions/6586896/does-libxml2-support-xpath-2-0-or-not

XPath 2.0 is much more complex than XPath - it is a new language.

Please, don't send top post mails to this mailing list

https://en.wikipedia.org/wiki/Posting_style#Top-posting

Regards

Pavel



> --
> Regards,
> Garfield A. Lewis
>
> On 2021-11-29, 12:08 PM, "Tom Lane"  wrote:
>
> Garfield Lewis  writes:
> > I am investigating whether there are any plans to have XPath 2.0
> support for Postgres XML natively or are there any available extension
> packages that includes this support?
>
> I don't know of anyone actively working on that.  The difficulty is
> that we don't especially want to write/maintain such logic ourselves,
> but there don't seem to be any suitable libraries available that
> (a) can be called from C and (b) have a compatible license.
>
> regards, tom lane
>
>

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

[Peter Eisentraut]

On 30.11.21 22:04, Tom Lane wrote:

Daniel Gustafsson  writes:

On 30 Nov 2021, at 20:59, Tom Lane  wrote:
AFAICS this is the only test in our configure script that is a hard
fail when cross-compiling, and I don't see a reason for it to be that.
We could just assume that /dev/urandom will be available --- that's no
worse than a lot of the other optimistic assumptions that configure
makes in that mode.



Agreed, I don't see a problem with that.  I'm not terribly familiar with
supporting cross compilation in autoconf, would a reasonable approach be to
just remove the check altogether like the below sketch?


It seems like a useful test when *not* cross compiling, which is most
of the time.


You still don't know whether the file will exist on the system you are 
running this on.



(I'm a bit surprised that the AC_CHECK_FILE macro doesn't provide
an action-if-cross-compiling option, but it apparently doesn't.)


Because you are only supposed to look for files that you need during the 
build.

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

[Kyotaro Horiguchi]

At Tue, 30 Nov 2021 21:53:06 +0800, Yi Sun  wrote in 
> # cat /home/sunyi/tls/root.crt /home/sunyi/tls/1/root.crl > /tmp/test_1.pem
> # openssl verify -extended_crl -verbose -CAfile /tmp/test_1.pem -crl_check
> /home/sunyi/tls/1/server.crt

I guess what you really wanted to revoke was not server.crt but
postgresql.crt.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

RE: Wildcarding json keys in json query

[Patrick FICHE]

I guess you could try something like this

SELECT * FROM ( SELECT * from json_each(( '{
  "ports" : {
  "port_abc":{"min": 5, "max": 
7, "mean": 6},
  "port_def":{"min": 5, "max": 
9, "mean": 7},
  "port_ghi":{"min": 6, "max": 
10, "mean": 8}
  }
  }'::json->'ports'
))
  ) T
WHERE (value::json->>'mean')::float >= 7;

From: David Gauthier 
Sent: Tuesday, November 30, 2021 9:40 PM
To: Postgres General 
Subject: Wildcarding json keys in json query

PG 11.5 on linux

Let's say I store a jsonb in a column called test_results that looks like 
this...

{
ports : {
port_abc:{min: 5, max: 7, mean: 6},
port_def:{min: 5, max: 9, mean: 7},
port_ghi:{min: 6, max: 10, mean: 8}
}
}

And I want to to get all the port names where the mean is >= 7.

This works...

select 1 from mytbl where cast(test_results#>'{ports,port_abc,mean}' as float) 
>= 7 ;

But I want all the ports that have mean >= 7.
Something akin to...

select 1 from mytbl where cast(test_results#>'{ports,*,mean}' as float) >= 7 ;

But the "*" doesn't work :-(

Any ideas ?
Thanks!

Re: case insensitive collation of Greek's sigma

[Frank Limpert]

Am 26.11.21 um 08:37 schrieb Jakub Jedelsky:

Hello,

during our tests of Postgres with ICU we found an issue with ILIKE of 
upper and lowercase sigma (Σ). The letter has two lowercase variants σ 
and ς (at the end of a word). I'm working with en_US and en-US-x-icu 
collations and results are a bit unexpected - they are inverted:


postgres=# SELECT
postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en_US",
postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en_US"
postgres-# ;
 ?column? | ?column?
--+--
 t        | f
(1 row)

postgres=# SELECT
postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en-US-x-icu",
postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en-US-x-icu";
 ?column? | ?column?
--+--
 f        | t
(1 row)

I run those commands on the latest (14.1) official docker image.

Is it possible to unify the behaviour?And which one is correct from 
the community point of view?


If I could start, I think both results are wrong as both should return 
True. If I got it right, in the background there is a lower() function 
running to compare strings, which is not enough for such cases (until 
the left side isn't taken as a standalone word).


Thanks,

- jj


Have you seen the subtle intricacies in this example?

=> SELECT 'ΣΣ Μ' ILIKE 'σσ Μ' COLLATE "en_US" AS c0,
      'ΣΣ Μ' ILIKE 'σς Μ' COLLATE "en_US" AS c1,
          'ΣΣ Μ' ~*    'σσ Μ' COLLATE "el-GR-x-icu" AS c2,
      'ΣΣ Μ' ~*    'σς Μ' COLLATE "el-GR-x-icu" AS c3,
      'ΣΣ Μ' ILIKE 'σσ Μ' COLLATE "el-GR-x-icu" AS c4,
          'ΣΣ Μ' ILIKE 'σς Μ' COLLATE "el-GR-x-icu" AS c5,
      'ΣΣ Μ' ~*    'σσ Μ' COLLATE "en-US-x-icu" AS c6,
      'ΣΣ Μ' ~*    'σς Μ' COLLATE "en-US-x-icu" AS c7,
          'ΣΣ Μ' ILIKE 'σσ Μ' COLLATE "en-US-x-icu" AS c8,
      'ΣΣ Μ' ILIKE 'σς Μ' COLLATE "en-US-x-icu" AS c9;
 c0 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9
+++++++++
 t  | f  | t  | t  | f  | t  | t  | t  | f  | t
(1 row)

Obviously, the ILIKE operator is really strict regarding to the correct 
letter at the end of the word. The regular expression operator works as 
you expected.


Happy computing...
Frank