Playing with PostgreSQL: Window functions

Selecting items before/after match

Create test data

--
CREATE TABLE test (x SERIAL, y INT);
INSERT INTO test (y) VALUES
	(64), (48), (32), (16),
	(50), (40), (30), (20),
	(10), (60), (70), (80);
--

View data

--
SELECT * FROM test;
 
 x  | y  
----+----
  1 | 64
  2 | 48
  3 | 32
  4 | 16
  5 | 50
  6 | 40
  7 | 30
  8 | 20
  9 | 10
 10 | 60
 11 | 70
 12 | 80

--

With respect to x-ordering, get y-value of the item before and the item after the item with y=50:

--
SELECT * FROM (
	SELECT
		x,
		lag(y,1,0) OVER (ORDER BY x) AS y_prev,
		y AS y_this,
		lead(y,1,0) OVER (ORDER BY x) AS y_next
	FROM test) AS tmp
WHERE this = 50;

| x | y_prev | y_this | y_next |
+---+--------+--------+--------+
| 5 |     16 |     50 |     40 |

--

Budget astrophotograhy

We took a Nikon D5100 and Sigma 150-500 f5-6.3 to a designated dark spot in the Cotswolds. I’ve imaged Jupiter with this lens from South Manchester, at 5.1AU distance, so I figured I could get photos at least that good from a dark site, with Jupiter at 5AU distance.

Well here is Jupiter, with some motion blur due to my strong and stable tripod:

Just out of curiosity, I also tried to get some shots of Saturn. Saturn is ~10% smaller than Jupiter, and was also almost twice as far away (9AU distance), so I didn’t expect to get any detail – maybe an oval would be the best result I could manage at 500mm with glass optics?


(Click for viewer)
 

In the creative and poetic words of Donald Trump: “Wrong!” – Saturn’s rings are visibly distinct from the planet itself, although no moons were resolved in any of the photos I took.

That’s Saturn and rings at 9AU distance, on a D5100 body and with a Sigma 150-500mm lens, processed with RawTherapee.

Nice. Unfortunately 9AU is the closest Saturn gets so I’m unlikely to get a better photo with the budget equipment although I’ll take some with a 1metre f/5 Skywatcher when I get time. Jupiter’s closest approach is 4.5AU (10% closer than so I’ll also try Jupiter again with the telescope when it’s at closest approach – the Skywatcher should be able to resolve the atmospheric bands on Jupiter too.

C++: Generic locker class for thread-safe proxy-access to arbitrary objects

I created a template class which can be used to provide thread-safe access to methods and fields of some target object.
It provides a convenient double-smart-pointer proxy mechanism for accessing one method or field of the target, and a functor mechanism for executing multiple operations on the target within one lock.
Naturally with a RAII solution, the double-smart-pointer style provides no thread-safety for iterator/pointer-based access or any other kind of access which involves taking pointers/references to the target and accessing them outside the lock. For such use cases, wrap the entire operation in the functor style instead.

Source is below and also available on Github

#if 0
(
set -eu
declare -r tmp="$(mktemp)"
g++ -std=c++14 -Wall -Wextra -o "$tmp" "$0"
valgrind --quiet "$tmp"
)
exit 0
#endif
/* Run this file with bash to compile+execute it */
#include <mutex>
#include <iostream>
#include <type_traits>

/* Demo: double-smart pointer for automatic locking of arbitrary class */

template <typename T>
class ThreadSafe
{
	T t;
	mutable std::mutex mx;
	class Proxy
	{
		std::unique_lock<std::mutex> lock;
		T& t;
	public:
		explicit Proxy(std::mutex& mx, T& t) : lock(mx), t(t) { std::cout << "  (lock)" << std::endl; }
		Proxy(Proxy&& src) : lock(std::move(src.lock)), t(src.t) { }
		~Proxy() { std::cout << "  (unlock)" << std::endl; }
		T *operator -> () { return &t; }
	};
public:
	template <typename ...Args>
	explicit ThreadSafe(Args&&... args) : t(std::forward<Args>(args)...) { }
	Proxy operator -> () { return Proxy(mx, t); }
	template <typename Func, typename R = typename std::enable_if<!std::is_void<typename std::result_of<Func(T&)>::type>::value>::type>
	auto operator () (const Func& func) {
		std::lock_guard<std::mutex> lock(mx);
		return func(t);
	}
	template <typename Func, typename R = typename std::enable_if<std::is_void<typename std::result_of<Func(T&)>::type>::value>::type>
	void operator () (const Func& func) {
		func( * operator -> () . operator -> () );
	}
};

int main(int argc, char *argv[])
{
	(void) argc;
	(void) argv;
	/* Initialise s as a thread-safe wrapper around a std::string */
	ThreadSafe<std::string> s("potato");
	/* For doing one operation, the pointer proxy style is convenient */
	s->append("es are awesome");
	/* Function style: Passed function is executed within lock and may do multiple operations */
	s([] (auto& str) { str += "!"; });
	/* Not thread safe, as our lock object expires before the pointer is used.  Use the function style in place of this: */
	std::cout << s->c_str() << std::endl;
	return 0;
}