Month: April 2021

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!

Wood Chips

Some of the Metroparks have a free wood chip pile. I’m sure they bring in a big, industrial tree chipper machine and a dump truck to clear out downed trees throughout the park. The chips are then dumped into a large pile and free for anyone who wants to haul them. We discovered this last year and started making a wonderful path through our woods — a path that did an awesome job of replacing the usual muck road we travel during maple season. We’ve been watching the location for a new pile of wood chips this year, and Anya spotted the pile a few days ago. It’s a bit of rush to collect them — local landscaping companies show up with large trailers and clear the place out.

We got a lot of wood chips the last few days. The first day, I shoveled and Scott ferried. There was a lot of time lost to driving, so he put together a rear hitch mount for one of our carts. We can now tow both carts simultaneously. The second day, I shoveled while he used a pitch fork — a far superior tool for moving wood chips — and then hung out with Anya while he ferried the carts. Today, he pitch-forked and ferried while I consolidated the pile and did a few other tasks (turned compost, disassembled the temporary hop greenhouse). We’re up to 38 cartloads of wood chips! We’ll probably move some more tomorrow, but it’ll all be soggy from the rain tonight.

Decoy Garden

We’ve had a lot of trouble with deer eating our veggies — corn, beans, lettuce. All very good deer munchies. We had a little luck playing talk radio all night long, but I think they get used to it pretty quickly. Then eat all your not-quite-ready-to-pick sweet corn. This year, we put in a decoy garden full of deer’s fav foods. There are brassicas, beets, radishes, oats, and rye grains. Scott tilled up a big area where our garden used to be, I raked it out to level the soil, and then I spread a bunch of seeds. We did it in three sections — the north-east quarter was finished first. The south-east quarter and west strip were done second, and the strip in the middle was done last. The first section is coming in quite vigorously. The second section is just starting to come in, and the final strip is pretty much dirt. We’ve been lucky to have a few heavier rains since the seeds were spread, so everything is watered well. This should be really cool. My next adventure is to replace some of the lawn with a wildflower seed mix so we’ve got plenty of bee chow available.

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.

Hop Greenhouse Redux

So we’re supposed to get five or six inches of snow tonight, and tomorrow night will get down to 28 degrees or so … which means the hop greenhouse is back! It’s sturdier this time so it’ll handle the snow load. There are two logs on each side, both with a long 2×6 board run across. Shorter 2×4’s were laid across the long boards to provide support for the roof. There are additional boards diagonal from the “roof” to the ground. The whole thing is covered with greenhouse plastic, and 4×4’s (and a few large logs) weigh down the edges.

I’ve also got plastic bags over the blueberries, raspberries, elderberries, baby pawpaws, garlic, and baby black walnuts. There’s a tarp over the part of the deer garden that’s actually sprouted. Hopefully everything fairs well. The hazelnuts aren’t protected — they’ve gotten quite large, but they should have plenty of energy in the root system. The kale isn’t protected either, but it survived the winter already … so that should be fine.

Trout Dough Ball Recipe

Scott’s going fishing, and Anya and I made dough balls for trout bait. Mix 1 cup all purpose flour, 1/3 cup corn meal, and 1-2T garlic powder (I used 1.5T of garlic powder, and it’s really garlicy).

Add enough water to make a firm but slightly sticky dough.

Pinch a bit of dough and roll between your palms to form into blueberry sized balls. Some of ours got a little big!

Drop in boiling water for 1-2 minutes.

Remove and place on napkin/towel (or, in my case, a perforated silicone baking mat) to dry. Cool for an hour then freeze overnight.

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 ( 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