Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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?

Answers

  • moshegmosheg Employee
    edited September 9

    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
            );
    
    
  • 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.

  • moshegmosheg Employee

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.