Sunday, October 4, 2020

PostgreSQL - get position of second delimiter in string

There's many articles with examples of getting the first or last position of a delimiter in a string. Here's an example of getting the position of the second delimiter in a string. 

PostgreSQL 10.12

Get the position of the first delimiter ';' from the public.cherryshoe.description column:
select description, 
position(';' in description) from public.cherryshoe; 

Get the position of the second delimiter ';'.   Add char_length of each split_part - since we want the position, need two char_length's.  Add an additional length of 2 for each split_part function that is used to account for each of the two delimiters:
select description, 
(char_length(split_part(description, ';', 1))
+ char_length(split_part(description, ';', 2)) 
+ 2) from public.cherryshoe;

Get the position of the third delimiter ';'.  Add char_length of each split_part - since we want the position, need three char_length's.  Add an additional length of 3 for each split_part function that is used to account for each of the three delimiters:
select description, 
(char_length(split_part(description, ';', 1)) 
+ char_length(split_part(description, ';', 2)) 
+ char_length(split_part(description, ';', 3)) 
+ 3) from public.cherryshoe;

So on and so forth...

Tuesday, September 1, 2020

Running jackd command line works, but not via systemd because [Cannot allocate memory]

I am working on a personal project with my husband (also a software dev), where he got the video saving piece working with a raspberry pi, python, and picamera.  Audio wasn't working yet, so I decided to take that piece on.

Raspberry Pi 2 Model B
Raspberry Pi Camera Module V2
Raspberry Pi USB WiFi Adapter
Raspberry Pi OS 10
USB Microphone
Python 3.7.3
systemctl --version => systemd 241 (241)

I'm currently working with JACK audio server and jack_capture to capture audio files with JACK.   The jackd2 package installation included the /etc/security/limits.d/audio.conf.disabled file, which when activated, enables realtime permissions and memlock permissions to the audio group.  You can activate it via the following command or rename the file without the .disabled extension at the end.  Make sure to logout and login again for the audio server to have the configuration recognized - no need to reboot/restart.

dpkg-reconfigure -p high jackd
I installed jackd2 via the following commands:

# audio server
sudo apt -y install jackd2
# modify /etc/security/limits.d/audio.conf to bring realtime priorities to the audio group (which is usually fine for a single user desktop usage)
sudo mv /etc/security/limits.d/audio.conf.disabled /etc/security/limits.d/audio.conf

The audio.conf file configures the following; I verified the default pi user is in the audio group.

@audio   -  rtprio     95
@audio   -  memlock    unlimited

I installed jack_capture via the following commands.  The liblo-tools were necessary for me to remote control jack_capture via UDP port with OSC (Open Sound Control) messages.

# audio capture
sudo apt -y install liblo-tools
sudo apt -y install jack-capture

Starting jackd with python via command line worked great!  When I started the python script as a service with systemd this error kept occurring:

Cannot lock down 13589280 byte memory area (Cannot allocate memory)

Why?!  I read through numerous jackd configuration articles.  Running command line was fine.  Checking "ulimit -l" was unlimited (as the audio.conf file told the machine to do).

I stumbled upon this and this article (looked very old but it still applied for my problem!) that pointed me in the right direction that "When using the RPM or Debian packages on systems that use systemd, system limits must be specified via systemd."  Essentially running jackd with systemd ignores the audio.conf file, so you must set it manually with systemd.

# The four settings under Service is for jackd support
# LimitRTPRIO and LimitMEMLOCK must be set in systemd service file, because when run as a service
# etc security limits.d audio.conf is not honored.  MEMLOCK unlimited is infinity in systemd


  • The LimitMEMLOCK is set to "infinity" for the systemd service, but in the audio.conf was set to "unlimited". 
  • The two environment variables DISPLAY and JACK_NO_AUDIO_RESERVATION were set to get rid of these two errors I had previous to the "Cannot allocate memory error".
To bypass device reservation via session bus, set JACK_NO_AUDIO_RESERVATION=1 
Failed to connect to session bus for device reservation Unable to autolaunch a dbus-daemon without a $DISPLAY for X11

These additional articles were helpful:

Friday, August 14, 2020

React - access ref of child component from parent component

This is how to get access to the ref of a child component from a parent component.  I am working with react-table so this example has references to that.

CentOS Linux release 7.3.1611 (Core)
React 16.2
react-table 6.10.3


const ALL_TOOLS = "";
const TOOL_1 = "tool1";
class MyParent extends Component {
  constructor(props) {

    this.state = {
      error: null,
      rows: [],
      numPages: -1,
      summary: null,
      loading: false,
      filter: ""
   * Allow the parent to get a reference to the child component
   * @param {Object} table
  setRef = table => {
    this.childRef = table;

   * Event handler for filter selection
   * @param {String} filter
  onFilterSelection = filter => {
    // must put this.fetchData in callback because need to guarantee filter will be updated
    // before calling
        filter: filter
      () => { = 0; // force react-table back to page 1

   * Fetches data for the table
   * @param {Object} tableState: the react table current table
  fetchData = tableState => {
    // show the loading overlay
    this.setState({ loading: true });
    const sortDir = tableState.sorted[0].desc ? "DESC" : "ASC";
    const offset = * tableState.pageSize;

  render() {
    const { error, rows, numPages, summary, loading } = this.state;
    return (
      <div className="MyParent">
        {!error && (
          <div className="content">
            <div className="left-panel">
                  onClick={() => this.onFilterSelection(ALL_TOOLS)}
                  onClick={() => this.onFilterSelection(TOOL_1)}
                >Tool 1</li>
            <div className="right-panel">

export default MyParent;


class MyChildTable extends React.PureComponent {
  constructor(props) {

    // add ref to react table, and pass along to parent
    const { setRef } = this.props;
    this.table = React.createRef();

  render() {
    const { rows, numPages, summary, loading, fetchData } = this.props;
    const columns = [
    return (
      <div className="MyChildTable">
          defaultSorted={[{ id: "tstamp", desc: true }]}
          className="-striped -highlight"
          showPageSizeOptions={false} // Only allow 25
          pageSizeOptions={[25]} // Only allow 25
          defaultPageSize={25} // Only allow 25

export default MyChildTable;

This article was helpful.

Tuesday, July 28, 2020

Raspberry Pi set up for development

I had a really old Raspberry Pi sitting around that I set up for development yesterday. The Pi 2 doesn't come with WiFi by default, so I set up a WiFi Adapter so it would be easier to develop on my localhost and ssh as necessary.

Host: macOS Catalina
Raspberry Pi 2 Model B
Raspberry Pi USB WiFi Adapter (I have this one)
  1. Install Raspberry Pi OS image on an SD card: Used Raspberry Pi Imager rbi-imager for macOS, chose Raspberry Pi OS Lite (32-bit) since I didn’t want the desktop environment -
  2. Boot into the Raspberry Pi and login
  3. Confirm the OS version. Mine is "Raspbian GNU/Linux 10 (buster)"
  4. cat /etc/os-release
  5. Update settings for US. I did I1, then I4, then I3. I think I3 may be the only one I needed for my keyboard to recognize the double-quote key
    sudo raspi-config 
    Choose 4 Localisation Options 
    Choose I1 Change Locale => en_US.UTF-8 UTF-8 
    Choose I3 Change Keyboard Layout => Generic 101-key PC => Other => English (US) (Country of Origin) => English (US) (Keyboard layout) => The default for the keyboard layout => No compose key => 
    Choose I4 Change WLAN Country => US United States 
    sudo reboot
  6. Configure Raspberry Pi to connect to WiFi

    Backup file and copy to your home folder
    sudo cp /etc/wpa_supplicant/wpa_supplicant.conf /etc/wpa_supplicant/wpa_supplicant.conf.orig
    cp /etc/wpa_supplicant/wpa_supplicant.conf ~

    Add below to the end of the file. I had to add scan_ssid=1 because my SSID is not broadcasting.  NOTE:  If you have special characters in your password, e.g. double quote I didn't have to escape it but I had to add in key_mgmt=WPA-PSK config attribute (with no quotes).
    vi ~/wpa_supplicant.conf 
      psk="<secret password>"

    Copy updated file back
    sudo cp ~/wpa_supplicant.conf /etc/wpa_supplicant/wpa_supplicant.conf 

    Verify if the “inet addr” is available on wlan0
    ifconfig wlan0 

    If not, reboot to connect
    sudo reboot 

    On reboot, I can see it says “My IP address is XXX.XXX.XX.XXXX”

    To verify internet connectivity perform a wget
  7. Enable ssh on Raspberry Pi
    sudo raspi-config 
    Choose I5 Interfacing Options 
    Choose P2 SSH => Yes 
    [Didn’t need to reboot]
  8. Connect to Raspberry Pi via ssh from localhost, update macOS hosts file with Raspberry Pi IP
    sudo vi /private/etc/hosts 

    Added this to bottom of file
    # raspberry pi 
    XXX.XXX.XX.XXXX csrp

    ssh to the Raspberry Pi
    ssh pi@csrp
  9. Shutdown the Raspberry Pi
    sudo halt

Tuesday, June 30, 2020

MacBook Pro 2019 recovery after migration assistant

Fun!  I got a new macbook pro 2019 (that came with macOS Catalina) and decided to try out the Migration Assistant from my old macbook pro (which was on macOS High Sierra).  The migration ran successfully, but soon after I decided I would prefer to have a clean slate machine to start off from for various reasons (Applications, tools, and languages I commonly use have changed through the years, etc).

I ran through the instructions to reinstall macOS from macOS Recovery:
Try 1:
- Selected "Reinstall macOS"
- Took recommended option and chose "Reinstall the latest macOS that was installed on your Mac"
- Decided not to erase the disk
- Warning with "An Internet connection is required to install macOS"; I went back to the step to connect a Wi-Fi connection, which is available while in macOS Recover, and set up the Wi-Fi connection.  (It didn't remember the connection I had set up originally during the Migration Assistant, or the one I manually set up after I created the initial admin account)
- Agreed to the macOS Catalina terms of the software license agreement
- Selected the disk "Macintosh HD", unlocked it, and started the installation
  - The login screen came back with both my user accounts once during installation
- The login screen came back with both my user accounts after the installation was complete (I wasn't expecting that, was expecting for me to set up a new account again)
  - Checking the Applications, all the same applications were still usable <= Problem!
  - Checking non-Applications, still usable  <= Problem!
- Decided to run through the instructions again, this time erasing the disk

Try 2:
- Selected Disk Utility
- Clicked View -> Show All Devices
- Confirmed the container is using "APFS Container", named the disk the same name as the existing name "APPLE SSD .... Media" format type and erased the disk
- Quit Disk Utility
- Set up the Wi-Fi connection
- Selected "Reinstall macOS"
- Took recommended option and chose "Reinstall the latest macOS that was installed on your Mac"
- Agreed to the macOS Catalina terms of the software license agreement
- Selected the disk "APPLE SSD .... Media", unlocked it, and started the installation
- The "complete setup screen came up" as it did when I first opened the macbook.  Success!
- After setup, I did verify that the Applications and non-Applications from the migration were no longer usable.

Thursday, May 28, 2020

Ansible - accessing hostname defined in another group

I had a use case where the target server I was running the ansible playbook against needed to know the hostname of another group (defined in the hosts file) that I passed in as an environment variable named "target_env".

CentOS Linux release 7.3.1611 (Core)
ansible 2.9.9

The hosts file had three groups defined, with only one hostname associated with each group:
[development] ansible_connection=local



i.e. I need target server to know the hostname of target_env production.  In this case the target_env "production" had hostname associated to it.
ansible-playbook cherryshoe.yml -u YOUR_USER_ON_TARGET_SERVER -k -K -e '' 'target_env=production'

These ansible magic variables didn't work:
- debug: var=inventory_hostname - had the hostname where the script was being run against (in this example
- debug: var=ansible_hostname - had hostname localhost

There were two solutions to this problem:
1. Access it via hostvars magic variable
- debug: msg="{{hostvars[inventory_hostname]['groups'][target_env][0]}}"

This works because the hostvars magic variable holds the following type of information that you can see when you debug the variable
- debug: var=hostvars

The "groups" attribute inside hostvars[inventory_hostname] has the "target_env" production and if you access the first element in the array it's "", which is what we want:
"groups": {
    "all": [
    "development": [
    "test": [
    "production": [
    "ungrouped": []

2.  Another way to do this was a hash variable in a group variables file,

and access it using:
- debug: msg=""{{hostnames[target_env]}}"

These articles were helpful:

Tuesday, April 21, 2020

Setting up redis with Node.js

I recently setup redis for the application I'm working on.  Because database records could take multiple hours to complete, the user has access to past requests by a unique identifier and can get subsequent results in seconds vs. hours.

The redis server setup was done with an ansible yum module, which installed an older version 3.2.12, but it was adequate for my needs so I pinned it at that version.

CentOS Linux release 7.3.1611 (Core)
node v8.16.0
redis server 3.2.12
redis 3.0.2 (client)

I verified with redis-cli that the redis server was installed appropriately:
redis-cli ping (should return PONG)
redis-cli --version (should return redis-cli 3.2.12)
keys * (gets all keys)
set hello world
get hello

I realized for my use case it would be advantageous to set an expire time/time to live for the keys.  You can practice this on command line first:
pttl hello (should return (integer) -1 since expire time/time to live wasn't set)
set foo bar ex 10 (expires in 10 seconds)
get foo (before expired)
pttl foo (should return a non-negative integer since expire time/time to live set)
get foo (after expired will return (nil))
del foo

Then I worked on client-side code to support this.  This is the client module that any other module can import and use.

cacheClient.js module
const redis = require("redis");
const client = redis.createClient();

// Node Redis currently doesn't natively support promises, however can wrap the methods with
// promises using the built-in Node.js
const { promisify } = require("util");
const getCacheAsync = promisify(client.get).bind(client);

const DEFAULT_TTL_SECONDS = 60*60*24*5; // 5 days time to live

 * Client will emit error when encountering an error connecting to the Redis server or when any
 * other in Node Redis occurs.  This is the only event type that the tool asks you to provide a
 * listener for.
client.on('error', function(error) {
  console.error(`Redis client error - ${error}`);

 * Adds key with value string to cache, wiith number of seconds to live.
 * @param {String} key
 * @param {String} value
 * @param {int} seconds: default is 5 days
function addToCache(key, value, seconds = DEFAULT_TTL_SECONDS) {
  console.log(`Cache add hash[${key}]`);
  // EX sets the specified expire time, in seconds.
  client.set(key, value, 'EX', seconds);

 * Retrieves value with key.
 * @param {String} key
 * @returns {String}
async function getFromCache(key) {
  const val = await getCacheAsync(key);
  if (val) {
    console.log(`Cache retrieve hash[${key}]`);
  return val;

module.exports = {

Then I tested locally.
1.  I made the max size of the cache very small (2MB), and I added two keys with two separate unique requests with a time to live of 5 days.  I then added the 3rd one, and I verified the older one was deleted to fit the new one
- backup /etc/redis.conf
- edit /etc/redis.conf maxmemory 2mb
- restart - sudo systemctl restart redis
- check maxmemory took by issuing the following in redis-cli
    - config get maxmemory
- check time to live for keys by issuing the following in redis-cli
   - pttl <key>
- add keys with requests
- check memory information by issuing the following in redis-cli
   - info memory
2.   I had several keys in the cache, and we went on spring break, by the time I got back to work 5 days had gone by and my keys had been expired and no longer in the cache

I then went back to custom configure with ansible what I needed for redis, which were the following redis.conf changes.
1.  Ensured /etc/redis dir exists in order to copy the default template /etc/redis.conf to it
2.  Copied template config to /etc/redis/redis.conf
3.  For the greatest level of data safety, run both persistence methods, so needed to enable append-only logs of all write operations performed by the redis server (AOF)
4.  Enable daemonize so redis server will keep running in the background
5.  Configure cache max memory (default out-of-the-box is no max)
6.  Configure cache eviction policy (default out-of-the-box is no eviction)
7.  Updated systemd to use custom config file and reloaded systemd

Number 5 and 6 together were key; if these two are not custom configured, it's possible to hit a RAM max and error out in your application!!

More commands that are useful:
info keyspace (summary of num keys, num keys with expire set, average ttl)
flushall (deletes all keys from existing database)
redis-cli --scan --pattern CS_* | xargs redis-cli del (flush only keys that start with)
redis-cli --scan | xargs -L1 redis-cli persist (make all keys persistent)
redis-cli --bigkeys (sample Redis keys looking for big keys)