1

I want to update the sequences for a table in RDS Postgres 11. Tried the following commands but I don't see the changes committed to the db. I even used commit.
What am I missing?

1.   SELECT setval(pg_get_serial_sequence('table1', 'id'), coalesce(max(id),0) + 1, false) FROM table1;

2.   SELECT setval('table1_id_seq', (SELECT COALESCE(max(id), 0) + 1 FROM table1));
     ALTER TABLE table1 ALTER COLUMN id SET DEFAULT nextval('table1_id_seq');

CREATE TABLE public.table1 (
    id serial4 NOT NULL, --default nextval('table1_id_seq'::regclass)  
    account_id int4 NOT NULL, 
    CONSTRAINT table1_pkey PRIMARY KEY (id) );

select currval('table1_id_seq') returns 6.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Falcon
  • 47
  • 6
  • I am not seeing how you are fetching a sequence with `pg_get_serial_sequence` for a column you have not set the sequence for yet? `pg_get_serial_sequence` will only work with a sequence that is 'owned' by a column. To your question add the table definition for `table1`. – Adrian Klaver Oct 22 '22 at 21:53
  • CREATE TABLE public.table1 ( id serial4 NOT NULL, --default nextval('table1_id_seq'::regclass) --select currval('table_id_seq')=6 account_id int4 NOT NULL, CONSTRAINT table1_pkey PRIMARY KEY (id), ); – Falcon Oct 22 '22 at 22:04
  • Please read the comments completely. I went ahead and added the table definition to the question as I asked you to do. In any case you either are having copy/paste issues or naming issues. You are referring to the sequence as either `table1_id_seq` or `table_id_seq`. – Adrian Klaver Oct 22 '22 at 22:12
  • @AdrianKlaver its typo. updated the question. – Falcon Oct 22 '22 at 22:51
  • I cannot reproduce. To your question add the result from `select * from table1_id_seq ;`. Also from `select max(id) from table1;`. – Adrian Klaver Oct 22 '22 at 23:10

1 Answers1

1

If you ...

don't see the changes committed to the db

... even though you positively committed the transaction (and you are connected to the right database), then I see only two possible explanations.

1. Barking up the wrong tree

In your question, public.table1 is schema-qualified in the CREATE TABLE statement, but not in either of:

SELECT setval(pg_get_serial_sequence('table1', 'id'), ...
SELECT setval('table1_id_seq', ...

If you have another table1 in another schema that comes before 'public' in the search_path, you end up modifying the respective sequence of that table.

Since the factory default in Postgres is search_path = "$user",public, the obvious suspect is a table of the same name in the "home" schema of the current role. See:

Solution:
Fix the search path or schema-qualify table and sequence names.

2. Missing privilege

It should be safe to assume your database role has SELECT (or even all) privileges on table1. But you need separate, additional privileges on the underlying SEQUENCE to run setval() on it.

You should see an error message for missing privileges, though!
See:

Solution:

GRANT USAGE ON SEQUENCE table1_id_seq TO the_role;  -- your role here

Or work with an IDENTITY column instead of a serial (Postgres 10+), which inherits privileges for the table implicitly. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • thank you. solution 1 is the fix in my case. I updated the sequences for all other tables in the target db. however, for some tables sequence "last_value" is entirely different than in source table. i can fix it using setval function, but wondering why is this difference in the first place. – Falcon Oct 23 '22 at 20:31
  • 1
    @Falcon: A `serial` column just offers default values. You can override by inserting any values manually - in which case the underlying `SEQUENCE` may fall out of sync. Make sure you understand the nature of `serial` columns: https://stackoverflow.com/a/27309311/939860 An `IDENTITY` column with `GENERATED ALWAYS` disallows that (but can still be overridden). – Erwin Brandstetter Oct 24 '22 at 01:47