From: Kamigishi Rei <iijima.yun@koumakan.jp>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Andres Freund <andres@anarazel.de>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: Thomas Munro <thomas.munro@gmail.com>
Subject: Re: BUG #17245: Index corruption involving deduplicated entries
Date: Fri, 29 Oct 2021 11:12:07 +0300
Message-ID: <3cb78dcf-e344-e7ca-3c52-51b1f0b6621d@koumakan.jp> (raw)
In-Reply-To: <CAH2-Wz=69Eoh8hWV6K-BzsrVnEzD61Jkaw=MGM_ecS3F4vWRLQ@mail.gmail.com>
References: <202110250104.jve5vzdedck7@alvherre.pgsql>
<87h7d57b3q.fsf@news-spur.riddles.org.uk>
<20211028214209.kbv4ulehz36up6kq@alap3.anarazel.de>
<125603c4-4403-dcbb-221e-fd1d4ef6ed83@koumakan.jp>
<CAH2-Wz=69Eoh8hWV6K-BzsrVnEzD61Jkaw=MGM_ecS3F4vWRLQ@mail.gmail.com>
On 29.10.2021 4:50, Peter Geoghegan wrote:
> Access to the WAL archive would be great. Now might be a good time to
> set "log_min_autovacuum_duration=0" in postgresql.conf -- that
> information might be very helpful later on. This will appear in the
> system logs for PostgreSQL. We'll be particularly interested in the
> "page" table, for the obvious reason.
It looks like this parameter is named "log_autovacuum_min_duration"
instead. Done.
> Also, can you please post output of the following on your server:
> "select * from pg_settings where setting != boot_val;"
Attached; this was done before the above.
--
K. R.
Attachments:settings.txt (text/plain, 17.4K) download
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
----------------------------+------------------------------------------------------------------------+------+------------------------------------------------------+-------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+----------------------+---------+------------+-----------------+-------------------+------------------------------------------------------------------------+------------+------------+-----------------
application_name | psql | | Reporting and Logging / What to Log | Sets the application name to be reported in statistics and logs. | | user | string | client | | | | | psql | | | f
archive_command | test ! -f /var/db/postgres/wal14/%f && cp %p /var/db/postgres/wal14/%f | | Write-Ahead Log / Archiving | Sets the shell command that will be called to archive a WAL file. | | sighup | string | configuration file | | | | | test ! -f /var/db/postgres/wal14/%f && cp %p /var/db/postgres/wal14/%f | | | f
archive_mode | on | | Write-Ahead Log / Archiving | Allows archiving of WAL files using archive_command. | | postmaster | enum | configuration file | | | {always,on,off} | off | on | | | f
client_encoding | UTF8 | | Client Connection Defaults / Locale and Formatting | Sets the client's character set encoding. | | user | string | default | | | | SQL_ASCII | UTF8 | | | f
data_directory_mode | 0700 | | Preset Options | Shows the mode of the data directory. | The parameter value is a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).) | internal | integer | default | 0 | 511 | | 448 | 448 | | | f
default_text_search_config | pg_catalog.english | | Client Connection Defaults / Locale and Formatting | Sets default text search configuration. | | user | string | configuration file | | | | pg_catalog.simple | pg_catalog.english | | | f
lc_collate | en_GB.UTF-8 | | Preset Options | Shows the collation order locale. | | internal | string | override | | | | C | en_GB.UTF-8 | | | f
lc_messages | C | | Client Connection Defaults / Locale and Formatting | Sets the language in which messages are displayed. | | superuser | string | configuration file | | | | | C | | | f
listen_addresses | 10.0.0.5 | | Connections and Authentication / Connection Settings | Sets the host name or IP address(es) to listen to. | | postmaster | string | configuration file | | | | localhost | 10.0.0.5 | | | f
log_destination | syslog | | Reporting and Logging / Where to Log | Sets the destination for server log output. | Valid values are combinations of "stderr", "syslog", "csvlog", and "eventlog", depending on the platform. | sighup | string | configuration file | | | | stderr | syslog | | | f
log_file_mode | 0600 | | Reporting and Logging / Where to Log | Sets the file permissions for log files. | The parameter value is expected to be a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).) | sighup | integer | default | 0 | 511 | | 384 | 384 | | | f
log_line_prefix | %m [%p] %d: | | Reporting and Logging / What to Log | Controls information prefixed to each log line. | If blank, no prefix is used. | sighup | string | configuration file | | | | %m [%p] | %m [%p] %d: | | | f
log_timezone | UTC | | Reporting and Logging / What to Log | Sets the time zone to use in log messages. | | sighup | string | configuration file | | | | GMT | UTC | | | f
max_connections | 200 | | Connections and Authentication / Connection Settings | Sets the maximum number of concurrent connections. | | postmaster | integer | configuration file | 1 | 262143 | | 100 | 200 | | | f
max_stack_depth | 2048 | kB | Resource Usage / Memory | Sets the maximum stack depth, in kilobytes. | | superuser | integer | environment variable | 100 | 2147483647 | | 100 | 2048 | | | f
server_encoding | UTF8 | | Preset Options | Shows the server (database) character set encoding. | | internal | string | override | | | | SQL_ASCII | UTF8 | | | f
shared_buffers | 65536 | 8kB | Resource Usage / Memory | Sets the number of shared memory buffers used by the server. | | postmaster | integer | configuration file | 16 | 1073741823 | | 1024 | 65536 | | | f
ssl | on | | Connections and Authentication / SSL | Enables SSL connections. | | sighup | bool | configuration file | | | | off | on | | | f
TimeZone | Japan | | Client Connection Defaults / Locale and Formatting | Sets the time zone for displaying and interpreting time stamps. | | user | string | configuration file | | | | GMT | Japan | | | f
unix_socket_permissions | 0777 | | Connections and Authentication / Connection Settings | Sets the access permissions of the Unix-domain socket. | Unix-domain sockets use the usual Unix file system permission set. The parameter value is expected to be a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).) | postmaster | integer | default | 0 | 511 | | 511 | 511 | | | f
update_process_title | off | | Reporting and Logging / Process Title | Updates the process title to show the active SQL command. | Enables updating of the process title every time a new SQL command is received by the server. | superuser | bool | configuration file | | | | on | off | | | f
wal_buffers | 2048 | 8kB | Write-Ahead Log / Settings | Sets the number of disk-page buffers in shared memory for WAL. | | postmaster | integer | override | -1 | 262143 | | -1 | 2048 | | | f
work_mem | 16384 | kB | Resource Usage / Memory | Sets the maximum memory to be used for query workspaces. | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files. | user | integer | configuration file | 64 | 2147483647 | | 4096 | 16384 | | | f
(23 rows)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: iijima.yun@koumakan.jp, pg@bowt.ie, andres@anarazel.de, andrew@tao11.riddles.org.uk, alvherre@alvh.no-ip.org, pgsql-bugs@lists.postgresql.org, thomas.munro@gmail.com
Subject: Re: BUG #17245: Index corruption involving deduplicated entries
In-Reply-To: <3cb78dcf-e344-e7ca-3c52-51b1f0b6621d@koumakan.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox