Postgresql – Temporal tables extension error with array columns

postgresql

I'm using the temporal tables Postgres extension (http://pgxn.org/dist/temporal_tables/) on Postgres 9.5.4 on Windows. This works well for most cases, but I'm running into a weird issue with array columns.

I've created some minimal steps to reproduce this, the setup of the table and the temporal tables extension is the following:

CREATE EXTENSION IF NOT EXISTS temporal_tables;

DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS test_history;

CREATE TABLE test
(
  id SERIAL PRIMARY KEY,
  a integer,
  directories text[],
  sys_period tstzrange NOT NULL
);

CREATE TABLE test_history (LIKE test);
CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON test FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'test_history', true);

And in separate transactions the following two commands:

INSERT INTO test(a) VALUES (1);


UPDATE test SET a = 5 WHERE id = 1;

I get the following error:

ERROR: column "directories" of relation "test" is of type text[] but column "directories" of history relation "test_history" is of type text[]

The error is nonsensical to me, both columns have the same type and the error even states this. This only happens when a text[] column is present in the table. It is not necessary to write to that column, it just has to be present. If I don't create the array column, there is no error.

The extension does not mention anything about incompatibility with arrays, and I'd expect that kind of restriction to be large enough to be mentioned. I'm wondering if there is anything wrong with the way I set up the tables, or if I'm missing anything else here. Is there any underlying reason why two text[] array columns would be of different type?

Any idea what actually causes this error and how I could get rid of it?

Best Answer

The problem is versioning.c:412:

 errmsg("column \"%s\" of relation \"%s\" is of type %s (%i) but column \"%s\" of history relation \"%s\" is of type %s (%i)",
                NameStr(attr->attname),
                RelationGetRelationName(relation),
                format_type_with_typemod(attr->atttypid,
                                                                 attr->atttypmod),
                attr->attndims,
                NameStr(history_attr->attname),
                RelationGetRelationName(history_relation),
                format_type_with_typemod(history_attr->atttypid,
                                                                 history_attr->atttypmod), history_attr->attndims)));

When I changed to show attndims, result was:

ERROR:  column "directories" of relation "test" is of type text[] (1) but column "directories" of history relation "test_history" is of type text[] (0)

I found the problem is in heap_openrv (PostgreSQL function) does not correctly fill this field for tables created by LIKE. I do not know how to repair it. But I found a solution:

CREATE TABLE test_history (
  id SERIAL PRIMARY KEY,
  a integer,
  directories text[],
  sys_period tstzrange NOT NULL
);

After that:

CREATE EXTENSION
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TRIGGER
INSERT 0 1
UPDATE 1
 id | a | directories |                            sys_period                             
----+---+-------------+-------------------------------------------------------------------
  1 | 1 |             | ["2017-02-07 15:12:16.383449+02","2017-02-07 15:12:16.387419+02")
(1 row)
Related Question