Re: Help with "Create Extension unaccent"

[Ian Lawrence Barwick]

2024年1月14日(日) 14:53 Lan Xu :
>
> With CREATE EXTENSION unaccent, I’m getting the following:
> ERROR:  extension "unaccent" has no installation script nor update path for 
> version “1.0"
>
> But I can find it under /usr/share/pgsql/extension/unaccent001.0.sql

For recent PostgreSQL versions, the extension files are:

  unaccent--1.0--1.1.sql
  unaccent--1.1.sql

so it looks like something is not right with your installation, but
without any more
details it's not possible to say what.

Regards

Ian Barwick

Why scan all columns when we select distinct c1?

[Yongtao Huang]

PostgreSQL version: 16.1
Operating system:   centos7
Description:

Let me show these explain results first, in PG9.4 and PG16.1.

### Behavior in PG9.4
``` SQL
gpadmin=# create table t1 (c1 int, c2 text);
CREATE TABLE
gpadmin=# explain (costs off, verbose) select distinct c1 from t1;
 QUERY PLAN
-
 HashAggregate
   Output: c1
   Group Key: t1.c1
   ->  Seq Scan on public.t1
 Output: c1 < pay attention <  !!!
(5 rows)
```

### Behavior in PG 16.1
``` SQL
gpadmin=# create table t1 (c1 int, c2 text);
CREATE TABLE
gpadmin=# explain (costs off, verbose) select distinct c1 from t1;
 QUERY PLAN
-
 HashAggregate
   Output: c1
   Group Key: t1.c1
   ->  Seq Scan on public.t1
 Output: c1, c2  < pay attention < !!!
(5 rows)
```

My question is why scan all columns in PG 16.01?
If `select distinct c1`, scan the column `c1` is enough, like PG 9.4.

Related GPDB issue link: https://github.com/greenplum-db/gpdb/issues/15266

Reporter: David Kimura and Yongtao Huang

Re: Issue with loading unicode characters with copy command

[Kiran K V]

 Its UTF-8. Also verified the load file and its utf-8.

Regards,
Kiran

On Fri, Jan 12, 2024 at 10:48 PM Adrian Klaver 
wrote:

> On 1/12/24 07:23, Kiran K V wrote:
> > Hi,
> >
> >
> > I have a UTF8 database and simple table with two columns (integer and
> > varchar). Created a csv file with some multibyte characters and trying
> > to perform load operation using the copy command.
>
> The multibyte characters come from what character set?
>
> >
> > __ __
> >
> > Database info:
> >
> > Postgresql database details:
> >
> > Name|  Owner   | Encoding |  Collate   |
> > Ctype|   Access privileges
> >
> >
> ---+--+--+++---
> >
> > postgres  | postgres | UTF8 | English_India.1252 |
> > English_India.1252 |
> >
> > __ __
> >
> > (Note: I also tried with collate utf8 and no luck)
> >
> >
> > postgres=# set client_encoding='UTF8';
> >
> > SET
> >
> > __ __
> >
> > Table:
> >
> > create table public.test ( PKCOL integer not null, STR1 character
> > varying(64) null, primary key( PKCOL )) 
> >
> > 
> >
> > csv contents:
> >
> > 1|"àáâãäåæçèéêëìíîï"
> >
> > __ __
> >
> > After data loading, actual data is becoming
> >
> > à áâãäåæçèéêëìÃîï
> >
> > hex of this is -
> > c2a1c2a2c2a3c2a4c2a5c2a6c2a7c2a8c2a9c2aac2abc2acc2aec2af
> >
> > __ __
> >
> > The hex values are indeed the UTF-8 encodings of the characters in your
> > expected string, and the presence of `C2` before each character is
> > indicative of how UTF-8 represents certain characters.
> >
> > In UTF-8, characters from the extended Latin set (like `à`, `á`, `â`,
> > etc.) are represented as two bytes. The first byte `C2` or `C3`
> > indicates that this is a two-byte character, and the second byte
> > specifies the character. For example:
> >
> > - `à` is represented as `C3 A0`
> >
> > - `á` is `C3 A1`
> >
> > - `â` is `C3 A2`, and so on.
> >
> > In this case, the `C2` byte is getting interpreted as a separate
> > character and that is the likely reason that an `Â` (which corresponds
> > to `C2`) is seen before each intended character. Looks like UTF-8
> > encoded data is mistakenly interpreted as Latin-1 (ISO-8859-1) or
> > Windows-1252, where each byte is treated as a separate character.
> >
> >
> > Please advise. Thank you very much.
> >
> >
> > Regards,
> >
> > Kiran
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Support for arm64 windows is absent - Would be nice to have!

