#2068 use real time for events
Merged 4 months ago by tkopecek. Opened 4 months ago by tkopecek.
tkopecek/koji issue1747  into  master

@@ -0,0 +1,16 @@ 

+ -- upgrade script to migrate the Koji database schema

+ -- from version 1.20 to 1.21

+ 

+ 

+ BEGIN;

+ 

+ -- make better events

+ ALTER TABLE events ALTER COLUMN time SET NOT NULL;

+ ALTER TABLE events ALTER COLUMN time SET DEFAULT clock_timestamp();

+ 

+ CREATE OR REPLACE FUNCTION get_event() RETURNS INTEGER AS '

+     INSERT INTO events (time) VALUES (clock_timestamp());

+     SELECT currval(''events_id_seq'')::INTEGER;

+ ' LANGUAGE SQL;

+ 

+ COMMIT;

file modified
+2 -2

@@ -7,12 +7,12 @@ 

  -- in the event that the system clock rolls back, event_ids will retain proper sequencing

  CREATE TABLE events (

  	id SERIAL NOT NULL PRIMARY KEY,

- 	time TIMESTAMP NOT NULL DEFAULT NOW()

+ 	time TIMESTAMP NOT NULL DEFAULT clock_timestamp()

  ) WITHOUT OIDS;

  

  -- A function that creates an event and returns the id, used as DEFAULT value for versioned tables

  CREATE FUNCTION get_event() RETURNS INTEGER AS '

- 	INSERT INTO events (time) VALUES (''now'');

+ 	INSERT INTO events (time) VALUES (clock_timestamp());

  	SELECT currval(''events_id_seq'')::INTEGER;

  ' LANGUAGE SQL;

  

NOW() is time of transaction start. For long transaction, there could be
race and some other transactions which ended earlier can have newer
events. clock_timestamp returns real time (changes during transaction),
so it should be better for this case.

Fixes: https://pagure.io/koji/issue/1747

Metadata Update from @tkopecek:
- Pull-request tagged with: testing-ready

4 months ago

1 new commit added

  • fix version
4 months ago

Getting an error applying the schema update:

ERROR:  syntax error at or near "TIMESTAMP"
LINE 1: ALTER TABLE events ALTER COLUMN time TIMESTAMP NOT NULL DEFA...
                                             ^

I think we need:

ALTER TABLE events ALTER COLUMN time SET NOT NULL;
ALTER TABLE events ALTER COLUMN time SET DEFAULT clock_timestamp();

Otherwise looks good :smile:

rebased onto b54c32a

4 months ago

Commit 08236dd fixes this pull-request

Pull-Request has been merged by tkopecek

4 months ago

Metadata Update from @jcupova:
- Pull-request tagged with: testing-done

3 months ago