This is a clause added in PG 9.2.

This is not a major issue (for me) but I noticed that when reading the json file, the index column reported on an index that used a function was incorrect.

I have some indices that are built using lower(columnname), the json reported the index on "otherfieldname" instead.

See CREATE TABLE, second paragraph under "REFERENCES reftable" for a description of MATCH syntax. Pyrseas should at least support the MATCH FULL option.

I am using Pyrseas tip version (0.6 beta) with postgresql 9.1

I have a composite type CustomType declared in schema foo
I am trying to generate diff script after having added and dropped attributes to CustomType

I observe the following issue in yamltodb generated diff script :

• ALTER TYPE for adding attributes are prefixing CustomType with schema name --> OK
• ALTER TYPE for dropping attributes are not prefixing CustomType with schema name --> NOT OK

dbtoyaml fails when a table with a foreign key has columns that have been dropped, and the FK column is after the dropped column.

(Original: dbtoyaml fails when tables in the sources schema contain multiple foreign keys.)

Test case:

create a fresh database, create a type:

create type test_type as (anattr int, anattr2 int);

Use yamltodb against the database, with the following db.yaml file (note the missing attribute):

language plpgsql:
  description: PL/pgSQL procedural language
  trusted: true
schema public:
  description: standard public schema
  type test_type:
    attributes:
    - anattr: integer

yamltodb  test testcase.yaml 

Output:

ALTER TABLE test_type DROP COLUMN anattr2;

Expected output:

ALTER TYPE test_type DROP ATTRIBUTE anattr2;

Not tested:

Other modifications: adding/altering an attribute

Currently database schema serialization format consist of single YAML file. The problem with that is when database schema becomes a bit too large — several views and functions along with twenty or so tables — maintaining and review of changes becomes a bit difficult.

What I propose is different serialization format:

• Serialization of views, functions, tables into separate files (schema.name.yaml or even using subdirs schema/name.yaml)
• Make dbtoyaml and yamltodb operate on collections of such files.

This is just a general view, details should be refined. Also, this serialization format could be optional.

Hello,
I'm trying pyrseas 0.6. I installed it and I tried to exec dbtoyaml by this syntax:

dbtoyaml -H /tmp -p 5887 -U redevolution redevolution

and I get this error message:

Traceback (most recent call last):
File "/usr/local/bin/dbtoyaml", line 9, in
load_entry_point('Pyrseas==0.6.0', 'console_scripts', 'dbtoyaml')()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbtoyaml.py", line 52, in main
no_owner=args.no_owner, no_privs=args.no_privileges)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 223, in to_map
self.from_catalog()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 161, in from_catalog
self.db = self.Dicts(self.dbconn)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 93, in init
self.triggers = TriggerDict(dbconn)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/init.py", line 389, in init
self._from_catalog()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/trigger.py", line 120, in _from_catalog
evtstart = trig.definition.index('AFTER ') + 6
ValueError: substring not found

Can you help me, please?

Thanks in advance.

Mephysto

Most of my dbs work find with dbtoyaml however I have one that gives this error every time. I tracked down the offending query in the source and ran it directly on the db with no row results, but not error either.

Output from dbtoyaml:

Traceback (most recent call last):
File "/usr/local/bin/dbtoyaml", line 9, in
load_entry_point('Pyrseas==0.4.1', 'console_scripts', 'dbtoyaml')()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbtoyaml.py", line 40, in main
dbmap = db.to_map()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 134, in to_map
self.from_catalog()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 95, in from_catalog
self.db = self.Dicts(self.dbconn)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 67, in init
self.tsparsers = TSParserDict(dbconn)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/init.py", line 305, in init
self._from_catalog()
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/init.py", line 312, in _from_catalog
for obj in self.fetch():
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/init.py", line 322, in fetch
data = self.dbconn.fetchall(self.query)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbconn.py", line 91, in fetchall
curs = self._execute(query)
File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbconn.py", line 63, in _execute
curs.execute(query)
File "/usr/local/lib/python2.7/dist-packages/psycopg2/extras.py", line 121, in execute
return _cursor.execute(self, query, vars)
psycopg2.ProgrammingError: ('column "prsnamespace" does not exist\nLINE 7: JOIN pg_namespace n ON (prsnamespace = n.oid...\n ^\n', "SELECT nspname AS schema, prsname AS name,\n prsstart::regproc AS start, prstoken::regproc AS gettoken,\n prsend::regproc AS end, prslextype::regproc AS lextypes,\n prsheadline::regproc AS headline,\n obj_description(p.oid, 'pg_ts_parser') AS description\n FROM pg_ts_parser p\n JOIN pg_namespace n ON (prsnamespace = n.oid)\n WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema')\n ORDER BY nspname, prsname")

postgresql log:

