In the previous post, you could see how to use the server programming interface to execute statements that modified the database. This was done by creating a statement containing the all the data that needed to be inserted, but that also meant generating a string from already parsed data, which seems like a waste of …
Continue reading Using prepared statements through the SPI
Category:Extensions
Creating JSON Values
In the previous post, you could see how to create set-returning functions that returns several rows of a table and we returned rows consisting of the timestamp, the metric, and two JSONB values: one for the tags and one for the fields. There were, however, no coverage of what JSONB is, how it differs from JSON, and how to construct them in your code. This post is going to answer those questions.
Parsing InfluxDB Line Protocol
In the previous post you could see how to create a background worker that received data over a socket as well as how to spawn a new background worker. In this post you will see how to write a simple parser for the InfluxDB Line Protocol and also get an introduction into PostgreSQL Memory Contexts and the Set-Returning Function (SRF) interface and learn how to write a function that returns multiple tuples.
It’s all in the Background
In contrast to MySQL—which is a multi-thread database system—PostgreSQL is a multi-process database system. In multi-process systems you have a process tree with several processes that interact to share the work, but only a single thread for each process. Multi-process and multi-thread systems both have advantages and disadvantages, some of which are discussed in this post. Since PostgreSQL is a multi-process system, the focus will be on PostgreSQL and multi-process systems.
In this post you will see how to add a background worker to the extension and how to spawn new background workers.
This post assume that you’re familiar with C programming and also familiar with programming for Linux. In particular, you need to know about processes, signals, and sockets in Linux: what they are, how they work, and what they are used for.
The Nobel Art of Writing PostgreSQL Extensions
Some things in the PostgreSQL code base is very straightforward, while other things can be more tricky to follow. I find that some areas are always not well-documented and understanding the rationale and reasoning behind a solution can require some research.
If you’re a developer that is interested in database internals and database implementations in general—or PostgreSQL internals in particular—this blog is for you.
In this post you will get an introduction to the structure of extensions and how to write an extension with a simple C function returning a complex type.