Versioning

We might be happy with our Extension and use it in production for a while without
any issues. Now that our business succeed, the range for integer might no longer be enough. That means we’ll need another bigint based type bigbase36, which can have up to 13 characters.

The problem here is that we can’t simply drop the extension and re-install the new version.

If we DROP ... CASCADE here, all our data would be lost. Also, dumping and recreating is not an option for a terabyte-sized database.
What we want is to ALTER EXTENSION UPDATE TO '0.0.2'. Luckily, Postgres has Versioning for Extensions built in.
Remember in the base36.control file we defined:

PG_FUNCTION_INFO_V1(bigbase36_in);Datumbigbase36_in(PG_FUNCTION_ARGS){longresult;char*bad;char*str=PG_GETARG_CSTRING(0);result=strtol(str,&bad,36);if(bad[0]!='\0'||strlen(str)==0)ereport(ERROR,(errcode(ERRCODE_SYNTAX_ERROR),errmsg("invalid input syntax for bigbase36: \"%s\"",str)));if(result<0)ereport(ERROR,(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),errmsg("negative values are not allowed"),errdetail("value %ld is negative",result),errhint("make it positive")));PG_RETURN_INT64((int64)result);}PG_FUNCTION_INFO_V1(bigbase36_out);Datumbigbase36_out(PG_FUNCTION_ARGS){int64arg=PG_GETARG_INT64(0);if(arg<0)ereport(ERROR,(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),errmsg("negative values are not allowed"),errdetail("value %d is negative",arg),errhint("make it positive")));charbase36[36]="0123456789abcdefghijklmnopqrstuvwxyz";/* max 13 char + '\0' */charbuffer[14];unsignedintoffset=sizeof(buffer);buffer[--offset]='\0';do{buffer[--offset]=base36[arg%36];}while(arg/=36);PG_RETURN_CSTRING(pstrdup(&buffer[offset]));}

It’s more or less the same code as for base36. In bigbase36_in, we don’t need
the overflow safe typecast to int32 anymore and can return the result directly
with PG_RETURN_INT64(result);.
For bigbase36_out, we expand the buffer to 14 characters as the result could be that long.

To be able to compile the two files into one shared-library object we need to adapt the Makefile as well.

If we take a look at results/bigbase36_io.out we see again some odd behavior
for too-big values.

1234567

-- to big valuesSELECT'abcdefghijklmn'::bigbase36;ERROR: negative values is not allowedLINE 1: SELECT 'abcdefghijklmn'::bigbase36; ^DETAIL: value -1 is negativeHINT: make it positive```

You’ll notice strtol() returns LONG_MAX if the result overflows. If you take a look how converting text to numbers is done in the postgres source code, you can see that there are lots of platform-specific edge and corner cases. For simplicity, let’s assume that we are on a 64 bit environment having 64 bit long results. On 32 bit machines our test suite and thus make installcheck would fail, telling our users that the Extension would not work as expected.

Here, by including <limits.h> we can check if the result overflowed.
The same can be applied for base36_in checking result < INT_MIN || result > INT_MAX and thus getting ride of the DirectFunctionCall1(int84,result).
The only caveat here is that we can’t cast LONG_MAX and LONG_MIN to
base36.

Now that we’ve created a bunch of code duplication, let’s improve the readability
with a common header file and define the errors in macros.

BEGIN;DROP EXTENSION base36;CREATE EXTENSION base36 VERSION '0.0.1';ALTER EXTENSION base36 UPDATE TO '0.0.2';ERROR: extension "base36" has no update path from version "0.0.1" to version "0.0.2"SELECT 'abcdefg'::bigbase36;ERROR: current transaction is aborted, commands ignored until end of transaction block

Although Version 0.0.2 exists we can’t run the Update command.
To make that work we’d need an updated script in the form extension--oldversion--newversion.sql that includes all commands needed to upgrade from one version to the other.

So we need to copy all base36 realted sql into base36--0.0.1--0.0.2.sql

MODULE_PATHNAME

For each SQL function that uses a C-Function defined AS '$libdir/base36', we are telling Postgres which shared library to use. If we renamed the shared library
we’d need to rewrite all the SQL functions.
We can do better:

Summary

In the last five articles you saw that you can define your own datatypes
and completely specify the behavior you want.
However, with great power comes great responsibility. Not only can you confuse
users with unexpected results, you can also completely break the server and loose data.
Luckily you learned how to debug things and how to write proper tests.

Before you start implementing things, you should first take a look on how Postgres does it
and try to reuse as much functionality as you can. So not only do you avoid reinventing
the wheel, but you also have trusted code from the well-tested PostgreSQL code base.
When you’re done, make sure to always think about the edge cases, write down everything into tests
to prevent breaking things, and to try out higher workloads and complex statements
to avoid finding bugs in production later.

As testing is so important, we at adjust wrote our own testing tool called pg_spec. We’ll cover this in out next post.