stats: optimism total contracts bug

[yvaskov]

blockscout api:

stats api:

reason

optimism cannot index all internal transactions and total_contracts uses new_contracts chart (like cache)

solution

write total_contracts as SQL not dependent counter

[yvaskov]

Total verified contracts affected as well

[bragov4ik]

Total verified contracts affected as well

@yvaskov seems to be in sync, isn't it?

[yvaskov]

@bragov4ik yeah, it caught up.

[bragov4ik]

We discussed the problem and possible solutions, so I'll summarize it here. There are 2 main ways to solve the problem (and 1 kinda funny):

1. Calculate total contracts independently from new_contracts using addresses table. +: quick fix, works, seems fast to query -: new_contracts remains inaccurate, query complexity increases cumulatively, additional load on db (since additional query is performed)
2. Fix new_contracts (probably using updated_at field) +: total contracts is consistent with blockscout's (to some degree), sql query results are getting reused -: complex logic (will take time to implement)
3. Request the counter value from blockscout (semantically the same as 1.) +: new_contracts remains inaccurate, full consistency with blockscout data, reusing sql results -: likely to be inconsistent with new_contracts (once it's fixed), kinda defers a responsibility of stats service

I think it makes sense to first implement 1. and later focus on 2. (we do not utilize 3. at the end 🙃 )

[bragov4ik]

btw about consistency of calculation methods:

There are two methods: via transaction lists and via addresses. I ran queries to check differences between results of them (on optimism sepolia; earlier today; queries are listed below):

• contracts found with addresses but not with transactions: 2069
• contracts found with transactions but not with addresses: 0

Thus, addresses seem to be a more accurate method.

Query for "contracts found with addresses but not with transactions":

SELECT contract_addresses_hashes.hash, contract_addresses_hashes.contract_code
FROM (
	SELECT
		DISTINCT ON (txns_plus_internal_txns.hash)
		txns_plus_internal_txns.hash,
		txns_plus_internal_txns.day
	FROM (
		SELECT
			t.created_contract_address_hash AS hash,
			b.timestamp::date AS day
		FROM transactions t
			JOIN blocks b ON b.hash = t.block_hash
		WHERE
			-- t.created_contract_address_hash NOTNULL AND
			b.consensus = TRUE AND
			b.timestamp != to_timestamp(0)
		UNION
		SELECT
			it.created_contract_address_hash AS hash,
			b.timestamp::date AS day
		FROM internal_transactions it
			JOIN blocks b ON b.hash = it.block_hash
		WHERE
			-- it.created_contract_address_hash NOTNULL AND
			b.consensus = TRUE AND
			b.timestamp != to_timestamp(0)
	) txns_plus_internal_txns
) contract_hashes
RIGHT JOIN (
	SELECT hash, contract_code FROM addresses WHERE contract_code IS NOT NULL
) contract_addresses_hashes
ON contract_hashes.hash = contract_addresses_hashes.hash
WHERE contract_hashes.hash IS NULL;

Query for "contracts found with transactions but not with addresses":

SELECT contract_hashes.hash, contract_hashes.day
FROM (
	SELECT
		DISTINCT ON (txns_plus_internal_txns.hash)
		txns_plus_internal_txns.hash,
		txns_plus_internal_txns.day
	FROM (
		SELECT
			t.created_contract_address_hash AS hash,
			b.timestamp::date AS day
		FROM transactions t
			JOIN blocks b ON b.hash = t.block_hash
		WHERE
			t.created_contract_address_hash NOTNULL AND
			b.consensus = TRUE AND
			b.timestamp != to_timestamp(0)
		UNION
		SELECT
			it.created_contract_address_hash AS hash,
			b.timestamp::date AS day
		FROM internal_transactions it
			JOIN blocks b ON b.hash = it.block_hash
		WHERE
			it.created_contract_address_hash NOTNULL AND
			b.consensus = TRUE AND
			b.timestamp != to_timestamp(0)
	) txns_plus_internal_txns
) contract_hashes
LEFT JOIN (
	SELECT hash, contract_code FROM addresses
) contract_addresses_hashes
ON contract_hashes.hash = contract_addresses_hashes.hash
WHERE contract_addresses_hashes.hash IS NULL;

[bragov4ik]

from this it might be viable to leave the calculation with addresses even after fixing chart new_contracts.

[bragov4ik]

seems to work well in staging blockscout api: stats api:

will close once the next release is shipped & it's confirmed that the problem dissapeared

[bragov4ik]

yep