0

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?

enter image description here

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.

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
Dolphin
  • 929
  • 5
  • 21
  • 40

1 Answers1

6

The term is IDENTITY, not IDENTIFY. (I fixed it in your question.)

IDENTITY columns (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY) are based on a SEQUENCE just like the older serial columns. You have to expect gaps in the serial numbers with either, that's in the their nature, and nothing to worry about.

Differences are addressed in my answer on stackexchange (where you obviously found the code to convert your serial to an IDENTITY column):

But nowhere did I suggest that the conversion would remove gaps in the ID space. Or that this would be desirable. If you positively think you need that, consider:

It starts with a similar disclaimer like you already received in the comments (and some alternatives):

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. [...]

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633