SQL Objects

This chapter introduces SQL objects additionally provided by PG-Strom.

Device Information

Function Result Description
gpu_device_name(int = 0) text It tells name of the specified GPU device.
gpu_global_memsize(int = 0) bigint It tells amount of the specified GPU device in bytes.
gpu_max_blocksize(int = 0) int It tells maximum block-size on the specified GPU device. 1024, in the currently supported GPU models.
gpu_warp_size(int = 0) int It tells warp-size on the specified GPU device. 32, in the currently supported GPU models.
gpu_max_shared_memory_perblock(int = 0) int It tells maximum shared memory size per block on the specified GPU device.
gpu_num_registers_perblock(int = 0) int It tells total number of registers per block on the specified GPU device.
gpu_num_multiptocessors(int = 0) int It tells number of SM(Streaming Multiprocessor) units on the specified GPU device.
gpu_num_cuda_cores(int = 0) int It tells number of CUDA cores on the specified GPU device.
gpu_cc_major(int = 0) int It tells major CC(Compute Capability) version of the specified GPU device.
gpu_cc_minor(int = 0) int It tells minor CC(Compute Capability) version of the specified GPU device.
gpu_pci_id(int = 0) int It tells PCI bus-id of the specified GPU device.

Array-based matrix support

You can use array data type of PostgreSQL to deliver matrix-data for PL/CUDA functions. A two-dimensional array of fixed-length boolean/numeric values without NULL has flat data structure (expect for the array header). It allows to identify the address of elements by indexes of the matrix uniquely. PG-Strom provides several SQL functions to handle array-based matrix.

Type cast

destination type source type description
int[] bit convert bit-string to 32bit integer array. Unaligned bits are filled up by 0.
bit int[] convert 32bit integer to bit-string

Array-based matrix functions

functions/operators result description
array_matrix_validation(anyarray) bool It checks whether the supplied array satisfies the requirement of array-based matrix.
array_matrix_height(anyarray) int It tells height of the array-based matrix.
array_matrix_width(anyarray) int It tells width of the array-based matrix.
array_vector_rawsize(regtype,int) bigint It tells expected size if N-items vector is created with the specified type.
array_matrix_rawsize(regtype,int,int) bigint It tells expected size if HxW matrix is created with the specified type.
array_cube_rawsize(regtype,int,int,int) bigint It tells expected size if HxWxD cube is created with the specified type.
type_len(regtype) bigint It tells unit length of the specified type.
composite_type_rawsize(LEN,...) bigint It tells expected size of the composite type if constructed with the specified data-length order. We expect to use the function with type_len()
LEN is either of int,bigint
matrix_unnest(anyarray) record It is a function to return set, to fetch rows from top of the supplied array-based matrix. PostgreSQL has no type information of the record, so needs to give type information using ROW() clause.
rbind(MATRIX,MATRIX) MATRIX It combines two array-based matrix vertically.
MATRIX is array type of any of bool,int2,int4,int8,float4,float8
rbind(TYPE,MATRIX) MATRIX It adds a scalar value on head of the array-based matrix. If multiple columns exist, the scalar value shall be set on all the column of the head row.
MATRIX is array type of any of bool,int2,int4,int8,float4,float8.
TYPE is element of MATRIX
rbind(MATRIX,TYPE) MATRIX It adds a scalar value on bottom of the array-based matrix. If multiple columns exist, the scalar value shall be set on all the column of the bottom row.MATRIX is array type of any of bool,int2,int4,int8,float4,float8
TYPE is element type of MATRIX
cbind(MATRIX,MATRIX) MATRIX It combines two array-based matrix horizontally.
MATRIX is array type of any of bool,int2,int4,int8,float4,float8
cbind(TYPE,MATRIX) MATRIX It adds a scalar value on left of the array-based matrix. If multiple rows exist, the scalar value shall be set on all the rows of the left column.
MATRIX is array type of any of bool,int2,int4,int8,float4,float8
TYPE is element type of MATRIX
cbind(MATRIX,TYPE) MATRIX It adds a scalar value on right of the array-based matrix. If multiple rows exist, the scalar value shall be set on all the rows of the right column.
MATRIX is array type of any of bool,int2,int4,int8,float4,float8.
TYPE is element type of MATRIX
transpose(MATRIX) MATRIX It transposes the array-based matrix.
MATRIX is array type of any of bool,int2,int4,int8,float4,float8

Aggregate functions

