Re: Query performance going from Oracle to Postgres

[Peter Geoghegan]

On Wed, Sep 6, 2023 at 1:07 PM Dirschel, Steve
 wrote:
> Oracle will find the same 332 rows using the same index but in Oracle it only 
> does 20 logical reads.  I thought maybe the index was fragmented so I 
> reindexed that index:

It seems likely that the problem here is that some of the predicates
appear as so-called "Filter:" conditions, as opposed to true index
quals. For reasons that aren't particularly fundamental, Postgres
B-Trees cannot push down != (or <>) to the index level. Strangely
enough, this is possible with IS NOT NULL. I'm working to improve
things in this area.

That difference is easy to see in the following example. The first
query does far fewer buffer accesses than the. second query, despite
being very similar:

regression=# explain (analyze, buffers) select * from multi_test where
a = 1 and b is not null;
 QUERY PLAN
-
 Index Only Scan using multi_test_idx on multi_test  (cost=0.29..50.10
rows=2191 width=8) (actual time=0.095..0.100 rows=14 loops=1)
   Index Cond: ((a = 1) AND (b IS NOT NULL))
   Heap Fetches: 0
   Buffers: shared hit=3
 Planning Time: 0.056 ms
 Execution Time: 0.145 ms
(6 rows)

regression=# explain (analyze, buffers) select * from multi_test where
a = 1 and b != 42;
  QUERY PLAN
--
 Index Only Scan using multi_test_idx on multi_test
(cost=0.29..222.57 rows=2191 width=8) (actual time=0.087..2.982
rows=14 loops=1)
   Index Cond: (a = 1)
   Filter: (b <> 42)
   Rows Removed by Filter: 1
   Heap Fetches: 0
   Buffers: shared hit=11
 Planning Time: 0.076 ms
 Execution Time: 3.204 ms
(8 rows)

(There are lots of index tuples matching (a, b) = "(1, NULL)" here, you see.)

-- 
Peter Geoghegan

Re: Query performance going from Oracle to Postgres

[David Rowley]

On Thu, 7 Sept 2023 at 11:14, Dirschel, Steve
 wrote:
> select count(historyeve0_.HISTORY_EVENT_SID) as col_0_0_ from 
> hist28.history_event_display_timestamp_20230301 historyeve0_ where 
> historyeve0_.IS_DELETED=0

> history_event_sid  | character varying(32)  |   | not 
> null |

Do you get an Index Only Scan if you use count(*) instead of
count(historyeve0_.HISTORY_EVENT_SID)?

It seems that column is the only one missing from the index and that
might be the only thing that's stopping PostgreSQL using an Index Only
Scan. Perhaps Oracle realises that the column is NOT NULL so can do
count(*) instead of counting the non-nulls from that column.  That may
allow it to Index Only Scan? I'm not versed in reading Oracle plans so
don't know if it's possible to tell.

David

Re: Query performance going from Oracle to Postgres

[Laurenz Albe]

On Wed, 2023-09-06 at 20:06 +, Dirschel, Steve wrote:
> We are in the process of converting from Oracle to Postgres and I have a 
> query that is using
> the same index in Postgres as is used in Oracle but in Postgres the query 
> does 16x more
> buffer/logical reads.  I’d like to understand why.
>  
> explain (analyze, buffers)
> select count(historyeve0_.HISTORY_EVENT_SID) as col_0_0_
> from hist28.history_event_display_timestamp_20230301 historyeve0_ where 
> historyeve0_.IS_DELETED=0
> and historyeve0_.PRISM_GUID='i0accd6a2018405f095ee669dc5b4'
> and historyeve0_.PRODUCT_SID='CARSWELL.WESTLAW'
> and (historyeve0_.EVENT_TYPE not in 
> ('versionsSearchWithinQueryEvent','notesOfDecisionsSearchWithinQueryEvent','citingReferencesSearchWithinQueryEvent','tocSearchWithinQueryEvent','searchWithinDocumentEvent','coCitesSearchWithinQueryE
> vent'))
> and (historyeve0_.PRODUCT_VIEW in 
> ('DefaultProductView','TNPPlus','PLCUS','Indigo','IndigoPremium','INDIGOCA')
> or historyeve0_.PRODUCT_VIEW is null)
> and historyeve0_.CLIENT_ID='WLNCE_VNJXL7'
> and (historyeve0_.DISPLAY_TIMESTAMP between '2022-03-01 00:00:00' and 
> '2023-03-01 23:59:59.999');
>  
> Aggregate  (cost=56.64..56.65 rows=1 width=8) (actual time=0.930..0.931 
> rows=1 loops=1)
>    Buffers:shared hit=341
>    ->  Index Scan using 
> history_event_display_timesta_prism_guid_display_timestamp_idx1 on 
> history_event_display_timestamp_20230301 historyeve0_  (cost=0.42..56.64 
> rows=1 width=33) (actual
> time=0.034..0.897 rows=332 loops=1)
>  Index Cond: (((prism_guid)::text = 
> 'i0accd6a2018405f095ee669dc5b4'::text) AND (display_timestamp >= 
> '2022-03-01 00:00:00'::timestamp without time zone) AND (display_timestamp <= 
> '2023-
> 03-01 23:59:59.999'::timestamp without time zone) AND ((product_sid)::text = 
> 'CARSWELL.WESTLAW'::text) AND (is_deleted = '0'::numeric) AND 
> ((client_id)::text = 'WLNCE_VNJXL7'::text))
>      Filter: (((event_type)::text <> ALL
> ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent,coCitesSearchWithinQueryEvent}'::t
> ext[])) AND (((product_view)::text = ANY 
> ('{DefaultProductView,TNPPlus,PLCUS,Indigo,IndigoPremium,INDIGOCA}'::text[])) 
> OR (product_view IS NULL)))
>  Buffers: shared hit=341
> Planning:
>    Buffers: shared hit=6
> Planning Time: 0.266 ms
> Execution Time: 0.965 ms
> (10 rows)
>  
> *** 341 logical reads to find 332 rows.
>  
> Plan hash value: 3361538278
>  
> --
> | Id  | Operation    | Name    | Starts | E-Rows | 
> A-Rows |   A-Time   | Buffers |
> --
> |   0 | SELECT STATEMENT | |  1 |    |
>   1 |00:00:00.01 | 20 |
> |   1 |  SORT AGGREGATE  |     |  1 |  1 |
>   1 |00:00:00.01 |  20 |
> |*  2 |   FILTER | |  1 |    |   
> 332 |00:00:00.01 |  20 |
> |   3 |    PARTITION RANGE SINGLE| |  1 |  1 |    
> 332 |00:00:00.01 |  20 |
> |*  4 | INDEX RANGE SCAN |HISTORY_EVENT_IDX02 |  1 |  1 |    
> 332 |00:00:00.01 |  20 |
> --
>  
> Predicate Information (identified by operation id):
> ---
>  
>    2 - 
> filter(TO_TIMESTAMP(:SYS_B_16,:SYS_B_17)<=TO_TIMESTAMP(:SYS_B_18,:SYS_B_19))
>    4 - access("HISTORYEVE0_"."PRISM_GUID"=:SYS_B_01 AND
>   
> "HISTORYEVE0_"."DISPLAY_TIMESTAMP">=TO_TIMESTAMP(:SYS_B_16,:SYS_B_17) AND
>   "HISTORYEVE0_"."PRODUCT_SID"=:SYS_B_02 AND 
> "HISTORYEVE0_"."IS_DELETED"=:SYS_B_00 AND
>   "HISTORYEVE0_"."CLIENT_ID"=:SYS_B_15 AND 
> "HISTORYEVE0_"."DISPLAY_TIMESTAMP"<=TO_TIMESTAMP(:SYS_B_1
>   8,:SYS_B_19))
>    filter(("HISTORYEVE0_"."CLIENT_ID"=:SYS_B_15 AND 
> "HISTORYEVE0_"."PRODUCT_SID"=:SYS_B_02
>   AND "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_03 AND 
> "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_04 AND
>   "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_05 AND 
> "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_06 AND
>   "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_07 AND 
> "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_08 AND
>   "HISTORYEVE0_"."IS_DELETED"=:SYS_B_00 AND 
> (INTERNAL_FUNCTION("HISTORYEVE0_"."PRODUCT_VIEW") OR
>   "HISTORYEVE0_"."PRODUCT_VIEW" IS NULL)))
>  
> It finds the same 332 rows but it does only 20 logical reads.

In PostgreSQL, the 332 matching rows seem to me stored mostly on different 
pages, while they are
better clustered in your Oracle table.

If it is really very important for you, 

Query performance going from Oracle to Postgres

[Dirschel, Steve]

We are in the process of converting from Oracle to Postgres and I have a query 
that is using the same index in Postgres as is used in Oracle but in Postgres 
the query does 16x more buffer/logical reads.  I'd like to understand why.  The 
query is hitting a partitioned table but to simply things I changed it to hit a 
single partition.  This partition has the same number of rows in Oracle as in 
Postgres.

Here is the Postgres query, partition definition, execution plan.  I will also 
include the Oracle execution plan below in case anyone is interested.

explain (analyze, buffers)
select count(historyeve0_.HISTORY_EVENT_SID) as col_0_0_ from 
hist28.history_event_display_timestamp_20230301 historyeve0_ where 
historyeve0_.IS_DELETED=0
and historyeve0_.PRISM_GUID='i0accd6a2018405f095ee669dc5b4'
and historyeve0_.PRODUCT_SID='CARSWELL.WESTLAW'
and (historyeve0_.EVENT_TYPE not in  
('versionsSearchWithinQueryEvent','notesOfDecisionsSearchWithinQueryEvent','citingReferencesSearchWithinQueryEvent','tocSearchWithinQueryEvent','searchWithinDocumentEvent','coCitesSearchWithinQueryEvent'))
and (historyeve0_.PRODUCT_VIEW in 
('DefaultProductView','TNPPlus','PLCUS','Indigo','IndigoPremium','INDIGOCA')
or historyeve0_.PRODUCT_VIEW is null)
and historyeve0_.CLIENT_ID='WLNCE_VNJXL7'
and (historyeve0_.DISPLAY_TIMESTAMP between '2022-03-01 00:00:00' and 
'2023-03-01 23:59:59.999');

Aggregate  (cost=56.64..56.65 rows=1 width=8) (actual time=0.930..0.931 rows=1 
loops=1)
   Buffers: shared hit=341
   ->  Index Scan using 
history_event_display_timesta_prism_guid_display_timestamp_idx1 on 
history_event_display_timestamp_20230301 historyeve0_  (cost=0.42..56.64 rows=1 
width=33) (actual time=0.034..0.897 rows=332 loops=1)
 Index Cond: (((prism_guid)::text = 
'i0accd6a2018405f095ee669dc5b4'::text) AND (display_timestamp >= 
'2022-03-01 00:00:00'::timestamp without time zone) AND (display_timestamp <= 
'2023-03-01 23:59:59.999'::timestamp without time zone) AND 
((product_sid)::text = 'CARSWELL.WESTLAW'::text) AND (is_deleted = 
'0'::numeric) AND ((client_id)::text = 'WLNCE_VNJXL7'::text))
 Filter: (((event_type)::text <> ALL 
('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent,coCitesSearchWithinQueryEvent}'::text[]))
 AND (((product_view)::text = ANY 
('{DefaultProductView,TNPPlus,PLCUS,Indigo,IndigoPremium,INDIGOCA}'::text[])) 
OR (product_view IS NULL)))
 Buffers: shared hit=341
Planning:
   Buffers: shared hit=6
Planning Time: 0.266 ms
Execution Time: 0.965 ms
(10 rows)

*** 341 logical reads to find 332 rows.

Oracle will find the same 332 rows using the same index but in Oracle it only 
does 20 logical reads.  I thought maybe the index was fragmented so I reindexed 
that index:

reindex index 
hist28.history_event_display_timesta_prism_guid_display_timestamp_idx1;

Plan after that:

Aggregate  (cost=40.64..40.65 rows=1 width=8) (actual time=0.707..0.708 rows=1 
loops=1)
   Buffers: shared hit=328
   ->  Index Scan using 
history_event_display_timesta_prism_guid_display_timestamp_idx1 on 
history_event_display_timestamp_20230301 historyeve0_  (cost=0.42..40.64 rows=1 
width=33) (actual time=0.032..0.683 rows=332 loops=1)
 Index Cond: (((prism_guid)::text = 
'i0accd6a2018405f095ee669dc5b4'::text) AND (display_timestamp >= 
'2022-03-01 00:00:00'::timestamp without time zone) AND (display_timestamp <= 
'2023-03-01 23:59:59.999'::timestamp without time zone) AND 
((product_sid)::text = 'CARSWELL.WESTLAW'::text) AND (is_deleted = 
'0'::numeric) AND ((client_id)::text = 'WLNCE_VNJXL7'::text))
 Filter: (((event_type)::text <> ALL 
('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent,coCitesSearchWithinQueryEvent}'::text[]))
 AND (((product_view)::text = ANY 
('{DefaultProductView,TNPPlus,PLCUS,Indigo,IndigoPremium,INDIGOCA}'::text[])) 
OR (product_view IS NULL)))
 Buffers: shared hit=328
Planning:
   Buffers: shared hit=27
Planning Time: 0.321 ms
Execution Time: 0.741 ms
(10 rows)

Shared hit came down a little but is still 16x more than Oracle.  The actual 
query will hit 12 partitions so the extra amount of work it does in Postgres 
adds up (note some data points will find significantly more rows than 332 so 
performance is much worse than shown here but this data point is used just to 
show the difference between Oracle and Postgres).  The interesting part it is 
seems Postgres is doing 1 shared hit per row.  I don't know anyone on this list 
knows Postgres's internal index implementations vs Oracle's  but is Postgres's 
Index Scan operation significantly different than Oracle's index range scan?  
There is something implemented much less efficiently here vs Oracle and I don't 
know what I can do to get this query to perform more closer to 

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Jehan-Guillaume de Rorthais]

On Wed, 6 Sep 2023 19:34:40 +0530
Sai Teja  wrote:

> In my local it is windows OS and locale is English_united_states.1252 and
> in local it is converting as expected ( testµ into TESTµ)

I'm not familiar with PostgreSQL under Windows. Maybe collation from locale
"English_united_states.1252" is available in PostgreSQL? Could you try creating
a database/table/column using this collation?

Or just look at available collations using:

select collname from pg_collation

++

Re: Ynt: Pgbackrest Restore Error - Segmentation fault (core dumped)

[Rob Sargent]

On 9/6/23 11:27, pgdba pgdba wrote:

I removed  it but I keep getting the same error

OK.  The custom here is to put your response at the bottom  of short 
messages ("bottom post") or intermixed with original as appropriate on 
longer posts.  Putting your reply first ("top posting") is discouraged 
due to archival and clarity issues.


At this point you're in to debugging the "core" file, likely in the dir 
you're running pgbackrest from.  Standard C tools required ;)  I suspect 
a library mismatch: maybe LDD on the executable would be informative (to 
the knowledgeable).

Re: running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"

[Imre Samu]

> But how is that corruption happening - I mean it is a docker image,
> freshly fetched from the registry.

Hi Torsten,

Maybe you have to increase the "*--stop-timeout" value ;   ( or
"*stop_grace_period"
in docker-compose )
https://github.com/docker-library/postgres/issues/544#issuecomment-455738848

*docker run: **" --stop-timeout  Timeout (in seconds) to stop a container "*
https://docs.docker.com/engine/reference/commandline/run/
or
https://docs.docker.com/compose/compose-file/compose-file-v3/#stop_grace_period

And recommended in the Dockerfile:
https://github.com/docker-library/postgres/blob/master/Dockerfile-debian.template#L208









*STOPSIGNAL SIGINT## An additional setting that is recommended for all
users regardless of this# value is the runtime "--stop-timeout" (or your
orchestrator/runtime's# equivalent) for controlling how long to wait
between sending the defined# STOPSIGNAL and sending SIGKILL (which is
likely to cause data corruption).## The default in most runtimes (such as
Docker) is 10 seconds, and the# documentation at
https://www.postgresql.org/docs/12/server-start.html
 notes# that even 90
seconds may not be long enough in many instances.*

regards,
 Imre


Torsten Krah  ezt írta (időpont: 2023. szept. 6., Sze,
14:45):

> Am Mittwoch, dem 06.09.2023 um 10:21 +0200 schrieb Laurenz Albe:
> > You are lucky that the corrupted table is one that holds data that
> > can be rebuilt.
>
> It is a test instance / container anyway which is deleted afterwards
> and can be setup again as often as I want.
>
> But how is that corruption happening - I mean it is a docker image,
> freshly fetched from the registry.
>
> After that I am starting a container from that image, (re)importing
> data (different tests => different data so the cycle of delete data /
> import data / analyze the data happens quite often) and running my
> tests.
> The OS does not report anything which would relate nor does any other
> tool / system fail nor does postgresl itself fail on any other table
> here - it always fails only on that analyze part.
>
> That happens all in about 8-10 minutes for the whole process - what is
> causing that corruption in that short timeframe here?
>
> regards
>
> Torsten
>
>
>
>

Re: Q: inet operators for IPv4 encapsulated in IPv6

[Albrecht Dreß]

Am 06.09.23 19:41 schrieb(en) Erik Wienhold:

The docs don't spell it out, but inet operators and functions expect values of 
the same inet family.  Comparing IPv4 and IPv6 always returns false, e.g. [1].
The only hint in the docs that may imply this is [2]:

"When sorting inet or cidr data types, IPv4 addresses will always sort
 before IPv6 addresses, including IPv4 addresses encapsulated or mapped
 to IPv6 addresses, such as ::10.2.3.4 or :::10.4.3.2."


Ah, I missed that section in the docs.  Thanks a lot for your explanation and 
the pointers!


You can write your own function that converts IPv4-mapped IPv6 addresses to 
IPv4 and then do the comparison:

postgres=# SELECT ltrim(host(':::192.168.1.5'::inet & 
'::255.255.255.255'::inet), ':')::inet;
ltrim
-
 192.168.1.5
(1 row)


That's a nice approach, will do that.

Thanks again, Albrecht.

pgpJ_K0el7bYl.pgp
Description: PGP signature

Re: Q: inet operators for IPv4 encapsulated in IPv6

[Erik Wienhold]

On 06/09/2023 16:51 CEST Albrecht Dreß  wrote:

> I have a question regarding the use of inet operators in environments with
> mixed IPv4 and IPv6 notations.
>
> Consider the example from sect. 9.12. “Network Address Functions and 
> Operators”
> in the docs which returns properly
>
> 
> postgres=# select '192.168.1.5'::inet << '192.168.1/24'::inet;
>   ?column?
> --
>   t
> (1 row)
> 
>
> However, for an IPv6 encapsulated IPv4 address, the result is
>
> 
> postgres=# select ':::192.168.1.5'::inet << '192.168.1/24'::inet;
>   ?column?
> --
>   f
> (1 row)
> 
>
> Although the representation is different, in reality '192.168.1.5' and
> ':::192.168.1.5' designate the same node, so IMHO it would be logical if
> the second statement would also return True.  Is there any option to simply
> achieve this?  I use PostgreSQL v. 15 on a Debian Bookworm box.

The docs don't spell it out, but inet operators and functions expect values of
the same inet family.  Comparing IPv4 and IPv6 always returns false, e.g. [1].
The only hint in the docs that may imply this is [2]:

"When sorting inet or cidr data types, IPv4 addresses will always sort
 before IPv6 addresses, including IPv4 addresses encapsulated or mapped
 to IPv6 addresses, such as ::10.2.3.4 or :::10.4.3.2."

You can write your own function that converts IPv4-mapped IPv6 addresses to
IPv4 and then do the comparison:

postgres=# SELECT ltrim(host(':::192.168.1.5'::inet & 
'::255.255.255.255'::inet), ':')::inet;
ltrim
-
 192.168.1.5
(1 row)


[1] 
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/network.c;h=ae11de0ba5007e445017b91acfeff3adc2e3d6af#l923
[2] https://www.postgresql.org/docs/current/datatype-net-types.html

--
Erik

Ynt: Pgbackrest Restore Error - Segmentation fault (core dumped)

[pgdba pgdba]

I removed  it but I keep getting the same error

Gönderen: Rob Sargent 
Gönderildi: 6 Eylül 2023 Çarşamba 16:32
Kime: pgsql-general@lists.postgresql.org 
Konu: Re: Pgbackrest Restore Error - Segmentation fault (core dumped)

On 9/6/23 05:08, pgdba pgdba wrote:
Hello, when I restore with Pgbackrest, I get the following error, I couldn't 
find a solution when I researched, can you support?

postgres@dev-test:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf 
--stanza=db3 --log-level-console=info --type=immediate restore
WARN: configuration file contains invalid option 'archive-mode'
2023-09-06 06:54:45.826 P00 INFO: restore command begin 2.27: 
--config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info 
--pg1-path=/var/lib/ postgresql/11 --repo1-host=0.0.0.1 
--repo1-host-user=postgres --stanza=db3 --type=immediate
Segmentation fault (core dumped)

Thanks,
Have you tried removing the invalid option ('archive-mode') from the config 
file?

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Sai Teja]

I added one column with generated always column with UPPER CASE like below:-

Alter table table_name t add column data varchar(8000) generated always as
(UPPER(t.content)) stored

Data column is generated always constraint here

This column has many sentences for each row in which some of the characters
are in Greek language.
Like µ, ë, ä, Ä etc..
So, for the example testµ when I choose
1. Select UPPER('testµ')
Output :- TESTM

But as per mail conversation I have used COLLATE ucs_basic like
2. Select UPPER('testµ' collate "ucs_basic")
Output :- TESTµ (which is correct)


3. SELECT UPPER('Mass' collate "ucs_basic")
Output :- MASS (which is correct)

4. Select data from table (here data is the column which is created with
generated always column like mentioned above)

For some of the rows which contains Greek characters I'm getting wrong
output.

For ex:- for the word 'MASS' I'm getting 'µASS' when I select the data from
the table

Summary:- I'm getting wrong output when I use upper keyword with collation
for the table
But when I explicitly call upper keyword with collation like mentioned in
above I'm getting the results as expected.

Even I tried to add collation in the column itself but it didn't worked.

Alter table table_name t add column data varchar(8000) generated always as
(UPPER(t.content, collation "ucs_basic")) stored
Or
Alter table table_name t add column data varchar(8000) generated always as
(UPPER(t.content) collation "ucs_basic") stored

Both didn't worked. As I got wrong output when I selected the data from the
table.

On Wed, 6 Sep, 2023, 10:18 pm Erik Wienhold,  wrote:

> On 06/09/2023 18:37 CEST Erik Wienhold  wrote:
>
> > Homoglyphs are one explanation if you get 'µass' from the generated
> column as
> > described.
>
> postgres=# SELECT upper('흻횊횜횜');
>  upper
> ---
>  흻횊횜횜
> (1 row)
>
> The codepoints I picked are:
>
> * MATHEMATICAL SANS-SERIF BOLD SMALL MU
> * MATHEMATICAL MONOSPACE SMALL A
> * MATHEMATICAL MONOSPACE SMALL S
>
> --
> Erik
>

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Erik Wienhold]

On 06/09/2023 18:37 CEST Erik Wienhold  wrote:

> Homoglyphs are one explanation if you get 'µass' from the generated column as
> described.

postgres=# SELECT upper('흻횊횜횜');
 upper
---
 흻횊횜횜
(1 row)

The codepoints I picked are:

* MATHEMATICAL SANS-SERIF BOLD SMALL MU
* MATHEMATICAL MONOSPACE SMALL A
* MATHEMATICAL MONOSPACE SMALL S

--
Erik

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Erik Wienhold]

On 06/09/2023 18:04 CEST Sai Teja  wrote:

> When I'm trying the below command
> Select UPPER('Mass')
> I'm getting MASS as output .
> But when I created the column with generated always constraint
> For example
> Alter table xyz add column xyz varchar(800) generated always as 
> (UPPER(content)) stored
>
> Here content is original string coming from the table
> The output is coming as 'µass" when I am selecting the data from the table

Please provide the statements (CREATE TABLE, INSERT, and SELECT) to reproduce
it otherwise it's hard to analyze what's going on.

Homoglyphs are one explanation if you get 'µass' from the generated column as
described.  Another explanation is that you just selected a different column.

--
Erik

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Sai Teja]

Thank you all for your response.
Got one more query,

When I'm trying the below command
Select UPPER('Mass')
I'm getting MASS as output .
But when I created the column with generated always constraint
For example
Alter table xyz add column xyz varchar(800) generated always as
(UPPER(content)) stored

Here content is original string coming from the table
The output is coming as 'µass" when I am selecting the data from the table

Even When I try to use collat C or ucs_basics I'm getting the same when I
select the data from the table
But when I select the data explicitly through UPPER netword like select
UPPER('Mass') then I'm getting expected output such as MASS

Would request you to please suggest the ideas to resolve this issue.

Thanks,
Sai Teja


On Wed, 6 Sep, 2023, 8:59 pm Francisco Olarte, 
wrote:

> On Wed, 6 Sept 2023 at 16:40, Sai Teja 
> wrote:
> > Actually we are migrating the data from DB2 to postgreSQL. So in DB2 the
> upper method is converting µ as µ only but not as M.
> > So, while validating the data we have encountered this problem.
> >
> > We want to have the data in both DB's should be similar (db2 and
> postgreSQL)
> Data seems to be the same, function behaviour is what seems different.
>
> > In my local it is windows OS and locale is English_united_states.1252
> and in local it is converting as expected ( testµ into TESTµ)
>
> Maybe because unicode has GREEK CAPITAL LETTER MU ( 924, which looks
> like capital M ), GREEK SMALL LETTER MU ( 956 ) and MICRO SIGN ( 181,
> which looks like small mu ) while windows-1252 only has 0xB6 as micro.
> OTOH Windows-1253 ( greek ansi ) has all three.
>
> If your small mu are really micro-sign ( which is suspected if youused
> 1252 ) maybe changing them to that helps ( but I do not have the
> resources to test that on hand ).
>
> Francisco Olarte.
>

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Francisco Olarte]

