PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16437
PG Version12.2
OSUbuntu 18.04
Opened2020-05-14 16:21:25+00
Reported byBoris
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16437
Logged by:          Boris
Email address:      (redacted)
PostgreSQL version: 12.2
Operating system:   Ubuntu 18.04
Description:        

Source server:

The dump was created on the server psql (11.6 (Ubuntu 11.6-1.pgdg18.04+1))
postgres=> select version();
                                                              version       
                                                      
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.6 (Ubuntu 11.6-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)

The command to create the dump is:
devuser@vps760542:~/tmp$ pg_dump --file "chartbaes_$(date
+"%Y-%m-%d-%T").backup" --host "localhost" --username "postgres" --verbose
--quote-all-identifiers --format=c --no-password "chartbaes"


Destination server:
I am using the command to restore the DB 
postgres=# \! pg_restore --host "127.0.0.1" --username "postgres"   -d tmp
--verbose --single-transaction --disable-triggers --strict-names
chartbaes_2020-05-14-15\:53\:45.backup

------------------
pg_restore: creating VIEW "ctrader.sentiment_summary_600"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 252; 1259 25769 VIEW sentiment_summary_600
quotefeeder
pg_restore: error: could not execute query: ERROR:  column
"_materialized_hypertable_2.Volume" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 8:     "_materialized_hypertable_2"."Volume",
            ^
Command was: CREATE VIEW "ctrader"."sentiment_summary_600" AS
 SELECT "_materialized_hypertable_2"."Time",
    "_materialized_hypertable_2"."m_tickerId",
   
"_timescaledb_internal"."finalize_agg"('first(anyelement,"any")'::"text",
NULL::"name", NULL::"name",
'{{pg_catalog,int4},{pg_catalog,timestamptz}}'::"name"[],
"_materialized_hypertable_2"."agg_3_3", NULL::integer) AS "Open",
    "_timescaledb_internal"."finalize_agg"('max(integer)'::"text",
NULL::"name", NULL::"name", '{{pg_catalog,int4}}'::"name"[],
"_materialized_hypertable_2"."agg_4_4", NULL::integer) AS "High",
    "_timescaledb_internal"."finalize_agg"('min(integer)'::"text",
NULL::"name", NULL::"name", '{{pg_catalog,int4}}'::"name"[],
"_materialized_hypertable_2"."agg_5_5", NULL::integer) AS "Low",
    "_timescaledb_internal"."finalize_agg"('last(anyelement,"any")'::"text",
NULL::"name", NULL::"name",
'{{pg_catalog,int4},{pg_catalog,timestamptz}}'::"name"[],
"_materialized_hypertable_2"."agg_6_6", NULL::integer) AS "Close",
    "_materialized_hypertable_2"."Volume",
    "_timescaledb_internal"."finalize_agg"('count()'::"text", NULL::"name",
NULL::"name", '{}'::"name"[], "_materialized_hypertable_2"."agg_8_8",
NULL::bigint) AS "Count"
   FROM "_timescaledb_internal"."_materialized_hypertable_2"
  GROUP BY "_materialized_hypertable_2"."Time",
"_materialized_hypertable_2"."m_tickerId";


About the destination server:

postgres=# SELECT version();
                                                              version       
                                                      
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)

postgres=# 

---------------------------------------
Expected:
DB is restored from the dump on the destination server.
Actual:
The command "CREATE VIEW "ctrader"."sentiment_summary_600..." fails to
create the object.

All required modules are installed on both servers.

Regards,
Boris.

Messages

DateAuthorSubject
2020-05-14 16:21:25+00PG Bug reporting formBUG #16437: The dump is stored with error in creating VIEW statement.
2020-05-14 17:18:51+00Tom LaneRe: BUG #16437: The dump is stored with error in creating VIEW statement.
2020-05-14 19:23:23+00Tom LaneRe: BUG #16437: The dump is stored with error in creating VIEW statement.