HafSQL v2.7.0

[mahdiyari]

HafSQL v2.7.0: changes for public database users

Removed tables

• hafsql.rc_delegations_table is gone. Use the hafsql.rc_delegations view, which now reads from balance_tracker.
• hafsql.proposal_approvals_table is gone. Use the hafsql.proposal_approvals view, which now reads from hafbe.

Removed columns from hafsql.accounts_table

reward_hive_balance, reward_hbd_balance, reward_vests_balance, to_withdraw, vesting_withdraw_rate, withdrawn and proxy are gone. The hafsql.accounts view still has all of them under the same names. They now come from balance_tracker / hafbe. If you queried accounts_table directly, switch to the view.

View columns that changed (same names, different values or types)

View.column	Before	Now
delegations.vests	raw ×1,000,000	VESTS, numeric(27,6)
accounts.incoming_vests / outgoing_vests	raw ×1,000,000	VESTS. *_hp columns unchanged
accounts.to_withdraw	remaining amount, dropped after each payout	total power-down amount (as in hived)
accounts.vesting_withdraw_rate	to_withdraw / 13	rounded up, as hived does since HF21
accounts.next_vesting_withdrawal	drifted 3s per weekly payout	exact. NULL when no power down is active
rc_delegations.rc	text	numeric
rc_delegations.timestamp	last sync time of the row	block time of the delegation op
comments.net_rshares	sum of absolute rshares	signed sum (downvotes subtract)
comments.abs_rshares	n/a	new column: sum of absolute rshares
comments.total_rshares / net_rshares	changed votes counted twice	only each voter's latest vote counts
operation_account_update*_table.json_metadata / posting_json_metadata	{} when the op left it empty	NULL (empty means "unchanged" on chain)

Behavior changes

• Comments deleted and then re-posted at the same permlink are now a new row with a new id, the same as hivemind. Before, they stayed marked as deleted.
• Weekly market buckets now run Monday to Sunday. Before, Sunday trades landed in the next week. Existing buckets are rebuilt.
• hafsql.get_timestamp, hafsql.id_from_timestamp and comments.cashout_time / last_payout now give correct results when your session timezone isn't UTC.
• hafsql.get_timestamp now also accepts a block number (int4).
• hafd.operation_id_to_type_id was removed from the docs.

Fixes (no query changes needed)

• Reblog permlinks were wrong.
• Account metadata was wiped by updates with empty metadata.
• Comment titles were blanked by edits with an empty title.
• get_balance(account, block) and balances_history were slow.

Comments

[databaron]

Making net_rshares a signed sum and counting only each voter's latest vote is a big fix for anyone analyzing curation data. Does it recalculate older comments too, or only rows synced after the upgrade?

[mahdiyari]

All the data is retroactively fixed.

[sequentialvibe]

Swapping the raw times-a-million vests columns for proper numeric values is a change everyone querying the views will quietly appreciate.