2018-10-05T17:19:00+02:00http://adjust.github.io/Octopress2018-09-04T14:01:00+02:00http://adjust.github.io/2018-09/unexpected-gc-pausesWe recently had an opportunity to compare the garbage collection (GC) behaviour of a small Go application at different heap sizes. This application was built to migrate data away from a legacy datastore. The application contains an in-memory cache which can be configured to clear itself at different rates. This means we can look at the command during a GC cycle at various heap sizes and compare the impact of this on GC behaviour.

We found unexpected and severe whole system pauses during the garbage collector’s mark phase. This was an surprising, as Go widely advertises itself as having a low pause garbage collector. In particular we found that the expected ‘stop the world’ pause was very short, but that whole system pauses experienced during the mark phase were several orders of magnitude longer. Observing that these pauses exist is important for understanding why we see such a large performance impact from GC cycles when there are plenty of CPU resources available and the reported pauses are very short.

In this post we will detail what we saw during this experiment and discuss how this impacts the way we investigate GC performance for all of our systems going forward.

It is worth noting that the system measured here was compiled with Go version 1.10. As the GC algorithm is under constant development these effects may disappear in later releases.

Details On Tracing And Terminology

In order to compare the behaviour of this application (we will call it the MT command) we will be using the go trace tool. We will narrow our definition of system performance to the rate of ‘network events’. This is an attractive metric because it appears directly in the trace itself and because the MT command’s main behaviour is making requests to two datastores, so any disruption to the performance of the MT command should be reflected here.

Each trace is taken while forcing a GC cycle. This guarantees that we can trace the behaviour we are interested in.

Because the trace tool is primarily visual we will only perform a visual analysis. There are no statistics used to interpret any of these results. Being able to inspect the trace tool output numerically would be a valuable development.

We will look at the traces with three different heap sizes. The heap size is described by a range as the heap size is constantly fluctuating due to memory being allocated and GC cycles completed.

We will look at 3 different states of our running program, During GC, Stop The World Pause and Behaviour After GC. The system behaviour during GC is the most interesting, here we see unexpected, and unexpectedly long, whole system pauses.

During GC

8-13 GB Heap

During GC the rate of network events (indicated by the red arrow) becomes very choppy, with frequent large blank gaps. Below the Network/Syscalls rows we saw three ‘Dedicated GC’ threads and a number of other threads which are a mix of Idle GC (dull red) and useful work (green and blue). Although not pictured here the remaining threads were dominated with Idle GC slices (here the word ‘slice’ refers to the period a goroutine was scheduled in a trace) with useful work being spread out across all threads.

20-30 GB Heap

With a medium sized heap we saw a very similar trace during GC. Again we saw that the rate of network events is very choppy, with frequent large blank gaps.

60-70 GB Heap

The largest heap size shows the same characteristic gaps as the previous smaller heaps.

A Single Unexpected Pause

When we looked closely at the mark phase pauses we found that at each heap size they were of roughly similar size. Here we show one in detail. The ~19 millisecond pause (indicated by the red arrow) shown here shows how large these gaps can be. It should be noted that this gap was chosen because it was particularly large and probably indicates a rough worst case pause. But other gaps were much larger than 1 millisecond and very frequent.

Stop The World Pause

8-13 GB Heap

The final ‘stop the world’ pause is very short. Of a different order of magnitude compared to the pauses experienced during the mark phase above.

20-30 GB Heap

Another very short stop the world pause.

60-70 GB Heap

Here again the stop the world pause is very short. The pauses at the end of the mark phase was very short. As widely reported.

Normal System Performance

Here we compare the system performance outside of GC cycles at different heap sizes. We see no clear difference at different heap sizes, which is pretty much what you would expect.

8-12 GB Heap

At the smallest heap size we saw a solid block of network events over a 100 millisecond period. Without GC running we get very constant performance with no large breaks in network events. Below the Network/Syscalls rows we saw the MT command running comfortably with many short running goroutine slices (top level green/blue slices) paired with syscalls (red slices just below).

20-30 GB Heap

With a medium sized heap we saw the same behaviour.

95-105 GB Heap

With a 60-70 GB heap the GC cycle ran for almost 10 seconds and our trace only ran for 5 seconds so we didn’t capture any ‘after gc’ trace. We do have a GC-less trace when the heap ranged 95-105 GB. We will use that to observe the ‘normal’ behaviour with a very large heap.

Even at this very large heap size we see the same normal system performance.

A ‘Normal’ Millisecond

Here we zoom into a typical millisecond to get a feel for the normal rate of network events. Although we only present a sample from the 95-105 GB heap, the ‘normal’ rate of network events was very similar at all heap sizes we tested. It is interesting to relate this typical 1 millisecond period back to the large (up to ~20 millisecond) gaps we saw during GC. For every millisecond of pause we experience we are missing out on a very large number of network events.

External Systems

We have seen the impact of a GC cycle in the trace taken directly from the running MT command. But does it have any observable impact outside the command itself? If the impact was only visible in a detailed trace then we would not need to worry about these different behaviours. We can look at the metrics reported by one of the datastores to observe the increasing impact on performance as the heap grows.

With a 8-15 GB heap we saw small shallow drops in read/write rates.

With a 98-107 GB heap we saw larger and deeper drops in read/write rates from the datastore’s perspective.

Conclusion

Go’s GC algorithm is advertised widely as having very low pauses. When people talk about GC pauses in Go applications they typically talk only about the ‘stop the world’ pause which occurs at the end of the mark phase. Our tests agree that this pause is very short, but we also experience clear and repeated pauses which are much larger than this during the mark phase. These pauses are much more interesting to us when trying to diagnose performance issues which may be GC related. Unfortunately these mark phase pauses are totally unreported by any of the standard metrics reported by the Go runtime and garbage collector.

The size of the mark pauses appear to be roughly similar at different heap sizes. The biggest impact heap sizes have is on the duration of the GC cycle itself. At 8-13 GB GC took 1.5 seconds to complete, at 20-30 GB GC took 4 seconds and at 60-70 GB heap GC took 12 seconds. This means that we experienced performance disruptions for longer periods the more the heap grew. It is particularly interesting to see a very large number of ‘Idle GC’ slices in each trace. The test was performed on a single machine with 48 cores. Execution without GC required roughly 2-4 cores, but with GOMAXPROCS unset the MT command could use all 48 cores. During GC it appears to try to use all available CPU cores, but most of them remain idle. The issue we see here could be the scheduler struggling to effectively schedule a very large number of non-performing GC goroutines.

If we want to understand the performance of Go systems with large heaps we cannot rely on the standard set of metrics. At this time we don’t know of any way to observe these performance degradations except by manually viewing traces, which is very awkward and time consuming. Further developments here would be very beneficial.

]]>2017-12-13T12:00:00+01:00http://adjust.github.io/2017-12/roleman-part-1-why-we-created-rolemanRecently, I wrote a PL/PGSQL extension
which provides some basic functions for creating and altering roles,
and managing permissions. The extension was built to improve our tooling
for PostgreSQL user creation internally.
Since this has large number of external applications,
it has been released to the public under the PostgreSQL license.

This blog post is the first in a series on this extension. In it
I cover the difficulties which come with creating tooling around
utility statements in PostgreSQL as a whole, why centralising this
in user defined functions is a good idea, and what kinds of problems
we are trying to solve.

In the next article in this series, we will cover the major implementation
details. In that post, we will discuss how we prevent SQL injection from occurring
within user-defined functions, both in terms of language injection and object
injection. We will also areas of development in this area which have,
for now, not been included in the extension and the security problems they pose.
In the final article in the series, we will discuss the unique testing needs of such
a security critical piece of infrastructure, the tooling available, and the difficulties
we ran into in trying to ensure that the tests run consistently on various versions
of PostgreSQL.

Word of Warning

This blog post includes sample ways of doing things which are wrong
but are included in order to communicate problems that happen.
Please resist the urge to copy and paste, and instead make sure you
understand what you are doing. Things like placeholders may be handled
in different ways depending on different database drivers, for example.

Role Management and DDL in PostgreSQL

PostgreSQL has supported the standard database role-based
permissions model since PostgreSQL 8.1. In this model we think
about granting access to roles, and also granting one role to
another. Depending on how roles are defined, they may pass on
permissions to child roles automatically or not. Managing the
permissions given to various roles is an important part of
securing a PostgreSQL database.

All permissions are managed by a part of the SQL language
known as DDL or “Data Definition Language.” A typical set of
role management statements might look like:

Now, on the surface these look like they pose no problems for automated
tooling, but how do we ensure that a schema, table, or role name
is handled in a correct way and is not a vector for SQL injection or
other bad things?

A naive approach might be to try to use placeholders where you want
to supply input but this doesn’t work for a couple of reasons. The
first is that placeholders are intended for literal values only and
typically we want to interpolate identifiers and SQL key words.

If the database driver sends the data separately from the query then
the parse tree will be invalid. If the client interpolates
client-side, the escaping will be incorrect because of the complexities
in rules for escaping portions of the query. SQL identifiers
have an escaping syntax that is related to but different from the escaping
of string literals, and you cannot escape keywords.

This Doesn’t Work:

12345

CREATEROLE?WITH??;-- this does not work either:GRANT?to?;

But, you may say, there is one case where you have a string literal
(the password). Unfortunately that does not always work but for a
different reason: in PostgreSQL, to have a parameterised
query you have to put it through the whole planning pipeline.
Utility statements, unfortunately have no plan attached.

Whether this works or not depends on your driver

1

CREATEROLEjoe_analystWITHINHERITLOGINPASSWORD?;

Now if this is interpolated on the client, then things are handled
properly, but if interpreted on the server, you will get a syntax
error.

Now, trying to parameterise these things is something that comes up
periodically, in various forums. People often do
want to create roles from the application. Sometimes this is because
of a desire to create database roles for application uses to let the
database enforce security, and sometimes (as here) it is to
try to improve tooling for setting up the database users across a series
of servers.

SQL Injection via Tooling

The only way you can run DDL statements in PostgreSQL is via string
concatenation. This opens up the issue of SQL injection in the tooling
used to create and manage roles. This is particularly true if you have
an automatic job that looks for new tables and ensures ownership is
correct. For example if a malicious user created a table or function with
a problematic name, it might be possible to inject sql into the script. For
example:

Attacking the tools

123

CREATETABLE"users SET schema public; alter role chris with superuser; --"(LIKEusers);

In a case like this, a naive script might run and try to assign ownership
to Postgres by using simple string interpolation:

Interpolate the table name, and lo and behold I am superuser!

123

ALTERTABLEusersSETschemapublic;alterrolechriswithsuperuser;-- owner to postgres;-- this comes from something like "alter table $tablename owner to postgres"

If I then drop the table after becoming superuser, maybe nobody ever notices…

The same trick can be done even if naive escaping of the identifier is done.
In other words, it is no different, really, than any other sort of SQL injection
except that most of the tools we have to combat the problem are not of any use.

PostgreSQL provides functions for escaping identifiers, but these cannot be safely used
inside client-side string interpolation without extra round-trips to the server and they
don’t apply to SQL keywords.

The Goal

The goal, simply, is to be able to use our ordinary SQL injection toolkits
for role management, and to ensure that the complexity of the queries
we are issuing for role creation are kept to an absolute minimum.

In other words, instead of our previous example, it would be better to
do this:

Suddenly all the queries are parameterised, and we can use all our
normal anti-sql-injection tools. Additionally, rather than
dealing with a different syntax for each type of statement, we have
a consistent semantic structure, making tool creation much easier.

Finally in doing this we can place the responsibility for safe operation
to the functions we call and only require that we pass in something
valid. This helps guard against changes in our tools introducing
accidental vulnerabilities.

The PostgreSQL server-side anti-SQL-Injection tools

On the server-side, PostgreSQL provides a very rich set of tools
for preventing SQL injection. These include parameterised queries
where possible (including with dynamic SQL in PL/PGSQL), solid escaping
functions for both literals and identifiers, and some handy data-types
which eliminate SQL injection in some cases. By using these methods
effectively as applicable, we can reduce our original problem to one
which is widely supported (where we pass in string literals into
parameterised queries, even for role management functions).

As we have already noted, parameterised queries, server-side don’t
apply to many of our cases, but there are two cases where they are very
Helpful, namely in error handling and passing control between functions
in the extension.

More frequently we will use escaping functions like quote_literal()
and quote_ident() as well as types which represent strings bound to
catalog entries, such as regclass and regprocedure. These types
not only ensure proper escaping, but when strings are passed into the
user-defined functions, they also ensure that the corresponding
database object exists and is found (numbers cast to OID’s however
are not so checked).

None of these tools address keywords, however. For keywords, we
use a whitelist system. This means we have to add new keywords to the
white lists as they are supported by PostgreSQL but it also means we are
protected against SQL injection issues through this vector.

Initially supported use cases

In addition to our immediate use case, there were a few other
use cases I knew of that I worked on supporting to ensure that
the extension could be of use beyond Adjust. These all fit in with
the idea that roleman should be safe toolkit for managing roles, and
that it should do this job well.

Among others, supported use cases include:

Create simple scripts for creating SQL commands for role management
to be run via psql. This requires restricting identifiers to
the subset that does not require escaping.

Safely manage roles via application code. Applications that want
to delegate security to the database have a safe framework for doing this.

A safe framework for database users to alter their own passwords
with appropriate administrator-supplied security policies. Note
that we are not doing this at Adjust but I know of others who are.

In the first case, you have the added complication that you cannot rely
on the server-side protections against SQL injection to prevent SQL injection
(though you can rely on client-side escaping of string literals). This
is not our responsibility and roleman already makes the situation better
by opening up more tools to be used to assure secure operation in this case.

The third case offers a particular problem, that any function which calls
roleman.set_password() must do so in a security definer context. This means
further that we must test against database object injection, not merely
SQL language injection. In general the security guarantees that supporting
scenario require are worth the extra effort in supporting them. Besides,
someone will probably use the module in this way at some point so it is better
if they don’t run into trouble over it.

SQL object injection is a form of SQL injection that we will discuss in more
detail in the next post, as well as how to prevent it. As of Roleman 0.2.1
we specifically test against a wide range of SQL injection techniques including
injecting objects that shadow expected objects. Version 0.2.1 is believed to
be safe when properly used.

Stay Tuned

I hope you have enjoyed this article.

Next we will discuss the implementation of this extension.
We will also discuss the various techniques used to tighten and
ensure security against a wide range of attacks.

]]>2017-09-01T12:41:00+02:00http://adjust.github.io/2017-09/this-programmer-tried-to-mock-an-http-slash-2-server-in-go-and-heres-what-happenedTesting has always been a must at Adjust. Serving tens of thousands of requests per second, we can’t afford tiny oopsies caused by some pointer being null when we didn’t expect it or a good old side effect in a presumably clean function. Tests are considered first-class citizens in our code base and are mandatory for any pull request that adds or changes functionality.

Just like tests, any dependency that we bring into our projects becomes a part of our code base. Since no one likes maintaining code written by some unknown dude (do you?), we try to reduce the number of third-party packages we use to an absolute minimum and prefer writing or own tailor-made libraries.

This is why we decided to stick with the minimalistic but decent testing package that Go provides as a part of its standard library.

Every test, regardless of programming language, contains four major parts: setup, invocation, assertion, and teardown. Besides providing the framework to run tests using the go tool, testing provides only one of these things–assertion–and leaves the rest up to its user. While the invocation part is usually very specific to implementation and there is not much that could be generalized there, setup and teardown are tightly coupled and can often be split into small reusable blocks that mock external services, provide data fixtures, etc. So we’ll focus on these two parts, specifically on mocking HTTP requests to external services.

Mocking an external HTTP API in tests

A common task in our tests is to verify the interaction with an HTTP API provided by a third party. We don’t want to send all the requests we’re making in our unit tests to an actual server for several reasons:

This would add an extra dependency on the network state between our CI server and the target service

It would be difficult to reproduce the error responses

Any API change would cause our tests to fail. Some would say that this is good rather than bad, but we believe
that test failures should be induced by only our changes.

We’re nice people and wouldn’t want to bother the API providers unless we really have to

Thus we came up with a fairly simple idea to mock http.Client:

testutils/client_mock.go

123456789101112

typeClientMockstruct{Responsesmap[string][]byte}func(m*ClientMock)Get(urlstring)(resp*http.Response,errerror){body,ok:=m.Responses[url]if!ok{// respond with HTTP 404 and return}// build an *http.Response with a copy of body and return it}

Now we only needed to mock the expected requests with canned responses:

This approach worked well for a while, but soon we found ourselves adding more and more functionality to testutils.ClientMock. Sometimes we’d need to add additional cookies to the response, send requests using different HTTP methods, or provide a different response depending on what was sent in the request. The mock became so complex, that we started thinking about writing tests for it.

Nobody was smitten with the idea of writing tests for tests, so we had to rethink our approach. By that time, our client mock looked almost like a limited http.Server without the transport part, so we decided to leave the honorable task of testing mocks to the Go team and came up with the following approach, which is currently used in most of our tests these days:

We’re now using the standard library *http.Client, and, instead of mocking its request methods, we override the host and port of the API server. This way we can redirect every HTTP request to our server mock:

external_service_test.go

1234567891011121314151617181920

funcTestExternalServiceClient_Call(t*testing.T){baseURL,mux,teardown:=testutil.ServerMock()deferteardown()varreqNumintmux.Handle("/",func(whttp.ResponseWriter,req*http.Request){reqNum++// inspect requestw.Write([]byte("Hello, world!"))})c:=ExternalServiceClient{BaseURL:baseURL}result,err:=c.Call()// ...ifexpectedReqNum:=1;reqNum!=expectedReqNum{t.Errorf("ExternalServiceClient.Call() expected to make %d request(s), but it sent %d instead",expectedReqNum,reqNum)}}

Mocking an HTTPS server

For tests that explicitly require HTTPS, we added a similar mock that creates an instance of httptest.Server by calling httptest.StartTLSServer() instead of httptest.StartServer(), while the rest of the code is completely the same as in testutil.ServerMock():

Since we did not provide any certificates at all while creating an httptest.Server instance, there should have been some default one hidden in net/http/httptest. It turned out that the Go standard library contains a self-signed certificate and a private key used by the httptest package. So we needed to make the http.Client trust this certificate:

If you’ve already upgraded to Go 1.9, then you don’t need x509.ParseCertificate() anymore. An instance of httptest.Server now has a Certificate() method that returns an *x509.Certificate used by this server.

All that was left now was to replace the system-default http.Client certificate pool with our own one, which held our server mock certificate:

So what about HTTP/2?

Since Go v1.6, http.Serversupports HTTP/2 out of the box, and we naturally assumed httptest.Server would, too. However, once we configured http.Client to use http2.Transport from golang.org/x/net/http2, our tests that used testutil.ServerMock() began to fail with an unexpected error:

A quick note on ALPN
ALPN or Application-Layer Protocol Negotiation is a TLS extension that allows parties to agree on which protocol should be handled over a secure connection. HTTP/2 uses this feature to avoid additional round trips, and, hence, TLS handshakes, by agreeing on an application protocol during the hello phase. The client provides a list of protocols it supports and the server is expected to choose one and send it back.

So unexpected ALPN protocol ""; want "h2" meant that our server did not know it now supported HTTP/2. There is a method in the http2 library to configure an existing server to support HTTP/2, but it expects an instance of http.Server as an argument, whereas we only had httptest.Server. Passing (httptest.Server).Config as an argument to http2.ConfigureServer() wouldn’t work, because httptest.Server uses Config to serve incoming connections using an already existing tls.Listener that is created when (*httptest.Server).StartTLS() gets called, and ALPN support is implemented by crypto/tls. Thus we needed a way to configure the httptest.Server listener to support "h2" as an application-level protocol.

$GOROOT/src/net/http/httptest/server.go

12345678

typeServerstruct{// ...// TLS is the optional TLS configuration, populated with a new config// after TLS is started. If set on an unstarted server before StartTLS// is called, existing fields are copied into the new config.TLS*tls.Config// ...}

Looks exactly like what we’re looking for! What was left was to apply the same configuration changes as http2.ConfigureServer() does and we’d have a nicely working HTTP/2 mock using Go standard library only:

Here, http2.NextProtoTLS is a constant for the "h2" string we were looking for and tls.TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 is a cipher suite required by the HTTP/2 specification.

Conclusion

Instead of mocking http.Client, mock the server it talks to. The Go standard library offers a very convenient net/http/httptest package that allows you to spawn an HTTP server with only a few lines of code, which can be easily configured to handle HTTP/2 requests.

]]>2016-12-22T18:00:00+01:00http://adjust.github.io/2016-12/http-streaming-in-elixirOne of the Elixir web apps we have at Adjust acts as an API gateway — it receives a request, applies authorization and/or authentication and then passes the request along to an actual requested service. As you probably know, if you’re building microservices, this approach is quite common. In our case, the API gateway also stands before a service that is responsible for generating reports. These reports are usually big json or csv blobs and sometimes they are as big as a few hundred megabytes. Because of this, downloading such a report via API gateway just to send it to a client does not sound like a good idea. Below, you can see what happened to our naïve implementation when a number of clients were trying to download sizeable reports.

In this blogpost, I’d like to describe how we’ve implemented transparent streaming of HTTP requests directly to a client.

In the above screenshot, the “Traffic” graph perfectly illustrates what happens without streaming: an application receives data from a requested service for quite a while (yellow line, “in”), and once all the data is there, it sends it to a client (“out” line). With the streaming approach, there should be no significant gaps between the “in” and “out” lines on this graph, because the API gateway should send a chunk to the client as soon as that chunk is received from the requested service.

Streaming with hackney

Due to past decisions at Adjust, our application already had HTTPoison in its dependencies list, which meant we already had hackney installed in our app, so we decided to try and implement HTTP streaming based on it. hackney provides an async option to receive a response asynchronously, but more importantly it allows us to pass {:async, :once} so we can process the next chunk of a response only when the previous chunk has been processed. HTTP streaming with hackney can be achieved using the following snippet:

defmoduleMyApp.HttpAsyncResponsedo defrun(conn)do{:ok,ref}=:hackney.get(url,[],'',[{:async,:once}])receivedo{:hackney_response,ref,{:status,200,reason}}->async_loop(ref,conn){:hackney_response,ref,{:status,status,reason}}->send_error(reason,conn){:error,status}{:hackney_response,ref,{:error,{:closed,:timeout}}}->send_error(:timeout,conn){:error,408}endenddefpasync_loop(ref,conn)do:ok=:hackney.stream_next(ref)receivedo{:hackney_response,^ref,{:headers,headers}}->conn=Plug.Conn.send_chunked(conn,200)# Here you might want to set proper headers to `conn`# based on `headers` from a response.async_loop(ref,conn){:hackney_response,^ref,:done}->conn{:hackney_response,^ref,data}->casePlug.Conn.chunk(conn,chunk)do{:ok,conn}->async_response(conn,id){:error,:closed}->Logger.info"Client closed connection before receiving the next chunk"conn{:error,reason}->Logger.info"Unexpected error, reason: #{inspect(reason)}"connendendendend

Once a request to a service is sent, hackney starts to send messages to a calling process. After receiving an initial response from the service, the API gateway calls the Plug.Conn.send_chunked/2 function, which sets proper headers and the state to conn. Then, every time the calling process receives a new response chunk, it sends this chunk to a client using Plug.Conn.chunk/2. If the chunk/2 function returns {:error, :closed}, the client most probably just closed a browser tab. send_error/2 here is the custom function, which sends an error to error tracking service.

That code did what we’d hoped and worked well for us in most cases. But soon we noticed that sometimes this code behaved as though it wasn’t streaming data, but instead first accumulated the entire response and then sent it to a client. When this happened, hackney consumed a lot of RAM, making an Erlang node unresponsive.

We spent quite some time investigating the issue and figured out that this behaviour was somehow related to cached responses. The whole investigation and its results deserve a separate blog post. In fact, @sumerman is preparing one with all the details about nginx caching, hackney streaming implementation details and more. Stay tuned!

In the meantime, we decided to replace hackney with ibrowse to see if it made any difference. And it did.

Streaming with ibrowse

For ibrowse there is HTTPotion — a simple Elixir wrapper. We switched all our simple requests without streaming to HTTPotion and implemented streaming with ibrowse for reports, as in the code snippet below.

defmoduleMyApp.HttpAsyncResponsedo defrun(conn,url)do caseHTTPotion.get(url,[ibrowse:[stream_to:{self(),:once}]])do%HTTPotion.AsyncResponse{id:id}->async_response(conn,id)%HTTPotion.ErrorResponse{message:"retry_later"}->send_error(conn,"retry_later")Plug.Conn.put_status(conn,503)%HTTPotion.ErrorResponse{message:msg}->send_error(conn,msg)Plug.Conn.put_status(conn,502)endenddefpasync_response(conn,id)do:ok=:ibrowse.stream_next(id)receivedo{:ibrowse_async_headers,^id,'200',_headers}->conn=Plug.Conn.send_chunked(conn,200)# Here you might want to set proper headers to `conn`# based on `headers` from a response.async_response(conn,id){:ibrowse_async_headers,^id,status_code,_headers}->{status_code_int,_}=:string.to_integer(status_code)# If a service responded with an error, we still need to send# this error to a client. Again, you might want to set# proper headers based on response.conn=Plug.Conn.send_chunked(conn,status_code_int)async_response(conn,id){:ibrowse_async_response_timeout,^id}->Plug.Conn.put_status(conn,408){:error,:connection_closed_no_retry}->Plug.Conn.put_status(conn,502){:ibrowse_async_response,^id,data}->casePlug.Conn.chunk(conn,chunk)do{:ok,conn}->async_response(conn,id){:error,:closed}->Logger.info"Client closed connection before receiving the last chunk"conn{:error,reason}->Logger.info"Unexpected error, reason: #{inspect(reason)}"connend{:ibrowse_async_response_end,^id}->connendendend

As you can see, the snippet for ibrowse looks very similar to the one for hackney. ibrowse gives you a stream_to option as well as the once parameter, which allows you to control when to get the next response chunk. Unfortunately, HTTPotion does not support the stream_to: [{pid, :once}] option directly. Instead, you have to pass it via the ibrowse option, but then all the messages coming from the ibrowse process are not converted to the corresponding HTTPotion structures. That’s why you have to pattern match against raw ibrowse messages.

We found that streaming with ibrowse worked very well. In cases when hackney started to consume a lot of RAM, ibrowse managed to keep memory consumption under control. Even when the gateway streams ~26 megabytes per second, memory usage stays stable around ~250 MB.

Look at the “Traffic” graph: the “in” and “out” lines are so close you can’t even see the green “out” line. Perfect!

Moreover, ibrowse gives you more control on how you want to process and stream chunks. For example there is stream_chunk_size parameter that lets you set your desired chunk size. There is also a spawn_worker_process/1 function, so it’s possible to create a separate worker for streaming per domain. You can find all the possible options in the ibrowsewiki.

HTTP streaming using ibrowse worked so well for us, that we haven’t even had a chance to try gun. According to its documentation, gun has been designed with streaming in mind, so you might like to give it a try.

That’s it for today, folks. Happy streaming!

]]>2016-12-13T11:41:00+01:00http://adjust.github.io/2016-12/fasthttp-clientAt adjust we recently tried to replace the Go standard http library with fasthttp. Fasthttp is a low allocation, high performance HTTP library, in synthetic benchmarks the client shows a 10x performance improvement and in real systems the server has been reported to provide a 3x speedup. The service we wanted to improve makes a very large number of HTTP requests and so we were very interested in using the fasthttp client.

In the course of making the switch we encountered a number of difficulties. First the fasthttp library presents a very different interface to the programmer which must be adjusted to. Second there were a number of quirks in the implementation which made progress rather slow.

Making a simple request

To begin with we would like to learn how to perform a simple HTTP request using the fasthttp client. Below is a very simple request using the Go standard library, error handling has been omitted for brevity.

For all the code snippets below the test server writes the request’s “User-Agent” header value and body into the response on separate lines. We write the actual output of the snippet in comments beneath each print statement.

Getting the response body

The body of an http.Response is exposed as an exported io.ReadCloser field. The body of a fasthttp.Response is exposed via the Body() method call which returns a []byte. The implication of this is that the entire body must be read and a sufficiently large []byte allocated before the body can be processed. This is a surprising feature of a library which prioritises performance and low memory allocations.

One curious aspect of the Body() method is that it returns no error, in contrast to reading from an io.ReadCloser. It would be interesting to see how that method is implemented to get a better idea of how fasthttp works.

The Body() method operates on two unexported fields body and bodyStream. It first checks if bodyStream is non-nil, and if it is, reads from the bodyStream into the body field. Finally the contents of the body field are returned to the caller.

This is pleasantly straightforward, but there is one odd wrinkle, this method will silently eat errors.

Looking at line 15 in the example above we can see that any errors encountered while reading from bodyStream are written into the body field and the original error is not returned. An error could occur, but we would never find out about it. Lets look further into our simple HTTP request example to see how the Body() method would actually execute.

If we trace the execution of our simple request above we find the following execution path:

We can see, on line 3, that the call to readBody(...) sets the bytes bodyBuf.B to be the result of reading from the connection. So the stream reader field will be nil. We can see that errors are being returned from the readBody(...) method call. That’s good, but we have only covered one simple case. From further analysis I do believe that errors are not swallowed by the fasthttp client, but I am not certain. There is a potential execution path which results in errors being silently swallowed.

Making POST requests

Our existing application performs both GET and POST requests. We ran into a small problem making POST requests. We will start with a simple POST example using fasthttp. Here we set our method to POST and fill in the body with some form-encoded values. Now we see both the “User-Agent” and the non-empty request body in the response.

While the standard library http.Client does provide a default “User-Agent” header value, this value is overridden when any other value is provided. Fasthttp is still sending it’s default “fasthttp” and our “Test-Agent” value is not being picked up.

We wanted to get a better look at the headers that were being set, so we added a single debug line println(req.Header.String()). Now we can no longer ignore errors in our code, because that innocent looking req.Header.String() causes client.Do(...) to fail.

When we print the request headers we get to see the preloaded “User-Agent: fasthttp” header value is still stored, and in particular ahead of our “Test-Agent” value. This certainly explains why we aren’t seeing our value. We will look into why this is happening after we deal with the request error.

Why do our requests return an error?

After adding a simple println statement we now get the error “Error: non-zero body for non-POST request. body=”p=q”“. The client now seems to believe that our request is not a POST. The critical call path here is

The method IsGet() reads the RequestHeader.method field and sets the RequestHeader.isGet cache field, to speed up future method calls. Unfortunately at this point we haven’t set our method and in the absence of any value it defaults to GET. So RequestHeader.isGet is set to true, which causes future calls to IsGet() to return true regardless of the value the RequestHeader.method field. Critically this method is also called inside HostClient.doNonNilReqResp(...) to test whether the request should have an empty body or not, causing the error we see above.

It’s worth noting that the call path contains 4 exported methods, any one of which would create the same confusing behaviour. You must be very careful to call req.Header.SetMethod(...) early if you intend to make POST requests.

Why is fasthttp sending its default “User-Agent”

Interestingly it looks like the unexpected “fasthttp” user-agent is a bug that is also caused by caching. If we look at the RequestHeader.AppendBytes(...), which builds the header args, it performs the following check

We can see, on line 13, that the userAgent value is taken from the field RequestHeader.userAgent, we could quickly confirm that our preferred header value “Test-Agent” was held inside a field RequestHeader.h but is completely missed by the call to h.parseRawHeaders which looks inside RequestHeader.rawHeaders. This complex arrangement of headers and various cached values makes interacting with headers a true minefield of unexpected behaviour.

Should You Use Fasthttp

It’s a difficult question to answer. Fasthttp does reduce allocations and I have no doubt it will bring significant benefits to some systems, particularly those performing high volume HTTP requests and not much else. Garbage collection is not free, and fasthttp could bring real performance improvements, and potentially reduce your hardware requirements. But, fasthttp is not simple and it appears that fasthttp has been built primarily for use on servers.

The client implementation reuses the data structures used on the server, this means, for example, that the fasthttp.Response used by the client contains a very large amount of code which is only useful to a server. This makes understanding the codebase and debugging any problems much harder.

The high level of complexity and the likelihood that the fasthttp client has not been extensively used in production means that you would need to expect a very large benefit to justify the adoption of fasthttp today.

Thanks

We would like to thank Valyala and other contributors for making a high performance http library available for Go. We know it is no small task.

]]>2016-06-14T11:13:00+02:00http://adjust.github.io/2016-06/how-we-deploy-elixir-appsWe at adjust recently started to use Elixir. We built a couple of small services using the Phoenix framework which successfully went live. In this blogpost I’d like to talk about, I’d say, the most undiscussed topic when it comes to Elixir — deployment.

