Search PostgreSQL documentation

Event Support FunctionsManual ChaptersPG1832.14.3. Event Support Functions PQregisterEventProc # Registers an event callback procedure with libpq. int PQregisterEventProc(PGconn *conn, PGEventProc proc, const char *name, void *passT…Event Support Functions Event SystemManual ChaptersPG1832.14. Event System libpq's event system is designed to notify registered event handlers about interesting libpq events, such as the creation or destruction of PGconn and PGresult objects. A…Event System Event Trigger FunctionsManual ChaptersPG189.30. Event Trigger Functions PostgreSQL provides these helper functions to retrieve information from event triggers. For more information about event triggers, see Chapter 38.Event Trigger Functions Event Trigger Functions in PL/TclManual ChaptersPG1842.7. Event Trigger Functions in PL/Tcl Event trigger functions can be written in PL/Tcl. PostgreSQL requires that a function that is to be called as an event trigger must be declared as a f…Event Trigger Functions in PL/Tcl Event TriggersManual ChaptersPG18Chapter 38. Event Triggers To supplement the trigger mechanism discussed in Chapter 37, PostgreSQL also provides event triggers. Unlike regular triggers, which are attached to a single table…Event Triggers Event Triggers in Aborted TransactionsManual ChaptersPG1838.1.6. Event Triggers in Aborted Transactions Event triggers (like other functions) cannot be executed in an aborted transaction. Thus, if a DDL command fails with an error, any associated …Event Triggers in Aborted Transactions Event TypesManual ChaptersPG1832.14.1. Event Types The enum PGEventId names the types of events handled by the event system. All its values have names beginning with PGEVT. For each event type, there is a corresponding e…Event Types event_managerExtensionsExtensionsLoosely coupled PostgreSQL event trigger and queue system with synchronous or asynchronous processing. async event-trigger listen-notify queue event_manager FeaturesFeatures · Extensions event_sourceConfigurationReferenceevent_source Reporting and Logging 10 When logging to event log is enabled, this parameter determines the program name used to identify PostgreSQL messages in the log. The default is Postgre…Configuration · Reporting and Logging event_triggerData TypesPG18event_trigger An event trigger function is declared to return event_trigger.Pseudo-Types event_triggersConfigurationReferenceevent_triggers Client Connection Defaults 17 Allow temporarily disabling execution of event triggers in order to troubleshoot and repair faulty event triggers. All event triggers will be dis…Configuration · Client Connection Defaults everyFunctionsReferenceevery Aggregate Functions 10 equivalent to bool_and every ( expression ) → bool equivalent to bool_and 11 12 13 This is the SQL standard's equivalent to bool_and. every ( boolean ) → boolean…Functions · Aggregate Functions evrExtensionsExtensionsPostgreSQL extension for RPM epoch/version/release datatype parsing. postgresql_evr Data typesData types · Extensions Examining Index UsageManual ChaptersPG1811.12. Examining Index Usage Although indexes in PostgreSQL do not need maintenance or tuning, it is still important to check which indexes are actually used by the real-life query workload.…Examining Index Usage ExampleManual ChaptersPG18Example To reproduce the build configuration of the current PostgreSQL installation, run the following command: eval ./configure `pg_config --configure` The output of pg_config --configure c…Example ExampleManual ChaptersPG18F.3.2. Example postgres=# LOAD 'auto_explain'; postgres=# SET auto_explain.log_min_duration = 0; postgres=# SET auto_explain.log_analyze = true; postgres=# SELECT count(*) FROM pg_class, pg_…Example ExampleManual ChaptersPG185.12.2.1. Example Suppose we are constructing a database for a large ice cream company. The company measures peak temperatures every day as well as ice cream sales in each region. Conceptual…Example ExampleManual ChaptersPG185.12.3.1. Example This example builds a partitioning structure equivalent to the declarative partitioning example above. Use the following steps: Create the “root” table, from which all of t…Example ExampleManual ChaptersPG18F.19.3. Example -- a message can be in one or more “sections” CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...); -- create specialized index with signature length of 32 bytes C…Example ExampleManual ChaptersPG18F.22.4. Example This example uses the following data (also available in file contrib/ltree/ltreetest.sql in the source distribution): CREATE TABLE test (path ltree); INSERT INTO test VALUES …Example Example ProgramManual ChaptersPG1833.5. Example Program Example 33.1 is a sample program which shows how the large object interface in libpq can be used. Parts of the program are commented out but are left in the source for …Example Program Example ProgramsManual ChaptersPG1832.23. Example Programs These examples and others can be found in the directory src/test/examples in the source code distribution. Example 32.1. libpq Example Program 1 /* * src/test/example…Example Programs Example UsageManual ChaptersPG18F.7.1. Example Usage CREATE TABLE test (a int4); -- create index CREATE INDEX testidx ON test USING GIN (a); -- query SELECT * FROM test WHERE a < 10;Example Usage Example UsageManual ChaptersPG18F.8.1. Example Usage Simple example using btree_gist instead of btree: CREATE TABLE test (a int4); -- create index CREATE INDEX testidx ON test USING GIST (a); -- query SELECT * FROM test WH…Example Usage ExamplesManual ChaptersPG18Examples To cluster the database test: $ clusterdb test To cluster a single table foo in a database named xyzzy: $ clusterdb --table=foo xyzzyExamples ExamplesManual ChaptersPG18Examples To create the database demo using the default database server: $ createdb demo To create the database demo using the server on host eden, port 5000, using the template0 template dat…Examples ExamplesManual ChaptersPG18Examples To create a user joe on the default database server: $ createuser joe To create a user joe on the default database server with prompting for some additional attributes: $ createuser…Examples ExamplesManual ChaptersPG18Examples To destroy the database demo on the default database server: $ dropdb demo To destroy the database demo using the server on host eden, port 5000, with verification and a peek at the…Examples ExamplesManual ChaptersPG18Examples To remove user joe from the default database server: $ dropuser joe To remove user joe using the server on host eden, port 5000, with verification and a peek at the underlying comma…Examples ExamplesManual ChaptersPG18Examples If you have an embedded SQL C source file named prog1.pgc, you can create an executable program using the following sequence of commands: ecpg prog1.pgc cc -I/usr/local/pgsql/includ…Examples ExamplesManual ChaptersPG18ExamplesExamples ExamplesManual ChaptersPG18Examples To dump all databases: $ pg_dumpall > db.out To restore database(s) from this file, you can use: $ psql -X -f db.out -d postgres It is not important which database you connect to he…Examples ExamplesManual ChaptersPG18Examples Standard Usage: $ pg_isready /tmp:5432 - accepting connections $ echo $? 0 Running with connection parameters to a PostgreSQL cluster in startup: $ pg_isready -h localhost -p 5433 l…Examples ExamplesManual ChaptersPG18Examples To create a base backup of the server at mydbserver and store it in the local directory /usr/local/pgsql/data: $ pg_basebackup -h mydbserver -D /usr/local/pgsql/data To create a bac…Examples ExamplesManual ChaptersPG18Examples To create a logical replica for databases hr and finance from a physical replica at foo: $ pg_createsubscriber -D /usr/local/pgsql/data -P "host=foo" -d hr -d financeExamples ExamplesManual ChaptersPG18Examples To dump a database called mydb into an SQL-script file: $ pg_dump mydb > db.sql To reload such a script into a (freshly created) database named newdb: $ psql -X -d newdb -f db.sql T…Examples ExamplesManual ChaptersPG18Examples To stream the write-ahead log from the server at mydbserver and store it in the local directory /usr/local/pgsql/archive: $ pg_receivewal -h mydbserver -D /usr/local/pgsql/archiveExamples ExamplesManual ChaptersPG18Examples See Section 47.1 for an example.Examples ExamplesManual ChaptersPG18Examples Assume we have dumped a database called mydb into a custom-format dump file: $ pg_dump -Fc mydb > db.dump To drop the database and recreate it from the dump: $ dropdb mydb $ pg_rest…Examples ExamplesManual ChaptersPG18Examples To create a base backup of the server at mydbserver and verify the integrity of the backup: $ pg_basebackup -h mydbserver -D /usr/local/pgsql/data $ pg_verifybackup /usr/local/pgsql…Examples