::Diff tries to modify old table after table rename for PK col name change

[SysPete]

I tend to use PK cols which are named after the table e.g. table 'foos' has PK 'foos_id'. When renaming tables via ::Diff ->extra( renamed_from => 'foos' then I get one of two possible breakages:

• if renamed_from is used on the column as well then I get:

Can't alter field in another table at .../SQL/Translator/Producer/PostgreSQL.pm line 769.

• if I skip the renamed_from on the PK (not a good idea in reality but an interesting test) then ::Diff gives a table rename followed by a DROP COLUMN on the table that no longer exists.

[SysPete]

https://github.com/dbsrgits/sql-translator/blob/master/lib/SQL/Translator/Diff.pm#L237 is where the madness starts. If a renamed table also has a renamed field then this is the wrong way round since field and possible constraints (pk/unique/...) must be renamed before the table or else you end up with nonsense like:

ALTER TABLE foos RENAME TO trees;
ALTER TABLE foos DROP CONSTRAINT foos_pkey;

[SysPete]

Time to describe the situation in full...
Assume we have an initial table:

CREATE TABLE "foos" (
  "foos_id" serial NOT NULL,
  "wibble" integer,
  PRIMARY KEY ("foos_id")
);

Which looks like:

                             Table "public.foos"
 Column  |  Type   |                        Modifiers                        
---------+---------+---------------------------------------------------------
 foos_id | integer | not null default nextval('foos_foos_id_seq'::regclass)
 wibble  | integer | 
Indexes:
    "foos_pkey" PRIMARY KEY, btree (foos_id)

If we want to rename everything then we need to perform the following:

ALTER TABLE foos RENAME foos_id TO bars_id;
ALTER SEQUENCE foos_foos_id_seq RENAME TO bars_bars_id_seq;
ALTER INDEX foos_pkey RENAME TO bars_pkey;
ALTER TABLE foos RENAME TO bars;

So we end up with this:

                            Table "public.bars"
 Column  |  Type   |                       Modifiers                        
---------+---------+--------------------------------------------------------
 bars_id | integer | not null default nextval('bars_bars_id_seq'::regclass)
 wibble  | integer | 
Indexes:
    "bars_pkey" PRIMARY KEY, btree (bars_id)

Handling renamed_from for table and field is not so hard but realising that the sequence and index also need renaming to keep things pretty is not so simple. Maybe the best solution would be to implement a batch_table_alter method in ::Producer::PostgreSQL ?
I'd appreciate comments here before I head too far down the wrong road.

[ilmari]

This was fixed by #44, closing.