25 ms·
Postgres 11 Beta 1 released
- justinclift 8y agoProbably useful: https://wiki.postgresql.org/wiki/HowToBetaTest https://wiki.postgresql.org/wiki/HowToBetaTest
- merb 8y ago> We have heard your frustrations and have now added the ability to quit the command-line using the keywords quit and exit and hope that quitting a PostgreSQL session is now as enjoyable as using PostgreSQL. o.O I always tought the right way is CTRL+D.
- mey 8y agoAlso \q
- sin7 8y agoThis is how you quit.
- shrumm 8y agoCTRL + D is news to me, i’m a \q guy too.
- stingraycharles 8y agoCtrl+D is more generic, as it sends the signal to close the pipe (i.e. stdin from the perspective of psql).
- JimDabell 8y agoIt's a standard quit command for shells in UNIX – e.g. if you hit ^D in a Bourne shell it will exit as well.
- derefr 8y agoIt's not really a "command", though. Unlike, say, Ctrl+C, which becomes a SIGINT signal that the program receives and gets to decide how to handle, Ctrl+D never reaches the program as a separate recognizable thing that you can write a handler for. Instead, Ctrl+D is the way to ask the TTY driver to pass the program an EOF instead of input in response to its next read(2) call to its stdin. It's the same signal that the program would receive if you had been piping input into the program from a file, and that file reached the end. Thus, any CLI program built to handle being piped into, will "automatically" support Ctrl+D as a way to quit out of it.
- paulryanrogers 8y agoThis also works in Mysql.
- zitterbewegung 8y agoThey should have added !wq as a way to exit also.
- dean177 8y agoWhat else uses that?
- zitterbewegung 8y agoVi / vim.
- kwk1 8y agoYou probably meant `:wq!`.
- nickserv 8y agoOr !x for maximum efficiency ;-)
- wiz21c 8y agoC-x C-c is much better !
- kqr 8y agoNot too fr off if you, like me, prefer writing your SQL in sql-mode and have a comint buffer with psql you send your queries to!
- brian_herman 8y ago:D
- kelvich 8y agoSurprisingly highest voted question on stackoverflow about postgres is exactly "how to quit?" https://stackoverflow.com/questions/tagged/postgresql?sort=votes&pageSize=15 https://stackoverflow.com/questions/tagged/postgresql?sort=v...
- hinkley 8y agoEvery year I get to teach a handful of people about CTRL-Z. (CTRL-U is my favorite teletype command). We hardly ever get to CTRL-D.
- kqr 8y agoYou do know that doesn't actually terminate anything, right?
- hinkley 8y agoWhat, CTRL-Z? That’s the point. A couple minutes into a ten minute task someone goes whups I should have backgrounded that and they CTRL-C, up arrow, & and enter, and I say something like, “dude... we need to talk about CTRL-Z bg”
- noncoml 8y agoHope Mariadb gets a page out of their book and stops Ctrl-C from exiting their command-line tool.
- Shelnutt2 8y agoI can not wait for this to happen! Can be frustrating when you accidentally exit the client. A few months ago someone took a stab at it but its not been merged yet. https://github.com/MariaDB/server/pull/703 https://github.com/MariaDB/server/pull/703 https://lists.launchpad.net/maria-developers/msg11217.html https://lists.launchpad.net/maria-developers/msg11217.html https://jira.mariadb.org/browse/MDEV-14448 https://jira.mariadb.org/browse/MDEV-14448
- jeltz 8y agoI would argue CTRL+D is the right way since it works in almost all shells, and I am surprised not more people know it.
- majewsky 8y agoCtrl+D has a learning curve because its default meaning (i.e. when you're not messing with termios) is quite surprising. From `man tcsetattr`: > VEOF (004, EOT, Ctrl-D): End-of-file character (EOF). More precisely: this character causes the pending tty buffer to be sent to the waiting user program without waiting for end-of-line. If it is the first character of the line, the read(2) in the user program returns 0, which signifies end-of-file. Recognized when ICANON is set, and then not passed as input. In other words, most programs that exit upon Ctrl+D never see the Ctrl+D, they see an empty result from read() on stdin and interpret that as end-of-file. However, that only works when the tty buffer is empty. Try this: On a Unix shell, run `cat`, type something without pressing Enter, then press Ctrl-D instead.
- pwaivers 8y ago> "PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure." Did PostgreSQL not have stored procs before?
- gmfawcett 8y agoYes it did. The "embedded transactions" is the new feature.
- j16sdiz 8y agoNo, it didn't. It had functions, not procedure. You can emulate (most of) procedure using function that return void. See also https://blog.2ndquadrant.com/postgresql-11-server-side-procedures-part-1/ https://blog.2ndquadrant.com/postgresql-11-server-side-proce...
- riku_iki 8y ago> most of Curious, what are those procedures which can't be emulated as functions. Maybe you have a chance to describe some example?
- cryptonector 8y agoThere is only one thing that procedures can do that functions can't: nest transactions inside the transaction that calls it.
- gmfawcett 8y agoYes, that's true, although slightly pedantic. I'm sure there are procedural cases that PG functions can't emulate, although I don't think I've ever encountered one in practice. Having said that, the addition of transaction support is a great step forward.
- cryptonector 8y ago
- sehrope 8y ago> PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure. Procedures can be created using the CREATE PROCEDURE command and executed using the CALL command. I'm pretty sure this is going to be my favorite feature in v11 as it will facilitate conditional DDL for the few remaining things at aren't already allowed in transactions, in particular add values to enums. Prior to v11 you could create new enums in a transaction but could not add values to an existing one. IIUC how this v11 feature works, you could now have a stored procedure check the current value list of the enum and decide to issue an ALTER TYPE ... ADD VALUE ... at runtime, optionally discarding any concurrency error. All of that could run from a SQL command, no external program needed. > Prior to PostgreSQL 11, one such feature was using the ALTER TABLE .. ADD COLUMN command where the newly created column had a DEFAULT value that was not NULL. Prior to PostgreSQL 11, when executing aforementioned statement, PostgreSQL would rewrite the whole table, which on larger tables in active systems could cause a cascade of problems. PostgreSQL 11 removes the need to rewrite the table in most cases, and as such running ALTER TABLE .. ADD COLUMN .. DEFAULT .. will execute extremely quickly. ... and this is a close second favorite. Having NOT NULL constraints on columns makes schemas much more pleasant to work with, but for schema migrations that means requiring DEFAULT clause to populate existing data. While there are ways to slowly migrate to a NOT NULL / DEFAULT config for a new column (e.g. add column without constraint, migrate data piecemeal, likely in batches of N records, then enable constraint), having it for free in core without rewriting the table at all is simply awesome.
- petereisentraut 8y agoFeature author here. Running not-allowed-in-transaction-block DDL, such as VACUUM, still won't work in stored procedures. Room for future improvement.
- cryptonector 8y agoI use DO for this sort of thing.
- 8y ago
- jakobegger 8y agoIf you want to try it without running an installer on macOS, you can try the latest build of Postgres.app: https://github.com/PostgresApp/PostgresApp/releases/tag/v2.2beta1 https://github.com/PostgresApp/PostgresApp/releases/tag/v2.2... (this build includes all the latest supported versions of PostgreSQL and the Beta of 11) It doesn’t include the JIT stuff yet, because that needs a lot of extra things (it needs the LLVM runtime library). I still need to look into how to best package that, and I’m not sure all the extra size is worth it. I’m excited to hear all about it next week at pgcon!
- beagle3 8y agoThis is super cool. Anything like that for Linux and/or Windows?
- openasocket 8y agoWoah, the JIT for queries sounds really interesting! I'm curious what that will do for performance, especially for linear scans of a large number of rows, which I imagine is where you would see the most improvement. I wonder if there are any other databases that do this, and what the advantages/disadvantages are? I'm fairly certain AWS Redshift does this also.
- jakobegger 8y agoI’d assume that most queries are limited by memory / disk access time, so for many queries the savings will be marginal. But I imagine that it could make complex aggregates a lot faster?
- openasocket 8y agoTrue, I guess you'd see the advantage most when there is a complex WHERE clause or some complex projections and aggregates. Also, looking here https://www.pgcon.org/2017/schedule/attachments/467_PGCon%202017-05-26%2015-00%20ISPRAS%20Dynamic%20Compilation%20of%20SQL%20Queries%20in%20PostgreSQL%20Using%20LLVM%20JIT.pdf https://www.pgcon.org/2017/schedule/attachments/467_PGCon%20... it seems they can JIT the process for creating an index, which has some modest improvements. I imagine there are plenty of things like that that could also benefit from compilation.
- anarazel 8y ago> t seems they can JIT the process for creating an index, which has some modest improvements We could, but currently don't yet. Not enough time / too short days...
- anarazel 8y ago> I’d assume that most queries are limited by memory / disk access time That's not true for all that many analytics statements actually. Good storage systems can deliver data quite fast, and in many cases the buffer cache can hide a lot of the access latency too. There's obviously issues with memory access latencies, but that's actually something that JIT compilation can hide with, because it increases the amount of work that can be done in the out-of-order window. Without JIT there's just too many instructions for that to hide all that memory access latencies.
- eb0la 8y ago>> UPDATE statements issued to a partition key now move affected rows to the appropriate partitions I wonder who's the author of this feature. I must buy him/her a beber/coffe/whatever
- petereisentraut 8y agoThe release notes say Amit Khandekar. (I don't know him.)
- jasonhansel 8y agoSuper excited about innovation in RDBMSs. ACID and relational models are (in my opinion) highly underrated, but need more modern syntax & flexibility. What I really want is Prolog-like, deductive features in a traditional RDBMS, but that may have to wait.
- gravypod 8y agoIt's very exciting to see table partitioning native support. I can't wait until foreign keys point to a partitioned table are supported with cascading. This single feature makes many database designs unprecedentedly salable especially for situations that are based on time (ie sales this quarter are all we care about and must be fast).