Category: Technology

PostgreSQL Sequences

I’m having a problem with a database refusing to change INSERTS to UPDATES on the ON CONFLICT condition — it insists that a ‘duplicate key value violates unique constraint’. A little time with a search engine tells me that sequences can get out of sync, and then you cannot insert items into the table. How do you know your sequence is out of sync?

SELECT NEXTVAL((SELECT PG_GET_SERIAL_SEQUENCE('"table_name"', 'name_of_column_with_sequence'))) as "NextValue", MAX("name_of_column_with_sequence") AS "Max Value" FROM "table_name";

So null seems like it would be a problem!

For future reference, when the next value is smaller than the max value in the table, the solution is to set the series value based on the max value

SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('"table_name"', 'name_of_column_with_sequence')), (SELECT (MAX("name_of_column_with_sequence") + 1) FROM "table_name"), FALSE);

Android 11 Arrived!

This morning, my phone was very slow. It got progressively worse — finally getting to a point where there was a ten second lag between touching something and a response. I was trying to reboot because closing all of the apps didn’t do anything. The phone locked instead. And, when I woke the screen back up, I had a strange circle arrow icon in the notification bar. It turns out my phone had been downloading an OS update. It was soon ready to install, and I was actually able to use my phone again. Installed the update — that took a long while too — and voila, I’ve got Android 11 on a TCL T770B. Woohoo!

Excel – Converting Unix Timestamp to Human Readable Date(time)

You can use the formula =(B2/86400)+DATE(1970,1,1) to convert a unix epoch time to a human readable date (or date time). In my case, I have the unix timestamp in microseconds so I’ve got to divide by 86400000. The value you get is a not-so-meaningful float … but that’s actually a date.

Select a date format to display the value as a date

Or chose a custom format and use something like “m/d/yyyy hh:mm” to display a date and time.

Blocking Device Internet Access

We block Internet access for a lot of our smart devices. All of our control is done through the local server; and, short of updating firmware, the devices have no need to be chatting with the Internet. Unfortunately, our DSL modem/router does not have any sort of parental control, blocking, or filtering features. Fortunately, ISC DHCPD allows you to define per-host options. Setting the router to the device’s IP (0.0.0.0 may work as well) allows us to have devices that can communicate with anything on their subnet without allowing access out to other subnets or the Internet.

Viewing and recording packets using tshark

This time, I’m writing this down so I don’t have to keep looking it up. To display some packet info to the screen while writing a network capture to a file, include the -P option (older versions of tshark used -S)

2021-04-18 13:58:58 [lisa@server ~]# tshark -f "udp port 123" -w /tmp/ntpd.cap -P
Running as user "root" and group "root". This could be dangerous.
Capturing on 'enp0s25'
1 0.000000000 10.x.x.x → x.x.x.18 NTP 90 NTP Version 4, client
2 3.898916081 10.x.x.x → x.x.x.199 NTP 90 NTP Version 4, client
3 7.898948128 10.x.x.x → x.x.x.20 NTP 90 NTP Version 4, client
4 7.928749596 x.x.x.20 → 10.x.x.x NTP 90 NTP Version 4, server
5 9.898958577 10.x.x.x → x.x.x.76 NTP 90 NTP Version 4, client
6 9.949450324 x.x.x.76 → 10.x.x.x NTP 90 NTP Version 4, server
7 10.898981132 10.x.x.x → x.x.x.185 NTP 90 NTP Version 4, client
8 11.009163093 x.x.x.185 → 10.x.x.x NTP 90 NTP Version 4, server

JQuery – Finding a set of checkboxes

A corollary to my JavaScript modifying checkbox values when the box is checked or unchecked … I needed a way to reset the form (in my form, the default is for the boxes to be checked and the value to be 1). The following code identifies all checkboxes with a particular class, checks them, and sets the value to 1.

/**
 * This function checks off each checkbox of the input class
 *
 * @param {string} strCheckboxClass     Name of class identifying in-scope checkboxes
 * @return {null} 
 *
 * @example
 *
 *     checkAllDatabases ('MyBoxes');
 */
 function checkAllDatabases(strCheckboxClass){
    arrayCheckboxes = $('.'+strCheckboxClass);
    for(i = 0; i < arrayCheckboxes.length; i++) {
        $( '#'+arrayCheckboxes[i].name).prop( "checked", true );
        $( '#'+arrayCheckboxes[i].name).val(1);
    } 
}

Changing checkbox value when (un)checked

This bit of code handles another rather esoteric scenario — I have a generic “go to this URL and download the resultant Excel file” JavaScript function. This is because I write a lot of reporting tools and didn’t want to write a lot of code for each new tool. The template is an input form with a submit button that calls the generic function. Params for the elements on the form from which values are read, the URL to call to generate the report, and the POST elements into which each corresponding form value is inserted gets stuffed. Works great for text inputs. Works fine for drop-downs. But the value of a checkbox is really a combination of the potential value (from the value tag) and the checked state. That is — my Button 1 has a potential value of 1, but if the box is checked or not is really important.

Instead of attempting to determine the type of element in each form input so I can evaluate the checked condition, I decided to just change the value when the checkbox state is changed. Now Button 1 has a potential value of 0 when unchecked and a potential value of 1 when checked. I don’t need to know if the box is checked because the value answers that question. So passing along button1’s value to my URL lets the target site know if I want whatever Button 1 represents. (In this case, users are able to select from a list of seven data sources — smaller numbers of data sources reduce the query time but also fail to provide the most robust report).

The JavaScript to handle changing the checkbox value when the checked state changes:

$("#button1").change(function () {
    if ($("#button1").is(':checked')) {
        $("#button1").val(1);
    }
    else{
        $("#button1").val(0);
    }
});

$("#button2").change(function () {
    if ($("#button2").is(':checked')) {
        $("#button2").val(1);
    }
    else{
        $("#button2").val(0);
    }
});

The HTML defining these two checkboxes:

<input type="checkbox" id="button1" name="button1" value="1" checked><label for="ngmss">Thing 1</label>
<input type="checkbox" id="button2" name="button2" value="1" checked><label for="ngmss">Thing 2</label>

Console access from virsh

I had a whole host of problems that were eventually resolved by rebooting the physical server … but, in the process of trying to figure out exactly what was wrong, I wanted to console into the virtual machines from the physical server. Using “virsh console vmname” should have worked … but it didn’t. Turns out you’ve got to enable a service on each guest before you’re able to console in from the physical server. To do so, run:

systemctl enable serial-getty@ttyS0.service

And, if you want to connect in *right now*, also start the service:

systemctl start serial-getty@ttyS0.service

Now, running “virsh console vmname” doesn’t appear to do much … but, if you hit the enter key, you’ll get a logon prompt for the VM.