6 ms·

Left out the fact that Schema-changing operations are transactional (big win over MySQL). [Edit: In migrations, specifically. Also! No mention of the MySQL dua

Left out the fact that Schema-changing operations are transactional (big win over MySQL). [Edit: In migrations, specifically. Also! No mention of the MySQL dual-license JDBC driver malarky... Not that it's a success of Postgres' as much as it's a failure of MySQL's... ] I'd never heard of Window Functions (not an Oracle user, and been on MySQL for a few years... left Postgres for replication and haven't made it back quite yet).. but I desperately want them. The idea of effectively aggregating over groups in a group by (which is how I understand these Window doohickeys) is something I come up on a few times a year and get really frustrated by. I think the only killer feature I'm left wanting from some RDBMS at this point is managed-sequences... The idea that I have a related list of things that I want to keep a sequence column for, and would like the database to understand the concept and help me maintain it.

empthought 15y ago
What's the trouble with PostgreSQL's sequence support? (http://www.postgresql.org/docs/9.1/interactive/sql-createsequence.html http://www.postgresql.org/docs/9.1/interactive/sql-createseq...)
- famousactress 15y ago
  You got me really excited, but I don't think this is what I want. Imagine a table "Chapter" with a Foreign Key "Book". Not uncommon would be to have a 'sequence' or 'rank' column in the Chapter table defining it's order within a book. Sequences shouldn't overlap (accomplishable with a unique constraint on sequence and book_id), but they also shouldn't have gaps, and it'd be nice if inserting a Chapter at a sequence had predictable behavior (like.. it incremented all chapters in that book with sequence >= it's). I find it a pretty typical and profound source of frustration, and since the whole idea of RDBMS is to model relationships.. and relationships so often have integral sequence/rank.. it's always seemed to me like something that ought to've be considered 'in scope' during the design of SQL.
  - empthought 15y ago
    OK, that's definitely a matter for a custom trigger on the table. Another option would be to keep the chapter order in an array column on the Book table, since arguably the arrangement of chapters belongs to the book rather than the chapter. PostgreSQL array documentation is here: http://www.postgresql.org/docs/9.1/static/arrays.html http://www.postgresql.org/docs/9.1/static/arrays.html
    - famousactress 15y ago
      arguably the arrangement of chapters belongs to the book rather than the chapter That's a really excellent point, and array columns look like a really cool solution! I suppose the only bummer is that it introduces a way to get things out of sync (I could delete a Chapter row, but forget to remove it from the array in Book), but in general this is a much better approach than the status-quo solution.
      - empthought 15y ago
        I think you should be able to write a delete rule on Chapter that will ensure that no array in Book contains that chapter, whether through cascading the delete or through raising an error.
    - ams6110 15y ago
      If you're ever thinking of writing a trigger for any purpose, think again. And again. They are used improperly far more often than not. Things should not happen as "magic" side effects in a database, which is what triggers do. Generally better to be explicit with code in a function or stored procedure.
      - empthought 15y ago
        In general I disagree, but in this specific case you are right that a procedural interface to manipulating books/chapters is preferable. Essentially if your API is single-table DML, then triggers are preferable. If multiple tables need to be manipulated at the same time to complete the full operation, then there should be a procedural API and triggers are likely unnecessary.
tzs 15y ago
> I think the only killer feature I'm left wanting from some RDBMS at this point is managed-sequences... I would like some RDBMS to give sharable transactions. What I mean by that is something like this. 1. When I begin a transaction, I can assign it a name. 2. If multiple connections to the database are in transactions with the same name, they can see the changes made by the others, as if none of them were in a transaction. Connections not party to the shared transaction do not see the changes until all of the connections in the shared transaction commit. 3. The connections in the shared transaction can do unnamed transactions, which isolate them from the others within the shared transaction. The idea here is that sometimes I have some operation that I would like to do atomically, but that involves multiple processes acting on the database. Process A wants to make some changes, then pass responsibility on to process B (which may be on a separate machine) to make more changes, and then process C finishes up, and if any of them encounter an error I want to rollback to the state before A started.
- kanak 15y ago
  At an initial glance, I think you're describing nested transactions.
- famousactress 15y ago
  Interesting. Without knowing what kind of system you're talking about I'm suspicious that I'd use this. I certainly have distributed problems like this where a logical concept is broken into a number of steps, and while I want to distribute the steps.. the effects of them to the entire system might not be realized until all of them are complete. I generally wouldn't defer that responsibility to the database though. I'd more often handle this as multiple database transactions that advance a state machine, at the end of which consequence-inducing-events are fired off. The idea that process A tickles the database into some state expected to be understood (and only seen by) process B seems like too flimsy a contract to me.
  - _sh 15y ago
    I get your point, but there is value in delegating to the database--how do I roll back an external state machine?
davyjones 15y ago
> I'd never heard of Window Functions Go ahead and give it a shot. Once you get used to it, you cannot live without it. Another killer feature in my opinion is the recursive query: http://www.postgresql.org/docs/current/static/queries-with.html http://www.postgresql.org/docs/current/static/queries-with.h...
- jaggederest 15y ago
  And, to go along with the recursive queries, the GEQO - genetic query optimizer, for when your queries are NP-complete to even optimize.
- jiggy2011 15y ago
  I wish I had know of these sooner, with MySQL I was jumping through all kinds of hoops to organize hierarchical data in my DB without generating insane amounts of queries.