Although you can find some blog posts about deploying Elixir applications, usually after reading them, it still remains unclear how to get the desired command which would deploy your code to production - and which would automate all the routines.

Capistrano way

The first thing we’ve tried was mina. I’d say, trying to use Capistrano or Mina is an obvious choice if you come from the Ruby world. However, it becomes clear very quickly that the Capistrano way doesn’t fit well for Elixir apps. As you probably know, the preferred way to deploy Elixir applications is to use releases, which means you need a place where a release should be built. It’s possible to write a Capistrano or Mina recipe to clone a project to the production host and build the release there, but that wouldn’t be very good idea. Compiling and building a release will take some resources (especially memory) which you don’t want to share on production.

Another option would be to build a release locally using the cross-compiling feature and copy it to production. There are a few gotchas with such approach:

there might be some differences in environment (dependency versions, elixir version, etc) between different developers’ computers, so two developers might build two different builds based on the same codebase;

it would be quite tricky to write such a recipe for Capistrano (although much easier for Mina); generally, using Capistrano just to copy one tarball to a server, unpack it and start it looks like overkill.

So using releases means that there should be a machine where every developer can build a release. Right, a build server! And the problem is that the concept of a build server isn’t something familiar for Capistrano or Mina. So there should be a tool which is aware of the concept of a build server, which maybe even knows how to work with Elixir releases…

Thankfully such a tool does indeed exist.

Edeliver

Edeliver is a deployment tool for Elixir and Erlang projects. It knows how to work with releases and how to apply hot-upgrades, it’s aware of a build host and helps you to automate the deployment workflow. Edeliver has very good and comprehensive documentation, including several wiki pages describing some edge cases as well. I don’t want to review edelivers README in this blogpost, but rather I’d like to cover some of those edge cases and gotchas which we’ve discovered while using it.

Auto Versioning

There is a small issue with release names — they must be unique, so every time the mix edeliver build release command finishes, a unique release should be generated. Edeliver solves this issue by having a special config parameter with which it’s possible to append a Git revision, Git branch, build date, etc to a release name. So you don’t need to go to the mix.exs file and change version in project/0 function – edeliver does it for you. We found that AUTO_VERSION=git-branch+git-revision generates sufficiently unique release names. With this combination a release name would be something like “awesome_adjust_app_0.0.1+master-01b4601.release.tar.gz”.

Custom environments

By default edeliver provides only two environments to which it’s possible to deploy — staging and production. There is no easy way to add custom environments, but as it turned out it’s still possible to achieve that by overriding STAGING_HOSTS and STAGING_USER variables in .deliver/config.

Let’s say we want to add beta and qa environments. To do so .deliver/config should look like this:

With this config, it would be possible to deploy a release to the beta and qa hosts (in addition to staging and production) and to maintain these custom hosts. For example, in order to check the version of the beta host, you’d run a command like this: mix edeliver version beta.

Deploy notifications

It’s quite common to send notifications about successful deployments. For example, we might display such notifications in a Slack channel. edeliver has hooks which can be implemented as bash functions. For example, there are two hook functions: pre_upgrade_release() and post_upgrade_release(). They are called exactly before applying an upgrade and right after an upgrade has been applied, respectively. Notifications about deployment usually contain information about the person who deployed, the Git branch and revision, and the environment name (staging/production).

The issue here is that you can’t get a Git branch and Git revision out of a release since a release is just a binary. With Capistrano, you can just run a couple of git commands on the target host to get the necessary data. With edeliver it becomes a bit more tricky. The current workaround we use is to include the Git revision and Git branch into a release name using the following config: AUTO_VERSION=git-branch+git-revision. This is as I described in the previous section on Auto-Versioning. Then in the project itself a Notifier module might look as follows:

123456789101112131415161718192021222324252627282930313233

defmoduleMyApp.Notifierdo defnotify(username,env,event)do{branch,revision}=:my_app|>Edeliver.release_version|>to_string|>extract_git_infobuild_notification(event,username,hostname,revision,branch,env)|>send_notificationenddefpextract_git_info(release_version)do[_,branch_revision]=String.split(release_version,"+")list=String.split(branch_revision,"-")rev=List.last(list)branch=List.delete_at(list,-1)|>Enum.join("-"){branch,rev}enddefpbuild_notification(event,username,hostname,revision,branch,env)do# create a map/list/keyword with necessary data for a notificationenddefpsend_notification(notification)do# send a HTTP request, create a job, etc enddefphostnamedoSystem.cmd("hostname",[])|>elem(0)|>String.stripendend

Then the pre_upgrade_release() and post_upgrade_release() hooks might look like this:

However, there are two flaws here. First, it works only when applying upgrades - not for releases. And second, when calling Elixir.MyApp.Notifier from pre_upgrade_release, Edeliver.release_version returns a git revision of the currently deployed release. So ‘deploying’ notification would have a git revision of the currently deployed version and the ‘deployed’ notification would have a git revision of the new version.

Different configurations on different deploy hosts

Most probably, your application has different settings for staging and production environments. Which means that you need either to build a release for each environment separately or somehow provide different settings on different hosts for the same release. Edeliver, following a philosophy “build once, deploy everywhere” suggests to solve this problem by using LINK_SYS_CONFIG or LINK_VM_ARGS config variables as described on this wiki page.

I’ll describe briefly how it works with LINK_VM_ARGS variable. The logic is the same for LINK_SYS_CONFIG. So it works as follows: you need to create a file which should have the same path on both staging and production hosts with config values specific for the target host. This could be /home/deploy_user/my_app/vm.args, for example. Then in .deliver/config you can specify LINK_VM_ARGS=/home/deploy_user/my_app/vm.args.

