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