1. 28 Mar, 2020 10 commits
    • Nikolay Shaplov's avatar
      Applying changes from commit b07642db:... · 20ba5b88
      Nikolay Shaplov authored
      Applying changes from commit b07642db: 'Trigger autovacuum based on number of INSERTs'
      20ba5b88
    • Nikolay Shaplov's avatar
      bc211ed1
    • Nikolay Shaplov's avatar
      Change names of options structures · 98450adc
      Nikolay Shaplov authored
      98450adc
    • Nikolay Shaplov's avatar
      this function is no longer needed · 0a7c274b
      Nikolay Shaplov authored
      0a7c274b
    • Nikolay Shaplov's avatar
      Part of get-rid-of-StrRdOptions patch that is about splitting StdRdptions into... · b83ec65b
      Nikolay Shaplov authored
      Part of get-rid-of-StrRdOptions patch that is about splitting StdRdptions into HeapRelOptions and ToastRelOptions
      b83ec65b
    • Dean Rasheed's avatar
      Improve the performance and accuracy of numeric sqrt() and ln(). · 4083f445
      Dean Rasheed authored
      Instead of using Newton's method to compute numeric square roots, use
      the Karatsuba square root algorithm, which performs better for numbers
      of all sizes. In practice, this is 3-5 times faster for inputs with
      just a few digits and up to around 10 times faster for larger inputs.
      
      Also, the new algorithm guarantees that the final digit of the result
      is correctly rounded, since it computes an integer square root with
      truncation, containing at least 1 extra decimal digit before rounding.
      The former algorithm would occasionally round the wrong way because
      it rounded both the intermediate and final results.
      
      In addition, arrange for sqrt_var() to explicitly support negative
      rscale values (rounding before the decimal point). This allows the
      argument reduction phase of ln_var() to be optimised for large inputs,
      since it only needs to compute square roots with a few more digits
      than the final ln() result, rather than computing all the digits
      before the decimal point. For very large inputs, this can be many
      thousands of times faster.
      
      In passing, optimise div_var_fast() in a couple of places where it was
      doing unnecessary work.
      
      Patch be me, reviewed by Tom Lane and Tels.
      
      Discussion: https://postgr.es/m/[email protected]om
      4083f445
    • Peter Eisentraut's avatar
      Enable Unix-domain sockets support on Windows · 8f3ec75d
      Peter Eisentraut authored
      As of Windows 10 version 1803, Unix-domain sockets are supported on
      Windows.  But it's not automatically detected by configure because it
      looks for struct sockaddr_un and Windows doesn't define that.  So we
      just make our own definition on Windows and override the configure
      result.
      
      Set DEFAULT_PGSOCKET_DIR to empty on Windows so by default no
      Unix-domain socket is used, because there is no good standard
      location.
      
      In pg_upgrade, we have to do some extra tweaking to preserve the
      existing behavior of not using Unix-domain sockets on Windows.  Adding
      support would be desirable, but it needs further work, in particular a
      way to select whether to use Unix-domain sockets from the command-line
      or with a run-time test.
      
      The pg_upgrade test script needs a fix.  The previous code passed
      "localhost" to postgres -k, which only happened to work because
      Windows used to ignore the -k argument value altogether.  We instead
      need to pass an empty string to get the desired effect.
      
      The test suites will continue to not use Unix-domain sockets on
      Windows.  This requires a small tweak in pg_regress.c.  The TAP tests
      don't need to be changed because they decide by the operating system
      rather than HAVE_UNIX_SOCKETS.
      Reviewed-by: Andrew Dunstan's avatarAndrew Dunstan <[email protected]>
      Discussion: https://www.postgresql.org/message-id/flat/[email protected]
      8f3ec75d
    • Dean Rasheed's avatar
      Prevent functional dependency estimates from exceeding column estimates. · 87779aa4
      Dean Rasheed authored
      Formerly we applied a functional dependency "a => b with dependency
      degree f" using the formula
      
        P(a,b) = P(a) * [f + (1-f)*P(b)]
      
      This leads to the possibility that the combined selectivity P(a,b)
      could exceed P(b), which is not ideal. The addition of support for IN
      and OR clauses (commits 8f321bd1 and ccaa3569) would seem to make
      this more likely, since the user-supplied values in such clauses are
      not necessarily compatible with the functional dependency.
      
      Mitigate this by using the formula
      
        P(a,b) = f * Min(P(a), P(b)) + (1-f) * P(a) * P(b)
      
      instead, which guarantees that the combined selectivity is less than
      each column's individual selectivity. Logically, this is modifies the
      part of the formula that accounts for dependent rows to handle cases
      where P(a) > P(b), whilst not changing the second term which accounts
      for independent rows.
      
      Additionally, this refactors the way that functional dependencies are
      applied, so now dependencies_clauselist_selectivity() estimates both
      the implying clauses and the implied clauses for each functional
      dependency (formerly only the implied clauses were estimated), and now
      all clauses for each attribute are taken into account (formerly only
      one clause for each implied attribute was estimated). This removes the
      previously built-in assumption that only equality clauses will be
      seen, which is no longer true, and opens up the possibility of
      applying functional dependencies to more general clauses.
      
      Patch by me, reviewed by Tomas Vondra.
      
      Discussion: https://postgr.es/m/CAEZATCXaNFZyOhR4XXAfkvj1tibRBEjje6ZbXwqWUB_tqbH%3Drw%40mail.gmail.com
      Discussion: https://postgr.es/m/20200318002946.6dvblukm3cfmgir2%40development
      87779aa4
    • Peter Eisentraut's avatar
      Cleanup in SQL features files · 145cb16d
      Peter Eisentraut authored
      Feature C011 was still listed in sql_feature_packages.txt but had been
      removed from sql_features.txt, so also remove from the former.
      145cb16d
    • David Rowley's avatar
      Trigger autovacuum based on number of INSERTs · b07642db
      David Rowley authored
      Traditionally autovacuum has only ever invoked a worker based on the
      estimated number of dead tuples in a table and for anti-wraparound
      purposes. For the latter, with certain classes of tables such as
      insert-only tables, anti-wraparound vacuums could be the first vacuum that
      the table ever receives. This could often lead to autovacuum workers being
      busy for extended periods of time due to having to potentially freeze
      every page in the table. This could be particularly bad for very large
      tables. New clusters, or recently pg_restored clusters could suffer even
      more as many large tables may have the same relfrozenxid, which could
      result in large numbers of tables requiring an anti-wraparound vacuum all
      at once.
      
      Here we aim to reduce the work required by anti-wraparound and aggressive
      vacuums in general, by triggering autovacuum when the table has received
      enough INSERTs. This is controlled by adding two new GUCs and reloptions;
      autovacuum_vacuum_insert_threshold and
      autovacuum_vacuum_insert_scale_factor. These work exactly the same as the
      existing scale factor and threshold controls, only base themselves off the
      number of inserts since the last vacuum, rather than the number of dead
      tuples. New controls were added rather than reusing the existing
      controls, to allow these new vacuums to be tuned independently and perhaps
      even completely disabled altogether, which can be done by setting
      autovacuum_vacuum_insert_threshold to -1.
      
      We make no attempt to skip index cleanup operations on these vacuums as
      they may trigger for an insert-mostly table which continually doesn't have
      enough dead tuples to trigger an autovacuum for the purpose of removing
      those dead tuples. If we were to skip cleaning the indexes in this case,
      then it is possible for the index(es) to become bloated over time.
      
      There are additional benefits to triggering autovacuums based on inserts,
      as tables which never contain enough dead tuples to trigger an autovacuum
      are now more likely to receive a vacuum, which can mark more of the table
      as "allvisible" and encourage the query planner to make use of Index Only
      Scans.
      
      Currently, we still obey vacuum_freeze_min_age when triggering these new
      autovacuums based on INSERTs. For large insert-only tables, it may be
      beneficial to lower the table's autovacuum_freeze_min_age so that tuples
      are eligible to be frozen sooner. Here we've opted not to zero that for
      these types of vacuums, since the table may just be insert-mostly and we
      may otherwise freeze tuples that are still destined to be updated or
      removed in the near future.
      
      There was some debate to what exactly the new scale factor and threshold
      should default to. For now, these are set to 0.2 and 1000, respectively.
      There may be some motivation to adjust these before the release.
      
      Author: Laurenz Albe, Darafei Praliaskouski
      Reviewed-by: Alvaro Herrera, Masahiko Sawada, Chris Travers, Andres Freund, Justin Pryzby
      Discussion: https://postgr.es/m/CAC8Q8t%2Bj36G_bLF%3D%2B0iMo6jGNWnLnWb1tujXuJr-%2Bx8ZCCTqoQ%40mail.gmail.com
      b07642db
  2. 27 Mar, 2020 5 commits
    • Peter Geoghegan's avatar
      Justify nbtree page split locking in code comment. · 9945ad6e
      Peter Geoghegan authored
      Delaying unlocking the right child page until after the point that the
      left child's parent page has been refound is no longer truly necessary.
      Commit 40dae7ec made nbtree tolerant of interrupted page splits.  VACUUM
      was taught to avoid deleting a page that happens to be the right half of
      an incomplete split.  As long as page splits don't unlock the left child
      page until the end of the second/final phase, it should be safe to
      unlock the right child page earlier (at the end of the first phase).
      
      It probably isn't actually useful to release the right child's lock
      earlier like this (it probably won't improve performance).  Even still,
      pointing out that it ought to be safe to do so should make it easier to
      understand the overall design.
      9945ad6e
    • Alvaro Herrera's avatar
      Allow walreceiver configuration to change on reload · 1e614803
      Alvaro Herrera authored
      The parameters primary_conninfo, primary_slot_name and
      wal_receiver_create_temp_slot can now be changed with a simple "reload"
      signal, no longer requiring a server restart.  This is achieved by
      signalling the walreceiver process to terminate and having it start
      again with the new values.
      
      Thanks to Andres Freund, Kyotaro Horiguchi, Fujii Masao for discussion.
      
      Author: Sergei Kornilov <[email protected]>
      Reviewed-by: default avatarMichael Paquier <[email protected]>
      Reviewed-by: default avatarÁlvaro Herrera <[email protected]>
      Discussion: https://postgr.es/m/[email protected]
      1e614803
    • Alvaro Herrera's avatar
      Set wal_receiver_create_temp_slot PGC_POSTMASTER · 092c6936
      Alvaro Herrera authored
      Commit 32973082 gave walreceiver the ability to create and use a
      temporary replication slot, and made it controllable by a GUC (enabled
      by default) that can be changed with SIGHUP.  That's useful but has two
      problems: one, it's possible to cause the origin server to fill its disk
      if the slot doesn't advance in time; and also there's a disconnect
      between state passed down via the startup process and GUCs that
      walreceiver reads directly.
      
      We handle the first problem by setting the option to disabled by
      default.  If the user enables it, its on their head to make sure that
      disk doesn't fill up.
      
      We handle the second problem by passing the flag via startup rather than
      having walreceiver acquire it directly, and making it PGC_POSTMASTER
      (which ensures a walreceiver always has the fresh value).  A future
      commit can relax this (to PGC_SIGHUP again) by having the startup
      process signal walreceiver to shutdown whenever the value changes.
      
      Author: Sergei Kornilov <[email protected]>
      Reviewed-by: default avatarMichael Paquier <[email protected]>
      Reviewed-by: default avatarÁlvaro Herrera <[email protected]>
      Discussion: https://postgr.es/m/[email protected]
      092c6936
    • Tom Lane's avatar
      Rearrange validity checks for plpgsql "simple" expressions. · fbc7a716
      Tom Lane authored
      Buildfarm experience shows what probably should've occurred to me before:
      if a cache flush occurs partway through building a generic plan, then
      the plansource may have is_valid = false even though the plan is valid.
      We need to accept this case, use the generated plan, and then try to
      replan the next time.  We can't try to replan immediately, because that
      would produce an infinite loop in CLOBBER_CACHE_ALWAYS builds; moreover
      it's really overkill.  (We can assume that the plan is valid, it's just
      possibly a bit stale.  Note that the pre-existing code behaved this way,
      and the non-simple-expression code paths do too.)  Conversely, not using
      the generated plan would drop us into the not-a-simple-expression code
      path, which is bad for performance and would also cause regression-test
      failures due to visibly different error-reporting behavior.
      
      Hence, refactor the validity-check functions so that the initial check
      and recheck cases can react differently to plansource->is_valid.
      This makes their usage a bit simpler, too.
      
      Discussion: https://postgr.es/m/[email protected]
      fbc7a716
    • Peter Eisentraut's avatar
      Update SQL features · 8d1b9648
      Peter Eisentraut authored
      Change F311 to supported.  This was already accomplished when
      subfeature F311-04 (WITH CHECK OPTION) was added, but the top-level
      feature wasn't updated at the time.
      8d1b9648
  3. 26 Mar, 2020 6 commits
    • Tom Lane's avatar
      Improve performance of "simple expressions" in PL/pgSQL. · 8f59f6b9
      Tom Lane authored
      For relatively simple expressions (say, "x + 1" or "x > 0"), plpgsql's
      management overhead exceeds the cost of evaluating the expression.
      This patch substantially improves that situation, providing roughly
      2X speedup for such trivial expressions.
      
      First, add infrastructure in the plancache to allow fast re-validation
      of cached plans that contain no table access, and hence need no locks.
      Teach plpgsql to use this infrastructure for expressions that it's
      already deemed "simple" (which in particular will never contain table
      references).
      
      The fast path still requires checking that search_path hasn't changed,
      so provide a fast path for OverrideSearchPathMatchesCurrent by
      counting changes that have occurred to the active search path in the
      current session.  This is simplistic but seems enough for now, seeing
      that PushOverrideSearchPath is not used in any performance-critical
      cases.
      
      Second, manage the refcounts on simple expressions' cached plans using
      a transaction-lifespan resource owner, so that we only need to take
      and release an expression's refcount once per transaction not once per
      expression evaluation.  The management of this resource owner exactly
      parallels the existing management of plpgsql's simple-expression EState.
      
      Add some regression tests covering this area, in particular verifying
      that expression caching doesn't break semantics for search_path changes.
      
      Patch by me, but it owes something to previous work by Amit Langote,
      who recognized that getting rid of plancache-related overhead would
      be a useful thing to do here.  Also thanks to Andres Freund for review.
      
      Discussion: https://postgr.es/m/[email protected]om
      8f59f6b9
    • Tom Lane's avatar
      Ensure that plpgsql cleans up cleanly during parallel-worker exit. · 86e5badd
      Tom Lane authored
      plpgsql_xact_cb ought to treat events XACT_EVENT_PARALLEL_COMMIT and
      XACT_EVENT_PARALLEL_ABORT like XACT_EVENT_COMMIT and XACT_EVENT_ABORT
      respectively, since its goal is to do process-local cleanup.  This
      oversight caused plpgsql's end-of-transaction cleanup to not get done
      in parallel workers.  Since a parallel worker will exit just after the
      transaction cleanup, the effects of this are limited.  I couldn't find
      any case in the core code with user-visible effects, but perhaps there
      are some in extensions.  In any case it's wrong, so let's fix it before
      it bites us not after.
      
      In passing, add some comments around the handling of expression
      evaluation resources in DO blocks.  There's no live bug there, but it's
      quite unobvious what's happening; at least I thought so.  This isn't
      related to the other issue, except that I found both things while poking
      at expression-evaluation performance.
      
      Back-patch the plpgsql_xact_cb fix to 9.5 where those event types
      were introduced, and the DO-block commentary to v11 where DO blocks
      gained the ability to issue COMMIT/ROLLBACK.
      
      Discussion: https://postgr.es/m/[email protected]
      86e5badd
    • Magnus Hagander's avatar
      Document that pg_checksums exists in checksums README · eff5b245
      Magnus Hagander authored
      Author: Daniel Gustafsson <[email protected]>
      eff5b245
    • Peter Eisentraut's avatar
      Drop slot's LWLock before returning from SaveSlotToPath() · 49bf8153
      Peter Eisentraut authored
      When SaveSlotToPath() is called with elevel=LOG, the early exits didn't
      release the slot's io_in_progress_lock.
      
      This could result in a walsender being stuck on the lock forever.  A
      possible way to get into this situation is if the offending code paths
      are triggered in a low disk space situation.
      
      Author: Pavan Deolasee <[email protected]>
      Reported-by: default avatarCraig Ringer <[email protected]>
      Discussion: https://www.postgresql.org/message-id/flat/56a138c5-de61-f553-7e8f-6789296de785%402ndquadrant.com
      49bf8153
    • Tom Lane's avatar
      Further fixes for ssl_passphrase_callback test module. · 958aa438
      Tom Lane authored
      The Makefile should set TAP_TESTS = 1, not implement the infrastructure
      for itself.  For one thing, it missed the appropriate "make clean"
      steps.  For another, the buildfarm isn't running this test because
      it wasn't hooked into "make installcheck" either.
      958aa438
    • Andrew Dunstan's avatar
      Don't listen to localhost in ssl_passphrase_callback test · e984fb34
      Andrew Dunstan authored
      Commit 896fcdb2 contained an unnecessary setting that listened to
      localhost. Since the test doesn't actually try to make an SSL connection
      to the database this isn't required. Moreover, it's a security hole.
      
      Per gripe from Tom Lane.
      e984fb34
  4. 25 Mar, 2020 11 commits
  5. 24 Mar, 2020 8 commits
    • Peter Geoghegan's avatar
      Fix nbtree deduplication README commentary. · b150a767
      Peter Geoghegan authored
      Descriptions of some aspects of how deduplication works were unclear in
      a couple of places.
      b150a767
    • Andres Freund's avatar
      logical decoding: Remove TODO about unnecessary optimization. · 112b006f
      Andres Freund authored
      Measurements show, and intuition agrees, that there's currently no
      known cases where adding a fastpath to avoid allocating / ordering a
      heap for a single transaction is worthwhile.
      
      Author: Dilip Kumar
      Discussion: https://postgr.es/m/[email protected]om
      112b006f
    • Peter Eisentraut's avatar
      Fix compiler warning on Cygwin · f15ace79
      Peter Eisentraut authored
      bf68b79e introduced an unused variable
      compiler warning on Cygwin.
      f15ace79
    • Tom Lane's avatar
      Improve the internal implementation of ereport(). · 17a28b03
      Tom Lane authored
      Change all the auxiliary error-reporting routines to return void,
      now that we no longer need to pretend they are passing something
      useful to errfinish().  While this probably doesn't save anything
      significant at the machine-code level, it allows detection of some
      additional types of mistakes.
      
      Pass the error location details (__FILE__, __LINE__, PG_FUNCNAME_MACRO)
      to errfinish not errstart.  This shaves a few cycles off the case where
      errstart decides we're not going to emit anything.
      
      Re-implement elog() as a trivial wrapper around ereport(), removing
      the separate support infrastructure it used to have.  Aside from
      getting rid of some now-surplus code, this means that elog() now
      really does have exactly the same semantics as ereport(), in particular
      that it can skip evaluation work if the message is not to be emitted.
      
      Andres Freund and Tom Lane
      
      Discussion: https://postgr.es/m/[email protected]om
      17a28b03
    • Tom Lane's avatar
      Re-implement the ereport() macro using __VA_ARGS__. · e3a87b49
      Tom Lane authored
      Now that we require C99, we can depend on __VA_ARGS__ to work, and
      revising ereport() to use it has several significant benefits:
      
      * The extra parentheses around the auxiliary function calls are now
      optional.  Aside from being a bit less ugly, this removes a common
      gotcha for new contributors, because in some cases the compiler errors
      you got from forgetting them were unintelligible.
      
      * The auxiliary function calls are now evaluated as a comma expression
      list rather than as extra arguments to errfinish().  This means that
      compilers can be expected to warn about no-op expressions in the list,
      allowing detection of several other common mistakes such as forgetting
      to add errmsg(...) when converting an elog() call to ereport().
      
      * Unlike the situation with extra function arguments, comma expressions
      are guaranteed to be evaluated left-to-right, so this removes platform
      dependency in the order of the auxiliary function calls.  While that
      dependency hasn't caused us big problems in the past, this change does
      allow dropping some rather shaky assumptions around errcontext() domain
      handling.
      
      There's no intention to make wholesale changes of existing ereport
      calls, but as proof-of-concept this patch removes the extra parens
      from a couple of calls in postgres.c.
      
      While new code can be written either way, code intended to be
      back-patched will need to use extra parens for awhile yet.  It seems
      worth back-patching this change into v12, so as to reduce the window
      where we have to be careful about that by one year.  Hence, this patch
      is careful to preserve ABI compatibility; a followup HEAD-only patch
      will make some additional simplifications.
      
      Andres Freund and Tom Lane
      
      Discussion: https://postgr.es/m/[email protected]om
      e3a87b49
    • Peter Eisentraut's avatar
      Fix compiler warning · cef27ae0
      Peter Eisentraut authored
      A variable was unused in non-assert builds.  Simplify the code to
      avoid the issue.
      Reported-by: default avatarErik Rijkers <[email protected]>
      cef27ae0
    • Tom Lane's avatar
      Doc: fix broken markup. · ab3e4fbd
      Tom Lane authored
      Sloppiness in commit cedffbdb, noted by Erikjan Rijkers.
      (It's fairly unfortunate that xmllint doesn't catch this.)
      
      Discussion: https://postgr.es/m/[email protected]
      ab3e4fbd
    • Peter Eisentraut's avatar
      Some refactoring of logical/worker.c · 97ee604d
      Peter Eisentraut authored
      This moves the main operations of apply_handle_{insert|update|delete},
      that of inserting, updating, deleting a tuple into/from a given
      relation, into corresponding
      apply_handle_{insert|update|delete}_internal functions.  This allows
      performing those operations on relations that are not directly the
      targets of replication, which is something a later patch will use for
      targeting partitioned tables.
      
      Author: Amit Langote <[email protected]>
      Reviewed-by: default avatarRafia Sabih <[email protected]>
      Reviewed-by: default avatarPeter Eisentraut <[email protected]>
      Discussion: https://www.postgresql.org/message-id/flat/[email protected]om
      97ee604d