l.s.d.postgresql : module documentation

Return a list of all foreign key references to the given table column

table and column are both case sensitive strings (so they should usually be lowercase strings as per PostgreSQL default behaviour).

cur is an open DB-API cursor

returns [(from_table, from_column, to_table, to_column, update, delete)]

from entries refer to the to entries. If indirect is True, the this method is recursive - not only does it return all references to the given table column, but also all references to those references etc. ie (indirect references).

update is the update clause (eg. on update cascade) delete is the delete clause (eg. on delete cascade)

Entries are returned in order traversed, so with care this can be used to change keys.

>>> for r in listReferences(cur, 'a', 'aid'):
...     print repr(r)
(u'a', u'selfref', u'a', u'aid', u'a', u'a')
(u'b', u'aid', u'a', u'aid', u'c', u'c')
(u'c', u'aid', u'b', u'aid', u'a', u'a')
(u'd', u'aid', u'b', u'aid', u'a', u'a')

Of course, there might not be any references

>>> listReferences(cur, 'a', 'selfref')
[]

Return a list of unique indexes on table that include the column

cur must be an open DB-API cursor.

Returns [ (column, [...]) ]. The column passed in will always be included in the tuple.

Simple UNIQUE index

>>> listUniques(cur, 'b', 'aid')
[(u'aid',)]

Primary keys are UNIQUE indexes too

>>> listUniques(cur, 'a', 'aid')
[(u'aid',)]

Compound indexes

>>> listUniques(cur, 'c', 'aid')
[(u'aid', u'bid')]
>>> listUniques(cur, 'c', 'bid')
[(u'aid', u'bid')]

And any combination

>>> l = listUniques(cur, 'd', 'aid')
>>> l.sort()
>>> l
[(u'aid',), (u'aid', u'bid')]

If there are no UNIQUE indexes using the secified column

>>> listUniques(cur, 'a', 'selfref')
[]

Return a list of (schema, sequence, table, column) tuples.

table and column refer to the column that appears to be automatically populated from the sequence. They will be None if this sequence is standalone.

>>> for r in listSequences(cur):
...     print repr(r)
(u'public', u'a_aid_seq', u'a', u'aid')
(u'public', u'standalone', None, None)

Reset table sequences to match the data in them.

Goes through the database resetting the values of sequences to match what is in their corresponding tables, where corresponding tables are known.

>>> cur.execute("SELECT nextval('a_aid_seq')")
>>> int(cur.fetchone()[0])
1
>>> cur.execute("SELECT nextval('a_aid_seq')")
>>> cur.execute("SELECT nextval('a_aid_seq')")
>>> resetSequences(cur)
>>> cur.execute("SELECT nextval('a_aid_seq')")
>>> int(cur.fetchone()[0])
1

Ask the PostgreSQL query optimizer for an estimated rowcount.

Stats will only be acurate if the table has been ANALYZEd recently. With standard Ubuntu installs, the autovacuum daemon does this.

>>> cur.execute("INSERT INTO A (selfref) VALUES (NULL)")
>>> cur.execute("ANALYZE A")
>>> estimateRowCount(cur, "SELECT * FROM A")
1
>>> cur.executemany(
...     "INSERT INTO A (selfref) VALUES (NULL)",
...     [(i,) for i in range(100)]
...     )
>>> cur.execute("ANALYZE A")
>>> estimateRowCount(cur, "SELECT * FROM A")
101

Is there a table of the given name?

Returns boolean answer.

>>> have_table(cur, 'thistabledoesnotexist_i_hope')
False
>>> cur.execute("CREATE TEMP TABLE atesttable (x integer)")
>>> have_table(cur, 'atesttable')
True
>>> drop_tables(cur, 'atesttable')
>>> have_table(cur, 'atesttable')
False

Does a table of the given name exist and have the given column?

Returns boolean answer.

>>> cur.execute("CREATE TEMP TABLE atesttable (x integer)")
>>> table_has_column(cur, 'atesttable', 'x')
True
>>> table_has_column(cur, 'atesttable', 'z')
False
>>> table_has_column(cur, 'thistabledoesnotexist_i_hope', 'pphwt')
False
>>> drop_tables(cur, 'atesttable')
>>> table_has_column(cur, 'atesttable', 'x')
False

Drop given tables (a list, one name, or None), if they exist.

>>> cur.execute("CREATE TEMP TABLE foo (a integer)")
>>> have_table(cur, 'foo')
True
>>> table_has_column(cur, 'foo', 'a')
True
>>> cur.execute("CREATE TEMP TABLE bar (b varchar)")
>>> have_table(cur, 'bar')
True
>>> cur.execute("INSERT INTO foo values (1)")
>>> cur.execute("INSERT INTO bar values ('hi mom')")
>>> drop_tables(cur, ['thistabledoesnotexist_i_hope', 'foo', 'bar'])
>>> have_table(cur, 'foo')
False
>>> have_table(cur, 'bar')
False
>>> drop_tables(cur, [])    # No explosion
>>> drop_tables(cur, None)  # No wailing sirens

Allow database to ignore indexes and scan sequentially when it wants?

DO NOT USE THIS WITHOUT REVIEW BY A DBA. When you find yourself wanting this function, chances are you're really hiding a bug in your code.

This is an unfortunate hack. In some cases we have found that postgres will resort to costly sequential scans when a perfectly good index is available. Specifically, this happened when we deleted one-third or so of a table's rows without an ANALYZE (as done by autovacuum) on the indexed column(s). Telling the database to regenerate its statistics for one primary-key indexed column costs almost nothing, but it will block for an autovacuum to complete. Autovacuums can take a long time, and currently cannot be disabled temporarily or selectively.

Instead, this function lets us tell the database to ignore the index degradation, and rely on autovacuum to restore it periodically. Pass a True or a False to change the setting for the ongoing database session. Default in PostgreSQL is False, though we seem to have it set to True in some of our databases.

>>> allow_sequential_scans(cur, True)
>>> cur.execute("SHOW enable_seqscan")
>>> print cur.fetchall()[0][0]
on

>>> allow_sequential_scans(cur, False)
>>> cur.execute("SHOW enable_seqscan")
>>> print cur.fetchall()[0][0]
off

Return the fully qualified name by combining the namespace and name.

Quoting is done for the non trivial cases.

>>> print fqn('public', 'foo')
public.foo
>>> print fqn(' foo ', '$bar')
" foo "."$bar"