Issues with using *.() projection after updating main table

[pavitrakumar78]

Hi,

I've been having some issues with using projections to get results for many symbols at once (like the VWAP for all symbols example).

Issue:

>select * from us_eod_quotes;
>select (us_eod_get(symbol_id)).* from us_eod_securities;
>select symbol_id,cs_sum(close*volume) / cs_sum(volume) as VWAP from us_eod_quotes;

All the above statements give the following error:

ERROR:  Column symbol_date of table us_eod can not be individually imported
CONTEXT:  PL/pgSQL function us_eod_load_column(text) line 1 at RETURN
SQL statement "select us_eod_load_column('symbol_date')"
PL/pgSQL function us_eod_get(character varying,date,date,bigint) line 6 at assignment

Here's what I do to get these tables:

Table us_symbols contains list of all symbols (columns: symbol_id (p.key), symbol_code ...) which gets updated everyday with new symbols, removal of some symbols etc.,

Table us_eod is the main table (symbol_id,symbol_date,o,h,l,c,v) which gets updated everyday with new symbols, removal of some symbols etc., This table's symbol_id is foreign key from us_symbols's symbol_id.

Following example similar to VWAP for all symbols in docs:

select cs_create('us_eod', 'symbol_date', 'symbol_id');
select us_eod_load();

create table us_eod_securities (symbol_id varchar NOT NULL);
insert into us_eod_securities select distinct symbol_id from us_eod;
create view us_eod_quotes as select (us_eod_get(symbol_id)).* from us_eod_securities;

Because there are additions and removals, I also update the us_eod_securities table using triggers like below.

CREATE OR REPLACE FUNCTION us_eod_securities_insert() RETURNS TRIGGER AS $us_eod_securities_insert$
BEGIN
    INSERT INTO us_eod_securities (symbol_id) VALUES (NEW.symbol_id);
    RETURN NEW;
END;
$us_eod_securities_insert$ language plpgsql;

CREATE TRIGGER us_eod_securities_insert_trigger AFTER INSERT ON us_symbols
FOR EACH ROW
EXECUTE PROCEDURE us_eod_securities_insert();

CREATE OR REPLACE FUNCTION us_eod_securities_delete() RETURNS TRIGGER AS $us_eod_securities_delete$
BEGIN
    DELETE FROM us_eod_securities WHERE symbol_id = OLD.symbol_id;
    RETURN NEW;
END;
$us_eod_securities_delete$ language plpgsql;

CREATE TRIGGER us_eod_securities_delete_trigger AFTER DELETE ON us_symbols
FOR EACH ROW
EXECUTE PROCEDURE us_eod_securities_delete();

This, along with the already existing triggers created by cs_create for us_eod should mean loading issues multi-symbols using us_eod_quotes should work. But still, I frequently get the error mentioned at the top of this post. Sometimes, I have to reinstall imcs, restart the server, and do the process again to make it work. Is there a solution for this?

Also, queries using plain *_get (`us_eod_get) work fine:

select cs_wavg(volume, close_price) as VWAP from us_eod_get('IBM.US', date('01-Jan-2015'), date('01-Jan-2016'));

The issue is only with the projection which is why I thought it had something to do with missing symbol_ids with the us_eod_securities table - but even after double checking all symbol tables have the same no.of rows, I still keep keep getting the error after every main table update.

Edit: the only solution I have so far for this issue is: -restart pgsql server -execute:

delete from us_eod_securities;
insert into us_eod_securities select distinct symbol_id from us_eod;
drop view us_eod_quotes;
create view us_eod_quotes as select (us_eod_get(symbol_id)).* from us_eod_securities;

[knizhnik]

Looks like the problem is somehow related with autoloading of data. But I could not understand the reason. Can you send me complete sample reproducing the problem (complete sql script for initializing database schema and populating it with some dummy data)?

[pavitrakumar78]

Here's a pg dump + testing queries pastebin: https://pastebin.com/k6TP8SMw

The problem is mainly from a symbol present in us_eod_securities but not in any of the other tables. Another issue is - why does us_eod_get still have a deleted data from the original table?

Edit: updated pastebin with error again for adding new data. As you said, the problem is with autoloading of data - probably triggers not firing properly?

[knizhnik]

I have committed the patch. Please notice that I have changed implementation of generated *_get() function. So to make this function works correctly you will have to recreate IMCS tables (call cs_create). If you have not do it: just rebuild extension and restart postgres, then there will be no autoload problem any more, but get will return empty row for absent symbol.

[pavitrakumar78]

Thank you for the fix!

I always reinstall imcs after any patch and then reload all tables for testing so should be no more problems!

I will close this issue.