4

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?

4

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)
  • Thanks. So is this a bug in LIKE, which sounds rather unlikely, or is the temporal tables extension doing something wrong? – Mad Scientist Feb 7 '17 at 14:28
  • Looks like it is PostgreSQL bug - function heap_openrv return incorrect value of attndims. If you want to use this extension for table with arrays you must create history table not via LIKE. – Roman Tkachuk Feb 7 '17 at 14:37
2

It is a long-standing PostgreSQL bug.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.