deso-protocol / deso-protocol/postgres-data-handler

postgres-data-handler migrations timeout - public_key_first_transaction

Đang mở
#107 2 bình luận 0 reaction 0 người được giao Xem trên GitHub
Ngôn ngữ chính
Go
Star
2
Fork
5
Chỉ số merge pull request
Không có pull request nào được merge trong 30 ngày

Mô tả

In re:

https://github.com/deso-protocol/postgres-data-handler/blob/cbef29c7f74bb3c1ef8a2fdcdd312bf2b2dde133/migrations/post_sync_migrations/migration_helpers.go#L85

This hardcoded time-out fails with the construction of the public_key_first_transaction table due to the sheer volume of transactions to be processed this late in the sync process.

The specific migration in question:
https://github.com/deso-protocol/postgres-data-handler/blob/cbef29c7f74bb3c1ef8a2fdcdd312bf2b2dde133/migrations/post_sync_migrations/20230713000002_create_statistic_views.go#L15
```
CREATE TABLE public_key_first_transaction (
public_key VARCHAR PRIMARY KEY ,
timestamp TIMESTAMP,
height BIGINT
);

CREATE INDEX idx_public_key_first_transaction_timestamp
ON public_key_first_transaction (timestamp desc);

CREATE INDEX idx_public_key_first_transaction_height
ON public_key_first_transaction (height desc);

INSERT INTO public_key_first_transaction (public_key, timestamp, height)
select apk.public_key, min(b.timestamp), min(b.height) FROM affected_public_key apk
JOIN transaction t ON apk.transaction_hash = t.transaction_hash
JOIN block b ON t.block_hash = b.block_hash
group by apk.public_key;
```

**Suggested fixes:**

**⌛ Environment Variable** - (workaround) expose an environment variable that operators can set for any such failures during the sync / migrations process.

**🔥 Re-order migration process** - (full fix) prepare these table relationships and indices BEFORE mass sync occurs. This would remove this huge overhead so late in the process and would mean that postgres itself would build this table automatically as the sync proceeds.

Hướng dẫn đóng góp

Chưa lập chỉ mục được hướng dẫn đóng góp cho kho mã nguồn này

Đánh giá

Issue này chưa được đánh giá.

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.