UPDATE

title: UPDATE(7)

summary: update rows of a table

text:
# UPDATE(7)

## NAME
UPDATE - update rows of a table

## SYNOPSIS

```
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]

    SET { column_name = { expression | DEFAULT } |

          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |

          ( column_name [, ...] ) = ( sub-SELECT )

        } [, ...]

    [ FROM from_item [, ...] ]

    [ WHERE condition | WHERE CURRENT OF cursor_name ]

    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]

                { * | output_expression [ [ AS ] output_name ] } [, ...] ]
```

## DESCRIPTION
UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.

There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the FROM clause. Which technique is more appropriate depends on the specific circumstances.

The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. By default, the new (post-update) values of the table's columns are used, but it is also possible to request the old (pre-update) values. The syntax of the RETURNING list is identical to that of the output list of SELECT.

You must have the UPDATE privilege on the table, or at least on the column(s) that are listed to be updated. You must also have the SELECT privilege on any column whose values are read in the expressions or condition.

## PARAMETERS
with_query

The WITH clause allows you to specify one or more subqueries that can be referenced by name in the UPDATE query. See Section 7.8 and SELECT(7) for details.

table_name

The name (optionally schema-qualified) of the table to update. If ONLY is specified before the table name, matching rows are updated in the named table only. If ONLY is not specified, matching rows are also updated in any tables inheriting from the named table. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

alias

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given UPDATE foo AS f, the remainder of the UPDATE statement must refer to this table as f not foo.

column_name

The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, UPDATE table_name SET table_name.col = 1 is invalid.

expression

An expression to assign to the column. The expression can use the old values of this and other columns in the table.

DEFAULT

Set the column to its default value (which will be NULL if no specific default expression has been assigned to it). An identity column will be set to a new value generated by the associated sequence. For a generated column, specifying this is permitted but merely specifies the normal behavior of computing the column from its generation expression.

sub-SELECT

A SELECT sub-query that produces as many output columns as are listed in the parenthesized column list preceding it. The sub-query must yield no more than one row when executed. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns. The sub-query can refer to old values of the current row of the table being updated.

from_item

A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the same syntax as the FROM clause of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

condition

An expression that returns a value of type boolean. Only rows for which this expression returns true will be updated.

cursor_name

The name of the cursor to use in a WHERE CURRENT OF condition. The row to be updated is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the UPDATE's target table. Note that WHERE CURRENT OF cannot be specified together with a Boolean condition. See DECLARE(7) for more information about using cursors with WHERE CURRENT OF.

output_alias

An optional substitute name for OLD or NEW rows in the RETURNING list. By default, old values from the target table can be returned by writing OLD.column_name or OLD.*, and new values can be returned by writing NEW.column_name or NEW.*. When an alias is provided, these names are hidden and the old or new rows must be referred to using the alias. For example RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*.

output_expression

An expression to be computed and returned by the UPDATE command after each row is updated. The expression can use any column names of the table named by table_name or table(s) listed in FROM. Write * to return all columns. A column name or * may be qualified using OLD or NEW, or the corresponding output_alias for OLD or NEW, to cause old or new values to be returned. An unqualified column name, or *, or a column name or * qualified using the target table name or alias will return new values.

output_name

A name to use for a returned column.

## OUTPUTS
On successful completion, an UPDATE command returns a command tag of the form

```
UPDATE count
```

The count is the number of rows updated, including matched rows whose values did not change. Note that the number may be less than the number of rows that matched the condition when updates were suppressed by a BEFORE UPDATE trigger. If count is 0, no rows were updated by the query (this is not considered an error).

If the UPDATE command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) updated by the command.

## NOTES
When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.

In the case of a partitioned table, updating a row might cause it to no longer satisfy the partition constraint of the containing partition. In that case, if there is some other partition in the partition tree for which this row satisfies its partition constraint, then the row is moved to that partition. If there is no such partition, an error will occur. Behind the scenes, the row movement is actually a DELETE and INSERT operation.

There is a possibility that a concurrent UPDATE or DELETE on the row being moved will get a serialization failure error. Suppose session 1 is performing an UPDATE on a partition key, and meanwhile a concurrent session 2 for which this row is visible performs an UPDATE or DELETE operation on this row. In such case, session 2's UPDATE or DELETE will detect the row movement and raise a serialization failure error (which always returns with an SQLSTATE code '40001'). Applications may wish to retry the transaction if this occurs. In the usual case where the table is not partitioned, or where there is no row movement, session 2 would have identified the newly updated row and carried out the UPDATE/DELETE on this new row version.

Note that while rows can be moved from local partitions to a foreign-table partition (provided the foreign data wrapper supports tuple routing), they cannot be moved from a foreign-table partition to another partition.

An attempt of moving a row from one partition to another will fail if a foreign key is found to directly reference an ancestor of the source partition that is not the same as the ancestor that's mentioned in the UPDATE query.

## EXAMPLES
Change the word Drama to Dramatic in the column kind of the table films:

```
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';
```

Adjust temperature entries and reset precipitation to its default value in one row of the table weather:

```
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT

  WHERE city = 'San Francisco' AND date = '2003-07-03';
```

Perform the same operation and return the updated entries, and the old precipitation value:

```
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT

  WHERE city = 'San Francisco' AND date = '2003-07-03'

  RETURNING temp_lo, temp_hi, prcp, old.prcp AS old_prcp;
```

Use the alternative column-list syntax to do the same update:

```
UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)

  WHERE city = 'San Francisco' AND date = '2003-07-03';
```

Increment the sales count of the salesperson who manages the account for Acme Corporation, using the FROM clause syntax:

```
UPDATE employees SET sales_count = sales_count + 1 FROM accounts

  WHERE accounts.name = 'Acme Corporation'

  AND employees.id = accounts.sales_person;
```

Perform the same operation, using a sub-select in the WHERE clause:

```
UPDATE employees SET sales_count = sales_count + 1 WHERE id =

  (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');
```

Update contact names in an accounts table to match the currently assigned salespeople:

```
UPDATE accounts SET (contact_first_name, contact_last_name) =

    (SELECT first_name, last_name FROM employees

     WHERE employees.id = accounts.sales_person);
```

A similar result could be accomplished with a join:

```
UPDATE accounts SET contact_first_name = first_name,

                    contact_last_name = last_name

  FROM employees WHERE employees.id = accounts.sales_person;
```

However, the second query may give unexpected results if employees.id is not a unique key, whereas the first query is guaranteed to raise an error if there are multiple id matches. Also, if there is no match for a particular accounts.sales_person entry, the first query will set the corresponding name fields to NULL, whereas the second query will not update that row at all.

Update statistics in a summary table to match the current data:

```
UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =

    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d

     WHERE d.group_id = s.group_id);
```

Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. To do this without failing the entire transaction, use savepoints:

```
BEGIN;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continue with other operations, and eventually
COMMIT;
```

Change the kind column of the table films in the row on which the cursor c_films is currently positioned:

```
UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
```

Updates affecting many rows can have negative effects on system performance, such as table bloat, increased replica lag, and increased lock contention. In such situations it can make sense to perform the operation in smaller batches, possibly with a VACUUM operation on the table between batches. While there is no LIMIT clause for UPDATE, it is possible to get a similar effect through the use of a Common Table Expression and a self-join. With the standard PostgreSQL table access method, a self-join on the system column ctid is very efficient:

```
WITH exceeded_max_retries AS (

  SELECT w.ctid FROM work_item AS w

    WHERE w.status = 'active' AND w.num_retries > 10

    ORDER BY w.retry_timestamp

    FOR UPDATE

    LIMIT 5000
)
UPDATE work_item SET status = 'failed'

  FROM exceeded_max_retries AS emr

  WHERE work_item.ctid = emr.ctid;
```

This command will need to be repeated until no rows remain to be updated. (This use of ctid is only safe because the query is repeatedly run, avoiding the problem of changed ctids.) Use of an ORDER BY clause allows the command to prioritize which rows will be updated; it can also prevent deadlock with other update operations if they use the same ordering. If lock contention is a concern, then SKIP LOCKED can be added to the CTE to prevent multiple commands from updating the same row. However, then a final UPDATE without SKIP LOCKED or LIMIT will be needed to ensure that no matching rows were overlooked.

## COMPATIBILITY
This command conforms to the SQL standard, except that the FROM and RETURNING clauses are PostgreSQL extensions, as is the ability to use WITH with UPDATE.

Some other database systems offer a FROM option in which the target table is supposed to be listed again within FROM. That is not how PostgreSQL interprets FROM. Be careful when porting applications that use this extension.

According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. PostgreSQL only allows the source value to be a row constructor or a sub-SELECT. An individual column's updated value can be specified as DEFAULT in the row-constructor case, but not inside a sub-SELECT.


VACUUM

title: VACUUM(7)

summary: garbage\-collect and optionally analyze a database

text:
# VACUUM(7)

## NAME
VACUUM - garbage-collect and optionally analyze a database

## SYNOPSIS

```
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
where option can be one of:

    FULL [ boolean ]

    FREEZE [ boolean ]

    VERBOSE [ boolean ]

    ANALYZE [ boolean ]

    DISABLE_PAGE_SKIPPING [ boolean ]

    SKIP_LOCKED [ boolean ]

    INDEX_CLEANUP { AUTO | ON | OFF }

    PROCESS_MAIN [ boolean ]

    PROCESS_TOAST [ boolean ]

    TRUNCATE [ boolean ]

    PARALLEL integer

    SKIP_DATABASE_STATS [ boolean ]

    ONLY_DATABASE_STATS [ boolean ]

    BUFFER_USAGE_LIMIT size
and table_and_columns is:

    [ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]
```

## DESCRIPTION
VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.

Without a table_and_columns list, VACUUM processes every table and materialized view in the current database that the current user has permission to vacuum. With a list, VACUUM processes only those table(s).

VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected table. This is a handy combination form for routine maintenance scripts. See ANALYZE(7) for more details about its processing.

Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. It also allows us to leverage multiple CPUs in order to process indexes. This feature is known as parallel vacuum. To disable this feature, one can use PARALLEL option and specify parallel workers as zero. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an ACCESS EXCLUSIVE lock on each table while it is being processed.

## PARAMETERS
FULL

Selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table.

FREEZE

Selects aggressive “freezing” of tuples. Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to zero. Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified.

VERBOSE

Prints a detailed vacuum activity report for each table at INFO level.

ANALYZE

Updates statistics used by the planner to determine the most efficient way to execute a query.

DISABLE_PAGE_SKIPPING

Normally, VACUUM will skip pages based on the visibility map. Pages where all tuples are known to be frozen can always be skipped, and those where all tuples are known to be visible to all transactions may be skipped except when performing an aggressive vacuum. Furthermore, except when performing an aggressive vacuum, some pages may be skipped in order to avoid waiting for other sessions to finish using them. This option disables all page-skipping behavior, and is intended to be used only when the contents of the visibility map are suspect, which should happen only if there is a hardware or software issue causing database corruption.

SKIP_LOCKED

Specifies that VACUUM should not wait for any conflicting locks to be released when beginning work on a relation: if a relation cannot be locked immediately without waiting, the relation is skipped. Note that even with this option, VACUUM may still block when opening the relation's indexes. Additionally, VACUUM ANALYZE may still block when acquiring sample rows from partitions, table inheritance children, and some types of foreign tables. Also, while VACUUM ordinarily processes all partitions of specified partitioned tables, this option will cause VACUUM to skip all partitions if there is a conflicting lock on the partitioned table.

INDEX_CLEANUP

Normally, VACUUM will skip index vacuuming when there are very few dead tuples in the table. The cost of processing all of the table's indexes is expected to greatly exceed the benefit of removing dead index tuples when this happens. This option can be used to force VACUUM to process indexes when there are more than zero dead tuples. The default is AUTO, which allows VACUUM to skip index vacuuming when appropriate. If INDEX_CLEANUP is set to ON, VACUUM will conservatively remove all dead tuples from indexes. This may be useful for backwards compatibility with earlier releases of PostgreSQL where this was the standard behavior. INDEX_CLEANUP can also be set to OFF to force VACUUM to always skip index vacuuming, even when there are many dead tuples in the table. This may be useful when it is necessary to make VACUUM run as quickly as possible to avoid imminent transaction ID wraparound (see Section 24.1.5). However, the wraparound failsafe mechanism controlled by vacuum_failsafe_age will generally trigger automatically to avoid transaction ID wraparound failure, and should be preferred. If index cleanup is not performed regularly, performance may suffer, because as the table is modified indexes will accumulate dead tuples and the table itself will accumulate dead line pointers that cannot be removed until index cleanup is completed.

This option has no effect for tables that have no index and is ignored if the FULL option is used. It also has no effect on the transaction ID wraparound failsafe mechanism. When triggered it will skip index vacuuming, even when INDEX_CLEANUP is set to ON.

PROCESS_MAIN

Specifies that VACUUM should attempt to process the main relation. This is usually the desired behavior and is the default. Setting this option to false may be useful when it is only necessary to vacuum a relation's corresponding TOAST table.

PROCESS_TOAST

Specifies that VACUUM should attempt to process the corresponding TOAST table for each relation, if one exists. This is usually the desired behavior and is the default. Setting this option to false may be useful when it is only necessary to vacuum the main relation. This option is required when the FULL option is used.

TRUNCATE

Specifies that VACUUM should attempt to truncate off any empty pages at the end of the table and allow the disk space for the truncated pages to be returned to the operating system. This is normally the desired behavior and is the default unless vacuum_truncate is set to false or the vacuum_truncate option has been set to false for the table to be vacuumed. Setting this option to false may be useful to avoid ACCESS EXCLUSIVE lock on the table that the truncation requires. This option is ignored if the FULL option is used.

PARALLEL

Perform index vacuum and index cleanup phases of VACUUM in parallel using integer background workers (for the details of each vacuum phase, please refer to Table 27.46). The number of workers used to perform the operation is equal to the number of indexes on the relation that support parallel vacuum which is limited by the number of workers specified with PARALLEL option if any which is further limited by max_parallel_maintenance_workers. An index can participate in parallel vacuum if and only if the size of the index is more than min_parallel_index_scan_size. Please note that it is not guaranteed that the number of parallel workers specified in integer will be used during execution. It is possible for a vacuum to run with fewer workers than specified, or even with no workers at all. Only one worker can be used per index. So parallel workers are launched only when there are at least 2 indexes in the table. Workers for vacuum are launched before the start of each phase and exit at the end of the phase. These behaviors might change in a future release. This option can't be used with the FULL option.

SKIP_DATABASE_STATS

Specifies that VACUUM should skip updating the database-wide statistics about oldest unfrozen XIDs. Normally VACUUM will update these statistics once at the end of the command. However, this can take awhile in a database with a very large number of tables, and it will accomplish nothing unless the table that had contained the oldest unfrozen XID was among those vacuumed. Moreover, if multiple VACUUM commands are issued in parallel, only one of them can update the database-wide statistics at a time. Therefore, if an application intends to issue a series of many VACUUM commands, it can be helpful to set this option in all but the last such command; or set it in all the commands and separately issue VACUUM (ONLY_DATABASE_STATS) afterwards.

ONLY_DATABASE_STATS

Specifies that VACUUM should do nothing except update the database-wide statistics about oldest unfrozen XIDs. When this option is specified, the table_and_columns list must be empty, and no other option may be enabled except VERBOSE.

BUFFER_USAGE_LIMIT

Specifies the Buffer Access Strategy ring buffer size for VACUUM. This size is used to calculate the number of shared buffers which will be reused as part of this strategy. 0 disables use of a Buffer Access Strategy. If ANALYZE is also specified, the BUFFER_USAGE_LIMIT value is used for both the vacuum and analyze stages. This option can't be used with the FULL option except if ANALYZE is also specified. When this option is not specified, VACUUM uses the value from vacuum_buffer_usage_limit. Higher settings can allow VACUUM to run more quickly, but having too large a setting may cause too many other useful pages to be evicted from shared buffers. The minimum value is 128 kB and the maximum value is 16 GB.

boolean

Specifies whether the selected option should be turned on or off. You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF, or 0 to disable it. The boolean value can also be omitted, in which case TRUE is assumed.

integer

Specifies a non-negative integer value passed to the selected option.

size

Specifies an amount of memory in kilobytes. Sizes may also be specified as a string containing the numerical size followed by any one of the following memory units: B (bytes), kB (kilobytes), MB (megabytes), GB (gigabytes), or TB (terabytes).

table_name

The name (optionally schema-qualified) of a specific table or materialized view to vacuum. If ONLY is specified before the table name, only that table is vacuumed. If ONLY is not specified, the table and all its inheritance child tables or partitions (if any) are also vacuumed. Optionally, * can be specified after the table name to explicitly indicate that inheritance child tables (or partitions) are to be vacuumed.

column_name

The name of a specific column to analyze. Defaults to all columns. If a column list is specified, ANALYZE must also be specified.

## OUTPUTS
When VERBOSE is specified, VACUUM emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.

## NOTES
To vacuum a table, one must ordinarily have the MAINTAIN privilege on the table. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. VACUUM will skip over any tables that the calling user does not have permission to vacuum.

While VACUUM is running, the search_path is temporarily changed to pg_catalog, pg_temp.

VACUUM cannot be executed inside a transaction block.

For tables with GIN indexes, VACUUM (in any form) also completes any pending index insertions, by moving pending index entries to the appropriate places in the main GIN index structure. See Section 65.4.4.1 for details.

We recommend that all databases be vacuumed regularly in order to remove dead rows. PostgreSQL includes an “autovacuum” facility which can automate routine vacuum maintenance. For more information about automatic and manual vacuuming, see Section 24.1.

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would.

The PARALLEL option is used only for vacuum purposes. If this option is specified with the ANALYZE option, it does not affect ANALYZE.

VACUUM causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions. Therefore, it is sometimes advisable to use the cost-based vacuum delay feature. For parallel vacuum, each worker sleeps in proportion to the work done by that worker. See Section 19.10.2 for details.

Each backend running VACUUM without the FULL option will report its progress in the pg_stat_progress_vacuum view. Backends running VACUUM FULL will instead report their progress in the pg_stat_progress_cluster view. See Section 27.4.5 and Section 27.4.2 for details.

## EXAMPLES
To clean a single table onek, analyze it for the optimizer and print a detailed vacuum activity report:

```
VACUUM (VERBOSE, ANALYZE) onek;
```

## COMPATIBILITY
There is no VACUUM statement in the SQL standard.

The following syntax was used before PostgreSQL version 9.0 and is still supported:

```
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
```

Note that in this syntax, the options must be specified in exactly the order shown.

## SEE ALSO
vacuumdb(1), Section 19.10.2, Section 24.1.6, Section 27.4.5, Section 27.4.2

branch: sisyphus


VALUES

title: VALUES(7)

summary: compute a set of rows

text:
# VALUES(7)

## NAME
VALUES - compute a set of rows

## SYNOPSIS

```
VALUES ( expression [, ...] ) [, ...]

    [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]

    [ LIMIT { count | ALL } ]

    [ OFFSET start [ ROW | ROWS ] ]

    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
```

## DESCRIPTION
VALUES computes a row value or set of row values specified by value expressions. It is most commonly used to generate a “constant table” within a larger command, but it can be used on its own.

When more than one row is specified, all the rows must have the same number of elements. The data types of the resulting table's columns are determined by combining the explicit or inferred types of the expressions appearing in that column, using the same rules as for UNION (see Section 10.5).

Within larger commands, VALUES is syntactically allowed anywhere that SELECT is. Because it is treated like a SELECT by the grammar, it is possible to use the ORDER BY, LIMIT (or equivalently FETCH FIRST), and OFFSET clauses with a VALUES command.

## PARAMETERS
expression

A constant or expression to compute and insert at the indicated place in the resulting table (set of rows). In a VALUES list appearing at the top level of an INSERT, an expression can be replaced by DEFAULT to indicate that the destination column's default value should be inserted. DEFAULT cannot be used when VALUES appears in other contexts.

sort_expression

An expression or integer constant indicating how to sort the result rows. This expression can refer to the columns of the VALUES result as column1, column2, etc. For more details see ORDER BY Clause in the SELECT(7) documentation.

operator

A sorting operator. For details see ORDER BY Clause in the SELECT(7) documentation.

count

The maximum number of rows to return. For details see LIMIT Clause in the SELECT(7) documentation.

start

The number of rows to skip before starting to return rows. For details see LIMIT Clause in the SELECT(7) documentation.

## NOTES
VALUES lists with very large numbers of rows should be avoided, as you might encounter out-of-memory failures or poor performance. VALUES appearing within INSERT is a special case (because the desired column types are known from the INSERT's target table, and need not be inferred by scanning the VALUES list), so it can handle larger lists than are practical in other contexts.

## EXAMPLES
A bare VALUES command:

```
VALUES (1, 'one'), (2, 'two'), (3, 'three');
```

This will return a table of two columns and three rows. It's effectively equivalent to:

```
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
```

More usually, VALUES is used within a larger SQL command. The most common use is in INSERT:

```
INSERT INTO films (code, title, did, date_prod, kind)

    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
```

In the context of INSERT, entries of a VALUES list can be DEFAULT to indicate that the column default should be used here instead of specifying a value:

```
INSERT INTO films VALUES

    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'),

    ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT);
```

VALUES can also be used where a sub-SELECT might be written, for example in a FROM clause:

```
SELECT f.*

  FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind)

  WHERE f.studio = t.studio AND f.kind = t.kind;
UPDATE employees SET salary = salary * v.increase

  FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)

  WHERE employees.depno = v.depno AND employees.sales >= v.target;
```

Note that an AS clause is required when VALUES is used in a FROM clause, just as is true for SELECT. It is not required that the AS clause specify names for all the columns, but it's good practice to do so. (The default column names for VALUES are column1, column2, etc. in PostgreSQL, but these names might be different in other database systems.)

When VALUES is used in INSERT, the values are all automatically coerced to the data type of the corresponding destination column. When it's used in other contexts, it might be necessary to specify the correct data type. If the entries are all quoted literal constants, coercing the first is sufficient to determine the assumed type for all:

```
SELECT * FROM machines
WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43'));
```

Tip

For simple IN tests, it's better to rely on the list-of-scalars form of IN than to write a VALUES query as shown above. The list of scalars method requires less writing and is often more efficient.

## COMPATIBILITY
VALUES conforms to the SQL standard. LIMIT and OFFSET are PostgreSQL extensions; see also under SELECT(7).

## SEE ALSO
INSERT(7), SELECT(7)


lei-mail-sync-overview

package: public-inbox

title: lei-mail-sync-overview(7)

summary: an overview of lei mail synchronization

text:
# lei-mail-sync-overview(7)

## NAME
lei - an overview of lei mail synchronization

## DESCRIPTION
lei(1) provides several plumbing-level commands to synchronize mail and keywords (flags) between lei/store and existing IMAP and Maildir stores. Nothing documented in this manpage is required for day-to-day use against externals.

Mail and keyword synchronization is currently a clunky process. Future work will be done to improve it and add IMAP IDLE support.

## TYPICAL WORKFLOW

```
  # import mail from a user's IMAP inbox and give it the "inbox" label:
  lei import +L:inbox imaps://user@example.com/INBOX
  # dump "inbox" labeled files from the past week to a Maildir
  lei q L:inbox rt:last.week.. -o /tmp/results
  # Open /tmp/results in your favorite mail agent.  If inotify or kevent
  # works, keyword changes (e.g. marking messages as `seen') are
  # synchronized automatically.
  # If the inotify queue overflows, or if lei-daemon crashes,
  # "lei index" will tell lei about keyword changes:
  lei index /tmp/results
  # Optional: cleanup stale entries from mail_sync.sqlite3
  lei refresh-mail-sync /tmp/results
  # to export keyword changes back to IMAP
  lei export-kw imaps://user@example.com/INBOX
```

## CONTACT
Feedback welcome via plain-text mail to <mailto:meta@public-inbox.org>

The mail archives are hosted at <https://public-inbox.org/meta/> and <http://4uok3hntl7oi7b4uf4rtfwefqeexfzil2w6kgk2jn5z2f764irre7byd.onion/meta/>

## COPYRIGHT
Copyright all contributors <mailto:meta@public-inbox.org>

License: AGPL-3.0+ <https://www.gnu.org/licenses/agpl-3.0.txt>

## SEE ALSO
lei-import(1), lei-q(1), lei-index(1), lei-refresh-mail-sync(1), lei-export-kw(1)


lei-overview

title: lei-overview(7)

summary: an overview of lei

text:
# lei-overview(7)

## NAME
lei - an overview of lei

## DESCRIPTION
lei(1) is a local email interface for public-inbox and personal mail. This document provides some basic examples.

## LEI STORE
lei has writable local storage based on public-inbox-v2-format(5). Commands will automatically initialize the store behind the scenes if needed, but you can call lei-init(1) directly if you want to use a store location other than the default "$XDG_DATA_HOME/lei/store".

The lei-import(1) command provides the primary interface for importing messages into the local storage. In addition, other commands, such as lei-q(1) and lei-blob(1), use the local store to memoize messages from remotes.

### EXAMPLES
$ lei import mboxrd:t.mbox.gz

Import the messages from a gzipped mboxrd into the local storage.

$ lei blob 59ec517f9

Show message with the git blob OID of 59ec517f9. If a message with that OID isn't found, check if the current git repository has the blob, trying to reconstruct it from a message if needed.

$ lei blob 59ec517f9 | lei tag -F eml +kw:flagged +L:next

Set the "flagged" keyword and "next" label on the message with the blob OID of 59ec517f9.

## EXTERNALS
In addition to the above store, lei can make read-only queries to "externals": inboxes and external indices. An external can be registered by passing a URL or local path to lei-add-external(1). For existing local paths, the external needs to be indexed with public-inbox-index(1) (in the case of a regular inbox) or public-inbox-extindex(1) (in the case of an external index).

## SYNCHRONIZATION
lei currently has primitive mail synchronization abilities; see lei-mail-sync-overview(7) for more details.

### EXAMPLES
$ lei add-external https://public-inbox.org/meta/

Add a remote external for public-inbox's inbox.

$ lei add-external --mirror https://public-inbox.org/meta/ path

Clone <https://public-inbox.org/meta/> to "path", index it with public-inbox-index(1), and add it as a local external.

## SEARCHING
The lei-q(1) command searches the local store and externals. The search prefixes match those available via public-inbox-httpd(1).

### EXAMPLES
$ lei q s:lei s:skeleton

Search for messages whose subject includes "lei" and "skeleton".

$ lei q -t s:lei s:skeleton

Do the same, but also report unmatched messages that are in the same thread as a matched message.

$ lei q -t -o /tmp/mdir --mua=mutt s:lei s:skeleton

Write results to a Maildir at "mdir". Mutt will be invoked to open mfolder ("mutt -f %f") while results are being fetched and written.

$ lei q kw:flagged L:next

Search for all flagged messages that also have a "next" label.

$ lei p2q HEAD | lei q -tt -o /tmp/mdir

Search for messages that have post-image git blob IDs that match those of the current repository's HEAD commit, writing them to the Maildir directory "mdir" and flagging the messages that were an exact match.

$ git show -s HEAD | lei lcat

Display a local message for the public-inbox link contained in a commit message.

$ lei q -f text m:MESSAGE-ID | lei rediff -U5

Feed a message containing a diff to lei-rediff(1) to regenerate its diff with five context lines. Unless "--git-dir" is specified, this requires the current working directory to be within the associated code repository.

## PERFORMANCE NOTES
Inline::C is required on BSDs and can speed things up on Linux.

lei runs as a background daemon to reduce startup costs and can provide real-time kqueue(2)/inotify(7) Maildir monitoring. IO::KQueue (p5-IO-KQueue on FreeBSD) and Linux::Inotify2 (liblinux-inotify2-perl and perl-Linux-Inotify2 in .deb and .rpm-based distros, respectively) are recommended.

Socket::MsgHdr is optional (libsocket-msghdr-perl in Debian), and further improves startup performance. Its effect is most felt when using shell completion.

## BASH COMPLETION
Preliminary Bash completion for lei is provided in "contrib/completion/". Contributions adding support for other shells, as well as improvements to the existing Bash completion, are welcome.

## UPGRADING
Since lei runs as a daemon, lei-daemon-kill(1) is required to kill the daemon so it can load new code. It will be restarted with the next invocation of any lei command.

## CAVEATS
IMAP and NNTP client performance is poor on high-latency connections. It will hopefully be fixed in 2022.

## CONTACT
Feedback welcome via plain-text mail to <mailto:meta@public-inbox.org>

The mail archives are hosted at <https://public-inbox.org/meta/> and <http://4uok3hntl7oi7b4uf4rtfwefqeexfzil2w6kgk2jn5z2f764irre7byd.onion/meta/>

## COPYRIGHT
Copyright all contributors <mailto:meta@public-inbox.org>

License: AGPL-3.0+ <https://www.gnu.org/licenses/agpl-3.0.txt>

## SEE ALSO
lei-mail-sync-overview(7)


lei-security

title: lei-security(7)

summary: security information

text:
# lei-security(7)

## NAME
lei - security information

## SYNOPSIS
lei(1) is intended for use with both publicly archived and "private" mail in personal mailboxes. This document is intended to give an overview of security implications and lower^Wmanage user expectations.

## DESCRIPTION
lei expects to be run as a regular user on a Unix-like system. It expects a case-sensitive filesystem with standard Unix permissions support.

It does not use POSIX ACLs, extended attributes, nor any other security-related functions which require non-standard Perl modules.

There is preliminary support for "virtual users", but it is incomplete and undocumented.

## INTERNAL FILES
lei runs with a umask of 077 to prevent other users on the system from accessing each other's mail.

The git storage and Xapian databases are located at "$XDG_DATA_HOME/lei/store" (typically "~/.local/share/lei/store"). Any personal mail imported will reside here, so this should be on an encrypted filesystem or block device.

"$XDG_RUNTIME_DIR/lei" (typically "/run/user/$UID/lei" or "/tmp/lei-$UID") contain the socket used to access the lei daemon. It must only be accessible to the owner (mode 0700).

"$XDG_CACHE_HOME/lei" (typically "~/.cache/lei") will contain IMAP and Maildir folder names which could leak sensitive information as well as git repository names.

"$XDG_DATA_HOME/lei/saved-searches" (typically "~/.local/share/lei/saved-searches") will contain aforementioned folder names as well as (removable) search history.

The configuration for lei resides at "$XDG_CONFIG_HOME/lei/config" (typically "~/.config/lei/config"). It may contain sensitive pathnames and hostnames in the config if a user chooses to configure them.

lei itself will never write credentials to the filesystem. However, git-credential(1) may be configured to do so. lei will only read "~/.netrc" if "--netrc" is used (and it will never write to "~/.netrc").

"$XDG_CACHE_HOME/public-inbox" (typically "~/.cache/public-inbox") can contain data and Inline::C-built modules which can be shared with public-facing public-inbox-daemon(8) instances; so no private data should be in "public-inbox" paths.

## EXTERNAL FILES
Locations set by lei-add-external(1) can be shared with public-facing public-inbox-daemon(8) processes. They may reside on shared storage and may be made world-readable to other users on the local system.

## CORE DUMPS
In case any process crashes, a core dump may contain passwords or contents of sensitive messages. Please report these so they can be fixed (see "CONTACT").

## NETWORK ACCESS
lei currently uses the curl(1) and git(1) executables in $PATH for HTTP and HTTPS network access. Interactive authentication for HTTP and HTTPS is not yet supported since all currently supported HTTP/HTTPS sources are PublicInbox::WWW instances.

The Mail::IMAPClient library is used for IMAP and IMAPS. Net::NNTP (standard library) is used for NNTP and NNTPS.

Mail::IMAPClient and Net::NNTP will use IO::Socket::SSL for TLS if available. In turn, IO::Socket::SSL uses the widely installed OpenSSL library.

STARTTLS will be attempted if advertised by the server unless IMAPS or NNTPS are used. "-c imap.starttls=0" and "-c nntp.startls=0" may be used to disable STARTTLS.

IO::Socket::Socks will be used if "-c imap.proxy" or "-c nntp.proxy" point to a "socks5h://$HOST:$PORT" address (common for Tor).

The "--netrc" switch may be passed to curl and used for NNTP/IMAP access (via Net::Netrc).

## CREDENTIAL DATA
lei uses git-credential(1) to prompt users for IMAP and NNTP usernames and passwords. These passwords are not encrypted in memory and get transferred across processes via anonymous UNIX sockets and pipes. They may be exposed via syscall tracing tools (e.g. strace(1)), kernel and hardware bugs/attacks.

While credentials are not written to the filesystem by default, it is possible for them to end up on disk if processes are swapped out. Use of an encrypted swap partition is recommended.

## AUTHENTICATION METHODS
LOGIN (username + password) is known to work over IMAP(S), as does AUTH=ANONYMOUS (which is used by public-inbox-imapd(1) as part of our test suite). AUTHINFO may work for NNTP, but is untested. Testers will be needed for other authentication methods.

## DENIAL-OF-SERVICE VECTORS
lei uses the same MIME parsing library as public-inbox-mda(1) with limits header sizes, parts, nesting and boundary limits similar to those found in SpamAssassin and postfix.

Email address parsing is handled by Email::Address::XS if available, but may fall back to regular expressions which favor speed and predictable execution times over correctness.

## ENCRYPTED EMAILS
Not yet supported, but it should eventually be possible to configure decryption and indexing of encrypted messages and attachments. When supported, decrypted terms will be stored in Xapian DBs under "$XDG_DATA_HOME/lei/store".

## CONTACT
Feedback welcome via plain-text mail to <mailto:meta@public-inbox.org>

The mail archives are hosted at <https://public-inbox.org/meta/> and <http://4uok3hntl7oi7b4uf4rtfwefqeexfzil2w6kgk2jn5z2f764irre7byd.onion/meta/>

## COPYRIGHT
Copyright all contributors <mailto:meta@public-inbox.org>

License: AGPL-3.0+ <https://www.gnu.org/licenses/agpl-3.0.txt>

## SEE ALSO
lei-overview(7), lei(1)


public-inbox-glossary

title: public-inbox-glossary(7)

summary: glossary for public\-inbox

text:
# public-inbox-glossary(7)

## NAME
public-inbox-glossary - glossary for public-inbox

## DESCRIPTION
public-inbox combines several independently-developed protocols and data formats with overlapping concepts. This document is intended as a guide to identify and clarify overlapping concepts with different names.

This is mainly intended for hackers of public-inbox, but may be useful for administrators of public-facing services and/or users building tools.

## TERMS
IMAP UID, NNTP article number, on-disk Xapian docid

A sequentially-assigned positive integer. These integers are per-inbox, or per-extindex. This is the "num" column of the "over" table in "over.sqlite3"

tid, THREADID

A sequentially assigned positive integer. These integers are per-inbox or per-extindex. In the future, this may be prefixed with "T" for JMAP (RFC 8621) and RFC 8474. This may not be strictly compliant with RFC 8621 since inboxes and extindices are considered independent entities from each other. This is the "tid" column of the "over" table in "over.sqlite3"

blob

For email, this is the git blob object ID (SHA-(1|256)) of an RFC-(822|2822|5322) email message.

IMAP EMAILID, JMAP Email Id

To be decided. This will likely be the git blob ID prefixed with "g" rather than the numeric UID to accommodate the same blob showing up in both an extindex and inbox (or multiple extindices).

newsgroup

The name of the NNTP newsgroup, see public-inbox-config(5).

IMAP (folder|mailbox) slice

A 50K slice of a newsgroup to accommodate the limitations of IMAP clients with public-inbox-imapd(1). This is the "newsgroup" name with a ".$INTEGER_SUFFIX", e.g. a newsgroup named "inbox.test" would have its first slice named "inbox.test.0", and second slice named "inbox.test.1" and so forth. If implemented, the RFC 8474 MAILBOXID of an IMAP slice will NOT have the same Mailbox Id as the public-facing full JMAP mailbox.

inbox name, public JMAP mailbox name

The HTTP(S) name of the public-inbox ("publicinbox.<name>.*"). JMAP will use this name rather than the newsgroup name since public-facing JMAP will be part of the PSGI code and not need a separate daemon like public-inbox-nntpd(1) or public-inbox-imapd(1)

epoch

A git repository used for blob storage. See "GIT EPOCHS" in public-inbox-v2-format(5).

keywords, (IMAP|Maildir) flags, mbox Status + X-Status

Private, per-message keywords or flags as described in RFC 8621 section 10.4. These are conveyed in the "Status:" and "X-Status:" headers for mbox(5), as system IMAP FLAGS (RFC 3501 section 2.3.2), or Maildir info flags. public-inbox-watch(1) ignores drafts and trashed (deleted) messages. lei-import(1) ignores trashed (deleted) messages, but it imports drafts.

labels, private JMAP mailboxes

For lei(1) users only. This will allow lei users to place the same email into one or more virtual folders for ease of filtering. This is NOT tied to public-inbox names, as messages stored by lei may not be public. These are similar in spirit to arbitrary freeform "tags" in mail software such as notmuch(1) and non-system IMAP FLAGS.

volatile metadata (VMD)

For lei(1) users only, this refers to the combination of keywords and labels which are subject to frequent change independently of immutable message content.

IMAP INTERNALDATE, JMAP receivedAt, rt: search prefix

The first valid timestamp value of Received: headers (top first). If no Received: header exists, the Date: header is used, and the current time if neither header(s) exist. When mirroring via git, this is the git commit time.

IMAP SENT*, JMAP sentAt, dt: and d: search prefixes

The first valid timestamp value of the Date: header(s). If no Date: header exists, the time from the Received: header is used, and then the current time if neither header exists. When mirroring via git, this is the git author time.

## COPYRIGHT
Copyright 2021 all contributors <mailto:meta@public-inbox.org>

License: AGPL-3.0+ <http://www.gnu.org/licenses/agpl-3.0.txt>

## SEE ALSO
public-inbox-v2-format(5), public-inbox-v1-format(5), public-inbox-extindex-format(5), gitglossary(7)


public-inbox-overview

title: public-inbox-overview(7)

summary: an overview of public\-inbox

text:
# public-inbox-overview(7)

## NAME
public-inbox-overview - an overview of public-inbox

## DESCRIPTION
public-inbox consists of many pieces which may be used independently or in conjunction of each other for:

1.

Mirroring existing public-inboxes.

2.

Mirroring mailing lists.

3.

Hosting standalone inboxes.

### Mirroring existing public-inboxes
Mirroring existing public-inboxes is the easiest way to get started. Your mirror will remain dependent on the REMOTE_URL you are mirroring and you only need to use two new commands in addition to common git(1) commands.

Instructions are different depending on whether the inbox is public-inbox-v1-format(5) or public-inbox-v2-format(5). See the "Archives are clonable:" part of the WWW interface of a given inbox for cloning instructions specific to that inbox. The instructions are roughly:

```
  # for v1 inboxes:
  git clone --mirror URL INBOX_DIR
  # for v2 inboxes (each epoch needs to be cloned):
  git clone --mirror URL/EPOCH INBOX_DIR/git/EPOCH.git
  # The following should create the necessary entry in
  # ~/.public-inbox/config, use "-V2" only for v2 inboxes:
  public-inbox-init [-V2] NAME INBOX_DIR MY_URL LIST_ADDRESS
  # Optional but strongly recommended for hosting HTTP
  # (and required for NNTP)
  # enable overview (requires DBD::SQLite) and, if Xapian is
  # available, search:
  public-inbox-index INBOX_DIR
  # Periodically fetch the repo using git-fetch(1)
  # for v1 inboxes:
  git --git-dir=INBOX_DIR fetch
  # for v2 (in most cases, only the newest epoch needs to be fetched):
  git --git-dir=INBOX_DIR/git/EPOCH.git fetch
  # index new messages after fetching:
  public-inbox-index INBOX_DIR
```

See "Serving public-inboxes" below for info on how to expose your mirror to other readers.

### Mirroring mailing lists
Mirroring mailing lists may be done by any reader of a mailing list using public-inbox-watch(1).

```
        # This will create a new v2 inbox:
        public-inbox-init -V2 NAME INBOX_DIR MY_URL LIST_ADDRESS
```

Then, see the public-inbox-watch(1) manual for configuring "watch", "watchheader", "listid" and the optional "spamcheck" and "watchspam" entries.

You will need to leave public-inbox-watch(1) running to keep the mailbox up-to-date as messages are delivered to the mailing list.

Running public-inbox-index(1) to create search indices is recommended. public-inbox-watch(1) will automatically maintain the indices if they were created by public-inbox-index(1)

```
        public-inbox-index INBOX_DIR
```

Instead of using public-inbox-watch(1), using public-inbox-mda(1) with the "--no-precheck" option and relying on the "listid" directive in public-inbox-config(5) is also an option.

### Hosting standalone inboxes
Using public-inbox-init(1) to initialize the inbox as in the other methods is recommended. See public-inbox-mda(1) for more details; but this also requires MTA-specific knowledge.

### Serving public-inboxes
Since public-inboxes are git repositories, they may be served to remote clients via git-daemon(1) as well as specialized HTTP and NNTP daemons distributed with public-inbox.

See public-inbox-httpd(1) and public-inbox-nntpd(1) for more information on using these daemons.

Hosting a public-inbox over HTTP or NNTP will never require write access to any files in the inbox directory.

Users familiar with PSGI and Plack may also use PublicInbox::WWW with the preferred server instead of public-inbox-httpd(1)

## CONTACT
Feedback welcome via plain-text mail to <mailto:meta@public-inbox.org>

The mail archives are hosted at <https://public-inbox.org/meta/> and <http://4uok3hntl7oi7b4uf4rtfwefqeexfzil2w6kgk2jn5z2f764irre7byd.onion/meta/>

## COPYRIGHT
Copyright 2016-2021 all contributors <mailto:meta@public-inbox.org>

License: AGPL-3.0+ <https://www.gnu.org/licenses/agpl-3.0.txt>


public-inbox-tuning

title: public-inbox-tuning(7)

summary: tuning public\-inbox

text:
# public-inbox-tuning(7)

## NAME
public-inbox-tuning - tuning public-inbox

## DESCRIPTION
public-inbox intends to support a wide variety of hardware. While we strive to provide the best out-of-the-box performance possible, tuning knobs are an unfortunate necessity in some cases.

1.

New inboxes: public-inbox-init -V2

2.

Optional Inline::C use

3.

Performance on rotational hard disk drives

4.

Btrfs (and possibly other copy-on-write filesystems)

5.

Performance on solid state drives

6.

Read-only daemons

7.

Other OS tuning knobs

8.

Scalability to many inboxes

9.

public-inbox-cindex --join performance

10.

public-inbox-clone with shared object stores

### New inboxes: public-inbox-init -V2
If you're starting a new inbox (and not mirroring an existing one), the -V2 requires DBD::SQLite, but is orders of magnitude more scalable than the original "-V1" format.

### Optional Inline::C use
Our optional use of Inline::C speeds up subprocess spawning from large daemon processes.

To enable Inline::C, either set the "PERL_INLINE_DIRECTORY" environment variable to point to a writable directory, or create "~/.cache/public-inbox/inline-c" for any user(s) running public-inbox processes.

If libgit2 development files are installed and Inline::C is enabled (described above), per-inbox "git cat-file --batch" processes are replaced with a single perl(1) process running "PublicInbox::Gcf2::loop" in read-only daemons. libgit2 use will be available in public-inbox 1.7.0+

More (optional) Inline::C use will be introduced in the future to lower memory use and improve scalability.

Note: Inline::C is required for lei(1), but not public-inbox-*

### Performance on rotational hard disk drives
Random I/O performance is poor on rotational HDDs. Xapian indexing performance degrades significantly as DBs grow larger than available RAM. Attempts to parallelize random I/O on HDDs leads to pathological slowdowns as inboxes grow.

While "-V2" introduced Xapian shards as a parallelization mechanism for SSDs, enabling "publicInbox.indexSequentialShard" repurposes sharding as a mechanism to reduce the kernel page cache footprint when indexing on HDDs.

Initializing a mirror with a high "--jobs" count to create more shards (in "-V2" inboxes) will keep each shard smaller and reduce its kernel page cache footprint. Keep in mind excessive sharding imposes a performance penalty for read-only queries.

Users with large amounts of RAM are advised to set a large value for "publicinbox.indexBatchSize" as documented in public-inbox-index(1).

"dm-crypt" users on Linux 4.0+ are advised to try the "--perf-same_cpu_crypt" "--perf-submit_from_crypt_cpus" switches of cryptsetup(8) to reduce I/O contention from kernel workqueue threads.

### Btrfs (and possibly other copy-on-write filesystems)
btrfs(5) performance degrades from fragmentation when using large databases and random writes. The Xapian + SQLite indices used by public-inbox are no exception to that.

public-inbox 1.6.0+ disables copy-on-write (CoW) on Xapian and SQLite indices on btrfs to achieve acceptable performance (even on SSD). Disabling copy-on-write also disables checksumming, thus "raid1" (or higher) configurations may be corrupt after unsafe shutdowns.

Fortunately, these SQLite and Xapian indices are designed to be recoverable from git if missing.

Disabling CoW does not prevent all fragmentation. Large values of "publicInbox.indexBatchSize" also limit fragmentation during the initial index.

Avoid snapshotting subvolumes containing Xapian and/or SQLite indices. Snapshots use CoW despite our efforts to disable it, resulting in fragmentation.

filefrag(8) can be used to monitor fragmentation, and "btrfs filesystem defragment -fr $INBOX_DIR" may be necessary.

Large filesystems benefit significantly from the "space_cache=v2" mount option documented in btrfs(5).

Older, non-CoW filesystems generally work well out of the box for our Xapian and SQLite indices.

### Performance on solid state drives
While SSD read performance is generally good, SSD write performance degrades as the drive ages and/or gets full. Issuing "TRIM" commands via fstrim(8) or similar is required to sustain write performance.

Users of the Flash-Friendly File System F2FS <https://en.wikipedia.org/wiki/F2FS> may benefit from optimizations found in SQLite 3.21.0+. Benchmarks are greatly appreciated.

### Read-only daemons
public-inbox-httpd(1), public-inbox-imapd(1), and public-inbox-nntpd(1) are all designed for C10K (or higher) levels of concurrency from a single process. SMP systems may use "--worker-processes=NUM" as documented in public-inbox-daemon(8) for parallelism.

The open file descriptor limit ("RLIMIT_NOFILE", "ulimit -n" in sh(1), "LimitNOFILE=" in systemd.exec(5)) may need to be raised to accommodate many concurrent clients.

Transport Layer Security (IMAPS, NNTPS, or via STARTTLS) significantly increases memory use of client sockets, be sure to account for that in capacity planning.

### Other OS tuning knobs
Linux users: the "sys.vm.max_map_count" sysctl may need to be increased if handling thousands of inboxes (with public-inbox-extindex(1)) to avoid out-of-memory errors from git.

Other OSes may have similar tuning knobs (patches appreciated).

### Scalability to many inboxes
public-inbox-extindex(1) allows any number of public-inboxes to share the same Xapian indices.

git 2.33+ startup time is orders of magnitude faster and uses less memory when dealing with thousands of alternates required for thousands of inboxes with public-inbox-extindex(1).

Frequent packing (via git-gc(1)) both improves performance and reduces the need to increase "sys.vm.max_map_count".

### public-inbox-cindex --join performance
A C++ compiler and the Xapian development files makes "--join" or "--join=aggressive" orders of magnitude faster in public-inbox-cindex(1). On Debian-based systems this is "libxapian-dev". RPM-based distros have these in "xapian-core-devel" or "xapian14-core-libs". *BSDs typically package development files together with runtime libraries, so the "xapian" or "xapian-core" package will already have the development files.

### public-inbox-clone with shared object stores
When mirroring manifests with many forks using the same objstore, git 2.41+ is highly recommended for performance as we automatically use the "fetch.hideRefs" feature to speed up negotiation.

## CONTACT
Feedback encouraged via plain-text mail to <mailto:meta@public-inbox.org>

Information for *BSDs and non-traditional filesystems especially welcome.

Our archives are hosted at <https://public-inbox.org/meta/>, <http://4uok3hntl7oi7b4uf4rtfwefqeexfzil2w6kgk2jn5z2f764irre7byd.onion/meta/>, and other places

## COPYRIGHT
Copyright all contributors <mailto:meta@public-inbox.org>

License: AGPL-3.0+ <https://www.gnu.org/licenses/agpl-3.0.txt>


lxc

package: pve-lxc

title: lxc(7)

summary: linux containers

text:
# lxc(7)

## NAME
lxc - linux containers

## OVERVIEW
The container technology is actively being pushed into the mainstream Linux kernel. It provides resource management through control groups and resource isolation via namespaces.

lxc, aims to use these new functionalities to provide a userspace container object which provides full resource isolation and resource control for an applications or a full system.

lxc is small enough to easily manage a container with simple command lines and complete enough to be used for other purposes.

## REQUIREMENTS
The kernel version >= 3.10 shipped with the distros, will work with lxc, this one will have less functionalities but enough to be interesting.

lxc relies on a set of functionalities provided by the kernel. The helper script lxc-checkconfig will give you information about your kernel configuration, required, and missing features.

## FUNCTIONAL SPECIFICATION
A container is an object isolating some resources of the host, for the application or system running in it.

The application / system will be launched inside a container specified by a configuration that is either initially created or passed as a parameter of the commands.

How to run an application in a container

Before running an application, you should know what are the resources you want to isolate. The default configuration is to isolate PIDs, the sysv IPC and mount points. If you want to run a simple shell inside a container, a basic configuration is needed, especially if you want to share the rootfs. If you want to run an application like sshd, you should provide a new network stack and a new hostname. If you want to avoid conflicts with some files eg. /var/run/httpd.pid, you should remount /var/run with an empty directory. If you want to avoid the conflicts in all the cases, you can specify a rootfs for the container. The rootfs can be a directory tree, previously bind mounted with the initial rootfs, so you can still use your distro but with your own /etc and /home

Here is an example of directory tree for sshd:

```

[root@lxc sshd]$ tree -d rootfs

rootfs
|-- bin
|-- dev
|   |-- pts
|   `-- shm
|       `-- network
|-- etc
|   `-- ssh
|-- lib
|-- proc
|-- root
|-- sbin
|-- sys
|-- usr
`-- var

    |-- empty

    |   `-- sshd

    |-- lib

    |   `-- empty

    |       `-- sshd

    `-- run

        `-- sshd

```

and the mount points file associated with it:

```
	[root@lxc sshd]$ cat fstab
	/lib /home/root/sshd/rootfs/lib none ro,bind 0 0
	/bin /home/root/sshd/rootfs/bin none ro,bind 0 0
	/usr /home/root/sshd/rootfs/usr none ro,bind 0 0
	/sbin /home/root/sshd/rootfs/sbin none ro,bind 0 0

```

How to run a system in a container

Running a system inside a container is paradoxically easier than running an application. Why? Because you don't have to care about the resources to be isolated, everything needs to be isolated, the other resources are specified as being isolated but without configuration because the container will set them up. eg. the ipv4 address will be setup by the system container init scripts. Here is an example of the mount points file:

```
	[root@lxc debian]$ cat fstab
	/dev	/home/root/debian/rootfs/dev none bind 0 0
	/dev/pts /home/root/debian/rootfs/dev/pts  none bind 0 0

```

### CONTAINER LIFE CYCLE
When the container is created, it contains the configuration information. When a process is launched, the container will be starting and running. When the last process running inside the container exits, the container is stopped.

In case of failure when the container is initialized, it will pass through the aborting state.

```
   ---------

  | STOPPED |<---------------

   ---------                 |

       |                     |

     start                   |

       |                     |

       V                     |

   ----------                |

  | STARTING |--error-       |

   ----------         |      |

       |              |      |

       V              V      |

   ---------    ----------   |

  | RUNNING |  | ABORTING |  |

   ---------    ----------   |

       |              |      |

  no process          |      |

       |              |      |

       V              |      |

   ----------         |      |

  | STOPPING |<-------       |

   ----------                |

       |                     |

        ---------------------

```

### CONFIGURATION
The container is configured through a configuration file, the format of the configuration file is described in lxc.conf(5)

### UNPRIVILEGED CONTAINERS
Unprivileged LXC containers run without root host-level privileges in a user namespace, mapping container UID 0 to a non-root host ID, which strictly limits the accessible devices and filesystems of the container. In order to mount a rootfs in an unprivileged container, the mapped host user must have execute permissions for all directories along the path to and including the rootfs. Additionally, all files and directories under the rootfs must be owned by the correct user ID and group ID. The correct user ID and group ID are the host IDs mapped to the container root(UID 0) in lxc.idmap.

### CREATING / DESTROYING CONTAINERS
A persistent container object can be created via the lxc-create command. It takes a container name as parameter and optional configuration file and template. The name is used by the different commands to refer to this container. The lxc-destroy command will destroy the container object.

```
	  lxc-create -n foo
	  lxc-destroy -n foo

```

### VOLATILE CONTAINER
It is not mandatory to create a container object before starting it. The container can be directly started with a configuration file as parameter.

### STARTING / STOPPING CONTAINER
When the container has been created, it is ready to run an application / system. This is the purpose of the lxc-execute and lxc-start commands. If the container was not created before starting the application, the container will use the configuration file passed as parameter to the command, and if there is no such parameter either, then it will use a default isolation. If the application ended, the container will be stopped, but if needed the lxc-stop command can be used to stop the container.

Running an application inside a container is not exactly the same thing as running a system. For this reason, there are two different commands to run an application into a container:

```
	  lxc-execute -n foo [-f config] /bin/bash
	  lxc-start -n foo [-f config] [/bin/bash]

```

The lxc-execute command will run the specified command into a container via an intermediate process, lxc-init. This lxc-init after launching the specified command, will wait for its end and all other reparented processes. (to support daemons in the container). In other words, in the container, lxc-init has PID 1 and the first process of the application has PID 2.

The lxc-start command will directly run the specified command in the container. The PID of the first process is 1. If no command is specified lxc-start will run the command defined in lxc.init.cmd or if not set, /sbin/init .

To summarize, lxc-execute is for running an application and lxc-start is better suited for running a system.

If the application is no longer responding, is inaccessible or is not able to finish by itself, a wild lxc-stop command will kill all the processes in the container without pity.

```
	  lxc-stop -n foo -k

```

### CONNECT TO AN AVAILABLE TTY
If the container is configured with ttys, it is possible to access it through them. It is up to the container to provide a set of available ttys to be used by the following command. When the tty is lost, it is possible to reconnect to it without login again.

```
	  lxc-console -n foo -t 3

```

### FREEZE / UNFREEZE CONTAINER
Sometime, it is useful to stop all the processes belonging to a container, eg. for job scheduling. The commands:

```
	  lxc-freeze -n foo

```

will put all the processes in an uninteruptible state and

```
	  lxc-unfreeze -n foo

```

will resume them.

This feature is enabled if the freezer cgroup v1 controller is enabled in the kernel.

### GETTING INFORMATION ABOUT CONTAINER
When there are a lot of containers, it is hard to follow what has been created or destroyed, what is running or what are the PIDs running in a specific container. For this reason, the following commands may be useful:

```
	  lxc-ls -f
	  lxc-info -n foo

```

lxc-ls lists containers.

lxc-info gives information for a specific container.

Here is an example on how the combination of these commands allows one to list all the containers and retrieve their state.

```
	  for i in $(lxc-ls -1); do
	    lxc-info -n $i
	  done

```

### MONITORING CONTAINER
It is sometime useful to track the states of a container, for example to monitor it or just to wait for a specific state in a script.

lxc-monitor command will monitor one or several containers. The parameter of this command accepts a regular expression for example:

```
	  lxc-monitor -n "foo|bar"

```

will monitor the states of containers named 'foo' and 'bar', and:

```
	  lxc-monitor -n ".*"

```

will monitor all the containers.

For a container 'foo' starting, doing some work and exiting, the output will be in the form:

```
	  'foo' changed state to [STARTING]
	  'foo' changed state to [RUNNING]
	  'foo' changed state to [STOPPING]
	  'foo' changed state to [STOPPED]

```

lxc-wait command will wait for a specific state change and exit. This is useful for scripting to synchronize the launch of a container or the end. The parameter is an ORed combination of different states. The following example shows how to wait for a container if it successfully started as a daemon.

```
	  # launch lxc-wait in background
	  lxc-wait -n foo -s STOPPED &
	  LXC_WAIT_PID=$!
	  # this command goes in background
	  lxc-execute -n foo mydaemon &
	  # block until the lxc-wait exits
	  # and lxc-wait exits when the container
	  # is STOPPED
	  wait $LXC_WAIT_PID
	  echo "'foo' is finished"

```

### CGROUP SETTINGS FOR CONTAINERS
The container is tied with the control groups, when a container is started a control group is created and associated with it. The control group properties can be read and modified when the container is running by using the lxc-cgroup command.

lxc-cgroup command is used to set or get a control group subsystem which is associated with a container. The subsystem name is handled by the user, the command won't do any syntax checking on the subsystem name, if the subsystem name does not exists, the command will fail.

```
	  lxc-cgroup -n foo cpuset.cpus

```

will display the content of this subsystem.

```
	  lxc-cgroup -n foo cpu.shares 512

```

will set the subsystem to the specified value.

## SEE ALSO
lxc(7), lxc-create(1), lxc-copy(1), lxc-destroy(1), lxc-start(1), lxc-stop(1), lxc-execute(1), lxc-console(1), lxc-monitor(1), lxc-wait(1), lxc-cgroup(1), lxc-ls(1), lxc-info(1), lxc-freeze(1), lxc-unfreeze(1), lxc-attach(1), lxc.conf(5)