public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v30 05/11] Add Incremental View Maintenance support to psql
6+ messages / 3 participants
[nested] [flat]
* [PATCH v30 05/11] Add Incremental View Maintenance support to psql
@ 2019-12-20 01:21 Yugo Nagata <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Yugo Nagata @ 2019-12-20 01:21 UTC (permalink / raw)
Add tab completion and meta-command output for IVM.
---
src/bin/psql/describe.c | 32 +++++++++++++++++++++++++++++++-
src/bin/psql/tab-complete.c | 14 +++++++++-----
2 files changed, 40 insertions(+), 6 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b6a4eb1d56..3664371aa6 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1570,6 +1570,7 @@ describeOneTableDetails(const char *schemaname,
char relpersistence;
char relreplident;
char *relam;
+ bool isivm;
} tableinfo;
bool show_column_details = false;
@@ -1582,7 +1583,26 @@ describeOneTableDetails(const char *schemaname,
initPQExpBuffer(&tmpbuf);
/* Get general table info */
- if (pset.sversion >= 120000)
+ if (pset.sversion >= 170000)
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
+ "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
+ "false AS relhasoids, c.relispartition, %s, c.reltablespace, "
+ "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
+ "c.relpersistence, c.relreplident, am.amname, "
+ "c.relisivm\n"
+ "FROM pg_catalog.pg_class c\n "
+ "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
+ "LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)\n"
+ "WHERE c.oid = '%s';",
+ (verbose ?
+ "pg_catalog.array_to_string(c.reloptions || "
+ "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
+ : "''"),
+ oid);
+ }
+ else if (pset.sversion >= 120000)
{
printfPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
@@ -1702,6 +1722,10 @@ describeOneTableDetails(const char *schemaname,
(char *) NULL : pg_strdup(PQgetvalue(res, 0, 14));
else
tableinfo.relam = NULL;
+ if (pset.sversion >= 170000)
+ tableinfo.isivm = strcmp(PQgetvalue(res, 0, 15), "t") == 0;
+ else
+ tableinfo.isivm = false;
PQclear(res);
res = NULL;
@@ -3555,6 +3579,12 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf, _("Access method: %s"), tableinfo.relam);
printTableAddFooter(&cont, buf.data);
}
+
+ /* Incremental view maintance info */
+ if (verbose && tableinfo.relkind == RELKIND_MATVIEW && tableinfo.isivm)
+ {
+ printTableAddFooter(&cont, _("Incremental view maintenance: yes"));
+ }
}
/* reloptions, if verbose */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index aa1acf8523..9977df2e28 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1245,6 +1245,7 @@ static const pgsql_thing_t words_after_create[] = {
{"FOREIGN TABLE", NULL, NULL, NULL},
{"FUNCTION", NULL, NULL, Query_for_list_of_functions},
{"GROUP", Query_for_list_of_roles},
+ {"INCREMENTAL MATERIALIZED VIEW", NULL, NULL, &Query_for_list_of_matviews, NULL, THING_NO_DROP | THING_NO_ALTER},
{"INDEX", NULL, NULL, &Query_for_list_of_indexes},
{"LANGUAGE", Query_for_list_of_languages},
{"LARGE OBJECT", NULL, NULL, NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
@@ -3252,7 +3253,7 @@ psql_completion(const char *text, int start, int end)
if (HeadMatches("CREATE", "SCHEMA"))
COMPLETE_WITH("TABLE", "SEQUENCE");
else
- COMPLETE_WITH("TABLE", "SEQUENCE", "MATERIALIZED VIEW");
+ COMPLETE_WITH("TABLE", "SEQUENCE", "MATERIALIZED VIEW", "INCREMENTAL MATERIALIZED VIEW");
}
/* Complete PARTITION BY with RANGE ( or LIST ( or ... */
else if (TailMatches("PARTITION", "BY"))
@@ -3597,13 +3598,16 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH("SELECT");
/* CREATE MATERIALIZED VIEW */
- else if (Matches("CREATE", "MATERIALIZED"))
+ else if (Matches("CREATE", "MATERIALIZED") ||
+ Matches("CREATE", "INCREMENTAL", "MATERIALIZED"))
COMPLETE_WITH("VIEW");
- /* Complete CREATE MATERIALIZED VIEW <name> with AS */
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny))
+ /* Complete CREATE MATERIALIZED VIEW <name> with AS */
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) ||
+ Matches("CREATE", "INCREMENTAL", "MATERIALIZED", "VIEW", MatchAny))
COMPLETE_WITH("AS");
/* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */
- else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS"))
+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") ||
+ Matches("CREATE", "INCREMENTAL", "MATERIALIZED", "VIEW", MatchAny, "AS"))
COMPLETE_WITH("SELECT");
/* CREATE EVENT TRIGGER */
--
2.25.1
--Multipart=_Mon__4_Mar_2024_11_58_46_+0900_UaponF/qQhQrVCFt
Content-Type: text/x-diff;
name="v30-0006-Add-Incremental-View-Maintenance-support.patch"
Content-Disposition: attachment;
filename="v30-0006-Add-Incremental-View-Maintenance-support.patch"
Content-Transfer-Encoding: 7bit
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: base backup vs. concurrent truncation
@ 2023-04-21 14:50 Aleksander Alekseev <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Aleksander Alekseev @ 2023-04-21 14:50 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Robert Haas <[email protected]>
Hi,
Just a quick observation:
> basebackup.c's theory about relation truncation is that it doesn't
> really matter because WAL replay will fix things up. But in this case,
> I don't think it will, because WAL replay relies on the above
> invariant holding.
Assuming this is the case perhaps we can reduce the scenario and
consider this simpler one:
1. The table is truncated
2. The DBMS is killed before making a checkpoint
3. We are in recovery and presumably see a pair of 0.5 Gb segments
Or can't we?
--
Best regards,
Aleksander Alekseev
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: base backup vs. concurrent truncation
@ 2023-04-21 14:56 Aleksander Alekseev <[email protected]>
parent: Aleksander Alekseev <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Aleksander Alekseev @ 2023-04-21 14:56 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Robert Haas <[email protected]>
Hi again,
> Assuming this is the case perhaps we can reduce the scenario and
> consider this simpler one:
>
> 1. The table is truncated
> 2. The DBMS is killed before making a checkpoint
> 3. We are in recovery and presumably see a pair of 0.5 Gb segments
>
> Or can't we?
Oh, I see. If the process will be killed this perhaps is not going to
happen. Whether this can happen if we pull the plug from the machine
is probably a design implementation of the particular filesystem and
whether it's journaled.
Hm...
--
Best regards,
Aleksander Alekseev
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: base backup vs. concurrent truncation
@ 2023-04-21 15:03 Robert Haas <[email protected]>
parent: Aleksander Alekseev <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Robert Haas @ 2023-04-21 15:03 UTC (permalink / raw)
To: Aleksander Alekseev <[email protected]>; +Cc: pgsql-hackers
On Fri, Apr 21, 2023 at 10:56 AM Aleksander Alekseev
<[email protected]> wrote:
> > Assuming this is the case perhaps we can reduce the scenario and
> > consider this simpler one:
> >
> > 1. The table is truncated
> > 2. The DBMS is killed before making a checkpoint
> > 3. We are in recovery and presumably see a pair of 0.5 Gb segments
> >
> > Or can't we?
>
> Oh, I see. If the process will be killed this perhaps is not going to
> happen. Whether this can happen if we pull the plug from the machine
> is probably a design implementation of the particular filesystem and
> whether it's journaled.
Right. I mentioned that scenario in the original email:
"Furthermore, I think that the problem could arise without performing a
backup at all: say that the server crashes on the OS level in
mid-truncation, and the truncation of segment 0 reaches disk but the
removal of segment 1 does not."
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: base backup vs. concurrent truncation
@ 2023-04-21 21:19 Aleksander Alekseev <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Aleksander Alekseev @ 2023-04-21 21:19 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Robert Haas <[email protected]>
Hi,
> > Oh, I see. If the process will be killed this perhaps is not going to
> > happen. Whether this can happen if we pull the plug from the machine
> > is probably a design implementation of the particular filesystem and
> > whether it's journaled.
>
> Right. I mentioned that scenario in the original email:
>
> "Furthermore, I think that the problem could arise without performing a
> backup at all: say that the server crashes on the OS level in
> mid-truncation, and the truncation of segment 0 reaches disk but the
> removal of segment 1 does not."
Right. I didn't fully understand this scenario at first.
I tried to reproduce it to see what actually happens. Here is what I did.
```
create table truncateme(id integer, val varchar(1024));
alter table truncateme set (autovacuum_enabled = off);
-- takes ~30 seconds
insert into truncateme
select id,
(
select string_agg(chr((33+random()*(126-33)) :: integer), '')
from generate_series(1,1000)
)
from generate_series(1,2*1024*1024) as id;
checkpoint;
select relfilenode from pg_class where relname = 'truncateme';
relfilenode
-------------
32778
```
We got 3 segments (and no VM fork since I disabled VACUUM):
```
$ cd ~/pginstall/data-master/base/16384/
$ ls -lah 32778*
-rw------- 1 eax staff 1.0G Apr 21 23:47 32778
-rw------- 1 eax staff 1.0G Apr 21 23:48 32778.1
-rw------- 1 eax staff 293M Apr 21 23:48 32778.2
-rw------- 1 eax staff 608K Apr 21 23:48 32778_fsm
```
Let's backup the last segment:
```
$ cp 32778.2 ~/temp/32778.2_backup
```
Truncate the table:
```
delete from truncateme where id > 1024*1024;
vacuum truncateme;
```
And kill Postgres:
```
$ pkill -9 postgres
```
... before it finishes another checkpoint:
```
LOG: checkpoints are occurring too frequently (4 seconds apart)
HINT: Consider increasing the configuration parameter "max_wal_size".
LOG: checkpoint starting: wal
[and there was no "checkpoint complete"]
```
Next:
```
$ ls -lah 32778*
-rw------- 1 eax staff 1.0G Apr 21 23:50 32778
-rw------- 1 eax staff 146M Apr 21 23:50 32778.1
-rw------- 1 eax staff 0B Apr 21 23:50 32778.2
-rw------- 1 eax staff 312K Apr 21 23:50 32778_fsm
-rw------- 1 eax staff 40K Apr 21 23:50 32778_vm
$ cp ~/temp/32778.2_backup ./32778.2
```
I believe this simulates the case when pg_basebackup did a checkpoint,
copied 32778.2 before the rest of the segments, Postgres did a
truncate, and pg_basebackup received the rest of the data including
WAL. The WAL contains the record about the truncation, see
RelationTruncate(). Just as an observation: we keep zero sized
segments instead of deleting them.
So if I start Postgres now I expect it to return to a consistent
state, ideally the same state it had before the crash in terms of the
segments.
What I actually get is:
```
LOG: database system was interrupted; last known up at 2023-04-21 23:50:08 MSK
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 9/C4035B18
LOG: invalid record length at 9/E8FCBF10: expected at least 24, got 0
LOG: redo done at 9/E8FCBEB0 system usage: CPU: user: 1.58 s, system:
0.96 s, elapsed: 2.61 s
LOG: checkpoint starting: end-of-recovery immediate wait
LOG: checkpoint complete: wrote 10 buffers (0.0%); 0 WAL file(s)
added, 0 removed, 36 recycled; write=0.005 s, sync=0.003 s,
total=0.016 s; sync files=9, longest=0.002 s, average=0.001 s;
distance=605784 kB, estimate=605784 kB; lsn=9/E8FCBF10, redo
lsn=9/E8FCBF10
LOG: database system is ready to accept connections
```
... and:
```
-rw------- 1 eax staff 1.0G Apr 21 23:50 32778
-rw------- 1 eax staff 146M Apr 21 23:53 32778.1
-rw------- 1 eax staff 0B Apr 21 23:53 32778.2
```
Naturally the database is consistent too. So it looks like the
recovery protocol worked as expected after all, at least in the
upcoming PG16 and this specific scenario.
Did I miss anything? If not, perhaps we should just update the comments a bit?
--
Best regards,
Aleksander Alekseev
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: base backup vs. concurrent truncation
@ 2023-04-24 15:37 Robert Haas <[email protected]>
parent: Aleksander Alekseev <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Robert Haas @ 2023-04-24 15:37 UTC (permalink / raw)
To: Aleksander Alekseev <[email protected]>; +Cc: pgsql-hackers
On Fri, Apr 21, 2023 at 5:19 PM Aleksander Alekseev
<[email protected]> wrote:
> Naturally the database is consistent too. So it looks like the
> recovery protocol worked as expected after all, at least in the
> upcoming PG16 and this specific scenario.
>
> Did I miss anything? If not, perhaps we should just update the comments a bit?
I was confused about what was happening here but after trying to
reproduce I think I figured it out. In my test, I saw the .1 file grow
to a full 1GB and then the truncate happened afterward.
Unsurprisingly, that works. I believe that what's happening here is
that the DELETE statement triggers FPIs for all of the blocks it
touches. Therefore, when the DELETE records are replayed, those blocks
get written back to the relation, extending it. That gets it up to the
required 1GB size after which the .2 file is visible to the smgr
again, so the truncate works fine. I think that to reproduce the
scenario, you want the truncate to happen in its own checkpoint cycle.
I also found out from Andres that he's complained about pretty much
the same problem just a couple of months ago:
https://www.postgresql.org/message-id/[email protected]
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-04-24 15:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-20 01:21 [PATCH v30 05/11] Add Incremental View Maintenance support to psql Yugo Nagata <[email protected]>
2023-04-21 14:50 Re: base backup vs. concurrent truncation Aleksander Alekseev <[email protected]>
2023-04-21 14:56 ` Re: base backup vs. concurrent truncation Aleksander Alekseev <[email protected]>
2023-04-21 15:03 ` Re: base backup vs. concurrent truncation Robert Haas <[email protected]>
2023-04-21 21:19 ` Re: base backup vs. concurrent truncation Aleksander Alekseev <[email protected]>
2023-04-24 15:37 ` Re: base backup vs. concurrent truncation Robert Haas <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox