6 ms·

Cosyan – Transactional RDBMS with multi-table constraint logic

44 points by gsvigruha 8y ago

paulryanrogers 8y ago
Interesting concept. Triggers can get close to the same effect in a traditional DB.
- gsvigruha 8y ago
  Yes they are similar but they are procedural, i'm trying to do a declarative thing. I want to eliminate the need to figure out what constraint can a specific insert/delete on a specific table can break exactly.
- snaky 8y ago
  That's a shame we have triggers as the only tool for this job, because e.g. CHECK constraint "cannot contain subqueries nor refer to variables other than columns of the current row"[1]. Triggers are very low-level thing actually, I'd consider them an assembler of RDBMS. So the questions like "Do the triggers and constraints outlined below actually cover all the bases, or is it still possible to add/modify data in such a way that the result would be inconsistent?"[2] are raised too often (usually they are not raised at all, and that's even worse). And the obvious answer[3] is application-level consistency checking - which is exactly me personally consider a shame for current state of RDBMS, especially in the light of Codd' ideas (referenced above), considering the more than half of century history of RDBMS industry. [1] https://www.postgresql.org/docs/devel/static/sql-createtable.html https://www.postgresql.org/docs/devel/static/sql-createtable... [2] https://stackoverflow.com/questions/27191677/how-to-maintain-cross-table-consistency-with-triggers-in-postgresql https://stackoverflow.com/questions/27191677/how-to-maintain... [3] https://www.postgresql.org/message-id/m3iq57gcn9.fsf@passepartout.tim-landscheidt.de https://www.postgresql.org/message-id/m3iq57gcn9.fsf@passepa...
alecbenzer 8y ago
It's not clear to me why this would get you faster development cycles or better performance than application-level checks? And I'm not really sure what "dependency tracking" is supposed to mean?
- gsvigruha 8y ago
  Yes you're totally right i need to write the readme properly. You get faster development cycle by skipping the app layer altogether. It's a few sql statements vs java code/release/deploy. Performance: you don't need multiple queries for a constraint plus you can optimize from info within the DB. Dependency tracking: no need to figure out yourself which table/operation can break which constraint.
danharaj 8y ago
I've wanted multi table foreign keys and join indices for Postgres. What exactly is the design here?
- gsvigruha 8y ago
  Probably the easiest if i link some of the doc to show some examples of usage: https://github.com/gsvigruha/cosyan/blob/master/src/main/resources/doc/rules/31_foreign_keys.md https://github.com/gsvigruha/cosyan/blob/master/src/main/res... https://github.com/gsvigruha/cosyan/blob/master/src/main/resources/doc/rules/32_reverse_foreign_keys.md https://github.com/gsvigruha/cosyan/blob/master/src/main/res...
trurl 8y ago
It isn't really clear to me how this differs from usual database integrity constraints? However, I'm not familiar with what typical SQL databases provide. The database I work on has had sophisticated integrity declarative integrity constraints for a decade now, so it seems surprising that this would be considered something new.
- snaky 8y ago
  Integrity (consistency) was the biggest point of relational database theory itself. > The true importance of relational theory is highlighted by the title of the original (and considerably shorter) version of Codd’s first paper. That version predated the published version by a year, and the title was “Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks.”[1] The title of this unpublished version emphasizes that the real importance of relational theory is that it provides a rigorous method of asserting arbitrarily complex consistency constraints that must be satisfied by the data within the database. https://iggyfernandez.wordpress.com/2013/04/24/whats-so-sacred-about-relational-anyway/3/ https://iggyfernandez.wordpress.com/2013/04/24/whats-so-sacr... [1] http://www.liberidu.com/blog/images/rj599.pdf http://www.liberidu.com/blog/images/rj599.pdf
- gsvigruha 8y ago
  Interesting, which DB is that? The ones i checked only have constraints on one table at a time (besides foreign keys). The idea here is that constraints could span across multiple tables via chains of foreign keys. It might not be a new idea but i haven't seen any implementations yet (unless you count triggers).
  - trurl 8y ago
    We've had that for at least a decade in the LogicBlox database (which is based upon Datalog and not SQL). So you could say write transfer_table(id, user, src, dest, amnt), amnt > 10000.0 -> auth_table(id, man), managed_by_table(user, man). If you wanted to express that for a transfer involving more than 10,000 dollars that it must have been approved by the user's manager. Basically any logical formula can be used as a constraint. It's also possible to express basic temporal constraints like +user_pass_table(user, pswd1), user_pass_table@prev(user, pswd2) -> pswd1 != pswd2. So that says if the user's password has changed, and the user had a password in the previous transaction, they cannot be the same password.

44 points by gsvigruha 8y ago

paulryanrogers 8y ago

Interesting concept. Triggers can get close to the same effect in a traditional DB.

gsvigruha 8y ago
Yes they are similar but they are procedural, i'm trying to do a declarative thing. I want to eliminate the need to figure out what constraint can a specific insert/delete on a specific table can break exactly.
snaky 8y ago
That's a shame we have triggers as the only tool for this job, because e.g. CHECK constraint "cannot contain subqueries nor refer to variables other than columns of the current row"[1]. Triggers are very low-level thing actually, I'd consider them an assembler of RDBMS. So the questions like "Do the triggers and constraints outlined below actually cover all the bases, or is it still possible to add/modify data in such a way that the result would be inconsistent?"[2] are raised too often (usually they are not raised at all, and that's even worse). And the obvious answer[3] is application-level consistency checking - which is exactly me personally consider a shame for current state of RDBMS, especially in the light of Codd' ideas (referenced above), considering the more than half of century history of RDBMS industry. [1] https://www.postgresql.org/docs/devel/static/sql-createtable.html https://www.postgresql.org/docs/devel/static/sql-createtable... [2] https://stackoverflow.com/questions/27191677/how-to-maintain-cross-table-consistency-with-triggers-in-postgresql https://stackoverflow.com/questions/27191677/how-to-maintain... [3] https://www.postgresql.org/message-id/m3iq57gcn9.fsf@passepartout.tim-landscheidt.de https://www.postgresql.org/message-id/m3iq57gcn9.fsf@passepa...

alecbenzer 8y ago

It's not clear to me why this would get you faster development cycles or better performance than application-level checks? And I'm not really sure what "dependency tracking" is supposed to mean?

gsvigruha 8y ago
Yes you're totally right i need to write the readme properly. You get faster development cycle by skipping the app layer altogether. It's a few sql statements vs java code/release/deploy. Performance: you don't need multiple queries for a constraint plus you can optimize from info within the DB. Dependency tracking: no need to figure out yourself which table/operation can break which constraint.

danharaj 8y ago

I've wanted multi table foreign keys and join indices for Postgres. What exactly is the design here?

gsvigruha 8y ago
Probably the easiest if i link some of the doc to show some examples of usage: https://github.com/gsvigruha/cosyan/blob/master/src/main/resources/doc/rules/31_foreign_keys.md https://github.com/gsvigruha/cosyan/blob/master/src/main/res... https://github.com/gsvigruha/cosyan/blob/master/src/main/resources/doc/rules/32_reverse_foreign_keys.md https://github.com/gsvigruha/cosyan/blob/master/src/main/res...

trurl 8y ago

It isn't really clear to me how this differs from usual database integrity constraints? However, I'm not familiar with what typical SQL databases provide. The database I work on has had sophisticated integrity declarative integrity constraints for a decade now, so it seems surprising that this would be considered something new.

snaky 8y ago
Integrity (consistency) was the biggest point of relational database theory itself. > The true importance of relational theory is highlighted by the title of the original (and considerably shorter) version of Codd’s first paper. That version predated the published version by a year, and the title was “Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks.”[1] The title of this unpublished version emphasizes that the real importance of relational theory is that it provides a rigorous method of asserting arbitrarily complex consistency constraints that must be satisfied by the data within the database. https://iggyfernandez.wordpress.com/2013/04/24/whats-so-sacred-about-relational-anyway/3/ https://iggyfernandez.wordpress.com/2013/04/24/whats-so-sacr... [1] http://www.liberidu.com/blog/images/rj599.pdf http://www.liberidu.com/blog/images/rj599.pdf
gsvigruha 8y ago
Interesting, which DB is that? The ones i checked only have constraints on one table at a time (besides foreign keys). The idea here is that constraints could span across multiple tables via chains of foreign keys. It might not be a new idea but i haven't seen any implementations yet (unless you count triggers).
- trurl 8y ago
  We've had that for at least a decade in the LogicBlox database (which is based upon Datalog and not SQL). So you could say write transfer_table(id, user, src, dest, amnt), amnt > 10000.0 -> auth_table(id, man), managed_by_table(user, man). If you wanted to express that for a transfer involving more than 10,000 dollars that it must have been approved by the user's manager. Basically any logical formula can be used as a constraint. It's also possible to express basic temporal constraints like +user_pass_table(user, pswd1), user_pass_table@prev(user, pswd2) -> pswd1 != pswd2. So that says if the user's password has changed, and the user had a password in the previous transaction, they cannot be the same password.