cancel
Showing results for 
Search instead for 
Did you mean: 

table ddl statement created by pg_dump is invalid

SOLVED
Adventurer

table ddl statement created by pg_dump is invalid

ppas 9.6  /centos6

 

1)  ppas 9.6 table description

------------------------------------------------------

CREATE TABLE tm_ap_event_entry_sate (
event_cd numeric(10,0) NOT NULL,
win_cond_seqno numeric(5,0) NOT NULL,
mobil_cust_no character varying(13) NOT NULL,
entry_seqno numeric(5,0) NOT NULL,
entry_sate character varying(1000) NOT NULL,
entry_chk_yn character varying(1) DEFAULT 'N'::character varying NOT NULL,
ltry_proc_yn character varying(1) DEFAULT 'N'::character varying NOT NULL,
gvwy_seqno numeric(5,0),
entry_dttm timestamp without time zone,
reg_user_id character varying(20) NOT NULL,
reg_dttm timestamp without time zone DEFAULT sysdate NOT NULL,
last_mod_user_id character varying(20) NOT NULL,
last_mod_dttm timestamp without time zone DEFAULT sysdate NOT NULL,
entry_sbj_amt numeric(15,0) DEFAULT 0 NOT NULL,
recr_num numeric(5,0) DEFAULT 1 NOT NULL,
gvwy_sate character varying(50),
win_dttm timestamp without time zone,
entry_stat_cd character varying(1) DEFAULT '1'::character varying NOT NULL
)
PARTITION BY RANGE(event_cd)

(
PARTITION pt_201806 VALUES LESS THAN (2018070000),
PARTITION pt_201812 VALUES LESS THAN (2019010000),
PARTITION pt_max VALUES LESS THAN (MAXVALUE)
);

 

ALTER TABLE tm_ap_event_entry_sate SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate SET (autovacuum_analyze_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate SET (fillfactor=50);

 

ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (autovacuum_analyze_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (fillfactor=50);

ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (autovacuum_analyze_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (fillfactor=50);

 

 

2) pg_dump output


pg_dump -U mig -d perf -n mig > a.txt


============= a.txt ===========================

--
-- EnterpriseDB database dump
--

-- Dumped from database version 9.6.2.7
-- Dumped by pg_dump version 9.6.2.7

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET edb_redwood_date = off;
SET default_with_rowids = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: mig; Type: SCHEMA; Schema: -; Owner: mig
--

CREATE SCHEMA mig;


ALTER SCHEMA mig OWNER TO mig;

SET search_path = mig, pg_catalog, sys, dbo;

SET default_tablespace = '';

SET default_with_oids = false;


--
-- Name: tm_ap_event_entry_sate; Type: TABLE; Schema: mig; Owner: mig
--

CREATE TABLE tm_ap_event_entry_sate (
event_cd numeric(10,0) NOT NULL,
win_cond_seqno numeric(5,0) NOT NULL,
mobil_cust_no character varying(13) NOT NULL,
entry_seqno numeric(5,0) NOT NULL,
entry_sate character varying(1000) NOT NULL,
entry_chk_yn character varying(1) DEFAULT 'N'::character varying NOT NULL,
ltry_proc_yn character varying(1) DEFAULT 'N'::character varying NOT NULL,
gvwy_seqno numeric(5,0),
entry_dttm timestamp without time zone,
reg_user_id character varying(20) NOT NULL,
reg_dttm timestamp without time zone DEFAULT sysdate NOT NULL,
last_mod_user_id character varying(20) NOT NULL,
last_mod_dttm timestamp without time zone DEFAULT sysdate NOT NULL,
entry_sbj_amt numeric(15,0) DEFAULT 0 NOT NULL,
recr_num numeric(5,0) DEFAULT 1 NOT NULL,
gvwy_sate character varying(50),
win_dttm timestamp without time zone,
entry_stat_cd character varying(1) DEFAULT '1'::character varying NOT NULL
)
PARTITION BY RANGE(event_cd)

(
PARTITION pt_201806 VALUES LESS THAN (2018070000) OPTIONS (tablename = tm_ap_event_entry_sate_pt_201806),
PARTITION pt_201812 VALUES LESS THAN (2019010000) OPTIONS (tablename = tm_ap_event_entry_sate_pt_201812),
PARTITION pt_max VALUES LESS THAN (MAXVALUE) OPTIONS (tablename = tm_ap_event_entry_sate_pt_max)
)
WITH (autovacuum_vacuum_scale_factor='0.0', autovacuum_vacuum_threshold='10000', autovacuum_analyze_scale_factor='0.0', autovacuum_analyze_threshold='10000', fillfactor='50');    -------------  wrong syntax !!!


ALTER TABLE tm_ap_event_entry_sate OWNER TO mig;
..................

 

3). invalid syntax 

 

$ psql -U mig -d perf
Password for user mig:
Null display is "<NULL>".
Timing is on.
Pager usage is off.
psql.bin (9.6.2.7)
Type "help" for help.

mig@[local]:7446Smiley Tongueerf]
SQL> select version();
version
--------------------------------------------------------------------------------------------------------------
EnterpriseDB 9.6.2.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
(1 row)

