Problem with arrays

[RomanTkachuk]

http://dba.stackexchange.com/questions/163340/temporal-tables-extension-error-with-array-columns/163459#163459do not

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[]

I found than problem is in check_attr where equal attndims. Main problem than heap_openrv do not correctly fill it for tables created by LIKE. Workaround - create the same table without LIKE

[arkhipov]

Hello @RomanTkachuk ,
It is a well-known PostgreSQL bug.

[RomanTkachuk]

Hi! Sorry, thanks. 2017-02-07 22:03 GMT+02:00 Vlad Arkhipov <[email protected]>:

…

Hello, @RomanTkachuk <https://github.com/RomanTkachuk> It is a well-known PostgreSQL bug ***@***.***> . — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#28 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AQeADKC3kxH76IEgBIsvhEWkAugaDqBUks5raM4JgaJpZM4L5efL> .

[mdrokz]

http://dba.stackexchange.com/questions/163340/temporal-tables-extension-error-with-array-columns/163459#163459do not

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[]

I found than problem is in check_attr where equal attndims. Main problem than heap_openrv do not correctly fill it for tables created by LIKE. Workaround - create the same table without LIKE

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 $$;

Leaving this here in case anybody encounters the same issue