Now I am use serial
to generate table primary key in PostgreSQL 13, but today I found the ID jumps and has big gaps in the ID space. It will waste many IDs. I read the docs and found that PostgreSQL has identity
as a SQL standard generated primary key. I found a recommendation to use identity
in new version of PostgreSQL but I don't know whether ID is continuous.
What should I do get a primary key id without gaps in PostgreSQL 13?
Solution 1: I tried to change serial
to identity
like this:
BEGIN;
ALTER TABLE public.article ALTER id DROP DEFAULT; -- drop default
DROP SEQUENCE public.article_id_seq; -- drop owned sequence
ALTER TABLE public.article
-- ALTER clientid SET DATA TYPE int, -- not needed: already int
ALTER id ADD GENERATED ALWAYS AS IDENTITY (RESTART 2270886);
COMMIT;
Still gaps.
Solution 2: I tried to add a redis distribution lock about my apps insert article like this:
def save_single(guid, pub_time, title, author, content, source, link):
if content is not None and len(content) > 0:
article = Article()
article_content = ArticleContent()
beta_parser = CnbetaParser()
second_parsed_content = beta_parser.parse_cnbeta_content(content, link)
article_content.article_content = second_parsed_content
cover_image = None
lock_identifier = None
try:
# cover_image = Utils.get_cover_image_url(self, content, link)
lock_identifier = acquire_lock("article-save", 5, 5)
article.save(title, guid, author, pub_time, article_content, link, source, cover_image)
except Exception as e:
logger.error("save article data error,rss:" + source.sub_url, e)
finally:
if lock_identifier is not None:
release_lock("article-save", lock_identifier)
else:
logger.error("article content is null,title:" + title)
Still gaps.