In the previous article, we talked about creating your first PostgreSQL database and I went over the syntax of createdb and psql in a bit of detail. Next up, we’re going to look at creating the structure of the pagila database.``` createdb -U jeremiah pagila psql -U jeremiah -f pagila-schema.sql pagila psql -U jeremiah -f pagila-insert-data.sql pagila ### Working With SQL Even though SQL is the name of the language that we use to work with data, there are two subsets of SQL that you will hear people refer to: DML and DDL.

Ever want to know how many queries are referring to other databases on a server? How about a different server? Worry no more! I have a query that will help you answer these pesky questions:``` SELECT DB_NAME() AS current_db_name, OBJECT_NAME(referencing_id) AS o_name , UPPER(COALESCE(sed.referenced_server_name, ‘’)) AS referenced_server_name, sed.referenced_database_name, sed.referenced_schema_name, sed.referenced_entity_name FROM sys.sql_expression_dependencies AS sed WHERE referenced_database_name <> DB_NAME() AND referenced_database_name <> ‘msdb’ ORDER BY UPPER(referenced_server_name) ; Want to run it across every database on your server?

Once you’ve installed PostgreSQL (OS X Instructions) (other platforms), you’ll probably want to get started with a new database. Unfortunately PostgreSQL does not include a sample database by default. You can download the pagila sample database from the pgFoundry (direct download link). Pagila is a simple movie rental database, sort of like what you would get if you ran a video rental store. It is also a port of a sample MySQL database with changes made to take advantage of PostgreSQL’s features – you can read about the differences in the README file.

I totally ripped this idea off. I think that’s okay, I’m giving credit. The premise is responding to a set of interview questions written thusly: I like _____________________ . I love _____________________ . I want _____________________ . I was _____________________ . I am _____________________ . I will _____________________ . I think _____________________ . I know _____________________ . Answers: I like making up the plan as I go. I love summer thunderstorms.

A while back, I wrote up a genius piece of code that would automatically shrink my log files whenever they grew. Kendra Little (blog | twitter) completely called me out for my horrible, sneaky, developer ways. Ostensibly, I had found a solution for my rampant log growth problem. Unfortunately, I had cured the symptom and not the underlying issue. After growing tired of her savage abuse and criticism via gtalk, I looked for the source of the problem.

Ever have a database log file grow to epic proportions on the weekend? I have. Ever forget to set up job monitoring on that server? I have. Ever get so pissed off that you decided to write a job to automatically shrink the log files whenever they grow? I have. I used my rudimentary knowledge of Service Broker, Event Notifications, and hackery to create a solution to my problem. For starters, I added the following stored procedure to every database:``` CREATE SCHEMA dba ; GO

The Story So Far I’ve been in love with data storage since I first opened up SQL*Plus and issued a select statement. The different ways to store data are fascinating. Even within a traditional OLTP database, there are a variety of design patterns that we can use to facilitate data access. We keep coming up with different ways to solve the problems what we’re facing in business. The problem is that as the field of computer science advances, and businesses increase in complexity, the ways that we store data must become more complex as well.

Let’s get this out of the way: I think triggers are cool. I also think triggers have their place, they just need to be used with care. One reason for this is that triggers are synchronous. That is – trigger actions will block a command from returning to the client until the the trigger’s actions have completed. What’s the solution? Do we run summary jobs on a regular basis? Maybe. Sometimes summary jobs aren’t enough.

As in, I didn’t participate in the most recent T-SQL Tuesday about my favorite feature in SQL 2008 R2. Want to know my favorite 2008R2 features? PostgreSQL 9.0 and MongoDB. PostgreSQL and MongoDB are rapidly advancing features that solve my daily problems. I’m an OLTP guy. Honestly, I don’t care about the latest reporting ding dongs and doo dads. I already know PowerShell. I manage 6 production servers and we’re unlikely to grow, so these MDM and Utility Control Points don’t make me giddy with excitement.

I had a production issue tonight. Still am, actually. I’ve admitted to it and here’s the email I’m sending to my management. At 9:00PM I took a backup of database_a and database_b prior to running the database migration scripts. Once the backups were finished, I began the migration process at approximately 9:20. I stopped the migration process at 10:15 after multiple failures and restarts. There are too many unknown cross-dependencies to go on with the roll forward.