2011-11-23 08:17:16 MST LOG: 00000: duration: 0.791 ms statement: BEGIN
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 1.123 ms statement: set search_path to public, pg_catalog
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.093 ms statement: COMMIT
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.905 ms statement: SHOW server_version_num
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.060 ms statement: ROLLBACK
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.068 ms statement: BEGIN
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 17.638 ms statement: SELECT lanname AS name, lanpltrusted AS trusted,
obj_description(l.oid, 'pg_language') AS description
FROM pg_language l
WHERE lanispl
ORDER BY lanname
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.094 ms statement: ROLLBACK
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.028 ms statement: BEGIN
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 35.562 ms statement: SELECT castsource::regtype AS source,
casttarget::regtype AS target,
CASE WHEN castmethod = 'f' THEN castfunc::regprocedure
ELSE NULL::regprocedure END AS function,
castcontext AS context, castmethod AS method,
obj_description(c.oid, 'pg_cast') AS description
FROM pg_cast c
JOIN pg_type s ON (castsource = s.oid)
JOIN pg_namespace sn ON (s.typnamespace = sn.oid)
JOIN pg_type t ON (casttarget = t.oid)
JOIN pg_namespace tn ON (t.typnamespace = tn.oid)
LEFT JOIN pg_proc p ON (castfunc = p.oid)
LEFT JOIN pg_namespace pn ON (p.pronamespace = pn.oid)
WHERE substring(sn.nspname for 3) != 'pg_'
OR substring(tn.nspname for 3) != 'pg_'
OR (castfunc != 0 AND substring(pn.nspname for 3) != 'pg_')
ORDER BY castsource, casttarget
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.062 ms statement: ROLLBACK
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.028 ms statement: BEGIN
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 1.440 ms statement: SELECT nspname AS name,
obj_description(n.oid, 'pg_namespace') AS description
FROM pg_namespace n
WHERE nspname NOT IN ('pg_catalog', 'information_schema',
'pg_temp_1', 'pg_toast', 'pg_toast_temp_1')
ORDER BY nspname
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.051 ms statement: ROLLBACK
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 56.556 ms statement: SELECT nspname AS schema, typname AS name, typtype AS kind,
format_type(typbasetype, typtypmod) AS type,
typnotnull AS not_null, typdefault AS default,
ARRAY(SELECT enumlabel FROM pg_enum e WHERE t.oid = enumtypid
ORDER BY e.oid) AS labels,
typinput::regproc AS input, typoutput::regproc AS output,
typreceive::regproc AS receive, typsend::regproc AS send,
typmodin::regproc AS typmod_in,
typmodout::regproc AS typmod_out,
typanalyze::regproc AS analyze,
typlen AS internallength, typalign AS alignment,
typstorage AS storage, typdelim AS delimiter,
obj_description(t.oid, 'pg_type') AS description
FROM pg_type t
JOIN pg_namespace n ON (typnamespace = n.oid)
LEFT JOIN pg_class c ON (typrelid = c.oid)
WHERE typisdefined AND (typtype in ('d', 'e')
OR (typtype = 'c' AND relkind = 'c')
OR (typtype = 'b' AND typarray != 0))
AND nspname NOT IN ('pg_catalog', 'pg_toast',
'information_schema')
ORDER BY nspname, typname
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.072 ms statement: ROLLBACK
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:16 MST LOG: 00000: duration: 0.028 ms statement: BEGIN
2011-11-23 08:17:16 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 1013.915 ms statement: SELECT nspname AS schema, relname AS name, relkind AS kind,
CASE WHEN relkind = 'v' THEN pg_get_viewdef(c.oid, TRUE)
ELSE '' END AS definition,
obj_description(c.oid, 'pg_class') AS description
FROM pg_class c
JOIN pg_namespace ON (relnamespace = pg_namespace.oid)
WHERE relkind in ('r', 'S', 'v')
AND (nspname != 'pg_catalog'
AND nspname != 'information_schema')
ORDER BY nspname, relname
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.238 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.067 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 1.443 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_566_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.063 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.039 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 43.950 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_566_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.081 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.054 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.825 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_567_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.039 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 12.031 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_567_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.054 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.044 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.849 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_568_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.045 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.045 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 11.760 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_568_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.071 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.045 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.595 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_569_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.029 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 11.926 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_569_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.064 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.053 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 1.053 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_570_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.044 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.028 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 12.925 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_570_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.052 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.655 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_571_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.032 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.038 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 10.630 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_571_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.062 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.042 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.772 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_572_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.029 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 10.861 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_572_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.055 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.635 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_573_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.031 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 16.640 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_573_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.092 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.023 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.839 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_574_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.020 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.028 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 12.523 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_574_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.067 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.031 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.827 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_575_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.044 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.015 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 11.863 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_575_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.056 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.019 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.711 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_576_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.031 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.027 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 10.985 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_576_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.053 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.038 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.642 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_577_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.040 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.018 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 10.174 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_577_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.039 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.018 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.719 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_578_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.019 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.020 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 10.462 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_578_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.040 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.031 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.870 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM imports.apsxls_agency_579_id_seq
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.026 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.017 ms statement: BEGIN
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 10.488 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'imports.apsxls_agency_579_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:17 MST LOG: 00000: duration: 0.069 ms statement: ROLLBACK
2011-11-23 08:17:17 MST LOCATION: exec_simple_query, postgres.c:1095
.
.
.
.
.
.
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.015 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.683 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'pqmd_prevalent_diseases_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.053 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.036 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 2.175 ms statement: SELECT adrelid::regclass
FROM pg_attrdef a JOIN pg_depend ON (a.oid = objid)
WHERE refobjid = 'pqmd_prevalent_diseases_id_seq'::regclass
AND classid = 'pg_attrdef'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.040 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.017 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.510 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.pqmd_shipment_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.031 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.016 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 11.081 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'pqmd_shipment_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.056 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.031 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.751 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.pqmd_shipment_products_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.031 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.032 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 11.054 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'pqmd_shipment_products_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.050 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.032 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 2.116 ms statement: SELECT adrelid::regclass
FROM pg_attrdef a JOIN pg_depend ON (a.oid = objid)
WHERE refobjid = 'pqmd_shipment_products_id_seq'::regclass
AND classid = 'pg_attrdef'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.052 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.027 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.522 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.profile_publisher_publisher_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.040 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.035 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 11.165 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'profile_publisher_publisher_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.062 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.040 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.605 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.program_access_record_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 11.194 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'program_access_record_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.049 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.043 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.665 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.program_dbs_os989_test_record_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.042 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 11.268 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'program_dbs_os989_test_record_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.065 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.041 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 2.230 ms statement: SELECT adrelid::regclass
FROM pg_attrdef a JOIN pg_depend ON (a.oid = objid)
WHERE refobjid = 'program_dbs_os989_test_record_id_seq'::regclass
AND classid = 'pg_attrdef'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.054 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.039 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.529 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.program_dbs_record_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.041 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 11.001 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'program_dbs_record_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.060 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.724 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.reg_activate_verify_rav_pk_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.034 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.026 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 11.030 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'reg_activate_verify_rav_pk_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.061 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.693 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.reg_schl_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.042 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.148 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'reg_schl_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.068 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 1.921 ms statement: SELECT adrelid::regclass
FROM pg_attrdef a JOIN pg_depend ON (a.oid = objid)
WHERE refobjid = 'reg_schl_id_seq'::regclass
AND classid = 'pg_attrdef'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.039 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.037 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.478 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.registration_agreements_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 9.535 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'registration_agreements_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.061 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.434 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.registration_rrecnum_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.028 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 16.718 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'registration_rrecnum_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.045 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.023 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 2.281 ms statement: SELECT adrelid::regclass
FROM pg_attrdef a JOIN pg_depend ON (a.oid = objid)
WHERE refobjid = 'registration_rrecnum_seq'::regclass
AND classid = 'pg_attrdef'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.047 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.628 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.report_group_config_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.047 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.034 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.925 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'report_group_config_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.087 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.032 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.531 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.report_group_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.048 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.025 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.080 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'report_group_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.069 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.575 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.report_group_report_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.016 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 9.593 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'report_group_report_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.053 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.023 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.513 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.report_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.033 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.015 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 9.827 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'report_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.040 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.021 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.569 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.reservation_record_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.020 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.017 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 11.748 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'reservation_record_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.090 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 3.058 ms statement: SELECT adrelid::regclass
FROM pg_attrdef a JOIN pg_depend ON (a.oid = objid)
WHERE refobjid = 'reservation_record_id_seq'::regclass
AND classid = 'pg_attrdef'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.041 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.017 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.381 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.rost_event_eventid_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.018 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.015 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.140 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'rost_event_eventid_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.056 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.504 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.rost_meeting_meetingid_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.030 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.016 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 9.906 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'rost_meeting_meetingid_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.057 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.020 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.563 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.roster_event_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.018 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.015 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.153 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'roster_event_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.040 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.518 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.roster_meeting_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.015 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.759 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'roster_meeting_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.038 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.018 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.628 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.safe_search_history_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.028 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.078 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'safe_search_history_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.044 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.652 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.sdesxml_fema_exchange_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 9.852 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'sdesxml_fema_exchange_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.038 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.193 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.504 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.security_template_template_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.018 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.033 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 9.904 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'security_template_template_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.037 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.673 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.server_prefs_recordid_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.018 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.016 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 9.410 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'server_prefs_recordid_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.038 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.018 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 2.073 ms statement: SELECT adrelid::regclass
FROM pg_attrdef a JOIN pg_depend ON (a.oid = objid)
WHERE refobjid = 'server_prefs_recordid_seq'::regclass
AND classid = 'pg_attrdef'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.029 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.018 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.544 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.service_access_record_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.037 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.016 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 9.468 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'service_access_record_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.039 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.514 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.service_dbs_os989_test_record_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.022 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.016 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.111 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'service_dbs_os989_test_record_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.039 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 1.925 ms statement: SELECT adrelid::regclass
FROM pg_attrdef a JOIN pg_depend ON (a.oid = objid)
WHERE refobjid = 'service_dbs_os989_test_record_id_seq'::regclass
AND classid = 'pg_attrdef'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.027 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.016 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.432 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.service_dbs_record_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.017 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 9.978 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'service_dbs_record_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.040 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.019 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.575 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.service_post_record_id_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.020 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.016 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 10.824 ms statement: SELECT refobjid::regclass, refobjsubid
FROM pg_depend
WHERE objid = 'service_post_record_id_seq'::regclass
AND refclassid = 'pg_class'::regclass
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.038 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.028 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.637 ms statement: SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM public.servicetracker_extra10_seq
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.020 ms statement: ROLLBACK
2011-11-23 08:17:22 MST LOCATION: exec_simple_query, postgres.c:1095
2011-11-23 08:17:22 MST LOG: 00000: duration: 0.016 ms statement: BEGIN
2011-11-23 08:17:22 MST LOCATION: exec_simple

