Re: [PATCH] Negative Transition Aggregate Functions (WIP)

On Thu, January 2, 2014 17:33, Erik Rijkers wrote:
> On Thu, January 2, 2014 13:36, Erik Rijkers wrote:
>> On Thu, January 2, 2014 13:05, David Rowley wrote:
>>> here's a slightly updated patch
>>> [inverse_transition_functions_v1.8.patch.gz ]
>>
>> patch applies, and compiles (although with new warnings).
>> But make check complains loudly

To figure out where this 'make check' failed, I lifted a few statements from the offending sql:
src/test/regress/sql/window.sql (see snafu.sh below).

That reliably crashes the server. It is caused by a SUM, but only when configured like this (i.e. *not* configured for
speed) :

$ pg_config --configure
'--prefix=/home/aardvark/pg_stuff/pg_installations/pgsql.inverse'
'--bindir=/home/aardvark/pg_stuff/pg_installations/pgsql.inverse/bin'
'--libdir=/home/aardvark/pg_stuff/pg_installations/pgsql.inverse/lib' '--with-pgport=6554' '--enable-depend'
'--enable-cassert' '--enable-debug' '--with-openssl' '--with-perl' '--with-libxml' '--with-libxslt' '--with-ossp-uuid'
'--with-zlib'

$ cat snafu.sh
#!/bin/sh

echo "
--
-- WINDOW FUNCTIONS
--

drop TABLE if exists empsalary ;

-- CREATE TEMPORARY TABLE empsalary (
CREATE TABLE empsalary (
depname varchar,
empno bigint,
salary int,
enroll_date date
);

INSERT INTO empsalary VALUES
('develop', 10, 5200, '2007-08-01'),
('sales', 1, 5000, '2006-10-01'),
('personnel', 5, 3500, '2007-12-10'),
('sales', 4, 4800, '2007-08-08'),
('personnel', 2, 3900, '2006-12-23'),
('develop', 7, 4200, '2008-01-01'),
('develop', 9, 4500, '2008-01-01'),
('sales', 3, 4800, '2007-08-01'),
('develop', 8, 6000, '2006-10-01'),
('develop', 11, 5200, '2007-08-15');

-- SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;

" | psql

echo "select * from empsalary;" | psql

echo "
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
" | psql

$ ./snafu.sh
Timing is on.
DROP TABLE
Time: 1.093 ms
CREATE TABLE
Time: 80.161 ms
INSERT 0 10
Time: 11.964 ms
Timing is on.
depname | empno | salary | enroll_date
-----------+-------+--------+-------------
develop | 10 | 5200 | 2007-08-01
sales | 1 | 5000 | 2006-10-01
personnel | 5 | 3500 | 2007-12-10
sales | 4 | 4800 | 2007-08-08
personnel | 2 | 3900 | 2006-12-23
develop | 7 | 4200 | 2008-01-01
develop | 9 | 4500 | 2008-01-01
sales | 3 | 4800 | 2007-08-01
develop | 8 | 6000 | 2006-10-01
develop | 11 | 5200 | 2007-08-15
(10 rows)

Time: 1.854 ms
Timing is on.
connection to server was lost

So, to repeat, this runs fine on a server compiled for speed.

I haven't looked any further (whether perhaps more statements are faulty)