Thread: [BUGS] 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.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

On 2017-05-02 15:57:39 +0000, ray.warren@artsalliancemedia.com wrote:
> 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.

That's to be expected if you use savepoints (and EXCEPTION blocks use
those internally).  Why do you care about xmin value?

Greetings,

Andres Freund


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi Andres,

I care about xmin values because we have a very high number of inserts/updates/deletes and we get a Vacuum Freeze (from
autovacuum)occurring every few days so I want to reduce that happening. The application is doing inserts in batches so
Iexpected to see batches with the same xmin. When I found they were all different I then identified what I thought was
anissue with the partition function. We can work around it but I wasn't expecting this behaviour. 

Regards ... Ray

-----Original Message-----
From: Andres Freund [mailto:andres@anarazel.de]
Sent: 02 May 2017 17:13
To: Ray Warren
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14639: Different xmin values in a transaction

On 2017-05-02 15:57:39 +0000, ray.warren@artsalliancemedia.com wrote:
> 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.

That's to be expected if you use savepoints (and EXCEPTION blocks use those internally).  Why do you care about xmin
value?

Greetings,

Andres Freund



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs