3 ms·

We have a similar setup; but there are still scenarios where you need a IF NOT EXISTS. A classic example of a table on dev being close to empty, and a table on

We have a similar setup; but there are still scenarios where you need a IF NOT EXISTS. A classic example of a table on dev being close to empty, and a table on prod being huge and taking longer than the default timeout ~30 seconds. Depending on what the timeout is (connection timeout, transaction timeout etc) the DDL operation _may_ still be running. But the migration "process" has failed. So you end up having to slightly modify the attempted migration to account for the fact the thing already exists. It's an increasingly rare occurrence, probably a few times a year. Like anything the more we do it, the better we get at pulling left and preventing issues in the first place.

sgarland 2y ago
SET statement_timeout = '1h'; CREATE INDEX... Or if you have a DDL user (which is smart; your app user should not be allowed to execute DDL), you can use `ALTER ROLE` for them to have whatever defaults you'd like. MySQL has a similar variable, `max_execution_time`.
- 1a527dd5 2y ago
  Yeah we are aware of statement_timeout. But it's not something we want to actively promote. Most people copy and paste a previous migration and then amend it to their previous needs. So if the previous migration set the statement timeout to 1h, and the new migration is doing something we want to stop/prohibit like ADD COLUMN DEFAULT (which takes a lock) or SET NOT NULL on a huge table (which again can cause a lock). We want to prevent locking and prefer the migration to fail earlier and hit the default statement timeout. At which point a conversation occurs how best to do the DDL without effecting prod. In terms of DDL DML users; we have it split into three- DDL (Migrations) DML (Data only, e.g. correcting data) AppTier user (which is DML + a few more things)
  - sgarland 2y ago
    Not trying to be snarky here (and this is by no means unique to your situation) but it sounds like what you have is a knowledge issue more than anything. Don’t apply things in prod you don’t understand. EDIT: by “you,” I mean people copy/pasting migrations and applying them, if that wasn’t clear. The rise of RDS et al. has utterly killed the majority of people’s knowledge of RDBMS, if they had it to start with. It’s a shame. Also a solid and enjoyable career for me, so I suppose it’s not all bad.
    - 1a527dd5 2y ago
      Eh, I'm 50/50 on this. I gave up long ago about people expecting to know and understanding about _everything_ they are doing. I've come to accept that some people just want to code etc. I pick and choose my battles and where possible enforce certain rules (e.g. you have to have `timestamp with time zone` etc etc) at build time. I personally find learning about stuff fun! It's why I've grown as an engineer and now have the capability to debug past the code stack into the database. You have to pick and choose your battles, those engineers who shown interest (what is an index and why do we need it type of questions) I take my time to educate and move the critical mass/inertia in the right direction.

sgarland 2y ago

SET statement_timeout = '1h'; CREATE INDEX... Or if you have a DDL user (which is smart; your app user should not be allowed to execute DDL), you can use `ALTER ROLE` for them to have whatever defaults you'd like. MySQL has a similar variable, `max_execution_time`.

1a527dd5 2y ago
Yeah we are aware of statement_timeout. But it's not something we want to actively promote. Most people copy and paste a previous migration and then amend it to their previous needs. So if the previous migration set the statement timeout to 1h, and the new migration is doing something we want to stop/prohibit like ADD COLUMN DEFAULT (which takes a lock) or SET NOT NULL on a huge table (which again can cause a lock). We want to prevent locking and prefer the migration to fail earlier and hit the default statement timeout. At which point a conversation occurs how best to do the DDL without effecting prod. In terms of DDL DML users; we have it split into three- DDL (Migrations) DML (Data only, e.g. correcting data) AppTier user (which is DML + a few more things)
- sgarland 2y ago
  Not trying to be snarky here (and this is by no means unique to your situation) but it sounds like what you have is a knowledge issue more than anything. Don’t apply things in prod you don’t understand. EDIT: by “you,” I mean people copy/pasting migrations and applying them, if that wasn’t clear. The rise of RDS et al. has utterly killed the majority of people’s knowledge of RDBMS, if they had it to start with. It’s a shame. Also a solid and enjoyable career for me, so I suppose it’s not all bad.
  - 1a527dd5 2y ago
    Eh, I'm 50/50 on this. I gave up long ago about people expecting to know and understanding about _everything_ they are doing. I've come to accept that some people just want to code etc. I pick and choose my battles and where possible enforce certain rules (e.g. you have to have `timestamp with time zone` etc etc) at build time. I personally find learning about stuff fun! It's why I've grown as an engineer and now have the capability to debug past the code stack into the database. You have to pick and choose your battles, those engineers who shown interest (what is an index and why do we need it type of questions) I take my time to educate and move the critical mass/inertia in the right direction.