Re: [HACKERS] Table collision in join.sql and aggregates.sql

[Douglas Doole]

D'oh! The "temp" declaration had been removed from our test since we don't
use temp tables. I missed that when applying it to the community code.

You can ignore me now.

On Fri, Mar 31, 2017 at 4:01 PM Tom Lane  wrote:

> Douglas Doole  writes:
> > As we've been merging our code with 9.6, a couple developers have had
> > one-off failures in the join.sql and aggregates.sql test because the
> tables
> > T1, T2 and T3 have the wrong definitions.
>
> > Digging into it, I found that both files create the tables T1, T2, and T3
> > for a short period of time and then drop them. Since the
> parallel_schedule
> > file puts these two files into the same group, they can run concurrently.
> > If it happens that the the two files hit the T1, T2, T3 tests at the same
> > time, then you see the table definition problems.
>
> Hmmm ... that would indeed be a problem, except that aggregate.sql's
> tables are temp tables, which should mean that they are in a schema
> different from the one that join.sql is putting its tables in.  Are you
> sure you've identified the issue correctly?  Because if this doesn't
> work, there are an awful lot of similar hazards elsewhere in the
> regression tests.
>
> regards, tom lane
>

Re: [HACKERS] Table collision in join.sql and aggregates.sql

[Tom Lane]

Douglas Doole  writes:
> As we've been merging our code with 9.6, a couple developers have had
> one-off failures in the join.sql and aggregates.sql test because the tables
> T1, T2 and T3 have the wrong definitions.

> Digging into it, I found that both files create the tables T1, T2, and T3
> for a short period of time and then drop them. Since the parallel_schedule
> file puts these two files into the same group, they can run concurrently.
> If it happens that the the two files hit the T1, T2, T3 tests at the same
> time, then you see the table definition problems.

Hmmm ... that would indeed be a problem, except that aggregate.sql's
tables are temp tables, which should mean that they are in a schema
different from the one that join.sql is putting its tables in.  Are you
sure you've identified the issue correctly?  Because if this doesn't
work, there are an awful lot of similar hazards elsewhere in the
regression tests.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Table collision in join.sql and aggregates.sql

[Douglas Doole]

As we've been merging our code with 9.6, a couple developers have had
one-off failures in the join.sql and aggregates.sql test because the tables
T1, T2 and T3 have the wrong definitions.

Digging into it, I found that both files create the tables T1, T2, and T3
for a short period of time and then drop them. Since the parallel_schedule
file puts these two files into the same group, they can run concurrently.
If it happens that the the two files hit the T1, T2, T3 tests at the same
time, then you see the table definition problems.

I took the easy way of solving this and renamed the tables in
aggregates.sql to AGG1, AGG2, and AGG3. (Picked on aggregates.sql since it
had the T1, T2, T3 tests added in 9.6.)

Doug
- Salesforce
*** a/src/test/regress/expected/aggregates.out
--- b/src/test/regress/expected/aggregates.out
***
*** 957,1023  LINE 1: select (select max(min(unique1)) from int8_tbl) from tenk1;
  --
  -- Test removal of redundant GROUP BY columns
  --
! create temp table t1 (a int, b int, c int, d int, primary key (a, b));
! create temp table t2 (x int, y int, z int, primary key (x, y));
! create temp table t3 (a int, b int, c int, primary key(a, b) deferrable);
  -- Non-primary-key columns can be removed from GROUP BY
! explain (costs off) select * from t1 group by a,b,c,d;
!   QUERY PLAN  
! --
   HashAggregate
 Group Key: a, b
!->  Seq Scan on t1
  (3 rows)
  
  -- No removal can happen if the complete PK is not present in GROUP BY
! explain (costs off) select a,c from t1 group by a,c,d;
!   QUERY PLAN  
! --
   HashAggregate
 Group Key: a, c, d
!->  Seq Scan on t1
  (3 rows)
  
  -- Test removal across multiple relations
  explain (costs off) select *
! from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
! group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.y,t2.z;
!   QUERY PLAN   
! ---
   Group
!Group Key: t1.a, t1.b, t2.x, t2.y
 ->  Merge Join
!  Merge Cond: ((t1.a = t2.x) AND (t1.b = t2.y))
!  ->  Index Scan using t1_pkey on t1
!  ->  Index Scan using t2_pkey on t2
  (6 rows)
  
! -- Test case where t1 can be optimized but not t2
! explain (costs off) select t1.*,t2.x,t2.z
! from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
! group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z;
!   QUERY PLAN   
! ---
   HashAggregate
!Group Key: t1.a, t1.b, t2.x, t2.z
 ->  Merge Join
!  Merge Cond: ((t1.a = t2.x) AND (t1.b = t2.y))
!  ->  Index Scan using t1_pkey on t1
!  ->  Index Scan using t2_pkey on t2
  (6 rows)
  
  -- Cannot optimize when PK is deferrable
! explain (costs off) select * from t3 group by a,b,c;
!   QUERY PLAN  
! --
   HashAggregate
 Group Key: a, b, c
!->  Seq Scan on t3
  (3 rows)
  
! drop table t1;
! drop table t2;
! drop table t3;
  --
  -- Test combinations of DISTINCT and/or ORDER BY
  --
--- 957,1023 
  --
  -- Test removal of redundant GROUP BY columns
  --
! create temp table agg1 (a int, b int, c int, d int, primary key (a, b));
! create temp table agg2 (x int, y int, z int, primary key (x, y));
! create temp table agg3 (a int, b int, c int, primary key(a, b) deferrable);
  -- Non-primary-key columns can be removed from GROUP BY
! explain (costs off) select * from agg1 group by a,b,c,d;
!QUERY PLAN   
! 
   HashAggregate
 Group Key: a, b
!->  Seq Scan on agg1
  (3 rows)
  
  -- No removal can happen if the complete PK is not present in GROUP BY
! explain (costs off) select a,c from agg1 group by a,c,d;
!QUERY PLAN   
! 
   HashAggregate
 Group Key: a, c, d
!->  Seq Scan on agg1
  (3 rows)
  
  -- Test removal across multiple relations
  explain (costs off) select *
! from agg1 inner join agg2 on agg1.a = agg2.x and agg1.b = agg2.y
! group by agg1.a,agg1.b,agg1.c,agg1.d,agg2.x,agg2.y,agg2.z;
!   QUERY PLAN   
! ---
   Group
!Group Key: agg1.a, agg1.b, agg2.x, agg2.y
 ->  Merge Join
!  Merge Cond: ((agg1.a = agg2.x) AND (agg1.b = agg2.y))
!  ->  Index Scan using agg1_pkey on agg1
!  ->  Index Scan using agg2_pkey on agg2
  (6 rows)
  
! -- Test case where agg1 can be optimized but not agg2
! explain (costs off) select agg1.*,agg2.x,agg2.z
! from agg1 inner join agg2 on agg1.a = agg2.x and agg1.b = agg2.y
! group by agg1.a,agg1.b,agg1.c,agg1.d,agg2.x,agg2.z;
!   QUERY PLAN   
! ---
   HashAggregate
!Group Key: agg1.a, agg1.b, agg2.x, agg2.z
 ->  Merge Join
!  Merge Cond: ((agg1.a =