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.
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
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.
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.
PgAgent is a basic scheduling agent that comes packaged with PgAdmin III (since pre-8.0 or so) and that can be managed by PgAdmin III.
PgAdmin III is the database administration tool that comes packaged with PostgreSQL.
For those familiar with
unix/linux cronjobs and crontab structure, PgAgent's scheduling structure should look very familiar.
For those familiar with using Microsoft SQL Server Scheduling Agent or Windows Scheduling Tasks, but not used to crontab structure,
the PgAdmin III Job Agent interface to PgAgent should look very welcoming, but the schedule tab may look a little unfamiliar.
PgAgent can run both PostgreSQL stored functions and sql statements as well as OS shell commands and batch tasks.
In the next couple of sections we will outline the various things one will find in a PostgreSQL database. Many of these exist in other DBMS systems, but some of these are quite unique to PostgreSQL.
Exploring PostgreSQL with PgAdmin III
PgAdmin III is the Administrative console that comes packaged with PostgreSQL. It works equally well on most OSes - Linux, Unix, Windows, MacOS and any OS supported by WsWidgets.
It is an extremely nice and capable management tool. PostgreSQL server comes packaged with this, but if you want to install this on a computer that doesn't have PostgreSQL server installed or you want the bleeding edge version or latest version, I suggest downloading from PgAdmin Site: http://www.pgadmin.org/download/. We will be exploring PostgreSQL with the newest stable releaseof PgAdmin III - 1.8.
When you first launch PgAdmin III and register your postgres server, you may be amazed at the number of things shown. In fact what is shown may not be all the objects that exist in PostgreSQL. PgAdmin III 1.8 and above hides a lot of things by default. For this exercise we will turn these settings on so we can see these objects and explore them.
To do so do the following
Open up PgAdminIII
Register a Postgres server if you haven't already by going to File->Add Server
Next go to File->Options and click on the Display tab
Check everything you see including the Show System Objects in the treeview?
Right mouse click on your registered server in the tree, click disconnect and then click connect
The Anatomy Lesson Begins
When you expand the Server tree, you will be first confronted with 4 groups of objects. As outlined below:
Databases - Yap these are databases.
Tablespaces - These define physical locations where stuff is kept. For people coming from a Microsoft SQL Server background (as I am), this is analogous to File Groups.
Group Roles - As the name suggests these represent security groups.
Login Roles - As the name suggests these represent logins.
In the next couple of sections, we will explore these areas a little deeper.
The first thing you will notice is that there are 3 system databases (databases you did not create) and they are postgres, template0, template1. These are outlined below
postgres - database to hold system wide information. If you install PgAgent job scheduling agent - a tool we will cover in a later excerpt, this is usually installed as a schema in the Postgres database.
template0, template1 - these are template databases used as boiler plates for new databases. For people coming from a Microsoft SQL Server background, template1 is analogous to the model database in SQL Server. In fact you can use any database you create as template for new databases.
Question: Why the heck are their 2 template databases? Answer:
template1 is the default template used for new databases. Most people will use template1 as a template for their databases or create more derivative templates. template0 is basically a pristine template unadulterated by any thing except the core postgres stuff. In fact you can not change template0, but you can change template1.
Tablespaces as I mentioned, represent physical locations on disk where things reside. There are 2 tablespaces installed by default:
pg_default - This is the default tablespace where all user-defined objects are stored
pg_global - This is the tablespace used to store system objects.
If you look at the location property of these 2 tablespaces, you will see nothing there. That is because these are always stored in the same
location as where you initialized your PostgreSQL database cluster. Tablespaces that are user created on the other hand, can be stored anywhere on any disk and these you will see location information for.
In general there is rarely a reason to create new tablespaces and such unless you are creating a system with massive numbers of users, databases, and intensive queries.
Tablespaces gives you the flexibility to leverage OS space in interesting ways - e.g. fast disks for commonly used tables, ability to have multiple disks in different RAID configurations for maximum seek performance, recoverability, or stability etc.
Finding Optimum tables placement in 2 tablespace situation by Hubert Lubaczewski is particularly interesting. Also check out Robert Treat's tablespace configuration variable tweaking tips.
There are a couple of facts I would like to close with on the topic of tablespaces.
Databases are homed in a tablespace. Basically you may say a database resides in X tablespace, but not all tables of a database
need to be stored in X tablespace.
The tablespace you denote for a database is the default location where objects of that database are stored. Also all objects that are not tables and indexes (e.g. functions, views, etc.) are stored in X tablespace and when creating new tables or indexes, they too will be stored in X tablespace unless otherwise noted.
As a corrollary to the above, you can only specifically designate a location for tables, indexes, and a database.
Group Roles and Login Roles
Prior to PostgreSQL 8.1, there existed Users and Groups, in 8.1 these were deprecated and replaced with Roles in order to be more ANSI compliant. This is actually a simplification of the security model. For more details check out the Chapter 18. Database Roles and Privileges
I'll summarize a few key facts about Group Roles and Login Roles
In PgAdmin III - Group Roles and Login Roles look like two different kinds of objects. In actuality a Login Role is really a subclass of a group role so to speak. It is a role that has login rights.
PostgreSQL has a feature which is a little different than some other databases, and that is that a role need not inherit rights from roles it is a member of.
If you are not aware of this, it can bite you. For example if you create a login role and have it set to not inherit rights from its parent roles, you may be surprised to find out that even though the roles it is a member of has rights to certain tables, the login role does not. In order to use those rights, the logged in user needs to do SET ROLE somerolename.
Now one may ask why would I ever need this feature? One reason that comes to mind is for debugging - you may want to create a user that is a member of several roles, and you want to test what rights each role has by constantly setting roles etc. Another reason is for security reasons - you may want to create a role that acts like an Application role and regardless of what login a user is logged into, you only want that user to have rights dictated by the security policy you set forth for that application. So within the application you could have logic that sets the role to that of the application, but allow the user to login with their standard login role.
Roles can be nested. You may have a role that is a member of yet another role, but you are prevented by the system from creating circular dependency roles -Roles that are members of other roles that those roles are a member of these roles
A role can be a member of multiple roles
LOGIN, SUPERUSER, CREATEDB, and CREATEROLE are not inheritable, but can be accessed by doing a SET ROLE call. Similar to in unix/linux when you do sudo
Coming Next Database Objects
In the next issue of this journal, we will go over database objects. In fact there are tons of these. I will leave you with a snapshot to wet your appetite.