koji

Clone

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

file added
+16 
@@ -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 years ago

1.20 to 1.21

:thumbsup: for the code

1 new commit added

  • fix version
4 years 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 years ago

Commit 08236dd fixes this pull-request

Pull-Request has been merged by tkopecek
4 years ago

Metadata Update from @jcupova:
- Pull-request tagged with: testing-done
4 years ago
Changes Summary 2
+16
file added
docs/schema-upgrade-1.20-1.21.sql
+2 -2
file changed
docs/schema.sql
Powered by Pagure 5.13.3
Documentation • File an Issue • About • SSH Hostkey/Fingerprint
© Red Hat, Inc. and others.