Cosyan – Transactional RDBMS with multi-table constraint logic | Hacker News Reader

Cosyan – Transactional RDBMS with multi-table constraint logic

github.com

44 pointsgsvigruha14 comments

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.

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-sacr...

[1] http://www.liberidu.com/blog/images/rj599.pdf

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).

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.

Thanks! I will check Datalog out, i have never used it (though i know Prolog a bit).

So the main difference is then that my version is SQL based: alter table transactions add constraint c_amount check (amount > 10000 -> auth.name = user.man.name) # or something similar, where you refer to other tables via a chain of foreign keys.

Another extra feature is that you can use aggregators in constraints in case of a one to many relationship: https://github.com/gsvigruha/cosyan/blob/master/src/main/res...

I'm not sure why you need to restrict constraints between tables to be based on foreign keys. I guess maybe that is a limitation inherent to SQL?

We also allow constraints over aggregations. Though our syntax does require giving the result of the aggregation a temporary "table" name so that it can be used as part of the constraint.

I dont need to, thats a design choice i made for the moment for various reasons, one being to stay close to SQL concepts.

It seems like your DB is basically a Datalog version of what im trying to do in SQL.

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

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/res... https://github.com/gsvigruha/cosyan/blob/master/src/main/res...

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

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.

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...

[2] https://stackoverflow.com/questions/27191677/how-to-maintain...

[3] https://www.postgresql.org/message-id/m3iq57gcn9.fsf@passepa...

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?

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.

Reply on news.ycombinator.com