Overview Update utransactions &am

<g-emoji class="g-emoji" alias="+1" fallback-src="https://github.githubassets.com/imag

Hey <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url=

Update transaction tables to reduce queries dispatched & executed by Postgres about explorer-backend HOT 3 CLOSED

alephium commented on September 17, 2024

Update transaction tables to reduce queries dispatched & executed by Postgres

from explorer-backend.

Comments (3)

tdroxler commented on September 17, 2024 1

👍 all good to close it

from explorer-backend.

tdroxler commented on September 17, 2024

We do use inputs and outputs for example to get the balance of an address:

explorer-backend/app/src/main/scala/org/alephium/explorer/persistence/queries/TransactionQueries.scala

Lines 303 to 327 in 08fc34a

 private val getBalanceQuery = Compiled { address: Rep[Address] => 

 outputsTable 

 .filter(output => output.mainChain && output.address === address) 

 .map(output => (output.key, output.amount, output.lockTime)) 

 .joinLeft(inputsTable.filter(_.mainChain)) 

 .on(_._1 === _.outputRefKey) 

 .filter(_._2.isEmpty) 

 .map { case ((_, amount, lockTime), _) => (amount, lockTime) } 

 } 

 def getBalanceAction(address: Address): DBActionR[(U256, U256)] = { 

 getBalanceQuery(address).result.map { outputs => 

 val now = TimeStamp.now() 

 outputs.foldLeft((U256.Zero, U256.Zero)) { 

 case ((total, locked), (amount, lockTime)) => 

 val newTotal = total.addUnsafe(amount) 

 val newLocked = if (lockTime.map(_.isBefore(now)).getOrElse(true)) { 

 locked 

 } else { 

 locked.addUnsafe(amount) 

 } 

 (newTotal, newLocked) 

 } 

 } 

 }

no tx involved here.

While writing this I also realize we need to have those two tables for example to be able to find the spent output when searching the inputs:

explorer-backend/app/src/main/scala/org/alephium/explorer/persistence/queries/TransactionQueries.scala

Lines 251 to 266 in 08fc34a

 private val getInputsQuery = Compiled { (txHash: Rep[Transaction.Hash]) => 

 mainInputs 

 .filter(_.txHash === txHash) 

 .join(mainOutputs) 

 .on(_.outputRefKey === _.key) 

 .sortBy(_._1.order) 

 .map { 

 case (input, output) => 

 (input.hint, 

 input.outputRefKey, 

 input.unlockScript, 

 output.txHash, 

 output.address, 

 output.amount) 

 } 

 }

In the UTXO model, an input is always linked to an older output, but when we insert the transaction with the outputs, we don't know in advance which inputs of a future tx will use those outputs. So we have to do all those join between outputs/inputs` table.

So I guess we need to go for option 1

Your thoughts @polarker ?

from explorer-backend.

simerplaha commented on September 17, 2024

Hey @tdroxler, you are right about this on our Slack conversion. I got Slick confused with Hibernate. In Hibernate adding foreign-key-constraints automatically optimises queries to use joins but in Slick we have to do this manually. My excuse: haven't used ORMs in a while :(

And now since we are writing raw SQL, adding foreign-key-constraints to optimise for performance makes even less sense. So this issue is invalid.

Thank you for pointing this out. If you are ok I will close this task?

from explorer-backend.

Update transaction tables to reduce queries dispatched & executed by Postgres about explorer-backend HOT 3 CLOSED

Comments (3)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent

	private val getBalanceQuery = Compiled { address: Rep[Address] =>
	outputsTable
	.filter(output => output.mainChain && output.address === address)
	.map(output => (output.key, output.amount, output.lockTime))
	.joinLeft(inputsTable.filter(_.mainChain))
	.on(_._1 === _.outputRefKey)
	.filter(_._2.isEmpty)
	.map { case ((_, amount, lockTime), _) => (amount, lockTime) }
	}

	def getBalanceAction(address: Address): DBActionR[(U256, U256)] = {
	getBalanceQuery(address).result.map { outputs =>
	val now = TimeStamp.now()
	outputs.foldLeft((U256.Zero, U256.Zero)) {
	case ((total, locked), (amount, lockTime)) =>
	val newTotal = total.addUnsafe(amount)
	val newLocked = if (lockTime.map(_.isBefore(now)).getOrElse(true)) {
	locked
	} else {
	locked.addUnsafe(amount)
	}
	(newTotal, newLocked)
	}
	}
	}

	private val getInputsQuery = Compiled { (txHash: Rep[Transaction.Hash]) =>
	mainInputs
	.filter(_.txHash === txHash)
	.join(mainOutputs)
	.on(_.outputRefKey === _.key)
	.sortBy(_._1.order)
	.map {
	case (input, output) =>
	(input.hint,
	input.outputRefKey,
	input.unlockScript,
	output.txHash,
	output.address,
	output.amount)
	}
	}