Dissolved gases in the water naturally find equilibrium with the gases in the atmosphere. Surface agitation promotes better gas exchange towards this equilibrium. If the dissolved gases are higher than this equilibrium they will offgas into the atmosphere. If dissolved gases are lower than this equilibrium then gases will be absorbed from the atmosphere. This works the same for both O2 and CO2. It isnt the case that O2 being absorbed into the water causes CO2 to offgas or vice versa. They will both find their equilibrium points more readily with good surface agitation.
If you are injecting CO2 into the water, surface agitation will cause it to offgas because the concentration is higher than the equilibrium. Poor surface agitation will trap dissolved gas, slowing down the offgassing. So you are right that filters that cause a lot of surface agitation arent the best with injected CO2. Canisters are good combinations with injected CO2. If you arent injecting CO2 then good surface agitation will promote gas exchange and O2 and CO2 will be absorbed into the water. With poor surface agitation, neither O2 or CO2 can be easily replenished as they get used up by fish and plant respiration.
With injected CO2 systems, you want low surface agitation when the lights and CO2 are on to prevent the CO2 from off gassing. Plant respiration will keep the water oxygenated, and the low surface agitation will prevent the O2 offgassing. When lights and CO2 are off, you want good surface agitation to keep the water oxygenated, so you turn on airstones at night.
If you arent injecting CO2 you want good surface agitation all the time, to keep O2 concentrations good for the fish, and CO2 good for any plants you might have.
