Author Archives: bullgare

PostgreSQL: using indices on json fields

It’s obviously a really bad idea. But if you really need it, that’s what you can do.

Postgresql: show all locked queries

WITH RECURSIVE l AS (
 SELECT pid, locktype, mode, granted,
ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj
 FROM pg_locks
), pairs AS (
 SELECT w.pid waiter, l.pid locker, l.obj, l.mode
 FROM l w
 JOIN l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype=w.locktype AND NOT l.pid=w.pid AND l.granted
 WHERE NOT w.granted
), tree AS (
 SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pids
 FROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l
 UNION ALL
 SELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()
 FROM tree JOIN pairs w ON tree.pid=w.locker AND NOT w.waiter = ANY ( all_pids )
)
SELECT (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,
      replace(a.state, 'idle in transaction', 'idletx') state,
      (clock_timestamp() - state_change)::interval(3) AS change_age,
      a.datname,tree.pid,a.usename,a.client_addr,lvl,
      (SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,
      repeat(' .', lvl)||' '||left(regexp_replace(query, '\s+', ' ', 'g'),100) query
FROM tree
JOIN pg_stat_activity a USING (pid)
ORDER BY path;

WITH RECURSIVE l AS (

SELECT pid, locktype, mode, granted,

ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj

FROM pg_locks

), pairs AS (

SELECT w.pid waiter, l.pid locker, l.obj, l.mode

FROM l w

JOIN l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype=w.locktype AND NOT l.pid=w.pid AND l.granted

WHERE NOT w.granted

), tree AS (

SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pids

FROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l

UNION ALL

SELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()

FROM tree JOIN pairs w ON tree.pid=w.locker AND NOT w.waiter = ANY ( all_pids )

)

SELECT (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,

replace(a.state, 'idle in transaction', 'idletx') state,

(clock_timestamp() - state_change)::interval(3) AS change_age,

a.datname,tree.pid,a.usename,a.client_addr,lvl,

(SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,

repeat(' .', lvl)||' '||left(regexp_replace(query, '\s+', ' ', 'g'),100) query

FROM tree

JOIN pg_stat_activity a USING (pid)

ORDER BY path;

Smart Home systems

There are quite a lot of that systems: MajorDoMo OpenHab Iobroker Domoticz HomeAssistant The most interesting ones are: Iobroker (Node.js-based) HomeAssistant (based on Python 3)

Install exact version via brew

For instance, we’re looking for kubernetes-helm@2.9 helm 2.9

git -C "$(brew --repo homebrew/core)" fetch --unshallow
cd "$(brew --repo homebrew/core)"
git log Formula/kubernetes-helm.rb
# searching for 2.9.0 with a command /2\.9\.0 and copying hash like "7ade79f13b4bbcc6126803245594d97b2abbe2fa"
git checkout -b kubernetes-helm@2.9.0 7ade79f13b4bbcc6126803245594d97b2abbe2fa
HOMEBREW_NO_AUTO_UPDATE=1 brew install kubernetes-helm

git -C "$(brew --repo homebrew/core)" fetch --unshallow

cd "$(brew --repo homebrew/core)"

git log Formula/kubernetes-helm.rb

# searching for 2.9.0 with a command /2\.9\.0 and copying hash like "7ade79f13b4bbcc6126803245594d97b2abbe2fa"

git checkout -b kubernetes-helm@2.9.0 7ade79f13b4bbcc6126803245594d97b2abbe2fa

HOMEBREW_NO_AUTO_UPDATE=1 brew install kubernetes-helm

Now we have needed version of helm. If you see an error message like Error: go: unknown version :mountain_lion, you should make a little hack (as it said here). Check all the files depending on

grep 'depends_on :macos => :mountain_lion' Formula/*
# you'll see something like
Formula/bashdb.rb:  depends_on :macos => :mountain_lion
Formula/chrome-cli.rb:  depends_on :macos => :mountain_lion
Formula/clipsafe.rb:  depends_on :macos => :mountain_lion
Formula/go.rb:  depends_on :macos => :mountain_lion
Formula/go@1.8.rb:  depends_on :macos => :mountain_lion
Formula/go@1.9.rb:  depends_on :macos => :mountain_lion
Formula/ios-sim.rb:  depends_on :macos => :mountain_lion
Formula/mesos.rb:  depends_on :macos => :mountain_lion
Formula/mongodb.rb:  depends_on :macos => :mountain_lion
Formula/mongodb@3.0.rb:  depends_on :macos => :mountain_lion
Formula/mongodb@3.2.rb:  depends_on :macos => :mountain_lion
Formula/mongodb@3.4.rb:  depends_on :macos => :mountain_lion
...

grep 'depends_on :macos => :mountain_lion' Formula/*

# you'll see something like

Formula/bashdb.rb: depends_on :macos => :mountain_lion

Formula/chrome-cli.rb: depends_on :macos => :mountain_lion

Formula/clipsafe.rb: depends_on :macos => :mountain_lion

Formula/go.rb: depends_on :macos => :mountain_lion

Formula/go@1.8.rb: depends_on :macos => :mountain_lion

Formula/go@1.9.rb: depends_on :macos => :mountain_lion

Formula/ios-sim.rb: depends_on :macos => :mountain_lion

Formula/mesos.rb: depends_on :macos => :mountain_lion

Formula/mongodb.rb: depends_on :macos => :mountain_lion

Formula/mongodb@3.0.rb: depends_on :macos => :mountain_lion

Formula/mongodb@3.2.rb: depends_on :macos => :mountain_lion

Formula/mongodb@3.4.rb: depends_on :macos => :mountain_lion

...

In each file just comment a string saying depends_on :macos …

docker-compose example for ceph, kafka, postgres cluster

That is how it can be done:

Setting zsh on you mac

This gist is great: https://gist.github.com/kevin-smets/8568070 I just added these line at the bottom:

plugins=(zsh-autosuggestions git)

DEFAULT_USER=...

POWERLEVEL9K_SHORTEN_DIR_LENGTH=2

source /usr/local/share/zsh-syntax-highlighting/zsh-syntax-highlighting.zsh

plugins=(zsh-autosuggestions git)

DEFAULT_USER=...

POWERLEVEL9K_SHORTEN_DIR_LENGTH=2

source /usr/local/share/zsh-syntax-highlighting/zsh-syntax-highlighting.zsh

List of embedded plugins: https://github.com/robbyrussell/oh-my-zsh/tree/master/plugins Custom plugins are installed here — ~/.oh-my-zsh/custom/plugins (actually, $ZSH_CUSTOM/plugins). Like this:

git clone https://github.com/zsh-users/zsh-autosuggestions ${ZSH_CUSTOM:-~/.oh-my-zsh/custom}/plugins/zsh-autosuggestions

1	git clone https://github.com/zsh-users/zsh-autosuggestions ${ZSH_CUSTOM:-~/.oh-my-zsh/custom}/plugins/zsh-autosuggestions

CO2 sensor DIY

Article says how to make your own CO2 sensor with Arduino and visualize data with Grafana. It’s in Russian. https://ovcharov.me/2018/10/21/co2-sensor-mh-z19b/

Spectacle for fullscreen apps on mac

It can make your app fullscreen and also manage your apps positioning. http://www.spectacleapp.com/ You could also try http://magnet.crowdcafe.com/ Now they suggest to use https://rectangleapp.com/ instead of spectacular.

Free video editor — Davinci Resolve

https://www.blackmagicdesign.com/ru/products/davinciresolve/

Online code editors

https://scrapfy.io — has go and js http://collabedit.com — has js