aboutsummaryrefslogtreecommitdiff
path: root/src/bin/psql
Commit message (Collapse)AuthorAge
* Add tab-complete for ALTER DOMAIN ADD [CONSTRAINT]Álvaro Herrera9 days
| | | | | | | | | | | | | | | We can add tab-completion with "CHECK (" and "NOT NULL" after ALTER DOMAIN ADD [CONSTRAINT]. ALTER DOMAIN dom ADD -> CHECK ( ALTER DOMAIN dom ADD -> NOT NULL ALTER DOMAIN dom ADD -> CONSTRAINT ALTER DOMAIN dom ADD CONSTRAINT nm -> CHECK ( ALTER DOMAIN dom ADD CONSTRAINT nm -> NOT NULL Author: jian he <jian.universality@gmail.com> Author: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> Discussion: https://postgr.es/m/CACJufxG_f6LzAT_McC-kKmQWpuWnOYKyNBw8Kv3xzTjPqmeHcA@mail.gmail.com
* Translation updatesPeter Eisentraut2025-05-05
| | | | | Source-Git-URL: https://git.postgresql.org/git/pgtranslation/messages.git Source-Git-Hash: f90ee4803c30491e5c49996b973b8a30de47bfb2
* psql: Fix assertion failures with pipeline modeMichael Paquier2025-04-24
| | | | | | | | | | | | | | | | | | | | | | | | | | A correct cocktail of COPY FROM, SELECT and/or DML queries and \syncpipeline was able to break the logic in charge of discarding results of a pipeline, done in discardAbortedPipelineResults(). Such sequence make the backend generate a FATAL, due to a protocol synchronization loss. This problem comes down to the fact that we did not consider the case of libpq returning a PGRES_FATAL_ERROR when discarding the results of an aborted pipeline. The discarding code is changed so as this result status is handled as a special case, with the caller of discardAbortedPipelineResults() being responsible for consuming the result. A couple of tests are added to cover the problems reported, bringing an interesting gain in coverage as there were no tests in the tree covering the case of protocol synchronization loss. Issue introduced by 41625ab8ea3d. Reported-by: Alexander Kozhemyakin <a.kozhemyakin@postgrespro.ru> Author: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> Co-authored-by: Michael Paquier <michael@paquier.xyz> Discussion: https://postgr.es/m/ebf6ce77-b180-4d6b-8eab-71f641499ddf@postgrespro.ru
* psql: Rework TAP routine psql_fails_like() to define WAL sender contextMichael Paquier2025-04-23
| | | | | | | | | | | | | | | The routine was coded so as a WAL sender was always used, state required only for one failure test related to START_REPLICATION. This test is changed so as a WAL sender is used by passing a replication option to psql_fails_like(), instead of forcing the use of a WAL sender for all the tests. This has come up as useful in the context of a separate bug fix where we are looking at extending tests for some failure scenarios. These tests need to happen in the context of a normal backend, and not a WAL sender where the extended query protocol cannot be used. Discussion: https://postgr.es/m/aAXkJIOildLUA7vQ@paquier.xyz
* psql: Split extended query protocol meta-commands in --help=commandsMichael Paquier2025-04-20
| | | | | | | | | | | | | | | Compared to v17 with only \bind able to do extended query protocol work, v18 has now a total of 11 meta-commands related to the extended query protocol. These were all listed under the "General" section of the --help=commands output and are specialized, bloating the output generated. All these meta-commands are moved into a new section called "Extended Query Protocol", listed at the end of --help=commands. This split has been suggested by Noah Misch. Discussion: https://postgr.es/m/20250415213450.1f.nmisch@google.com
* psql: Improve descriptions of \\flush[request] in --helpMichael Paquier2025-04-20
| | | | | | | | | Noah has reported that the current wording was confusing compared to the description of the underlying libpq routine. The new wording is from me. Reported-by: Noah Misch <noah@leadboat.com> Discussion: https://postgr.es/m/20250415213450.1f.nmisch@google.com
* psql: Fix incorrect status code returned by \getresultsMichael Paquier2025-04-20
| | | | | | | | | | | When an invalid number of results is requested for \getresults, the status code returned by exec_command_getresults() was PSQL_CMD_SKIP_LINE and not PSQL_CMD_ERROR. This led to incorrect behaviors, with ON_ERROR_STOP for example. Reported-by: Noah Misch <noah@leadboat.com> Discussion: https://postgr.es/m/20250415213450.1f.nmisch@google.com
* Fix typos and grammar in the codeMichael Paquier2025-04-19
| | | | | | | | The large majority of these have been introduced by recent commits done in the v18 development cycle. Author: Alexander Lakhin <exclusion@gmail.com> Discussion: https://postgr.es/m/9a7763ab-5252-429d-a943-b28941e0e28b@gmail.com
* Fixup various older misuses of appendPQExpBufferDavid Rowley2025-04-18
| | | | | | | | | | | | | | | | Use appendPQExpBufferStr when there are no parameters and appendPQExpBufferChar when the string length is 1. Unlike 3fae25cbb, which fixed this issue for code that was new to v18, this one fixes up instances which exist in the backbranches. We've historically tried to maintain this standard and if we're going to continue doing that, then we won't be doing that selectively based on when the code was introduced. Now seems like a good time to flush out the existing misuses. Waiting until v19 just prolongs their existence in terms of released versions that the misuses exist in. Author: David Rowley <drowleyml@gmail.com> Discussion: https://postgr.es/m/CAApHDvoARMvPeXTTC0HnpARBHn-WgVstc8XFCyMGOzvgu_1HvQ@mail.gmail.com
* Allow NOT NULL constraints to be added as NOT VALIDÁlvaro Herrera2025-04-07
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This allows them to be added without scanning the table, and validating them afterwards without holding access exclusive lock on the table after any violating rows have been deleted or fixed. Doing ALTER TABLE ... SET NOT NULL for a column that has an invalid not-null constraint validates that constraint. ALTER TABLE .. VALIDATE CONSTRAINT is also supported. There are various checks on whether an invalid constraint is allowed in a child table when the parent table has a valid constraint; this should match what we do for enforced/not enforced constraints. pg_attribute.attnotnull is now only an indicator for whether a not-null constraint exists for the column; whether it's valid or invalid must be queried in pg_constraint. Applications can continue to query pg_attribute.attnotnull as before, but now it's possible that NULL rows are present in the column even when that's set to true. For backend internal purposes, we cache the nullability status in CompactAttribute->attnullability that each tuple descriptor carries (replacing CompactAttribute.attnotnull, which was a mirror of Form_pg_attribute.attnotnull). During the initial tuple descriptor creation, based on the pg_attribute scan, we set this to UNRESTRICTED if pg_attribute.attnotnull is false, or to UNKNOWN if it's true; then we update the latter to VALID or INVALID depending on the pg_constraint scan. This flag is also copied when tupledescs are copied. Comparing tuple descs for equality must also compare the CompactAttribute.attnullability flag and return false in case of a mismatch. pg_dump deals with these constraints by storing the OIDs of invalid not-null constraints in a separate array, and running a query to obtain their properties. The regular table creation SQL omits them entirely. They are then dealt with in the same way as "separate" CHECK constraints, and dumped after the data has been loaded. Because no additional pg_dump infrastructure was required, we don't bump its version number. I decided not to bump catversion either, because the old catalog state works perfectly in the new world. (Trying to run with new catalog state and the old server version would likely run into issues, however.) System catalogs do not support invalid not-null constraints (because commit 14e87ffa5c54 didn't allow them to have pg_constraint rows anyway.) Author: Rushabh Lathia <rushabh.lathia@gmail.com> Author: Jian He <jian.universality@gmail.com> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Tested-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Discussion: https://postgr.es/m/CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com
* psql: Clarify help message for WATCH_INTERVALDaniel Gustafsson2025-04-07
| | | | | | | | | | | The help message for WATCH_INTERVAL was hard to interpret and didn't follow the style of other messages, this updates it to nake it fit in better and be easier to interpret. Author: Daniel Gustafsson <daniel@yesql.se> Reported-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Discussion: https://postgr.es/m/20250326.120732.1167093737847500721.horikyota.ntt@gmail.com
* Extend ALTER DEFAULT PRIVILEGES to define default privileges for large objects.Fujii Masao2025-04-04
| | | | | | | | | | | | | | | | | | | | | | | | Previously, ALTER DEFAULT PRIVILEGES did not support large objects. This meant that to grant privileges to users other than the owner, permissions had to be manually assigned each time a large object was created, which was inconvenient. This commit extends ALTER DEFAULT PRIVILEGES to allow defining default access privileges for large objects. With this change, specified privileges will automatically apply to newly created large objects, making privilege management more efficient. As a side effect, this commit introduces the new keyword OBJECTS since it's used in the syntax of ALTER DEFAULT PRIVILEGES. Original patch by Haruka Takatsuka, with some fixes and tests by Yugo Nagata, and rebased by Laurenz Albe. Author: Takatsuka Haruka <harukat@sraoss.co.jp> Co-authored-by: Yugo Nagata <nagata@sraoss.co.jp> Co-authored-by: Laurenz Albe <laurenz.albe@cybertec.at> Reviewed-by: Masao Fujii <masao.fujii@gmail.com> Discussion: https://postgr.es/m/20240424115242.236b499b2bed5b7a27f7a418@sraoss.co.jp
* Add support for NOT ENFORCED in foreign key constraintsPeter Eisentraut2025-04-02
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This expands the NOT ENFORCED constraint flag, previously only supported for CHECK constraints (commit ca87c415e2f), to foreign key constraints. Normally, when a foreign key constraint is created on a table, action and check triggers are added to maintain data integrity. With this patch, if a constraint is marked as NOT ENFORCED, integrity checks are no longer required, making these triggers unnecessary. Consequently, when creating a NOT ENFORCED foreign key constraint, triggers will not be created, and the constraint will be marked as NOT VALID. Similarly, if an existing foreign key constraint is changed to NOT ENFORCED, the associated triggers will be dropped, and the constraint will also be marked as NOT VALID. Conversely, if a NOT ENFORCED foreign key constraint is changed to ENFORCED, the necessary triggers will be created, and the will be changed to VALID by performing necessary validation. Since not-enforced foreign key constraints have no triggers, the shortcut used for example in psql and pg_dump to skip looking for foreign keys if the relation is known not to have triggers no longer applies. (It already didn't work for partitioned tables.) Author: Amul Sul <sulamul@gmail.com> Reviewed-by: Joel Jacobson <joel@compiler.org> Reviewed-by: Andrew Dunstan <andrew@dunslane.net> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: jian he <jian.universality@gmail.com> Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Reviewed-by: Isaac Morland <isaac.morland@gmail.com> Reviewed-by: Alexandra Wang <alexandra.wang.oss@gmail.com> Tested-by: Triveni N <triveni.n@enterprisedb.com> Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b962c5AcYW9KUt_R_ER5qs3fUGbe4az-SP-vuwPS-w-AGA@mail.gmail.com
* psql: Fix incorrect equality comparisonDaniel Gustafsson2025-03-27
| | | | | | | | Commit 1a759c83278 contained an incorrect equality comparison which was discovered by Coverity. Reported-by: Ranier Vilela <ranier.vf@gmail.com> Discussion: https://postgr.es/m/CAEudQApfAWzLo+oSuy2byXktdr7R8KJC_ACT5VV8fontrL35Pw@mail.gmail.com
* psql: Make test robust against locale variationsDaniel Gustafsson2025-03-26
| | | | | | | | | | | The test committed in 1a759c83278 was prone to failing when using locales with a different decimal separator. Since the test value isn't the important part, change to using an integer instead. Author: Daniel Gustafsson <daniel@yesql.se> Reported-by: Pavel Stehule <pavel.stehule@gmail.com> Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com> Discussion: https://postgr.es/m/CAFj8pRDE=7uW7QP4rg-OQLE2i-puYsUUt+eHE-L6_b_J9w=eWg@mail.gmail.com
* psql: Make default \watch interval configurableDaniel Gustafsson2025-03-25
| | | | | | | | | | | | | | | | | | | The default interval for \watch to wait between executing queries, when executed without a specified interval, was hardcoded to two seconds. This adds the new variable WATCH_INTERVAL which is used to set the default interval, making it configurable for the user. This makes \watch the first command which has a user configurable default setting. Author: Daniel Gustafsson <daniel@yesql.se> Reviewed-by: Heikki Linnakangas <hlinnaka@iki.fi> Reviewed-by: Michael Paquier <michael@paquier.xyz> Reviewed-by: Kirill Reshke <reshkekirill@gmail.com> Reviewed-by: Masahiro Ikeda <ikedamsh@oss.nttdata.com> Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at> Reviewed-by: Greg Sabino Mullane <htamfids@gmail.com> Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Discussion: https://postgr.es/m/B2FD26B4-8F64-4552-A603-5CC3DF1C7103@yesql.se
* psql: use consistent alias for pg_descriptionMagnus Hagander2025-03-24
| | | | | | Author:Jelte Fennema-Nio <github-tech@jeltef.nl> Suggested-By: Michael Banck <mbanck@gmx.net> Discussion: https://www.postgresql.org/message-id/67813520.170a0220.183245.7bf0%40mx.google.com
* psql: show default extension version in \dx outputMagnus Hagander2025-03-24
| | | | | | | | | Reviewed-By: Julien Rouhaud <rjuju123@gmail.com> Reviewed-By: Michael Banck <mbanck@gmx.net> Reviewed-By: Yugo Nagata <nagata@sraoss.co.jp> Reviewed-By: Nathan Bossart <nathandbossart@gmail.com> Reviewed-By: Jelte Fennema-Nio <postgres@jeltef.nl> Discussion: https://postgr.es/m/CABUevEyTMyXC6OvCWkj+rPnHrfi8_Rw_+DD_jzgFFNPqgf+Oig@mail.gmail.com
* psql: Add tab completion for VACUUM and ANALYZE ... ONLY option.Tom Lane2025-03-23
| | | | | | | | | | | | | Improve psql's tab completion for VACUUM and ANALYZE by supporting the ONLY option introduced in 62ddf7ee9. In passing, simplify some of the VACUUM patterns by making use of MatchAnyN. Author: Umar Hayat <postgresql.wizard@gmail.com> Reviewed-by: Vignesh C <vignesh21@gmail.com> Reviewed-by: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com> Discussion: https://postgr.es/m/CAD68Dp3L6yW_nWs+MWBs6s8tKLRzXaQdQgVRm4byZe0L-hRD8g@mail.gmail.com
* psql: Allow queries terminated by semicolons while in pipeline modeMichael Paquier2025-03-19
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Currently, the only way to pipe queries in an ongoing pipeline (in a \startpipeline block) is to leverage the meta-commands able to create extended queries such as \bind, \parse or \bind_named. While this is good enough for testing the backend with pipelines, it has been mentioned that it can also be very useful to allow queries terminated by semicolons to be appended to a pipeline. For example, it would be possible to migrate existing psql scripts to use pipelines by just adding a set of \startpipeline and \endpipeline meta-commands, making such scripts more efficient. Doing such a change is proving to be simple in psql: queries terminated by semicolons can be executed through PQsendQueryParams() without any parameters set when the pipeline mode is active, instead of PQsendQuery(), the default, like pgbench. \watch is still forbidden while in a pipeline, as it expects its results to be processed synchronously. The large portion of this commit consists in providing more test coverage, with mixes of extended queries appended in a pipeline by \bind and friends, and queries terminated by semicolons. This improvement has been suggested by Daniel Vérité. Author: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> Discussion: https://postgr.es/m/d67b9c19-d009-4a50-8020-1a0ea92366a1@manitou-mail.org
* psql: Add \sendpipeline to send query buffers while in a pipelineMichael Paquier2025-03-18
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | In the initial pipeline support for psql added in 41625ab8ea3d, \g was used as the way to push extended query into an ongoing pipeline. \gx was blocked. These two meta-commands have format-related options that can be applied when fetching a query result (expanded, etc.). As the results of a pipeline are fetched asynchronously, not at the moment of the meta-command execution but at the moment of a \getresults or a \endpipeline, authorizing \g while blocking \gx leads to a confusing implementation, making one think that psql should be smart enough to remember the output format options defined from the time when \g or \gx were executed. Doing so would lead to more code complications when retrieving a batch of results. There is an extra argument other than simplicity here: the output format options defined at the point of a \getresults or a \endpipeline execution should be what affect the output format for a batch of results. To avoid any confusion, we have settled to the introduction of a new meta-command called \sendpipeline, replacing \g when within a pipeline. An advantage of this design is that it is possible to add new options specific to pipelines when sending a query buffer, independent of \g and \gx, should it prove to be necessary. Most of the changes of this commit happen in the regression tests, where \g is replaced by \sendpipeline. More tests are added to check that \g is not allowed. Per discussion between the author, Daniel Vérité and me. Author: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> Discussion: https://postgr.es/m/ad4b9f1a-f7fe-4ab8-8546-90754726d0be@manitou-mail.org
* Apply more consistent style for command options in TAP testsMichael Paquier2025-03-17
| | | | | | | | | | | | | | This commit reshapes the grammar of some commands to apply a more consistent style across the board, following rules similar to ce1b0f9da03e: - Elimination of some pointless used-once variables. - Use of long options, to self-document better the options used. - Use of fat commas to link option names and their assigned values, including redirections, so as perltidy can be tricked to put them together. Author: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> Discussion: https://postgr.es/m/87jz8rzf3h.fsf@wibble.ilmari.org
* psql: Fix memory leak with \gx used within a pipelineMichael Paquier2025-03-05
| | | | | | | | | While inside a pipeline, \gx is currently forbidden and will make exec_command_g() exit early. There was a memory leak in this code path, so let's fix it. Author: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> Discussion: https://postgr.es/m/CAO6_XqqFVQjLjZQiL7xdwLpzZEy1ghO_JWvCFPM_OmwF9s7XdA@mail.gmail.com
* psql: Add pipeline status to prompt and some state variablesMichael Paquier2025-02-25
| | | | | | | | | | | | | | | | | | | | | | This commit adds %P to psql prompts, able to report the status of a pipeline depending on PQpipelineStatus(): on, off or abort. The following variables are added to report the state of an ongoing pipeline: - PIPELINE_SYNC_COUNT: reports the number of piped syncs. - PIPELINE_COMMAND_COUNT: reports the number of piped commands, a command being either \bind, \bind_named, \close or \parse. - PIPELINE_RESULT_COUNT: reports the results available to read with \getresults. These variables can be used with \echo or in a prompt, using "%:name:" in PROMPT1, PROMPT2 or PROMPT3. Some basic regression tests are added for these. The suggestion to use variables to show the details about the status counters comes from me. The original patch proposed was less extensible, hardcoding the output in the prompt. Author: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> Discussion: https://postgr.es/m/CAO6_XqroE7JuMEm1sWz55rp9fAYX2JwmcP_3m_v51vnOFdsLiQ@mail.gmail.com
* Change \conninfo to use tabular formatÁlvaro Herrera2025-02-22
| | | | | | | | | | | | | | | | | | | | | | | | | | (Initially the proposal was to keep \conninfo alone and add this feature as \conninfo+, but we decided against keeping the original.) Also display more fields than before, though not as many as were suggested during the discussion. In particular, we don't show 'role' nor 'session authorization', for both which a case can probably be made. These can be added as followup commits, if we agree to it. Some (most?) reviewers actually reviewed rather different versions of the patch and do not necessarily endorse the current one. Co-authored-by: Maiquel Grassi <grassi@hotmail.com.br> Co-authored-by: Hunaid Sohail <hunaidpgml@gmail.com> Reviewed-by: Nathan Bossart <nathandbossart@gmail.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: Sami Imseih <simseih@amazon.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Reviewed-by: Jim Jones <jim.jones@uni-muenster.de> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Pavel Luzanov <p.luzanov@postgrespro.ru> Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com> Reviewed-by: Erik Wienhold <ewie@ewie.name> Discussion: https://postgr.es/m/CP8P284MB24965CB63DAC00FC0EA4A475EC462@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM
* psql: Add support for pipelinesMichael Paquier2025-02-21
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | With \bind, \parse, \bind_named and \close, it is possible to issue queries from psql using the extended protocol. However, it was not possible to send these queries using libpq's pipeline mode. This feature has two advantages: - Testing. Pipeline tests were only possible with pgbench, using TAP tests. It now becomes possible to have more SQL tests that are able to stress the backend with pipelines and extended queries. More tests will be added in a follow-up commit that were discussed on some other threads. Some external projects in the community had to implement their own facility to work around this limitation. - Emulation of custom workloads, with more control over the actions taken by a client with libpq APIs. It is possible to emulate more workload patterns to bottleneck the backend with the extended query protocol. This patch adds six new meta-commands to be able to control pipelines: * \startpipeline starts a new pipeline. All extended queries are queued until the end of the pipeline are reached or a sync request is sent and processed. * \endpipeline ends an existing pipeline. All queued commands are sent to the server and all responses are processed by psql. * \syncpipeline queues a synchronisation request, without flushing the commands to the server, equivalent of PQsendPipelineSync(). * \flush, equivalent of PQflush(). * \flushrequest, equivalent of PQsendFlushRequest() * \getresults reads the server's results for the queries in a pipeline. Unsent data is automatically pushed when \getresults is called. It is possible to control the number of results read in a single meta-command execution with an optional parameter, 0 means that all the results should be read. Author: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> Reviewed-by: Jelte Fennema-Nio <postgres@jeltef.nl> Reviewed-by: Kirill Reshke <reshkekirill@gmail.com> Discussion: https://postgr.es/m/CAO6_XqroE7JuMEm1sWz55rp9fAYX2JwmcP_3m_v51vnOFdsLiQ@mail.gmail.com
* Add braces for if block with large comment in psql's common.cMichael Paquier2025-02-21
| | | | | | | | | | A patch touching this area of the code is under review, and this format makes the readability of the code slightly harder to parse. Extracted from a larger patch by the same author. Author: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> Discussion: https://postgr.es/m/CAO6_XqroE7JuMEm1sWz55rp9fAYX2JwmcP_3m_v51vnOFdsLiQ@mail.gmail.com
* Remove various unnecessary (char *) castsPeter Eisentraut2025-02-20
| | | | | | | | Remove a number of (char *) casts that are unnecessary. Or in some cases, rewrite the code to make the purpose of the cast clearer. Reviewed-by: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> Discussion: https://www.postgresql.org/message-id/flat/fd1fcedb-3492-4fc8-9e3e-74b97f2db6c7%40eisentraut.org
* Add tab completion for ALTER USER/ROLE RESETTomas Vondra2025-02-17
| | | | | | | | | | | Currently tab completion for ALTER USER RESET shows a list of all configuration parameters that may be set on a role, irrespectively of which parameters are actually set. This patch improves tab completion to offer only parameters that are set. Author: Robins Tharakan Reviewed-By: Tomas Vondra Discussion: https://postgr.es/m/CAEP4nAzqiT6VbVC5r3nq5byLTnPzjniVGzEMpYcnAHQyNzEuaw%40mail.gmail.com
* Add tab completion for ALTER DATABASE RESETTomas Vondra2025-02-17
| | | | | | | | | | | Currently tab completion for ALTER DATABASE RESET shows a list of all configuration parameters that may be set on a database, irrespectively of which parameters are actually set. This patch improves tab completion to offer only parameters that are set. Author: Robins Tharakan Reviewed-By: Tomas Vondra Discussion: https://postgr.es/m/CAEP4nAzqiT6VbVC5r3nq5byLTnPzjniVGzEMpYcnAHQyNzEuaw%40mail.gmail.com
* Eagerly scan all-visible pages to amortize aggressive vacuumMelanie Plageman2025-02-11
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Aggressive vacuums must scan every unfrozen tuple in order to advance the relfrozenxid/relminmxid. Because data is often vacuumed before it is old enough to require freezing, relations may build up a large backlog of pages that are set all-visible but not all-frozen in the visibility map. When an aggressive vacuum is triggered, all of these pages must be scanned. These pages have often been evicted from shared buffers and even from the kernel buffer cache. Thus, aggressive vacuums often incur large amounts of extra I/O at the expense of foreground workloads. To amortize the cost of aggressive vacuums, eagerly scan some all-visible but not all-frozen pages during normal vacuums. All-visible pages that are eagerly scanned and set all-frozen in the visibility map are counted as successful eager freezes and those not frozen are counted as failed eager freezes. If too many eager scans fail in a row, eager scanning is temporarily suspended until a later portion of the relation. The number of failures tolerated is configurable globally and per table. To effectively amortize aggressive vacuums, we cap the number of successes as well. Capping eager freeze successes also limits the amount of potentially wasted work if these pages are modified again before the next aggressive vacuum. Once we reach the maximum number of blocks successfully eager frozen, eager scanning is disabled for the remainder of the vacuum of the relation. Original design idea from Robert Haas, with enhancements from Andres Freund, Tomas Vondra, and me Reviewed-by: Robert Haas <robertmhaas@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Robert Treat <rob@xzilla.net> Reviewed-by: Bilal Yavuz <byavuz81@gmail.com> Discussion: https://postgr.es/m/flat/CAAKRu_ZF_KCzZuOrPrOqjGVe8iRVWEAJSpzMgRQs%3D5-v84cXUg%40mail.gmail.com
* Specify the encoding of input to fmtId()Andres Freund2025-02-10
| | | | | | | | | | | | | | | | | | This commit adds fmtIdEnc() and fmtQualifiedIdEnc(), which allow to specify the encoding as an explicit argument. Additionally setFmtEncoding() is provided, which defines the encoding when no explicit encoding is provided, to avoid breaking all code using fmtId(). All users of fmtId()/fmtQualifiedId() are either converted to the explicit version or a call to setFmtEncoding() has been added. This commit does not yet utilize the now well-defined encoding, that will happen in a subsequent commit. Reviewed-by: Noah Misch <noah@leadboat.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Backpatch-through: 13 Security: CVE-2025-1094
* Virtual generated columnsPeter Eisentraut2025-02-07
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This adds a new variant of generated columns that are computed on read (like a view, unlike the existing stored generated columns, which are computed on write, like a materialized view). The syntax for the column definition is ... GENERATED ALWAYS AS (...) VIRTUAL and VIRTUAL is also optional. VIRTUAL is the default rather than STORED to match various other SQL products. (The SQL standard makes no specification about this, but it also doesn't know about VIRTUAL or STORED.) (Also, virtual views are the default, rather than materialized views.) Virtual generated columns are stored in tuples as null values. (A very early version of this patch had the ambition to not store them at all. But so much stuff breaks or gets confused if you have tuples where a column in the middle is completely missing. This is a compromise, and it still saves space over being forced to use stored generated columns. If we ever find a way to improve this, a bit of pg_upgrade cleverness could allow for upgrades to a newer scheme.) The capabilities and restrictions of virtual generated columns are mostly the same as for stored generated columns. In some cases, this patch keeps virtual generated columns more restricted than they might technically need to be, to keep the two kinds consistent. Some of that could maybe be relaxed later after separate careful considerations. Some functionality that is currently not supported, but could possibly be added as incremental features, some easier than others: - index on or using a virtual column - hence also no unique constraints on virtual columns - extended statistics on virtual columns - foreign-key constraints on virtual columns - not-null constraints on virtual columns (check constraints are supported) - ALTER TABLE / DROP EXPRESSION - virtual column cannot have domain type - virtual columns are not supported in logical replication The tests in generated_virtual.sql have been copied over from generated_stored.sql with the keyword replaced. This way we can make sure the behavior is mostly aligned, and the differences can be visible. Some tests for currently not supported features are currently commented out. Reviewed-by: Jian He <jian.universality@gmail.com> Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com> Tested-by: Shlok Kyal <shlok.kyal.oss@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org
* Introduce autovacuum_vacuum_max_threshold.Nathan Bossart2025-02-05
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | One way autovacuum chooses tables to vacuum is by comparing the number of updated or deleted tuples with a value calculated using autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor. The threshold specifies the base value for comparison, and the scale factor specifies the fraction of the table size to add to it. This strategy ensures that smaller tables are vacuumed after fewer updates/deletes than larger tables, which is reasonable in many cases but can result in infrequent vacuums on very large tables. This is undesirable for a couple of reasons, such as very large tables incurring a huge amount of bloat between vacuums. This new parameter provides a way to set a limit on the value calculated with autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor so that very large tables are vacuumed more frequently. By default, it is set to 100,000,000 tuples, but it can be disabled by setting it to -1. It can also be adjusted for individual tables by changing storage parameters. Author: Nathan Bossart <nathandbossart@gmail.com> Co-authored-by: Frédéric Yhuel <frederic.yhuel@dalibo.com> Reviewed-by: Melanie Plageman <melanieplageman@gmail.com> Reviewed-by: Robert Haas <robertmhaas@gmail.com> Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at> Reviewed-by: Michael Banck <mbanck@gmx.net> Reviewed-by: Joe Conway <mail@joeconway.com> Reviewed-by: Sami Imseih <samimseih@gmail.com> Reviewed-by: David Rowley <dgrowleyml@gmail.com> Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com> Reviewed-by: Vinícius Abrahão <vinnix.bsd@gmail.com> Reviewed-by: Robert Treat <rob@xzilla.net> Reviewed-by: Alena Rybakina <a.rybakina@postgrespro.ru> Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com
* Show more-intuitive titles for psql commands \dt, \di, etc.Tom Lane2025-02-05
| | | | | | | | | | | | | | | | | | | | | If exactly one relation type is requested in a command of the \dtisv family, say "tables", "indexes", etc instead of "relations". This should cover the majority of actual uses, without creating a huge number of new translatable strings. The error messages for no matching relations are adjusted as well. In passing, invent "pg_log_error_internal()" to be used for frontend error messages that don't seem to need translation, analogously to errmsg_internal() in the backend. The implementation is a bit cheesy, being just a macro to prevent xgettext from recognizing a trigger keyword. This won't avoid a useless gettext lookup cycle at runtime --- but surely we don't care about an extra microsecond or two in what's supposed to be a can't-happen case. I (tgl) also made "pg_fatal_internal()", though it's not used in this patch. Author: Greg Sabino Mullane <htamfids@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CAKAnmm+7o93fQV-RFkGaN1QnP-0D4d3JTykD+cLueqjDMKdfag@mail.gmail.com
* Avoid breaking SJIS encoding while de-backslashing Windows paths.Tom Lane2025-01-29
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | When running on Windows, canonicalize_path() converts '\' to '/' to prevent confusing the Windows command processor. It was doing that in a non-encoding-aware fashion; but in SJIS there are valid two-byte characters whose second byte matches '\'. So encoding corruption ensues if such a character is used in the path. We can fairly easily fix this if we know which encoding is in use, but a lot of our utilities don't have much of a clue about that. After some discussion we decided we'd settle for fixing this only in psql, and assuming that its value of client_encoding matches what the user is typing. It seems hopeless to get the server to deal with the problematic characters in database path names, so we'll just declare that case to be unsupported. That means nothing need be done in the server, nor in utility programs whose only contact with file path names is for database paths. But psql frequently deals with client-side file paths, so it'd be good if it didn't mess those up. Bug: #18735 Reported-by: Koichi Suzuki <koichi.suzuki@enterprisedb.com> Author: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Koichi Suzuki <koichi.suzuki@enterprisedb.com> Discussion: https://postgr.es/m/18735-4acdb3998bb9f2b1@postgresql.org Backpatch-through: 13
* Rename pubgencols_type to pubgencols in pg_publication.Amit Kapila2025-01-28
| | | | | | | | | | | The column added in commit e65dbc9927, pubgencols_type, was inconsistent with the naming conventions of other columns in the pg_publication catalog. Author: Vignesh C <vignesh21@gmail.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Discussion: https://postgr.es/m/CALDaNm1u-ufVOW-RUsXSooqzkpohxfZYy=z78fbcr_9Pq5hbCg@mail.gmail.com
* Change publication's publish_generated_columns option type to enum.Amit Kapila2025-01-23
| | | | | | | | | | | | | | | The current boolean publish_generated_columns option only supports a binary choice, which is insufficient for future enhancements where generated columns can be of different types (e.g., stored or virtual). The supported values for the publish_generated_columns option are 'none' and 'stored'. Author: Vignesh C <vignesh21@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/d718d219-dd47-4a33-bb97-56e8fc4da994@eisentraut.org Discussion: https://postgr.es/m/B80D17B2-2C8E-4C7D-87F2-E5B4BE3C069E@gmail.com
* Fix \dRp+ output when describing publications with a lower server version.Amit Kapila2025-01-22
| | | | | | | | | | The psql was not careful that the new column "Generated columns" won't be present in the lower version. This was introduced in recent commit 7054186c4e. Author: Vignesh C Reviewed-by: Peter Smith Discussion: https://postgr.es/m/CALDaNm3OcXdY0EzDEKAfaK9gq2B67Mfsgxu93+_249ohyts=0g@mail.gmail.com
* Improve grammar of options for command arrays in TAP testsMichael Paquier2025-01-22
| | | | | | | | | | | | | | | | | | | | | | | This commit rewrites a good chunk of the command arrays in TAP tests with a grammar based on the following rules: - Fat commas are used between option names and their values, making it clear to both humans and perltidy that values and names are bound together. This is particularly useful for the readability of multi-line command arrays, and there are plenty of them in the TAP tests. Most of the test code is updated to use this style. Some commands used parenthesis to show the link, or attached values and options in a single string. These are updated to use fat commas instead. - Option names are switched to use their long names, making them more self-documented. Based on a suggestion by Andrew Dunstan. - Add some trailing commas after the last item in multi-line arrays, which is a common perl style. Not all the places are taken care of, but this covers a very good chunk of them. Author: Dagfinn Ilmari Mannsåker Reviewed-by: Michael Paquier, Peter Smith, Euler Taveira Discussion: https://postgr.es/m/87jzc46d8u.fsf@wibble.ilmari.org
* Drop warning-free support for Flex 2.5.35Peter Eisentraut2025-01-15
| | | | | | | | | | | | | | | | | | This removes all the various workarounds for avoiding compiler warnings with Flex 2.5.35. Several recent patches have added additional warnings that would either need to be fixed along the lines of the existing workarounds, or we decide to no longer care about this, which we do here. Flex 2.5.35 is extremely outdated, and you can't even download it anymore from any of the Flex project sites, so it's nearly impossible to support. After this, using Flex 2.5.35 will still work, but the generated code will produce numerous compiler warnings. Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://www.postgresql.org/message-id/1a204ccd-7ae6-478c-a431-407b5c48ccc6@eisentraut.org
* psql: Add option to use expanded mode to all list commands.Dean Rasheed2025-01-14
| | | | | | | | | | | | | | | | This allows "x" to be appended to any psql list-like meta-command, forcing its output to be displayed in expanded mode. This improves readability in cases where the output is very wide. For example, "\dfx+" (or equivalently "\df+x") will produce a list of functions, with additional details, in expanded mode. This works with all \d* meta-commands, plus \l, \z, and \lo_list, with the one exception that the expanded mode option "x" cannot be appended to "\d" by itself, since "\dx" already means something else. Dean Rasheed, reviewed by Greg Sabino Mullane. Discussion: https://postgr.es/m/CAEZATCVXJk3KsmCncf7PAVbxdDAUDm3QzDgGT7mBYySWikuOYw@mail.gmail.com
* psql: Add leakproof indicator to \df+, \do+, \dAo+, and \dC+ output.Dean Rasheed2025-01-14
| | | | | | | | | | | | This allows users to determine whether particular functions are leakproof, and whether the underlying functions used by operators and casts are leakproof. This is useful to determine whether indexes can be used in queries on security barrier views or tables with row-level security policies. Yugo Nagata, reviewed by Erik Wienhold and Dean Rasheed. Discussion: https://postgr.es/m/20240701220817.483f9b645b95611f8b1f65da%40sranhm.sraoss.co.jp
* flex code modernization: Replace YY_EXTRA_TYPE define with flex optionPeter Eisentraut2025-01-06
| | | | | | | | Replace #define YY_EXTRA_TYPE with %option extra-type. The latter is the way recommended by the flex manual (available since flex 2.5.34). Reviewed-by: Heikki Linnakangas <hlinnaka@iki.fi> Discussion: https://www.postgresql.org/message-id/flat/eb6faeac-2a8a-4b69-9189-c33c520e5b7b@eisentraut.org
* Update copyright for 2025Bruce Momjian2025-01-01
| | | | Backpatch-through: 13
* Partial pgindent of .l and .y filesPeter Eisentraut2024-12-25
| | | | | | | Trying to clean up the code a bit while we're working on these files for the reentrant scanner/pure parser patches. This cleanup only touches the code sections after the second '%%' in each file, via a manually-supervised and locally hacked up pgindent.
* psql: Add more information about service nameMichael Paquier2024-12-18
| | | | | | | | | | | | | | This commit adds support for the following items in psql, able to show a service name, when available: - Variable SERVICE. - Substitution %s in PROMPT{1,2,3}. This relies on 4b99fed7541e, that has made the service name available in PGconn for libpq. Author: Michael Banck Reviewed-by: Greg Sabino Mullane Discussion: https://postgr.es/m/6723c612.050a0220.1567f4.b94a@mx.google.com
* psql: Tab completion for JOIN ... USING column listTomas Vondra2024-12-16
| | | | | | | | | For JOIN ... USING, offer attribute names for the first member of the column list. Author: Andreas Karlsson Reviewed-By: Tomas Vondra Discussion: https://postgr.es/m/3a7e27bc-d6ed-4cb0-9b21-f21143fc1b37@proxel.se
* psql: Tab completion for JOIN ... ON/USINGTomas Vondra2024-12-16
| | | | | | | | | Offer ON/USING clauses for join types that require join conditions (i.e. anything except for NATURAL/CROSS joins). Author: Andreas Karlsson Reviewed-By: Tomas Vondra Discussion: https://postgr.es/m/3a7e27bc-d6ed-4cb0-9b21-f21143fc1b37@proxel.se
* psql: Tab completion for LATERAL joinsTomas Vondra2024-12-16
| | | | | | | | When listing selectable objects after a JOIN, offer also LATERAL. Author: Andreas Karlsson Reviewed-By: Tomas Vondra Discussion: https://postgr.es/m/3a7e27bc-d6ed-4cb0-9b21-f21143fc1b37@proxel.se
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-20 17:37:06 +0000