{"action":"created","issue":{"url":"https://api.github.com/repos/decred/dcrdata/issues/327","repository_url":"https://api.github.com/repos/decred/dcrdata","labels_url":"https://api.github.com/repos/decred/dcrdata/issues/327/labels{/name}","comments_url":"https://api.github.com/repos/decred/dcrdata/issues/327/comments","events_url":"https://api.github.com/repos/decred/dcrdata/issues/327/events","html_url":"https://github.com/decred/dcrdata/issues/327","id":292672935,"number":327,"title":"Full transaction list in address view with postgres","user":{"login":"raedah","id":15878246,"avatar_url":"https://avatars2.githubusercontent.com/u/15878246?v=4","gravatar_id":"","url":"https://api.github.com/users/raedah","html_url":"https://github.com/raedah","followers_url":"https://api.github.com/users/raedah/followers","following_url":"https://api.github.com/users/raedah/following{/other_user}","gists_url":"https://api.github.com/users/raedah/gists{/gist_id}","starred_url":"https://api.github.com/users/raedah/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/raedah/subscriptions","organizations_url":"https://api.github.com/users/raedah/orgs","repos_url":"https://api.github.com/users/raedah/repos","events_url":"https://api.github.com/users/raedah/events{/privacy}","received_events_url":"https://api.github.com/users/raedah/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":8,"created_at":"2018-01-30T07:01:26Z","updated_at":"2018-04-07T22:59:01Z","closed_at":null,"author_association":"MEMBER","body":"Jon Chappelow [11:15 PM]\r\nIn full mode, with psql, we get the transactions for a certain address by querying the `addresses` table, which is structured as follows...\r\neach row is an occurence of an address in a certain transaction output\r\nSo there are multiple rows in this table for a given address\r\nThus, getting all the rows for a given address provides you right away with all the *funding transactions*\r\nA spending transaction is defined as a transaction that spends one of these outpoints.  Because we want to simulate the concept of a balance for an address, we record these spending transactions in the addresses table as additional columns (edited)\r\nSo each row has 0 or 1 spending transactions, just as a transaction outpoint may only be spent once\r\nIf the spending transactions _columns_ are still `NULL`, then it is a UTXO (unspend transaction outpoint)\r\nIf there is spending txn data in those columns for some row, then that outpoint (funded via some address we're interested in) is spent\r\nIt's nice because when you get a row, you potentially get two transactions: the one that funded that outpoint (w/ an address) and one that spent that outpoint\r\nThis is very logical to me and reflects how coins actually flow in Decred (and Bitcoin, etc.)\r\nWith the `searchrawtransactions` RPC, it pulls any txns but it's really confusing.\r\n\r\nI'm looking at how we use `SearchRawTransactionsVerbose` for a solution, but there's nothing simple or quick\r\nIf the 10 most recent transactions spend outpoints w/ the given address, those will show first (edited)\r\nBut the full mode focuses on funding transactions, which in this example are older\r\nso it would show the 10 most recent funding transactions with any spendingtransactions right below the funding one (paired), and possibly none of the 10 spending transactions that `SearchRawTransactionsVerbose` returned (edited)\r\nThe psql addresses table does not even store tx heights, just hash and vout index.  The ordering comes from the query ordering by row id, which is the same ordering as the height that the funding tx would give\r\n2.0 will bring a db scheme change requiring a rebuild of psql, so if we want to add spend height (and recieve/fund height) to the addresses table, we can do that\r\nthat would allow us to query w/ \"order by spend_height limit 10\", for example (edited)\r\nnot sure how far that would get us though...\r\nI'd only like to add data to the tables that is actually useful\r\n\r\nraedah [3:02 AM]\r\nEach row is an occurrence of an address in a certain transaction output, and we are simply querying the table and getting the corresponding txhash. The same rows being returned also contain the txhash of the spending transaction (which is all the transactions that use that address as the input.) Sounds like we already ahve the complete return set we need, it would just be a matter of including the spending txhash set in the list of transactions we are displaying.\r\nWe could add a drop down filter to select (All, Funding, Spending) so that a user could narrow what they are viewing.\r\n\r\nJon Chappelow [3:24 AM]\r\nYes, we do have the data we need, but the thing is the ordering and how that affects the offset and limit parts of the query (edited)\r\nWe use the `...ORDER BY id LIMIT n OFFSET x;` query so we don't get the huge list when we just want n starting at x (edited)\r\nWe do show the spending transactions, but just the ones that corresponding to the next N funding transactions (edited)\r\nWe can write separate queries for spending and funding, but...\r\nThe only order that makes sense for the spending ones is the order from the funding ones of the same rows\r\nhttps://github.com/dcrdata/dcrdata/blob/master/db/dcrpg/internal/addrstmts.go#L46-L56\r\n\r\nraedah [4:00 AM]\r\nShould be able to be done with some joins and perhaps a temp table.\r\n\r\nJon Chappelow [4:18 AM]\r\nTo have both spending and funding in the same web page table, there needs to be some common ordering (edited)\r\nThe spending and funding are linked, so I just tag on any spending one\r\nSeems like the spend height, or a second query into the transactions transactions table (different table) would be needed\r\nHmm, but to limit the result from the address table query, some way to order the spend txns separately from the funding ones is needed still\r\n"},"comment":{"url":"https://api.github.com/repos/decred/dcrdata/issues/comments/379505190","html_url":"https://github.com/decred/dcrdata/issues/327#issuecomment-379505190","issue_url":"https://api.github.com/repos/decred/dcrdata/issues/327","id":379505190,"user":{"login":"chappjc","id":9373513,"avatar_url":"https://avatars1.githubusercontent.com/u/9373513?v=4","gravatar_id":"","url":"https://api.github.com/users/chappjc","html_url":"https://github.com/chappjc","followers_url":"https://api.github.com/users/chappjc/followers","following_url":"https://api.github.com/users/chappjc/following{/other_user}","gists_url":"https://api.github.com/users/chappjc/gists{/gist_id}","starred_url":"https://api.github.com/users/chappjc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chappjc/subscriptions","organizations_url":"https://api.github.com/users/chappjc/orgs","repos_url":"https://api.github.com/users/chappjc/repos","events_url":"https://api.github.com/users/chappjc/events{/privacy}","received_events_url":"https://api.github.com/users/chappjc/received_events","type":"User","site_admin":false},"created_at":"2018-04-07T22:59:01Z","updated_at":"2018-04-07T22:59:01Z","author_association":"MEMBER","body":"> Concerning the duplication of data is that if funding_tx_hash can be obtained from the transactions table using funding_tx_row_id then having funding_tx_hash in the addresses table is what I am calling Duplication of data. I have already stated the demerits of having duplicate data thus I should not repeat them.\r\n\r\nYes, I acknowledged this in my previous response.  The concern I raised is that you need another query to get the hash from the other table."}}
{"actor":{"display_login":"chappjc"}}