PgSQL · 应用案例 · 什么情况下可能表膨胀

postgres=# select relname from pg_class   where reltablespace in   (select oid from pg_tablespace where spcname='pg_global')   and relkind='r';            relname          -----------------------   pg_authid   pg_subscription   pg_database   pg_db_role_setting   pg_tablespace   pg_pltemplate   pg_auth_members   pg_shdepend   pg_shdescription   pg_replication_origin   pg_shseclabel  (11 rows)

postgres=#   select relname from pg_class where relkind='r'   and relnamespace ='pg_catalog'::regnamespace   except   select relname from pg_class where reltablespace in   (select oid from pg_tablespace where spcname = 'pg_global')   and relkind='r';
         relname           -------------------------   pg_language   pg_sequence   pg_largeobject   pg_policy   pg_ts_template   pg_attrdef   pg_operator   pg_ts_parser   pg_depend   pg_attribute   pg_ts_config   pg_conversion   pg_inherits   pg_subscription_rel   pg_publication   pg_foreign_table   pg_largeobject_metadata   pg_ts_dict   pg_statistic   pg_init_privs   pg_opfamily   pg_type   pg_am   pg_default_acl   pg_proc   pg_index   pg_rewrite   pg_statistic_ext   pg_constraint   pg_opclass   pg_partitioned_table   pg_namespace   pg_trigger   pg_enum   pg_amop   pg_event_trigger   pg_collation   pg_foreign_server   pg_foreign_data_wrapper   pg_user_mapping   pg_description   pg_cast   pg_publication_rel   pg_aggregate   pg_transform   pg_extension   pg_class   pg_seclabel   pg_amproc   pg_range   pg_ts_config_map  (51 rows)

postgres=# select pg_create_logical_replication_slot('a','test_decoding');   pg_create_logical_replication_slot   ------------------------------------   (a,0/92C9C038)  (1 row)

postgres=# select * from pg_get_replication_slots();   slot_name |    plugin     | slot_type | datoid | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn   -----------+---------------+-----------+--------+-----------+--------+------------+------+--------------+-------------+---------------------   a         | test_decoding | logical   |  13585 | f         | f      |            |      |      1982645 | 0/92C9BFE8  | /92C9C038  (1 row)

postgres=# select pg_xact_commit_timestamp(xmin),pg_xact_commit_timestamp(catalog_xmin) from pg_get_replication_slots();  psql: ERROR:  could not get commit timestamp data  HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.

postgres=# select pg_walfile_name(restart_lsn) from pg_get_replication_slots();       pg_walfile_name        --------------------------   000000010000000000000092  (1 row)
postgres=# select * from pg_stat_file('pg_wal/000000010000000000000092');     size   |         access         |      modification      |         change         | creation | isdir   ----------+------------------------+------------------------+------------------------+----------+-------   16777216 | 2019-06-29 22:56:16+08 | 2019-07-01 09:50:16+08 | 2019-07-01 09:50:16+08 |          | f  (1 row)
postgres=# select * from pg_ls_waldir() where name='000000010000000000000092';             name           |   size   |      modification        --------------------------+----------+------------------------   000000010000000000000092 | 16777216 | 2019-07-01 09:50:16+08  (1 row)

postgres=# create table b(id int);  CREATE TABLE  postgres=# insert into b values (1);  INSERT  1

postgres=# select * from pg_logical_slot_get_changes('a',pg_current_wal_lsn(),1);      lsn     |   xid   |      data        ------------+---------+----------------   /92C9C0C | 1982645 | BEGIN 1982645   0/92CA4A40 | 1982645 | COMMIT 1982645  (2 rows)
postgres=# select * from pg_logical_slot_get_changes('a',pg_current_wal_lsn(),1);      lsn     |   xid   |                 data                    ------------+---------+---------------------------------------   /92CA4A78 | 1982646 | BEGIN 1982646   0/92CA4A78 | 1982646 | table public.b: INSERT: id[integer]:1   0/92CA4AE8 | 1982646 | COMMIT 1982646  (3 rows)

postgres=# delete from b;  DELETE 1

postgres=# vacuum verbose b;  psql: INFO:  vacuuming "public.b"  psql: INFO:  "b": removed 1 row versions in 1 pages  psql: INFO:  "b": found 1 removable,  nonremovable row versions in 1 out of 1 pages  DETAIL:   dead row versions cannot be removed yet, oldest xmin: 1982648  There were  unused item identifiers.  Skipped  pages due to buffer pins,  frozen pages.   pages are entirely empty.  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  psql: INFO:  "b": truncated 1 to  pages  DETAIL:  CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.09 s  VACUUM

postgres=# create table c (id int);  CREATE TABLE  postgres=# drop table c;  DROP TABLE  postgres=# create table c (id int);  CREATE TABLE  postgres=# drop table c;  DROP TABLE

postgres=# vacuum verbose pg_class;  psql: INFO:  vacuuming "pg_catalog.pg_class"  psql: INFO:  "pg_class": found  removable, 465 nonremovable row versions in 13 out of 13 pages  DETAIL:  2 dead row versions cannot be removed yet, oldest xmin: 1982646  There were 111 unused item identifiers.  Skipped  pages due to buffer pins,  frozen pages.   pages are entirely empty.  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  VACUUM

