OSDN Git Service
Tom Lane [Thu, 13 Mar 2008 19:21:43 +0000 (19:21 +0000)]
Update to tzdata 2008a distribution (Chilean DST law change).
Tom Lane [Thu, 13 Mar 2008 18:31:56 +0000 (18:31 +0000)]
Fix varstr_cmp's special case for UTF8 encoding on Windows so that strings
that are reported as "equal" by wcscoll() are checked to see if they really
are bitwise equal, and are sorted per strcmp() if not. We made this happen
a couple of years ago in the regular code path, but it unaccountably got
left out of the Windows/UTF8 case (probably brain fade on my part at the
time). As in the prior set of changes, affected users may need to reindex
indexes on textual columns.
Backpatch as far as 8.2, which is the oldest release we are still supporting
on Windows.
Tom Lane [Thu, 13 Mar 2008 18:00:32 +0000 (18:00 +0000)]
Fix heap_page_prune's problem with failing to send cache invalidation
messages if the calling transaction aborts later on. Collapsing out line
pointer redirects is a done deal as soon as we complete the page update,
so syscache *must* be notified even if the VACUUM FULL as a whole doesn't
complete. To fix, add some functionality to inval.c to allow the pending
inval messages to be sent immediately while heap_page_prune is still
running. The implementation is a bit chintzy: it will only work in the
context of VACUUM FULL. But that's all we need now, and it can always be
extended later if needed. Per my trouble report of a week ago.
Bruce Momjian [Thu, 13 Mar 2008 01:56:46 +0000 (01:56 +0000)]
Add URL for:
* Do async I/O for faster random read-ahead of data
Async I/O allows multiple I/O requests to be sent to the disk with
results coming back asynchronously.
> http://archives.postgresql.org/pgsql-performance/2007-09/msg00255.php
Tom Lane [Wed, 12 Mar 2008 23:58:27 +0000 (23:58 +0000)]
Fix pg_plan_queries() to restore the previous setting of ActiveSnapshot
(probably NULL) before exiting. Up to now it's just left the variable as it
set it, which means that after we're done processing the current client
message, ActiveSnapshot is probably pointing at garbage (because this function
is typically run in MessageContext which will get reset). There doesn't seem
to have been any code path in which that mattered before 8.3, but now the
plancache module might try to use the stale value if the next client message
is a Bind for a prepared statement that is in need of replanning. Per report
from Alex Hunsaker.
Bruce Momjian [Wed, 12 Mar 2008 20:50:22 +0000 (20:50 +0000)]
Add psql TODO:
< * Include the symbolic SQLSTATE name in verbose error reports
<
< http://archives.postgresql.org/pgsql-general/2007-09/msg00438.php
Bruce Momjian [Wed, 12 Mar 2008 20:19:46 +0000 (20:19 +0000)]
Add to TODO:
> * Expire published xmin for read-only and idle transactions
>
> http://archives.postgresql.org/pgsql-hackers/2007-09/msg00343.php
Tom Lane [Wed, 12 Mar 2008 20:11:46 +0000 (20:11 +0000)]
Fix LISTEN/NOTIFY race condition reported by Laurent Birtz, by postponing
pg_listener modifications commanded by LISTEN and UNLISTEN until the end
of the current transaction. This allows us to hold the ExclusiveLock on
pg_listener until after commit, with no greater risk of deadlock than there
was before. Aside from fixing the race condition, this gets rid of a
truly ugly kludge that was there before, namely having to ignore
HeapTupleBeingUpdated failures during NOTIFY. There is a small potential
incompatibility, which is that if a transaction issues LISTEN or UNLISTEN
and then looks into pg_listener before committing, it won't see any resulting
row insertion or deletion, where before it would have. It seems unlikely
that anyone would be depending on that, though.
This patch also disallows LISTEN and UNLISTEN inside a prepared transaction.
That case had some pretty undesirable properties already, such as possibly
allowing pg_listener entries to be made for PIDs no longer present, so
disallowing it seems like a better idea than trying to maintain the behavior.
Bruce Momjian [Wed, 12 Mar 2008 19:41:23 +0000 (19:41 +0000)]
Add:
>
> * Consider a special data type for regular expressions
>
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg01067.php
Bruce Momjian [Wed, 12 Mar 2008 19:40:01 +0000 (19:40 +0000)]
Back out text search change to TODO.
Bruce Momjian [Wed, 12 Mar 2008 19:32:07 +0000 (19:32 +0000)]
Update TODO:
* Add array_accum() and array_to_set() functions for arrays
The standards specify array_agg() and UNNEST.
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00464.php
Bruce Momjian [Wed, 12 Mar 2008 19:30:59 +0000 (19:30 +0000)]
Add URL for:
* Consider a simplified API for full text searches
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg01067.php
Bruce Momjian [Wed, 12 Mar 2008 19:28:03 +0000 (19:28 +0000)]
Add for Win32 TODO:
>
> o Convert MSVC build system to remove most batch files
>
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00961.php
Bruce Momjian [Wed, 12 Mar 2008 19:15:25 +0000 (19:15 +0000)]
Add URL for:
* Add REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY
> http://archives.postgresql.org/pgsql-performance/2007-08/msg00289.php
Bruce Momjian [Wed, 12 Mar 2008 18:33:54 +0000 (18:33 +0000)]
Add for Win32 TODO:
> o Diagnose problem where shared memory can sometimes not be
> attached by postmaster children
>
> http://archives.postgresql.org/pgsql-general/2007-08/msg01377.php
>
Bruce Momjian [Wed, 12 Mar 2008 18:32:29 +0000 (18:32 +0000)]
Add to TODO:
>
> * Remove use of MAKE_PTR and MAKE_OFFSET macros
>
> http://archives.postgresql.org/pgsql-general/2007-08/msg01510.php
Bruce Momjian [Wed, 12 Mar 2008 15:14:50 +0000 (15:14 +0000)]
Add to TODO:
>
> * Add array_accum() and array_to_set() functions for arrays
>
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00464.php
Tom Lane [Wed, 12 Mar 2008 02:18:33 +0000 (02:18 +0000)]
Add a comment explaining one of the ways that pgbench fails to fully
comply with TPC-B. Per Itagaki Takahiro and discussion of bug#3681.
Bruce Momjian [Wed, 12 Mar 2008 01:48:08 +0000 (01:48 +0000)]
Add URL for:
* Improve speed with indexes
For large table adjustments during VACUUM FULL, it is faster to cluster
or reindex rather than update the index. Also, index updates can bloat
the index.
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00307.php
Bruce Momjian [Wed, 12 Mar 2008 01:40:09 +0000 (01:40 +0000)]
Add:
>
> * Allow domains to be cast
>
> http://archives.postgresql.org/pgsql-hackers/2003-06/msg01206.php
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00289.php
Bruce Momjian [Wed, 12 Mar 2008 01:35:00 +0000 (01:35 +0000)]
Add:
>
> * Consider simplifying how memory context resets handle child contexts
>
> http://archives.postgresql.org/pgsql-patches/2007-08/msg00067.php
Bruce Momjian [Wed, 12 Mar 2008 01:21:25 +0000 (01:21 +0000)]
Add URL for:
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php
Bruce Momjian [Wed, 12 Mar 2008 01:08:32 +0000 (01:08 +0000)]
Add URL for:
* Consider increasing NUM_CLOG_BUFFERS
> http://archives.postgresql.org/pgsql-performance/2007-08/msg00024.php
Bruce Momjian [Wed, 12 Mar 2008 01:03:04 +0000 (01:03 +0000)]
Add URL for:
* Consider increasing NUM_CLOG_BUFFERS
>
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00030.php
>
Bruce Momjian [Tue, 11 Mar 2008 23:30:56 +0000 (23:30 +0000)]
Prevent psql \copy from accepting multiple string parameters, e.g.
test=> \copy billing_data from ../BillingSamplePricerFile.csv with csv
header quote as '"' null as 'abc' null as '123'
\copy: parse error at "null"
Per report from Stephen Frost
Bruce Momjian [Tue, 11 Mar 2008 23:17:05 +0000 (23:17 +0000)]
Update TODO:
o Allow COPY in CSV mode to control whether a quoted zero-length
string is treated as NULL
Currently this is always treated as a zero-length string,
which generates an error when loading into an integer column
Bruce Momjian [Tue, 11 Mar 2008 22:47:28 +0000 (22:47 +0000)]
add to TODO COPY:
>
> o Allow COPY in CSV mode to control whether "" is treated as NULL
>
> http://archives.postgresql.org/pgsql-hackers/2007-07/msg00905.php
Bruce Momjian [Tue, 11 Mar 2008 22:31:02 +0000 (22:31 +0000)]
Add to pl/pgsql:
>
> o Improve logic of determining if an identifier is a a
> variable or column name
>
> http://archives.postgresql.org/pgsql-hackers/2007-07/msg00436.php
Bruce Momjian [Tue, 11 Mar 2008 21:25:02 +0000 (21:25 +0000)]
Add:
>
> * Add automated check for invalid C++ source code constructs
>
> http://archives.postgresql.org/pgsql-patches/2007-07/msg00056.php
Bruce Momjian [Tue, 11 Mar 2008 21:13:13 +0000 (21:13 +0000)]
Add:
> * Research use of signals and sleep wake ups
>
> http://archives.postgresql.org/pgsql-hackers/2007-07/msg00003.php
Bruce Momjian [Tue, 11 Mar 2008 21:06:10 +0000 (21:06 +0000)]
Add:
* Test to see if calling PreallocXlogFiles() from the background writer
will help with WAL segment creation latency
http://archives.postgresql.org/pgsql-patches/2007-06/msg00340.php
Bruce Momjian [Tue, 11 Mar 2008 20:46:20 +0000 (20:46 +0000)]
< * Consider adding buffers the BGW finds reusable to the free list
> * Consider adding buffers the background writer finds reusable to the
> free list
>
> * Consider wither increasing BM_MAX_USAGE_COUNT improves performance
>
> http://archives.postgresql.org/pgsql-hackers/2007-06/msg01007.php
Bruce Momjian [Tue, 11 Mar 2008 20:28:13 +0000 (20:28 +0000)]
Add URL for:
* Reduce storage space for small NUMERICs
> http://archives.postgresql.org/pgsql-hackers/2007-06/msg00715.php
Tom Lane [Tue, 11 Mar 2008 20:20:35 +0000 (20:20 +0000)]
Make TransactionIdIsInProgress check transam.c's single-item XID status cache
before it goes groveling through the ProcArray. In situations where the same
recently-committed transaction ID is checked repeatedly by tqual.c, this saves
a lot of shared-memory searches. And it's cheap enough that it shouldn't
hurt noticeably when it doesn't help.
Concept and patch by Simon, some minor tweaking and comment-cleanup by Tom.
Bruce Momjian [Tue, 11 Mar 2008 20:16:40 +0000 (20:16 +0000)]
Add:
> o Consider normalizing fractions in postgresql.conf, perhaps
> using '%'
>
> http://archives.postgresql.org/pgsql-hackers/2007-06/msg00550.php
Bruce Momjian [Tue, 11 Mar 2008 20:04:56 +0000 (20:04 +0000)]
Add:
> * Consider sorting writes during checkpoint
>
> http://archives.postgresql.org/pgsql-hackers/2007-06/msg00541.php
Bruce Momjian [Tue, 11 Mar 2008 19:48:17 +0000 (19:48 +0000)]
Add:
>
> * Prefix command-line utilities like createuser with 'pg_'
>
> http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php
Bruce Momjian [Tue, 11 Mar 2008 19:44:39 +0000 (19:44 +0000)]
Add:
>
> * Change memory allocation for multi-byte functions so memory is
> allocated inside conversion functions
>
> Currently we preallocate memory based on worst-case usage.
Bruce Momjian [Tue, 11 Mar 2008 19:34:12 +0000 (19:34 +0000)]
Add another URL for:
* Consider increasing the number of default statistics target, and
reduce statistics target overhead
Also consider having a larger statistics target for indexed columns
and expression indexes
<
> http://archives.postgresql.org/pgsql-general/2007-06/msg00542.php
Bruce Momjian [Tue, 11 Mar 2008 19:32:03 +0000 (19:32 +0000)]
Add URL for:
* Consider increasing the number of default statistics target, and
reduce statistics target overhead
Also consider having a larger statistics target for indexed columns
and expression indexes
> http://archives.postgresql.org/pgsql-general/2007-05/msg01228.php
>
Bruce Momjian [Tue, 11 Mar 2008 18:30:50 +0000 (18:30 +0000)]
Add:
>
> * Consider increasing the number of default statistics target, and
> reduce statistics target overhead
>
> Also consider having a larger statistics target for indexed columns
> and expression indexes
Bruce Momjian [Tue, 11 Mar 2008 18:14:57 +0000 (18:14 +0000)]
Add:
>
> * Consider using a hash for joining to a large IN (VALUES ...) list
>
> http://archives.postgresql.org/pgsql-hackers/2007-05/msg00450.php
Bruce Momjian [Tue, 11 Mar 2008 18:05:10 +0000 (18:05 +0000)]
Add for VACUUM:
>
> * Consider a more compact data representation for dead tuples
>
> http://archives.postgresql.org/pgsql-patches/2007-05/msg00143.php
Bruce Momjian [Tue, 11 Mar 2008 18:01:04 +0000 (18:01 +0000)]
Add URL for:
* Fix problem when multiple subtransactions of the same outer transaction
hold different types of locks, and one subtransaction aborts
> http://archives.postgresql.org/pgsql-hackers/2007-05/msg00773.php
Bruce Momjian [Tue, 11 Mar 2008 17:42:13 +0000 (17:42 +0000)]
Add:
>
> * Add temporal versions of generate_series()
>
> http://archives.postgresql.org/pgsql-hackers/2007-04/msg01180.php
Bruce Momjian [Tue, 11 Mar 2008 17:20:47 +0000 (17:20 +0000)]
Add for pl/pgsql:
o Review handling of MOVE and FETCH
http://archives.postgresql.org/pgsql-patches/2007-04/msg00527.php
Bruce Momjian [Tue, 11 Mar 2008 17:02:33 +0000 (17:02 +0000)]
Add:
> o Prevent pg_dump/pg_restore from being affected by
> statement_timeout
>
> Using psql to restore a pg_dump dump is also affected.
Bruce Momjian [Tue, 11 Mar 2008 16:59:00 +0000 (16:59 +0000)]
Add to documentation:
+ Setting <varname>statement_timeout</> in
+ <filename>postgresql.conf</> is not recommended because it
+ affects all sessions.
Backpatch to 8.3.X.
Bruce Momjian [Tue, 11 Mar 2008 15:52:02 +0000 (15:52 +0000)]
Add:
> * Allow statistics last vacuum/analyze execution times to be displayed
> without requiring stats_row_level to be enabled
Bruce Momjian [Tue, 11 Mar 2008 15:49:46 +0000 (15:49 +0000)]
Back out doc addition that statement_timeout affects autovacuum.
Bruce Momjian [Tue, 11 Mar 2008 15:48:50 +0000 (15:48 +0000)]
Remove item, per Alvaro:
<
< o Set up autovacuum to ignore statement_timeout set in
< postgresql.conf
<
< http://archives.postgresql.org/pgsql-hackers/2007-03/msg01753.php
Bruce Momjian [Tue, 11 Mar 2008 15:45:05 +0000 (15:45 +0000)]
Add:
>
> * Consider adding buffers the BGW finds reusable to the free list
>
> http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php
>
> * Automatically tune bgwriter_delay based on activity rather then using a
> fixed interval
>
> http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php
Bruce Momjian [Tue, 11 Mar 2008 15:39:23 +0000 (15:39 +0000)]
Document that statement_timeout is not recommended in postgresql.conf
because it affects all sessions, including autovacuum.
Bruce Momjian [Tue, 11 Mar 2008 15:38:54 +0000 (15:38 +0000)]
Add:
>
> o Set up autovacuum to ignore statement_timeout set in
> postgresql.conf
>
> http://archives.postgresql.org/pgsql-hackers/2007-03/msg01753.php
Bruce Momjian [Tue, 11 Mar 2008 14:56:52 +0000 (14:56 +0000)]
Add URL for:
* Optimize referential integrity checks
> http://archives.postgresql.org/pgsql-hackers/2007-04/msg00744.php
Bruce Momjian [Tue, 11 Mar 2008 14:25:51 +0000 (14:25 +0000)]
Add URL for:
* Allow administrators to safely terminate individual sessions either
via an SQL function or SIGTERM
> http://archives.postgresql.org/pgsql-hackers/2007-04/msg00218.php
Tom Lane [Mon, 10 Mar 2008 21:50:16 +0000 (21:50 +0000)]
Use -fwrapv in CFLAGS if we are using a version of gcc that accepts this flag.
This prevents compiler optimizations that assume overflow won't occur, which
breaks numerous overflow tests that we need to have working. It is known
that gcc 4.3 causes problems and possible that 4.1 does. Per my proposal
of some time ago and a recent report from Kris Jurka.
Backpatch as far as 8.0, which is as far as the patch conveniently goes.
7.x was pretty short of overflow tests anyway, so it may not matter there,
even assuming that anyone cares whether 7.x builds on recent gcc.
Tom Lane [Mon, 10 Mar 2008 20:06:27 +0000 (20:06 +0000)]
Provide a build-time option to store large relations as single files, rather
than dividing them into 1GB segments as has been our longtime practice. This
requires working support for large files in the operating system; at least for
the time being, it won't be the default.
Zdenek Kotala
Bruce Momjian [Mon, 10 Mar 2008 14:16:37 +0000 (14:16 +0000)]
Add:
>
> * Consider increasing the minimum allowed number of shared buffers
>
> http://archives.postgresql.org/pgsql-bugs/2008-02/msg00157.php
>
Magnus Hagander [Mon, 10 Mar 2008 13:53:35 +0000 (13:53 +0000)]
Bump catversion from guc enum patch, which I forgot. Sorry!
Tom Lane [Mon, 10 Mar 2008 12:57:05 +0000 (12:57 +0000)]
Fix unportable coding of new error message, per Kris Jurka.
Magnus Hagander [Mon, 10 Mar 2008 12:55:13 +0000 (12:55 +0000)]
Implement enum type for guc parameters, and convert a couple of existing
variables to it. More need to be converted, but I wanted to get this in
before it conflicts with too much...
Other than just centralising the text-to-int conversion for parameters,
this allows the pg_settings view to contain a list of available options
and allows an error hint to show what values are allowed.
Tom Lane [Mon, 10 Mar 2008 12:39:23 +0000 (12:39 +0000)]
Document and enforce that the usable range of setseed() arguments is
-1 to 1, not 0 to 1. The actual behavior for values within this range
does not change. Kris Jurka
Tom Lane [Mon, 10 Mar 2008 03:37:59 +0000 (03:37 +0000)]
Throw an error for negative LIMIT or OFFSET values, instead of silently
treating them as zero. Simon Riggs
Tom Lane [Mon, 10 Mar 2008 03:22:29 +0000 (03:22 +0000)]
Increase the default value of log_min_messages to WARNING, so that
NOTICE-grade messages are not logged by default. Per pgsql-hackers
discussion back on 21-Nov-2007.
Tom Lane [Mon, 10 Mar 2008 03:01:28 +0000 (03:01 +0000)]
Add a CaseSensitive parameter to synonym dictionaries.
Simon Riggs
Tom Lane [Mon, 10 Mar 2008 02:13:22 +0000 (02:13 +0000)]
Remove no-longer-used XLogCacheByte field of XLogCtl.
Itagaki Takahiro
Tom Lane [Mon, 10 Mar 2008 02:04:10 +0000 (02:04 +0000)]
Reduce memory consumption during VACUUM of large relations, by using
FSMPageData (6 bytes) instead of PageFreeSpaceInfo (8 or 16 bytes)
for the temporary array of page-free-space information.
Itagaki Takahiro
Tom Lane [Mon, 10 Mar 2008 01:23:04 +0000 (01:23 +0000)]
Fix pgbench's getrand() function so that min and max have approximately
the same chance of being selected as do numbers between them. Problem
noted by Greg Stark; fix by Alexey Klyukin.
Tom Lane [Sun, 9 Mar 2008 04:56:28 +0000 (04:56 +0000)]
Remove postc's check that NBuffers is at least twice MaxBackends.
With the addition of multiple autovacuum workers, our choices were to delete
the check, document the interaction with autovacuum_max_workers, or complicate
the check to try to hide that interaction. Since this restriction has never
been adequate to ensure backends can't run out of pinnable buffers, it doesn't
really have enough excuse to live to justify the second or third choices.
Per discussion of a complaint from Andreas Kling (see also bug #3888).
This commit also removes several documentation references to this restriction,
but I'm not sure I got them all.
Tom Lane [Sun, 9 Mar 2008 00:32:09 +0000 (00:32 +0000)]
Change patternsel() so that instead of switching from a pure
pattern-examination heuristic method to purely histogram-driven selectivity at
histogram size 100, we compute both estimates and use a weighted average.
The weight put on the heuristic estimate decreases linearly with histogram
size, dropping to zero for 100 or more histogram entries.
Likewise in ltreeparentsel(). After a patch by Greg Stark, though I
reorganized the logic a bit to give the caller of histogram_selectivity()
more control.
Tom Lane [Sat, 8 Mar 2008 22:41:38 +0000 (22:41 +0000)]
Modify prefix_selectivity() so that it will never estimate the selectivity
of the generated range condition var >= 'foo' AND var < 'fop' as being less
than what eqsel() would estimate for var = 'foo'. This is intuitively
reasonable and it gets rid of the need for some entirely ad-hoc coding we
formerly used to reject bogus estimates. The basic problem here is that
if the prefix is more than a few characters long, the two boundary values
are too close together to be distinguishable by comparison to the column
histogram, resulting in a selectivity estimate of zero, which is often
not very sane. Change motivated by an example from Peter Eisentraut.
Arguably this is a bug fix, but I'll refrain from back-patching it
for the moment.
Tom Lane [Sat, 8 Mar 2008 21:57:59 +0000 (21:57 +0000)]
Refactor heap_page_prune so that instead of changing item states on-the-fly,
it accumulates the set of changes to be made and then applies them. It had
to accumulate the set of changes anyway to prepare a WAL record for the
pruning action, so this isn't an enormous change; the only new complexity is
to not doubly mark tuples that are visited twice in the scan. The main
advantage is that we can substantially reduce the scope of the critical
section in which the changes are applied, thus avoiding PANIC in foreseeable
cases like running out of memory in inval.c. A nice secondary advantage is
that it is now far clearer that WAL replay will actually do the same thing
that the original pruning did.
This commit doesn't do anything about the open problem that
CacheInvalidateHeapTuple doesn't have the right semantics for a CTID change
caused by collapsing out a redirect pointer. But whatever we do about that,
it'll be a good idea to not do it inside a critical section.
Bruce Momjian [Sat, 8 Mar 2008 17:57:21 +0000 (17:57 +0000)]
Add:
>
> * Consider a function-based API for '@@' full text searches
>
> http://archives.postgresql.org/pgsql-hackers/2007-11/msg00511.php
>
Andrew Dunstan [Sat, 8 Mar 2008 01:16:26 +0000 (01:16 +0000)]
Improve efficiency of attribute scanning in CopyReadAttributesCSV.
The loop is split into two parts, inside quotes, and outside quotes, saving some instructions in both parts.
Heikki Linnakangas
Tom Lane [Sat, 8 Mar 2008 01:09:36 +0000 (01:09 +0000)]
Improve pglz_decompress() so that it cannot clobber memory beyond the
available output buffer when presented with corrupt input. Some testing
suggests that this slows the decompression loop about 1%, which seems an
acceptable price to pay for more robustness. (Curiously, the penalty
seems to be *less* on not-very-compressible data, which I didn't expect
since the overhead per output byte ought to be more in the literal-bytes
path.)
Patch from Zdenek Kotala. I fixed a corner case and did some renaming
of variables to make the routine more readable.
Tom Lane [Fri, 7 Mar 2008 23:20:21 +0000 (23:20 +0000)]
This patch addresses some issues in TOAST compression strategy that
were discussed last year, but we felt it was too late in the 8.3 cycle to
change the code immediately. Specifically, the patch:
* Reduces the minimum datum size to be considered for compression from
256 to 32 bytes, as suggested by Greg Stark.
* Increases the required compression rate for compressed storage from
20% to 25%, again per Greg's suggestion.
* Replaces force_input_size (size above which compression is forced)
with a maximum size to be considered for compression. It was agreed
that allowing large inputs to escape the minimum-compression-rate
requirement was not bright, and that indeed we'd rather have a knob
that acted in the other direction. I set this value to 1MB for the
moment, but it could use some performance studies to tune it.
* Adds an early-failure path to the compressor as suggested by Jan:
if it's been unable to find even one compressible substring in the
first 1KB (parameterizable), assume we're looking at incompressible
input and give up. (Possibly this logic can be improved, but I'll
commit it as-is for now.)
* Improves the toasting heuristics so that when we have very large
fields with attstorage 'x' or 'e', we will push those out to toast
storage before considering inline compression of shorter fields.
This also responds to a suggestion of Greg's, though my original
proposal for a solution was a bit off base because it didn't fix
the problem for large 'e' fields.
There was some discussion in the earlier threads of exposing some
of the compression knobs to users, perhaps even on a per-column
basis. I have not done anything about that here. It seems to me
that if we are changing around the parameters, we'd better get some
experience and be sure we are happy with the design before we set
things in stone by providing user-visible knobs.
Bruce Momjian [Fri, 7 Mar 2008 20:38:59 +0000 (20:38 +0000)]
Add:
>
> * Add a function like pg_get_indexdef() that report more detailed index
> information
>
> http://archives.postgresql.org/pgsql-bugs/2007-12/msg00166.php
>
Bruce Momjian [Fri, 7 Mar 2008 20:22:25 +0000 (20:22 +0000)]
Add:
>
>
> o Prevent autovacuum from running if an old transaction is still
> running from the last vacuum
>
> http://archives.postgresql.org/pgsql-hackers/2007-11/msg00899.php
>
Bruce Momjian [Fri, 7 Mar 2008 20:06:03 +0000 (20:06 +0000)]
Added to TODO:
>
> * Allow text search dictionary to filter out only stop words
>
> http://archives.postgresql.org/pgsql-patches/2007-11/msg00081.php
Bruce Momjian [Fri, 7 Mar 2008 20:01:24 +0000 (20:01 +0000)]
Add to TODO:
>
> * Consider allowing higher priority queries to have referenced buffer
> cache pages stay in memory longer
>
> http://archives.postgresql.org/pgsql-hackers/2007-11/msg00562.php
Bruce Momjian [Fri, 7 Mar 2008 19:18:59 +0000 (19:18 +0000)]
Add:
>
> * Improve referential integrity checks
>
> http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php
Bruce Momjian [Fri, 7 Mar 2008 19:03:39 +0000 (19:03 +0000)]
Add item:
> o Store per-table autovacuum settings in pg_class.reloptions.
>
> http://archives.postgresql.org/pgsql-hackers/2007-02/msg01440.php
> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00724.php
Bruce Momjian [Fri, 7 Mar 2008 18:53:49 +0000 (18:53 +0000)]
Add URL for:
* Allow multiple identical NOTIFY events to always be communicated to the
client, rather than sent as a single notification to the listener
http://archives.postgresql.org/pgsql-general/2008-01/msg00057.php
Bruce Momjian [Fri, 7 Mar 2008 17:54:15 +0000 (17:54 +0000)]
Add:
> o Add SQLSTATE severity to PGconn return status
>
> http://archives.postgresql.org/pgsql-interfaces/2007-11/msg00015.php
Tom Lane [Fri, 7 Mar 2008 15:59:03 +0000 (15:59 +0000)]
Change hashscan.c to keep its list of active hash index scans in
TopMemoryContext, rather than scattered through executor per-query contexts.
This poses no danger of memory leak since the ResourceOwner mechanism
guarantees release of no-longer-needed items. It is needed because the
per-query context might already be released by the time we try to clean up
the hash scan list. Report by ykhuang, diagnosis by Heikki.
Back-patch to 8.0, where the ResourceOwner-based cleanup was introduced.
The given test case does not fail before 8.2, probably because we rearranged
transaction abort processing somehow; but this coding is undoubtedly risky
so I'll patch 8.0 and 8.1 anyway.
Bruce Momjian [Fri, 7 Mar 2008 15:56:40 +0000 (15:56 +0000)]
Add:
> o Have \l+ show database size, if permissions allow
>
> Ideally it will not generate an error for invalid permissions
>
Bruce Momjian [Fri, 7 Mar 2008 14:57:39 +0000 (14:57 +0000)]
Add:
>
> * Add comments on system tables/columns using the information in
> catalogs.sgml
>
> Ideally the information would be pulled from the SGML file
> automatically.
>
Teodor Sigaev [Fri, 7 Mar 2008 14:30:20 +0000 (14:30 +0000)]
Fix memory arrangement of tsquery after removing stop words. It causes
a unused memory holes in tsquery.
Per report by Richard Huxton <dev@archonet.com>.
It was working well because in fact tsquery->size is not used for any
kind of operation except comparing tsqueries. So, in HEAD it's enough to
fix to_tsquery function, but for previous version it's needed to
remove optimization in CompareTSQ to prevent requirement of renew all
stored tsquery.
Bruce Momjian [Fri, 7 Mar 2008 01:46:41 +0000 (01:46 +0000)]
Clearify PITR doc wording.
Bruce Momjian [Fri, 7 Mar 2008 01:06:55 +0000 (01:06 +0000)]
Update wording:
< o Require all check constraints to be inherited
> o Require all CHECK constraints to be inherited
Bruce Momjian [Fri, 7 Mar 2008 01:06:36 +0000 (01:06 +0000)]
Add:
>
> o Require all check constraints to be inherited
>
> http://archives.postgresql.org/pgsql-bugs/2007-04/msg00026.php
Bruce Momjian [Fri, 7 Mar 2008 00:10:13 +0000 (00:10 +0000)]
Add:
> o Have \d show foreign keys that reference a table's primary key
>
> http://archives.postgresql.org/pgsql-hackers/2007-04/msg00424.php
>
> o Have \d show child tables that inherit from the specified parent
Bruce Momjian [Thu, 6 Mar 2008 22:17:41 +0000 (22:17 +0000)]
Add URL for:
* Consider compressing indexes by storing key values duplicated in
several rows as a single index entry
> http://archives.postgresql.org/pgsql-patches/2007-03/msg00163.php
Bruce Momjian [Thu, 6 Mar 2008 22:15:27 +0000 (22:15 +0000)]
Add URL for:
* Add SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT
> http://archives.postgresql.org/pgsql-patches/2007-03/msg00139.php
Bruce Momjian [Thu, 6 Mar 2008 22:09:43 +0000 (22:09 +0000)]
Add URLs for:
* Consider compressing indexes by storing key values duplicated in
several rows as a single index entry
>
> http://archives.postgresql.org/pgsql-hackers/2006-12/msg00341.php
> http://archives.postgresql.org/pgsql-hackers/2007-02/msg01264.php
> http://archives.postgresql.org/pgsql-hackers/2007-03/msg00465.php
>
Bruce Momjian [Thu, 6 Mar 2008 22:04:57 +0000 (22:04 +0000)]
Add URL for:
* Add SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT
> http://archives.postgresql.org/pgsql-hackers/2007-01/msg01375.php
Bruce Momjian [Thu, 6 Mar 2008 21:37:33 +0000 (21:37 +0000)]
Document that enabling asserts can _significantly_ slow down the server.
Back patch to 8.3.X.
Bruce Momjian [Thu, 6 Mar 2008 21:25:50 +0000 (21:25 +0000)]
Add:
>
> * Allow client certificate names to be checked against the client
> hostname
>
> This is already implemented in
> libpq/fe-secure.c::verify_peer_name_matches_certificate() but the code
> is commented out.
Bruce Momjian [Thu, 6 Mar 2008 18:49:32 +0000 (18:49 +0000)]
Document use of pg_locks.objid for advisory locks, suggestion from Marc Mamin