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

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 occu

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

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

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

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

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 wha

Re: Max connections reached without max connections reached

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

Re: Max connections reached without max connections reached

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

Re: Max connections reached without max connections reached

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

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

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 para

Re: Max connections reached without max connections reached

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 see

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

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/ro

Re: Max connections reached without max connections reached

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 t

Re: Max connections reached without max connections reached

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

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

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.

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

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 colum

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

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

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

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

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

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 >

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

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

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

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 (("Locker

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

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 p

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

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 :=

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

"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'

Re: case insensitive collation of Greek's sigma

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 A

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

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 bas

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

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,

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

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,

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

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/c

Re: case insensitive collation of Greek's sigma

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", po

INSERT ... ON CONFLICT doesn't work

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

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

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

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

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

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

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

Re: Database Scalability

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

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

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

Re: Database Scalability

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 shardin

Re: Database Scalability

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 s

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

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, gues

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

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 roo

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

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 roo

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

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

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

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 replicat

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

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/s

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

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

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

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

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

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

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

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

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

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 +

Re: Max connections reached without max connections reached

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 in