Re: Suggestion about tcp_keepalives_idle parameter in the document
On Sat, 2024-06-08 at 05:37 +0800, yanliang lei wrote: > there is the following description in the > https://www.postgresql.org/docs/current/runtime-config-connection.html： > > A value of 0 (the default) selects the operating system's default. > but there is no description about“ the operating system's default > value in the linux is tcp_keepalive_time kernel parameter” Documenting the defaults for each supported operating system is not PostgreSQL's task. In this article[1], I figured out the defaults for Linux, MacOS and Windows. Yours, Laurenz Albe [1]: https://www.cybertec-postgresql.com/en/tcp-keepalive-for-a-better-postgresql-experience/#defaults
Re:Re: Suggestion about tcp_keepalives_idle parameter in the document
thank you very much！ At 2024-06-10 17:03:07, "Laurenz Albe" wrote: >On Sat, 2024-06-08 at 05:37 +0800, yanliang lei wrote: >> there is the following description in the >> https://www.postgresql.org/docs/current/runtime-config-connection.html： >> >> A value of 0 (the default) selects the operating system's default. >> but there is no description about“ the operating system's default >> value in the linux is tcp_keepalive_time kernel parameter” > >Documenting the defaults for each supported operating system is not >PostgreSQL's task. > >In this article[1], I figured out the defaults for Linux, MacOS >and Windows. > >Yours, >Laurenz Albe > > [1]: > https://www.cybertec-postgresql.com/en/tcp-keepalive-for-a-better-postgresql-experience/#defaults >
Re: Clarify the ordering guarantees in combining queries (or lack thereof)
The manual still seems to offer just such a guarantee here: https://www.postgresql.org/docs/current/sql-select.html#SQL-UNION > Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses. In the case of UNION ALL, is this supposed to mean ... a.) Individual legs are evaluated left to right, but sets returned from each are not guaranteed to be appended in the same order, nor is the order within each set guaranteed to be preserved. b.) Individual legs are evaluated left to right, sets returned from each are appended in order, but the order within each set is not guaranteed to be preserved. c.) Individual legs are evaluated left to right, sets returned from each are appended in order, and the order within each set is guaranteed to be preserved. d.) The manual is outdated. Since the advent of "Parallel Append" in Postgres 11, left to right evaluation is not guaranteed in all cases. Obviously, the order *within* each leg is not guaranteed without ORDER BY attached to it, enclosed in parentheses. But that's an orthogonal issue. The question is, what of the returned order is preserved after UNION ALL? And what is *guaranteed*? I guess the term "evaluated" is ambiguous. I would love the manual to be clear about this. Related discussion here: https://dba.stackexchange.com/questions/316818/are-results-from-union-all-clauses-always-appended-in-order Regards Erwin On Mon, Jun 10, 2024 at 3:03 PM Shay Rojansky wrote: > >> No, there is no guarantee. It's just that UNION ALL works this way today > >> (preserving the order of the subselects) - and I'm not even sure about > >> that, it may not preserve the order in all cases, with different > indexes or > >> partitioning or a parallel plan, etc. > > > > Yeah, that. You can get a parallelized plan today for UNION ALL: > > ... > > > Since the documentation doesn't make a guarantee there is none. > > Thanks all for the confirmation. > > I'd still suggest documenting the lack of guarantee; yes, mathematically > it may be correct to not document lack of guarantees, but users can come > with various expectations and misunderstandings (I also wasn't clear on > this specifically for UNION ALL), and it's always good to say this kind of > thing explicitly. >
Documenting more pitfalls of non-default collations?
Hi there,
I mentioned to Jeremy at pgConf.dev that using non-default collations
in some SQL idioms can produce undesired results, and he asked me to
send an email. An example idiom is the way Django implements
case-insensitive comparisons using "upper(x) = upper(y)" [1][2][3] ,
which returns false if x = y but they have different collations that
produce different uppercase.
For example, assuming the default collation performs standard Unicode
case mapping:
# select upper('i') = upper('i' collate "tr-x-icu");
f
# select upper('é') = upper('é' collate "C");
f
Or with collations in DDL instead:
# create table t (
tr text collate "tr-x-icu",
c text collate "C"
);
CREATE TABLE
# insert into t values ('i', 'é');
INSERT 0 1
# select count(*) from t where upper('i') = upper(tr);
0
# select count(*) from t where upper('é') = upper(c);
0
This is expected, given a careful reading of the collation docs, but
it's not really highlighted in any of the examples--in each example
that doesn't produce an error, all of the collation-sensitive
functions/operators end up applying the same collation. Maybe there
should be an example that applies different collations in different
subexpressions, and/or a warning against constructions like "upper(x)
= upper(y)"?
[1]
https://github.com/django/django/blame/stable/5.1.x/django/db/backends/postgresql/operations.py#L175
[2]
https://github.com/django/django/blame/stable/5.1.x/django/db/backends/postgresql/base.py#L155
[3] https://code.djangoproject.com/ticket/32485
