Administration

==============

This chapter covers the administration of py-postgresql. This includes

installation and other aspects of working with py-postgresql such as

environment variables and configuration files.

Installation

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

py-postgresql uses Python's standard distutils package to manage the

build and installation process of the package. The normal entry point for

this is the ``setup.py`` script contained in the root project directory.

After extracting the archive and changing the into the project's directory,

installation is normally as simple as::

$ python3 ./setup.py install

However, if you need to install for use with a particular version of python,

just use the path of the executable that should be used::

$ /usr/opt/bin/python3 ./setup.py install

Under most POSIX systems, the above should work without problem if the proper

Python executable is referenced. However, if it does fail, it is likely due

to a C extension's inability to compile.

The building of C extensions can be disable using the ``PY_BUILD_EXTENSIONS``

environment variable::

$ env PY_BUILD_EXTENSIONS=0 python3 ./setup.py install

Extension Modules under Windows

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

By default, a Python installation on Windows cannot build extension modules.

py-postgresql provides optimizations for various key points, but can be

installed and used without them. When a source installation is performed on

'win32' systems, extension modules are *not* built by default.

In order to enable the compilation of extensions, set the environment variable

``PY_BUILD_EXTENSIONS`` to '1' before executing the ``setup.py``

script in the prompt::

C:\-> set PY_BUILD_EXTENSIONS=1

C:\-> c:\python31\python setup.py install

Or, compile using mingw32::

C:\-> set PY_BUILD_EXTENSIONS=1

C:\-> c:\python31\python setup.py build_ext --compiler=mingw32

C:\-> c:\python31\python setup.py install

See http://www.mingw.org/ to get the compiler.

Environment

-----------

These environment variables effect the operation of the package:

============== ===============================================================================

PGINSTALLATION The path to the ``pg_config`` executable of the installation to use by default.

============== ===============================================================================

.. _alock:

**************

Advisory Locks

**************

`Explicit Locking in PostgreSQL <http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS>`_.

PostgreSQL's advisory locks offer a cooperative synchronization primitive.

These are used in cases where an application needs access to a resource, but

using table locks may cause interference with other operations that can be

safely performed alongside the application-level, exclusive operation.

Advisory locks can be used by directly executing the stored procedures in the

database or by using the :class:`postgresql.alock.ALock` subclasses, which

provides a context manager that uses those stored procedures.

Currently, only two subclasses exist. Each represents the lock mode

supported by PostgreSQL's advisory locks:

* :class:`postgresql.alock.ShareLock`

* :class:`postgresql.alock.ExclusiveLock`

Acquiring ALocks

================

An ALock instance represents a sequence of advisory locks. A single ALock can

acquire and release multiple advisory locks by creating the instance with

multiple lock identifiers::

>>> from postgresql import alock

>>> table1_oid = 192842

>>> table2_oid = 192849

>>> l = alock.ExclusiveLock(db, (table1_oid, 0), (table2_oid, 0))

>>> l.acquire()

>>> ...

>>> l.release()

:class:`postgresql.alock.ALock` is similar to :class:`threading.RLock`; in

order for an ALock to be released, it must be released the number of times it

has been acquired. ALocks are associated with and survived by their session.

Much like how RLocks are associated with the thread they are acquired in:

acquiring an ALock again will merely increment its count.

PostgreSQL allows advisory locks to be identified using a pair of `int4` or a

single `int8`. ALock instances represent a *sequence* of those identifiers::

>>> from postgresql import alock

>>> ids = [(0,0), 0, 1]

>>> with alock.ShareLock(db, *ids):

... ...

Both types of identifiers may be used within the same ALock, and, regardless of

their type, will be aquired in the order that they were given to the class'

constructor. In the above example, ``(0,0)`` is acquired first, then ``0``, and

lastly ``1``.

ALocks

======

`postgresql.alock.ALock` is abstract; it defines the interface and some common

functionality. The lock mode is selected by choosing the appropriate subclass.

There are two:

``postgresql.alock.ExclusiveLock(database, *identifiers)``