[NAVSYSTEMS LTD]

Thank you Dominique

For CI and build I think the following could be suitable


[azure-icon-250x250.png]
General availability: Arm-based VMs now available in four additional Azure 
regions | Azure updates | Microsoft 
Azure
azure.microsoft.com




Personally I use an M2 Mac (Xcode) for dev and Parallels VM (Visual Studio) for 
WoA.

Cheers
Sent from my iPhone

On 12 Jan 2024, at 17:19, Dominique Devienne  wrote:


On Fri, Jan 12, 2024 at 6:13 PM David Hussey 
mailto:d...@bluespider.im>> wrote:
It would be good to have proper support for Windows arm64 architecture.

Hi. Have you seen:
https://www.linaro.org/windows-on-arm/postgres/ ?
https://linaro.atlassian.net/wiki/spaces/WOAR/pages/28707291320/PostgreSQL ?

I just did a quick search, FWIW. --DD

Re: Software Bill of Materials (SBOM)

[Julian Coccia]

Hi Cristina,

Have you tried SCANOSS?

To install:

pip3 install scanoss

To generate your SBOM (SPDX lite):

scanoss-py scan --format spdxlite DIRECTORY/

Alternatively, in CycloneDX format instead:

scanoss-py scan --format cyclonedx DIRECTORY/

Hope this helps.

Regards,
Julian

From: Кристина Валентей 
Date: Saturday, 13 January 2024 at 12:03
To: pgsql-general@lists.postgresql.org 
Subject: Software Bill of Materials (SBOM)
Good afternoon.
I'm looking for a way to build sbom files for assembly postgresql, to perform 
software composition analysis (SCA).

Please, tell me how can I do this?

Thank you.

Re: Why scan all columns when we select distinct c1?

[Ron Johnson]

On Sun, Jan 14, 2024 at 6:18 AM Yongtao Huang 
wrote:

> PostgreSQL version: 16.1
> Operating system:   centos7
> Description:
>
> Let me show these explain results first, in PG9.4 and PG16.1.
>
> ### Behavior in PG9.4
> ``` SQL
> gpadmin=# create table t1 (c1 int, c2 text);
> CREATE TABLE
> gpadmin=# explain (costs off, verbose) select distinct c1 from t1;
>  QUERY PLAN
> -
>  HashAggregate
>Output: c1
>Group Key: t1.c1
>->  Seq Scan on public.t1
>  Output: c1 < pay attention <  !!!
> (5 rows)
> ```
>
> ### Behavior in PG 16.1
> ``` SQL
> gpadmin=# create table t1 (c1 int, c2 text);
> CREATE TABLE
> gpadmin=# explain (costs off, verbose) select distinct c1 from t1;
>  QUERY PLAN
> -
>  HashAggregate
>Output: c1
>Group Key: t1.c1
>->  Seq Scan on public.t1
>  Output: c1, c2  < pay attention < !!!
> (5 rows)
> ```
>
> My question is why scan all columns in PG 16.01?
> If `select distinct c1`, scan the column `c1` is enough, like PG 9.4.
>

You can't scan just one column of a row-oriented table.

The real question is why it mentions c2.

Re: Why scan all columns when we select distinct c1?

[Andreas Kretschmer]

Am 14.01.24 um 12:17 schrieb Yongtao Huang:



My question is why scan all columns in PG 16.01?
If `select distinct c1`, scan the column `c1` is enough, like PG 9.4.


good question, I think because the seq scan always reads the column. If 
you create an index on c1, this changes:


postgres=# create index idx1 on t1(c1);
CREATE INDEX
postgres=# set enable_seqscan to off;
SET
postgres=# explain (costs, verbose) select distinct c1 from t1;
 QUERY PLAN
-
 Unique  (cost=0.15..63.93 rows=200 width=4)
   Output: c1
   ->  Index Only Scan using idx1 on public.t1  (cost=0.15..61.10 
rows=1130 width=4)

 Output: c1
(4 rows)


now we scan only the index and not the heap.

Regards, Andreas

--
Andreas Kretschmer
CYBERTEC PostgreSQL Services and Support

Re: Why scan all columns when we select distinct c1?

[Tom Lane]

Ron Johnson  writes:
> On Sun, Jan 14, 2024 at 6:18 AM Yongtao Huang 
> wrote:
>> gpadmin=# create table t1 (c1 int, c2 text);
>> CREATE TABLE
>> gpadmin=# explain (costs off, verbose) select distinct c1 from t1;
>> QUERY PLAN
>> -
>> HashAggregate
>> Output: c1
>> Group Key: t1.c1
>> ->  Seq Scan on public.t1
>> Output: c1, c2  < pay attention < !!!
>> (5 rows)
>> 
>> My question is why scan all columns in PG 16.01?

> You can't scan just one column of a row-oriented table.
> The real question is why it mentions c2.

The planner did that so that the SeqScan step doesn't have to
perform a projection: it can just return (a pointer to)
the physical tuple it found in the table, without doing extra
work to form a tuple containing only c1.  The upper HashAgg
step won't really care.  See use_physical_tlist() in createplan.c.

What I'm confused about is why 9.4 didn't do the same.
That optimization heuristic is very old, and certainly
would be applied by 9.4 in some circumstances.  Testing
says the behavior in this specific case changed at 9.6.
I'm not quite interested enough to drill down further...

regards, tom lane

Re: Help with "Create Extension unaccent"

[Adrian Klaver]

On 1/13/24 21:52, Lan Xu wrote:

With CREATE EXTENSION unaccent, I’m getting the following:
ERROR:  extension "unaccent" has no installation script nor update path for version 
“1.0"

But I can find it under /usr/share/pgsql/extension/unaccent001.0.sql

Thank you for your help!


What OS and version?

What Postgres version?

How did you install Postgres?



Lan



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Why scan all columns when we select distinct c1?

[Yongtao Huang]

Thanks for the explanation.

Tom Lane  于2024年1月14日周日 23:46写道：

> Ron Johnson  writes:
> > On Sun, Jan 14, 2024 at 6:18 AM Yongtao Huang 
> > wrote:
> >> gpadmin=# create table t1 (c1 int, c2 text);
> >> CREATE TABLE
> >> gpadmin=# explain (costs off, verbose) select distinct c1 from t1;
> >> QUERY PLAN
> >> -
> >> HashAggregate
> >> Output: c1
> >> Group Key: t1.c1
> >> ->  Seq Scan on public.t1
> >> Output: c1, c2  < pay attention < !!!
> >> (5 rows)
> >>
> >> My question is why scan all columns in PG 16.01?
>
> > You can't scan just one column of a row-oriented table.
> > The real question is why it mentions c2.
>
> The planner did that so that the SeqScan step doesn't have to
> perform a projection: it can just return (a pointer to)
> the physical tuple it found in the table, without doing extra
> work to form a tuple containing only c1.  The upper HashAgg
> step won't really care.  See use_physical_tlist() in createplan.c.
>
> What I'm confused about is why 9.4 didn't do the same.
> That optimization heuristic is very old, and certainly
> would be applied by 9.4 in some circumstances.  Testing
> says the behavior in this specific case changed at 9.6.
> I'm not quite interested enough to drill down further...
>
> regards, tom lane
>

Moving to Postgresql database

[veem v]

Hello Experts,
If some teams are well versed with the Oracle database architecture and its
optimizers working and designing applications on top of this. Now moving
same team to work on AWS aurora postgresql databases design/development
projects. Is any key design/architectural changes should the app
development team or the database design team, should really aware about, so
as to take right decision on any new development project in AWS aurora
postgresql database?
Or
Is there any list of differences(as compared to Oracle database) in key
concepts like for example basic design concepts, Normalization,
Partitioning, clustering, backup and recovery, Indexing strategy, isolation
level, performance which one should definitely be aware of?