This project is mirrored from git://git.postgresql.org/git/postgresql.git. Updated .
  1. 31 Mar, 2020 6 commits
  2. 30 Mar, 2020 16 commits
    • Michael Paquier's avatar
      Revert "Skip redundant anti-wraparound vacuums" · dd9ac7d5
      Michael Paquier authored
      This reverts commit 2aa6e331, that added a fast path to skip
      anti-wraparound and non-aggressive autovacuum jobs (these have no sense
      as anti-wraparound implies aggressive).  With a cluster using a high
      amount of relations with a portion of them being heavily updated, this
      could cause autovacuum to lock down, with autovacuum workers attempting
      repeatedly those jobs on the same relations for the same database, that
      just kept being skipped.  This lock down can be solved with a manual
      VACUUM FREEZE.
      
      Justin King has reported one environment where the issue happened, and
      Julien Rouhaud and I have been able to reproduce it in a second
      environment.  With a very aggressive autovacuum_freeze_max_age,
      triggering those jobs with pgbench is a matter of minutes, and hitting
      the lock down is a lot harder (my local tests failed to do that).
      
      Note that anti-wraparound and non-aggressive jobs can only be triggered
      on a subset of shared catalogs:
      - pg_auth_members
      - pg_authid
      - pg_database
      - pg_replication_origin
      - pg_shseclabel
      - pg_subscription
      - pg_tablespace
      While the lock down was possible down to v12, the root cause of those
      jobs is a much older issue, which needs more analysis.
      
      Bonus thanks to Andres Freund for the discussion.
      
      Reported-by: Justin King
      Discussion: https://postgr.es/m/[email protected]om
      Backpatch-through: 12
      dd9ac7d5
    • Peter Geoghegan's avatar
      Refactor nbtree high key truncation. · 7c2dbc69
      Peter Geoghegan authored
      Simplify _bt_truncate(), the routine that generates truncated leaf page
      high keys.  Remove a micro-optimization that avoided a second palloc0()
      call (this was used when a heap TID was needed in the final pivot tuple,
      though only when the index happened to not be an INCLUDE index).
      
      Removing this dubious micro-optimization allows _bt_truncate() to use
      the index_truncate_tuple() indextuple.c utility routine in all cases.
      This was already the common case.
      
      This commit is a HEAD-only follow up to bugfix commit 4b42a899.
      7c2dbc69
    • Andres Freund's avatar
      Deduplicate PageIsNew() check in lazy_scan_heap(). · d4b34f60
      Andres Freund authored
      The recheck isn't needed anymore, as RelationGetBufferForTuple() now
      extends the relation with RBM_ZERO_AND_LOCK. Previously we needed to
      handle the fact that relation extension extended the relation and then
      separately acquired a lock on the page - while expecting that the page
      is empty.
      
      Reported-By: Ranier Vilela
      Discussion: https://postgr.es/m/[email protected]om
      d4b34f60
    • Alexander Korotkov's avatar
      Fix missing SP-GiST support in 911e7020 · 364bdd0b
      Alexander Korotkov authored
      911e7020 misses setting of amoptsprocnum for SP-GiST.  This commit fixes
      that.
      364bdd0b
    • Alexander Korotkov's avatar
      Remove rudiments of supporting procnum == 0 from 911e7020 · 851b14b0
      Alexander Korotkov authored
      Early versions of opclass options patch uses zero support procedure as opclass
      options procedure.  This commit removes rudiments of it, which were committed
      in 911e7020.  Also, it implements correct handling of amoptsprocnum == 0.
      851b14b0
    • Peter Geoghegan's avatar
      Consistently truncate non-key suffix columns. · 4b42a899
      Peter Geoghegan authored
      INCLUDE indexes failed to have their non-key attributes physically
      truncated away in certain rare cases.  This led to physically larger
      pivot tuples that contained useless non-key attribute values.  The
      impact on users should be negligible, but this is still clearly a
      regression (Postgres 11 supports INCLUDE indexes, and yet was not
      affected).
      
      The bug appeared in commit dd299df8, which introduced "true" suffix
      truncation of key attributes.
      
      Discussion: https://postgr.es/m/[email protected]om
      Backpatch: 12-, where "true" suffix truncation was introduced.
      4b42a899
    • Alexander Korotkov's avatar
      Implement operator class parameters · 911e7020
      Alexander Korotkov authored
      PostgreSQL provides set of template index access methods, where opclasses have
      much freedom in the semantics of indexing.  These index AMs are GiST, GIN,
      SP-GiST and BRIN.  There opclasses define representation of keys, operations on
      them and supported search strategies.  So, it's natural that opclasses may be
      faced some tradeoffs, which require user-side decision.  This commit implements
      opclass parameters allowing users to set some values, which tell opclass how to
      index the particular dataset.
      
      This commit doesn't introduce new storage in system catalog.  Instead it uses
      pg_attribute.attoptions, which is used for table column storage options but
      unused for index attributes.
      
      In order to evade changing signature of each opclass support function, we
      implement unified way to pass options to opclass support functions.  Options
      are set to fn_expr as the constant bytea expression.  It's possible due to the
      fact that opclass support functions are executed outside of expressions, so
      fn_expr is unused for them.
      
      This commit comes with some examples of opclass options usage.  We parametrize
      signature length in GiST.  That applies to multiple opclasses: tsvector_ops,
      gist__intbig_ops, gist_ltree_ops, gist__ltree_ops, gist_trgm_ops and
      gist_hstore_ops.  Also we parametrize maximum number of integer ranges for
      gist__int_ops.  However, the main future usage of this feature is expected
      to be json, where users would be able to specify which way to index particular
      json parts.
      
      Catversion is bumped.
      
      Discussion: https://postgr.es/m/d22c3a18-31c7-1879-fc11-4c1ce2f5e5af%40postgrespro.ru
      Author: Nikita Glukhov, revised by me
      Reviwed-by: Nikolay Shaplov, Robert Haas, Tom Lane, Tomas Vondra, Alvaro Herrera
      911e7020
    • Peter Eisentraut's avatar
      Allow using Unix-domain sockets on Windows in tests · 1d53432f
      Peter Eisentraut authored
      The test suites currently don't use Unix-domain sockets on Windows.
      This optionally allows enabling that by setting the environment
      variable PG_TEST_USE_UNIX_SOCKETS.
      
      This should currently be considered experimental.  In particular,
      pg_regress.c contains some comments that the cleanup code for
      Unix-domain sockets doesn't work correctly under Windows, which hasn't
      been an problem until now.  But it's good enough for locally
      supervised testing of the functionality.
      Reviewed-by: default avatarAndrew Dunstan <[email protected]>
      Discussion: https://www.postgresql.org/message-id/flat/[email protected]
      1d53432f
    • Tom Lane's avatar
      Be more careful about extracting encoding from locale strings on Windows. · 8c49454c
      Tom Lane authored
      GetLocaleInfoEx() can fail on strings that setlocale() was perfectly
      happy with.  A common way for that to happen is if the locale string
      is actually a Unix-style string, say "et_EE.UTF-8".  In that case,
      what's after the dot is an encoding name, not a Windows codepage number;
      blindly treating it as a codepage number led to failure, with a fairly
      silly error message.  Hence, check to see if what's after the dot is
      all digits, and if not, treat it as a literal encoding name rather than
      a codepage number.  This will do the right thing with many Unix-style
      locale strings, and produce a more sensible error message otherwise.
      
      Somewhat independently of that, treat a zero (CP_ACP) result from
      GetLocaleInfoEx() as meaning that we must use UTF-8 encoding.
      
      Back-patch to all supported branches.
      
      Juan José Santamaría Flecha
      
      Discussion: https://postgr.es/m/[email protected]
      8c49454c
    • David Rowley's avatar
      Attempt to fix unstable regression tests, take 2 · 24566b35
      David Rowley authored
      Following up on 2dc16efe, petalura has suffered some additional
      failures in stats_ext which again appear to be around the timing of an
      autovacuum during the test, causing instability in the row estimates.
      
      Again, let's fix this by explicitly performing a VACUUM on the table
      and not leave it to happen by chance of an autovacuum pass.
      
      Discussion: https://postgr.es/m/CAApHDvok5hmXr%2BbUbJe7%2B2sQzWo4B_QzSk7RKFR9fP6BjYXx5g%40mail.gmail.com
      24566b35
    • Fujii Masao's avatar
      Report waiting via PS while recovery is waiting for buffer pin in hot standby. · 64638ccb
      Fujii Masao authored
      Previously while the startup process was waiting for the recovery conflict
      with snapshot, tablespace or lock to be resolved, waiting was reported in
      PS display, but not in the case of recovery conflict with buffer pin.
      This commit makes the startup process in hot standby report waiting via PS
      while waiting for the conflicts with other backends holding buffer pins to
      be resolved.
      
      Author: Masahiko Sawada
      Reviewed-by: Fujii Masao
      Discussion: https://postgr.es/m/[email protected]om
      64638ccb
    • Peter Eisentraut's avatar
      Improve handling of parameter differences in physical replication · 246f136e
      Peter Eisentraut authored
      When certain parameters are changed on a physical replication primary,
      this is communicated to standbys using the XLOG_PARAMETER_CHANGE WAL
      record.  The standby then checks whether its own settings are at least
      as big as the ones on the primary.  If not, the standby shuts down
      with a fatal error.
      
      The correspondence of settings between primary and standby is required
      because those settings influence certain shared memory sizings that
      are required for processing WAL records that the primary might send.
      For example, if the primary sends a prepared transaction, the standby
      must have had max_prepared_transaction set appropriately or it won't
      be able to process those WAL records.
      
      However, fatally shutting down the standby immediately upon receipt of
      the parameter change record might be a bit of an overreaction.  The
      resources related to those settings are not required immediately at
      that point, and might never be required if the activity on the primary
      does not exhaust all those resources.  If we just let the standby roll
      on with recovery, it will eventually produce an appropriate error when
      those resources are used.
      
      So this patch relaxes this a bit.  Upon receipt of
      XLOG_PARAMETER_CHANGE, we still check the settings but only issue a
      warning and set a global flag if there is a problem.  Then when we
      actually hit the resource issue and the flag was set, we issue another
      warning message with relevant information.  At that point we pause
      recovery, so a hot standby remains usable.  We also repeat the last
      warning message once a minute so it is harder to miss or ignore.
      Reviewed-by: default avatarSergei Kornilov <[email protected]>
      Reviewed-by: default avatarMasahiko Sawada <[email protected]>
      Reviewed-by: default avatarKyotaro Horiguchi <[email protected]>
      Discussion: https://www.postgresql.org/message-id/flat/[email protected]
      246f136e
    • Peter Eisentraut's avatar
      a01e1b8b
    • Fujii Masao's avatar
      Allow the planner-related functions and hook to accept the query string. · 6aba63ef
      Fujii Masao authored
      This commit adds query_string argument into the planner-related functions
      and hook and allows us to pass the query string to them.
      
      Currently there is no user of the query string passed. But the upcoming patch
      for the planning counters will add the planning hook function into
      pg_stat_statements and the function will need the query string. So this change
      will be necessary for that patch.
      
      Also this change is useful for some extensions that want to use the query
      string in their planner hook function.
      
      Author: Pascal Legrand, Julien Rouhaud
      Reviewed-by: Yoshikazu Imai, Tom Lane, Fujii Masao
      Discussion: https://postgr.es/m/[email protected]om
      Discussion: https://postgr.es/m/[email protected]
      6aba63ef
    • Fujii Masao's avatar
      Expose BufferUsageAccumDiff(). · 4a539a25
      Fujii Masao authored
      Previously pg_stat_statements calculated the difference of buffer counters
      by its own code even while BufferUsageAccumDiff() had the same code.
      This commit expose BufferUsageAccumDiff() and makes pg_stat_statements
      use it for the calculation, in order to simply the code.
      
      This change also would be useful for the upcoming patch for the planning
      counters in pg_stat_statements because the patch will add one more code
      for the calculation of difference of buffer counters and that can easily be
      done by using BufferUsageAccumDiff().
      
      Author: Julien Rouhaud
      Reviewed-by: Fujii Masao
      Discussion: https://postgr.es/m/[email protected]
      4a539a25
    • Amit Kapila's avatar
      Introduce vacuum errcontext to display additional information. · b61d161c
      Amit Kapila authored
      The additional information displayed will be block number for error
      occurring while processing heap and index name for error occurring
      while processing the index.
      
      This will help us in diagnosing the problems that occur during a vacuum.
      For ex. due to corruption (either caused by bad hardware or by some bug)
      if we get some error while vacuuming, it can help us identify the block
      in heap and or additional index information.
      
      It sets up an error context callback to display additional information
      with the error.  During different phases of vacuum (heap scan, heap
      vacuum, index vacuum, index clean up, heap truncate), we update the error
      context callback to display appropriate information.  We can extend it to
      a bit more granular level like adding the phases for FSM operations or for
      prefetching the blocks while truncating. However, I felt that it requires
      adding many more error callback function calls and can make the code a bit
      complex, so left those for now.
      
      Author: Justin Pryzby, with few changes by Amit Kapila
      Reviewed-by: Alvaro Herrera, Amit Kapila, Andres Freund, Michael Paquier
      and Sawada Masahiko
      Discussion: https://www.postgresql.org/message-id/[email protected]
      b61d161c
  3. 29 Mar, 2020 7 commits
    • Tom Lane's avatar
      Cosmetic improvements in ltree code. · 2743d9ae
      Tom Lane authored
      Add more comments in ltree.h, and correct a misstatement or two.
      
      Use a symbol, rather than hardwired constants, for the maximum length
      of an ltree label.  The max length is still hardwired in the associated
      error messages, but I want to clean that up as part of a separate patch
      to improve the error messages.
      2743d9ae
    • Tom Lane's avatar
      Doc: correct misstatement about ltree label maximum length. · 122b0ccf
      Tom Lane authored
      The documentation says that the max length is 255 bytes, but
      code inspection says it's actually 255 characters; and relevant
      lengths are stored as uint16 so that that works.
      122b0ccf
    • Peter Eisentraut's avatar
      Document color support · e1ff7804
      Peter Eisentraut authored
      Add a documentation appendix that explains the PG_COLOR and PG_COLORS
      environment variables.
      
      Discussion: https://www.postgresql.org/message-id/flat/[email protected]
      e1ff7804
    • Peter Eisentraut's avatar
      pg_regress: Observe TMPDIR · 9cedb166
      Peter Eisentraut authored
      Put the temporary socket directory under TMPDIR, if that environment
      variable is set, instead of the hardcoded /tmp.
      
      This allows running the tests if there is no /tmp at all (for example
      on Windows, although running the tests with Unix-domain sockets is not
      enabled on Windows yet).  We also use TMPDIR everywhere else /tmp is
      hardcoded, so this makes the behavior consistent.
      Reviewed-by: default avatarAndrew Dunstan <[email protected]>
      Discussion: https://www.postgresql.org/message-id/flat/[email protected]
      9cedb166
    • Peter Eisentraut's avatar
      Update SQL features · b79911dc
      Peter Eisentraut authored
      Change F181 to supported.  It requires that an embedded C program can
      be split across multiple files, which ECPG easily supports.
      b79911dc
    • David Rowley's avatar
      Attempt to fix unstable regression tests · 2dc16efe
      David Rowley authored
      b07642db added code to trigger autovacuums based on the number of
      inserts into a table. This seems to have caused some regression test
      results to destabilize. I suspect this is due to autovacuum triggering a
      vacuum sometime after the test's ANALYZE run and perhaps reltuples is
      ending up being set to a slightly different value as a result.
      
      Attempt to resolve this by running a VACUUM ANALYZE on the affected table
      instead of just ANALYZE. pg_class.reltuples will still get set to whatever
      ANALYZE chooses but we should no longer get the proceeding autovacuum
      overriding that.
      
      The overhead this adds to each test's runtime seems small enough not to
      worry about. I measure 3-4% on stats_ext and can't measure any change in
      partition_aggregate.
      
      I'm unable to recreate the issue locally, so this is a bit of a blind
      fix.
      
      Discussion: https://postgr.es/m/CAApHDvpWmpqYrKwwDQyeDq8dAyK7GMNaxDhrG69CkSuXoEg%2BVg%40mail.gmail.com
      2dc16efe
    • Peter Geoghegan's avatar
      Make deduplication use number of key attributes. · a7b9d24e
      Peter Geoghegan authored
      Use IndexRelationGetNumberOfKeyAttributes() rather than
      IndexRelationGetNumberOfAttributes() when determining whether or not two
      index tuples are suitable for merging together into a single posting
      list tuple.  This is a little bit tidier.  It brings affected code in
      nbtdedup.c a little closer to similar, related code in nbtsplitloc.c.
      a7b9d24e
  4. 28 Mar, 2020 9 commits
    • Tom Lane's avatar
      Fix lquery's behavior for consecutive '*' items. · 9950c8aa
      Tom Lane authored
      Something like "*{2}.*{3}" should presumably mean the same as
      "*{5}", but it didn't.  Improve that.
      
      Get rid of an undocumented and remarkably ugly (though not, as far as
      I can tell, actually unsafe) static variable in favor of passing more
      arguments to checkCond().
      
      Reverse-engineer some commentary.  This function, like all of ltree,
      is still far short of what I would consider the minimum acceptable
      level of internal documentation, but at least now it has more than
      zero comments.
      
      Although this certainly seems like a bug fix, people might not
      thank us for changing query behavior in stable branches, so
      no back-patch.
      
      Nikita Glukhov, with cosmetic improvements by me
      
      Discussion: https://postgr.es/m/[email protected]om
      9950c8aa
    • Tom Lane's avatar
      Protect against overflow of ltree.numlevel and lquery.numlevel. · 95f7ddfd
      Tom Lane authored
      These uint16 fields could be overflowed by excessively long input,
      producing strange results.  Complain for invalid input.
      
      Likewise check for out-of-range values of the repeat counts in lquery.
      (We don't try too hard on that one, notably not bothering to detect
      if atoi's result has overflowed.)
      
      Also detect length overflow in ltree_concat.
      
      In passing, be more consistent about whether "syntax error" messages
      include the type name.  Also, clarify the documentation about what
      the size limit is.
      
      This has been broken for a long time, so back-patch to all supported
      branches.
      
      Nikita Glukhov, reviewed by Benjie Gillam and Tomas Vondra
      
      Discussion: https://postgr.es/m/[email protected]om
      95f7ddfd
    • Andres Freund's avatar
      Ensure snapshot is registered within ScanPgRelation(). · 42750b08
      Andres Freund authored
      In 9.4 I added support to use a historical snapshot in
      ScanPgRelation(), while adding logical decoding. Unfortunately a
      conflict with the concurrent removal of SnapshotNow was incorrectly
      resolved, leading to an unregistered snapshot being used.
      
      It is not correct to use an unregistered (or non-active) snapshot for
      anything non-trivial, because catalog invalidations can cause the
      snapshot to be invalidated.
      
      Luckily it seems unlikely to actively cause problems in practice, as
      ScanPgRelation() requires that we already have a lock on the relation,
      we only look for a single row, and we don't appear to rely on the
      result's tid to be correct. It however is clearly wrong and potential
      negative consequences would likely be hard to find. So it seems worth
      backpatching the fix, even without a concrete hazard.
      
      Discussion: https://postgr.es/m/[email protected]
      Backpatch: 9.5-
      42750b08
    • Jeff Davis's avatar
      Fix costing for disk-based hash aggregation. · 7351bfed
      Jeff Davis authored
      Report and suggestions from Richard Guo and Tomas Vondra.
      
      Discussion: https://postgr.es/m/[email protected]om
      7351bfed
    • 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: default 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
  5. 27 Mar, 2020 2 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