Jelenlegi hely

PostreSQL.org

Feliratkozás PostreSQL.org hírcsatorna csatornájára
PostgreSQL news
Frissítve: 19 óra 2 másodperc

PostgreSQL Weekly News - May 30, 2021

2021, június 10 - 02:00
PostgreSQL Weekly News - May 30, 2021

pgSCV, a Prometheus-compatible monitoring agent and metrics exporter for PostgreSQL, released.

Pgpool-II 4.2.3, 4.1.7, 4.0.14, 3.7.19 and 3.6.26, a connection pooler and statement replication system for PostgreSQL, released.

sqlite_fdw 1.2.0 released.

Crunchy PostgreSQL Operator 4.7.0, a system for deploying and managing open source PostgreSQL clusters on Kubernetes, released.

pgAdmin4 5.3, a web- and native GUI control center for PostgreSQL, released.

InfluxDB fdw 1.0.0 released https://github.com/pgspider/influxdb_fdw

griddb_fdw 2.0 released.

PostgreSQL Product News PostgreSQL Jobs for May

https://archives.postgresql.org/pgsql-jobs/2021-05/

PostgreSQL in the News

Planet PostgreSQL: https://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

Applied Patches

David Rowley pushed:

Tom Lane pushed:

  • Doc: move some catalogs.sgml entries to the right place. pg_statistic_ext_data.stxdexpr was listed under the wrong catalog, as was pg_stats_ext.exprs. Also there was a bogus entry for pg_statistic_ext_data.stxexprs. Apparently a merge failure in commit a4d75c86b. Guillaume Lelarge and Tom Lane Discussion: https://postgr.es/m/CAECtzeUHw+w64eUFVeV_2FJviAw6oZ0wNLkmU843ZH4hAQfiWg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/713a431c781fbfe1a22fae4991836077f0f4c513

  • Fix use of uninitialized variable in inline_function(). Commit e717a9a18 introduced a code path that bypassed the call of get_expr_result_type, which is not good because we need its rettupdesc result to pass to check_sql_fn_retval. We'd failed to notice right away because the code path in which check_sql_fn_retval uses that argument is fairly hard to reach in this context. It's not impossible though, and in any case inline_function would have no business assuming that check_sql_fn_retval doesn't need that value. To fix, move get_expr_result_type out of the if-block, which in turn requires moving the construction of the dummy FuncExpr out of it. Per report from Ranier Vilela. (I'm bemused by the lack of any compiler complaints...) Discussion: https://postgr.es/m/CAEudQAqBqQpQ3HruWAGU_7WaMJ7tntpk0T8k_dVtNB46DqdBgw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e30e3fdea873e4e9517c490232ea1d3bcef6c643

  • Rethink definition of pg_attribute.attcompression. Redefine '\0' (InvalidCompressionMethod) as meaning "if we need to compress, use the current setting of default_toast_compression". This allows '\0' to be a suitable default choice regardless of datatype, greatly simplifying code paths that initialize tupledescs and the like. It seems like a more user-friendly approach as well, because now the default compression choice doesn't migrate into table definitions, meaning that changing default_toast_compression is usually sufficient to flip an installation's behavior; one needn't tediously issue per-column ALTER SET COMPRESSION commands. Along the way, fix a few minor bugs and documentation issues with the per-column-compression feature. Adopt more robust APIs for SetIndexStorageProperties and GetAttributeCompression. Bump catversion because typical contents of attcompression will now be different. We could get away without doing that, but it seems better to ensure v14 installations all agree on this. (We already forced initdb for beta2, anyway.) Discussion: https://postgr.es/m/626613.1621787110@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/e6241d8e030fbd2746b3ea3f44e728224298f35b

  • Reduce the range of OIDs reserved for genbki.pl. Commit ab596105b increased FirstBootstrapObjectId from 12000 to 13000, but we've had some push-back about that. It's worrisome to reduce the daylight between there and FirstNormalObjectId, because the number of OIDs consumed during initdb for collation objects is hard to predict. We can improve the situation by abandoning the assumption that these OIDs must be globally unique. It should be sufficient for them to be unique per-catalog. (Any code that's unhappy about that is broken anyway, since no more than per-catalog uniqueness can be guaranteed once the OID counter wraps around.) With that change, the largest OID assigned during genbki.pl (starting from a base of 10000) is a bit under

  • This allows reverting FirstBootstrapObjectId to 12000 with reasonable confidence that that will be sufficient for many years to come. We are not, at this time, abandoning the expectation that hand-assigned OIDs (below 10000) are globally unique. Someday that'll likely be necessary, but the need seems years away still. This is late for v14, but it seems worth doing it now so that downstream software doesn't have to deal with the consequences of a change in FirstBootstrapObjectId. In any case, we already bought into forcing an initdb for beta2, so another catversion bump won't hurt. Discussion: https://postgr.es/m/1665197.1622065382@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/a4390abecf0f5152cff864e82b67e5f6c8489698

  • Doc: improve libpq service-file docs, avoid overspecifying pathnames. Clarify libpq.sgml's description of service file locations and semantics. Avoid use of backtick'ed pg_config calls to describe paths; that doesn't work on Windows, and even on Unix it's an idiom that not all readers may be instantly familiar with. Don't overspecify the locations of include files, instead writing only as much as you'd use in #include directives. The previous text in these places was incorrect for some installations, depending on where "postgresql" is in the install path. Our convention for referencing the user's home directory seems to be "~", so change the one place that spelled it "$HOME". install-windows.sgml follows the platform convention of spelling file paths with "\", so change the one place that used "/". Haiying Tang and Tom Lane Discussion: https://postgr.es/m/162149020918.26174.7150424047314144297@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/ba356a397de565c014384aa01a945aab7d50928c

Peter Geoghegan pushed:

Michaël Paquier pushed:

  • Disallow SSL renegotiation. SSL renegotiation is already disabled as of 48d23c72, however this does not prevent the server to comply with a client willing to use renegotiation. In the last couple of years, renegotiation had its set of security issues and flaws (like the recent CVE-2021-3449), and it could be possible to crash the backend with a client attempting renegotiation. This commit takes one extra step by disabling renegotiation in the backend in the same way as SSL compression (f9264d15) or tickets (97d3a0b0). OpenSSL 1.1.0h has added an option named SSL_OP_NO_RENEGOTIATION able to achieve that. In older versions there is an option called SSL3_FLAGS_NO_RENEGOTIATE_CIPHERS that was undocumented, and could be set within the SSL object created when the TLS connection opens, but I have decided not to use it, as it feels trickier to rely on, and it is not official. Note that this option is not usable in OpenSSL < 1.1.0h as the internal contents of the *SSL object are hidden to applications. SSL renegotiation concerns protocols up to TLSv1.2. Per original report from Robert Haas, with a patch based on a suggestion by Andres Freund. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/YKZBXx7RhU74FlTE@paquier.xyz Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/01e6f1a842f406170e5f717305e4a6cf0e84b3ee

  • Fix memory leak when de-toasting compressed values in VACUUM FULL/CLUSTER. VACUUM FULL and CLUSTER can be used to enforce the use of the existing compression method of a toastable column if a value currently stored is compressed with a method that does not match the column's defined method. The code in charge of decompressing and recompressing toast values at rewrite left around the detoasted values, causing an accumulation of memory allocated in TopTransactionContext. When processing large relations, this could cause the system to run out of memory. The detoasted values are not needed once their tuple is rewritten, and this commit ensures that the necessary cleanup happens. Issue introduced by bbe0a81d. The comments of the area are reordered a bit while on it. Reported-by: Andres Freund Analyzed-by: Andres Freund Author: Michael Paquier Reviewed-by: Dilip Kumar Discussion: https://postgr.es/m/20210521211929.pcehg6f23icwstdb@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/fb0f5f0172edf9f63c8f70ea9c1ec043b61c770e

  • Fix typo in heapam.c. Author: Hou Zhijie Discussion: https://postgr.es/m/OS0PR01MB571612191738540B27A8DE5894249@OS0PR01MB5716.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/190fa5a00a8f9ecee8eef2c8e26136b772b94e19

  • doc: Fix description of some GUCs in docs and postgresql.conf.sample. The following parameters have been imprecise, or incorrect, about their description (PGC_POSTMASTER or PGC_SIGHUP): - autovacuum_work_mem (docs, as of 9.6~) - huge_page_size (docs, as of 14~) - max_logical_replication_workers (docs, as of 10~) - max_sync_workers_per_subscription (docs, as of 10~) - min_dynamic_shared_memory (docs, as of 14~) - recovery_init_sync_method (postgresql.conf.sample, as of 14~) - remove_temp_files_after_crash (docs, as of 14~) - restart_after_crash (docs, as of 9.6~) - ssl_min_protocol_version (docs, as of 12~) - ssl_max_protocol_version (docs, as of 12~) This commit adjusts the description of all these parameters to be more consistent with the practice used for the others. Revewed-by: Justin Pryzby Discussion: https://postgr.es/m/YK2ltuLpe+FbRXzA@paquier.xyz Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/2941138e60fc711bd221b3264807f36cc079dfbb

  • Fix MSVC scripts when building with GSSAPI/Kerberos. The deliverables of upstream Kerberos on Windows are installed with paths that do not match our MSVC scripts. First, the include folder was named "inc/" in our scripts, but the upstream MSIs use "include/". Second, the build would fail with 64-bit environments as the libraries are named differently. This commit adjusts the MSVC scripts to be compatible with the latest installations of upstream, and I have checked that the compilation was able to work with the 32-bit and 64-bit installations. Special thanks to Kondo Yuta for the help in investigating the situation in hamerkop, which had an incorrect configuration for the GSS compilation. Reported-by: Brian Ye Discussion: https://postgr.es/m/162128202219.27274.12616756784952017465@wrigleys.postgresql.org Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/025110663448a8c877f4b591495f2e5d187d8936

Amit Kapila pushed:

Peter Eisentraut pushed:

Álvaro Herrera pushed:

Andrew Dunstan pushed:

Thomas Munro pushed:

Pending Patches

Fabien COELHO and Aleksander Alekseev traded patches to replace the rand48 pseudo-random number generator with something more appropriate to the 64-bit era.

Greg Nancarrow and Pavel Borisov traded patches to fix a parallel worker failed assertion and coredump.

Vigneshwaran C sent in another revision of a patch to add schema level support for PUBLICATIONs.

Hou Zhijie and Amit Langote traded patches to skip partition tuple routing in the case where the partition key is constant.

Dilip Kumar, Tsutomu Yamada, and Kyotaro HORIGUCHI traded patches to fix a race condition in recovery.

Hou Zhijie sent in another revision of a patch to make it possible to parallelize INSERT ... SELECT.

Tom Lane sent in two revisions of a patch to fix an infelicity between CALL and procedures with output-only arguments.

Justin Pryzby sent in another revision of a patch to make WAL compression methods pluggable and default to lz4.

Andy Fan sent in a patch to use planner_rt_fetch instead of rt_fetch when root->simple_rte_array.

Ajin Cherian sent in five more revisions of a patch to skip empty transactions for logical replication.

Mark Dilger sent in another revision of a patch to delegate superuser tasks to new security roles.

Hou Zhijie, Bharath Rupireddy, and Tomáš Vondra traded patches to make sure postgres_fdw batching does not use too many parameters.

Bharath Rupireddy sent in a patch to provide TDE nonce size as an initdb option, add TDE nonce bytes to page pd_special structure, and adjust tests to account for configurable TDE nonce size.

Bharath Rupireddy sent in another revision of a patch to disambiguate error messages that use "non-negative."

Antonin Houska sent in a patch to narrow the cases where a concurrent UPDATE restarts heap_lock_tuple(), reducing unneeded calls to same.

Greg Sabino Mullane sent in a patch to speed up pg_checksums in cases where checksum already set by avoiding writing same when it's already set to the value expected.

Michaël Paquier sent in a patch intended to fix a bug that manifested as incorrect snapshots while promoting hot standby node when 2PC is used.

Bharath Rupireddy sent in two more revisions of a patch to check for duplicate options and error out if they're found in CREATE COLLATION.

Andrey V. Lepikhov sent in a patch to teach the optimizer to consider a partition-wise join of a non-partitioned table with each partition of a partitioned table.

Andrey V. Lepikhov sent in another revision of a patch to remove unneeded self-joins via a new equivalence class.

Peter Eisentraut sent in a patch to fix RADIUS error reporting in hba file parsing.

Vigneshwaran C and Bharath Rupireddy traded patches to improve publication error messages.

Tom Lane sent in another revision of a patch to replace pg_depend PIN entries with a fixed range check.

Kyotaro HORIGUCHI sent in two revisions of a patch to change confusing 'bracket' use to clearer wording, and add test cases for trailing garbage of (multi)range types.

Bharath Rupireddy and Hou Zhijie traded patches to make it possible to use parallel inserts in CREATE TABLE AS.

Dilip Kumar sent in a patch to fix a memory leak in decoding speculative insert with toast.

Peter Geoghegan sent in a patch to Generalize VACUUM's INDEX_CLEANUP option in a way that lets users disable the index vacuum bypassing optimization added by commit 5100010e, as well as any similar optimizations that may be added in the future.

Paul Guo sent in another revision of a patch to fsync only the affected files/directories and use copy_file_range() for file copying in pg_rewind.

Takamichi Osumi sent in another revision of a patch to log the plans of long-running queries.

Daniel Gustafsson sent in another revision of a patch to support NSS as a libpq TLS backend.

Etsuro Fujita sent in a patch to fix rescans in asynchronous appends in the PostgreSQL FDW.

Vigneshwaran C sent in a patch to add alias types regpublication and regsubscription.

Fabien COELHO sent in a patch to reduce some duplication in psql's echo code.

Laurenz Albe sent in another revision of a patch to expand the docs on PostgreSQL extension coding and background worker development a little so that key topics like allocation, interrupt handling, exit callbacks, transaction callbacks, PG_TRY()/PG_CATCH(), resource owners, transaction and snapshot state, etc are at least briefly mentioned with a few pointers to where to learn more.

Tom Lane sent in a patch to reduce memory consumption for pending inval messages.

Andreas Karlsson sent in a patch to shrink GISTSTATE.

Yura Sokolov sent in a patch to clear empty space in a page.

Tomáš Vondra sent in a patch to revert part of the COPY FREEZE improvements, namely tweaking heap_multi_insert, and removing most of 39b66a91bd (except for the heap_xlog_multi_insert bit).

Thomas Munro sent in two revisions of a patch to support direct I/O on macOS.

Kategóriák: Informatika

pg_statement_rollback v1.2 has been released

2021, június 7 - 02:00

Zurich, Switzerland - June 7th, 2021

Server side rollback at statement level for PostgreSQL

pg_statement_rollback is a PostgreSQL extension to add server side transaction with rollback at statement level like in Oracle or DB2.

Release v1.2 of pg_statement_rollback was released past week. This is a maintenance release to fix portability issues on different PostgreSQL version as well as taking care of parallelism.

See ChangeLog for a complete list of changes.

Links & Credits

pg_statement_rollback is an open project from LzLabs (https://www.lzlabs.com/). Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools.

Links :

Kategóriák: Informatika

PostgreSQL Weekly News - June 6, 2021

2021, június 7 - 02:00
PostgreSQL Weekly News - June 6, 2021

PG Day Russia will be in on line on July 8-9, 2021. The CfP is open at Submit your talk by June 7, 2021!

Person of the week

PostgreSQL Product News

PL/R 8.4.2, an embedding of the R language in PostgreSQL, released.

WAL-G 1.0 a backup management system for PostgreSQL and other databases written in Go, released.

pgtt 2.4, an extension to implement global temporary tables, released.

Database .NET v32.6, a multi-database management tool, now with support for PostgreSQL, released.

pg_partman 4.5.1, a management system for partitioned tables, released.

hypopg 1.3.0, an extension which implements hypothetical indexes, released.

PostgreSQL Jobs for June

Jobs

PostgreSQL in the News

Planet PostgreSQL: https://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

Applied Patches

Michaël Paquier pushed:

Noah Misch pushed:

Tom Lane pushed:

  • Fix mis-planning of repeated application of a projection. create_projection_plan contains a hidden assumption (here made explicit by an Assert) that a projection-capable Path will yield a projection-capable Plan. Unfortunately, that assumption is violated only a few lines away, by create_projection_plan itself. This means that two stacked ProjectionPaths can yield an outcome where we try to jam the upper path's tlist into a non-projection-capable child node, resulting in an invalid plan. There isn't any good reason to have stacked ProjectionPaths; indeed the whole concept is faulty, since the set of Vars/Aggs/etc needed by the upper one wouldn't necessarily be available in the output of the lower one, nor could the lower one create such values if they weren't available from its input. Hence, we can fix this by adjusting create_projection_path to strip any top-level ProjectionPath from the subpath it's given. (This amounts to saying "oh, we changed our minds about what we need to project here".) The test case added here only fails in v13 and HEAD; before that, we don't attempt to shove the Sort into the parallel part of the plan, for reasons that aren't entirely clear to me. However, all the directly-related code looks generally the same as far back as v11, where the hazard was introduced (by d7c19e62a). So I've got no faith that the same type of bug doesn't exist in v11 and v12, given the right test case. Hence, back-patch the code changes, but not the irrelevant test case, into those branches. Per report from Bas Poot. Discussion: https://postgr.es/m/534fca83789c4a378c7de379e9067d4f@politie.nl https://git.postgresql.org/pg/commitdiff/6ee41a301e70fc8e4ad383bad22d695f66ccb0ac

  • Reject SELECT ... GROUP BY GROUPING SETS (()) FOR UPDATE. This case should be disallowed, just as FOR UPDATE with a plain GROUP BY is disallowed; FOR UPDATE only makes sense when each row of the query result can be identified with a single table row. However, we missed teaching CheckSelectLocking() to check groupingSets as well as groupClause, so that it would allow degenerate grouping sets. That resulted in a bad plan and a null-pointer dereference in the executor. Looking around for other instances of the same bug, the only one I found was in examine_simple_variable(). That'd just lead to silly estimates, but it should be fixed too. Per private report from Yaoguang Chen. Back-patch to all supported branches. https://git.postgresql.org/pg/commitdiff/1103033aedc10295eb689a4b7158f21ef4c14a11

  • Teach tab-complete.c about recently-added CREATE TYPE options. Commit c7aba7c14 missed adding SUBSCRIPT here, and commit 6df7a9698 missed adding MULTIRANGE_TYPE_NAME. Haiying Tang and Tom Lane Discussion: https://postgr.es/m/OS0PR01MB6113F9EDA46FA53BAA5445BDFB3D9@OS0PR01MB6113.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/9e3b3ff2664dd0b349d2a6d6f047128cb3489cf2

  • Update plannodes.h's comments about PlanRowMark. The reference here to different physical column numbers in inherited UPDATE/DELETE plans is obsolete as of 86dc90056; remove it. Also rework the text about inheritance cases to make it clearer. https://git.postgresql.org/pg/commitdiff/79c50ca57828e9f8375766b36cce1e2960eebf87

  • Fix planner's row-mark code for inheritance from a foreign table. Commit 428b260f8 broke planning of cases where row marks are needed (SELECT FOR UPDATE, etc) and one of the query's tables is a foreign table that has regular table(s) as inheritance children. We got the reverse case right, but apparently were thinking that foreign tables couldn't be inheritance parents. Not so; so we need to be able to add a CTID junk column while adding a new child, not only a wholerow junk column. Back-patch to v12 where the faulty code came in. Amit Langote Discussion: https://postgr.es/m/CA+HiwqEmo3FV1LAQ4TVyS2h1WM=kMkZUmbNuZSCnfHvMcUcPeA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/889592344c48d3965567f331b4ea89dfe6447bce

  • Re-allow custom GUC names that have more than two components. Commit 3db826bd5 disallowed this case, but it turns out that some people are depending on it. Since the core grammar has allowed it since 3dc37cd8d, it seems like this code should fall in line. Per bug #17045 from Robert Sosinski. Discussion: https://postgr.es/m/17045-6a4a9f0d1513f72b@postgresql.org https://git.postgresql.org/pg/commitdiff/2955c2be79b35fa369c83fa3b5f44661cb88afa9

  • Fix incorrect permissions on pg_subscription. The documented intent is for all columns except subconninfo to be publicly readable. However, this has been overlooked twice. subsynccommit has never been readable since it was introduced, nor has the oid column (which is important for joining). Given the lack of previous complaints, it's not clear that it's worth doing anything about this in the back branches. But there's still time to fix it inexpensively for v14. Per report from Israel Barth (via Euler Taveira). Patch by Euler Taveira, possibly-vain comment updates by me. Discussion: https://postgr.es/m/b8f7c17c-0041-46b6-acfe-2d1f5a985ab4@www.fastmail.com https://git.postgresql.org/pg/commitdiff/3590680b85a8e51ef8df550e5a10dedd0d2dfd88

  • Doc: fix bogus intarray index example. The siglen parameter is provided by gist__intbig_ops not gist__int_ops. Simon Norris Discussion: https://postgr.es/m/11BF2AA9-17AE-432A-AFE1-584FB9FB079D@hillcrestgeo.ca https://git.postgresql.org/pg/commitdiff/e4539386decae1c435767a69507cc7cbb11ac3ff

  • Fix postgres_fdw failure with whole-row Vars of type RECORD. Commit 86dc90056 expects that FDWs can cope with whole-row Vars for their tables, even if the Vars are marked with vartype RECORDOID. Previously, whole-row Vars generated by the planner had vartype equal to the relevant table's rowtype OID. (The point behind this change is to enable sharing of resjunk columns across inheritance child tables.) It turns out that postgres_fdw fails to cope with this, though through bad fortune none of its test cases exposed that. Things mostly work, but when we try to read back a value of such a Var, the expected rowtype is not available to record_in(). Fortunately, it's not difficult to hack up the tupdesc that controls this process to substitute the foreign table's rowtype for RECORDOID. Thus we can solve the runtime problem while still sharing the resjunk column with other tables. Per report from Alexander Pyhalov. Discussion: https://postgr.es/m/7817fb9ebd6661cdf9b67dec6e129a78@postgrespro.ru https://git.postgresql.org/pg/commitdiff/f61db909dfb94f3411f8719916601a11a905b95e

Peter Eisentraut pushed:

Thomas Munro pushed:

Amit Kapila pushed:

Fujii Masao pushed:

Tomáš Vondra pushed:

David Rowley pushed:

Andrew Dunstan pushed:

  • In PostgresNode.pm, don't pass SQL to psql on the command line. The Msys shell mangles certain patterns in its command line, so avoid handing arbitrary SQL to psql on the command line and instead use IPC::Run's redirection facility for stdin. This pattern is already mostly whats used, but query_poll_until() was not doing the right thing. Problem discovered on the buildfarm when a new TAP test failed on msys. https://git.postgresql.org/pg/commitdiff/11e9caff82bc7326e2bc9782937cb03875050cc4
Pending Patches

Emre Hasegeli sent in a patch to handle boolean comparison predicates in the PostgreSQL FDW, as not all were recognized.

Pavel Stěhule sent in another revision of a patch to get the PL/ppgsql debug API to returns the text value of variable contents.

Dilip Kumar sent in four more revisions of a patch to plug a memory leak when decoding speculative insert with TOAST.

Hou Zhijie sent in three more revisions of a patch to make it possible for INSERT ... SELECT to execute in parallel.

Peter Smith and Ajin Cherian traded patches to support prepared transactions in built-in logical replication.

Dilip Kumar sent in three revisions of a patch to Extract unchanged replica identity key if it is stored externally. If replica identity is set to key and the key is not modified we don't log key separately because it should be logged along with the updated tuple. But if the key is stored externally we must have to detoast and log it separately.

Justin Pryzby sent in another revision of a patch to implement CREATE TABLE (LIKE .. INCLUDING ACCESS METHOD).

Zhihong Yu sent in a patch to return the correct error code from pgtls_init().

Peter Smith and Takamichi Osumi traded patches to document the deadlock risk that AELs on the catalog can cause in logical decoding in synchronous mode.

Etsuro Fujita sent in another revision of a patch to fix rescan of async appends.

Bharath Rupireddy sent in two more revisions of a patch to refactor parse_subscription_options to make them easier to extend and make mutually exclusive options easier to detect via a bitmap.

Hou Zhijie and Amit Langote traded patches to skip partition tuple routing with constant partition key.

Kyotaro HORIGUCHI sent in two revisions of a patch to fix a bug where it was possible to get a duplicate history file.

Aleksander Alekseev sent in another revision of a patch to add a way to specify column projection lists to the table access method API.

Joe Wildish sent in another revision of a patch to Allow queries in WHEN expression of FOR EACH STATEMENT triggers.

Greg Sabino Mullane sent in two more revisions of a patch to speed up pg_checksums in cases where the checksum is already set.

Andrew Dunstan sent in three revisions of a patch to clarify and update the short version installation documentation.

Matthias van de Meent sent in a patch to report phase progress for the sort phase in parallel btree construction.

Jim Mlodgenski sent in a patch to implement CREATE MODULE.

John Naylor sent in two more revisions of a patch to speed up verifying UTF-8.

Thomas Munro sent in a patch to remove more obsolete comments about semaphores.

Thomas Munro sent in another revision of a patch to make ProcSendSignal() more efficient by changing from referring to target processes by pid to using pgprocno, which makes it possible to avoid scanning ProcArray and keeping track of the startup process.

Greg Nancarrow sent in another revision of a patch to implement event triggers for client connect.

Amit Langote sent in another revision of a patch to add an assertion to ensure that child row marks don't come into existence prematurely.

Nitin Jadhav sent in another revision of a patch to implement multi-column list partitioning.

David Christensen sent in another revision of a patch to expand the units that pg_size_pretty(numeric) knows about up through YB, and expand the supported units in pg_size_bytes to cover all units.

Zhihong Yu sent in two revisions of a patch to document the reason that it's not necessary to check the return value from unlink in write_relcache_init_file.

Robert Haas and Dilip Kumar traded patches to fix a corner case failure of a new standby to follow the new primary.

Bharath Rupireddy sent in a patch to use (void) when the return value of fsm_set_and_search is ignored.

David Christensen sent in a patch to implement DELETE...CASCADE.

Kyotaro HORIGUCHI sent in a patch to make an error message in pg_waldump clearer and more explicit as to what went wrong and where.

Andrey V. Lepikhov sent in another revision of a patch to use COPY for bulk writes on tables with foreign partitions.

Zeng Wenjing sent in another revision of a patch to implement global temporary tables.

Tomáš Vondra sent in a patch to add PQ_QUERY_PARAM_MAX_LIMIT, create a copy of a descriptor for batching, and initialize slots only once for batching.

Nitin Jadhav sent in a patch to create a progress indicator for the startup process.

Aleksander Alekseev sent in a patch to add the ZSON extension to contrib.

Andrew Dunstan sent in a patch to ensure that pg_upgrade echos Windows commands.

Maxim Orlov sent in a patch intended to fix a bug that manifested as a core dump in parallel scan with SubTransGetTopmostTransaction assert.

Ajin Cherian sent in a patch to remove the two-phase option from the CreateReplicationSlotCmd struct, and add an option to set two-phase in CREATE_REPLICATION_SLOT.

Tom Lane sent in another revision of a patch to fix an infelicity between CALL and procedures with output-only arguments.

Melanie Plageman sent in another revision of a patch to add a system view tracking shared buffer actions.

Jehan-Guillaume de Rorthais sent in another revision of a patch to add a pg_stat_waitaccum view, and change the measuring method of wait event time from INSTR_TIME to rdtsc.

Michaël Paquier sent in a patch to prevent VACUUM from re-compressing.

Álvaro Herrera sent in a patch to make toast_get_compression_id inline.

Álvaro Herrera sent in another revision of a patch to reduce the overhead of TOAST recompression on table rewrite.

David Rowley sent in a patch to fix a few typos in brin_minmax_multi.c.

Vigneshwaran C sent in another revision of a patch to add schema level support for publication, and add tests for same.

Ranier Vilela sent in another revision of a patch to reduce the overhead of TOAST recompression on table rewrite.

Vigneshwaran C sent in another revision of a patch to identify missing publications from publisher during CREATE/ALTER SUBSCRIPTION.

Julien Rouhaud sent in another revision of a patch to add a parser_hook hook, add a sqlol parser based on same, add a new MODE_SINGLE_QUERY to the core parser and use it in pg_parse_query, and teach sqlol to use the new MODE_SINGLE_QUERY parser mode.

Julien Rouhaud sent in a patch to fix SQL-standard body empty statements handling.

Michaël Paquier sent in a patch intended to fix a misplaced superuser check in pg_log_backend_memory_contexts().

Tomáš Vondra sent in another revision of a patch to fix a bug in psql's \dX (extended statistics) which was failing to check for object visibility.

Kategóriák: Informatika

Global Temporary Table v2.4 released

2021, június 4 - 02:00

Grenoble - June 4th, 2021

PostgreSQL Global Temporary Tables

pgtt is a PostgreSQL extension to create, manage and use DB2 or Oracle-style Global Temporary Tables. Once created the use is just like with the other RDBMS.

pgtt v2.4 has been released, this version allow use of the extension by non superuser and especially the creation and maintenance of GTT. It also allow compatibility with PostgreSQL v14.

Complete list of changes is available here

Links & Credits

pgtt is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly to gilles@darold.net.

Links :

About pgtt

Docs & Download at https://github.com/darold/pgtt/

Kategóriák: Informatika

HypoPG 1.3 is out!

2021, június 4 - 02:00

HypoPG is a PostgreSQL extension adding support for hypothetical indexes.

Version 1.3 has been released, and brings the following changes:

New features:

  • Add support for hypothetical hash indexes (pg10+)

Links

For more thorough information about this version or the extension, please consult the official documentation.

Kategóriák: Informatika

Database .NET v32.6 released

2021, június 4 - 02:00

Database .NET v32 is an innovative, powerful and intuitive multiple database management tool. With it you can easily and intuitive manage your PostgreSQL databases. (Free for non-commercial and a single executable file without installation)

Major New features from version 31.6 to 32.6:

  • Added support for PostgreSQL 14 beta 1
  • Added support for Asynchronous Processing
  • Added the public schema from the default search path for PostgreSQL
  • Added support for the hstore and ghstore types of PostgreSQL
  • Improved user experience and user interface
  • Improved support of high-DPI displays
  • Improved Transaction Mode
  • Improved Data Export and Import
  • Improved Data Browser and Editor
  • Redesign Vertical Tabs Mode
  • Performance improvements
  • Updated to Npgsql.dll 4.1.9
  • ...and more

The new version is immediately available for download.

Kategóriák: Informatika

Chinese Translation of Community Code of Conduct Posted

2021, június 4 - 02:00

The PostgreSQL Community Code of Conduct Committee is pleased to announce that the Chinese translation of the Code of Conduct Policy updated August 18, 2020 has been posted:

https://www.postgresql.org/about/policies/coc/zh/

The English version of the Policy is at: https://www.postgresql.org/about/policies/coc/

The Committee thanks Wensheng Zhang (张文升) and Yandong Yao (姚延栋) for their work in preparing the translation.

If you are interested in contributing a translation, the process overview is here.

Kategóriák: Informatika

Submit your talk to PG Day&#39;21 Russia by 7 June!

2021, június 4 - 02:00

Dear friends,

We would like to announce that PG Day’21 Russia will take place online on 8-9 July!

Submit your talk by 7 June!

We look forward to receiving your submissions on the following topics:

  • Interesting use-cases of operating PostgreSQL in high volume systems.
  • PostgreSQL internals.
  • Migration to PostgreSQL from Oracle, MySQL, DB2, MSSQL.
  • PostgreSQL and database training.
  • Using PostgreSQL in enterprise systems.
  • Tuning and performance optimization of PostgreSQL.
  • Extending PostgreSQL's functionality.
  • Scaling PostgreSQL.

Register! Participation is free, but we encourage you to register on our Slack channel to receive updates regarding the programme and links to the conference stream.

We missed you greatly and look forward to welcoming you at this online PostgreSQL community event on 8-9 July.

Team PG Day'21 Russia

(Ilya, Roman, Maryana, Valeria)

Kategóriák: Informatika

pg_partman 4.5.1 released

2021, június 4 - 02:00

PostgreSQL Partition Manager (pg_partman) v4.5.1 has been released. This release contains a critical security fix and other important bug fixes, so it is highly recommended to update this extension as soon as possible.

  • CVE-2021-33204 - Fixed security issue that could allow arbitrary code execution using SECURITY DEFINER functions. https://nvd.nist.gov/vuln/detail/CVE-2021-33204
  • Allow relation options set on the template table to be inherited on the child table. As of PG13 and earlier, relation options set on the parent are not being set on the child tables.
  • Fixed several bugs in sub-partitioning when using a mixture of epoch and regular integer partitioning in the same partition set

Additional details can be found in the CHANGELOG.

Links:

Website - https://github.com/pgpartman/pg_partman

Installation - https://github.com/pgpartman/pg_partman#installation

Documentation - https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md

Release Notes - https://github.com/pgpartman/pg_partman/blob/master/CHANGELOG.txt

Crunchy Data - https://www.crunchydata.com

Crunchy Data is proud to support the development and maintenance of pg_partman.

Kategóriák: Informatika

WAL-G 1.0 released

2021, június 1 - 02:00

Hello world!

This time we decided to bump the number to 1.0 to switch to a common versioning pattern. WAL-G 1.0 is compatible with its versions v0.2.0+. Also, WAL-G 1.0 can restore WAL-E backups and WALs. Good news for non-PostgreSQL databases: WAL-G is now production-ready for MS SQL, MySQL databases, meanwhile the API can be changed in future releases. MongoDB and Redis support is in the beta stage.

Some interesting features/fixes:

#780 PostgreSQL remote backups #905 Improve compatibility with PostgreSQL 13, introduced WALG_PREFETCH_DIR option #824 New option was added to wal-verify, now it requires an argument timeline|integrity #903 It's now not allowed to create non-permanent delta backups from permanent backups #842 Fix compatibility with S3 implementations that do not support ListObjectsV2 #950 It's now again possible to tell WAL-G to rename .ready files to .done directly #898, #962 WAL prefetch is now slightly faster #858 Catchup-list command to list delta backups taken specifically for catchup #847 Support for Yandex Cloud KMS #817, #833 Multipart retries for GCS storage #873 Support for AzureUSGovernmentCloud, AzureChinaCloud, AzureGermanCloud #899 Add metadata for WAL files #913, #917 New option to delete single backup for PostgreSQL Add Redis backups. Now we have Redis support with backup-fetch, backup-push, backup-list, and delete commands. Add MySQL catchup mode: allow replay binlogs while uploading new ones Add SQLServer single database restore with renaming Huge amount of bug fixes, tests, and improvements for Redis/MongoDB/SQLServer/etc

The new release is available here.

Thanks to all WAL-G users, developers, and the PostgreSQL community in general.

Best regards, WAL-G team.

Kategóriák: Informatika

PL/R 8.4.2 released

2021, június 1 - 02:00

The PL/R team is excited to announce the release of version 8.4.2

  • Added support for transactions in procedures.
  • Added support for pg custom type (tuple) arguments in window functions

  • Windows builds with R 4.10.0

We would like to thank everyone who contributed!

Dave

Kategóriák: Informatika

PGCon Unconference - Friday May 28

2021, május 27 - 02:00

Hello,

The PGCon 2021 Unconference is this upcoming Friday, May 28th starting at 10 AM.

Times are based in Ottawa, Canada which is UTC-4 - the schedule is at:

https://www.pgcon.org/events/pgcon_2021/schedule/

This schedule is also available in iCalendar format as well as an XML format suitable for scheduling apps. See the above URL.

This URL helps with additional information about the Unconference:

https://wiki.postgresql.org/wiki/PgConUnconferenceFAQ

To propose a topic, you need to be involved with the first session of the day - the 'Pitches and Scheduling' session.

You can participate over IRC or Zoom.

We'll be on the irc.libera.chat IRC network - the website will have an embedded IRC client if you prefer to use that. Channel details will be available on the day via the website.

At present, the links to the streams go to the home page. They will ready in time for the Unconference.

The original post contained the wrong month and the wrong UTC offset. Sorry about that.

Thank you.

Kategóriák: Informatika

GridDB fdw 2.0.0 released

2021, május 26 - 02:00

We have just released version 2.0.0 of the Foreign Data Wrapper for GridDB.

This release can work with PostgreSQL 9.6, 10, 11, 12 and 13.
This release improves following item :

  • Support UPDATE
  • Support LIMIT OFFSET clause pushdown
  • Support ORDER BY pushdown
  • Support function pushdown in WHERE clause
  • Support aggregation pushdown
  • Support column_name options
  • Refactored tests

The FDW supports following features :

  • SELECT, INSERT, UPDATE and DELETE of foreign tables
  • Function is push-downed in WHERE clause
  • LIMIT and OFFSET are push-downed when having LIMIT clause only or both LIMIT and OFFSET.
  • ORDER BY is push-downed.
  • Aggregation is push-downed.

This is developed by Toshiba Software Engineering & Technology Center.
Please see the repository for details.

Source repository : https://github.com/pgspider/griddb_fdw

GridDB is KVS and time series database.
Please see the repository for details.

https://github.com/griddb/griddb_nosql

Kategóriák: Informatika

Announcing the release of pgSCV 0.5.0

2021, május 26 - 02:00

pgSCV is a Prometheus-compatible monitoring agent and metrics exporter for PostgreSQL environment. The goal of the project is to provide a single tool (exporter) for collecting metrics from PostgreSQL and related services.

Features:

  • Wide range of builtin metrics about system, PostgreSQL and Pgbouncer services.
  • Pull mode, by default all metrics are exposed through /metrics HTTP endpoint.
  • Push mode, metrics can be pushed to a HTTP service.
  • Local services auto-discovery.
  • Remote services support.
  • User-defined metrics.

More information on project page and wiki:

Kategóriák: Informatika

SQLite fdw 2.0.0 released

2021, május 26 - 02:00

We have just released version 2.0.0 of the Foreign Data Wrapper for SQLite.

This release can work with PostgreSQL 9.6, 10, 11, 12 and 13.
This release improves following item :

  • Support JOIN pushdown (LEFT,RIGHT,INNER)
  • Support direct modification (UPDATE/DELETE)
  • Support pushdown nest functions
  • Support pushdown scalar operator ANY/ALL (ARRAY)
  • Support pushdown ON CONFLICT DO NOTHING
  • Refactored tests

Bug fixings :

  • Don't push down lower/upper function
  • Fix processing for DATE data type
  • Do not prepare SQL statement during EXPLAIN

The FDW supports following features :

  • SELECT, INSERT, UPDATE and DELETE of foreign tables
  • WHERE clauses is push-downed
  • Aggregate is push-downed
  • ORDER BY is push-downed
  • JOIN is push-downed (LEFT,RIGHT,INNER)
  • nest functions is push-downed
  • LIMIT and OFFSET are push-downed (*when all tables queried are fdw)
  • scalar operator ANY/ALL (ARRAY) are push-downed
  • ON CONFLICT DO NOTHING is push-downed
  • Transactions

This is developed by Toshiba Software Engineering & Technology Center.
Please see the repository for details.

Source repository : https://github.com/pgspider/sqlite_fdw

Best Regards,
Mototaka Kanematsu

Kategóriák: Informatika

InfluxDB fdw 1.0.0 released

2021, május 26 - 02:00

We have just released version 1.0.0 of the Foreign Data Wrapper for InfluxDB.

This release can work with PostgreSQL 9.6, 10, 11, 12 and 13.
This release supports following features :

  • Support INSERT/DELETE features
  • Support add more functions to pushdown
  • Support LIMIT OFFSET clause pushdown
  • Support pushdown scalar operator ANY/ALL (ARRAY)
  • Refactored tests

The FDW supports following features :

  • InfluxDB FDW supports pushed down some aggregate functions: count, stddev, sum, max, min.
  • InfluxDB FDW supports INSERT, DELETE statements.
  • time and time_text column can used for INSERT, DELETE statements.
  • time column can express timestamp with precision down to microseconds.
  • time_text column can express timestamp with precision down to nanoseconds.
  • WHERE clauses including timestamp, interval and now() functions are pushed down.
  • LIMIT...OFFSET clauses are pushed down when there is LIMIT clause only or both LIMIT and OFFSET.

Limitations :

  • UPDATE is not supported.
  • WITH CHECK OPTION constraints is not supported. Following limitations originate from data model and query language of InfluxDB.
  • Result sets have different number of rows depending on specified target list. For example, SELECT field1 FROM t1 and SELECT field2 FROM t1 returns different number of rows if the number of points with field1 and field2 are different in InfluxDB database.
  • Timestamp precision may be lost because timestamp resolution of PostgreSQL is microseconds while that of InfluxDB is nanoseconds.
  • Conditions like WHERE time + interval '1 day' < now() do not work. Please use WHERE time < now() - interval '1 day'.

This is developed by Toshiba Software Engineering & Technology Center.
Please see the repository for details.

Source repository : https://github.com/pgspider/influxdb_fdw

Kategóriák: Informatika

PGO, the Crunchy Postgres Operator 4.7 Released: PVC Resize, GCS, Advanced Cloning, and more!

2021, május 25 - 02:00

Crunchy Data is pleased to announce the release of version 4.7 of PGO, the Postgres Operator from Crunchy Data, which automates and simplifies deploying and managing open source Postgres clusters on Kubernetes and other Kubernetes-enabled Platforms. Instructions for getting started with the Postgres Operator can be found here:

https://access.crunchydata.com/documentation/postgres-operator/latest/quickstart/

or get started by running:

kubectl create ns pgo; kubectl apply -f https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.7.0/installers/kubectl/postgres-operator.yml

PGO 4.7, designed for high availability PostgreSQL workloads on Kubernetes, adds support for more "Day 2" functions, such as PVC resize operations with minimal downtime, and enhancements to the monitoring system with a focus on query performance metrics, backups, and recovery. PGO 4.7 expands its support for GitOps methods and includes additional extensions, including, pg_partman, pg_cron, and TimescaleDB.

Major features of the PGO 4.7 release include:

and more.

For a full list of features, including detailed descriptions of these changes, please see the release notes.

Links

Crunchy Data is proud to support the development and maintenance of PGO, the Postgres Operator from Crunchy Data.

Kategóriák: Informatika

pgAdmin 4 v5.3 Released

2021, május 24 - 02:00

The pgAdmin Development Team is pleased to announce pgAdmin 4 version 5.3. This release of pgAdmin 4 includes 25 bug fixes and new features. For more details please see the release notes.

pgAdmin is the leading Open Source graphical management tool for PostgreSQL. For more information, please see the website.

Notable changes in this release include:

Features:
  • Connect PostgreSQL servers using Kerberos authentication

    This feature provides the user to connect to the database servers using Kerberos authentication. Make sure 'kerberos' must be set as AUTHENTICATION_SOURCES in the config file. Added ‘Kerberos authentication?’ switch control in the server dialog if it is set to True then the user can connect to the database server using Kerberos authentication.

  • Auto width of columns by content size in the data output window

    This feature provides the user to set the auto width option of the columns by content size. Added the ‘Resize by data?’ option in the preferences. If set to True then data columns will auto-size to the maximum width of the data in the column as loaded in the first batch. If False, the column will be sized to the widest of the data type or column name.

Bugs/Housekeeping:
  • Fixed an issue where data is displayed in the wrong order when executing the query repeatedly.
  • Fixed an issue where the wrong SQL is showing for the child partition tables.
  • Fixed an issue where CSV download quotes the numeric columns.
  • Ensure that pgAdmin should not allow opening external files that are dragged into it.
  • Fixed an issue where schema diff does not create DROP DEFAULT statement for columns.
  • Fixed an issue where the filter 'Include/Exclude By Selection' not working for null values.
  • Added support for the creation of Nested Table and Varying Array Type for Advanced Server.
  • Fixed ModuleNotFoundError when running setup.py from outside of the root.
  • Fixed an issue where the current debug line is not visible in the 'Dark' theme.
  • Fixed an issue where duplicate columns are visible in the browser tree if dependent on multiple sequences.
  • Ensure that pgAdmin4 shuts down completely on the Quit command on macOS.

Builds for Windows and macOS are available now, along with a Python Wheel, Docker Container, RPM, DEB Package, and source code tarball from the tarball area.

Kategóriák: Informatika

Pgpool-II 4.2.3, 4.1.7, 4.0.14, 3.7.19 and 3.6.26 released.

2021, május 24 - 02:00
What is Pgpool-II?

Pgpool-II is a tool to add useful features to PostgreSQL, including:

  • connection pooling
  • load balancing
  • automatic fail over and more.

For more information, please see the website.

Minor releases

Pgpool Global Development Group is pleased to announce the availability of following versions of Pgpool-II:

  • 4.2.3
  • 4.1.7
  • 4.0.14
  • 3.7.19
  • 3.6.26

Please take a look at release notes.

You can download the source code and RPMs.

Kategóriák: Informatika

PostgreSQL Weekly News - May 23, 2021

2021, május 24 - 02:00
PostgreSQL Weekly News - May 23, 2021

PostgreSQL 14 Beta 1 released. Test!

The official IRC channels have moved from Freenode to Libera. Details here.

PostgreSQL Product News

DBD::Pg 3.15.0, a Perl driver for PostgreSQL, released.

pg_back 2.0.1, is a tool that can dump PostgreSQL databases to files, released.

PostgreSQL Jobs for May

https://archives.postgresql.org/pgsql-jobs/2021-05/

PostgreSQL in the News

Planet PostgreSQL: https://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

Applied Patches

Bruce Momjian pushed:

Etsuro Fujita pushed:

Magnus Hagander pushed:

Peter Eisentraut pushed:

Tom Lane pushed:

  • Stamp 14beta1. https://git.postgresql.org/pg/commitdiff/e4f9737fac77a5cb03a84d1f4038d300ffd28afd

  • Avoid creating testtablespace directories where not wanted. Recently we refactored things so that pg_regress makes the "testtablespace" subdirectory used by the core regression tests, instead of doing that in the makefiles. That had the undesirable side effect of making such a subdirectory in every directory that has "input" or "output" test files. Since these subdirectories remain empty, git doesn't complain about them, but nonetheless they're clutter. To fix, invent an explicit --make-testtablespace-dir switch, so that pg_regress only makes the subdirectory when explicitly told to. Discussion: https://postgr.es/m/2854388.1621284789@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/413c1ef98e0c9c708c4a9a13a838a55b65b16a80

  • Clean up cpluspluscheck violation. "typename" is a C++ keyword, so pg_upgrade.h fails to compile in C++. Fortunately, there seems no likely reason for somebody to need to do that. Nonetheless, it's project policy that all .h files should pass cpluspluscheck, so rename the argument to fix that. Oversight in 57c081de0; back-patch as that was. (The policy requiring pg_upgrade.h to pass cpluspluscheck only goes back to v12, but it seems best to keep this code looking the same in all branches.) https://git.postgresql.org/pg/commitdiff/6d59a218c38adf5b993200a804713df4982a0c75

  • Avoid detoasting failure after COMMIT inside a plpgsql FOR loop. exec_for_query() normally tries to prefetch a few rows at a time from the query being iterated over, so as to reduce executor entry/exit overhead. Unfortunately this is unsafe if we have COMMIT or ROLLBACK within the loop, because there might be TOAST references in the data that we prefetched but haven't yet examined. Immediately after the COMMIT/ROLLBACK, we have no snapshots in the session, meaning that VACUUM is at liberty to remove recently-deleted TOAST rows. This was originally reported as a case triggering the "no known snapshots" error in init_toast_snapshot(), but even if you miss hitting that, you can get "missing toast chunk", as illustrated by the added isolation test case. To fix, just disable prefetching in non-atomic contexts. Maybe there will be performance complaints prompting us to work harder later, but it's not clear at the moment that this really costs much, and I doubt we'd want to back-patch any complicated fix. In passing, adjust that error message in init_toast_snapshot() to be a little clearer about the likely cause of the problem. Patch by me, based on earlier investigation by Konstantin Knizhnik. Per bug #15990 from Andreas Wicht. Back-patch to v11 where intra-procedure COMMIT was added. Discussion: https://postgr.es/m/15990-eee2ac466b11293d@postgresql.org https://git.postgresql.org/pg/commitdiff/f21fadafaf0fb5ea4c9622d915972651273d62ce

  • Restore the portal-level snapshot after procedure COMMIT/ROLLBACK. COMMIT/ROLLBACK necessarily destroys all snapshots within the session. The original implementation of intra-procedure transactions just cavalierly did that, ignoring the fact that this left us executing in a rather different environment than normal. In particular, it turns out that handling of toasted datums depends rather critically on there being an outer ActiveSnapshot: otherwise, when SPI or the core executor pop whatever snapshot they used and return, it's unsafe to dereference any toasted datums that may appear in the query result. It's possible to demonstrate "no known snapshots" and "missing chunk number N for toast value" errors as a result of this oversight. Historically this outer snapshot has been held by the Portal code, and that seems like a good plan to preserve. So add infrastructure to pquery.c to allow re-establishing the Portal-owned snapshot if it's not there anymore, and add enough bookkeeping support that we can tell whether it is or not. We can't, however, just re-establish the Portal snapshot as part of COMMIT/ROLLBACK. As in normal transaction start, acquiring the first snapshot should wait until after SET and LOCK commands. Hence, teach spi.c about doing this at the right time. (Note that this patch doesn't fix the problem for any PLs that try to run intra-procedure transactions without using SPI to execute SQL commands.) This makes SPI's no_snapshots parameter rather a misnomer, so in HEAD, rename that to allow_nonatomic. replication/logical/worker.c also needs some fixes, because it wasn't careful to hold a snapshot open around AFTER trigger execution. That code doesn't use a Portal, which I suspect someday we're gonna have to fix. But for now, just rearrange the order of operations. This includes back-patching the recent addition of finish_estate() to centralize the cleanup logic there. This also back-patches commit 2ecfeda3e into v13, to improve the test coverage for worker.c (it was that test that exposed that worker.c's snapshot management is wrong). Per bug #15990 from Andreas Wicht. Back-patch to v11 where intra-procedure COMMIT was added. Discussion: https://postgr.es/m/15990-eee2ac466b11293d@postgresql.org https://git.postgresql.org/pg/commitdiff/84f5c2908dad81e8622b0406beea580e40bb03ac

  • Fix usage of "tableoid" in GENERATED expressions. We consider this supported (though I've got my doubts that it's a good idea, because tableoid is not immutable). However, several code paths failed to fill the field in soon enough, causing such a GENERATED expression to see zero or the wrong value. This occurred when ALTER TABLE adds a new GENERATED column to a table with existing rows, and during regular INSERT or UPDATE on a foreign table with GENERATED columns. Noted during investigation of a report from Vitaly Ustinov. Back-patch to v12 where GENERATED came in. Discussion: https://postgr.es/m/CAM_DEiWR2DPT6U4xb-Ehigozzd3n3G37ZB1+867zbsEVtYoJww@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2b0ee126bbf01cbfd657bd53c94f9284ba903ca2

  • Disallow whole-row variables in GENERATED expressions. This was previously allowed, but I think that was just an oversight. It's a clear violation of the rule that a generated column cannot depend on itself or other generated columns. Moreover, because the code was relying on the assumption that no such cross-references exist, it was pretty easy to crash ALTER TABLE and perhaps other places. Even if you managed not to crash, you got quite unstable, implementation-dependent results. Per report from Vitaly Ustinov. Back-patch to v12 where GENERATED came in. Discussion: https://postgr.es/m/CAM_DEiWR2DPT6U4xb-Ehigozzd3n3G37ZB1+867zbsEVtYoJww@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4b10074453d182b5fc11a5667bab2ef8532ff3a6

  • Remove plpgsql's special-case code paths for SET/RESET. In the wake of 84f5c2908, it's no longer necessary for plpgsql to handle SET/RESET specially. The point of that was just to avoid taking a new transaction snapshot prematurely, which the regular code path through _SPI_execute_plan() now does just fine (in fact better, since it now does the right thing for LOCK too). Hence, rip out a few lines of code, going back to the old way of treating SET/RESET as a generic SQL command. This essentially reverts all but the test cases from b981275b6. Discussion: https://postgr.es/m/15990-eee2ac466b11293d@postgresql.org https://git.postgresql.org/pg/commitdiff/30168be8f75b95183abccf48f0da7a64a0cfbd9f

  • Fix access to no-longer-open relcache entry in logical-rep worker. If we redirected a replicated tuple operation into a partition child table, and then tried to fire AFTER triggers for that event, the relation cache entry for the child table was already closed. This has no visible ill effects as long as the entry is still there and still valid, but an unluckily-timed cache flush could result in a crash or other misbehavior. To fix, postpone the ExecCleanupTupleRouting call (which is what closes the child table) until after we've fired triggers. This requires a bit of refactoring so that the cleanup function can have access to the necessary state. In HEAD, I took the opportunity to simplify some of worker.c's function APIs based on use of the new ApplyExecutionData struct. However, it doesn't seem safe/practical to back-patch that aspect, at least not without a lot of analysis of possible interactions with a04daa97a. In passing, add an Assert to afterTriggerInvokeEvents to catch such cases. This seems worthwhile because we've grown a number of fairly unstructured ways of calling AfterTriggerEndQuery. Back-patch to v13, where worker.c grew the ability to deal with partitioned target tables. Discussion: https://postgr.es/m/3382681.1621381328@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/b39630fd41f25b414d0ea9b30804f4105f2a0aff

  • Be more verbose when the postmaster unexpectedly quits. Emit a LOG message when the postmaster stops because of a failure in the startup process. There already is a similar message if we exit for that reason during PM_STARTUP phase, so it seems inconsistent that there was none if the startup process fails later on. Also emit a LOG message when the postmaster stops after a crash because restart_after_crash is disabled. This seems potentially helpful in case DBAs (or developers) forget that that's set. Also, it was the only remaining place where the postmaster would do an abnormal exit without any comment as to why. In passing, remove an unreachable call of ExitPostmaster(0). Discussion: https://postgr.es/m/194914.1621641288@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/bc2a389efb3b52d259cefd53c16cfa00742116f2

  • Re-order pg_attribute columns to eliminate some padding space. Now that attcompression is just a char, there's a lot of wasted padding space after it. Move it into the group of char-wide columns to save a net of 4 bytes per pg_attribute entry. While we're at it, swap the order of attstorage and attalign to make for a more logical grouping of these columns. Also re-order actions in related code to match the new field ordering. This patch also fixes one outright bug: equalTupleDescs() failed to compare attcompression. That could, for example, cause relcache reload to fail to adopt a new value following a change. Michael Paquier and Tom Lane, per a gripe from Andres Freund. Discussion: https://postgr.es/m/20210517204803.iyk5wwvwgtjcmc5w@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/f5024d8d7b04de2f5f4742ab433cc38160354861

David Rowley pushed:

  • Fix typo and outdated information in README.barrier. README.barrier didn't seem to get the memo when atomics were added. Fix that. Author: Tatsuo Ishii, David Rowley Discussion: https://postgr.es/m/20210516.211133.2159010194908437625.t-ishii%40sraoss.co.jp Backpatch-through: 9.6, oldest supported release https://git.postgresql.org/pg/commitdiff/2ded19fa3a4dafbae80245710fa371d5163bdad4

  • Fix planner's use of Result Cache with unique joins. When the planner considered using a Result Cache node to cache results from the inner side of a Nested Loop Join, it failed to consider that the inner path's parameterization may not be the entire join condition. If the join was marked as inner_unique then we may accidentally put the cache in singlerow mode. This meant that entries would be marked as complete after caching the first row. That was wrong as if only part of the join condition was parameterized then the uniqueness of the unique join was not guaranteed at the Result Cache's level. The uniqueness is only guaranteed after Nested Loop applies the join filter. If subsequent rows were found, this would lead to: ERROR: cache entry already complete This could have been fixed by only putting the cache in singlerow mode if the entire join condition was parameterized. However, Nested Loop will only read its inner side so far as the first matching row when the join is unique, so that might mean we never get an opportunity to mark cache entries as complete. Since non-complete cache entries are useless for subsequent lookups, we just don't bother considering a Result Cache path in this case. In passing, remove the XXX comment that claimed the above ERROR might be better suited to be an Assert. After there being an actual case which triggered it, it seems better to keep it an ERROR. Reported-by: David Christensen Discussion: https://postgr.es/m/CAOxo6X+dy-V58iEPFgst8ahPKEU+38NZzUuc+a7wDBZd4TrHMQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/9e215378d7fbb7d4615be917917c52f246cc6c61

Michaël Paquier pushed:

Fujii Masao pushed:

  • Fix issues in pg_stat_wal. 1) Previously there were both pgstat_send_wal() and pgstat_report_wal() in order to send WAL activity to the stats collector. With the former being used by wal writer, the latter by most other processes. They were a bit redundant and so this commit merges them into pgstat_send_wal() to simplify the code. 2) Previously WAL global statistics counters were calculated and then compared with zero-filled buffer in order to determine whether any WAL activity has happened since the last submission. These calculation and comparison were not cheap. This was regularly exercised even in read-only workloads. This commit fixes the issue by making some WAL activity counters directly be checked to determine if there's WAL activity stats to send. 3) Previously pgstat_report_stat() did not check if there's WAL activity stats to send as part of the "Don't expend a clock check if nothing to do" check at the top. It's probably rare to have pending WAL stats without also passing one of the other conditions, but for safely this commit changes pgstat_report_stats() so that it checks also some WAL activity counters at the top. This commit also adds the comments about the design of WAL stats. Reported-by: Andres Freund Author: Masahiro Ikeda Reviewed-by: Kyotaro Horiguchi, Atsushi Torikoshi, Andres Freund, Fujii Masao Discussion: https://postgr.es/m/20210324232224.vrfiij2rxxwqqjjb@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/d8735b8b4651f5ed50afc472e236a8e6120f07f2

  • Make standby promotion reset the recovery pause state to 'not paused'. If a promotion is triggered while recovery is paused, the paused state ends and promotion continues. But previously in that case pg_get_wal_replay_pause_state() returned 'paused' wrongly while a promotion was ongoing. This commit changes a standby promotion so that it marks the recovery pause state as 'not paused' when it's triggered, to fix the issue. Author: Fujii Masao Reviewed-by: Dilip Kumar, Kyotaro Horiguchi Discussion: https://postgr.es/m/f706876c-4894-0ba5-6f4d-79803eeea21b@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/167bd4804995afd654bd97ca9486acbece24377e

