Unsupported Query for Postgres-14

[akshayjain3450]

This is the response of SHOW PROXIED QUERIES:

And the Table Definition:

Why is this query not supported?

This are the docker readyset logs:
2024-04-04 17:25:26 2024-04-04T11:55:26.288356Z INFO readyset: Starting ReadySet adapter options=Options { address: Some(0.0.0.0:5433), deployment: "readyset.db", database_type: None, deployment_mode: Standalone, standalone: false, embedded_readers: false, authority: Standalone, authority_address: ".", log_slow: false, allow_unauthenticated_connections: false, query_caching: Explicit, max_processing_minutes: 15, migration_task_interval: 20000, metrics_address: 0.0.0.0:6034, username: None, password: None, prometheus_metrics: true, noria_metrics: false, query_log_mode: Enabled, external_address: None, tracing: Options { log_path: None, log_rotation: Daily, log_format: Full, no_color: false, log_level: "info", tracing_host: None, tracing_sample_percent: Percent(0.01), statement_logging: false, statement_log_path: None }, psql_options: Options { readyset_identity_file: None, readyset_identity_file_password: None, postgres_authentication_method: ScramSha256 }, allow_unsupported_set: false, unsupported_set_mode: Error, views_polling_interval: 5, migration_request_timeout_ms: 1800000, controller_request_timeout_ms: 5000, query_max_failure_seconds: 9223372036854775, fallback_recovery_seconds: 0, non_blocking_reads: false, server_worker_options: WorkerOptions { durability: Permanent, persistence_threads: None, memory_limit: 0, memory_check_freq: 1, eviction_kind: LRU, no_partial: false, allow_full_materialization: false, enable_packet_filters: false, min_workers: 1, shards: 0, volume_id: None, enable_experimental_topk_support: false, enable_experimental_paginate_support: false, enable_experimental_mixed_comparisons: false, enable_experimental_straddled_joins: false, enable_experimental_post_lookup: false, storage_dir: None, db_dir: None, domain_replication_options: ReplicationOptions { reader_replicas: None, non_base_replicas: None }, replicator_config: UpstreamConfig { upstream_db_url: Some("postgres://akshayjain:[email protected]:5432/postgres"), disable_upstream_ssl_verification: false, ssl_root_cert: None, disable_setup_ddl_replication: false, replication_server_id: None, replicator_restart_timeout: 1s, replication_tables: None, replication_tables_ignore: None, snapshot_report_interval_secs: 30, max_parallel_snapshot_tables: None, replication_pool_size: 50, ignore_ulimit_check: false, status_update_interval_secs: 10 }, worker_request_timeout_seconds: 1800, background_recovery_interval_seconds: 20, verbose_domain_metrics: false }, disable_telemetry: false, wait_for_failpoint: false, experimental_placeholder_inlining: false, no_upstream_connections: false, cleanup: false, controller_address: None, query_status_capacity: 100000, cache_ddl_address: None } 2024-04-04 17:25:26 2024-04-04T11:55:26.288528Z INFO readyset: version={release-version: stable-240328, commit_id: 66f30ac630e463b91a31e3af6f094565a7886369, platform: x86_64-unknown-linux-gnu, rustc_version: rustc 1.75.0-nightly (fdaaaf9f9 2023-11-08), profile: release, opt_level: 3} 2024-04-04 17:25:26 2024-04-04T11:55:26.288578Z INFO readyset: Listening for new connections listen_address=0.0.0.0:5433 2024-04-04 17:25:26 2024-04-04T11:55:26.288617Z INFO Connecting to RS server: readyset: options.authority_address=. options.deployment=readyset.db 2024-04-04 17:25:26 2024-04-04T11:55:26.307532Z INFO Connecting to RS server: readyset: ReadySetHandle created 2024-04-04 17:25:26 2024-04-04T11:55:26.307694Z INFO Connecting to RS server: readyset: Now capturing ctrl-c and SIGTERM events 2024-04-04 17:25:26 2024-04-04T11:55:26.307791Z INFO Connecting to RS server: readyset: PrometheusHandle created 2024-04-04 17:25:26 2024-04-04T11:55:26.307812Z INFO Connecting to RS server: readyset: Query logs are enabled. Spawning query logger 2024-04-04 17:25:26 2024-04-04T11:55:26.307919Z INFO Connecting to RS server: readyset: Will perform Blocking Reads 2024-04-04 17:25:26 2024-04-04T11:55:26.307960Z INFO Connecting to RS server: readyset: migration_style=Explicit 2024-04-04 17:25:26 2024-04-04T11:55:26.309881Z INFO Connecting to RS server: readyset: migration_mode=OutOfBand 2024-04-04 17:25:26 2024-04-04T11:55:26.310011Z INFO Connecting to RS server: readyset: Spawning HTTP request server task 2024-04-04 17:25:26 2024-04-04T11:55:26.507011Z INFO Connecting to RS server: readyset: Spawning migration handler task 2024-04-04 17:25:26 2024-04-04T11:55:26.507138Z INFO Connecting to RS server: readyset: Spawning explicit migrations task 2024-04-04 17:25:26 2024-04-04T11:55:26.507169Z INFO readyset_server::builder: Using 8 persistence threads 2024-04-04 17:25:26 2024-04-04T11:55:26.507471Z INFO Connecting to RS server: readyset: supported=false 2024-04-04 17:25:26 2024-04-04T11:55:26.508082Z INFO readyset_server::controller: won leader election, creating Leader 2024-04-04 17:25:26 2024-04-04T11:55:26.508325Z INFO readyset_server::controller::inner: received registration payload from worker worker_uri=http://127.0.0.1:6033/ reader_addr=127.0.0.1:4000 2024-04-04 17:25:26 2024-04-04T11:55:26.666526Z INFO readyset_server::worker: worker informed of new controller controller_uri=http://127.0.0.1:6033/ 2024-04-04 17:25:26 2024-04-04T11:55:26.666979Z INFO readyset_server::worker: controller requested that this worker clears its existing domains 2024-04-04 17:25:26 2024-04-04T11:55:26.667674Z INFO readyset_server::controller::inner: now have 1 of 1 required workers 2024-04-04 17:25:26 2024-04-04T11:55:26.741522Z INFO replicators::postgres_connector::ddl_replication: Setting up DDL replication 2024-04-04 17:25:26 2024-04-04T11:55:26.742748Z INFO tokio_postgres::connection: NOTICE: schema "readyset" already exists, skipping 2024-04-04 17:25:26 2024-04-04T11:55:26.753972Z INFO replicators::postgres_connector::ddl_replication: Set up DDL replication 2024-04-04 17:25:26 2024-04-04T11:55:26.771571Z INFO replicators::postgres_connector::connector: Dropping replication slot if exists slot="readyset" 2024-04-04 17:25:26 2024-04-04T11:55:26.773474Z INFO replicators::postgres_connector::connector: Creating replication slot slot="readyset" temporary=false 2024-04-04 17:25:26 2024-04-04T11:55:26.779937Z INFO replicators::noria_adapter: Connected to PostgreSQL 2024-04-04 17:25:26 2024-04-04T11:55:26.801294Z INFO replicators::postgres_connector::snapshot: Snapshotting tables tables=0 max_parallel_snapshot_tables=15 2024-04-04 17:25:26 2024-04-04T11:55:26.801703Z INFO replicators::noria_adapter: Snapshot finished 2024-04-04 17:25:26 2024-04-04T11:55:26.804725Z INFO replicators::postgres_connector::connector: confirmed_flush_lsn="0/22D6200" wal_status="reserved" 2024-04-04 17:25:26 2024-04-04T11:55:26.805975Z INFO replicators::noria_adapter: Streaming replication started 2024-04-04 17:25:27 2024-04-04T11:55:27.510193Z INFO readyset_server::worker: worker informed of new controller controller_uri=http://127.0.0.1:6033/ 2024-04-04 17:25:28 2024-04-04T11:55:28.485366Z WARN readyset_telemetry_reporter::reporter: failed to send telemetry res=Err(Elapsed(())) event=AdapterStart payload=Telemetry { db_backend: Some("postgresql"), db_version: None, adapter_version: Some("1.2.0"), server_version: None, query_id: None, schema: None, proxied_query: None, migration_status: None } 2024-04-04 17:25:30 2024-04-04T11:55:30.487167Z WARN readyset_telemetry_reporter::reporter: failed to send telemetry res=Err(Elapsed(())) event=ServerStart payload=Telemetry { db_backend: None, db_version: None, adapter_version: None, server_version: Some("0.7.0"), query_id: None, schema: None, proxied_query: None, migration_status: None } 2024-04-04 17:42:26 2024-04-04T12:12:26.886122Z WARN replicators::noria_adapter: Restarting adapter after error encountered error=Error during replication: WAL error: ReadySetError(ReplicationFailed("connection closed")) 2024-04-04 17:42:26 2024-04-04T12:12:26.886213Z ERROR replicators: Error in replication, will retry after timeout error=Error during replication: WAL error: ReadySetError(ReplicationFailed("connection closed")) timeout_sec=1 2024-04-04 17:42:28 2024-04-04T12:12:28.008810Z INFO replicators::postgres_connector::ddl_replication: Setting up DDL replication 2024-04-04 17:42:28 2024-04-04T12:12:28.010077Z INFO tokio_postgres::connection: NOTICE: schema "readyset" already exists, skipping 2024-04-04 17:42:28 2024-04-04T12:12:28.017976Z INFO replicators::postgres_connector::ddl_replication: Set up DDL replication 2024-04-04 17:42:28 2024-04-04T12:12:28.039931Z INFO replicators::noria_adapter: Connected to PostgreSQL 2024-04-04 17:42:28 2024-04-04T12:12:28.044024Z INFO replicators::noria_adapter: readyset_slot_exists full_resnapshot=false resnapshot=false pos=Some(0/22E0F80) 2024-04-04 17:42:28 2024-04-04T12:12:28.047553Z INFO replicators::postgres_connector::connector: confirmed_flush_lsn="0/22E10A0" wal_status="reserved" 2024-04-04 17:42:28 2024-04-04T12:12:28.050507Z INFO replicators::noria_adapter: Streaming replication started

[altmannmarcelo]

Hi @akshayjain3450 .

You can run CREATE CACHE FROM [YOUR QUERY]; and it will give you a more verbose message on why this is not supported.
From what I can see your query has no filters, meaning it will require a full materialization of the base table, which is disabled by default.

[akshayjain3450]

How can I enable it so that full materialization is possible?