reflection on postgres can cause wrong sequence names

[Former user]

If you rename a sequence in postgres, it's kind of weird...

because of transactional issues, the sequence retains its initiallly created id in 'select * from %(sequencename)' , however it is correctly manipulated with the new name.

during reflection ( and possibly during normal table setup? i did not test ), SqlAlchemy pulls the original id for the sequence , i do not know from where.

SqlAlchemy should pull the current sequence name instead.

The only way to 'sync' both ids, is to dump db, drop db, reload.

This means that renaming any sequence in postgres ( granted, not a common task) seems to break sqlalchemy integration.

the response i got last week from - http://archives.postgresql.org/pgsql-general/2008-05/msg00961.php

some older relevant pg threads: http://archives.postgresql.org/pgsql-hackers/2008-03/msg00008.php http://archives.postgresql.org/pgsql-hackers/2002-02/msg01095.php

This is a walkthrough of the phenomena.

=> CREATE TABLE sample_table ( id bigserial primary key not null ); NOTICE: CREATE TABLE will create implicit sequence "sample_table_id_seq" for serial column "sample_table.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sample_table_pkey" for table "sample_table" CREATE TABLE

=> \d sample_table; Table "public.sample_table" Column | Type | Modifiers
--------+--------+---------------------------------------------- id | bigint | not null default nextval('sample_table_id_seq'::regclass)

=>select * from sample_table_id_seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- sample_table_id_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f (1 row)

=> alter table sample_table_id_seq rename to id_seq ; => \d sample_table; Table "public.sample_table" Column | Type | Modifiers
--------+--------+---------------------------------------------- id | bigint | not null default nextval('id_seq'::regclass)

=> select * from id_seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- sample_table_id_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f

[Mike Bayer]

SQLA doesn't know anything about the method you're using there to get at the sequence name. reflection figures out attached sequences by reflecting the full blown DEFAULT of the column as stated in pg_catalog. So if this isn't working, something else is going on.

[Former user]

I'm just using that to show how Postgres doesn't change the names everywhere because of transactional issues.

I don't know what is going on... all I do know is that if you rename a sequence and reflect into SqlAlchemy, it pulls the old sequence name every time.

You could see this by stopping an app, renaming a sequence in any pg table, then running your app and trying to do an insert - it'll fail showing an error message that used the original sequence name. You can then rename back and run the app again -- it'll work again.

At first I thought this was from caching, and deleted every pyc and tmp file imaginable.. then i realized it was somehow related to that pg system design i had encountered once before.

[Mike Bayer]

uh yeah the method that we do use to get the sequence, i.e. querying pg_catalog.pg_attrdef for the value of the column's default, also returns the old name. what's very annoying is that "\d tablename" does return the correct value. I've looked in every single table in pg_catalog and the new name is not there. So clearly we can't do anything about this until PG gets their act together. or if someone knows how "\d" on the console magically gets the right answer.

[Former user]

With an empty database, having created the sample table and renamed the sequence:

# select sequence_name from id_seq;
-[ RECORD 1 ]-+--------------------
sequence_name | sample_table_id_seq

# select pg_catalog.pg_get_expr(adbin, adrelid) from pg_catalog.pg_attrdef;
-[ RECORD 1 ]----------------------------
pg_get_expr | nextval('id_seq'::regclass)

Note that you can start psql with -E to see the SQL issued by *-commands.

Example:

$ psql -E
(...)
# \d sample_table
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(sample_table)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, 
relhasoids , reltablespace 
FROM pg_catalog.pg_class WHERE oid = '188175'
**************************

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '188175' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
WHERE c.oid = '188175' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
**************************

********* QUERY **********
SELECT c.oid::regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '188175' ORDER BY inhseqno ASC
**************************

                  Table "public.sample_table"
 Column |  Type  |                  Modifiers                   
--------+--------+----------------------------------------------
 id     | bigint | not null default nextval('id_seq'::regclass)
Indexes:
    "sample_table_pkey" PRIMARY KEY, btree (id)

– AlexB

[Former user]

I hit approximately the same problem after renaming a table; SQLAlchemy attempts to read from newtablename_id_seq whereas the sequence name is still oldtablename_id_seq.

The nextval expression can be read using something like:

SELECT c.oid
FROM pg_catalog.pg_class c
WHERE c.relname='fubar' AND pg_catalog.pg_table_is_visible(c.oid);

SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
FROM pg_catalog.pg_attrdef d
     JOIN pg_catalog.pg_attribute a ON d.adrelid=a.attrelid and d.adnum=a.attnum and a.atthasdef
WHERE a.attrelid = '2358133'
      AND a.attname='id' AND NOT a.attisdropped;

(where 2358133 is the oid obtained from the former query). The sequence name can be parsed out of that, if it is not available anywhere else.

This is a bit of extra trouble for a rare case, though, and it is probably a good habit anyway to rename any satellite objects whenever we rename our tables. But strictly, it is not safe to assume that the sequence name is always '\"%s_%s_seq\"' % (column.table.name, column.name)

[Former user]

fixes reflection SQL for getting sequence names

[Former user]

Can this fix be applied? The attached diff patches against the 0.6.x Mercurial repository. The diff is a one line change. I couldn't figure out an easy way to write a test for it but all you need to do is create a table, rename its sequence, then apply a mapper and perform a select all with the mapper. - Kumar McMillan

diff -r e9cdc0b86ebae7463d33383b9a7e2706387fc6cf lib/sqlalchemy/dialects/postgresql/base.py
--- a/lib/sqlalchemy/dialects/postgresql/base.py        Tue Apr 06 12:32:51 2010 -0400
+++ b/lib/sqlalchemy/dialects/postgresql/base.py        Tue Apr 06 17:21:10 2010 -0500
@@ -870,7 +870,7 @@
         SQL_COLS = """
             SELECT a.attname,
               pg_catalog.format_type(a.atttypid, a.atttypmod),
-              (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
+              (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d
                WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
               AS DEFAULT,
               a.attnotnull, a.attnum, a.attrelid as table_oid

[Mike Bayer]

Replying to guest:

This is a bit of extra trouble for a rare case, though, and it is probably a good habit anyway to rename any satellite objects whenever we rename our tables. But strictly, it is not safe to assume that the sequence name is always '\"%s_%s_seq\"' % (column.table.name, column.name)

we don't assume a name of <tablename>_id_seq if you are reflecting. If you are not reflecting, then you have to use a Sequence construct to specify the desired name - name_id_seq is just the default.

0.6 usually uses RETURNING in any case so the name isn't even needed.

[Mike Bayer]

Replying to guest:

Can this fix be applied? The attached diff patches against the 0.6.x Mercurial repository. The diff is a one line change. I couldn't figure out an easy way to write a test for it but all you need to do is create a table, rename its sequence, then apply a mapper and perform a select all with the mapper. - Kumar McMillan

you gottit, a6480a2c31a52c7fcf1cd5487033d61afab8a7ee

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)