Seven months ago, I wrote the presentation Programming the SQL Way with Common Table Expressions. Today, I was finally able to present it at the Montreal ConFoo conference, so I have placed the slides online.

My motivation for writing the talk is that, while I have seen several Postgres common table expression (CTE) talks, they started at too advanced a level for me to understand how all the CTE parts worked together. My talk starts at a basic level and adds features slowly until complex CTE queries are shown. The audience seemed to like the talk and had good questions, so I will take that as a good sign. 

If you're new here, you may want to subscribe to our RSS feed or follow us on Twitter for product announcements, event information, and industry news.

During recent training, I realized there five ways to control who can access Postgres, and it isn’t totally clear in the documentation how they fit together. There are basically a series of gates that users must pass through to connect to a Postgres database. I thought I would list them here, in the order they are applied:

• listen_addresses: This controls which network interfaces Postgres will listen on for connections. This default so “localhost” on most Postgres distributions, including the source install. However, the click-through installers default to ”*”, meaning they listen on all interfaces by default.
• Host-Based Authentication (pg_hba.conf): The pg_hba.conf file controls connection access to the server. It can filter on:
  • ssl mode
  • database name
  • user name
  • IP address
• Authentication: Postgres supports eleven authentication methods; hopefully one of them matches your site-wide standard.
• Database CONNECTION LIMIT: ALTER DATABASE allows you to set the number of people who are allowed to connect to a specific database. Setting it to zero disallows new database connections.
• Role CONNECTION LIMIT: ALTER USER allows the same control as ALTER DATABASE, but for roles.

Once all of these gates have been passed, authorization is now controlled by the GRANT and REVOKE commands.

In my last blog,I performed a very short DB migration from a local PostgreSQL installation to one running in the cloud. In thisblog, I want to share the small “wine database” application that I used. All of the code is available inthis zip file, including a pom.xml file for building through maven (if you want to build without maven, the only dependencies are the Java EE APIs and Vaadin — see below). My target audience is developers who know how to set up JDBC connection pools and generate DB tables, but if there’s interest I can follow up with a step-by-step guide to deploying this application.

Before getting into the application,hereis the SQL used for creating the database tables and initial information (the file is also includedin the zip above in the ’sql’ directory). There are two simple tables, one for wines and one for wineclassifications. The wine table holds a foreign key to entries in the classifications table.

The application is a small Java EE app using Vaadin for the web framework.Because the last post was more about the database than the application, it’s not a full CRUD app: it’smore CR-oriented in that you can save and view data only. Very little code is required to create thisapplication: one POJO for each DB entity/table, a simpleEJBservice object between the UI and back end, three files for the whole UI, and a very shortservlet class used to load the UI. Please see the source code for full comments, but here is the relevant part of the servlet class:

@WebServlet(urlPatterns = {”/app/*”, “/VAADIN/*”})
public class UIServlet extends AbstractApplicationServlet {
@EJB
WineService service;
@Override
protected Application getNewApplication(HttpServletRequest request)
throws ServletException {
return new MainUI(service);
}
@Override
protected Class<? extends Application> getApplicationClass()
throws ClassNotFoundException {
return MainUI.class;
}
}

That’s all that’s needed to create the Vaadin application object and pass a reference to the EJB service to it. From there, the MainUI.java class creates a small user interface with a table and single button for adding more entries to the table. The button has a listener that pops up a child window containing the form for adding another entry:

To access the back-end data, I’m using two separate strategies. That may sound complicated, but it’s actually due to laziness (a good trait in engineers when used properly!). The table is using Vaadin’s SQL Container, which is very easy to set up. It connects to the database through SQL directly, and handles details like paging, optimistic locking, etc, automatically. The form that adds new wines is using JPA, which also means very little code to write since the EJB can persist an entity while handling transactions, locking, etc, with one line of code:

public void store(Wine wine) {
em.persist(wine);
}

The data source for the table, the SQLContainer, can use a database connection pool to perform its queries. This is as simple as using new J2EEConnectionPool(”jdbc/WineDB”) in the table class. Because this jndi name is used here and in persistence.xml, both database access methods use a single definition of the datasource, as shown here from the GlassFish administration console. Here is the resource definition:

…and here are the relevant details of the winedb-pool connection pool:

Thus, I can change the location of the database in only one place and the application will find it. I’m letting the database assign primary keys rather than letting JPA do it. Here is the SQL, for example, for creating the classification table and inserting some data:

create table classification (
id               serial primary key,
class_name       varchar(255) not null
);
insert into classification (class_name) values (’Cabernet’);
insert into classification (class_name) values (’Merlot’);

Today I realized that I have nine events completed or scheduled for 2012, and they are all in the eastern part of North America (except for Austin, Texas). This is exciting because I am on track to cover the same number of conferences as last year, but without the crazy travel schedule. Not that I don’t love going to distant lands, but it is also nice to stay close to home for a while. This might become typical as the density of Postgres users increases.

I got many requests to hear my Virtualizing Postgres presentation, so I have placed the first part of my talk online. Part 1 is a virtualization primer. It took a lot of digging to find these details, and I have included many URLs for people who want more information. Unfortunately, this part doesn’t cover many Postgres-specific aspects, which will be covered in parts 2 and 3.

A few people have asked where I am presenting this talk; I have no idea, but perhaps it will be chosen by the organizers of one of the upcoming USA one-day conferences.

The United States is planning three one-day Postgres conferences in a one-week span:

• Austin, Texas: March 28, 2012
• Reston, Virginia: March 30, 2012
• New York City, New York: April 2, 2012

I am excited to be attending all three events, along with Greg Smith and Magnus Hagander. I will also be doing training in Austin and New York City. (I am already doing training near Reston next week.)

As part of the work on Postgres 9.2, yesterday Robert Haas applied a much-discussed (December, January, February) patch by Peter Geoghegan to improve sort performance. Having recently blogged about how work_mem affects sort performance, I thought I would re-run my tests to see the improvement:

   Temporary Storage   |   Old  |   New
-----------------------+--------+-------
magnetic               |    68  |    67
magnetic (writeback)   |    50  |    48
SSD                    |    36  |    32
tmpfs                  |    30  |    27
none (high work_mem)   |    12  |     9.5

Continue Reading »

In Series of New Features in Advanced Server 9.1, today I tought to write about Parition Table Syntax Supported in PPAS 9.1. In PostgreSQL and till PPAS 9.0, user has to follow method given below for partitioning a table: 1. …

Continue reading »

Now that EnterpriseDB has launched the Postgres Plus Cloud Database, it’s easy to get a database running in the cloud. This blog covers the next step: migrating a PostgreSQL database that you already have running on traditional hardware to your new cloud database.

To provide a full example, this entry uses a simple web application written in Vaadin. I’m running it on a GlassFish 3.1.1 application server (supports Java EE 6), and the application talks to a PostgreSQL 9.1 database through a JDBC connection that’s configured in the server. This means there’s only one place I need to change my connection information once the data has been migrated.

The application itself is a very simple database for storing information about wine. I’ll give a walkthrough of the application in a subsequent blog, followed by a version that runs on Tomcat that could be deployed to Amazon Elastic Beanstalk.

For better viewing, here is a direct link to the video embedded below.

After setting up the remote database in the cloud, the migration happens in three steps:

1. Back up the existing database to a file.
2. Restore the data into the new database.
3. Point the application to the new database location (and restart if necessary).

For the screen cast, I’m using all command line tools. For more information on using the PostgreSQL tools from the command line, see this article from EnterpriseDB.

Note that the GlassFish administration can be performed from the command line as well. To see a list of the properties that are set, you can use the asadmin “get” command and grep for the information (if you don’t already know the property names):

BobbyMac}> ./asadmin get “*” | grep wine | grep property
resources.jdbc-connection-pool.winedb-pool.property.PortNumber=5432
resources.jdbc-connection-pool.winedb-pool.property.User=postgres
resources.jdbc-connection-pool.winedb-pool.property.ServerName=localhost
resources.jdbc-connection-pool.winedb-pool.property.Password=DemoPass
resources.jdbc-connection-pool.winedb-pool.property.DatabaseName=wine_db

Similarly, you can use the asadmin “set” command to change these values. Then you can redeploy the application to pick up the new values.

Great News is Postgres Plus Advanced 9.1 is now available for users. So, I thought to write something about Virtual Private Database features, which is part of 9.1 Virtual Private Database (VPD) is a feature which enables Administrator to create …

Continue reading »