pg_wal lifecycle

[Luca Ferrari]

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_size.
Is this correct? In particular, what is in simple words, the
discrimination between recycling a segment and creating a new one?


% sudo du -hs $PGDATA/pg_wal/
273M/postgres/13/data/pg_wal/
% psql -U postgres -c "show max_wal_size;" -c "show checkpoint_timeout;"
 max_wal_size
--
 1GB
(1 riga)

 checkpoint_timeout

 5min
(1 riga)

and from the last log checkpoint entry:

2021-07-13 02:48:10.492 EDT [2424] LOG:  checkpoint starting: time
2021-07-13 02:50:44.515 EDT [2424] LOG:  checkpoint complete: wrote
9170 buffers (28.0%); 0 WAL file(s) added, 0 removed, 13 recycled;
write=148.878 s, sync=1.889 s, total=154.023 s; sync files=42,
longest=0.109 s, average=0.045 s; distance=217376 kB, estimate=217376
kB

Thanks,
Luca

Re: libicu global support

[Peter Eisentraut]

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 quite complicated to adopt 
as there isn't support of global setup per cluster (initdb) nor creating 
of database.


So, my silly question: is there any chance a work can be done on it with 
a new version anytime soon?


It's definitely high on various developers' wishlists, but I am not 
aware of any activity on it right now.

Re: pg_wal lifecycle

[Peter Eisentraut]

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 pg_wal
size lower than max_wal_size.
Is this correct? In particular, what is in simple words, the
discrimination between recycling a segment and creating a new one?


Recycling in this context just means that instead of creating a new WAL 
file for new WAL traffic, it reuses an old file.  So if you have WAL 
files 5, 6, 7, 8, 9, and you know that you don't need 5 and 6 anymore, 
when you need to start WAL file 10, instead of creating a new file "10", 
the system just renames "5" to "10" and starts overwriting what was in 
there.  This is just an optimization to use the file system better; it 
doesn't affect the logical principles of what is going on.

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

[David Gauthier]

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),
('prj3','prj1,prj2');

I need to make sure that the elements of 'prj1,prj2' are both valid
projects.

I'm thinking the csv should be split into an array (regexp_split_to_array)
but the constraint needs to somehow iterate over each element to check that
they are all valid.

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.

Thanks in Advance !

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

[David G. Johnston]

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 trigger function though.  Or normalize the table and use the
built-it foreign key triggers.

David J.

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

[David Gauthier]

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:

> 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 trigger function though.  Or normalize the table and use the
> built-it foreign key triggers.
>
> David J.
>
>

psycopg2 mail list subscription issues

[Rich Shepard]

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 mail list and, apparently
other lists, requires a community account password which my password to this
mail list apparently is.

Thinking I might have a postgres community account (needed to subscribe to
that mail list) I tried the reset password link on this page

but no email message with instructions to reset my password were received.

This suggested I didn't have an account, so I tried to create one. The
immediate response is that I do have a community account. Checkmate. Can't
reset my password and I have an existing account with my email address.

Suggestions on how I can subscribe to the psycopg2 mail list are needed.

TIA,

Rich

Re: psycopg2 mail list subscription issues

[Diego]

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
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 mail list and, apparently
other lists, requires a community account password which my password to 
this

mail list apparently is.

Thinking I might have a postgres community account (needed to subscribe to
that mail list) I tried the reset password link on this page
 


but no email message with instructions to reset my password were received.

This suggested I didn't have an account, so I tried to create one. The
immediate response is that I do have a community account. Checkmate. Can't
reset my password and I have an existing account with my email address.

Suggestions on how I can subscribe to the psycopg2 mail list are needed.

TIA,

Rich

Json:- Accepting 'integer' value

[shaurya jain]

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
Table "public.kp"
 Column |  Type   | Collation | Nullable |Default
+-+---+--+
 id | integer |   | not null | nextval('kp_id_seq'::regclass)
 jj | json|   |  |

postgres=# insert into kp(jj) values('98');
INSERT 0 1

But while we checking the same thing in Oracle it doesn't accept this. Is
it an issue with Postgres data type?


-- 
Thanks and Regards,
Shaurya Jain
email:- 12345shau...@gmail.com
*Mobile:- +91-8802809405*
LinkedIn:- https://www.linkedin.com/in/shaurya-jain-74353023

Re: psycopg2 mail list subscription issues

[Rich Shepard]

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 page:
no URL to reset the password and an instant message from the Mail Delivery
System :

A message that you sent could not be delivered to one or more of its
recipients. This is a permanent error. The following address(es) failed:

  da...@fetter.org
host mail.fetter.org [46.226.110.146]
SMTP error from remote mail server after RCPT TO::
550 5.7.23 : Recipient address rejected:
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=

Thanks,

Rich

Re: Json:- Accepting 'integer' value

[Tom Lane]

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 values.  I see though that it
says

   A JSON text is a serialized value.  Note that certain previous
   specifications of JSON constrained a JSON text to be an object or an
   array.

so apparently Oracle is following some obsolete version of the spec.

regards, tom lane

Re: psycopg2 mail list subscription issues

[Tom Lane]

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.1159IN  TXT "v=spf1 ip4:50.126.108.78 -all"
appl-ecosys.com.1159IN  TXT "v=spf2.0/pra ?all"

Is it possible that you're sending the list request mail out of some
other IP address?

regards, tom lane

Re: psycopg2 mail list subscription issues

[Rich Shepard]

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

[Stephen Frost]

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

This was addressed and we got it all sorted.

Thanks,

Stephen


signature.asc
Description: PGP signature

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

[Ken Tanzer]

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 had hopes for this, and still do.  It would be great
for my use cases.  From what I remember, it was "complicated."  There was a
patch proposed at least as far back as 9.3 for this[1].Googling for
this now though, I see there was some activity on it this year[2].  Does
anyone know the status of this, or if/when this is likely to show up in
Postgresql?  Thanks!

Ken


1.
https://www.2ndquadrant.com/en/blog/postgresql-9-3-development-array-element-foreign-keys/

2.
https://www.postgresql-archive.org/GSoC-2017-Foreign-Key-Arrays-td5962835i140.html



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: PostgreSQL 9.2 high replication lag

[Lucas]

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 long a standby server should wait before canceling 
queries that conflict with pending WAL entries received via streaming 
replication.

My question then is: Which queries, if the slave can only receive SELECTs?

---
Regards,

Lucas

> This message is encrypted. Both the Public Key and the GPG encrypted message 
> are included in this email so that you can verify its origin.

‐‐‐ Original Message ‐‐‐

On Wednesday, July 14th, 2021 at 1:09 PM, Lucas  wrote:

> Hello all,
> 

> I have a cluster of PostgreSQL 9.2 servers (yes, I know... we're working on a 
> migration to PG 13) that has one master and one slave.
> [image.png]
> 

> I know that the streaming replication is expected to have delays, but this is 
> getting worse and worse everyday. You can see the replication gap is reaching 
> >10 minutes quite often.
> 

> The servers are hosted in AWS as EC2 instances and they're both in different 
> AZs. I also know that the streaming replication was first introduced in PG 
> 9.0, so we're using a very old version of it.. I'm sure it has improved over 
> the years...
> What can I look for to make this lag not so high? Is there anything I could 
> change in my postgresql.conf files? Any tips?
> 

> Thanks!
> 

> ---
> Regards,
> 

> Lucas
> 

> > This message is encrypted. Both the Public Key and the GPG encrypted 
> > message are included in this email so that you can verify its origin.

publickey - root@sud0.nz - 0xC5E964A1.asc
Description: application/pgp-keys


signature.asc
Description: OpenPGP digital signature

Re: PostgreSQL 9.2 high replication lag

[David G. Johnston]

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 received via streaming replication.
>
> My question then is: Which queries, if the slave can only receive SELECTs?
>

The select queries...

Follow the link in the docs for max_stanbdy_steaming_delay to:

https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT

for the details.

David J.

Re: PostgreSQL 9.2 high replication lag

[Laurenz Albe]

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 conflict
> >  with pending WAL entries received via streaming replication.
> > 
> > My question then is: Which queries, if the slave can only receive SELECTs?
> 
> The select queries...
> 
> Follow the link in the docs for max_stanbdy_steaming_delay to:
> 
> https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT
> 
> for the details.

Perhaps you might find the following article interesting, where I tried to
discuss this topic in some depth:

https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

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

[Laurenz Albe]

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, and the
exercise will be simple.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com