BUG #14639: Different xmin values in a transaction

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.6 on 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 | 1493731429
528760492 | 10 | 1493731430
528760493 | 11 | 1493731431
528760494 | 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 partition should 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 | 1493731429
528760498 | 18 | 1493731430
528760498 | 19 | 1493731431
528760498 | 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