[BUGS] BUG #14639: Different xmin values in a transaction
От | ray.warren@artsalliancemedia.com |
---|---|
Тема | [BUGS] BUG #14639: Different xmin values in a transaction |
Дата | |
Msg-id | 20170502155739.24364.42206@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: [BUGS] BUG #14639: Different xmin values in a transaction
(Andres Freund <andres@anarazel.de>)
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 14639 Logged by: Ray Warren Email address: ray.warren@artsalliancemedia.com PostgreSQL version: 9.5.6 Operating system: Linux Description: Under certain conditions inserts in the same transaction are getting different xmin values. A reproducible example is below. I can work around it but I think this is a bug. select version(); version ----------------------------------------------------------------------------------------------------------PostgreSQL 9.5.6on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit --------------------------------------------------------------------------------------------------------- -- Create Table --------------------------------------------------------------------------------------------------------- CREATE TABLE partition_table ( id serial NOT NULL, timestamp double precision); --------------------------------------------------------------------------------------------------------- -- Create partition function --------------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION partition_table_function() RETURNS TRIGGER AS $BODY$ DECLARE _tablename text; _partition_key character varying (6); _timestamp_epoch double precision; _start_week_timestamp timestamp; _end_week_timestamp timestamp; _start_week_epoch double precision; _end_week_epoch double precision; _result record; BEGIN -- Derive the partition key from the timestamp that is stored as epoch. For this table we are using ISO Year and Week _timestamp_epoch := NEW."timestamp"; _partition_key := to_char((to_timestamp(_timestamp_epoch) AT TIME ZONE 'UTC')::date, 'IYYYIW')::text; -- Derive the partition name that will hold this data row _tablename := 'partition_table_'||_partition_key; -- Check if the partition needed for the current record exists PERFORM 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND c.relname = _tablename AND n.nspname = 'public'; -- If the partition needed does not yet exist, then we create it: IF NOT FOUND THEN -- Need to find the epoch values _start_week_timestamp := date_trunc('WEEK',(to_timestamp(_timestamp_epoch) AT TIME ZONE 'UTC')::date)::timestamp; _end_week_timestamp := (date_trunc('WEEK',(to_timestamp(_timestamp_epoch) AT TIME ZONE 'UTC')::timestamp)::timestamp + '1 week'); _start_week_epoch := extract(epoch from _start_week_timestamp); _end_week_epoch:= extract(epoch from _end_week_timestamp); -- create the partition table EXECUTE 'CREATE TABLE ' || quote_ident(_tablename) || ' (CHECK ( "timestamp" >= ' || quote_literal(_start_week_epoch) || ' AND "timestamp" < '|| quote_literal(_end_week_epoch) || ')) INHERITS (partition_table)'; END IF; -- Insert the current record into the correct partition, which we are sure will now exist. EXECUTE 'INSERT INTO ' || quote_ident(_tablename) || ' VALUES ($1.*)' USING NEW; RETURN NULL; EXCEPTION WHEN OTHERS THEN -- Retry on conflict, now the partition should exist. EXECUTE 'INSERT INTO ' || quote_ident(_tablename)|| ' VALUES ($1.*)' USING NEW; RETURN NULL; END; $BODY$ LANGUAGE plpgsql; --------------------------------------------------------------------------------------------------------- -- create trigger that calls the partition function --------------------------------------------------------------------------------------------------------- CREATE TRIGGER partition_table_trigger BEFORE INSERT ON partition_table FOR EACH ROW EXECUTE PROCEDURE partition_table_function(); begin transaction; insert into partition_table(timestamp) values (1493731429); insert into partition_table(timestamp) values (1493731430); insert into partition_table(timestamp) values (1493731431); insert into partition_table(timestamp) values (1493731432); commit; select xmin,* from partition_table; xmin | id | timestamp -----------+----+------------528760491 | 9 | 1493731429528760492 | 10 | 1493731430528760493 | 11 | 1493731431528760494 |12 | 1493731432 (4 rows)xmin values should be all be the same because they are done in the same transaction.If I comment out these 4 lines in the function--EXCEPTION WHEN OTHERS THEN-- -- Retry on conflict, now the partitionshould exist.-- EXECUTE 'INSERT INTO ' || quote_ident(_tablename) || ' VALUES ($1.*)' USING NEW;-- RETURN NULL; drop table partition_table_201718; begin transaction; insert into partition_table(timestamp) values (1493731429); insert into partition_table(timestamp) values (1493731430); insert into partition_table(timestamp) values (1493731431); insert into partition_table(timestamp) values (1493731432); commit; select xmin,* from partition_table; xmin | id | timestamp -----------+----+------------528760498 | 17 | 1493731429528760498 | 18 | 1493731430528760498 | 19 | 1493731431528760498 |20 | 1493731432 (4 rows) As expected all 4 rows have the same transaction id. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Andres FreundДата:
Сообщение: Re: [BUGS] Concurrent ALTER SEQUENCE RESTART Regression
Следующее
От: Andres FreundДата:
Сообщение: Re: [BUGS] BUG #14639: Different xmin values in a transaction