Custom Properties for PostgreSQL Database Objects Without Core Patches

Working in development, there is a common challenge: how to attach custom metadata to database objects without modifying Postgres's core code. In this article, I briefly demonstrate a practical solution using Postgres's SECURITY LABELS mechanism to implement custom properties that are transactional, properly linked to database objects, and work with standard Postgres operations.

The Problem: Managing Replication Conflicts

Consider a two-node setup where two active nodes replicate data to a third node. Such configurations are prone to UPDATE/UPDATE conflicts. While resolving replication conflicts is always a complex and generally unresolved task, certain columns lend themselves to simpler approaches.

For example, imagine a banking application with account balances. If operations on the balance column are limited to increments and decrements, you can use a delta-based approach: instead of choosing between conflicting absolute values, apply the delta (change amount) from both updates. This requires only basic validation (overflow checks, ensuring balance ≥ 0) and guarantees all updates are accounted for.

The challenge is: how do you mark specific columns to use this delta-based conflict resolution if Postgres doesn't support it? Ideally, we'd want something like:

ALTER TABLE accounts ALTER COLUMN balance SET delta_apply = 'true';

Such a level of integration with Postgres's SQL dialect is probably difficult to achieve and hardly necessary for portability. Still, it would be nice to have the ability to set a parameter using a call like:

SELECT my_extension.set_delta_apply('accounts', 'balance', true);

Proposals for customising properties of particular objects have been discussed in the community several times (see, for example, here and here), and core patches have even been proposed. However, their code is quite substantial, and the use case is narrow (or at least appears to be at the moment), which calls into question the practicality of merging them into core. Moreover, sometimes you want to attach a property not to a table but, say, to an index, large object, or data type - and that's a matter of time, code complexity, and the fact that an accepted patch would only change the behaviour of future versions of the DBMS core. But what if you need this functionality today?

Requirements

To understand how to do this, let's first define the requirements for the functionality:

• Object lifecycle binding - Lifecycle properties must have an internal dependency on the database object. For example, when you delete the parent object, your object should be deleted along with it when you invoke a DROP... CASCADE command.

• Extension association - Object properties must have some association with our extension so the DBMS can correctly handle the situation where the extension has been removed from the system.

• Transactional behaviour - Object properties must behave transactionally and follow visibility rules. That is, when modified simultaneously in parallel transactions, the new property value should be visible only within the transaction until commit and should revert to its original state on ROLLBACK.

• Upgrade/migration support - pg_upgrade and dump/restore should correctly transfer properties along with the database.

Of course, we'd like not just transactional but also session-level behaviour, similar to Postgres GUCs - but implementing that would be much more difficult.

Critics

Using a simple global hash table with the object's OID as the key doesn't suit us, since the parameter value can be variable-length (e.g., a string), and implementing the link between the object and the property is quite complex. But most importantly, how do we ensure transactional behaviour and MVCC?

The only simple way to satisfy our 3rd requirement is a database table. Perhaps we should create a <key><value>table as part of the extension and add properties to it, with the extension extracting the value from this table? That's an option! However, having extensive experience using tables in extensions (see, for example, AQO and sr_plan), I can say that this approach is quite dodgy - it causes various problems. And that's not just the challenge of upgrades, dump/restore, and replication, but also the constant need to check that the object exists in the database. Plus, there's the overhead of lookups…

Implementation Details

My specific task was to create a 'delta_apply' property for a column of an arbitrary table. This property is intended for managing logical replication and should define the conflict-resolution mechanism for UPDATE/UPDATE conflicts involving numeric values. If this property is set, the subscriber, instead of using various conflict resolution strategies, calculates the delta between the new and old column values and adds it to the current value on the subscriber.

For this parameter, all the above requirements are relevant; to satisfy these requirements in PostgreSQL, we find only one mechanism - SECURITY LABELS. While we may call them SECURITY labels, nothing restricts us from using them for non-security metadata. Just be aware:

• Security-focused tools may inspect or validate labels.

• We need to use a distinctive provider name to avoid conflicts.

How does it work? Let's look at the picture:

Implementation of Postgres DB object properties based on the SECURITY LABELs mechanism.

The solution uses the pg_seclabel system table, whose records are, by design, linked to the database objects. Adding, updating, and deleting records in this table is done via the 'SECURITY LABEL ...' UTILITY command, which allows monitoring this process in the extension using a utility hook.

The SECURITY LABEL tool allows marking objects of many types, including VIEWs and even functions, and this should be sufficient for most tasks. The label record contains a reference to the object's OID and the object class - table, attribute, function, etc. - which is quite convenient. The label's text field lets us attach any data we want to the given object, and the 'provider' field lets us distinguish labels created by a specific module.

To minimise overhead (going to the table every time is expensive, and a system cache for the pg_seclabel table hasn't been added yet), we add a cache in the form of a local hash table:

typedef struct PropertyCacheEntry {
    Oid classId;
    Oid objectId;
    char *propertyValue;
    bool valid;
} PropertyCacheEntry;
static HTAB *property_cache = NULL;

Invalidation of entries in this table are managed by registering a RelcacheCallback, whose job is to mark the entry as invalid in case of any ALTER TABLE call to our object.

The logic for adding entries to the cache can be implemented differently depending on the expected use cases. For my task, it's sufficient to add an entry to this cache when accessing the object in a hook (if the call comes from core) or in the extension's UI function (if the action is user-initiated).

Nuances of Implementing Property Addition and Deletion

To keep local caches of our properties consistent, we need to send invalidation messages for relevant objects on each change, and in each backend, receive these messages and clear the corresponding cache entry. If the change happens to the object itself, then, as mentioned earlier, we handle this event in the RelcacheCallback and mark the entry as invalid. But what if we're changing the property itself, which is actually just DML in the pg_seclabel table? How do we notify all other processes in time?

Postgres has a tool for sending invalidation messages to other objects: CacheInvalidateRelcacheByRelid. It's convenient, but limited to objects from pg_class. If the property is assigned to, say, a data type, this function won't help. So in practice, when adding or deleting a security label, I trigger an update of the object itself without actually changing anything in it, and the corresponding invalidation callback marks the entry in the extension's local cache.

The extension provides the user with an interface via the set_property() function, which assigns a SECURITY LABEL to the specified object. The label description specifies the object property value, for example: 'delta_apply: true'. The seclabel_provider callback, implemented in the extension for each label creation, controls the correctness of the object and the property being set.

Text description of the label leaves a wide range of possibilities; for example, it allows you to include complex property logic stored in a JSON description inside the field.

Thus, the client and extension gain a new communication tool that's fairly native, even though it's implemented outside the core. The extension now has the ability to check whether the user has assigned a property, and change behaviour accordingly.

In the delta_apply feature code, this is used as follows (see, for example, commits 67bf955, 09d5d7e). The logical replication subscriber process receives an UPDATE record containing the old and new values of a table row. When opening the table, we simultaneously access the delta_apply property cache. If the table includes one or more such 'incremental' attributes, for each such attribute, the delta between the old and new incoming values is calculated and added to the value in the subscriber's database. Even if, according to the conflict resolution strategy (e.g., last-update-wins), a decision is made to reject the incoming update, changes to the delta_apply columns will nevertheless be applied. This reduces the likelihood of conflicts between records, and ensures that all updates to the incremental column are accounted for.

External interference

Remember, pg_seclabel is still a catalogue table. As a result, any user with sufficient rights (DBA) can change the table, and attempt to create/alter/drop a security label manually.To avoid this issue, I propose introducing an internal GUC in the extension, for example myextension.call_guard. The GUC would be set to true at the start of any extension UI routine and reset to false when the routine completes. The extension can then check the GUC value before and after execution and take corrective action if the state does not match the expected lifecycle.

There is a possibility that when a superuser creates a routine, they might access this parameter and break the guard manually. Of course, we can resolve the issue by assigning a hook on this parameter, but it already looks like we're paranoid and overengineering…