Hi Folks
I'm trying to upgrade our development environment from 13.11 to 15.4 as we look forward to starting to use merge and a few other new features.
The only error that we have with our pgtap tests with is:
ERROR: non-integer constant in GROUP BY
It is only happening to a particular "group by true" that is dynamically compiled when the function parameter asks to group by all data instead of grouping other time series data.
I wrote the following script to reproduce also with an example of what we do with the group by when the parameter is not all.:
#!/usr/bin/env bash
{
for version in "13.11" "14.9" "15.4" "15.0" "15.1" "15.0" "15.1" "15.2" "15.3"; do #
docker rm -f postgres || true
echo "Testing postgres:$version"
docker run --rm --name postgres --net host -ePOSTGRES_USER=postgres -e POSTGRES_PASSWORD=mysecretpassword -e PGPORT=54321 -d postgres:$version
timeout 90s /usr/bin/env bash -c "until docker exec postgres pg_isready ; do sleep 5 ; done" # wait for db to be ready
psql -v ON_ERROR_STOP=on postgresql://postgres:mysecretpassword@localhost:54321/postgres <<EOF
CREATE TABLE IF NOT EXISTS test_data (id serial, proccess_time timestamp with time zone, value NUMERIC);
INSERT INTO test_data(proccess_time, value)
SELECT test_time, random() * 100
FROM generate_series(now() - interval '30 days', now() + interval '30 days', INTERVAL '30 MIN') d(test_time);
SELECT (array_agg(proccess_time order by proccess_time asc))[1], avg(value) FROM test_data GROUP BY date_part('week' , proccess_time); -- working example
SELECT (array_agg(proccess_time order by proccess_time asc))[1], avg(value) FROM test_data GROUP BY true;
EOF
done
}