vyrotek 15y ago
I think the only killer feature I'm left wanting from some RDBMS at this point is managed-sequences... Excuse me if I didn't completely understand what you wanted but it seemed similar to SQL Server's sequence: http://msdn.microsoft.com/en-us/library/ff878091.aspx http://msdn.microsoft.com/en-us/library/ff878091.aspx
- famousactress 15y ago
  Thanks, but nope.. See my comment to @empthought below, who pointed at the same feature in Postgres. I was sloppy by using the term 'sequence' which already has some meaning in RDBMS apparently.

empthought 15y ago

What's the trouble with PostgreSQL's sequence support? (http://www.postgresql.org/docs/9.1/interactive/sql-createsequence.html http://www.postgresql.org/docs/9.1/interactive/sql-createseq...)

famousactress 15y ago
You got me really excited, but I don't think this is what I want. Imagine a table "Chapter" with a Foreign Key "Book". Not uncommon would be to have a 'sequence' or 'rank' column in the Chapter table defining it's order within a book. Sequences shouldn't overlap (accomplishable with a unique constraint on sequence and book_id), but they also shouldn't have gaps, and it'd be nice if inserting a Chapter at a sequence had predictable behavior (like.. it incremented all chapters in that book with sequence >= it's). I find it a pretty typical and profound source of frustration, and since the whole idea of RDBMS is to model relationships.. and relationships so often have integral sequence/rank.. it's always seemed to me like something that ought to've be considered 'in scope' during the design of SQL.
- empthought 15y ago
  OK, that's definitely a matter for a custom trigger on the table. Another option would be to keep the chapter order in an array column on the Book table, since arguably the arrangement of chapters belongs to the book rather than the chapter. PostgreSQL array documentation is here: http://www.postgresql.org/docs/9.1/static/arrays.html http://www.postgresql.org/docs/9.1/static/arrays.html
  - famousactress 15y ago
    arguably the arrangement of chapters belongs to the book rather than the chapter That's a really excellent point, and array columns look like a really cool solution! I suppose the only bummer is that it introduces a way to get things out of sync (I could delete a Chapter row, but forget to remove it from the array in Book), but in general this is a much better approach than the status-quo solution.
    - empthought 15y ago
      I think you should be able to write a delete rule on Chapter that will ensure that no array in Book contains that chapter, whether through cascading the delete or through raising an error.
  - ams6110 15y ago
    If you're ever thinking of writing a trigger for any purpose, think again. And again. They are used improperly far more often than not. Things should not happen as "magic" side effects in a database, which is what triggers do. Generally better to be explicit with code in a function or stored procedure.
    - empthought 15y ago
      In general I disagree, but in this specific case you are right that a procedural interface to manipulating books/chapters is preferable. Essentially if your API is single-table DML, then triggers are preferable. If multiple tables need to be manipulated at the same time to complete the full operation, then there should be a procedural API and triggers are likely unnecessary.

tzs 15y ago

> I think the only killer feature I'm left wanting from some RDBMS at this point is managed-sequences... I would like some RDBMS to give sharable transactions. What I mean by that is something like this. 1. When I begin a transaction, I can assign it a name. 2. If multiple connections to the database are in transactions with the same name, they can see the changes made by the others, as if none of them were in a transaction. Connections not party to the shared transaction do not see the changes until all of the connections in the shared transaction commit. 3. The connections in the shared transaction can do unnamed transactions, which isolate them from the others within the shared transaction. The idea here is that sometimes I have some operation that I would like to do atomically, but that involves multiple processes acting on the database. Process A wants to make some changes, then pass responsibility on to process B (which may be on a separate machine) to make more changes, and then process C finishes up, and if any of them encounter an error I want to rollback to the state before A started.

kanak 15y ago
At an initial glance, I think you're describing nested transactions.
famousactress 15y ago
Interesting. Without knowing what kind of system you're talking about I'm suspicious that I'd use this. I certainly have distributed problems like this where a logical concept is broken into a number of steps, and while I want to distribute the steps.. the effects of them to the entire system might not be realized until all of them are complete. I generally wouldn't defer that responsibility to the database though. I'd more often handle this as multiple database transactions that advance a state machine, at the end of which consequence-inducing-events are fired off. The idea that process A tickles the database into some state expected to be understood (and only seen by) process B seems like too flimsy a contract to me.
- _sh 15y ago
  I get your point, but there is value in delegating to the database--how do I roll back an external state machine?

davyjones 15y ago

> I'd never heard of Window Functions Go ahead and give it a shot. Once you get used to it, you cannot live without it. Another killer feature in my opinion is the recursive query: http://www.postgresql.org/docs/current/static/queries-with.html http://www.postgresql.org/docs/current/static/queries-with.h...

jaggederest 15y ago
And, to go along with the recursive queries, the GEQO - genetic query optimizer, for when your queries are NP-complete to even optimize.
jiggy2011 15y ago
I wish I had know of these sooner, with MySQL I was jumping through all kinds of hoops to organize hierarchical data in my DB without generating insane amounts of queries.

vyrotek 15y ago

I think the only killer feature I'm left wanting from some RDBMS at this point is managed-sequences... Excuse me if I didn't completely understand what you wanted but it seemed similar to SQL Server's sequence: http://msdn.microsoft.com/en-us/library/ff878091.aspx http://msdn.microsoft.com/en-us/library/ff878091.aspx

famousactress 15y ago
Thanks, but nope.. See my comment to @empthought below, who pointed at the same feature in Postgres. I was sloppy by using the term 'sequence' which already has some meaning in RDBMS apparently.