Pyrseas requires 'setuptools >= 0.6' to be available, but PyPI only have 0.6c11 version as the latest one, so I get

No local packages or download links found for setuptools>=0.6
Best match: None
Traceback (most recent call last):
  File "setup.py", line 51, in <module>
    license='BSD')
  File "/usr/local/lib/python2.6/distutils/core.py", line 152, in setup
    dist.run_commands()
  File "/usr/local/lib/python2.6/distutils/dist.py", line 975, in run_commands
    self.run_command(cmd)
  File "/usr/local/lib/python2.6/distutils/dist.py", line 995, in run_command
    cmd_obj.run()
  File "/usr/home/andreypopp/virtualenv/lib/python2.6/site-packages/setuptools-0.6c11-py2.6.egg/setuptools/command/develop.py", line 27, in run
  File "/usr/home/andreypopp/virtualenv/lib/python2.6/site-packages/setuptools-0.6c11-py2.6.egg/setuptools/command/develop.py", line 102, in install_for_development
  File "/usr/home/andreypopp/virtualenv/lib/python2.6/site-packages/setuptools-0.6c11-py2.6.egg/setuptools/command/easy_install.py", line 519, in process_distribution
  File "/usr/home/andreypopp/virtualenv/lib/python2.6/site-packages/setuptools-0.6c11-py2.6.egg/pkg_resources.py", line 563, in resolve
  File "/usr/home/andreypopp/virtualenv/lib/python2.6/site-packages/setuptools-0.6c11-py2.6.egg/pkg_resources.py", line 799, in best_match
  File "/usr/home/andreypopp/virtualenv/lib/python2.6/site-packages/setuptools-0.6c11-py2.6.egg/pkg_resources.py", line 811, in obtain
  File "/usr/home/andreypopp/virtualenv/lib/python2.6/site-packages/setuptools-0.6c11-py2.6.egg/setuptools/command/easy_install.py", line 434, in easy_install
  File "/usr/home/andreypopp/virtualenv/lib/python2.6/site-packages/setuptools-0.6c11-py2.6.egg/setuptools/package_index.py", line 475, in fetch_distribution
AttributeError: 'NoneType' object has no attribute 'clone'

during install

After the patch for DEFAULT in OPERATOR CLASS submitted, I've been able to perform a full roundtrip of pgmp.

The resulting schema had a few small differences:

• CATEGORY = 'N' in CREATE TYPE
• SORTOP = > in CREATE AGGREGATE
• HASHES, MERGES in CREATE OPERATOR

everything else roundtripped perfectly, which is a great result I'd say.

Hi,

I use -N public as argument to exclude public schema in dbtoyaml, as i don't use it in my database and it's fill with postgis functions that introduce noise.

Trying to see the changes with yamltodb i get this error:

Traceback (most recent call last):
  File "/usr/local/bin/yamltodb", line 9, in <module>
    load_entry_point('Pyrseas==0.5.0', 'console_scripts', 'yamltodb')()
  File "/usr/local/lib/python2.7/dist-packages/Pyrseas-0.5.0-py2.7.egg/pyrseas/yamltodb.py", line 56, in main
    stmts = db.diff_map(inmap, args.schlist)
  File "/usr/local/lib/python2.7/dist-packages/Pyrseas-0.5.0-py2.7.egg/pyrseas/database.py", line 289, in diff_map
    self.from_map(input_map, langs)
  File "/usr/local/lib/python2.7/dist-packages/Pyrseas-0.5.0-py2.7.egg/pyrseas/database.py", line 201, in from_map
    self.ndb.schemas.from_map(input_schemas, self.ndb)
  File "/usr/local/lib/python2.7/dist-packages/Pyrseas-0.5.0-py2.7.egg/pyrseas/dbobject/schema.py", line 163, in from_map
    raise KeyError("Expected typed object, found '%s'" % key)
KeyError: "Expected typed object, found 'owner'"

I've tried this but the error is the same

Could UserMappingDict class be updated in a way that pg_user_mappings is used instead of pg_user_mapping? So that non-superusers could use dbtoyaml.

I have a schema

schema example_schema:
  table greatgrandparents:
    columns:
    - greatgrandparent_id:
        not_null: true
        type: integer
    - greatgrandparent_name:
        not_null: true
        type: character varying(40)
    - qty:
        type: integer
    primary_key:
      greatgrandparents_pkey:
        access_method: btree
        columns:
        - greatgrandparent_id

And an extend.yaml

schema example_schema:
  table greatgrandparents:
    audit_columns: default

When I run dbextend I get a new yaml file

language plpgsql:
  description: PL/pgSQL procedural language
  trusted: true
schema example_schema:
  function aud_dflt():
    description: Provides modified_by_user and modified_timestamp values for audit
      columns.
    language: plpgsql
    returns: trigger
    security_definer: true
    source: "BEGIN\n    NEW.modified_by_user = CURRENT_USER;\n    NEW.modified_timestamp\
      \ = CURRENT_TIMESTAMP;\n    RETURN NEW;\nEND "
  table greatgrandparents:
    columns:
    - greatgrandparent_id:
        not_null: true
        type: integer
    - greatgrandparent_name:
        not_null: true
        type: character varying(40)
    - qty:
        type: integer
    - modified_by_user:
        not_null: true
        type: character varying(63)
    - modified_timestamp:
        not_null: true
        type: timestamp with time zone
    primary_key:
      greatgrandparents_pkey:
        access_method: btree
        columns:
        - greatgrandparent_id
    triggers:
      greatgrandparents_20_aud_dflt:
        events:
        - update
        level: row
        procedure: example_schema.aud_dflt()
        timing: before
schema public:
  description: standard public schema

When I run this yaml file using yamltodb to put the changes into the database I get

~/git/Pyrseas$ python pyrseas/yamltodb.py -n example_schema $PGDATABASE temp.yaml 
Traceback (most recent call last):
  File "pyrseas/yamltodb.py", line 70, in <module>
    main()
  File "pyrseas/yamltodb.py", line 45, in main
    stmts = db.diff_map(inmap, args.schlist)
  File "/home/roger/git/Pyrseas/pyrseas/database.py", line 200, in diff_map
    self.from_map(input_map)
  File "/home/roger/git/Pyrseas/pyrseas/database.py", line 169, in from_map
    self._link_refs(self.ndb)
  File "/home/roger/git/Pyrseas/pyrseas/database.py", line 90, in _link_refs
    db.languages.link_refs(db.functions)
  File "/home/roger/git/Pyrseas/pyrseas/dbobject/language.py", line 82, in link_refs
    assert self[(func.language)]
KeyError: 'plpgsql'

Although extensions can be created within or moved to a particular schema, a given named extension can only exist once in each database. Therefore, extensions should extensions should be in YAML maps at the top level, not under the schema where they reside.

This is a PostgreSQL 9.3 feature. See http://www.postgresql.org/docs/devel/static/sql-creatematerializedview.html. Probably should be implemented as a new class (MaterializedView) in pyrseas/dbobject/table.py as a subclass of View or of DbClass, but perhaps could refactor View and MaterializedView into a separate module.

Create a function as follows:

CREATE FUNCTION foo(x integer DEFAULT 0) RETURNS integer
    LANGUAGE plpgsql
    AS $_$BEGIN RETURN x * 2; END$_$;

dbtoyaml creates valid YAML identifying the function as function foo(x integer DEFAULT 0):, however, yamltodb after recreating the function (in a second database) as above, it issues the statement:

ALTER FUNCTION foo(x integer DEFAULT 0) OWNER TO <owner>;

The DEFAULT 0 should not be present in the ALTER statement. Ditto for a DROP FUNCTION.

Given the following table and index:

CREATE TABLE tbl (
    dt date);
CREATE INDEX idx ON tbl (
    EXTRACT(MONTH FROM dt), EXTRACT(DAY FROM dt));

dbtoyaml goes into a loop around line 146 of pyrseas/dbobject/index.py, method _from_catalog of class IndexDict.

I am using Pyrseas tip version ( "0.6 beta") with postgresql 9.1

I have a table t_foo which is created as follow :
CREATE UNLOGGED TABLE t_foo (
id integer NOT NULL,
...
) ;

I am trying to generate sql diff script using dbtoyaml/yamltodb ...
On databases in which the definition of t_foo is missing, diff script reads :
...
CREATE TABLE t_foo(
id integer NOT NULL,
...
) ;

Thanks for this very usefull project

This is a PostgreSQL 9.3 feature. See http://www.postgresql.org/docs/devel/static/sql-createeventtrigger.html. This can probably be implemented as a subclass of Trigger in pyrseas/dbobject/trigger.py.

Please support operation on a single schema instead of the whole database.

I have a database with some schemas in it. Some of them (postgis) create user-defined CAST functions. I also have the plpythonu language installed.

I have a yaml file that defines a new schema and I want to add the new schema without affecting the other schemas in the database so I run:

yamltodb.py -n example_schema pyrdev example.yaml

This gives me the CREATE SCHEMA, CREATE TABLE, etc. that I am expecting to create the new schema, but it also gives:

