8.3 Changelog
2007-09-26 18:36 tgl Minor improvements in backup and recovery:
- create a separate archive_mode GUC, on which archive_command is dependent
- %r option in recovery.conf sends last restartpoint to recovery command
- %r used in pg_standby, updated README
- minor other code cleanup in pg_standby
- doc on Warm Standby now mentions pg_standby and %r
- log_restartpoints recovery option emits LOG message at each restartpoint
- end of recovery now displays last transaction end time, as requested by Warren Little; also shown at each restartpoint
- restart archiver if needed to carry away WAL files at shutdown
Simon Riggs
2007-09-26 14:51 tgl Create a function variable "join_search_hook" to let plugins override the join search order portion of the planner; this is specifically intended to simplify developing a replacement for GEQO planning. Patch by Julius Stroffek, editorialized on by me. I renamed make_one_rel_by_joins to standard_join_search and make_rels_by_joins to join_search_one_level to better reflect their place within this scheme.
2007-09-26 06:57 meskes Applied patch by ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> to get prepare thread-safe.
2007-09-25 21:10 tgl In the integer-datetimes case, date2timestamp and date2timestamptz need to check for overflow because the legal range of type date is actually wider than timestamp's. Problem found by Neil Conway.
2007-09-25 20:32 tgl Use SYSV semaphores rather than POSIX on Darwin >= 6.0 (i.e., OS X 10.2 and up), per Chris Marcellino. This avoids consuming O(N^2) file descriptors to support N backends. Tests suggest it's about a wash for small installations, but large ones would have a problem.
2007-09-25 18:21 tgl Change on-disk representation of NUMERIC datatype so that the sign_dscale word comes before the weight instead of after. This will allow future binary-compatible extension of the representation to support compact formats, as discussed on pgsql-hackers around 2007/06/18. The reason to do it now is that we've already pretty well broken any chance of simple in-place upgrade from 8.2 to 8.3, but it's possible that 8.3 to 8.4 (or whenever we get around to squeezing NUMERIC) could otherwise be data-compatible.
2007-09-25 16:03 tgl Just-in-time background writing strategy. This code avoids re-scanning buffers that cannot possibly need to be cleaned, and estimates how many buffers it should try to clean based on moving averages of recent allocation requests and density of reusable buffers. The patch also adds a couple more columns to pg_stat_bgwriter to help measure the effectiveness of the bgwriter.
Greg Smith, building on his own work and ideas from several other people, in particular a much older patch from Itagaki Takahiro.
2007-09-23 23:12 tgl Simplify and rename some GUC variables, per various recent discussions:
* stats_start_collector goes away; we always start the collector process, unless prevented by a problem with setting up the stats UDP socket.
* stats_reset_on_server_start goes away; it seems useless in view of the availability of pg_stat_reset().
* stats_block_level and stats_row_level are merged into a single variable "track_counts", which controls all reports sent to the collector process.
* stats_command_string is renamed to track_activities.
* log_autovacuum is renamed to log_autovacuum_min_duration to better reflect its meaning.
The log_autovacuum change is not a compatibility issue since it didn't exist before 8.3 anyway. The other changes need to be release-noted.
2007-09-23 21:29 adunstan Remove "convert 'blah' using conversion_name" facility, because if it produces text it is an encoding hole and if not it's incompatible with the spec, whatever the spec means (which we're not sure about anyway).
2007-09-23 19:39 tgl Replace linear searches with binary searches in pg_dump's code to lookup objects by OID. Per gripe from nikitathespider.
2007-09-23 17:52 adunstan Add perl replacements for build.bat and vcregress.bat. In due course the .bat files will be altered to become tiny wrappers for these scripts, and one or two other .bat files will disappear.
2007-09-23 16:07 tgl Make autovacuum report the start time of its current activity in pg_stat_activity. Per gripe from Jim Nasby.
2007-09-22 17:36 tgl backend/optimizer/plan/subselect.c, include/optimizer/cost.h: Fix cost estimates for EXISTS subqueries that are evaluated as initPlans (because they are uncorrelated with the immediate parent query). We were charging the full run cost to the parent node, disregarding the fact that only one row need be fetched for EXISTS. While this would only be a cosmetic issue in most cases, it might possibly affect planning outcomes if the parent query were itself a subquery to some upper query. Per recent discussion with Steve Crawford.
2007-09-21 14:24 tgl Change tqual.c tests to use !TransactionIdIsCurrentTransactionId, rather than TransactionIdDidAbort, when handling the case that xmin is one of the current transaction's XIDs and the tuple has been deleted. xmax must also be one of the current transaction's XIDs, since no one else can see it yet, and it's cheaper to look at local state than shared state to find out if xmax aborted. Per an idea of Heikki's.
2007-09-21 13:36 tgl Make some simple performance improvements in TransactionIdIsInProgress(). For XIDs of our own transaction and subtransactions, it's cheaper to ask TransactionIdIsCurrentTransactionId() than to look in shared memory. Also, the xids[] work array is always the same size within any given process, so malloc it just once instead of doing a palloc/pfree on every call; aside from being faster this lets us get rid of some goto's, since we no longer have any end-of-function pfree to do. Both ideas by Heikki.
2007-09-20 20:30 tgl Insert a hack in pl/tcl to disable Tcl's built-in Notifier subsystem, which has a bad habit of launching multiple threads within the backend and thereby causing all kinds of havoc. Fortunately, we don't need it, and recent Tcl versions provide an easy way to disable it. Diagnosis and fix by Steve Marshall, Paul Bayer, and Doug Knight of WSI Corporation.
2007-09-20 13:56 tgl HOT updates. When we update a tuple without changing any of its indexed columns, and the new version can be stored on the same heap page, we no longer generate extra index entries for the new version. Instead, index searches follow the HOT-chain links to ensure they find the correct tuple version.
In addition, this patch introduces the ability to "prune" dead tuples on a per-page basis, without having to do a complete VACUUM pass to recover space. VACUUM is still needed to clean up dead index entries, however.
Pavan Deolasee, with help from a bunch of other people.
2007-09-18 13:41 adunstan Close previously open holes for invalidly encoded data to enter the database via builtin functions, as recently discussed on -hackers.
chr() now returns a character in the database encoding. For UTF8 encoded databases the argument is treated as a Unicode code point. For other multi-byte encodings the argument must designate a strict ascii character, or an error is raised, as is also the case if the argument is 0.
ascii() is adjusted so that it remains the inverse of chr().
The two argument form of convert() is gone, and the three argument form now takes a bytea first argument and returns a bytea. To cover this loss three new functions are introduced: . convert_from(bytea, name) returns text - converts the first argument from the named encoding to the database encoding . convert_to(text, name) returns bytea - converts the first argument from the database encoding to the named encoding . length(bytea, name) returns int - gives the length of the first argument in characters in the named encoding
2007-09-12 18:10 tgl Redefine the lp_flags field of item pointers as having four states, rather than two independent bits (one of which was never used in heap pages anyway, or at least hadn't been in a very long time). This gives us flexibility to add the HOT notions of redirected and dead item pointers without requiring anything so klugy as magic values of lp_off and lp_len. The state values are chosen so that for the states currently in use (pre-HOT) there is no change in the physical representation.
2007-09-12 16:49 adunstan Perform post-escaping encoding validity checks on SQL literals and COPY input so that invalidly encoded data cannot enter the database by these means.
2007-09-10 20:06 tgl Arrange for SET LOCAL's effects to persist until the end of the current top transaction, unless rolled back or overridden by a SET clause for the same variable attached to a surrounding function call. Per discussion, these seem the best semantics. Note that this is an INCOMPATIBLE CHANGE: in 8.0 through 8.2, SET LOCAL's effects disappeared at subtransaction commit (leading to behavior that made little sense at the SQL level).
I took advantage of the opportunity to rewrite and simplify the GUC variable save/restore logic a little bit. The old idea of a "tentative" value is gone; it was a hangover from before we had a stack. Also, we no longer need a stack entry for every nesting level, but only for those in which a variable's value actually changed.
2007-09-09 16:40 adunstan Provide for a file specifying non-standard config options for temp install for pg_regress, via --temp-config option. Pick this up in the make file via TEMP_CONFIG setting.
2007-09-08 16:31 tgl Replace the former method of determining snapshot xmax --- to wit, calling ReadNewTransactionId from GetSnapshotData --- with a "latestCompletedXid" variable that is updated during transaction commit or abort. Since latestCompletedXid is written only in places that had to lock ProcArrayLock exclusively anyway, and is read only in places that had to lock ProcArrayLock shared anyway, it adds no new locking requirements to the system despite being cluster-wide. Moreover, removing ReadNewTransactionId from snapshot acquisition eliminates the need to take both XidGenLock and ProcArrayLock at the same time. Since XidGenLock is sometimes held across I/O this can be a significant win. Some preliminary benchmarking suggested that this patch has no effect on average throughput but can significantly improve the worst-case transaction times seen in pgbench. Concept by Florian Pflug, implementation by Tom Lane.
2007-09-07 16:59 tgl Don't take ProcArrayLock while exiting a transaction that has no XID; there is no need for serialization against snapshot-taking because the xact doesn't affect anyone else's snapshot anyway. Per discussion. Also, move various info about the interlocking of transactions and snapshots out of code comments and into a hopefully-more-cohesive discussion in access/transam/README.
Also, remove a couple of now-obsolete comments about having to force some WAL to be written to persuade RecordTransactionCommit to do its thing.
2007-09-06 20:58 tgl Allow CREATE INDEX CONCURRENTLY to disregard transactions in other databases, per gripe from hubert depesz lubaczewski. Patch from Simon Riggs.
2007-09-06 13:31 tgl Make eval_const_expressions() preserve typmod when simplifying something like null::char(3) to a simple Const node. (It already worked for non-null values, but not when we skipped evaluation of a strict coercion function.) This prevents loss of typmod knowledge in situations such as exhibited in bug #3598. Unfortunately there seems no good way to fix that bug in 8.1 and 8.2, because they simply don't carry a typmod for a plain Const node.
In passing I made all the other callers of makeNullConst supply "real" typmod values too, though I think it probably doesn't matter anywhere else.
2007-09-05 14:10 tgl Implement lazy XID allocation: transactions that do not modify any database rows will normally never obtain an XID at all. We already did things this way for subtransactions, but this patch extends the concept to top-level transactions. In applications where there are lots of short read-only transactions, this should improve performance noticeably; not so much from removal of the actual XID-assignments, as from reduction of overhead that's driven by the rate of XID consumption. We add a concept of a "virtual transaction ID" so that active transactions can be uniquely identified even if they don't have a regular XID. This is a much lighter-weight concept: uniqueness of VXIDs is only guaranteed over the short term, and no on-disk record is made about them.
Florian Pflug, with some editorialization by Tom.
2007-09-03 14:46 tgl Support SET FROM CURRENT in CREATE/ALTER FUNCTION, ALTER DATABASE, ALTER ROLE. (Actually, it works as a plain statement too, but I didn't document that because it seems a bit useless.) Unify VariableResetStmt with VariableSetStmt, and clean up some ancient cruft in the representation of same.
2007-09-02 20:39 tgl Implement function-local GUC parameter settings, as per recent discussion. There are still some loose ends: I didn't do anything about the SET FROM CURRENT idea yet, and it's not real clear whether we are happy with the interaction of SET LOCAL with function-local settings. The documentation is a bit spartan, too.
2007-08-29 13:24 tgl Relax permissions checks on dbsize functions, per discussion. Revert out all checks for individual-table-size functions, since anyone in the database could get approximate values from pg_class.relpages anyway. Allow database-size to users with CONNECT privilege for the target database (note that this is granted by default). Allow tablespace-size if the user has CREATE privilege on the tablespace (which is *not* granted by default), or if the tablespace is the default tablespace for the current database (since we treat that as implicitly allowing use of the tablespace).
2007-08-28 18:59 tgl Reduce the permissions check needed to use pgrowlocks() to having SELECT on the target table. Per discussion.
2007-08-26 23:36 tgl Fix a couple of misbehaviors rooted in the fact that the default creation namespace isn't necessarily first in the search path (there could be implicit schemas ahead of it). Examples are
test=# set search_path TO s1;
test=# create view pg_timezone_names as select * from pg_timezone_names(); ERROR: "pg_timezone_names" is already a view
test=# create table pg_class (f1 int primary key); ERROR: permission denied: "pg_class" is a system catalog
You'd expect these commands to create the requested objects in s1, since names beginning with pg_ aren't supposed to be reserved anymore. What is happening is that we create the requested base table and then execute additional commands (here, CREATE RULE or CREATE INDEX), and that code is passed the same RangeVar that was in the original command. Since that RangeVar has schemaname = NULL, the secondary commands think they should do a path search, and that means they find system catalogs that are implicitly in front of s1 in the search path.
This is perilously close to being a security hole: if the secondary command failed to apply a permission check then it'd be possible for unprivileged users to make schema modifications to system catalogs. But as far as I can find, there is no code path in which a check doesn't occur. Which makes it just a weird corner-case bug for people who are silly enough to want to name their tables the same as a system catalog.
The relevant code has changed quite a bit since 8.2, which means this patch wouldn't work as-is in the back branches. Since it's a corner case no one has reported from the field, I'm not going to bother trying to back-patch.
2007-08-26 21:39 tgl Remove the 'not in' operator (!!=). This was a hangover from Berkeley days that was obsolete the moment we had IN (SELECT ...) capability. It's arguably a security hole since it applied no permissions check to the table it searched, and since it was never documented anywhere, removing it seems more appropriate than fixing it.
2007-08-26 21:24 tgl Require SELECT privilege on a table to do dblink_get_pkey(). This is not all that exciting when the system catalogs are readable by all, but some people try to lock them down, and would not like this sort of end run ...
2007-08-26 21:19 tgl Restrict pg_relation_size to relation owner, pg_database_size to DB owner, and pg_tablespace_size to superusers. Perhaps we could weaken the first case to just require SELECT privilege, but that doesn't work for the other cases, so use ownership as the common concept.
2007-08-26 20:57 tgl Make currtid() functions require SELECT privileges on the target table. While it's not clear that TID linkage info is of any great use to a nefarious user, it's certainly unexpected that these functions wouldn't insist on read privileges.
2007-08-26 20:13 tgl Restrict pgrowlocks function to superusers. (This might be too strict, but no permissions check at all is certainly no good.) Clean up usage of some deprecated APIs.
2007-08-26 19:59 tgl Restrict pgstattuple functions to superusers. (This might be too strict, but no permissions check at all is certainly no good.) Clean up usage of some deprecated APIs.
2007-08-26 17:44 tgl Make ARRAY(SELECT ...) return an empty array, rather than a NULL, when the sub-select returns zero rows. Per complaint from Jens Schicke. Since this is more in the nature of a definition change than a bug, not back-patched.
2007-08-20 21:12 tgl contrib/tsearch2 now fails its regression test because of conflicts with the core additions. For the moment I diked it out of contrib/Makefile. We should look at turning it into a backward-compatibility package.
2007-08-20 21:11 tgl Tsearch2 functionality migrates to core. The bulk of this work is by Oleg Bartunov and Teodor Sigaev, but I did a lot of editorializing, so anything that's broken is probably my fault.
Documentation is nonexistent as yet, but let's land the patch so we can get some portability testing done.
2007-08-20 04:53 petere Add configure option --with-system-tzdata to use operating system time zone database.
2007-08-18 21:41 adunstan Provide for logfiles in machine readable CSV format. In consequence, rename redirect_stderr to logging_collector. Original patch from Arul Shaji, subsequently modified by Greg Smith, and then heavily modified by me.
2007-08-14 06:01 meskes Finished major rewrite to use new protocol version - Really prepare statements - Added more regression tests - Added auto-prepare mode - Use '$n' for positional variables, '?' is still possible via ecpg option - Cleaned up the sources a little bit
2007-08-12 16:39 tgl Remove an "optimization" I installed in 2001, to make repalloc() attempt to enlarge the memory chunk in-place when it was feasible to do so. This turns out to not work well at all for scenarios involving repeated cycles of palloc/repalloc/pfree: the eventually freed chunks go into the wrong freelist for the next initial palloc request, and so we consume memory indefinitely. While that could be defended against, the number of cases where the optimization can still be applied drops significantly, and adjusting the initial sizes of StringInfo buffers makes it drop to almost nothing. Seems better to just remove the extra complexity. Per recent discussion and testing.
2007-08-12 16:18 tgl Increase the initial size of StringInfo buffers to 1024 bytes (from 256); likewise increase the initial size of the scanner's literal buffer to 1024 (from 128). Instrumentation of the regression tests suggests that this saves a useful amount of repalloc() traffic --- the number of calls occurring during one set of tests drops from about 6900 to about 3900. The old sizes were chosen in the late 90's with an eye to machines much smaller than are common today.
2007-08-08 21:18 tgl Revise postmaster startup/shutdown logic to eliminate the problem that a constant flow of new connection requests could prevent the postmaster from completing a shutdown or crash restart. This is done by labeling child processes that are "dead ends", that is, we know that they were launched only to tell a client that it can't connect. These processes are managed separately so that they don't confuse us into thinking that we can't advance to the next stage of a shutdown or restart sequence, until the very end where we must wait for them to drain out so we can delete the shmem segment. Per discussion of a misbehavior reported by Keaton Adams.
Since this code was baroque already, and my first attempt at fixing the problem made it entirely impenetrable, I took the opportunity to rewrite it in a state-machine style. That eliminates some duplicated code sections and hopefully makes everything a bit clearer.
2007-08-07 02:25 neilc Adjust the output of MemoryContextStats() so that the stats for a child memory contexts is indented two spaces to the right of its parent context. This should make it easier to deduce the memory context hierarchy from the output of MemoryContextStats().
2007-08-03 21:26 tgl Switch over to using the src/timezone functions for formatting timestamps displayed in the postmaster log. This avoids Windows-specific problems with localized time zone names that are in the wrong encoding, and generally seems like a good idea to forestall other potential platform-dependent issues. To preserve the existing behavior that all backends will log in the same time zone, create a new GUC variable log_timezone that can only be changed on a system-wide basis, and reference log-related calculations to that zone instead of the TimeZone variable.
This fixes the issue reported by Hiroshi Saito that timestamps printed by xlog.c startup could be improperly localized on Windows. We still need a simpler patch for that problem in the back branches, however.
2007-08-02 19:39 adunstan Move session_start out of MyProcPort stucture and make it a global called MyStartTime, so that we will be able to create a cookie for all processes for CSVlogs. It is set wherever MyProcPid is set. Take the opportunity to remove the now unnecessary session-only restriction on the %s and %c escapes in log_line_prefix.
2007-08-01 18:45 tgl Support an optional asynchronous commit mode, in which we don't flush WAL before reporting a transaction committed. Data consistency is still guaranteed (unlike setting fsync = off), but a crash may lose the effects of the last few transactions. Patch by Simon, some editorialization by Tom.
2007-07-26 11:15 tgl Remove FileUnlink(), which wasn't being used anywhere and interacted poorly with the recent patch to log temp file sizes at removal time. Doesn't seem worth fixing since it's unused. In passing, make a few elog messages conform to the message style guide.
2007-07-25 18:16 tgl Arrange to put TOAST tables belonging to temporary tables into special schemas named pg_toast_temp_nnn, alongside the pg_temp_nnn schemas used for the temp tables themselves. This allows low-level code such as the relcache to recognize that these tables are indeed temporary, which enables various optimizations such as not WAL-logging changes and using local rather than shared buffers for access. Aside from obvious performance benefits, this provides a solution to bug #3483, in which other backends unexpectedly held open file references to temporary tables. The scheme preserves the property that TOAST tables are not in any schema that's normally in the search path, so they don't conflict with user table names.
initdb forced because of changes in system view definitions.
2007-07-25 08:22 mha Rename DLLIMPORT macro to PGDLLIMPORT to avoid conflict with third party includes (like tcl) that define DLLIMPORT.
2007-07-25 00:19 neilc Implement RETURN QUERY for PL/PgSQL. This provides some convenient syntax sugar for PL/PgSQL set-returning functions that want to return the result of evaluating a query; it should also be more efficient than repeated RETURN NEXT statements. Based on an earlier patch from Pavel Stehule.
2007-07-24 00:54 tgl Create a new dedicated Postgres process, "wal writer", which exists to write and fsync WAL at convenient intervals. For the moment it just tries to offload this work from backends, but soon it will be responsible for guaranteeing a maximum delay before asynchronously-committed transactions will be flushed to disk.
This is a portion of Simon Riggs' async-commit patch, committed to CVS separately because a background WAL writer seems like it might be a good idea independently of the async-commit feature. I rebased walwriter.c on bgwriter.c because it seemed like a more appropriate way of handling signals; while the startup/shutdown logic in postmaster.c is more like autovac because we want walwriter to quit before we start the shutdown checkpoint.
2007-07-23 21:53 alvherre Set a default autovacuum vacuum_cost_delay value of 20ms, to avoid excessive I/O utilization, per discussion.
While at it, lower the autovacuum vacuum and analyze threshold values to 50 tuples. It is a bit higher (i.e. more conservative) than what I originally proposed but much better than the old values for small tables.
2007-07-23 13:52 mha Use PQExpBuffer for error message in fe-auth.c. In passing, change functions that passedin both PGconn and parts of it to just pass in the PGconn.
2007-07-23 06:16 mha SSPI authentication on Windows. GSSAPI compatible client when doing Kerberos against a Unix server, and Windows-specific server-side authentication using SSPI "negotiate" method (Kerberos or NTLM).
2007-07-17 23:13 momjian Properly adjust age() seconds to match the sign of the larger units. Patch from Tom.
2007-07-17 01:02 neilc Implement CREATE TABLE LIKE ... INCLUDING INDEXES. Patch from NikhilS, based in part on an earlier patch from Trevor Hardcastle, and reviewed by myself.
2007-07-16 13:01 tgl Allow plpgsql function parameter names to be qualified with the function's name. With this patch, it is always possible for the user to qualify a plpgsql variable name if needed to avoid ambiguity. While there is much more work to be done in this area, this simple change removes one unnecessary incompatibility with Oracle. Per discussion.
2007-07-15 18:18 tgl Because plpgsql's scanner uses %option case-insensitive, flex's results could theoretically vary depending on what the compile-time locale setting is. Hence, force it to see LC_CTYPE=C to ensure consistent build results. (It's likely that this makes no difference in practice, since our specification for "identifier" surely includes both ends of any possible uppercase/lowercase pair anyway. But it should silence warnings about ambiguous character classes that are reported by some buildfarm members.)
2007-07-14 22:15 tgl Reject zero or negative BY step in plpgsql integer FOR-loops, and behave sanely if the loop value overflows int32 on the way to the end value. Avoid useless computation of "SELECT 1" when BY is omitted. Avoid some type-punning between Datum and int4 that dates from the original coding.
2007-07-12 17:13 tgl Some of our port-specific dynloader implementations are careful to define pg_dlsym() as returning a PGFunction pointer, not just any pointer-to-function. But many are not. Suppress compiler warnings on platforms that aren't careful by inserting explicit casts at the two call sites that didn't have a cast already. Per Stefan.
2007-07-12 10:43 mha Enable GSSAPI to build using MSVC. Always build GSSAPI when Kerberos is enabled, because the only Kerberos library supported always contains it.
2007-07-12 10:36 mha Support GSSAPI builds where the header is <gssapi.h> and not <gssapi/gssapi.h>, such as OpenBSD (possibly all Heimdal). Stefan Kaltenbrunner
2007-07-10 09:14 mha Add support for GSSAPI authentication.
Documentation still being written, will be committed later.
Henry B. Hotz and Magnus Hagander
2007-07-09 20:21 tgl Adjust processSQLNamePattern() so that $ within the pattern is always matched literally, whether quoted or not. Since we allow $ as a character within identifiers, this behavior is useful, whereas the previous behavior of treating it as the regexp ending anchor was nearly useless given that the pattern is automatically anchored anyway. This affects the arguments of psql's \d commands as well as pg_dump's -n and -t switches. Per discussion.
2007-07-08 21:15 tgl Fix single-user mode so that interrupts (particularly SIGTERM and SIGQUIT) will be recognized and processed while waiting for input, rather than only after something has been typed. Also make SIGQUIT do the same thing as SIGTERM in single-user mode, ie, do a normal shutdown and exit. Since it's relatively easy to provoke SIGQUIT from the keyboard, people may try that instead of control-D, and we'd rather this leads to orderly shutdown. Per report from Leon Mergen and subsequent discussion.
2007-07-08 15:07 tgl Get rid of client-code dependencies on the exact text of the no-password error message, by using PQconnectionUsedPassword() instead. Someday we might be able to localize that error message, but not until this coding technique has disappeared everywhere.
2007-07-08 13:12 joe Restrict non-superusers to password authenticated connections to prevent possible escalation of privilege. Provide new SECURITY DEFINER functions with old behavior, but initially REVOKE ALL from public for these functions. Per list discussion and design proposed by Tom Lane. A different approach will be used for back-branches, committed separately.
2007-07-08 13:11 joe Arrange for the authentication request type to be preserved in PGconn. Invent a new libpq connection-status function, PQconnectionUsedPassword() that returns true if the server demanded a password during authentication, false otherwise. This may be useful to clients in general, but is immediately useful to help plug a privilege escalation path in dblink. Per list discussion and design proposed by Tom Lane.
2007-07-06 16:17 wieck Changed new \usleep command into \sleep with an optional time unit argument to specify us, ms or s. As per suggestion by Peter E.
Jan
2007-07-06 09:36 wieck Added a new scripting meta command
\usleep [milliseconds|:variable]
which can be used in -f scripts to insert a thinking time between other commands.
Jan
2007-07-02 21:30 neilc Add ALTER VIEW ... RENAME TO, and a RENAME TO clause to ALTER SEQUENCE. Sequences and views could previously be renamed using ALTER TABLE, but this was a repeated source of confusion for users. Update the docs, and psql tab completion. Patch from David Fetter; various minor fixes by myself.
2007-07-01 14:30 tgl Reduce the maximum sleep interval in the autovac launcher to 1 second, so that it responds to SIGQUIT reasonably promptly even on machines where SA_RESTART signals restart a sleep from scratch. (This whole area could stand some rethinking, but for now make it work like the other processes do.) Also some marginal stylistic cleanups.
2007-07-01 14:28 tgl Treat the autovac launcher more like a regular backend, in that we wait for it to die before telling the bgwriter to initiate shutdown checkpoint. Since it's connected to shared memory, this seems more prudent than the alternative of letting it quit asynchronously. Resolves my complaint of yesterday about repeated shutdown checkpoints in CVS HEAD.
2007-06-30 15:12 tgl Improve logging of checkpoints. Patch by Greg Smith, worked over by Heikki and a little bit by me.
2007-06-29 13:07 alvherre Arrange for SIGINT in autovacuum workers to cancel the current table and continue with the schedule. Change current uses of SIGINT to abort a worker into SIGTERM, which keeps the old behaviour of terminating the process.
Patch from ITAGAKI Takahiro, with some editorializing of my own.
2007-06-28 02:40 neilc Add the function's volatility to the output of psql's \df+ command. Update the psql reference page accordingly.
2007-06-27 20:02 tgl Implement "distributed" checkpoints in which the checkpoint I/O is spread over a fairly long period of time, rather than being spat out in a burst. This happens only for background checkpoints carried out by the bgwriter; other cases, such as a shutdown checkpoint, are still done at full speed.
Remove the "all buffers" scan in the bgwriter, and associated stats infrastructure, since this seems no longer very useful when the checkpoint itself is properly throttled.
Original patch by Itagaki Takahiro, reworked by Heikki Linnakangas, and some minor API editorialization by me.
2007-06-26 18:05 tgl Fix PGXS conventions so that extensions can be built against Postgres installations whose pg_config program does not appear first in the PATH. Per gripe from Eddie Stanley and subsequent discussions with Fabien Coelho and others.
2007-06-23 18:12 tgl Separate parse-analysis for utility commands out of parser/analyze.c (which now deals only in optimizable statements), and put that code into a new file parser/parse_utilcmd.c. This helps clarify and enforce the design rule that utility statements shouldn't be processed during the regular parse analysis phase; all interpretation of their meaning should happen after they are given to ProcessUtility to execute. (We need this because we don't retain any locks for a utility statement that's in a plan cache, nor have any way to detect that it's stale.)
We are also able to simplify the API for parse_analyze() and related routines, because they will now always return exactly one Query structure.
In passing, fix bug #3403 concerning trying to add a serial column to an existing temp table (this is largely Heikki's work, but we needed all that restructuring to make it safe).
2007-06-21 14:14 tgl Provide a HINT listing the allowed unit names when a GUC variable seems to contain a wrong unit specification, per discussion. In passing, fix the code to avoid unnecessary integer overflows when converting units, and to detect overflows when they do occur.
2007-06-18 17:40 tgl Arrange for quote_identifier() and pg_dump to not quote keywords that are unreserved according to the grammar. The list of unreserved words has gotten extensive enough that the unnecessary quoting is becoming a bit of an eyesore. To do this, add knowledge of the keyword category to keywords.c's table. (Someday we might be able to generate keywords.c's table and the keyword lists in gram.y from a common source.) For the moment, lie about WITH's status in the table so it will still get quoted --- this is because of the expectation that WITH will become reserved when the SQL recursive-queries patch gets done.
I didn't force initdb because this affects nothing on-disk; but note that a few regression tests have changed expected output.
2007-06-15 16:56 tgl Tweak the API for per-datatype typmodin functions so that they are passed an array of strings rather than an array of integers, and allow any simple constant or identifier to be used in typmods; for example create table foo (f1 widget(42,'23skidoo',point)); Of course the typmodin function has still got to pack this info into a non-negative int32 for storage, but it's still a useful improvement in flexibility, especially considering that you can do nearly anything if you are willing to keep the info in a side table. We can get away with this change since we have not yet released a version providing user-definable typmods. Per discussion.
2007-06-12 07:07 mha Rewrite ECPG regression test driver in C, by splitting the standard regression driver into two parts and reusing half of it. Required to run ECPG tests without a shell on MSVC builds.
Fix ECPG thread tests for MSVC build (incl output files).
Joachim Wieland and Magnus Hagander
2007-06-11 18:22 tgl Improve UPDATE/DELETE WHERE CURRENT OF so that they can be used from plpgsql with a plpgsql-defined cursor. The underlying mechanism for this is that the main SQL engine will now take "WHERE CURRENT OF $n" where $n is a refcursor parameter. Not sure if we should document that fact or consider it an implementation detail. Per discussion with Pavel Stehule.
2007-06-10 21:16 tgl Support UPDATE/DELETE WHERE CURRENT OF cursor_name, per SQL standard.
Along the way, allow FOR UPDATE in non-WITH-HOLD cursors; there may once have been a reason to disallow that, but it seems to work now, and it's really rather necessary if you want to select a row via a cursor and then update it in a concurrent-safe fashion.
Original patch by Arul Shaji, rather heavily editorialized by Tom Lane.
2007-06-08 14:23 tgl Arrange for large sequential scans to synchronize with each other, so that when multiple backends are scanning the same relation concurrently, each page is (ideally) read only once.
Jeff Davis, with review by Heikki and Tom.
2007-06-07 17:45 tgl Redefine IsTransactionState() to only return true for TRANS_INPROGRESS state, which is the only state in which it's safe to initiate database queries. It turns out that all but two of the callers thought that's what it meant; and the other two were using it as a proxy for "will GetTopTransactionId() return a nonzero XID"? Since it was in fact an unreliable guide to that, make those two just invoke GetTopTransactionId() always, then deal with a zero result if they get one.
2007-06-07 14:53 alvherre Avoid losing track of data for shared tables in pgstats. Report by Michael Fuhr, patch from Tom Lane after a messier suggestion by me.
2007-06-06 19:00 tgl Fix up text concatenation so that it accepts all the reasonable cases that were accepted by prior Postgres releases. This takes care of the loose end left by the preceding patch to downgrade implicit casts-to-text. To avoid breaking desirable behavior for array concatenation, introduce a new polymorphic pseudo-type "anynonarray" --- the added concatenation operators are actually text || anynonarray and anynonarray || text.
2007-06-05 17:31 tgl Downgrade implicit casts to text to be assignment-only, except for the ones from the other string-category types; this eliminates a lot of surprising interpretations that the parser could formerly make when there was no directly applicable operator.
Create a general mechanism that supports casts to and from the standard string types (text,varchar,bpchar) for *every* datatype, by invoking the datatype's I/O functions. These new casts are assignment-only in the to-string direction, explicit-only in the other, and therefore should create no surprising behavior. Remove a bunch of thereby-obsoleted datatype-specific casting functions.
The "general mechanism" is a new expression node type CoerceViaIO that can actually convert between *any* two datatypes if their external text representations are compatible. This is more general than needed for the immediate feature, but might be useful in plpgsql or other places in future.
This commit does nothing about the issue that applying the concatenation operator || to non-text types will now fail, often with strange error messages due to misinterpreting the operator as array concatenation. Since it often (not always) worked before, we should either make it succeed or at least give a more user-friendly error; but details are still under debate.
Peter Eisentraut and Tom Lane
2007-06-04 06:02 petere Remove gratuitous response messages from utility programs.
(Possibly release notes material, lest users be confused.)
The --quiet option is now obsolete and without effect in createdb, createuser, dropdb, dropuser; kept for compatibility but marked for removal in 8.4.
Progress messages when acting on all databases now go to stdout instead of stderr, since they are not in fact errors.
Ordered options in reindexdb reference page alphabetically, like in other programs' pages.
2007-06-03 13:05 tgl Create a GUC parameter temp_tablespaces that allows selection of the tablespace(s) in which to store temp tables and temporary files. This is a list to allow spreading the load across multiple tablespaces (a random list element is chosen each time a temp object is to be created). Temp files are not stored in per-database pgsql_tmp/ directories anymore, but per-tablespace directories.
Jaime Casanova and Albert Cervera, with review by Bernd Helmle and Tom Lane.
2007-06-01 22:03 adunstan Improve efficiency of LIKE/ILIKE code, especially for multi-byte charsets, and most especially for UTF8. Remove unnecessary special cases for bytea processing and single-byte charset ILIKE. a ILIKE b is now processed as lower(a) LIKE lower(b) in all cases. The code is now considerably simpler. All comparisons are now performed byte-wise, and the text and pattern are also advanced byte-wise where it is safe to do so - essentially where a wildcard is not being matched. Andrew Dunstan, from an original patch by ITAGAKI Takahiro, with ideas from Tom Lane and Mark Mielke.
2007-06-01 19:40 neilc Allow leading and trailing whitespace in the input to the boolean type. Also, add explicit casts between boolean and text/varchar. Both of these changes are for conformance with SQL:2003.
Update the regression tests, bump the catversion.
2007-06-01 15:38 tgl Make CREATE/DROP/RENAME DATABASE wait a little bit to see if other backends will exit before failing because of conflicting DB usage. Per discussion, this seems a good idea to help mask the fact that backend exit takes nonzero time. Remove a couple of thereby-obsoleted sleeps in contrib and PL regression test sequences.
2007-06-01 11:33 tgl Fix several hash functions that were taking chintzy shortcuts instead of delivering a well-randomized hash value. I got religion on this after observing that performance of multi-batch hash join degrades terribly if the higher-order bits of hash values aren't random, as indeed was true for say hashes of small integer values. It's now expected and documented that hash functions should use hash_any or some comparable method to ensure that all bits of their output are about equally random.
initdb forced because this change invalidates existing hash indexes. For the same reason, this isn't back-patchable; the hash join performance problem will get a band-aid fix in the back branches.
2007-05-31 12:57 tgl Change build_index_pathkeys() so that the expressions it builds to represent index key columns always have the type expected by the index's associated operators, ie, we add RelabelType nodes when dealing with binary-compatible index opclasses. This is needed to get varchar indexes to play nicely with the new EquivalenceClass machinery, as per recent gripe from Josh Berkus that CVS HEAD was failing to match a varchar index column to a constant restriction in the query.
It seems likely that this change will allow removal of a lot of ugly ad-hoc RelabelType-stripping that the planner has traditionally done while matching expressions to other expressions, but I'll worry about that some other day.
2007-05-31 10:03 teodor Replace ReadBuffer to ReadBufferWithStrategy in all vacuum-involved places to implement limited-size "ring" of buffers for VACUUM for GIN & GIST
2007-05-31 03:36 petere Downgrade some low-level startup messages to DEBUG1.
2007-05-30 16:11 tgl Make large sequential scans and VACUUMs work in a limited-size "ring" of buffers, rather than blowing out the whole shared-buffer arena. Aside from avoiding cache spoliation, this fixes the problem that VACUUM formerly tended to cause a WAL flush for every page it modified, because we had it hacked to use only a single buffer. Those flushes will now occur only once per ring-ful. The exact ring size, and the threshold for seqscans to switch into the ring usage pattern, remain under debate; but the infrastructure seems done. The key bit of infrastructure is a new optional BufferAccessStrategy object that can be passed to ReadBuffer operations; this replaces the former StrategyHintVacuum API.
This patch also changes the buffer usage-count methodology a bit: we now advance usage_count when first pinning a buffer, rather than when last unpinning it. To preserve the behavior that a buffer's lifetime starts to decrease when it's released, the clock sweep code is modified to not decrement usage_count of pinned buffers.
Work not done in this commit: teach GiST and GIN indexes to use the vacuum BufferAccessStrategy for vacuum-driven fetches.
Original patch by Simon, reworked by Heikki and again by Tom.
2007-05-30 14:10 momjian Update /contrib OS/X startup files, and move to a separate OS/X directory.
Mark Cotner and David Fetter
2007-05-27 01:37 tgl pgstat's on-proc-exit hook has to execute after the last transaction commit or abort within a backend; rearrange InitPostgres processing to make it so. Revealed by just-added Asserts along with ECPG regression tests (hm, I wonder why the core regression tests didn't expose it?). This possibly is another reason for missing stats updates ...
2007-05-26 23:50 tgl Fix up pgstats counting of live and dead tuples to recognize that committed and aborted transactions have different effects; also teach it not to assume that prepared transactions are always committed.
Along the way, simplify the pgstats API by tying counting directly to Relations; I cannot detect any redeeming social value in having stats pointers in HeapScanDesc and IndexScanDesc structures. And fix a few corner cases in which counts might be missed because the relation's pgstat_info pointer hadn't been set.
2007-05-25 13:54 tgl Create hooks to let a loadable plugin monitor (or even replace) the planner and/or create plans for hypothetical situations; in particular, investigate plans that would be generated using hypothetical indexes. This is a heavily-rewritten version of the hooks proposed by Gurjeet Singh for his Index Advisor project. In this formulation, the index advisor can be entirely a loadable module instead of requiring a significant part to be in the core backend, and plans can be generated for hypothetical indexes without requiring the creation and rolling-back of system catalog entries.
The index advisor patch as-submitted is not compatible with these hooks, but it needs significant work anyway due to other 8.2-to-8.3 planner changes. With these hooks in the core backend, development of the advisor can proceed as a pgfoundry project.
2007-05-24 07:13 petere Add deprecation notice about planned removal in 8.4 and advice to try using the newer XML stuff in core.
(This should probably also be referred to in the release notes.)
2007-05-21 13:57 tgl Teach tuplestore.c to throw away data before the "mark" point when the caller is using mark/restore but not rewind or backward-scan capability. Insert a materialize plan node between a mergejoin and its inner child if the inner child is a sort that is expected to spill to disk. The materialize shields the sort from the need to do mark/restore and thereby allows it to perform its final merge pass on-the-fly; while the materialize itself is normally cheap since it won't spill to disk unless the number of tuples with equal key values exceeds work_mem.
Greg Stark, with some kibitzing from Tom Lane.
2007-05-20 17:08 tgl To support external compression of archived WAL data, add a flag bit to WAL records that shows whether it is safe to remove full-page images (ie, whether or not an on-line backup was in progress when the WAL entry was made). Also make provision for an XLOG_NOOP record type that can be used to fill in the extra space when decompressing the data for restore.
This is the portion of Koichi Suzuki's "full page writes" patch that has to go into the core database. The remainder of that work is two external compression and decompression programs, which for the time being will undergo separate development on pgfoundry. Per discussion.
Also, twiddle the handling of BTREE_SPLIT records to ensure it'll be possible to compress them (the previous coding caused essential info to be omitted). The other commonly-used record types seem OK already, with the possible exception of GIN and GIST WAL records, which I don't understand well enough to opine on.
2007-05-18 19:19 alvherre Have CLUSTER advance the table's relfrozenxid. The new frozen point is the FreezeXid introduced in a recent commit, so there isn't any data loss in this approach.
Doing it causes ALTER TABLE (or rather, the forms of it that cause a full table rewrite) to be affected as well. In this case, the frozen point is RecentXmin, because after the rewrite all the tuples are relabeled with the rewriting transaction's Xid.
TOAST tables are fixed automatically as well, as fallout of the way they were already being handled in the respective code paths.
With this patch, there is no longer need to VACUUM tables for Xid wraparound purposes that have been cleaned up via TRUNCATE or CLUSTER.
2007-05-17 15:11 momjian Add database page inspection /contrib module.
Simon and Heikki
2007-05-17 11:28 alvherre Move the tuple freezing point in CLUSTER to a point further back in the past, to avoid losing useful Xid information in not-so-old tuples. This makes CLUSTER behave the same as VACUUM as far a tuple-freezing behavior goes (though CLUSTER does not yet advance the table's relfrozenxid).
While at it, move the actual freezing operation in rewriteheap.c to a more appropriate place, and document it thoroughly. This part of the patch from Tom Lane.
2007-05-16 13:28 alvherre Have TRUNCATE advance the affected table's relfrozenxid to RecentXmin, to avoid a later needless VACUUM for Xid-wraparound purposes. We can do this since the table is known to be left empty, so no Xid remains on it.
Per discussion.
2007-05-16 12:36 alvherre Have the rewriteheap code freeze old tuples. This is safe because it is only applied to live tuples older than a recent Xmin, not to tuples that may be part of an update chain. Those still keep their original markings.
This patch makes it possible for CLUSTER to advance relfrozenxid, thus avoiding the need of vacuuming the table for Xid wraparound purposes. That will be patched separately.
Patch from Heikki Linnakangas.
2007-05-14 16:24 tgl Get rid of the pg_shdepend entry for a TOAST table; it's unnecessary since there's an indirect dependency on the owner via the parent table. We were already handling indexes that way, but not toast tables for some reason. Saves a little catalog space and cuts down the verbosity of checkSharedDependencies reports.
2007-05-14 12:50 alvherre Report all dependent objects to the server log when a shared object is dropped, and only a truncated log of the objects in the current database to the client. Also, instead of reporting object counts for all databases on which the user might own objects, report only as many as fit in the predefined line count.
This is to avoid flooding the client when the user owns too many objects, which could cause problems.
Per report from Ed L. on April 4th and subsequent discussion.
2007-05-11 20:54 tgl Fix the problem that creating a user-defined type named _foo, followed by one named foo, would work but the other ordering would not. If a user-specified type or table name collides with an existing auto-generated array name, just rename the array type out of the way by prepending more underscores. This should not create any backward-compatibility issues, since the cases in which this will happen would have failed outright in prior releases.
Also fix an oversight in the arrays-of-composites patch: ALTER TABLE RENAME renamed the table's rowtype but not its array type.
2007-05-11 13:57 tgl Support arrays of composite types, including the rowtypes of regular tables and views (but not system catalogs, nor sequences or toast tables). Get rid of the hardwired convention that a type's array type is named exactly "_type", instead using a new column pg_type.typarray to provide the linkage. (It still will be named "_type", though, except in odd corner cases such as maximum-length type names.)
Along the way, make tracking of owner and schema dependencies for types more uniform: a type directly created by the user has these dependencies, while a table rowtype or auto-generated array type does not have them, but depends on its parent object instead.
David Fetter, Andrew Dunstan, Tom Lane
2007-05-08 14:56 neilc Add a hash function for "numeric". Mark the equality operator for numerics as "oprcanhash", and make the corresponding system catalog updates. As a result, hash indexes, hashed aggregation, and hash joins can now be used with the numeric type. Bump the catversion.
The only tricky aspect to doing this is writing a correct hash function: it's possible for two Numerics to be equal according to their equality operator, but have different in-memory bit patterns. To cope with this, the hash function doesn't consider the Numeric's "scale" or "sign", and explictly skips any leading or trailing zeros in the Numeric's digit buffer (the current implementation should suppress any such zeros, but it seems unwise to rely upon this). See discussion on pgsql-patches for more details.
2007-05-04 17:29 tgl Add a line to the EXPLAIN ANALYZE output for a Sort node, showing the actual sort strategy and amount of space used. By popular demand.
2007-05-04 11:20 tgl tas() support for Renesas' M32R processor. Kazuhiro Inaoka
2007-05-03 22:01 tgl A few fixups in error handling: mark pg_re_throw() as noreturn for gcc, and for other compilers, insert a dummy exit() call so that they understand PG_RE_THROW() doesn't return. Insert fflush(stderr) in ExceptionalCondition, per recent buildfarm evidence that that might not happen automatically on some platforms. And const-ify ExceptionalCondition's declaration while at it.
2007-05-03 21:13 tgl Teach tuplesort.c about "top N" sorting, in which only the first N tuples need be returned. We keep a heap of the current best N tuples and sift-up new tuples into it as we scan the input. For M input tuples this means only about M*log(N) comparisons instead of M*log(M), not to mention a lot less workspace when N is small --- avoiding spill-to-disk for large M is actually the most attractive thing about it. Patch includes planner and executor support for invoking this facility in ORDER BY ... LIMIT queries. Greg Stark, with some editorialization by moi.
2007-05-03 12:45 tgl Tweak hash index AM to use the new ReadOrZeroBuffer bufmgr API when fetching pages it intends to zero immediately. Just to show there is some use for that function besides WAL recovery :-). Along the way, fold _hash_checkpage and _hash_pageinit calls into _hash_getbuf and friends, instead of expecting callers to do that separately.
2007-05-02 19:18 tgl During WAL recovery, when reading a page that we intend to overwrite completely from the WAL data, don't bother to physically read it; just have bufmgr.c return a zeroed-out buffer instead. This speeds recovery significantly, and also avoids unnecessary failures when a page-to-be-overwritten has corrupt page headers on disk. This replaces a former kluge that accomplished the latter by pretending zero_damaged_pages was always ON during WAL recovery; which was OK when the kluge was put in, but is unsafe when restoring a WAL log that was written with full_page_writes off.
Heikki Linnakangas
2007-05-02 17:08 tgl Fix things so that when CREATE INDEX CONCURRENTLY sets pg_index.indisvalid true at the very end of its processing, the update is broadcast via a shared-cache-inval message for the index; without this, existing backends that already have relcache entries for the index might never see it become valid. Also, force a relcache inval on the index's parent table at the same time, so that any cached plans for that table are re-planned; this ensures that the newly valid index will be used if appropriate. Aside from making C.I.C. behave more reasonably, this is necessary infrastructure for some aspects of the HOT patch. Pavan Deolasee, with a little further stuff from me.
2007-05-02 11:32 tgl Fix oversight in PG_RE_THROW processing: it's entirely possible that there isn't any place to throw the error to. If so, we should treat the error as FATAL, just as we would have if it'd been thrown outside the PG_TRY block to begin with.
Although this is clearly a *potential* source of bugs, it is not clear at the moment whether it is an *actual* source of bugs; there may not presently be any PG_TRY blocks in code that can be reached with no outer longjmp catcher. So for the moment I'm going to be conservative and not back-patch this. The change breaks ABI for users of PG_RE_THROW and hence might create compatibility problems for loadable modules, so we should not put it into released branches without proof that it's needed.
2007-04-30 17:01 tgl Change the timestamps recorded in transaction commit/abort xlog records from time_t to TimestampTz representation. This provides full gettimeofday() resolution of the timestamps, which might be useful when attempting to do point-in-time recovery --- previously it was not possible to specify the stop point with sub-second resolution. But mostly this is to get rid of TimestampTz-to-time_t conversion overhead during commit. Per my proposal of a day or two back.
2007-04-29 23:23 tgl Implement rate-limiting logic on how often backends will attempt to send messages to the stats collector. This avoids the problem that enabling stats_row_level for autovacuum has a significant overhead for short read-only transactions, as noted by Arjen van der Meijden. We can avoid an extra gettimeofday call by piggybacking on the one done for WAL-logging xact commit or abort (although that doesn't help read-only transactions, since they don't WAL-log anything).
In my proposal for this, I noted that we could change the WAL log entries for commit/abort to record full TimestampTz precision, instead of only time_t as at present. That's not done in this patch, but will be committed separately.
2007-04-28 21:21 neilc Support for MOVE in PL/PgSQL. Initial patch from Magnus, some improvements by Pavel Stehule, and reviewed by Neil Conway.
2007-04-28 19:54 neilc Add support for IN as alternative to FROM in PL/PgSQL's FETCH statement, for consistency with the backend's FETCH command. Patch from Pavel Stehule, reviewed by Neil Conway.
2007-04-26 12:13 neilc Rename the newly-added commands for discarding session state. RESET SESSION, RESET PLANS, and RESET TEMP are now DISCARD ALL, DISCARD PLANS, and DISCARD TEMP, respectively. This is to avoid confusion with the pre-existing RESET variants: the DISCARD commands are not actually similar to RESET. Patch from Marko Kreen, with some minor editorialization.
2007-04-21 17:01 tgl Some further performance tweaks for planning large inheritance trees that are mostly excluded by constraints: do the CE test a bit earlier to save some adjust_appendrel_attrs() work on excluded children, and arrange to use array indexing rather than rt_fetch() to fetch RTEs in the main body of the planner. The latter is something I'd wanted to do for awhile anyway, but seeing list_nth_cell() as 35% of the runtime gets one's attention.
2007-04-21 16:02 petere Make configuration parameters fall back to their default values when they are removed from the configuration file.
Joachim Wieland
2007-04-21 13:26 petere Contrib module uuid-ossp for generating UUID values using the OSSP UUID library. New configure option --with-ossp-uuid to activate.
2007-04-20 15:40 momjian Add script major_release_split to simplify creating release notes for multiple releases.
2007-04-18 12:44 alvherre Enable configurable log of autovacuum actions. Initial patch from Simon Riggs, additional code and docs by me. Per discussion.
2007-04-16 14:29 alvherre Add a multi-worker capability to autovacuum. This allows multiple worker processes to be running simultaneously. Also, now autovacuum processes do not count towards the max_connections limit; they are counted separately from regular processes, and are limited by the new GUC variable autovacuum_max_workers.
The launcher now has intelligence to launch workers on each database every autovacuum_naptime seconds, limited only on the max amount of worker slots available.
Also, the global worker I/O utilization is limited by the vacuum cost-based delay feature. Workers are "balanced" so that the total I/O consumption does not exceed the established limit. This part of the patch was contributed by ITAGAKI Takahiro.
Per discussion.
2007-04-16 13:21 tgl Support scrollable cursors (ie, 'direction' clause in FETCH) in plpgsql. Pavel Stehule, reworked a bit by Tom.
2007-04-15 21:14 tgl Expose more cursor-related functionality in SPI: specifically, allow access to the planner's cursor-related planning options, and provide new FETCH/MOVE routines that allow access to the full power of those commands. Small refactoring of planner(), pg_plan_query(), and pg_plan_queries() APIs to make it convenient to pass the planning options down from SPI.
This is the core-code portion of Pavel Stehule's patch for scrollable cursor support in plpgsql; I'll review and apply the plpgsql changes separately.
2007-04-15 08:48 adunstan Add --with-libxslt configure option
2007-04-15 06:56 ishii Make JOHAB client only encoding per discussions in pgsql-hackers "Server-side support of all encodings" around 2007/3/26. initdb required.
2007-04-13 16:40 mha Allow \timing in psql to have a better resolution than ~15ms on Windows.
ITAGAKI Takahiro
2007-04-13 14:50 adunstan Enable building contrib/xml2 if configured using --with-libxml. If this breaks things due to missing libxslt, then I'll have to revert it, but let's see if it breaks the buildfarm.
Workarounds in case libxslt is missing include: . don't configure with libxml, or . don't build contrib modules from the contrib Makefile (use the individual module Makefiles instead), or . change the xml2 Makefile
2007-04-12 02:53 neilc RESET SESSION, plus related new DDL commands. Patch from Marko Kreen, reviewed by Neil Conway. This patch adds the following DDL command variants: RESET SESSION, RESET TEMP, RESET PLANS, CLOSE ALL, and DEALLOCATE ALL. RESET SESSION is intended for use by connection pool software and the like, in order to reset a client session to something close to its initial state.
Note that while most of these command variants can be executed inside a transaction block (but are not transaction-aware!), RESET SESSION cannot. While this is inconsistent, it is intended to catch programmer mistakes: RESET SESSION in an open transaction block is probably unintended.
2007-04-09 18:03 tgl Minor tweaking of index special-space definitions so that the various index types can be reliably distinguished by examining the special space on an index page. Per my earlier proposal, plus the realization that there's no need for btree's vacuum cycle ID to cycle through every possible 16-bit value. Restricting its range a little costs nearly nothing and eliminates the possibility of collisions. Memo to self: remember to make bitmap indexes play along with this scheme, assuming that patch ever gets accepted.
2007-04-09 14:21 mha Add cancel handlers so it's possible to Ctrl-C clusterdb, reindexdb and vacuumdb. ITAGAKI Takahiro, with minor fixes from me.
2007-04-07 21:26 tgl Make CLUSTER MVCC-safe. Heikki Linnakangas
2007-04-07 21:15 ishii Add -F option to set pgbench fillfactor for tellers, accounts and branches. Patch contributed by Pavan Deolasee. Along with Japanese doc modification by Tatsuo Ishii.
2007-04-07 20:26 momjian Support syntax "CLUSTER table USING index", which is more logical.
Holger Schurig
2007-04-07 12:09 momjian Add the usage count statistics to the information available in contrib/pgbuffercache.
Greg Smith
2007-04-06 18:33 tgl Make 'col IS NULL' clauses be indexable conditions.
Teodor Sigaev, with some kibitzing from Tom Lane.
2007-04-06 05:16 ishii Enhance pgbench -l option to add timestamp. Patch contributed by Greg Smith. Along with Japanese doc updation by Tasuo Ishii.
> This patch changes the way pgbench outputs its latency log files so that > every transaction gets a timestamp and notes which transaction type was > executed. It's a one-line change that just dumps some additional > information that was already sitting in that area of code. I also made a > couple of documentation corrections and clarifications on some of the more > confusing features of pgbench. > > It's straightforward to parse log files in this format to analyze what > happened during the test at a higher level than was possible with the > original format. You can find some rough sample code to convert this > latency format into CVS files and then into graphs at > http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll > be expanding on once I get all my little patches sent in here.
2007-04-06 04:49 ishii Various pgbench enhancements. Patch contributed by ITAGAKI Takahiro.
Also tweak README.pgbench/README.pgbench_jis: Remove history after pgbench was added to PostgreSQL contrib module. Those info was not only redundant since it has already been in CVS log, but also incomplete. ------------------------------------------------------------------- ------- The attached is a patch to optimize contrib/pgbench using new 8.3 features.
- Use DROP IF EXISTS to suppress errors for initial loadings. - Use a combination of TRUNCATE and COPY to reduce WAL on creating the accounts table.
Also, there are some cosmetic changes.
- Change the output of -v option from "starting full vacuum..." to "starting vacuum accounts..." in reflection of the fact. - Shape duplicated error checks into executeStatement().
There is a big performance win in "COPY with no WAL" feature. Thanks for the efforts! ------------------------------------------------------------------- -------
2007-04-06 00:21 tgl Support varlena fields with single-byte headers and unaligned storage.
This commit breaks any code that assumes that the mere act of forming a tuple (without writing it to disk) does not "toast" any fields. While all available regression tests pass, I'm not totally sure that we've fixed every nook and cranny, especially in contrib.
Greg Stark with some help from Tom Lane
2007-04-03 12:34 tgl Remove the CheckpointStartLock in favor of having backends show whether they are in their commit critical sections via flags in the ProcArray. Checkpoint can watch the ProcArray to determine when it's safe to proceed. This is a considerably better solution to the original problem of race conditions between checkpoint and transaction commit: it speeds up commit, since there's one less lock to fool with, and it prevents the problem of checkpoint being delayed indefinitely when there's a constant flow of commits. Heikki, with some kibitzing from Tom.
2007-04-03 11:50 momjian Allow pl/pythonu >= version 2.3 to return boolean, rather than 1/0.
Marko Kreen
2007-04-03 09:37 momjian Allow PL/PythonU to compile on Python 2.5. Change needed because of API changes in Python.
Marko Kreen
2007-04-03 00:14 tgl Decouple the values of TOAST_TUPLE_THRESHOLD and TOAST_MAX_CHUNK_SIZE. Add the latter to the values checked in pg_control, since it can't be changed without invalidating toast table content. This commit in itself shouldn't change any behavior, but it lays some necessary groundwork for experimentation with these toast-control numbers.
Note: while TOAST_TUPLE_THRESHOLD can now be changed without initdb, some thought still needs to be given to needs_toast_table() in toasting.c before unleashing random changes.
2007-04-02 18:20 momjian Allow NOTIFY/LISTEN/UNLISTEN to only take relation names, not schema.relation, because the notify code only honors the relation name. schema.relation will now generate a syntax error.
2007-04-01 23:49 tgl Support enum data types. Along the way, use macros for the values of pg_type.typtype whereever practical. Tom Dunstan, with some kibitzing from Tom Lane.
2007-03-30 14:34 mha Add some instrumentation to the bgwriter, through the stats collector. New view pg_stat_bgwriter, and the functions required to build it.
2007-03-29 11:30 mha Make ECPG regression tests use native threading instead of pthreads, now that ecpglib supports it. Change configure (patch from Bruce) and msvc build system to no longer require pthreads on win32, since all parts of postgresql can be thread-safe using the native platform functions.
2007-03-29 08:02 meskes Added patch by Magnus Hagander <magnus@hagander.net> to use native win32 threads. - Fixed regression tests to run threading tests.
2007-03-28 20:15 tgl Teach CLUSTER to skip writing WAL if not needed (ie, not using archiving) --- Simon. Also, code review and cleanup for the previous COPY-no-WAL patches --- Tom.
2007-03-28 18:48 neilc Replace pgcrypto 4-clause licensed blf.[ch] with blowfish implementation from PuTTY with is under minimal BSD/MIT license. Marko Kreen.
2007-03-28 18:17 alvherre Add the "recheck" logic to autovacuum worker code. The worker first builds its table list and then rechecks pgstat before vacuuming each table to verify that no one has vacuumed the table in the meantime.
In the current autovacuum world this only means that a worker will not vacuum a table that a user has vacuumed manually after the worker started. When support for multiple autovacuum workers is introduced, this will reduce the probability of simultaneous workers on the same database doing redundant work.
2007-03-27 19:21 tgl Fix array coercion expressions to ensure that the correct volatility is seen by code inspecting the expression. The best way to do this seems to be to drop the original representation as a function invocation, and instead make a special expression node type that represents applying the element-type coercion function to each array element. In this way the element function is exposed and will be checked for volatility. Per report from Guillaume Smet.
2007-03-26 17:44 momjian Remove advertising clause from Berkeley BSD-licensed files, per instructions from Berkeley.
2007-03-26 12:58 tgl Allow non-superuser database owners to create procedural languages. A DBA is allowed to create a language in his database if it's marked "tmpldbacreate" in pg_pltemplate. The factory default is that this is set for all standard trusted languages, but of course a superuser may adjust the settings. In service of this, add the long-foreseen owner column to pg_language; renaming, dropping, and altering owner of a PL now follow normal ownership rules instead of being superuser-only. Jeremy Drake, with some editorialization by Tom Lane.
2007-03-25 19:27 tgl Remove the prohibition on executing cursor commands through SPI_execute. Vadim had included this restriction in the original design of the SPI code, but I'm darned if I can see a reason for it.
I left the macro definition of SPI_ERROR_CURSOR in place, so as not to needlessly break any SPI callers that are checking for it, but that code will never actually be returned anymore.
2007-03-25 15:45 tgl Clean up the representation of special snapshots by including a "method pointer" in every Snapshot struct. This allows removal of the case-by-case tests in HeapTupleSatisfiesVisibility, which should make it a bit faster (I didn't try any performance tests though). More importantly, we are no longer violating portable C practices by assuming that small integers are distinct from all pointer values, and HeapTupleSatisfiesDirty no longer has a non-reentrant API involving side-effects on a global variable.
There were a couple of places calling HeapTupleSatisfiesXXX routines directly rather than through the HeapTupleSatisfiesVisibility macro. Since these places had to be changed anyway, I chose to make them go through the macro for uniformity.
Along the way I renamed HeapTupleSatisfiesSnapshot to HeapTupleSatisfiesMVCC to emphasize that it's only used with MVCC-type snapshots. I was sorely tempted to rename HeapTupleSatisfiesVisibility to HeapTupleSatisfiesSnapshot, but forebore for the moment to avoid confusion and reduce the likelihood that this patch breaks some of the pending patches. Might want to reconsider doing that later.
2007-03-25 07:56 ishii Add new encoding EUC_JIS_2004 and SHIFT_JIS_2004, along with new conversions among EUC_JIS_2004, SHIFT_JIS_2004 and UTF-8. catalog version has been bump up.
2007-03-23 09:51 ishii Allow 4 bytes UTF-8 (UCS-4 range 00010000-001FFFFF) This is necessary to support JIS X 0213 <--> UTF-8 conversion.
2007-03-22 16:14 momjian Add xmlpath() to evaluate XPath expressions, with namespaces support.
Nikolay Samokhvalov
2007-03-22 15:55 tgl Arrange for PreventTransactionChain to reject commands submitted as part of a multi-statement simple-Query message. This bug goes all the way back, but unfortunately is not nearly so easy to fix in existing releases; it is only the recent ProcessUtility API change that makes it fixable in HEAD. Per report from William Garrison.
2007-03-22 15:53 momjian
Allow the pgstat process to restart immediately after a receiving SIGQUIT signal, rather than waiting for PGSTAT_RESTART_INTERVAL.
2007-03-22 15:51 tgl Allow DROP TABLESPACE to succeed (with a warning) if the pg_tblspc symlink doesn't exist. This allows DROP to be used to clean out the pg_tablespace catalog entry in a situation where a previous DROP attempt failed before committing but after having removed the directories and symlink.
Per report from William Garrison. Even though his test case depends on an unrelated bug in PreventTransactionChain, it's certainly possible for this situation to arise due to other problems, eg a system crash at just the right time.
2007-03-22 15:42 momjian Properly enforce pg_dump -F formation options; only single letter or full words support, per report from Mark Stosberg.
2007-03-22 14:57 alvherre Remove the currently unused FRONTEND case in dllist.c. This allows the usage of palloc instead of malloc, which means a list can be freed simply by deleting the memory context that contains it.
2007-03-21 18:18 tgl Fix some problems with selectivity estimation for partial indexes.
First, genericcostestimate() was being way too liberal about including partial-index conditions in its selectivity estimate, resulting in substantial underestimates for situations such as an indexqual "x = 42" used with an index on x "WHERE x >= 40 AND x < 50". While the code is intentionally set up to favor selecting partial indexes when available, this was too much...
Second, choose_bitmap_and() was likewise easily fooled by cases of this type, since it would similarly think that the partial index had selectivity independent of the indexqual.
Fixed by using predicate_implied_by() rather than simple equality checks to determine redundancy. This is a good deal more expensive but I don't see much alternative. At least the extra cost is only paid when there's actually a partial index under consideration.
Per report from Jeff Davis. I'm not going to risk back-patching this, though.
2007-03-21 10:39 mha Native shared memory implementation for win32. Uses same underlying tech as before, but not the sysv emulation layer.
2007-03-20 01:44 neilc Add three new regexp functions: regexp_matches, regexp_split_to_array, and regexp_split_to_table. These functions provide access to the capture groups resulting from a POSIX regular expression match, and provide the ability to split a string on a POSIX regular expression, respectively. Patch from Jeremy Drake; code review by Neil Conway, additional comments and suggestions from Tom and Peter E.
This patch bumps the catversion, adds some regression tests, and updates the docs.
2007-03-19 19:38 wieck Changes pg_trigger and extend pg_rewrite in order to allow triggers and rules to be defined with different, per session controllable, behaviors for replication purposes.
This will allow replication systems like Slony-I and, as has been stated on pgsql-hackers, other products to control the firing mechanism of triggers and rewrite rules without modifying the system catalog directly.
The firing mechanisms are controlled by a new superuser-only GUC variable, session_replication_role, together with a change to pg_trigger.tgenabled and a new column pg_rewrite.ev_enabled. Both columns are a single char data type now (tgenabled was a bool before). The possible values in these attributes are:
'O' - Trigger/Rule fires when session_replication_role is "origin" (default) or "local". This is the default behavior.
'D' - Trigger/Rule is disabled and fires never
'A' - Trigger/Rule fires always regardless of the setting of session_replication_role
'R' - Trigger/Rule fires when session_replication_role is "replica"
The GUC variable can only be changed as long as the system does not have any cached query plans. This will prevent changing the session role and accidentally executing stored procedures or functions that have plans cached that expand to the wrong query set due to differences in the rule firing semantics.
The SQL syntax for changing a triggers/rules firing semantics is
ALTER TABLE <tabname> <when> TRIGGER|RULE <name>;
<when> ::= ENABLE | ENABLE ALWAYS | ENABLE REPLICA | DISABLE
psql's \d command as well as pg_dump are extended in a backward compatible fashion.
Jan
2007-03-17 15:25 meskes Changed some whitespacing in connect statement. - Made some chars const as proposed by Stefan Huehner <stefan@huehner.org>. - Synced parser and keyword lists. - Copied two token parsing from backend parser to ecpg parser. - Also added a test case for this.
2007-03-16 20:11 tgl Fix up the remaining places where the expression node structure would lose available information about the typmod of an expression; namely, Const, ArrayRef, ArrayExpr, and EXPR and ARRAY SubLinks. In the ArrayExpr and SubLink cases it wasn't really the data structure's fault, but exprTypmod() being lazy. This seems like a good idea in view of the expected increase in typmod usage from Teodor's work to allow user-defined types to have typmods. In particular this responds to the concerns we had about eliminating the special-purpose hack that exprTypmod() used to have for BPCHAR Consts. We can now tell whether or not such a Const has been cast to a specific length, and report or display properly if so.
initdb forced due to changes in stored rules.
2007-03-16 13:57 mha Add new columns for tuple statistics on a database level to pg_stat_database.
2007-03-16 09:41 adunstan Remove undocumented support for copy syntax from before 7.3. Update comments to reflect syntax actually supported, e.g. by including CSV params.
2007-03-16 04:28 mha Show aggregate return types in psql \da output.
Greg Sabino Mullane
2007-03-15 19:12 tgl Make use of plancache module for SPI plans. In particular, since plpgsql uses SPI plans, this finally fixes the ancient gotcha that you can't drop and recreate a temp table used by a plpgsql function.
Along the way, clean up SPI's API a little bit by declaring SPI plan pointers as "SPIPlanPtr" instead of "void *". This is cosmetic but helps to forestall simple programming mistakes. (I have changed some but not all of the callers to match; there are still some "void *"'s in contrib and the PL's. This is intentional so that we can see if anyone's compiler complains about it.)
2007-03-14 10:21 teodor Add GIN support for pg_trgm. From Guillaume Smet <guillaume.smet@gmail.com> with minor editorization by me.
Hstore improvements * add operation hstore ? text - excat equivalent of exist() * remove undocumented behaviour of contains operation with NULL value * now 'key'::text=>NULL returns '"key"=>NULL' instead of NULL * Add GIN support for contains and exist operations * Add GiST support for exist operatiion * improve regression tests
2007-03-14 10:15 teodor Add GIN support for pg_trgm. From Guillaume Smet <guillaume.smet@gmail.com> with minor editorization by me.
2007-03-13 12:03 mha Rewrite win32 install documentation (it's not client only anymore, and it's now complete). Update for the MSVC6/Borland support now being only libpq. Move most of the information about full MSVC build from README file into documentation.
2007-03-12 20:33 tgl First phase of plan-invalidation project: create a plan cache management module and teach PREPARE and protocol-level prepared statements to use it. In service of this, rearrange utility-statement processing so that parse analysis does not assume table schemas can't change before execution for utility statements (necessary because we don't attempt to re-acquire locks for utility statements when reusing a stored plan). This requires some refactoring of the ProcessUtility API, but it ends up cleaner anyway, for instance we can get rid of the QueryContext global.
Still to do: fix up SPI and related code to use the plan cache; I'm tempted to try to make SQL functions use it too. Also, there are at least some aspects of system state that we want to ensure remain the same during a replan as in the original processing; search_path certainly ought to behave that way for instance, and perhaps there are others.
2007-03-11 00:22 alvherre Fix a race condition that caused pg_database_size() and pg_tablespace_size() to fail if an object was removed between calls to ReadDir() and stat(). Per discussion in pgsql-hackers.
http://archives.postgresql.org/pgsql-hackers/2007-03/msg00671.php
Bug report and patch by Michael Fuhr.
2007-03-05 09:18 mha Remove old-style win32 client-only visual c++ build infrastructure for everything except libpq. We need to keep libpq to build static libraries and to use PQtrace with clients using older versions of MSVC.
2007-03-03 14:52 momjian Add lo_truncate() to backend and libpq for large object truncation.
Kris Jurka
2007-03-03 14:32 neilc Add resetStringInfo(), which clears the content of a StringInfo, and fixup various places in the tree that were clearing a StringInfo by hand. Making this function a part of the API simplifies client code slightly, and avoids needlessly peeking inside the StringInfo interface.
2007-03-03 13:46 momjian Add GUC log_lock_waits to log long wait times.
Simon Riggs
2007-03-03 12:19 momjian Allow \pset and \x, \t to use boolean constants on/off, rather than just toggle.
Chad Wagner
2007-03-02 18:37 tgl Make log_min_error_statement put LOG level at the same priority as log_min_messages does; and arrange to suppress the duplicative output that would otherwise result from log_statement and log_duration messages. Bruce Momjian and Tom Lane.
2007-03-01 19:48 tgl Suppress useless searches for unused line pointers in PageAddItem. To do this, add a 16-bit "flags" field to page headers by stealing some bits from pd_tli. We use one flag bit as a hint to indicate whether there are any unused line pointers; the remaining 15 are available for future use.
This is a cut-down form of an idea proposed by Hiroki Kataoka in July 2005. At the time it was rejected because the original patch increased the size of page headers and it wasn't clear that the benefit outweighed the distributed cost. The flag-bit approach gets most of the benefit without requiring an increase in the page header size.
Heikki Linnakangas and Tom Lane
2007-02-27 18:48 tgl Replace direct assignments to VARATT_SIZEP(x) with SET_VARSIZE(x, len). Get rid of VARATT_SIZE and VARATT_DATA, which were simply redundant with VARSIZE and VARDATA, and as a consequence almost no code was using the longer names. Rename the length fields of struct varlena and various derived structures to catch anyplace that was accessing them directly; and clean up various places so caught. In itself this patch doesn't change any behavior at all, but it is necessary infrastructure if we hope to play any games with the representation of varlena headers. Greg Stark and Tom Lane
2007-02-27 13:49 petere Allow information schema definitions to work without requiring implicit casts to text.
2007-02-23 16:59 tgl Now that plans have flat rangetable lists, it's a lot easier to get EXPLAIN to drill down into subplan targetlists to print the referent expression for an OUTER or INNER var in an upper plan node. Hence, make it do that always, and banish the old hack of showing "?columnN?" when things got too complicated.
Along the way, fix an EXPLAIN bug I introduced by suppressing subqueries from execution-time range tables: get_name_for_var_field() assumed it could look at rte->subquery to find out the real type of a RECORD var. That doesn't work anymore, but instead we can look at the input plan of the SubqueryScan plan node.
2007-02-23 13:20 momjian Add psql \prompt capability.
Chad Wagner
2007-02-22 17:00 tgl Turn the rangetable used by the executor into a flat list, and avoid storing useless substructure for its RangeTblEntry nodes. (I chose to keep using the same struct node type and just zero out the link fields for unneeded info, rather than making a separate ExecRangeTblEntry type --- it seemed too fragile to have two different rangetable representations.)
Along the way, put subplans into a list in the toplevel PlannedStmt node, and have SubPlan nodes refer to them by list index instead of direct pointers. Vadim wanted to do that years ago, but I never understood what he was on about until now. It makes things a *whole* lot more robust, because we can stop worrying about duplicate processing of subplans during expression tree traversals. That's been a constant source of bugs, and it's finally gone.
There are some consequent simplifications yet to be made, like not using a separate EState for subplans in the executor, but I'll tackle that later.
2007-02-21 10:12 momjian Add configure --enable-profiling to enable GCC profiling. Patches from Korry Douglas and Nikhil S
2007-02-20 22:27 adunstan Allow pltcl args to spi_prepare and plpython args to plpy.prepare to be standard type aliases as well as those known in pg_type. Similar to recent change in plperl.
2007-02-20 18:49 momjian Prevent BLCKSZ < 1024, and have initdb test shared buffers based on the BLCKSZ value.
2007-02-20 12:32 tgl Remove the Query structure from the executor's API. This allows us to stop storing mostly-redundant Query trees in prepared statements, portals, etc. To replace Query, a new node type called PlannedStmt is inserted by the planner at the top of a completed plan tree; this carries just the fields of Query that are still needed at runtime. The statement lists kept in portals etc. now consist of intermixed PlannedStmt and bare utility-statement nodes --- no Query. This incidentally allows us to remove some fields from Query and Plan nodes that shouldn't have been there in the first place.
Still to do: simplify the execution-time range table; at the moment the range table passed to the executor still contains Query trees for subqueries.
initdb forced due to change of stored rules.
2007-02-20 05:23 petere Identify schema of inherited table in psql \d when necessary.
by Bernd Helmle
2007-02-19 12:41 momjian Add "isodow" option to EXTRACT() and date_part() where Sunday = 7.
2007-02-16 22:11 momjian Remove rint() for to_char MS and US output. We can't us rint() because we can't overflow to the next higher units, and we might print the lower units for MS.
2007-02-16 19:55 momjian Create AVG() aggregates for int8 and NUMERIC which do not compute X^2, as a performance enhancement.
Mark Kirkwood
2007-02-16 18:32 tgl Teach find_nonnullable_rels to handle OR cases: if every arm of an OR forces a particular relation nonnullable, then we can say that the OR does. This is worth a little extra trouble since it may allow reduction of outer joins to plain joins.
2007-02-16 02:46 petere Functions for mapping table data and table schemas to XML (a.k.a. XML export)
2007-02-15 22:39 momjian Add two new format fields for use with to_char(), to_date() and to_timestamp(): - ID for day-of-week - IDDD for day-of-year
This makes it possible to convert ISO week dates to and from text fully represented in either week ('IYYY-IW-ID') or day-of-year ('IYYY-IDDD') format.
I have also added an 'isoyear' field for use with extract / date_part.
Brendan Jurd
2007-02-15 21:59 momjian SSL improvements:
o read global SSL configuration file o add GUC "ssl_ciphers" to control allowed ciphers o add libpq environment variable PGSSLKEY to control SSL hardware keys
Victor B. Wagner
2007-02-15 18:23 alvherre Restructure autovacuum in two processes: a dummy process, which runs continuously, and requests vacuum runs of "autovacuum workers" to postmaster. The workers do the actual vacuum work. This allows for future improvements, like allowing multiple autovacuum jobs running in parallel.
For now, the code keeps the original behavior of having a single autovac process at any time by sleeping until the previous worker has finished.
2007-02-14 00:10 momjian Fix to_date()/to_timestamp() 'D' field for day of week, was off by one. Converting from char using 'D' doesn't make lots of sense, of course.
Report from Brendan Jurd.
2007-02-13 20:58 tgl Fix up foreign-key mechanism so that there is a sound semantic basis for the equality checks it applies, instead of a random dependence on whatever operators might be named "=". The equality operators will now be selected from the opfamily of the unique index that the FK constraint depends on to enforce uniqueness of the referenced columns; therefore they are certain to be consistent with that index's notion of equality. Among other things this should fix the problem noted awhile back that pg_dump may fail for foreign-key constraints on user-defined types when the required operators aren't in the search path. This also means that the former warning condition about "foreign key constraint will require costly sequential scans" is gone: if the comparison condition isn't indexable then we'll reject the constraint entirely. All per past discussions.
Along the way, make the RI triggers look into pg_constraint for their information, instead of using pg_trigger.tgargs; and get rid of the always error-prone fixed-size string buffers in ri_triggers.c in favor of building up the RI queries in StringInfo buffers.
initdb forced due to columns added to pg_constraint and pg_trigger.
2007-02-13 14:18 tgl Improve postmaster's behavior if an accept() call fails. Because the server socket is still read-ready, the code was a tight loop, wasting lots of CPU. We can't do anything to clear the failure, other than wait, but we should give other processes more chance to finish and release FDs; so insert a small sleep. Also, avoid bogus "close(-1)" in this case. Per report from Jim Nasby.
2007-02-10 09:58 petere StrNCpy -> strlcpy (not complete)
2007-02-09 11:12 tgl Call pgstat_drop_database during DROP DATABASE, so that any stats file entries for the victim database go away sooner rather than later. We already did the equivalent thing at the per-relation level, not sure why it's not been done for whole databases. With this change, pgstat_vacuum_tabstat should usually not find anything to do; though we still need it as a backstop in case DROPDB or TABPURGE messages get lost under load.
2007-02-08 22:35 tgl Combine cmin and cmax fields of HeapTupleHeaders into a single field, by keeping private state in each backend that has inserted and deleted the same tuple during its current top-level transaction. This is sufficient since there is no need to be able to determine the cmin/cmax from any other transaction. This gets us back down to 23-byte headers, removing a penalty paid in 8.0 to support subtransactions. Patch by Heikki Linnakangas, with minor revisions by moi, following a design hashed out awhile back on the pghackers list.
2007-02-08 10:28 momjian Win32 regression test fixes:
For win32 in general, this makes it possible to run the regression tests as an admin user by using the same restricted token method that's used by pg_ctl and initdb.
For vc++, it adds building of pg_regress.exe, adds a resultmap, and fixes how it runs the install.
Magnus Hagander
2007-02-08 10:16 momjian Add /contrib/pg_standby:
pg_standby is a production-ready program that can be used to create a Warm Standby server. Other configuration is required as well, all of which is described in the main server manual.
Simon Riggs
2007-02-08 00:05 momjian
* src/: backend/access/nbtree/nbtinsert.c, backend/access/nbtree/nbtxlog.c, include/access/nbtree.h: Reduce WAL activity for page splits:
> Currently, an index split writes all the data on the split page to > WAL. That's a lot of WAL traffic. The tuples that are copied to the > right page need to be WAL logged, but the tuples that stay on the > original page don't.
Heikki Linnakangas
2007-02-07 22:22 momjian Fix bug in our code when using to_timestamp() or to_date() without "TM".
Assume "TM" when input fields are variable-length, like month or day names. This matches Oracle behavior.
2007-02-07 18:11 tgl Add a function pg_stat_clear_snapshot() that discards any statistics snapshot already collected in the current transaction; this allows plpgsql functions to watch for stats updates even though they are confined to a single transaction. Use this instead of the previous kluge involving pg_stat_file() to wait for the stats collector to update in the stats regression test. Internally, decouple storage of stats snapshots from transaction boundaries; they'll now stick around until someone calls pgstat_clear_snapshot --- which xact.c still does at transaction end, to maintain the previous behavior. This makes the logic a lot cleaner, at the price of a couple dozen cycles per transaction exit.
2007-02-07 13:34 tgl Modify the stats regression test to delay until the stats file actually changes (with an upper limit of 30 seconds), and record the delay time in the postmaster log. This should give us some info about what's happening with the intermittent stats failures in buildfarm. After an idea of Andrew Dunstan's.
2007-02-07 11:44 tgl Remove the xlog-centric "database system is ready" message and replace it with "database system is ready to accept connections", which is issued by the postmaster when it really is ready to accept connections. Per proposal from Markus Schiltknecht and subsequent discussion.
2007-02-06 19:52 petere Replace some strncpy() by strlcpy().
2007-02-06 19:28 petere Add strlcat() from OpenBSD, to be used for replacing strncat and other strange coding practices.
2007-02-06 04:16 petere Move NAMEDATALEN definition from postgres_ext.h to pg_config_manual.h. It used to be part of libpq's exported interface many releases ago, but now it's no longer necessary to make it accessible to clients.
2007-02-05 21:59 tgl Add support for cross-type hashing in hashed subplans (hashed IN/NOT IN cases that aren't turned into true joins). Since this is the last missing bit of infrastructure, go ahead and fill out the hash integer_ops and float_ops opfamilies with cross-type operators. The operator family project is now DONE ... er, except for documentation ...
2007-02-04 23:22 tgl Rename MaxTupleSize to MaxHeapTupleSize to clarify that it's not meant to describe the maximum size of index tuples (which is typically AM-dependent anyway); and consequently remove the bogus deduction for "special space" that was built into it.
Adjust TOAST_TUPLE_THRESHOLD and TOAST_MAX_CHUNK_SIZE to avoid wasting two bytes per toast chunk, and to ensure that the calculation correctly tracks any future changes in page header size. The computation had been inaccurate in a way that didn't cause any harm except space wastage, but future changes could have broken it more drastically.
Fix the calculation of BTMaxItemSize, which was formerly computed as 1 byte more than it could safely be. This didn't cause any harm in practice because it's only compared against maxalign'd lengths, but future changes in the size of page headers or btree special space could have exposed the problem.
initdb forced because of change in TOAST_MAX_CHUNK_SIZE, which alters the storage of toast tables.
2007-02-03 09:06 petere Implement XMLSERIALIZE for real. Analogously, make the xml to text cast observe the xmloption.
Reorganize the representation of the XML option in the parse tree and the API to make it easier to manage and understand.
Add regression tests for parsing back XML expressions.
2007-01-31 10:09 teodor Allow GIN's extractQuery method to signal that nothing can satisfy the query. In this case extractQuery should returns -1 as nentries. This changes prototype of extractQuery method to use int32* instead of uint32* for nentries argument. Based on that gincostestimate may see two corner cases: nothing will be found or seqscan should be used.
Per proposal at http://archives.postgresql.org/pgsql-hackers/2007-01/msg01581.php
PS tsearch_core patch should be sightly modified to support changes, but I'm waiting a verdict about reviewing of tsearch_core patch.
2007-01-29 20:33 tgl Add support for cross-type hashing in hash index searches and hash joins. Hashing for aggregation purposes still needs work, so it's not time to mark any cross-type operators as hashable for general use, but these cases work if the operators are so marked by hand in the system catalogs.
2007-01-29 15:17 momjian Update process termination message to display signal number and name from exec.c and postmaster.c.
2007-01-28 18:21 tgl Improve hash join to discard input tuples immediately if they can't match because they contain a null join key (and the join operator is known strict). Improves performance significantly when the inner relation contains a lot of nulls, as per bug #2930.
2007-01-28 12:58 tgl Make some small improvements in the accuracy of plpgsql's error location reports; inspired by the misleading CONTEXT lines shown in recent bug report from Stefan Kaltenbrunner. Also, allow statement-type names shown in these messages to be translated.
2007-01-28 11:16 neilc Add a new builtin type, "uuid". This implements a UUID type, similar to that defined in RFC 4122. This patch includes the basic implementation, plus regression tests. Documentation and perhaps some additional functionality will come later. Catversion bumped.
Patch from Gevik Babakhani; review from Peter, Tom, and myself.
2007-01-27 17:44 momjian Modify SGML makefile to allow 'gmake draft' to build draft html ('draft' is now a target, no longer a modifier).
2007-01-26 20:55 adunstan Allow args to spi_prepare to be standard type aliaes as well as those known in pg_type. Fixes bug #2917. Add some regression tests for these cases.
2007-01-24 22:30 momjian Add 'output file' option for pg_dumpall, especially useful for Win32, where output redirection of child processes (pg_dump) doesn't work.
Dave Page
2007-01-24 21:46 momjian Allow pg_dumpall to specify a database name rather than the default 'template1'.
Dave Page
2007-01-24 21:30 momjian Add --tablespaces-only and --roles-only options to pg_dumpall.
Dave Page
2007-01-24 21:17 momjian Prevent WAL logging when COPY is done in the same transation that created it.
Simon Riggs
2007-01-23 00:07 tgl Add CREATE/ALTER/DROP OPERATOR FAMILY commands, also COMMENT ON OPERATOR FAMILY; and add FAMILY option to CREATE OPERATOR CLASS to allow adding a class to a pre-existing family. Per previous discussion. Man, what a tedious lot of cutting and pasting ...
2007-01-21 20:35 tgl Add COST and ROWS options to CREATE/ALTER FUNCTION, plus underlying pg_proc columns procost and prorows, to allow simple user adjustment of the estimated cost of a function call, as well as control of the estimated number of rows returned by a set-returning function. We might eventually wish to extend this to allow function-specific estimation routines, but there seems to be consensus that we should try a simple constant estimate first. In particular this provides a relatively simple way to control the order in which different WHERE clauses are applied in a plan node, which is a Good Thing in view of the fact that the recent EquivalenceClass planner rewrite made that much less predictable than before.
2007-01-20 18:13 tgl Simplify pg_am representation of ordering-capable access methods: provide just a boolean 'amcanorder', instead of fields that specify the sort operator strategy numbers. We have decided to require ordering-capable AMs to use btree-compatible strategy numbers, so the old fields are overkill (and indeed misleading about what's allowed).
2007-01-20 16:47 neilc Make setseed() return void, rather than an int4 without any use. Per pgsql-patches discussion of September 20, 2006. Bump the catversion.
2007-01-20 16:17 neilc List disabled triggers separately in psql display table output. Previously, disabled triggers were not displayed any differently than enabled ones, which was quite misleading. Patch from Brendan Jurd.
2007-01-20 15:45 tgl Refactor planner's pathkeys data structure to create a separate, explicit representation of equivalence classes of variables. This is an extensive rewrite, but it brings a number of benefits: * planner no longer fails in the presence of "incomplete" operator families that don't offer operators for every possible combination of datatypes. * avoid generating and then discarding redundant equality clauses. * remove bogus assumption that derived equalities always use operators named "=". * mergejoins can work with a variety of sort orders (e.g., descending) now, instead of tying each mergejoinable operator to exactly one sort order. * better recognition of redundant sort columns. * can make use of equalities appearing underneath an outer join.
2007-01-20 13:43 neilc Refactor the index AM API slightly: move currentItemData and currentMarkData from IndexScanDesc to the opaque structs for the AMs that need this information (currently gist and hash).
Patch from Heikki Linnakangas, fixes by Neil Conway.
2007-01-19 11:58 petere Add support for converting binary values (i.e. bytea) into xml values, with new GUC parameter "xmlbinary" that controls the output encoding, as per SQL/XML standard.
2007-01-16 16:41 neilc Implement width_bucket() for the float8 data type.
The implementation is somewhat ugly logic-wise, but I don't see an easy way to make it more concise.
When writing this, I noticed that my previous implementation of width_bucket() doesn't handle NaN correctly:
postgres=# select width_bucket('NaN', 1, 5, 5); width_bucket -------------- 6 (1 row)
AFAICS SQL:2003 does not define a NaN value, so it doesn't address how width_bucket() should behave here. The patch changes width_bucket() so that ereport(ERROR) is raised if NaN is specified for the operand or the lower or upper bounds to width_bucket(). For float8, NaN is disallowed for any of the floating-point inputs, and +/- infinity is disallowed for the histogram bounds (but allowed for the operand).
Update docs and regression tests, bump the catversion.
2007-01-16 13:26 alvherre Enable autovacuum in the default configuration, per discussion.
2007-01-16 08:28 alvherre Arrange for autovacuum to be killed when another operation wants to be alone accessing it, like DROP DATABASE. This allows the regression tests to pass with autovacuum enabled, which open the gates for finally enabling autovacuum by default.
2007-01-12 05:00 meskes Simplified regression handling Added patch by Joachim to work around OpenBSD bug in regression suite.
2007-01-11 10:47 meskes Applied Joachim's patch for a --regression option. Made this option mark the .c files, so the environment variable is no longer needed. Created a special MinGW file with the special error message. Do not print port into log file when running regression tests.
2007-01-10 13:06 tgl Change the planner-to-executor API so that the planner tells the executor which comparison operators to use for plan nodes involving tuple comparison (Agg, Group, Unique, SetOp). Formerly the executor looked up the default equality operator for the datatype, which was really pretty shaky, since it's possible that the data being fed to the node is sorted according to some nondefault operator class that could have an incompatible idea of equality. The planner knows what it has sorted by and therefore can provide the right equality operator to use. Also, this change moves a couple of catalog lookups out of the executor and into the planner, which should help startup time for pre-planned queries by some small amount. Modify the planner to remove some other cavalier assumptions about always being able to use the default operators. Also add "nulls first/last" info to the Plan node for a mergejoin --- neither the executor nor the planner can cope yet, but at least the API is in place.
2007-01-09 17:00 momjian Enable another five tuple status bits by using the high bits of the nattr field, and rename the field.
Heikki Linnakangas
2007-01-09 16:31 momjian Add GUC log_temp_files to log the use of temporary files.
Bill Moran
2007-01-08 21:14 tgl Support ORDER BY ... NULLS FIRST/LAST, and add ASC/DESC/NULLS FIRST/NULLS LAST per-column options for btree indexes. The planner's support for this is still pretty rudimentary; it does not yet know how to plan mergejoins with nondefault ordering options. The documentation is pretty rudimentary, too. I'll work on improving that stuff later.
Note incompatible change from prior behavior: ORDER BY ... USING will now be rejected if the operator is not a less-than or greater-than member of some btree opclass. This prevents less-than-sane behavior if an operator that doesn't actually define a proper sort ordering is selected.
2007-01-06 14:40 momjian Allow initdb to specify the pg_xlog directory.
Euler Taveira de Oliveira
2007-01-05 11:17 adunstan Call setrlimit if possible in pg_regress to allow core file generation, and provide a switch for similar behaviour in pg_ctl.
2007-01-03 13:11 tgl Clean up smgr.c/md.c APIs as per discussion a couple months ago. Instead of having md.c return a success/failure boolean to smgr.c, which was just going to elog anyway, let md.c issue the elog messages itself. This allows better error reporting, particularly in cases such as "short read" or "short write" which Peter was complaining of. Also, remove the kluge of allowing mdread() to return zeroes from a read-beyond-EOF: this is now an error condition except when InRecovery or zero_damaged_pages = true. (Hash indexes used to require that behavior, but no more.) Also, enforce that mdwrite() is to be used for rewriting existing blocks while mdextend() is to be used for extending the relation EOF. This restriction lets us get rid of the old ad-hoc defense against creating huge files by an accidental reference to a bogus block number: we'll only create new segments in mdextend() not mdwrite() or mdread(). (Again, when InRecovery we allow it anyway, since we need to allow updates of blocks that were later truncated away.) Also, clean up the original makeshift patch for bug #2737: move the responsibility for padding relation segments to full length into md.c.
2007-01-03 09:35 momjian Attempt to return proper overflow/underflow messages for platforms that only return Nan and set errno for pow/exp overflow/underflow.
2007-01-02 20:19 darcy Widen the money type to 64 bits.
2007-01-02 16:25 momjian finite() no longer used; remove finite() platform-specific infrastructure.
2007-01-02 15:59 momjian Add n_live_tuples and n_dead_tuples to pg_stat_all_tables.
The purpose is to allow autovacuum-esq conditional vacuuming and clustering using SQL to discover the required stats.
No documentation updates required. Catalog version updated.
Glen Parker
2007-01-02 15:00 momjian Fix float4/8 to handle Infinity and Nan consistently, e.g. Infinity is a valid result from a computation if one of the input values was infinity. The previous code assumed an operation that returned infinity was an overflow.
Handle underflow/overflow consistently, and add checks for aggregate overflow.
Consistently prevent Inf/Nan from being cast to integer data types.
Fix INT_MIN % -1 to prevent overflow.
Update regression results for new error text.
Per report from Roman Kononov.
2006-12-30 16:21 tgl Support type modifiers for user-defined types, and pull most knowledge about typmod representation for standard types out into type-specific typmod I/O functions. Teodor Sigaev, with some editorialization by Tom Lane.
2006-12-27 19:29 tgl fflush the \o file, if any, after each backslash command. We already do this for ordinary SQL commands, so it seems consistent to do it for backslash commands too. Per gripe from Rajesh Kumar Mallah.
2006-12-27 17:30 tgl Improve memory management code to avoid inefficient behavior when a context has a small maxBlockSize: the maximum request size that we will treat as a "chunk" needs to be limited to fit in maxBlockSize. Otherwise we will round up the request size to the next power of 2, wasting space, which is a bit pointless if we aren't going to make the blocks big enough to fit additional stuff in them. The example motivating this is local buffer management, which makes repeated allocations of 8K (one BLCKSZ buffer) in TopMemoryContext, which has maxBlockSize = 8K because for the most part allocations there are small. This leads to each local buffer actually eating 16K of space, which adds up when there are thousands of them. I intend to change localbuf.c to aggregate its requests, which will prevent this particular misbehavior, but it seems likely that similar scenarios could arise elsewhere, so fixing the core problem seems wise as well.
2006-12-22 19:52 momjian For GUC values, check for partial string matches on 'on' and 'off', but require at least two characters for uniqueness. This now matches the behavior of other boolean strings we support, per report from Gurjeet Singh.
2006-12-22 19:43 tgl Restructure operator classes to allow improved handling of cross-data-type cases. Operator classes now exist within "operator families". While most families are equivalent to a single class, related classes can be grouped into one family to represent the fact that they are semantically compatible. Cross-type operators are now naturally adjunct parts of a family, without having to wedge them into a particular opclass as we had done originally.
This commit restructures the catalogs and cleans up enough of the fallout so that everything still works at least as well as before, but most of the work needed to actually improve the planner's behavior will come later. Also, there are not yet CREATE/DROP/ALTER OPERATOR FAMILY commands; the only way to create a new family right now is to allow CREATE OPERATOR CLASS to make one by default. I owe some more documentation work, too. But that can all be done in smaller pieces once this infrastructure is in place.
2006-12-21 11:05 petere Initial SQL/XML support: xml data type and initial set of functions.
2006-12-18 20:53 adunstan
* doc/src/sgml/libpq.sgml, doc/src/sgml/ref/psql-ref.sgml, src/interfaces/libpq/fe-connect.c: Interpret a dbName param to PQsetdbLogin as a conninfo string if it contains an = sign. Tom Lane and Andrew Dunstan.
2006-12-18 13:56 tgl Set pg_am.amstrategies to zero for index AMs that don't have fixed operator strategy numbers, ie, GiST and GIN. This is almost cosmetic enough to not need a catversion bump, but since the opr_sanity regression test has to change in sync with the catalog entry, I figured I'd better do one.
2006-12-15 19:38 adunstan enable \timing oputput for \copy commands
2006-12-12 14:43 petere Allow augmenting CPPFLAGS from the configure command line. This generally works, but some platform templates overwrote it without asking.
2006-12-10 17:13 tgl Add a paramtypmod field to Param nodes. This is dead weight for Params representing externally-supplied values, since the APIs that carry such values only specify type not typmod. However, for PARAM_SUBLINK Params it is handy to carry the typmod of the sublink's output column. This is a much cleaner solution for the recently reported 'could not find pathkey item to sort' and 'failed to find unique expression in subplan tlist' bugs than my original 8.2-compatible patch. Besides, someday we might want to support typmods for external parameters ...
2006-12-08 14:50 tgl Remove the logId/logSeg fields from pg_control, because they are not needed in normal operation, and we can avoid rewriting pg_control at every log segment switch if we don't insist that these values be valid. Reducing the number of pg_control updates is a good idea for both performance and reliability. It does make pg_resetxlog's life a bit harder, but that seems a good tradeoff; and anyway the change to pg_resetxlog amounts to automating something people formerly needed to do by hand, namely look at the existing pg_xlog files to make sure the new WAL start point was past them.
In passing, change the wording of xlog.c's "database system was interrupted" messages: describe the pg_control timestamp as "last known up at" rather than implying it is the exact time of service interruption. With this change the timestamp will generally be the time of the last checkpoint, which could be many minutes before the failure; and we've already seen indications that people tend to misinterpret the old wording.
initdb forced due to change in pg_control layout. Simon Riggs and Tom Lane
2006-12-06 13:06 neilc Add a txn_start column to pg_stat_activity. This makes it easier to identify long-running transactions. Since we already need to record the transaction-start time (e.g. for now()), we don't need any additional system calls to report this information.
Catversion bumped, initdb required.
