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

16-Feb-2003 Issue: 13

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.

Selecting the nth record
[GENERAL] agregates 22-Jan-2003

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;
This orders the set in reverse order (DESC), gets you the next to last one (OFFSET 1) and gives you only one record (LIMIT 1). For the specific case it doesn't get you the payment by customer, but it is a place to start. This one will get you the hook for the customer information.
	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);
This is not quite as fast because it does a subselect on each customer, but it is not as slow as the first try. And this is the data you want, after all.

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.

ItemPostgreSQLOracleComment
Stored ProceduresyesyesNot in MySQL
Licensing/Pricefreenot freeYou can spend the saving on hardware,
and talented :-) PostgreSQL consultants
but some people think expensive means better.
Replicationnot yetyesO requires teams of dbas
Engineer Hordeslotslotspgsql lists are free, timely and
PostgreSQL is open sourced
Table Tuning Minutiasomelotstime consuming and only sometimes worthwhile
Table Spacesnot yetyesSee General Bits Issue #10
Custom Plug-inscontribyesExtensibility vs. App specific
Oracle plug-ins were poor quality
Additional Appsmostly adminERP, appserver 
Installationunix centrictrickyexport LD_ASSUME_KERNEL=2.2.5??
Benchmarksby anyoneonly by Oraclelong discussion on relevance of
benchmarks and hacking of benchmarks
Speedit dependsit dependssee long discussion on relevance of benchmarks
Relational Integrityyesyes...but not MySQL...
Nested Transactionsnonocommit in O closes all open transactions
Save Pointsnoyesrollback 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
and rewrite it to perform the query that you defined when you created the view.
	create table foo ((f1 int, f2 text);
	create view foo_view as select * from foo;
The view foo_view is more or less translated into
 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;
The pre-parsed query tree is substituted when the query is invoked. I.e. "select * from foo;" is not literally substituted for the query, but its query tree is.

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' 
In postgresql.conf the line is:
	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 
Contributors: evon600c jcelle at deolis.com, Jean-Michel Chabanne jeanmichel.chabanne at free.fr, Bruce Momjian pgman at candle.pha.pa.us, Oliver Elphick olly at lfix.co.uk
Vacuuming
[GENERAL] Postgres Databases growing without much reason 14-Feb-2003

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


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