DROP CAST (postgis.box3d AS box);
DROP CAST (postgis.box3d AS postgis.box2d);
DROP CAST (postgis.geometry AS bytea);
DROP CAST (postgis.geometry AS text);
DROP CAST (postgis.geometry AS postgis.geography);
DROP CAST (postgis.geography AS postgis.geography);
DROP CAST (postgis.box2d AS postgis.box3d);
DROP CAST (postgis.geometry AS postgis.box3d);
DROP CAST (postgis.chip AS postgis.geometry);
DROP CAST (postgis.geography AS postgis.geometry);
DROP CAST (text AS postgis.geometry);
DROP CAST (postgis.box3d_extent AS postgis.box2d);
DROP CAST (postgis.box2d AS postgis.geometry);
DROP CAST (postgis.geometry AS box);
DROP CAST (postgis.box3d_extent AS postgis.box3d);
DROP CAST (bytea AS postgis.geometry);
DROP CAST (postgis.geometry AS postgis.box2d);
DROP CAST (postgis.box3d AS postgis.geometry);
DROP CAST (postgis.box3d_extent AS postgis.geometry);
DROP LANGUAGE plpythonu;

I think that when yamltodb is run with a schema list parameter it should ignore objects defined at the database-wide level.

In order for round-tripping to work as expected I would also expect dbtoyaml not to include database-wide objects when running with a schema list parameter.

dbtoyaml does not allow a password to the database to be passed on the command line, it relies on .pgpass.

We do not allow passwords to be stored in .pgpass because they are stored in clear text.

It would be very helpful if dbtoyaml could prompt the user for a password when one is required, in the same way that psql does.

An aggregate such as the following (thanks, Josh Berkus):

CREATE AGGREGATE mode(boolean) (
    SFUNC = mode_bool_state,
    STYPE = INT[],
    FINALFUNC = mode_bool_final,
    INITCOND = '{0,0}');

is output by dbtoyaml as:

  aggregate mode(boolean):
    finalfunc: mode_bool_final(integer[])
    initcond: '{0,0}'
    sfunc: mode_bool_state(integer[],boolean)
    stype: integer[]

The function arguments shown for finalfunc and sfunc are unnecessary since they can be derived from stype and the arguments to the aggregate. It should suffice for dbtoyaml to output:

 aggregate mode(boolean):
    finalfunc: mode_bool_final
    initcond: '{0,0}'
    sfunc: mode_bool_state
    stype: integer[]

which more closely parallels the CREATE AGGREGATE statement.

I found that recent version of Pyrseas tries to manage system schemas like pg_temp. They are should be ignored.

When using directory output mode for dbtoyaml, if you have a function with the same name, but different types or numbers of parameters, only one is created in the YAML. This does not happen when using a single file output (i.e the output is correct, both functions are listed). Tested using PG 9.2 and the latest source on Ubuntu 12.10

For example if the database has two functions: foo(integer) and foo(text), only foo(integer) will be in the output when using directory mode.

a constraint with condeferrable = true should be DEFERRABLE
a constraint with condeferred = true should be INITIALLY DEFERRED

Hi,

I'm trying to test Pyrseas in order to evaluate if it's suitable for our workflow.

I clone it from trunk (commit 2be406c) and install it with:

sudo setup.py install

Trying to run it i get this error:

fpuga@ELNATH$ python dbtoyaml.py test_database
Traceback (most recent call last):
  File "dbtoyaml.py", line 13, in <module>
    from pyrseas.database import Database
  File "/usr/local/lib/python2.7/dist-packages/Pyrseas-0.5.0-py2.7.egg/pyrseas/database.py", line 13, in <module>
    from pyrseas.lib.dbconn import DbConnection
ImportError: No module named lib.dbconn

There are no lib folder on my folder installation "/usr/local/lib/python2.7/dist-packages/Pyrseas-0.5.0-py2.7.egg/pyrseas/"

fpuga@ELNATH$ ls /usr/local/lib/python2.7/dist-packages/Pyrseas-0.5.0-py2.7.egg/pyrseas/
cmdargs.py  cmdargs.pyc  database.py  database.pyc  dbobject  dbtoyaml.py  dbtoyaml.pyc  __init__.py  __init__.pyc  testutils.py  testutils.pyc  yamltodb.py  yamltodb.pyc

I'm using ubuntu 12.04

The --table option should behave like pg_dump --table, which outputs info not only for tables, but views and sequences.

As reported by Josep Martinez:

Given a view with accented characters in its definition text, e.g., with the word 'Jurídico', dbtoyaml causes an error in pyYAML such as the following:

UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 98: ordinal not in range(128)

The i with acute accent is U+00ED, but it's encoded as 0xc3ad in UTF-8. The problem does not occur under Python 3.x or under Python 2.x but prior to the change f13980d which introduced MultiLineStr.

I am using master as of 15 Mar 2012.

I have a database pyrdev with a schema example_schema and some tables. Some of the tables contain unwanted additional tables (i.e not present in the yaml file).

I run

python pyrseas/yamltodb.py -W -n example_schema pyrdev example.yaml |  psql -W --dbname=pyrdev

The SQL generated is

ALTER TABLE greatgrandparents DROP COLUMN modified_by_user;
ALTER TABLE children DROP COLUMN modified_by_user;
ALTER TABLE parents DROP COLUMN modified_by_user;
ALTER TABLE grandparents DROP COLUMN modified_by_user;

I do not have example_schema in my search_path so the DDL fails with "ERROR: relation "greatgrandparents" does not exist"

I think that when yamltodb generates an ALTER TABLE it should use the schema qualified table name. i.e. ALTER example_schema.greatgrandparents

See http://www.postgresql.org/docs/current/static/indexes-opclass.html (on behalf of rdunklau).

cat schema.yaml

    schema public:
      description: standard public schema
      table t1:
        columns:
        - c1:
            not_null: true
            type: integer
        - c2:
            type: smallint
        - c3:
            default: 'false'
            type: boolean
        - c4:
            type: text
        foreign_keys:
          t1_c2_fkey:
            columns:
            - c2
            references:
              columns:
              - c21
              schema: s1
              table: t2
        primary_key:
          t1_pkey:
            access_method: btree
            columns:
            - c1
    schema s1:
      table t2:
        columns:
        - c21:
            not_null: true
            type: integer
        - c22:
            type: character varying(16)
        primary_key:
          t2_pkey:
            access_method: btree
            columns:
            - c21

yamltodb -H melezhik.x --user test -1 test test.yml | psql --user=test -h melezhik.x test

