On 27th of March 2022, Andrew Dunstan committed patch:
This patch introduces the SQL/JSON standard constructors for JSON:
For the most part these functions provide facilities that mimic
existing json/jsonb functions. However, they also offer some useful
additional functionality. In addition to text input, the JSON() function
accepts bytea input, which it will decode and constuct a json value from.
The other functions provide useful options for handling duplicate keys
and null values.
This series of patches will be followed by a consolidated documentation
Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu,
Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby.
Continue reading Waiting for PostgreSQL 15 – SQL/JSON constructors
On 28th of March 2022, Alvaro Herrera committed patch:
Add support for MERGE SQL command
MERGE performs actions that modify rows in the target table using a
source table or query. MERGE provides a single SQL statement that can
conditionally INSERT/UPDATE/DELETE rows -- a task that would otherwise
require multiple PL statements. For example,
MERGE INTO target AS t
USING source AS s
ON t.tid = s.sid
WHEN MATCHED AND t.balance > s.delta THEN
UPDATE SET balance = t.balance - s.delta
WHEN MATCHED THEN
WHEN NOT MATCHED AND s.delta > 0 THEN
INSERT VALUES (s.sid, s.delta)
WHEN NOT MATCHED THEN
MERGE works with regular tables, partitioned tables and inheritance
hierarchies, including column and row security enforcement, as well as
support for row and statement triggers and transition tables therein.
MERGE is optimized for OLTP and is parameterizable, though also useful
for large scale ETL/ELT. MERGE is not intended to be used in preference
to existing single SQL commands for INSERT, UPDATE or DELETE since there
is some overhead. MERGE can be used from PL/pgSQL.
MERGE does not support targetting updatable views or foreign tables, and
RETURNING clauses are not allowed either. These limitations are likely
fixable with sufficient effort. Rewrite rules are also not supported,
but it's not clear that we'd want to support them.
Author: Pavan Deolasee <firstname.lastname@example.org>
Author: Álvaro Herrera <email@example.com>
Author: Amit Langote <firstname.lastname@example.org>
Author: Simon Riggs <email@example.com>
Reviewed-by: Peter Eisentraut <firstname.lastname@example.org>
Reviewed-by: Andres Freund <email@example.com> (earlier versions)
Reviewed-by: Peter Geoghegan <firstname.lastname@example.org> (earlier versions)
Reviewed-by: Robert Haas <email@example.com> (earlier versions)
Reviewed-by: Japin Li <firstname.lastname@example.org>
Reviewed-by: Justin Pryzby <email@example.com>
Reviewed-by: Tomas Vondra <firstname.lastname@example.org>
Reviewed-by: Zhihong Yu <email@example.com>
Continue reading Waiting for PostgreSQL 15 – Add support for MERGE SQL command
On 22nd of March 2022, Dean Rasheed committed patch:
Add support for security invoker views.
A security invoker view checks permissions for accessing its
underlying base relations using the privileges of the user of the
view, rather than the privileges of the view owner. Additionally, if
any of the base relations are tables with RLS enabled, the policies of
the user of the view are applied, rather than those of the view owner.
This allows views to be defined without giving away additional
privileges on the underlying base relations, and matches a similar
feature available in other database systems.
It also allows views to operate more naturally with RLS, without
affecting the assignments of policies to users.
Christoph Heiss, with some additional hacking by me. Reviewed by
Laurenz Albe and Wolfgang Walther.
Continue reading Waiting for PostgreSQL 15 – Add support for security invoker views.
Just got information that PGDay.PL is postponed till 4th quarter of 2022. This is due to situation in Ukraine. Figured I'll let you know, given that I previously wrote about it.
Autovacuum was added LONG time ago (in 7.4, as pg_autovacuum). Since then, there were many changes related to it.
These days, hopefully, we no longer see someone saying that they have to disable autovacuum due to performance issues.
But I still see people that say that they have to run daily/weekly vacuum because “autovacuum is not enough". Is it really?
Continue reading Is my autovacuum configured properly?
As of now, explain.depesz.com will provide hints for seq scans with multiple equality conditions joined with AND.
SELECT * FROM TABLE WHERE proj_type = 'GR14' AND week = 2000 AND pop_flag = '1'
You can see it in action in this plan.
Hope you'll find it useful.
Someone asked recently on Slack, whether one should build tsvector data in a field, and index it, or make index on expression.
We talked about it for a while, and I thought I'll reformat my thoughts into blogpost to avoid retyping it next time.
Continue reading How to index data with tsearch?