Is IDENTITY continuous in new versions of PostgreSQL?

All we need is an easy explanation of the problem, so here it is.

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?

Is IDENTITY continuous in new versions of PostgreSQL?

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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. […]

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply