outlier_lynn: (Default)

January 2015

181920 21222324

Most Popular Tags

Expand Cut Tags

No cut tags
outlier_lynn: (Default)
Wednesday, July 17th, 2013 10:57 am
I am getting ready to replace the production email server at work with a new one using a different set of components. Right now, I handle virtual domains and virtual users in a hard-to-maintain fashion using courier-auth and courier-imapd and some hocus-pocus involving flat files.

I want to put as much as possible in postgresql. To that end I'm using DBmail with a few of my own database extensions.

I have a test server running. And while troubleshooting a delivery problem I turned up verbosity on all bits of postfix. And a warning appeared in the logs. A warning on a piece I thought was settled long ago. It seems postfix thought the localhost domain was in mydestinations and virtual_mailbox_domains. Well, I knew for a fact (and double checked) that localhost was not in the virtual tables anywhere.

When postfix is looking to see if we accept mail for a given domain, it checks a bunch of places. One of the last is the virtual_mailbox_domains list. Postfix is not asking for a yes/no response from the database; rather, it is counting the NUMBER OF ROWS returned by the database query which will be 0 or 1. I'm sure there must be a good reason for this. I suspect it allows the same code to be used to support a lot of ways of storing this information and some can't return boolean.

I wrote a series of functions in postgresql so that the postfix queries call my functions. And that is the rub. If you have a function foo(domain text) that looks like this:

CREATE FUNCTION foo (domain text) RETURNS text AS $$ SELECT domain FROM allowed_domains WHERE domain = $1 $$ LANGUAGE sql

And you call this function like this:

SELECT foo('');

You will always get one row returned and it doesn't matter if is in the database or not. That row will either be "" or "NULL". foo() returns no rows or 1 row, but the select that calls it returns the result of foo or null. Postfix counts rows and it doesn't care that the value of the row is unassigned.

It has also been suggested that returning arbitrary text might be an issue for postfix. I have seen no evidence of that, but returning a "1" when there is a match seems safer. That means that I could do something like this:

SELECT 1 FROM (select domain from foo('%s') where domain is not null) x

Postfix substitutes the necessary key value for the '%s'. This call to my function returns one row with a value of 1 if the domain is found and returns no rows if the domain is not found.

But this is a bit clunky looking. The answer is a view.

CREATE VIEW allowed_domain AS
SELECT domain FROM allowed_domains
WHERE domain <> 'specialname' AND
domain <> 'otherspecialname';

Then the postfix query can look like this:

SELECT 1 FROM allowed_domain WHERE domain = '%s';

Better. Much, much better. It is an error to return more than one row to postfix. The table allowed_domains is not allowed to have duplicate entries so we will "never" return more than 1 row and now we can return 0 rows.
outlier_lynn: (Default)
Friday, July 12th, 2013 10:40 am
I've been working with postgresql for years now. I've written thousands of lines of sql code defining domains, types, tables, views, functions, triggers and so forth. I've read the documentation over and over and over. It's pretty damn good documentation as software manuals go, too.

Along with my friend, Greg, I've created several utility functions for dealing with time, xml and xslt, arrays, debugging, and other routine issues. Some have become obsolete as postgresql moves from major version to major version.

I feel pretty confident in my ability to design reasonable databases.

And, then, I discover something that would have made my life a bit easier a long time ago and increased the efficiency of parts of my code dramatically. There are times when I really need a function to return void even when the last statement of the function would be a select that isn't going to return void. I wrote a function that would return void that I could slip into other functions as the last statement. I did it badly. Turns out that "select null::void" is the way to do this. I won't even tell you the ridiculous function I wrote. :)

After I found this gem, I searched the postgresql documentation for any sign of the ability to cast null to void. Found nothing. I found lots of instances where one might cast null to something, but nothing that directly said I could cast null to void. It is vaguely implied with the nothing that void is a pseudotype. It's a neat trick in any event.

I wonder just how many other wonderful ways of doing things are also just out my sight.