Amit Kapila pushed:

  • Fix 020_messages.pl test. We were not waiting for a publisher to catch up with the subscriber after creating a subscription. Now, it can happen that apply worker starts replication even after we have disabled the subscription in the test. This will make the test expect that there is no active slot whereas there exists one. Fix this symptom by allowing the publisher to wait for catching up with the subscription. It is not a good idea to ensure if the slot is still active by checking for walsender existence as we release the slot after we clean up the walsender related memory. Fix that by checking the slot status in pg_replication_slots. Also, it is better to avoid repeated enabling/disabling of the subscription. Finally, we make autovacuum off for this test to avoid any empty transaction appearing in the test while consuming changes. Reported-by: as per buildfarm Author: Vignesh C Reviewed-by: Amit Kapila, Michael Paquier Discussion: https://postgr.es/m/CAA4eK1+uW1UGDHDz-HWMHMen76mKP7NJebOTZN4uwbyMjaYVww@mail.gmail.com https://git.postgresql.org/pg/commitdiff/0a442a408b40d2c6710de7e5397cb2e769d8c630

  • Fix deadlock for multiple replicating truncates of the same table. While applying the truncate change, the logical apply worker acquires RowExclusiveLock on the relation being truncated. This allowed truncate on the relation at a time by two apply workers which lead to a deadlock. The reason was that one of the workers after updating the pg_class tuple tries to acquire SHARE lock on the relation and started to wait for the second worker which has acquired RowExclusiveLock on the relation. And when the second worker tries to update the pg_class tuple, it starts to wait for the first worker which leads to a deadlock. Fix it by acquiring AccessExclusiveLock on the relation before applying the truncate change as we do for normal truncate operation. Author: Peter Smith, test case by Haiying Tang Reviewed-by: Dilip Kumar, Amit Kapila Backpatch-through: 11 Discussion: https://postgr.es/m/CAHut+PsNm43p0jM+idTvWwiGZPcP0hGrHMPK9TOAkc+a4UpUqw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6d0eb38557155855539cd007f04736dc3b2ba16f

