Extensions that add new _kinds_ of database object

[jcflack]

it's easy to write an extension that creates new database objects of existing kinds: functions, types, operators.... These all have first-class support in PostgreSQL, they can have owners and ACLs and they are recorded in pg_depend as being members of the extension that creates them.

It is less nice when your extension makes possible some new kind of database object.

For one example, when the PL/Java extension is installed, a Java jar file is a new kind of database object. ISO 9075-13 defines it; you can load one, you can drop one, it has an owner , and you can grant USAGE on it. These are all familiar things you can do with the built-in kinds of object. (PL/Java currently implements the owner kind of poorly, and the ACL not at all. Why? read on.)

And, ideally, there could then be other extensions, which supply their own instances of objects of this new kind, and those instances would be recorded as members of those extensions.

While this Java example may be the top one on my mind, it's surely not the only case where an extension supplies a new kind of database object. @mkindahl and I had a brief hallway conversation about it in Vancouver.

But these new kinds of object are decidedly second-class citizens. I can have PL/Java maintain a table of jar files, just as Postgres maintains, say, pg_proc. The oid of my table plus the id of a row form the "object address" of a jar file, just as the oid of pg_proc and the oid of a row form the ObjectAddress of a routine.

But my "object address" isn't a real one 😢. It can't be entered in pg_depend as a member of an extension. It can't depend on or be depended on by anything else. If ISO SQL says my jar objects should have owners, I can add an owner column, but with little support from the system. Even if the column is typed regrole, Postgres won't record that a newly-inserted row depends on its owner role. (And if it isn't typed regrole, the choice is between two ways of being wrong: store the role oid and be broken by dump/restore or upgrade, or store the role name and be broken by renames).

If I want my objects to have ACLs, as ISO says they should, I can add an aclitem[] column, but again, Postgres will not record dependencies on the roles named in my ACLs.

The bulk of the problems here boil down to the restrictions on what can go into pg_depend and pg_shdepend.

When I first raised these issues on -hackers in 2015, it led to my making this proposal.

---

A note from the passage-of-time department:

Two relevant things have changed in PostgreSQL since I made that proposal:

1. Non-system tables can no longer be declared WITH OIDS
   • the tiniest technical change to the proposal; an int4 primary key is perfectly workable.
2. Large objects now have owners and ACLs (or, the PG versions where they didn't have receded into the unsupported past)
   • That nearly gives me a solution for PL/Java jar files without any work: I could store a jar, not in a table, but as a LO, and have dependency-tracked owners and ACLs for free.
   • But still no follow-on extension could supply a jar file as an extension member (and why is there no ALTER EXTENSION ... ADD LARGE OBJECT? An oversight, perhaps?)
   • And even if it served my purposes, it would fall far short of a general solution to the "support new kinds of object" problem.

So I still think the proposal from 2015, with minor updates, is worth thinking about.

---

The proposal started with key observations:

• Nothing inherently stops pg_depend (or pg_shdepend) from mentioning rows in a non-system table. A pair (oid of table, 32-bit key into table) can identify a row in an extension-supplied table just as easily as in a system table.
• What currently stops such entries being added to pg_depend/pg_shdepend is explicit logic enforcing that the table oid must refer to a system table.
• That restriction was probably meant to protect the dependency-management subsystem from widespread, poorly-controlled usage with arbitrary user tables.

So, I asked, how could the restriction be cautiously loosened to allow selective, well-controlled usage with particular tables managed by extensions?

How about, if a dependency is about to be added and the table oid (classid or refclassid in pg_depend, or classid in pg_shdepend) does not identify a system table, then the table it does identify must:

• be a member of an extension,
• have an int4 primary key, and
• have a DELETE FOR EACH ROW trigger with a specific system-provided target function.

CREATE TRIGGER would forbid creating any trigger with that target function, except when:

• creating_extension is true, and
• the trigger is being created on a table belonging to the current extension, and
• the table has an int4 primary key, and
• any other conditions hold that we deem necessary

Making CREATE TRIGGER responsible for checking the full list of conditions (when that special target function is named) allows the dependency machinery to get away with only checking that the trigger is present. If so, the table is allowed to be mentioned in pg_depend/pg_shdepend entries.

That was a short recap of the more detailed proposal from 2015 here.

One remaining piece would be a hook allowing the extension to supply names for the new class of object and instances of it, for use by getObjectTypeDescription and getObjectDescription. So one more condition to be checked above would be that getObjectTypeDescription returns a name for the table's oid, confirming that the hook has been hooked.

Edit: my original proposal contemplated a hook. Peter Eisentraut recently submitted patches for virtual generated columns. When those make it into PostgreSQL, they'd be a perfect SQL-only replacement for the proposed hook. Simply require that the extension-managed table have two virtual columns, ObjectTypeDescription (returning a constant string like 'jar file') and ObjectDescription (returning a constructed string for that row like 'jar file foo').

---

This seems like a proposal with a small handful of moving parts but nothing really difficult or complex. Possibly I should start implementing it as a PR to pgedc's fork.

I would welcome reactions and comments.

[yrashk]

Hi Chapman,

Thank you for this post! It's an interesting proposal, and I've run into cases where I wanted new "kinds" of database objects. I'll digest it more deeply over the next few days, but I ponder if this should necessarily be limited to extensions. What would be the reasons for not allowing the creation of new database object kinds through a normal course of operation? If we allowed this, we would have less complex restrictions to implement and explain.

[jcflack]

I ponder if this should necessarily be limited to extensions. What would be the reasons for not allowing the creation of new database object kinds through a normal course of operation? If we allowed this, we would have less complex restrictions to implement and explain.

I'd say I was trying to propose a patch of modest scope, one that would not add new SQL syntax or new catalog tables. It seemed the necessary behavior could be arrived at by insisting there's a particular trigger on a particular table, and to make sure that condition was satisfied by design and not by accident or mischief (and that it stayed satisfied and wouldn't be violated by later dropping the trigger, say), it could be enough to require the table and trigger were both created in the script of an extension. A convenient way to keep the moving parts together.

A more ambitious patch might add new syntax ... would that look something like

CREATE OBJECT CLASS foo ( ...? );

or maybe just

CREATE TABLE foo (id int4 PRIMARY KEY, fooname name, fooowner regrole, fooacl aclitem[])
WITH DEPENDENCY MANAGEMENT;

and that could set up all the pieces together, and be allowed inside or out of an extension script. But then we'd have to start with months of bikeshedding on how the syntax should look.

If we had an initial patch that just enables dependency management based on the right combination of existing pieces (grouped together in an extension script), then maybe it could be a future patch to add new syntax that would set up all the pieces automagically.

Each row defines some unique name, perhaps associated with a PL, and then there's a payload: JAR file, rust Crate, Perl module, etc.

Where possible, the Postgres PL implementation hooks into the module-loading API of the PL (e.g., Perl's use and Java's import (this gets hand-wavy, as I think a Java import is different from using a JAR). When a PL function imports a thing, the PL implementation looks in the PL dependency table, and if the item is there, loads it.

This allows the superuser to load supporting libraries for any PL into the database and set up appropriate permissions for PL functions to call them.

Anyway, seems like there might be a more general problem to be solved here.

I might argue that the arrow of generalization points the other way: if we add a general way to define a new kind of database object, then we can easily define PL dependencies (crates, jar files, whatnot) as new kinds of database object. But I'm not sure that PL dependencies cover all the new kinds of object an extension could want to supply.

There might end up being enough varying details in how different PLs manage dependencies to make any single system catalog for PL dependencies an imperfect fit somewhere. It may be enough for PL/Rust to have its own schema for crates, PL/Java its own schema for jars, and so on. (PL/Java already has a start on that, of course, it just can't be Done Right without dependency support.)

That wouldn't necessarily stop several PLs from agreeing on a common dependency-management approach and schema (maybe it could be supplied by a separate extension they would all depend on).
PL/Java's way might still look different just because it is specified in ISO 9075-13, and other PL designers with the freedom to choose otherwise might not want their dependency management to look like what the SQL committee came up with.

It has crossed my mind that if PL/Java were to depart from the ISO spec and represent Java dependencies some other way, that might look like a database-internal implementation of a Maven repository.

[mkindahl]

Hi Chapman!

I ran into a similar problem when we implemented the job handling in TimescaleDB. Each job has an owner and associated procedures, and if you drop the owner or the procedure, we want the dependency to be tracked so that you either get an error message that you have a dependency, or can use cascade to delete the job.

When I tried to use the recordDependencyOn I ran into the issue that it only allowed specific objects (just as you mention), so just being able to register an arbitrary table and a OID-like value should be enough.

Strictly speaking, I do not think that it needs to be part of an extension but I do not think that is a serious limitation since in most cases these will be in an extension.

Thanks for writing down this proposal. I will take a look at it over the coming days.

[theory]

This reminds me of an idea I had, in thinking about the problem of making third-party packages available to trusted languages. Today, if you want to make Perl modules or Rust crates available to PL/Perl or PL/Rust — well, you can't.

But what if there was core support for PL dependencies? The idea is something like this:

There's a system catalog for PL dependencies. All the dependency stuff you mention would apply to their rows, such as ACLs.

Each row defines some unique name, perhaps associated with a PL, and then there's a payload: JAR file, rust Crate, Perl module, etc.

Where possible, the Postgres PL implementation hooks into the module-loading API of the PL (e.g., Perl's use and Java's import (this gets hand-wavy, as I think a Java import is different from using a JAR). When a PL function imports a thing, the PL implementation looks in the PL dependency table, and if the item is there, loads it.

This allows the superuser to load supporting libraries for any PL into the database and set up appropriate permissions for PL functions to call them.

Anyway, seems like there might be a more general problem to be solved here.

[yrashk]

@theory David, thanks for posting this. Indeed, this is a problem worth trying to address. Outside of truly external dependencies (ones that we'd need to download), bundling/packaging PL's dependencies is important if we want to bring the full value of those PL's ecosystems and, in fact, the modality of how programs in those languages are written. They aren't typically written in isolation in individual functions, but in files in an editor, so there's a mismatch.

To this end, I've worked on the first crude iteration of omni_vfs that provides an abstraction of the filesystem for the purpose of dynamic file loading, either from a local filesystem (in development) or tables and third-party sources like S3 (in production). It's not yet perfect, but it did show some promise.

It's a different approach than database objects, as when used with non-database backends, these VFSs intentionally do not represent anything in the database. Instead, the focus is on enabling convenient D3X (development, debugging and deployment experiences) as mapping source code and related artifacts to what's in the repository/working directory of the project is what developers focus on.