Re: identity columns

New patch.

On 9/9/16 11:45 PM, Vitaly Burovoy wrote:
> 1. The standard requires "... ALTER COLUMN ... SET GENERATED { ALWAYS
> | BY DEFAULT }" (9075-2:2011 subcl 11.20), but the patch implements it
> as "... ALTER COLUMN ... ADD GENERATED { ALWAYS | BY DEFAULT } AS
> IDENTITY"

Has both now.  They do different things, as documented.

> 2. The standard requires not more than one identity column, the patch
> does not follow that requirement, but it does not mentioned in the
> doc.

fixed

> 3. Changes in the table "information_schema.columns" is not full.

fixed

> 4. "<alter identity column specification>" is not fully implemented
> because "<set identity column generation clause>" is implemented
> whereas "<alter identity column option>" is not.

done

> 5. According to 9075-2:2011 subcl 14.11 Syntax Rule 11)c) for a column
> with an indication that values are generated by default the only
> possible "<override clause>" is "OVERRIDING USER VALUE".
> Implementation allows to use "OVERRIDING SYSTEM VALUE" (as "do
> nothing"), but it should be mentioned in "Compatibility" part in the
> doc.

done (documented)

> 6. "CREATE TABLE ... (LIKE ... INCLUDING ALL)" fails Assertion  at
> src/backend/commands/tablecmds.c:631

fixed

> 7. Changing default is allowed but a column is still "identity":

fixed

> 8. Changing a column to be "identity" raises "duplicate key" exception:

fixed

> 9. Changing type of a column deletes linked sequence but leaves
> "default" and "identity" marks:

fixed

> 10. "identity" modifier is lost when the table inherits another one:

fixed, but I invite more testing of inheritance-related things

> 11. The documentation says "OVERRIDING ... VALUE" can be placed even
> before "DEFAULT VALUES", but it is against SQL spec and the
> implementation:

fixed

> 12. Dump/restore is broken for some cases:

fixed

> 13. doc/src/sgml/ref/create_table.sgml (5th chunk) has "TODO". Why?

fixed

> 14. It would be fine if psql has support of new clauses.

done

> 15. Initializing attidentity in most places is ' ' but makefuncs.c has
> "n->identity = 0;". Is it correct?

fixed

> 16. I think it is a good idea to not raise exceptions for "SET
> GENERATED/DROP IDENTITY" if a column has the same type of identity/not
> an identity. To be consistent with "SET/DROP NOT NULL".

The present behavior is per SQL standard.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services