PRAGMA passes options to the Manifold query engine to alter the operation of the query engine from default. The word pragma is computer terminology for a directive, comment or message within the text code given to a compiler or other program that guides the operation of that program, for example, by instructing the program to operate differently from default settings. In Manifold, PRAGMA is used mostly for debugging as well as for guiding the operation of the query engine.
PRAGMA ('pragma1'='...', 'pragma2'='...');
<statement>;
<statement>;
...
PRAGMA ('pragma1'='...', 'pragma3'='...');
<statement>;
<statement>;
...
The PRAGMA directive in Manifold SQL specifies options for the query engine by passes individual optional directives, known in lower case also as pragmas and each having a name for that option, with the desired value specified for each such optional directive.
Pragma values are part of state for the current execution context, a command window or query. For a pragma value with a given name a later assignment overrides an earlier one. Each assignment remains effective until it is overridden. The last assignment for a given pragma value persists between runs of a query.
The PragmaValue(<pragma>) : <value> function returns the value of the specified pragma.
Suppose in a query we set
PRAGMA ('gpgpu' = 'aggressive');
and then run the query. If we next remove that PRAGMA line and run the query again the same aggressive value will still apply. It will keep the same value in the current execution context until we change it.
Pragmas for Created Names
Values for these pragmas are the names of components created by CREATE statements or of tables created by the SELECT INTO statements in SQL. They are set automatically by use of a CREATE or a SELECT INTO statement in a query.
Pragmas specify options for how the Manifold query engine utilizes GPGPU, as follows:
gpgpu.device - Restricts GPGPU code to specified GPGPU device with devices numbered beginning with 0. Values for the pragma:
gpgpu.fp - Selects floating-point precision for GPGPU code. Values for the pragma:
Running a query from the Project pane or any other window or dialog runs the query in a background thread and displays a progress dialog. INSERT / DELETE / UPDATE and similar query statements track the number of processed records / size of processed values and will update progress status. If the number of records is known beforehand, the statements will also advance the progress bar.
Several pragmas control the progress display:
PRAGMA supports user-defined pragmas, which are frequently used for debugging. The PragmaValue query function reports the value of the specified pragma.
Pragma values pierce function calls, for example:
FUNCTION f(g GEOM) GEOM AS
CASE PragmaValue('normalize') WHEN 'y' THEN GeomNormalize(g, 0) ELSE g END
END;
SELECT f([geom])=[geom], PragmaValue('normalize') FROM [states];
-- no normalization, function sees the same pragma value as the SELECT
PRAGMA ('normalize'='y');
SELECT f([geom])=[geom], PragmaValue('normalize') FROM [states];
-- normalization
Multiple pragmas - We can specify more than one pragma, as in:
PRAGMA ('gpgpu'='aggressive', 'gpgpu.fp'='32');
UPDATE ... ;
As a result of the above PRAGMA directive the UPDATE statement will try to offload computations to GPGPU aggressively. When it succeeds in using GPGPU those GPGPU computations will be done using 32-bit floating-point math.
Multiple statements - PRAGMA setting of pragma values persist through the entire rest of the query, including to more than one statement, as in:
PRAGMA ('gpgpu.fp'='32');
UPDATE ... ;
UPDATE ... ;
INSERT ... ;
In the above, both UPDATE statements as well as the INSERT statement will do any GPGPU computations within those statements using 32-bit floating-point math.
Multiple Use to Override pragmas - Subsequent use of PRAGMA in a query can override values set previously:
PRAGMA ('gpgpu.fp'='32');
UPDATE ... ;
PRAGMA ('gpgpu'='none');
INSERT ... ;
In the above, the UPDATE statement will do GPGPU computations using 32-bit floating-point math while the INSERT will not use GPGPU and will do everything on the CPU.
Create a drawing using a table's name
- An SQL fragment shows how we can use the creatednamequoted
pragma to use the name of a table when setting properties for a drawing
that uses that table:
CREATE TABLE [tile boxes Table] (
[mfd_id] INT64,
[X] INT32,
[Y] INT32,
[Geom] GEOM,
INDEX [mfd_id_x] BTREE ([mfd_id]),
INDEX [Geom_x] RTREE ([Geom]),
PROPERTY 'FieldCoordSystem.Geom'
ComponentProperty(@table, 'FieldCoordSystem.' + @field)
-- indexed space
);
CREATE DRAWING [tile boxes] (
PROPERTY 'Table' PragmaValue('creatednamequoted'),
PROPERTY 'FieldGeom' 'Geom',
PROPERTY 'StyleAreaColorBack' '{ "Value": -16777216 }' -- clear
);
Boundaries not crossed - Pragmas do not cross the boundary of a query component or an EXECUTE statement. Consider the following:
PRAGMA ('gpgpu'='none');
SELECT ... FROM [query component];
...or the following:
PRAGMA ('gpgpu'='none');
EXECUTE [[ ... ]] ON ... ;
In both of the above cases the pragmas will have no effect. The code inside the query component referenced by the SELECT query and the code in the [[ ]] brackets in the EXECUTE statement will decide on its own in the default way whether or not to use GPGPU.
For example the following will report the value of the pragma:
PRAGMA ('custom'='abc');
VALUES ('custom', PragmaValue('custom'));
But this does not:
PRAGMA ('custom'='abc');
EXECUTE [[ VALUES ('custom', PragmaValue('custom')); ]]
When it comes to pragmas not crossing the boundary of a first query into a second query, two queries behave the same as EXECUTE. A second query will have its own set of pragmas and will not see pragmas from the first query.
Errors imply default - Specifying an invalid value for a pragma does not throw an error and is equivalent to specifying the default value, for example:
PRAGMA ('gpgpu'='1');
SELECT ... ;
The above sets gpgpu to the default value of auto.
Pragmas work with Functions too - We can use pragmas with functions. For example, we can utilize pragmas to specify use in different functions of both 32-bit and 64-bit floating-point math on GPGPU all within the same query:
PRAGMA ('gpgpu.fp'='32');
FUNCTION f(x TILE) TILE AS ... END;
PRAGMA ('gpgpu.fp'='64');
FUNCTION g(x TILE) TILE AS ... f(...) ... END);
SELECT ... g(...) ... FROM ... ;
In the above, f() will use GPGPU code with 32-bit floating-point math and g() will use GPGPU with 64-bit floating-point math. When it calls f() the query engine will create a different GPGPU frame.
Finding the gpgpu.device number - For use with the gpgpu.device pragma we need to know the device number of a given GPGPU-capable device. In the Command Window run ? CALL SystemGpgpus() to see the index, that is, the device number of all GPGPU-capable devices in the system. Note that no semicolon ; terminates the ? expression.