CODEWITHSUNDEEP
postgresqlsequenceinformation-schemacolumn-defaults
Satheesh K
Satheesh K

Reputation: 132

PostgreSQL shows wrong sequence details in the table's information schema

Recently I saw a strange scenario with my PostgreSQL DB. The information schema of my database is showing a different sequence name than the one actually allocated for the column of my table.

The issue is:

I have a table tab_1

id  name 
1   emp1
2   emp2
3   emp3

Previously the id column (integer) of the table was an auto generated field where the sequence number was generated at run time via JPA. (Sequence name: tab_1_seq)

We made a change and updated the table's column id to bigserial and the sequence is maintained in the column level (allocated new sequence: tab_1_temp_seq) not handled by the JPA anymore.

After this change everything was working fine for few months and after that we faced an error - "the sequence "tab_1_temp_seq" is not yet defined in this session"

On analyzing the issue I found out that there is a mismatch between the sequences allocated for the table.

In the table structure, we where shown the sequence as tab_1_temp_seq and in the information_schema the table was allocated with the old sequence - tab_1_seq.

I am not sure what has really triggered this to happen, as we are not managing our database system. If you have faced any issues like this, kindly let me know its root cause.

Queries:

SELECT table_name, column_name, column_default from information_schema.columns where table_name = ‘tab_1’;

result : 

table_name   column_name  column_default
tab_1        id           nextval('tab_1_seq::regclass')

Below are the details found in the table structure/properties: 

id      nextval('tab_1_temp_seq::regclass')  

name    varChar

Upvotes: 0

Views: 784

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246023

Perhaps you are suffering from data corruption, but it is most likely that you are suffering from bad tools to visualize your database objects. Whatever program shows you the “table structure/properties” might be confused.

To find out the truth (which DEFAULT value PostgreSQL uses), run:

SELECT pg_get_expr(adbin, adrelid)
FROM pg_attrdef
WHERE adrelid = 'tab1'::regclass;

This is also what information_schema.columns will show, but I added the naked query for clarity.

This DEFAULT value will be used whenever the INSERT statement either doesn't specify the id column or fills it with the special value DEFAULT.

Perhaps the confusion is also caused by different programs that may set default values in their way. The way I showed above is PostgreSQL's way, but nothing can keep a third-party tool from using its own sequence to filling the id.

Upvotes: 0

Related Questions