Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8299471

Browse files
committed
Use LEFT JOINs in some system views in case referenced row doesn't exist.
In particular, left join to pg_authid so that rows in pg_stat_activitydon't disappear if the session's owning user has been dropped.Also convert a few joins to pg_database to left joins, in the same spirit,though that case might be harder to hit. We were doing this in otherviews already, so it was a bit inconsistent that these views didn't.Oskari Saarenmaa, with some further tweaking by meDiscussion: <56E87CD8.60007@ohmu.fi>
1 parent65a603e commit8299471

File tree

3 files changed

+19
-23
lines changed

3 files changed

+19
-23
lines changed

‎src/backend/catalog/system_views.sql

Lines changed: 9 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -642,9 +642,9 @@ CREATE VIEW pg_stat_activity AS
642642
S.backend_xid,
643643
s.backend_xmin,
644644
S.query
645-
FROMpg_database D,pg_stat_get_activity(NULL)AS S, pg_authid U
646-
WHERES.datid=D.oidAND
647-
S.usesysid=U.oid;
645+
FROM pg_stat_get_activity(NULL)AS S
646+
LEFT JOIN pg_databaseAS DON (S.datid=D.oid)
647+
LEFT JOIN pg_authidAS UON (S.usesysid=U.oid);
648648

649649
CREATEVIEWpg_stat_replicationAS
650650
SELECT
@@ -664,10 +664,9 @@ CREATE VIEW pg_stat_replication AS
664664
W.replay_location,
665665
W.sync_priority,
666666
W.sync_state
667-
FROM pg_stat_get_activity(NULL)AS S, pg_authid U,
668-
pg_stat_get_wal_senders()AS W
669-
WHERES.usesysid=U.oidAND
670-
S.pid=W.pid;
667+
FROM pg_stat_get_activity(NULL)AS S
668+
JOIN pg_stat_get_wal_senders()AS WON (S.pid=W.pid)
669+
LEFT JOIN pg_authidAS UON (S.usesysid=U.oid);
671670

672671
CREATEVIEWpg_stat_wal_receiverAS
673672
SELECT
@@ -813,7 +812,7 @@ CREATE VIEW pg_stat_progress_vacuum AS
813812
S.param4AS heap_blks_vacuumed,S.param5AS index_vacuum_count,
814813
S.param6AS max_dead_tuples,S.param7AS num_dead_tuples
815814
FROM pg_stat_get_progress_info('VACUUM')AS S
816-
JOIN pg_database DONS.datid=D.oid;
815+
LEFT JOIN pg_database DONS.datid=D.oid;
817816

818817
CREATEVIEWpg_user_mappingsAS
819818
SELECT
@@ -832,12 +831,11 @@ CREATE VIEW pg_user_mappings AS
832831
NULL
833832
ENDAS umoptions
834833
FROM pg_user_mapping U
835-
LEFTJOINpg_authid AON (A.oid=U.umuser)JOIN
836-
pg_foreign_server SON (U.umserver=S.oid);
834+
JOINpg_foreign_server SON (U.umserver=S.oid)
835+
LEFT JOIN pg_authid AON (A.oid=U.umuser);
837836

838837
REVOKE ALLon pg_user_mappingFROM public;
839838

840-
841839
CREATEVIEWpg_replication_origin_statusAS
842840
SELECT*
843841
FROM pg_show_replication_origin_status();

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201608171
56+
#defineCATALOG_VERSION_NO201608191
5757

5858
#endif

‎src/test/regress/expected/rules.out

Lines changed: 9 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1664,10 +1664,9 @@ pg_stat_activity| SELECT s.datid,
16641664
s.backend_xid,
16651665
s.backend_xmin,
16661666
s.query
1667-
FROM pg_database d,
1668-
pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn),
1669-
pg_authid u
1670-
WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
1667+
FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn)
1668+
LEFT JOIN pg_database d ON ((s.datid = d.oid)))
1669+
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
16711670
pg_stat_all_indexes| SELECT c.oid AS relid,
16721671
i.oid AS indexrelid,
16731672
n.nspname AS schemaname,
@@ -1776,7 +1775,7 @@ pg_stat_progress_vacuum| SELECT s.pid,
17761775
s.param6 AS max_dead_tuples,
17771776
s.param7 AS num_dead_tuples
17781777
FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10)
1779-
JOIN pg_database d ON ((s.datid = d.oid)));
1778+
LEFTJOIN pg_database d ON ((s.datid = d.oid)));
17801779
pg_stat_replication| SELECT s.pid,
17811780
s.usesysid,
17821781
u.rolname AS usename,
@@ -1793,10 +1792,9 @@ pg_stat_replication| SELECT s.pid,
17931792
w.replay_location,
17941793
w.sync_priority,
17951794
w.sync_state
1796-
FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn),
1797-
pg_authid u,
1798-
pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
1799-
WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
1795+
FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn)
1796+
JOIN pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) ON ((s.pid = w.pid)))
1797+
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
18001798
pg_stat_ssl| SELECT s.pid,
18011799
s.ssl,
18021800
s.sslversion AS version,
@@ -2155,8 +2153,8 @@ pg_user_mappings| SELECT u.oid AS umid,
21552153
ELSE NULL::text[]
21562154
END AS umoptions
21572155
FROM ((pg_user_mapping u
2158-
LEFTJOINpg_authid a ON ((a.oid =u.umuser)))
2159-
JOINpg_foreign_server s ON ((u.umserver =s.oid)));
2156+
JOINpg_foreign_server s ON ((u.umserver =s.oid)))
2157+
LEFTJOINpg_authid a ON ((a.oid =u.umuser)));
21602158
pg_views| SELECT n.nspname AS schemaname,
21612159
c.relname AS viewname,
21622160
pg_get_userbyid(c.relowner) AS viewowner,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp