RE: How SPS Stakeholders and Staking HP and HBD?

avatar

You are viewing a single comment's thread:

This query seems better but still takes too long for "spammy" accounts. It's fine though.

Those tables are not created by me. They are from haf balance tracker. Those tables store each balance separated by their symbol aka nai. So for HIVE balance you have one row and another row for HBD balance. It would be storing VESTS so your can use vests_to_hive function.

Anyway, I plan on dropping balances from hafsql and depending on balance tracker. I can probably recreate the same view you are using so shouldn't be any big breaking changes. I should probably create more views and take advantage of other HAF apps/tables including the two mentioned tables.

hive-169191
0
0
0.000
9 comments
avatar

Nice info we are getting closer... if this better I could start using this part of the tables.

Only now i'm missing the delegated do you know why that is not part of the VESTS balance?

WITH nai_map (nai, asset_name) AS (
  VALUES
    (21, 'HIVE'),
    (13, 'HBD'),
    (37, 'VESTS')
)
SELECT 
    a.name AS account_name,
    x.updated_at,
    x.source_op,
    x.source_op_block,
    -- nm.asset_name,
    -- x.balance,
    /* Convert to HIVE only when the asset is VESTS */
    CASE 
      WHEN nm.asset_name = 'VESTS' 
        THEN hafsql.vests_to_hive(x.balance/1e6,  a.block_num)
      ELSE x.balance/1e3
    END AS balance,
    /* The unit of balance_value */
    CASE 
      WHEN nm.asset_name = 'VESTS' THEN 'HP'
      ELSE nm.asset_name
    END AS asset,
    s.balance AS saving
FROM hafbe_bal.balance_history_by_month x
JOIN hafd.accounts a 
  ON x.account = a.id
LEFT JOIN hafbe_bal.saving_history_by_month s
  ON s.account    = x.account
 AND s.updated_at = x.updated_at
 AND s.nai        = x.nai
LEFT JOIN nai_map nm
  ON x.nai = nm.nai
WHERE a.name = 'beaker007'
ORDER BY x.updated_at DESC;

image.png

0
0
0.000
Reply
avatar

There is no historical tracking of delegations. There are only current values available.

0
0
0.000
Reply
avatar

Oke clear. you cannot have everything 😁 some limitation over performance that is the question.

@azricon, how important do think it is to include the delegations?

0
0
0.000
Reply
avatar

I can take a look later to see if I can come up with a query that tracks historical delegations and make that a materialized view which can refresh once an hour or so depending on the query.

0
0
0.000
Reply
avatar

Do not what to spam you 😂. But i found a another method to limit the call that i will commit for now

This does the first last record and every first of the month, exactly what is want

WITH bh AS (
  SELECT
    bh.*,
    hb.timestamp AS block_timestamp,
    date_trunc('month', hb.timestamp) AS month_start,
    ROW_NUMBER() OVER (
      PARTITION BY bh.account_name
      ORDER BY bh.block_num ASC
    ) AS rn_first,
    ROW_NUMBER() OVER (
      PARTITION BY bh.account_name
      ORDER BY bh.block_num DESC
    ) AS rn_last,
    ROW_NUMBER() OVER (
      PARTITION BY bh.account_name, date_trunc('month', hb.timestamp)
      ORDER BY hb.timestamp ASC, bh.block_num ASC
    ) AS rn_month
  FROM hafsql.balances_history bh
  JOIN hafsql.haf_blocks hb
    ON hb.block_num = bh.block_num
  WHERE bh.account_name IN ({placeholders})
)
SELECT
  *
FROM bh
WHERE rn_first = 1
   OR rn_last  = 1
   OR rn_month = 1
ORDER BY account_name, block_num DESC;

limited the number of account at once to 5

Thanks again for the help 🙏

0
0
0.000
Reply
avatar

You are not spamming. This one takes too long for top witnesses.

0
0
0.000
Reply
avatar

BTW: what i also can do easily is limit the request one account at the time

0
0
0.000
Reply
avatar

There is a limit on how many concurrent connections you can have per IP. Currently it is set to 6. So it's not a problem on the database side but your will get errors so you should probably setup a pool on the client side and run queries on the pool.

0
0
0.000
Reply
avatar

oke thanks again just updated my pool to 6. it was on default 10

0
0
0.000
Reply