When making a release or an upgrade, edeliver would put a symlink inside a release (instead of the real generated vm.args) which will point to /home/deploy_user/my_app/vm.args. So this tricky and sophisticated approach solves the issue. In theory. I couldn’t actually make it work. After a release deployment I see a symlink as expected, but on release start, my custom symlinked vm.args file should replace vm.args from running-config which does not happen. However, if I remove the running-config folder first and start a release afterwards, it works.

So since this approach didn’t fully work, we decided to build a release per environment, which is also suboptimal:

you need to build a release per environment

it violates a release philosophy: build once, deploy everywhere

error prone: somebody can by mistake deploy a release on production, which has been built for staging

To partially fix the last bullet from the list above it’s possible to add a mix-env parameter to the AUTO_VERSION config value: AUTO_VERSION=git-branch+git-revision+mix-env. So every build would have -environment in its name to indicate for which environment a release has been built.

Usually, for Phoenix applications secret production settings (like database connection credentials for production DB) are stored in prod.secret.exs. This file is not under version control, but it should be inside a release. To achieve that you might want to put this file manually into the build host, but the issue here is that a folder where a project is built is cleaned by edeliver before every release build. The ‘cleaning’ means that everything which is not under version control will be removed before every build, so config/prod.secret.exs will be gone. To avoid that there is an option to explicitly instruct edeliver which folders should be cleaned. Having the config option GIT_CLEAN_PATHS="_build rel deps" tells edeliver to clean _build, rel and deps folders before every release build, so config folder stays untouched and therefore prod.secret.exs stays alive between release builds.

Alternatives

Currently, there are not so many alternatives to edeliver. But there is at least one: dicon. It’s in the early stages of development, it doesn’t have comprehensive readme, it’s not aware of build host and it does not support hot-upgrades yet. However, Digital Conveyor has some niceties: it’s written completely in Elixir, it’s small and it supports configurations per target host out of the box. It will be interesting to see how dicon will be evolving.

Conclusion

Edeliver is a great, ready-to-use deployment tool packed with a lot of useful features. It works with releases, supports hot-upgrades and build host concept, has very good documentation and gives you simple commands to automate deployment routines. Importantly, the project is in active development. I’d like to thank bharendt for amazing support, almost every tip or trick I’ve described in the post is a result of a detailed answer from him to an opened issue. Sometimes I had a feeling that I’m literally chatting with him in the Issues tab, that’s amazing.

That’s it for today. Happy deploying!

]]>2016-02-16T14:50:00+01:00http://adjust.github.io/2016-02/dive-into-deep-linkingIn the mobile world, deep linking is a technology that launches an app and opens a specific page once a user clicks a URL on a web page or in another app. We will dive into the details of implementing deep linking for your app in this article.

Why do you need deep linking?

Let’s assume you’ve published a music app. To celebrate the release of a new song, you’ve paid tons of money to run a campaign on a popular website. In your campaign, you feature a brief sample of the song – and you probably want the user to
listen to the sample inside of your app rather than on your website, where they would only see the album cover. In another example, let’s say you want to regain inactive users through a sales campaign. In this campaign, users would be directed to the sale products page in your app with a single click, without having to search for it or manually type a coupon code. This is where deep links come into play: in both examples, deep linking makes these campaigns possible.

In short, deep linking brings seamless user experience and can increase
your conversion rate and retention rate significantly.
More information on the effects of deep linking in campaigns can be found on our company blog.

How do you implement deep links?

I won’t dive into how to implement deep links. Both
scheme-based deep linking (Android
and iOS) and iOS 9+ Universal Link are fully documented. The basic ideas are quite similar: associate a URL (scheme based youapp:// or universal link https://yourdomain.com/) with your app and when the URL is clicked, the system will open the app if it’s installed.

But the world isn’t perfect

You’re probably wondering, “What if someone clicks on a deep link URL but doesn’t have the app installed?”
Unfortunately, they’ll either see an error message or nothing will happen.
This is the problem we’re going to discuss in this article.

If your app is installed, then it will be opened and the following JavaScript
won’t run.

If your app is not installed, then nothing will happen while loading yourapp://path/. After 2 seconds, the page will be redirected by the JavaScript to to the Play Store,
and the user can install the app from there.

The above code has a little problem, though – after the app is opened and the user switches back to their browser, the JavaScript may continue and redirect them back to the Play Store. So we can do some optimization by checking the time
a user switches back to their browser in order to determine whether they need to be redirected to the store or not:

Intent solution

Since Chrome for Android version 25 and later, the above code stopped working
according to Chrome documentation.
Fortunately, Google provides theIntent URL for a better solution. When a user
clicks on the URL intent://path/#Intent;scheme=yourapp;package=com.yourapp.example;end, then

if the app is installed, it will be opened by Chrome.

if the app is not installed, Chrome will open Play Store.

Which solution should I use?

The Intent solution is highly recommended because it’s much simpler to implement and the user experience is
more seamless. However, it requires browser support, and the Android system is unfortunately
so fragmented that there are still plenty old OSes and browsers out there.
Moreover, the Android WebView used by tons of apps don’t support Intent URLs by default.
The following table shows which solution you should use for mainstream Android browsers:

if the app is installed, the first relocation code will open the app and the
following script won’t run.

if the app is not installed, the first relocation code will do nothing and the
timeout function will redirect to App Store.

But as we discovered, this script works well in iOS 8 or below with Safari but doesn’t always work with other versions. Here is the table:

Browser

JavaScript

iOS 8 or below Safari

√

iOS Chrome

√

iOS 8 Facebook in-app Browser

√ *

iOS 8 Twitter in-app Browser

iOS 9 or above

* partially working depends on Facebook app Version

Universal link solution

Starting with iOS 9, Apple published the universal link,
which works similar to Android’s Intent but requires more setup. And
moreover, since iOS 9.2, the JavaScript solution stopped working since Apple made
the prompt window non-modal. You can read more about this here.

In order to enable universal links, you need to have a SSL certificated domain
(https://yourdomain.com/, for example) associated with your app, and to serve a
special JSON file under https://yourdomain.com/apple-app-site-association
similar to:

This file tells your device which path serves as deep link for which app.

Then, in XCode you need to enter applinks:yourdomain.com in your
com.apple.developer.associated-domains entitlement
.
One domain can be associated with multiple apps and vice versa.

Next, you need to adopt the UIApplicationDelegate methods for Handoff
(specifically application:continueUserActivity:restorationHandler:) so that
your app can receive a link and handle it appropriately.

Let’s assume you associate https://yourdomain.com/dress/* with your app by
setting "paths": [ "/dress/*"] in the JSON file. When user clicks the link
https://yourdomain.com/dress/1 in Safari,

if the app is installed, your app will be opened and
https://yourdomain.com/dress/1 will be passed to UIApplicationDelegate.
You can handle it there to decide which View to open.

if the app is not installed,https://yourdomain.com/dress/1 will be opened with
Safari and you can still display the product on your website or redirect the
user to App Store

Universal links sound like a perfect solution for iOS. But again,
unfortunately, they have their limitations.

Universal links only work with Safari and Chrome

When another site redirects with a universal link, it
works only if the click happens within Safari and Chrome. For instance, if
there is a link in your Email app https://anotherDomain.com/ redirecting to the universal link https://yourDomain.com/dress/1, it won’t deeplink into your App. But if the link https://anotherDomain.com is clicked from Safari, it works.

Universal links won’t work if you paste the link directly into address bar.

Universal links won’t work if the redirect is triggered by JavaScript.

Universal links won’t work when you open the link programmatically inside
your app (with openUrl, for instance)

Welcome to the world of deep links

Deep linking is complicated – there is no silver bullet that works in all scenarios. Fortunately, Adjust will detect all those
scenarios and use the best strategy to make deep linking functional. You can read more about Adjust deep linking here and ping support@adjust.com if you have more questions.

]]>2016-02-10T17:10:00+01:00http://adjust.github.io/2016-02/istore-documents-for-analytical-workloadsInspired by the PostgreSQL key/value data-type hstore, we developed the istore extension with
support for operators like + and aggregates like SUM for semi-structured integer-based data.

While the hstore allows arbitrary textual-data as its keys and values, in an istore document
both keys and values are represented and stored as integers. Therefore istore fits nicely in an
analytical workload. User journeys, cohort or funnel data, distributional data and many other
scenarios can be efficiently modeled and stored in PostgreSQL using istore.

The extension comes with two data types: istore and bigistore, the former having int and the
latter bigint as values; keys being int for both. This article demonstrates the efficiency of
istore and some of its applications through two examples - aggregating logs and analyzing event
funnels.

This creates a sample table with 5M rows. In each row, you store the information that on day X
in segment Y, the event Z hit count number of times, and this brought revenue amount of
revenue.

Let’s now say you want to look at the hit-counts per event ID and revenue per event ID
distributions for each (date, segment) pair.

You could define a table with two istore fields per (date, segment). The first field would have event
IDs as keys and hit-counts as values, and the other field would have event IDs as keys and revenue as values:

SQL aggregation and division of istore documents

Typically, you’d be interested in aggregated
distributions for all event IDs instead of just a single event ID. Let’s say you want the revenue per single event-hit for each
event ID. With the non-istore setup, you could write:

This illustrates how you can use the SQL SUM aggregate-function to perform aggregations on
istore data. The result from the SUM application would be an istore with event IDs as keys and the revenues and counts as values, respectively. The istore / istore division operator will subsequently
result in an istore with event IDs as keys and the desired ratios as values.

If you prefer the result as a set instead of an istore, you can simply apply the each(istore)
on the result from the division, the same way you would with an hstore.

Note again the improved efficiency of the istore v.s. non-istore data model.

Filtering istore documents

Suppose you want a report of all segments that triggered event ID 5 at least once, but never triggered
event ID 100.

Using the istore ? integer operator to check if a given key exists in an istore might be
intuitive from using PostreSQL’s hstore or json types. And the compact(istore) function
returns an istore with all pairs with value 0 removed.

Sum up istore values together

Suppose you now need the total count of events hit by all segments in all time.

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.

]]>2015-11-06T15:11:00+01:00http://adjust.github.io/2015-11/writing-postgres-extensions-part-ivIn 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.

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.

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

externDatumint84(PG_FUNCTION_ARGS);

So we cannot directly call this function from our code. Instead, we have to use the
DirectFunctionCall macros from fmgr.h:

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.

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.

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.

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.

1234

If endptr is not NULL, strtol() stores the address of the first invalid
character in *endptr. If there were no digits at all, however, strtol()
stores the original value of str in *endptr.
(Thus, if *str is not `\0' but **endptr is `\0' on return, the entire string was valid.)

UPDATEbase36_testSETval='-aa'whereval='3c';SELECT * FROM base36_test;ERROR: negative values are not allowedDETAIL: value -370 is negativeHINT: make it positive

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:

1234

SELECT'-aa'::base36;ERROR: negative values are not allowedDETAIL: value -370 is negativeHINT: make it positive

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

base36.c

1234567891011121314151617181920212223242526

PG_FUNCTION_INFO_V1(base36_in);Datumbase36_in(PG_FUNCTION_ARGS){int64result;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 base36: \"%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_DATUM(DirectFunctionCall1(int84,result));}

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.

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:

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.

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:

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.

]]>2015-10-30T14:38:00+01:00http://adjust.github.io/2015-10/writing-postgres-extensions-part-iiiIn the last post about Writing Postgres Extensions we created a new data type base36 from ground up. However we left with a serious bug causing our server to crash.

Now let’s hunt that bug down with a debugger and complete the testsuite.

We created a dedicated github repo following the content from these series on writing PostgreSQL extensions. The code from the
last article could be found on branch part_ii and
today’s changes are on branch part_iii.

The Bug

First let’s reproduce the bug.

123456789

test=#CREATEEXTENSIONbase36;test=#CREATETABLEbase36_test(valbase36);test=#EXPLAINSELECT*FROMbase36_testwhere'3c'::base36>val;server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.The connection to the server was lost. Attempting reset: Failed.Time: 680,225 ms!>

We definitely don’t want this to happen on our production database,
so lets find out where the problem is.
We only wrote two relatively simple C-functions
base36_out and base36_in. If we assume that we are not smarter than the folks from the PostgreSQL-Core team - which is at least for me personally a reasonable assumption - then the bug must be in one of these.

Set up debugging environment

In order to use a debugger such as LLDB you’ll need to compile PostgreSQL with
debug symbols. The following short guidance through debugging works for me on MacOS
having PostgreSQL installed with homebrew and using LLDB with Xcode.

Firstly, let’s shut down any running Postgres instances - you don’t want to mess up your existing DB or work :)

Add pgsql/bin path from the new installation to the PATH environment variable

1

$ export PATH=/usr/local/pgsql/bin:$PATH

Install the extension (due to the export above this time pgxn from the new installation is used).

1

$ make && make install

Now we can create a test db

1

$ /usr/local/pgsql/bin/createdb test

and connect to it

1

$ /usr/local/pgsql/bin/psql test

Check if it works – well or not

123456789101112