functions/operators result description
array_matrix(TYPE,...) TYPE[] An aggregate function with varidic arguments. It produces M-cols x N-rows array-based matrix if N-rows were supplied with M-columns.
TYPE is any of bool,int2,int4,int8,float4,float8
array_matrix(bit) bit[] An aggregate function to produce int4[] array-based matrix. It considers bit-string as a set of 32bits integer values.
rbind(MATRIX) MATRIX An aggregate function to combine the supplied array-based matrix vertically.
MATRIX is array type of any of bool,int2,int4,int8,float4,float8
cbind(MATRIX) MATRIX An aggregate function to combine the supplied array-based matrix horizontally.MATRIX is array type of any of bool,int2,int4,int8,float4,float8

Miscellaneous functions

Function Result Description
pgstrom_ccache_enabled(regclass) text Enables in-memory columnar cache on the specified table.
pgstrom_ccache_disabled(regclass) text Disables in-memory columnar cache on the specified table.
pgstrom_ccache_prewarm(regclass) int Build in-memory columnar cache on the specified table synchronously, until cache usage is less than the threshold.
Function Result Description
gstore_fdw_format(reggstore) text It tells internal format of the specified gstore_fdw foreign table.
gstore_fdw_nitems(reggstore) bigint It tells number of rows of the specified gstore_fdw foreign table.
gstore_fdw_nattrs(reggstore) bigint It tells number of columns of the specified gstore_fdw foreign table.
gstore_fdw_rawsize(reggstore) bigint It tells raw size of the specified gstore_fdw foreign table in bytes.
Function Result Description
gstore_export_ipchandle(reggstore) bytea It tells IPC-handle of the GPU device memory region of the specified gstore_fdw foreign table.
lo_import_gpu(int, bytea, bigint, bigint, oid=0) oid It maps GPU device memory region acquired by external application, then import its contents into a largeobject.
lo_export_gpu(oid, int, bytea, bigint, bigint) bigint It maps GPU device memory region acquired by external application, then export contents of the specified largeobject into the region.
Function Result Description
plcuda_kernel_max_blocksz int It tells maximum block size of the GPU kernel of PL/CUDA function when it is called as its helper.
plcuda_kernel_static_shmsz() int It tells size of the statically acquired shared memory per block by the GPU kernel of PL/CUDA function when it is called as its helper.
plcuda_kernel_dynamic_shmsz() int It tells size of the dynamic shared memory per block, which GPU kernel of the PL/CUDA function can allocate, when it is called as its helper.
plcuda_kernel_const_memsz() int It tells size of the constant memory acquired by the GPU kernel of PL/CUDA function, when it is called as its helper.
plcuda_kernel_local_memsz() int It tells size of the local memory per thread acquired by the GPU kernel of PL/CUDA function, when it is called as its helper.
Function Result Description
pgstrom.license_validation() text It validates commercial subscription.
pgstrom.license_query() text It shows the active commercial subscription.

System View

PG-Strom provides several system view to export its internal state for users or applications. The future version may add extra fields here. So, it is not recommended to reference these information schemas using SELECT * FROM ....

pgstrom.device_info

pgstrom.device_into system view exports device attributes of the GPUs recognized by PG-Strom. GPU has different specification for each model, like number of cores, capacity of global memory, maximum number of threads and etc, user's software should be optimized according to the information if you try raw GPU programming with PL/CUDA functions.

Name Data Type Description
device_nr int GPU device number
aindex int Attribute index
attribute text Attribute name
value text Value of the attribute

pgstrom.device_preserved_meminfo

pgstrom.device_preserved_meminfo system view exports information of the preserved device memory; which can be shared multiple PostgreSQL backend. Right now, only gstore_fdw uses this feature.

Name Data Type Description
device_nr int GPU device number
handle bytea IPC handle of the preserved device memory
owner regrole Owner of the preserved device memory
length bigint Length of the preserved device memory in bytes
ctime timestamp with time zone Timestamp when the preserved device memory is created

pgstrom.ccache_info

pgstrom.ccache_info system view exports attribute of the columnar-cache chunks (128MB unit for each).

Name Data Type Description
database_id oid Database Id
table_id regclass Table Id
block_nr int Head block-number of the chunk
nitems bigint Number of rows in the chunk
length bigint Raw size of the cached chunk
ctime timestamp with time zone Timestamp of the chunk creation
atime timestamp with time zone Timestamp of the least access to the chunk

pgstrom.ccache_builder_info

pgstrom.ccache_builder_info system view exports information of asynchronous builder process of columnar cache.

Name Data Type Description
builder_id int Asynchronous builder Id of columnar cache
state text State of the builder process (shutdown, startup, loading or sleep)
database_id oid Database Id where builder process is assigned on
table_id regclass Table Id where the builder process is scanning on, if state is loading.
block_nr int Block number where the builder process is scanning on, if state is loading.