== PostgreSQL Weekly News - March 08 2015 == - Mailing list pgsql-announce

== 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 david@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.  Spanish language
to pwn@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.GC6717@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.brightwell@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.