On Wed, 6 Sept 2023 at 16:40, Sai Teja  wrote:
> Actually we are migrating the data from DB2 to postgreSQL. So in DB2 the 
> upper method is converting µ as µ only but not as M.
> So, while validating the data we have encountered this problem.
>
> We want to have the data in both DB's should be similar (db2 and postgreSQL)
Data seems to be the same, function behaviour is what seems different.

> In my local it is windows OS and locale is English_united_states.1252 and in 
> local it is converting as expected ( testµ into TESTµ)

Maybe because unicode has GREEK CAPITAL LETTER MU ( 924, which looks
like capital M ), GREEK SMALL LETTER MU ( 956 ) and MICRO SIGN ( 181,
which looks like small mu ) while windows-1252 only has 0xB6 as micro.
OTOH Windows-1253 ( greek ansi ) has all three.

If your small mu are really micro-sign ( which is suspected if youused
1252 ) maybe changing them to that helps ( but I do not have the
resources to test that on hand ).

Francisco Olarte.

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Sai Teja]

Hi Tom Lane, Thank you for your reply!

Actually we are migrating the data from DB2 to postgreSQL. So in DB2 the
upper method is converting µ as µ only but not as M.

So, while validating the data we have encountered this problem.

We want to have the data in both DB's should be similar (db2 and postgreSQL)

In my local it is windows OS and locale is English_united_states.1252 and
in local it is converting as expected ( testµ into TESTµ)

So, even I want to change locale i cannot change now. Since already db is
created. BTW this db is hosted in azure.

Would request to please suggest if any alternatives are there to resolve
this issue.

Thanks,
Sai Teja



On Wed, 6 Sep, 2023, 7:23 pm Tom Lane,  wrote:

> Sai Teja  writes:
> > I am using UPPER document name for converting the text from lower case
> into
> > upper case.
> > But here for the below example
> > Command:- select UPPER(testµ)
> > Input :- testµ
> > Output:- TESTM
> > Expected output :-  TESTµ
>
> According to https://en.wikipedia.org/wiki/Greek_alphabet
> the upper-case form of µ (mu) is in fact M, so I'm not sure
> what problem you have with this.  But in any case, you are
> complaining in the wrong place.  This conversion is determined
> by the rules of the selected locale.  If you don't like it,
> choose another locale, or make your own, or try to persuade
> the glibc maintainers that they are wrong.
>
> regards, tom lane
>

Q: inet operators for IPv4 encapsulated in IPv6

[Albrecht Dreß]

I have a question regarding the use of inet operators in environments with 
mixed IPv4 and IPv6 notations.

Consider the example from sect. 9.12. “Network Address Functions and Operators” 
in the docs which returns properly


postgres=# select '192.168.1.5'::inet << '192.168.1/24'::inet;
 ?column?
--
 t
(1 row)


However, for an IPv6 encapsulated IPv4 address, the result is


postgres=# select ':::192.168.1.5'::inet << '192.168.1/24'::inet;
 ?column?
--
 f
(1 row)


Although the representation is different, in reality '192.168.1.5' and 
':::192.168.1.5' designate the same node, so IMHO it would be logical if 
the second statement would also return True.  Is there any option to simply 
achieve this?  I use PostgreSQL v. 15 on a Debian Bookworm box.

Thanks in advance, Albrecht.

pgpDPf4y3ba3s.pgp
Description: PGP signature

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Erik Wienhold]

On 06/09/2023 15:42 CEST Sai Teja  wrote:

> I am using UPPER document name for converting the text from lower case into
> upper case.
> But here for the below example
> Command:- select UPPER(testµ)
> Input :- testµ
> Output:- TESTM
> Expected output :- TESTµ
>
> Here, that character is converting into M which should not be case
> The postgreSQL is hosted in Linux Machine which is using Locale_ctype:- 
> en_US_utf.8

Use can use collation C or ucs_basic if you want to uppercase ASCII only:

postgres=# SELECT upper('testµ' COLLATE "C");
 upper
---
 TESTµ
(1 row)

--
Erik

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Laurenz Albe]

On Wed, 2023-09-06 at 19:34 +0530, Sai Teja wrote:
> Actually we are migrating the data from DB2 to postgreSQL. So in DB2 the 
> upper method is converting µ as µ only but not as M.

Then you are using a different collation in DB2, or "upper" (which is not 
standardized,
as far as I know), is defined differently there.

> So, while validating the data we have encountered this problem.
> 
> We want to have the data in both DB's should be similar (db2 and postgreSQL)
> 
> In my local it is windows OS and locale is English_united_states.1252 and in 
> local it is converting as expected ( testµ into TESTµ)
> 
> So, even I want to change locale i cannot change now. Since already db is 
> created. BTW this db is hosted in azure.
> 
> Would request to please suggest if any alternatives are there to resolve this 
> issue. 

Specify a collation that does what you want explicitly:

SELECT upper('testµ' COLLATE "C");

 upper 
═══
 TESTµ
(1 row)

But then don't complain if your "ö" and "é" are not uppercased either.

Yours,
Laurenz Albe

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Tom Lane]

Sai Teja  writes:
> I am using UPPER document name for converting the text from lower case into
> upper case.
> But here for the below example
> Command:- select UPPER(testµ)
> Input :- testµ
> Output:- TESTM
> Expected output :-  TESTµ

According to https://en.wikipedia.org/wiki/Greek_alphabet
the upper-case form of µ (mu) is in fact M, so I'm not sure
what problem you have with this.  But in any case, you are
complaining in the wrong place.  This conversion is determined
by the rules of the selected locale.  If you don't like it,
choose another locale, or make your own, or try to persuade
the glibc maintainers that they are wrong.

