Flatten table failure when combining current quantities with most recent prices (Top1 Projection)

BHIFP

In the context of showing someone's portfolio with most up-to-date prices, I have a table of each position's quantities and a prices table that keeps updating.

This creates latest price and works fine:

CREATE PROJECTION prices_sync.ticks_cs /*+basename(ticks),createtype(L)*/
    (
     distribution_id ENCODING RLE,
     base_asset_id ENCODING RLE,
     counter_asset_id ENCODING RLE,
     business_time ENCODING DELTAVAL,
     price ENCODING AUTO
        )
    AS
        SELECT ticks.distribution_id,
               ticks.base_asset_id,
               ticks.counter_asset_id,
               ticks.business_time,
               ticks.price
        FROM prices_sync.ticks
        LIMIT 1
    OVER (PARTITION BY distribution_id, base_asset_id, counter_asset_id
    ORDER BY business_time DESC);
SELECT START_REFRESH();
SELECT REFRESH('prices_sync.ticks');

When creating the flatten table, this failed due to ROLLBACK 7347: Default queries must refer to tables:

ALTER TABLE public.std_trades
    ADD COLUMN latest_price numeric(35, 20)
        DEFAULT (
            SELECT price
            FROM prices_sync.ticks_cs b
            WHERE base_asset_id = b.base_asset_id
              and counter_asset_id = b.counter_asset_id
              and b.distribution_id = 1000
        );

If I put topK inside though, got a different error:

ROLLBACK 4840: Subquery used as an expression returned more than one row
DETAIL: Error occurred in Join [(UNKNOWN x prices_sync.ticks_cs) using subquery and subquery (PATH ID: 1)] with localplan_id=[1]

ALTER TABLE public.std_trades
    ADD COLUMN latest_price numeric(35, 20)
        DEFAULT (
            SELECT price
            FROM (
                     SELECT ticks.distribution_id,
                            ticks.base_asset_id,
                            ticks.counter_asset_id,
                            ticks.price
                     FROM prices_sync.ticks
                     LIMIT 1 OVER (PARTITION BY distribution_id, base_asset_id, counter_asset_id ORDER BY business_time DESC)
                 ) b
            WHERE base_asset_id = b.base_asset_id
              and counter_asset_id = b.counter_asset_id
              and b.distribution_id = 1000
        );

Please advise on how to do it correctly. Maybe the best solution is not the use flatten table but a simple join whenever I need the data?

mosheg

Vertica automatically populates the DEFAULT column latest_price in the following code example.
Instead of DEFAULT, if we would use the "SET USING" syntax the latest_price would remain NULL, and would be populated by calling the function REFRESH_COLUMNS on flattened table prices_sync.std_trades.

CREATE SCHEMA prices_sync;

CREATE TABLE prices_sync.std_trades
    (distribution_id INT,
     base_asset_id  INT,
     counter_asset_id  INT,
     business_time TIMESTAMP,
     price numeric(35, 20)) 
ORDER BY business_time;

CREATE TABLE prices_sync.ticks_cs
    (distribution_id INT,
     base_asset_id  INT,
     counter_asset_id  INT,
     business_time TIMESTAMP,
     price numeric(35, 20))
ORDER BY business_time;

ALTER TABLE prices_sync.std_trades
    ADD COLUMN latest_price numeric(35, 20)
        DEFAULT (
            SELECT max(price)
            FROM prices_sync.ticks_cs b
            WHERE base_asset_id = b.base_asset_id
              and counter_asset_id = b.counter_asset_id
              and b.distribution_id = 1000
        );

BHIFP

SELECT max(price) is not what I am looking for. I am looking for the price associated with SELECT max(business_time), which was why TopK projection is needed.

mosheg

Since in your case the subquery to find the max business_time may return more than one value, a join is more suitable.