Re: [pgAdmin4][Patch] - RM 4030 - IDENTITY column not recognised

Hi

On Thu, Mar 21, 2019 at 9:52 AM Khushboo Vashi <khushboo.vashi@enterprisedb.com> wrote:

Hi Dave,

On Wed, Mar 20, 2019 at 8:37 PM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Wed, Mar 20, 2019 at 7:30 AM Khushboo Vashi <khushboo.vashi@enterprisedb.com> wrote:

Hi,

Please find the attached patch to fix the RM #4030 - IDENTITY column not recognised.

- Added support for IDENTITY column for PostgreSQL >= 10.0

A few issues unfortunately:

- There's an extra space in the generated SQL for an identity column on a table, right before the end comma (in both the CREATE and reverse engineered SQL.

- I cannot make an IDENTITY column a primary key through the UI, nor does it reverse-engineer that property in the SQL if I create it via SQL (it does properly set the switch value though).

This issue has already been logged earlier, but I will fix this with this patch. 

- After creating an IDENTITY column, there should be a dependency on the sequence, but I don't see this listed.

If the Show System Object is enabled, then only you can see. 

Hmm, OK. I turned on "Show System Objects" and I do now see the dependencies. Something seems funky though:

- The sequence has a dependency of the column

- The sequence is dependent on the table

Shouldn't they both be dependencies? It's the table and column that needs the sequence.

- The sequence is only listed as a dependent of the column (which is the opposite of what is seen on the sequence, as you'd expect), but it doesn't show that it's dependent on the table, in the same way that a table lists a schema as a dependency.

Is our SQL messed up here, or is PostgreSQL listing things in a funky way? I'd expect to see:

When clicking on the sequence:

- Dependencies should list the schema.

- Dependents  should list the table and column.

When clicking on the column:

- Dependencies should list the sequence.

- Dependents should list the table.

I grant you it's confusing and open to interpretation though. I think as long as we're definitely listing things as PG tracks them, it's all good.

 

- We should consider the auto-created sequence a system object, and hide it in the treeview by default as it's an implementation detail.

How, can I identify those as a system object? I tried but couldn't find a way.

Check if there's a dependency on a column.

 

- If I click on an IDENTITY column in the treeview, the reverse-engineered SQL just shows the plain datatype.

Fixed. 

- Can we reasonably support the sequence_options clause?

I will look into it. 

Thanks.

 

Thanks.

Thanks,

Khushboo 

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company