liquibase scripts can not represent clustered indexes
Description
Environment
sql server
Activity
Nathan Voxland March 5, 2015 at 5:22 PM
I created to track support for postgres
Brar Piening February 7, 2015 at 10:09 PM
clustered="true" currently doesn't work for PostgreSQL while it could.
If Liquibase would generate the command 'CLUSTER dbo."testTable" USING "ClusteredIndex-20140612-164512";', the table would be clustered according to this index and could be reclustered with the command 'CLUSTER dbo."testTable";'.
See http://www.postgresql.org/docs/current/static/sql-cluster.html
Currently the query "SELECT indisclustered FROM pg_index i INNER JOIN pg_class c ON i.indexrelid = c.oid WHERE c.relname = 'ClusteredIndex-20140612-164512';" returns false, indicating that no clustering has taken place.
Nathan Voxland October 28, 2014 at 9:10 PM
YOu can now specify a clustered="true" or clustered="false" attribute on createIndex and addPrimaryKey. On Oracle, this will create an index organized table.
CREATE TABLE dbo.testTable
(
ID nchar(10) NOT NULL,
TestLastUpdateValue timestamp NOT NULL,
TestDate datetime2(3) NULL
)
go
ALTER TABLE dbo.testTable ADD CONSTRAINT
PK_testTable PRIMARY KEY NONCLUSTERED
(
ID
)
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20140612-164512] ON dbo.testTable
(
TestLastUpdateValue
)
GO
liquibase diff this against an empty database, and you get a primary key constraint and an index.
when sql server creates a primary key, it automatically creates a clustered index (unless one already exists, or the nonclustered keyword is used)
the result of rebuilding this is that the table has a clustered index on the primary key, and a non-clustered index on the other column. This represents a physically different layout on disk, and could adversely affect performance.
The concept of clustered index is present in sql server, and mysql.
Oracle calls them " index organised tables" however there are typically better index organisation options in oracle.
–
recomendation: liquibase diff runs updateSQL and compares it's output to its input and have some type of tag to hold any noticed code irregularities so that code differences are noticed, rather than flaws in diff/update resulting in silent failures.; diff/update/diff should either not return anything, or emit some warnings somewhere along the line that irregular DDL was detected.