Open RomanTkachuk opened 7 years ago
Hello @RomanTkachuk , It is a well-known PostgreSQL bug.
Hi!
Sorry, thanks.
2017-02-07 22:03 GMT+02:00 Vlad Arkhipov notifications@github.com:
Hello, @RomanTkachuk https://github.com/RomanTkachuk It is a well-known PostgreSQL bug http://www.postgresql.org/message-id/20150707072942.1186.98151@wrigleys.postgresql.org .
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/arkhipov/temporal_tables/issues/28#issuecomment-278123181, or mute the thread https://github.com/notifications/unsubscribe-auth/AQeADKC3kxH76IEgBIsvhEWkAugaDqBUks5raM4JgaJpZM4L5efL .
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 thanheap_openrv
do not correctly fill it for tables created byLIKE
. 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
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 thanheap_openrv
do not correctly fill it for tables created byLIKE
. Workaround - create the same table without LIKE