There is a very simple way to select the nth record from a selection. But it is not obvious. The case in question was trying to select the next to the last payment per person. This was tried first; it works, but is very slow.

	SELECT max(paymentid) as paymentid
	FROM tablepayment 
	WHERE paymentdebit > 0
		AND paymentid NOT IN (SELECT max(paymentid) as paymentid
			FROM tablepayment 
			WHERE paymentdebit > 0
			GROUP BY customerid)

In general, the query of the following form will get you the next to the last record from a select:

	SELECT paymentid
	from tablepayment
	WHERE paymentdebit > 0
	ORDER BY paymentid DESC LIMIT 1 OFFSET 1;

	SELECT customersid, paymentid 
	FROM tblpayment p1
	WHERE paymentdebit > 0 and paymentid = ( 
			SELECT paymentid 
			FROM tblpayment p2 
			WHERE p2.paymentdebit > 0 and p2.customerid = p1.customerid 
			ORDER BY paymentid DESC LIMIT 1 OFFSET 1);

Variations on the basic selection with ORDER BY, LIMIT 1 and OFFSET can get you the nth record from either direction.
Select the third record with ORDER BY id LIMIT 1 OFFSET 2.
Select the fifth record with ORDER BY id LIMIT 1 OFFSET 4.
Select the fourth from the last record with ORDER BY id DESC LIMIT 1 OFFSET 3.
Contributors: David Blood david at matraex.com, Andrew Sullivan andrew at libertyrms.info, Eric B.Ridge ebr at tcdi.com, Jean-Luc Lachance jllachan at nsd.ca, codeWarrior GPatnude at adelphia.net

PostgreSQL client error after upgrade to 7.3.2 [GENERAL] Not finding local variables and libs 11-Feb-2002

After upgrading from source to PostgreSQL 7.2.1, the following errors occurred on the cron job to do vacuuming.

	Your "cron" job on bugs
	/usr/local/pgsql/bin/vacuumdb -z -v lismarch

	produced the following output:

	ld.so.1: /usr/local/pgsql/bin/psql: fatal: 
	libgcc_s.so.1: open failed: No such file or directory
	Killed
	vacuumdb: vacuum  lismarch failed 

This is caused by not loading the correct libgcc. It is looking for libgcc_s.so.1. In this case you should check the value of LD_LIBRARY_PATH to be sure it includes the directory where that library resides. You can find the location of the local libgcc by issuing the shell command:

	>  gcc -print-libgcc-file-name 

On the system in question, it was sufficient to link the libgcc over to /usr/lib. Alternatively you can ensure that the loader, ld.so, knows the proper place to look. To do this, you must edit /etc/ld.so.conf and run ldconfig using the -rpath argument of the linker to set to the location of libgcc_s.so. And then recompile with LD_RUN_PATH set to the location of libgcc_s.so. The man pages for ldd and ld.so will be helpful.

Contributors: Martin Marques martin at bugs.unl.edu.ar, Stephan Szabo sszabo at megazone23.bigpanda.com, ahoward ahoward at fsl.noaa.gov

Comparisons [GENERAL] PostgreSQL x Oracle 09-Feb-2003

I tried really hard to ignore this item, but it went on and on and on. When I needed to justify using PostgreSQL over Oracle to management, simply comparing the cost did the trick. I have functional reasons for preferring PostgreSQL over Oracle; they primarily have to do with the ability to extend the server easily. But those are harder to explain to management than, hmmmm, both are mature, well supported and stable but one is $120,000 and one is free.

DISCLAIMER: I do not vouch for the accuracy of any of the statements made about Oracle reported in this item. Most of these statements were made by contributors and are opinions. You'll have to look up the thread to see who said what.

Item	PostgreSQL	Oracle	Comment
Stored Procedures	yes	yes	Not in MySQL
Licensing/Price	free	not free	You can spend the saving on hardware, and talented :-) PostgreSQL consultants but some people think expensive means better.
Replication	not yet	yes	O requires teams of dbas
Engineer Hordes	lots	lots	pgsql lists are free, timely and PostgreSQL is open sourced
Table Tuning Minutia	some	lots	time consuming and only sometimes worthwhile
Table Spaces	not yet	yes	See General Bits Issue #10
Custom Plug-ins	contrib	yes	Extensibility vs. App specific Oracle plug-ins were poor quality
Additional Apps	mostly admin	ERP, appserver
Installation	unix centric	tricky	export LD_ASSUME_KERNEL=2.2.5??
Benchmarks	by anyone	only by Oracle	long discussion on relevance of benchmarks and hacking of benchmarks
Speed	it depends	it depends	see long discussion on relevance of benchmarks
Relational Integrity	yes	yes	...but not MySQL...
Nested Transactions	no	no	commit in O closes all open transactions
Save Points	no	yes	rollback to savepoints in transactions