Dean Rasheed pushed:

Andrew Dunstan pushed:

Pending Patches

Yugo Nagata sent in another revision of a patch to implement incrementally materialized views.

Amul Sul sent in another revision of a patch to separate the WAL writing code from StartupXLOG(), implement WAL prohibit state using global barriers, error or Assert before START_CRIT_SECTION for WAL write, and document all this. This is infrastructure for, among other things, ALTER SYSTEM READ {ONLY | WRITE}.

Pavel Stěhule sent in another revision of a patch to implement schema variables.

Bharath Rupireddy sent in another revision of a patch to avoid catalog accesses in slot_store_error_callback and conversion_error_callback.

Amit Langote sent in a patch to reword some comments in pathnodes.h for clarity.

Ranier Vilela sent in another revision of a patch to fix a possible memory corruption in zic.

Bharath Rupireddy sent in three revisions of a patch to tighten up batch_size and fetch_size options against non-numeric values in the PostgreSQL FDW.

Masahiro Ikeda sent in two more revisions of a patch to improve the performance of reporting WAL stats without introducing a new variable.

Hou Zhijie and Amit Langote traded patches to skip partition tuple routing when there is a constant partition key.

Peter Smith and Ajin Cherian traded patches to add support for prepared transactions to built-in logical replication, add prepare API support for streaming transactions, and skip empty transactions for logical replication.

Amit Langote sent in four more revisions of a patch to pgoutput to fix memory management of RelationSyncEntry.map by releasing memory allocated when creating the tuple-conversion map and its component TupleDescs when its owning sync entry is invalidated and freeing TupleDescs when no map is deemed necessary to begin with.

Nitin Jadhav sent in two more revisions of a patch to remove an extra malloc from create_list_bounds(), allocate the PartitionListValue as a single chunk, do the same in create_hash_bounds for PartitionHashBound, allocate datum arrays in bulk to avoid palloc overhead, and pfree intermediate results in create_range_bounds().

Bertrand Drouvot sent in another revision of a patch to keep oldestxid in pgupgrade.

Andrew Dunstan sent in another revision of a patch to implement SQL/JSON functions.

Andrew Dunstan sent in another revision of a patch to implement SQL/JSON JSON_TABLE.

Matthias van de Meent sent in another revision of a patch to improve the usage of line pointer array truncation in heapam.

Heikki Linnakangas sent in a patch to allow specifying pg_waldump --rmgr option multiple times.

Robert Haas, Dilip Kumar, and Kyotaro HORIGUCHI traded patches intended to fix a bug that manifested as a race condition in recovery.

Takashi Menjo sent in another revision of a patch to map WAL segment files on PMEM as WAL buffers.

Justin Pryzby sent in another revision of a patch to implement different compression methods for FPI.

Takamichi Osumi sent in a patch to disallow TRUNCATE on user_catalog_table.

Peter Eisentraut and Álvaro Herrera traded patches to add a NO_INSTALL option to pgxs.

Bharath Rupireddy sent in three more revisions of a patch to disambiguate error messages that use "non-negative".

Daniel Gustafsson sent in two revisions of a patch to extend configure_test_server_for_ssl to add extensions, and add tests for sslinfo.

Mathis Rudolf sent in a patch intended to fix a bug that manifested as an alias collision in REFRESH MATERIALIZED VIEW CONCURRENTLY by adding adds the prefix _pg_internal_ to aliases like 'mv' and 'newdata' in 'refresh_by_match_merge()', which makes it unlikely to cause any collisions with user-created MVs.

Yura Sokolov sent in a patch to add a PortalDrop call to exec_execute_message().

Bharath Rupireddy and Peter Smith traded patches to refactor "mutually exclusive options" error reporting code in parse_subscription_options.

Michaël Paquier sent in another revision of a patch to switch tests of pg_upgrade to use TAP.

Greg Nancarrow sent in another revision of a patch to fix a parallel worker failed assertion and coredump.

Kirill Reshke sent in a patch intended to fix a bug that manifested as slow standby snapshot by using a doubly linked list in KnownAssignedXids.

