pg_wal lifecycle

Hi all, I'd like to see if I get it right about pg_wal: it grows up to pretty much max_wal_size, at which point a checkpoint is triggered. If the server triggers a timed checkpoint before the pg_wal is at max_wal_size, the system recycles the wals thus keeping the pg_wal size lower than max_wal_siz

Re: libicu global support

On 11.07.21 23:52, Jakub Jedelsky wrote: during the adoption of Centos 8 on our servers we ran into problems with Postgresql (13.3), glibc (delivered by the Centos) and performance of sorting. Because of that we're planning to use the ICU collations (en-x-icu), but the current implementation is

Re: pg_wal lifecycle

On 13.07.21 09:07, Luca Ferrari wrote: I'd like to see if I get it right about pg_wal: it grows up to pretty much max_wal_size, at which point a checkpoint is triggered. If the server triggers a timed checkpoint before the pg_wal is at max_wal_size, the system recycles the wals thus keeping the p

Need to check each element of an array satisfies a foreign key constraint

Hi: I have a column in a table which is a csv of values and I need to make sure each element of the csv = the PK of that same table. create table projects ( project varchar primary key, children_csv varchar ); insert into projects (project,children_csv) values ('prj1',null), ('prj2',null), (

Re: Need to check each element of an array satisfies a foreign key constraint

On Tuesday, July 13, 2021, David Gauthier wrote: > > > I suppose I could write a stored procedure to do this and call it in a > check constraint. But I was wondering if there is something more elegant. > > You cannot use a check constraint here as the behavior is not immutable. You can use a trigg

Re: Need to check each element of an array satisfies a foreign key constraint

Ok, thanks. I was looking for, but didn't find, something like... each_element_of(regexp_split_to_array(children_csv)) references projects(project); Of course the "each_element_of" is my creation here :-) On Tue, Jul 13, 2021 at 10:07 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > O

psycopg2 mail list subscription issues

I apologize for writing to the list, but my attempts to subscribe to the psycopg2 mail list fail and I've not seen a response to my email message sent to the mail list owner. I don't know my postgres mail list password as I've not had a reason to use it ... until now. Subscribing to the psycopg2

Re: psycopg2 mail list subscription issues

Here is the link to reset your password and manage your account: https://www.postgresql.org/account/reset/ On 13/07/2021 14:29, Rich Shepard wrote: I apologize for writing to the list, but my attempts to subscribe to the psycopg2 mail list fail and I've not seen a response to my email message s

Json:- Accepting 'integer' value

Hi Team, Postgres Version:- 12.7 Issue:- JSON data type column accepting 'integer' value Priority:-Minor Below is the table definition and insert statement, which is working in Postgres however it doesn't work in oracle with check constraint(jj is JSON strict). postgres=# \d kp

Re: psycopg2 mail list subscription issues

On Tue, 13 Jul 2021, Diego wrote: Here is the link to reset your password and manage your account: https://www.postgresql.org/account/reset/ Diego, I tried the list management link and that didn't work. The URL you sent has the same results as the password reset link on the community account

Re: Json:- Accepting 'integer' value

shaurya jain <12345shau...@gmail.com> writes: > But while we checking the same thing in Oracle it doesn't accept this. Is > it an issue with Postgres data type? I'd call it an issue with Oracle. We follow RFC 7159, which specifies that "bare" scalars (numbers, strings, booleans) are valid JSON va

Re: psycopg2 mail list subscription issues

Rich Shepard writes: > Message rejected due to: SPF fail - not authorized. Please see > http://www.openspf.net/Why?s=mfrom;id=rshep...@appl-ecosys.com;ip=217.196.149.56;r= Hmm, I see appl-ecosys.com is advertising this SPF data: $ dig appl-ecosys.com txt ... appl-ecosys.com.1159

Re: psycopg2 mail list subscription issues

On Tue, 13 Jul 2021, Tom Lane wrote: Is it possible that you're sending the list request mail out of some other IP address? tom, No. I have a personal domain but use my business domain for all business-related mail lists. Thanks, Rich

Re: psycopg2 mail list subscription issues

Greetings, * Rich Shepard (rshep...@appl-ecosys.com) wrote: > On Tue, 13 Jul 2021, Tom Lane wrote: > > >Is it possible that you're sending the list request mail out of some other > >IP address? > > tom, > > No. I have a personal domain but use my business domain for all > business-related mail

Re: Need to check each element of an array satisfies a foreign key constraint

On Tue, Jul 13, 2021 at 7:47 AM David Gauthier wrote: > Ok, thanks. > > I was looking for, but didn't find, something like... > each_element_of(regexp_split_to_array(children_csv)) references > projects(project); > Of course the "each_element_of" is my creation here :-) > > Several years ago, I h

Re: PostgreSQL 9.2 high replication lag

Okay. Looks like I found the "issue". A while ago we set max_standby_streaming_delay to -1, because we were getting to many errors "ERROR: canceling statement due to conflict with recovery.". According to the documentation, max_standby_streaming_delay is aconfiguration parameterdetermining how

Re: PostgreSQL 9.2 high replication lag

On Tue, Jul 13, 2021 at 8:01 PM Lucas wrote: > According to the documentation, max_standby_streaming_delay is a configuration > parameter determining how long a > standby server should wait before canceling queries that conflict with > pending WAL entries receive

Re: PostgreSQL 9.2 high replication lag

On Tue, 2021-07-13 at 20:14 -0700, David G. Johnston wrote: > On Tue, Jul 13, 2021 at 8:01 PM Lucas wrote: > > According to the documentation, max_standby_streaming_delay is a > > configuration parameter > > determining how long a standby server should wait before canceling queries > > that conf

Re: Need to check each element of an array satisfies a foreign key constraint

On Tue, 2021-07-13 at 10:00 -0400, David Gauthier wrote: > I have a column in a table which is a csv of values and I need to make sure > each element of the csv = the PK of that same table. This won't work, and it is broken by design. Change your data model to adhere to the first normal form, an