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