Writing Reports in LedgerSMB 1.4, part 3, Stored Procedure Conventions and Best Practices

The stored procedures/UDFs form the heart of the LedgerSMB reports. Everything else basically is there to handle input to the stored procedure and output from it.  It's for this reason that the stored procedures are covered before the Perl modules.

The stored procedures follow the conventions and design principles laid out in the very first post on this blog.  Arguments are prefixed with 'in_' and given consistent names from report criteria.  Anyone wanting to write reports should start with that post in terms of understanding where to start.

Additionally there are a number of standardized conventions for common elements.  The most important of these currently is that many dates require a date range, and the arguments here are standardized as in_date_from and in_date_to (corresponding to date_from and date_to on the application side).   The conventions are set up on the Perl side so these will be covered in the next section.

In general, there are a number of do's and dont's associated with these procedures.

1.  Do provide a default ordering that is useful.

2.  Don't provide windowing functions that depend on ordering that could be overridden.  Running totals should not be implemented here.  As a good general rule, if you use ROWS UNBOUNDED PROCEDING in one of these functions, that's a red flag.

3.  Do try to have one central query with, possibly, some small support logic.

4.   Do define your types so several different reports can share the same output type.

5.  Do write test scripts to test your reporting functions.  Use transactions that roll back.

7.  Do use plpgsql instead of sql if you have more than a few arguments.  named arguments are easier to read.  Once we can drop support for 9.2 and lower this will cease to be an issue though.

An example:

CREATE OR REPLACE FUNCTION report__gl
(in_reference text, in_accno text, in_category char(1),
in_source text, in_memo text,  in_description text, in_from_date date,
in_to_date date, in_approved bool, in_from_amount numeric, in_to_amount numeric,
in_business_units int[])
RETURNS SETOF gl_report_item AS
$$
DECLARE
         retval gl_report_item;
         t_balance numeric;
         t_chart_id int;
BEGIN

IF in_from_date IS NULL THEN
   t_balance := 0;
ELSIF in_accno IS NOT NULL THEN
   SELECT id INTO t_chart_id FROM account WHERE accno  = in_accno;
   t_balance := account__obtain_balance(in_from_date ,
                                       (select id from account
                                         where accno = in_accno));
ELSE
   t_balance := null;
END IF;

FOR retval IN
       WITH RECURSIVE bu_tree (id, path) AS (
            SELECT id, id::text AS path
              FROM business_unit
             WHERE parent_id is null
            UNION
            SELECT bu.id, bu_tree.path || ',' || bu.id
              FROM business_unit bu
              JOIN bu_tree ON bu_tree.id = bu.parent_id
            )
       SELECT g.id, g.type, g.invoice, g.reference, g.description, ac.transdate,
              ac.source, ac.amount, c.accno, c.gifi_accno,
              g.till, ac.cleared, ac.memo, c.description AS accname,
              ac.chart_id, ac.entry_id,
              sum(ac.amount) over (rows unbounded preceding) + t_balance
                as running_balance,
              compound_array(ARRAY[ARRAY[bac.class_id, bac.bu_id]])
         FROM (select id, 'gl' as type, false as invoice, reference,
                      description, approved,
                      null::text as till
                 FROM gl
               UNION
               SELECT ar.id, 'ar', invoice, invnumber, e.name, approved, till
                 FROM ar
                 JOIN entity_credit_account eca ON ar.entity_credit_account
                      = eca.id
                 JOIN entity e ON e.id = eca.entity_id
               UNION
               SELECT ap.id, 'ap', invoice, invnumber, e.name, approved,
                      null as till
                 FROM ap
                 JOIN entity_credit_account eca ON ap.entity_credit_account
                      = eca.id
                 JOIN entity e ON e.id = eca.entity_id) g
         JOIN acc_trans ac ON ac.trans_id = g.id
         JOIN account c ON ac.chart_id = c.id
    LEFT JOIN business_unit_ac bac ON ac.entry_id = bac.entry_id
    LEFT JOIN bu_tree ON bac.bu_id = bu_tree.id
        WHERE (g.reference ilike in_reference || '%' or in_reference is null)
              AND (c.accno = in_accno OR in_accno IS NULL)
              AND (ac.source ilike '%' || in_source || '%'
                   OR in_source is null)
              AND (ac.memo ilike '%' || in_memo || '%' OR in_memo is null)
             AND (in_description IS NULL OR
                  g.description
                  @@
                  plainto_tsquery(get_default_lang()::regconfig, in_description))
              AND (transdate BETWEEN in_from_date AND in_to_date
                   OR (transdate >= in_from_date AND  in_to_date IS NULL)
                   OR (transdate <= in_to_date AND in_from_date IS NULL)
                   OR (in_to_date IS NULL AND in_from_date IS NULL))
              AND (in_approved is false OR (g.approved AND ac.approved))
              AND (in_from_amount IS NULL OR ac.amount >= in_from_amount)
              AND (in_to_amount IS NULL OR ac.amount <= in_to_amount)
              AND (in_category = c.category OR in_category IS NULL)
     GROUP BY g.id, g.type, g.invoice, g.reference, g.description, ac.transdate,
              ac.source, ac.amount, c.accno, c.gifi_accno,
              g.till, ac.cleared, ac.memo, c.description,
              ac.chart_id, ac.entry_id, ac.trans_id
       HAVING in_business_units is null or in_business_units
                <@ compound_array(string_to_array(bu_tree.path, ',')::int[])
     ORDER BY ac.transdate, ac.trans_id, c.accno
LOOP
   RETURN NEXT retval;
END LOOP;
END;
$$ language plpgsql;

The above is the function that provides the GL report and search.  It is a monster query and we could use RETURN QUERY but for older versions this seems more reliable.  Ideally, the inline view would be moved to a formal one, and a few other tweaks, but it works and works well enough and despite the length and the inline view, it is not hard to debug.

Others in series:

Part 1:  Overview

Part 2:  Filter Screens

Part 4:  Perl Modules

Part 5:  Conclusions

Interlude: A cool PL/PGSQL trick

I will be getting back to the report writing in LedgerSMB next or the week after.   I have an open source, PostgreSQL-based point of sale project I am hoping to make to a minimally useful version in the mean time.

In the mean time, here's a useful trick I discovered while trying to solve a problem.  The basic problem is one of Object-Relational design, in the sense that we may want to use types as classes and therefore return a tuple which is garanteed to be of the type defined by one table or view.

However, we may want to override some of values in this tuple with values from another table.

In older versions of PostgreSQL, I seem to recall it was possible to add additional columns onto the end of a result which would be silently ignored on return.  This doesn't work in current versions and isn't very safe anyway.  A better way is to work with composite tuples, where each member is a tuple you need for processing.

So for example, suppose we have a table which contains pricing information, and a second table which contains temporary pricing information for a given customer, and these are different table structures.  Suppose, for sake of argument we have tables like this:

CREATE TABLE goods (
   id int serial not null unique,
   sku text primary key,
   sell numeric not null,  -- sell price
   buy numeric not null  -- purchase price
);

CREATE TABLE pricematrix (
   customer_id int not null references customer(id),
   goods_id int not null references goods(id),
   valid_from date,
   valid_to date,
   sell numeric not null
);

CREATE VIEW pricematrix_today AS 
select * from pricematrix
WHERE (valid_from is null or valid_from <= 'today') and
              (valid_to is null or valid_to >= 'today');

Now, what I want to do is pull records from the goods table, but override the sell column with the one from the pricematrix if it exists, choosing the lowest available price.

A simple approach (this code is not tested unlike my actual production code based on the same principle):

CREATE OR REPLACE FUNCTION get_goods_by_sku(in_sku text, in_customer_id int)
RETURNS goods LANGUAGE PLPGSQL AS
$$
DECLARE t_rec record;
       retval goods;
      pmatrix record;
BEGIN
  
           SELECT goods, pm INTO t_rec
             FROM goods
        LEFT JOIN (SELECT *, 
                          rank() OVER 
                           (ORDER BY sell
                            PARTITION BY goods_id) as ranking
                     FROM pricematrix_today
                    WHERE customer_id = in_customer_id
                  ) pm ON (goods.id = pm.goods_id and ranking = 1)
            WHERE goods.sku = in_sku
  
       pmatrix := t_rec.pm;
       retval  := t_rec.goods;
       IF pmatrix.sell < retval.sell THEN
          retval.sell = pmatrix.sell;
       END IF;
       RETURN retval;
END;
$$;

By keeping our types isolated we can safely manipulate them inside the function before returning them and still be guaranteed a named type tuple on output.

Now obviously this is a contrived example and it is less likely we will usually be selecting from tables than views, but the same goes for views since we can define "type methods" on those as well.

Writing Reports for LedgerSMB 1.4 part 2: Input Filter Screens

The input filter screen system is designed to be fairly simple and yet easily extended.  This post will cover the overall design of the system, how to handle custom dropdowns, and general reusable components.

In general, common controls and the like have been centralized to ensure consistency of interfaces for both user and developer.  Not only does the user get to see common date selection routines, for example, but the developer can count on dates being available to the report.

Common Controls

 The report_base.html template contains a number of basic controls used for data entry. In general these are the best way to add certain kinds of common data to a report because they ensure a consistent API and user experience.  To use one of these controls, just PROCESS them  like:

<?lsmb PROCESS date_row_div ?>

We offer the following common controls for report filter screens in the report_base.html template:

• business_classes, a table row for selecting the business reporting class (project, department, and the like)
• business_classes_div, similar to business_classes above but in a div instead of a table row
• entity_class, a table row for selecting entity class (customer, vendor, etc)
• gifi_or_standard, a radio button set for selecting GIFI or standard reports (table row)
• igore_yearend, a table row for selecting whether yearends should be ignored or not (options: all, none, or last)
• date_row is a set of two table rows for selecting date, either by year, month, and period or by from and two dates identifying a range.
• date_row_div uses divs instead of table rows.
• employee_row is a row for selecting an employee for a report

Now, all of the controls come in a table row form and some come in a div form.  The reason for this is that migrated forms have been only minimally changed at present, and div-based forms have not been written yet.  In 1.5 or even possibly 1.4, it is likely that div-based versions of the table-based forms will be written.

General Functionality Available

For reports only requiring general functionality you can simply put the filter screen in UI/Reports/filters/ with a name based on the report name (myreport.html for example) and then call it with a URL like: http://myhost/ledgersmb/reports.pl&action=start_report&report_name=myreport&module=gl.

The module option determines which business unit classes are available for selection.

The following are added to the request hash before passing it on to the template:

• heading_list is a list of account headings
• account_list is a list of accounts
• all_years is a list of years for which there is financial activity (does not include uninvoiced orders)
• all_months is a list of localized months for the date selection dropdown
• batch_classes is a list of batch classes
• bu_classes is an array of business unit classes, for the appropriate common control
• b_units is an array of business units by class, for the common control
• country_list is a list of countries
• entity_classes is an array of entity classes for the common control
• employees is a list of employees for the common control

If you do not  require more option sets than these, you can use the basic method mentioned above.

Handling Custom Selection Boxes

There are of course times when one needs more option sets than these.  The best way to handle this is to add a new workflow script, which preprocesses the $request hash before passing this on to same function.

For example the relevant code for searching for orders is (abbreviated):

use LedgerSMB::Scripts::reports;
use LedgerSMB::Report::Orders;

sub get_criteria {
    my ($request) = @_;
    my $locale = $LedgerSMB::App_State::Locale;
    $request->{entity_class} = $request->{oe_class_id} % 2 + 1;
    $request->{report_name} = 'orders';
    $request->{open} = 1 if $request->{search_type} ne 'search';
    if ($request->{oe_class_id} == 1){
        if ($request->{search_type} eq 'search'){
            $request->{title} = $locale->text('Search Sales Orders');
        } elsif ($request->{search_type} eq 'generate'){
            $request->{title} =
                   $locale->text('Generate Purchase Orders from Sales Orders');
        } elsif ($request->{search_type} eq 'combine'){
            $request->{title} = $locale->text('Combine Sales Orders');
        } elsif ($request->{search_type} eq 'ship'){
            $request->{title} = $locale->text('Ship');
        }
    } elsif ($request->{oe_class_id} == 2){
        if ($request->{search_type} eq 'search'){
            $request->{title} = $locale->text('Search Purchase Orders');
        } elsif ($request->{search_type} eq 'combine'){
            $request->{title} = $locale->text('Combine Purchase Orders');
        } elsif ($request->{search_type} eq 'generate'){
            $request->{title} =
                   $locale->text('Generate Sales Orders from Purchase Orders');
        } elsif ($request->{search_type} eq 'ship'){
            $request->{title} = $locale->text('Receive');
        }
    } elsif ($request->{oe_class_id} == 3){
        if ($request->{search_type} eq 'search'){
            $request->{title} = $locale->text('Search Quotations');
        }
    } elsif ($request->{oe_class_id} == 4){
        if ($request->{search_type} eq 'search'){
            $request->{title} = $locale->text('Search Requests for Quotation');
        }
    }
    LedgerSMB::Scripts::reports::start_report($request);
}

The point is you can effectively put whatever logic you want before passing on to our general functionality.

You can use the templates in UI/Reports/filters as examples for coding the template itself.

Conclusions

The filter screen system is intended to make it relatively easy to create filters for report inputs.  In the future we will probably add more common controls and the like.  However this gives you the basic information needed to start writing reports.

Others in Series

1. Overview

3. Stored Procedure Best Practices

4.  (Mostly) Declarative Reporting Perl Modules

5.  Conclusions

Writing Reports in LedgerSMB 1.4 Part 1: Overview

One of the major features of the upcoming LedgerSMB 1.4 branch is the new reporting system.  This post gives a very basic breakdown of this new reporting engine.

The goal of the system was to make a framework for quickly and easily converting stored procedures into reports.  The basic components are:

1. The input criteria filter (usually required)
2. The main Perl module that defines the report
3. The stored procedure which does the main work
4. An optional output template.
5. A workflow script to coordinate.

Some of these may be very lightweight, if not almost optional.  The two however which are critical are the defining perl module, which defines a report in nearly declarative terms, and the stored procedure which does the
work.

For simple reports, it is enough to put a form template for input values in UI/Reports/filters/ and then access it as through a URL referencing the name of the form as the filter template, like this:

http://lsmb/reports.pl?action=begin_report&report_name=mycustomreport

This part is quite simple.

The next thing you need is the PostgreSQL user-defined-function.  This is usually a wrapper around an SQL query which provides a discoverable interface for input values to output values and returns tabular data.  This function must return all data related to the report, and needed for support functions like click-through links.

You also need a Perl module which defines the basic nature of the report, its name, layout and the like.  This is done mostly declaratively and will be covered in depth in a future article in this series.  The approach is sufficiently simple that basic reports can be written without any real knowledge of Perl (working from sample code only).

Finally there is a little bit of glue that is required for displaying the report in the workflow scripts.  This is simple enough that we can cover it here and be done with it.

Normally one will either have a new workflow script that managed this report or lump it together with a series of similar reports.  If the Perl module is in LedgerSMB/Reports/MyCustomReport.pm, then your workflow script would need to have, minimally the following few lines:

use LedgerSMB::Reports::MyCustomReport;

sub my_custom_report {
    my ($request)  = @_;
    LedgerSMB::Reports::MyCustomReport
        ->new(%$request)->render($request);
 }

That's all that is required to render the report from this framework, based on inputs from the filter screen.

This system shows the general power that putting such reports in the database and detecting their arguments can have.  The key skill in writing such reports is thus SQL, not Perl.

Forthcoming parts:

2.  Understanding Input Filter Screens for Reports

3.  Reporting Stored Procedures Best Practices

4.  (Mostly) Declarative Reporting Perl Modules

5.  Conclusions

What is coming in LedgerSMB 1.4?

I figure it is a good time to review the changes that are going into 1.4 and discuss where things are going, where expected features have changed, and the like.

Framework Changes

1.4 moves all reports onto the new framework and depends on Moose for core functionality.  Independent extras may use Moo instead (both are declarative object systems).  This poses some performance costs and so we will be recommending our FCGI wrappers for standard operation instead of CGI.

Of particular interest is the new reporting framework.  This framework allows one to write new reports quickly in a nearly declarative way, with a minimum of Perl knowledge.  Reports are defined first and foremost through stored procedures/UDF's and secondly through perl modules which define the parameters of the report.  An open reporting system is something LedgerSMB has needed for some time and hopefully this will help us move forward rather quickly.

New Interface

We have based our user interface on Dojo widgets, which are, in most screens, dynamically allocated via html class and javascript.  Support for declarative pages is likely to be available too.  We have a lot of work going into good Dojo support and this is something which is really positively impacting our usability.

New Functionality

We have a bunch of new features coming including:

1. LedgerSMB 1.4 will include a new payroll framework which local organizations can use to implement payroll systems based on local rules.
2. Invoices with inventory can be subject to batch workflows and separation of duties in the same way as AR transactions.  COGS is posted FIFO, using time of approval as the transaction ordering time.
3. Template transactions will be supported natively
4. CSV uploads for a number of types of data will be supported automatically.
5. Most reports will be available in ODS, CSV, and PDF formats automatically, even if the author did not explicitly make such available.
6. Better handling of localized number and date formats.
7. Much, much more.

This is a big release and provides a lot of things that larger organizations will need.

PostgreSQL, Community Development, and Support

With the impressive release of PostgreSQL 9.3 I have noticed that a number of journalists seem to only mention a single provider of support.    I decided to write a different sort of article here discussing the PostgreSQL commercial support offerings and how these fit into development.  Please note that the same basic approach applies to LedgerSMB as well, as we have done our best to emulate them (though quite obviously with different support providers).

This is exactly how not to run an open source project if you want it to be a cash cow for a single business but it is how to run an open source project if you want it to reach as large of a user base as possible and provide as many economic opportunities as possible.

PostgreSQL is a community developed, multi-vendor project.  Vendors come and go, but the community endures.  Many vendors who used to contribute to the project no longer do so but there are a number of mainstays.  This article was written in September of 2013, and if you are reading it years later, please be aware there may be additional sources of support available.

Because PostgreSQL is developed by multiple vendors working together, in theory any vendor which employs competent programmers can fix bugs, offer hot fixes, and more for clients, and can, reasonably, if the patches are of good quality, get them accepted upstream.  This is an extremely important selling point for the database management system.

There are several long-standing companies in the community which offer support accounts on the database itself.  This is on top of vendors like Red Hat who offer high quality support with their OS service level agreements.

This list provided here is largely for journalists and others who wish to discuss PostgreSQL support.  It is by no means exhaustive nor is it intended to be.  Support is available in various markets through other companies as well and one of our tasks as a community is to create a larger amount of support and consulting services, serving a larger variety of markets.  This is a strength of the community development model (as opposed to the vendor development model).

In the interest of full disclosure, I am a principal consultant for 2ndQuadrant, and I have worked with folks from Command Prompt, PGExperts, and some other companies on various projects.  Some aspects of what I say here come from something of an insider's perspective.

1. 2ndQuadrant offers high quality 24x7 support delivered by support engineers which include actual contributors to the software.  Some of their support offerings offer guarantees not found by the vendors of proprietary databases.  I say this as a former employee of Microsoft's Product Support Services division.
2. Command Prompt. Inc offers service level agreements which ensure quite a bit of proactive assistance.  The firm is one of the long-standing mainstays of the PostgreSQL scene.
3. PGExperts offers a number of services aimed at ensuring support for critical production environments.
4. EnterpriseDB offers support for the official version of PostgreSQL, as well as their own proprietary spinoff, "Postgres Plus Advanced Server."  Their proprietary version has a number of features aimed at smoother migration from Oracle, although it is sometimes mistaken for an "enterprise edition" of PostgreSQL.

The companies which I have listed above are largely those where I have met people from them and know something about their business models.  The project also maintains list of professional service providers who have opted in to be listed.   Note that some service providers may provide technical support or not.

In the end, this model of support is a selling point of the software.  Unlike with Oracle, the companies which provide support have to serve the customer's needs because otherwise the customer can go elsewhere.

PostgreSQL is used in a large number of critical production capabilities where the ability to call someone for support, and get a very competent second set of eyes when things go wrong is absolutely necessary, and the companies above provide that.  But the companies listed go further, and are able to support the software as if they were the vendor (or likely even better).

Encryption: MySQL vs PostgreSQL

First a note, all my tests involved a relatively simple table with a schema like this (column names did vary):

CREATE TABLE enctest (
   id int,
   id_text text,
   id_enc bytea
);

In MySQL varbinary(64) was used instead of bytea.

The id was formed from a sequence from 1 to 100000.  I had more trouble loading this in MySQL than in PostgreSQL. id_text was a text cast of id, and id_enc was the value of id_text encrypted using 128-bit AES encryption.  This was intended to mimic sales data consisting of short strings that would be decrypted and converted to numeric data before aggregation.

The goal was to see how fast the different implementations would decrypt all records and aggregate as numeric data types. For PostgreSQL, pgcrypto was used.  The tests were conducted under ANSI mode on MySQL, and the tables were innodb.

What I found was remarkably disturbing.  While MySQL was blazingly fast, this speed came at the cost of basic error checking and rather than an error, decrypting with the wrong key would give the wrong data back sometimes, even on traditional modes.  This is because the errors instead of warnings, per the documentation, are only transformed on insert, not on select.  In other words, MySQL is just as permissive in read operations with STRICT mode turned on as turned off.

mysql> select sum(cast(aes_decrypt(id_enc, sha2('secret', 512)) as decimal)) FROM enctest;
+----------------------------------------------------------------+
| sum(cast(aes_decrypt(id_enc, sha2('secret', 512)) as decimal)) |
+----------------------------------------------------------------+
|                                                     5000050000 |
+----------------------------------------------------------------+
1 row in set (0.33 sec)

That is fast.  Very fast,  My similar query in PostgreSQL took about 200 seconds, so approx 600x as long, and was entirely CPU-bound the whole time.

efftest=# explain (analyse, verbose, costs, buffers) select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd')::numeric) from sumtest;
                                                          QUERY PLAN           
                                              
--------------------------------------------------------------------------------
-----------------------------------------------
 Aggregate  (cost=7556.16..7556.17 rows=1 width=62) (actual time=217381.965..217
381.966 rows=1 loops=1)
   Output: sum((pgp_sym_decrypt(testvalsym, 'mysecretpasswd'::text))::numeric)
   Buffers: shared read=5556 written=4948
   ->  Seq Scan on public.sumtest  (cost=0.00..6556.08 rows=100008 width=62) (ac
tual time=0.015..1504.897 rows=100000 loops=1)
         Output: testval, testvaltext, testvalenc, testvalsym
         Buffers: shared read=5556 written=4948
 Total runtime: 217382.010 ms
(7 rows)

My first thought was that for there to be a 3-orders-of-magnitude difference between the two implementations, something must be seriously wrong on the PostgreSQL side.   This is a huge difference.  But then something occurred to me.  What if I use the wrong password?

On PostgreSQL:

efftest=# explain (analyse, verbose, costs, buffers)
select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd2')::numeric) from sumtest;
ERROR:  Wrong key or corrupt data


On MySQL, it is a very different story:

mysql> select sum(cast(aes_decrypt(id_enc, sha2('secret2', 512)) as decimal)) FROM enctest;
+-----------------------------------------------------------------+
| sum(cast(aes_decrypt(id_enc, sha2('secret2', 512)) as decimal)) |
+-----------------------------------------------------------------+
|                                                            1456 |
+-----------------------------------------------------------------+
1 row in set, 6335 warnings (0.34 sec)

Hmmm, out of 100000 rows, only 6000 (6%) gave a warning, and we got a meaningless answer back.  Thanks, MySQL.  So I tried some others:

mysql> select sum(cast(aes_decrypt(id_enc, sha2('s', 512)) as decimal)) FROM enctest;
+-----------------------------------------------------------+
| sum(cast(aes_decrypt(id_enc, sha2('s', 512)) as decimal)) |
+-----------------------------------------------------------+
|                                                      1284 |
+-----------------------------------------------------------+
1 row in set, 6230 warnings (0.35 sec)

Again 6% warnings, meaningless answer returned.  Wow this is fun.....

Try as I might I couldn't get MySQL to throw any errors, and I always got meaningless results back with the wrong key.   A closer look would reveal that MySQL was throwing warnings only when certain rare criteria were met and was performing no validation on the data to ensure it matched the data in.  Further review showed that the cryptograms were much shorter on MySQL than PostgreSQL suggesting that PostgreSQL was padding short strings in order to ensure that cryptography would better protect the data.  More on this later.

This suggested that the difference in the performance might well be related to extra sanity checks in PostgreSQL that MySQL omitted for speed-related purposes.  Armed with this knowledge, I tried the following:

efftest=# update sumtest set testvalsym = pgp_sym_encrypt(testvaltext, 'mysecretpasswd', 's2k-mode=0, s2k-digest-algo=md5');
UPDATE 100000

The query returned pretty fast.  However these settings are not really recommended for production environments.

I went ahead and tried again my data test queries and my performance queries and the results were two orders of magnitude faster:

efftest=# explain (analyse, verbose, costs, buffers)
select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd2')::numeric) from sumtest;
ERROR:  Wrong key or corrupt data
efftest=# update sumtest set testvalsym = pgp_sym_encrypt(testvaltext, 'mysecretpasswd', 's2k-mode=0, s2k-digest-algo=md5');
UPDATE 100000
efftest=# explain (analyse, verbose, costs, buffers) select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd2')::numeric) from sumtest;
ERROR:  Wrong key or corrupt data
efftest=# explain (analyse, verbose, costs, buffers)
select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd')::numeric) from sumtest;
                                                          QUERY PLAN           
                                              
--------------------------------------------------------------------------------
-----------------------------------------------
 Aggregate  (cost=13111.00..13111.01 rows=1 width=71) (actual time=1996.574..199
6.575 rows=1 loops=1)
   Output: sum((pgp_sym_decrypt(testvalsym, 'mysecretpasswd'::text))::numeric)
   Buffers: shared hit=778 read=10333
   ->  Seq Scan on public.sumtest  (cost=0.00..12111.00 rows=100000 width=71) (a
ctual time=0.020..128.722 rows=100000 loops=1)
         Output: testval, testvaltext, testvalenc, testvalsym
         Buffers: shared hit=778 read=10333
 Total runtime: 1996.617 ms
(7 rows)

Much, much faster.  Of course that comes at the cost of security features.

The primary security features changed here are what are called string to key functions.  PostgreSQL also offers some relatively complex containers for short data which include things like padding and session keys.  MySQL does not provide string to key management, and requires that you generate the hexadecimal key yourself.  PostgreSQL provides a number of options for string to key generation which allow for salted hashes to be used for the actual encryption.

One of the most obvious implications here is that with MySQL, you have to generate your salted hash yourself, while with PostgreSQL, it may generate a different salted hash for each line.   This is very important for encryption particularly with smaller strings because this helps thwart rainbow tables.  In essence with salted keys, there is no 1:1 relationship between the passphrase/data combination and the cryptogram, because there is no 1:1 relationship between the passphrase and the key.   Further testing suggests that this is not responsible for the performance difference but it does suggest there are more checks lurking beneath the surface which are omitted from MySQL.

So given that the issue is not string to key management, the issue must be padding.  For very short strings, PostgreSQL is managing padding and containers, while MySQL is purely encrypting short strings without more than minimal padding.  Since there is insufficient padding, the decryption routines are much faster, but this comes at a cost of any reasonable security.  Additionally PostgreSQL provides data checks that are not done on MySQL.

So what does this tell us?  I think the primary lesson which I have had driven home a few times is that database-level encryption is tricky.  This is particularly true when other considerations are involved, like performance aggregating data over significant sets.     Add to this the woes of in-db key management and the like and in-db encryption is definitely expert territory.  In this regard, MySQL's approach seems to require a lot more complexity to maintain security than PostgreSQL's.

It is important to remember that short encrypted strings are relatively common in databases which use encryption.  One of the most common uses is for things like credit card numbers.    For the reasons mentioned here I would suggest that PostgreSQL is much more trustworthy in these cases.