thinc's Journal

Finding the postgresql tables that have been clustered

thinc — 2010-04-30T20:30:51+00:00

At $work we have a job that runs every night that calls clusterdb [1] on our database. clusterdb reruns cluster [2] against any tables that have previously been clustered.

If you want to know which tables have previously had cluster run on them, the following query does the trick:

SELECT pg_class.relname, pg_index.indisclustered FROM pg_class JOIN pg_index ON pg_class.oid = pg_index.indrelid AND pg_index.indisclustered = 't';

This will list all the tables that cluster will reorder.

[1] http://www.postgresql.org/docs/8.3/static/app-clusterdb.html/

[2] http://www.postgresql.org/docs/8.3/static/sql-cluster.html/

DBD::Sybase on solaris 10

thinc — 2006-08-17T00:04:45+00:00

Here we go again. This time I need DBD::Sybase on solaris 10 x86.

First thing you need is the sybase SDK for solaris 10 x86. I think you have to pay for that (the database group took care of that for me, so I have no idea.)

It gets installed to /opt/sybase

Then to compile DBD::Sybase against it, you do this:

1. export PATH=$PATH:/usr/sfw/bin/
2. export SYBASE=/opt/sybase/
3. export SYBASE_OCS=OCS-12_5
4. /usr/perl5/bin/perlgcc Makefile.PL
5. gmake
6. gmake test
7. sudo gmake install

1 - you need gmake in your path
2,3 - the Makefile.PL looks for the libs you are compiling against in $SYBASE/SYBASE_OCS/lib
4 - use the perlgcc for solaris magic
5,6,7 - the standard perl module installation, but with gnu make.

also in your apache configuration (if you're going to be using DBD::Sybase with apache, you need:
SetEnv SYBASE /opt/sybase/

perl modules vs. solaris 10

thinc — 2006-06-21T19:52:04+00:00

So I've got this solaris 10 workstation, and I want to install DateTime on it.

No problem, I think, just fire up cpan and "install DateTime".

Yeah right. This is solaris. Of course I don't have "Sun WorkShop" installed. So I install gcc, and gmake.

But wait, perl was compiled with Sun WorkShop, so the configure flags are different.

Then I find out about perlgcc. It works beautifully.

Here's the magic of how to compile perl modules on solaris 10 without having Sun developer tools installed:

# echo $PATH /usr/sbin:/usr/bin:/usr/local/bin/:/usr/sfw/bin/ # /usr/perl5/bin/perlgcc Makefile.PL ... # gmake ... # gmake test ... # gmake install

Note the /usr/sfw/bin/ on path to pick up the gcc and gmake.

Thank you Alan Burlison and perlgcc, you just made my job a lot easier.

DBD::Sybase on Debian sarge

thinc — 2006-04-12T20:49:30+00:00

So I needed to get DBD::Sybase installed today. Here's my notes:

download:
---------
$ wget http://download.sybase.com/pub/ase1192_linux/sybase-openclient-11.1.1-3.i386.rpm
$ wget http://download.sybase.com/pub/ase1192_linux/sybase-common-11.9.2-3.i386.rpm
$ wget http://search.cpan.org/CPAN/authors/id/M/ME/MEWP/DBD-Sybase-1.07.tar.gz

install:
--------
# apt-get install alien

$ alien --to-deb sybase-openclient-11.1.1-3.i386.rpm
$ alien --to-deb sybase-common-11.9.2-3.i386.rpm
# dpkg -i sybase-openclient_11.1.1-4_i386.deb
# dpkg -i sybase-common_11.9.2-4_i386.deb

make a standard location
------------------------
# ln -s /opt/sybase-11.9.2 /opt/sybase

fix the locales
see http://www.peppler.org/FAQ/linux.html#q1.14
-----------------------------------------------
emacs /opt/sybase/locales/locales.dat

find the string [linux] and add this directly below:

locale = en_US, us_english, iso_1

add to ~/.bashrc:
-----------------
export SYBASE=/opt/sybase/
export PATH=$PATH:$SYBASE/bin

$ source ~/.bashrc

install the perl module:
------------------------
$ tar xvfz DBD-Sybase-1.07.tar.gz
$ cd DBD-Sybase-1.07
$ perl Makefile.PL
$ make
$ sudo make install

postgresql functions

thinc — 2004-11-11T09:00:59+00:00

So a couple of years ago I started using postgresql, and really wanted a feature that I had used in MS-SQL, which is stored procedures that returned recordsets.

So I searched and searched trying to find out how to do it.

Turns out it was a new feature in 7.4 (or mabye 7.3) but nobody knew how to do it. So I kept searcing and finally found a newsgroup post by Tom Lane (I think) that halfway described it. This news post, combined with knowledge gained from my previous searches and investigations yeilded the solution I was after.

So, since I haven't seen it anywhere else (and it's not doing me any good not sharing it), here is the full text for a function (aka stored procedure) that returns a recordset.

