update – How to “succinctly” detect changed values in the PostgreSQL’s upsert (on conflict) where clause while supporting null changes?

We maintain a large data warehouse implemented in PostgreSQL and python. One very common pattern we do is to make upserts, and log when things were updated. We have some unique key my_key and values, say, my_uuid, my_text, my_int, my_date. If any any of those values change for a given my_key we would like to update the row. That’s fine, and we have a pattern which works well:

insert into my_table (
    my_key,
    my_uuid,
    my_text,
    my_int,
    my_date
)
select
    some_key,
    some_uuid,
    some_text,
    some_int,
    some_date
from some_table
on conflict (my_key) do update set
    some_uuid = excluded.some_uuid,
    some_text = excluded.some_text,
    some_int = excluded.some_int,
    some_date = excluded.some_date,
    update_timestamp = now()
where 
    coalesce(my_table.some_uuid, uuid_nil()) <> coalesce(excluded.some_uuid, uuid_nil())
    or coalesce(my_table.some_text, '') <> coalesce(excluded.some_text, '')
    or coalesce(my_table.some_int, -1) <> coalesce(excluded.some_int, -1)
    or coalesce(my_table.some_date, '3000-01-01'::date) <> coalesce(excluded.some_date, '3000-01-01'::date)

The last on conflict ... where clause is important, because it makes sure that the update_timestamp is only updated when there are changes. It also makes sure we don’t update rows unnecessarily, improving performance.

Anyway, we often have an issue with the coalesce() logic. The reason it exists in this pattern is to support the value going to and from null. Let’s take the following example:

coalesce(my_table.some_text, '') <> coalesce(excluded.some_text, '')

This works fine, and produces the following results for a comprehensive list of test cases:

select coalesce('a', '') <> coalesce('a', '')  --> false
union all
select coalesce(null, '') <> coalesce(null, '')  --> false
union all
select coalesce('a', '') <> coalesce('b', '')  --> true
union all
select coalesce(null, '') <> coalesce('b', '')  --> true
union all
select coalesce('a', '') <> coalesce(null, '')  --> true

That is, it is only true when the value actually changed. However, what happens if a value genuinely is empty string ''? Then it won’t update.

This means we need to be creative about choosing the dummy value '' such that it isn’t a value which would naturally occur. We could just invent a keyword which is unlikely to occur in production. But I’d rather find another pattern which doesn’t have this drawback.

What options exist to do this susinclty giving the same change “truth table” I showed above? We could always use case when ..., but it becomes extremely verbose. We need something which is easy to write and easy to read. A row can often include 5-15 value columns

Are there any alternatives which could do an upsert without the drawback of the pattern we are using today?


The following can be used as a testbed to find a suitable pattern:

select
    v1, v2, expected,
    COALESCE(v1, '') <> COALESCE(v2, '') as current_version,
    CCOALESCE(v1 <> v2, true) as candidate_version
from (
    select 'a' as v1, 'a' as v2, false as expected
    union all
    select null as v1, null as v2, false as expected
    union all
    select '' as v1, null as v2, true as expected
    union all
    select null as v1, '' as v2, true as expected
    union all
    select 'a' as v1, null as v2, true as expected
    union all
    select null as v1, 'b' as v2, true as expected
    union all
    select 'a' as v1, 'b' as v2, true as expected
) q

Returning:

v1     v2     expected current_version candidate_version
a      a      false    false           false
null   null   false    false           true
''     null   true     false           true
null   ''     true     false           true
a      null   true     true            true
null   b      true     true            true
a      b      true     true            true

new social media supporting panel

Hi dear forum members,

I would like to introduce you to our new social media supporting panel socialocean.net you can buy likes, followers, web traffic, views and more it is the best panel to support your social account. you can also earn 1$ by signing up and you can also earn 25% cashback by adding funds to your account.

please let me know how we did

Thank you

[​IMG]

bitcoin core – Why does bitcoind have to listen to the public internet when supporting lnd?

I have been part of experimenting with setting up a system to send and receive lightning network payments. The system uses bitcoin-core (bitcoind) and lnd. Initially, the system was set up on a home network, behind a NAT, so neither bitcoind nor lnd could listen for incoming connections from the public internet. In this initial configuration, it was possible to use lnd to send payments, but it could not receive incoming payments.

In the process of troubleshooting, we moved the bitcoind to a server outside the NAT, so it had a public IP number and opened the port 8333 for inbound connections. The lnd server is still behind the NAT, and it does not have any ports opened for inbound connections. Suddenly the system started to work and we could receive incoming lightning networks payments.

So we solved the problem, but I’m kind of baffled as to why this actually works. Was this just a fluke? Or is there a reason why a bitcoin node needs to be able to receive inbound connections in order to properly support a lightning network node?

Has VLC permanently stopped supporting Twitch?

I used to be able to play Twitch streams in VLC. A few months ago, this started breaking. Then they updated VLC and since then (I have the latest version), it just does nothing when you enter a Twitch stream URL — no stream and no error message. Literally nothing happens.

I don’t know how to interpret this, but it’s a nightmare now that I’m forced to use a browser. It takes like 10-15 seconds to open each stream now compared to a split second with VLC.

why is supporting digest authentication necessary instead of other more secure ways like non-reversible transformation to password?

Why is supporting digest authentication necessary instead of other more secure ways like applying non-reversible transformation to password to encrypt them? Is it because the receiving end cannot process and verify?

blockchain – What am I supporting? Anarchy?

I suggest a few resources that will help you understand bitcoin better. You can go through 3blue1brown’s video on Bitcoin.

Some of the Bitcoin’s earliest ideological supporters were ideologically Anarchists, but most supporters would self-identify as Libertarians. The reasons to support Bitcoin has mostly been motivated by the idea that Government and Money are two separate entities and should exist as such.

Bitcoin is a protocol for decentralized peer to peer network, bitcoin is the currency (token) used by this network. Some models of banking are old, Bitcoin aims to solve that. Money is simply a social contract. The note you hold is a promise of payment, not actual payment. It is fundamentally a social construct. Bitcoin is no different, except it is not controlled by governments or banks. We have rules without rulers in Bitcoin.

My most pressing question is this: who or what am I supporting when I buy bitcoin or ether? Did I unwittingly support the overthrow of governments when I bought crypto?

You support no one, BTC is the currency used by censorship resistant network, Bitcoin that can be owned by whoever wishes to own it. Yes, countries with cheaper electricity have a lot of mining rigs that helps them mine and hold Bitcoin compared to countries with high electricity cost, but consensus rules are enforced by full nodes. No, you definitely did not support the overthrow of the government.

You’re not a terrorist for simply holding Bitcoin, neither are you an anarchist.

beginner – Prime number sequence supporting repeated iteration

I’m a long-time Java programmer, but recently I decided to give Kotlin a try. So far, I’m loving the language features, it feels much more expressive than what I’m used to. As practice, I wrote this function that produces a repeatable sequence of prime numbers (i.e. the sequence can be iterated multiple times, and will make use of results from past iterations to speed up future iterations). It feels pretty solid to me, but I’m wondering how it would look to a Kotlin veteran – are there language features that I’m not taking full advantage of, or conventions that I should be following?

One question I have in particular is regarding top-level function vs. subclass; here I chose to implement a top-level function primeSequence(): Sequence<Long> that returns an object, but I could have just about as easily implemented a class PrimeSequence : Sequence<Long>. Is there a good rule of thumb for when to use one or the other?

fun primeSequence(): Sequence<Long> = object: Sequence<Long> {
    private val knownPrimes: MutableList<Long> = arrayListOf(2L, 3L)

    private fun Long.isMultipleOf(n: Long) = (this%n == 0L)
    
    private fun Long.isPrime() = knownPrimes
            .takeWhile { it*it <= this }
            .none { this.isMultipleOf(it) }

    override fun iterator(): Iterator<Long> = object: Iterator<Long> {
        private var lastPrime: Long? = null

        override fun hasNext() = true

        override fun next(): Long {
            val nextPrime = when(val lastPrime = this.lastPrime) {
                null -> 2L
                2L -> 3L
                else -> {
                    if (knownPrimes.last() > lastPrime ) {
                        knownPrimes(knownPrimes.binarySearch(lastPrime) + 1)
                    } else {
                        val np = generateSequence(lastPrime+2) { it+2 }
                                .first { it.isPrime() }
                        knownPrimes.add(np)
                        np
                    }
                }
            }
            this.lastPrime = nextPrime
            return nextPrime
        }
    }
}

Find top N oldest files on AIX system not supporting printf in find command

I am trying to find a solution similar to the one used below to find the Top N oldest files (modification time) on my AIX system starting from a given directory and digging though all sub-directories as well under it . Unfortunately printf is not supported on AIX ( my version being 7.1) find command. Is there an alternative way to accomplish the same task on AIX?

$ find /home/sk/ostechnix/ -type f -printf '%T+ %pn' | sort | head -n 5

Source: https://ostechnix.com/find-oldest-file-directory-tree-linux/

AIX man page for find command: https://www.ibm.com/support/knowledgecenter/ssw_aix_71/f_commands/find.html

enterprise architecture – Is there any recognized pattern supporting or discouraging the access to multiple database from a single application?

Connecting multiple applications to the same database is generally a bad idea. It’s an integration anti-pattern you might call The Shared Database and it’s unfortunately a very easy anti-pattern to fall into.

It’s common for a database to be created as part of building an application. That application needs to store data somewhere, right? So you need a database and you create one. But then, after some time, other applications get created that might need access to some data that already exists (the usual suspects: users, customers, products, orders, etc). How do they get the data? Well, nothing simpler: just connect to the same database, right?

And now you have problems:

  • a database is an implementation detail. Now you exposed this to other applications;
  • an implementation detail can be changed because it’s just that, an implementation detail. Want to change the implementation of your database, change tables structures, schemas, etc? You can’t do it anymore without affecting other applications (anyone with the database connection credentials can basically reach out into your database and fetch data – you might have applications connecting to your DB without even knowing they exist).
  • if your database isn’t exactly how others need it, you might need to make changes to your DB for someone else’s use case. Or they will do so themselves 🙂 and break your application with your own database.
  • hello tight coupling, bye bye loose coupling. That’s bad however you put it.
  • you only store data, you don’t store behavior. The behavior is in your application. How can others reuse behavior? Maybe create a crap load of stored procedures to share the behavior. You get more and more entrenched into using that database vendor implementation. Want to change from Oracte to MySQL? You now need approval from others.
  • etc.

A shared database is like having neighbors paying you a visit and then refusing to leave. You now have to live with them.

So exposing “your” database to others by using and API, or a gateway, or a facade, or a service, or whatever, is a way to share the data and behavior without having to live with your neighbors. It’s a good practice that hides the implementation details. Of course it’s not without it’s disadvantages: it’s more complex than “select * from”, more verbose, it still is an integration point (which needs to evolve, maybe not at the same pace for everyone using it, so you now need API versioning), etc.

When it comes to one application connecting yo multiple DBs (as your title asks), things are simpler and less problematic concerning what I said above. But again, it’s a trade-off. Transactions might suck across multiple databases, so will joins. Keeping things separated makes sense but will cause performance degradation as now you need to open connections to more databases, etc.

At the end of the day, it’s not about patterns or anti-patterns, but trade-offs, thinking carefully about one design decision or another, and basically, a good dose of common sense.

transactions – The best JavaScript libraries supporting Bitcoin Core?

I highly recommend bcoin:

https://bcoin.io/
https://github.com/bcoin-org/bcoin

It is a nodejs library that can also run a fully validating node, SPV node, and BIP44 wallet. It’s used in production by many long-standing bitcoin companies such as Purse.io

You can even use the bcoin client to make RPC calls to bitcoin core, and of course you do not need to run the bcoin full node to take advantage of the library for TX construction and wallet functions.