DEV Community: Johan Lindstrom

Cheap count(*) in Postgres

Johan Lindstrom — Mon, 11 Nov 2019 17:47:44 +0000

Running select count(*) from mytable in Postgres can be expensive for large tables. Beacause of its MVCC concurrency model it must do a lot of work to get an exact figure.

If we're not careful the database can start using a lot of CPU, disk IO and cache memory. Not great in production where the database presumably is pretty busy doing other important work...

But if we're new to a database and just looking around, we don't care about the exact number. We just need to know if it would be dangerous to run queries on the table to start investigating the broken data after that nasty bug.

There are other ways to do this, but here's a simple and easy-to-remember trick: use the query plan from explain.

explain select count(*) from my_table;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Aggregate  (cost=1901498.40..1901498.41 rows=1 width=8)
   ->  Seq Scan on my_table  (cost=0.00..1815135.52 rows=34545152 width=0)
(2 rows)

See that rows=34545152? That's the rough number of rows in the table.

35 million. Careful with those full table scans...

DBIC testing: rs_with_new_rows

Johan Lindstrom — Tue, 18 Jun 2019 12:33:37 +0000

Here's a handy little DBIx::Class ResultSet method for when you need to write a test verifying database changes.

=head2 rs_with_new_rows() : $resultset_with_new_data

Resultset with only PK ids which are greater than the currently
largest one, i.e. it will only return rows created going forwards.

Great for testing.

=cut

sub rs_with_new_rows {
    my $self = shift;
    my $max_id = $self->get_column("id")->max();
    return $self->search({ id => { ">" => $max_id } });
}

Put that in the base class for your ResultSet classes to make it available to all of them in one go.

As you can see, the basic operation is to look for the highest id column value and then find rows with an id larger than that.

This brings along a few caveats. First off, the PK column needs to be an ever increasing value (probably an auto_increment or sequence depending on your database).

Second, the PK column obviously needs to be called id. If that's not the case, you could solve this by using the primary_columns method of the ResultSource class and use that.

Third, if this were a proper library it should probably be flexible and and robust enough to be used as part of a more complex query, and we should use the ResultSet current_source_alias to fully qualify the column names.

For this simple use case, it works fine though.

Finally, let's write that test

Here's how the rs_with_new_rows method would be used in a test:

note "*** Setup";
my $tree_node_rs = $schema->resultset("TreeNode")->rs_with_new_rows();

note "*** Run";
# Run code under test, which creates tree nodes

note "*** Test";
is($tree_node_rs->count, 3, "Correct number of tree nodes created");
ok(
    ! $tree_node_rs
        ->search(undef, { order_by => "id" })->first
        ->parent_id,
    "First (top) tree node has no parent",
);

This works because of the suuuper nice way we can chain resultsets in DBIC without actually executing any SQL query.

The method rs_with_new_rows ends with a call to ->search, which returns a new resultset with the fresh search criteria added. The query doesn't run until we ask for some data, e.g. using ->all, or ->count.

Perl I never write: snippets

Johan Lindstrom — Wed, 12 Jun 2019 19:03:58 +0000

Emacs has this cool package yasnippet, which is a simple template expansion system.

So for instance when I'm declaring a method, I can type a simple trigger word: argsm (args for a method), hit <Tab> and the trigger word expands into the corresponding template (| is the cursor (or "point" in Emacs parlance)):

    my $self = shift;
    my (|) = @_;

This is obviously something that needs to be done all. the. time.

In this case I also use it when there are no method arguments, because it turns out it's way less hassle to type argsm<Tab> and delete the current line than to type my $self = shift;.

Snippets are a very useful mechanism for reducing the friction of doing common, slightly fiddly things. Reducing friction is important, because it allows you to keep your mind on what you actually want to accomplish, rather than the rote typing of syntax (and shifty characters like ()$@_).

yasnippet is a good snippets package for Emacs; I'm sure your editor has a similar feature/plugin available.

Yasnippets

Here's the actual snippet file snippets/perl-mode/argsm:

#name : methods args -- my $self = shift; my () = @_;
# --
my \$self = shift;
my ($0) = @_;

As part of the snippet expansion, you can hit <tab> to move between specific places (tab stops, like $1, $2, etc.) in the snippet, usually to type custom text there. $0 is the final tab stop where the expansion process ends and you can continue editing.

The tab stop text can also have a default value which can be a string, or come from calling a function, or be mirrored from what you type elsewhere in the snippet.

This is all quite powerful, especially if you add in some simple elisp functions of your own. We'll see some of these things in action below.

There's a lot more to yasnippet, like different trigger mechanisms, dropdown menus, completion support, etc. Start small and simple, and take it from there.

You can find more yasnippet info and installation instructions at the end of this article.

Perl snippets

yasnippet comes with a lot of existing little snippets for various languages and modes.

I don't use most of the canned Perl snippets however: they are mostly regular Perl keyword constructs that aren't a chore to type in the first place. Instead, here are some of the ones I've defined myself, in order of actual usage and utility:

Argument unpacking

I use these right after having typed the method skeleton (with autopair-mode for the braces):

sub basket_total_price {
    |
}

argsm

#name : methods args -- my $self = shift; my () = @_;
# --
my \$self = shift;
my ($0) = @_;

args

#name : args my () = @_;
# --
my ($0) = @_;

But, wouldn't it be more convenient to include the whole sub skeleton in the snippet, curly braces and all?

Sure, you could do that. But I find that it pays off to try to hook the automated parts into the flow only where the friction starts to increase, and leave the low friction bits alone. So you type along, and just when it gets annoying, you switch over to inserting a snippet instead.

In addition, the shorter snippet can be added when editing an already existing sub. This makes the smaller, more targeted piece of text useful in more situations.

Method/sub POD

Writing POD for method signatures (i.e. the sub name, arguments and return value) is a very good habit: spending more time thinking about what's going on is usually a good thing.

In some respects doing this is like a half-way house to TDD (ok, maybe fifth-way house), because writing things down makes you think through what the method does, what parameters it takes and what it returns, any special cases that may apply and how things can go wrong. Basically imagining how it should and could be called, while not going all the way to actually writing code to do that.

podx

Invoke this just above a sub declaration to expand into boilerplate POD text:

#name : pod above sub -- =head2 name() :
# --
=head2 ${1:`(my-yas-perl-next-sub-name)`}($2) :$3

$0

=cut

This snippet is a good demonstration of the different tabstops $1, $2, $3 and finally $0. The first stop is the pre-filled-in subroutine name, and when I hit <tab> the point jumps to the next place where I can continue to enter the parameters, etc.

The default value for the sub name is where things gets interesting. The sub name comes from calling the elisp function my-yas-perl-next-sub-name, which in this case is a simple regex match:

(defun my-yas-perl-next-sub-name ()
  (interactive)
  (save-excursion
    (if (search-forward-regexp "sub +\\([a-z0-9_]+\\)" nil t)
        (match-string 1)
      "")))

So having this:

podx|
sub get_workflow_type_row {

turns into this:

=head2 |get_workflow_type_row() :



=cut

sub get_workflow_type_row {

and finally this, after documenting the method:

=head2 get_workflow_type_row($workflow_type) : $workflow_type_row | undef

Return a $workflow_type_row for the $workflow_type
(e.g. "multi_role"), or undef if it doesn't exist (either because
it's invalid or because the company has no Workflows with that
$workflow_type).

=cut

Moo/Moose lazy attribute

These are basic Moo/Moose attributes with a lazy builder, easily the most common style of attribute in our code. And when it's not, the inserted snippet text is easy to adjust.

Using the trigger words haslm (Moo) and hasls (Moose) for this also has the benefit of getting the syntax right, since that is annoyingly different and I can never remember which is which.

haslm

#name : has => () Moo attribute with lazy builder
# --
has ${1:name} => ( is => "lazy" );
sub _build_$1 {
    my $self = shift;
    $0
}

hasls

#name : has => () Moose attribute with lazy builder
# --
has ${1:name} => ( is => "ro", lazy_build => 1 );
sub _build_$1 {
    my $self = shift;
    $0
}

If you look at the part where it says ${1:name} you'll see another cool yasnippet feature: mirroring. While we type the attribute name, the builder sub name is updated in real time:

has csv_writer| => ( is => "lazy" );
sub _build_csv_writer {
    my $self = shift;

}

Other Moo/Moose attributes

For completeness, here are some other less commonly used attribute declarations.

hasr

#name : has => () Moose/Moo attribute with required => 1
# --
has ${1:name} => ( is => "ro", required => 1 );

has

#name : has => () Simple Moose/Moo attribute
# --
has ${1:name} => ( is => "ro" );

hasbm

#name : has => () Moo attribute with lazy "builder" subref
# --
has ${1:name} => ( is => "lazy", builder => sub { $0 } );

POD fragments

Not as commonly used, but still somewhat useful: POD =head1 sections.

podn

#name : name with current package -- =head1 NAME
# --
=head1 NAME

${1:`(ps/current-package-name)`} -- $0

=head1 DESCRIPTION

=cut

This one is interesting in that it reuses the elisp function from Devel::PerlySense -- Copy Package Name to get the string with the current package name, which is what we want for the =head1 NAME POD section.

Let's stop typing things that are already known!

poda

#name : pod =head1 ATTRIBUTES
# --
=head1 ATTRIBUTES
$0
=cut

podm

#name : pod =head1 METHODS
# --
=head1 METHODS
$0
=cut

podcm

#name : pod =head1 CLASS METHODS
# --
=head1 CLASS METHODS
$0
=cut

pods

#name : pod =head1 SUBROUTINES
# --
=head1 SUBROUTINES
$0
=cut

Start using `yasnippet`

END

That's it!

After reading this I'm sure you've got some great ideas for snippets of your own, or maybe you already have an existing library of useful snippets?

Please share in the comments! (or over at reddit.com/r/perl)

use List::AllUtils;

Johan Lindstrom — Mon, 12 Nov 2018 14:00:15 +0000

Did you know about the module List::AllUtils?

It puts the goodness of List::Utils and List::MoreUtils into one nice package. Or... was it List::Util and List::MoreUtils? List::MoreUtil? Or is it actually List::SomeUtil? Gaaahrhg!

Well, that's the problem it solves. Now you can forget about which of those modules were named what, use List::AllUtils; and just go on with your life. It also adds a few more nice functions from List::UtilsBy.

Props to Dave Rolsky (@houseabsolute on the Twitter machine) for being lazy on our behalf!

RTFM

It's a good idea to be roughly aware what kinds of operations are available for dealing with lists for next time you need them.

Read the manual on meta::cpan, or locally using perldoc (after installing the module, probably using the cpanm client).

perldoc List::AllUtils

Highlights

Here are a few standard things on the level of basic language features you should know about.

sum, sum0
min, max
uniq

And a few niceties worth taking a look at.

any, all
pairmap, pairgrep
pairwise
part
sort_by, nsort_by

But there are many more useful functions in there.

Used correctly these could save you a lot of time and bugs by replacing imperative for loops with more expressive and precise flow control.

Which function helped you out the most? Add to the discussion below, or on /r/perl!

Pg fulltext search with DBIx::Class

Johan Lindstrom — Mon, 05 Nov 2018 13:46:53 +0000

Recently I wrote an admin interface for an internal service. It's basically a list of things processed by the service.

For troubleshooting problems it seemed quite useful to be able to filter the list by some text columns containing parameters and the processed result. Using Postgres fulltext search turned out to be a trivial affair, so I thought I'd share it here.

Fulltext search in Postgres

In Postgres, performing a fulltext search is done using the Text Search datatypes and operators. Here's a good introduction.

As an example, assume we have a book table with a blurb TEXT column containing a free form description of the book.

Let's find books in The Laundry File series by Charles Stross, because the latest one, "The Labyrinth Index" was just published. Yay!

select id, title, blurb from book where
    to_tsvector('english', blurb)
    @@
    to_tsquery(
        'english',
        '(scifi | sci-fi) & paranormal & bureacracy
    );

Briefly what's going on here is that the blurb column is parsed into a tsvector which contains English words we can match against. The matching is done using the to_tsquery function, which contains a search query in a certain format. From the example you can probably figure out the overall syntax.

That's it! Working full text search on any column. Well done Pg!

There's a lot more to this and I only know the very first things about it, so go read the Postgres manual for more information.

Improving performance

With the query above, the to_tsvector('english', blurb) part will run this function for every row, so that's going to be a bit slow.

In this particular case, the data volumes weren't that huge initially. Even with several thousand rows this felt quite snappy, with queries returning in tens of ms.

That won't last with increasing data volumes though, so let's create an expression index on the blurb column for this one query.

CREATE INDEX CONCURRENTLY book_blurb_gin
    ON book USING GIN ( to_tsvector('english', blurb) );

Note how to_tsvector('english', blurb) matches exactly what we did in the query above.

Search usability

As a good starting point, especially for non-technical users, it's probably a good idea to not overcomplicate things with the tsquery format.

Just say that all words the they type must match the text: construct the tsquery string yourself by joining words in the user input with ' & '.

If you do allow your users to enter free-form tsquery search strings, you'll have to explain it somewhat plainly to them. For technically inclined users, the briefest explanation I've come up with is:

Use &, |, ! between words, and () to group expressions.
Match 'single-quoted phrases'.

Examples are very powerful for conveying details like this. You should pick some useful search terms related to your domain, maybe similar to this:

(scifi | sci-fi) & horror & 'paranormal bureacracy'

But do try to include all the available features in the example, because people don't read text, and the example may be the only thing they actually cast their busy eyes at. ¯\(ツ)/¯

Simpler-to-use query formats

In Postgres 11 and onwards, there are more user friendly ways to get user input. Read further about plainto_tsquery and websearch_to_tsquery.

tsquery errors

If the user enters invalid tsquery syntax, Postgres will throw an exception when the query is executed. You'll need to be prepared to handle that and rephrase the error into something nicer. You can match the error message against this:

syntax error in tsquery

Chained DBIx::Class search methods

The Perl module DBIx::Class is an ORM on top of the SQL-level DBI module. It is excellent in many ways.

One of the nicer features of DBIC is chained resultsets. This means you can have a resultset object with a where clause applied to it, and then pass around the object and modify the query by applying further conditions, joining more tables etc.

my $book_rs = $schema->resultset("Book");
my $title_book_rs = $book_rs->search({
    title => "...",
});
my $title_and_author_book_rs = $title_book_rs->search({
    author => "...",
});

# The query is only executed when we ask for the rows
my @book_rows = $title_and_author_book_rs->all;

Of course, we can define one of these calls to search in its own method on the ResultSet::Book class.

sub search_title_rs($self, $title) {
    return $self->search({ author => $title });
}

Now, this is a trivial example, but encapsulating the details about the query into a search method like this makes the actual application code very readable, even when we need to construct quite complicated queries.

Extracting small query fragments like this is also a good way to ensure that details related to each resultset is placed in the class actually responsible for it, rather than spread around the code base as part of larger queries.

This technique is what we'll use to search the fulltext column.

search_tsquery_rs

Using the to_tsvector and to_tsquery syntax in SQL is a bit ardous, and in DBIC it is even more fiddly. But as we just saw it can be nicely encapsulated in a search_tsquery_rs method that can be chained onto the resultset.

Put this in the ResultSet::Book class for your table, or better, in a common base class which all your ResultSet classes inherit from:


=head2 search_tsquery_rs($column, $tsquery) : $rs

Search the $column as a Postgres tsquery fulltext search.

Use &, |, ! between words, and () to group expressions.

Invalid tsqueries will throw an exception that matches
m/syntax error in tsquery/.

=cut

sub search_tsquery_rs($self, $column, $tsquery) {
    $self->search({
        -and => [
            \[
                "to_tsvector('english', $column) @@ to_tsquery('english', ?)",
                $tsquery,
            ],
        ],
    });
}

Note the peculiarities needed to make this work in DBIC:

Literal SQL is inserted in the query, but (and this is cool) we can still use placeholders for this
This feels more like a hack, but to get a syntactically correct where clause, we need to combine this with an AND operator

Now we can use this method seamlessly in our application code:

my $book_rs = $schema->resultset("Book");
my $book_count = $book_rs
    ->search({ author => "..." })
    ->search_tsquery_rs("blurb", "horror & scifi")
    ->count;

Handling tsquery errors in DBIC

While you can pass around resultset objects and chain them together, the actual SQL query isn't executed until you ask for some data to be returned, e.g. by calling ->all or ->count.

This means that we can't encapsulate the error message handling inside the search_tsquery_rs method itself, because it's not necessarily near the place and time where the exception will be thrown.

Instead you'll have to wrap the place of execution in an eval (or better: use Try::Tiny) and identify the tsquery related exception there.

Wrap-up

That's it!

I hope that was a helpful intro to Postgres fulltext search, and how to use it neatly with DBIx::Class.

To get an idea for how nicely Postgres supports this: all in all, it probably took longer to write up this blog post than to research and implement this for my simple needs. It was also an absolute delight to be using software where well designed parts just seem to work out when composed togeher.

Bonus: Postgres regex search-method

Sometimes, fulltext search is overkill. So while we're at it, here's how to do a LIKE query, except using Postgres' regular expressions.

=head2 search_regex_rs($column, $regex) : $rs

Search the $column as a Postgres SIMILAR TO regex match.

Invalid regexes will throw an exception that matches
m/invalid regular expression/.

=cut

sub search_regex_rs($self, $column, $regex) {
    return $self->search({
        $column => \[ "SIMILAR TO ?" => $regex ],
    });
}

Extract Variable in Emacs

Johan Lindstrom — Thu, 01 Nov 2018 14:02:56 +0000

If you're using Emacs to wrangle Perl, here's a useful little refactoring feature you can use:

Extract Variable - C-o e e v -- Do the refactoring Extract Variable of the active region.

Installation instructions below.

What is refactoring again?

Martin Fowler introduced the concept of refactoring in his 1999 book Refactoring: Improving the Design of Existing Code.

Refactoring is a way to restructure code to make it easier to work with, while preserving behaviour. Refactoring happens in small predictable steps, with tests passing after each step.

 "your system should not be broken
  for more than a few minutes
  at a time"
                   -- Martin Fowler

Since then, the original meaning of the term has shifted to mean... really, any old rewrite of existing code, regardless of how large-scale it is, or whether it's actually changing behaviour by fixing a bug or changing requirements.

These are all legitimate things you can do to a code base, but it's not the activity that Fowler set out to describe.

Extract Variable

Extract Variable is one of the easiest and smallest refactorings to implement while still being quite useful in day-to-day programming. Anything that helps making code clearer on a line or method level is a welcome help.

It's also easy to work with, because it really only has one step.

Walk-through

Here's an example with a piece of code accessing the database config for the application user and the db admin user. The are mostly the same.

my $app     = $self->app->config->{services}->{db}->{product}->{app};
my $dbadmin = $self->app->config->{services}->{db}->{product}->{dbadmin};

Select a piece of code (on either of the lines) that is duplicated a lot within the current subroutine and hit C-o e e v (mnemonic: Edit - Extract Variable).

In this case this seems to be the common part:

$self->app->config->{services}->{db}->{product}

You will be asked for a variable name to put this in. The default is the last word in the selected code ($product).

All occurrences of the selection will now be replaced with $product, and the new variable $product will be declared just before the earliest usage.

my $product = $self->app->config->{services}->{db}->{product};
my $app     = $product->{app};
my $dbadmin = $product->{dbadmin};

Before the edit, the mark was pushed at the location where you started, so you can hit C-u C-SPC to jump back.

After the edit, the point is left at the new variable declaration so you can ensure that it is in a reasonable location. It's not unusual to need to move it to an outer scope in order for all the usages to be covered by the declaration.

Now you need to ensure this edit makes sense. Both replacements and the declaration are highlighted, so it's easy to see what was changed.

If it's all wrong, just M-x undo to revert the change.

Once you've eye-balled the edits, hit C-o e h to remove the Highlights.

Note that the replacement is syntax unaware, so you'll have to ensure it's syntactically correct yourself, and placed in a nice spot in the source code (althugh most of the time it works just fine).

In this particular example, had there been no arrows between the hash keys, the final code would have looked like this:

my $product = $self->app->config->{services}{db}{product};
my $app     = $product{app};
my $dbadmin = $product{dbadmin};

and that clearly isn't equivalent Perl code, the $product hashref being treated as a hash. This is probably the most common failure mode though, and shouldn't happen that often. Now you know.

By default, only the current subroutine is changed. Invoke with the prefix arg C-u to change the entire buffer: C-u C-o e e v.

Neat uses for Extract Variable

Duplication

Remove duplicated code (duh), beause small-scale duplication is just shoddy, and this opens up an opportunity to introduce well named variables that describes what things are.

Long lines

Break a long unreadable line into two by extracting an (again, well named) temporary variable. This is particularly useful when there's an if-statement with an unreadably long condition.

Rename

Rename variable - Extract Variable on a variable name, then just delete the new variable declaration.

my $books = $schema->resultset("Book");
my @books = $books->search({ ... })->all;

Let's rename $books --> $book_rs. Extract Variable on $books turns this into:

my $book_rs = $books;                      # <-- delete this line
my $book_rs = $schema->resultset("Book");
my @books = $book_rs->search({ ... })->all;

Point is now on the first declaration, so just delete that line and we're done.

Strings

Make method call in string:

print "So, you want to make a $object->method_call inside a string\n";

But that doesn't work obviously. There are various ways around that, but one thing you can do is to mark $object->method_call to extract it, and end up with this:

my $method_call = $object->method_call;
print "So, you want to make a $method_call inside a string\n";

Nice!

Recap

Select the repeated code you want to extract. It should be syntactically ready to be assigned to a variable
C-o e e v to Extract Variable
Confirm the variable name or enter a better one
Confirm that the extract makes sense, or fix up the code
C-o e h to end the refactoring by removing the Highlights

Getting the software

Extract Variable is available in Devel::PerlySense, which is a Perl IDE for Emacs. Most of the walkthrough example is actually from the POD of Devel::PerlySense. The key bindings described above is what you get out of the box with PerlySense.

Install with your regular CPAN client and check out the configuration section for details on how to set it up. This also installs all the elisp code.

Extract Variable is all implemented in Elisp, so if you don't want to install PerlySense, you can instead install the Melpa package lang-refactor-perl which contains this one function. If you do this, you'll have to create your own key bindings though.

Cool, now go clean up some code! :)

