All opinions expressed are those of the authors and not necessarily those of OSNews.com, our sponsors, or our affiliates.
  Add to My Yahoo!  Subscribe with Bloglines  Subscribe in NewsGator Online

published by noreply@blogger.com (Josh Williams) on 2017-04-01 23:24:00 in the "database" category

In the spirit of April 1st, resurrecting this old classic post:


Maybe you work at one of those large corporations that has a dedicated DBA staff, separate from the development team. Or maybe you're lucky and just get to read about it on thedailywtf.com. But you've probably seen battles between database folk and the developers that "just want a table with "ID " VARCHAR(255), name VARCHAR(255), price VARCHAR(255), post_date VARCHAR(255). Is that so much to ask?!"

Well if you ever feel the need to get back at them, here's a few things you can try. Quoted identifiers let you name your objects anything you want, even if they don't look like a normal object name...

CREATE TABLE "; rollback; drop database postgres;--" ("'';
delete from table order_detail;commit;" INT PRIMARY KEY,
";commit;do $$`rm -rf *`$$ language plperlu;" TEXT NOT NULL);

COMMENT ON TABLE "; rollback; drop database postgres;--"
IS 'DON''T FORGET TO QUOTE THESE';

Good advice, that comment. Of course, assuming they learn, they'll be quoting everything you give them. So, drop a quote right in the middle of it:

CREATE TABLE "messages"";rollback;update products set price=0;commit;--"
("am i doing this right" text);

[local]:5432|production=# dt *messages*
 List of relations
 Schema |                           Name                           | Type  |   Owner   
--------+----------------------------------------------------------+-------+-----------
 public | messages";rollback;update products set price=0;commit;-- | table | jwilliams
(1 row)
A copy & paste later...
[local]:5432|production=# SELECT "am i doing this right" FROM "messages";rollback;update products set price=0;commit;--";
ERROR:  relation "messages" does not exist
LINE 1: select "am i doing this right" from "messages";
                                            ^
NOTICE:  there is no transaction in progress
ROLLBACK
UPDATE 100
WARNING:  there is no transaction in progress
COMMIT

Then again, if this is your database, that'll eventually cause you a lot of headache. Restores aren't fun. But UTF-8 can be...

CREATE TABLE suo????su??? (?nu???p?o SERIAL PRIMARY KEY,
???u??sn text REFERENCES s??sn, ???o????p?o NUMERIC(5,2));

CREATE TABLE ?????_????? (?????_????_?? SERIAL PRIMARY KEY, ... );

Comments

published by noreply@blogger.com (Patrick Lewis) on 2016-12-21 16:19:00 in the "database" category

Rails seed files are a useful way of populating a database with the initial data needed for a Rails project. The Rails db/seeds.rb file contains plain Ruby code and can be run with the Rails-default rails db:seed task. Though convenient, this "one big seed file" approach can quickly become unwieldy once you start pre-populating data for multiple models or needing more advanced mechanisms for retrieving data from a CSV file or other data store.

The Seedbank gem aims to solve this scalability problem by providing a drop-in replacement for Rails seed files that allows developers to distribute seed data across multiple files and provides support for environment-specific files.

Organizing seed files in a specific structure within a project's db/seeds/ directory enables Seedbank to either run all of the seed files for the current environment using the same rails db:seed task as vanilla Rails or to run a specific subset of tasks by specifying a seed file or environment name when running the task. It's also possible to fall back to the original "single seeds.rb file" approach by running rails db:seed:original.

Given a file structure like:

db/seeds/
  courses.seeds.rb
  development/
    users.seeds.rb
  students.seeds.rb

Seedbank will generate tasks including:

rails db:seed                   # load data from db/seeds.rb, db/seeds/*.seeds.rb, and db/seeds/[ENVIRONMENT]/*.seeds.rb
rails db:seed:courses           # load data from db/seeds/courses.seeds.rb
rails db:seed:common            # load data from db/seeds.rb, db/seeds/*.seeds.rb
rails db:seed:development       # load data from db/seeds.rb, db/seeds/*.seeds.rb, and db/seeds/development/*.seeds.rb
rails db:seed:development:users # load data from db/seeds/development/users.seeds.rb
rails db:seed:original          # load data from db/seeds.rb

I've found the ability to define development-specific seed files helpful in recent projects for populating 'test user' accounts for sites running in development mode. We've been able to maintain a consistent set of test user accounts across multiple development sites without having to worry about accidentally creating those same test accounts once the site is running in a publicly accessible production environment.

Splitting seed data from one file into multiple files does introduce a potential issue when the data created in one seed file is dependent on data from a different seed file. Seedbank addresses this problem by allowing for dependencies to be defined within the seed files, enabling the developer to control the order in which the seed files will be run.

Seedbank runs seed files in alphabetical order by default but simply wrapping the code in a block allows the developer to manually enforce the order in which tasks should be run. Given a case where Students are dependent on Course records having already been created, the file can be set up like this:

# db/seeds/students.seeds.rb
after :courses do
  course = Course.find_by_name('Calculus')
  course.students.create(first_name: 'Patrick', last_name: 'Lewis')
end

The added dependency block will ensure that the db/seeds/courses.seeds.rb file is executed before the db/seeds/students.seeds.rb file, even when the students file is run via a specific rails db:seed:students task.

Seedbank provides additional support for adding shared methods that can be reused within multiple seed files and I encourage anyone interested in the gem to check out the Seedbank README for more details. Though the current 0.4 version of Seedbank doesn't officially have support for Rails 5, I've been using it without issue on Rails 5 projects for over six months now and consider it a great addition to any Rails project that needs to pre-populate a database with a non-trivial amount of data.


Comments

published by noreply@blogger.com (Selvakumar Arumugam) on 2016-11-16 11:00:00 in the "database" category
The JPA Repository is a useful Spring Framework library that provides object-relational mapping for Java web applications to be able to connect to a wide variety of databases. Most applications need to establish a connection with one database to store and retrieve the data though sometimes there could be more than one database to read and write. There could also be some cases where the application needs to choose which database should be used dynamically, based on each request's parameters. Let's see how to configure and establish connections for these three cases.

1. Single Static Connection

In order to use JPA the following configurations are required to get the database connection handle and define the interface to map a database table by extending JpaRepository class.

UserRepository.java - this part of the code configures how to map the user table
package com.domain.data;

import com.domain.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository <User, Integer> {
}
persistent-context.xml - the dataSourceReadWrite bean class defines the database connection while the entityManagerFactoryReadWrite bean helps to access the database from the base package com.domain
...
<jpa:repositories base-package="com.domain" entity-manager-factory-ref="entityManagerFactoryReadWrite" transaction-manager-ref="transactionManager">
</jpa:repositories>

<bean abstract="true" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="abstractDataSource" p:driverclassname="${jdbc.driverClassName}" p:maxactive="20" p:maxidle="20" p:minidle="20" p:testonborrow="true" p:validationquery="SELECT 1" />

<bean id="dataSourceReadWrite" p:password="${jdbc.password}" p:url="${jdbc.url}" p:username="${jdbc.username}" parent="abstractDataSource" />

<bean abstract="true" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="abstractEntityManagerFactory" p:jpadialect-ref="hibernateJpaDialect" p:jpavendoradapter-ref="jpaAdapter">
  <property name="jpaProperties">
    <props>
      <prop key="hibernate.use_sql_comments">true</prop>
      <prop key="hibernate.temp.use_jdbc_metadata_defaults">false</prop>
    </props>
  </property>
</bean>

<bean id="entityManagerFactoryReadWrite" p:datasource-ref="dataSourceReadWrite" p:persistenceunitname="readWritePU" parent="abstractEntityManagerFactory">
  <property name="packagesToScan">
    <list>
      <value>com.domain</value>
    </list>
  </property>
</bean>

<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager" p:datasource-ref="dataSourceReadWrite" p:entitymanagerfactory-ref="entityManagerFactoryReadWrite" />
...
UserController.java - the userRepository object access defines how to use a static database configuration to fetch the User object record
@Api(name = "User", description = "User API Service")
@Controller
public class UserController {

  @Autowired
  private UserRepository userRepository;


  @ApiMethod(
    description = "Return the user object using the userId in the URL",
    produces = {MediaType.APPLICATION_JSON_VALUE},
    roles = {"read", "write"}
  )
  @RequestMapping(value = "/users/{userId}", method = RequestMethod.GET, produces = "application/json")
  @ResponseBody
  public UserModel getUser(@PathVariable @ApiParam(name = "userId", description = "User ID") Integer userId) throws ServiceException {
    User user = (userRepository.findOne(userId));
    if (user != null) {
    return new UserModel(user);
  }
  else {
    throw new ResourceNotFoundServiceException("UserId " + userId + " was not found");
  }
}
}

2. Multiple Static Connections

In some cases, we may need to connect more than one database in our application. Usually there will be a primary database and a secondary one which syncs data from the primary, most likely as a readonly replica load balancing approach. In this case the application needs to be configure to establish connection with two different datasources.

To achieve this result it's possible to define ReadWrite and ReadOnly datasources in the spring configuration and then declare the specific Repository classes for each specific datasource.

UserRepository.java - ReadWrite repository definition under the package com.domain.data
package com.domain.data;

import com.domain.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Integer> {
}
UserReadonlyRepository.java - ReadOnly repository definition under the package com.domain.data.readonly
package com.domain.data.readonly;

import com.domain.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserReadonlyRepository extends JpaRepository<User, Integer> {
}
persistent-context.xml - this file defines two different datasources (dataSourceReadWrite and dataSourceReadOnly) while jpa repositories specify the repositories package path
...
<jpa:repositories base-package="com.domain" entity-manager-factory-ref="entityManagerFactoryReadWrite" transaction-manager-ref="transactionManager">
  <repository:exclude-filter expression="com.domain.data.readonly" type="regex"></repository:exclude-filter>
</jpa:repositories>

<jpa:repositories base-package="com.domain.data.readonly" entity-manager-factory-ref="entityManagerFactoryReadOnly" transaction-manager-ref="transactionManagerReadOnly">

<bean abstract="true" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="abstractDataSource" p:driverclassname="${jdbc.driverClassName}" p:maxactive="20" p:maxidle="20" p:minidle="20" p:testonborrow="true" p:validationquery="SELECT 1" />
<bean id="dataSourceReadWrite" p:password="${jdbc.password}" p:url="${jdbc.url}" p:username="${jdbc.username}" parent="abstractDataSource" />
<bean id="dataSourceReadOnly" p:password="${jdbc.readonly.password}" p:url="${jdbc.readonly.url}" p:username="${jdbc.readonly.username}" parent="abstractDataSource" />
<bean abstract="true" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="abstractEntityManagerFactory" p:jpadialect-ref="hibernateJpaDialect" p:jpavendoradapter-ref="jpaAdapter">
  <property name="jpaProperties">
    <props>
      <prop key="hibernate.use_sql_comments">true</prop>
      <prop key="hibernate.temp.use_jdbc_metadata_defaults">false</prop>
    </props>
  </property>
</bean>

<bean id="entityManagerFactoryReadWrite" p:datasource-ref="dataSourceReadWrite" p:persistenceunitname="readWritePU" parent="abstractEntityManagerFactory">
  <property name="packagesToScan">
    <list>
      <value>com.domain</value>
    </list>
  </property>
</bean>

<bean id="entityManagerFactoryReadOnly" p:datasource-ref="dataSourceReadOnly" p:persistenceunitname="readOnlyPU" parent="abstractEntityManagerFactory">
  <property name="packagesToScan">
    <list>
      <value>com.domain</value>
    </list>
  </property>
</bean>

<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager" p:datasource-ref="dataSourceReadWrite" p:entitymanagerfactory-ref="entityManagerFactoryReadWrite" />

<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManagerReadOnly" p:datasource-ref="dataSourceReadOnly" p:entitymanagerfactory-ref="entityManagerFactoryReadOnly" />
...
UserController.java - in this definition it's interesting to note the the readonly flag, which will establish a connection with ReadWrite or ReadOnly database, based on that flag value
@Api(name = "User", description = "User API Service")
@Controller
public class UserController {

