0

When doing administration on big production postgres database, it seems that even such ALTER TABLE -ddl queries cannot be safely run, that should be no-operations?

Has anyone looked into Postgres source codes or have previous knowledge on what happens in such case?

Looking back at what just happened it seems that on query like: ALTER TABLE table ALTER COLUMN column TYPE int8;

  • Table statistics disappeared on that column?
  • pages were thrown out of cache?

Even though the column type was already in correct type.

What I learned from this is, that all such queries, even if no operation, should be wrapped inside a function that checks, if anything needs to be done.

Improve this question
8
  • 1
    That's where schema migration tools (Liquibase, Flyway, squitch, ... ) come into play: they will only run those migrations that are needed.
    – user1822
    Apr 11 at 11:10
  • Thanks for the comment although it didn't answer the actual question.
    – anttik
    Apr 11 at 11:26
  • To be honest: I am not sure what the question actually is.
    – user1822
    Apr 11 at 11:35
  • question is: what happens in such case? What does the ALTER TABLE table ALTER COLUMN column TYPE int8; change when column type is already int8?
    – anttik
    Apr 11 at 11:47
  • Looking at the postgres source codes, this might be at least what happens: github.com/postgres/postgres/blob/… It seems that the function call is executed without checking column type at all.
    – anttik
    Apr 11 at 12:10

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.