Re: Max connections reached without max connections reached

[Dilip Kumar]

On Tue, Nov 30, 2021 at 5:16 AM James Sewell  wrote:
>>
>> How did you verify that, maybe some process started IO and stuck
>> there? Can we check pg_stat_activity that is there some process that
>> shows in the wait event as SLRURead/SLRUWrite and not coming out of
>> that state?

Looking again into the back trace[1], it appeared that the backend is
getting stuck while getting the subtransaction's parent information,
and that can only happen if the snapshot is getting marked as
overflowed.  So it seems that some of the scripts are using a lot of
sub-transaction (> 64) within a transaction and in such case, we can
not cache the sub-transaction information and the snapshot will be
marked overflowed.  After that, for checking the visibility of any
tuple we need to consult the SLRU to get TopMost transaction
information and if the subtransaction SLRU cache is full then it will
have to do the I/O.  So in this case it appeared that a lot of
parallel scans/new connections are trying to read the pg_subtrans and
due to limited cache size that is not even able to load the data in
the cache and getting frequently blocked.  So I assume that when you
say the startup is getting stuck forever, how long did you wait, maybe
it is moving but getting stuck again for the next tuple, so to confirm
that attach the process with gdb put breakpoint in XidInMVCCSnapshot()
and check whether it is hitting that breakpoint again or not.  Also,
check whether you are seeing "SubtransControlLock" in
pg_stat_activity.  There is one nice blog[2] explaining this issue so
you can refer to that as well.


[1]
   21 #2  0x7f18fb9dec6b in sem_wait@@GLIBC_2.2.5 () from
/lib64/libpthread.so.0
   20 #3  0x0071c482 in PGSemaphoreLock ()
   19 #4  0x0079078c in LWLockAcquire ()
   18 #5  0x004fa9ae in SimpleLruWaitIO.isra.1 ()
   17 #6  0x004fb2a4 in SimpleLruReadPage ()
   16 #7  0x004fbc07 in SubTransGetParent ()
   15 #8  0x004fbc57 in SubTransGetTopmostTransaction ()
   14 #9  0x008f65a7 in XidInMVCCSnapshot ()
   13 #10 0x008f662e in HeapTupleSatisfiesMVCC ()
   12 #11 0x004c436e in heapgetpage ()

[2]https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

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

[Yi Sun]

Hi Kyotaro,

We want to revoke server certificate, just don't know why doesn't take
affect
https://www.postgresql.org/docs/11/ssl-tcp.html
https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-SSL-CRL-FILE

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

> 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: Issues cross-compiling libpq 14.x to MacOS armv8

[Daniel Gustafsson]

> On 1 Dec 2021, at 07:11, Peter Eisentraut  
> wrote:
> On 30.11.21 22:04, Tom Lane wrote:

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

So by that logic, do you think the AC_CHECK_FILE call should be removed?

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

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

[Tom Lane]

Daniel Gustafsson  writes:
>> On 1 Dec 2021, at 07:11, Peter Eisentraut 
>>  wrote:
>> Because you are only supposed to look for files that you need during the 
>> build.

> So by that logic, do you think the AC_CHECK_FILE call should be removed?

I don't buy that.  The test is useful on net, and I don't particularly
believe that /dev/urandom will be there on one instance of a platform
and not another.

regards, tom lane

Re: Logical replication issue after Postgresql upgrade from 13 to 14

[Marcos Pegoraro]

> 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?
>

Amit, wouldn't it be better to document all steps needed to use pg_upgrade
with logical replication ?
Sergey is showing a different problem than mine.

Re: Logical replication issue after Postgresql upgrade from 13 to 14

[Sergey Belyashov]

I think there are some bugs in Posgresql logical replication upgrade.
Because dropping and recreating subscriptions with manual
synchronization has solved the problem for me. But it is not the
correct way, IMHO.

Sergey Belyashov

ср, 1 дек. 2021 г. в 15:26, Marcos Pegoraro :
>
>
>> 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?
>
>
> Amit, wouldn't it be better to document all steps needed to use pg_upgrade 
> with logical replication ?
> Sergey is showing a different problem than mine.

Linux: directory change .../lib to .../lib64

[Matthias Apitz]

Hello,

Below the top level directory (--prefix) the lib directory changed with
version 14.x now from .../lib to .../lib64:

ls -ld /usr/local/sisis-pap/pgsql-*/li*
drwxr-xr-x 1 root root 2754 19. Jul 09:58 /usr/local/sisis-pap/pgsql-13.3/lib
drwxr-xr-x 1 root root 1594 19. Okt 10:36 /usr/local/sisis-pap/pgsql-14.0/lib64
drwxr-xr-x 1 root root 2810 29. Nov 10:59 /usr/local/sisis-pap/pgsql-14.1/lib64

This would mean changes of LD_LIBRARY_PATH of our servers using the
PostgreSQL server with ESQL/C interface (libecpg.so.6). Why this change
was made? For a future 128-bit Linux?

I will see if I can overwrite this with:

./configure --prefix=/usr/local/sisis-pap/pgsql-14.1 \
--libdir=/usr/local/sisis-pap/pgsql-14.1/lib ...

Btw: the help of ./configure says wrong things about the default:

./configure --help | egrep 'libdir=|prefix='
  --prefix=PREFIX install architecture-independent files in PREFIX
  --exec-prefix=EPREFIX   install architecture-dependent files in EPREFIX for 
instance `--prefix=$HOME'.
  --libdir=DIRobject code libraries [EPREFIX/lib]

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Re: Logical replication issue after Postgresql upgrade from 13 to 14

[Amit Kapila]

On Wed, Dec 1, 2021 at 5:56 PM Marcos Pegoraro  wrote:
>
>> 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?
>
>
> Amit, wouldn't it be better to document all steps needed to use pg_upgrade 
> with logical replication ?
> Sergey is showing a different problem than mine.
>

I am fine with that. Feel free to propose a patch on -hackers. I can
help in reviewing and improving the same.

-- 
With Regards,
Amit Kapila.

Re: Linux: directory change .../lib to .../lib64

[Tom Lane]

Matthias Apitz  writes:
> Below the top level directory (--prefix) the lib directory changed with
> version 14.x now from .../lib to .../lib64:

> ls -ld /usr/local/sisis-pap/pgsql-*/li*
> drwxr-xr-x 1 root root 2754 19. Jul 09:58 /usr/local/sisis-pap/pgsql-13.3/lib
> drwxr-xr-x 1 root root 1594 19. Okt 10:36 
> /usr/local/sisis-pap/pgsql-14.0/lib64
> drwxr-xr-x 1 root root 2810 29. Nov 10:59 
> /usr/local/sisis-pap/pgsql-14.1/lib64

> This would mean changes of LD_LIBRARY_PATH of our servers using the
> PostgreSQL server with ESQL/C interface (libecpg.so.6). Why this change
> was made?

You'd have to talk to the packager of whatever distribution you're using.
No such change was made in the core PG code: the configure script still
defaults to LIBDIR=EPREFIX/lib.

regards, tom lane

Re: Linux: directory change .../lib to .../lib64

[Matthias Apitz]

El día Mittwoch, Dezember 01, 2021 a las 08:11:34 -0500, Tom Lane escribió:

> Matthias Apitz  writes:
> > Below the top level directory (--prefix) the lib directory changed with
> > version 14.x now from .../lib to .../lib64:
> 
> > ls -ld /usr/local/sisis-pap/pgsql-*/li*
> > drwxr-xr-x 1 root root 2754 19. Jul 09:58 
> > /usr/local/sisis-pap/pgsql-13.3/lib
> > drwxr-xr-x 1 root root 1594 19. Okt 10:36 
> > /usr/local/sisis-pap/pgsql-14.0/lib64
> > drwxr-xr-x 1 root root 2810 29. Nov 10:59 
> > /usr/local/sisis-pap/pgsql-14.1/lib64
> 
> > This would mean changes of LD_LIBRARY_PATH of our servers using the
> > PostgreSQL server with ESQL/C interface (libecpg.so.6). Why this change
> > was made?
> 
> You'd have to talk to the packager of whatever distribution you're using.
> No such change was made in the core PG code: the configure script still
> defaults to LIBDIR=EPREFIX/lib.

It's me who is the packager. I compiled from source and the used
configure run is:

#!/bin/sh

cd ~sisis/mpi-V73/postgresql-14.1 || exit

export LDFLAGS="-L/usr/local/sisis-pap/lib -L/usr/lib64"
export CFLAGS="-m64 -I/usr/local/sisis-pap/include"
export CPPFLAGS="-m64 -I/usr/local/sisis-pap/include"

./configure --prefix=/usr/local/sisis-pap/pgsql-14.1 \
--enable-nls \
--with-perl \
--with-ldap \
--with-pam \
--with-openssl

echo now run \'make world\' and as root \'make install-world\'

And this brings the shared libs to  /usr/local/sisis-pap/pgsql-14.1/lib64

Thanks

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Re: Linux: directory change .../lib to .../lib64

[Matthias Apitz]

El día Mittwoch, Dezember 01, 2021 a las 08:11:34 -0500, Tom Lane escribió:

> Matthias Apitz  writes:
> > Below the top level directory (--prefix) the lib directory changed with
> > version 14.x now from .../lib to .../lib64:
> 
> > ls -ld /usr/local/sisis-pap/pgsql-*/li*
> > drwxr-xr-x 1 root root 2754 19. Jul 09:58 
> > /usr/local/sisis-pap/pgsql-13.3/lib
> > drwxr-xr-x 1 root root 1594 19. Okt 10:36 
> > /usr/local/sisis-pap/pgsql-14.0/lib64
> > drwxr-xr-x 1 root root 2810 29. Nov 10:59 
> > /usr/local/sisis-pap/pgsql-14.1/lib64
> 
> > This would mean changes of LD_LIBRARY_PATH of our servers using the
> > PostgreSQL server with ESQL/C interface (libecpg.so.6). Why this change
> > was made?

>From the file config.log:

...
configure:2687: loading site script /usr/share/site/x86_64-unknown-linux-gnu
| #!/bin/sh
| # Site script for configure. It is resourced via $CONFIG_SITE environment 
varaible.
|
| # If user did not specify libdir, guess the correct target:
| # Use lib64 for 64 bit bi-arch targets, keep the default for the rest.
| if test "$libdir" = '${exec_prefix}/lib' ; then
...

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Re: Linux: directory change .../lib to .../lib64

[Ron]

On 12/1/21 8:14 AM, Matthias Apitz wrote:
[snip]

From the file config.log:
...
configure:2687: loading site script /usr/share/site/x86_64-unknown-linux-gnu
| #!/bin/sh
| # Site script for configure. It is resourced via $CONFIG_SITE environment 
varaible.
|
| # If user did not specify libdir, guess the correct target:
| # Use lib64 for 64 bit bi-arch targets, keep the default for the rest.
| if test "$libdir" = '${exec_prefix}/lib' ; then
...


Is bi-arch really still relevant in 2021 (almost 2022)?

--
Angular momentum makes the world go 'round.

Re: Database Scalability

[Ben Chobot]

Saurav Sarkar wrote on 11/30/21 7:08 PM:
So are all the schemas on one DB or are distributed/sharded across 
multiple DBs ?
In our use case, every db entirely homes one or more schemas. Some dbs 
host many schemas for small customers, some dbs host a handful of 
schemas for medium customers, and some dbs are dedicated to a single 
large customer. If the customer is very large, we get a bigger db than 
normal for them. So theoretically we still have a problem of vertical 
scale but in practice it's rarely an issue and when it is, we can 
usually split that single customer into multiple, at the application layer.

Re: Database Scalability

[Ben Chobot]

Mladen Gogala wrote on 11/30/21 7:52 PM:


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



OK I'll bite.

Not only does PostgreSQL natively support table partitioning (which is 
absolutely a form of sharding), there multiple well-regarded extensions 
that can help with sharding, all of which are orthogonal to how you can 
configure your application to use Postgres in the first place. So to say 
Postgres doesn't support sharding is misleading, at best.


Also, the general concept of sharding to move your scaling challenges 
from vertical ones to horizontal ones has multiple self-evident 
advantages. If your work history has all happened to fit on a single 
server, then bully for you, but not everybody has it so easy.

Re: Linux: directory change .../lib to .../lib64

[Tom Lane]

Matthias Apitz  writes:
> configure:2687: loading site script /usr/share/site/x86_64-unknown-linux-gnu
> | #!/bin/sh
> | # Site script for configure. It is resourced via $CONFIG_SITE environment 
> varaible.
> |
> | # If user did not specify libdir, guess the correct target:
> | # Use lib64 for 64 bit bi-arch targets, keep the default for the rest.
> | if test "$libdir" = '${exec_prefix}/lib' ; then
> ...

Well, Autoconf's provision for site-local scripts is very ancient.
I say again that this isn't anything we've changed: either that
file wasn't there last time you did this, or its contents changed
underneath you.

regards, tom lane

Re: Database Scalability

[SQL Padawan]

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


> Not only does PostgreSQL natively support table partitioning (which is
>
> absolutely a form of sharding), there multiple well-regarded extensions
>
> that can help with sharding, all of which are orthogonal to how you can
>
> configure your application to use Postgres in the first place. So to say
>
> Postgres doesn't support sharding is misleading, at best.
>
> Also, the general concept of sharding to move your scaling challenges
>
> from vertical ones to horizontal ones has multiple self-evident
>
> advantages. If your work history has all happened to fit on a single
>
> server, then bully for you, but not everybody has it so easy.

It supports partitioning out of the box - not sharding where different tables 
reside on different machines!

CitusData and TimescaleDB provide sharding as extensions - both of which appear 
useful for TimeSeries data. There was PostgresXL which was a general sharding 
(multi-machine) solution that appears to have died.

SQLP!

Pgcrypto extension - decrypt(encrypt(... not returning original data?

[SQL Padawan]

I tried to use the pgcrypto extension.

from the manual.

https://www.postgresql.org/docs/14/pgcrypto.html

there are two functions - encrypt and decrypt - signatures as follows.

encrypt(data bytea, key bytea, type text) returns bytea
decrypt(data bytea, key bytea, type text) returns bytea

OK.

I try to run this -- see a fiddle

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=d2c102f5fb6e62acb1c70a2dc755fdca

SELECT
encrypt('da'::BYTEA, 'pw'::BYTEA, 'bf'),
pg_typeof(encrypt('da'::BYTEA, 'pw'::BYTEA, 'bf')),
decrypt(encrypt('da'::BYTEA, 'pw'::BYTEA, 'bf'), 'pw', 'bf');

but I get this as my result.

encrypt pg_typeof   decrypt
\x54027d78b34ac951  bytea   \x6461

Why is my decrypt function not return the string 'da'? Have I not understand 
something important?

SQLP!

Re: Pgcrypto extension - decrypt(encrypt(... not returning original data?

[Wim Bertels]

hex 2 ascii

SQL Padawan schreef op wo 01-12-2021 om 18:37 [+]:
> \x6461

-- 
mvg,
Wim Bertels
--
https://coronalert.be

Lector
UC Leuven-Limburg
--
Truth is the most valuable thing we have -- so let us economize it.
-- Mark Twain

Re: Pgcrypto extension - decrypt(encrypt(... not returning original data?

[Tom Lane]

SQL Padawan  writes:
> Why is my decrypt function not return the string 'da'? Have I not understand 
> something important?

See the bytea_output setting.

regards, tom lane

INSERT ... ON CONFLICT doesn't work

[Jenda Krynicky]

So let's suppose I have a table like this:

CREATE TABLE IF NOT EXISTS public."Lockers"
(
"Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( 
INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
"Uuid" text COLLATE pg_catalog."default",
"IpAddress" text COLLATE pg_catalog."default",
"State" integer NOT NULL,
"DoorColumns" bytea,
"ConnectionStatus" integer NOT NULL,
"LastConnected" timestamp without time zone,
"LastReportId" integer,
"LongOpenedDoors" bit varying,
"Created" timestamp without time zone,
"Updated" timestamp without time zone,
CONSTRAINT "PK_Lockers" PRIMARY KEY ("Id")
)

CREATE UNIQUE INDEX IF NOT EXISTS "IX_Lockers_Uuid"
ON public."Lockers" USING btree
("Uuid" COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;

and a function like this:

CREATE OR REPLACE FUNCTION public.findorcreatelocker(
lockeruuid text,
ipaddress text)
RETURNS TABLE("Id" integer, "Created" timestamp without time 
zone, "Uuid" text, "State" integer, "ConnectionStatus" integer, 
"LastConnected" timestamp without time zone, "DoorColumns" bytea, 
"IpAddress" text, "LastReportCreated" timestamp without time zone) 
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000

AS $BODY$

declare
updated numeric;
current timestamp;
begin
current := timezone('utc', now());

update "Lockers"
set "ConnectionStatus" = 0/*connected*/,
"LastConnected" = current,
"IpAddress" = COALESCE(ipAddress, "Lockers"."IpAddress"),
"Updated" = current
where "Lockers"."Uuid" = lockerUuid;
GET DIAGNOSTICS updated = ROW_COUNT;

IF (updated = 0) then
INSERT INTO "Lockers" ("Uuid", "IpAddress", "State", 
"DoorColumns", "ConnectionStatus", "LastConnected", "LastReportId", 
"LongOpenedDoors", "Created", "Updated")
VALUES (lockerUuid, ipAddress, 0/*new*/, null, 
0/*connected*/, current, null, null, current, current)
ON CONFLICT ("Uuid")
DO NOTHING;
end if;

return Query (
SELECT 
"Lockers"."Id", 
"Lockers"."Created",
"Lockers"."Uuid",
"Lockers"."State",
"Lockers"."ConnectionStatus",
"Lockers"."LastConnected",
"Lockers"."DoorColumns",
"Lockers"."IpAddress", 
"LockerReports"."Created" as "LastReportCreated" 
FROM "Lockers" 
LEFT JOIN "LockerReports" ON "LockerReports"."Id" = 
"Lockers"."LastReportId"
WHERE "Lockers"."Uuid" = lockerUuid);
end;
$BODY$;


How the (censored) am I supposed to write the ON CONFLICT () clause 
so that it works?

Like this it reports:

ERROR:  column reference "Uuid" is ambiguous
LINE 3: ON CONFLICT ("Uuid")
^
DETAIL:  It could refer to either a PL/pgSQL variable or a table 
column.


THERE IS NO (CENSORED) VARIABLE "Uuid"!


If I drop the quotes and use just
ON CONFLICT (Uuid)
I get 

ERROR:  column "uuid" does not exist
LINE 3: ON CONFLICT (Uuid)
^
HINT:  Perhaps you meant to reference the column "Lockers.Uuid".

Yes, thank you, that's exactly what I meant. That's what I wrote too, 
you (censored). I didn't write uuid, I wrote Uuid, you imbecile!


If I try to include the table name as ON CONFLICT (Lockers.Uuid) or 
ON CONFLICT ("Lockers"."Uuid"), I get a syntax error.

If I specify it as 
ON CONFLICT ("Lockers.Uuid")
I get quite understandably
ERROR:  column "Lockers.Uuid" does not exist
LINE 3: ON CONFLICT ("Lockers.Uuid")


So pretty please with a cherry on top, how do I explain to postgres 
13.4, that yes indeed by "Uuid" I mean the stinking column "Uuid".

Jenda

=== je...@krynicky.cz == http://Jenda.Krynicky.cz ==
There is a reason for living. There must be. I've seen it somewhere.
It's just that in the mess on my table ... and in my brain
I can't find it.
--- me

Re: case insensitive collation of Greek's sigma

[Peter Eisentraut]

On 26.11.21 08:37, Jakub Jedelsky wrote:

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)

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


The reason for these results is that for multibyte encodings, a ILIKE b 
basically does lower(a) LIKE lower(b), and


select lower('ΣΣ' COLLATE "en_US"), lower('ΣΣ' COLLATE "en-US-x-icu");
 lower | lower
---+---
 σσ| σς

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.

Re: INSERT ... ON CONFLICT doesn't work

[Adrian Klaver]

On 12/1/21 11:20 AM, Jenda Krynicky wrote:

So let's suppose I have a table like this:





So pretty please with a cherry on top, how do I explain to postgres
13.4, that yes indeed by "Uuid" I mean the stinking column "Uuid".


The basic issue is described here:

https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

"Since the names of variables are syntactically no different from the 
names of table columns, there can be ambiguity in statements that also 
refer to tables: is a given name meant to refer to a table column, or a 
variable? Let's change the previous example to ..."


ERROR:  column reference "Uuid" is ambiguous
LINE 3: ON CONFLICT ("Uuid")

Is occurring because there is ambiguity between:

"Uuid" text

in RETURNS TABLE and

"Lockers"."Uuid"

I would say the easiest way out of this is to change:

"Uuid" text --> "uuid_out" text



Jenda




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

Re: INSERT ... ON CONFLICT doesn't work

[David G. Johnston]

On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky  wrote:

>
> CREATE OR REPLACE FUNCTION public.findorcreatelocker(
> lockeruuid text,
> ipaddress text)
> RETURNS TABLE("Id" integer, "Created" timestamp without time
> zone, "Uuid" text, "State" integer, "ConnectionStatus" integer,
> "LastConnected" timestamp without time zone, "DoorColumns" bytea,
> "IpAddress" text, "LastReportCreated" timestamp without time zone)
>
>

> INSERT INTO "Lockers" ("Uuid", "IpAddress", "State",
> "DoorColumns", "ConnectionStatus", "LastConnected", "LastReportId",
> "LongOpenedDoors", "Created", "Updated")
> VALUES (lockerUuid, ipAddress, 0/*new*/, null,
> 0/*connected*/, current, null, null, current, current)
> ON CONFLICT ("Uuid")
> DO NOTHING;
> end if;
>
> How the (censored) am I supposed to write the ON CONFLICT () clause
> so that it works?
>
> Like this it reports:
>
> ERROR:  column reference "Uuid" is ambiguous
> LINE 3: ON CONFLICT ("Uuid")
> ^
> DETAIL:  It could refer to either a PL/pgSQL variable or a table
> column.
>
>
> THERE IS NO (CENSORED) VARIABLE "Uuid"!
>

Yes, there is.  RETURNS TABLE (... "Uuid" text ... )

Changing that to something else should remove the ambiguity.  I agree it is
not an ideal solution though.  I'm not sure what other options exist though.


> If I drop the quotes and use just
> ON CONFLICT (Uuid)
> I get
>
> ERROR:  column "uuid" does not exist
> LINE 3: ON CONFLICT (Uuid)
> ^
> HINT:  Perhaps you meant to reference the column "Lockers.Uuid".
>
> Yes, thank you, that's exactly what I meant. That's what I wrote too,
> you (censored). I didn't write uuid, I wrote Uuid, you imbecile!
>

This one is on you for removing the double quotes that your choice of
identifier names forces you to basically put everywhere.


> If I try to include the table name as ON CONFLICT (Lockers.Uuid) or


Even if you didn't get a syntax error that isn't the name of your column...


> ON CONFLICT ("Lockers"."Uuid"), I get a syntax error.
>

This is the one that should work so if its giving a syntax error it is the
one worth questioning.


> If I specify it as
> ON CONFLICT ("Lockers.Uuid")
> I get quite understandably
> ERROR:  column "Lockers.Uuid" does not exist
> LINE 3: ON CONFLICT ("Lockers.Uuid")
>
>
Yes, the double quotes in the earlier "Perhaps you meant" error message are
a readability thing, not a "write this instead" thing.

David J.

Re: INSERT ... ON CONFLICT doesn't work

[Jenda Krynicky]

From:   "David G. Johnston" 
> On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky  wrote:
> 
> >
> > CREATE OR REPLACE FUNCTION public.findorcreatelocker(
> > lockeruuid text,
> > ipaddress text)
> > RETURNS TABLE("Id" integer, "Created" timestamp without time
> > zone, "Uuid" text, "State" integer, "ConnectionStatus" integer,
> > "LastConnected" timestamp without time zone, "DoorColumns" bytea,
> > "IpAddress" text, "LastReportCreated" timestamp without time zone)
> >
> >
> 
> > INSERT INTO "Lockers" ("Uuid", "IpAddress", "State",
> > "DoorColumns", "ConnectionStatus", "LastConnected", "LastReportId",
> > "LongOpenedDoors", "Created", "Updated")
> > VALUES (lockerUuid, ipAddress, 0/*new*/, null,
> > 0/*connected*/, current, null, null, current, current)
> > ON CONFLICT ("Uuid")
> > DO NOTHING;
> > end if;
> >
> > How the (censored) am I supposed to write the ON CONFLICT () clause
> > so that it works?
> >
> > Like this it reports:
> >
> > ERROR:  column reference "Uuid" is ambiguous
> > LINE 3: ON CONFLICT ("Uuid")
> > ^
> > DETAIL:  It could refer to either a PL/pgSQL variable or a table
> > column.
> >
> >
> > THERE IS NO (CENSORED) VARIABLE "Uuid"!
> >
> 
> Yes, there is.  RETURNS TABLE (... "Uuid" text ... )
> 
> Changing that to something else should remove the ambiguity.  I agree it is
> not an ideal solution though.  I'm not sure what other options exist though.

How's that a variable for gawd's sake? It's a column name too! A 
column name in the definition of the resulting table.

> > If I drop the quotes and use just
> > ON CONFLICT (Uuid)
> > I get
> >
> > ERROR:  column "uuid" does not exist
> > LINE 3: ON CONFLICT (Uuid)
> > ^
> > HINT:  Perhaps you meant to reference the column "Lockers.Uuid".
> >
> > Yes, thank you, that's exactly what I meant. That's what I wrote too,
> > you (censored). I didn't write uuid, I wrote Uuid, you imbecile!
> >
> 
> This one is on you for removing the double quotes that your choice of
> identifier names forces you to basically put everywhere.

Right. Because lowercasing everything I write and then comparing it case 
sensitively to the names of database objects makes a lot of sense. I mean who 
would want to use capital letters in names of objects in the first place?

Oh, PostgreSQL, how I hate thee, let me count the ways.


Jenda
= je...@krynicky.cz === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed 
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

Re: INSERT ... ON CONFLICT doesn't work

[Jenda Krynicky]

From:   Adrian Klaver 
> On 12/1/21 11:20 AM, Jenda Krynicky wrote:
> > So let's suppose I have a table like this:
> > 
> 
> > 
> > So pretty please with a cherry on top, how do I explain to postgres
> > 13.4, that yes indeed by "Uuid" I mean the stinking column "Uuid".
> 
> The basic issue is described here:
> 
> https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST
> 
> "Since the names of variables are syntactically no different from the 
> names of table columns, there can be ambiguity in statements that also 
> refer to tables: is a given name meant to refer to a table column, or a 
> variable? Let's change the previous example to ..."

Looks like a bad design.

> 
> ERROR:  column reference "Uuid" is ambiguous
> LINE 3: ON CONFLICT ("Uuid")
> 
> Is occurring because there is ambiguity between:
> 
> "Uuid" text
> 
> in RETURNS TABLE and
> 
> "Lockers"."Uuid"

While the ON CONFLICT () very explicitely insists on there being a 
name of a column of the table being inserted into. Makes nonsense.

> I would say the easiest way out of this is to change:
> 
> "Uuid" text --> "uuid_out" text

That would require changes to the application that consumes this 
data. 

A colleague found a better solution in the meantime. To add 

#variable_conflict use_column

right above the DECLARE

Thanks for your time anyway, Jenda
= je...@krynicky.cz === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed 
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

Re: case insensitive collation of Greek's sigma

[Tom Lane]

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.

regards, tom lane

Re: INSERT ... ON CONFLICT doesn't work

[Tom Lane]

"David G. Johnston"  writes:
> On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky  wrote:
>> THERE IS NO (CENSORED) VARIABLE "Uuid"!

> Yes, there is.  RETURNS TABLE (... "Uuid" text ... )

> Changing that to something else should remove the ambiguity.  I agree it is
> not an ideal solution though.  I'm not sure what other options exist though.

The variable_conflict options that Adrian pointed to are one way out.

It's also possible to qualify the name in the ON CONFLICT clause,
although I think you have to parenthesize it to do so:

... ON CONFLICT (("Lockers"."Uuid"))

regards, tom lane

Re: INSERT ... ON CONFLICT doesn't work

[David G. Johnston]

On Wed, Dec 1, 2021 at 12:44 PM Jenda Krynicky  wrote:

> How's that a variable for gawd's sake? It's a column name too! A
> column name in the definition of the resulting table.
>

The columns of the returns table are provided to the function as variables
so that one can write:

output_column1 := 'value';
output_column2 := 'value';
return;

Instead of having to do:
return (output_column1, output_column2);


> Right. Because lowercasing everything I write and then comparing it case
> sensitively to the names of database objects makes a lot of sense. I mean
> who would want to use capital letters in names of objects in the first
> place?
>

Fair point, but you're not going to get much sympathy for not knowing the
rules of the tool that you are using and the choices you've made regarding
them.  I agree that your quoting everything has merit, but don't go
complaining that when you forgot the quotes the system tells you the name
is no longer found.

David J.

Re: INSERT ... ON CONFLICT doesn't work

[David G. Johnston]

On Wed, Dec 1, 2021 at 12:52 PM Tom Lane  wrote:

> It's also possible to qualify the name in the ON CONFLICT clause,
> although I think you have to parenthesize it to do so:
>
> ... ON CONFLICT (("Lockers"."Uuid"))
>
>
This really needs to be confirmed and, if so, better documented on the
INSERT page, since adding a schema to the index_column_name does not
intuitively turn it into an index_expression which is what the syntax
diagram says requires an extra pair of parentheses.

David J.

Re: INSERT ... ON CONFLICT doesn't work

[David G. Johnston]

On Wed, Dec 1, 2021 at 12:59 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Dec 1, 2021 at 12:52 PM Tom Lane  wrote:
>
>> It's also possible to qualify the name in the ON CONFLICT clause,
>> although I think you have to parenthesize it to do so:
>>
>> ... ON CONFLICT (("Lockers"."Uuid"))
>>
>>
> This really needs to be confirmed and, if so, better documented on the
> INSERT page, since adding a schema to the index_column_name does not
> intuitively turn it into an index_expression which is what the syntax
> diagram says requires an extra pair of parentheses.
>
>
That of course should be adding a table qualifier to the column, not a
schema qualifier.  I get why INSERT doesn't really care, the source table
is never in question in SQL-land, but as we see here there is a valid need
for it in plpgsql.

David J.

Re: INSERT ... ON CONFLICT doesn't work

[Jenda Krynicky]

From:   Tom Lane 
> "David G. Johnston"  writes:
> > On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky  wrote:
> >> THERE IS NO (CENSORED) VARIABLE "Uuid"!
> 
> > Yes, there is.  RETURNS TABLE (... "Uuid" text ... )
> 
> > Changing that to something else should remove the ambiguity.  I agree it is
> > not an ideal solution though.  I'm not sure what other options exist though.
> 
> The variable_conflict options that Adrian pointed to are one way out.
> 
> It's also possible to qualify the name in the ON CONFLICT clause,
> although I think you have to parenthesize it to do so:
> 
> ... ON CONFLICT (("Lockers"."Uuid"))
> 
>   regards, tom lane
> 

ERROR:  invalid reference to FROM-clause entry for table "Lockers"
LINE 3: ON CONFLICT (("Lockers"."Uuid"))
  ^
HINT:  There is an entry for table "Lockers", but it cannot be 
referenced from this part of the query.

= je...@krynicky.cz === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed 
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

Re: INSERT ... ON CONFLICT doesn't work

[Jenda Krynicky]

From: "David G. Johnston" 
> On Wed, Dec 1, 2021 at 12:44 PM Jenda Krynicky  wrote:
> 
> > How's that a variable for gawd's sake? It's a column name too! A
> > column name in the definition of the resulting table.
> >
> 
> The columns of the returns table are provided to the function as variables
> so that one can write:
> 
> output_column1 := 'value';
> output_column2 := 'value';
> return;
> 
> Instead of having to do:
> return (output_column1, output_column2);

Yeah ... after I specified that instead of a few scalars I intend to 
return a resultset/recordset/table/whatever-you-want-to-call-it and 
while using a language that cannot distinguish between columns and 
variables even at a place that doesn't accept anything other than a 
column name. Right.

> > Right. Because lowercasing everything I write and then comparing it case
> > sensitively to the names of database objects makes a lot of sense. I mean
> > who would want to use capital letters in names of objects in the first
> > place?
> >
> 
> Fair point, but you're not going to get much sympathy for not knowing the
> rules of the tool that you are using and the choices you've made regarding
> them.  I agree that your quoting everything has merit, but don't go
> complaining that when you forgot the quotes the system tells you the name
> is no longer found.

I did not create the table and I did not forget the quotes. I removed 
them in one of many attempts to appease PostgreSQL. I've already 
learned about those braindead rules.

Jenda
= je...@krynicky.cz === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed 
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

Re: INSERT ... ON CONFLICT doesn't work

[Tom Lane]

"Jenda Krynicky"  writes:
> From: Tom Lane 
>> It's also possible to qualify the name in the ON CONFLICT clause,
>> although I think you have to parenthesize it to do so:
>> ... ON CONFLICT (("Lockers"."Uuid"))

> ERROR:  invalid reference to FROM-clause entry for table "Lockers"
> LINE 3: ON CONFLICT (("Lockers"."Uuid"))
>   ^
> HINT:  There is an entry for table "Lockers", but it cannot be 
> referenced from this part of the query.

Hmm, sorry, I'd tested that in HEAD, but it seems not to work in v13.
Looking closer, I think it was a not-mentioned-in-the-commit-log
side-effect of [1].  Anyway, seems like you need to fall back on
variable_conflict in v13.

regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=6c0373ab7

Re: INSERT ... ON CONFLICT doesn't work

[David G. Johnston]

On Wed, Dec 1, 2021 at 1:04 PM Jenda Krynicky  wrote:

> I did not create the table and I did not forget the quotes. I removed
> them in one of many attempts to appease PostgreSQL. I've already
> learned about those braindead rules.
>
>
Nothing is perfect, especially something that started decades ago and has
to be concerned about ongoing compatibility.  The best part is you got some
clarification (that the RETURNS column is indeed the main cause of the
issue) quickly from two different people and eventually came across the
#variable_conflict hack that at least here is the least invasive solution
to what is arguably a deficiency in the interaction between SQL and
pl/pgsql.  They happen, again nothing is perfect.

David J.

Re: INSERT ... ON CONFLICT doesn't work

[Adrian Klaver]

On 12/1/21 11:43, Jenda Krynicky wrote:

From:   Adrian Klaver 

On 12/1/21 11:20 AM, Jenda Krynicky wrote:

So let's suppose I have a table like this:





So pretty please with a cherry on top, how do I explain to postgres
13.4, that yes indeed by "Uuid" I mean the stinking column "Uuid".


The basic issue is described here:

https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

"Since the names of variables are syntactically no different from the
names of table columns, there can be ambiguity in statements that also
refer to tables: is a given name meant to refer to a table column, or a
variable? Let's change the previous example to ..."


Looks like a bad design.


House rules.

My experience on this across a variety jobs software and not:

1) Learn the house rules

2) Do not expect them to follow your view of world.

3) Do not be surprised if the house does not follow it's own rules.




While the ON CONFLICT () very explicitely insists on there being a
name of a column of the table being inserted into. Makes nonsense.


No it does not expect this(house rules remember):

https://www.postgresql.org/docs/current/sql-insert.html

"
[ ON CONFLICT [ conflict_target ] conflict_action ]

where conflict_target can be one of:

 ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ 
opclass ] [, ...] ) [ WHERE index_predicate ]

ON CONSTRAINT constraint_name
"

And further down:

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

"conflict_target

Specifies which conflicts ON CONFLICT takes the alternative action 
on by choosing arbiter indexes. Either performs unique index inference, 
or names a constraint explicitly. For ON CONFLICT DO NOTHING, it is 
optional to specify a conflict_target; when omitted, conflicts with all 
usable constraints (and unique indexes) are handled. For ON CONFLICT DO 
UPDATE, a conflict_target must be provide


...

index_column_name

The name of a table_name column. Used to infer arbiter indexes. 
Follows CREATE INDEX format. SELECT privilege on index_column_name is 
required.

index_expression

Similar to index_column_name, but used to infer expressions on 
table_name columns appearing within index definitions (not simple 
columns). Follows CREATE INDEX format. SELECT privilege on any column 
appearing within index_expression is required.

collation

When specified, mandates that corresponding index_column_name or 
index_expression use a particular collation in order to be matched 
during inference. Typically this is omitted, as collations usually do 
not affect whether or not a constraint violation occurs. Follows CREATE 
INDEX format.

opclass

When specified, mandates that corresponding index_column_name or 
index_expression use particular operator class in order to be matched 
during inference. Typically this is omitted, as the equality semantics 
are often equivalent across a type's operator classes anyway, or because 
it's sufficient to trust that the defined unique indexes have the 
pertinent definition of equality. Follows CREATE INDEX format.

index_predicate

Used to allow inference of partial unique indexes. Any indexes that 
satisfy the predicate (which need not actually be partial indexes) can 
be inferred. Follows CREATE INDEX format. SELECT privilege on any column 
appearing within index_predicate is required.

constraint_name

Explicitly specifies an arbiter constraint by name, rather than 
inferring a constraint or index.

condition

An expression that returns a value of type boolean. Only rows for 
which this expression returns true will be updated, although all rows 
will be locked when the ON CONFLICT DO UPDATE action is taken. Note that 
condition is evaluated last, after a conflict has been identified as a 
candidate to update.

"

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

Re: INSERT ... ON CONFLICT doesn't work

[Adrian Klaver]

On 12/1/21 12:55, Adrian Klaver wrote:

On 12/1/21 11:43, Jenda Krynicky wrote:

From:   Adrian Klaver 

On 12/1/21 11:20 AM, Jenda Krynicky wrote:



While the ON CONFLICT () very explicitely insists on there being a
name of a column of the table being inserted into. Makes nonsense.


No it does not expect this(house rules remember):


Better wording would be, it is only one of the things it expects.


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

Re: Max connections reached without max connections reached

[James Sewell]

> Looking again into the back trace[1], it appeared that the backend is
> getting stuck while getting the subtransaction's parent information,
> and that can only happen if the snapshot is getting marked as
> overflowed.  So it seems that some of the scripts are using a lot of
> sub-transaction (> 64) within a transaction and in such case, we can
> not cache the sub-transaction information and the snapshot will be
> marked overflowed.  After that, for checking the visibility of any
> tuple we need to consult the SLRU to get TopMost transaction
> information and if the subtransaction SLRU cache is full then it will
> have to do the I/O.  So in this case it appeared that a lot of
> parallel scans/new connections are trying to read the pg_subtrans and
> due to limited cache size that is not even able to load the data in
> the cache and getting frequently blocked.  So I assume that when you
> say the startup is getting stuck forever, how long did you wait, maybe
> it is moving but getting stuck again for the next tuple, so to confirm
> that attach the process with gdb put breakpoint in XidInMVCCSnapshot()
> and check whether it is hitting that breakpoint again or not.  Also,
> check whether you are seeing "SubtransControlLock" in
> pg_stat_activity.  There is one nice blog[2] explaining this issue so
> you can refer to that as well.


Ah. Snap. This makes a lot of sense. The developers have a nasty habit of
using LOOP over rows which has never been stamped out. I bet they have
applied this to something with a lot of rows.

Is there a way of seeing which statements / backends / processes have
greater than 64 stxns? I'd like to monitor this.

- 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: Max connections reached without max connections reached

[Rob Sargent]

On 12/1/21 3:29 PM, James Sewell wrote:


Looking again into the back trace[1], it appeared that the backend is
getting stuck while getting the subtransaction's parent information,
and that can only happen if the snapshot is getting marked as
overflowed.  So it seems that some of the scripts are using a lot of
sub-transaction (> 64) within a transaction and in such case, we can
not cache the sub-transaction information and the snapshot will be
marked overflowed.  After that, for checking the visibility of any
tuple we need to consult the SLRU to get TopMost transaction
information and if the subtransaction SLRU cache is full then it will
have to do the I/O.  So in this case it appeared that a lot of
parallel scans/new connections are trying to read the pg_subtrans and
due to limited cache size that is not even able to load the data in
the cache and getting frequently blocked.  So I assume that when you
say the startup is getting stuck forever, how long did you wait, maybe
it is moving but getting stuck again for the next tuple, so to confirm
that attach the process with gdb put breakpoint in XidInMVCCSnapshot()
and check whether it is hitting that breakpoint again or not.  Also,
check whether you are seeing "SubtransControlLock" in
pg_stat_activity.  There is one nice blog[2] explaining this issue so
you can refer to that as well.


Ah. Snap. This makes a lot of sense. The developers have a nasty habit 
of using LOOP over rows which has never been stamped out. I bet they 
have applied this to something with a lot of rows.


Is there a way of seeing which statements / backends / processes have 
greater than 64 stxns? I'd like to monitor this.


- 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. 
Also be on the look-out for begin trans; begin trans; etc  I read 
Kumar's report as nested transactions.  If your gang is doing a 
transaction per row, they need a kick in the rear.  Anyone not closing 
those needs a pink slip.

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

[Kyotaro Horiguchi]

At Wed, 1 Dec 2021 16:56:11 +0800, Yi Sun  wrote in 
> We want to revoke server certificate, just don't know why doesn't take
> affect
> https://www.postgresql.org/docs/11/ssl-tcp.html
> https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-SSL-CRL-FILE

Understood.  ~/.postgresq/root.crl is required to check server
revokation.

https://www.postgresql.org/docs/11/libpq-ssl.html

> To allow server certificate verification, one or more root
> certificates must be placed in the file ~/.postgresql/root.crt in the
> user's home directory. (On Microsoft Windows the file is named
> %APPDATA%\postgresql\root.crt.) Intermediate certificates should also
> be added to the file if they are needed to link the certificate chain
> sent by the server to the root certificates stored on the client.
> 
> Certificate Revocation List (CRL) entries are also checked if the file
> ~/.postgresql/root.crl exists (%APPDATA%\postgresql\root.crl on
> Microsoft Windows).

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Max connections reached without max connections reached

[Michael Lewis]

On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent  wrote:

> Also be on the look-out for begin trans; begin trans; etc  I read Kumar's
> report as nested transactions.  If your gang is doing a transaction per
> row, they need a kick in the rear.  Anyone not closing those needs a pink
> slip.
>

I have seen the second attempt at calling begin seem to be ignored with
"WARNING:  there is already a transaction in progress" similar to calling
rollback without a transaction open. Are you stating or suggesting that
calling begin a second time actually is more similar to
creating a savepoint? That would be rather unexpected from the messaging,
at least for me.

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

[Yi Sun]

Hi Kyotaro

>From the description, seems  ~/.postgresql/root.crl  is store client
revoked certificate

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

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.

Re: Max connections reached without max connections reached

[Rob Sargent]

On 12/1/21 7:08 PM, Michael Lewis wrote:
On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent > wrote:


Also be on the look-out for begin trans; begin trans; etc  I read
Kumar's report as nested transactions.  If your gang is doing a
transaction per row, they need a kick in the rear.  Anyone not
closing those needs a pink slip.


I have seen the second attempt at calling begin seem to be ignored 
with "WARNING:  there is already a transaction in progress" similar to 
calling rollback without a transaction open. Are you stating or 
suggesting that calling begin a second time actually is more similar 
to creating a savepoint? That would be rather unexpected from the 
messaging, at least for me.
No.  Hard to know what the devs are up to here (what stack they're 
using, etc), but I'm suggesting one transaction is getting started and 
not closed. Warnings have been know to be ignored.  I admit I am not 
familiar with Kumar's term of "sub-transaction" and how those might be 
created but it sounds to me like a transaction is begun early in the 
processing and more work happens unaware of the transaction. And 
possible not committed.

Re: Max connections reached without max connections reached

[Dilip Kumar]

On Thu, Dec 2, 2021 at 3:59 AM James Sewell  wrote:
>
>
>> Looking again into the back trace[1], it appeared that the backend is
>> getting stuck while getting the subtransaction's parent information,
>> and that can only happen if the snapshot is getting marked as
>> overflowed.  So it seems that some of the scripts are using a lot of
>> sub-transaction (> 64) within a transaction and in such case, we can
>> not cache the sub-transaction information and the snapshot will be
>> marked overflowed.  After that, for checking the visibility of any
>> tuple we need to consult the SLRU to get TopMost transaction
>> information and if the subtransaction SLRU cache is full then it will
>> have to do the I/O.  So in this case it appeared that a lot of
>> parallel scans/new connections are trying to read the pg_subtrans and
>> due to limited cache size that is not even able to load the data in
>> the cache and getting frequently blocked.  So I assume that when you
>> say the startup is getting stuck forever, how long did you wait, maybe
>> it is moving but getting stuck again for the next tuple, so to confirm
>> that attach the process with gdb put breakpoint in XidInMVCCSnapshot()
>> and check whether it is hitting that breakpoint again or not.  Also,
>> check whether you are seeing "SubtransControlLock" in
>> pg_stat_activity.  There is one nice blog[2] explaining this issue so
>> you can refer to that as well.
>
>
> Ah. Snap. This makes a lot of sense. The developers have a nasty habit of 
> using LOOP over rows which has never been stamped out. I bet they have 
> applied this to something with a lot of rows.
>
> Is there a way of seeing which statements / backends / processes have greater 
> than 64 stxns? I'd like to monitor this.

I think there is no such view or anything which tells about which
backend or transaction has more than 64 sub transaction.  But if we
are ready to modify the code then we can LOG that information in
GetNewTransactionId(), when first time we are marking it overflown.
if (nxids < PGPROC_MAX_CACHED_SUBXIDS)
{
   MyProc->subxids.xids[nxids] = xid;
  pg_write_barrier();
  MyProc->subxidStatus.count = substat->count = nxids + 1;
}
else
{
  MyProc->subxidStatus.overflowed = substat->overflowed = true;
 <-- we can log or put breakpoint here and identify which statement is
creating oeverflow-->
}

IMHO, it is good to LOG such information if we are not already logging
this anywhere.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Re: Max connections reached without max connections reached

[Michael Lewis]

On Wed, Dec 1, 2021 at 8:54 PM Rob Sargent  wrote:

> On 12/1/21 7:08 PM, Michael Lewis wrote:
>
> On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent  wrote:
>
>> Also be on the look-out for begin trans; begin trans; etc  I read
>> Kumar's report as nested transactions.  If your gang is doing a transaction
>> per row, they need a kick in the rear.  Anyone not closing those needs a
>> pink slip.
>>
>
> I have seen the second attempt at calling begin seem to be ignored with
> "WARNING:  there is already a transaction in progress" similar to calling
> rollback without a transaction open. Are you stating or suggesting that
> calling begin a second time actually is more similar to
> creating a savepoint? That would be rather unexpected from the messaging,
> at least for me.
>
> No.  Hard to know what the devs are up to here (what stack they're using,
> etc), but I'm suggesting one transaction is getting started and not closed.
> Warnings have been know to be ignored.  I admit I am not familiar with
> Kumar's term of "sub-transaction" and how those might be created but it
> sounds to me like a transaction is begun early in the processing and more
> work happens unaware of the transaction.  And possible not committed.
>

Okay cool. Subtransactions are interesting. They are used when a savepoint
is issued, and with exception blocks in PL/pgSQL functions. They allow the
system to keep track of which "sub" process made changes to rows. So just
like the main transaction gets a transaction ID and rows get marked with
xmin or xmax as needed for MVCC, the subtransactions get their own
transaction ID and the objects they modify get updated to set xmin and xmax
with their transaction ID. There are some interesting performance
bottlenecks when many subtransactions get involved, but I am still learning
about that.

Disclaimer- I am no kind of PG contributor and very much still learning,
the above explanation is my best understanding but I welcome any
corrections or clarifications.

Re: Max connections reached without max connections reached

[Michael Lewis]

On Wed, Dec 1, 2021 at 9:06 PM Dilip Kumar  wrote:

> IMHO, it is good to LOG such information if we are not already logging
> this anywhere.
>

+1

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.

Re: INSERT ... ON CONFLICT doesn't work

[Michael Lewis]

Perhaps I missed something, but why all this effort to reference the column
and not just reference IX_Lockers_Uuid for the on conflict clause?

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

[Kyotaro Horiguchi]

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
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index ab617c7b86..4ac617615c 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1248,7 +1248,7 @@ include_dir 'conf.d'
   
   

-Specifies the name of the file containing the SSL server certificate
+Specifies the name of the file containing the SSL client certificate
 revocation list (CRL).
 Relative paths are relative to the data directory.
 This parameter can only be set in the postgresql.conf
@@ -1267,7 +1267,7 @@ include_dir 'conf.d'
   
   

-Specifies the name of the directory containing the SSL server
+Specifies the name of the directory containing the SSL client
 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
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index c17d33a54f..eb3a0c6b55 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -1742,11 +1742,10 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
   sslcrl
   

-This parameter specifies the file name of the SSL certificate
+This parameter specifies the file name of the SSL server certificate
 revocation list (CRL).  Certificates listed in this file, if it
-exists, will be rejected while attempting to authenticate the
-server's certificate.  If neither
- nor
+exists, will be rejected while attempting to authenticate the server's
+certificate.  If neither  nor
  is set, this setting is
 taken as
 ~/.postgresql/root.crl.
@@ -1758,9 +1757,9 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
   sslcrldir
   

-This parameter specifies the directory name of the SSL certificate
-revocation list (CRL).  Certificates listed in the files in this
-directory, if it exists, will be rejected while attempting to
+This parameter specifies the directory name of the SSL server
+certificate revocation list (CRL).  Certificates listed in the files
+in this directory, if it exists, will be rejected while attempting to
 authenticate the server's certificate.

 

Re: INSERT ... ON CONFLICT doesn't work

[Adrian Klaver]

On 12/1/21 20:20, Michael Lewis wrote:
Perhaps I missed something, but why all this effort to reference the 
column and not just reference IX_Lockers_Uuid for the on conflict clause?


Two reasons:

1) The OP thought that ON CONFLICT could only reference a column.

2) Explaining why the error occurred in the first place, which is down 
to variable/column name ambiguity.



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