Roguelikes are the ultimate in procgen technology, and should be the basis of any design. (DF is the ultimate simulation but uses procgen as a tool to achieve it; a different vein than NMS trying to explore the procgen and its direct outcomes itself)
1. Any imaginary world needs to be internally consistent to be interesting. Procgen does not void this rule; randomization needs to be gated to that internal consistency.
2. A world can be composed of multiple environments, and each environment can a different definition of internal consistency. Dungeon Crawl Stone Soup does this very openly and to fairly good effect, but pretty much any decent procgen game will inevitably end up with the same choice, because it's what enables you to play with various procgen systems/rules while still having a coherent overall game.
3. Generalizing 2, procgen systems generally do not start from nothing; You embed reasonably well-defined bases (races, weapon forms, etc) and then apply randomization on top of those bases to form the final element. Meaningful fundamental variation in those bases leads to guaranteed meaningful variation in the end result.
4. Games are fundamentally interactive systems. Generating a thousand variations without actually changing interaction is equivalent to generating exactly one variation. A blue orc vs a red orc is a not real variant, unless you make it so by attaching effects / statuses / capabilities. The color distinction is then a signal into the more abstracted changes that have occurred. This is where NMS falls over -- the images have changed, but the interaction has not.
5. A corollary to point 4, changing numbers without changing interaction is equivalent to doing nothing. If a meaningful damage threshold is functionally every 10 points of damage, then generating numbers 0-9, 11-19, etc is equivalent to generating just the numbers 0 and 10. This is where borderlands falls over -- a million variations in guns, but the vast majority of those variations are just numeric differences that don't change anything in practice.
6. If the procgen applies to equipment/builds, you pretty much need permadeath as a driver for exploring a procgen game and its systems, because you need players to explore for a local optima. Exploring the space for a global optima in a procgen game inevitably devolves into a gambling simulator, because players optimize out the fun, and the optimal play is to the kill unit XYZ repeatedly for the 0.5% drop. Games like Terraria fall into this trap (though Terraria resolves it mostly by making unit XYZ be trivially summon-able bosses)
7. Procgen can never guarantee interesting results; You inevitably need a system of vaults/artifacts -- hardcoded quality items -- to guarantee periodic interesting locations/items. Ideally more notable than average generation, but less than strictly optimal so as to not overpower the other systems -- an issue I've had with games like Diablo 3, where finding an item-set was vastly more powerful than anything else that I acquired for the next 5+ hours, eliminating the entire looting system (and even character skill system, since it forced my build too)
8. Decent Procgen is inevitably rule-based, with possibly multiple chained-rules each doing their own rolls, to ensure coherency. Ideally the rule-chain is roughly discoverable from the end results (by virtue of following internally consistent and apparent rules) -- an end-result that cannot be explained is indistinguishable from pure-random, and pure-random in incoherent. See any Tabletop RPG generation tables.
9. Hell, just start any procgen design from tabletop RPG tables.
10. The number of variations that can be produced is meaningless. This is only for marketing.
11. A procedural system that generates exactly once is fairly pointless. Permadeath.
12. A procedural system is inherently unfair, because you cannot guarantee difficulty, proper placement, unit composition, etc without removing the procedural systems outright. Embrace the unfairness, and design around it. Permadeath.
13. A procedural system is inherently unfair; you need to give some guaranteed items with guaranteed results to navigate an uncertain world (e.g. escape tools).
14. It's always good to enable some control by the player over the procedural generation. Choice of environments, chances at additional loot, etc. You want the player to engage with procgen rules, because they're interesting (and if they're not, what are you doing?), and the easiest way is to give them tools to manipulate it to their advantage. Also permadeath.
You can work around 12. with constraint satisfaction solvers.
After generating the level structure, emplace an entrance and exit such that they are maximally apart. And emplace a strong monster in one of the rooms around the exit. And emplace a health potion in one room before the strong monster. And emplace treasure in the exit room.
That sort of stuff and be specialized and coded towards your particular games structures fairly easily.
Scale is much harder though. In something like NMS it’d be intensive doing such a constraint solver on the whole galaxy. Might work in chunks or regions though.
The problem is setups like in slay the spire, if we imagine there was no path selection, you can run into scenarios that are functionally unwinnable (I recall someone finding a seed in StS that was literally unwinnable, but ignoring that). Eg Act1 elites that counter your build, but too early for you to consistently have secondary options/workarounds.
Obviously people have high consistent wins in StS1/2 so it’s not like they’re guaranteed unwinnable, but for the average human (mostly just boils down to “path to do act1 elites until after you probably have a counter to all of them)
Your options are:
1. All scenarios are easy enough that you can get away with just about anything sensible
2. You don’t have any counter mechanics, which is basically the same as 1, and is difficult to do anything interesting with
3. You announce up-front what you’re going to deal with, so the onus is now on the player to find a solution in the meantime
4. You give the player a NOPE utility
In a roguelike, I think 4 is the better design, but 3 is also reasonable. 1 & 2 just makes things less interesting.
I’m not sure you have to squint; an mview is just a cached query. The only difference between an mview and a cache is the “incremental” part of the equation.
That is, my only disagreement is the lack of gusto
And it's correct detail. Earlier models had things like detached hats, or lighthouses floating above or below the level they should be at, etc. Everything is where it should be, the fish is clearly in the basket and not clipping through it, the animation has cartoon-correct parallax which is fairly impressive. By "cartoon-correct" I mean that if that was a real photo with real physics, that would be way too much motion for the boat and clouds, but for a cartoon, that is completely acceptable.
I feel like the baseline teaching is trivially necessary, and should simply be assumed in these kinds of assessments. Getting someone to invest themselves emotionally into a subject is both infinitely harder and valuable, and should always be the focal point.
That is, the true “origin” isn’t worth much — the origin of interest is whatever kicked off the feedback loop of joy —> learning —> joy (in my own case, it was learning djikstra’s algorithm)
Per the article, the replicas are configured the same. MIXED is just dynamically selecting the format automatically, and it seems to be the case that AUTOINCREMENT has fundamentally broken semantics under replication.
And instead of erroring out when replicating, it instead chooses ROW format and plays a game of complete nonsense.
The user error is in not sufficiently reading the docs, but it seems to me MySQL is going out of its way to wrap the noose
No, the binlog format is actually irrelevant here; the post author was just incorrect about that part. ALTER TABLE always gets replicated as a statement, regardless of the binlog format.
Auto_increment only has broken replication semantics in the very specific situation the author encountered: a table has data, but no primary key (and also no alternative unique index which could serve as the clustered index key) and then an attempt is made to alter the table to add an auto_increment primary key.
Basically that form of ALTER TABLE statement is telling the database to add a sequential ID to each existing row, but without providing any deterministic way that those numbers should be assigned. So each replica may choose a different numbering, causing the problem experienced by the author.
It's a foot gun, but not a common one in production at any real scale where you'd have a replica in the first place. InnoDB tables really should always have primary keys, and there are various ways to ensure that happens (sql_require_primary_key option, generated invisible primary key option, external linters, etc).
Author here. I didn't make it clear in the article but the table in question did have a primary key, which was migrated to an unique key, and then the new auto-incremental primary key was added.
As I mentioned in the article, the old ID field was then used in the update statement for the other tables.
I'm updating the article now to make that part clear.
Interesting, I wouldn't have expected that auto_increment replication problem to occur for InnoDB tables that have a clustered index key (the old PK converted to UNIQUE), that is indeed surprising. Maybe the SQL layer does something dumb here and thinks the table doesn't have a defined ordering because it has no explicit PK -- even though internally in the storage engine it would still have the table organized by the old PK if it was a UNIQUE KEY over non-NULLable columns.
But even that aside, I still say the binlog_format is irrelevant and the core problem here is 100% the ALTER to add the auto_increment: it resulted in different IDs on the replica than on the primary. That's a problem if you refer to IDs externally anywhere, regardless of whether it's 6 child tables or an external cache or logging etc. As soon as you promote a replica for any reason (not just an upgrade, any failover reason whatsoever) this would be a massive problem as all the IDs would now refer to different rows. And even before a failover event, if you do any reads from the replica for any purpose (read scaling, backups, OLAP queries), the data is going to be wrong.
Essentially for the 6 child tables, it wouldn't have mattered if their UPDATEs had all used ROW or all used STATEMENT; either way you would have still had a fundamental data inconsistency between primary and replica here for the parent table. If these 6 tables' UPDATEs all used ROW, they would refer to the IDs from the primary which are locally "wrong" on the replica. Or if they all used STATEMENT then the data on the replica would be consistent locally but completely different than what's on the primary.
yeah, now that you mention it i remember a similar issue for us on postgres where we transitioned tables without primary key to being primary key serial.
It’s hard to imagine someone creating these for PRs? Unless it was auto-generated, but even then it’d only be relevant for fairly large/novel PRs — and then it’s functionally the same as documentation use case.
You’d also probably need some mechanism to visually diff the flowcharts to participate in discussions/changes
The thing I’ve never understood is why SQL itself is not the target of attack. There’s already an inherent language abstraction with the planner; Postgres in theory could be the JVM with any number of languages implemented on top. Including a language that lends itself to composition and auto generation of PL functions.
ORMs are fundamentally difficult because of the mapping problem, but SQL code builders should be trivial. Auto-generating and exposing every DB functionality as a type-safe $LANG function should be trivial. Instead, they’re also accidentally difficult because building SQL is difficult.
Outside of SQL, you’ve got datalog… and that’s about it. And I guess whatever horrors the NoSQL crowd keeps coming up with
agreed! I feel like basic ocaml syntax would map very well to a higher level SQL - `let` to define reusable subexpressions, `let ... in` to define inline pieces of a large query, partial application to fill in variable values, and a final function call to execute the query.
Learning SQL doesn’t absolve you from the fact that, from the perspective of your PL, you’re smashing arbitrary strings together like a Neanderthal, and you can be offered all the support otherwise given to your string smashing problems (exactly none)
It also doesn’t absolve the fact that SQL is not a particularly well-designed language for smashing strings together like a Neanderthal. In fact, you might even say it’s absolutely horrid at it, with random keywords, extraneous syntax, and general lack of compositional capabilities.
The relational model is fantastic — Codd is Godd, after all. The engines are a work of art. The SQL language is a shitshow. PL/SQL and all its variants are a crime upon the PL community. The programmatic interface to a database is a shitshow, because it is SQL and only SQL. The SQL standard is a joke and standardizes nothing.
None of this is contentious, or should be, once you’ve learned SQL.
PL/SQL is cursed and the unstandardized library system means every DB’s ecosystem is anemic.
Instead of smashing strings, you can code with all the affordances of C90 and still get the chance to smash strings together if you need to do anything beyond utilizing simple variables (EXECUTE) — now with an even worse string manipulation stdlib. And you also get the privilege of working with the some of the most worthless parser errors known to modern man. As an added bonus, DB IDEs are universally worse at text-editing & refactoring than the equivalent application editor
You can reuse code through extensions/external instead, and have access to real programming languages with actual libraries… but now you’re kicked out of managed environments because it’s not whitelisted, and even if you do run it, you’re back to smashing strings together like a Neanderthal trying to communicate to your DB.
Sprocs/functions are useful because they do useful engine things — they run locally with the data, they have an easier time playing with transaction flow, some logic is much easier to express with a cursor instead of set logic and you get to avoid most of the penalties you’d have otherwise.
They do absolutely nothing to make SQL a less terrible interface to your database, except by stuffing it under a rug (CALL).
Stored procedures have the wrong versioning model. If they were version-locked to the application code, instead of to the database schema, they'd be less of a pain and people might be more willing to use them.
Version differences in distributed systems (including Web apps) are a real pain! In many circumstances they're unavoidable, and we've developed various techniques to make them marginally easier, but if you can avoid the issue entirely by just not having the thing be distributed, that's the more maintainable choice.
Oracle has a feature called 'editions' that does this. Different DB sessions can have different versions of redefinable objects like stored procs and packages.
1. Any imaginary world needs to be internally consistent to be interesting. Procgen does not void this rule; randomization needs to be gated to that internal consistency.
2. A world can be composed of multiple environments, and each environment can a different definition of internal consistency. Dungeon Crawl Stone Soup does this very openly and to fairly good effect, but pretty much any decent procgen game will inevitably end up with the same choice, because it's what enables you to play with various procgen systems/rules while still having a coherent overall game.
3. Generalizing 2, procgen systems generally do not start from nothing; You embed reasonably well-defined bases (races, weapon forms, etc) and then apply randomization on top of those bases to form the final element. Meaningful fundamental variation in those bases leads to guaranteed meaningful variation in the end result.
4. Games are fundamentally interactive systems. Generating a thousand variations without actually changing interaction is equivalent to generating exactly one variation. A blue orc vs a red orc is a not real variant, unless you make it so by attaching effects / statuses / capabilities. The color distinction is then a signal into the more abstracted changes that have occurred. This is where NMS falls over -- the images have changed, but the interaction has not.
5. A corollary to point 4, changing numbers without changing interaction is equivalent to doing nothing. If a meaningful damage threshold is functionally every 10 points of damage, then generating numbers 0-9, 11-19, etc is equivalent to generating just the numbers 0 and 10. This is where borderlands falls over -- a million variations in guns, but the vast majority of those variations are just numeric differences that don't change anything in practice.
6. If the procgen applies to equipment/builds, you pretty much need permadeath as a driver for exploring a procgen game and its systems, because you need players to explore for a local optima. Exploring the space for a global optima in a procgen game inevitably devolves into a gambling simulator, because players optimize out the fun, and the optimal play is to the kill unit XYZ repeatedly for the 0.5% drop. Games like Terraria fall into this trap (though Terraria resolves it mostly by making unit XYZ be trivially summon-able bosses)
7. Procgen can never guarantee interesting results; You inevitably need a system of vaults/artifacts -- hardcoded quality items -- to guarantee periodic interesting locations/items. Ideally more notable than average generation, but less than strictly optimal so as to not overpower the other systems -- an issue I've had with games like Diablo 3, where finding an item-set was vastly more powerful than anything else that I acquired for the next 5+ hours, eliminating the entire looting system (and even character skill system, since it forced my build too)
8. Decent Procgen is inevitably rule-based, with possibly multiple chained-rules each doing their own rolls, to ensure coherency. Ideally the rule-chain is roughly discoverable from the end results (by virtue of following internally consistent and apparent rules) -- an end-result that cannot be explained is indistinguishable from pure-random, and pure-random in incoherent. See any Tabletop RPG generation tables.
9. Hell, just start any procgen design from tabletop RPG tables.
10. The number of variations that can be produced is meaningless. This is only for marketing.
11. A procedural system that generates exactly once is fairly pointless. Permadeath.
12. A procedural system is inherently unfair, because you cannot guarantee difficulty, proper placement, unit composition, etc without removing the procedural systems outright. Embrace the unfairness, and design around it. Permadeath.
13. A procedural system is inherently unfair; you need to give some guaranteed items with guaranteed results to navigate an uncertain world (e.g. escape tools).
14. It's always good to enable some control by the player over the procedural generation. Choice of environments, chances at additional loot, etc. You want the player to engage with procgen rules, because they're interesting (and if they're not, what are you doing?), and the easiest way is to give them tools to manipulate it to their advantage. Also permadeath.
reply