
Postgres 11 Beta 1 released - hardwaresofton
https://www.postgresql.org/about/news/1855/
======
sehrope
> 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
Feature author here. Running not-allowed-in-transaction-block DDL, such as
VACUUM, still won't work in stored procedures. Room for future improvement.

~~~
cryptonector
I use DO for this sort of thing.

~~~
petereisentraut
You also cannot run VACUUM inside a DO block. It's the same thing underneath.

~~~
cryptonector
No, but I use it for the sort of DDL that's missing IF [NOT] EXISTS syntax.

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

~~~
zitterbewegung
They should have added !wq as a way to exit also.

~~~
wiz21c
C-x C-c is much better !

~~~
kqr
Not 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!

------
jakobegger
If 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.2...](https://github.com/PostgresApp/PostgresApp/releases/tag/v2.2beta1)

(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
This is super cool. Anything like that for Linux and/or Windows?

------
openasocket
Woah, 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
I’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
True, 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%20...](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)
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
> 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...

------
justinclift
Probably useful:

[https://wiki.postgresql.org/wiki/HowToBetaTest](https://wiki.postgresql.org/wiki/HowToBetaTest)

------
eb0la
>> 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
The release notes say Amit Khandekar. (I don't know him.)

------
pwaivers
> "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?

~~~
jrs95
I think the new part is "allow users to use embedded transactions (i.e. BEGIN,
COMMIT/ROLLBACK) within a procedure."

~~~
protomyth
That's a rather nice feature that Transact-SQL (both Sybase and Microsoft) has
had for a long time. It should make it easier for some folks to convert over.

~~~
endorphone
Just as a point of clarity on that, SQL Server only allows one encapsulating
transaction per session. You can have begins and so on within a stored
procedure, but really that is little more than a "just in case" defense (e.g.
just in case the caller didn't already do this), as you can only commit or
rollback the outer transaction.

If you want to have atomic transactions within a transaction you have to do
hacks like make a distributed call back to yourself, in that new session
creating another transaction.

It's an element of SQL Server that confuses people to this day.

~~~
protomyth
I was more on the Sybase side and dealt with an environment where the calling
program didn't initiate a transaction so it worked quite well for me. We were
kind of touchy about having a transaction open longer than it needed to be.

------
jasonhansel
Super 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
It'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).

