Greenplum Database provides an administrative schema called gptoolkit that you can use to query the system catalogs, log files, and operating environment for system status information. The gptoolkit schema contains a number of views that you can access using SQL commands. The gptoolkit schema is accessible to all database users, although some objects may require superuser permissions. For convenience, you may want to add the gptoolkit schema to your schema search path.
For example: = ALTER ROLE myrole SET searchpath TO myschema,gptoolkit; This documentation describes the most useful views in gptoolkit. You may notice other objects (views, functions, and external tables) within the gptoolkit schema that are not described in this documentation (these are supporting objects to the views described in this section). The following views can help identify tables that need routine table maintenance ( VACUUM and/or ANALYZE). The VACUUM or VACUUM FULL command reclaims disk space occupied by deleted or obsolete rows. Because of the MVCC transaction concurrency model used in Greenplum Database, data rows that are deleted or updated still occupy physical space on disk even though they are not visible to any new transactions. Expired rows increase table size on disk and eventually slow down scans of the table. The ANALYZE command collects column-level statistics needed by the query optimizer.
I am using an INFILE statement to try and import a dataset, however I keep on getting this error for multiple lines: NOTE: Invalid data for CA_ENGLISH in line 21 320. Zstd, short for Zstandard, is a fast lossless compression algorithm, targeting real-time. If an error occurred (e.g. Invalid magic number, srcSize too small) note 1: a 0. A dictionary can be any arbitrary data segment (also called a prefix), or a.
Greenplum Database uses a cost-based query optimizer that relies on database statistics. Accurate statistics allow the query optimizer to better estimate selectivity and the number of rows retrieved by a query operation in order to choose the most efficient query plan. Gpstatsmissing view Column Description smischema Schema name. Smitable Table name. Smisize Does this table have statistics? False if the table does not have row count and row sizing statistics recorded in the system catalog, which may indicate that the table needs to be analyzed.
This will also be false if the table does not contain any rows. For example, the parent tables of partitioned tables are always empty and will always return a false result. Smicols Number of columns in the table. Smirecs Number of rows in the table. When a transaction accesses a relation (such as a table), it acquires a lock.
![Error Error](/uploads/1/2/5/3/125387663/913406403.png)
Depending on the type of lock acquired, subsequent transactions may have to wait before they can access the same relation. For more information on the types of locks, see 'Managing Data' in the Greenplum Database Administrator Guide. Greenplum Database resource queues (used for workload management) also use locks to control the admission of queries into the system. The gplocks. family of views can help diagnose queries and sessions that are waiting to access an object due to a lock. Gplocksonrelation view Column Description lorlocktype Type of the lockable object: relation, extend, page, tuple, transactionid, object, userlock, resource queue, or advisory lordatabase Object ID of the database in which the object exists, zero if the object is a shared object.
Lorrelname The name of the relation. Lorrelation The object ID of the relation. Lortransaction The transaction ID that is affected by the lock. Lorpid Process ID of the server process holding or awaiting this lock. NULL if the lock is held by a prepared transaction. Lormode Name of the lock mode held or desired by this process.
Lorgranted Displays whether the lock is granted (true) or not granted (false). Lorcurrentquery The current query in the session. Gplocksonresqueue view Column Description lorusename Name of the user executing the session. Lorrsqname The resource queue name. Lorlocktype Type of the lockable object: resource queue lorobjid The ID of the locked transaction.
Lortransaction The ID of the transaction that is affected by the lock. Lorpid The process ID of the transaction that is affected by the lock.
Lormode The name of the lock mode held or desired by this process. Lorgranted Displays whether the lock is granted (true) or not granted (false). Lorwaiting Displays whether or not the session is waiting.
The gptoolkit schema includes a set of diagnostic functions you can use to investigate the state of append-optimized tables. When an append-optimized table (or column-oriented append-optimized table) is created, another table is implicitly created, containing metadata about the current state of the table. The metadata includes information such as the number of records in each of the table's segments.
Append-optimized tables may have non-visible rows—rows that have been updated or deleted, but remain in storage until the table is compacted using VACUUM. The hidden rows are tracked using an auxiliary visibility map table, or visimap. The following functions let you access the metadata for append-optimized and column-oriented tables and view non-visible rows. Some of the functions have two versions: one that takes the oid of the table, and one that takes the name of the table.
The latter version has 'name' appended to the function name. gpaovisimapcompactioninfo output table Column Description content Greenplum Database segment ID. Datafile ID of the data file on the segment. Compactionpossible The value is either t or f. The value t indicates that the data in data file be compacted when a VACUUM operation is performed. The server configuration parameter gpappendonlycompactionthreshold affects this value.
Hiddentupcount In the data file, the number of hidden (deleted or updated) rows. Totaltupcount In the data file, the total number of rows. Percenthidden In the data file, the ratio (as a percentage) of hidden (deleted or updated) rows to total rows. Note: If you upgraded your cluster to Greenplum Database 4.3.5.0 or later, you can create the gpaovisimapcompactioninfo function in an existing Greenplum database by running run the script $GPHOME/share/postgresql/compactioninfo.sql once for each database. For example, to install the functions in database testdb, use this command: $ psql -d testdb -f $GPHOME/share/postgresql/compactioninfo.sql If you created the database with Greenplum Database 4.3.5.0 or later, this function is automatically created in the database.
gpaosegname output table Column Description segno The file segment number. Eof The effective end of file for this file segment. Tupcount The total number of tuples in the segment, including invisible tuples. Varblockcount The total number of varblocks in the file segment. Eofuncompressed The end of file if the file segment were uncompressed. Modcount The number of data modification operations.
State The state of the file segment. Indicates if the segment is active or ready to be dropped after compaction.
gpaoseghistory output table Column Description gptid The id of the tuple. Gpxmin The id of the earliest transaction.
Gpxminstatus Status of the gpxmin transaction. Gpxmincommit The commit distribution id of the gpxmin transaction. Gpxmax The id of the latest transaction. Gpxmaxstatus The status of the latest transaction. Gpxmaxcommit The commit distribution id of the gpxmax transaction. Gpcommandid The id of the query command.
Gpinfomask A bitmap containing state information. Gpupdatetid The ID of the newer tuple if the row is updated. Gpvisibility The tuple visibility status. Segno The number of the segment in the segment file. Tupcount The number of tuples, including hidden tuples.
Eof The effective end of file for the segment. Eofuncompressed The end of file for the segment if data were uncompressed. Modcount A count of data modifications.
State The status of the segment. gpaocsseghistory output table Column Description gptid The oid of the tuple. Gpxmin The earliest transaction. Gpxminstatus The status of the gpxmin transaction. Gpxmin Text representation of gpxmin.
Gpxmax The latest transaction. Gpxmaxstatus The status of the gpxmax transaction.
Gpxmax Text representation of gpmax. Gpcommandid ID of the command operating on the tuple. Gpinfomask A bitmap containing state information. Gpupdatetid The ID of the newer tuple if the row is updated. Gpvisibility The tuple visibility status. Segno The segment number in the segment file.
Columnnum The column number. Physicalsegno The segment containing data for the column. Tupcount The total number of tuples in the segment. Eof The effective end of file for the segment. Eofuncompressed The end of file for the segment if the data were uncompressed. Modcount A count of the data modification operations.
State The state of the segment. Gplogdatabase view Column Description logtime The timestamp of the log message. Loguser The name of the database user. Logdatabase The name of the database. Logpid The associated process id (prefixed with 'p'). Logthread The associated thread count (prefixed with 'th').
Loghost The segment or master host name. Logport The segment or master port. Logsessiontime Time session connection was opened. Logtransaction Global transaction id. Logsession The session identifier (prefixed with 'con'). Logcmdcount The command number within a session (prefixed with 'cmd').
Logsegment The segment content identifier (prefixed with 'seg' for primary or 'mir' for mirror. The master always has a content id of -1). Logslice The slice id (portion of the query plan being executed). Logdistxact Distributed transaction id. Loglocalxact Local transaction id. Logsubxact Subtransaction id.
Logseverity LOG, ERROR, FATAL, PANIC, DEBUG1 or DEBUG2. Logstate SQL state code associated with the log message. Logmessage Log or error message text. Logdetail Detail message text associated with an error message. Loghint Hint message text associated with an error message.
Logquery The internally-generated query text. Logquerypos The cursor index into the internally-generated query text. Logcontext The context in which this message gets generated.
![Invalid Invalid](https://ars.els-cdn.com/content/image/1-s2.0-S1383762118301693-gr28.jpg)
Logdebug Query string with full detail for debugging. Logcursorpos The cursor index into the query string. Logfunction The function in which this message is generated.
Logfile The log file in which this message is generated. Logline The line in the log file in which this message is generated. Logstack Full text of the stack trace associated with this message. Gplogsystem view Column Description logtime The timestamp of the log message. Loguser The name of the database user.
Logdatabase The name of the database. Logpid The associated process id (prefixed with 'p'). Logthread The associated thread count (prefixed with 'th'). Loghost The segment or master host name. Logport The segment or master port.
Logsessiontime Time session connection was opened. Logtransaction Global transaction id. Logsession The session identifier (prefixed with 'con'). Logcmdcount The command number within a session (prefixed with 'cmd'). Logsegment The segment content identifier (prefixed with 'seg' for primary or 'mir' for mirror. The master always has a content id of -1). Logslice The slice id (portion of the query plan being executed).
Logdistxact Distributed transaction id. Loglocalxact Local transaction id. Logsubxact Subtransaction id. Logseverity LOG, ERROR, FATAL, PANIC, DEBUG1 or DEBUG2. Logstate SQL state code associated with the log message. Logmessage Log or error message text. Logdetail Detail message text associated with an error message.
Loghint Hint message text associated with an error message. Logquery The internally-generated query text. Logquerypos The cursor index into the internally-generated query text. Logcontext The context in which this message gets generated.
Logdebug Query string with full detail for debugging. Logcursorpos The cursor index into the query string. Logfunction The function in which this message is generated.
Logfile The log file in which this message is generated. Logline The line in the log file in which this message is generated. Logstack Full text of the stack trace associated with this message. The purpose of resource queues is to limit the number of active queries in the system at any given time in order to avoid exhausting system resources such as memory, CPU, and disk I/O. All database users are assigned to a resource queue, and every statement submitted by a user is first evaluated against the resource queue limits before it can run. The gpresq.
family of views can be used to check the status of statements currently submitted to the system through their respective resource queue. Note that statements issued by superusers are exempt from resource queuing. Gpresqprioritystatement view Column Description rqpdatname The database name that the session is connected to. Rqpusename The user who issued the statement. Rqpsession The session ID.
Rqpcommand The number of the statement within this session (the command id and session id uniquely identify a statement). Rqppriority The resource queue priority for this statement (MAX, HIGH, MEDIUM, LOW). Rqpweight An integer value associated with the priority of this statement. Rqpquery The query text of the statement.
Gpresqueuestatus view Column Description queueid The ID of the resource queue. Rsqname The name of the resource queue.
Rsqcountlimit The active query threshold of the resource queue. A value of -1 means no limit. Rsqcountvalue The number of active query slots currently being used in the resource queue. Rsqcostlimit The query cost threshold of the resource queue. A value of -1 means no limit. Rsqcostvalue The total cost of all statements currently in the resource queue. Rsqmemorylimit The memory limit for the resource queue.
Rsqmemoryvalue The total memory used by all statements currently in the resource queue. Rsqwaiters The number of statements currently waiting in the resource queue. Rsqholders The number of statements currently running on the system from this resource queue.
Gpworkfileentries Column Type References Description commandcnt integer Command ID of the query. Content smallint The content identifier for a segment instance. Currentquery text Current query that the process is running. Datname name Greenplum database name. Directory text Path to the work file.
Optype text The query operator type that created the work file. Procpid integer Process ID of the server process.
Sessid integer Session ID. Size bigint The size of the work file in bytes. Numfiles bigint The number of files created. Slice smallint The query plan slice. The portion of the query plan that is being executed. State text The state of the query that created the work file.
Usename name Role name. Workmem integer The amount of memory allocated to the operator in KB. Gpworkfileusageperquery Column Type References Description commandcnt integer Command ID of the query.
Content smallint The content identifier for a segment instance. Currentquery text Current query that the process is running. Datname name Greenplum database name. Procpid integer Process ID of the server process. Sessid integer Session ID. Size bigint The size of the work file in bytes.
Numfiles bigint The number of files created. State text The state of the query that created the work file.
Usename name Role name. The gpsize. family of views can be used to determine the disk space usage for a distributed Greenplum Database, schema, table, or index. The following views calculate the total size of an object across all primary segments (mirrors are not included in the size calculations). The table and index sizing views list the relation by object ID (not by name).
To check the size of a table or index by name, you must look up the relation name ( relname) in the pgclass table. For example: SELECT relname as name, sotdsize as size, sotdtoastsize as toast, sotdadditionalsize as other FROM gpsizeoftabledisk as sotd, pgclass WHERE sotd.sotdoid=pgclass.oid ORDER BY relname. Gpsizeofpartitionandindexesdisk view Column Description sopaidparentoid The object ID of the parent table sopaidpartitionoid The object ID of the partition table sopaidpartitiontablesize The partition table size in bytes sopaidpartitionindexessize The total size of all indexes on this partition Sopaidparentschemaname The name of the parent schema Sopaidparenttablename The name of the parent table Sopaidpartitionschemaname The name of the partition schema sopaidpartitiontablename The name of the partition table. Gpsizeoftabledisk view Column Description sotdoid The object ID of the table sotdsize The size of the table in bytes. The size is only the main table size. The size does not include auxiliary objects such as oversized (toast) attributes, or additional storage objects for AO tables.
Sotdtoastsize The size of the TOAST table (oversized attribute storage), if there is one. Sotdadditionalsize Reflects the segment and block directory table sizes for append-optimized (AO) tables. Sotdschemaname The schema name sotdtablename The table name. Gpskewidlefractions view Column Description sifoid The object id of the table. Sifnamespace The namespace where the table is defined. Sifrelname The table name. Siffraction The percentage of the system that is idle during a table scan, which is an indicator of uneven data distribution or query processing skew.
For example, a value of 0.1 indicates 10% skew, a value of 0.5 indicates 50% skew, and so on. Tables that have more than 10% skew should have their distribution policies evaluated.
Is a robust library for unzipping. Design principles:. Follow the spec. Don't scan for local file headers. Read the central directory for file metadata. Don't block the JavaScript thread.
Use and provide async APIs. Keep memory usage under control.
Don't attempt to buffer entire files in RAM at once. Never crash (if used properly). Don't let malformed zip files bring down client applications who are trying to catch errors. Catch unsafe filenames entries. A zip file entry throws an error if its file name starts with '/' or /A-Za-z:// or if it contains '.' Path segments or ' ' (per the spec).
Currently has 97% test coverage.