In Part III about Writing Postgres Extensions we fixed a serious bug using LLDB debugger and completed the base36
type by using type casts. Now it’s time to recover what we’ve actually achieved – and to do some more testing.
You can review the current code base on on github branch part_iii.
Full-Power Testsuite
Simply trying out some stuff in the Postgres-console and assuming that everything will work just fine is a bad idea, especially since we introduced some serious bugs while developing our extension. Because of this, we learned how important it is to have a fully covered test suite that tests not only the “happy path,” but also the edge and error cases.
We already did a good job on testing in the first post, where we used the built-in regression testing for extensions. So let’s write down our findings in some test script.
1 2 3 4 5 6 7 8 9 10 |
|
Note that I added (COSTS OFF)
to the EXPLAIN
command to make sure the test won’t fail on different machines with different cost parameters.
If we now run:
1
|
|
we get our output in results/base36_test.out
and can copy it over to sql/expected/
. But wait – let’s read it carefully first to make sure this all is as expected.
1 2 3 4 5 |
|
Well, it’s obviously not. The base36_in
seems to also have a serious bug when we put too long strings into it. Let’s look into the manual from strtol
:
1 2 |
|
So in line 13 we cast a long
to an int which overflows
1
|
|
Reuse Internal DirectFunctionCall
Let’s do the cast correctly by again reusing Postgres internals: So how does Postgres cast a bigint
to an integer
?
1 2 3 4 5 6 7 |
|
The SQL-function int4
is used here — how is that defined?
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
So int84
is what we are looking for. You’ll find the definition in utils/int8.h
, which we need to include in our source code to be able to use it. You already learned in the first post that in order to use C-functions in SQL you’ll have to define them using the “version 1” calling convention. Thus, these functions have a specific signature for int84
. Here it is:
1
|
|
So we cannot directly call this function from our code. Instead, we have to use the DirectFunctionCall
macros from fmgr.h
:
1 2 3 4 5 6 7 8 9 |
|
With these macros we can directly call any function from our C code, depending on the number of arguments. But be careful using that: these macros are not type-safe, as the arguments passed and returned are just Datums
which is any kind of data. Using this you won’t get an error from the compiler. You’ll simply get strange results on runtime if you pass the wrong data types around - one more reason to have a fully covered test suite.
As the macro already returns a Datum type, we’d end up with:
1 2 3 4 5 6 7 8 9 |
|
To finally get:
1 2 3 |
|
Pimp the Makefile
To have a better overview about the different tests, let’s split them up into different files and store them under the test/sql
directory. To make this work, we need to adapt the Makefile as well.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
|
TESTS
defines our different test files which you can find under test/sql/*.sql
. Also we added REGRESS_OPTS
changing the test input directory to test
(--inputdir=test
), that is the directory where the regression runner expects the sql
directory with the test scripts and the expected
directory with the expected output. We also define that the extension base36 should be created in the test database beforehand (--load-extension=base36
), avoiding running the CREATE EXTENSION
command on top of each test script. We also define to load the plpgsql
language into the test database, which is actually not needed for our test suite. But it doesn’t hurt, and gives us a more general Makefile for our future projects.
Test Files Organization
Let’s now add the test files:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
|
Note I wrapped the state changing commands in a transaction that will be rolled back at the end. This is to ensure that that each script starts with a clean state. If we now look at what we got in results/base36_io.out
we see that we have again some interesting behavior on malicious input.
1 2 3 4 5 6 7 8 9 10 11 12 |
|
The strtol
function converts into the given base, stopping at the end of the string or at the first character that does not produce a valid digit in the given base. We definitely don’t want this surprise, so let’s read the man page man strtol
and fix it.
1 2 3 4 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
|
Now after running make clean && make && make install && make installcheck
, results/base36_io.out
looks good. Let’s copy it into the expected folder:
1 2 |
|
And rerun our test suite:
1
|
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
|
Here we played with some runtime query configuration to force index usage and a hash aggregate.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
|
Thus, we can make sure COMMUTATOR
and NEGATOR
are set up correctly.
As we didn’t write much own code but used Postgres’ internals we see results/operators.out
looks good. We’ll copy it over as well.
1 2 |
|
getting
1 2 3 4 5 6 7 |
|
One more test
So far we implemented input and output functions, reused Postgres’ comparison functions and operators and tested everything. Are we done? Nope! There is one more test we could add:
1 2 3 4 5 6 7 8 9 10 |
|
Here we try to update to a negative value which should fail:
1 2 3 4 5 |
|
But it doesn’t…Well, it does, but not on the update step – only when retrieving the value. While we disallow negative values for the OUTPUT
function, it’s still allowed for the INPUT
. When we execute the following command:
1 2 3 4 |
|
both INPUT
and OUTPUT
functions are called, resulting in the error. But for the UPDATE
command only input is called, resulting in a negative value on disk which then can never be retrieved. Let’s fix that quickly
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
|
Is it worth the effort?
While it’s fun to extend Postgres, let’s not forget why we actually built all of this. Let’s compare the base36
approach to the Postgres-native approach of using varchar
type. We’ll compare two aspects: the storage requirements for each type and the respective query performance.
Storage Requirements
Our initial motivation was to save space and just store 4 byte integers instead of 6 characters, which according to the documentation would waste 7 bytes.
So let’s compare it.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
Oops…we didn’t save a single byte! That’s quite unfortunate for all the effort we put into our datatype. So how does this happen? Well, we have to know how Postgres actually stores the data. Our little example would end up with the following:
base36_check: 23 bytes for the header + 1 byte for the null bitmap + 4 byte for data = 28 bytes varchar_check: 23 bytes for the header + 1 byte for the null bitmap + 7 byte for data = 31 bytes
So we should indeed save 3 bytes per row but still end up with the same table size. We also need to consider that Postgres stores data in a page which typically contains 8kB (8192 bytes) of data, and that a single row can not span two pages. Each row would also end up with a multiple of maximum data alignment setting, which is 8 bytes on a modern 64bit system.
So in the end, we’d need 32 bytes + 4 bytes tuple pointer per row in both situations.
1 2 3 |
|
The picture would (of course) totally change in a real world example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
|
As we added data into the database, due to alignment 4 wasted bytes on our base36_check
table it didn’t grow, while the base36_check table grew by 4 bytes of data plus 4 bytes alignment per row.
Now we’re saving a good 20% of space.
Query Performance
Let’s also do some timing.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
|
Besides the fact that the sorting of base36 feels more natural, it’s also 8 times faster. If you keep in mind that sorting is a key operation for databases, then this fact gives us the real optimization. For example, when creating an index:
1 2 3 4 5 6 |
|
It’s also useful for join operations or grouping by statements.
More to come …
Now that we’ve fixed all the bugs and added tests to ensure they won’t come back, our extension is almost complete. In the next post on this series we’ll complete the extension with a bigbase36
type and see how we can structure our code a bit better.