test_sweight_formulas.sh

#!/bin/sh
current_dir=$(pwd)
export PGDATA="$current_dir/pgtemp-data"
export PGPORT=54321
export PATH="$current_dir/pgtemp/bin:$PATH"

if [ ! -d "postgresql" ]; then

	git clone https://git.postgresql.org/git/postgresql.git
	mv postgresql-foo postgresql
	(
		cd postgresql && \
		git reset --hard && \
		./configure --prefix="$current_dir/pgtemp" && \
		make -j4 && \
		make install
	) && \
	initdb && \
	pg_ctl -l "$current_dir/pg.log" start && \
	createdb "$USER" || \
	exit 1
	cat <<-"EOF" | psql -X || exit

		CREATE OR REPLACE FUNCTION sig_figs(numeric) RETURNS int LANGUAGE sql AS $$
			SELECT 1+coalesce(floor(log(abs(nullif($1,0))))::int,0)+scale($1)
		$$;

		CREATE TABLE test_results
		(
			"DEC_DIGITS"	int,
			pg_version  	text,
			n_double        double precision, -- used for sorting, can't use numeric since that's what we are testing
			n_text          text,
			sqrt        	text,
			sqrt_text       text,
			sqrt_sig_figs	int
		);

	EOF

else

	pg_ctl -l "$current_dir/pg.log" start || exit

	cat <<-"EOF" | psql || exit

		TRUNCATE TABLE test_results;

	EOF

fi

for pg_version in "HEAD" "fix-sweight-v2"; do
	for DEC_DIGITS in 4 2 1; do
		(
			cd postgresql && \
			git reset --hard && \
			git apply "$current_dir/fix-pow10-assert.patch"
		) || exit
		if [ "$pg_version" != "HEAD" ]
		then
			(cd postgresql && \
			git apply "$current_dir/$pg_version.patch") || exit
		fi
		if [ $DEC_DIGITS != 4 ]
		then
			(cd postgresql && \
			git apply "$current_dir/DEC_DIGITS_$DEC_DIGITS.patch") || exit
		fi
		(cd postgresql && make install && pg_ctl restart) || exit
		cat <<-EOF | psql -X || exit
			WITH nums AS
			(
				SELECT
					exp,
					trim_scale(2::numeric*10::numeric^exp) n
				FROM generate_series(-40,40) exp
			)
			INSERT INTO test_results
				("DEC_DIGITS", pg_version, n_double, n_text, sqrt, sqrt_sig_figs)
			SELECT
				'$DEC_DIGITS',
				'$pg_version',
				n::double precision,
				CASE WHEN abs(exp) > 10 THEN
					format('2e%s%s',CASE WHEN exp > 0 THEN '+' END,exp)
				ELSE
					n::text
				END,
				sqrt(n),
				sig_figs(sqrt(n))
			FROM nums;
		EOF
	done
done

for DEC_DIGITS in 4 2 1; do
	echo "--"
	echo "-- Comparison of sig_figs(sqrt(n)) for HEAD vs fix-sweight-v2"
	echo "-- when DEC_DIGITS == $DEC_DIGITS"
	echo "--"
	cat <<-EOF | psql -X || exit
	SELECT
		a."DEC_DIGITS",
		a.n_text AS n,
		a.sqrt_sig_figs AS "HEAD",
		b.sqrt_sig_figs AS "fix-sweight-v2",
		b.sqrt_sig_figs - a.sqrt_sig_figs AS diff
	FROM test_results a
	JOIN test_results b USING ("DEC_DIGITS",n_text)
	WHERE a.pg_version = 'HEAD'
	AND b.pg_version = 'fix-sweight-v2'
	AND a."DEC_DIGITS" = $DEC_DIGITS
	ORDER BY a.n_double
	EOF
done

pg_ctl stop