https://www.depesz.com Thu, 12 Dec 2024 14:15:24 +0000 en-US hourly 1 https://wordpress.org/?v=6.7.1 https://www.depesz.com/2024/12/12/how-to-alter-tables-without-breaking-application/ https://www.depesz.com/2024/12/12/how-to-alter-tables-without-breaking-application/#respond <![CDATA[depesz]]> Thu, 12 Dec 2024 14:15:24 +0000 <![CDATA[Uncategorized]]> <![CDATA[alter]]> <![CDATA[alter table]]> <![CDATA[infinite]]> <![CDATA[locks]]> <![CDATA[loop]]> <![CDATA[postgresql]]> <![CDATA[tutorial]]> <![CDATA[wait]]> https://www.depesz.com/?p=4312 <![CDATA[I once wrote about this problem, but given that we now have DO blocks and procedures I can make nicer, easy to use example. Over the years there have been many improvements to how long ALTER TABLE can take. You can now (in some cases) change datatype without rewrite or add default value. Regardless how … Continue reading "How to ALTER tables without breaking application?"]]> https://www.depesz.com/2024/12/12/how-to-alter-tables-without-breaking-application/feed/ 0 https://www.depesz.com/2024/12/01/sql-best-practices-dont-compare-count-with-0/ https://www.depesz.com/2024/12/01/sql-best-practices-dont-compare-count-with-0/#comments <![CDATA[depesz]]> Sun, 01 Dec 2024 09:21:57 +0000 <![CDATA[Uncategorized]]> <![CDATA[best]]> <![CDATA[count]]> <![CDATA[exists]]> <![CDATA[postgresql]]> <![CDATA[practices]]> <![CDATA[tutorial]]> https://www.depesz.com/?p=4310 <![CDATA[Every now and then I see something like this: SELECT u.* FROM users u WHERE 0 = (SELECT COUNT(*) FROM addresses a WHERE a.user_id = u.id); and it kinda pains me. So figured, I'll write about what is the problem with it, and how to avoid such constructs. Lets consider what went through someones mind … Continue reading "SQL best practices – don’t compare count(*) with 0"]]> https://www.depesz.com/2024/12/01/sql-best-practices-dont-compare-count-with-0/feed/ 12 https://www.depesz.com/2024/11/25/4307/ https://www.depesz.com/2024/11/25/4307/#respond <![CDATA[depesz]]> Mon, 25 Nov 2024 22:45:34 +0000 <![CDATA[Uncategorized]]> <![CDATA[example]]> <![CDATA[http]]> <![CDATA[job]]> <![CDATA[listen]]> <![CDATA[mail]]> <![CDATA[notify]]> <![CDATA[postgresql]]> <![CDATA[ruby]]> <![CDATA[tutorial]]> https://www.depesz.com/?p=4307 <![CDATA[This question happens every now and then in one of PostgreSQL support places. Whenever it happens, I just suggest to not try, as interacting with outside world from database can be problematic, and instead use LISTEN/NOTIFY. But it occurred to me, that while I know how to do it, I don't think I actually did … Continue reading "How can I send mail or HTTP request from database?"]]> https://www.depesz.com/2024/11/25/4307/feed/ 0 https://www.depesz.com/2024/11/22/changes-on-pgdoc-link/ https://www.depesz.com/2024/11/22/changes-on-pgdoc-link/#respond <![CDATA[depesz]]> Fri, 22 Nov 2024 14:24:41 +0000 <![CDATA[Uncategorized]]> <![CDATA[contrib]]> <![CDATA[documentation]]> <![CDATA[environment]]> <![CDATA[file]]> <![CDATA[keyword]]> <![CDATA[pgdoc]]> <![CDATA[postgresql]]> <![CDATA[search]]> https://www.depesz.com/?p=4303 <![CDATA[Based on checking logs, and my own personal needs I added more categories of keywords to pgdoc.link: environment variables, like: PGHOST config file names, like: pgpass functions from some contrib modules, like: akeys, or cube_ll_coord various keyword-like functions (?), like: current_user. This happened thanks to bug report by Benjie Gillam This brought the total number … Continue reading "Changes on pgdoc.link"]]> https://www.depesz.com/2024/11/22/changes-on-pgdoc-link/feed/ 0 https://www.depesz.com/2024/11/15/grouping-data-into-array-of-sums-fun-with-custom-aggregates/ https://www.depesz.com/2024/11/15/grouping-data-into-array-of-sums-fun-with-custom-aggregates/#respond <![CDATA[depesz]]> Fri, 15 Nov 2024 14:55:58 +0000 <![CDATA[Uncategorized]]> <![CDATA[aggregate]]> <![CDATA[day]]> <![CDATA[hour]]> <![CDATA[postgresql]]> <![CDATA[sum]]> <![CDATA[tutorial]]> https://www.depesz.com/?p=4300 <![CDATA[Was asked recently about optimization of interesting case. There was table like: =$ CREATE TABLE input_data ( category_id INT8, object_id INT8, interaction_ts timestamptz, interaction_type TEXT, interaction_count INT4 ); And there was a code that was grouping it all by sum()ing interaction_count per category, object, interaction_type, and timestamp truncated to hour. Basically, storing somewhere result of: … Continue reading "Grouping data into array of sums – fun with custom aggregates"]]> https://www.depesz.com/2024/11/15/grouping-data-into-array-of-sums-fun-with-custom-aggregates/feed/ 0 https://www.depesz.com/2024/11/06/system-views-added-to-pgdoc-link/ https://www.depesz.com/2024/11/06/system-views-added-to-pgdoc-link/#respond <![CDATA[depesz]]> Wed, 06 Nov 2024 11:44:53 +0000 <![CDATA[Uncategorized]]> <![CDATA[documentation]]> <![CDATA[keyword]]> <![CDATA[pgdoc]]> <![CDATA[postgresql]]> <![CDATA[search]]> https://www.depesz.com/?p=4292 <![CDATA[Checking logs for pgdoc.link I noticed that some people where searching for system views, like pg_stat_activity, or pg_stat_all_tables in Pg 9.3. Now, these will work. This increased total number of known keywords from 1840 to 1883. Not much, but it's a progress 🙂]]> https://www.depesz.com/2024/11/06/system-views-added-to-pgdoc-link/feed/ 0 https://www.depesz.com/2024/11/05/waiting-for-postgresql-18-add-sql-function-array_reverse/ https://www.depesz.com/2024/11/05/waiting-for-postgresql-18-add-sql-function-array_reverse/#respond <![CDATA[depesz]]> Tue, 05 Nov 2024 08:17:35 +0000 <![CDATA[Uncategorized]]> <![CDATA[array]]> <![CDATA[array_reverse]]> <![CDATA[pg18]]> <![CDATA[postgresql]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4290 <![CDATA[On 1st of November 2024, Michael Paquier committed patch: Add SQL function array_reverse()   This function takes in input an array, and reverses the position of all its elements. This operation only affects the first dimension of the array, like array_shuffle().   The implementation structure is inspired by array_shuffle(), with a subroutine called array_reverse_n() that … Continue reading "Waiting for PostgreSQL 18 – Add SQL function array_reverse()"]]> https://www.depesz.com/2024/11/05/waiting-for-postgresql-18-add-sql-function-array_reverse/feed/ 0 https://www.depesz.com/2024/10/29/new-way-to-search-postgresql-documentation/ https://www.depesz.com/2024/10/29/new-way-to-search-postgresql-documentation/#comments <![CDATA[depesz]]> Tue, 29 Oct 2024 10:45:23 +0000 <![CDATA[Uncategorized]]> <![CDATA[documentation]]> <![CDATA[keyword]]> <![CDATA[pgdoc]]> <![CDATA[postgresql]]> <![CDATA[search]]> https://www.depesz.com/?p=4280 <![CDATA[PostgreSQL documentation is, generally speaking, great. But it isn't the easiest thing to search in. Over the years I memorized urls to certain docs, but there is a limit to it. What's more, there are certain inconsistencies. For example – most pages that describe program have name that starts with app-. But not all. Some … Continue reading "New way to search PostgreSQL documentation"]]> https://www.depesz.com/2024/10/29/new-way-to-search-postgresql-documentation/feed/ 6 https://www.depesz.com/2024/10/28/case-study-optimization-of-weirdly-picked-bad-plan/ https://www.depesz.com/2024/10/28/case-study-optimization-of-weirdly-picked-bad-plan/#comments <![CDATA[depesz]]> Mon, 28 Oct 2024 12:04:44 +0000 <![CDATA[Uncategorized]]> <![CDATA[analyze]]> <![CDATA[index]]> <![CDATA[materialize]]> <![CDATA[materialized]]> <![CDATA[memory]]> <![CDATA[optimization]]> <![CDATA[postgresql]]> <![CDATA[query]]> <![CDATA[stats]]> <![CDATA[tutorial]]> https://www.depesz.com/?p=4277 <![CDATA[We recently hit an interesting case where planner picked wrong plan. Kinda. And figured it could be used to show how to deal with such cases. So, we have some databases on PostgreSQL 14 (yes, I know, we should upgrade, but it is LONG project to upgrade, so it's not really on the table now). … Continue reading "Case study: optimization of weirdly picked bad plan"]]> https://www.depesz.com/2024/10/28/case-study-optimization-of-weirdly-picked-bad-plan/feed/ 1 https://www.depesz.com/2024/10/11/sql-json-is-here-kinda-waiting-for-pg-17/ https://www.depesz.com/2024/10/11/sql-json-is-here-kinda-waiting-for-pg-17/#respond <![CDATA[depesz]]> Fri, 11 Oct 2024 21:37:40 +0000 <![CDATA[Uncategorized]]> <![CDATA[json]]> <![CDATA[jsonb]]> <![CDATA[json_query]]> <![CDATA[json_table]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[sql/json]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4273 <![CDATA[Amazing. Awesome. Well, but what is it? We could store json data in Pg since PostgreSQL 9.2 – so it's been there for over 12 years now. How is the new shiny thing different? What does it allow you to do? Let's see if I can shed some light on it… For starters: SQL/JSON is … Continue reading "SQL/JSON is here! (kinda “Waiting for Pg 17”)"]]> https://www.depesz.com/2024/10/11/sql-json-is-here-kinda-waiting-for-pg-17/feed/ 0 https://www.depesz.com/2024/10/03/waiting-for-postgresql-18-add-temporal-foreign-key-contraints/ https://www.depesz.com/2024/10/03/waiting-for-postgresql-18-add-temporal-foreign-key-contraints/#respond <![CDATA[depesz]]> Thu, 03 Oct 2024 18:19:03 +0000 <![CDATA[Uncategorized]]> <![CDATA[foreign key]]> <![CDATA[period]]> <![CDATA[pg18]]> <![CDATA[postgresql]]> <![CDATA[range]]> <![CDATA[temporal]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4270 <![CDATA[On 17th of September 2024, Peter Eisentraut committed patch: Add temporal FOREIGN KEY contraints   Add PERIOD clause to foreign key constraint definitions. This is supported for range and multirange types. Temporal foreign keys check for range containment instead of equality.   This feature matches the behavior of the SQL standard temporal foreign keys, but … Continue reading "Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints"]]> https://www.depesz.com/2024/10/03/waiting-for-postgresql-18-add-temporal-foreign-key-contraints/feed/ 0 https://www.depesz.com/2024/09/30/waiting-for-postgresql-18-add-temporal-primary-key-and-unique-constraints/ https://www.depesz.com/2024/09/30/waiting-for-postgresql-18-add-temporal-primary-key-and-unique-constraints/#comments <![CDATA[depesz]]> Mon, 30 Sep 2024 10:19:09 +0000 <![CDATA[Uncategorized]]> <![CDATA[period]]> <![CDATA[pg18]]> <![CDATA[postgresql]]> <![CDATA[primary key]]> <![CDATA[range]]> <![CDATA[temporal]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4266 <![CDATA[On 17th of September 2024, Peter Eisentraut committed patch: Add temporal PRIMARY KEY and UNIQUE constraints   Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints. These are backed by GiST indexes instead of B-tree indexes, since they are essentially exclusion constraints with = for the scalar parts of the key and && for … Continue reading "Waiting for PostgreSQL 18 – Add temporal PRIMARY KEY and UNIQUE constraints"]]> https://www.depesz.com/2024/09/30/waiting-for-postgresql-18-add-temporal-primary-key-and-unique-constraints/feed/ 4 https://www.depesz.com/2024/07/19/what-tables-were-touched-within-given-range-of-wal-lsn/ https://www.depesz.com/2024/07/19/what-tables-were-touched-within-given-range-of-wal-lsn/#respond <![CDATA[depesz]]> Fri, 19 Jul 2024 15:00:09 +0000 <![CDATA[Uncategorized]]> <![CDATA[cdc]]> <![CDATA[decoding]]> <![CDATA[failover]]> <![CDATA[logical]]> <![CDATA[postgresql]]> <![CDATA[tutorial]]> <![CDATA[wal]]> <![CDATA[waldump]]> https://www.depesz.com/?p=4262 <![CDATA[We had a case recently where one of our DBs failed over to a new primary. To this server (old primary, and then new primary) we had connection from some kind of CDC tool (Debezium, I think). The thing is that while there was failover, this tool read (using logical decoding) changes on old primary … Continue reading "What tables were touched within given range of wal LSN?"]]> https://www.depesz.com/2024/07/19/what-tables-were-touched-within-given-range-of-wal-lsn/feed/ 0 https://www.depesz.com/2024/06/11/how-much-speed-youre-leaving-at-the-table-if-you-use-default-locale/ https://www.depesz.com/2024/06/11/how-much-speed-youre-leaving-at-the-table-if-you-use-default-locale/#comments <![CDATA[depesz]]> Tue, 11 Jun 2024 18:53:35 +0000 <![CDATA[Uncategorized]]> <![CDATA[benchmark]]> <![CDATA[collation]]> <![CDATA[performance]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> https://www.depesz.com/?p=4251 <![CDATA[I've been to PGConf.dev recently, and one of the talks was about collations. The whole talk was interesting (to put it mildly), but the thing that stuck with me is that we really shouldn't be using default collation provider (libc with locale based collation), unless it's really needed, because we're wasting performance. But how much … Continue reading "How much speed you’re leaving at the table if you use default locale?"]]> https://www.depesz.com/2024/06/11/how-much-speed-youre-leaving-at-the-table-if-you-use-default-locale/feed/ 3 https://www.depesz.com/2024/05/22/waiting-for-postgresql-17-merge-split-partitions/ https://www.depesz.com/2024/05/22/waiting-for-postgresql-17-merge-split-partitions/#comments <![CDATA[depesz]]> Wed, 22 May 2024 11:41:42 +0000 <![CDATA[Uncategorized]]> <![CDATA[merge]]> <![CDATA[partitioning]]> <![CDATA[partitions]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[split]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4249 <![CDATA[I thought about it for quite some time, whether I should write about it, and how. That's why there is delay since: On 6th of April 2024, Alexander Korotkov committed patch: Implement ALTER TABLE ... MERGE PARTITIONS ... command   This new DDL command merges several partitions into the one partition of the target table. … Continue reading "Waiting for PostgreSQL 17 – MERGE / SPLIT partitions"]]> https://www.depesz.com/2024/05/22/waiting-for-postgresql-17-merge-split-partitions/feed/ 2 https://www.depesz.com/2024/04/12/waiting-for-sql-json-is-coming-back-hopefully/ https://www.depesz.com/2024/04/12/waiting-for-sql-json-is-coming-back-hopefully/#comments <![CDATA[depesz]]> Fri, 12 Apr 2024 11:28:05 +0000 <![CDATA[Uncategorized]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[sql/json]]> <![CDATA[teaser]]> <![CDATA[trailer]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4244 <![CDATA[This is not the usual Waiting for post, but something should be said. Back in March/April of 2022 Andrew Dunstan committed a series of patches that added support for lots of really interesting features from SQL/JSON standard. While I'm not avid user of json in database, I was very, very happy. Wrote couple of blogposts … Continue reading "Waiting for …: SQL/JSON is coming back. Hopefully."]]> https://www.depesz.com/2024/04/12/waiting-for-sql-json-is-coming-back-hopefully/feed/ 4 https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-invent-serialize-option-for-explain/ https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-invent-serialize-option-for-explain/#respond <![CDATA[depesz]]> Thu, 11 Apr 2024 19:51:35 +0000 <![CDATA[Uncategorized]]> <![CDATA[analyze]]> <![CDATA[explain]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[serialize]]> <![CDATA[toast]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4242 <![CDATA[On 3rd of April 2024, Tom Lane committed patch: Invent SERIALIZE option for EXPLAIN.   EXPLAIN (ANALYZE, SERIALIZE) allows collection of statistics about the volume of data emitted by a query, as well as the time taken to convert the data to the on-the-wire format. Previously there was no way to investigate this without actually … Continue reading "Waiting for PostgreSQL 17 – Invent SERIALIZE option for EXPLAIN."]]> https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-invent-serialize-option-for-explain/feed/ 0 https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-add-new-copy-option-log_verbosity/ https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-add-new-copy-option-log_verbosity/#respond <![CDATA[depesz]]> Thu, 11 Apr 2024 19:38:06 +0000 <![CDATA[Uncategorized]]> <![CDATA[copy]]> <![CDATA[log_verbosity]]> <![CDATA[on_error]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4240 <![CDATA[On 1st of April 2024, Masahiko Sawada committed patch: Add new COPY option LOG_VERBOSITY.   This commit adds a new COPY option LOG_VERBOSITY, which controls the amount of messages emitted during processing. Valid values are 'default' and 'verbose'.   This is currently used in COPY FROM when ON_ERROR option is set to ignore. If 'verbose' … Continue reading "Waiting for PostgreSQL 17 – Add new COPY option LOG_VERBOSITY."]]> https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-add-new-copy-option-log_verbosity/feed/ 0 https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-add-support-for-merge-when-not-matched-by-source/ https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-add-support-for-merge-when-not-matched-by-source/#respond <![CDATA[depesz]]> Thu, 11 Apr 2024 19:26:31 +0000 <![CDATA[Uncategorized]]> <![CDATA[delete]]> <![CDATA[matched]]> <![CDATA[merge]]> <![CDATA[not matched]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[source]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4238 <![CDATA[On 30th of March 2024, Dean Rasheed committed patch: Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.   This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE actions, which operate on rows that exist in the target relation, but not in the data source. These actions can execute UPDATE, DELETE, or … Continue reading "Waiting for PostgreSQL 17 – Add support for MERGE … WHEN NOT MATCHED BY SOURCE."]]> https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-add-support-for-merge-when-not-matched-by-source/feed/ 0 https://www.depesz.com/2024/03/19/waiting-for-postgresql-17-add-returning-support-to-merge/ https://www.depesz.com/2024/03/19/waiting-for-postgresql-17-add-returning-support-to-merge/#respond <![CDATA[depesz]]> Tue, 19 Mar 2024 18:41:48 +0000 <![CDATA[Uncategorized]]> <![CDATA[merge]]> <![CDATA[merge_action]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[returning]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4235 <![CDATA[On 17th of March 2024, Dean Rasheed committed patch: Add RETURNING support to MERGE.   This allows a RETURNING clause to be appended to a MERGE query, to return values based on each row inserted, updated, or deleted. As with plain INSERT, UPDATE, and DELETE commands, the returned values are based on the new contents … Continue reading "Waiting for PostgreSQL 17 – Add RETURNING support to MERGE."]]> https://www.depesz.com/2024/03/19/waiting-for-postgresql-17-add-returning-support-to-merge/feed/ 0 https://www.depesz.com/2024/03/18/what-the-hell-is-transaction-wraparound/ https://www.depesz.com/2024/03/18/what-the-hell-is-transaction-wraparound/#comments <![CDATA[depesz]]> Mon, 18 Mar 2024 10:59:55 +0000 <![CDATA[Uncategorized]]> <![CDATA[freeze]]> <![CDATA[mvcc]]> <![CDATA[postgresql]]> <![CDATA[slack]]> <![CDATA[tutorial]]> <![CDATA[vacuum]]> <![CDATA[wraparound]]> https://www.depesz.com/?p=4230 <![CDATA[Recently someone asked on Slack about what is transaction wraparound. Full answer is a bit too much for slack reply, but I can try to explain it in here. So, every row in PostgreSQL containst two hidden columns: xmin, and xmax. You can see them: =$ CREATE TABLE wrapit (a int4); CREATE TABLE   =$ … Continue reading "What the hell is transaction wraparound?"]]> https://www.depesz.com/2024/03/18/what-the-hell-is-transaction-wraparound/feed/ 5 https://www.depesz.com/2024/02/07/waiting-for-postgresql-17-add-new-copy-option-save_error_to-rename-copy-option-from-save_error_to-to-on_error/ https://www.depesz.com/2024/02/07/waiting-for-postgresql-17-add-new-copy-option-save_error_to-rename-copy-option-from-save_error_to-to-on_error/#comments <![CDATA[depesz]]> Wed, 07 Feb 2024 21:41:37 +0000 <![CDATA[Uncategorized]]> <![CDATA[copy]]> <![CDATA[error]]> <![CDATA[ignore]]> <![CDATA[on_error]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[save_error_to]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4227 <![CDATA[On 16th of January 2024, Alexander Korotkov committed patch: Add new COPY option SAVE_ERROR_TO   Currently, when source data contains unexpected data regarding data type or range, the entire COPY fails. However, in some cases, such data can be ignored and just copying normal data is preferable.   This commit adds a new option SAVE_ERROR_TO, … Continue reading "Waiting for PostgreSQL 17 – Add new COPY option SAVE_ERROR_TO / Rename COPY option from SAVE_ERROR_TO to ON_ERROR"]]> https://www.depesz.com/2024/02/07/waiting-for-postgresql-17-add-new-copy-option-save_error_to-rename-copy-option-from-save_error_to-to-on_error/feed/ 3 https://www.depesz.com/2024/02/07/waiting-for-postgresql-17-support-identity-columns-in-partitioned-tables/ https://www.depesz.com/2024/02/07/waiting-for-postgresql-17-support-identity-columns-in-partitioned-tables/#comments <![CDATA[depesz]]> Wed, 07 Feb 2024 20:12:08 +0000 <![CDATA[Uncategorized]]> <![CDATA[generated]]> <![CDATA[identity]]> <![CDATA[partitioning]]> <![CDATA[partitions]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4224 <![CDATA[On 16th of January 2024, Peter Eisentraut committed patch: Support identity columns in partitioned tables   Previously, identity columns were disallowed on partitioned tables. (The reason was mainly that no one had gotten around to working through all the details to make it work.) This makes it work now.   Some details on the behavior: … Continue reading "Waiting for PostgreSQL 17 – Support identity columns in partitioned tables"]]> https://www.depesz.com/2024/02/07/waiting-for-postgresql-17-support-identity-columns-in-partitioned-tables/feed/ 1 https://www.depesz.com/2024/01/22/waiting-for-postgresql-17-in-plpgsql-allow-type-and-rowtype-to-be-followed-by-array-decoration/ https://www.depesz.com/2024/01/22/waiting-for-postgresql-17-in-plpgsql-allow-type-and-rowtype-to-be-followed-by-array-decoration/#respond <![CDATA[depesz]]> Mon, 22 Jan 2024 11:36:38 +0000 <![CDATA[Uncategorized]]> <![CDATA[array]]> <![CDATA[arrays]]> <![CDATA[declaration]]> <![CDATA[pg17]]> <![CDATA[plpgsql]]> <![CDATA[postgresql]]> <![CDATA[variable]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4221 <![CDATA[On 4th of January 2024, Tom Lane committed patch: In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration.   This provides the useful ability to declare a variable that is an array of the type of some other variable or some table column.   Quan Zongliang, Pavel Stehule   Discussion: https://postgr.es/m/[email protected] This … Continue reading "Waiting for PostgreSQL 17 – In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration."]]> https://www.depesz.com/2024/01/22/waiting-for-postgresql-17-in-plpgsql-allow-type-and-rowtype-to-be-followed-by-array-decoration/feed/ 0 https://www.depesz.com/2024/01/15/waiting-for-postgresql-17-alter-table-command-to-change-generation-expression/ https://www.depesz.com/2024/01/15/waiting-for-postgresql-17-alter-table-command-to-change-generation-expression/#comments <![CDATA[depesz]]> Mon, 15 Jan 2024 15:30:01 +0000 <![CDATA[Uncategorized]]> <![CDATA[alter]]> <![CDATA[expression]]> <![CDATA[generated]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4219 <![CDATA[On 4th of January 2024, Peter Eisentraut committed patch: ALTER TABLE command to change generation expression   This adds a new ALTER TABLE subcommand ALTER COLUMN ... SET EXPRESSION that changes the generation expression of a generated column.   The syntax is not standard but was adapted from other SQL implementations.   This command causes … Continue reading "Waiting for PostgreSQL 17 – ALTER TABLE command to change generation expression"]]> https://www.depesz.com/2024/01/15/waiting-for-postgresql-17-alter-table-command-to-change-generation-expression/feed/ 1 https://www.depesz.com/2024/01/08/waiting-for-postgresql-17-add-support-for-incremental-backup/ https://www.depesz.com/2024/01/08/waiting-for-postgresql-17-add-support-for-incremental-backup/#comments <![CDATA[depesz]]> Mon, 08 Jan 2024 18:23:52 +0000 <![CDATA[Uncategorized]]> <![CDATA[backup]]> <![CDATA[backups]]> <![CDATA[incremental]]> <![CDATA[pg17]]> <![CDATA[pg_basebackup]]> <![CDATA[postgresql]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4216 <![CDATA[On 20th of December 2023, Robert Haas committed patch: Add support for incremental backup.   To take an incremental backup, you use the new replication command UPLOAD_MANIFEST to upload the manifest for the prior backup. This prior backup could either be a full backup or another incremental backup. You then use BASE_BACKUP with the INCREMENTAL … Continue reading "Waiting for PostgreSQL 17 – Add support for incremental backup."]]> https://www.depesz.com/2024/01/08/waiting-for-postgresql-17-add-support-for-incremental-backup/feed/ 1 https://www.depesz.com/2023/12/29/proper-support-for-i-o-timings-on-explain-depesz-com/ https://www.depesz.com/2023/12/29/proper-support-for-i-o-timings-on-explain-depesz-com/#respond <![CDATA[depesz]]> Fri, 29 Dec 2023 12:20:55 +0000 <![CDATA[Uncategorized]]> <![CDATA[announcements]]> <![CDATA[explain]]> <![CDATA[explain.depesz.com]]> <![CDATA[perl]]> <![CDATA[postgresql]]> https://www.depesz.com/?p=4213 <![CDATA[Very long time ago (in PostgreSQL 9.2) we got ability to get, from EXPLAIN, information how long Pg spent on I/O operations. Reads and writes. Over the years situation has changed. Originally it looked like this: I/O Timings: read=15.792 write=26.804 This told us that Pg spend 15.8ms reading from disk, and 26.8ms writing. In Pg … Continue reading "Proper support for I/O Timings on explain.depesz.com"]]> https://www.depesz.com/2023/12/29/proper-support-for-i-o-timings-on-explain-depesz-com/feed/ 0 https://www.depesz.com/2023/10/24/human-version-sort-in-postgresql-revisited/ <![CDATA[depesz]]> Tue, 24 Oct 2023 21:37:19 +0000 <![CDATA[Uncategorized]]> <![CDATA[collation]]> <![CDATA[compare]]> <![CDATA[howto]]> <![CDATA[human]]> <![CDATA[icu]]> <![CDATA[postgresql]]> <![CDATA[sort]]> <![CDATA[unicode]]> <![CDATA[version]]> https://www.depesz.com/?p=4210 <![CDATA[Couple of months ago I wrote how to do human sort in Pg by using arrays, and splitting string. This works, but Matt mentioned in comments that it could be done with ICU collations. So I looked into it … First, I figured I'll write longer blogpost about collations, but to be honest – I … Continue reading "Human/version sort in PostgreSQL – revisited"]]> https://www.depesz.com/2023/10/24/waiting-for-postgresql-17-add-support-event-triggers-on-authenticated-login/ https://www.depesz.com/2023/10/24/waiting-for-postgresql-17-add-support-event-triggers-on-authenticated-login/#comments <![CDATA[depesz]]> Tue, 24 Oct 2023 13:19:19 +0000 <![CDATA[Uncategorized]]> <![CDATA[authentication]]> <![CDATA[event]]> <![CDATA[initialization]]> <![CDATA[login]]> <![CDATA[perl]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[trigger]]> <![CDATA[waiting]]> https://www.depesz.com/?p=4207 <![CDATA[On 16th of October 2023, Alexander Korotkov committed patch: Add support event triggers on authenticated login   This commit introduces trigger on login event, allowing to fire some actions right on the user connection. This can be useful for logging or connection check purposes as well as for some personalization of environment. Usage details are … Continue reading "Waiting for PostgreSQL 17 – Add support event triggers on authenticated login"]]> https://www.depesz.com/2023/10/24/waiting-for-postgresql-17-add-support-event-triggers-on-authenticated-login/feed/ 3 https://www.depesz.com/2023/09/07/waiting-for-postgresql-17-allow-watch-queries-to-stop-on-minimum-rows-returned/ <![CDATA[depesz]]> Thu, 07 Sep 2023 10:47:46 +0000 <![CDATA[Uncategorized]]> <![CDATA[pg17]]> <![CDATA[postgresql]]> <![CDATA[psql]]> <![CDATA[scripting]]> <![CDATA[vacuum]]> <![CDATA[waiting]]> <![CDATA[watch]]> https://www.depesz.com/?p=4205 <![CDATA[On 29th of August 2023, Daniel Gustafsson committed patch: Allow \watch queries to stop on minimum rows returned   When running a repeat query with \watch in psql, it can be helpful to be able to stop the watch process when the query no longer returns the expected amount of rows. An example would be … Continue reading "Waiting for PostgreSQL 17 – Allow \watch queries to stop on minimum rows returned"]]>