1

I'm using PostgreSQL in DBeaver and querying data from AWS.

ana.bookings is a materialized view and it shows only the data up to 26th November 2018 even though all the tables and views prior to it show data from 3rd December 2018. I tried to refresh this materialized view by right-clicking refresh in DBeaver GUI. I also tried refreshing it using:

REFRESH MATERIALIZED VIEW
ana.bookings WITH DATA;

but it doesn't pull through the recent data. I created a view and materialized view substitutes ana.bookings2 for ana.bookings but the queries keep loading and don't show any output even if I include a limit. How can I make ana.bookings, rpt.daily_actuals and rpt.daily_performace show the latest data?

Schema[1]

SQL behind this view:

 CREATE MATERIALIZED VIEW ana.bookings2
TABLESPACE pg_default
AS WITH credits AS (
         SELECT transactions.transaction_id,
            transactions.transaction_type,
            transactions.billing_id,
            transactions.billing_date,
            transactions.member_id,
            transactions.billing_product,
            transactions.description,
            transactions.trip_id,
            transactions.net,
            transactions.tax,
            transactions.total,
            transactions.gl_code,
            transactions.gl_description,
            transactions.vin,
            transactions.trip_date,
            transactions.trip_duration,
            transactions.trip_distance
           FROM prc.transactions
          WHERE transactions.gl_code::text = 'PROMO.FREE_DRIVING'::text
        ), usage_rev AS (
         SELECT transactions.transaction_id,
            transactions.transaction_type,
            transactions.billing_id,
            transactions.billing_date,
            transactions.member_id,
            transactions.billing_product,
            transactions.description,
            transactions.trip_id,
            transactions.net,
            transactions.tax,
            transactions.total,
            transactions.gl_code,
            transactions.gl_description,
            transactions.vin,
            transactions.trip_date,
            transactions.trip_duration,
            transactions.trip_distance
           FROM prc.transactions
          WHERE transactions.transaction_type = 'trip'::text
        ), waiver_revenue AS (
         SELECT transactions.transaction_id,
            transactions.transaction_type,
            transactions.billing_id,
            transactions.billing_date,
            transactions.member_id,
            transactions.billing_product,
            transactions.description,
            transactions.trip_id,
            transactions.net,
            transactions.tax,
            transactions.total,
            transactions.gl_code,
            transactions.gl_description,
            transactions.vin,
            transactions.trip_date,
            transactions.trip_duration,
            transactions.trip_distance
           FROM prc.transactions
          WHERE transactions.description::text = 'Collision Damage Waiver'::text
        ), grouped_rev AS (
         SELECT u.trip_id AS booking_id,
            max(u.net) AS usage_revenue,
            COALESCE(sum(c.net), 0::double precision) AS credit,
            round((max(u.net) + COALESCE(sum(c.net), 0::double precision))::numeric, 2) AS uc_revenue,
            max(wr.net) AS waiver_revenue
           FROM usage_rev u
             LEFT JOIN credits c ON c.trip_id::text = u.trip_id::text
             LEFT JOIN waiver_revenue wr ON u.trip_id::text = wr.trip_id::text
          GROUP BY u.trip_id
        )
 SELECT tr.booking_id,
    tr.booking_duration,
    tr.reservation_duration,
    tr.trip_duration,
    tr.booking_start_date,
    tr.trip_start_date,
    tr.booking_end_date,
    tr.vin,
    tr.vehicle_plate,
    tr.member_id,
    m.role AS member_type,
    t.usage_revenue,
    t.credit,
    t.uc_revenue,
    t.waiver_revenue,
    tr.distance,
    tr.start_lat,
    tr.start_lon,
    tr.end_lat,
    tr.end_lon,
    sz.name AS start_zone_name,
    ez.name AS end_zone_name,
    tr.start_pos_gis,
    tr.end_pos_gis,
    sz.zone_id AS start_zone_id,
    ez.zone_id AS end_zone_id
   FROM prc.trips tr
     LEFT JOIN grouped_rev t ON t.booking_id::text = tr.booking_id::text
     JOIN ana.members m ON m.member_id::text = tr.member_id::text
     LEFT JOIN rw.quartiers sz ON st_contains(sz.geom, tr.start_pos_gis)
     LEFT JOIN rw.quartiers ez ON st_contains(ez.geom, tr.end_pos_gis)
     JOIN billed_trips_raw bt ON bt.trip_id::text = tr.booking_id::text
  WHERE tr.booking_start_date >= '2016-09-01 00:00:00'::timestamp without time zone AND tr.booking_end_date < ('now'::text::date - 1) AND m.test_account_p = 'f'::text
  ORDER BY tr.booking_end_date
WITH DATA;
  • Can you take dbeaver out of the equation by connecting with some other tool, like "psql" and refreshing? It could be a dbeaver-specific bug where it never actually sends the refresh command. Also, are you using RDS (amazon managed and tweaked flavor of PostgreSQL) , or did you install unchanged community PostgreSQL yourself on an aws instance, and what version? – jjanes Dec 5 '18 at 20:47
2

This was fixed by upgrading the AWS RDS instance and using PostgreSQL commands:

REFRESH MATERIALIZED VIEW ana.bookings;
REFRESH MATERIALIZED VIEW rpt.daily_performace;
  • 3
    Please accept your answer so that your question is marked as resolved – a_horse_with_no_name Sep 22 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.