Monday, December 21, 2020

[PostgreSQL] How to check null in jsonb array

 Use jsonb_array_elements  to convert jsonb array to set (queriable similar to normal table) then we can use  where x::text = 'null' to match the null value.

Examples:

=> select x->'id' as id 
from jsonb_array_elements(
'[{"id": 4, "val": [null,1,null]}, 
{"id": 2, "val": [3]}]'::jsonb
) as x, 
jsonb_array_elements(x->'val') as y 
where y::text = 'null';
 id 
----
 4
 4
(2 rows)


=> select x->'id' as id
from jsonb_array_elements(
'[{"id": 4, "val": [null,1,null]}, 
{"id": 2, "val": [3]}]'::jsonb
) as x, 
jsonb_array_elements(x->'val') as y 
group by x->'id' 
having count(x) filter (where y::text  = 'null') > 1;
 id 
----------
 4
(1 row)


[PostgreSQL] How to check if there's a NULL value in array

WHERE array_position(array_column, NULL) IS NOT NULL 

Friday, June 26, 2020

Some usages of jstat

-gcutil

$ jstat -gcutil


This command shows utilization percentage of memory spaces of a java process.

S0: Survivor space 0
S1: Survivor space 1
E: Eden space
O: Old-gen space

GC activities

YGC: Young GC count
YGCT: Time spent by Young GC 
FGC: Full GC count
FGCT: Time spent by Full GC


-gc

$ jstat -gc

Find the current vm heap size by sum value under S0C, S01, EC, OC


More at https://docs.oracle.com/javase/8/docs/technotes/tools/unix/jstat.html

[Linux] Visualize parent - child process relationship

$ ps -axf -o pid,ppid,tty,stat,cmd

Credit: Orphan vs Zombie vs Daemon processes

Collectd PostgreSQL Plugin

I couldn't find this link when searching with google https://www.collectd.org/documentation/manpages/collectd.conf.html#plugin-postgresql