== PostgreSQL Weekly News - March 08 2015 == The constituent assembly of the Swiss PostgreSQL Users Group (SwissPUG) will be Friday, April 10, 2015 http://www.swisspug.org
There is a Postgres track in a database technology conference(DTCC) in April 18, 2015 in Beijing, China. http://dtcc.it168.com/list_jiabin.html pgDay Paris will be held in Paris France on April 21, 2015. http://pgday.paris/ == PostgreSQL Product News == pguint, an extension that adds unsigned integer types to PostgreSQL, released. https://github.com/petere/pguint POWA 2.0, a PostgreSQL workload analyzer, released. http://dalibo.github.io/powa/ == PostgreSQL Local == pgDaySF 2015 will be held March 10, 2015 in Burlingame, California. http://sfpostgres.org/pgday-sf-2015-call-for-speakers-and-sponsors/ Registration for Nordic PostgreSQL Day, March 11, 2015 in Copenhagen, Denmark, is open. http://2015.nordicpgday.org/registration/ PGConf US 2015 takes place March 25-27, 2015 in NYC. http://nyc.pgconf.us/2015/ The CfP for the second Swiss Postgres Conference, to be held June 25-26, 2015 at HSR Rapperswil, is open until April 1. http://www.postgres-conference.ch/cfp/ India PostgreSQL UserGroup will hold a PGday in Bengaluru, Karnataka, India on April 11, 2015. RSVP at http://www.meetup.com/India-PUG/events/220553997/ PGCon 2015 is June 16-20 in Ottawa, Canada. http://www.pgcon.org/2015/ == PostgreSQL in the News == Planet PostgreSQL: http://planet.postgresql.org/ PostgreSQL Weekly News is brought to you this week by David Fetter Submit news and announcements by Sunday at 3:00pm Pacific time. Please send English language ones to da...@fetter.org, German language to p...@pgug.de, Italian language to p...@itpug.org. Spanish language to p...@arpug.com.ar. == Applied Patches == Stephen Frost pushed: - Fix pg_dump handling of extension config tables Since 9.1, we've provided extensions with a way to denote "configuration" tables- tables created by an extension which the user may modify. By marking these as "configuration" tables, the extension is asking for the data in these tables to be pg_dump'd (tables which are not marked in this way are assumed to be entirely handled during CREATE EXTENSION and are not included at all in a pg_dump). Unfortunately, pg_dump neglected to consider foreign key relationships between extension configuration tables and therefore could end up trying to reload the data in an order which would cause FK violations. This patch teaches pg_dump about these dependencies, so that the data dumped out is done so in the best order possible. Note that there's no way to handle circular dependencies, but those have yet to be seen in the wild. The release notes for this should include a caution to users that existing pg_dump-based backups may be invalid due to this issue. The data is all there, but restoring from it will require extracting the data for the configuration tables and then loading them in the correct order by hand. Discussed initially back in bug #6738, more recently brought up by Gilles Darold, who provided an initial patch which was further reworked by Michael Paquier. Further modifications and documentation updates by me. Back-patch to 9.1 where we added the concept of extension configuration tables. http://git.postgresql.org/pg/commitdiff/ebd092bc2a07787b31b249d62033b9c8140a5d85 Robert Haas pushed: - pgbench: Add a real expression syntax to \set. Previously, you could do \set variable operand1 operator operand2, but nothing more complicated. Now, you can \set variable expression, which makes it much simpler to do multi-step calculations here. This also adds support for the modulo operator (%), with the same semantics as in C. Robert Haas and Fabien Coelho, reviewed by Álvaro Herrera and Stephen Frost http://git.postgresql.org/pg/commitdiff/878fdcb843e087cc1cdeadc987d6ef55202ddd04 - pgbench: Fix mistakes in Makefile. My commit 878fdcb843e087cc1cdeadc987d6ef55202ddd04 was not quite right. Tom Lane pointed out one of the mistakes fixed here, and I noticed the other myself while reviewing what I'd committed. http://git.postgresql.org/pg/commitdiff/e5f36902495d0c8d5dee9a5f43fb45d44540f795 - Remove residual NULL-pstate handling in addRangeTableEntry. Passing a NULL pstate wouldn't actually work, because isLockedRefname() isn't prepared to cope with it; and there hasn't been any in-core code that tries in over a decade. So just remove the residual NULL handling. Spotted by Coverity; analysis and patch by Michael Paquier. http://git.postgresql.org/pg/commitdiff/5223ddacdc737b401ed58184e321f354bdf46686 Tom Lane pushed: - Fix busted markup. Evidently from commit 878fdcb843e087cc1cdeadc987d6ef55202ddd04. Per buildfarm. http://git.postgresql.org/pg/commitdiff/d1479011744d80d80c669b5bd64dc32187f26c1e - Reduce json <=> jsonb casts from explicit-only to assignment level. There's no reason to make users write an explicit cast to store a json value in a jsonb column or vice versa. We could probably even make these implicit, but that might open us up to problems with ambiguous function calls, so for now just do this. http://git.postgresql.org/pg/commitdiff/b67f1ce181910e012b3a8ec7a35ba20a48247757 - Fix long-obsolete code for separating filter conditions in cost_index(). This code relied on pointer equality to identify which restriction clauses also appear in the indexquals (and, therefore, don't need to be applied as simple filter conditions). That was okay once upon a time, years ago, before we introduced the equivalence-class machinery. Now there's about a 50-50 chance that an equality clause appearing in the indexquals will be the mirror image (commutator) of its mate in the restriction list. When that happens, we'd erroneously think that the clause would be re-evaluated at each visited row, and therefore inflate the cost estimate for the indexscan by the clause's cost. Add some logic to catch this case. It seems to me that it continues not to be worthwhile to expend the extra predicate-proof work that createplan.c will do on the finally-selected plan, but this case is common enough and cheap enough to handle that we should do so. This will make a small difference (about one cpu_operator_cost per row) in simple cases; but in situations where there's an expensive function in the indexquals, it can make a very large difference, as seen in recent example from Jeff Janes. This is a long-standing bug, but I'm hesitant to back-patch because of the possibility of destabilizing plan choices that people may be happy with. http://git.postgresql.org/pg/commitdiff/497bac7d290df13d8b00ba48653a96015ff4741b - Fix cost estimation for indexscans on expensive indexed expressions. genericcostestimate() and friends used the cost of the entire indexqual expressions as the charge for initial evaluation of indexscan arguments. But of course the index column is not evaluated, only the other side of the qual expression, so this was a bad overestimate if the index column was an expensive expression. To fix, refactor the logic in this area so that there's a single routine charged with deconstructing index quals and figuring out what is the index column and what is the comparison expression. This is more or less free in the case of btree indexes, since btcostestimate() was doing equivalent deconstruction already. It probably adds a bit of new overhead in the cases of other index types, but not a lot. (In the case of GIN I think I saved something by getting rid of code that wasn't aware that the index column associations were already available "for free".) Per recent gripe from Jeff Janes. Arguably this is a bug fix, but I'm hesitant to back-patch because of the possibility of destabilizing plan choices that people may be happy with. http://git.postgresql.org/pg/commitdiff/b9896198cfbc1b0cd0c631d2af72ffe34bd4c7e5 - Use standard casting mechanism to convert types in plpgsql, when possible. plpgsql's historical method for converting datatypes during assignments was to apply the source type's output function and then the destination type's input function. Aside from being miserably inefficient in most cases, this method failed outright in many cases where a user might expect it to work; an example is that "declare x int; ... x := 3.9;" would fail, not round the value to 4. Instead, let's convert by applying the appropriate assignment cast whenever there is one. To avoid breaking compatibility unnecessarily, fall back to the I/O conversion method if there is no assignment cast. So far as I can tell, there is just one case where this method produces a different result than the old code in a case where the old code would not have thrown an error. That is assignment of a boolean value to a string variable (type text, varchar, or bpchar); the old way gave boolean's output representation, ie 't'/'f', while the new way follows the behavior of the bool-to-text cast and so gives 'true' or 'false'. This will need to be called out as an incompatibility in the 9.5 release notes. Aside from handling many conversion cases more sanely, this method is often significantly faster than the old way. In part that's because of more effective caching of the conversion info. http://git.postgresql.org/pg/commitdiff/1345cc67bbb014209714af32b5681b1e11eaf964 - Need to special-case RECORD as well as UNKNOWN in plpgsql's casting logic. This is because can_coerce_type thinks that RECORD can be cast to any composite type, but coerce_record_to_complex only works for inputs that are RowExprs or whole-row Vars, so we get a hard failure on a CaseTestExpr. Perhaps these corner cases ought to be fixed so that coerce_to_target_type actually returns NULL as per its specification, rather than failing ... but for the moment an extra check here is the path of least resistance. http://git.postgresql.org/pg/commitdiff/45f2c2fc4e4adcf75cd689e18dab77ebe622fc2e - Change plpgsql's cast cache to consider source typmod as significant. I had thought that there was no need to maintain separate cache entries for different source typmods, but further experimentation shows that there is an advantage to doing so in some cases. In particular, if a domain has a typmod (say, "CREATE DOMAIN d AS numeric(20,0)"), failing to notice the source typmod leads to applying a length-coercion step even when the source has the correct typmod. http://git.postgresql.org/pg/commitdiff/7f3014dce56c7975113809f2ff5e92cf7c1563a3 - Avoid unused-variable warning in non-assert builds. Oversight in my commit b9896198cfbc1b0cd0c631d2af72ffe34bd4c7e5. http://git.postgresql.org/pg/commitdiff/a5c29d37aab00e9e70e72c97f2be29030f6ee84c - Remove comment claiming that PARAM_EXTERN Params always have typmod -1. This hasn't been true in quite some time, cf plpgsql's make_datum_param(). http://git.postgresql.org/pg/commitdiff/3200b15b20d9248be1b0f436ee787b2077d00298 - Rethink function argument sorting in pg_dump. Commit 7b583b20b1c95acb621c71251150beef958bb603 created an unnecessary dump failure hazard by applying pg_get_function_identity_arguments() to every function in the database, even those that won't get dumped. This could result in snapshot-related problems if concurrent sessions are, for example, creating and dropping temporary functions, as noted by Marko Tiikkaja in bug #12832. While this is by no means pg_dump's only such issue with concurrent DDL, it's unfortunate that we added a new failure mode for cases that used to work, and even more so that the failure was created for basically cosmetic reasons (ie, to sort overloaded functions more deterministically). To fix, revert that patch and instead sort function arguments using information that pg_dump has available anyway, namely the names of the argument types. This will produce a slightly different sort ordering for overloaded functions than the previous coding; but applying strcmp directly to the output of pg_get_function_identity_arguments really was a bit odd anyway. The sorting will still be name-based and hence independent of possibly-installation-specific OID assignments. A small additional benefit is that sorting now works regardless of server version. Back-patch to 9.3, where the previous commit appeared. http://git.postgresql.org/pg/commitdiff/e3bfe6d84d4919433d8323cfb8194ca60d99f2c4 - Fix erroneous error message for REINDEX SYSTEM. Missed case in commit fe263d115a7dd16095b8b8f1e943aff2bb4574d2. Sawada Masahiko http://git.postgresql.org/pg/commitdiff/ac0914285ac90bd411730c3219f226bbbbc57f3a - Code cleanup for REINDEX DATABASE/SCHEMA/SYSTEM. Fix some minor infelicities. Some of these things were introduced in commit fe263d115a7dd16095b8b8f1e943aff2bb4574d2, and some are older. http://git.postgresql.org/pg/commitdiff/90c35a9ed06c1353a0d3818c259e629ff09dba18 - Fix documentation for libpq's PQfn(). The SGML docs claimed that 1-byte integers could be sent or received with the "isint" options, but no such behavior has ever been implemented in pqGetInt() or pqPutInt(). The in-code documentation header for PQfn() was even less in tune with reality, and the code itself used parameter names matching neither the SGML docs nor its libpq-fe.h declaration. Do a bit of additional wordsmithing on the SGML docs while at it. Since the business about 1-byte integers is a clear documentation bug, back-patch to all supported branches. http://git.postgresql.org/pg/commitdiff/1a0bc4c2bfc278b63965486b1525ad04a1f85989 - Remove struct PQArgBlock from server-side header libpq/libpq.h. This struct is purely a client-side artifact. Perhaps there was once reason for the server to know it, but any such reason is lost in the mists of time. We certainly don't need two independent declarations of it. http://git.postgresql.org/pg/commitdiff/01cca2c1b1a0d52c83f250c50942ee00e62637ca - Cast to (void *) rather than (int *) when passing int64's to PQfn(). This is a possibly-vain effort to silence a Coverity warning about bogus endianness dependency. The code's fine, because it takes care of endianness issues for itself, but Coverity sees an int64 being passed to an int* argument and not unreasonably suspects something's wrong. I'm not sure if putting the void* cast in the way will shut it up; but it can't hurt and seems better from a documentation standpoint anyway, since the pointer is not used as an int* in this code path. Just for a bit of additional safety, verify that the result length is 8 bytes as expected. Back-patch to 9.3 where the code in question was added. http://git.postgresql.org/pg/commitdiff/ef75508efc789c79c5a5d4acd7ad5da85f1e4f08 Álvaro Herrera pushed: - Add comment for "is_internal" parameter. This was missed in my commit f4c4335 of 9.3 vintage, so backpatch to that. http://git.postgresql.org/pg/commitdiff/6f9d79904748c26a58991942dc6719db558f77b0 - Change many routines to return ObjectAddress rather than OID. The changed routines are mostly those that can be directly called by ProcessUtilitySlow; the intention is to make the affected object information more precise, in support for future event trigger changes. Originally it was envisioned that the OID of the affected object would be enough, and in most cases that is correct, but upon actually implementing the event trigger changes it turned out that ObjectAddress is more widely useful. Additionally, some command execution routines grew an output argument that's an object address which provides further info about the executed command. To wit: * for ALTER DOMAIN / ADD CONSTRAINT, it corresponds to the address of the new constraint * for ALTER OBJECT / SET SCHEMA, it corresponds to the address of the schema that originally contained the object. * for ALTER EXTENSION {ADD, DROP} OBJECT, it corresponds to the address of the object added to or dropped from the extension. There's no user-visible change in this commit, and no functional change either. Discussion: 20150218213255.gc6...@tamriel.snowman.net Reviewed-By: Stephen Frost, Andres Freund http://git.postgresql.org/pg/commitdiff/a2e35b53c39b2a27d3e332dc7c506539c306fd44 - Silence warning in non-assert-enabled build. An OID return value was being used only for a (rather pointless) assert. Silence by removing the variable and the assert. Per note from Peter Geoghegan http://git.postgresql.org/pg/commitdiff/bf22d2707a2f47a7cc4caa239a14f2bf0a72bfd0 - Fix user mapping object description. We were using "user mapping for user XYZ" as description for user mappings, but that's ambiguous because users can have mappings on multiple foreign servers; therefore change it to "for user XYZ on server UVW" instead. Object identities for user mappings are also updated in the same way, in branches 9.3 and above. The incomplete description string was introduced together with the whole SQL/MED infrastructure by commit cae565e503 of 8.4 era, so backpatch all the way back. http://git.postgresql.org/pg/commitdiff/cf34e373fcf42239a73f36e3054d9e9fbdc1e0de - Fix contrib/file_fdw's expected file. I forgot to update it on yesterday's cf34e373fcf. http://git.postgresql.org/pg/commitdiff/c6ee39bc8587042f018979ddd6ed9825acbbd3d8 - Add some more tests on event triggers. Fabien Coelho. Reviewed by Robert Haas http://git.postgresql.org/pg/commitdiff/6510c832bbf91d52541c7aeefa371123abc2d832 Fujii Masao pushed: - Fix an obsolete reference to SnapshotNow in comment. Peter Geoghegan http://git.postgresql.org/pg/commitdiff/f8b031bca848a21fb9a7c9530f8cdc7eb6919dd9 - Fix typo in comment. http://git.postgresql.org/pg/commitdiff/934d12268585823c271c1490fc9aa4467c301e9c Peter Eisentraut pushed: - Remove rolcatupdate. This role attribute is an ancient PostgreSQL feature, but could only be set by directly updating the system catalogs, and it doesn't have any clearly defined use. Author: Adam Brightwell <adam.brightw...@crunchydatasolutions.com> http://git.postgresql.org/pg/commitdiff/bb8582abf3c4db18b508627a52effd43672f9410 - Sort SUBDIRS variable in src/bin/Makefile. The previous order appears to have been historically grown randomness. http://git.postgresql.org/pg/commitdiff/5a2a48f0367e24f8f12cfe0a90a886dae0db1ce2 Noah Misch pushed: - Build fls.o only when AC_REPLACE_FUNCS so dictates via $(LIBOBJS). By building it unconditionally, libpgport inadvertently replaced any libc version of the function. This is essentially a code cleanup; any effect on performance is almost surely too small to notice. http://git.postgresql.org/pg/commitdiff/9d265ae77af6f8d99bbd68b6ee644e7572b16754 - Add CHECK_FOR_INTERRUPTS() to the wait_pid() loop. Though the one contemporary caller uses it in a limited way, this function could loop indefinitely if pointed to an arbitrary PID. http://git.postgresql.org/pg/commitdiff/93751570731ba08dca4e791234984c3b1a15e885 == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == Michael Paquier sent in a patch to fix broken Install.bat when target directory contains a space. Jan de Visser sent in two more revisions of a patch to let pg ctl check the result of a postmaster config reload. Gregory Stark and Haribabu Kommi traded patches to provide a catalog view of pg_hba.conf. Joshua Berkus sent in four revisions of a patch to raise default for max_wal_segments to 1GB. SAWADA Masahiko sent in another revision of a patch to implement REINDEX...VERBOSE. Dean Rasheed sent in two more revisions of a patch to update RLS timings. Álvaro Herrera sent in four more revisions of a patch to implement ALTER USER/ROLE ... CURRENT USER. Michael Paquier sent in two more revisions of a patch to improve test coverage with pg_dump. Shigeru HANADA and Ashutosh Bapat traded patches to implement push-down JOIN support for foreign tables. Marko Kreen sent in a patch to fix excessive float lossiness in PL/Python. Julien Tachoires sent in another revision of a patch to allow toast tables to be moved to a different tablespace. Amit Kapila sent in another revision of a patch to implement parallel seq scan. Kaigai Kouhei and Shigeru HANADA traded patches to add custom foreign join APIs. Peter Geoghegan sent in another revision of a patch to add logical decoding support for ON CONFLICT UPDATE. Peter Geoghegan sent in a patch to remove an obsolete SnapshotNow reference within snapbuild.c. Kyotaro HORIGUCHI sent in another revision of a patch to add regrole and regnamespace. Michael Paquier sent in a patch to compare primary/HS standby in tests. Robert Haas sent in a patch to make some fixes in tuplesort. Rahila Syed sent in two more revisions of a patch to allow compressing improve full-page writes. Fabien COELHO sent in four more revisions of a patch to improve pgbench syntax error messages. David Rowley sent in another revision of a patch to improve performance for joins where outer side is unique. Michael Paquier sent in a patch to install shared libraries in bin/ and lib/ on MSVC. Michael Paquier sent in a patch to add dummy pstate fixes. Peter Geoghegan sent in another revision of a patch to add INSERT ... ON CONFLICT IGNORE (and UPDATE). Andreas Karlsson sent in another revision of a patch to use 128-bit integers for sum, avg and statistics aggregates. Etsuro Fujita sent in another revision of a patch to make updating foreign tables in the Postgres FDW work faster. SAWADA Masahiko sent in another revision of a patch to add a way to see the contents of configuration files via SQL. Michael Paquier sent in two more revisions of a patch to add a table level log_autovacuum_min_duration. Michael Paquier sent in a flock of patches to move the freeze parameters of VacuumStmt into a separate spot, eliminate VacuumStmt from lower level routines of ANALYZE and VACUUM, and add wraparound control parameters in VacuumStmt. Bruce Momjian sent in two more revisions of a patch to help fix pg_upgrade with reference to rsync. Robert Haas sent in another revision of a patch to implement parallel mode and parallel contexts. Tom Lane sent in a patch to fix some weirdly pesimistic estimates in optimizer. Álvaro Herrera sent in a flock of patches to patches add get_object_address support for user mappings, default ACLs, and operators and functions of operator families Kyotaro HORIGUCHI and Tom Lane traded patches to clamp row number of join product by the row number calculated from joining paths. Stephen Frost sent in another revision of a patch to add catalog_function_acls. Marco Nenciarini sent in another revision of a patch to implement file-based incremental backup. SAWADA Masahiko sent in a patch to fix an incorrect error message in REINDEX. Fabrízio de Royes Mello sent in two revisions of a patch to fix an odd debug in walreceiver. Peter Eisentraut sent in another revision of a patch to speed up make check-world. Peter Eisentraut sent in another revision of a patch to add TRANSFORMS. Pavel Stehule sent in a PoC patch to enforce casting to most common type automatically. Dmitry Voronin sent in a patch to adds functions to sslinfo extension module: ssl_extension_names(), ssl_extension_value(text), and ssl_extension_is_critical(text). Tomas Vondra sent in a patch to allow merging pgbench logs. Tomas Vondra sent in a patch to allow logging both aggregate and transaction info in pgbench, rather having to choose one or the other. Tom Lane sent in a patch to rethink the parameter access hooks for plpgsql's benefit. -- Sent via pgsql-announce mailing list (pgsql-announce@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-announce
