Postgresql – strategy to use timestamp as primary key

postgresqlprimary-keytimestamp

The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that strategy, because I assume that the machine cannot do two things at the same "time"…right?

I am working on a database with huge hibernate sequences in the keys, so I thought of using timestamps as a solution. Are there any approaches like this that are used in practise that work with any database? That would be good to know.

EDIT : What I am asking is that since no two moments of time are the same for us (humans), does that also hold true for computers? I know that the computers track time through the vibration of some crystal (not sure how small a time it can measure).

In the database I have some table where no primary key is applicable except time (Audit trail), and specifying a sequence (hibernate_sequence) increases the number to an extremely high degree which makes it inconvenient to work with other tables as it affects all tables with sequence as primary key.

So, if PostgreSQL can guarantee that no two insert/update operations will occur at the same timestamp, then I could use it as a primary key, BUT if not then obviously I cannot use this solution.

Basically the question is: Does PostgreSQL provide any guarantee (through some approach or configuration) that no two records will be inserted/updated at the same "time"?

Thanks

Best Answer

Not a good idea.

PostgreSQL provides many functions that pertain to current timestamps,

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

The problem is none of these are insertion time. You have multiple backends. Each backend can process one transaction. These can transactions can start at the same time, or they can call these functions at the same time (they'll be on different threads/processes).

Direct reference of sequence and using concat

One possible solution is to reference the seqence in insert statement directly and prepend your node-id. A similar question including answer you can find here: https://stackoverflow.com/a/17925601/4206293

Using a UUID

Another possible solution is, if you don't need you node-id in the primary-key field, you can use the uuid-ossp extension which provides the type uuid and the functions to generate uuids: http://www.postgresql.org/docs/9.4/static/uuid-ossp.html

Use a trigger for these solutions

For both solutions: you can use a trigger to set the primary key.

Example:

-- table
CREATE TABLE test(
   id character varying(10) NOT NULL,
  "name" character varying,
   CONSTRAINT idx_pk PRIMARY KEY (id)
);

-- seqence
CREATE SEQUENCE test_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Create Function
CREATE OR REPLACE FUNCTION insert_trigger() 
RETURNS TRIGGER    
AS $$
BEGIN
   NEW.id := '100' || nextval('test_seq')::TEXT;
   RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- add Trigger
CREATE TRIGGER insert_table_trigger
BEFORE INSERT ON pkTable
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

If you now insert a value in the table

INSERT INTO test (name) VALUES ('test text');

you get your primary key '1001'

Using MINVALUE and MAXVALUE

Another way is you use the MINVALUE and MAXVALUE of SEQUENCE to define an numeric space:

CREATE SEQUENCE node100_seq
  INCREMENT 1
  MINVALUE 100000000000
  MAXVALUE 100999999999
  START 100000000000
  CACHE 1;

CREATE SEQUENCE node200_seq
  INCREMENT 1
  MINVALUE 200000000000
  MAXVALUE 200999999999
  START 200000000000
  CACHE 1;

Best Answer

Not a good idea.

Related Solutions

Database Design – Is Matriculation Number a Good Primary Key?

Sql-server – Non-sequential Primary Key performance in PostgreSQL 9.3

Direct reference of sequence and using concat

Using a UUID

Use a trigger for these solutions

Using MINVALUE and MAXVALUE

Related Question