New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Update fails with enabled optimizer. #6053

Closed
tkhannan opened this Issue Oct 22, 2018 · 8 comments

Comments

Projects
None yet
5 participants
@tkhannan
Copy link

tkhannan commented Oct 22, 2018

Greenplum version or build

PostgreSQL 8.3.23 (Greenplum Database 5.11.0 build commit:6f447c24554134df0c1d473e63e0124e7ee6de6b) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Sep 12 2018 20:18:01

OS version and uname -a

Linux kzgreenplum5 3.10.0-514.el7.x86_64 #1 SMP Tue Nov 22 16:42:41 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

Expected behavior

Update with enabled optimizer runs successfully.

Actual behavior

Update fails with error:
ERROR: could not read block 0 of relation 1663/16385/169456: read only 0 of 32768 bytes (seg4 dbhost:40004 pid=14330);
Error while executing the query

If optimizer is disabled statement runs without error.

Step to reproduce the behavior

set optimizer=on;

CREATE TABLE "autotest"."test_2"
(
"v_date_old" TIMESTAMP,
"v_date_new" TIMESTAMP,
"v_varchar1_old" CHARACTER VARYING(10),
"v_varchar1_new" CHARACTER VARYING(10),
"v_varchra2_old" CHARACTER VARYING(10),
"v_varchra2_new" CHARACTER VARYING(10),
"v_numb_old" NUMERIC(30, 5),
"v_numb_new" NUMERIC(30, 5),
"v_concat_old" CHARACTER VARYING(20),
"v_concat_new" CHARACTER VARYING(20),
"s_varchar_old" CHARACTER VARYING(10),
"s_varchar_new" CHARACTER VARYING(10),
"s_numb_old" NUMERIC(30, 5),
"s_numb_new" NUMERIC(30, 5),
"s_varchar2_old" CHARACTER VARYING(10),
"s_varchar2_new" CHARACTER VARYING(10),
"s_date_old" DATE,
"s_date_new" DATE
)
DISTRIBUTED BY ("s_varchar_old")

insert into "autotest"."test_2"
values
('2012-03-16 00:00:00', '3315-05-11 00:00:00', NULL, 'IF:i', NULL, NULL, NULL, NULL, NULL, NULL, 'IF:i', 'IF:i', '-75227456.29300', '-7528614852357723003027456.29823', 'IR$6D', 'IR$6D', '2012-03-16', '3315-05-11')

UPDATE "autotest"."test_2" SET v_varchar1_old = s_varchar_old;

@vraghavan78 vraghavan78 self-assigned this Oct 22, 2018

@vraghavan78

This comment has been minimized.

Copy link
Member

vraghavan78 commented Oct 22, 2018

Thank you for reporting @tkhannan.

A smaller repro is:

CREATE TABLE test
(
"v_date_old" TIMESTAMP,
"v_varchar1_old" CHARACTER VARYING(10),
"s_varchar_old" CHARACTER VARYING(10)
)
DISTRIBUTED BY ("s_varchar_old");
insert into test values ('2012-03-16 00:00:00', NULL, 'IR$6D');
UPDATE test SET v_varchar1_old = s_varchar_old;

The explain plan looks fine:

vraghavan=# explain UPDATE test SET v_varchar1_old = s_varchar_old;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Update  (cost=0.00..431.09 rows=1 width=1)
   ->  Result  (cost=0.00..431.00 rows=1 width=34)
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=28)
               Hash Key: prod_wrk.test.s_varchar_old
               ->  Result  (cost=0.00..431.00 rows=1 width=28)
                     ->  Split  (cost=0.00..431.00 rows=1 width=28)
                           ->  Table Scan on test  (cost=0.00..431.00 rows=1 width=24)
 Settings:  gp_segments_for_planner=16; optimizer=on
 Optimizer status: PQO version 3.4.0
(9 rows)
@kainwen

This comment has been minimized.

Copy link
Member

kainwen commented Nov 19, 2018

@yydzero
@vraghavan78
Seems orca generate split-update even for a non-distributed-key update.

This bug blocks the PR: #6245 because it fails on ORCA-pipeline.

@yydzero

This comment has been minimized.

Copy link
Member

yydzero commented Nov 20, 2018

@vraghavan78 As this is blocking MPP PR #6245, would you please help to prioritize this? Or we could pair on it if needed. Thanks!

@hardikar

This comment has been minimized.

Copy link
Member

hardikar commented Nov 21, 2018

Not sure how this bug is related to PR #6245. This is a real bug in ORCA, however, and we'll try to get to it as soon as possible. But, I feel that the issue fixed in #6245 is a critical one, so if you need to push this in sooner than next week, I'd say disable orca for that test. I say this because the two issues look unrelated. If you think they are, then we should wait for a proper fix.

@kainwen

This comment has been minimized.

Copy link
Member

kainwen commented Nov 22, 2018

@hardikar
The PR #6245 add a test cases to concurrently updating two columns(not hash columns).

And two bugs together will lead to more tuples in the result:

  1. BUG1: the ORCA generate split-update for not-hash-columns
  2. BUG2: concurrently split-update will lead to more tuples( this PR handles this, but not ideal: #5989 )

But for this PR #6245 , I think the root problem is the ORCA's plan is not right.

@kainwen

This comment has been minimized.

Copy link
Member

kainwen commented Nov 27, 2018

The blocked PR #6245 is merged. But it is a workaround it removes the case for ORCA.

Please add it back when this issue solved. Thanks!

@vraghavan78

This comment has been minimized.

Copy link
Member

vraghavan78 commented Dec 14, 2018

The issue mention here and PR #6245 are different. So created a new issue #6488

@vraghavan78

This comment has been minimized.

Copy link
Member

vraghavan78 commented Dec 14, 2018

Merged.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment