At this date, the code and feature freeze is looming for 7.4. This is the good news. The bad news is that the most wanted and most risky features will probably not make it into this release. That list includes:

• Native Windows support
• Replication
• Point in Time recovery
• Nested Transactions

Holding a release for features has resulted in some very unhappy people and some very late releases. It is determined that we should release the features that are now available. Many people are waiting for the completed features, too.

On the discussion about 7.4 on hackers, there are good comments about the new features for 7.4.

Rod Taylor

A quick glance at the TODO list shows a number of speed improvements in specific areas (IN, GROUP BY, Subselects in views), ARRAY improvements, some utility command improvements / additions, and a significant protocol update.

The protocol update may not be flashy, but it is a large step forward in presenting a clean experience for developers using PostgreSQL (reduces chance of rare, unexpected, and difficult to find logic errors).

...it makes for an excellent cleanup release that rounds off some of the sharp corners (tab completion for schema elements in psql, schema dump in psql, fixed cluster support, transactional truncate, alter sequence, new regex code for fast MultiByte, etc).

[T]he index growth problem [is] solved in this release. I think that is a killer feature that solves a big problem for a lot of people.

We have a lot of pretty good stuff. You're not happy that the performance of IN (subselect) has been fixed? That btree index bloat is fixed (at least in large part, it remains to be seen whether the field performance is all that we need...)?

In my opinion the project is not at a state where whizzy new Features with a capital F are going to jump out of the woodwork. We are making good advances in performance, reliability, SQL spec compliance, and stuff like that, but fancy-sounding bullet points are hard to come by.

I can tell you that Red Hat's CCM group (the former Ars Digita) is waiting ... for 7.4, because it fixes a number of problems (IN-subselect being one) that prevent 7.3 from being a serious competitor to Oracle for their platform.

I think the auto vacuum work will be pretty big, and I personally think statement level triggers are pretty important too.

I'm not sure if contrib/tsearch is a "killer" feature, but we hope to submit completely new version of tsearch V2 before July 1.

We should not forget the availability of PostgreSQL companion products, like pgAdmin3 and phpPgAdmin3. These two GUIs [are rumored to] be ready for release during July.

• -Add start time to pg_stat_activity
• -Change NUMERIC data type to use base 10,000 internally
• -Add GUC variables to control floating number output digits (Pedro Ferreira)
• -Make a transaction-safe TRUNCATE (Rod)
• -Add ALTER TABLE tab SET WITHOUT OIDS (Rod)
• -Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values
• -Allow CLUSTER to cluster all tables (Alvaro Herrera)
• -MOVE 0 should not move to end of cursor (Bruce)
• -Allow cursors outside transactions
• -Make PL/PgSQL %TYPE schema-aware
• -Add schema, cast, and conversion backslash commands to psql (Christopher)
• -Allow pg_dump to dump a specific schema (Neil Conway)
• -Support statement-level triggers (Neil)
• -Add checkpoint_min_warning postgresql.conf option to warn about checkpoints that are too frequent (Bruce)
• -Add hash for evaluating GROUP BY aggregates (Tom)
• -Make IN/NOT IN have similar performance to EXISTS/NOT EXISTS (Tom)
• -Inline simple SQL functions to avoid overhead (Tom)
• -Get faster regex() code from Henry Spencer
• -Modify regression tests to prevent failures do to minor numeric rounding
• -Add OpenBSD's getpeereid() call for local socket authentication
• and lots of bug fixes.

Contributors: Bruce Momjian pgman at candle.pha.pa.us, Christopher Kings-Lynne chriskl at familyhealth.com.au, Rod Taylor rbt at rbt.ca, Matthew T. O'Connor matthew at zeut.net, Alvaro Herrera alvherre at dcc.uchile.cl, Tom Lane tgl at sss.pgh.pa.us, Robert Treat xzilla at users.sourceforge.net, Andrew Dunstan andrew at dunslane.net, Oleg Bartunov oleg at sai.msu.su, Jean-Michel POURE jm.poure at freesurf.fr, Josh Berkus josh at agliodbs.com, Bruno Wolff III bruno at wolff.to, greg at turnstep.com

Forcing Join Order (or not) [GENERAL] explicit joins vs implicit joins 19-Jun-2003

By using the JOIN command in a query, you have control over the JOIN ordering. This is a good thing if you have information that the planner does not--and often you do when there are a lot of tables in the query.

When you join tables using the WHERE clause, the planner does the join ordering with all of the information it has. The planner is exponentially slower for each additional table in the join. However, the planner is really good at choosing the best plan.

In 7.4, JOINs will also hand over responsibility for join order to the planner and at the same time expand control for WHERE type joins. The variables from_collapse_limit and join_collapse_limit will limit the planner's consideration of multiple tables to, say 1, when you are using known and tested JOIN statements or just using multiple tables in your FROM clause.

Contributors: culley harrelson culley at fastmail.fm, Bruno Wolff III bruno at wolff.to, Tom Lane tgl at sss.pgh.pa.us

Debugging C Functions [GENERAL] debugging C functions 20-Jun-2003

Debugging a C function can be frustrating if you don't know the sequence of events and the tools available.

To attach gdb to the server to debug your function, first you must be sure the shared library which contains your function is loaded into the server. The shared object is not loaded until it is needed so you need to force the load for the first invocation, especially if any invocation crashes the server.

Once the shared library is loaded then you can attach to the server process with gdb Tom Lane's example is:

	start fresh session
	psql=> LOAD 'libraryname';
	attach to backend with gdb
	gdb> b myfunc
	gdb> cont
	psql=> SELECT myfunc();

If this does not work, you may have to tell gdb about the shared libraries. Issue a sharedlibrary command to gdb to force it to absorb symbol definitions from the shlib.

The old fashioned alternative is to use elog(DEBUG...) statements (or printfs) to print out the status of the function as it is running. Printfs just send to standard out, i.e. your log file. (You are not sending your log file to /dev/null, right?) Using elog( WARNING, ...); can also be very enlightening before it crashes on you.

And of course, no tool or process is better than a code review or at least a second set of knowledgeable eyes.

Contributors: Nigel J. Andrews nandrews at investsystems.co.uk, Tom Lane tgl at sss.pgh.pa.us, Peter Eisentraut peter_e at gmx.net, elein at varlena.com

Multi-part Primary Keys and Foreign Key References [GENERAL] Foreign Key can't refer to one of 2 primary keys 21-Jun-2003

In a table where the primary key is a composite key, you must always refer to the entire composite key for foreign key references. A table here is defined with the first two columns as the primary key.

	CREATE TABLE foo (
		col_one		integer,
		col_two		integer,
		col_three	text,
		col_four		text,
		PRIMARY KEY (col_one, col_two) );

	CREATE TABLE bar (
		col_a	integer,
		col_b	integer,
		col_c	text,
	FOREIGN KEY (col_a, col_b)
	REFERENCES foo (col_one, col_two));

If by chance, the foo table were unique on col_one, you would only needed to define it as the primary key. And if you wanted an index across the three columns, you could create it separately.

A primary key is one that is designated as a primary key and has the following constraints, it is unique and not null. You can have several candidate keys, a single or a group of columns, which meet this criteria. However, only one candidate key can be designated as the primary key.

You can create a foreign key reference for any candidate key, whether is is the defined primary key or not. However, if your candidate is part of a composite key, you still must use all elements of the composite key in your foreign key reference.

Contributors: Reuben D. Budiardja techlist at voyager.phys.utk.edu, Gianni Mariani gianni at mariani.ws, Mark Wilson mwilson13 at cox.net

Dumping, Zipping and Restoring [GENERAL] Backups and restores. 6-Jun-2003

If you have a dump created by pg_dump with the following command, you must be sure to unzip it before restoring the resultant file: pgdump_2003-6-5-csp.gz. If you zip it, you have to unzip it.

/usr/local/pgsql/bin/pg_dump $db | gzip > /usr/local/pgsql/backups/$filename

There are two methods to use to restore dump files produced by pg_dump depending on whether or not it was called with the compression options -Fc or -Ft.

If you use pg_dump without the compression options the dump is plain text SQL. Restoration is accomplished by using psql

psql -f pgdump_2003-6-5-csp

Contributors: Brian Avis brian.avis at searhc.org Steve Lane slane at moyergroup.com Tom Lane tgl at sss.pgh.pa.us Jeff Fitzmyers jeff at cloverpub.com Murthy Kambhampaty murthy.kambhampaty at goeci.com btober at seaworthysys.com Doug McNaught doug at mcnaught.org Peter Eisentraut peter_e at gmx.net Richard Huxton dev at archonet.com scott.marlowe scott.marlowe at ihs.com