On 2/26/15 1:49 PM, Jim Nasby wrote: > On 2/23/15 5:09 PM, Tomas Vondra wrote: >> Over the time I've heard various use cases for this patch, but in most >> cases it was quite speculative. If you have an idea where this might be >> useful, can you explain it here, or maybe point me to a place where it's >> described? > > For better or worse, table structure is a form of documentation for a > system. As such, it's very valuable to group related fields in a table > together. When creating a table, that's easy, but as soon as you need to > alter your careful ordering can easily end up out the window. > > Perhaps to some that just sounds like pointless window dressing, but my > experience is that on a complex system the less organized things are the > more bugs you get due to overlooking something.
I agree with Jim's comments. I've generally followed column ordering that goes something like: 1) primary key 2) foreign keys 3) flags 4) other programmatic data fields (type, order, etc.) 5) non-programmatic data fields (name, description, etc.) The immediate practical benefit of this is that users are more likely to see fields that they need without scrolling right. Documentation is also clearer because fields tend to go from most to least important (left to right, top to bottom). Also, if you are consistent enough then users just *know* where to look. I wrote a function a while back that reorders columns in tables (it not only deals with reordering, but triggers, constraints, indexes, etc., though there are some caveats). It's painful and not very efficient, but easy to use. Most dimension tables that I've worked with are in the millions of rows so reordering is not problem. With fact tables, I assess on a case-by-case basis. It would be nice to not have to do that triage. The function is attached if anyone is interested. -- - David Steele da...@pgmasters.net
/********************************************************************************
CATALOG_TABLE_COLUMN_MOVE Function
********************************************************************************create
or replace function _utility.catalog_table_column_move
(
strSchemaName text,
strTableName text,
strColumnName text,
strColumnNameBefore text
)
returns void as $$
declare
rIndex record;
rConstraint record;
rColumn record;
strSchemaTable text = strSchemaName || '.' || strTableName;
strDdl text;
strClusterIndex text;
begin
-- Raise notice that a reorder is in progress
raise notice 'Reorder columns in table %.% (% before %)', strSchemaName,
strTableName, strColumnName, strColumnNameBefore;
-- Get the cluster index
select pg_index.relname
into strClusterIndex
from pg_namespace
inner join pg_class
on pg_class.relnamespace = pg_namespace.oid
and pg_class.relname = strTableName
inner join pg_index pg_index_map
on pg_index_map.indrelid = pg_class.oid
and pg_index_map.indisclustered = true
inner join pg_class pg_index
on pg_index.oid = pg_index_map.indexrelid
where pg_namespace.nspname = strSchemaName;
if strClusterIndex is null then
raise exception 'Table %.% must have a cluster index before
reordering', strSchemaName, strTableName;
end if;
-- Disable all user triggers
strDdl = 'alter table ' || strSchemaTable || ' disable trigger user';
raise notice ' Disable triggers [%]', strDdl;
execute strDdl;
-- Create temp table to hold ddl
create temp table temp_catalogtablecolumnreorder
(
type text not null,
name text not null,
ddl text not null
);
-- Save index ddl in a temp table
raise notice ' Save indexes';
for rIndex in
with index as
(
select _utility.catalog_index_list_get(strSchemaName, strTableName)
as name
),
index_ddl as
(
select index.name,
_utility.catalog_index_create_get(_utility.catalog_index_get(strSchemaName,
index.name)) as ddl
from index
)
select index.name,
index_ddl.ddl
from index
left outer join index_ddl
on index_ddl.name = index.name
and index_ddl.ddl not like '%[function]%'
loop
raise notice ' Save %', rIndex.name;
insert into temp_catalogtablecolumnreorder values ('index',
rIndex.name, rIndex.ddl);
end loop;
-- Save constraint ddl in a temp table
raise notice ' Save constraints';
for rConstraint in
with constraint_list as
(
select _utility.catalog_constraint_list_get(strSchemaName,
strTableName, '{p,u,f,c}') as name
),
constraint_ddl as
(
select constraint_list.name,
_utility.catalog_constraint_create_get(_utility.catalog_constraint_get(strSchemaName,
strTableName,
constraint_list.name)) as ddl
from constraint_list
)
select constraint_list.name,
constraint_ddl.ddl
from constraint_list
left outer join constraint_ddl
on constraint_ddl.name = constraint_list.name
loop
raise notice ' Save %', rConstraint.name;
insert into temp_catalogtablecolumnreorder values ('constraint',
rConstraint.name, rConstraint.ddl);
end loop;
-- Move column
for rColumn in
with table_column as
(
select pg_attribute.attname as name,
rank() over (order by pg_attribute.attnum) as rank,
pg_type.typname as type,
case when pg_attribute.atttypmod = -1 then null else
((atttypmod - 4) >> 16) & 65535 end as precision,
case when pg_attribute.atttypmod = -1 then null else
(atttypmod - 4) & 65535 end as scale,
not pg_attribute.attnotnull as nullable,
pg_attrdef.adsrc as default,
pg_attribute.*
from pg_namespace
inner join pg_class
on pg_class.relnamespace = pg_namespace.oid
and pg_class.relname = strTableName
inner join pg_attribute
on pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum >= 1
and pg_attribute.attisdropped = false
inner join pg_type
on pg_type.oid = pg_attribute.atttypid
left outer join pg_attrdef
on pg_attrdef.adrelid = pg_class.oid
and pg_attrdef.adnum = pg_attribute.attnum
where pg_namespace.nspname = strSchemaName
order by pg_attribute.attnum
)
select table_column.*
from table_column table_column_before
inner join table_column
on table_column.rank >= table_column_before.rank
and table_column.name <> strColumnName
where table_column_before.name = strColumnNameBefore
loop
raise notice ' Move column %', rColumn.name;
strDdl = 'alter table ' || strSchemaTable || ' rename column "' ||
rColumn.name || '" to "@' || rColumn.name || '@"';
raise notice ' Rename [%]', strDdl;
execute strDdl;
strDdl = 'alter table ' || strSchemaTable || ' add "' || rColumn.name
|| '" ' || rColumn.type ||
case when rColumn.precision is not null then '(' ||
rColumn.precision || ', ' || rColumn.scale || ')' else '' end;
raise notice ' Create [%]', strDdl;
execute strDdl;
strDdl = 'update ' || strSchemaTable || ' set "' || rColumn.name || '"
= "@' || rColumn.name || '@"';
raise notice ' Copy [%]', strDdl;
execute strDdl;
strDdl = 'alter table ' || strSchemaTable || ' drop column "@' ||
rColumn.name || '@"';
raise notice ' Drop [%]', strDdl;
execute strDdl;
if rColumn."default" is not null then
strDdl = 'alter table ' || strSchemaTable || ' alter column "' ||
rColumn.name || '" set default ' || rColumn.default;
raise notice ' Default [%]', strDdl;
execute strDdl;
end if;
if rColumn.nullable = false then
strDdl = 'alter table ' || strSchemaTable || ' alter column "' ||
rColumn.name || '" set not null';
raise notice ' Not Null [%]', strDdl;
execute strDdl;
end if;
end loop;
-- Rebuild indexes
raise notice ' Rebuild indexes';
for rIndex in
select name,
ddl
from temp_catalogtablecolumnreorder
where type = 'index'
loop
begin
execute rIndex.ddl;
raise notice ' Rebuild % [%]', rIndex.name, rIndex.ddl;
exception
when duplicate_table then
raise notice ' Skip % [%]', rIndex.name, rIndex.ddl;
end;
end loop;
-- Rebuild constraints
raise notice ' Rebuild constraints';
for rConstraint in
select name,
ddl
from temp_catalogtablecolumnreorder
where type = 'constraint'
loop
begin
execute rConstraint.ddl;
raise notice ' Rebuild % [%]', rConstraint.name,
rConstraint.ddl;
exception
when duplicate_object or duplicate_table or
invalid_table_definition then
raise notice ' Skip % [%]', rConstraint.name,
rConstraint.ddl;
end;
end loop;
-- Recluster table
strDdl = 'cluster ' || strSchemaTable || ' using ' || strClusterIndex;
raise notice ' Recluster [%]', strDdl;
execute strDdl;
-- Enable all user triggers
strDdl = 'alter table ' || strSchemaTable || ' enable trigger user';
raise notice ' Enable triggers [%]', strDdl;
execute strDdl;
-- Drop temp tables
drop table temp_catalogtablecolumnreorder;
end
$$ language plpgsql security invoker;
comment on function _utility.catalog_table_column_move(text, text, text, text)
is
'Moves a column before another column in a table. For example:
{{perform _utility.catalog_table_column_move(''attribute'', ''attribute'',
''target'', ''active'');}}
will position the "target" column right before the "active" column. It''s not
currently possible to directly move a column to the
right but this can be achieved by multiple moves of columns to the left.
There are a few caveats:
* The table must have a cluster index. Moving columns is messy on the storage
and the table needs to be re-clustered afterwards.
* Column referencing triggers will not automatically be dropped or rebuilt.
* Column specific permissions are not restored after the move.
* A column cannot be moved before the primary key if there are foreign key
references from other tables.';
signature.asc
Description: OpenPGP digital signature
