community.postgresql.postgresql_sequence – Create, drop, or alter a PostgreSQL sequence

Note

This plugin is part of the community.postgresql collection (version 1.1.1).

To install it use: ansible-galaxy collection install community.postgresql.

To use it in a playbook, specify: community.postgresql.postgresql_sequence.

Synopsis

  • Allows to create, drop or change the definition of a sequence generator.

Requirements

The below requirements are needed on the host that executes this module.

  • psycopg2

Parameters

Parameter Choices/Defaults Comments
ca_cert
string
Specifies the name of a file containing SSL certificate authority (CA) certificate(s).
If the file exists, the server's certificate will be verified to be signed by one of these authorities.

aliases: ssl_rootcert
cache
integer
Cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default.
cascade
boolean
    Choices:
  • no ←
  • yes
Automatically drop objects that depend on the sequence, and in turn all objects that depend on those objects.
Ignored if state=present.
Only used with state=absent.
cycle
boolean
    Choices:
  • no ←
  • yes
The cycle option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively.
If false (NO CYCLE) is specified, any calls to nextval after the sequence has reached its maximum value will return an error. False (NO CYCLE) is the default.
data_type
string
    Choices:
  • bigint
  • integer
  • smallint
Specifies the data type of the sequence. Valid types are bigint, integer, and smallint. bigint is the default. The data type determines the default minimum and maximum values of the sequence. For more info see the documentation https://www.postgresql.org/docs/current/sql-createsequence.html.
Supported from PostgreSQL 10.
db
string
Name of database to connect to and run queries against.

aliases: database, login_db
increment
integer
Increment specifies which value is added to the current sequence value to create a new value.
A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1.
login_host
string
Host running the database.
login_password
string
The password used to authenticate with.
login_unix_socket
string
Path to a Unix domain socket for local connections.
login_user
string
Default:
"postgres"
The username used to authenticate with.
maxvalue
integer
Maxvalue determines the maximum value for the sequence. The default for an ascending sequence is the maximum value of the data type. The default for a descending sequence is -1.

aliases: max
minvalue
integer
Minvalue determines the minimum value a sequence can generate. The default for an ascending sequence is 1. The default for a descending sequence is the minimum value of the data type.

aliases: min
newschema
string
The new schema for the sequence. Will be used for moving a sequence to another schema.
Works only for existing sequences.
owner
string
Set the owner for the sequence.
port
integer
Default:
5432
Database port to connect to.

aliases: login_port
rename_to
string
The new name for the sequence.
Works only for existing sequences.
schema
string
Default:
"public"
The schema of the sequence. This is be used to create and relocate a sequence in the given schema.
sequence
string / required
The name of the sequence.

aliases: name
session_role
string
Switch to session_role after connecting. The specified session_role must be a role that the current login_user is a member of.
Permissions checking for SQL commands is carried out as though the session_role were the one that had logged in originally.
ssl_mode
string
    Choices:
  • allow
  • disable
  • prefer ←
  • require
  • verify-ca
  • verify-full
Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server.
Default of prefer matches libpq default.
start
integer
Start allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones.
state
string
    Choices:
  • absent
  • present ←
The sequence state.
If state=absent other options will be ignored except of name and schema.
trust_input
boolean
added in 0.2.0 of community.postgresql
    Choices:
  • no
  • yes ←
If no, check whether values of parameters sequence, schema, rename_to, owner, newschema, session_role are potentially dangerous.
It makes sense to use no only when SQL injections via the parameters are possible.

Notes

Note

  • Supports check_mode.

  • If you do not pass db parameter, sequence will be created in the database named postgres.

  • The default authentication assumes that you are either logging in as or sudo’ing to the postgres account on the host.

  • To avoid “Peer authentication failed for user postgres” error, use postgres user as a become_user.

  • This module uses psycopg2, a Python PostgreSQL database adapter. You must ensure that psycopg2 is installed on the host before using this module.

  • If the remote host is the PostgreSQL server (which is the default case), then PostgreSQL must also be installed on the remote host.

  • For Ubuntu-based systems, install the postgresql, libpq-dev, and python-psycopg2 packages on the remote host before using this module.

  • The ca_cert parameter requires at least Postgres version 8.4 and psycopg2 version 2.4.3.

See Also

See also

community.postgresql.postgresql_table

The official documentation on the community.postgresql.postgresql_table module.

community.postgresql.postgresql_owner

The official documentation on the community.postgresql.postgresql_owner module.

community.postgresql.postgresql_privs

The official documentation on the community.postgresql.postgresql_privs module.

community.postgresql.postgresql_tablespace

The official documentation on the community.postgresql.postgresql_tablespace module.

CREATE SEQUENCE reference

Complete reference of the CREATE SEQUENCE command documentation.

ALTER SEQUENCE reference

Complete reference of the ALTER SEQUENCE command documentation.

DROP SEQUENCE reference

Complete reference of the DROP SEQUENCE command documentation.

Examples

- name: Create an ascending bigint sequence called foobar in the default
        database
  community.postgresql.postgresql_sequence:
    name: foobar

- name: Create an ascending integer sequence called foobar, starting at 101
  community.postgresql.postgresql_sequence:
    name: foobar
    data_type: integer
    start: 101

- name: Create an descending sequence called foobar, starting at 101 and
        preallocated 10 sequence numbers in cache
  community.postgresql.postgresql_sequence:
    name: foobar
    increment: -1
    cache: 10
    start: 101

- name: Create an ascending sequence called foobar, which cycle between 1 to 10
  community.postgresql.postgresql_sequence:
    name: foobar
    cycle: yes
    min: 1
    max: 10

- name: Create an ascending bigint sequence called foobar in the default
        database with owner foobar
  community.postgresql.postgresql_sequence:
    name: foobar
    owner: foobar

- name: Rename an existing sequence named foo to bar
  community.postgresql.postgresql_sequence:
    name: foo
    rename_to: bar

- name: Change the schema of an existing sequence to foobar
  community.postgresql.postgresql_sequence:
    name: foobar
    newschema: foobar

- name: Change the owner of an existing sequence to foobar
  community.postgresql.postgresql_sequence:
    name: foobar
    owner: foobar

- name: Drop a sequence called foobar
  community.postgresql.postgresql_sequence:
    name: foobar
    state: absent

- name: Drop a sequence called foobar with cascade
  community.postgresql.postgresql_sequence:
    name: foobar
    cascade: yes
    state: absent

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key Returned Description
cycle
string
always
Shows if the sequence cycle or not.

Sample:
NO
data_type
string
always
Shows the current data type of the sequence.

Sample:
bigint
increment
integer
always
The value of increment of the sequence. A positive value will make an ascending sequence, a negative one a descending sequence.

Sample:
-1
maxvalue
integer
always
The value of maxvalue of the sequence.

Sample:
9223372036854775807
minvalue
integer
always
The value of minvalue of the sequence.

Sample:
1
newname
string
on success
Shows the new sequence name after rename.

Sample:
barfoo
newschema
string
on success
Shows the new schema of the sequence after schema change.

Sample:
foobar
owner
string
always
Shows the current owner of the sequence after the successful run of the task.

Sample:
postgres
queries
string
always
List of queries that was tried to be executed.

Sample:
['CREATE SEQUENCE "foo"']
schema
string
always
Name of the schema of the sequence.

Sample:
foo
sequence
string
always
Sequence name.

Sample:
foobar
start
integer
always
The value of start of the sequence.

Sample:
12
state
string
always
Sequence state at the end of execution.

Sample:
present


Authors

  • Tobias Birkefeld (@tcraxs)

  • Thomas O’Donnell (@andytom)