Weird epoch start/end times

[erikd]

@disassembler reported this from testnet:

 id |      out_sum       | tx_count | blk_count | no |     start_time      |      end_time       
----+--------------------+----------+-----------+----+---------------------+---------------------
 66 |      1067003543579 |      123 |     21600 | 65 | 2020-06-15 16:52:16 | 2020-06-18 16:53:16
 67 |       578038294968 |      125 |     21600 | 66 | 2020-06-18 20:45:16 | 2020-06-23 18:25:56
 68 |      1592555038454 |      464 |     21600 | 67 | 2020-06-23 21:35:36 | 2020-06-28 18:09:56
 69 |       328656189722 |      269 |     21600 | 68 | 2020-06-29 02:41:36 | 2020-07-03 18:08:36
 70 |       569276928156 |       97 |     21588 | 69 | 2020-07-03 20:55:36 | 2020-07-08 18:33:16
 71 |       164742305235 |       66 |     21600 | 70 | 2020-07-08 23:06:16 | 2020-07-13 14:15:16
 72 |       624663372465 |      122 |     21567 | 71 | 2020-07-13 20:40:56 | 2020-07-18 02:26:56
 73 |       412396743090 |       56 |     21600 | 72 | 2020-07-20 07:37:56 | 2020-07-23 11:48:56
 74 |       273165637452 |       55 |     21528 | 73 | 2020-07-24 00:16:16 | 2020-07-28 17:13:36
 75 |     70266487006221 |      115 |     21429 | 74 | 2020-07-28 20:25:36 | 2020-08-02 12:35:36
 76 | 193001931295419280 |      126 |     21260 | 75 | 2020-08-02 20:59:16 | 2020-08-07 19:11:56
 77 |  31431127815958678 |      133 |     12464 | 76 | 2020-08-08 04:57:36 | 2020-08-10 19:12:16

The start and end times seem to be all over the place.

Version is refs/tags/3.1.0, testnet config from https://hydra.iohk.io/build/3670619/download/1/index.html

[erikd]

The times above very definitely are strange. The start_time and end_time for each block should be the time stamp on the first block and last block of the epoch respecively.

The epoch table is filled in by the queryCalcEpochEntry query. That query function was developed by accretion; first getting it working for mainnet (which was relatively easy) and then adding handling for special cases until it worked for unusual cases.

This was all fine for Byron and its testnets, but may no longer be acceptable for Shelley.

[erikd]

Ok, have managed to reproduce the problem. Querying the block table:

testnet=# select sum(tx_count) as tx_count, count(id) as blk_count, min(time), max(time) from block
               where epoch_no = 67 ;
 tx_count | blk_count |         min         |         max         
----------+-----------+---------------------+---------------------
      464 |     21600 | 2020-06-23 20:20:16 | 2020-06-28 20:19:56

but querying the epoch table:

testnet=# select * from epoch where no = 67 ;
 id |    out_sum    | tx_count | blk_count | no |     start_time      |      end_time       
----+---------------+----------+-----------+----+---------------------+---------------------
 68 | 1592555038454 |      464 |     21600 | 67 | 2020-06-23 21:35:36 | 2020-06-28 18:09:56
(1 row)

[erikd]

Stopping db-sync, then delete from epoch where no >= 67 ; and restarting db-sync results in the same weird results.

This suggests that the Esqueleto query is not being accurately translating to SQL.

[erikd]

Seems to have been an issue with the way the query was written.

The queryCalcEpochEntry used two queries and the start and end times were extracted from a query that joined block and tx. By rearranging the two queries it is possible to extract the start and end times in a query that only operates on the block table and the resulting start and end times are correct:

testnet=# select * from epoch where no >= 67 order by no asc ;
 id  |      out_sum       | tx_count | blk_count | no |     start_time      |      end_time       
-----+--------------------+----------+-----------+----+---------------------+---------------------
  96 |      1592555038454 |      464 |     21600 | 67 | 2020-06-23 20:20:16 | 2020-06-28 20:19:56
  97 |       328656189722 |      269 |     21600 | 68 | 2020-06-28 20:20:16 | 2020-07-03 20:19:56
  98 |       569276928156 |       97 |     21588 | 69 | 2020-07-03 20:20:16 | 2020-07-08 20:19:56
  99 |       164742305235 |       66 |     21600 | 70 | 2020-07-08 20:20:16 | 2020-07-13 20:19:56
 100 |       624663372465 |      122 |     21567 | 71 | 2020-07-13 20:20:16 | 2020-07-18 20:19:56
 101 |       412396743090 |       56 |     21600 | 72 | 2020-07-18 20:20:16 | 2020-07-23 20:19:56
 102 |       273165637452 |       55 |     21528 | 73 | 2020-07-23 20:20:16 | 2020-07-28 20:19:56
 103 |     70266487006221 |      115 |     21429 | 74 | 2020-07-28 20:20:16 | 2020-08-02 20:19:56
 104 | 193001931295419280 |      126 |     21260 | 75 | 2020-08-02 20:20:16 | 2020-08-07 20:19:56
 105 |  69416219804158377 |      176 |     14395 | 76 | 2020-08-07 20:20:16 | 2020-08-11 06:00:56
(10 rows)