Paul Guo sent in a patch to fix a pg_rewind failure due to read only file open() error by making it writable.

Alexander Pyhalov sent in a patch to make it possible to push joins with function RTEs to PostgreSQL data sources.

Nitin Jadhav sent in another revision of a patch to support tzh tzm patterns.

Michaël Paquier sent in a patch to force disable of SSL renegotiation in the server.

Ivan Panchenko sent in another revision of a patch to make it possible to trigger actions on login.

Takayuki Tsunakawa sent in another revision of a patch to propagate CTE property flags in the rewriter.

Ashutosh Bapat sent in two revisions of a patch to report new catalog_xmin candidate in LogicalIncreaseXminForSlot().

Michaël Paquier sen in another revision of a patch to add authenticated data to pg_stat_activity.

Bharath Rupireddy sent in another revision of a patch to reword error messages and docs for parallel vacuum.

Hou Zhijie sent in two revisions of a patch intended to fix a bug that manifested as caused FDW batched inserts to fail when batch_size > 65535.

Dmitry Dolgov sent in another revision of a patch to implement index skip scans.

Tomáš Vondra sent in a patch intended to fix a bug that manifested as performance degradation of REFRESH MATERIALIZED VIEW.

Michaël Paquier and Tom Lane traded patches to reduce the memory footprint of the pg_attribute struct.

David Rowley sent in another revision of a patch to speed up NOT IN() with a set of Consts.

Vigneshwaran C sent in another revision of a patch to add tab completion for missing options in PUBLICATION and SUBSCRIPTION commands.

Kategóriák: Informatika

Oldalak

Theme by me