BEGIN
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
ALTER TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
ALTER TABLE 
ALTER TABLE
COMMIT

alter table t1 rename column c4 to cc4;
yamltodb -H melezhik.x --user test -1 test test.yml

BEGIN;
ALTER TABLE t1 DROP COLUMN cc4;
COMMIT;

which is wrong, because only drop column cc4, but also has to create column c4!

Hello!

With the current version of Pyrseas (0.7beta), when I use yamltodb to generate the delta SQL, it creates unnecessary REVOKE/GRANT sentences.

I've been able to reproduce the problem, so I attach the code to create a database with a table and some grants. Be aware to create a new connection to the new database once the database is created.

CREATE ROLE advanced_user LOGIN
PASSWORD 'helloWorld'
NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;

CREATE ROLE normal_user LOGIN
PASSWORD 'helloWorld'
NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

CREATE DATABASE test_privileges
WITH OWNER = advanced_user
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'Spanish_Spain.1252'
LC_CTYPE = 'Spanish_Spain.1252'
TEMPLATE = template0
CONNECTION LIMIT = -1;

-- New connection to the new database!!

CREATE TABLE mytable
(
id bigint NOT NULL,
code character varying(3) NOT NULL,
desc character varying(10) NOT NULL,
CONSTRAINT mytable_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE mytable OWNER TO advanced_user;
GRANT ALL ON TABLE mytable TO advanced_user;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE mytable TO normal_user;

After running the script, I executed dbtoyaml:
dbtoyaml.exe --host="127.0.0.1" --user=postgres --directory C:\Temp\pyrseas\test_priv_A test_privileges

And without modifiying anything, I executed yamltodb:
yamltodb.exe --host="127.0.0.1" --user=postgres --directory C:\Temp\pyrseas\test_priv_A test_privileges --output="C:\Temp\pyrseas\delta_priv.sql"

The delta SQL file:
REVOKE INSERT, DELETE, SELECT, UPDATE ON TABLE mytable FROM normal_user;
GRANT INSERT, DELETE, SELECT, UPDATE ON TABLE mytable TO normal_user;

I hope you can also reproduce it! :-)

Josep

Object names with mixed case names should be quoted in DbSchemaObject.qualname.
For example public.Name does not work while public."Name" does.

Pyrseas installed from master branch, PostgreSQL 9.1, Python 2.7, Mac OS X

Traceback (most recent call last):
  File "/Users/andreypopp/.virtualenvs/mus/bin/yamltodb", line 9, in <module>
    load_entry_point('Pyrseas==0.6.0beta', 'console_scripts', 'yamltodb')()
  File "/Users/andreypopp/.virtualenvs/mus/src/pyrseas/pyrseas/yamltodb.py", line 45, in main
    stmts = db.diff_map(inmap, args.schlist)
  File "/Users/andreypopp/.virtualenvs/mus/src/pyrseas/pyrseas/database.py", line 289, in diff_map
    self.from_map(input_map, langs)
  File "/Users/andreypopp/.virtualenvs/mus/src/pyrseas/pyrseas/database.py", line 201, in from_map
    self.ndb.schemas.from_map(input_schemas, self.ndb)
  File "/Users/andreypopp/.virtualenvs/mus/src/pyrseas/pyrseas/dbobject/schema.py", line 159, in from_map
    inschema[key], schema.allprivs, schema.owner)
AttributeError: 'Schema' object has no attribute 'owner'

I cannot get Pyrseas to handle functions in plpythonu.

I am using Pyrseas/master from July 2:

git log -1
commit 8ec7ae0ab7835bb941b34470d47d31584786b85a
Author: Joe Abbate <[email protected]>
Date:   Mon Jul 2 16:12:28 2012 -0400

    Collation support for CREATE INDEX.

     * pyrseas/dbobject/index.py (Index.key_expressions): Add COLLATE
       clause.  (IndexDict._from_catalog): Identify collation if present.
     * tests/dbobject/test_collation.py: Two new tests to verify new
       functionality.

For example: create a DB and then create the plpythonu extension and a simple function:

createdb pyrtmp
psql -d pyrtmp <<'EOF'
CREATE EXTENSION plpythonu;
CREATE OR REPLACE FUNCTION public.get_session_variable(var_name character varying)
  RETURNS character varying AS
$BODY$
return GD[var_name]
$BODY$
  LANGUAGE plpythonu;
EOF

Then run dbtoyaml:

python $PYTHONPATH/pyrseas/dbtoyaml.py pyrtmp
Traceback (most recent call last):
  File "/home/roger/git/Pyrseas/pyrseas/dbtoyaml.py", line 56, in <module>
    main()
  File "/home/roger/git/Pyrseas/pyrseas/dbtoyaml.py", line 47, in main
    exclude_tables=args.excl_tables)
  File "/home/roger/git/Pyrseas/pyrseas/database.py", line 217, in to_map
    self.from_catalog()
  File "/home/roger/git/Pyrseas/pyrseas/database.py", line 165, in from_catalog
    self._link_refs(self.db)
  File "/home/roger/git/Pyrseas/pyrseas/database.py", line 119, in _link_refs
    db.languages.link_refs(db.functions)
  File "/home/roger/git/Pyrseas/pyrseas/dbobject/language.py", line 90, in link_refs
    raise exc
KeyError: 'plpythonu'

Similarly, using a simple schema.yml file:

schema pg_catalog:
  description: system catalog schema
  extension plpgsql:
    description: PL/pgSQL procedural language
  extension plpythonu:
    description: PL/PythonU untrusted procedural language
schema public:
  description: standard public schema
  function sys_get_session_variable(var_name character varying):
    language: plpythonu
    returns: character varying
    source: "\nreturn GD[var_name]\n   "

Create the database and then run yamltodb:

createdb pyrtmp
python $PYTHONPATH/pyrseas/yamltodb.py pyrtmp schema.yml 
Traceback (most recent call last):
  File "/home/roger/git/Pyrseas/pyrseas/yamltodb.py", line 67, in <module>
    main()
  File "/home/roger/git/Pyrseas/pyrseas/yamltodb.py", line 45, in main
    stmts = db.diff_map(inmap, args.schlist)
  File "/home/roger/git/Pyrseas/pyrseas/database.py", line 278, in diff_map
    self.from_map(input_map)
  File "/home/roger/git/Pyrseas/pyrseas/database.py", line 200, in from_map
    self._link_refs(self.ndb)
  File "/home/roger/git/Pyrseas/pyrseas/database.py", line 119, in _link_refs
    db.languages.link_refs(db.functions)
  File "/home/roger/git/Pyrseas/pyrseas/dbobject/language.py", line 90, in link_refs
    raise exc
KeyError: 'plpythonu'

The following query doesn't work with my PostgreSQL 9.0.4 setup:

psycopg2.ProgrammingError: ('cannot cast type regclass to name
LINE 2: ...        CASE WHEN contypid = 0 THEN conrelid::regclass::name
                                                                 ^
', "SELECT nspname AS schema,
                  CASE WHEN contypid = 0 THEN conrelid::regclass::name
                       ELSE contypid::regtype::name END AS table,
                  conname AS name,
                  CASE WHEN contypid != 0 THEN 'd' ELSE '' END AS target,
                  contype AS type, conkey AS keycols,
                  condeferrable AS deferrable,
                  condeferred AS deferred,
                  confrelid::regclass AS ref_table, confkey AS ref_cols,
                  consrc AS expression, confupdtype AS on_update,
                  confdeltype AS on_delete, amname AS access_method,
                  obj_description(c.oid, 'pg_constraint') AS description
           FROM pg_constraint c
                JOIN pg_namespace ON (connamespace = pg_namespace.oid)
                LEFT JOIN pg_class on (conname = relname)
                LEFT JOIN pg_am on (relam = pg_am.oid)
           WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema')
                 AND conislocal
           ORDER BY schema, 2, name")

Given tables test1 and test2, the latter inheriting from the former:

CREATE TABLE test1 (
    c1 integer NOT NULL,
    c2 text);
CREATE TABLE test2 (
    c3 date) INHERITS (test1);

If we add column c4 to test1, e.g., ALTER TABLE test1 ADD c4 char(5);, even though dbtoyaml flags c4 as inherited in test2, yamltodb generates two ALTER TABLES:

ALTER TABLE test1
    ADD COLUMN c4 character(5);
ALTER TABLE test2
    ADD COLUMN c4 character(5);

which as expected causes an error in Postgres: column "c4" of relation "test2" already exists.

Similarly, if column c2 is dropped from test1, .e.g., ALTER TABLE test1 DROP c2, yamltodb generates two drops:

ALTER TABLE test1 DROP COLUMN c2;
ALTER TABLE test2 DROP COLUMN c2;

Addding a default to the parent table, e.g., ALTER TABLE test1 ALTER c2 SET DEFAULT 'Some text', also causes two ALTER TABLEs to be generated by yamltodb, but PostgreS accepts those (but it's incorrect and inefficient).

$ /usr/bin/dbtoyaml-3.2 mydb
Traceback (most recent call last):
  File "/usr/lib64/python3.2/site-packages/pyrseas/dbconn.py", line 56, in connect
    connection_factory=DictConnection)
  File "/usr/lib64/python3.2/site-packages/psycopg2/__init__.py", line 179, in connect
    connection_factory=connection_factory, async=async)
psycopg2.OperationalError: FATAL:  Peer authentication failed for user "user"

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/bin/dbtoyaml-3.2", line 9, in <module>
    load_entry_point('Pyrseas==0.5.0', 'console_scripts', 'dbtoyaml')()
  File "/usr/lib64/python3.2/site-packages/pyrseas/dbtoyaml.py", line 33, in main
    dbmap = db.to_map()
  File "/usr/lib64/python3.2/site-packages/pyrseas/database.py", line 177, in to_map
    self.from_catalog()
  File "/usr/lib64/python3.2/site-packages/pyrseas/database.py", line 131, in from_catalog
    self.db = self.Dicts(self.dbconn)
  File "/usr/lib64/python3.2/site-packages/pyrseas/database.py", line 56, in __init__
    self.languages = LanguageDict(dbconn)
  File "/usr/lib64/python3.2/site-packages/pyrseas/dbobject/__init__.py", line 305, in __init__
    self._from_catalog()
  File "/usr/lib64/python3.2/site-packages/pyrseas/dbobject/__init__.py", line 312, in _from_catalog
    for obj in self.fetch():
  File "/usr/lib64/python3.2/site-packages/pyrseas/dbobject/__init__.py", line 321, in fetch
    self.dbconn.connect()
  File "/usr/lib64/python3.2/site-packages/pyrseas/dbconn.py", line 58, in connect
    if exc.message[:6] == 'FATAL:':
AttributeError: 'OperationalError' object has no attribute 'message'

Fixed for me (also tested on Python 2.7) with:

--- pyrseas/dbconn.py.orig
+++ pyrseas/dbconn.py
@@ -55,8 +55,8 @@
                     self.user or os.getenv("USER"), self.pswd),
                                 connection_factory=DictConnection)
         except Exception as exc:
-            if exc.message[:6] == 'FATAL:':
-                sys.exit("Database connection error: %s" % exc.message[8:])
+            if str(exc)[:6] == 'FATAL:':
+                sys.exit("Database connection error: %s" % str(exc)[8:])
             else:
                 raise
         try:

dbtoyaml allows you to extract the definition for a single named schema:

Usage: dbtoyaml [options] dbname

Options:
  -n SCHEMA, --schema=SCHEMA
                        only for named schema (default none)

It also allows you to extract the definitions for a list of named tables, ignoring the other tables:

Usage: dbtoyaml [options] dbname

Options:
  -t TABLIST, --table=TABLIST
                        only for named tables (default all)

It would be useful it we could use the same approach with schemas - i.e. default to extracting all schemas, but allow the --schema= option to accept a list of schemas.

Usage: dbtoyaml [options] dbname

Options:
  -n SCHEMALIST, --schema=SCHEMALIST
                        only for named schemas (default all)

If you also implement #19 then the new syntax should apply to yamltodb too.

When using the dbtoyaml tool, the program emits an error:

