aboutsummaryrefslogtreecommitdiff
path: root/doc/src
Commit message (Collapse)AuthorAge
...
* Cosmetic fixups for WAL usage work.Amit Kapila2020-04-13
| | | | | | | Reported-by: Justin Pryzby and Euler Taveira Author: Justin Pryzby and Julien Rouhaud Reviewed-by: Amit Kapila Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com
* Doc: introduce new layout for tables of functions and operators.Tom Lane2020-04-12
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | We've long fought with the draconian space limitations of our traditional table layout for describing SQL functions and operators. This commit introduces a new approach, though so far I've only applied it to a few of those tables. The new way makes use of DocBook's support for different layouts in different rows of a table, and allows the descriptions and examples for a function or operator to run to several lines without as much ugliness and wasted space as before. The core layout concept is now Name Signature Description Example Example Result so that a function or operator really has three table rows not one, but we group them to look like one row by having the name column have only one entry for all three rows. (Actually, there could be four or more rows if you wanted to have more than one example, which is another thing that was painful before but works easily now.) This is handled by a "morerows" annotation on the name entry, which isn't perfect (notably, the toolchain is not smart enough to avoid breaking these row groups across PDF pages) but there seems no better solution in DocBook. The name column is normally fairly narrow, allowing plenty of space for the other column(s), and not wasting too much space when one of the other components runs to multiple lines. The varying row layout is managed by defining named "spans" and then tagging entries with a "spanname" of "name", "sig", "desc", "example", or "exresult". This provides a bit of semantic annotation to go with the formatting improvement, which seems like a good thing. (It seems that we have to re-define these spans afresh for each table, which is annoying, but it's not any worse than the duplication involved in the table headers. At least that gives us an opportunity to vary the relative column widths per-table, which is handy since function tables sometimes need much wider name columns than operator tables.) Signature entries should be written in the style <function>fname</function>(<type>typename</type> ...) <returnvalue>typename</returnvalue> The <returnvalue> tag produces a right arrow before the result type name. (I'll document that convention in a user-visible place later.) While this provides significantly more horizontal space than before for examples, it's still true that PDF output is a lot narrower than typical webpage viewing windows, so some examples need to be broken in places where there is no whitespace. I've added &zwsp; markers in suitable places to allow the tables to render warning-free in PDF. I've so far converted only the date/time operator, date/time function, and enum function tables in sections 9.9 and 9.10; these were chosen to provide a reasonable sample of the formatting problems that need to be solved. Assuming that this looks good on the website and doesn't provoke howls of anguish, I'll work on the other similar tables in the near future. There's a moderate amount of new editorial content in this patch along with the raw formatting changes; for instance I had to write text descriptions for operators that lacked them. I failed to resist the temptation to improve some other descriptions and examples, too. Patch by me, with thanks to Alexander Lakhin for assistance with figuring out some formatting issues. Discussion: https://postgr.es/m/9326.1581457869@sss.pgh.pa.us
* Doc: introduce and document "&zwsp;" for allowing optional line breaks.Tom Lane2020-04-12
| | | | | | | | | | | | | | We already had a couple of places using zero-width spaces for formatting hackery, and we're going to need more if we ever want the PDF manuals to look decent. But please let's not write hard-coded Unicode escapes. We can avoid that by using a custom entity, which also provides a place to put a teeny bit of documentation about what it is and how to use it. I'd previously posted a patch using "&break;" for this, but on reflection that would be horrible to grep for. Instead let's use "&zwsp;", based on the name of the Unicode symbol ("zero width space"). Discussion: https://postgr.es/m/9326.1581457869@sss.pgh.pa.us
* Rename pg_validatebackup to pg_verifybackup.Robert Haas2020-04-12
| | | | | | | | | Also, use "verify" rather than "validate" to refer to the process being undertaken here. Per discussion, that is a more appropriate term. Discussion: https://www.postgresql.org/message-id/172c9d9b-1d0a-1b94-1456-376b1e017322@2ndquadrant.com Discussion: http://postgr.es/m/CA+TgmobLgMh6p8FmLbj_rv9Uhd7tPrLnAyLgGd2SoSj=qD-bVg@mail.gmail.com
* Doc: Fix contrib/amcheck tip.Peter Geoghegan2020-04-11
| | | | Fixes an oversight in commit 20fbb711.
* doc: restore intentional typoAndrew Gierth2020-04-11
| | | | | | | | Commit ac8623760 "fixed" a typo in an example of what would happen in the event of a typo. Restore the original typo and add a comment about its intentionality. Backpatch to 12 where the error was introduced. Per report from irc user Nicolás Alvarez.
* Add contrib/amcheck debug message.Peter Geoghegan2020-04-10
| | | | | | | | | | | | | Add a DEBUG1 message indicating that verification of the index structure is underway. Also reduce the severity level of the existing "tree level" debug message to DEBUG1. It should never have been made DEBUG2. Any B-Tree index with more than a couple of levels will generally also have so many pages that the per-page DEBUG2 messages will become completely unmanageable. In passing, add a new "Tip" to the docs that advises users that run into corruption that the debug messages might provide useful additional context.
* Doc: clarify locking requirements for ALTER TABLE ADD FOREIGN KEY.Tom Lane2020-04-10
| | | | | | | | | The docs explained that a SHARE ROW EXCLUSIVE lock is needed on the referenced table, but failed to say the same about the table being altered. Since the page says that ACCESS EXCLUSIVE lock is taken unless otherwise stated, this left readers with the wrong conclusion. Discussion: https://postgr.es/m/834603375.3470346.1586482852542@mail.yahoo.com
* Doc: sync CREATE GROUP syntax synopsis with CREATE ROLE.Tom Lane2020-04-10
| | | | | | | | | | | CREATE GROUP is an exact alias for CREATE ROLE, and CREATE USER is almost an exact alias, as can easily be confirmed by checking the code. So the man page syntax descriptions ought to match up. The last few additions of role options seem to have forgotten to update create_group.sgml, though. Fix that, and add a naggy reminder to create_role.sgml in hopes of not forgetting again. Discussion: https://postgr.es/m/158647836143.655.9853963229391401576@wrigleys.postgresql.org
* Fix collection of typos and grammar mistakes in the treeMichael Paquier2020-04-10
| | | | | | | This fixes some comments and documentation new as of Postgres 13. Author: Justin Pryzby Discussion: https://postgr.es/m/20200408165653.GF2228@telsasoft.com
* Doc: improve documentation about ts_headline() function.Tom Lane2020-04-09
| | | | | Now that I've had my nose in that code, I thought the docs about it left something to be desired.
* Fix typo in pg_validatebackup documentation.Fujii Masao2020-04-09
| | | | | | Author: Fujii Masao Reviewed-by: Robert Haas Discussion: https://postgr.es/m/78f76a3d-1a28-a97d-0394-5c96985dd1c0@oss.nttdata.com
* Add note in pg_stat_statements documentation about planning statistics.Fujii Masao2020-04-09
| | | | | | | | | The added note explains that the numbers of planning and execution in the statement are not always expected to match because their statistics are updated at their respective end phase, and only for successful operations. Author: Pascal Legrand, Julien Rouhaud, tweaked a bit by Fujii Masao Discussion: https://postgr.es/m/1585857868967-0.post@n3.nabble.com
* Allow publishing partition changes via ancestorsPeter Eisentraut2020-04-08
| | | | | | | | | | | | | | | To control whether partition changes are replicated using their own identity and schema or an ancestor's, add a new parameter that can be set per publication named 'publish_via_partition_root'. This allows replicating a partitioned table into a different partition structure on the subscriber. Author: Amit Langote <amitlangote09@gmail.com> Reviewed-by: Rafia Sabih <rafia.pghackers@gmail.com> Reviewed-by: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> Reviewed-by: Petr Jelinek <petr@2ndquadrant.com> Discussion: https://www.postgresql.org/message-id/flat/CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com
* Revert 0f5ca02f53Alexander Korotkov2020-04-08
| | | | | | | | 0f5ca02f53 introduces 3 new keywords. It appears to be too much for relatively small feature. Given now we past feature freeze, it's already late for discussion of the new syntax. So, revert. Discussion: https://postgr.es/m/28209.1586294824%40sss.pgh.pa.us
* Allow partitionwise joins in more cases.Etsuro Fujita2020-04-08
| | | | | | | | | | | | | | | | | | | | | | | | | Previously, the partitionwise join technique only allowed partitionwise join when input partitioned tables had exactly the same partition bounds. This commit extends the technique to some cases when the tables have different partition bounds, by using an advanced partition-matching algorithm introduced by this commit. For both the input partitioned tables, the algorithm checks whether every partition of one input partitioned table only matches one partition of the other input partitioned table at most, and vice versa. In such a case the join between the tables can be broken down into joins between the matching partitions, so the algorithm produces the pairs of the matching partitions, plus the partition bounds for the join relation, to allow partitionwise join for computing the join. Currently, the algorithm works for list-partitioned and range-partitioned tables, but not hash-partitioned tables. See comments in partition_bounds_merge(). Ashutosh Bapat and Etsuro Fujita, most of regression tests by Rajkumar Raghuwanshi, some of the tests by Mark Dilger and Amul Sul, reviewed by Dmitry Dolgov and Amul Sul, with additional review at various points by Ashutosh Bapat, Mark Dilger, Robert Haas, Antonin Houska, Amit Langote, Justin Pryzby, and Tomas Vondra Discussion: https://postgr.es/m/CAFjFpRdjQvaUEV5DJX3TW6pU5eq54NCkadtxHX2JiJG_GvbrCA@mail.gmail.com
* Allow users to limit storage reserved by replication slotsAlvaro Herrera2020-04-07
| | | | | | | | | | | | | | | | Replication slots are useful to retain data that may be needed by a replication system. But experience has shown that allowing them to retain excessive data can lead to the primary failing because of running out of space. This new feature allows the user to configure a maximum amount of space to be reserved using the new option max_slot_wal_keep_size. Slots that overrun that space are invalidated at checkpoint time, enabling the storage to be released. Author: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Jehan-Guillaume de Rorthais <jgdr@dalibo.com> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Discussion: https://postgr.es/m/20170228.122736.123383594.horiguchi.kyotaro@lab.ntt.co.jp
* Allow psql's \g and \gx commands to transiently change \pset options.Tom Lane2020-04-07
| | | | | | | | | | | | | | | | | We invented \gx to allow the "\pset expanded" flag to be forced on for the duration of one command output, but that turns out to not be nearly enough to satisfy the demand for variant output formats. Hence, make it possible to change any pset option(s) for the duration of a single command output, by writing "option=value ..." inside parentheses, for example \g (format=csv csv_fieldsep='\t') somefile \gx can now be understood as a shorthand for including expanded=on inside the parentheses. Patch by me, expanding on a proposal by Pavel Stehule Discussion: https://postgr.es/m/CAFj8pRBx9OnBPRJVtfA5ycUpySge-XootAXAsv_4rrkHxJ8eRg@mail.gmail.com
* Implement waiting for given lsn at transaction startAlexander Korotkov2020-04-07
| | | | | | | | | | | | | | | | | | | | This commit adds following optional clause to BEGIN and START TRANSACTION commands. WAIT FOR LSN lsn [ TIMEOUT timeout ] New clause pospones transaction start till given lsn is applied on standby. This clause allows user be sure, that changes previously made on primary would be visible on standby. New shared memory struct is used to track awaited lsn per backend. Recovery process wakes up backend once required lsn is applied. Author: Ivan Kartyshov, Anna Akenteva Reviewed-by: Craig Ringer, Thomas Munro, Robert Haas, Kyotaro Horiguchi Reviewed-by: Masahiko Sawada, Ants Aasma, Dmitry Ivanov, Simon Riggs Reviewed-by: Amit Kapila, Alexander Korotkov Discussion: https://postgr.es/m/0240c26c-9f84-30ea-fca9-93ab2df5f305%40postgrespro.ru
* Support FETCH FIRST WITH TIESAlvaro Herrera2020-04-07
| | | | | | | | | | | | | | | | | | WITH TIES is an option to the FETCH FIRST N ROWS clause (the SQL standard's spelling of LIMIT), where you additionally get rows that compare equal to the last of those N rows by the columns in the mandatory ORDER BY clause. There was a proposal by Andrew Gierth to implement this functionality in a more powerful way that would yield more features, but the other patch had not been finished at this time, so we decided to use this one for now in the spirit of incremental development. Author: Surafel Temesgen <surafel3000@gmail.com> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Tomas Vondra <tomas.vondra@2ndquadrant.com> Discussion: https://postgr.es/m/CALAY4q9ky7rD_A4vf=FVQvCGngm3LOes-ky0J6euMrg=_Se+ag@mail.gmail.com Discussion: https://postgr.es/m/87o8wvz253.fsf@news-spur.riddles.org.uk
* Adjust bytea get_bit/set_bit to use int8 not int4 for bit numbering.Tom Lane2020-04-07
| | | | | | | | | | | | | | | | | | | | | | | | | | | Since the existing bit number argument can't exceed INT32_MAX, it's not possible for these functions to manipulate bits beyond the first 256MB of a bytea value. Lift that restriction by redeclaring the bit number arguments as int8 (which requires a catversion bump, hence is not back-patchable). The similarly-named functions for bit/varbit don't really have a problem because we restrict those types to at most VARBITMAXLEN bits; hence leave them alone. While here, extend the encode/decode functions in utils/adt/encode.c to allow dealing with values wider than 1GB. This is not a live bug or restriction in current usage, because no input could be more than 1GB, and since none of the encoders can expand a string more than 4X, the result size couldn't overflow uint32. But it might be desirable to support more in future, so make the input length values size_t and the potential-output-length values uint64. Also add some test cases to improve the miserable code coverage of these functions. Movead Li, editorialized some by me; also reviewed by Ashutosh Bapat Discussion: https://postgr.es/m/20200312115135445367128@highgo.ca
* Introduce xid8-based functions to replace txid_XXX.Thomas Munro2020-04-07
| | | | | | | | | | | | | | | | | | | | | The txid_XXX family of fmgr functions exposes 64 bit transaction IDs to users as int8. Now that we have an SQL type xid8 for FullTransactionId, define a new set of functions including pg_current_xact_id() and pg_current_snapshot() based on that. Keep the old functions around too, for now. It's a bit sneaky to use the same C functions for both, but since the binary representation is identical except for the signedness of the type, and since older functions are the ones using the wrong signedness, and since we'll presumably drop the older ones after a reasonable period of time, it seems reasonable to switch to FullTransactionId internally and share the code for both. Reviewed-by: Fujii Masao <masao.fujii@oss.nttdata.com> Reviewed-by: Takao Fujii <btfujiitkp@oss.nttdata.com> Reviewed-by: Yoshikazu Imai <imai.yoshikazu@fujitsu.com> Reviewed-by: Mark Dilger <mark.dilger@enterprisedb.com> Discussion: https://postgr.es/m/20190725000636.666m5mad25wfbrri%40alap3.anarazel.de
* Add SQL type xid8 to expose FullTransactionId to users.Thomas Munro2020-04-07
| | | | | | | | | | | Similar to xid, but 64 bits wide. This new type is suitable for use in various system views and administration functions. Reviewed-by: Fujii Masao <masao.fujii@oss.nttdata.com> Reviewed-by: Takao Fujii <btfujiitkp@oss.nttdata.com> Reviewed-by: Yoshikazu Imai <imai.yoshikazu@fujitsu.com> Reviewed-by: Mark Dilger <mark.dilger@enterprisedb.com> Discussion: https://postgr.es/m/20190725000636.666m5mad25wfbrri%40alap3.anarazel.de
* Implement Incremental SortTomas Vondra2020-04-06
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Incremental Sort is an optimized variant of multikey sort for cases when the input is already sorted by a prefix of the requested sort keys. For example when the relation is already sorted by (key1, key2) and we need to sort it by (key1, key2, key3) we can simply split the input rows into groups having equal values in (key1, key2), and only sort/compare the remaining column key3. This has a number of benefits: - Reduced memory consumption, because only a single group (determined by values in the sorted prefix) needs to be kept in memory. This may also eliminate the need to spill to disk. - Lower startup cost, because Incremental Sort produce results after each prefix group, which is beneficial for plans where startup cost matters (like for example queries with LIMIT clause). We consider both Sort and Incremental Sort, and decide based on costing. The implemented algorithm operates in two different modes: - Fetching a minimum number of tuples without check of equality on the prefix keys, and sorting on all columns when safe. - Fetching all tuples for a single prefix group and then sorting by comparing only the remaining (non-prefix) keys. We always start in the first mode, and employ a heuristic to switch into the second mode if we believe it's beneficial - the goal is to minimize the number of unnecessary comparions while keeping memory consumption below work_mem. This is a very old patch series. The idea was originally proposed by Alexander Korotkov back in 2013, and then revived in 2017. In 2018 the patch was taken over by James Coleman, who wrote and rewrote most of the current code. There were many reviewers/contributors since 2013 - I've done my best to pick the most active ones, and listed them in this commit message. Author: James Coleman, Alexander Korotkov Reviewed-by: Tomas Vondra, Andreas Karlsson, Marti Raudsepp, Peter Geoghegan, Robert Haas, Thomas Munro, Antonin Houska, Andres Freund, Alexander Kuzmenkov Discussion: https://postgr.es/m/CAPpHfdscOX5an71nHd8WSUH6GNOCf=V7wgDaTXdDd9=goN-gfA@mail.gmail.com Discussion: https://postgr.es/m/CAPpHfds1waRZ=NOmueYq0sx1ZSCnt+5QJvizT8ndT2=etZEeAQ@mail.gmail.com
* Add logical replication support to replicate into partitioned tablesPeter Eisentraut2020-04-06
| | | | | | | | | | | | Mainly, this adds support code in logical/worker.c for applying replicated operations whose target is a partitioned table to its relevant partitions. Author: Amit Langote <amitlangote09@gmail.com> Reviewed-by: Rafia Sabih <rafia.pghackers@gmail.com> Reviewed-by: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> Reviewed-by: Petr Jelinek <petr@2ndquadrant.com> Discussion: https://www.postgresql.org/message-id/flat/CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com
* Add the option to report WAL usage in EXPLAIN and auto_explain.Amit Kapila2020-04-06
| | | | | | | | | | | | | | | This commit adds a new option WAL similar to existing option BUFFERS in the EXPLAIN command. This option allows to include information on WAL record generation added by commit df3b181499 in EXPLAIN output. This also allows the WAL usage information to be displayed via the auto_explain module. A new parameter auto_explain.log_wal controls whether WAL usage statistics are printed when an execution plan is logged. This parameter has no effect unless auto_explain.log_analyze is enabled. Author: Julien Rouhaud Reviewed-by: Dilip Kumar and Amit Kapila Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com
* Allow pg_stat_statements to track WAL usage statistics.Amit Kapila2020-04-05
| | | | | | | | | | | | This commit adds three new columns in pg_stat_statements output to display WAL usage statistics added by commit df3b181499. This commit doesn't bump the version of pg_stat_statements as the same is done for this release in commit 17e0328224. Author: Kirill Bychik and Julien Rouhaud Reviewed-by: Julien Rouhaud, Fujii Masao, Dilip Kumar and Amit Kapila Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com
* Skip WAL for new relfilenodes, under wal_level=minimal.Noah Misch2020-04-04
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Until now, only selected bulk operations (e.g. COPY) did this. If a given relfilenode received both a WAL-skipping COPY and a WAL-logged operation (e.g. INSERT), recovery could lose tuples from the COPY. See src/backend/access/transam/README section "Skipping WAL for New RelFileNode" for the new coding rules. Maintainers of table access methods should examine that section. To maintain data durability, just before commit, we choose between an fsync of the relfilenode and copying its contents to WAL. A new GUC, wal_skip_threshold, guides that choice. If this change slows a workload that creates small, permanent relfilenodes under wal_level=minimal, try adjusting wal_skip_threshold. Users setting a timeout on COMMIT may need to adjust that timeout, and log_min_duration_statement analysis will reflect time consumption moving to COMMIT from commands like COPY. Internally, this requires a reliable determination of whether RollbackAndReleaseCurrentSubTransaction() would unlink a relation's current relfilenode. Introduce rd_firstRelfilenodeSubid. Amend the specification of rd_createSubid such that the field is zero when a new rel has an old rd_node. Make relcache.c retain entries for certain dropped relations until end of transaction. Bump XLOG_PAGE_MAGIC, since this introduces XLOG_GIST_ASSIGN_LSN. Future servers accept older WAL, so this bump is discretionary. Kyotaro Horiguchi, reviewed (in earlier, similar versions) by Robert Haas. Heikki Linnakangas and Michael Paquier implemented earlier designs that materially clarified the problem. Reviewed, in earlier designs, by Andrew Dunstan, Andres Freund, Alvaro Herrera, Tom Lane, Fujii Masao, and Simon Riggs. Reported by Martijn van Oosterhout. Discussion: https://postgr.es/m/20150702220524.GA9392@svana.org
* Revert "Improve handling of parameter differences in physical replication"Peter Eisentraut2020-04-04
| | | | | | | | This reverts commit 246f136e76ecd26844840f2b2057e2c87ec9868d. That patch wasn't quite complete enough. Discussion: https://www.postgresql.org/message-id/flat/E1jIpJu-0007Ql-CL%40gemulon.postgresql.org
* Generate backup manifests for base backups, and validate them.Robert Haas2020-04-03
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | A manifest is a JSON document which includes (1) the file name, size, last modification time, and an optional checksum for each file backed up, (2) timelines and LSNs for whatever WAL will need to be replayed to make the backup consistent, and (3) a checksum for the manifest itself. By default, we use CRC-32C when checksumming data files, because we are trying to detect corruption and user error, not foil an adversary. However, pg_basebackup and the server-side BASE_BACKUP command now have options to select a different algorithm, so users wanting a cryptographic hash function can select SHA-224, SHA-256, SHA-384, or SHA-512. Users not wanting file checksums at all can disable them, or disable generating of the backup manifest altogether. Using a cryptographic hash function in place of CRC-32C consumes significantly more CPU cycles, which may slow down backups in some cases. A new tool called pg_validatebackup can validate a backup against the manifest. If no checksums are present, it can still check that the right files exist and that they have the expected sizes. If checksums are present, it can also verify that each file has the expected checksum. Additionally, it calls pg_waldump to verify that the expected WAL files are present and parseable. Only plain format backups can be validated directly, but tar format backups can be validated after extracting them. Robert Haas, with help, ideas, review, and testing from David Steele, Stephen Frost, Andrew Dunstan, Rushabh Lathia, Suraj Kharage, Tushar Ahuja, Rajkumar Raghuwanshi, Mark Dilger, Davinder Singh, Jeevan Chalke, Amit Kapila, Andres Freund, and Noah Misch. Discussion: http://postgr.es/m/CA+TgmoZV8dw1H2bzZ9xkKwdrk8+XYa+DC9H=F7heO2zna5T6qg@mail.gmail.com
* Add a glossary to the documentationAlvaro Herrera2020-04-03
| | | | | | | | | | | More work is still needed, but this is a good start. Co-authored-by: Corey Huinker <corey.huinker@gmail.com> Co-authored-by: Jürgen Purtz <juergen@purtz.de> Co-authored-by: Roger Harkavy <rogerharkavy@gmail.com> Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Justin Pryzby <pryzby@telsasoft.com> Discussion: https://postgr.es/m/CADkLM=eP6HOeqDjn0FdXuGRusQu4oWH_LFsKjjafmhvWD=aSpQ@mail.gmail.com
* Add wait events for recovery conflicts.Fujii Masao2020-04-03
| | | | | | | | | | | | | | | | | This commit introduces new wait events RecoveryConflictSnapshot and RecoveryConflictTablespace. The former is reported while waiting for recovery conflict resolution on a vacuum cleanup. The latter is reported while waiting for recovery conflict resolution on dropping tablespace. Also this commit changes the code so that the wait event Lock is reported while waiting in ResolveRecoveryConflictWithVirtualXIDs() for recovery conflict resolution on a lock. Basically the wait event Lock is reported during that wait, but previously was not reported only when that wait happened in ResolveRecoveryConflictWithVirtualXIDs(). Author: Masahiko Sawada Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/CA+fd4k4mXWTwfQLS3RPwGr4xnfAEs1ysFfgYHvmmoUgv6Zxvmg@mail.gmail.com
* Add support for \aset in pgbenchMichael Paquier2020-04-03
| | | | | | | | | | | | | | This option is similar to \gset, except that it is able to store all results from combined SQL queries into separate variables. If a query returns multiple rows, the last result is stored and if a query returns no rows, nothing is stored. While on it, add a TAP test for \gset to check for a failure when a query returns multiple rows. Author: Fabien Coelho Reviewed-by: Ibrar Ahmed, Michael Paquier Discussion: https://postgr.es/m/alpine.DEB.2.21.1904081914200.2529@lancre
* pg_waldump: Add a --quiet option.Robert Haas2020-04-02
| | | | | | | | | | The primary motivation for this change is that it will be used by the upcoming patch to add backup manifests, but it also seems to have some potential more general use. Andres Freund and Robert Haas Discussion: http://postgr.es/m/20200330020814.nspra4mvby42yoa4@alap3.anarazel.de
* doc: remove unnecessary INNER keywordBruce Momjian2020-04-02
| | | | | | | | | | | | A join that was added in commit 9b2009c4cf that did not use the INNER keyword but the existing query used it. It was cleaner to remove the existing INNER keyword. Reported-by: Peter Eisentraut Discussion: https://postgr.es/m/a1ffbfda-59d2-5732-e5fb-3df8582b6434@2ndquadrant.com Backpatch-through: 9.5
* doc: remove comma, related to commit 92d31085e9Bruce Momjian2020-04-02
| | | | | | | | Reported-by: Peter Eisentraut Discussion: https://postgr.es/m/750b8832-d123-7f9b-931e-43ce8321b2d7@2ndquadrant.com Backpatch-through: 9.5
* Improve user control over truncation of logged bind-parameter values.Tom Lane2020-04-02
| | | | | | | | | | | | | | | | | | | | | | | | | | | This patch replaces the boolean GUC log_parameters_on_error introduced by commit ba79cb5dc with an integer log_parameter_max_length_on_error, adding the ability to specify how many bytes to trim each logged parameter value to. (The previous coding hard-wired that choice at 64 bytes.) In addition, add a new parameter log_parameter_max_length that provides similar control over truncation of query parameters that are logged in response to statement-logging options, as opposed to errors. Previous releases always logged such parameters in full, possibly causing log bloat. For backwards compatibility with prior releases, log_parameter_max_length defaults to -1 (log in full), while log_parameter_max_length_on_error defaults to 0 (no logging). Per discussion, log_parameter_max_length is SUSET since the DBA should control routine logging behavior, but log_parameter_max_length_on_error is USERSET because it also affects errcontext data sent back to the client. Alexey Bashtanov, editorialized a little by me Discussion: https://postgr.es/m/b10493cc-a399-a03a-67c7-068f2791ee50@imap.cc
* Fix typo in SLRU stats documentationTomas Vondra2020-04-02
| | | | | Author: Noriyoshi Shinoda Discussion: https://www.postgresql.org/message-id/flat/20200119143707.gyinppnigokesjok@development
* Add SQL functions for Unicode normalizationPeter Eisentraut2020-04-02
| | | | | | | | | | | | | | | | | This adds SQL expressions NORMALIZE() and IS NORMALIZED to convert and check Unicode normal forms, per SQL standard. To support fast IS NORMALIZED tests, we pull in a new data file DerivedNormalizationProps.txt from Unicode and build a lookup table from that, using techniques similar to ones already used for other Unicode data. make update-unicode will keep it up to date. We only build and use these tables for the NFC and NFKC forms, because they are too big for NFD and NFKD and the improvement is not significant enough there. Reviewed-by: Daniel Verite <daniel@manitou-mail.org> Reviewed-by: Andreas Karlsson <andreas@proxel.se> Discussion: https://www.postgresql.org/message-id/flat/c1909f27-c269-2ed9-12f8-3ab72c8caf7a@2ndquadrant.com
* doc: Update for Unix-domain sockets on WindowsPeter Eisentraut2020-04-02
| | | | | Update the documentation to reflect that Unix-domain sockets are now usable on Windows.
* Allow pg_stat_statements to track planning statistics.Fujii Masao2020-04-02
| | | | | | | | | | | | | | | | | | | | | | | | | | | | This commit makes pg_stat_statements support new GUC pg_stat_statements.track_planning. If this option is enabled, pg_stat_statements tracks the planning statistics of the statements, e.g., the number of times the statement was planned, the total time spent planning the statement, etc. This feature is useful to check the statements that it takes a long time to plan. Previously since pg_stat_statements tracked only the execution statistics, we could not use that for the purpose. The planning and execution statistics are stored at the end of each phase separately. So there are not always one-to-one relationship between them. For example, if the statement is successfully planned but fails in the execution phase, only its planning statistics are stored. This may cause the users to be able to see different pg_stat_statements results from the previous version. To avoid this, pg_stat_statements.track_planning needs to be disabled. This commit bumps the version of pg_stat_statements to 1.8 since it changes the definition of pg_stat_statements function. Author: Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao Reviewed-by: Sergei Kornilov, Tomas Vondra, Yoshikazu Imai, Haribabu Kommi, Tom Lane Discussion: https://postgr.es/m/CAHGQGwFx_=DO-Gu-MfPW3VQ4qC7TfVdH2zHmvZfrGv6fQ3D-Tw@mail.gmail.com Discussion: https://postgr.es/m/CAEepm=0e59Y_6Q_YXYCTHZkqOc6H2pJ54C_Xe=VFu50Aqqp_sA@mail.gmail.com Discussion: https://postgr.es/m/DB6PR0301MB21352F6210E3B11934B0DCC790B00@DB6PR0301MB2135.eurprd03.prod.outlook.com
* Collect statistics about SLRU cachesTomas Vondra2020-04-02
| | | | | | | | | | | | | | | | | | | | | | | | | | | | There's a number of SLRU caches used to access important data like clog, commit timestamps, multixact, asynchronous notifications, etc. Until now we had no easy way to monitor these shared caches, compute hit ratios, number of reads/writes etc. This commit extends the statistics collector to track this information for a predefined list of SLRUs, and also introduces a new system view pg_stat_slru displaying the data. The list of built-in SLRUs is fixed, but additional SLRUs may be defined in extensions. Unfortunately, there's no suitable registry of SLRUs, so this patch simply defines a fixed list of SLRUs with entries for the built-in ones and one entry for all additional SLRUs. Extensions adding their own SLRU are fairly rare, so this seems acceptable. This patch only allows monitoring of SLRUs, not tuning. The SLRU sizes are still fixed (hard-coded in the code) and it's not entirely clear which of the SLRUs might need a GUC to tune size. In a way, allowing us to determine that is one of the goals of this patch. Bump catversion as the patch introduces new functions and system view. Author: Tomas Vondra Reviewed-by: Alvaro Herrera Discussion: https://www.postgresql.org/message-id/flat/20200119143707.gyinppnigokesjok@development
* Improve selectivity estimation for assorted match-style operators.Tom Lane2020-04-01
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Quite a few matching operators such as JSONB's @> used "contsel" and "contjoinsel" as their selectivity estimators. That was a bad idea, because (a) contsel is only a stub, yielding a fixed default estimate, and (b) that default is 0.001, meaning we estimate these operators as five times more selective than equality, which is surely pretty silly. There's a good model for improving this in ltree's ltreeparentsel(): for any "var OP constant" query, we can try applying the operator to all of the column's MCV and histogram values, taking the latter as being a random sample of the non-MCV values. That code is actually 100% generic, except for the question of exactly what default selectivity ought to be plugged in when we don't have stats. Hence, migrate the guts of ltreeparentsel() into the core code, provide wrappers "matchingsel" and "matchingjoinsel" with a more-appropriate default estimate, and use those for the non-geometric operators that formerly used contsel (mostly JSONB containment operators and tsquery matching). Also apply this code to some match-like operators in hstore, ltree, and pg_trgm, including the former users of ltreeparentsel as well as ones that improperly used contsel. Since commit 911e70207 just created new versions of those extensions that we haven't released yet, we can sneak this change into those new versions instead of having to create an additional generation of update scripts. Patch by me, reviewed by Alexey Bashtanov Discussion: https://postgr.es/m/12237.1582833074@sss.pgh.pa.us
* Update SQL features countPeter Eisentraut2020-04-01
| | | | | | | | | | | The previously listed total of 179 does not appear to be correct for SQL:2016 anymore. (Previous SQL versions had slightly different feature sets, so it's plausible that it was once correct.) The currently correct count is the number of rows in the respective tables in appendix F in SQL parts 2 and 11, minus 2 features that are listed twice. Thus the correct count is currently 177. This also matches the number of Core entries the built documentation currently shows, so it's internally consistent.
* Fix typo in contrib/intarray documentationAlexander Korotkov2020-04-01
| | | | | Reported-by: Erik Rijkers Discussion: https://postgr.es/m/82529ecf9bcc58d5b5cf9f3ffb699881%40xs4all.nl
* Correct CREATE INDEX documentation for opclass parametersAlexander Korotkov2020-04-01
| | | | | | Old versions of opclass parameters patch supported ability to specify DEFAULT as the opclass name in CREATE INDEX command. This ability was removed in the final version, but 911e702077 still mentions that in the documentation.
* Documentation corrections for opclass parametersAlexander Korotkov2020-04-01
| | | | | Discussion: https://postgr.es/m/20200331024419.GB14618%40telsasoft.com Author: Justin Pryzby
* Add -c/--restore-target-wal to pg_rewindMichael Paquier2020-04-01
| | | | | | | | | | | | | | | | | | | | | | | | | | pg_rewind needs to copy from the source cluster to the target cluster a set of relation blocks changed from the previous checkpoint where WAL forked up to the end of WAL on the target. Building this list of relation blocks requires a range of WAL segments that may not be present anymore on the target's pg_wal, causing pg_rewind to fail. It is possible to work around this issue by copying manually the WAL segments needed but this may lead to some extra and actually useless work. This commit introduces a new option allowing pg_rewind to use a restore_command while doing the rewind by grabbing the parameter value of restore_command from the target cluster configuration. This allows the rewind operation to be more reliable, so as only the WAL segments needed by the rewind are restored from the archives. In order to be able to do that, a new routine is added to src/common/ to allow frontend tools to restore files from archives using an already-built restore command. This version is more simple than the backend equivalent as there is no need to handle the non-recovery case. Author: Alexey Kondratov Reviewed-by: Andrey Borodin, Andres Freund, Alvaro Herrera, Alexander Korotkov, Michael Paquier Discussion: https://postgr.es/m/a3acff50-5a0d-9a2c-b3b2-ee36168955c1@postgrespro.ru
* doc: remove mention of bitwise operators as solely type-limitedBruce Momjian2020-03-31
| | | | | | | | | | | There are other operators that have limited number data type support, so just remove the sentence. Reported-by: Sergei Agalakov Discussion: https://postgr.es/m/158032651854.19851.16261832706661813796@wrigleys.postgresql.org Backpatch-through: 9.5
* doc: clarify hierarchy of objects: global, db, schema, etc.Bruce Momjian2020-03-31
| | | | | | | | | | | The previous wording was confusing because it wasn't in decreasing order and had to backtrack. Also clarify role/user wording. Reported-by: jbird@nuna.com Discussion: https://postgr.es/m/158057750885.1123.2806779262588618988@wrigleys.postgresql.org Backpatch-through: 9.5