From fe30e7ebfa3846416f1adeb7cf611006513a4ee0 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 6 Mar 2020 12:19:29 -0500 Subject: Allow ALTER TYPE to change some properties of a base type. Specifically, this patch allows ALTER TYPE to: * Change the default TOAST strategy for a toastable base type; * Promote a non-toastable type to toastable; * Add/remove binary I/O functions for a type; * Add/remove typmod I/O functions for a type; * Add/remove a custom ANALYZE statistics functions for a type. The first of these can be done by the type's owner; all the others require superuser privilege since misuse could cause problems. The main motivation for this patch is to allow extensions to upgrade the feature sets of their data types, so the set of alterable properties is biased towards that use-case. However it's also true that changing some other properties would be a lot harder, as they get baked into physical storage and/or stored expressions that depend on the type. Along the way, refactor GenerateTypeDependencies() to make it easier to call, refactor DefineType's volatility checks so they can be shared by AlterType, and teach typcache.c that it might have to reload data from the type's pg_type row, a scenario it never handled before. Also rearrange alter_type.sgml a bit for clarity (put the composite-type operations together). Tomas Vondra and Tom Lane Discussion: https://postgr.es/m/20200228004440.b23ein4qvmxnlpht@development --- doc/src/sgml/catalogs.sgml | 28 ++++--- doc/src/sgml/ref/alter_type.sgml | 155 +++++++++++++++++++++++++++++++++------ 2 files changed, 152 insertions(+), 31 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 34bc0d05266..c6f95fa6881 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -7828,28 +7828,38 @@ SCRAM-SHA-256$<iteration count>:&l types (those with typlen = -1) if the type is prepared for toasting and what the default strategy for attributes of this type should be. - Possible values are + Possible values are: - p: Value must always be stored plain. + + p (plain): Values must always be stored plain + (non-varlena types always use this value). + - e: Value can be stored in a secondary - relation (if relation has one, see + e (external): Values can be stored in a + secondary TOAST relation (if relation has one, see pg_class.reltoastrelid). - m: Value can be stored compressed inline. + + m (main): Values can be compressed and stored + inline. + - x: Value can be stored compressed inline or stored in secondary storage. + + x (extended): Values can be compressed and/or + moved to a secondary relation. + - Note that m columns can also be moved out to secondary - storage, but only as a last resort (e and x columns are - moved first). + x is the usual choice for toast-able types. + Note that m values can also be moved out to + secondary storage, but only as a last resort (e + and x values are moved first). diff --git a/doc/src/sgml/ref/alter_type.sgml b/doc/src/sgml/ref/alter_type.sgml index 67be1dd5683..e0afaf8d0b0 100644 --- a/doc/src/sgml/ref/alter_type.sgml +++ b/doc/src/sgml/ref/alter_type.sgml @@ -23,13 +23,14 @@ PostgreSQL documentation -ALTER TYPE name action [, ... ] ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } -ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ] ALTER TYPE name RENAME TO new_name ALTER TYPE name SET SCHEMA new_schema +ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ] +ALTER TYPE name action [, ... ] ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ] ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value +ALTER TYPE name SET ( property = value [, ... ] ) where action is one of: @@ -48,60 +49,69 @@ ALTER TYPE name RENAME VALUE - ADD ATTRIBUTE + OWNER - This form adds a new attribute to a composite type, using the same syntax as - . + This form changes the owner of the type. - DROP ATTRIBUTE [ IF EXISTS ] + RENAME - This form drops an attribute from a composite type. - If IF EXISTS is specified and the attribute - does not exist, no error is thrown. In this case a notice - is issued instead. + This form changes the name of the type. - SET DATA TYPE + SET SCHEMA - This form changes the type of an attribute of a composite type. + This form moves the type into another schema. - OWNER + RENAME ATTRIBUTE - This form changes the owner of the type. + This form is only usable with composite types. + It changes the name of an individual attribute of the type. - RENAME + ADD ATTRIBUTE - This form changes the name of the type or the name of an - individual attribute of a composite type. + This form adds a new attribute to a composite type, using the same syntax as + . - SET SCHEMA + DROP ATTRIBUTE [ IF EXISTS ] - This form moves the type into another schema. + This form drops an attribute from a composite type. + If IF EXISTS is specified and the attribute + does not exist, no error is thrown. In this case a notice + is issued instead. + + + + + + ALTER ATTRIBUTE ... SET DATA TYPE + + + This form changes the type of an attribute of a composite type. @@ -135,6 +145,84 @@ ALTER TYPE name RENAME VALUE + + + + SET ( property = value [, ... ] ) + + + + This form is only applicable to base types. It allows adjustment of a + subset of the base-type properties that can be set in CREATE + TYPE. Specifically, these properties can be changed: + + + + RECEIVE can be set to the name of a binary input + function, or NONE to remove the type's binary + input function. Using this option requires superuser privilege. + + + + + SEND can be set to the name of a binary output + function, or NONE to remove the type's binary + output function. Using this option requires superuser privilege. + + + + + TYPMOD_IN can be set to the name of a type + modifier input function, or NONE to remove the + type's type modifier input function. Using this option requires + superuser privilege. + + + + + TYPMOD_OUT can be set to the name of a type + modifier output function, or NONE to remove the + type's type modifier output function. Using this option requires + superuser privilege. + + + + + ANALYZE can be set to the name of a type-specific + statistics collection function, or NONE to remove + the type's statistics collection function. Using this option + requires superuser privilege. + + + + + STORAGE + TOAST + per-type storage settings + + can be set to plain, + extended, external, + or main (see for + more information about what these mean). However, changing + from plain to another setting requires superuser + privilege (because it requires that the type's C functions all be + TOAST-ready), and changing to plain from another + setting is not allowed at all (since the type may already have + TOASTed values present in the database). Note that changing this + option doesn't by itself change any stored data, it just sets the + default TOAST strategy to be used for table columns created in the + future. See to change the TOAST + strategy for existing table columns. + + + + See for more details about these + type properties. Note that where appropriate, a change in these + properties for a base type will be propagated automatically to domains + based on that type. + + + @@ -156,7 +244,7 @@ ALTER TYPE name RENAME VALUE USAGE privilege on the data type. + have USAGE privilege on the attribute's data type. @@ -262,6 +350,16 @@ ALTER TYPE name RENAME VALUE + + property + + + The name of a base-type property to be modified; see above for + possible values. + + + + CASCADE @@ -336,7 +434,7 @@ ALTER TYPE email SET SCHEMA customers; - To add a new attribute to a type: + To add a new attribute to a composite type: ALTER TYPE compfoo ADD ATTRIBUTE f3 int; @@ -353,7 +451,20 @@ ALTER TYPE colors ADD VALUE 'orange' AFTER 'red'; To rename an enum value: ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve'; - + + + + + To create binary I/O functions for an existing base type: + +CREATE FUNCTION mytypesend(mytype) RETURNS bytea ...; +CREATE FUNCTION mytyperecv(internal, oid, integer) RETURNS mytype ...; +ALTER TYPE mytype SET ( + SEND = mytypesend, + RECEIVE = mytyperecv +); + + -- cgit v1.2.3