On PostgreSQL, how can I alter a table to make it an inheritor from multiple parents?

Question

I am working on creating a function in C-language in Apache AGE extension that performs an ALTER TABLE command to inherit from a parent table.

According to the PostgreSQL documentation for CREATE TABLE, the INHERITS keyword can take a list of parent tables. However, in the documentation for ALTER TABLE, it only mentions inheriting from a single parent table using the syntax INHERIT parent_table.

My question is whether it's possible to inherit from a list of parent tables using the ALTER TABLE command in PostgreSQL. If so, what would be the correct syntax to achieve this? PS: I'm using the version 11 of PostgreSQL.

Here's the function I'm creating (it's not working, system shutting down unexpectedly)

static void alter_table_inherit(char *graph_name, char *label_name,
                                    char *schema_name, char *seq_name,
                                    Oid relid, List* parents)
{
    ParseState *pstate;
    AlterTableStmt *tbl_stmt;
    AlterTableCmd *tbl_cmd;
    RangeVar *rv;

    /* RangeVar represents the table being altered */
    rv = makeRangeVar(schema_name, label_name, -1);

    pstate = make_parsestate(NULL);
    pstate->p_sourcetext = "(generated ALTER TABLE command)";

    tbl_stmt = makeNode(AlterTableStmt);
    tbl_stmt->relation = rv;
    tbl_stmt->missing_ok = false;

    tbl_cmd = makeNode(AlterTableCmd);
    tbl_cmd->subtype = AT_AddInherit;
    tbl_cmd->def = (Node *) parents;
    tbl_stmt->cmds = lappend(tbl_stmt->cmds, tbl_cmd);

    tbl_stmt->cmds = list_make1(tbl_cmd);

    AlterTable(relid, AccessExclusiveLock, tbl_stmt);

    CommandCounterIncrement();
}

I think you need to run one ALTER TABLE statement for each parent to inherit from. — , Apr 11 '23 at 05:51

score 2 · Accepted Answer · answered Apr 11 '23 at 07:31

2

You can add multiple inheritance parents like this:

ALTER TABLE tab
   INHERIT parent1,
   INHERIT parent2;

In C, that will result in an AlterTableStmt, where cmds will be a List* consisting of two AlterTableCmd, one for each parent table. Don't set tbl_cmd->def to a List*!

Another thing that I notice is that you didn't set tbl_stmt->objtype, so it is probably set to OBJECT_ACCESS_METHOD, which is not correct.

It might be simpler to use the SPI interface to execute SQL statements.

I would not develop with an old PostgreSQL version like v11. If Apache Age doesn't support anything more recent, abandon it.

answered Apr 11 '23 at 07:31

Laurenz Albe

209,280
17
206
263

Thank you for the answer, it was clarifying! But does using the SPI interface have a significant impact on performance, or can I "abuse" it? And, yeah... Apache Age supports up to PG13, and is being developed for 14 and 15, so I guess I'll jump to that version then. – Marco Souza Apr 11 '23 at 14:01
1

Performance might be a tad slower, but you don't have to deal with the internals of `AlterTableStmt`, which may change from version to version. Surely you don't run millions of `ALTER TABLE` statements in a performance-critical code path, do you? – Laurenz Albe Apr 11 '23 at 14:41
Yeah I don't lol. Thanks! I'll do that. – Marco Souza Apr 12 '23 at 00:11

On PostgreSQL, how can I alter a table to make it an inheritor from multiple parents?

1 Answers1