  @Autowired
  private UserRepository userRepository;
  @Autowired
  private UserReadOnlyRepository userReadOnlyRepository;

  @ApiMethod(
  description = "Return the user object using the userId in the URL",
  produces = {MediaType.APPLICATION_JSON_VALUE},
  roles = {"read", "write"}
  )
  @RequestMapping(value = "/users/{userId}", method = RequestMethod.GET, produces = "application/json")
  @ResponseBody
  public UserModel getUser(@PathVariable @ApiParam(name = "userId", description = "User ID") Integer userId, @ApiParam(name = "readOnly", description = "Param to set data source to read from") Boolean readOnly) throws ServiceException {
    User user = (readOnly ?
    userReadOnlyRepository.findOne(userId) : userRepository.findOne(userId));

    if (user != null) {
      return new UserModel(user);
    }
    else {
      throw new ResourceNotFoundServiceException("UserId " + userId + " was not found");
    }
  }
}

3. Multiple Dynamic Connections

Recently there was an application that needed to choose the database during API request processing. Unfortunately defining multiple datasources and choosing the database based on the hard coded checks in the code is really cumbersome. Instead it's possible to use JPA Repository which provides a feature to override the database lookup dynamically using AbstractRoutingDataSource when a request is sent to the application.

UserRepository.java - defines mapping to the user table
package com.domain.data;

import com.domain.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Integer> {
}
persistence-context.xml - dataSourceRootDB and dataSourceLiveDB beans defines two different databases. MultitenantRoutingDataSource holds the datasources available to chose dynamically from the code
...
<jpa:repositories base-package="com.domain" entity-manager-factory-ref="genericEntityManagerFactory" transaction-manager-ref="transactionManager">
</jpa:repositories>

<bean abstract="true" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="abstractDataSource" p:driverclassname="${jdbc.driverClassName}" p:maxactive="20" p:maxidle="20" p:minidle="20" p:testonborrow="true" p:validationquery="SELECT 1" />

<bean id="dataSourceRootDB" p:password="${jdbc.password}" p:url="${jdbc.url}" p:username="${jdbc.username}" parent="abstractDataSource" />

<bean id="dataSourceLiveDB" p:password="${jdbc.livedb.password}" p:url="${jdbc.livedb.url}" p:username="${jdbc.livedb.username}" parent="abstractDataSource" />

<bean class="com.domain.route.MultitenantRoutingDataSource" id="dataSource">
  <property name="targetDataSources">
    <map key-type="java.lang.String">
      <entry key="rootdb" value-ref="dataSourceRootDB"></entry>
      <entry key="livedb" value-ref="dataSourceLiveDB"></entry>
    </map>
  </property>
  <property name="defaultTargetDataSource" ref="dataSourceRootDB">
  </property>
</bean>

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="genericEntityManagerFactory" p:datasource-ref="dataSource" p:jpadialect-ref="hibernateJpaDialect" p:jpavendoradapter-ref="jpaAdapter" p:persistenceunitname="readWriteDynamicPU">
  <property name="jpaProperties">
    <props>
      <prop key="hibernate.use_sql_comments">true</prop>
      <prop key="hibernate.temp.use_jdbc_metadata_defaults">false</prop>
    </props>
  </property>
  <property name="packagesToScan">
    <list>
      <value>com.data.domain</value>
    </list>
  </property>
</bean>

<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager" p:datasource-ref="dataSource" p:entitymanagerfactory-ref="genericEntityManagerFactory" />
...
UserController.java - this class choose the datasource dynamically based on the request and calls the selected service to complete the action
...
@Api(name = "User", description = "User API Service") 
@Controller public class UserController {
    @Autowired     private UserService userService;

    @ApiMethod(
            description = "Return the user object using the userId in the URL",
            produces = {MediaType.APPLICATION_JSON_VALUE},
            roles = {"oms-read", "oms-write"}
    )
    @RequestMapping(value = "/users/{userId}", method = RequestMethod.GET, produces = "application/json")
    @ResponseBody
    public UserModel getUser(@PathVariable @ApiParam(name = "userId", description = "User ID") Integer userId, @RequestHeader(value="X-Database", defaultValue= DatabaseEndpointUtils.ROOT_DB, required=false) String databaseEndpoint) throws ServiceException {
        MultiTenantContext.setCurrentTenant(databaseEndpoint);
        return userService.getUser(userId, true);
    }
}
...
MultiTenantContext.java - this code sets the datasource connection based on the request from Controller
package com.domain.common;

import com.domain.util.DatabaseEndpointUtils;
import com.domain.supplychain.app.ws.exceptions.InvalidDatabaseEndpointException;
import com.domain.exceptions.ServiceException;

public class MultiTenantContext {
    private static ThreadLocal<Object> currentTenant = new ThreadLocal<>();

    public static Logger logger = LoggerFactory.getLogger(MultiTenantContext.class.getName());
    public static void setCurrentTenant(Object tenant) throws ServiceException {
        logger.info("MultiTenantContext setCurrentTenant: [{}]", tenant);
        if(DatabaseEndpointUtils.isValidEndpoint(tenant.toString())) {
            currentTenant.set(tenant);
        } else {
            throw new InvalidDatabaseEndpointException("Invalid database endpoint");
        }
    }

    public static Object getCurrentTenant() {
        logger.info("MultiTenantContext getCurrentTenant: [{}]", currentTenant.get());
        return currentTenant.get();
    }

}
MultitenantRoutingDataSource.java - here there's the definition which determines how the datasource establish the connection. Specifically it will get the datasource which was set previously based on the request parameters
package com.domain.route;  
import com.domain.common.MultiTenantContext; 
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;  

public class MultitenantRoutingDataSource extends AbstractRoutingDataSource {

    private Logger logger = LoggerFactory.getLogger(MultitenantRoutingDataSource.class.getName());
    @Override
    protected Object determineCurrentLookupKey() {
        logger.info("MultitenantRoutingDataSource determineCurrentLookupKey: [{}]", MultiTenantContext.getCurrentTenant());
        return MultiTenantContext.getCurrentTenant();
    }

}
DefaultUserService.java - Fetch the user data from the dynamically chosen database.
@Service
public class DefaultUserService implements UserService {

    @Autowired
    private UserRepository userRepository;

    @Override
    @Transactional
    public UserModel getUser(Integer userId, boolean readOnly) throws ServiceException {
        User user = (userRepository.findOne(userId));
        if (user != null) {
            return new UserModel(user);
        }
        else {
            throw new ResourceNotFoundServiceException("UserId " + userId + " was not found");
        }
    }
}

Conclusion

The application establishes a connection with database through any one of these methods, based on the requirement. The single or multiple static connections are commonly used in most of the applications. But when there is a requirement to choose the database dynamically to establish a connection, AbstractRoutingDataSource class in spring framework features a wonderful way to implement the functionality as explained above.

Comments

published by noreply@blogger.com (Kamil Ciemniewski) on 2016-11-02 17:56:00 in the "database" category
Some time ago I was working on customizing a WordPress system for a client. The system was running in a multi-site mode, being a host of a large number of blogs.
Because some blogs had not been updated in a long while, we wanted to pull information about recent posts from all of the blogs. This in turn was going to be used for pruning any blogs that weren't considered 'active'.
While the above description may sound simple, the scale of the system made the task a bit more involving that it would be usually.

How WordPress handles the "multi-site" scenario

The goal of computing the summary of posts for many blogs residing in the hypotethical blogging platform, in the same database doesn't seem so complicated. Tasks like that are being performed all the time using relational databases.
The problem in WordPress arises though because of the very unusual way that it organises blogs data. Let's see how the database tables look like in the "normal" mode first:
It has a number of tables that start with user configurable prefix. In the case of the screenshot above, the prefix was wp_.
We can see there's a wp_posts table which contains rows related to blog posts. Thinking about the multi-blog setup, one would expect some kind of a blog_id column in the wp_posts column. Selecting data for the given blog would still be a cinch. It would also be very performant after adding an index on that column.
Instead, this is what we're getting when setting up WordPress in a multi-site mode:
WordPress just creates a new set of tables we seen before appending the index of the blog to the tables prefix! Instead of having a nice, clean and easy to use wp_posts with the blog_id column, we get a number of tables - one for each blog: wp_1_posts, wp_2_posts etc. Why does it matter that much? Just try to get the counts of posts in each blog in one query ? it's impossible with such a tables setup. Getting such info involves querying each table separately. This means that with each new blog within the system, the cost of running such sequence of queries adds up dramatically. This is also known as a N+1 problem ? bad WordPress! bad!

The approach around it

The counts of posts for each blog was needed to be computed very quickly in my case. The system consisted of hundreds of different mini-blogs and the stats were to be shown in the admin dashboard. Obviously making admins wait for the page to load for a long time wasn't an option.
I went the route of creating an additional database table, holding the info about the number of posts for each blog. This table was then being updated upon each post creation, update and deletion. It was also updated upon the blog removal.
WordPress has a helper function for ensuring the table is created in the database. You feed it the DDL containing the definition of the table and it makes sure it is present in the database.
I created a function that was being fired on each plugin class instantiation, while making that class a singleton. Here's the code that makes the plugin class a singleton:
class BlogsPruner
{
  private static $instance;

  private function __construct()
  {
    $this->ensureDatabaseSetup();
    $this->ensureBlogStatsGetUpdated();
    // ... other initialization here
  }

  public static function singleton()
  {
    if(!isset(self::$instance))
    {
      $_class = __CLASS__;
      self::$instance = new $_class;
    }
    return self::$instance;
  }

  public function __clone()
  {
    trigger_error('Cannot clone the pruner plugin', E_USER_ERROR);
  }

  // ... rest of the class
}

BlogsPruner::singleton();
The next step was to implement the function ensuring there's a stats table in the database:
function ensureDatabaseSetup()
  {
    global $wpdb;
    $tableName = $this->blogStatsTableName();
    $charsetCollate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $tableName (
            blog_id bigint(20),
            count_posts int(2),
            UNIQUE KEY blog_id (blog_id)
    ) $charsetCollate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
    $this->ensureBlogStatsUpdated();
  }
This code uses a helper function to correctly construct the name for the table:
function blogStatsTableName()
{
  global $wpdb;
  return $wpdb->base_prefix . 'blog_stats';
}
This made sure the table was using the correct prefix, just like all the other tables in the database.
Now, I needed to ensure the stats were updated upon each post change:
function ensureBlogStatsGetUpdated()
{
  add_action('save_post', array($this, 'onPostUpdated'));
  add_action('delete_post', array($this, 'onPostDeleted'));
}

function onPostUpdated($postId)
{
  global $blog_id;
  $post = get_post($postId);
  if(wp_is_post_revision($postId) || $post->post_status == 'auto-draft')
  {
    return;
  }
  $this->updateBlogStats($blog_id);
}

function onPostDeleted()
{
  global $blog_id;
  $this->updateBlogStats($blog_id);
}

function updateBlogStats($blogId)
{
  $count = $this->getBlogUserCreatedPostsCount($blogId);
  $this->updateBlogPostsCount($blogId, $count);
}

// Here we're specifically not including the post that is auto-created
// upon the blog creation:
function getBlogUserCreatedPostsCount($blogId)
{
  global $wpdb;
  $sql = "SELECT
            COUNT(DISTINCT `wp_" . $blogId . "_posts`.`id`) AS count_user_posts
          FROM `wp_blogs`
          INNER JOIN `wp_" . $blogId . "_posts`
                  ON `wp_blogs`.`blog_id` = $blogId
          WHERE
            `wp_" . $blogId . "_posts`.`post_type` = 'post' AND
            `wp_" . $blogId . "_posts`.`post_status` = 'publish' AND
            TIMESTAMPDIFF(SECOND, `wp_" . $blogId . "_posts`.`post_date`, `wp_blogs`.`last_updated`) > 60";
  $row = $wpdb->get_row($sql);
  return intval($row->count_user_posts);
}

function updateBlogPostsCount($blogId, $count)
{
  global $wpdb;
  $data = array('count_posts' => $count);
  $where = array('blog_id' => $blogId);
  $wpdb->update($this->blogStatsTableName(), $data, $where);
}
The actual production plugin implemented many more features than this sample code demonstrates. It was listing the blogs that could be considered stale, automatically pruning them after specified in the admin screen time and allowing admins to configure it via the WordPress interface. The full set of features is beyond the scope of this post.
The overall result made getting the statistics about very large set of blogs very fast. The cost of querying for the number of posts of each blog was moved to incremental, small updates upon each post being created, modified or removed. For the end user, this cost was imperceptable.

Final thoughts

WordPress is loved by many users. If you're not just a user but also working with the code, there's a number of traps you may fall into though. If I were to employ techniques that get advised as "WordPress usual/default/preferred" ? I'd end up with a very unhappy client who's be owning a very broken WordPress system. Fortunately, the set of WordPress tables isn't casted in stone and you can freely extend it - as long as you're cautious and know what you're doing. Provided that these two prerequisites are met ? WordPress is just a database backed platform - like any other.
Comments

published by noreply@blogger.com (Josh Williams) on 2016-07-01 22:08:00 in the "database" category
I originally titled this: Inferring Record Timestamps by Analyzing PITR Streams for Transaction Commits and Cross-Referencing Tuple xmin Values. But that seemed a little long, though it does sum up the technique.

In other words, it's a way to approximate an updated_at timestamp column for your tables when you didn't have one in the first place.

PostgreSQL stores the timestamp of a transaction's commit into the transaction log. If you have a hot standby server, you can see the value for the most-recently-applied transaction as the output of the pg_last_xact_replay_timestamp() function. That's useful for estimating replication lag. But I hadn't seen any other uses for it, at least until I came up with the hypothesis that all the available values could be extracted wholesale, and matched with the transaction ID's stored along with every record.

If you're on 9.5, there's track_commit_timestamps in postgresql.conf, and combined with the pg_xact_commit_timestamp(xid) function has a similar result. But it can't be turned on retroactively.

This can -- sort of. So long as you have those transaction logs, at least. If you're doing Point-In-Time Recovery you're likely to at least have some of them, especially more recent ones.

I tested this technique on a pgbench database on stock PostgreSQL 9.4, apart from the following postgresql.conf settings that (sort of) turn on WAL archival -- or at least make sure the WAL segments are kept around:

wal_level = archive
archive_mode = on
archive_command = '/bin/false'

We'll be using the pg_xlogdump binary to parse those WAL segments, available from 9.3 on. If you're on an earlier version, the older xlogdump code will work.

Once pgbench has generated some traffic, then it's time to see what's contained in the WAL segments we have available. Since I have them all I went all the way back to the beginning.

$ pg_xlogdump -p pg_xlog/ --start 0/01000000 --rmgr=Transaction
rmgr: Transaction len (rec/tot):     12/    44, tx:          3, lsn: 0/01006A58, prev 0/01005CA8, bkp: 0000, desc: commit: 2016-05-15 22:32:32.593404 EDT
rmgr: Transaction len (rec/tot):     12/    44, tx:          4, lsn: 0/01008BC8, prev 0/01008A60, bkp: 0000, desc: commit: 2016-05-15 22:32:32.664374 EDT
rmgr: Transaction len (rec/tot):     12/    44, tx:          5, lsn: 0/01012EA8, prev 0/01012E58, bkp: 0000, desc: commit: 2016-05-15 22:32:32.668813 EDT
(snip)
rmgr: Transaction len (rec/tot):     12/    44, tx:       1746, lsn: 0/099502D0, prev 0/099501F0, bkp: 0000, desc: commit: 2016-05-15 22:55:12.711794 EDT
rmgr: Transaction len (rec/tot):     12/    44, tx:       1747, lsn: 0/09951530, prev 0/09951478, bkp: 0000, desc: commit: 2016-05-15 22:55:12.729122 EDT
rmgr: Transaction len (rec/tot):     12/    44, tx:       1748, lsn: 0/099518D0, prev 0/099517F0, bkp: 0000, desc: commit: 2016-05-15 22:55:12.740823 EDT
pg_xlogdump: FATAL:  error in WAL record at 0/99518D0: record with zero length at 0/9951900

The last line just indicates that we've hit the end of the transaction log records, and it's written to stderr, so it can be ignored. Otherwise, that output contains everything we need, we just need to shift around the components so we can read it back into Postgres. Something like this did the trick for me, and let me import it directly:

$ pg_xlogdump -p pg_xlog/ --start 0/01000000 --rmgr=Transaction | awk -v Q=' '{sub(/;/, ""); print $8, Q$17, $18, $19Q}' > xids

postgres=# CREATE TABLE xids (xid xid, commit timestamptz);
CREATE TABLE
postgres=# copy xids from xids csv
COPY 1746

At which point it's a simple join to pull in the commit timestamp records:

postgres=# select xmin, aid, commit from pgbench_accounts inner join xids on pgbench_accounts.xmin = xids.xid;
 xmin |  aid   |            commit             
------+--------+-------------------------------
  981 | 252710 | 2016-05-15 22:54:34.03147-04
 1719 | 273905 | 2016-05-15 22:54:35.622406-04
 1183 | 286611 | 2016-05-15 22:54:34.438701-04
 1227 | 322132 | 2016-05-15 22:54:34.529027-04
 1094 | 331525 | 2016-05-15 22:54:34.26477-04
 1615 | 383361 | 2016-05-15 22:54:35.423995-04
 1293 | 565018 | 2016-05-15 22:54:34.688494-04
 1166 | 615272 | 2016-05-15 22:54:34.40506-04
 1503 | 627740 | 2016-05-15 22:54:35.199251-04
 1205 | 663690 | 2016-05-15 22:54:34.481523-04
 1585 | 755566 | 2016-05-15 22:54:35.368891-04
 1131 | 766042 | 2016-05-15 22:54:34.33737-04
 1412 | 777969 | 2016-05-15 22:54:34.953517-04
 1292 | 818934 | 2016-05-15 22:54:34.686746-04
 1309 | 940951 | 2016-05-15 22:54:34.72493-04
 1561 | 949802 | 2016-05-15 22:54:35.320229-04
 1522 | 968516 | 2016-05-15 22:54:35.246654-04

Comments

published by noreply@blogger.com (Kent K.) on 2016-05-12 17:33:00 in the "database" category

I was recently asked about options for displaying a random set of items from a table using Ruby on Rails. The request was complicated by the fact that the technology stack hadn?t been completely decided on and one of the items still up in the air was the database. I?ve had an experience with a project I was working on where the decision was made to switch from MySQL to PostgreSQL. During the switch, a sizable amount of hand constructed queries stopped functioning and had to be manually translated before they would work again. Learning from that experience, I favor avoidance of handwritten SQL in my Rails queries when possible. This precludes the option to use built-in database functions like rand() or random().

With the goal set in mind, I decided to look around to find out what other people were doing to solve similar requests. While perusing various suggested implementations, I noticed a lot of comments along the lines of ?Don?t use this approach if you have a large data set.? or ?this handles large data sets, but won?t always give a truly random result.?

These comments and the variety of solutions got me thinking about evaluating based not only on what database is in use, but what the dataset is expected to look like. I really enjoyed the mental gymnastics and thought others might as well.

Let?s pretend we?re working on an average project. The table we?ll be pulling from has several thousand entries and we want to pull back something small like 3-5 random records. The most common solution offered based on the research I performed works perfectly for this situation.

records_desired = 3
count = [OurObject.count, 1].max
offsets = records_desired.times.inject([]) do |offsets|
  offsets << rand(count)
end
while count > offsets.uniq!.size && offsets.size < records_desired do
  offsets << rand(count)
end
offsets.collect {|offset| OurObject.offset(offset).first}

Analyzing this approach, we?re looking at minimal processing time and a total of four queries. One to determine the total count and the rest to fetch each of our three objects individually. Seems perfectly reasonable.

What happens if our client needs 100 random records at a time? The processing is still probably within tolerances, but 101 queries? I say no unless our table is Dalmations! Let?s see if we can tweak things to be more large-set friendly.

records_desired = 100
count = [OurObject.count - records_desired, 1].max
offset = rand(count)
OurObject.limit(records_desired).offset(offset)

How?s this look? Very minimal processing and only 2 queries. Fantastic! But is this result going to appear random to an observer? I think it?s highly possible that you could end up with runs of related looking objects (created at similar times or all updated recently). When people say they want random, they often really mean they want unrelated. Is this solution close enough for most clients? I would say it probably is. But I can imagine the possibility that for some it might not be. Is there something else we can tweak to get a more desirable sampling without blowing processing time sky-high? After a little thought, this is what I came up with.

records_desired = 100
count = records_desired * 3
offset = rand([OurObject.count - count, 1].max)
set = OurObject.limit(count).offset(offset).pluck(:id)
OurObject.find(ids.sample(records_desired))

While this approach may not truly provide more random results from a mathematical perspective, by assembling a larger subset and pulling randomly from inside it, I think you may be able to more closely achieve the feel of what people expect from randomness if the previous method seemed to return too many similar records for your needs.


Comments

published by noreply@blogger.com (Greg Sabino Mullane) on 2016-01-28 17:49:00 in the "database" category

The recent release of Postgres 9.5 has many people excited about the big new features such as UPSERT (docs) and row-level security (docs). Today I would like to celebrate three of the smaller features that I love about this release.

Before jumping into my list, I'd like to thank everyone who contributes to Postgres. I did some quick analysis and found that 85 people, from Adrien to Zeus, have helped version 9.5 of Postgres, at least according to the git logs. Of course, that number is actually higher, as it doesn't take into account people helping out on the #postgresql channel, running buildfarm animals, doing packaging work, keeping the infrastructure running, etc. Thanks to you all!

Feature: REINDEX VERBOSE

The first feature is one I've been wishing for a long time - a verbose form of the REINDEX command. Thanks to Sawada Masahiko for adding this. Similar to VACUUM, REINDEX gets kicked off and then gives no progress or information until it finishes. While VACUUM has long had the VERBOSE option to get around this, REINDEX gives you no clue to which index it was working on, or how much work each index took to rebuild. Here is a normal reindex, along with another 9.5 feature, the ability to reindex an entire schema:

greg=# reindex schema public;
## What seems like five long minutes later...
REINDEX

The new syntax uses parenthesis to support VERBOSE and any other future options. If you are familiar with EXPLAIN's newer options, you may see a similarity. More on the syntax in a bit. Here is the much improved version in action:

greg=# reindex (verbose) schema public;
INFO:  index "foobar_pkey" was reindexed
DETAIL:  CPU 11.00s/0.05u sec elapsed 19.38 sec.
INFO:  index "foobar_location" was reindexed
DETAIL:  CPU 5.21s/0.05u sec elapsed 18.27 sec.
INFO:  index "location_position" was reindexed
DETAIL:  CPU 9.10s/0.05u sec elapsed 19.70 sec.
INFO:  table "public.foobar" was reindexed
INFO:  index "foobaz_pkey" was reindexed
DETAIL:  CPU 7.04s/0.05u sec elapsed 19.61 sec.
INFO:  index "shoe_size" was reindexed
DETAIL:  CPU 12.26s/0.05u sec elapsed 19.33 sec.
INFO:  table "public.foobaz" was reindexed
REINDEX

Why not REINDEX VERBOSE TABLE foobar? Or even REINDEX TABLE foobar WITH VERBOSE? There was a good discussion of this on pgsql-hackers when this feature was being developed, but the short answer is that parenthesis are the correct way to do things moving forward. Robert Haas summed it up well:

The unparenthesized VACUUM syntax was added back before we realized that that kind of syntax is a terrible idea. It requires every option to be a keyword, and those keywords have to be in a fixed order. I believe the intention is to keep the old VACUUM syntax around for backward-compatibility, but not to extend it. Same for EXPLAIN and COPY.

The psql help option should show the new syntax:

greg=# h REINDEX
Command:     REINDEX
Description: rebuild indexes
Syntax:
REINDEX [ ( { VERBOSE } [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name

Feature: pg_ctl defaults to "fast" mode

The second feature in Postgres 9.5 I am happy about is the change in niceness of pg_ctl from "smart" mode to "fast" mode. The help of pg_ctl explains the different modes fairly well:

pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage:
  pg_ctl stop    [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
...
Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown
  immediate   quit without complete shutdown; will lead to recovery on restart

In the past, the default was 'smart'. Which often means your friendly neighborhood DBA would type "pg_ctl restart -D data", then watch the progress dots slowly marching across the screen, until they remembered that the default mode of "smart" is kind of dumb - as long as there is one connected client, the restart will not happen. Thus, the DBA had to cancel the command, and rerun it as "pg_ctl restart -D data -m fast". Then they would vow to remember to add the -m switch in next time. And promptly forget to the next time they did a shutdown or restart. :) Now pg_ctl has a much better default. Thanks, Bruce Momjian!

Feature: new 'cluster_name' option

When you run a lot of different Postgres clusters on your server, as I tend to do, it can be hard to tell them apart as the version and port are not reported in the ps output. I sometimes have nearly a dozen different clusters running, due to testing different versions and different applications. Similar in spirit to the application_name option, the new cluster_name option solves the problem neatly by allowing a custom string to be put in to the process title. Thanks to Thomas Munro for inventing this. So instead of this:

greg      7780     1  0 Mar01 pts/0    00:00:03 /home/greg/pg/9.5/bin/postgres -D data
greg      7787  7780  0 Mar01 ?        00:00:00 postgres: logger process   
greg      7789  7780  0 Mar01 ?        00:00:00 postgres: checkpointer process   
greg      7790  7780  0 Mar01 ?        00:00:09 postgres: writer process   
greg      7791  7780  0 Mar01 ?        00:00:06 postgres: wal writer process   
greg      7792  7780  0 Mar01 ?        00:00:05 postgres: autovacuum launcher process   
greg      7793  7780  0 Mar01 ?        00:00:11 postgres: stats collector process  
greg      6773     1  0 Mar01 pts/0    00:00:02 /home/greg/pg/9.5/bin/postgres -D data2
greg      6780  6773  0 Mar01 ?        00:00:00 postgres: logger process   
greg      6782  6773  0 Mar01 ?        00:00:00 postgres: checkpointer process   
greg      6783  6773  0 Mar01 ?        00:00:04 postgres: writer process   
greg      6784  6773  0 Mar01 ?        00:00:02 postgres: wal writer process   
greg      6785  6773  0 Mar01 ?        00:00:02 postgres: autovacuum launcher process   
greg      6786  6773  0 Mar01 ?        00:00:07 postgres: stats collector process

One can adjust the cluster_name inside each postgresql.conf (for example, to 'alpha' and 'bravo'), and get this:

greg      8267     1  0 Mar01 pts/0    00:00:03 /home/greg/pg/9.5/bin/postgres -D data
greg      8274  8267  0 Mar01 ?        00:00:00 postgres: alpha: logger process   
greg      8277  8267  0 Mar01 ?        00:00:00 postgres: alpha: checkpointer process   
greg      8278  8267  0 Mar01 ?        00:00:09 postgres: alpha: writer process   
greg      8279  8267  0 Mar01 ?        00:00:06 postgres: alpha: wal writer process   
greg      8280  8267  0 Mar01 ?        00:00:05 postgres: alpha: autovacuum launcher process   
greg      8281  8267  0 Mar01 ?        00:00:11 postgres: alpha: stats collector process  
greg      8583     1  0 Mar01 pts/0    00:00:02 /home/greg/pg/9.5/bin/postgres -D data2
greg      8590  8583  0 Mar01 ?        00:00:00 postgres: bravo: logger process   
greg      8592  8583  0 Mar01 ?        00:00:00 postgres: bravo: checkpointer process   
greg      8591  8583  0 Mar01 ?        00:00:04 postgres: bravo: writer process   
greg      8593  8583  0 Mar01 ?        00:00:02 postgres: bravo: wal writer process   
greg      8594  8583  0 Mar01 ?        00:00:02 postgres: bravo: autovacuum launcher process   
greg      8595  8583  0 Mar01 ?        00:00:07 postgres: bravo: stats collector process

There are a lot of other things added in Postgres 9.5. I recommend you visit this page for a complete list, and poke around on your own. A final shout out to all the people that are continually improving the tab-completion of psql. You rock.


Comments

published by noreply@blogger.com (David Christensen) on 2016-01-15 20:30:00 in the "database" category

With all the major changes and improvements to PostgreSQL's native replication system through the last few major releases, it's easy to forget that there can be benefits to having some of the tried and true functionalities from older PostgreSQL versions in place.

In particular, with the ease of setting up Hot Standby/Streaming Replication, it's easy to get replication going with almost no effort. Replication is great for redundancy, scaling, and backups, however it does not solve all potential data-loss problems; for best results when used in conjunction with Point-in-time Recovery (PITR) and the archiving features of PostgreSQL.

Background

We recently had a client experience a classic blunder with their database; mainly that of performing a manual UPDATE of the database without wrapping in a transaction block. The table in question was the main table in the application, and the client had done an unqualified UPDATE, unintentionally setting a specific field to a constant value instead of targetting the specific row they thought they were going for.

Fortunately, the client had backups. Unfortunately the backups themselves would not be enough; being a snapshot of the data earlier in the day, we would have lost all changes made throughout the day.

This resulted in a call to us to help out with the issue. We fortunately had information about precisely when the errant UPDATE took place, so we were able to use this information to help target a PITR-based restore.

The Approach

Since we did not want to lose other changes made in this database cluster either before or after this mistake, we came up with the following strategy which would let us keep the current state of the database but just recover the field in question:

  1. Create a parallel cluster for recovery.
  2. Load the WAL until just before the time of the event.
  3. Dump the table in question from the recovery cluster.
  4. Load the table in the main cluster with a different name.
  5. Use UPDATE FROM to update the field values for the table with their old values based on the table's Primary Key.

In practice, this worked out pretty well, though of course there were some issues that had to be worked around.

PostgreSQL's PITR relies on its WAL archiving mechanism combined with taking regular base backups of the data directory. As part of the archive setup, you choose the strategies (such as the frequency of the base backups) and ensure that you can recover individual WAL segment files when desired.

In order for the above strategy to work, you need hardware to run this on. The client had proposed their standby server which was definitely equipped to handle this and did not have much load. The client had initially suggested that we could break the replication, but we recommended against that, due to both having sufficient disk space and being able to avoid future work and risk by having to rebuild the replica after this stage.

We copied over the daily base backup into its own directory/mount point here, adjusted the recovery.conf file to point to the local WAL directory, and copied the necessary WAL files from the archive location to the pg_xlog directory of the new cluster. We also had to adjust a few parameters in the new cluster, most notably the "port" parameter to run the cluster on a different port. We also used the timestamp of the incident as a target for the recovery.conf's recovery_target_time setting. After starting up the new cluster and letting things process, we were able to dump the table in question and finish the recovery on the master.

Some issues did come up for us that we needed expert-level knowledge of the system, as well as having some good luck in the timing if the event. We had to locate several of the WAL files in the initial archive on the primary server due to some issues with the (inherited by us) configuration. Also due to the timing of the event and the amount of time it took to create the parallel cluster, we successfully were able to create the new instance before the next nightly base backup was run, which was fortunate, because it otherwise would have resulted in our inability to resolve this issue. (The client had things configured to keep only a single base backup around.)

Lessons Learned

With any issue, there is a takeaway, so what are those here?

  • Always use explicit transactions when manually modifying data, or modify your production environment's .psqlrc to addset AUTOCOMMIT off.
  • Not all data-loss situations can be fixed with replication alone?Point in Time Recovery is absolutely still relevant these days.
  • It helps to have a PostgreSQL expert on-hand day-or-night. End Point offers 24x7 PostgreSQL support, which you can engage by getting a hold of us here.

Comments

published by noreply@blogger.com (Greg Sabino Mullane) on 2015-12-31 21:39:00 in the "database" category

Way back in 2013, Postgres introduced a feature known as data checksums. When this is enabled, a small integer checksum is written to each "page" of data that Postgres stores on your hard drive. Upon reading that block, the checksum value is recomputed and compared to the stored one. This detects data corruption, which (without checksums) could be silently lurking in your database for a long time. We highly recommend to our Postgres clients to turn checksums on; hopefully this feature will be enabled by default in future versions of Postgres.

However, because TANSTAAFL (there ain't no such thing as a free lunch), enabling checksums does have a performance penalty. Basically, a little bit more CPU is needed to compute the checksums. Because the computation is fast, and very minimal compared to I/O considerations, the performance hit for typical databases is very small indeed, often less than 2%. Measuring the exact performance hit of checksums can be a surprisingly tricky problem.

There are many factors that influence how much slower things are when checksums are enabled, including:

  • How likely things are to be read from shared_buffers, which depends on how large shared_buffers is set, and how much of your active database fits inside of it
  • How fast your server is in general, and how well it (and your compiler) are able to optimize the checksum calculation
  • How many data pages you have (which can be influenced by your data types)
  • How often you are writing new pages (via COPY, INSERT, or UPDATE)
  • How often you are reading values (via SELECT)

Enough of the theory, let's see checksums in action. The goal is that even a single changed bit anywhere in your data will produce an error, thanks to the checksum. For this example, we will use a fresh 9.4 database, and set it up with checksums:

~$ cd ~/pg/9.4
~/pg/9.4$ bin/initdb --data-checksums lotus
The files belonging to this database system will be owned by user "greg".
...
Data page checksums are enabled.
...
~/pg/9.4$ echo port=5594 >> lotus/postgresql.conf
~/pg/9.4$ bin/pg_ctl start -D lotus -l lotus.log
server starting
~/pg/9.4$ bin/createdb -p 5594 testdb

For testing, we will use a table with a single char(2000) data type. This ensures that we have a relatively high number of pages compared to the number of rows (smaller data types means more rows shoved into each page, while higher types also mean less pages, as the rows are TOASTed out). The data type will be important for our performance tests later on, but for now, we just need a single row:

~/pg/9.4$ psql testdb -p 5594 -c "create table foobar as select 'abcd'::char(2000) as baz"
SELECT 1

Finally, we will modify the data page on disk using sed, then ask Postgres to display the data, which should cause the checksum to fail and send up an alarm. (Unlike my coworker Josh's checksum post, I will change the actual checksum and not the data, but the principle is the same).

~/pg/9.4$ export P=5594
## Find the actual on-disk file holding our table, and store it in $D
~/pg/9.4$ export D=`psql testdb -p$P -Atc "select setting || '/' || pg_relation_filepath('foobar') from pg_settings where name ~ 'data_directory'"`
~/pg/9.4$ echo $D
/home/greg/pg/9.4/lotus/base/16384/16385
## The checksum is stored at the front of the header: in this case it is: 41 47
~/pg/9.4$ hexdump -C $D | head -1
00000000  00 00 00 00 00 00 00 00  41 47 00 00 1c 00 10 18  |........AG......|

## Use sed to change the checksum in place, then double check the result
~/pg/9.4$ LC_ALL=C sed -r -i "s/(.{8})../1NI/" $D
~/pg/9.4$ hexdump -C $D | head -1
00000000  00 00 00 00 00 00 00 00  4E 49 00 00 1c 00 10 18  |........NI......|
~/pg/9.4$ psql testdb -p$P -tc 'select rtrim(baz) from foobar'
 abcd

Hmmm...why did this not work? Because of the big wrinkle to testing performance, shared buffers. This is a special shared memory segment used by Postgres to cache data pages. So when we asked Postgres for the value in the table, it pulled it from shared buffers, which does not get a checksum validation. Our changes are completely overwritten as the row leaves shared buffers and heads back to disk, generating a new checksum:

~/pg/9.4$ psql testdb -p$P -tc 'checkpoint'
CHECKPOINT
~/pg/9.4$ hexdump -C $D | head -1
00000000  00 00 00 00 80 17 c2 01  7f 19 00 00 1c 00 10 18  |................|

How can we trigger a checksum warning? We need to get that row out of shared buffers. The quickest way to do so in this test scenario is to restart the database, then make sure we do not even look at (e.g. SELECT) the table before we make our on-disk modification. Once that is done, the checksum will fail and we will, as expected, receive a checksum error:

~/pg/9.4$ bin/pg_ctl restart -D lotus -l lotus.log
waiting for server to shut down.... done
server stopped
server starting
~/pg/9.4$ LC_ALL=C sed -r -i "s/(.{8})../1NI/" $D
~/pg/9.4$ psql testdb -p$P -tc 'select rtrim(baz) from foobar'
WARNING:  page verification failed, calculated checksum 6527 but expected 18766
ERROR:  invalid page in block 0 of relation base/16384/16385

The more that shared buffers are used (and using them efficiently is a good general goal), the less checksumming is done, and the less the impact of checksums on database performance will be. Because we want to see the "worst-case" scenario when doing performance testing, let's create a second Postgres cluster, with a teeny-tiny shared buffers. This will increase the chances that any reads come not from shared buffers, but from the disk (or more likely the OS cache, but we shall gloss over that for now).

To perform some quick performance testing on writes, let's do a large insert, which will write many pages to disk. I originally used pg_bench for these tests, but found it was doing too much SQL under the hood and creating results that varied too much from run to run. So after creating a second cluster with checksums disabled, and after adjusting both with "shared_buffers = 128kB", I created a test script that inserted many rows into the char(2000) table above, which generated a new data page (and thus computed a checksum for the one cluster) once every four rows. I also did some heavy selects of the same table on both clusters.

Rather than boring you with large charts of numbers, I will give you the summary. For inserts, the average difference was 6%. For selects, that jumps to 19%. But before you panic, remember that this tests are with a purposefully crippled Postgres database, doing worst-case scenario runs. When shared_buffers was raised to a sane setting, the statistical difference between checksums and not-checksums disappeared.

In addition to this being an unrealistic worst-case scenario, I promise that you would be hard pressed to find a server to run Postgres on with a slower CPU than the laptop I ran these tests on. :) The actual calculation is pretty simple and uses a fast Fowler/Noll/Vo hash - see the src/include/storage/checksum_impl.h file. The calculation used is:

hash = (hash ^ value) * FNV_PRIME ^ ((hash ^ value) >> 17)

Can you handle the performance hit? Here's a little more incentive for you: if you are doing this as part of a major upgrade (a common time to do so, as part of a pg_dump oldversion | psql newversion process), then you are already getting performance boosts from the new version. Which can nicely balance out (or at least mitigate) your performance hit from enabling checksums! Look how much speedup you get doing basic inserts just leaving the 8.x series:

It is very hard to hazard any number for the impact of checksums, as it depends on so many factors, but for a rough ballpark, I would say a typical database might see a one or two percent difference. Higher if you are doing insane amounts of inserts and updates, and higher if your database doesn't fit at all into shared buffers. All in all, a worthy trade-off. If you want some precise performance impact figures, you will need to do A/B testing with your database and application.

To sum this page up (ha!), enable those checksums! It's worth the one-time cost of not being able to use pg_upgrade, and the ongoing cost of a little more CPU. Don't wait for your corruption to get so bad the system catalogs start getting confused - find out the moment a bit gets flipped.


Comments

published by noreply@blogger.com (Greg Sabino Mullane) on 2015-08-12 15:15:00 in the "database" category

While Bucardo is known for doing "multi-master" Postgres replication, it can do a lot more than simple "master to master" replication (better known as "source to source" replication). As people have been asking for simple Bucardo Bucardo 5 recipes based on pgbench, I decided to present a few here. Since Bucardo allows any number of sources and targets, I will demonstrate a source-source-source-target replication. Targets do not have to be Postgres, so let's also show that we can do source - MariaDB - SQLite replication. Because my own boxes are so customized, I find it easier and more honest when writing demos to start with a fresh system, which also allows you to follow along at home. For this example, I decided to fire up Amazon Web Services (AWS) again.

After logging in at https://aws.amazon.com, I visited the AWS Management Console, selected "EC2", clicked on "Launch Instance", and picked the Amazon Linux AMI (in this case, "Amazon Linux AMI 2015.03 (HVM), SSD Volume Type - ami-1ecae776"). Demos like this require very little resources, so choosing the smallest AMI (t2.micro) is more than sufficient. After waiting a couple of minutes for it to start up, I was able to SSH in and begin. The first order of business is always updating the box and installing some standard tools. After that I make sure we can install the most recent version of Postgres. I'll skip the initial steps and jump to the Major Problem I encountered:

$ sudo yum install postgresql94-plperl
Error: Package: postgresql94-plperl-9.4.4-1PGDG.rhel6.x86_64 (pgdg94)
           Requires: perl(:MODULE_COMPAT_5.10.1)
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

Well, that's not good (and the "You could try" are useless in this case). Although all the other Postgres packages installed without a problem (postgresql94, postgresql94-server, and postgresql94-libs), there is a major incompatibility preventing Pl/Perl from working. Basically, the rpm was compiled against Perl version 5.10, but Amazon Linux is using 5.16! There are many solutions to this problem, from using perlbrew, to downgrading the system Perl, to compiling Postgres manually. However, this is the Age of the Cloud, so a simpler solution is to ditch this AMI and pick a different one. I decided to try a RHEL (Red Hat Enterprise Linux) AMI. Again, I used a t2.micro instance and launched RHEL-7.1 (AMI ID RHEL-7.1_HVM_GA-20150225-x86_64-1-Hourly2-GP2). As always when starting up an instance, the first order of business when logging in is to update the box. Then I installed some important tools, and set about getting the latest and greatest version of Postgres up and running:

$ sudo yum update
$ sudo yum install emacs-nox mlocate git perl-DBD-Pg

Checking the available Postgres version reveals, as expected, that it is way too old:

$ sudo yum list postgresql*-server
Loaded plugins: amazon-id, rhui-lb
Available Packages
postgresql-server.x86_64        9.2.13-1.el7_1        rhui-REGION-rhel-server-release

Luckily, there is excellent support for Postgres packaging on most distros. The first step is to find a rpm to use to get the "pgdg" yum repository in place. Visit http://yum.postgresql.org/ and choose the latest version (as of this writing, 9.4). Then find your distro, and copy the link to the rpm. Going back to the AWS box, add it in like this:

$ sudo yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-redhat94-9.4-1.noarch.rpm

This installs a new entry in the /etc/yum.repos.d/ directory named pgdg-94-redhat.repo. However, we want to make sure that we never touch the old, stale versions of Postgres provided by the native yum repos. Keeping it from appearing is as simple as finding out which repo it is in, and adding an exclusion to that repository section by writing exclude=postgres*. Finally, we verify that all yum searches for Postgres return only the 9.4 items:

## We saw above that repo was "rhui-REGION-rhel-server-release"
## Thus, we know which file to edit
$ sudo emacs /etc/yum.repos.d/redhat-rhui.repo
## At the end of the [rhui-REGION-rhel-server-releases] section, add this line:
exclude=postgres*

## Now we can retry the exact same command as above
$ sudo yum list postgresql*-server
Loaded plugins: amazon-id, rhui-lb
Installed Packages
postgresql94-server.x86_64        9.4.4-1PGDG.rhel7        pgdg94

Now it is time to install Postgres 9.4. Bucardo currently needs to use Pl/Perl, so we will install that package (which will also install the core Postgres packages for us). As we are going to need the pgbench utility, we also need to install the postgresql-contrib package.

$ sudo yum install postgresql-plperl postgresql-contrib

This time it went fine - and Perl is at 5.16.3. The next step is to start Postgres up. Red Hat has gotten on the systemd bandwagon, for better or for worse, so gone is the familiar /etc/init.d/postgresql script. Instead, we need to use systemctl. We will find the exact service name, enable it, then try to start it up:

$ systemctl list-unit-files | grep postgres
## postgresql-9.4.service                      disabled

$ sudo systemctl enable postgresql-9.4
ln -s '/usr/lib/systemd/system/postgresql-9.4.service' '/etc/systemd/system/multi-user.target.wants/postgresql-9.4.service'
$ sudo systemctl start postgresql-9.4
Job for postgresql-9.4.service failed. See 'systemctl status postgresql-9.4.service' and 'journalctl -xn' for details.

As in the pre-systemd days, we need to run initdb before we can start Postgres. However, the simplicity of the init.d script is gone (e.g. "service postgresql initdb"). Poking in the systemd logs reveals the solution:

$ sudo systemctl -l status postgresql-9.4.service
postgresql-9.4.service - PostgreSQL 9.4 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; enabled)
   Active: failed (Result: exit-code) since Wed 2015-08-03 10:20:25 EDT; 1min 21s ago
  Process: 11916 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

Aug 03 10:20:25 ip-12.15.22.5.ec2.internal systemd[1]: Starting PostgreSQL 9.4 database server...
Aug 03 10:20:25 ip-12.15.22.5.ec2.internal postgresql94-check-db-dir[11916]: "/var/lib/pgsql/9.4/data/" is missing or empty.
Aug 03 10:20:25 ip-12.15.22.5.ec2.internal postgresql94-check-db-dir[11916]: Use "/usr/pgsql-9.4/bin/postgresql94-setup initdb" to initialize the database cluster.
Aug 03 10:20:25 ip-12.15.22.5.ec2.internal postgresql94-check-db-dir[11916]: See %{_pkgdocdir}/README.rpm-dist for more information.
Aug 03 10:20:25 ip-12.15.22.5.ec2.internal systemd[1]: postgresql-9.4.service: control process exited, code=exited status=1
Aug 03 10:20:25 ip-12.15.22.5.ec2.internal systemd[1]: Failed to start PostgreSQL 9.4 database server.
Aug 03 10:20:25 ip-12.15.22.5.ec2.internal systemd[1]: Unit postgresql-9.4.service entered failed state.

That's ugly output, but what can you do? Let's run initdb, start things up, and create a test database. As I really like to use Postgres with checksums, we can set the environment variables to pass that flag to initdb. After that completes, we can startup Postgres.

$ sudo PGSETUP_INITDB_OPTIONS=--data-checksums /usr/pgsql-9.4/bin/postgresql94-setup initdb
Initializing database ... OK

$ sudo systemctl start postgresql-9.4

Now that Postgres is up and running, it is time to create some test databases and populate them via the pgbench utility. First, a few things to make life easier. Because pgbench installs into /usr/pgsql-9.4/bin, which is certainly not in anyone's PATH, we will put it in a better location. We also want to loosen the Postgres login restrictions, and reload Postgres so it takes effect:

$ sudo ln -s /usr/pgsql-9.4/bin/pgbench /usr/local/bin/
$ sudo sh -c 'echo "local all all trust" > /var/lib/pgsql/9.4/data/pg_hba.conf'
$ sudo systemctl reload postgresql-9.4

Now we can create a test database, put the pgbench schema into it, and then give the pgbench_history table a primary key, which Bucardo needs in order to replicate it:

$ export PGUSER=postgres
$ createdb test1
$ pgbench -i --foreign-keys test1
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.10 s, remaining 0.00 s).
vacuum...
set primary keys...
set foreign keys...
done.
$ psql test1 -c 'alter table pgbench_history add column hid serial primary key'
ALTER TABLE

We want to create three copies of the database we just created, but without the data:

$ createdb test2
$ createdb test3
$ createdb test4
$ pg_dump --schema-only test1 | psql -q test2
$ pg_dump --schema-only test1 | psql -q test3
$ pg_dump --schema-only test1 | psql -q test4

Next up is installing Bucardo itself. We shall grab version 5.4.0 from the git repository, after cryptographically verifying the tag:

$ git clone http://bucardo.org/bucardo.git
Cloning into 'bucardo'...
$ cd bucardo
$ gpg --keyserver pgp.mit.edu --recv-keys 2529DF6AB8F79407E94445B4BC9B906714964AC8

$ git tag -v 5.4.0
object f1f8b0f6ed0be66252fa203c20a3f03a9382cd98
type commit
tag 5.4.0
tagger Greg Sabino Mullane  1438906359 -0400

Version 5.4.0, released August 6, 2015
gpg: Signature made Thu 06 Aug 2015 08:12:39 PM EDT using DSA key ID 14964AC8
gpg: please do a --check-trustdb
gpg: Good signature from "Greg Sabino Mullane "
gpg:                 aka "Greg Sabino Mullane (End Point Corporation) "
gpg: WARNING: This key is not certified with a trusted signature!
gpg:          There is no indication that the signature belongs to the owner.
Primary key fingerprint: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8

$ git checkout 5.4.0

Before Bucardo can be fully installed, some dependencies must be installed. What you need will depend on what your particular OS already has. For RHEL 7.1, this means a few things via yum, as well as some things via the cpan program:

$ sudo yum install perl-Pod-Parser perl-Sys-Syslog perl-Test-Simple perl-ExtUtils-MakeMaker cpan
$ echo y | cpan
$ (echo o conf make_install_make_command "'sudo make'"; echo o conf commit) | cpan
$ cpan boolean DBIx::Safe

## Now we can install the Bucardo program:
$ perl Makefile.PL
$ make
$ sudo make install

## Setup some directories we will need
$ sudo mkdir /var/run/bucardo /var/log/bucardo
$ sudo chown $USER /var/run/bucardo /var/log/bucardo

## Install the Bucardo database:
$ bucardo install ## hit "P" twice

Now that Bucardo is ready to go, let's teach it about our databases and tables, then setup a three-source, one-target database sync (aka multimaster or master-master-master-slave)

$ bucardo add db A,B,C,D dbname=test1,test2,test3,test4
Added databases "A","B","C","D"

$ bucardo add all tables relgroup=bench
Creating relgroup: bench
Added table public.pgbench_branches to relgroup bench
Added table public.pgbench_tellers to relgroup bench
Added table public.pgbench_accounts to relgroup bench
Added table public.pgbench_history to relgroup bench
New tables added: 4

$ bucardo add all sequences relgroup=bench
Added sequence public.pgbench_history_hid_seq to relgroup bench
New sequences added: 1

$ bucardo add sync btest relgroup=bench dbs=A:source,B:source,C:source,D:target
Added sync "btest"
Created a new dbgroup named "btest"

$ bucardo start
Checking for existing processes
Starting Bucardo

Time to test that it works. The initial database, "test1", should have many rows in the pgbench_accounts table, while the other databases should have none. Once we update some of the rows in the test1 database, it should replicate to all the others. Changes in test2 and test3 should go everywhere as well, because they are source databases. Changes made to the database test4 should stay in test4, as it is only a target.

$ psql test1 -xtc 'select count(*) from pgbench_accounts'
count | 100000

$ for i in {2,3,4}; do psql test$i -xtc 'select count(*) from pgbench_accounts'; done
count | 0
count | 0
count | 0

## We want to "touch" these four rows to make sure they replicate out:
$ psql test1 -c 'UPDATE pgbench_accounts set aid=aid where aid <= 4'
UPDATE 4

$ for i in {2,3,4}; do psql test$i -xtc 'select count(*) from pgbench_accounts'; done
count | 4
count | 4
count | 4

$ for i in {1,2,3,4}; do psql test$i -xtc "update pgbench_accounts set abalance=$i*100 where aid=$i"; done
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
$ psql test1 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |        0
$ psql test2 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |        0
$ psql test3 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |        0
$ psql test4 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |      400

What happens if we change aid '4' on one of the sources? The local changes to test4 will get overwritten:

$ psql test1 -c 'update pgbench_accounts set abalance=9999 where aid = 4'
UPDATE 1
$ psql test4 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |     9999

Let's create one more sync - this time, we want to replicate our Postgres data to a MariaDB and a SQLite database. (Bucardo can also do systems like Oracle, but getting it up and running is NOT an easy task for a quick demo like this!). The first step is to get both systems up and running, and provide them with a copy of the pgbench schema:

## The program 'sqlite3' is already installed, but we still need the Perl module:
$ sudo yum install perl-DBD-SQLite

## MariaDB takes a little more effort
$ sudo yum install mariadb-server ## this also (surprisingly!) installs DBD::MySQL!

$ systemctl list-unit-files | grep maria
mariadb.service                             disabled
$ sudo systemctl enable mariadb
ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'
$ sudo systemctl start mariadb

$ sudo mysql
mysql> create user 'ec2-user'@'localhost' identified by 'sixofone';
mysql> grant all on *.* TO 'ec2-user'@'localhost';
mysql> quit

## Store the MariaDB / MySQL password so we don't have to keep entering it:
$ cat > ~/.my.cnf 
[client]
password = sixofone

Now we can create the necessary tables for both. Note that SQLite does not allow you to change a table's structure once it has been created, so we cannot use the MySQL/Postgres way of using ALTER TABLE after the fact to add the primary keys. Knowing this, we can put everything into the CREATE TABLE statement. This schema will work on all of our systems:

CREATE TABLE pgbench_accounts (
    aid      integer NOT NULL PRIMARY KEY,
    bid      integer,
    abalance integer,
    filler   character(84)
);
CREATE TABLE pgbench_branches (
    bid      integer NOT NULL PRIMARY KEY,
    bbalance integer,
    filler   character(88)
);
CREATE TABLE pgbench_history (
    hid    integer NOT NULL PRIMARY KEY,
    tid    integer,
    bid    integer,
    aid    integer,
    delta  integer,
    mtime  datetime,
    filler character(22)
);
CREATE TABLE pgbench_tellers (
    tid      integer NOT NULL PRIMARY KEY,
    bid      integer,
    tbalance integer,
    filler   character(84)
);

$ mysql
msyql> create database pgb;
mysql> use pgb
pgb> ## add the tables here
pgb> quit

$ sqlite3 pgb.sqlite
sqlite> ## add the tables here
sqlite> .q

Teach Bucardo about these new databases, then add them to a new sync. As we do not want changes to get immediately replicated, we set this sync to "autokick off". This will ensure that the sync will only run when it is manually started via the "bucardo kick" command. Since database C is also part of another Bucardo sync and may get rows written to it that way, we need to set it as a "makedelta" database, which ensures that the replicated rows from the other sync are replicated onwards in our new sync.

## Teach Bucardo about the MariaDB database
$ bucardo add db M dbname=pgb type=mariadb user=ec2-user dbpass=fred
Added database "M"

## Teach Bucardo about the SQLite database
$ bucardo add db S dbname=pgb.sqlite type=sqlite
Added database "S"

## Create the new sync, replicating from C to our two non-Postgres databases:
$ bucardo add sync abc relgroup=bench dbs=C:source,M:target,S:target autokick=off
Added sync "abc"
Created a new dbgroup named "abc"

## Make sure any time we replicate to C, we create delta rows for the other syncs
$ bucardo update db C makedelta=on
Changed bucardo.db makedelta from off to on

$ bucardo restart
Creating /var/run/bucardo/fullstopbucardo ... Done
Checking for existing processes
Removing file "/var/run/bucardo/fullstopbucardo"
Starting Bucardo

For the final test, all changes to A, B, or C should end up on M and S!

$ for i in {1,2,3,4}; do psql test$i -xtc "update pgbench_accounts set abalance=$i*2222 where aid=$i"; done
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1

$ psql test4 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |     2222
   2 |     4444
   3 |     6666
   4 |     8888

$ sqlite3 pgb.sqlite 'select count(*) from pgbench_accounts'
0

$ mysql pgb -e 'select count(*) from pgbench_accounts'
+----------+
| count(*) |
+----------+
|        0 |
+----------+
$ bucardo kick abc 0
Kick abc: [1 s] DONE!

$ sqlite3 pgb.sqlite 'select count(*) from pgbench_accounts'
3

$ mysql pgb -e 'select count(*) from pgbench_accounts'
+----------+
| count(*) |
+----------+
|        3 |
+----------+

$ sqlite3 pgb.sqlite 'select aid,abalance from pgbench_accounts where aid <=4 order by aid'
1|2222
2|4444
3|6666

$ mysql pgb -e 'select aid,abalance from pgbench_accounts where aid <=4 order by aid'
+-----+----------+
| aid | abalance |
+-----+----------+
|   1 |     2222 |
|   2 |     4444 |
|   3 |     6666 |
+-----+----------+

Excellent. Everything is working as expected. Note that the changes from the test4 database were not replicated onwards, as test4 is not a source database. Feel free to ask any questions in the comments below, or better still, on the Bucardo mailing list.


Comments

published by noreply@blogger.com (Greg Sabino Mullane) on 2015-07-15 18:36:00 in the "database" category

Being able to disable Postgres triggers selectively can be an important skill when doing tasks like bulk updates, in which you only want a subset of the triggers on the table to be fired. Read below for the long explanation, but the TL;DR version of the best solution is to set a WHEN clause on the trigger you wish to skip, making it conditional on a variable such as session_replication_role, or application_name


CREATE TRIGGER mytrig AFTER INSERT ON foobar FOR EACH 
  ROW WHEN (current_setting('session_replication_role') <> 'local') EXECUTE PROCEDURE myfunc();
BEGIN;
SET LOCAL session_replication_role = 'local';
UPDATE foobar SET baz = 123;
COMMIT;

I decided to spin up a free Heroku "Hobby Dev" database to illustrate the solutions. Generating a test table was done by using the Pagila project, as it has tables which contain triggers. Heroku gives you a randomly generated user and database name. To install the Pagila schema, I did:

$ export H="postgres://vacnvzatmsnpre:2iCDp-46ldaFxgdIx8HWFeXHM@ec2-34-567-89.compute-1.amazonaws.com:5432/d5q5io7c3alx9t"
$ cd pagila-0.10.1
$ psql $H -q -f pagila-schema.sql
$ psql $H -q -f pagila-data.sql

Errors appeared on the import, but they can be safely ignored. One error was because the Heroku database does not have a user named "postgres", and the other error was due to the fact that the Heroku user is not a superuser. The data, however, was all intact. The sample data is actually quite funny, as the movie titles were semi auto-generated at some point. For example, seven random movie descriptions:

  • A Brilliant Panorama of a Madman And a Composer who must Succumb a Car in Ancient India
  • A Touching Documentary of a Madman And a Mad Scientist who must Outrace a Feminist in An Abandoned Mine Shaft
  • A Lackluster Reflection of a Eskimo And a Wretch who must Find a Fanny Pack in The Canadian Rockies
  • A Emotional Character Study of a Robot And a A Shark who must Defeat a Technical Writer in A Manhattan Penthouse
  • A Amazing Yarn of a Hunter And a Butler who must Defeat a Boy in A Jet Boat
  • A Beautiful Reflection of a Womanizer And a Sumo Wrestler who must Chase a Database Administrator in The Gulf of Mexico
  • A Awe-Inspiring Reflection of a Waitress And a Squirrel who must Kill a Mad Cow in A Jet Boat

The table we want to use for this post is named "film", and comes with two triggers on it, 'film_fulltext_trigger', and 'last_updated':

heroku=> d film
                            Table "public.film"
        Column        |            Type             |       Modifiers
----------------------+-----------------------------+---------------------------------
 film_id              | integer                     | not null default 
                                                      nextval('film_film_id_seq'::regclass)
 title                | character varying(255)      | not null
 description          | text                        | 
 release_year         | year                        | 
 language_id          | smallint                    | not null
 original_language_id | smallint                    | 
 rental_duration      | smallint                    | not null default 3
 rental_rate          | numeric(4,2)                | not null default 4.99
 length               | smallint                    | 
 replacement_cost     | numeric(5,2)                | not null default 19.99
 rating               | mpaa_rating                 | default 'G'::mpaa_rating
 last_update          | timestamp without time zone | not null default now()
...
Triggers:
    film_fulltext_trigger BEFORE INSERT OR UPDATE ON film FOR EACH ROW EXECUTE 
       PROCEDURE tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description')
    last_updated BEFORE UPDATE ON film FOR EACH ROW EXECUTE PROCEDURE last_updated()

The last_updated trigger calls the last_updated() function, which simply sets the last_update column to CURRENT_TIMESTAMP, which is often seen as its shorter-to-type form, now(). This is a handy metric to track, but there are times when you want to make changes and not update this field. A typical example is some sort of bulk change that does not warrant changing all the rows' last_update field. How to accomplish this? We need to ensure that the trigger does not fire when we do our UPDATE. The way many people are familiar with is to simply disable all triggers on the table. So you would do something like this:

BEGIN;
ALTER TABLE film DISABLE TRIGGER ALL;
UPDATE film SET rental_duration = 10;
ALTER TABLE film ENABLE TRIGGER ALL;
COMMIT;

When using Heroku, you are given a regular user, not a Postgres superuser, so the above will generate an error that looks like this:

ERROR:  permission denied: "RI_ConstraintTrigger_a_88776583" is a system trigger.

This is caused by the failure of a normal user to disable the internal triggers Postgres uses to maintain foreign key relationships between tables. So the better way is to simply disable the specific trigger like so:

BEGIN;
ALTER TABLE film DISABLE TRIGGER last_updated;
UPDATE film SET rental_duration = 10;
ALTER TABLE film ENABLE TRIGGER last_updated;
COMMIT;

This works on Heroku, but there are two major problems with the ALTER TABLE solution. First, the ALTER TABLE will take a very heavy lock on the entire table, meaning that nobody else will be able to access the table - even to read it! - until your transaction is complete (although Postgres 9.5 will reduce this lock!). The other problem with disabling triggers this way is that it is too easy to accidentally leave it in a disabled state (although the check_postgres program has a specific check for this!). Let's take a look at the lock, and double check that the trigger has been disabled as well:

heroku=> SELECT last_update FROM film WHERE film_id = 123;
        last_update         
----------------------------
 2015-06-21 16:38:00.891019
heroku=> BEGIN;
heroku=> ALTER TABLE film DISABLE TRIGGER last_updated;
heroku=> SELECT last_update FROM film WHERE film_id = 123;
heroku=> UPDATE film SET rental_duration = 10;
-- We need the subselect because we share with a gazillion other Heroku databases!
heroku=> select relation::regclass,mode,granted from pg_locks where database = 
heroku->   (select oid from pg_database where datname = current_database());
 relation |        mode         | granted 
----------+---------------------+---------
 pg_locks | AccessShareLock     | t
 film     | RowExclusiveLock    | t
 film     | AccessExclusiveLock | t  ## This is a very heavy lock!
## Version 9.5 and up will have a ShareRowExclusive lock only!
heroku=> ALTER TABLE film ENABLE TRIGGER last_updated;
heroku=> COMMIT;

-- This is the same value, because the trigger did not fire when we updated
heroku=> select last_update FROM film WHERE film_id = 123;
        last_update         
----------------------------
 2015-06-21 16:38:00.891019

What we really want is to use the powerful session_replication_role parameter to safely disable the triggers. The problem is that the canonical way to disable triggers, by setting session_replication_role to 'replica', will disable ALL triggers and rules, for ALL tables. This is not wanted. In our example, we want to stop the last_updated trigger from firing, but also want all the other user triggers to fire, as well as the hidden system triggers that are enforcing foreign key referential integrity.

You can set session_replication_role to one of three values: origin (the default), local, and replica. Setting it to "replica" is commonly used in replication systems such as Bucardo and Slony to prevent all rules and triggers from firing. It can also be used for careful bulk loading. Only triggers explicitly set as "replica triggers" will fire when the session_replication_role is set to 'replica'. The local setting is a little harder to understand, as it does not have a direct mapping to a trigger state, as 'origin' and 'replica' do. Instead, it can be thought of as an alias to 'origin' - same functionality, but with a different name. What use is that? Well, you can check the value of session_replication_role and do things differently depending on whether it is 'origin' or 'local'. Thus, it is possible to teach a trigger that it should not fire when session_replication_role is set to 'local' (or to fire only when it is set to 'local').

Thus, our previous problem of preventing the last_updated trigger from firing can be solved by careful use of the session_replication_role. We want the trigger to NOT fire when session_replication_role is set to 'local'. This can be accomplished in two ways: modification of the trigger, or modification of the underlying function. Each has its strengths and weaknesses. Note that session_replication_role can only be set by a superuser, which means I'll be switching from Heroku (which only allows connecting as a non-superuser) to a local Pagila database.

For the modify-the-function route, add a quick block at the top to short-circuit the trigger if the session_replication_role (srr) is set to 'local'. An advantage to this method is that all triggers that invoke this function will be affected. In the pagila database, there are 14 tables that have a trigger that calls the last_updated function. Another advantage is that the exception to the function firing is clearly visible in the functions definition itself, and thus easy to spot when you examine the function. Here is how you would modify the last_updated function to only fire when in 'local' srr mode:

CREATE OR REPLACE FUNCTION public.last_updated()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $bc$
BEGIN
  IF current_setting('session_replication_role') = 'local' THEN
    RETURN NEW;
  END IF;

  NEW.last_update = CURRENT_TIMESTAMP;
  RETURN NEW;
END
$bc$;

To invoke it, we change session_replication_role (temporarily!) to 'local', then make our changes. Observe how the value of last_update does not change when we are in 'local' mode:

pagila=# show session_replication_role tg
 origin

pagila=# begin;
BEGIN
pagila=# select last_update from film where film_id = 203;
 2015-06-21 16:38:00.711411

pagila=# update film set rental_duration = 10 WHERE film_id = 203;
UPDATE 1
pagila=# select last_update from film where film_id = 203;
 2015-06-21 16:38:03.543831
pagila=# commit;
COMMIT

pagila=# begin;
BEGIN
pagila=# set LOCAL session_replication_role = 'local';
SET
pagila=# select last_update from film where film_id = 203;
 2015-06-21 16:38:03.543831
pagila=# update film set rental_duration = 10 WHERE film_id = 203;
UPDATE 1
pagila=# select last_update from film where film_id = 203;
 2015-06-21 16:38:03.543831
pagila=# commit;
COMMIT

pagila=# show session_replication_role;
 origin

The second method for skipping a trigger by using session_replication_role is to modify the trigger definition itself, rather than changing the function. This has the advantage of not having to touch the function at all, and also allows you to see that the trigger has been modified when doing a d of the table. Using ALTER TRIGGER only allows a rename, so we will need to drop and recreate the trigger. By adding a WHEN clause to the trigger, we can ensure that it does NOT fire when session_replication_role is set to 'local'. The SQL looks like this:

pagila=# begin;
BEGIN
pagila=# drop trigger last_updated ON film;
DROP TRIGGER
pagila=# create trigger last_updated before update on film for each row 
pagila-#   when (current_setting('session_replication_role') <> 'local') execute procedure last_updated();
CREATE TRIGGER
pagila=# commit;
COMMIT

Voila! As before, we can test it out by setting session_replication_role to 'local' and confirming that the function does not modify the last_update column. Before doing that, let's also change the function back to its original form, to keep things honest:

-- Restore the original version, with no session_replication_role logic:
pagila=# CREATE OR REPLACE FUNCTION public.last_updated() RETURNS TRIGGER LANGUAGE plpgsql
AS $bc$ BEGIN NEW.last_update = CURRENT_TIMESTAMP; RETURN NEW; END $bc$;
CREATE FUNCTION

-- Normal update will change the last_update column:
pagila=# select last_update from film where film_id = 203;
        last_update         
----------------------------
 2015-06-21 16:38:00.121011

pagila=# update film set rental_duration = 10 WHERE film_id = 203;
UPDATE 1
pagila=# select last_update from film where film_id = 203;
        last_update         
----------------------------
 2015-06-21 16:38:03.011004

pagila=# begin;
pagila=# set LOCAL session_replication_role = 'local';
SET
pagila=# update film set rental_duration = 10 WHERE film_id = 203;
UPDATE 1
pagila=# select last_update from film where film_id = 203;
        last_update         
----------------------------
 2015-06-21 16:38:03.011004

-- Show that we are not holding a heavy lock:
pagila=# select relation::regclass,mode,granted from pg_locks where relation::regclass::text = 'film';
 relation |       mode       | granted 
----------+------------------+---------
 film     | AccessShareLock  | t
 film     | RowExclusiveLock | t

pagila=# commit;
COMMIT

Those are the three main ways to selectively disable a trigger on a table: using ALTER TABLE to completely disable it (and invoking a heavy lock), having the function check session_replication_role (affects all triggers using it, requires superuser), and having the trigger use a WHEN clause (requires superuser). Sharp readers may note that being a superuser is not really required, as something other than session_replication_role could be used. Thus, a solution is to use a parameter that can be changed by anyone, that will not affect anything else, and can be set to a unique value. Here is one such solution, using the handy "application_name" parameter. We will return to the Heroku database for this one:

heroku=> drop trigger last_updated on film;
heroku=> create trigger last_updated before update on film for each row 
  when (current_setting('application_name') <> 'skiptrig') execute procedure last_updated();

heroku=> select last_update from film where film_id = 111;
 2015-06-21 16:38:00.365103
heroku=> update film set rental_duration = 10 WHERE film_id = 111;
UPDATE 1
heroku=> select last_update from film where film_id = 111;
 2015-06-21 16:38:03.101115

heroku=> begin;
BEGIN
heroku=> set LOCAL application_name = 'skiptrig';
SET
heroku=> update film set rental_duration = 10 WHERE film_id = 111;
UPDATE 1
heroku=> select last_update from film where film_id = 111;
 2015-06-21 16:38:03.101115

-- Show that we are not holding a heavy lock:
heroku=> select relation::regclass,mode,granted from pg_locks where database = 
heroku->   (select oid from pg_database where datname = current_database());
 relation |       mode       | granted 
----------+------------------+---------
 film     | AccessShareLock  | t
 film     | RowExclusiveLock | t

heroku=> commit;
COMMIT

So there you have it - four solutions to the problem of skipping a single trigger. Which to use depends on your circumstances. I prefer the WHEN + session_replication_role option, as it forces you to be a superuser, and is very visible when looking at the trigger via d.


Comments

published by noreply@blogger.com (Marina Lohova) on 2015-06-17 11:00:00 in the "database" category

If you need to dump the production database locally Heroku has a nice set of tools to make this as smooth as humanly possible. In short, remember these two magic words: pg:pull and pg:push. This article details the process https://devcenter.heroku.com/articles/heroku-postgresql#pg-push-and-pg-pull

However, when I first tried it I had to resolved few issues.

My first problem was:

pg:pull not found

To fix this:

1. Uninstall the 'heroku' gem with

gem uninstall heroku (Select 'All Versions')

2. Find your Ruby 'bin' path by running

gem env
(it's under 'EXECUTABLE DIRECTORY:')

3. Cd to the 'bin' folder.

4. Remove the Heroku executable with

rm heroku

5. Restart your shell (close Terminal tab and re-open)

6. Type

heroku version
you should now see something like:
heroku-toolbelt/2.33.1 (x86_64-darwin10.8.0) ruby/1.9.3


Now you can proceed with the transfer:

1. Type

heroku config --app production-app

Note the DATABASE_URL, for example let's imagine that the production database url is HEROKU_POSTGRESQL_KANYE_URL, and the staging database url is HEROKU_POSTGRESQL_NORTH

2. Run

heroku pg:pull HEROKU_POSTGRESQL_KANYE rtwtransferdb --app production-app
heroku config --app staging-app
heroku pg:push rtwtransferdb HEROKU_POSTGRESQL_NORTH --app rtwtest


This is when I hit the second problem:

database is not empty

I fixed it by doing:

heroku pg:reset HEROKU_POSTGRESQL_NORTH

Happy database dumping!


Comments

published by noreply@blogger.com (Greg Sabino Mullane) on 2015-05-18 12:00:00 in the "database" category

Determining if your current database connection is using PgBouncer, or going directly to Postgres itself, can be challenging, as PgBouncer is a very low-level, transparent interface. It is possible, and here are some detection methods you can use.

This was inspired by someone asking on the Perl DBD IRC channel if it was possible to easily tell if your current database handle (usually "$dbh") is connected to PgBouncer or not. Since I've seen this question asked in other venues, I decided to take a crack at it.

There are actually two questions to be answered: (1) are we connected to PgBouncer, and if so, (2) what pool_mode is being run? The quickest and easiest way I found to answer the first question is to try and connect to a non-existent database. Normally, this is a FATAL message, as seen here:

$ psql testdb -p 5432
testdb=# c ghostdb
FATAL:  database "ghostdb" does not exist
Previous connection kept
testdb=# 

However, a slightly different ERROR message is returned if the same thing is attempted while connected to PgBouncer:

$ psql testdb -p 6432
testdb=# c ghostdb
ERROR:  No such database: ghostdb
Previous connection kept
testdb=# 

Thus, an ERROR will always indicate that you are connected to PgBouncer and not directly to Postgres, which will always issue a FATAL.

In the future, there will be an even simpler method. As of this writing, pgBouncer 1.6 has not been released, but it will have the ability to customize the application_name. This is a configurable session-level variable that is fairly new in Postgres. Andrew Dunstan wrote a patch which enables adding this to your pgbouncer.ini file:

application_name_add_host = 1

This will make PgBouncer modify the application_name to append some information to it such as the remote host, the remote port, and the local port. This is a feature many PgBouncer users will appreciate, as it offers an escape from the black hole of connection information that PgBouncer suffers from. Here is what it looks like on both a normal Postgres connection, and a PgBouncer connection. As you can see, this is an easier check than the "invalid database connection" check above:

## Postgres:
$ psql testdb -p 5432 -c 'show application_name'
 application_name 
------------------
 psql

## PgBouncer:
$ psql testdb -p 6432 -c 'show application_name'
        application_name        
--------------------------------
 psql - unix(7882@gtsm.com):6432

## DBD::Pg connections to PgBouncer get a very similar change:
$ perl testme.tmp.pl --port 6432
app - unix(6772@gtsm.com):6432

Now we have answered question of "are we connected to PgBouncer or not?". The next question is which pool mode we are in. There are three pool modes you can set for PgBouncer, which controls when your particular connection is returned to "the pool". For "session" mode, you keep the same Postgres backend the entire time you are connected. For "transaction", you keep the same Postgres backend until the end of a transaction. For "statement", you may get a new Postgres backend after each statement.

First, we can check if we are connected to PgBouncer in a statement level pool mode by taking advantage of the fact that multi-statement transactions are prohibited. PgBouncer enforces this by intercepting any attempts to enter a transaction (e.g. by issuing a BEGIN command). A very PgBouncer specific error about "Long transactions not allowed" is issued back to the client like so:

$ psql testdb -p 6432
testdb=# begin;
ERROR:  Long transactions not allowed

So, that takes care of detecting a pool_mode set to 'statement'. The other two modes, transaction and session, will *not* give the same error. Thus, seeing that error indicates you are using a statement-level PgBouncer connection.

The next pool mode is "transaction", which means that the server connection if released back to the pool at the end of a transaction. To figure out if we are in this mode, we take advantage of the fact that PgBouncer can be set to clean up the connection at the end of each transaction by issuing a specific command. By default, the command set by server_reset_query is DISCARD ALL, which invalidates any prepared statements, temporary tables, and other transation-spanning, session-level items. Thus, our test will see if these session-level artifacts get discarded or not:

## Direct Postgres:
$ psql testdb -p 5432
testdb=# prepare abc(int) as select $1::text;
PREPARE
testdb=# execute abc(1);
 text
------
 1

## PgBouncer:
$ psql testdb -p 6432
testdb=# prepare abc(int) as select $1::text;
PREPARE
testdb=# execute abc(1);
ERROR:  prepared statement "abc" does not exist

Keep in mind that there are no true "transactionless" commands in Postgres. Even though we did not use a BEGIN in the psql prompt above, each command is treated as its own mini-transaction. In the case of the PgBouncer connection, the prepare is immediately followed with a DISCARD ALL, which means that our prepared statement no longer exists. Hence, we have determined that we are using a transaction-level PgBouncer connection.

Unfortunately, not getting an error does not necessarily mean your PgBouncer is NOT in transaction mode! It could be that server_reset_query is empty, meaning that temporary artifacts are not discarded at the end of the transaction. In such a case, we can take advantage of the fact that PgBouncer will allow other clients to share in our current connection, and thus be able to see the temporary items. If we create a temporary table in one pgbouncer connection, then connect again as a new client, the temporary table will only show up if we are sharing sessions but not transactions. Easier shown than explained, I suspect:

## Regular Postgres gets a fresh session:
$ psql test1 -p 5432
test1=# create temp table abc(a int);
CREATE TABLE
test1=# select * from abc;
(No rows)
test1=# ^Z ## (we suspend with CTRL-Z)
[2]+  Stopped                 psql test1 -p 5432

$ psql test1 -p 5432
test1=# select * from abc;
ERROR:  relation "abc" does not exist

## PgBouncer will re-use the same session:
$ psql test1 -p 6432
test1=# create temp table abc(a int);
CREATE TABLE
test1=# select * from abc;
(No rows)
test1=# ^Z
[2]+  Stopped                 psql test1 -p 6432

$ psql test1 -p 6432
test1=# select * from abc;
(No rows)

The final PgBouncer pool mode is "session", and basically means the only advantage over a normal Postgres connection is the overhead to start up and connect to a new Postgres backend. Thus, the PgBouncer connections are only returned to the pool upon disconnection. The only way to tell if you are in this mode is by determining that you are *not* in the other two modes. :)

So, although PgBouncer is extremely transparent, there are some tricks to determine if you are connected to it, and at what pool_mode. If you can think of other (SQL-level!) ways to check, please let me know in the comments section.


Comments

published by noreply@blogger.com (Greg Sabino Mullane) on 2015-03-11 11:00:00 in the "database" category

The inability to easily search the Postgres release notes has been a long-standing annoyance of mine, and a recent thread on the pgsql-general mailing list showed that others share the same frustration. One common example when a new client comes to End Point with a mysterious Postgres problem. Since it is rare that a client is running the latest Postgres revision (sad but true), the first order of business is to walk through all the revisions to see if a simple Postgres update will cure the problem. Currently, the release notes are arranged on the postgresql.org web site as a series of individual HTML pages, one per version. Reading through them can be very painful - especially if you are trying to search for a specific item. I whipped up a Perl script to gather all of the information, reformat it, clean it up, and summarize everything on one giant HTML page. This is the result: https://bucardo.org/postgres_all_versions.html

Please feel free to use this page however you like. It will be updated as new versions are released. You may notice there are some differences from the original separate pages:

  • All 270 versions are now on a single page. Create a local greppable version with:
    links -dump https://bucardo.org/postgres_all_versions.html > postgres_all_versions.txt
  • All version numbers are written clearly. The confusing "E.x.y" notation was stripped out
  • A table of contents at the top allows for jumping to each version (which has the release date next to it).
  • Every bulleted feature has the version number written right before it, so you never have to scroll up or down to see what version you are currently reading.
  • If a feature was applied to more than one version, all the versions are listed (the current version always appears first).
  • All CVE references are hyperlinks now.
  • All "mailtos" were removed, and other minor cleanups.
  • Replaced single-word names with the full names (e.g. "Massimo Dal Zotto" instead of "Massimo") (see below)

Here's a screenshot showing the bottom of the table of contents, and some of the items for Postgres 9.4:

The name replacements took the most time, as some required a good bit of detective work. Most were unambiguous: "Tom" became "Tom Lane", "Bruce" became "Bruce Momjian", and so on. For the final document, 3781 name replacements were performed! Some of the trickier ones were "Greg" - both myself ("Greg Sabino Mullane") and "Greg Stark" had single-name entries. Similar problems popped up with "Ryan", and with "Peter" *not* being the familiar Peter Eisentraut (but Peter T. Mount) threw me off for a second. The only one I was never able to figure out was "Clark", who is attributed (via Bruce) with "Fix tutorial code" in version 6.5. Pointers or corrections welcome.

Hopefully this page will be of use to others. It's a very large page, but not remarkably wasteful of space, like many HTML pages these days. Perhaps some of the changes will make their way to the official docs over time.


Comments

published by noreply@blogger.com (Jeff Boes) on 2015-02-25 15:00:00 in the "database" category

SQL queries can get complex in a big hurry. If you are querying multiple tables, and in particular if your query involves operations like UNION and INTERSECT, then you can find yourself in a big, messy pile of SQL. Worse, that big pile will often run slowly, sometimes to the point where a web application times out!

I won't inflict a real-world example on you; that would be cruel. So let's look at a "toy" problem, keeping in mind that this won't illustrate any time-savings, just the technique involved.

Here's the original SQL:

SELECT p.* FROM products p
JOIN (SELECT * FROM inventory WHERE /* complex clause here */) i USING (sku)
UNION ALL
SELECT p.* FROM clearance_products p
JOIN (SELECT * FROM inventory WHERE /* complex clause here */) i USING (sku)

Bonus hint: using "UNION ALL" instead of just "UNION" will allow the query processor to skip an unnecessary step here. "UNION ALL" says you know the rows on either side of the clause are unique. "UNION" means the results will be post-processed to remove duplicates. This might save you more than a smidgen of time, depending on how large the two sub-queries get.

Now, many times the query optimizer will just do the right thing here. But sometimes (cough, cough-MySQL), your database isn't quite up to the task. So you have to shoulder the burden and help out. That's where we can apply a temporary table.

Temporary tables are created for the length of the database session; that's different than a transaction. For a web application, that's usually (not always) the length of the request (i.e., from the time your web application opens a database connection, until it explicitly closes it, or until it returns control to the web server, usually by passing it a completed page). For a script, it's a similar duration, e.g. until the script exits.

CREATE TEMPORARY TABLE cross_inventory AS
SELECT * FROM inventory WHERE /* complex clause here */;

CREATE INDEX cross_inv_sku ON cross_inventory(sku);
There's no significant difference for our purposes between a "permanent" and a "temporary" table. However, you do have to keep in mind that these tables are created without indexes, so if your goal is to improve the speed of queries involving the data here, adding an index after creating the table is usually desirable.

With all this in place, now we can:

SELECT p.* FROM products p
JOIN cross_inventory i USING (sku)
UNION
SELECT p.* FROM clearance_products p
JOIN cross_inventory i USING (sku)

Sometimes your temporary table will be built up not by a straightforward "CREATE ... AS SELECT ...", but by your application:

CREATE TEMPORARY TABLE tmp_inventory AS SELECT * FROM inventory WHERE false;
CREATE INDEX tmp_inv_sku ON tmp_inventory(sku);

And then within the application:

# Pseudocode
while (more_data) {
  row = build_inv_record(more_data);
  sql_do('INSERT INTO tmp_inventory VALUES (?,?,...)', row);
}

Here, we are creating an empty "inventory" table template as a temporary table ("SELECT * FROM inventory WHERE false"), then adding rows to it from the application, and finally running our query. Note that in a practical application of this, it's not likely to be a lot faster, because the individual INSERT statements will take time. But this approach may have some utility where the existing "inventory" table doesn't have the data we want to JOIN against, or has the data, but not in a way we can easily filter.

I've used temporary tables (in a MySQL/Interchange/Perl environment) to speed up a query by a factor of two or more. It's usually in those cases where you have a complex JOIN that appears in two or more parts of the query (again, usually a UNION). I've even had big-win situations where the same temporary table was used in two different queries during the same session.

A similar approach is the Common Table Expression (CTE) found in PostgreSQL starting with version 8.4. This allows you to identify the rows you would be pouring into your temporary table as a named result-set, then reference it in your query. Our "toy" example would become:

WITH cross_inventory AS
(SELECT * FROM inventory WHERE /* complex clause here */)
SELECT p.* FROM products p
JOIN cross_inventory i USING (sku)
UNION
SELECT p.* FROM clearance_products p
JOIN cross_inventory i USING (sku)

I've not had an opportunity to use CTEs yet, and of course they aren't available in MySQL, so the temporary-table technique will still have a lot of value for me in the foreseeable future.


Comments