PostgreSQL is a very versatile database. It has countless ways of bringing different functionalities to an already very sophisticated piece of software. Today, I’m going to show you how to use Rust and pgrx to bring etcd into your PostgreSQL database through a nice little interface called Foreign Data Wrappers.
Table of Contents
(an image representing rust, etcd and postgres working together. Drawn by Jeremy Sztavinovszki)
Etcd is a distributed key-value store. It puts an emphasis on consistency and is most often used when a distributed system or a cluster needs to access the same data. (https://etcd.io/). You will find it in software such as Kubernetes, OpenShift and so on serving as a decentralized configuration store.
In Postgres, a foreign data wrapper is a construct that can be used to access foreign data through Postgres, as if it was right there in the database. A foreign data wrapper consists of 2 functions that Postgres calls:
The handler function is responsible for registering all of the functions that handle planning, reading and modifying the foreign data source. These functions are not PL/pgsql functions, but rather C functions that are passed into a struct, which Postgres then uses to interact with the FDW.
The validator function is responsible for validating the configuration options passed to the foreign data wrapper (e.g. connection strings).
With these functions, a SERVER and FOREIGN TABLE can be created and the data contained in the foreign data source can be retrieved.
Foreign data wrappers can be as simple as writing tuples to a csv file and as complex as e.g. reading the current temperature from some kind of remote sensor.
etcd_fdw is a foreign data wrapper for etcd written in Rust using pgrx and supabase-wrappers. It currently supports all of the most common CRUD operations and there’s more to come.
Imagine the following scenario:
You have two instances of an application that share some kind of configuration which impacts database logic. There’s an obvious path you can take here, which is to do some kind of replication for the databases. This is very tedious though, and instead of only sharing the configuration you’d be sharing all of the data, which in this case could be undesirable. This is where etcd_fdw comes in. Using etcd_fdw, we can let etcd keep track of our configuration and changes to it and read what we need from a foreign table.
pgrx is an awesome framework for building Postgres extensions with Rust. It comes packed with a lot of nice interfaces for implementing, e.g. something like a custom Postgres aggregate, but as of writing it’s lacking a nice interface for writing foreign data wrappers. Luckily, supabase provides a very nice crate, which covers just this functionality, in the form of supabase-wrappers. Using its macros, an FDW can be implemented just by writing the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
[wrappers_fdw( version = "0.0.1", author = "Cybertec PostgreSQL International GmbH", error_type = "SimpleKvStoreError" )] pub(crate) struct SimpleKvStore { pub map: HashMap<String, String> } #[derive(Error, Debug)] pub enum SimpleKvStoreError {} impl ForeignDataWrapper<SimpleKvStore> for SimpleKvStore { fn new(server: ForeignServer) -> Result<SimpleKvStore, SimpleKvStoreError> {...} fn begin_scan( &mut self, _quals: &[Qual], columns: &[Column], _sorts: &[Sort], limit: &Option<Limit>, _options: &std::collections::HashMap<String, String>, ) -> Result<(), SimpleKvStoreError> {...} fn iter_scan(&mut self, row: &mut Row) -> Result<Option<()>, SimpleKvStoreError> {...} fn end_scan(&mut self) -> Result<(), SimpleKvStoreError> {...} fn begin_modify( &mut self, _options: &std::collections::HashMap<String, String>, ) -> Result<(),SimpleKvStoreError> {...} fn update(&mut self, rowid: &Cell, new_row: &Row) -> Result<(),SimpleKvStoreError> {...} fn delete(&mut self, rowid: &Cell) -> Result<(), SimpleKvStoreError> {...} fn end_modify(&mut self) -> Result<(), SimpleKvStoreError> {...} } |
Once you’ve implemented the logic for reading and writing to the hashmap, you can use it just as you would use a table with ‘key’ and ‘value’ rows.
Now that we’ve showcased how to write a simple FDW, we can do something more advanced. I won’t go into details about the code used to interact with etcd, but I’ll just quickly show off how etcd_fdw can be used to read and write values to etcd.
First we have to load our extension into Postgres
|
1 |
CREATE EXTENSION etcd_fdw; |
Now we can define our FDW
|
1 |
CREATE FOREIGN DATA WRAPPER etcd_fdw handler etcd_fdw_handler validator etcd_fdw_validator; |
Then we can define our server and create a table using the server
|
1 2 |
CREATE SERVER etcd_server foreign data wrapper etcd_fdw options (connstr '127.0.0.1:2379'); CREATE FOREIGN TABLE t_etcd_table (key text, value text) SERVER etcd_server OPTIONS (rowid_column ‘key’); |
Once that is all set up, we can interact with etcd as if it were just a normal table
|
1 2 |
INSERT INTO t_etcd_table (key, value) VALUES ('foo', 'bar'), ('bar', 'baz'); SELECT * FROM t_etcd_table ; |
which yields
|
1 |
INSERT 0 2 |
and
|
1 2 3 4 5 |
key | value -----+------- bar | baz foo | bar (2 rows) |
etcd_fdw is also able to see any changes made to the data outside of Postgres. When we add a key-value pair to etcd using etcd_ctl, the changes are immediately visible in our foreign table.
|
1 |
etcdctl put 'alice' 'bob' |
|
1 2 3 4 5 6 7 |
SELECT * FROM t_etcd_table ; key | value -------+------- alice | bob bar | baz foo | bar (3 rows) |
pgrx is a very easy way to write extensions for Postgres in Rust. Being able to use a crate like supabase-wrappers on top of that enables it to take advantage of Rust's speed, safety and expressive type system for FDWs.
You are currently viewing a placeholder content from Turnstile. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply