Saturday, March 29. 2008
Printer Friendly
Problem:
You've created a database but made an embarrassing typo in the name or for whatever reason you don't like it. How do you rename this database?
Solution:
If you are using PgAdmin III, you will not see this option. Just one of the ways PgAdmin III lets us down. However there is a simple way of doing it with a PostgreSQL command which has been in existence even in the 7.4 days of PostgreSQL which is documented in PostgreSQL official docs on ALTER DATABASE. In order to do it, you need to first make sure everyone is out of the database (including yourself) otherwise you'll get an annoying database is being accessed by other users or current database may not be renamed error.
- Connect to some other database other than the one you are trying to rename such as say the postgres db.
- Kick everyone out of the database you are trying to rename - to figure out users, you can run
SELECT *
FROM pg_stat_activity
WHERE datname = 'myolddbname_goes_here'
- Now just run this command -
ALTER DATABASE myolddbname_here RENAME TO mynewdbname_here
Friday, March 28. 2008
Printer Friendly
What is Constraint Exclusion?
Constraint Exclusion is a feature introduced in PostgreSQL 8.1 which is used in conjunction with Table Inheritance to implement
table partitioning strategies. The basic idea is you put check constraints on tables to limit what kind of data can be inserted into it.
Constraint Exclusion will then in theory skip over tables whose check constraints guarantee there is no way for it to satisfy the
condition of a query.
Constraint Exclusion is a great thing, but it has some limitations and quirks one needs to be aware of that in some cases
will prevent it from kicking in. Below are a couple of reasons why it sometimes doesn't work.
Continue reading "Constraint Exclusion when it fails to work"
Monday, March 24. 2008
Printer Friendly
What is PuTTY
PuTTY was developed by Simon Tatham and is a very common light-weight MIT-Licensed
free and open source Secure Shell (SSH) client for connecting to Linux/Unix systems via a Teletype (TTY) terminal emulation mode console.
Currently there are ports for Microsoft Windows, other unix like systems,
and ports in progress for Mac OSX and Symbian mobile phone OS.
PuTTY fits into that class of tools we affectionately call Swiss Army Knives because it is
Light, Multi-Purpose, and Good Enough. As an added benefit it is free and open source with a generous license so it is commonly embedded in
commercial apps.
PuTTY comes in handy both as an SSH terminal console and as a SSH Tunneling tool which allows you for example
to use PgAdmin III from a local windows workstation against a remote PostgreSQL server even in cases where the linux/unix PostgreSQL pg_hba.conf and postgresql.conf file only allow local connections or non-SSH traffic is blocked by
firewall.
For more about the nuances
of configuring the pg_hba.conf PostgreSQL server file that controls user access check out Hubert Lubaczewski's “FATAL: Ident authentication failed”, or how cool ideas get bad usage schemas
http://www.depesz.com/index.php/2007/10/04/ident/
In this article we shall cover how to use PuTTY's SSH Tunneling feature to access a remote PostgreSQL server that doesn't allow
remote connections. To make it a little more interesting we shall demonstrate how to do this for PgAdmin III.
Continue reading "PuTTY for SSH Tunneling to PostgreSQL Server"
Tuesday, March 18. 2008
Printer Friendly
Setting up the .NET application
-
Download the npgsql 1.01 driver from pgfoundary -
http://pgfoundry.org/projects/npgsql.
For ASP.NET 2.0 you'll want - Npgsql1.0.1-bin-ms2.0.zip and for Mono.NET you'll want Npgsql1.0.1-bin-mono-2.0.zip. Unzip and place the files in bin folder of your web app project.
- Since we are just creating a simple REST web service and don't need any plumming of the standard SOAP like webservice, we will be using
a .NET handler class (ashx) instead of an asmx. We have two versions listed below. One for C# and one for VB.NET/Mono Basic
Continue reading "REST in PostgreSQL Part 2 A - The REST Server service with ASP.NET"
Sunday, March 16. 2008
Printer Friendly
In this section we'll go over creating the views and stored functions that our REST Server service will rely on.
Our REST service will be a thin wrapper around a pgsql function that accepts film queries and returns results in XML.
Loading the database
Step 1: Download the Pagila 0.10.0 database from
http://pgfoundry.org/frs/?group_id=1000150&release_id=570
and load the Pagila database using the following commands:
Note:For windows users - psql is usually located at "C:\Program Files\PostgreSQL\8.3\bin\psql"
psql -h localhost -p 5433 -U postgres -c "CREATE DATABASE pagila ENCODING 'UTF8'"
psql -h localhost -p 5433 -U postgres -c "CREATE ROLE pagila_app LOGIN PASSWORD 'pg@123'"
psql -h localhost -p 5433 -U postgres -d pagila -f "pagila-schema.sql"
psql -h localhost -p 5433 -U postgres -d pagila -f "pagila-data.sql"
Continue reading "REST in PostgreSQL Part 1 - The DB components"
Wednesday, March 12. 2008
Printer Friendly
What is REST?
Representationl State Transfer (REST) is a term to describe an architectural style of sharing information with consumers using already existing protocols such as HTTP. In the strictest sense of the term, the transport protocol need not be HTTP.
REST was first coined by Roy Fielding in his
year 2000 doctoral thesis. Unlike things like Simple Object Access Protocol (SOAP), Distributed Component Object Model (DCOM), Common Object Request Broker Architecture (CORBA), it is not an architecture nor a protocol but rather a style
of architecture. As a result, a lot of things that don't call themselves RESTFUL full under that umbrella or use some of the same concepts, or can arguably fall under that umbrella.
What is probably so alluring about REST is that it gives a catchy name to something a lot of people were doing already and describes how much of the web operates.
Contrary to some popular belief, it is not NEW technology but rather a grouping of mainstream technology with a flashier name. Part of this confusion is that the cult following of REST is a fairly new phenomenon although the underpinnings are relatively old. The REST movement reflects a return back to the basics that prioritizes simplicity and accessibility over complexity and formality.
Nowadays REST is most often used to refer to web services where resources are requested via plain URIs and GET requests, representations are returned in simple XML or JSON format and resources are created using POST, updated using PUT and deleted using DELETE HTTP verbs. This is similar to XML-RPC except that
XML-RPC has a concept of state and everything is generally encoded in an XML message envelop. XML-RPC also uses POST for both updating and accessing resources unlike REST which tends to use GETS and URIS for resource access. The advantage of using get is that resources can be bookmarked. SOAP is similar to XML-RPC and in fact was born from the XML-RPC standard except the XML message streams are more complicated and formal, but arguably richer in functionality.
Contrary to some popular belief, REST is not a silver bullet nor was it designed to be. It doesn't work for all problems and web applications. Our personal opinion: REST is well suited for transporting data that will be consumed by various kinds
of clients, but is not well suited for updating of data or where authenticated transactions are needed.
REST has 4 basic features that differentiate/and it shares with similar Architectural styles.
- Client Stateless Server - As the name suggests - the state of an object is part of the message, and is commonly referred to as a stateless communication. It is not done with things like Session cookies where the
server maintains some stateful view of the client and the client passes a session cookie saying (here is my ticket - give me my state). The server does not hold information about state, only the client.
Right away one can tell - this can not work for all modes of communication that require immense amounts of state information to be maintained, but does have the advantage of should the application server hiccup or connection to the server times out or breaks only the current message is lost.
It also works well for Web-Farms that are simply outputing data since the need for such Web-Farms to replicate state is not needed (think image caching networks such as Akamai).
- Client-Cache - The idea of client caching. The server can dictate certain requests as being cacheable and if cacheable a client can use the cache request to satisfy future similar requests instead of going back to the server. This saves on band-width but has disadvantage of possibly resulting in stale results. Keep in mind again this concept is not
new and most webservers are designed to work that way and pass this info via http headers.
- Layered System - two way interaction. In a REST style architecture, there is a client and a server. The client is only dependent on the server it communicates with. It has no knowledge of the components the server uses to fulfill its request.
That server can be a client in another REST interaction and keep its own cache to serve up like requests. This does not break the client keeps the cache rule as the server is acting as a client in this context. Think DNS. DNS is a perfect example of such a style where intermediaries cache
requests for a certain period of time and act as clients to DNS servers further up the root and behave as servers to DNS and client computers below.
- Resource and Resource Identifiers - REST is predominantly a mechanism for accessing resources although it can be used for editing as well. The
key element of it is a mechanism for defining resources,
how a resource or grouping of resources are requested via a Resource Identifier (URL or URN), transfer of representation via (HTML, XML, Jpeg etc.),
representational metadata (e.g. media type, last modified), control data (such as how long it can be cached). Yes this is pretty much
a common concept in web interfaces.
Continue reading "Showcasing REST in PostgreSQL - The PreQuel"
Friday, February 29. 2008
Printer Friendly
What is TSearch?
TSearch is a Full-Text Search engine that is packaged with PostgreSQL. The key developers of TSearch are Oleg Bartunov and Teodor Sigaev who have also done extensive
work with GiST and GIN indexes used by PostGIS, PgSphere and other projects. For more about how TSearch and OpenFTS got started check out A Brief History of FTS in PostgreSQL.
Check out the TSearch Official Site if you are interested in related TSearch tips or interested in donating to this very worthy project.
Tsearch is different from regular string searching in
PostgreSQL in a couple of key ways.
- It is well-suited for searching large blobs of text since each word is indexed using a Generalized Inverted Index (GIN) or Generalized Search Tree (GiST) and searched using text search vectors. GIN is generally used for indexing. Search vectors
are at word and phrase boundaries.
- TSearch has a concept of Linguistic significance using various language dictionaries, ISpell, thesaurus, stop words, etc. therefore it can ignore common words and
equate like meaning terms and phrases.
- TSearch is for the most part case insensitive.
- While various dictionaries and configs are available out of the box with TSearch, one can create new ones and customize existing further to
cater to specific niches within industries - e.g. medicine, pharmaceuticals, physics, chemistry, biology, legal matters.
Prior to PostgreSQL 8.3, it was a contrib module
located in the shared/contribs folder. As of PostgreSQL 8.3 it is now fully integrated into the PostgreSQL core.
The official documents for using TSearch in 8.3 are located in
Chapter 12. Full Text Search of the official
PostgreSQL documentation.
In this article we shall provide a quick primer to using TSearch in 8.3.
In the next month's issue of the Postgres OnLine Journal we shall provide a TSearch cheat sheet similar to our PostgreSQL 8.3 cheat sheet.
Continue reading "TSearch Primer"
Tuesday, February 26. 2008
Printer Friendly
PostgreSQL 8.3 introduced a couple of new features that improves the processing of functions and makes plpgsql
functions easier to write. These are as follows:
- The new ROWS and COST declarations for a function. These can be used for any PostgreSQL function written in any language. These declarations
allow the function designer to dictate to the planner how many records to expect and provide a hint as to how expensive a function call is. COST is measured in CPU cycles. A higher COST number means more costly.
For example a high cost function called in an AND where condition will not be called
if any of the less costly functions result in a false evaluation. The number of ROWs as well as COST will give the planner a better idea of
which strategy to use.
- RETURN QUERY functionality was introduced as well and only applies to plpgsql written functions.
This is both an easier as well as a more efficient way of returning query results in plpgsql functions.
Hubert Lubazeuwski provides an example of this in set returning functions in 8.3.
We shall provide yet another example of this.
- Server configuration parameters can now be set on a per-function basis. This is useful say in cases where you know a function will need a lot of work_mem, but you don't want to
give all queries accessing the database that greater level of worker memory or you are doing something that index scan just works much better than sequential scan and you want to
change the planners default behavior only for this function.
- Scrollable Cursors in PL/pgSQL - this is documented in Declaring Cursor Variables
- Plan Invalidation - Merlin Moncure covers this in PostgreSQL 8.3 Features: Plan Invalidation so we won't bother giving
another example of this. Basic point to take away from this is that in procedures where you have stale plans floating dependent on tables being dropped by a function, those plans will
be automagically deleted so you don't have ghost plans breaking your function.
Continue reading "New Features for PostgreSQL Stored Functions"
Wednesday, February 20. 2008
Printer Friendly
What is FWTools and OGR GDAL?
FWTools GIS Toolkit is a freely available open source toolkit for Windows and Linux that can do more than GIS tricks.
It is a precompiled bundle of Open Source GIS tools.
The FW comes from the initials of Frank Warmerdam,
the originator of the toolkit and current President of the Open Source Geospatial Foundation (OSGEO).
One key component of the GIS Toolkit is the GDAL/OGR library. Parts of the library have been enhanced by several in the OSGEO community.
GDAL is a basic foundation of countless Open source GIS as well as commercial GIS applications. Here are
Listings of commercial and open source software
that use it and GDAL sponsors.
This is a library which historically has been developed and maintained by Frank Warmerdam, but has started to garner quite a few developers.
GDAL is X/MIT licensed (similar to BSD license), therefore the licensing is very generous for commercial use. The toolkit can be downloaded from
http://fwtools.maptools.org/
Continue reading "GDAL OGR2OGR for Data Loading"
Wednesday, February 13. 2008
Printer Friendly
Every once in a while you are tasked with an issue such as having to create logging fields
in each of your tables or having to put like constraints on each of your tables or you need to Grant an X group or X user rights to
a certain set of tables.
The nice thing about having an information_schema is that it provides an easy way to generate scripts to do just that with plain SELECT statements.
In PostgreSQL its even easier with the array_to_string functions and ARRAY functions, you can get the script in a single field result.
In the following sections we'll demonstrate some examples of this.
Continue reading "DML to generate DDL and DCL- Making structural and Permission changes to multiple tables"
Tuesday, February 12. 2008
Printer Friendly
As Robert Treat pointed out in our PostgreSQL 8.3 is out and the Project Moves On,
one of the features that was introduced in PostgreSQL 8.0 was the syntax of
ALTER TABLE sometable
ALTER COLUMN somecolumn TYPE new_data_type
USING some_function_call_to_cast(somecolumn);
Continue reading "How to convert a table column to another data type"
Printer Friendly
PostgreSQL 8.3 is out
As many have said - PostgreSQL 8.3 was released on February 4th, 2008 and has numerous enhancements.
Listing of features can be found at PostgreSQL 8.3 release notes,
and has been mentioned ad-nauseum by several Postgres bloggers. Robert Treat has provided a nice round-up of blog entries
that demonstrate various 8.3 enhancements in his PostgreSQL Blog's 8.3 Feature Round-Up.
As a side note, the new EnterpriseDb funded Stack Builder feature for windows provides a nice complement for getting add-ons to PostgreSQL.
Horizon of PostgreSQL
Many PostgreSQL contributors are very proud of the fact that PostgreSQL is an open source
project and therefore can not be bought like MySQL which is an open source product made by a commercial company. I'm not sure general PostgreSQL users really care that much
about this. I suspect that many think
- yah - and Microsoft can be bought, Oracle can be bought, IBM can be bought - who is big enough to buy them and like they will kill off their prize cows
- and if Oracle, IBM or
Microsoft one day were to give away non-crippled versions of Oracle 11G, SQL Server 2008, DB2 to leverage their other holdings (perhaps slightly unrealistic),
what would that mean to PostgreSQL or MySQL?
- Can a community grow without money pumping into it and if it is not growing does it F*** matter that it is an open source project?
Continue reading "PostgreSQL 8.3 is out and the Project Moves On"
Friday, February 08. 2008
Printer Friendly
One of our favorite features of PgAdmin is the graphical explain plan feature. While a graphical explain plan is not a complete
substitute for EXPLAIN or EXPLAIN ANALYZE text plans, it does provide a quick and easy to read view that can be used for further analysis.
In this article, we'll walk thru using
the explain plan to troubleshoot query performance.
To use the graphical explain plan feature in PgAdmin III - do the following
- Launch PgAdmin III and select a database.
- Click the SQL icon
- Type in a query or set of queries, and highlight the text of the query you want to analyse.
- Click the F7 button or go under Query->Explain or click the Explain Query icon .
- If you see no graphical explain plan, make sure that Query->Explain options->Verbose is unchecked - otherwise graphical explain will not work
- In terms of Explain option under the Query->Explain options-> you can choose Analyze which will give you the actual Explain plan in use and actual time and will take longer to run. Unchecking
this feature gives you the approximate explain plan and does not include time since its approximate. In terms of the graphical display - the raw display doesn't look too different between the 2, but if you click
on a section of the graph, a little tip will pop up showing the stats for that part of the graph. For analyze, you will see time metrics in the tip.
Continue reading "Reading PgAdmin Graphical Explain Plans"
Monday, February 04. 2008
Printer Friendly
Question: How do you move tables and views from one schema to another?
Often times when you start a new database, you put all your tables and views in the public schema. For databases
with few tables and all relatively commonly grouped data, the benefits of using schemas for logical groupings vs. the downside
of having to reference it with the schema name is more trouble than its worth.
As time goes by and with organic growth, this simple database you had that does one thing suddenly starts doing a lot of other things
you hadn't initially planned for. Now to keep your sanity you really need the benefit of schemas for logical groupings. How do you
retroactively do this? The answer is not quite as easy as one would hope. Ideally you would want to do a RENAME from public.sometable to newschema.sometable,
but that doesn't work.
Continue reading "Moving tables from one schema to another"
Friday, February 01. 2008
Printer Friendly
Welcome to our third Issue (March 2008) of Postgres Online Journal. We folded January and February together because February is a short month and we felt we had already covered quite a bit of ground. This also gives us time
to fill March.
We have made a couple of changes to the underlying PDF output structure that we
would like to cover.
- We now include Reader comments as an appendix at the end of the PDF version with a Bookmark back to the commented article since it seems people made a lot of useful comments that provided additional
information to the topics we discussed.
- It turns out we have a lot of Non-U.S readers. Our stats suggest US readers make up about 25% of our population followed by
Germany, Poland, Italy, Japan, France, Spain, UK, and Canada. I'm actually not sure what paper sizes the other nations use, but we have created
an A4 version as well to make UK people happy.
Our January/February 2008 (Issue 2), can be downloaded from these links 8 1/2 by 11" and A4
In this issue as mentioned we hope to cover creating a lite Representational State Transfer (REST) application to showcase the new Full Text and XML features introduced in 8.3. Someone
suggested we create a Java based server version to compliment our proposed ASP.NET and PHP versions since a lot of PostgreSQL users are Java Programmers. It has been a while since we've programmed with Java Servlets and JSP
so not sure if we'll embarass ourselves too much by attempting such an example, but we'll think about it.
We are looking forward to PostgreSQL 8.3 and have started to test out 8.3 RC2 with our existing apps. So far so good, except for some little
glitch we had using PostGIS in conjunction with UMN Mapserver. That particular problem seems to be more an issue with the UMN Mapserver Postgis driver with the way its reading
the version number in PostgreSQL than anything else (the RC seems to be throwing it off). This issue only affects our more complicated view-based layers and we suspect will be a non-issue when PostgreSQL 8.3 has a bonafide version name e.g. 8.3.0 instead of 8.3 RC2. Aside from that we have noticed speed improvements among other things.
Check out Stefan Kaltenbrunner's Feature Matrix described here and displayed here. It is
really quite a useful chart for those thinking of upgrading or wondering why they should or planning to move over from some other DBMS and waiting for a specific feature to be available in Postgres before they can make the jump.
|