Обсуждение: Connection Taking time to authentication of database

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 ?

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