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

02-Dec-2002 Issue: 2

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 www.PostgreSQL.org.

Congratulations to the PostgreSQL Global Development Group
Announcing Version 7.3! 28-Nov-2002

Source for this release is available at:
    
    http://advocacy.postgresql.org/download/

More information on PostgreSQL is available in nine languages on the PostgreSQL
Advocacy website:
    
    http://advocacy.postgresql.org

A complete list of changes in PostgreSQL version 7.3 can be found in the HISTORY
file included with the release, or available on the web at:
    
    http://advocacy.postgresql.org/changes/73/
Contributors: PostgreSQL Global Development Group
Using the to_char() formatting function
[GENERAL] Formatting psql output 06-Nov-2002

To format the of digits represented in a result set of numbers, use to_char(). If the size of your number is greater than what will fit into the mask, the digits are represented as '#' to show overflow. For example:

	# select to_char( 12345.67, '999G999D99S');
   	  to_char
	-------------
  	12,345.67+
	(1 row)

	# select to_char( 12345.67, '99D99S');
	  to_char
	---------
	 ##.##+
	(1 row)

Contributors: markMLl.pgsql-general at telemetry.co.uk, Bruce Momjian pgman at candle.pha.pa.us, Tom Lane tgl at sss.pgh.pa.us, Bruno Wolff III bruno at wolff.to
Logging
[GENERAL] command 08-Nov-2002

Sending the output of the postmaster to stdout will not give you a log file. You must not only capture stdout but stderr.

	WRONG: postmaster -i -D .... -d 10 > logfile 
	RIGHT: postmaster -i -D .... -d 10 > logfile 2> &1 

Using the pg_ctl with the -l option is easier. Many people use a shell wrapper on pg_ctl with the command line options to start the server consistently, for example:

	#!/bin/bash
	pg_ctl start -D $PGDATA -l $PGDATA/pglog -o "-i"

You can also set the appropriate debug levels and features in postgres.conf. These are documented in the 7.2 PostgreSQL manual under Section 3 Server Runtime Environment Section 4 Run-time configuration.

The LOG_TIMESTAMP option in postgres.conf will put a timestamp on the server log messages.

You can also use your system's logging features. In this example for Linux make changes to postgres.conf and /etc/syslog.conf.

	postgres.conf:
	syslog          = 1 # range 0-2
	syslog_facility = 'LOCAL0'
	syslog_ident    = 'postgres'

	/etc/syslog.conf:
	local0.*        /var/log/postgresql
Then do a restart of both syslog and postgres:
	/etc/init.d/syslog restart
	/etc/init.d/postgresql restart
Contributors: Florian Litot flitot at besancon.sema.slb.com, Shridhar Daithankar shridhar_daithankar at persistent.co.in, Lee Kindness lkindness at csl.co.uk, Neil Conway neilc at samurai.com, Tom Lane tgl at sss.pgh.pa.us, Medi Montaseri medi.montaseri at intransa.com
Copy errors indicate improper (dos) file format
[GENERAL] Error on import 24-Oct-2002

copy gives import errors on files with dos carriage returns (\r\n) instead of unix carriage returns (\n). You can fix this using vi or vim by editing the file and typing:

	:set fileformat=unix
and write out the file. But there are also (too? :-) many ways to fix this in Perl. Here are three suggestions:

	cat origfile|perl -e 'while (<>){$_ =~ s/\r//g;print $_;}' > prepfile
	perl -i -pe 's/\r\n/\n/' filename
	perl -p -e 's/\r//g' < origfile > prepfile

Contributors: John johnp at totcon.com, Tom Lane tgl at sss.pgh.pa.us, Medi Montaseri medi.montaseri at intransa.com, Richard Huxton dev at archonet.com, elein at varlena.com
Retrieve the latest record by date or timestamp
[GENERAL] newbie question for return date 28-Oct-2002

To retrieve the latest record in a table with a date or timestamp, the concensus on the best way is

	SELECT * FROM t ORDER BY d DESC LIMIT 1;
where there is an index on d.

Contributors: tviardot tviardot at sympatico.ca, Lee Harr missive at frontiernet.net, Medi Montaseri medi.montaseri at intransa.com, Tom Lane tgl at sss.pgh.pa.us, terry at greatgulfhomes.com, Richard Huxton dev at archonet.com
pgtcl: Data Manipulation counts
[GENERAL] number of affected rows in pgtcl 24-Oct-2002

In pgtcl when using pg_exec(), pg_result does not provide the number of rows updated, deleted or inserted. You should use pg_execute() instead of pg_exec(). pg_execute() has just been documented in PostgreSQL 7.3, however, it has been available since 7.1.

Contributors: Christian Traber christian at traber-net.de, Tom Lane tgl at sss.pgh.pa.us
[GENERAL] Unwanted Log Entries
[GENERAL] Unwanted Log Entries 22-Nov-2002

The log entries

 NOTICE:  TRANSACTION ISOLATION LEVEL is READ COMMITTED
are caused by the client issuing the statement:
 SHOW TRANSACTION ISOLATION LEVEL

You can remove that statement from your client to remove those messages. If you cannot edit your client, you must move to 7.3. In 7.3 SHOW commands will no longer generate log entries.

Contributors: Jon Swinth jswinth at atomicpc.com, Tom Lane tgl at sss.pgh.pa.us


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

Top
Google
Search General Bits & varlena.com Search WWW