When you issue a CREATE SEQUENCE statement in a session, the current session user becomes the owner of the seqeunce.
Example:
yugabyte=# SELECT current_user;
current_user
--------------
yugabyte
(1 row)
yugabyte=# CREATE SEQUENCE s;
CREATE SEQUENCE
yugabyte=# \ds s;
List of relations
Schema | Name | Type | Owner
--------+------+----------+----------
public | s | sequence | yugabyte
(1 row)
But what if you want to later change the owner of the sequence to another user? Is that possible? Let’s try…
yugabyte=# CREATE USER luis;
CREATE ROLE
yugabyte=# ALTER SEQUENCE s OWNER TO luis;
ERROR: ALTER SEQUENCE not supported yet
LINE 1: ALTER SEQUENCE s OWNER TO luis;
^
HINT: See https://github.com/yugabyte/yugabyte-db/issues/1002. React with thumbs up to raise its priority
Uh oh – No luck there. I guess we have to wait on YB engineering to fix the GH issue.
Or do we?
Fun Fact: In YSQL, the sequences metadata is handled by the system tables as in Postgres. But the sequences data (e.g. the current val, increment, etc) is stored in one global table with one row per sequence — unlike postgres where there is one table (with exactly one row) for each sequence.
If a sequence is a table, does that mean we can SELECT from and ALTER a sequence just like a regular table? Let’s see…
yugabyte=# SELECT * FROM s;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
yugabyte=# ALTER TABLE s OWNER TO luis;
ALTER TABLE
yugabyte=# \ds s;
List of relations
Schema | Name | Type | Owner
--------+------+----------+-------
public | s | sequence | luis
(1 row)
I’ll be darned – That’s how you change the owner of a Sequence in YSQL!
UPDATE: The ALTER SEQUENCE..OWNER TO syntax will be supported beginning in YugabyteDB 2024.2.1!
Have Fun!
