Thread: BUG #16437: The dump is stored with error in creating VIEW statement.

The following bug has been logged on the website:

Bug reference:      16437
Logged by:          Boris
Email address:      koktos632@gmail.com
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.

PG Bug reporting form <noreply@postgresql.org> writes:
> 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

Hm.  I suppose that this view is valid in the source database because
"Time" and/or "m_tickerId" are the primary key of
_materialized_hypertable_2, and the restore is failing because pg_dump
didn't restore the primary key constraint first.  But it should know
to do that, and you haven't provided nearly enough information to track
down why it didn't.

Can you provide a self-contained test case that fails like this?
We probably don't need to see any data, just the database
object declarations.

            regards, tom lane

[ thanks for sending the off-list dump ]

Borys K <koktos632@gmail.com> writes:
> It is not a pure PostgreSQL server.
> I am using https://www.timescale.com/

Well, you'd really need to take this up with them.  This view
seems to be using TimeScale-specific features, and I can't
even tell whether it ought to be legal.  The view would certainly
not be legal in regular Postgres, because the table it's selecting
from has no unique indexes at all, so there's no basis for allowing
an abbreviated GROUP BY.

I'm suspecting that TimeScale may have special provisions for
dumping databases, because it's hard to see how this dump could
be reloaded as-is.  But I don't know much about that product.

            regards, tom lane