Nouvelles hebdomadaires de PostgreSQL - 27 novembre 2016
Les nouveautés des produits dérivés
- Ora2PG 17.6, un outil de migration de bases Oracle vers PostgreSQL : http://ora2pg.darold.net/
- pgpool-II 3.6.0 : http://pgpool.net/mediawiki/index.php/Downloads
- pitrery 1.12, un ensemble de scripts Bash permettant de gérer les sauvegardes PITR de PostgreSQL : https://github.com/dalibo/pitrery
Offres d'emplois autour de PostgreSQL en novembre
- Internationales : http://archives.postgresql.org/pgsql-jobs/2016-11/threads.php
- Francophones : http://forums.postgresql.fr/viewforum.php?id=4
- La PGconf.ASIA 2016 aura lieu les 1, 2 & 3 décembre à Tokyo (Japon). Les inscriptions sont ouvertes. Il y aura une UnConference le 1er décembre avec une inscription séparée et requise : http://www.pgconf.asia/EN/registration/ http://www.pgconf.asia/EN/day-0/
- CHAR(16) aura lieu à New York le 6 décembre 2016 : http://charconference.org/
- Le PGDay.IT 2016 aura lieu à Prato le 13 décembre 2016 : http://pgday.it
- La PGConf India 2017 aura lieu les 2 & 3 mars 2017 à Bengalore (État du Karnataka en Inde). Les propositions sont attendues sur <papers AT pgconf DOT in> jusqu'au 31 décembre 2016 : http://pgconf.in/
- PostgreSQL@SCaLE aura lieu les 2 & 3 mars 2017 au centre des conventions de Pasadena, comme partie du SCaLE 15X : http://www.socallinuxexpo.org/scale/15x/
PostgreSQL dans les média
- Planet PostgreSQL : http://planet.postgresql.org/
- Planet PostgreSQLFr : http://planete.postgresql.fr/
PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA. La version originale se trouve à l'adresse suivante : http://www.postgresql.org/message-id/20161127230258.GE21874@fetter.org
Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.
Tom Lane pushed:
- Fix test for subplans in force-parallel mode. We mustn't force parallel mode if the query has any subplans, since ExecSerializePlan doesn't transmit them to workers. Testing top_plan->initPlan is inadequate because (1) there might be initPlans attached to lower plan nodes, and (2) non-initPlan subplans don't work either. There's certainly room for improvement in those restrictions, but for the moment that's what we've got. Amit Kapila, per report from Andreas Seltenreich Discussion: <email@example.com> http://git.postgresql.org/pg/commitdiff/f24cf960d7ae3503e21fcb59dca652575619d9d4
- Fix optimization for skipping searches for parallel-query hazards. Fix thinko in commit da1c91631: even if the original query was free of parallel hazards, we might introduce such a hazard by adding PARAM_EXEC Param nodes. Adjust is_parallel_safe() so that it will scan the given expression whenever any such nodes have been created. Per report from Andreas Seltenreich. Discussion: <firstname.lastname@example.org> http://git.postgresql.org/pg/commitdiff/4324ade9a6880113b08070305482ace2e8a2617c
- Fix PGLC_localeconv() to handle errors better. The code was intentionally not very careful about leaking strdup'd strings in case of an error. That was forgivable probably, but it also failed to notice strdup() failures, which could lead to subsequent null-pointer-dereference crashes, since many callers unsurprisingly didn't check for null pointers in the struct lconv fields. An even worse problem is that it could throw error while we were setlocale'd to a non-C locale, causing unwanted behavior in subsequent libc calls. Rewrite to ensure that we cannot throw elog(ERROR) until after we've restored the previous locale settings, or at least attempted to. (I'm sorely tempted to make restore failure be a FATAL error, but will refrain for the moment.) Having done that, it's not much more work to ensure that we clean up strdup'd storage on the way out, too. This code is substantially the same in all supported branches, so back-patch all the way. Michael Paquier and Tom Lane Discussion: <CAB7nPqRMbGqa_mesopcn4MPyTs34eqtVEK7ELYxvvV=oqS00YA@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/a4930e7ca2aa49c13ccba089f5fd2c416d87e96a
- Fix uninitialized variable. Oversight in a734fd5d1. Michael Paquier http://git.postgresql.org/pg/commitdiff/ae92a9a3806c025653140ee6316a82d55e24b82c
- Make contrib/test_decoding regression tests safe for CZ locale. A little COLLATE "C" goes a long way. Pavel Stehule, per suggestion from Craig Ringer Discussion: <CAFj8pRA8nJZcozgxN=RMSqMmKuHVOkcGAAKPKdFeiMWGDSUDLA@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/e2a0ee69006bf31f24958b72c93455412cba035c
- Doc: add a section in Part II concerning RETURNING. There are assorted references to RETURNING in Part II, but nothing that would qualify as an explanation of the feature, which seems like an oversight considering how useful it is. Add something. Noted while looking for a place to point a cross-reference to ... http://git.postgresql.org/pg/commitdiff/1c7861e81b4220364bef75d2445e9c0619f3f3f8
- Improve handling of "UPDATE ... SET (column_list) = row_constructor". Previously, the right-hand side of a multiple-column assignment, if it wasn't a sub-SELECT, had to be a simple parenthesized expression list, because gram.y was responsible for "bursting" the construct into independent column assignments. This had the minor defect that you couldn't write ROW (though you should be able to, since the standard says this is a row constructor), and the rather larger defect that unlike other uses of row constructors, we would not expand a "foo.*" item into multiple columns. Fix that by changing the RHS to be just "a_expr" in the grammar, leaving it to transformMultiAssignRef to separate the elements of a RowExpr; which it will do only after performing standard transformation of the RowExpr, so that "foo.*" behaves as expected. The key reason we didn't do that before was the hard-wired handling of DEFAULT tokens (SetToDefault nodes). This patch deals with that issue by allowing DEFAULT in any a_expr and having parse analysis throw an error if SetToDefault is found in an unexpected place. That's an improvement anyway since the error can be more specific than just "syntax error". The SQL standard suggests that the RHS could be any a_expr yielding a suitable row value. This patch doesn't really move the goal posts in that respect --- you're still limited to RowExpr or a sub-SELECT --- but it does fix the grammar restriction, so it provides some tangible progress towards a full implementation. And the limitation is now documented by an explicit error message rather than an unhelpful "syntax error". Discussion: <email@example.com> http://git.postgresql.org/pg/commitdiff/906bfcad7ba7cb3863fe0e2a7810be8e3cd84fbd
- Doc: improve documentation about composite-value usage. Create a section specifically for the syntactic rules around whole-row variable usage, such as expansion of "foo.*". This was previously documented only haphazardly, with some critical info buried in unexpected places like xfunc-sql-composite-functions. Per repeated questions in different mailing lists. Discussion: <firstname.lastname@example.org> http://git.postgresql.org/pg/commitdiff/e1320266edd7df53c60af10b4c33ab2754278b3e
- Make sure ALTER TABLE preserves index tablespaces. When rebuilding an existing index, ALTER TABLE correctly kept the physical file in the same tablespace, but it messed up the pg_class entry if the index had been in the database's default tablespace and "default_tablespace" was set to some non-default tablespace. This led to an inaccessible index. Fix by fixing pg_get_indexdef_string() to always include a tablespace clause, whether or not the index is in the default tablespace. The previous behavior was installed in commit 537e92e41, and I think it just wasn't thought through very clearly; certainly the possible effect of default_tablespace wasn't considered. There's some risk in changing the behavior of this function, but there are no other call sites in the core code. Even if it's being used by some third party extension, it's fairly hard to envision a usage that is okay with a tablespace clause being appended some of the time but can't handle it being appended all the time. Back-patch to all supported versions. Code fix by me, investigation and test cases by Michael Paquier. Discussion: <email@example.com> http://git.postgresql.org/pg/commitdiff/bd673e8e864a1987eca8d40c593e857ab8d0a5ba
- Avoid masking a function parameter name with a local variable name. No actual bug here, but it might confuse readers, so change the name of the local variable. Ashutosh Bapat http://git.postgresql.org/pg/commitdiff/6fa391be4e83139cd134d5ccfc1499809bb8c98c
- Check that default_tablespace affects ALTER TABLE ADD UNIQUE/PRIMARY KEY. Seems like a good thing to test, considering that we nearly broke it yesterday. Michael Paquier http://git.postgresql.org/pg/commitdiff/4cc6a3f1106034187be3a371e61a02915bb93c11
- Check for pending trigger events on far end when dropping an FK constraint. When dropping a foreign key constraint with ALTER TABLE DROP CONSTRAINT, we refuse the drop if there are any pending trigger events on the named table; this ensures that we won't remove the pg_trigger row that will be consulted by those events. But we should make the same check for the referenced relation, else we might remove a due-to-be-referenced pg_trigger row for that relation too, resulting in "could not find trigger NNN" or "relation NNN has no triggers" errors at commit. Per bug #14431 from Benjie Gillam. Back-patch to all supported branches. Report: <firstname.lastname@example.org> http://git.postgresql.org/pg/commitdiff/4e026b32d4024b03856b4981b26c747b7fef7afb
- Mark a query's topmost Paths parallel-unsafe if they will have initPlans. Andreas Seltenreich found another case where we were being too optimistic about allowing a plan to be considered parallelizable despite it containing initPlans. It seems like the real issue here is that if we know we are going to tack initPlans onto the topmost Plan node for a subquery, we had better mark that subquery's result Paths as not-parallel-safe. That fixes this problem and allows reversion of a kluge (added in commit 7b67a0a49 and extended in f24cf960d) to not trust the parallel_safe flag at top level. Discussion: <email@example.com> http://git.postgresql.org/pg/commitdiff/ab77a5a4561fad847af4a101a29c922c66449870
- Bring some clarity to the defaults for the xxx_flush_after parameters. Instead of confusingly stating platform-dependent defaults for these parameters in the comments in postgresql.conf.sample (with the main entry being a lie on Linux), teach initdb to install the correct platform-dependent value in postgresql.conf, similarly to the way we handle other platform-dependent defaults. This won't do anything for existing 9.6 installations, but since it's effectively only a documentation improvement, that seems OK. Since this requires initdb to have access to the default values, move the #define's for those to pg_config_manual.h; the original placement in bufmgr.h is unworkable because that file can't be included by frontend programs. Adjust the default value for wal_writer_flush_after so that it is 1MB regardless of XLOG_BLCKSZ, conforming to what is stated in both the SGML docs and postgresql.conf. (We could alternatively make it scale with XLOG_BLCKSZ, but I'm not sure I see the point.) Copy-edit related SGML documentation. Fabien Coelho and Tom Lane, per a gripe from Tomas Vondra. Discussion: <firstname.lastname@example.org> http://git.postgresql.org/pg/commitdiff/dbdfd114f34443f1e4ad16ce2721f9817d3b3d80
- Fix test about ignoring extension dependencies during extension scripts. Commit 08dd23cec introduced an exception to the rule that extension member objects can only be dropped as part of dropping the whole extension, intending to allow such drops while running the extension's own creation or update scripts. However, the exception was only applied at the outermost recursion level, because it was modeled on a pre-existing check to ignore dependencies on objects listed in pendingObjects. Bug #14434 from Philippe Beaudoin shows that this is inadequate: in some cases we can reach an extension member object by recursion from another one. (The bug concerns the serial-sequence case; I'm not sure if there are other cases, but there might well be.) To fix, revert 08dd23cec's changes to findDependentObjects() and instead apply the creating_extension exception regardless of stack level. Having seen this example, I'm a bit suspicious that the pendingObjects logic is also wrong and such cases should likewise be allowed at any recursion level. However, changing that would interact in subtle ways with the recursion logic (at least it would need to be moved to after the recursing-from check). Given that the code's been like that a long time, I'll refrain from touching it without a clear example showing it's wrong. Back-patch to all active branches. In HEAD and 9.6, where suitable test infrastructure exists, add a regression test case based on the bug report. Report: <email@example.com> Discussion: <firstname.lastname@example.org> http://git.postgresql.org/pg/commitdiff/182db070403fb33566da156a3a77cbcda16583b4
Robert Haas pushed:
- autovacuum: Drop orphan temp tables more quickly but with more caution. Previously, we only dropped an orphan temp table when it became old enough to threaten wraparound; instead, doing it immediately. The only value of waiting is that someone might be able to examine the contents of the orphan temp table for forensic purposes, but it's pretty difficult to actually do that and few users will wish to do so. On the flip side, not performing the drop immediately generates log spam and bloats pg_class. In addition, per a report from Grigory Smolkin, if a temporary schema contains a very large number of temporary tables, a backend attempting to clear the temporary schema might fail due to lock table exhaustion. It's helpful for autovacuum to clean up after such cases, and we don't want it to wait for wraparound to threaten before doing so. To prevent autovacuum from failing in the same manner as a backend trying to drop an entire temp schema, remove orphan temp tables in batches of 50, committing after each batch, so that we don't accumulate an unbounded number of locks. If a drop fails, retry other orphan tables that need to be dropped up to 10 times before giving up. With this system, if a backend does fail to clean a temporary schema due to lock table exhaustion, autovacuum should hopefully put things right the next time it processes the database. Discussion: CAB7nPqSbYT6dRwsXVgiKmBdL_ARemfDZMPA+RPeC_ge0GK70hA@mail.gmail.com Michael Paquier, with a bunch of comment changes by me. http://git.postgresql.org/pg/commitdiff/a734fd5d1c309cc553b7c8c79fba96218af090f7
- Support condition variables. Condition variables provide a flexible way to sleep until a cooperating process causes an arbitrary condition to become true. In simple cases, this can be accomplished with a WaitLatch/ResetLatch loop; the cooperating process can call SetLatch after performing work that might cause the condition to be satisfied, and the waiting process can recheck the condition each time. However, if the process performing the work doesn't have an easy way to identify which processes might be waiting, this doesn't work, because it can't identify which latches to set. Condition variables solve that problem by internally maintaining a list of waiters; a process that may have caused some waiter's condition to be satisfied must "signal" or "broadcast" on the condition variable. Robert Haas and Thomas Munro http://git.postgresql.org/pg/commitdiff/e8ac886c24776295dd9b025386a821061da8e4d1
- Code review for commit 274bb2b3857cc987cfa21d14775cae9b0dababa5. Avoid memory leak in conninfo_uri_parse_options. Use the current host rather than the comma-separated list of host names when the host name is needed for GSS, SSPI, or SSL authentication. Document the way connect_timeout interacts with multiple host specifications. Takayuki Tsunakawa http://git.postgresql.org/pg/commitdiff/9a1d0af4ad2cbd419115b453d811c141b80d872b
- Remove barrier.h. A new thing also called a "barrier" is proposed, but whether we decide to take that patch or not, this file seems to have outlived its usefulness. Thomas Munro http://git.postgresql.org/pg/commitdiff/e343dfa42bff35983c582da3916b205763aeac90
- Mark IsPostmasterEnvironment and IsBackgroundWorker as PGDLLIMPORT. Per request from Craig Ringer. http://git.postgresql.org/pg/commitdiff/273270593f42bdbd5422923eb70fbd0fb0f65bf0
Ãlvaro Herrera pushed:
- Fix commit_ts for FrozenXid and BootstrapXid. Previously, requesting commit timestamp for transactions FrozenTransactionId and BootstrapTransactionId resulted in an error. But since those values can validly appear in committed tuples' Xmin, this behavior is unhelpful and error prone: each caller would have to special-case those values before requesting timestamp data for an Xid. We already have a perfectly good interface for returning "the Xid you requested is too old for us to have commit TS data for it", so let's use that instead. Backpatch to 9.5, where commit timestamps appeared. Author: Craig Ringer. Discussion: https://www.postgresql.org/message-id/CAMsr+YFM5Q=+ry3mKvWEqRTxrB0iU3qUSRnS28nz6FJYtBwhJg@mail.gmail.com http://git.postgresql.org/pg/commitdiff/4aaddf2f009821e29aea3735e44332ad9ca47aaa
Magnus Hagander pushed:
- Fix typo in comment. Thomas Munro http://git.postgresql.org/pg/commitdiff/8afb811088c2a8bfa2b68bd24c05471b8ea4a8ce
- Mention server start requirement for ssl parameters. Fix that the documentation for three ssl related parameters did not specify that they can only be changed at server start. Michael Paquier http://git.postgresql.org/pg/commitdiff/c6dbc7b65175f791b8e63a1970ee6b6f68ebec09
Correctifs en attente
Craig Ringer sent in two revisions of a patch to document that test must pass with Perl 5.8.0 and show how to ensure that they do.
MichaÃ«l Paquier sent in another revision of a patch to fix the checkpoint skip logic on idle systems by tracking LSN progress.
Corey Huinker sent in a patch to ensure that dblink knows about valid PostgreSQL FDW options.
Thomas Munro sent in another revision of a patch to add delta relations in AFTER triggers, complete with exposure to PL/pgsql and PL/PythonU.
KaiGai Kohei sent in another revision of a patch to enable passing LIMIT to FDWs AND CUSTOMSCANS.
Daniel VÃ©ritÃ© sent in two more revisions of a patch to improve psql hooks for variables.
Amit Kapila sent in a patch to prevent cascading standbys from getting stuck.
Etsuro Fujita sent in three more revisions of a patch to push down more full joins to the PostgreSQL FDW.
Craig Ringer sent in a patch to add logical decoding on standbys.
Thomas Munro sent in another revision of a patch to measure replay lag.
Matheus de Oliveira sent in a patch to extend ALTER DEFAULT PRIVILEGES to include schemas.
Thomas Munro sent in another revision of a patch to add barriers for synchronizing cooperating processes.
Peter Geoghegan sent in another revision of a patch to add amcheck, a B-Tree integrity checking tool.
Mithun Cy sent in two more revisions of a patch to implement failover on the libpq connect level.
MichaÃ«l Paquier sent in another revision of a patch to forbid the use of LF and CR characters in database and role names.
Amit Langote sent in two more revisions of a patch to add declarative partitioning.
Erik Rijkers sent in a patch to fix the CREATE SUBSCRIPTION documents, which broke the oldhtml target.
Andres Freund sent in a patch to fix a performance degradation in Bitmapscan.
Amul Sul sent in another revision of a patch to add a pg_background contrib extension.
Amit Kapila sent in another revision of a patch to make hash indexes concurrency-safe.
Ãlvaro Herrera and Pavel StÄ›hule traded patches to add xmltable().
Thomas Munro sent in two more revisions of a patch to add dynamic shared memory areas (DSAs).
Thomas Munro sent in two more revisions of a patch to add a DSA for the parallel execution.
Karl O. Pinc and Gilles Darold traded patches to implement pg_current_logfile().
Rushabh Lathia sent in another revision of a patch to implement Gather Merge.
Andreas Karlsson sent in a WIP patch to make PQHost() return the value of the host parameter rather than the hostaddr.
Amit Kapila sent in two revisions of a patch to allow safe subquery with parallel workers.
Haribabu Kommi sent in another revision of a patch to add an 8-byte MAC address type called macaddr8 which conforms to EUI-64.
Pavel StÄ›hule sent in a PoC patch to add session server side variables.
Ming Li sent in a patch to fix a pg_class refcache leak when the meta tuple in pg_class is invalid.
Amit Kapila sent in another revision of a patch to add parallel index scans.
MichaÃ«l Paquier sent in another revision of a patch to add quorum commit for multiple synchronous replication.
Artur Zakirov sent in a patch to fix a bug in which caused a pg_event_trigger_ddl_commands() error with ALTER TEXT SEARCH CONFIGURATION.