Support columns GENERATED AS (x) STORED

[philpep]

Hi,

Seems having a generated column make dbtoyaml crash due to a self reference of Table in depends_on:

Simple test case to reproduce the bug:

$ psql -tAc 'show server_version'
15.2 (Debian 15.2-1.pgdg110+1)
$ cat << EOF > t.sql
CREATE TABLE test (
    v double precision,
    g double precision GENERATED ALWAYS AS (v) STORED
);
EOF
$ dropdb --if-exists test && createdb test && psql test -f t.sql
CREATE TABLE
$ dbtoyaml test
Traceback (most recent call last):
  File "dbtoyaml", line 33, in <module>
    sys.exit(load_entry_point('Pyrseas', 'console_scripts', 'dbtoyaml')())
  File "Pyrseas/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "Pyrseas/pyrseas/database.py", line 491, in to_map
    dbmap.update(self.db.schemas.to_map(self.db, opts))
  File "Pyrseas/pyrseas/dbobject/schema.py", line 361, in to_map
    schemas.update(self[sch].to_map(db, self, opts))
  File "Pyrseas/pyrseas/dbobject/schema.py", line 107, in to_map
    schobjs.append((obj, obj.to_map(db, dbschemas, opts)))
  File "Pyrseas/pyrseas/dbobject/table.py", line 463, in to_map
    dct = super(Table, self).to_map(db, opts.no_owner, opts.no_privs)
  File "Pyrseas/pyrseas/dbobject/__init__.py", line 355, in to_map
    deps = set(dct.pop('depends_on', ()))
  File "Pyrseas/pyrseas/dbobject/__init__.py", line 205, in __hash__
    return hash((self.__class__, self.key()))
  File "Pyrseas/pyrseas/dbobject/__init__.py", line 308, in key
    lst = [getattr(self, k) for k in self.keylist]
  File "Pyrseas/pyrseas/dbobject/__init__.py", line 308, in <listcomp>
    lst = [getattr(self, k) for k in self.keylist]
AttributeError: 'Table' object has no attribute 'schema'

Tested with Pyrseas 0.10.0 and current master branch.

I was quite hard to dig into this because the traceback isn't very useful. The bug is caused by a self reference of the Table object through depends_on which is added by _build_dependency_graph(), I guess this is where the issue should be fixed, adding if src != tgt before adding in depends_on fixes the bug.

In to_map() we use copy.deepcopy() which seems to not handle well the self reference and we get a unexpected object which lead to this weird traceback.

Thanks!

[jmafc]

I'm afraid we never added support for that form of GENERATED. The only thing I find is GENERATED AS IDENTITY in 811329f (2017-11-16). The GENERATED ... STORED syntax was added for PG 12 (2019-10-3). It seems the first thing to do is to retrieve pg_attribute.attgenerated in the query in dbobject/column.py and then take it from there (not only for dbtoyaml but also for yamltodb).

[jmafc]

This requires

• fetching pg_attribute.attgenerated
• mapping generated columns for dbtoyaml
• handling CREATE TABLE and ALTER TABLE statements with such columns