Parse recent transactions with bitcoin core in pruned mode

[reardenlife]

You can still accept payments, simply use the master public key to derive addresses.
Once your core-server goes back up, all your payments (received when it was down) will get processed again.

Hmm... I had an impression that I have to use seed to generate addresses by certain derivation path.  But it is possible to use just bip44 public key to generate addresses by derivation path as well?

[1714056200]

1714056200

[1714056200]

1714056200

[1714056200]

1714056200

[1714056200]

1714056200

[1714056200]

1714056200

[bob123]

Hmm... I had an impression that I have to use seed to generate addresses by certain derivation path.  But it is possible to use just bip44 public key to generate addresses by derivation path as well?

Yes, the master public key (xpub / ypub / zpub depending on the address type you want) is enough to derive all public keys and therefore also addresses.
To derive the corresponding private key, you'll need the master private key (xpriv).

The addresses can be derived safely on an online machine while the private keys never touch any online device at all (for example).


The derivation 'hierarchy' is: Seed -> master private key -> master public key -> public key -> address.

[reardenlife]

I am still thinking that I should use the mempool and provide the service as soon as I see the correct amount designated to my address. It was pointed out to me that in rare cases, the transactions may be dropped from the mempool and that is why it is unreliable.  I was wondering if I can save the transaction in my DB and then detect if it disappeared from mempool and re-broadcast it myself.  It is still unreliable since my server can go down and fail to re-broadcast, but such risks are acceptable by me.

The question is - will the owner of the transaction be able to broadcast another transaction to the different address which depletes UTXO from their previous transaction to my address?  What is going to happen if such transactions will appear in the mempool at the same time (which try to spend from the same UTXO more than it actually has)?

[reardenlife]

I am reading articles like this one: https://www.coindesk.com/charts-determining-ideal-block-size-bitcoin

A side note on ‘coffee’
Users of the bitcoin network, and in particular businesses, tell us that fees have increased to the point where paying for coffee and other even smaller use cases (such as ad network payments) are not viable anymore.

One can hear about that "pay for a coffee with BTC" argument over and over again even in papers like WJS.
I am failing to understand how come the transaction fees become so high.
Say the Bitcoin block size is 1MB.  Even with a huge fee like 50k Satoshi per 1kb it follows that only 0.5 BTC will go to the miner from the all block transactions.  With a current block reward of 12.5 BTC per block, it is unclear what is the purpose of such high fees from financial perspective.  Simply put, there is not much difference for the miner between high fees or low fees.

I think that fees should be voluntary but progressive, depending on the amount of BTC being transferred.  The guy who transfers say 1000BTC should not rely on say electrum dynamic fee estimation and should have no problem setting up a really big fee like 1m Satoshi per kb, and expect his transaction to be included into the block right away.  At the same time, the guy who buys a coffee for a few $ should put a minimum fee and at the same time expect to get a service (in this case the transaction fees for him will be only about 2 cents).  In this case, the service provider should use a mempool explorer and get the transaction out of there.  If it drops from the mempool, he should pick it up and re-broadcast.  I doubt the coffee buyer will even try to double-spend by waiting for his transaction to be dropped from the mempool and then broadcasting the one with same inputs but different output right away.

The above described sounds like a common sense to me.  So I will continue to use mempool explorer.  I can't see any problem here.

[nc50lc]

The question is - will the owner of the transaction be able to broadcast another transaction to the different address which depletes UTXO from their previous transaction to my address?

By default, Bitcoin core won't allow you to re-spend a UTXO previously used by an unconfirmed tx.
But it's possible using 3rd-party tools to generate the raw TX or by doing some "extra technical work" with the client.

What is going to happen if such transactions will appear in the mempool at the same time (which try to spend from the same UTXO more than it actually has)?

Both will stay in the nodes' mempool (if allowed, ex. RBF) until one get mined making the other transaction invalid.
Some Blockexplorer like live.blockcypher.com marks those type of transactions red as "double-spend attempt!" for days even after the other transaction was discarded dropped.

[reardenlife]

The question is - will the owner of the transaction be able to broadcast another transaction to the different address which depletes UTXO from their previous transaction to my address?

By default, Bitcoin core won't allow you to re-spend a UTXO previously used by an unconfirmed tx.

Even if that unconfirmed tx was previously dropped from the mempool?  

Edited:
I am trying to figure out solution which will protect me from double-spend attack in case if I accept the tx from the mempool but then it will be dropped from it.
I wonder if I should re-broadcast such tx myself say every hour in case if they stuck in the mempool.

[nc50lc]

Even if that unconfirmed tx was previously dropped from the mempool?  

If it was dropped, then he can re-spend the UTXO as long as it was removed from his transaction history.
If not, the user can manually delete it using -zapwallettxes command.

I am trying to figure out solution which will protect me from double-spend attack in case if I accept the tx from the mempool but then it will be dropped from it.
I wonder if I should re-broadcast such tx myself say every hour in case if they stuck in the mempool.

What do you mean "stuck in the mempool"?
By default any unconfirmed tx will be dropped from a "node's mempool" after 2weeks (each node have their individual mempool).

The solution is: do not finalize a deal without any confirmation.

An indirect reply to post below: If you're not seeing the txid in any blockexplorer,
chances that it was dropped by most nodes is high.

[reardenlife]

Even if that unconfirmed tx was previously dropped from the mempool?  

If it was dropped, then he can re-spend the UTXO as long as it was removed from his transaction history.
If not, the user can manually delete it using -zapwallettxes command.

Aha.  So what if I will re-broadcast the tx which is stuck in the mempool every hour or so - will there be any penalties? Will it improve the chances of this transaction not to be dropped from the mempool?  Any elegant way to detect the moment when the tx gets dropped?

Edit: I was also wondering why the transactions gets dropped from the mempool.  Why not to store them in DB and simply wait for a good moment to insert them into the block based on the fees specified in them?  There is no risk of DDOS for the bitcoin infrastructure due to the minimum fee of 1k Satoshi per kb.

[ranochigo]

Aha.  So what if I will re-broadcast the tx which is stuck in the mempool every hour or so - will there be any penalties?

No. The node does not keep track of the transactions that has been dropped. They wouldn't know that they've had the transaction before in their mempool.

Will it improve the chances of this transaction not to be dropped from the mempool?

Technically, no. Nodes would still drop your transaction after some time but if you rebroadcast it periodically, you'll ensure that their mempool would contain your transaction for most of the time.

Any elegant way to detect the moment when the tx gets dropped?

No. A mempool is unique to every single node; it is possible for your transaction to exist in only some of the nodes on the network and not on the others. Keeping track of the entire network's mempool would be impossible.

[reardenlife]

What do you mean "stuck in the mempool"?
By default any unconfirmed tx will be dropped from a "node's mempool" after 2weeks (each node have their individual mempool).

I mean if the tx has low fee, it appears to me that it can get stuck in the mempool for the prolonged amount of time and then get dropped from it without any insertion into the block.

The solution is: do not finalize a deal without any confirmation.

That is the solution which works in theory.  In practice people do not want to wait.  They want everything and right now.

An indirect reply to post below: If you're not seeing the txid in any blockexplorer,
chances that it was dropped by most nodes is high.

Txid??   The transaction hash you mean?
 Anyways,  I don't use "any blockexplorers".  I am using a bitcoin core node in pruned mode.

[reardenlife]

Technically, no. Nodes would still drop your transaction after some time but if you rebroadcast it periodically, you'll ensure that their mempool would contain your transaction for most of the time.

Alright. Cool.  So I will periodically re-broadcast the transactions that I accepted (provided the service for) that where not included into the blockchain to ensure the tx stays in the mempool and will get mined sooner or later.  

[nc50lc]

_[1] Txid??   The transaction hash you mean?
Anyways,  I don't use "any blockexplorers".  _[2] I am using a bitcoin core node in pruned mode.

[1] Yes, it's shorter and widely used term.
The reason behind the reply is: blockexplorers usually have more than one nodes to connect to more peers than a regular user's node,
so if your transaction's TXID can't be seen by them, most of the network's node must have dropped that TX.

[2] Then there's no clear indicator if other nodes have already dropped your tx.
At least, after more than two weeks after the last broadcast, it's safe to assume that your tx was dropped.

[reardenlife]

The reason behind the reply is: blockexplorers usually have more than one nodes to connect to more peers than a regular user's node,
so if your transaction's TXID can't be seen by them, most of the network's node must have dropped that TX.

Does the bitcoin core provide the api to:
1.) Enumerate the other nodes connected to mine.
2.) Ask these nodes if they have a specific txid in their mempool.

Or it is just easier to re-broadcast every hour?  

[nc50lc]

Or it is just easier to re-broadcast every hour?  

This is unnecessary, nodes will keep it as long as stated in their "mempoolexpiry", 336 (336hrs/14days) default.

If you're planning to do this as a "protection against double spend" like what you've said somewhere above, it won't help.
If the double spend tx's fee is higher and a mining node accepted it/both, the one with the higher fee will be mined 1st.

What you need to do is don't set the TXs with RBF and set mempoolreplacement=0 for you to reject tx with double spent UTXO.
But some nodes will still accept replacement tx with mempoolreplacement=1 which is enabled by default.

Does the bitcoin core provide the api to:
1.) Enumerate the other nodes connected to mine.
2.) Ask these nodes if they have a specific txid in their mempool.

I have no idea, others might.It seems like the discussion is now off-topic based from the OP, perhaps you need to create a new thread.

[reardenlife]

If you're planning to do this as a "protection against double spend" like what you've said somewhere above, it won't help.
If the double spend tx's fee is higher and a mining node accepted it/both, the one with the higher fee will be mined 1st.

Interesting... So RBF allows a specification of a different destination address, which means that the attacker can successfully implement double-spend.
.. should I look into nSequence of the transaction I am accepting in order to determine if RBF is enabled on it perhaps?

It seems like the discussion is now off-topic based from the OP, perhaps you need to create a new thread

Uh oh.
The blockchain explorer on bash (as well as mempool explorer BTW) that I published above works just fine.  So the problem is solved.  Now we just talking about deployment-related issues which is somewhat within topic.

[reardenlife]

What you need to do is don't set the TXs with RBF and set mempoolreplacement=0 for you to reject tx with double spent UTXO.
But some nodes will still accept replacement tx with mempoolreplacement=1 which is enabled by default.

I've got a better idea.  Here is the main code of my mempool explorer:

Code:

# Get hashes of transactions in the mempool
#
sql="START TRANSACTION;"
Tx=$(bitcoin-cli -rpccookiefile=$cookie getrawmempool | jq -r .[])
sql=${sql}$(echo "$Tx" | sed -e "s/^/INSERT IGNORE INTO exp_mempool(hash) VALUES('/" | sed -e "s/$/');/");
sql="${sql}COMMIT;"

mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  $sql
EOF

# Cleanup
#
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  DELETE FROM exp_mempool WHERE tstamp < NOW() - INTERVAL 3 HOUR;
  DELETE FROM exp_mempool_tx WHERE tstamp < NOW() - INTERVAL 3 HOUR;
EOF

# Select only unprocessed transactions
#
Tx=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  SELECT hash FROM exp_mempool WHERE is_processed = FALSE ORDER BY tstamp ASC LIMIT 500;
EOF
)

# Process mempool transactions (populate exp_mempool_tx table)
#
_header "$(date --rfc-3339=seconds)"
sql="START TRANSACTION;"
IFS=$'\n'
aTx=($(echo "$Tx"))
nTx=$(echo "$Tx" | wc -l)
echo "transactions:$nTx"
if (( $nTx == 0 )); then
  exit 2
fi

tsstart=$(date +%s%N | cut -b1-13)
i=0
for txhash in $Tx; do
  if (( $(($i%100==42)) )); then
    tsnow=$(date +%s%N | cut -b1-13)
    opsec=$(echo "scale=2;$i/(($tsnow-$tsstart)/1000)" | bc)
    p=$(echo "$i*100/$nTx" | bc)
    printf "$p%% ($opsec tx/sec)\r"
  fi

set +e
  txjson=$(bitcoin-cli -rpccookiefile=$cookie getrawtransaction $txhash 1 $bhash 2>/dev/null)
  if (( $? != 0 )); then
set -e
    # Gone from the mempool already?
    #
    #echo "Deleting tx:$txhash"
    sql=${sql}"DELETE FROM exp_mempool WHERE hash = '$txhash' LIMIT 1;"$'\n'
    i=$((i+1))
    continue
  fi
set -e

  # Make sure the transaction doesn't support RBF
  #
  rbf=$(echo "$txjson" | jq -r '.vin[] | select(.sequence < 4294967294)' | wc -l)
  if (( $rbf > 0 )); then
    #echo "$txjson" > /tmp/tx/$txhash
    sql="${sql}UPDATE exp_mempool SET is_processed = TRUE WHERE hash = '$txhash' LIMIT 1;"$'\n'
    i=$((i+1))
    continue
  fi

  r=$(echo "$txjson" | jq -r '. as $tx | .vout[] | select(.scriptPubKey.type == "pubkeyhash") | $tx.txid, .value, .scriptPubKey.addresses[]' | grep . || true )
  if [ ! -z "$r" ]; then
    r=$(echo "$r" | awk 'NR%3==2 {printf("%d\n", sprintf("%.08f", $0*100000000)); next} {print $0}' | awk '{printf "'\''%s'\''," (NR%3==0?RS:FS),$1}')
    sql=${sql}$(echo "$r" | sed -e "s/^/INSERT INTO exp_mempool_tx(hash, amount, address_to) VALUES(/" | sed -e 's/,$/);/')
  fi
  sql="${sql}UPDATE exp_mempool SET is_processed = TRUE WHERE hash = '$txhash' LIMIT 1;"$'\n'
  i=$((i+1))
done
tsnow=$(date +%s%N | cut -b1-13)
opsec=$(echo "scale=2;$i/(($tsnow-$tsstart)/1000)" | bc)
p=100
printf "$p%% ($opsec tx/sec)\r"
echo
sql="${sql}COMMIT;"

Note the condition regarding RBF. I simply skipping such transactions so they never have a chance to be parsed and inserted into exp_mempool_tx from which I am doing my balance monitoring.

So the transactions with RBF supported should wait to be fully inserted into blockchain and picked up by my blockchain explorer.

[reardenlife]

Or it is just easier to re-broadcast every hour?  

This is unnecessary, nodes will keep it as long as stated in their "mempoolexpiry", 336 (336hrs/14days) default.

Alright. I see.  So it should mean that the transactions should never get dropped from the mempool in practice.

Yet the users like KittenBob trying to convince me that the transactions are dropping from the mempool all the time.  I am still trying to figure out when and why though.

[nc50lc]

Or it is just easier to re-broadcast every hour?  

This is unnecessary, nodes will keep it as long as stated in their "mempoolexpiry", 336 (336hrs/14days) default.

Alright. I see.  So it should mean that the transactions should never get dropped from the mempool in practice.

Yet the users like KittenBob trying to convince me that the transactions are dropping from the mempool all the time.  I am still trying to figure out when and why though.

You mean bob? He didn't, based on the quoted message "wasn't mined" scenario,
he meant if there's too much unconfirmed transactions in the mempool,
a low fee tx might get dropped after the default timeout as most nodes are using default relay settings.

Example: December 2017 bubble. hover over dec 2017
During that time, a lot of unconfirmed transactions got dropped after 2 weeks.
-edit-

It seems like the discussion is now off-topic based from the OP, perhaps you need to create a new thread

Uh oh.
The blockchain explorer on bash (as well as mempool explorer BTW) that I published above works just fine.  So the problem is solved.  Now we just talking about deployment-related issues which is somewhat within topic.

Seriously, others who are interested in your topic will be mislead by the changing title of each post and the OP.
Start a new topic with what you're actually trying to achieve with this.

Something like a mempool_explorer/API for low-value instant Bitcoin payment?
AFAIK, there's a few discussions in the past about a no-confirmation "coffee payments" and I've said something like this:
"If it's a coffee shop and the customer is there, he wont dare to double spend as his face is in plain sight (people/CCTV)"

BTW, even if you prevented "them" in your mempool, you have no control over other nodes.