The latter part of the thread scattered into a JSP vs. PHP with a little Python advocacy thrown in. The conclusion of that part seemed to be, design and write it well and it will go fast with web apps.

Contributors: Marcelo Pereira gandalf at sum.desktop.com.br, Simon Mitchell pgsql at jseb.com, Christopher Browne cbbrowne at acm.org, Sean Chittenden sean at chittenden.org, Mark Kirkwood markir at paradise.net.nz, Karel Zak zakkr at zf.jcu.cz, Shridhar Daithankar shridhar_daithankar at persistent.co.in, ahoward ahoward at fsl.noaa.gov, Andrew Sullivan andrew at libertyrms.info, Nigel J. Andrews nandrews at investsystems.co.uk, common common_mailbox at 21cn.com, Fabrizio Ermini hermooz at tin.it, snpe snpe at snpe.co.yu, scott.marlowe scott.marlowe at ihs.com, Tony Grant tony at tgds.net, Joel Rodrigues borgempath at Phreaker.net, Ben-Nes Michael miki at canaan.co.il, Lincoln Yeoh lyeoh at pop.jaring.my, karthikeyan.balasubramanian karthikeyan.balasubramanian at aspiresys.com, Terry Yapt pgsql at technovell.com

What is behind views? [GENERAL] Parsing of VIEW definitions 7-Feb-2003

A view in PostgreSQL is implemented using RULES. A RULE can come in several flavors, ON SELECT, UPDATE, INSERT or DELETE. A view is an "ON SELECT RULE." A RULE enables the developer to intervene in queries and change their behavior.

For a view, the RULE would take

select * from myview

	create table foo ((f1 int, f2 text);
	create view foo_view as select * from foo;

 select * from (select * from foo) as foo_view; 

But what happens behind the rewrite and to ensure speed should you avoid views?

What happens is this. The query tree of the rewrite rule is stored in pg_rewrite. You can see the ugly, flat text representation of it (in 7.3) by issuing:

select ev_action from pg_rewrite where ev_class = 'foo_view'::regclass;

In the matter of speed, the rewrite overhead more or less balances out the cost saved by going directly to the query tree. Tom Lane says, "My guess is it'd be about a wash --- but I've never tried to measure."

To see query trees in a nicer format, set DEBUG_PRINT_REWRITTEN and DEBUG_PRETTY_PRINT to on and issue the query you want. The pretty printed query trees are recorded in your log file. To decipher the structures in the query tree, you can also look in src/include/nodes/*.h, primarily primnodes.h and parsenodes.h.

Contributors: Eric B.Ridge ebr at tcdi.com, Tom Lane tgl sss.pgh.pa.us

Setting Date Style [GENERAL] French date 31-Dec-2002

To set your date style to recognize the date format dd/mm/yy in 7.3, you can set an SQL variable in the session or you can set a configuration variable in postgresql.conf for the installation. In SQL the command is:

	SET DATESTYLE TO 'ISO,EUROPEAN' 

	DATESTYLE = 'ISO,European'

In earlier releases you could set the SQL variable as above or set a shell environment variable before you start up your postmaster.

	export PGDATESTYLE=ISO,European 

A small database is growing. The nightly VACUUM ANALYZE is doing the right thing. But the database is still growing. The database was dumped and restored and that seemed to help, but then it began growing again. The solution seemed to be a VACUUM FULL but the database may continue to grow again.

It is important to notice when plain VACUUM does not reclaim space and yet VACUUM FULL does reclaim space; this may indicate that your free space map settings are too low.

In general regular VACUUM ANALYZE is sufficient. An occasional VACUUM FULL can help consolidate the table space, particularly when a large deletion occurs. VACUUM FULL will shrink the on-disk size of the tables, but not the indexes. There is work going on to fix this for the next version of PostgreSQL. You may want to occasionally drop and recreate the indexes as a work around to the index bloat issue.

See also General Bits Issue #10 for more information about VACUUM and the free space mapping options.

Contributors: Andreas Rust rust at webnova.de, Tony Grant tony at tgds.net, Martijn van Oosterhout kleptog at svana.org, Jeff Eckermann jeff_eckermann at yahoo.com, Stephan Szabo sszabo at megazone23.bigpanda.com