postgres=# vacuum verbose pg_attribute ;  psql: INFO:  vacuuming "pg_catalog.pg_attribute"  psql: INFO:  "pg_attribute": found  removable, 293 nonremovable row versions in 6 out of 62 pages  DETAIL:  14 dead row versions cannot be removed yet, oldest xmin: 1982646  There were 55 unused item identifiers.  Skipped  pages due to buffer pins, 55 frozen pages.   pages are entirely empty.  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  VACUUM

postgres=# begin;  BEGIN  postgres=# delete from a;  DELETE 1

db1=# create table b(id int);  CREATE TABLE  db1=# insert into b values (1);  INSERT  1  db1=# delete from b;  DELETE 1  db1=# vacuum verbose b;  psql: INFO:  vacuuming "public.b"  psql: INFO:  "b": removed 1 row versions in 1 pages  psql: INFO:  "b": found 1 removable,  nonremovable row versions in 1 out of 1 pages  DETAIL:   dead row versions cannot be removed yet, oldest xmin: 1982671  There were  unused item identifiers.  Skipped  pages due to buffer pins,  frozen pages.   pages are entirely empty.  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  psql: INFO:  "b": truncated 1 to  pages  DETAIL:  CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.09 s  VACUUM

switch (HeapTupleSatisfiesVacuum(&tuple, OldestXmin, buf))  {    case HEAPTUPLE_DEAD:
    /*       * Ordinarily, DEAD tuples would have been removed by       * heap_page_prune(), but it's possible that the tuple       * state changed since heap_page_prune() looked.  In       * particular an INSERT_IN_PROGRESS tuple could have       * changed to DEAD if the inserter aborted.  So this       * cannot be considered an error condition.       *       * If the tuple is HOT-updated then it must only be       * removed by a prune operation; so we keep it just as if       * it were RECENTLY_DEAD.  Also, if it's a heap-only       * tuple, we choose to keep it, because it'll be a lot       * cheaper to get rid of it in the next pruning pass than       * to treat it like an indexed tuple. Finally, if index       * cleanup is disabled, the second heap pass will not       * execute, and the tuple will not get removed, so we must       * treat it like any other dead tuple that we choose to       * keep.       *       * If this were to happen for a tuple that actually needed       * to be deleted, we'd be in trouble, because it'd       * possibly leave a tuple below the relation's xmin       * horizon alive.  heap_prepare_freeze_tuple() is prepared       * to detect that case and abort the transaction,       * preventing corruption.       */      if (HeapTupleIsHotUpdated(&tuple) ||          HeapTupleIsHeapOnly(&tuple) ||          params->index_cleanup == VACOPT_TERNARY_DISABLED)        nkeep += 1;      else        tupgone = true; /* we can delete the tuple */      all_visible = false;      break;
  case HEAPTUPLE_RECENTLY_DEAD:
    /*       * If tuple is recently deleted then we must not remove it       * from relation.       */      nkeep += 1;      all_visible = false;      break;

*       HeapTupleSatisfiesVacuum()   *                visible to any running transaction, used by VACUUM

/*   * HeapTupleSatisfiesVacuum   *   *      Determine the status of tuples for VACUUM purposes.  Here, what   *      we mainly want to know is if a tuple is potentially visible to *any*   *      running transaction.  If so, it can't be removed yet by VACUUM.   *   * OldestXmin is a cutoff XID (obtained from GetOldestXmin()).  Tuples   * deleted by XIDs >= OldestXmin are deemed "recently dead"; they might   * still be visible to some open transaction, so we can't remove them,   * even if we see that the deleting transaction has committed.   */  HTSV_Result  HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin,                                                   Buffer buffer)  

        /*           * Deleter committed, but perhaps it was recent enough that some open           * transactions could still see the tuple.           */          if (!TransactionIdPrecedes(HeapTupleHeaderGetRawXmax(tuple), OldestXmin))                  return HEAPTUPLE_RECENTLY_DEAD;  

                else if (TransactionIdDidCommit(xmax))                  {                          /*                           * The multixact might still be running due to lockers.  If the                           * updater is below the xid horizon, we have to return DEAD                           * regardless -- otherwise we could end up with a tuple where the                           * updater has to be removed due to the horizon, but is not pruned                           * away.  It's not a problem to prune that tuple, because any                           * remaining lockers will also be present in newer tuple versions.                           */                          if (!TransactionIdPrecedes(xmax, OldestXmin))                                  return HEAPTUPLE_RECENTLY_DEAD;  
                        return HEAPTUPLE_DEAD;                  }

Hot Standby feedback message (F)  Byte1('h')  Identifies the message as a Hot Standby feedback message.  
Int64  The client's system clock at the time of transmission, as microseconds since midnight on 2000-01-01.  
Int32  The standby's current global xmin, excluding the catalog_xmin from any replication slots. If both this value and the following catalog_xmin are  this is treated as a notification that Hot Standby feedback will no longer be sent on this connection. Later non-zero messages may reinitiate the feedback mechanism.  
Int32  The epoch of the global xmin xid on the standby.  
Int32  The lowest catalog_xmin of any replication slots on the standby. Set to  if no catalog_xmin exists on the standby or if hot standby feedback is being disabled.  
Int32  The epoch of the catalog_xmin xid on the standby.