Sounds about right, I've seen similar things happen in my game.
For me, any workyard not a part of my food production chains I set to "on demand". This is so that my carriers will always prioritize my food industry to prevent a crash. Also, any base goods that are gathered such as wool, wood, coal, etc. I always have on so that I can stockpile them.
Another useful tip I can offer is to use food boosting at the lower level workyards that do the gathering or refining like grain farms and windmills at least until a base supply is made. Like when I first setup a few grain farms, windmills and bakeries, I'll have the wheat farms run on common food their first couple cycles and then I'll switch the windmills to common food for the next few cycles.
This way if I were to start bread production elsewhere on the map, my carriers can take resources from another production chain without causing my economy to crash.
If my pig farm settler was complaining about not enough grain, instead of setting my breweries to on demand and having them stop selling beer, I'd put the grain farms nearest to the pig farm to consume food either common or fancy temporarily.
Yup, on demand system works fine. I set even wood cutter and sawmill on demand since a hunter was working on same forest. I used two foresters to replenish the trees that were cut. Guess what... the hunter was able to find animals for most of the game . So the animals replenish if the forest is replenished. The only down side is that construction time is slightly incresed since wood is cut after order is placed.
Food boosting is very useful in raw material if there is excess food sitting around. I always run out of coal by the time cannons are built.
The problem is not so much the grain than the water.
5 well in the sector / 100 water in stock and my 1 pig farm is still complaining... I solved that by putting brewery on demand and disable selling beer for coins when I start making my own but even set on always it shouldn't do that with 100 water in stock.
The problem is that military / religious take first priority and the goods needed to produce them are all reserved no matter how much they need.