test=#CREATEEXTENSIONbase36;CREATE EXTENSIONtest=#CREATETABLEbase36_test(valbase36);CREATE TABLEtest=#INSERTINTObase36_testVALUES('123'),('3c'),('5A'),('zZz');INSERT 0 4test=#EXPLAINSELECT*FROMbase36_testwhereval='3c';server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.The connection to the server was lost. Attempting reset: Failed.!>

Debugging

Now that we have our debugging environment setup, let’s start the actual chasing of the problem. Firstly, let’s look at the log file. That’s the file we
specified with the -l flag to pg_ctl. In our case /usr/local/pgsql/data/postmaster.log.

1234567891011121314151617

TRAP:FailedAssertion("!(pointer == (void *) (((uintptr_t) ((pointer)) + ((8) - 1)) & ~((uintptr_t) ((8) - 1))))",File:"mcxt.c",Line:699)LOG:serverprocess(PID6515)wasterminatedbysignal6:AborttrapDETAIL:Failedprocesswasrunning:EXPLAINSELECT*FROMbase36_testwhereval='3c';LOG: terminating any other active server processesWARNING: terminating connection because of crash of another server processDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.HINT: In a moment you should be able to reconnect to the database and repeat your command.LOG: all server processes terminated; reinitializingLOG: database system was interrupted; last known up at 2015-10-09 15:11:18 CESTLOG: database system was not properly shut down; automatic recovery in progressLOG: redo starts at 0/22D0868LOG: record with zero length at 0/2359140LOG: redo done at 0/2359110LOG: last completed transaction was at log time 2015-10-09 15:12:01.344859+02LOG: MultiXact member wraparound protections are now enabledLOG: database system is ready to accept connectionsLOG: autovacuum launcher started

Reconnect to the database and find out the pid of your current db session

Ok what do we have? The exception is thrown in pfree which is defined in mcxt.c:699.
pfree is called from get_const_expr in ruleutils.c:8002 and so forth.
If we go four times up the call stack. We’d end up here:

Postgres uses pfree to release memory from the
current memory context. Somehow we messed up our memory.

Let’s take a look at the pointers content

12

(lldb) print (char *)pointer
(char *)$0= 0x00007ff9e1813674 "3c"

It’s indeed our search condition 3c. So what did we do wrong here?
As mentioned in the first article pfree and palloc are Postgres
counterparts of free and malloc to safely allocate and free memory in the
current memory context. Somehow we messed it up.
In base36_out we used

1

char*buffer=palloc0(7*sizeof(char));

to allocate 7 bytes of memory. Finally we return a pointer

1

PG_RETURN_CSTRING(&buffer[offset]);

at offset 4 in this case. The assertion in mcxt.c:699

1

Assert(pointer==(void*)MAXALIGN(pointer));

Makes sure that the data to be released are correctly aligned. The
condition here is:

Wow that is relatively easy. As integer and base36 are binary coercible (that is
the binary internal representations are the same) the conversion can be done for free
(WITHOUT FUNCTION). We also marked this cast as IMPLICIT thus telling postgres
that it can perform the cast automatically whenever suitable.
For example consider this query:

12345

test=#SELECT10::integer+'5a'::base36; ?column?---------- 200(1 row)

There is no integer + base36 operator defined but by implicit casting base36
to integer Postgres can use the integer + integer operator and give us the
result as integer. However implicit casts should be defined with care as the result
of certain operations might be suspicious. For the above operation a user wouldn’t
know if the result is integer or base36 and thus might misinterpret it.
Queries will totally break if we later decide to add an operator integer + base36
which returns base36.

Even more confusing might be this query result:

12345

test=#SELECT-50::base36; ?column?---------- -50(1 row)

Although we disallowed negative values we get one here how is that possible?
Internally Postgres does this operation:

1234

test=#SELECT-(50::base36)::integer; ?column?---------- -50

We can and should avoid such a confusing behavior. One option would be to add
a prefix to base36 output (like it is common for hex or octal numbers) or by giving
the responsibility to the user and only allow explicit casts.

Another option to clarify things would be to mark the cast AS ASSIGNMENT.
With that casting would only be automatically performed if you assign an integer
to a base36 type and vice versa. This is typically suitable for INSERT or UPDATE
statements. Let’s try this:

More to come…

You have seen how important it is to test everything, not only to find bugs that
in the worst case might crash the server, but also to specify the expected output
from certain operations such as casts. In the next post we’ll elaborate on that
creating a full-coverage test suite.

]]>2015-10-23T13:16:00+02:00http://adjust.github.io/2015-10/writing-postgres-extensions-part-iiIn the last post about Writing Postgres Extensions, we covered the basics of extending PostgresSQL with extension. Now it’s time for the fun part – developing our own type.

A small disclaimer

It’s in your best interest to resist the urge to copy and paste the code found within this article. There are some serious bugs along the lines, which were intentionally left in for illustrative purposes. If you’re looking for a production-ready base36 type definition, then take
a look at here.

A refresher on base36

What we’re after is the solid implementation of a base36 data type to use for storing and retrieving base36 numbers.
We already created the basic skeleton for our extension, including base36.control and Makefile, which you can find in the GitHub repo dedicated to this series of blog posts.
You can check out what we ended up with in Part 1
and the code from this post can be found on the part_ii branch.

Custom data type in Postgres

Let’s rewrite the SQL script file to show our own data type:

base36–0.0.1.sql

123456789101112131415161718

-- complain if script is sourced in psql, rather than via CREATE EXTENSION\echoUse"CREATE EXTENSION base36"toloadthisfile.\quitCREATEFUNCTIONbase36_in(cstring)RETURNSbase36AS'$libdir/base36'LANGUAGECIMMUTABLESTRICT;CREATEFUNCTIONbase36_out(base36)RETURNScstringAS'$libdir/base36'LANGUAGECIMMUTABLESTRICT;CREATETYPEbase36(INPUT=base36_in,OUTPUT=base36_out,LIKE=integer);

This is the minimum required to create a base type in Postgres:
We need the two functions input and output that tell Postgres how to convert the
input text to the internal representation (base36_in) and back from the internal representation
to text (base36_out).
We also need to tell Postgres to treat our type like integer. This can also be achieved by specifying these additional parameters in the type definition as in the example below.

test=#SELECT*FROMbase36_testORDERBYval;ERROR: could not identify an ordering operator for type base36LINE 1: SELECT * FROM base36_test ORDER BY val; ^HINT: Use an explicit ordering operator or modify the query.

Hmmm… looks like we missed something.

Operators

Keep in mind that we’re dealing with a completely bare data type. In order
to do any sorting, we need to define what it means for an instance of the
data type to be less than another instance, for it to be greater than another instance
or for two instances to be equal.

This shouldn’t be too strange – in fact, it resembles how you would include the
Enumerable mixin in a Ruby class or implement the sort.Interface in a Golang type
to introduce the ordering rules for your objects.

Wow…that’s a lot.
To break it down: First, we defined a comparison function to power each comparison operator (<, <=, =, >= and >). We then put them together
in an operator class that will enable us to create indexes on our new data type.

For the functions themselves we could simply reuse the corresponding,
built-in functions for the integer type:
int4eq, int4ne, int4lt, int4le, int4gt, int4ge, btint4cmp and hashint4.

Now let’s take a look at the operator definitions.

Each operator has a left argument (LEFTARG), a right argument (RIGHTARG), and a
function (PROCEDURE).

So, if we write:

12345

SELECT'larg'::base36<'rarg'::base36; ?column?---------- t(1 row)

Postgres will use the base36_lt function and do a base36_lt('larg','rarg').

COMMUTATOR and NEGATOR

Each operator also has a COMMUTATOR and a NEGATOR (see Line 52-53).
These are used by the query planer to do optimizations.
A commutator is the operator that should be used to denote the same result, but with the arguments flipped.
Thus, since (x < y) equals (y > x) for all possible values x and y,
the operator > is the commutator of the operator <. For the same reason
< is the commutator of >.

The negator is the operator that would negate the boolean result of an operator.
That is, (x < y) equals NOT(x >= y) for all possible values x and y.

So while COMMUTATOR and NEGATOR clauses are not strictly required in a custom Postgres type
definition, without them the above rewrites won’t be possible. Therefore, the respective queries
won’t use the index and in most situations lose performance.

RESTRICT and JOIN

Luckily, we don’t need to write our own RESTRICT function (see Line 54-55) and can use simply use this:

1234

eqselfor=neqselfor<>scalarltselfor<or<=scalargtselfor>or>=

These are restriction selectivity estimation functions which give Postgres a hint
on how many rows will satisfy a WHERE-clause given a constant as the right argument.
If the constant is the left argument, we can flip it to the right using the commutator.

You may already know that Postgres collects some statistics of each table when you or the autovacuum daemon run an ANALYZE. You can also take a look at these statistics on the
pg_stats view.

1

SELECT*FROMpg_statsWHEREtablename='base36_test';

All the estimation function does is to give a value between 0 and 1, indicating the
estimated fraction of rows based on these statistics. This is quite important to know
as typically the = operator satisfies fewer rows than the <> operator. Since you are relatively
free in naming and defining your operators, you need to tell how they work.

If you really want to know what the estimation functions look like, take a look at the source code in src/backend/utils/adt/selfuncs.c. Disclaimer: your eyes might start bleeding.

So, it’s pretty great that we don’t need to write our own JOIN selectivity estimation function. This one
is for queries where an operator is used to join tables in the form table1.column1 OP table2.column2, but it has essentially the same idea: it estimates how many rows will be returned by the
operation to finally decide which of the possible plans (i.e. which join order) to use.

Here table3 has only a few rows, while table1 and table2 are really big. So it makes sense
to first join table3, amass a few rows and then join the other tables.

HASHES and MERGES

For the equality operator, we also define the parameters HASHES and MERGES (Line 35).
When we do this, we’re telling Postgres that it’s suitable to use this function for hash to respectively
merge join operations. To make the hash join actually work, we also need to define a
hash function and put both together in an operator class.
You can read further in the PostgreSQL Documentation about the different Operator Optimization clauses.

More to come…

So far you’ve seen how to implement a basic data type using INPUT and OUTPUT functions.
On top of this we added comparison operators by reusing Postgres internals. This allows us to order
tables and use indexes.

However, if you followed the implementation on your computer step-by-step, you might find
that the above mentioned EXPLAIN command doesn’t really work.

1234567

#EXPLAINSELECT*FROMbase36_testwhere'c1'::base36>val;server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.The connection to the server was lost. Attempting reset: Failed.Time: 275,327 ms!>

That’s because right we just did the worst possible thing: in some situations, our code makes the whole
server crash.

In the next post we’ll see how we can debug our code using LLDB, and how to avoid these
errors with the proper testing.

]]>2015-10-16T09:23:00+02:00http://adjust.github.io/2015-10/writing-postgres-extensions-part-iPostgres has a ton of features and offers a wide range of data types,
functions, operators, and aggregates. But sometimes it’s just not enough for your
use case. Luckily, it’s easy to extend Postgres’ functionality through extension.
So why not write your own?

This is the first in a series of articles about extending Postgres through extensions.
You can follow the code examples here on branch part_i

base36

You might already know the trick used by url shorteners. Use some unique random characters
such as http://goo.gl/EAZSKW to point to something else. You have to remember what points to where, of course, so you need to store it in a database.
But instead of
saving 6 characters using varchar(6) (and thus wasting 7 bytes) why not use an integer
with 4 bytes and represent it as base36?

The Extension Skeleton

To be able to run the CREATE EXTENSION command in your database, your extension needs at least two files: a control file in the format extension_name.control, which tells Postgres some basics about your extension, and a extension’s SQL script file in the format extension--version.sql.
So let’s add them into our project directory.

As of now, our extension has no functionality. Let’s add some in an SQL
script file:

base36–0.0.1.sql

123456789101112131415161718192021222324252627

-- complain if script is sourced in psql, rather than via CREATE EXTENSION\echoUse"CREATE EXTENSION base36"toloadthisfile.\quitCREATEFUNCTIONbase36_encode(digitsint)RETURNStextLANGUAGEplpgsqlIMMUTABLESTRICTAS$$DECLAREcharschar[];retvarchar;valint;BEGINchars:=ARRAY['0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'];val:=digits;ret:='';WHILEval!=0LOOPret:=chars[(val%36)+1]||ret;val:=val/36;ENDLOOP;RETURN(ret);END;$$;

The second line ensures that the file won’t be loaded into the database directly,
but only via CREATE EXTENSION.

The simple plpgsql function allows us to encode any integer into its base36
representation.
If we copied these two files into postgres SHAREDIR/extension directory, then we could start using the extension with CREATE EXTENSION.
But we won’t bother users with figuring out where to put these files and how to
copy them manually – that’s what Makefiles are made for. So, let’s add one to our project.

Makefile

Every PostgreSQL installation from 9.1 onwards provides a build infrastructure for extensions
called PGXS, allowing extensions to be easily built against an already-installed
server. Most of the environment variables needed to build an extension are
setup in pg_config and can simply be reused.

Write tests

These days, every serious developer writes tests. And as database developer who
deals with data (probably the most valuable thing in your company) you should as well.

