Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can yugabyte-db modify column ordinal position? #8781

Closed
bnuzhouwei opened this issue Jun 5, 2021 · 10 comments
Closed

Can yugabyte-db modify column ordinal position? #8781

bnuzhouwei opened this issue Jun 5, 2021 · 10 comments

Comments

@bnuzhouwei
Copy link

MySQL and TiDB offer alter column ... after ... to modify column ordinal position.

MSSQL offer a gui tool to modify column position by recreate table automatically.

Can yugabyte-db do this by some method?

@bnuboy
Copy link

bnuboy commented Jun 7, 2021

It is common to update the structure of the tables, thus it is very helpful for developers to modify the column ordinal position.

@ddorian
Copy link
Contributor

ddorian commented Jun 7, 2021

Hi @equiclouds

Currently each column is stored as a separate key-value in rocksdb underneath and not the full-row-inside-tuple like in normal PostgreSQL.

So this operation doesn't exist in our case. We first need to implement #3520. Closing as non applicable.

@ddorian ddorian closed this as completed Jun 7, 2021
@bnuzhouwei
Copy link
Author

Currently each column is stored as a separate key-value in rocksdb underneath and not the full-row-inside-tuple like in normal PostgreSQL.

So this operation doesn't exist in our case. We first need to implement #3520. Closing as non applicable.

Yeah, if stored as a [separate key-value in rocksdb underneath], may i update the metadata to modify columns positions, such that i will show changes in the table desinger (such as navicat).

@ddorian
Copy link
Contributor

ddorian commented Jun 7, 2021

Yeah, if stored as a [separate key-value in rocksdb underneath], may i update the metadata to modify columns positions, such that i will show changes in the table desinger (such as navicat).

By doing what operation ? Cause an "alter column position" isn't available in normal PostgreSQL, hence not with us.

@bnuzhouwei
Copy link
Author

If i update attnum in pg_attribute

update pg_attribue set attnum=@attnum where oid=object_id(@relname)

will cause shutdown from postgres.

For YGDB store as kvs, is this operation safe?

@ddorian
Copy link
Contributor

ddorian commented Jun 7, 2021

For YGDB store as kvs, is this operation safe?

I'll get a developer from YSQL team to comment here.

@bnuzhouwei
Copy link
Author

The process of designing software is gradual. In the DB design process, we can’t do a perfect solution that considerded all the properties for implement the business. Some columns may be added, if new columns can only at the end of columns list. The automancally generated db document will be inaesthetic and unnatural, that will cost must time to reposion them in Words or Excel.

MSSQL also provide a GUI tool for reposition the columns by automancally recreate the table.
I think for CRDB is a NewSQL product with kv store, it is much easier to reposition columns without recreated table needed. May be just modify some metadata by (just like TiDB another NewSQL db):

alter table alter column columname after columnother

Or just updating the metabase:

update meta set attnum=@attnum where relname=@relname and attname=@attname

@ddorian
Copy link
Contributor

ddorian commented Jun 7, 2021

@equiclouds column names (IDs, really) become part of the DocDB key in rocksdb. It’d require rewriting the entire table to change column position.

@bnuzhouwei
Copy link
Author

Is there a logic position that dba can set without set the phisical postion, so that the column postions saw in db desginer software are same as expected.

@ddorian
Copy link
Contributor

ddorian commented Jun 8, 2021

@equiclouds by setting the column positions order right from the start. Or by dropping/re-creating the tables. Currently we take column-id into the key-value in rocksdb so even if we added this features it would be a table-rewrite.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants