Skip to content
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

GPDB6 partition table could not use appendoptimized #8636

Closed
taojin0505 opened this issue Sep 17, 2019 · 3 comments
Closed

GPDB6 partition table could not use appendoptimized #8636

taojin0505 opened this issue Sep 17, 2019 · 3 comments
Assignees

Comments

@taojin0505
Copy link

@taojin0505 taojin0505 commented Sep 17, 2019

Greenplum version or build

 PostgreSQL 9.4.24 (Greenplum Database 6.0.0 build commit:a2ff2fc318c327d702e66ab58ae4aff34c42296c) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-b
it compiled on Sep  4 2019 01:12:33

OS version and uname -a

LSB Version:    :core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch
Linux master.hd.com 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

autoconf options used ( config.status --config )

Installation information ( pg_config )

BINDIR = /usr/local/greenplum-db-6.0.0/bin
DOCDIR = /usr/local/greenplum-db-6.0.0/share/doc/postgresql
HTMLDIR = /usr/local/greenplum-db-6.0.0/share/doc/postgresql
INCLUDEDIR = /usr/local/greenplum-db-6.0.0/include
PKGINCLUDEDIR = /usr/local/greenplum-db-6.0.0/include/postgresql
INCLUDEDIR-SERVER = /usr/local/greenplum-db-6.0.0/include/postgresql/server
LIBDIR = /usr/local/greenplum-db-6.0.0/lib
PKGLIBDIR = /usr/local/greenplum-db-6.0.0/lib/postgresql
LOCALEDIR = /usr/local/greenplum-db-6.0.0/share/locale
MANDIR = /usr/local/greenplum-db-6.0.0/man
SHAREDIR = /usr/local/greenplum-db-6.0.0/share/postgresql
SYSCONFDIR = /usr/local/greenplum-db-6.0.0/etc/postgresql
PGXS = /usr/local/greenplum-db-6.0.0/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--disable-debug-extensions' '--enable-orca' '--with-zstd' '--with-gssapi' '--with-libxml' '--with-perl' '--with-python' '--with-openssl' '--with-pam' '--with-ldap' '--prefix=/usr/local/greenplum-db-oss' '--mandir=/usr/local/greenplum-db-oss/man' 'CC=gcc' 'CFLAGS=-O3 -fargument-noalias-global -fno-omit-frame-pointer -g'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/local/greenplum-db-6.0.0/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fno-aggressive-loop-optimizations -Wno-unused-but-set-variable -Wno-address -O3 -fargument-noalias-global -fno-omit-frame-pointer -g -std=gnu99 -Werror=uninitialized -Werror=implicit-function-declaration -I/usr/local/greenplum-db-6.0.0/include
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/usr/local/greenplum-db-oss/lib',--enable-new-dtags -L/usr/local/greenplum-db-6.0.0/lib
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lgpopt -lnaucrates -lgpdbcost -lgpos -lxerces-c -lxml2 -lpam -lrt -lgssapi_krb5 -lzstd -lrt -lcrypt -ldl -lm -L/usr/local/greenplum-db-6.0.0/lib
VERSION = PostgreSQL 9.4.24

Expected behavior

create table use appendoptimized=true

Actual behavior

ERROR: unrecognized parameter "appendoptimized"
After I set appendoptimized to appendonly in subtable,problem was solved.

CREATE TABLE cppaydrptflow (
    uuid character varying,
    filler character varying,
    fildate timestamp without time zone,
    lastmodifyoper character varying,
    lstupdtime timestamp without time zone,
    oca integer,
    sequuid bigint,
    tenantid character varying,
    paytime timestamp without time zone,
    orderuuid character varying,
    etl_uuid character varying(32),
    etl_modified timestamp without time zone,
    etl_md5 character varying(16),
    etl_source character varying,
    etl_dbsource character varying,
    etl_customercode character varying
)
WITH (appendoptimized=true, orientation=row, compresstype=zstd) DISTRIBUTED RANDOMLY PARTITION BY RANGE(fildate)
          (
          START ('2017-11-01 00:00:00'::timestamp without time zone) END ('2018-01-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_1',appendonly=true, orientation=row, compresstype=zstd ),
          START ('2018-01-01 00:00:00'::timestamp without time zone) END ('2018-03-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_2',appendonly=true, orientation=row, compresstype=zstd ),
          START ('2018-03-01 00:00:00'::timestamp without time zone) END ('2018-05-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_3',appendonly=true, orientation=row, compresstype=zstd ),
          START ('2018-05-01 00:00:00'::timestamp without time zone) END ('2018-07-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_4',appendonly=true, orientation=row, compresstype=zstd ),
          START ('2018-07-01 00:00:00'::timestamp without time zone) END ('2018-09-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_5',appendonly=true, orientation=row, compresstype=zstd ),
          START ('2018-09-01 00:00:00'::timestamp without time zone) END ('2018-11-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_6',appendonly=true, orientation=row, compresstype=zstd ),
          START ('2018-11-01 00:00:00'::timestamp without time zone) END ('2019-01-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_7',appendonly=true, orientation=row, compresstype=zstd )
          );

Step to reproduce the behavior

incorrect ddl

CREATE TABLE cppaydrptflow (
    uuid character varying,
    filler character varying,
    fildate timestamp without time zone,
    lastmodifyoper character varying,
    lstupdtime timestamp without time zone,
    oca integer,
    sequuid bigint,
    tenantid character varying,
    paytime timestamp without time zone,
    orderuuid character varying,
    etl_uuid character varying(32),
    etl_modified timestamp without time zone,
    etl_md5 character varying(16),
    etl_source character varying,
    etl_dbsource character varying,
    etl_customercode character varying
)
WITH (appendoptimized=true, orientation=row, compresstype=zstd) DISTRIBUTED RANDOMLY PARTITION BY RANGE(fildate)
          (
          START ('2017-11-01 00:00:00'::timestamp without time zone) END ('2018-01-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_1', appendoptimized=true, orientation=row, compresstype=zstd ),
          START ('2018-01-01 00:00:00'::timestamp without time zone) END ('2018-03-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_2', appendoptimized=true, orientation=row, compresstype=zstd ),
          START ('2018-03-01 00:00:00'::timestamp without time zone) END ('2018-05-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_3', appendoptimized=true, orientation=row, compresstype=zstd ),
          START ('2018-05-01 00:00:00'::timestamp without time zone) END ('2018-07-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_4', appendoptimized=true, orientation=row, compresstype=zstd ),
          START ('2018-07-01 00:00:00'::timestamp without time zone) END ('2018-09-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_5', appendoptimized=true, orientation=row, compresstype=zstd ),
          START ('2018-09-01 00:00:00'::timestamp without time zone) END ('2018-11-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_6', appendoptimized=true, orientation=row, compresstype=zstd ),
          START ('2018-11-01 00:00:00'::timestamp without time zone) END ('2019-01-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) WITH (tablename='cppaydrptflow_1_prt_7', appendoptimized=true, orientation=row, compresstype=zstd )
          );
@kainwen

This comment has been minimized.

Copy link
Member

@kainwen kainwen commented Sep 17, 2019

I cannot reproduce this isse under master and latest 6X_STABLE.

@paul-guo-

This comment has been minimized.

Copy link
Member

@paul-guo- paul-guo- commented Sep 17, 2019

I tested this on several of my dev virtual machines. It can be reproduced on master, 6 and 5. @kainwen I suspect you run the workaround DDL.

I quickly search string "appendoptimized" in code base.

In gram.y

/* This should match def_elem and also allow qualified names */
reloption_elem:
            ColLabel '=' def_arg
                {
                    /*
                     * appendoptimized is an alias for appendonly in order to
                     * provide a reloption syntax which better reflects the
                     * featureset of AO tables. It is implemented as a very
                     * thin alias, the reloptions and messaging will still
                     * say appendonly.
                     */
                    if (strcmp($1, "appendoptimized") == 0)
                        $$ = makeDefElem("appendonly", (Node *) $3);
                    else
                        $$ = makeDefElem($1, (Node *) $3);
                }

It looks like the alias is handled in gram.y I suspect some partition related code in gram.y does not check or replace appendoptimized somehow.

@gaos1 gaos1 self-assigned this Oct 14, 2019
@kainwen

This comment has been minimized.

Copy link
Member

@kainwen kainwen commented Oct 22, 2019

A PR is opened for the issue: #8880

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants
You can’t perform that action at this time.