Обсуждение: Connection Taking time to authentication of database
Samed YILDIRIM
Hi All,From few days I am facing an issue while connecting to PostgreSQL database. It took 20sec to make a connection from application Currently I am using PostgreSQL v13.5. Could you please provide me some input on this.Thanks and regardsVivek Gadge.
On Tue, 2023-01-17 at 23:02 +0530, Vivek Gadge wrote: > From few days I am facing an issue while connecting to PostgreSQL database. > It took 20sec to make a connection from application Currently I am using > PostgreSQL v13.5. Could you please provide me some input on this. Before you investigate that with a network sniffer, see if you have "pre_auth_delay" or "post_auth_delay" set on the PostgreSQL server. Yours, Laurenz Albe
I had troubles with some unique constraints recently. They simply didn't work, not seeing some records (select found only 47 records with specific values instead of 65, insert could create duplicate records). The constraint were on multiple columns. Drop constraint and create constraint fixed the problem. This happened on two servers : - a version 10 server, where an abnormal poweroff happened 6 months ago, found 3 tables with the problem throughout last 6 months. - a test server, found the problem on 1 table after upgrading version 10 to 14 Anythng similar happened to anybody ? How can I find out if similar problems are present ? constraint and index looked ok and were used
> On 26/01/2023 11:11 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote: > > I had troubles with some unique constraints recently. > > They simply didn't work, not seeing some records (select found only 47 > records with specific values instead of 65, insert could create > duplicate records). > > The constraint were on multiple columns. Drop constraint and create > constraint fixed the problem. > > This happened on two servers : > > - a version 10 server, where an abnormal poweroff happened 6 months > ago, found 3 tables with the problem throughout last 6 months. > > - a test server, found the problem on 1 table after upgrading version 10 > to 14 > > Anythng similar happened to anybody ? > How can I find out if similar problems are present ? constraint and > index looked ok and were used Are the indexes on string types? Changes to the locale data is one cause: https://www.postgresql.org/message-id/328626.1647362495@sss.pgh.pa.us -- Erik
we had OS upgrades, but with the same locale.
Is there any chance to avoid rebuilding of all indexes/constraints by checking the correctness of indexes ?
Massimo Ortensi - Responsabile ICT
Via Cristoforo Colombo, 21 - 40131, Bologna
Tel. 051 4195069
Cell. 3351092560
mortensi@unimaticaspa.it
Questa e-mail e i suoi allegati contengono informazioni di proprietà di Unimatica-RGI S.p.A. e devono essere utilizzati esclusivamente dal destinatario in relazione alle finalità per le quali sono stati ricevuti. E’ vietata qualsiasi forma di riproduzione o di divulgazione senza l’esplicito consenso di Unimatica-RGI S.p.A. Qualora la presente e-mail fosse stata ricevuta per errore, si prega di informare tempestivamente il mittente e distruggere la copia in possesso.
Privacy: I dati personali contenuti in questa e-mail, nonché nei file ivi inclusi, risultano oggetto di tutela ai sensi del Reg. UE 2016/679 (GDPR). Il Titolare del trattamento dei suddetti dati è Unimatica-RGI S.p.A. Gli interessati potranno esercitare tutti i diritti ex artt. 15 e ss. del GDPR inviando un messaggio all’indirizzo privacy@pec.unimaticaspa.it. Qualsiasi trattamento effettuato da chi ha ricevuto per errore tali dati costituisce violazione delle disposizioni previste dal GDPR. In ogni momento è possibile proporre reclamo all’Autorità competente. Per maggiori informazioni si rinvia al sito www.unimaticaspa.it. Ambiente: Considera la responsabilità che hai verso l’ambiente prima di stampare questa e-mail
On 26/01/2023 11:11 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote: I had troubles with some unique constraints recently. They simply didn't work, not seeing some records (select found only 47 records with specific values instead of 65, insert could create duplicate records). The constraint were on multiple columns. Drop constraint and create constraint fixed the problem. This happened on two servers : - a version 10 server, where an abnormal poweroff happened 6 months ago, found 3 tables with the problem throughout last 6 months. - a test server, found the problem on 1 table after upgrading version 10 to 14 Anythng similar happened to anybody ? How can I find out if similar problems are present ? constraint and index looked ok and were usedAre the indexes on string types? Changes to the locale data is one cause: https://www.postgresql.org/message-id/328626.1647362495@sss.pgh.pa.us -- Erik
Вложения
> On 26/01/2023 12:52 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote: > > we had OS upgrades, but with the same locale. Which OS? Locale may still be the same but collation information may have changed, resulting in a different sort order. https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html Has libc version changed with the OS upgrades? > Is there any chance to avoid rebuilding of all indexes/constraints by checking > the correctness of indexes ? Extension amcheck (functions bt_index_check and bt_index_parent_check) can verify that btree entries for text are in lexical order. https://www.postgresql.org/docs/current/amcheck.html > Il 26/01/2023 11:56, Erik Wienhold ha scritto: > > > On 26/01/2023 11:11 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote: > > > > I had troubles with some unique constraints recently. > > > > They simply didn't work, not seeing some records (select found only 47 > > records with specific values instead of 65, insert could create > > duplicate records). > > > > The constraint were on multiple columns. Drop constraint and create > > constraint fixed the problem. > > > > This happened on two servers : > > > > - a version 10 server, where an abnormal poweroff happened 6 months > > ago, found 3 tables with the problem throughout last 6 months. > > > > - a test server, found the problem on 1 table after upgrading version 10 > > to 14 > > > > Anythng similar happened to anybody ? > > How can I find out if similar problems are present ? constraint and > > index looked ok and were used > > > Are the indexes on string types? Changes to the locale data is one cause: > https://www.postgresql.org/message-id/328626.1647362495@sss.pgh.pa.us -- Erik
we had OS upgrades, but with the same locale.
Is there any chance to avoid rebuilding of all indexes/constraints by checking the correctness of indexes ?
Massimo Ortensi - Responsabile ICT
Via Cristoforo Colombo, 21 - 40131, Bologna
Tel. 051 4195069
Cell. 3351092560
mortensi@unimaticaspa.it
In case of a change in the locale properties - albeit the name of the locale remained the same - index corruption is possible.
This has been reported on RedHat platforms. The reaction of the PostgreSQL project was to incorporate the locale libraries by ICU. They can be used on demand. If they change, the database server will automatically reindex if necessary.
If something in the OS libraries changes, the database server won't be informed.
When indexes are corrupt (and data are fine), the best solution is to reindex all indexes over varchar and text columns.
Kind Regards,
Holger
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Вложения
> On Jan 26, 2023, at 5:42 AM, Holger Jakobs <holger@jakobs.com> wrote: > > This has been reported on RedHat platforms. Happened to me on Ubuntu--I think it's Linux in general.
Confirmed, the local is the same but sorting changed. Thanks Il 26/01/2023 13:39, Erik Wienhold ha scritto: >> On 26/01/2023 12:52 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote: >> >> we had OS upgrades, but with the same locale. > Which OS? Locale may still be the same but collation information may have > changed, resulting in a different sort order. > > https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html > > Has libc version changed with the OS upgrades? > >> Is there any chance to avoid rebuilding of all indexes/constraints by checking >> the correctness of indexes ? > Extension amcheck (functions bt_index_check and bt_index_parent_check) can > verify that btree entries for text are in lexical order. > > https://www.postgresql.org/docs/current/amcheck.html > >> Il 26/01/2023 11:56, Erik Wienhold ha scritto: >> >>> On 26/01/2023 11:11 CET Massimo Ortensi <mortensi@unimaticaspa.it> wrote: >>> >>> I had troubles with some unique constraints recently. >>> >>> They simply didn't work, not seeing some records (select found only 47 >>> records with specific values instead of 65, insert could create >>> duplicate records). >>> >>> The constraint were on multiple columns. Drop constraint and create >>> constraint fixed the problem. >>> >>> This happened on two servers : >>> >>> - a version 10 server, where an abnormal poweroff happened 6 months >>> ago, found 3 tables with the problem throughout last 6 months. >>> >>> - a test server, found the problem on 1 table after upgrading version 10 >>> to 14 >>> >>> Anythng similar happened to anybody ? >>> How can I find out if similar problems are present ? constraint and >>> index looked ok and were used >>> >> Are the indexes on string types? Changes to the locale data is one cause: >> https://www.postgresql.org/message-id/328626.1647362495@sss.pgh.pa.us > -- > Erik
On Thu, 2023-01-26 at 11:11 +0100, Massimo Ortensi wrote: > I had troubles with some unique constraints recently. > > They simply didn't work, not seeing some records (select found only 47 > records with specific values instead of 65, insert could create > duplicate records). > > The constraint were on multiple columns. Drop constraint and create > constraint fixed the problem. > > This happened on two servers : > > - a version 10 server, where an abnormal poweroff happened 6 months > ago, found 3 tables with the problem throughout last 6 months. > > - a test server, found the problem on 1 table after upgrading version 10 > to 14 > > > Anythng similar happened to anybody ? Yes, there were quite a number of such reports. > How can I find out if similar problems are present ? constraint and > index looked ok and were used This is almost certainly caused by an update to the library that provides your collations. The update must have changed the definition of those collations and consequently the sort order, corrupting your collations. These articles describe the problem and ways to a solution: https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/ Yours, Laurenz Albe