Time: 0.522 ms
mig@[local]:7446Smiley Tongueerf]
SQL>
mig@[local]:7446Smiley Tongueerf]
SQL> CREATE TABLE tm_ap_event_entry_sate (
event_cd numeric(10,0) NOT NULL,
win_cond_seqno numeric(5,0) NOT NULL,
mobil_cust_no character varying(13) NOT NULL,
entry_seqno numeric(5,0) NOT NULL,
entry_sate character varying(1000) NOT NULL,
entry_chk_yn character varying(1) DEFAULT 'N'::character varying NOT NULL,
ltry_proc_yn character varying(1) DEFAULT 'N'::character varying NOT NULL,
gvwy_seqno numeric(5,0),
entry_dttm timestamp without time zone,
reg_user_id character varying(20) NOT NULL,
reg_dttm timestamp without time zone DEFAULT sysdate NOT NULL,
last_mod_user_id character varying(20) NOT NULL,
last_mod_dttm timestamp without time zone DEFAULT sysdate NOT NULL,
entry_sbj_amt numeric(15,0) DEFAULT 0 NOT NULL,
recr_num numeric(5,0) DEFAULT 1 NOT NULL,
gvwy_sate character varying(50),
win_dttm timestamp without time zone,
entry_stat_cd character varying(1) DEFAULT '1'::character varying NOT NULL
)
PARTITION BY RANGE(event_cd)

(
PARTITION pt_201806 VALUES LESS THAN (2018070000) OPTIONS (tablename = tm_ap_event_entry_sate_pt_201806),
PARTITION pt_201812 VALUES LESS THAN (2019010000) OPTIONS (tablename = tm_ap_event_entry_sate_pt_201812),
PARTITION pt_max VALUES LESS THAN (MAXVALUE) OPTIONS (tablename = tm_ap_event_entry_sate_pt_max)
)
WITH (autovacuum_vacuum_scale_factor='0.0', autovacuum_vacuum_threshold='10000', autovacuum_analyze_scale_factor='0.0', autovacuum_analyze_threshold='10000', fillfactor='50');
ERROR: syntax error at or near "WITH"
LINE 27: WITH (autovacuum_vacuum_scale_factor='0.0', autovacuum_vacuu...
^
Time: 0.483 ms
mig@[local]:7446Smiley Tongueerf]
SQL>

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
EDB Team Member

Re: table ddl statement created by pg_dump is invalid

Hi hellower,

 

 

Your issue was escalated via case number "794870"and is fixed in the version EPAS 9.6.11.18.

 

Please go through the technical update link below :

 

https://postgresrocks.enterprisedb.com/t5/EDB-Technical-Updates-and-Alerts/Technical-Update-for-EDB-...

 

 

 

 

Highlighted
EDB Team Member

Re: table ddl statement created by pg_dump is invalid

Hi hellower,

 

Your issue was escalated via case number "794870"and is fixed in the version EPAS 9.6.11.18.

 

Please go through the technical update link below :

 

https://postgresrocks.enterprisedb.com/t5/EDB-Technical-Updates-and-Alerts/Technical-Update-for-EDB-...

 

4 REPLIES
EDB Team Member

Re: table ddl statement created by pg_dump is invalid

Hi Hellower,

 

We are able to reproduce this error from our end on our local machine we are escalating this issue to our engineering team and will let you know with their feedback.

EDB Team Member

Re: table ddl statement created by pg_dump is invalid

Hi Hellower,

 

This issue has been recognized as a bug and our team is working on the fix. Once the fix is avaliable we will let you know.  

EDB Team Member

Re: table ddl statement created by pg_dump is invalid

Hi hellower,

 

 

Your issue was escalated via case number "794870"and is fixed in the version EPAS 9.6.11.18.

 

Please go through the technical update link below :

 

https://postgresrocks.enterprisedb.com/t5/EDB-Technical-Updates-and-Alerts/Technical-Update-for-EDB-...

 

 

 

 

Highlighted
EDB Team Member

Re: table ddl statement created by pg_dump is invalid

Hi hellower,

 

Your issue was escalated via case number "794870"and is fixed in the version EPAS 9.6.11.18.

 

Please go through the technical update link below :

 

https://postgresrocks.enterprisedb.com/t5/EDB-Technical-Updates-and-Alerts/Technical-Update-for-EDB-...