[YSQL] SELECTing directly from a sequence throws an error

[hectorgcr]

Currently we don't support selecting directly from a sequence:

CREATE SEQUENCE
postgres=# select last_value from s;
ERROR:  Not found: Error loading table with id 000047c5000030008000000000006710: Not found (yb/master/catalog_manager.cc:3398): The table does not exist: table_id: "000047c5000030008000000000006710"```

[kmuthukk]

This could be lower-pri, right? I am not sure if this is a common flow. Most apps would do s.nextVal() rather than query this table.

[hectorgcr]

This could be lower-pri, right? I am not sure if this is a common flow. Most apps would do s.nextVal() rather than query this table.

Correct. But I'm just trying to open bugs for all the issues I know that are related to sequences.

For now we will improve the error message to let the user know that we don't support selecting from sequences directly instead of throwing some error directly from catalog_manager.cc.

The disadvantage of using nextval() is that it increments the last_value.

[kmuthukk]

makes sense @hectorgcr .

[hectorgcr]

@ndeodhar found that pg_dump doesn't work correctly for sequences because it is trying to do a SELECT on the sequence:

pg_dump: [archiver (db)] query failed: ERROR:  "bar_a_seq" is a sequence table
DETAIL:  Querying sequence tables is not supported yet.
HINT:  Use lastval() and currval() instead.
pg_dump: [archiver (db)] query was: SELECT last_value, is_called FROM public.bar_a_seq./postgres/bin/pg_dump -d postgres -h 127.0.0.1 -p 5433 -U postgres
pg_dump: [archiver (db)] query failed: ERROR:  "bar_a_seq" is a sequence table
DETAIL:  Querying sequence tables is not supported yet.
HINT:  Use lastval() and currval() instead.
pg_dump: [archiver (db)] query was: SELECT last_value, is_called FROM public.bar_a_seq```

[ndeodhar]

Another thing to verify: Since we create a separate postgres table for sequence, it's likely that pg_dump will add a "create table" statement for that sequence table. This may cause issues while loading the data.