Traceback (most recent call last):
  File "/usr/local/bin/dbtoyaml", line 9, in 
    load_entry_point('Pyrseas==0.1.0', 'console_scripts', 'dbtoyaml')()
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.1.0-py2.6.egg/pyrseas/dbtoyaml.py", line 40, in main
    dbmap = db.to_map()
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.1.0-py2.6.egg/pyrseas/database.py", line 92, in to_map
    self.from_catalog()
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.1.0-py2.6.egg/pyrseas/database.py", line 67, in from_catalog
    self.db.indexes)
  File "/usr/local/lib/python2.6/dist-packages/Pyrseas-0.1.0-py2.6.egg/pyrseas/dbobject/table.py", line 357, in link_refs
    constr.references = self[(constr.ref_schema, constr.ref_table)]
KeyError: ('phone', 'clients')

Where phone is a schema and clients is a table in the public schema.
There is a table in the phone schema that references the clients table (in the public schema).
I've tried to work around this problem by explicitly defining clients as public.clients in the foreign key, but postgresql 9.0 drops the schema qualification. The constr object lists the ref_schema as phone, even though the table is in the public schema

pg_dump generates:

COMMENT ON CONSTRAINT schema.constraint_name ON schema.table IS 'blah blah...';

this is wrong: the schema must not be specified in the constraint name: this works:

COMMENT ON CONSTRAINT constraint_name ON schema.table IS 'blah blah...';

Please support converting sequences created as SERIAL columns back.

When I pull from a database using dbtoyaml I can limit the output to a single schema using --schema=example_schema.

However, if want to use the resulting yaml file to recreate that schema in an existing database, there is no corresponding option in yamltodb

Consequently, yamltodb tries to drop all the other schemas in the database.

Please can you add

Usage: yamltodb [options] dbname yamlspec

Options:
  -n SCHEMA, --schema=SCHEMA
                        only for named schema (default none)

This would allow me to use Pyrseas to maintain the schema I am responsible for in a shared database, without affecting other schemas.

We are using Pyrseas tip version ( '0.6 beta'), postgresql 9.1, and sqlalchemy 0.7

Stored functions are created by sqlalchemy 'templated' DDL and functions join tables contained in several schemas. They work perfectly when database schema is constructed through sqlalchemy.

Some details on general code syntax :

SET SCHEMA '%(schema)';

bunch of types definition pertaining to schema...

CREATE OR REPLACE do_something(...) RETURNS ... AS
$$
...
join tables contained in various schemas...
table names are not prefixed with schema name
...
$$
LANGUAGE SQL
SET search_path = %(schema),%(otherschema),public;

When using dbtoyaml/yamltodb to generate diff script for database not containing function do_something , the generated code does not contain any search_path. Reconstructed function can not be used.

See http://pyrseas.wordpress.com/2012/01/02/business-logic-in-the-database/ for an interesting discussion regarding this...

This functionality is based on the following hypothesis:

• it is better to enforce data integrity as close to the database as possible
• it is better to use native Postgresql functionality where possible, e.g. domains, enums, foreign key and check constraints
• Some common data integrity and business logic requirements cannot be enforced in the database without code, e.g. denormalisations, rollups, audit timestamps and journal/history tables
• These things are frequently not implemented in the database because it is not considered cost-effective
• If these things could be implemented declaratively then it would be cost-effective to implement them in the database, and the data would be better protected as a result, and both the developer and the DBA would sleep better at night.

In implementing this functionality in Pyrseas we have the following general requirements - these are a starting point for discussion, so feel free to add new ones or recommend deletions :-)

• yamltodb should only create objects required to support the definitions in the file. I.e. if the .yaml file contains no business logic declarations then no additional objects are created in the database and Pyrseas can be used exactly as it is now
• we should only require schema-level privileges, rather than superuser privileges. This means that all code has to go into the schema defined in the .yaml file and we will not create a pyrseas schema to hold code.
• if we need session variables we will use the GD global dictionary from pl/pythonu, in preference to using temporary tables or requiring new custom_variable_classes in postgresql.conf
• Pyrseas should support round-tripping: if we do yamltodb followed by dbtoyaml we should end up with the same file, if possible. If this is not possible (because it would require identifying unaltered Pyrseas-generated functions and triggers and turning them back into the correct yaml declarations), then at the very least doing yamltodb, followed by dbtoyaml followed by yamltodb should generate exactly the same database
• dbtoyaml should capture any changes made to functionality originally generated by Pyrseas such that they are retained by a subsequent yamltodb
• much of this functionality will be implemented by row-level triggers and so performance will be a primary consideration
• Pyrseas-generated code will be plpgsql where possible and plpythonu otherwise (see the earlier comment about session variables)
• Pyrseas will generate the minimum code required to implement the functionality, e.g. it will generate a single trigger function called from many triggers rather than a separate trigger function for each trigger, where the code for the trigger function is essentially the same
• Pyrseas-generated triggers will have a naming convention to allow them to be identified and to allow the firing order to be controlled, e.g. tablename_pyr_20_set_audit_columns. It might be useful to allow the .yaml file to specify a shorter table alias which gets used in trigger and function names if it exists, e.g. cst_pyr_20_set_audit_columns instead of acme_customers_pyr_20_set_audit_columns
• code generated by Pyrseas will use the default SECURITY INVOKER privilege where possible. However, it is likely that some functionality will require SECURITY DEFINER privileges. For example, if we implement journaling/history table functionality then the schema owner will have insert privileges on those tables, but the users won't (otherwise they could fake the audit trail). Therefore the insert to the journal table will need to be done by a SECURITY DEFINER function. However, there are some issues with this: see http://lwn.net/Articles/223129/ and http://thread.gmane.org/gmane.comp.db.postgresql.general/85365/focus=77287. I think this means we need to store the current search path at the start of every function and then set search path = <the schema for this function> and then restore the previous search path at the end, assuming that the problem with the functions and operators being resolved before the function is executed has been solved in the intervening 5 years. If not, then the Pyrseas-generated code needs to schema-qualify every function and operator (e.g. 3 OPERATOR(pg_catalog.+) 4 OPERATOR(pg_catalog.=) 7

Presuming that we reach consensus on the general design principles I will create additional issues, linked to this one, for individual bits of functionality, hopefully with examples of the database-side code that Pyrseas could generate and potentially with some unit tests that we could use, e.g. pgTAP tests

yamltodb handles empty enum types as ones which meant to be dropped.

See pg_index.indisclustered and ALTER TABLE name CLUSTER USING index_name or ALTER TABLE name SET WITHOUT CLUSTER.