Explore Posterous

Using "citext" in PostgreSQL for case insensitive comparisons

I think its new in PostgreSQL 8.4, there is a new field type called citext, which stands for case insensitive text.  Currently on Ubuntu 9.10 its in the PostgreSQL contrib package, which often serves as a testbed for features before they are adopted into PostgreSQL proper.

Using citext ensures that all comparisons are case insensitive, which includes referential integrity on inserts, updates and deletes, and SQL queries for lookups for example, which I think will work well for those fields that should always be compared case insensitive.

Although citext (and probably many other modules) are not listed on the contrib page, they are included, you can see them in the complete file list.

Steps to install on Ubuntu 9.10 (Karmic Koala):

# Install the contrib package
sudo apt-get install postgresql-contrib-8.4
# Add the citext module to a database of your choice
psql [username] -d [databaseName] -f /usr/share/postgresql/8.4/contrib/citext.sql

Where in this case /usr/share/postgresql/8.4 is the PostgreSQL SHARE_DIR folder.

Now you can use the field citext instead of text.  If you'd like to use citext on Ubuntu 9.04 Jaunty (which only includes PostgreSQL 8.3), then check out Mark's post about installing PostgreSQL 8.4 on Ubuntu 9.04.

JRuby + Postgresql + Sequel

Your life of Ruby + Java is going to be fun and painful. I hope this will get you started quickly so you can move on to the more annoying stuff. (JRuby is still amazing though. JVM is also quite lovely from what I've read.)

Required: Make sure you have a Java VM and Postgresql setup.

Download JRuby 1.3.1. (Archived. Already built.):
http://kenai.com/projects/jruby/pages/Home

Extract the contents.

Go to that folder:
cd jruby-1.3.1

Check everything is ok:
bin/jruby -v

Now do:
bin/jruby -S gem install sequel

Now go to: http://jdbc.postgresql.org/download.html
Download a driver (.jar file) where it says "Current Version."

Remember the location.

Now to test if it works:

# FILE NAME:  quick_start.rb
require 'rubygems'
require '/Path/To/Driver/postgres-..-..jdbc4.jar'  # Put the path/filename of the driver you downloaded. 
require 'sequel'
host = 'localhost' 
db_name = 'my-database'    # <=== Change this.
user = 'my-username'     # <=== Change this.
pass = 'my-password'    # <=== Change this.
DB = Sequel.connect( "jdbc:postgresql://%s/%s?user=%s&password=%s" % [ host, db_name, user, pass] )
my_table = :schema_info  # <=== Change this.
puts DB[ my_table  ].all.inspect  

Now do:
bin/jruby ~/Path/To/quick_start.rb

If it works, you can go and properly install and integrate JRuby into your system: http://kenai.com/projects/jruby/pages/Home
(Go to:  "JRuby Basics" > "Getting Started")
There is also: "JRuby Basics" > "Walkthroughs and Tutorials"

COMMON MISTAKES:

• The equlivalent of "gem install" is "jruby -S gem" (with a capital 'S')
• Write a proper Sequel connection string: jdbc:postgresql://HOST/DB?user=USER&password=PASS
  Notice how it differs from other connections strings in regular Ruby + Sequel.
  More info. on Sequel + JRuby + connection strings: http://sequel.rubyforge.org/rdoc/classes/Sequel/JDBC.html
• An alternative to downloading the .jar/jdbc driver for postgresql is to install the JDBC-Postgresql gem:
  bin/jruby -S gem install jdbc-postgres
  (It uses a slightly older JDBC driver. It's compatiable for Postgresql 8.2).

Eventually, I gave up JRuby for Ruby Enterprise Edition. http://www.rubyenterpriseedition.com/

Postgresql: duplicate schemas automatically

After this comment to a my question, im thinking if is better using 1 database with X schemas or viceversa. My situation: im developing a web-app where, when people do register, i create (actually) a database (no, its not a social network: everyone must have access to his own data and never see the data of the other user). Thats the way i used for the previus verison of my application (that is still running on mysql): throught the plesk api, for every registration i do: Create a database user with limited privileges; Create a database that can be accessed just by the previous created user and the superuser (for maintenance) Populate the db Now, i'll need to do the same with posrtgresql (the project is getting mature and mysql.. dont fulfill all the needes) I need to have all the databases/schemas backups indipendent: pg_dump works perfectly in both ways, the same for the users that can be configured to access just 1 schema or 1 database. So, assuming you are more experienced potsgres users than me, what do you think is the best solution for my situation, and why? There will be performance differences using $x db instead of $x schemas? And what solution will be better to mantein in future (reliability)? Every help and suggestion is really appreciated. Edit: i almost forgot: all of my databases/schemas will allways have the same structure! Edit2: For the backups issue (using pg_dump), is maybe better using 1 db and many schemas, dumping all the schemas at once: recovering will be quite simple loading the main dump in a dev machine and then dump and restore just the schema needed: there is 1 additional step, but dumping all the schema seem faster then dumpin them one by one. p.s: sorry if i forgot some 'W' char in the text, my keyboard suffer that button ;) postgres database-design database database-permissions edit|close|delete|flag edited 16 hours ago asked 21 hours ago DaNieL 578●9

"all of my databases/schemas will ever have the same structure!" do you mean they all have the same structure? Or never? – Osama ALASSIRY 17 hours ago Sorry, yes, they all have the same structure forever: if i change one, i'll change all of them ;) – DaNieL 16 hours ago

1	A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database. link answered 4 hours ago kquinn 3,115●1●11
	1   This. Postgres doesn't allow you to query across databases, which can be pretty annoying. – matt b 3 hours ago Great, i havent thought the possibility to query another schema without changing database or changing the connection. that's all, i'll go for 1 db and many schemas. Thanks guys! – DaNieL 25 mins ago  add comment

0

I would say, go with multiple databases AND multiple schemas :) Schemas in postgres are a lot like packages in Oracle, in case you are familiar with those. Databases are meant to differentiate between entire sets of data, while schemas are more like data entities. For instance, you could have one database for an entire application with the schemas "UserManagement", "LongTermStorage" and so on. "UserManagement" would then contain the "User" table, as well as all stored procedures, triggers, sequences etc. that are needed for the user management. Databases are entire programs, schemas are components. link|flag answered 16 hours ago Callash 31●3

... and so i'll have 1 database, with inside the schemas: $customer1_user_schema, $customer2_user_schema, $customer3_user_schema, $customer1_documents_schema, $customer2_documents_schema, $customer3_documents_schema? Mh... dont seem a reliable way... and what about performance? And what about the code of my application (will be php and python)? so many schemas.. – DaNieL 15 hours ago  add comment

0

A number of schemas should be more lightweight than a number of databases, although I cannot find a reference which confirms this. But if you really want to keep things very separate (instead of refactoring the web application so that a "costomer" column is added to your tables), you may still want to use separate databases: I assert that you can more easily make restores of a particular customer's database this way -- without disturbing the other customers. link|flag answered 9 hours ago Troels Arvin 983●3●9

add comment

0

Definitely, i'll go for the 1-db-many-schemas approach. This let me to dump all the database but restore just 1 very easly, in many ways: Dump the db (all the schema), load the dump in a new db, dump just the schema i need, and restore back in main db Dump the schema separately, one by one (but i think the machine ill suffer more this way.. and im aspecting like 500 schemas!) Otherwise, googlin around iv'se seen that there is no auto-procedure to duplicate a schema (using one as a template), but many suggest this way: Create a template-schema When need to duplicate, rename it with new name Dump it Rename it back Restore the dump The magis is done. I've written 2 rows in python to do that, hope thay can help someone (in-2-seconds-written-code, dont use it in production): import os import sys import pg #Take the ne shcema name from the second cmd arguments (the first is the filename) newSchema = sys.argv[1] #Temp folder for the dumps dumpFile = '/test/dumps/' + str(newSchema) + '.sql' #Settings db_name = 'db_name' db_user = 'db_user' db_pass = 'db_pass' schema_as_template = 'schema_name' #Connection pgConnect = pg.connect(dbname= db_name, host='localhost', user= db_user, passwd= db_pass) #Rename schema with the new name pgConnect.query("ALTER SCHEMA " + schema_as_template + " RENAME TO " + str(newSchema)) #Dump it command = 'export PGPASSWORD="' + db_pass + '" && pg_dump -U ' + db_user + ' -n ' + str(newSchema) + ' ' + db_name + ' > ' + dumpFile os.system(command) #Rename back with its default name pgConnect.query("ALTER SCHEMA " + str(newSchema) + " RENAME TO " + schema_as_template) #Restore the previus dump to create the new schema restore = 'export PGPASSWORD="' + db_pass + '" && psql -U ' + db_user + ' -d ' + db_name + ' < ' + dumpFile os.system(restore) #Want to delete the dump file? os.remove(dumpFile) #Close connection pgConnect.close()

Postgresql Explainer of Explain plan (ma LOL che figata)

http://explain.depesz.com/
Copy'n'paste your query plan, it will highligth all the 'bad steps'.

Dont know how does this works withour knowing the database structure, data-types and indexes, but.. its not bad

Postgresql Inherithance

CREATE TABLE main (...);

CREATE TABLE child () INHERITS (main);

CREATE INDEX $index_name ON child USING btree (field)

ALTER TABLE child ADD FOREIGN KEY ($field) REFERENCES other_table($field) ON DELETE SET NULL

Installing PostgreSQL on Debian

Description

This tutorial is about howto installing PostgreSQL on Debian Server.

Installing PostgreSQL

apt-get update
apt-get install pgsql

Create Language

Example plpgsql

su postgres
createlang plpgsql template1
exit

Change authentication method

We need to edit file pg_hba.conf to change authentification method for accessing PostgreSQL database.

cp /etc/postgresql/pg_hba.conf /etc/postgresql/pg_hba.confbak
vi  /etc/postgresql/pg_hba.conf

Find this section

# TYPE  DATABASE  USER  IP-ADDRESS  IP-MASK  METHOD
# Database administrative login by UNIX sockets
local  all  postgres        ident sameuser
#
# All other connections by UNIX sockets
local  all  all        ident sameuser

#
# All IPv4 connections from localhost
host  all  all  127.0.0.1  255.255.255.255  ident sameuser
#
# All IPv6 localhost connections
host  all  all  ::1  ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff  ident sameuser

host  all  all    ::ffff:127.0.0.1/128  ident sameuser
#
# reject all other connection attempts
host  all  all  0.0.0.0  0.0.0.0  reject

Edit that section to

# TYPE  DATABASE  USER  IP-ADDRESS  IP-MASK  METHOD
# Database administrative login by UNIX sockets
local  all  postgres        ident sameuser
#
# All other connections by UNIX sockets
local  all  all        password

#
# All IPv4 connections from localhost
host  all  all  127.0.0.1  255.255.255.255  password
#
# All IPv6 localhost connections
host  all  all  ::1  ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff  password
host  all  all    ::ffff:127.0.0.1/128  password

#
# reject all other connection attempts
host  all  all  0.0.0.0  0.0.0.0  reject

Restart PostgreSQL Server

/etc/init.d/postgresql restart

Create a New Database

Example wordpress

su postgres
createdb -T template1 wordpress
exit

Create a New User

Example: User supriyadisw with password cak3p

su postgres
createuser supriyadisw -P
Enter password for new user: cak3p [enter]
Enter it again: cak3p [enter]
Shall the new user be allowed to create databases? (y/n) y [enter]
Shall the new user be allowed to create more new users? (y/n) n [enter]

CREATE USER
exit

Login to PostgreSQL

pgsql -U supriyadisw wordpress [enter]
Password: cak3p [enter]

Good Luck

FROM: http://www.supriyadisw.net

What PostgreSQL type does CLSQL use for symbol?

I really like that clsql will create database tables for me using create-view-from-class. (For some reason I haven't gotten drop-view-from-class to work--it just returns "No Value" and leaves the table intact--but that doesn't really bother, since it's easy enough to drop tables.
 
I do, however, wish that there were an updated-view-from-class command that I could run when I added a slot to a class. Dropping the table myself, then creating it again certainly works, but then I lose my data, which is rarely what I'm going for. Usually, I don't care much, because I can just log into phpPgAdmin and update the table manually. In the past, it hasn't been too hard for me to figure out how to do so. I'm capable of converting hyphens to underscores, and it was always obvious which type to make the table, integer would be integer, float a double precision, string a character varying. I just had a symbol type slot in my object though, and I wasn't sure what that would be.
 
Annoying as it was, I deleted the table and used create-view-from class.

The answer is: clsql converts the symbol type to a "character varying", just as it does with strings. So now I know, and I won't have to go through that again.

Getting PostgreSQL to work with clsql on Ubuntu

Checking out an old version with darcs

Okay, so probably my terminology exposes how I am not nearly fluent in darcs, but am translating it to svn in my head.  Darcs has tags, patches, and changesets, but not versions, and it has get instead of checkout.   

So far, I've been doing Weblocks development on my Macbook, but I wanted to be able to show what we've been working on to other people, so I decided to set it up on the Slicehost slice I've been paying for and not using for months now.  Mostly, installing everything on Ubuntu was much easier than it had been on OS X, party because I'd done it before, but more because I didn't have to recompile SBCL.  I was basically able to apt-get install everything I needed: sbcl, postgresql, cvs, svn, git, mercurial, darcs, and possibly some things I'm forgetting.  I got clbuild from darcs and did ./clbuild install weblocks prevalence, just as I had done in the past.  I updated my clsql to the clsql-fluid branch, and installed bordeaux-threads.  I got an error saying I needed usocket, and I installed that.  Then, I got this error:

;     SB-INT:SIMPLE-READER-PACKAGE-ERROR at 437 (line 14, column 28) 
on #<SB-SYS:FD-STREAM for "file /home/smee/clbuild/source/weblocks/src/ 
debug-mode.lisp" {100437C231}>: 
;       package "HUNCHENTOOT-MP" not found 
; compilation aborted after 0:00:00 

WARNING: 
   COMPILE-FILE warned while performing #<COMPILE-OP NIL {1002CF3021}> 
on 
   #<CL-SOURCE-FILE "debug-mode" {10029E26C1}>. 

debugger invoked on a ASDF:COMPILE-FAILED in thread #<THREAD "initial 
thread" RUNNING {100266B2E1}>: 
  erred while invoking #<COMPILE-OP NIL {1002CF3021}> on 
  #<CL-SOURCE-FILE "debug-mode" {10029E26C1}> 

basically and didn't know what to do, so I asked the ever-helpful Weblocks google group, and was informed that the current stable version of Weblocks didn't yet work with the latest version of Hunchentoot, which had been just updated, but that it should work if I downgraded my Hunchentoot installation.  This is where, as usual, I ran into some problems.  

It took me a while to actually figure out how to get an old version from a darcs repository.  Here's an example of one of the less helpful bits of darcs documentation I encountered:

How do I go back to an older version of my repository?

Short answer: darcs get --to-match

Long answer: What exactly do you mean by go back to an older version?

http://wiki.darcs.net/DarcsWiki/FrequentlyAskedQuestions#head-e393e169c2c1ab8e991ff19fd0d09d29ce6c23f3

I went here and saw this:

To reproduce the state of a repository `R' as at tag `t', use the command `darcs get -tag t R'. The command `darcs show tags' lists all tags in the current repository.

but got confused when running "darcs show tags" revealed that there were no tags in this repository.  The same page also said:

If you want to get a specific version of a repository, you have a few options. You can either use the --tag, --to-patch or --to-match options, or you can use the --context=FILENAME option, which specifies a file containing a context generated with darcs changes --context.

which seemed like it was getting me closer, but which I still got wrong, because I was stuck on the idea of tags. This page was the one that finally made sense to me:

Here's what did end up working:

1. darcs changes, to see the patch names so as to use them later.  In this case, I saw that it had gone from "version 0.15.7" (the one I wanted) to "version 1.0.0".
2. darcs unrecord -p 1.0, to remove the most recent patch and go back to the version before that.
3. darcs revert, for the change made in 2. to actually take effect, with 'a' to revert all changes at once without saying 'y' for each one.

And that did it!  Weblocks is now working on my slice!  As usual with me, the way darcs works seems pretty clear from the documentation after the fact.

My Predictions for 2009

At the request of the Silicon Florist aka Rick Turoczy, here are my predictions for 2009. While he only asked for predictions about the local scene in the Portland area, I can't resist getting more general. After all, it is the end of the year and I am snowed in. :-)

1. PDX Technology
   
   
2. Sadly, I don't see any way to avoid job destruction in large organizations in the first quarter of the year. The lucky ones will be the ones who "only" need to institute hiring freezes. I do think the shrinkage will be smaller here than in other parts of the world, and I think the worst will be over by the end of the first quarter. General technology is too important a component of infrastructure for it to sink too low in my opinion.


3. Three technology areas where there will be intense focus, both in large and small organizations, are performance engineering / capacity planning / tuning, "green" computing and security. As you may know, the first of those three is my specialty, so I am probably biased in that respect. But organizations can no longer "just throw hardware" at scalability problems, nor do they need to tolerate poor-performing applications.


4. Some of the other posters at Silicon Florist were predicting a "march towards being the center of mobile web development", etc. While I'm not very familiar with what's going on in that arena, I think that's an over-optimistic projection for a variety of reasons.

   First of all, while gadgets and gizmos are cool, the emphasis of the incoming Congress and White House is going to be restoring the health of the Finance, Insurance and Real Estate (FIRE), transportation / automotive and other more fundamental sectors. And after that, they're going to tackle health care, science and education. While we in Portland are blessed with Tri-Met, a bicycle-friendly culture and a climate that's usually conducive to mobility, there are places that are hurting very badly. So think food, clothing, shelter, and transportation before you think "Pac-Manhattan".

   Second, there are too many competing "mobile platforms" -- Blackberry, Android, iPhone, other "smart phones", etc. They can't all thrive, and some probably won't even survive. And finally, I think that the "smarter" mobile platforms get, the more security / privacy disasters there are just waiting to happen. Still, I'm hoping someone more familiar with this technology will step in here and tell me why I should be more optimistic. :-)



5. What I think will continue to survive and maybe even thrive here is open source. Remember, though, the mantra is "Free as in freedom, not as in price." Portland is blessed with some major names in Linux, PostgreSQL, Ruby / Rails, Perl, Smalltalk / Seaside, and others. And a number of large companies here -- big names like IBM, HP, Novell, and Intel -- pay people to work on open source projects because it is in their best financial interests to do so.


6. PDX in general
   
   
7. As I noted above, we are blessed with Tri-Met and a bicycle-friendly culture, so we managed to shrug off the high gasoline prices this summer. But as the current "Snowpocalypse" has showed, we are no less vulnerable to Mother Nature than other places. There is going to be a substantial hit to local businesses from this event. Other than the standard advice of keeping emergency supplies on hand, there's not much anyone can do about it. And let me remind everyone that we live in a seismically active area, and there is more construction on flood plains than I think is wise. Let's just say I think we will have our readiness tested again in 2009.


8. I don't have children or grandchildren in school, so I'm not an expert on the situation in the educational system here. But when there's an economic downturn, the schools suffer. I think that's wrong -- very wrong. And I hope I'm wrong here, but I am predicting a decrease in the quality of public education in Portland for 2009. Again, I'm hoping someone with more knowledge will chime in and give me reasons why I should be more optimistic.


9. On a positive note, I have been and continue to be impressed -- even wowed -- by the leadership skills of the people I have met in the technology and blogging communities in 2008. I think we will see a number of successful events here in 2009 as a result of this, and I think we will see some of these leaders enter public service. I've got my number two pencil all sharpened and ready. :-)


10. Technology in general
    
    
11. I think there's going to be a major shakeout in technology. As I noted above, I think there are too many mobile platforms and too many other risk areas in mobile computing. Other "hot" areas that are vulnerable are virtualization, "cloud computing", consumer electronics in general and "social media", whatever that is. Again, think food, clothing, shelter and transportation, not cool gizmos.


12. I think everyone expects Yahoo to find a buyer. But what if they don't?


13. I think there are going to be major changes at the highest executive levels at Microsoft and Apple. And this isn't just based on rumors about Steve Jobs' health or the personality of Steve Ballmer.

    For Microsoft, it's based on the disaster that is / was Windows Vista, failure of Microsoft to acquire Yahoo, and the apparent inability of their engineers to provide a solution for the botnet problem. For Apple, again, it's the plethora of mobile platforms, portable media players, netbooks and laptops. They can defend their market shares in telephony and portable media players only by cutting costs, and I don't see how they can possibly defend against the onslaught of Windows 7 and low-cost open source devices as capable as a Mac. I may be biased because I have never owned an Apple product, but there are reasons for that. :-)



14. I don't think Google can sustain their current trajectory. I haven't looked at their financials recently, but they have ventured into a number of areas where they don't have a compelling advantage. There's a saying in the markets: "No tree grows to the sky." I'd look for them to cut loose some things that aren't panning out, and I wouldn't count on advertising subsidizing all of their other services too much longer. Microsoft may well have the last laugh in a "Google - Microsoft war". :-)


15. Everything else
    
    
16. The incoming Congress and White House have clearly communicated their intent to improve the lot of the middle class. I'm not sure how that's defined, but I'm guessing that if you are reading this, you are not middle class. :-) So I wouldn't look for any help from "the government" until the "middle class" has the resources to take advantage of your goods and services.


17. I think the "recession" is going to be shorter in the USA than the general consensus of predictions in the USA. But I think it's going to be longer and deeper in Europe and Asia. I think the FIRE sector is healing nicely, there should be a boost when the television industry cuts over to the newer technologies in the spring, and I don't think there is either a major upside or downside risk to gasoline prices. But I expect the transportation sector to remain chaotic because of the terrible balance sheets of the automobile manufacturers and airlines.


18. To end on a positive note, I think there are going to be some major advances in world politics, human rights, the environment and the global war on disease and poverty in 2009. For example, I think we'll see another US-brokered deal between Israel and the Palestinians. I think we'll see the fall of several oppressive regimes, and I think we could even see Korea re-unified in 2009.