decred.org/dcrdex@v1.0.5/server/db/driver/pg/internal/system.go (about)

     1  package internal
     2  
     3  // The following queries retrieve various system settings and other system
     4  // information from the database server.
     5  const (
     6  	// IndexExists checks if an index with a given name in certain namespace
     7  	// (schema) exists.
     8  	IndexExists = `SELECT 1
     9  		FROM   pg_class c
    10  		JOIN   pg_namespace n ON n.oid = c.relnamespace
    11  		WHERE  c.relname = $1 AND n.nspname = $2;`
    12  
    13  	// IndexIsUnique checks if an index with a given name in certain namespace
    14  	// (schema) exists, and is a UNIQUE index.
    15  	IndexIsUnique = `SELECT indisunique
    16  		FROM   pg_index i
    17  		JOIN   pg_class c ON c.oid = i.indexrelid
    18  		JOIN   pg_namespace n ON n.oid = c.relnamespace
    19  		WHERE  c.relname = $1 AND n.nspname = $2`
    20  
    21  	// RetrieveSysSettingsConfFile retrieves system settings that are set by a
    22  	// configuration file.
    23  	RetrieveSysSettingsConfFile = `SELECT name, setting, unit, short_desc, source, sourcefile, sourceline
    24  		FROM pg_settings
    25  		WHERE source='configuration file';`
    26  
    27  	// RetrieveSysSettingsServer retrieves system settings related to the
    28  	// postgres server configuration.
    29  	RetrieveSysSettingsServer = `SELECT name, setting, unit, short_desc, source, sourcefile, sourceline
    30  		FROM pg_settings
    31  		WHERE name='max_connections'
    32  			OR name='timezone'
    33  			OR name='max_files_per_process'
    34  			OR name='dynamic_shared_memory_type'
    35  			OR name='unix_socket_directories'
    36  			OR name='port'
    37  			OR name='data_directory'
    38  			OR name='config_file'
    39  			OR name='listen_address';`
    40  
    41  	// RetrieveSysSettingsPerformance retrieves postgres performance-related
    42  	// settings.
    43  	RetrieveSysSettingsPerformance = `SELECT name, setting, unit, short_desc, source, sourcefile, sourceline
    44  		FROM pg_settings
    45  		WHERE name='synchronous_commit'
    46  			OR name='max_connections'
    47  			OR name='shared_buffers'
    48  			OR name='effective_cache_size'
    49  			OR name='maintenance_work_mem'
    50  			OR name='work_mem'
    51  			OR name='autovacuum_work_mem'
    52  			OR name='wal_buffers'
    53  			OR name='min_wal_size'
    54  			OR name='max_wal_size'
    55  			OR name='wal_level'
    56  			OR name='checkpoint_completion_target'
    57  			OR name='default_statistics_target'
    58  			OR name='random_page_cost'
    59  			OR name='seq_page_cost'
    60  			OR name='effective_io_concurrency'
    61  			OR name='max_worker_processes'
    62  			OR name='max_parallel_workers_per_gather'
    63  			OR name='max_parallel_workers'
    64  			OR name='autovacuum'
    65  			OR name='fsync'
    66  			OR name='full_page_writes'
    67  			OR name='huge_pages'
    68  			OR name='temp_buffers'
    69  			OR name='max_stack_depth'
    70  			OR name='force_parallel_mode'
    71  			OR name='jit'
    72  			OR name='jit_provider';`
    73  
    74  	// RetrieveSyncCommitSetting retrieves just the synchronous_commit setting.
    75  	RetrieveSyncCommitSetting = `SELECT setting FROM pg_settings WHERE name='synchronous_commit';`
    76  
    77  	// RetrievePGVersion retrieves the version string from the database process.
    78  	RetrievePGVersion = `SELECT version();`
    79  
    80  	// CreateSchema creates a database schema.
    81  	CreateSchema = `CREATE SCHEMA IF NOT EXISTS %s;`
    82  )