Carp::Always sometimes

Johan Lindstrom — Tue, 23 Oct 2018 16:53:24 +0000

Troubleshooting

Your program just died. Great, now what?

If the exception string ended with a \n, it didn't give you a line
number. But even if it did, a line number is often not enough to debug the
problem. That's where the problem occurred, but how did we get there?

Carp::Always to the rescue.

use Carp::Always;

Put this anywhere and all exceptions (and warnings) will have a full
stack trace, including subroutine arguments.

But wait...

You probably don't want to turn this on permanently for the entire program. Using Carp::Always is a global, process-wide change, and the stack trace will be added to all exceptions, even inside an eval BLOCK. That's probably not what you wanted.

So since it's a temporary debugging tool, this is a pretty bad thing
to forget in the code base since it changes global behaviour.

Do you know that nothing will go weird in the entire rest of your app
now that exceptions will look different? No you do not. So put a
reminder comment you can 'grep' for on the same line:

use Carp::Always; ### TODO: MyInitials: Remove before committing

Yes, it needs to have your initials on it, it's your personal
responsibility to remove this.

Preventing mistakes

This low-key way of reminding yourself to not screw up may not be enough. After all, programmers are people, and people forget things and screw up all the time. You may even be one of them.

If this turns out to be a problem for you there are several approaches you can take, e.g.

Commit hooks in your source control system that prevents them to be checked in
Automated test that looks through the source tree for any use Carp::Always; statements

Ad-hoc Carp

Still worried about leaving in the use statements? You can also load the module on the command line with:

perl -MCarp::Always script.pl
prove -MCarp::Always -r t

just for that script invocation. Or, to make it semi permanent, set the PERL5OPT variable with this option:

export PERL5OPT=-MCarp::Always
perl script.pl

Remember: Carp::Always, but not all of the time!

What's the version of that installed Perl module?

Johan Lindstrom — Thu, 18 Oct 2018 15:23:14 +0000

Here's a neat trick to find out what version of a Perl module is installed.

Let's try with Carp.

perl -MCarp\ 99999 -e 1

Carp version 99999 required--this is only version 1.42.
BEGIN failed--compilation aborted.

The way this works is that we ask the runtime to load a very high version, and then wait for perl to apologize that it only has current version x, in this case 1.42.

Note the escaped space between the module and the version:
-MCarp\ 99999.