You can easily add some regression tests to your project that can be invoked
by make installcheck after doing make install. For this to work you can put
test script files in a subdirectory named sql/. For each test file there should
also be a file containing the expected output in a subdirectory named expected/
with the same name and the extension .out. The make installcheck command executes
each test script with psql, and compares the resulting output to the matching expected file.
Any differences will be written to the file regression.diffs.
Let’s do so:

If we now run make install && make installcheck, then our tests would fail. This is because we didn’t specify the expected output. However, we’d find the new directory results, which would contain base36_test.out and base36_test.out.diff.
The former contains the actual output from our test script file. Let’s move
it into the desired directory.

You’ll notice that in line 6, base36_encode(0) returns an empty string where we’d expect 0. If we fix our
expectation, our test would fail again.

123456789101112

============== running regression test queries==============test base36_test ... FAILED====================== 1 of 1 tests failed.
======================The differences that caused some tests to fail can be viewed in the
file "regression.diffs". A copy of the test summary that you see
above is saved in the file "regression.out".
make: *** [installcheck] Error 1

And we can easily inspect the failing test by looking at the mentioned regression.diffs

Now let’s implement the fix in the encoding function to make the tests pass again (Line 12-14):

base36–0.0.1.sql

123456789101112131415161718192021222324252627282930

-- complain if script is sourced in psql, rather than via CREATE EXTENSION\echoUse"CREATE EXTENSION base36"toloadthisfile.\quitCREATEFUNCTIONbase36_encode(digitsint)RETURNScharactervaryingLANGUAGEplpgsqlIMMUTABLESTRICTAS$$DECLAREcharschar[];retvarchar;valint;BEGINIFdigits=0THENRETURN('0');ENDIF;chars:=ARRAY['0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'];val:=digits;ret:='';WHILEval!=0LOOPret:=chars[(val%36)+1]||ret;val:=val/36;ENDLOOP;RETURN(ret);END;$$;

Optimize for speed, write some C

While shipping related functionality in an extension is a convenient way to share
code, the real fun starts when you implement stuff in C.
Let’s get the first 1M base36 numbers.

PG_MODULE_MAGIC is the “magic block” needed as of PostgreSQL 8.2 in one (and only one) of the module source files after including the header fmgr.h.

PG_FUNCTION_INFO_V1(base36_encode); introduces the function to Postges as Version 1 Calling Convention, and is only needed if
you want the function to interface with Postgres.

Datum is the return type of every C-language Postgres function and can be any data type. You can think of it as something similar to a void *.

base36_encode(PG_FUNCTION_ARGS) our function is named base36_encodePG_FUNCTION_ARGS and can take any number and any type of arguments.

int32 arg = PG_GETARG_INT32(0); get the first argument. The arguments are numbered starting from 0. You must use the PG_GETARG_XXX macros defined in fmgr.h to get the actual argument value.

char *buffer = palloc(7 * sizeof(char)); to prevent memory leaks when allocating memory, always use the PostgreSQL functions palloc and pfree instead of the corresponding C library functions malloc and free.
Memory allocated by palloc will be freed automatically at the end of each transaction. You can also use palloc0 to ensure the bytes are zeroed.

PG_RETURN_TEXT_P(cstring_to_text(&buffer[offset])); to return a value to Postgres you always have to use
one of the PG_RETURN_XXX macros. cstring_to_text converts the cstring to Postgres text type before.

Once we’re finished with the C-part, we need to modify our SQL function.

base36–0.0.1.sql

12345

-- complain if script is sourced in psql, rather than via CREATE EXTENSION\echoUse"CREATE EXTENSION base36"toloadthisfile.\quitCREATEFUNCTIONbase36_encode(integer)RETURNStextAS'$libdir/base36'LANGUAGECIMMUTABLESTRICT;

To be able to use the function we also need to modify the Makefile (Line 4)

Returning errors

You might have noticed that our simple implementation would not work with negative
numbers. Just as it did before with 0, it would return an empty string. We might want to add
a - sign for negative values or simply error out. Let’s go for the latter. (Line 12-20)

test=#SELECTbase36_encode(-10);ERROR: negative values are not allowedDETAIL: value -10 is negativeHINT: make it positive

Postgres has some nice error reporting build in. While for this use case a simple errmsg might have been enough,
you can (but don’t need to) add details, hints and more.

For simple debugging, it’s also convenient to use a

1

elog(INFO,"value here is %d",value);

The INFO level error would only result into a log message and not immediately stop
the function call. Severity levels range from DEBUG to PANIC.

More to come…

Now that we know the basics for writing extensions and C-Language functions, in
the next post we’ll take the next step and implement a complete new datatype.

]]>2015-10-06T10:00:00+02:00http://adjust.github.io/2015-10/pgbundle-manage-your-postgres-extension-dependenciesOne of Postgres’ most powerful features is its extensibility. Although Postgres
offers a large number of data types, functions, operators, and aggregates,
sometimes you may still want more. Postgres itself already comes with a large
amount of additional extensions.
Even more can be installed through the PostgreSQL Extension Network
and if that is not enough for you, you can also write your own.

However, there isn’t a standard tool for managing Postgres dependencies in applications. To avoid falling into the
dependency hell and to enable lean extension development, we developed pgbundle - the Postgres extension management
tool.

Installation

pgbundle has been inspired by the Ruby way of managing dependencies through bundler. It is
distributed as a Ruby gem, but as you’ll see from this article, you don’t need any Ruby knowledge to use it.

The quickest way to get pgbundle is to install the gem through RubyGems with gem install pgbundle. In case
you’re on a Ruby project, however, you might prefer to add pgbundle as a dependency to your Gemfile.

Describing Dependencies using Pgfile

Once you have pgbundle installed, you can and define your dependent Postgres extensions in a Pgfile like this:

The database command

database defines on which database(s) the extensions should be installed. The first
argument is the database name, the additional options may specify your setup but
come with reasonable default values.

1234567

user:'postgres'# the database user (needs privilege to CREATE EXTENSION)host:'localhost'# the database host (needs to be accessible from where pgbundle runs)use_sudo:false# if true use sudo to run make install if neededsystem_user:'postgres'# the (os) system user that is allowed to install an extension (through make)port:5432# the database portforce_ssh:false# run commands via ssh even if host is 'localhost'slave:false# defines if the database runs as a read-only slave thus skips any CREATE command

Specify a Dependency using pgx

The pgx command defines your actual extension. The first argument specifies the extension name,
the second optional parameter defines the required version. If the extension is not yet
installed on the server you may wish to define how pgbundle can find its source to build
and install it. And which extensions may be required

123456

git:'url'# any git repository pgbundle can clone fromgithub:'user/repo'# any github repository in the form `user/repository`branch:'branch_name'# an optional branch name for git or github sources defaults to `master`requires:'extension_name'# an optional extension that the extension depends onpath:'/some/local/path'# any absolute or relative local path e.g. './foo/bar'pgxn:'extension_name'# any repository available on http://pgxn.org/

Resolving Dependencies using requires

Some extensions may require other extensions. To allow pgbundle to resolve dependencies
and install them in the right order you can define them with requires.
If the required extension is not yet available on the target server or the extension
requires a specific version you should define it as well.
E.g.

12345678910111213141516

# Pgfile# database configuration goes herepgx'hstore'pgx'foo','0.1.2',github:me/foo# set foo as dependency for barpgx'bar','1.2.3',github:me/bar,requires:'foo'# set bar and hstore as dependency for baz# will automatically set foo as dependency as well# note as hstore is a build in contrib module for postgres# there is no need to explicitly define it, however for readability we recommend itpgx'baz','0.2.3',github:me/baz,requires:['bar','hstore']

The pgbundle executable

With a Pgfile configured for your project, you can run the pgbundle executable to actually download and setup the
dependencies.

The pgbundle executable comes with 4 commands. All of these commands need a Pgfile to run against and you can either
use the pgfile argument to provide a custom file path, or simply create a file named Pgfile in the current directory
and define your dependencies in it. By default the pgfile executable will try loading that file.

Note that another benefit of maintaining a Pgfile, is that it will allow you to keep your Postgres extension
dependencies, configured under version control.

Let’s go through each command that the pgbundle executable supports.

12

# checks availability of required extensionspgbundlecheck[pgfile]

check does not change anything on your system, it only checks which
of your specified extensions are available and which are missing.
It returns with exit code 1 if any extension is missing and 0 otherwise.

12

# installs extensionspgbundleinstall[pgfile][-f|--force]

install tries to install missing extensions. If --force is given it installs
all extensions even if they are already installed.

12

# create the extension at the desired versionpgbundlecreate[pgfile]

create runs the CREATE EXTENSION command on the specified databases. If a version
is specified in the Pgfile it tries to install with CREATE EXTENSION VERSION version.
If the extension is already created but with a wrong version, it will run
ALTER EXTENSION extension_name UPDATE TO new_version.

init is there to help you get started. If you already have a database with installed
extensions you get the content for an initial Pgfile. pgbundle will figure out
which extension at which version are already in use and print a reasonable starting
point for your Pgfile.

However this is only meant to help you get started; you would probably need to edit the generated file in order to
specify sources and dependencies correctly.

How it works

You may already have noticed that using extensions on Postgres requires two different
steps. Building the extension on the database cluster with make install
and creating the extension into the database with CREATE/ALTER EXTENSION.
pgbundle reflects that with the two different commands install and create.

Usually pgbundle runs along with your application on your application server
which often is different from your database machine. Thus the install step
will (if necessary) try to download the source code of the extension into a
temporary folder and then copy it to your database servers into /tmp/pgbundle.
From there it will run make clean && make && make install for each database.
You may specify as which user you want these commands to run with the system_user
option. Although for security reasons not recommended, you can specify to run the
install step with sudo use_sudo: true. We prefer to give write permission
for the postgres system user on the install targets. If you are not sure which these
are, run

1

pg_config

and find the LIBDIR, SHAREDIR and DOCDIR.

Handling master/slave database setups

Every serious production database cluster usually has a slave often run as Hot Standby.
You should make sure that all your extensions are also installed on all slaves.
Because database slaves run as read-only servers any attempt to CREATE or ALTER
extension will fail, these commands should only run on the master server and will
be replicated to the slave from there. You can tell pgbundle that it should skip
these steps with slave: true.

]]>2015-09-24T15:11:00+02:00http://adjust.github.io/2015-09/virtual-public-network-setup-with-gentoo-and-kvmThis article will show you how to create a network with 5 virtual machines which have public IP addresses and can be accessed via Internet. Virtual machines will run on Gentoo.

Prerequisites

I assume that you’re using Gentoo on both local computer and the server, run the example commands with an administrative user (e.g. root permissions), and both machines are up and connected to the Internet. For virtualization, we will use Kernel-based Virtual Machine (KVM). KVM can only be used if your CPU supports the Vt-x (Intel) or AMD-V (AMD) extensions.
If you want to check if your CPU supports KVM, then run the following command:

1

grep --color -E "vmx|svm" /proc/cpuinfo

As KVM works in kernel space you need to compile the corresponding modules. For detailed kernel configuration of your local computer and the host server, take a look at the article Creating Virtual Networks With KVM on Gentoo.

QEMU / libvirt / virt-manager setup

While The Quick Emulator (QEMU) can work with many virtualization drivers (such as KVM or XEN) or with its own built-in user-space driver, libvirt is a management tool for various virtualization solutions.
As we want to use the virtual network capabilities and the QEMU support of libvirt, we need to enable the corresponding USE-flag on a server side.

So, the first step is to install libvirt on your local computer:

1

emerge -v libvirt

After you successfully installed libvirt, you can start it with:

1

/etc/init.d/libvirtd start

If you don’t want to manage your virtual machines from console, then you can install the virt-manager for managing your virtual machines, also on your local computer:

1

emerge -v app-emulation/virt-manager

After your local computer setup is ready, now you can start with setting up the server, where your local machines will be hosted. That’s why, we need to install libvirt on the server as well.

So, enable the qemu USE-flag:

1

echo"app-emulation/libvirt qemu" >> /etc/portage/package.use

After this, you need to start the libvirtd service. The next step is to install the following tools:

brctl (net-misc/bridge-utils)

tunctl (sys-apps/usermode-utilities)

Network setup

Now we need to set up our virtual network. I assume that you have a subnet of 6 usable addresses (x.x.x.6/29), and that your CPU is Intel. The first thing we need to do is to run the following commands in order to load the necessary modules:

12

modprobe tun
modprobe kvm-intel

If the CPU on your server is AMD, then you should run:

12

modeprobe tun
modprobe kvm-amd

The next step is to turn on the IP forwarding:

1

echo 1 > /proc/sys/net/ipv4/ip_forward

If you want to keep IP forwarding enabled and after reboot of the system, then you need to edit /etc/sysctl.conf file and in the following line change 0 to 1: net.ipv4.ip_forward = 1

As I already mentioned, we have a subnet of 6 usable public IP addresses. (x.x.x.6/29).
Our usable addresses are: x.x.x.7, x.x.x.8, x.x.x.9, x.x.x.10, x.x.x.11, x.x.x.12.
So, each KVM virtual machine will receive their own virtual network cards which are combined into a bridge. This bridge serves as gateway.

First, we need to set up the bridge. As this is going to operate as a gateway later on, it receives its own IP address. For this purpose, we will take the first IP from our subnet (x.x.x.7).

Then we add the bridge interface and set it up the IP address and subnet:

12

brctl addbr br0
ip address add x.x.x.7/29 dev br0

Next, we need to set up the virtual network interface for the first virtual machine:

1

tunctl -b -u root -t qtap0

Then, we need to add this interface to the bridge:

1

brctl addif br0 qtap0

And finally put the interface into promiscuous mode:

1

ip link set dev qtap0 promisc on

Last three steps need to be repeated for all the virtual machines. However, always increase qtap0, i.e. qtap1, qtap2 etc. The next step is to set up the routes for our virtual machines:

1

ip route add x.x.x.8 dev br0

This step needs to be repeated for all the other virtual machines as well. Make sure that you adjust the appropriate IP address each time.
And that’s all you need to do about the network setup at the server side. Now, we need to set up the virtual machines.

Virtual machine setup

On your local computer, start virt-manager and add a new connection to your server.
Then, start a wizard for creating a new virtual machine instance.
Select your ISO image, define the resources for new instance, such as amount of RAM, storage space and number of CPUs.

Please note that, on the end of this wizard you make sure that you turn ON the option: Customize configuration before install (this is important, soon you’ll see why).

Also, make sure that for Virt Type you select kvm, and that you select your Host device qtap0 (bridge you have created) under Advanced options. Finish the wizard, and wait for the new window where you can configure your virtual machine. You only need to remove the sound device, and then to click Begin installation.

Possible errors

In this phase, you can get to a few different errors. For example:

virt-manager expects qemu to be compiled with ALSA/PulseAudio support, so you should compile qemu with ALSA/PulseAudio support.

In order to avoid errors related to USB ports, compile qemu with usb and usbredir USE flags.

If you get an error message which is related to “spicevnc”, then you need to reinstall qemu on server with spice USE flag. This will enable Spice - a remote-display system built for virtual environments which allows users to view a computing “desktop” environment, not only on its computer-server machine, but also from anywhere on the Internet and using a wide variety of machine architectures.

Base installation

At this step, you should already have access to the virtual console running Gentoo ISO image.
The first thing we need to do is the set up your network connectivity. We need to run the following commands:

12

ip address add x.x.x.8/29 dev eth0
ip route add default via x.x.x.7

Also, we need to edit /etc/resolv.conf and add the DNS server. In this case, we add Google Public DNS server:

1

nameserver 8.8.8.8

Now you should be able to ping your gateway (x.x.x.7), ping your own IP, and ping the Internet.

If everything went OK, you now have installed Gentoo on virtual instance which is publicly visible from the Internet, and which can also “see” the Internet.

At this point, you just need to clone this virtual machine as many times as you want, using virt-manager, configure the network settings for all the machines (in our case, we make 4 clones) and you’ll have your network of virtual machines up and running.

The easier way

There is an easier way to set up the virtual network and to configure the virtual machines. In order to do this, you need to follow this guide until QEMU / libvirt / virt-manager setup (including this step as well). Then, come back here and continue.

One of the tools you’ll get, as a part of a libvirt core, is virsh - an interactive shell, and batch scriptable tool for performing management tasks on all libvirt managed domains, networks and storage. Using virsh you can create, delete, run, stop and manage your KVM virtual machines. More information you can find at Virsh Command Reference.

So, we will use virsh to make our virtual network, and configure all the virtual machines with appropriate IP addresses, MAC addresses and hostnames, by creating a simple libvirt XML file. To find out more about how to create these kinds of files, go to XML Format page.

that our virtual network will route all traffic to physical network interface eno1

our bridge is called virbr3, with an IP address x.x.x.7

first virtual machine with the MAC address 00:00:00:00:00:01 will have hostname vm1 and the IP address x.x.x.8.
For more details, take a look at this page.

Just notice that, when we create our virtual machines, it’s important to give them the appropriate MAC address, so they can automatically get the right hostnames and IP addresses.

Before you create an XML file for your virtual network, it’s good to check if there are already some virtual networks:

1

virsh net-list

Also, you should check which virtual interfaces already exist, so you don’t try to use the same in your XML file. You can check this with:

1

ip a

Once when you create this XML file on your server, you need to create your network with:

1

virsh net-define your_file.xml

Then run:

1

virsh net-list --all

and you should see your network, but shown as inactive. Now you just need to activate it with:

1

virsh net-start YourNetworkName

Now, when your new virtual network is active, you need to start virt-manager on your local computer, and add a new connection to your server. Then, start a wizard for creating a new virtual machine instance. Select your ISO image, define the resources for the new virtual machine, such as amount of RAM, storage space and number of CPUs.

On the end of this wizard make sure that you turn ON the option: Customize configuration before install.

Also, make sure that for Virt Type you select kvm, and that you select your virtual network device under Advanced options. It’s really important that you set the appropriate MAC address as well. Option Set a fixed MAC address must be on. In our case, for first virtual machine, we will set the following MAC address: 00:00:00:00:00:01 and it will automatically get the vm1 hostname and x.x.x.8 IP address.
Then finish the wizard, and wait for the new window where you can configure your virtual machine. You only need to remove the sound device, and then to click Begin installation.

From this point, you can get back to Possible errors section, and continue with the Base installation section. Of course, you can skip the part with setting up network connectivity for the virtual machine, since this was already configured automatically.

]]>2015-06-09T10:55:00+02:00http://adjust.github.io/2015-06/rex-in-practice-test-driven-infrastructureI hate writing tests.
There’s only one thing I hate more: not having tests.
So I like it when writing and running tests for anything is easy.

In part 1 of Rex in practice series,
we got started with describing our infrastructure as code.
All of those automation bits are kept in git repositories.
They are nothing but code after all.
Since they are code, we want them covered by tests.

Normally, we would start with writing tests
which can check for the expected state of a remote machine,
and then we write our code in iterations to pass all the cases.

Rex supports managing virtual machines and containers
through different methods,
like LibVirt, VirtualBox or Docker
(and even some cloud providers).
Built on top of this functionality,
it also has a Vagrant-like feature called Rex::Box.

Rex::Test in turn,
makes use of Rex::Box
to quickly create a VM,
provision it by running one or more tasks
and then run a series of tests
checking the state of the machine.

Following up on the example in the previous part of the series,
our NTP tests would probably be similar to this:

First we give a name to the VM which will run the tests
(ntp_test in this case),
then point Rex to the base image to use when creating this new VM,
and finally specify the authentication credentials for the VM.

Rex downloads the specified image into ./tmp
and then tries to import it as a new VM,
cloning the base image into ./storage
(so the original file is left untouched
and can be reused multiple times).
Depending on the virtualization method requested
and the type of the image,
Rex also tries to extract and/or convert it before using it.
For example
if the specified base image is a .gz file
or if a file in OVA format should be used with KVM.

1

$test_vm->run_task('ntp');

As the last step of initialization,
this line will provision the VM
by running a Rex task called ntp on it.
That’s the task we specified in the previous post,
but normally would define it when this step fails.
It is possible to run multiple tasks,
by passing them as an array reference.

After the boilerplate and test initialization,
let’s see the tests themselves.
The above code snippet executes the following tests inside the VM in order:

check if it has a specific package installed,
called ntp

check if it has a specific file present,
called /etc/ntp.conf

check if that file has specific properties,
like owner and group

check if that file has a specific content,
matching the regular expression server /d.gentoo.pool.ntp.org

run the md5 Rex command inside the VM
to calculate the MD5 checksum for the same configuration file,
and then check if it matches a specific value

check if the VM has a specific service (ntpd) in a running state

finish the test suite

As you can see,
we’re not limited to the built-in tests,
but we can run arbitrary commands inside the VM,
record their output or return code,
and then check them against their expected values
with the ok() method.

Before we can actually run the test via Rex,
we need to add one more line to our Rexfile
we showed in the previous post:

Rexfile

1234

use Rex -feature => [ '1.2' ];
+use Rex::Test;user 'root';

This enables an internal Rex task,
called Test:run,
which by default runs all test cases under the ./t directory:

1

$ rex Test:run

If we had more tests there,
we could pick only one or few of them to be run:

12

$ rex Test:run --test=t/ntp.t
$ rex Test:run --test=t/ntp*

When running a test,
Rex outputs its current progress
and of course each of the test results,
plus an overall result
like how many tests were run,
and whether the test suite failed or passed.
For example something like this
(note the -q command line option
to make Rex output quiet):

About the author

Ferenc Erki is a core developer of Rex
and a system administrator at adjust,
where he is known as tamer of the ELK beast.

]]>2015-05-26T16:02:00+02:00http://adjust.github.io/2015-05/rex-in-practice-infrastructure-as-codeAt adjust we use (R)?ex extensively
to automate tasks related to our infrastructure,
and we also started to use it for application deployment.

We would like to share our use case with this tool,
highlighting some of its features
through a series of introductory posts and examples.

Introduction to Rex

Rex is a deployment and configuration management framework
written in Perl,
which uses SSH to manage remote hosts.

Since nothing else is needed for the core functionality,
chances are high that you can just start using it right away,
regardless of whether you would like to do the management
from a machine running Linux, Mac OS X, Windows
or practically anything that can run Perl code.

Using SSH as a transport layer means that
solutions to problems like
authentication and encryption
are simply reused,
allowing Rex to focus on automation.
It also enables Rex to manage a bit more exotic remote machines
such as OpenWRT boxes or even iDRAC interfaces
(with Windows management support on the roadmap).

Rex provides a simple DSL
to easily describe the steps you would like to automate,
but in the end everything is just plain Perl,
so you are free to harness its full power if needed.
If you are not familiar with Perl
and would like to get a quick introduction on the basics,
check out Rex authors’ Just enough Perl for Rex page.

While Rex is primarily used as a push-style configuration management tool,
as it is usual with Perl,
there is more than one way to do it (TIMTOWTDI). For example:

you can do pull-style management
by periodically downloading task definitions
from a git repository or a web server,
and then running them locally
(by the way, official server and agent are also on the roadmap, if that is your fancy)

if you need to scale
to more than a couple of hundred remote servers,
you can distribute job execution
e.g. via Gearman
(even with optional queueing
to have deferred execution in case a remote is down for a while)

As you can see
flexibility is a key design concept for Rex
and it lets you solve your own problems in your own style
without getting too much in your way.

A simple example

Whether you call yourself a software developer or system administrator
(which are less and less distinct, by the way),
you are most probably providing services to customers.
I mean customer as in:
any end user of any service is a customer.
In general the lifecycle of those services
has the following three common tasks:

installing

configuring

running

Of course we can extend it
with upgrading, monitoring and uninstalling,
but for the sake of simplicity
let’s focus on the previous list for now
and see an example of how Rex deals with them.

At the core of any automation project based on Rex there is a Rexfile:

A Rexfile has three main parts:
authentication details,
configuration options
and task definitions.
Let’s see the details of this example:

1

useRex-feature=>['1.2'];

First we import Rex
and enable the feature flag for version 1.2.0.

1

user'root';

In order to connect to a remote machine,
we need to specify the credentials to be used during authentication.
Having only a user specified is the most simple case
(while using the default SSH provider on Unix-like systems and an SSH agent).
Of course, there are many ways to authenticate for a remote system,
but instead of giving a boring list of those options here,
we’d like to point the reader to further resources on this topic:

The next step is to define which servers and server groups we have.
Our example will generate a server group called servers
with 12 hosts in it: server-1.domain.tld, server-2.domain.tld, ..., server-12.domain.tld.

Optionally, those server group definitions can come from external sources
like INI or XML files, SQL queries, Nagios configuration, etc.
Or, since it is nothing more than plain Perl,
any array can be passed to group to be used as a list of servers.

123

environmentdemo=>sub {groupservers=>'demo-[1,2].domain.tld';};

If you have several environments to manage - like testing, staging, qa, demo, production, and so on -
you can easily override group definitions or authentication options
for these environments.
Or even define tasks that are only available for these specific environments.
Later on, you can choose to run a task on only one of the environments
with rex -E demo ... on the command line.

1

desc'Setup NTP';

With desc
we give our following task a nice description to be shown,
e.g. in the task list printed by rex -T.

Let’s go on to the most interesting part:
the task definition itself.
In our example we define a task called ntp,
and associate it with the server group called servers by default.

Within the task itself, we specify the main lifecycle steps of the NTP service:

install the package called ntp

configure NTP by copying the local files/etc/ntp.conf to /etc/ntp.conf on the remotes
and ensuring proper owner/group/mode properties for it,
plus restarting the ntpd service on the remotes
where the configuration file in question has changed

ensure that the ntpd service is running
and will be started after reboot

That’s it. Three steps, three commands.

Please also note that this code doesn’t assume any specific OS on the remotes
(well, other than the package and service names).
It’s the job of Rex to figure that out
and use the proper package or service management methods.

Given that Rexfile,
it takes only a single command to setup,
configure and run NTP on all twelve servers:

1

$ rex ntp

Or the same but using the demo environment:

1

$ rex -E demo ntp

Since the Rexfile is nothing but code,
it makes sense to include it in a version control system
such as git
and share it with your coworkers,
so they also can start using and improving it.
There you go,
from zero to infrastructure as code in a few easy steps.

About the author

Ferenc Erki is a core developer of Rex
and a system administrator at adjust,
where he is known as tamer of the ELK beast.

]]>2014-11-06T16:42:00+01:00http://adjust.github.io/2014-11/building-your-own-user-databaseFacebook does it, Google does it, Twitter does it. There are many companies that create databases and lists for you to be able to re-engage and re-target your mobile app users.
But why not do it yourself and be independent of any 3rd party to tell you who your users are.
Today, as a first article of a series, we want to show you the theoretical basics of user databases and why you should run them yourself.

Who should read this?

You have an app and you are running user acquisition campaigns aka. app marketing to get new users into your app?
You may even already use advanced tracking systems like adjust.com to identify where your
users are coming from? Great, but what to do about those users once they start or stop using your app?

In order to maximize your LTV you need to re-engage them, either via targeted ads on e.g. Facebook or via push
notifications custom tailored to them. If this is you, read on.

Why do it yourself?

As initially stated, there are many companies that can use tracking data from your app to generate user lists to know
which users to re-engage. The big inventory providers like Facebook, Google or Twitter have this already integrated into
their platforms. Companies like Applovin, Appboy or Tapcommerce offer it as a service often combining it with push
capabilities to leverage multiple re-engagement channels.

The main problem with all of them is that you are dependent on
a 3rd party to store your user data, that, at least to a certain degree, will sell exactly this data back
to you. In order to get started with a re-targeting campaign you typically need to populate the provider database
by forwarding your tracking data for a couple of weeks.
And when you feel like changing the provider the whole game starts again.

So why not take this into your own hands and take your app marketing to the next level?

Basic problems to solve

There are 3 basic problems that need to be solved before we can run our own re-engagement campaigns by providing IDFA
or Google Advertiser ID lists to the inventory provider of our choice or sending custom push messages to the right users.

1. Importing data

First, we need to get all the installs, sessions, events plus any segmentation data from your app.
If you are using an app tracking provider you usually have the option to receive a daily export of all your raw data.

More advanced providers like adjust.com allow you to set up real-time callbacks to stream
your data ad-hoc into your data warehouse. In this case you need an HTTP endpoint to receive those callbacks
and save them to your database, which leads us to the next point.

2. Storing user profiles

The most crucial point in maintaining your own user database is the way you will structure the stored data.
The goal of a user database is the ability to query quickly for users that match certain criteria.
This means we need to optimize our data structure in order to enable these kind of queries.
Interestingly enough, it matters less what database technology you use but how you store the data.

For the sake of argument, we will use a row-based database as example, column stores and document-based databases
with map-reduce capabilities will work quite similar in our use cases.

one row per event leads to huge databases, typically making sharded/distributed databases
necessary because each attribute being stored over and over again for all the events of a user takes up a lot of space

trying to look up single users can lead to having to scan the full dataset to collect all his events

Querying a database like that for all users that have spent more than $10.00 and have been inactive for 3 days
would result in complex joins or multiple expensive map-reduce steps.

In our use case this approach would pretty much generate a giant database that’s almost impossible to query.
So what’s the plan B?

User-based databases

The alternative to storing each event individually is to store one record/row per user.
This moves the database load from inserts to updates and you may want to consider this picking your technology.

The underlying concept for this kind of database is to reduce/aggregate the individual event data into columns
of our user database by triggering updates on a given user row.
This requires some planning ahead on what questions we want to be able to ask.
A typical case is a column for the sum of e.g. revenue, session count or time spent.
Another common pattern is to have a first and last occurrence time stamp column for events in your app
along with fields for installed_at.

So that gives us an excellent basic framework for retrieving user segments and behavioural data on-the-fly. This is often enough.
It is relatively easily extendable, to make it even quicker and easier to work with for marketing folks. From here, if you wanted to help your marketing folks work with it more intuitively, a simple API and a connected UI could let them retrieve their lists and segments right from the browser.

Summary

The scope of this article was to discuss the theoretical base for a system to store and retrieve device id lists with
the purpose of re-engaging users of a mobile app.

We examined the available storage schemata and found that a user based database would be best suited for this task.

Over the course of the next weeks we will build an open source prototype of this concept and
publish a series of articles about it here
for you to participate. The goal will be to establish a standard that allows partners
to get started faster with
re-targeting campaigns by using a publishers internal database (via an HTTP API) and publishers to take
ownership of their data. Till then,

Have fun.

]]>2014-10-06T12:00:00+02:00http://adjust.github.io/2014-10/crunch-csv-with-apswAs part of our export functions for adjust.com, we generate CSV reports for our clients, which are typically
used to create pivot tables in Office.
Sometimes those reports can be 100’s of megabytes in size and Office will not be able to open them. And even those
files that can be opened often take minutes to load.

Being asked one too many times to “quickly” check one of those reports I came across a very helpful little tool to
transform CSVs into SQL without all the hustle of creating complex table structures.

So my basic requirement was to dynamically create the table for a CSV file and then import it into an SQL database.
The “dynamically” part is rather important as our CSVs can easily have 50 or more columns and typing all that
into a CREATE TABLE statement isn’t exactly fun.

After some searching I found APSW.
It does exactly what I was looking for and is super simple to use.

Installation

Since we work with OS X at adjust I’ll only describe how to get going there,
but APSW is available for almost any OS incl. Windows.

The best part is, that SQLite is a lot faster than Excel and you can handle millions of lines without any problem.

Quirks and recommendations

Working with APSW a bit it turned out to be quite robust. The only real issue I encountered is that it tries to parse
dates as US format yyyy-dd-mm and thus has problems figuring out the correct format if you have
standard ISO format yyyy-mm-dd with no days > 12 like in the data range 2014-08-[01-12].
My solution was to simply add a line with one day that clarifies it for APSW, so for example one with the date 2013-12-31.

Now if you are reading this and are not familiar enough with SQL to get started right away
I can recommend a couple of good places to start:

]]>2014-06-12T10:00:00+02:00http://adjust.github.io/2014-06/unrolling-line-charts-d3js“Unrolling” line charts are everywhere - where the lines gradually enter from origin, point by point. This is the world’s favourite way of animating a line chart, particularly as it makes a ton of sense when graphing a time series. d3 tends to transition line charts really weirdly, though. So what is d3 actually doing when creating transitions on line charts, and how can we make them prettier?

In the graph below, I had two series comparing two alternative calculations of lifetime value in mobile users. This was for a blog post, which we published here. The original version, as drafted by our talented designer, simply had two different graphs displayed at different points in the text - but I wanted to jazz it up a bit.

How SVG and d3 work with lines

Before we dig into generating transitions, you need to understand how the SVG standard and d3 interact when it comes to lines. SVG draws lines (more correctly, paths) from the points in a line being concatenated in a string, like this:

1

<pathd='M0,200 L50,30 L100,75, L200,30'></path>

Normally, though, your data will be organized in a series of x: , y: datapoints, as an array of objects. d3 provides handy generators that take such arrays of objects, apply some transformation and scaling, and spit out the SVG string. Very easy:

In the problem above, where the second timeseries should be entered via a transition, we’ll start out with a line object with zero associated datapoints, i.e. an empty d string. Then we’ll ask d3 to .transition() to .attr('d', lineFunction(secondSeries). That’s the same pattern as we would use with any other transitions, except it looks awful:

N.B.: For code examples from here on, I’ll provide excerpts highlighting the method. For each step of the process, hit up the source code - JS in full is below each graph.

This isn’t what I was looking for at all! All the points simply spark into existence like some hitchhiker’s whale; that’s hardly a transition. d3, however, thinks it’s being entirely reasonable about this.

Whenever you create a transition in d3, you pass two states to d3: the initial state and the final state. You then ask d3 to interpolate between the two for each frame of the animation, expressed as a variable t {0,1}. In this example, you’re passing an empty string as the initial state, and a fully-generated string as the end state.

Because these are strings, d3 attempts to use interpolateString. This interpolation function runs a regex across the strings looking for numbers to manipulate. Except in this case, it finds no numbers in the initial state, and so manipulates nothing over t.

Ah-ha, you say, I’ll just pass the initial state as a string of origin coordinates! Yeah, no, I tried that:

As you see, d3 then finds all the (0,0) coordinates, then slowly scales them up to your end state coordinates, meaning that the line just appears in the top left of the graph and slowly scales into place. Matt Bostock discusses this himself and suggests some solutions for graphing realtime data here. All that said, this is still not unrolling properly.

The solution

For very simple graphs, you could simply clip or mask the path, and then move the mask over to the right as the animation progresses. That won’t be very scalable, though, as the complexity of the mask grows if you have multiple lines in the graph, or god forbid a background axis, as we do here. Besides, I find it a bit hacky. ;)

For a general solution, you need to tell d3 exactly how to interpolate between the two states using a custom interpolation function. Nick Rabinowitz posted a base interpolator here, which gives us the fundamental animation:

What we’re doing here is creating a custom function, which returns the interpolation function, and passing a reference to that function to the attrTween of the transition. Transitions in d3 are created in several stages, where the transition is evaluated asynchronously. d3 calls the getInterpolation function once the transition is ready to go, and then evaluates the interpolation function that was returned for every frame t.

In this interpolation, for each t, we returned the d coordinate string, cropped to the latest whole datapoint for that particular t. d3 will figure out the steps of t required and call the function returned by getInterpolation for every frame in turn, and we will return the value of the line function for that subset. In large datasets, with maybe a few hundred datapoints, each or every other frame will add another point, so it’ll look perfectly smooth. With our smaller dataset, though, it looks rather choppy.

Let’s expand a little on Nick’s code to create a smoother, more general approach.

Fixing for a small dataset

So what we want to do now is smooth the animation out, giving not just the full datapoints for certain states, but also giving intermediary points for the t that don’t map to a full new point. For each t, we’ll pass the line function the sliced array with all the points that should be fully drawn, as well as a last intermediary point which has coordinates mapping to the middle state between the two points.

This point needs a set of coordinates, x: , y:. x is easy: for each t, we’ll just pass the interpolated t value, which we know to be the current value between 0 and the end of the line. (To do this, we’ll swap out the quantile scale for a linear scale, and floor the scaled t when slicing the array.)

For y, we’ll calculate a weighted average of the current point and the next point. The weight would be the distance between x and x-1, which we can get by pulling the decimals of t:

Now that’s looking pretty nifty. You’d probably not be surprised to learn that d3 already provides this kind of interpolation - in terms of interpolateObject and interpolateArray. Implementing these, however, turned out to be more code only to hack in the formats it expects, whereas we can more easily and readably simply provide the calculation of the average ourselves.

Fixing for arbitrary datasets

There’s one glaring flaw here, though, in that the interpolation function gleefully pulls a global variable for the data, instead of letting us define that on a transition-by-transition basis. If we had several arbitrary series to animate, letting that remain would mean declaring an entirely new, but identical function merely to use a different dataset, or constantly redefining the global dataset. That’s very silly.

It gets a bit muddy because d3 expects the interpolation function to pass a reference to a function which returns the interpolation function, so that the interpolation function can be fetched when the transition is started. That is, we need a reference to function getInterpolation to return a reference to function interpolate, which in turn calls function lineFunction.

To programmatically define the dataset we need, we need to wrap the, let’s say, function generator as an anonymous function in a return statement, and give the array as a parameter to a getGetInterpolation function:

Using this, we can call the getInterpolation function with our series as a parameter, have that function evaluate to a reference of the function that returns a reference to interpolation, and use it seamlessly within standard d3 transitions.

Fixing for partial transition

Finally, in some cases we may already have part of the series displayed, and simply want to expand it to the right with the same animation. For example, imagine that we are displaying a metric over time, and that we then increase the timespan displayed. So we need to interpolate t to map to a certain subset of the series.

You may already have spotted that the interpolation functions are always provided with three arguments: function (d,i,a), where the d parameter is set to the current datum. With line graphs it’s often not necessary to bind the data to the elements, but in this case we want to access the data later.

With .datum() we can associate any data with the selected object, and thus receive it back in the interpolation function as the d parameter. We’ll simply set the scale range as starting at the end of the current dataset, and going to the end:

Voilà. There’s plenty of ways we can expand upon this last exercise, for example by animating the scale as well, or by adding logic to allow for going backwards in the time series. The speed of the animation is also not even across the line, rendering more pixels per frame in certain segments, which makes it look like we are emphasising the portions of the line with higher delta; you could change the interpolate scale to scale t evenly on the number of pixels rendered.