asynchronous commit risk window is overly optimistic

[Jeff Janes]

https://www.postgresql.org/docs/current/wal-async-commit.html:

"If the database crashes during the risk window between an asynchronous
commit and the writing of the transaction's WAL records, then changes made
during that transaction will be lost. The duration of the risk window is
limited because a background process (the “WAL writer”) flushes unwritten
WAL records to disk every wal_writer_delay milliseconds. The actual maximum
duration of the risk window is three times wal_writer_delay because the WAL
writer is designed to favor writing whole pages at a time during busy
periods."

I think the phrase "actual maximum duration" here is far too reassuring.
There is no guarantee that the kernel will wake WAL writer three times in a
row at the times it requested, or even any other smalish multiple of that
time. Even if the wal_writer does repeatedly wake on schedule and requests
a fsync, that fsync itself can take a very large multiple of
wal_writer_delay milliseconds before it takes effect.

If your server experiences a sudden power failure during normal operations
with uncongested IO, then it is very likely that anything asynchronously
committed more than three wal_writer_delay (plus two disk rotations) ago
has made it to disk.  But if it crashes for some other reason than a sudden
power failure, it is less likely to be on disk.  A stricken server can go
wobbly for a long time before finally falling over.

Maybe it should be replaced with something less confident, like "Under
normal conditions, the flush will be initiated within three times
wal_writer_delay because the WAL writer is designed to favor writing whole
pages at a time during busy periods."

Although the whole "because" clause seems to be more inside baseball than
is warranted here.

Cheers,

Jeff

how to install postgresql

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/install-procedure.html
Description:

Hi,

I am a newbie to postgresql. 

usually use to out of box installs like tar balls or pkg or rpm files. 

I am not able to locate this. 

I want to install postgresql on my redhat linux server release 6 , x86_64
machine. 

can you please help me locate self contained binary to complete the install.


kind regards
Chandresh.

ORDER BY LIMIT n does not work as described when using partitioned tables

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/indexes-ordering.html
Description:

Dear Postgres team,
First, thanks for an excellent product and great documentation. This is
world class.

We have found an issue with index and ORDER BY LIMIT 1 when using
partitioned tables. The tabled is partitioned using declarative
partitioning, the partition is created on timestamptz. There is one
partition per day.

Looking at the query planner, it is clear that it is trying to open up all
the indexes (we have hundreds of partitioned tables) to find the 1 row that
we are asking for, even if the row is in the first table that the index
should look at. 

The result is that the query is extremely slow. All the benefits of using
partitioned tables seems to be gone, it is worse than not partitioning.

I would prefer that the issue is solved in the partitioned table, in this
case the documentation is correct. If it is not possible to solve the
partitioning issue, then the documentation should be updated.

Here are the scripts:

CREATE TABLE history.history_bools
(
thing_id bigint,
boolvalue boolean,
"timestamp" timestamp with time zone NOT NULL
) PARTITION BY RANGE ("timestamp") 
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE history.history_bools
OWNER to postgres;

-- Partitions SQL

CREATE TABLE history.history_bools_2018_05_01 PARTITION OF
history.history_bools
FOR VALUES FROM ('2018-04-30 17:00:00-07') TO ('2018-05-01
17:00:00-07');

CREATE UNIQUE INDEX history_bools_2018_05_01_idx
ON history.history_bools_2018_05_01 USING btree
(thing_id, "timestamp")
TABLESPACE pg_default;

CREATE TABLE history.history_bools_2018_05_02 PARTITION OF
history.history_bools
FOR VALUES FROM ('2018-05-01 17:00:00-07') TO ('2018-05-02
17:00:00-07');

CREATE UNIQUE INDEX history_bools_2018_05_02_idx
ON history.history_bools_2018_05_02 USING btree
(thing_id, "timestamp")
TABLESPACE pg_default;



SELECT * FROM history.history_bools WHERE thing_id = 1 AND "timestamp" <
'2018-05-03 12:00:00';