Search PostgreSQL documentation

role_table_grantsManual ChaptersPG1835.37. role_table_grants The view role_table_grants identifies all privileges granted on tables or views where the grantor or grantee is a currently enabled role. Further information can be …role_table_grants role_udt_grantsManual ChaptersPG1835.38. role_udt_grants The view role_udt_grants is intended to identify USAGE privileges granted on user-defined types where the grantor or grantee is a currently enabled role. Further infor…role_udt_grants role_usage_grantsManual ChaptersPG1835.39. role_usage_grants The view role_usage_grants identifies USAGE privileges granted on various kinds of objects where the grantor or grantee is a currently enabled role. Further informat…role_usage_grants ROLLBACKManual ChaptersPG18ROLLBACK ROLLBACK — abort the current transaction Synopsis ROLLBACK [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]ROLLBACK ROLLBACK PREPAREDManual ChaptersPG18ROLLBACK PREPARED ROLLBACK PREPARED — cancel a transaction that was earlier prepared for two-phase commit Synopsis ROLLBACK PREPARED transaction_idROLLBACK PREPARED ROLLBACK TO SAVEPOINTManual ChaptersPG18ROLLBACK TO SAVEPOINT ROLLBACK TO SAVEPOINT — roll back to a savepoint Synopsis ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_nameROLLBACK TO SAVEPOINT Routine Database Maintenance TasksManual ChaptersPG18Chapter 24. Routine Database Maintenance Tasks PostgreSQL, like any database software, requires that certain tasks be performed regularly to achieve optimum performance. The tasks discussed …Routine Database Maintenance Tasks Routine ReindexingManual ChaptersPG1824.2. Routine Reindexing In some situations it is worthwhile to rebuild indexes periodically with the REINDEX command or a series of individual rebuilding steps. B-tree index pages that have…Routine Reindexing Routine VacuumingManual ChaptersPG1824.1. Routine Vacuuming PostgreSQL databases require periodic maintenance known as vacuuming. For many installations, it is sufficient to let vacuuming be performed by the autovacuum daemon,…Routine Vacuuming routine_column_usageManual ChaptersPG1835.40. routine_column_usage The view routine_column_usage identifies all columns that are used by a function or procedure, either in the SQL body or in parameter default expressions. (This o…routine_column_usage routine_privilegesManual ChaptersPG1835.41. routine_privileges The view routine_privileges identifies all privileges granted on functions to a currently enabled role or by a currently enabled role. There is one row for each com…routine_privileges routine_routine_usageManual ChaptersPG1835.42. routine_routine_usage The view routine_routine_usage identifies all functions or procedures that are used by another (or the same) function or procedure, either in the SQL body or in …routine_routine_usage routine_sequence_usageManual ChaptersPG1835.43. routine_sequence_usage The view routine_sequence_usage identifies all sequences that are used by a function or procedure, either in the SQL body or in parameter default expressions. (…routine_sequence_usage routine_table_usageManual ChaptersPG1835.44. routine_table_usage The view routine_table_usage is meant to identify all tables that are used by a function or procedure. This information is currently not tracked by PostgreSQL. Tab…routine_table_usage routinesManual ChaptersPG1835.45. routines The view routines contains all functions and procedures in the current database. Only those functions and procedures are shown that the current user has access to (by way of …routines Row and Array ComparisonsManual ChaptersPG189.25. Row and Array Comparisons This section describes several specialized constructs for making multiple comparisons between groups of values. These forms are syntactically related to the s…Row and Array Comparisons Row Constructor ComparisonManual ChaptersPG189.25.5. Row Constructor Comparison row_constructor operator row_constructor Each side is a row constructor, as described in Section 4.2.13. The two row constructors must have the same number…Row Constructor Comparison Row ConstructorsManual ChaptersPG184.2.13. Row Constructors A row constructor is an expression that builds a row value (also called a composite value) using values for its member fields. A row constructor consists of the key …Row Constructors Row Estimation ExamplesManual ChaptersPG1869.1. Row Estimation Examples The examples shown below use tables in the PostgreSQL regression test database. Note also that since ANALYZE uses random sampling while producing statistics, th…Row Estimation Examples Row Filter RulesManual ChaptersPG1829.4.1. Row Filter Rules Row filters are applied before publishing the changes. If the row filter evaluates to false or NULL then the row is not replicated. The WHERE clause expression is ev…Row Filter Rules Row FiltersManual ChaptersPG1829.4. Row Filters By default, all data from all published tables will be replicated to the appropriate subscribers. The replicated data can be reduced by using a row filter. A user might cho…Row Filters Row Locking in Foreign Data WrappersManual ChaptersPG1858.5. Row Locking in Foreign Data Wrappers If an FDW's underlying storage mechanism has a concept of locking individual rows to prevent concurrent updates of those rows, it is usually worthw…Row Locking in Foreign Data Wrappers Row Ordering DifferencesManual ChaptersPG1831.2.5. Row Ordering Differences You might see differences in which the same rows are output in a different order than what appears in the expected file. In most cases this is not, strictly …Row Ordering Differences Row Security PoliciesManual ChaptersPG185.9. Row Security Policies In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies that restrict, on a per-user basis, which rows can …Row Security Policies Row TypesManual ChaptersPG1841.3.4. Row Types name table_name%ROWTYPE; name composite_type_name; A variable of a composite type is called a row variable (or row-type variable). Such a variable can hold a whole row of a…Row Types Row-Level LocksManual ChaptersPG1813.3.2. Row-Level Locks In addition to table-level locks, there are row-level locks, which are listed as below with the contexts in which they are used automatically by PostgreSQL. See Table…Row-Level Locks Rules and Command StatusManual ChaptersPG1839.6. Rules and Command Status The PostgreSQL server returns a command status string, such as INSERT 149592 1, for each command it receives. This is simple enough when there are no rules inv…Rules and Command Status Rules and PrivilegesManual ChaptersPG1839.5. Rules and Privileges Due to rewriting of queries by the PostgreSQL rule system, other tables/views than those used in the original query get accessed. When update rules are used, this …Rules and Privileges Rules on INSERT, UPDATE, and DELETEManual ChaptersPG1839.4. Rules on INSERT, UPDATE, and DELETE Rules that are defined on INSERT, UPDATE, and DELETE are significantly different from the view rules described in the previous sections. First, thei…Rules on INSERT, UPDATE, and DELETE Rules Versus TriggersManual ChaptersPG1839.7. Rules Versus Triggers Many things that can be done using triggers can also be implemented using the PostgreSQL rule system. One of the things that cannot be implemented by rules are so…Rules Versus Triggers Run ANALYZE AfterwardsManual ChaptersPG1814.4.8. Run ANALYZE Afterwards Whenever you have significantly altered the distribution of data within a table, running ANALYZE is strongly recommended. This includes bulk loading large amou…Run ANALYZE Afterwards Run-time StatisticsManual ChaptersPG1819.9. Run-time StatisticsRun-time Statistics Running SQL CommandsManual ChaptersPG1834.3. Running SQL Commands Any SQL command can be run from within an embedded SQL application. Below are some examples of how to do that.Running SQL Commands Running the TestsManual ChaptersPG1831.1. Running the Tests The regression tests can be run against an already installed and running server, or using a temporary installation within the build tree. Furthermore, there is a “par…Running the Tests Running the Tests Against a Temporary InstallationManual ChaptersPG1831.1.1. Running the Tests Against a Temporary Installation To run the parallel regression tests after building but before installation, type: make check in the top-level directory. (Or you c…Running the Tests Against a Temporary Installation Running the Tests Against an Existing InstallationManual ChaptersPG1831.1.2. Running the Tests Against an Existing Installation To run the tests after installation (see Chapter 17), initialize a data directory and start the server as explained in Chapter 18, …Running the Tests Against an Existing Installation s2k-cipher-algoManual ChaptersPG18F.26.3.8.10. s2k-cipher-algo Which cipher to use for encrypting separate session key. Values: bf, aes, aes128, aes192, aes256 Default: use cipher-algo Applies to: pgp_sym_encrypts2k-cipher-algo s2k-countManual ChaptersPG18F.26.3.8.8. s2k-count The number of iterations of the S2K algorithm to use. It must be a value between 1024 and 65011712, inclusive. Default: A random value between 65536 and 253952 Applies …s2k-count s2k-digest-algoManual ChaptersPG18F.26.3.8.9. s2k-digest-algo Which digest algorithm to use in S2K calculation. Values: md5, sha1 Default: sha1 Applies to: pgp_sym_encrypts2k-digest-algo s2k-modeManual ChaptersPG18F.26.3.8.7. s2k-mode Which S2K algorithm to use. Values: 0 - Without salt. Dangerous! 1 - With salt but with fixed iteration count. 3 - Variable iteration count. Default: 3 Applies to: pgp_s…s2k-mode