How to make a PostgreSQL query slow
By Szymon Lipiński
November 5, 2012
Some applications can be very vulnerable to long running queries. When you test an application, sometimes it is good to have a query running for, let’s say, 10 minutes. What’s more it should be a normal query, so the application can get the normal results, however this query should run for some longer time than usual.
PostgreSQL has quite a nice function pg_sleep which takes exactly one parameter, it is the number of seconds this function will wait before returning. You can use it as a normal PostgreSQL function, however it’s not very sensible:
# SELECT pg_sleep(10);
pg_sleep
----------
(1 row)
Time: 10072.794 ms
The most interesting usage is adding this function into a query. Let’s take this query:
# SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname <> 'pg_catalog';
Time: 0.985 ms
As you can see, this query is quite fast and returns data in less than 1 ms. Let’s now make this query much slower, however returning exactly the same data, but after 15 seconds:
# SELECT schemaname, tablename
FROM pg_tables, pg_sleep(15)
WHERE schemaname <> 'pg_catalog';
Time: 15002.084 ms
In fact the query execution time is a little bit longer, the …
postgres
How to Build a Command Line Executable Installer with Rubygems and Thor
By Tim Case
November 2, 2012
Gems for Rails often need the user to do something more for installation than just adding the gem to a Gemfile and running bundler install. Sometimes it’s a simple matter of copying over some migration files and sometimes it’s just setting up a config file, and most of the time these simple installation steps are best handled with a well written installation section in the README file. When the installation process is more complex a long README might not be so enticing to the potential gem user, in a world where everyone has a finger on the back button it’s nice to be able to create an installer that allows the user to complete complex installation tasks by executing a one liner and that’s where an installer made through Gem executables and Thor can come in handy.
We wanted to make it easier for new users of Piggybak to get started and decided that an installer was the best way to do that. Creating a binary installer that is installed by Rubygems is one of those esoteric things that may not be thought of as one of the core strengths of Rubygems and Rails but it’s a bonus to be able to do something like this without a whole lot of fuss.
Creating an …
piggybak rails
Association Extensions in Rails for Piggybak
By Steph Skardal
October 31, 2012
I recently had a problem with Rails named scopes while working on minor refactoring in Piggybak, an open source Ruby on Rails ecommerce platform that End Point created and maintains. The problem was that I found that named scopes were not returning uncommitted or new records. Named scopes allow you to specify ActiveRecord query conditions and can be combined with joins and includes to query associated data. For example, based on recent line item rearchitecture, I wanted order.line_items.sellables, order.line_items.taxes, order.line_items.shipments to return all line items where line_item_type was sellable, tax, or shipment, respectively. With named scopes, this might look like:
class Piggybak::LineItem < ActiveRecord::Base
scope :sellables, where(:line_item_type => "sellable")
scope :taxes, where(:line_item_type => "tax")
scope :shipments, where(:line_item_type => "payment")
scope :payments, where(:line_item_type => "payment")
end
However, while processing an order, any uncommited or new records would not be returned when using these named scopes. To work around this, I added the Enumerable select method to iterate …
ecommerce piggybak ruby rails
PostgreSQL auto_explain Module
By Szymon Lipiński
October 30, 2012
PostgreSQL has many nice additional modules, usually hidden and not enabled by default. One of them is auto_explain, which can be very helpful for bad query plan reviews. Autoexplain allows for automatic logging of query plans, according to the module’s configuration.
This module is very useful for testing. Due to some ORM features, it is hard to repeat exactly the same queries with exactly the same parameters as ORMs do. Even without ORM, many applications make a lot of different queries depending on input data and it can be painful the repeat all the queries from logs. It’s much easier to run the app and let it perform all the queries normally. The only change would be adding a couple of queries right after the application connects to the database.
At the beginning let’s see how my logs look when I run “SELECT 1” query:
2012-10-24 14:55:09.937 CEST 5087e52d.22da 1 [unknown]@[unknown] LOG: connection received: host=127.0.0.1 port=33004
2012-10-24 14:55:09.947 CEST 5087e52d.22da 2 szymon@szymon LOG: connection authorized: user=szymon database=szymon
2012-10-24 14:55:10.860 CEST 5087e52d.22da 3 szymon@szymon LOG: statement: SELECT 1;
2012-10-24 14:55 …postgres
An Encouraging LinuxFest
By Josh Williams
October 29, 2012
A few weekends ago I gave a talk at Ohio LinuxFest: Yes, You Can Run Your Business On PostgreSQL. Next Question? (slides freshly posted.) The talk isn’t as technically oriented as the ones I’ll usually give, but rather more inspirational and encouraging. It seemed like a good and reasonable topic, centered around Postgres but applicable to open source in general, and it’s something I’d been wanting to get out there for a while.
In a previous life I worked with Microsoft shops a bit more often. You know, companies that use Windows and related software pretty much exclusively. This talk was, more or less, a result of a number of conversations with those companies about open source software and why it’s a valid option. I heard a number of arguments against, some reasonable, some pretty far out there, so it felt like it’d be a good thing to gather up all of those that I’d heard over time.
These days I don’t interact with those companies so much, so I was a little worried at first that the landscape had changed enough that the talk wouldn’t really be useful any more. But after talking with a few people around the conference a …
conference database open-source postgres
Postgres system triggers error: permission denied
By Greg Sabino Mullane
October 25, 2012
This mystifying Postgres error popped up for one of my coworkers lately while using Ruby on Rails:
ERROR: permission denied: "RI_ConstraintTrigger_16410" is a system trigger
On PostgreSQL version 9.2 and newer, the error may look like this:
ERROR: permission denied: "RI_ConstraintTrigger_a_32778" is a system trigger
ERROR: permission denied: "RI_ConstraintTrigger_c_32780" is a system trigger
I labelled this as mystifying because, while Postgres’ error system is generally well designed and gives clear messages, this one stinks. A better one would be something similar to:
ERROR: Cannot disable triggers on a table containing foreign keys unless superuser
As you can now guess, this error is caused by a non-superuser trying to disable triggers on a table that is used in a foreign key relationship, via the SQL command:
ALTER TABLE foobar DISABLE TRIGGERS ALL;
Because Postgres enforces foreign keys through the use of triggers, and because data integrity is very important to Postgres, one must be a superuser to perform such an action and bypass the foreign keys. (A superuser is a Postgres role that has “do anything” privileges). We’ll look at an example of …
database postgres rails
The truth about Google Wallet integration
By Marina Lohova
October 19, 2012
Google Wallet integration is quite a bumpy ride for every developer. I would like to describe one integration pattern that actually works. It is written in PHP for Google Wallet 2.5 API.
Google Merchant account settings
First, one must sign up for Google Merchant account. Once this is done, it is very important to configure the service properly on the Settings > Integration tab
Buy now button
Buy Now buttons are the simplest form of integration. The code for the button can be obtained on the Tools > Buy Now Buttons tab.
I modified the code provided by Google to transfer information to Google Wallet server via the hidden fields on the form.
<form method="POST" action="https://sandbox.google.com/checkout/api/checkout/v2/checkoutForm/Merchant/<merchant_id>" accept-charset="utf-8">
<input type="hidden" name="item_name_1" value=""/>
<input type="hidden" name="item_description_1" value="Subscription Fees"/>
Enter Amount to Deposit:<input type="text" class="normal" size="5" name="item_price_1" value=""/>
<input type="hidden" name= …ecommerce payments php api
Case Sensitive MySQL Searches
By Brian Buchalter
October 18, 2012
MySQL’s support for case sensitive search is explained somewhat opaquely in the aptly titled Case Sensitivity in String Searches documentation. In short, it explains that by default, MySQL won’t treat strings as case sensitive when executing a statement such as:
SELECT first_name FROM contacts WHERE first_name REGEXP '^[a-z]';
This simple search to look for contacts whose first name starts with a lower case letter, will return all contacts because in the default character set used by MySQL (latin1), upper and lower case letters share the same “sort value”.
UPDATE: After many helpful comments from readers, it would seem the term I should have used was collation, not sort value. The documentation for both MySQL and PostgreSQL have lengthy discussions on the topic.
Enough with the backstory, how do I perform case sensitive searches!
The docs say to convert the string representation to a binary one. This allows “comparisons [to] use the numeric values of the bytes in the operands”. Let’s see it in action:
SELECT first_name FROM contacts WHERE BINARY(first_name) REGEXP '^[a-z]';
There are other strategies available, such as changing the character set being used for …
database mysql