regards, tom lane

postgreSQL UPPER Method is converting the character "µ" into "M"

[Sai Teja]

Hi team,

I am using UPPER document name for converting the text from lower case into
upper case.
But here for the below example
Command:- select UPPER(testµ)
Input :- testµ
Output:- TESTM
Expected output :-  TESTµ

Here, that character is converting into M which should not be case
The postgreSQL is hosted in Linux Machine which is using Locale_ctype:-
en_US_utf.8

Would request you to please suggest any ideas to resolve this issue.

It'll would be very helpful and appreciated.

Thanks,
Sai Teja

Re: Pgbackrest Restore Error - Segmentation fault (core dumped)

[Rob Sargent]

On 9/6/23 05:08, pgdba pgdba wrote:
Hello, when I restore with Pgbackrest, I get the following error, I 
couldn't find a solution when I researched, can you support?


postgres@dev-test:~$ pgbackrest 
--config=/etc/pgbackrest/pgbackrest.conf --stanza=db3 
--log-level-console=info --type=immediate restore

WARN: configuration file contains invalid option 'archive-mode'
2023-09-06 06:54:45.826 P00 INFO: restore command begin 2.27: 
--config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info 
--pg1-path=/var/lib/ postgresql/11 --repo1-host=0.0.0.1 
--repo1-host-user=postgres --stanza=db3 --type=immediate

Segmentation fault (core dumped)

Thanks,
Have you tried removing the invalid option ('archive-mode') from the 
config file?

Re: Pgbackrest Restore Error - Segmentation fault (core dumped)

[Daniel Gustafsson]

> On 6 Sep 2023, at 13:08, pgdba pgdba  wrote:
> 
> Hello, when I restore with Pgbackrest, I get the following error, I couldn't 
> find a solution when I researched, can you support?

You might be better off raising this question on the pgbackreset Github repo as
an issue, where more pgbackrest developers are likely to see it.

Are you able to repeat the issue such that you can get a stack trace from it?
That will be helpful for anyone trying to find the issue.  The postgres wiki
has some information on getting a stack trace from a postgres process, and
while not the same the process is similar enough that it might be helpful
reading.

--
Daniel Gustafsson

Re: Postgres partition max limit

[Chris Travers]

On Wed, Sep 6, 2023 at 5:27 PM Daulat  wrote:

> Hi Team,
>
> Do we have a max limit of partitions for a table in postgres?
>

As far as I can tell, there is no real hard limit to that per se.  After
all, you will eventually run out of OIDs for pg_attribute but that would
affect the number of columns in the database.

However I can say that based on past testing you probably don't want
thousands of partitions in your database.  At least last time I tried,
planning time would eventually become unreasonably high though I assume
things have improved somewhat since then (and to be fair, that system was
also in a pretty unusual configuration that may have made things worse).

Since that is a gradual process the specific limits for your application
may depend on your latency requirements for the queries in your
application.   I would recommend testing your application by creating all
the partitions you expect even if they are empty, and seeing how long
EXPLAIN takes to run.  If that's good enough, then go for it.  If it is too
long then you could pre-calculate what partition to hit or you could use
fewer partitions.

>
> Thanks.
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Pgbackrest Restore Error - Segmentation fault (core dumped)

[pgdba pgdba]

Hello, when I restore with Pgbackrest, I get the following error, I couldn't 
find a solution when I researched, can you support?

postgres@dev-test:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf 
--stanza=db3 --log-level-console=info --type=immediate restore
WARN: configuration file contains invalid option 'archive-mode'
2023-09-06 06:54:45.826 P00 INFO: restore command begin 2.27: 
--config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info 
--pg1-path=/var/lib/ postgresql/11 --repo1-host=0.0.0.1 
--repo1-host-user=postgres --stanza=db3 --type=immediate
Segmentation fault (core dumped)

Thanks,

Re: running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"

[Torsten Krah]

Am Mittwoch, dem 06.09.2023 um 12:04 +0200 schrieb Erik Wienhold:
> > I am running that one (official docker image)
> > 
> > PostgreSQL 13.11 (Debian 13.11-1.pgdg110+1) on x86_64-pc-linux-gnu,
> > compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> 
> Have you also tried with 13.12?

Yes, but it did also happen on previous versions before 13.11 / 13.12
sometimes (I just ignored it until now because it happens so rarely).

> 
> 
> Does the unique constraint violation always occur for the same row? 
> OID 2609
> is pg_description.

As I don't have a reproducer yet (I did not track stats but lets say it
runs fine for 100 / 200 times and 1 or 2 of those are failing with
those mentioned analyze errors - it may even be less than that) I can't
tell you if it fails always over that OID - I need to wait for it to
happen again, I will report here if it is the same - may take some time
;).


Torsten

Re: running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"

[Erik Wienhold]

On 06/09/2023 09:46 CEST Torsten Krah  wrote:

> I am running that one (official docker image)
>
> PostgreSQL 13.11 (Debian 13.11-1.pgdg110+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Have you also tried with 13.12?

> and one of my nightly jobs reported that error yesterday when running
> an "ANALYZE":
>
> FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint 
> »pg_statistic_relid_att_inh_index«
>   Detail: Schlüssel »(starelid, staattnum, stainherit)=(2609, 4, f)« 
> existiert bereits.
>
> which should translate to something like:
>
> ERROR:  duplicate key value violates unique constraint 
> "pg_statistic_relid_att_inh_index"
> > DETAIL:  Key (starelid, staattnum, stainherit)=(2609, 4, f) already exists.
>
> Anyone an idea what's wrong?
>
> Maybe (not?) related but sometimes the analyze does fail with:
>
> ERROR: attempted to delete invisible tuple
>
>
> Both errors are only happening here and there - so I don't have a
> reproducer, but still I am curious what is wrong here with me running
> an "ANALYZE" after my data import.

Does the unique constraint violation always occur for the same row?  OID 2609
is pg_description.

--
Erik

Re: running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"

[Laurenz Albe]

On Wed, 2023-09-06 at 10:33 +0200, Torsten Krah wrote:
> Am Mittwoch, dem 06.09.2023 um 10:21 +0200 schrieb Laurenz Albe:
> > You are lucky that the corrupted table is one that holds data that
> > can be rebuilt.
> 
> It is a test instance / container anyway which is deleted afterwards
> and can be setup again as often as I want.
> 
> But how is that corruption happening - I mean it is a docker image,
> freshly fetched from the registry.
> 
> After that I am starting a container from that image, (re)importing
> data (different tests => different data so the cycle of delete data /
> import data / analyze the data happens quite often) and running my
> tests.
> The OS does not report anything which would relate nor does any other
> tool / system fail nor does postgresl itself fail on any other table
> here - it always fails only on that analyze part.
> 
> That happens all in about 8-10 minutes for the whole process - what is
> causing that corruption in that short timeframe here?

If you have a reproducible way to create the data corruption, that would
be very interesting.  It micht be a software bug.

Yours,
Laurenz Albe

Re: running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"

[Torsten Krah]

Am Mittwoch, dem 06.09.2023 um 10:21 +0200 schrieb Laurenz Albe:
> You are lucky that the corrupted table is one that holds data that
> can be rebuilt.

It is a test instance / container anyway which is deleted afterwards
and can be setup again as often as I want.

But how is that corruption happening - I mean it is a docker image,
freshly fetched from the registry.

After that I am starting a container from that image, (re)importing
data (different tests => different data so the cycle of delete data /
import data / analyze the data happens quite often) and running my
tests.
The OS does not report anything which would relate nor does any other
tool / system fail nor does postgresl itself fail on any other table
here - it always fails only on that analyze part.

That happens all in about 8-10 minutes for the whole process - what is
causing that corruption in that short timeframe here?

regards

Torsten

Re: running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"

[Laurenz Albe]

On Wed, 2023-09-06 at 09:46 +0200, Torsten Krah wrote:
> I am running that one (official docker image)
> 
> PostgreSQL 13.11 (Debian 13.11-1.pgdg110+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> 
> and one of my nightly jobs reported that error yesterday when running
> an "ANALYZE":
> 
> FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint 
> »pg_statistic_relid_att_inh_index«
>   Detail: Schlüssel »(starelid, staattnum, stainherit)=(2609, 4, f)« 
> existiert bereits.
> 
> which should translate to something like:
> 
> ERROR:  duplicate key value violates unique constraint 
> "pg_statistic_relid_att_inh_index"
> > DETAIL:  Key (starelid, staattnum, stainherit)=(2609, 4, f) already exists.
> 
> 
> Anyone an idea what's wrong?

Yes: the metadata table pg_statistic has data corruption.

> Maybe (not?) related but sometimes the analyze does fail with:
> 
> ERROR: attempted to delete invisible tuple

That also looks like data corrupton, albeit different one.

> Both errors are only happening here and there - so I don't have a
> reproducer, but still I am curious what is wrong here with me running
> an "ANALYZE" after my data import.

To fix the "pg_statistic" error:

- take down time

- set "allow_system_mods = on"

- TRUNCATE pg_statistic;

- ANALYZE;


You are lucky that the corrupted table is one that holds data that can be 
rebuilt.

Yours,
Laurenz Albe

Re: Postgres partition max limit

[Laurenz Albe]

On Wed, 2023-09-06 at 13:35 +0530, Daulat wrote:
> Do we have a max limit of partitions for a table in postgres?

I don't think there is a technical limit.

But as soon as you have more than a few thousand partitions, the
experience won't be that great any more.

Yours,
Laurenz Albe

Postgres partition max limit

[Daulat]

Hi Team,

Do we have a max limit of partitions for a table in postgres?

Thanks.

running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"

[Torsten Krah]

Hi,

I am running that one (official docker image)

PostgreSQL 13.11 (Debian 13.11-1.pgdg110+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

and one of my nightly jobs reported that error yesterday when running
an "ANALYZE":

FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint 
»pg_statistic_relid_att_inh_index«
  Detail: Schlüssel »(starelid, staattnum, stainherit)=(2609, 4, f)« existiert 
bereits.

which should translate to something like:

ERROR:  duplicate key value violates unique constraint 
"pg_statistic_relid_att_inh_index"
> DETAIL:  Key (starelid, staattnum, stainherit)=(2609, 4, f) already exists.


Anyone an idea what's wrong?

Maybe (not?) related but sometimes the analyze does fail with:

ERROR: attempted to delete invisible tuple


Both errors are only happening here and there - so I don't have a
reproducer, but still I am curious what is wrong here with me running
an "ANALYZE" after my data import.

thanks for insights :)

kind regards

Torsten