Re: Problem with update on partitioned table

Alex Solovey <a(dot)solovey(at)gmail(dot)com> writes:
> We have pretty big production database (running PostgreSQL 8.3.1) with
> many partitioned tables. In most cases, they work well (since 8.2.1 at
> least) -- constraint exclusion is able to select correct partitions.
> However, there is an exception: queries on partitioned tables using
> PostgreSQL 'UPDATE Foo ... FROM Bar' syntax extension.

Hmm, the immediate problem is that cost_mergejoin is coming out with
a silly cost (NaN) because of division by zero. The attached patch
should get it back to 8.2-equivalent behavior. But really we're missing
a bet because the sub-joins ought to get discarded entirely when we know
they must be empty. There are various places testing for this but it
looks like make_join_rel() needs to do it too.

regards, tom lane

Index: src/backend/optimizer/path/costsize.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v
retrieving revision 1.191
diff -c -r1.191 costsize.c
*** src/backend/optimizer/path/costsize.c 1 Jan 2008 19:45:50 -0000 1.191
--- src/backend/optimizer/path/costsize.c 24 Mar 2008 20:55:42 -0000
***************
*** 1385,1390 ****
--- 1385,1396 ----
Selectivity joininfactor;
Path sort_path; /* dummy for result of cost_sort */

+ /* Protect some assumptions below that rowcounts aren't zero */
+ if (outer_path_rows <= 0)
+ outer_path_rows = 1;
+ if (inner_path_rows <= 0)
+ inner_path_rows = 1;
+
if (!enable_mergejoin)
startup_cost += disable_cost;