Instantiate an ALock object representing the `identifiers` for use with the

`database`. Exclusive locks will conflict with other exclusive locks and share

locks.

``postgresql.alock.ShareLock(database, *identifiers)``

Instantiate an ALock object representing the `identifiers` for use with the

`database`. Share locks can be acquired when a share lock with the same

identifier has been acquired by another backend. However, an exclusive lock

with the same identifier will conflict.

ALock Interface Points

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

Methods and properties available on :class:`postgresql.alock.ALock` instances:

``alock.acquire(blocking = True)``

Acquire the advisory locks represented by the ``alock`` object. If blocking is

`True`, the default, the method will block until locks on *all* the

identifiers have been acquired.

If blocking is `False`, acquisition may not block, and success will be

indicated by the returned object: `True` if *all* lock identifiers were

acquired and `False` if any of the lock identifiers could not be acquired.

``alock.release()``

Release the advisory locks represented by the ``alock`` object. If the lock

has not been acquired, a `RuntimeError` will be raised.

``alock.locked()``

Returns a boolean describing whether the locks are held or not. This will

return `False` if the lock connection has been closed.

``alock.__enter__()``

Alias to ``acquire``; context manager protocol. Always blocking.

``alock.__exit__(typ, val, tb)``

Alias to ``release``; context manager protocol.

Commands

********

This chapter discusses the usage of the available console scripts.

postgresql.bin.pg_python

========================

The ``pg_python`` command provides a simple way to write Python scripts against a

single target database. It acts like the regular Python console command, but

takes standard PostgreSQL options as well to specify the client parameters

to make establish connection with. The Python environment is then augmented

with the following built-ins:

``db``

The PG-API connection object.

``xact``

``db.xact``, the transaction creator.

``settings``

``db.settings``

``prepare``

``db.prepare``, the statement creator.

``proc``

``db.proc``

``do``

``db.do``, execute a single DO statement.

``sqlexec``

``db.execute``, execute multiple SQL statements (``None`` is always returned)

pg_python Usage

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

Usage: postgresql.bin.pg_python [connection options] [script] ...

Options:

--unix=UNIX path to filesystem socket

--ssl-mode=SSLMODE SSL requirement for connectivity: require, prefer,

allow, disable

-s SETTINGS, --setting=SETTINGS

run-time parameters to set upon connecting

-I PQ_IRI, --iri=PQ_IRI

database locator string

