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?

0

3 Answers 3

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)
2
  • Thanks. So is this a bug in LIKE, which sounds rather unlikely, or is the temporal tables extension doing something wrong? Feb 7, 2017 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. Feb 7, 2017 at 14:37
2

It is a long-standing PostgreSQL bug.

0

I also encountered the same issue and i was able to fix it by re altering the array columns with the same type

heres the script to do this for all history tables

DO $$ 
DECLARE
    current_table_name text;
    current_column_name text;
    current_type text;
    current_data_type text;
    new_type text;
BEGIN
    RAISE NOTICE 'Fixing array columns';
    FOR current_table_name IN (SELECT table_name FROM information_schema.tables WHERE table_name LIKE '%_history') 
    LOOP
        RAISE NOTICE 'Table %', current_table_name;
        FOR current_column_name, current_type, current_data_type IN 
            (SELECT column_name, udt_name, data_type 
             FROM information_schema.columns 
             WHERE table_name = current_table_name AND data_type LIKE 'ARRAY')
        LOOP
            -- Assuming you want to change the number of dimensions to 1 for array columns
            RAISE NOTICE 'Column % in table % has type %', current_column_name, current_table_name, current_type;
            IF current_data_type LIKE 'ARRAY' THEN
                new_type := substr(current_type, 2) || '[]';
                EXECUTE 'ALTER TABLE ' || current_table_name || ' ALTER COLUMN ' || current_column_name || ' TYPE ' || new_type;
                RAISE NOTICE 'Altered column % in table % to type %', current_column_name, current_table_name, new_type;
            END IF;
        END LOOP;
    END LOOP;
END $$;

i tested it by running test cases for my db model repository

Leaving this here in case anybody encounters the same issue

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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