Hi. Porting a unit test to PostgreSQL, we got a failure related to ordering.
We've distilled it to the below. The DB is en_US.UTF-8, and the sorting we
get
does not make sense to me. The same prefix can be sorted differently based
on the suffix apprently, which doesn't make any sense to me.
Surel
On Tue, 2023-02-14 at 10:31 +0100, Dominique Devienne wrote:
> Hi. Porting a unit test to PostgreSQL, we got a failure related to ordering.
>
> We've distilled it to the below. The DB is en_US.UTF-8, and the sorting we get
> does not make sense to me. The same prefix can be sorted differently base
On Tue, Feb 14, 2023 at 11:23 AM Laurenz Albe
wrote:
> On Tue, 2023-02-14 at 10:31 +0100, Dominique Devienne wrote:
> > Hi. Porting a unit test to PostgreSQL, we got a failure related to
> ordering.
> >
> > We've distilled it to the below. The DB is en_US.UTF-8, and the sorting
> we get
> > does
On 2023-Feb-14, Dominique Devienne wrote:
> Honestly, who expects the same prefix to sort differently based on what
> comes after, in left-to-right languages?
Look, we don't define the collation rules. We just grab the collation
rules defined by experts in collations. In this case the experts h
On Tue, 2023-02-14 at 12:17 +0100, Dominique Devienne wrote:
> On Tue, Feb 14, 2023 at 11:23 AM Laurenz Albe
> wrote:
> > On Tue, 2023-02-14 at 10:31 +0100, Dominique Devienne wrote:
> > > Surely sorting should be "constant left-to-right", no? What are we
> > > missing?
> >
> > No, it isn't. T
On Tue, Feb 14, 2023 at 12:35 PM Alvaro Herrera
wrote:
> On 2023-Feb-14, Dominique Devienne wrote:
> > Honestly, who expects the same prefix to sort differently based on what
> > comes after, in left-to-right languages?
>
Look, we don't define the collation rules.
>
Ok, ok, sorry. To you, Lauren
On Tue, 2023-02-14 at 13:06 +0100, Dominique Devienne wrote:
> > Sure, just make sure to use the definition of C that uses UTF-8 encoding
> > (I think it's typically called C.UTF-8).
>
> OK, so for new DBs, sounds like we need to
>
> CREATE DATABASE ... WITH LOCALE 'C.UTF-8' ENCODING UTF8
>
>
Hello!
When creating an index on multiple columns, does the order of the columns
matter?
(I guess so)
It's mostly for SELECT statements using a condition that include ALL columns of
the index (pkey):
SELECT * FROM art WHERE etb='L1' and code='ART345'
I would naturally put the columns wit
On Mon, Feb 13, 2023 at 12:38:12PM -0500, Tom Lane wrote:
! Peter writes:
! > "rows=1" in the "Hash Anti Join" line is WRONG. It should be
! > 300. Or at least some thousands.
!
! FWIW, this behaves better in v14 and up. In older versions there's
! an ambiguity about what relpages=reltuples=
> On 14/02/2023 18:53 CET Sebastien Flaesch wrote:
>
> Hello!
>
> When creating an index on multiple columns, does the order of the columns
> matter? (I guess so)
Maybe, depending on the queries.
> It's mostly for SELECT statements using a condition that include ALL
> columns of the index (pkey)
Is this the place for suggestions for postgres.org?
I had occasion yesterday to visit the page of available support
companies. I see it's sorted alphabetically. Does that tend to favour
the "A"s? Thinking of Yellow Page (tangible, phone company version)
listings like " knife sharpening"
Hello,
Long time listener, first time caller.
We have a large table (~470 million rows) with integer primary key id (not
null) on a Postgres 14.5 cluster. A third-party tool is attempting to
perform a SELECT-based full table copy in preparation for log-based sync
with a query like the following:
On 2/14/23 15:04, Ben Chrobot wrote:
Hello,
Long time listener, first time caller.
We have a large table (~470 million rows) with integer primary key id
(not null) on a Postgres 14.5 cluster. A third-party tool is
attempting to perform a SELECT-based full table copy in preparation
for log-ba
On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent wrote:
>
> When will id be null in a primary key?
>
>
The OP seems to be aware of this...
"We cannot change the query being executed. Is there any way we can make
the query planner ignore `OR (id IS NULL)` (as that will never be the case
for the PK) an
On 2/14/23 15:30, David G. Johnston wrote:
On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent wrote:
When will id be null in a primary key?
The OP seems to be aware of this...
"We cannot change the query being executed. Is there any way we can
make the query planner ignore `OR (id IS NULL)` (
On 2023-02-14 17:04:51 -0500, Ben Chrobot wrote:
> We have a large table (~470 million rows) with integer primary key id (not
> null) on a Postgres 14.5 cluster. A third-party tool is attempting to perform
> a
> SELECT-based full table copy in preparation for log-based sync with a query
> like the
On 2023-02-14 15:36:32 -0700, Rob Sargent wrote:
> But if the query is supposed to be generic and re-used in a situation where id
> could be null, wouldn't the null id records be fetched every time?
No, they will never be fetched because of the AND (("id" <= ?)).
hp
--
_ | Peter J.
On 2/14/23 15:43, Peter J. Holzer wrote:
On 2023-02-14 15:36:32 -0700, Rob Sargent wrote:
But if the query is supposed to be generic and re-used in a situation where id
could be null, wouldn't the null id records be fetched every time?
No, they will never be fetched because of the AND (("id" <=
> Are these UPDATE's actually necessary?
> In other words has nobody noticed a problem with the data over that time
frame?
I don't know what to make of it.
I had the same question you did.
I now have proof that these hundreds of errors are not an issue.
The postgres logs in pg_log clearly show an
In general, the order of the rows in a result set is stated to be unpredictable
without an "order by" at the outermost level. Famously, beginners observe what
seems to be reliably reproducible ordering in some queries that don't have an
"order by"—and it can take some effort to persuade them tha
On Tue, Feb 14, 2023 at 4:49 PM Bryn Llewellyn wrote:
> I've found that a table function with "returns table(r text)" provides a
> convenient way to write a nicely formatted report using psql that can be
> easily directed to a file with the "\o" metacommand. In general, for cases
> like this, I c
On Wed, 15 Feb 2023 at 11:39, Peter J. Holzer wrote:
> OTOH it could also be argued that the optimizer should be able to
> perform the same simplifications as I did above and produce the same
> code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
> as for WHERE (("id" > ?)) AND (("id" <=
On 2/14/23 14:58, Brad White wrote:
> Are these UPDATE's actually necessary?
> In other words has nobody noticed a problem with the data over that
time frame?
I don't know what to make of it.
I had the same question you did.
I now have proof that these hundreds of errors are not an issue.
T
David Rowley writes:
> On Wed, 15 Feb 2023 at 11:39, Peter J. Holzer wrote:
>> OTOH it could also be argued that the optimizer should be able to
>> perform the same simplifications as I did above and produce the same
>> code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
>> as for WHERE
Hi
> It adds an uncomfortable amount of clutter.
>
> ** Is it essential for correctness? **
>
> It's annoying that the use of "return next" prevents the pattern that each
> "print line" follows from being encapsulated into a procedure. But it is
> what it is, yes?
>
RETURN NEXT, RETURN QUERY (RE
> david.g.johns...@gmail.com wrote:
>
>> b...@yugabyte.com wrote:
>>
>> I've found that a table function with "returns table(r text)" provides a
>> convenient way to write a nicely formatted report using psql that can be
>> easily directed to a file with the "\o" metacommand. In general, for ca
26 matches
Mail list logo