[pq://user:password@host:port/database?setting=value]

-h HOST, --host=HOST database server host

-p PORT, --port=PORT database server port

-U USER, --username=USER

user name to connect as

-W, --password prompt for password

-d DATABASE, --database=DATABASE

database's name

--pq-trace=PQ_TRACE trace PQ protocol transmissions

-C PYTHON_CONTEXT, --context=PYTHON_CONTEXT

Python context code to run[file://,module:,<code>]

-m PYTHON_MAIN Python module to run as script(__main__)

-c PYTHON_MAIN Python expression to run(__main__)

--version show program's version number and exit

--help show this help message and exit

Interactive Console Backslash Commands

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

Inspired by ``psql``::

>>> \?

Backslash Commands:

\? Show this help message.

\E Edit a file or a temporary script.

\e Edit and Execute the file directly in the context.

\i Execute a Python script within the interpreter's context.

\set Configure environment variables. \set without arguments to show all

\x Execute the Python command within this process.

pg_python Examples

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

Module execution taking advantage of the new built-ins::

$ python3 -m postgresql.bin.pg_python -h localhost -W -m timeit "prepare('SELECT 1').first()"

Password for pg_python[pq://jwp@localhost:5432]:

1000 loops, best of 3: 1.35 msec per loop

$ python3 -m postgresql.bin.pg_python -h localhost -W -m timeit -s "ps=prepare('SELECT 1')" "ps.first()"

Password for pg_python[pq://jwp@localhost:5432]:

1000 loops, best of 3: 442 usec per loop

Simple interactive usage::

$ python3 -m postgresql.bin.pg_python -h localhost -W

Password for pg_python[pq://jwp@localhost:5432]:

>>> ps = prepare('select 1')

>>> ps.first()

1

>>> c = ps()

>>> c.read()

[(1,)]

>>> ps.close()

>>> import sys

>>> sys.exit(0)

postgresql.bin.pg_dotconf

=========================

pg_dotconf is used to modify a PostgreSQL cluster's configuration file.

It provides a means to apply settings specified from the command line and from a

file referenced using the ``-f`` option.

.. warning::

``include`` directives in configuration files are *completely* ignored. If

modification of an included file is desired, the command must be applied to

that specific file.

pg_dotconf Usage

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

Usage: postgresql.bin.pg_dotconf [--stdout] [-f filepath] postgresql.conf ([param=val]|[param])*

Options:

--version show program's version number and exit

-h, --help show this help message and exit

-f SETTINGS, --file=SETTINGS

A file of settings to *apply* to the given

"postgresql.conf"

--stdout Redirect the product to standard output instead of

writing back to the "postgresql.conf" file

Examples

--------

Modifying a simple configuration file::

$ echo "setting = value" >pg.conf

# change 'setting'

$ python3 -m postgresql.bin.pg_dotconf pg.conf setting=newvalue

$ cat pg.conf

setting = 'newvalue'

# new settings are appended to the file

$ python3 -m postgresql.bin.pg_dotconf pg.conf another_setting=value

$ cat pg.conf

setting = 'newvalue'

another_setting = 'value'

# comment a setting

$ python3 -m postgresql.bin.pg_dotconf pg.conf another_setting

$ cat pg.conf

setting = 'newvalue'

#another_setting = 'value'

When a setting is given on the command line, it must been seen as one argument

to the command, so it's *very* important to avoid invocations like::

$ python3 -m postgresql.bin.pg_dotconf pg.conf setting = value

ERROR: invalid setting, '=' after 'setting'

HINT: Settings must take the form 'setting=value' or 'setting_name_to_comment'. Settings must also be received as a single argument.

Changes

=======

1.0 in development

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

* **DEPRECATION**: Removed 2PC support documentation.

* **DEPRECATION**: Removed pg_python and pg_dotconf 'scripts'.

They are still accessible by python3 -m postgresql.bin.pg_*

* Added db.do() method for DO-statement support(convenience method).

* Fix handling of unix domain sockets by pg.open and driver.connect.

[Reported by twitter.com/rintavarustus]

* Set the default client_min_messages level to WARNING.

NOTICEs are often not desired by programmers, and py-postgresql's

high verbosity further irritates that case.

* Added postgresql.project module to provide project information.

Project name, author, version, etc.

* Fixed db.tracer and pg_python's --pq-trace=

* Increased default recvsize and chunksize for improved performance.

* 'D' messages are special cased as builtins.tuples instead of

protocol.element3.Tuple

* Alter Statement.chunks() to return chunks of builtins.tuple. Being

an interface intended for speed, types.Row() impedes its performance.

* Fix handling of infinity values with timestamptz, timestamp, and date.

[Bug reported by Axel Rau.]

* Correct representation of PostgreSQL ARRAYs by properly recording

lowerbounds and upperbounds. Internally, sub-ARRAYs have their own

element lists.

* Implement a NotificationManager for managing the NOTIFYs received

by a connection. The class can manage NOTIFYs from multiple

connections, whereas the db.wait() method is tailored for single targets.

* Implement an ALock class for managing advisory locks using the

threading.Lock APIs. [Feedback from Valentine Gogichashvili]

* Implement reference symbols. Allow libraries to define symbols that

are used to create queries that inherit the original symbol's type and

execution method. ``db.prepare(db.prepare(...).first())``

* Fix typo/dropped parts of a raise LoadError in .lib.

[Reported by Vlad Pranskevichus]

* Fix db.tracer and pg_python's --pq-trace=

* Fix count return from .first() method. Failed to provide an empty

tuple for the rformats of the bind statement.

[Reported by dou dou]

* Add support for binary hstore.