DROP TYPE get_contacts_return CASCADE; CREATE TYPE get_contacts_return AS ( "first_name" varchar(50) , "middle_name" varchar(50) , "last_name" varchar(50) , "nickname" varchar(50) , "contact_id" bigint , "title" varchar(20) , "address_city" varchar(20) ); CREATE FUNCTION get_contacts( integer --contact_type_id ) RETURNS SETOF get_contacts_return AS ' DECLARE in_contact_type_id ALIAS FOR $1; rec get_contacts_return%rowtype; BEGIN --===================================================-- --error checking ------------------------------------------------------- IF( in_contact_type_id IS NULL ) THEN RAISE EXCEPTION ''Invalid contact_type_id: % '' , in_contact_type_id; END IF; --===================================================-- --query ------------------------------------------------------- FOR rec IN SELECT DISTINCT ON ( contact.first_name , contact.id ) contact.first_name AS first_name , contact.middle_name AS middle_name , contact.last_name AS last_name , contact.nickname AS nickname , contact.id AS contact_id , contact.title AS title , address.city AS address_city FROM contact JOIN address ON contact.id = address.fk_contact_id WHERE contact.display = true AND contact.contact_type_id = in_contact_type_id ORDER BY contact.first_name , contact.id LOOP RETURN NEXT rec; END LOOP; RETURN null; END; ' LANGUAGE 'plpgsql';

Note: The select statement is not complete, I have removed some of the where clause.

It boils down to this:
You create a new "recordset" return type, populate it with your select statement, then return it.

I have no idea if it is faster, uses less memory or if it is any better at all than calling straight sql from dbi, I just thought it was cool and wanted to try it. And They ave always told me that stored procedures are faster and "more efficient" than just sending queries to the database.

Oh, to call it, use this:

SELECT "first_name" , "middle_name" , "last_name" , "nickname" , "contact_id" , "title" , "address_city" FROM get_contacts( "contact_type_id" );

Makefile fun with Test::Inline

thinc — 2004-07-20T20:47:32+00:00

So I was playing around with Test::Inline, and wanted to integrate it with the little Makefile I use to ease my development.

Finally figured out this:

pod_tests: lib/*.pm $(foreach file,$?,pod2test $(file) t/$(notdir $(file)).t ;) test: pod_tests perl -I$(LIB) -MTest::Harness -e 'runtests(@ARGV)' t/*.t

This generates the inline tests and saves them as t/Module.pm.t everytime I run make test, which works great for what I need.

wOOt! dishwasher NOT broken

thinc — 2004-06-28T17:13:33+00:00

Upon further inspection, the diswasher has a "rinse and hold" button which was selected. Pressing "normal wash" resolved the problem. Doh! /me slaps forehead.

Next up: natural language product compatibility:

cake requires and is compatible with frosting
cake:flavor:carrot does not require and is not compatible with whipping cream
etc...

on to the next....

thinc — 2004-06-28T17:05:59+00:00

So I knocked the product dependencies into submission, and even gave the product page a nice dhtml tree view (Tree::Simple::View). Now I just have a few niceties to put in (expand all) and displaying the products with their parents in appropriate places ("Cake: Frosting: Chocolate" instead of just "Chocolate").

In other news, our dishwasher died...luckily I know of a used dishwasher place, and they should be able to repair it or sell us a new (used) one. We'll see.

Might as well start my own

thinc — 2004-06-28T04:07:40+00:00

I've started reading others journals and decided that since I'm enjoying it I might as well start my own. I've always been really bad at keeping these things up to date, so we'll see how it goes.

Well to start, I'm working on this application for a little hotel/resort so they can track their reservations. I got stuck today trying to figure out "product" dependencies. You see, they offer cakes, and you can specify the size of the cake, the type of cake (white, chocolate, carrot), the type of frosting and filling if you want it. So I had to figure out how to make it so that if they select "Whipping Cream" frosting, it only allows you to have "White" cake.

My original spec only allowed for a one to one (ex: cake requires frosting), but as I was coding it I realized that would not work. There are 4 types of frosting and 8 inch round cake cannot require all of them.

So I have to redo some of it so that products are in groups and you can have a generic "cake" require "frosting" which has sub items of the frosting types. Then you can also have single dependencies (Whipping Cream Frosting requires White cake).

So I can have my cake and eat it too...(ouch that was bad)