Re: [PATCH] Negative Transition Aggregate Functions (WIP) - Mailing list pgsql-hackers

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-01sales     |     1 |   5000 |
2006-10-01personnel|     5 |   3500 | 2007-12-10sales     |     4 |   4800 | 2007-08-08personnel |     2 |   3900 |
2006-12-23develop  |     7 |   4200 | 2008-01-01develop   |     9 |   4500 | 2008-01-01sales     |     3 |   4800 |
2007-08-01develop  |     8 |   6000 | 2006-10-01develop   |    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)