PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

23-Jun-2003 Issue: 31

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

Status of 7.4
7.4 Release discussion from pgsql-advocacy and pgsql-hackers 18-Jun-2003

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
These features are in progress but will not be completed in time for the 7.4 deadlines. For example, the native Windows support is now rumored to be targeted around October.

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). 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).

Matthew T. O'Connor
[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.
Tom Lane
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.

Robert Treat
I think the auto vacuum work will be pretty big, and I personally think statement level triggers are pretty important too.
Oleg Bartunov
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.
Jean-Michel POURE
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.
These are the items from the TODO list targeted for 7.4. The TODO list was last updated Mon Jun 2 15:25:37 EDT 2003
  • -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 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.
If you cannot or do not follow pgsql-hackers, be sure to read Robert Treat's PostgreSQL Weekly News on pgsql-announce for ongoing developer accomplishments.

Contributors: Bruce Momjian pgman at, Christopher Kings-Lynne chriskl at, Rod Taylor rbt at, Matthew T. O'Connor matthew at, Alvaro Herrera alvherre at, Tom Lane tgl at, Robert Treat xzilla at, Andrew Dunstan andrew at, Oleg Bartunov oleg at, Jean-Michel POURE jm.poure at, Josh Berkus josh at, Bruno Wolff III bruno at, greg at
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, Bruno Wolff III bruno at, Tom Lane tgl at
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, Tom Lane tgl at, Peter Eisentraut peter_e at, elein at
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.

		col_one		integer,
		col_two		integer,
		col_three	text,
		col_four		text,
		PRIMARY KEY (col_one, col_two) );
To reference this table in a foreign key, you must use all the fields, like this:
		col_a	integer,
		col_b	integer,
		col_c	text,
	FOREIGN KEY (col_a, col_b)
	REFERENCES foo (col_one, col_two));
The idea behind the foreign key is to identify one and only one row in table foo.

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, Gianni Mariani gianni at, Mark Wilson mwilson13 at
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
If you used either of the compression options, the dump is either a tar'd or compressed file. In that case you must use pg_restore to restore your dump file.

Contributors: Brian Avis brian.avis at Steve Lane slane at Tom Lane tgl at Jeff Fitzmyers jeff at Murthy Kambhampaty murthy.kambhampaty at btober at Doug McNaught doug at Peter Eisentraut peter_e at Richard Huxton dev at scott.marlowe scott.marlowe at

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW