Greenplum unsigned tinyint development

One background 1 signed tinyint using 2 signed tinyint extended two unsigned tinyint design and encoding 1 unsigned tinyint design 2 unsigned tinyint coding three encountered problems 1 the compilation was unsuccessful when compiling the expansion Pack 2 climits li Mitsh four additional content 1 unsigned tinyint expansion pack compilation installation 2 Create function five summary and Outlook 1 Summary 2 next expansion

I. Background 1.1 Use of signed tinyint

Signed tinyint this data type occupies only 1 bytes in the database and can save a lot of storage space relative to the 4-byte int or the Long data type of 8 bytes higher. In real life, there are a large number of small-scale integer data, which is important for saving data storage space if using tinyint storage in database. Therefore, its application in the database is very extensive.
The use of the tinyint data type can be supported in PostgreSQL. First, download the tinyint expansion pack from GitHub, and then put the signed tinyint extension package into the contrib directory, create the extension, and then make it easy to use the data type. The steps are as follows:
(1) Create an extension

(2) Building tables and inserting data

(3) Data size Quiz

(4) data range out of bounds
1.2 extension of signed tinyint

Compared to the data type of the signed tinyint, real life is more of a few unsigned small-range integers, such as age, seniority, family population, etc., so refer to the symbolic tinyint to construct the unsigned tinyint data type. For these non-negative data, the use of unsigned tinyint can effectively reduce the error probability of the data, such as can avoid a similar to age-20 years of error. This can reduce the generation of dirty data to some extent, which is of great significance for improving data quality. In addition, the maximum value for the signed tinyint is 127, and the unsigned tinyint range is 0 to 255. Extends the use of positive integers, and unsigned tinyint will be applied to more positive integer data storage scenarios. Therefore, by modifying the Greenplum source code, we implement an extension of the data type of unsigned tinyint in Greenplum. two, unsigned tinyint design and coding 2.1 Design of unsigned tinyint

(1) Code structure

(2) Compiling files

EXTENSION = utinyint
extversion = 0.1.1
extsql = $ (EXTENSION)--$ (extversion). sql

MODULES = utinyint
OBJS = utinyint.o
data_built = utinyint.sql
DATA = uninstall_utinyint.sql
DOCS = doc/utinyint.md
regress = utinyint

sql_in = utinyint.sql.in
Extra_clean + = $ (sql_in) $ (extsql)

use_extension = $ (Shell pg_ Config--version | Grep-qe "8\.| 9\.0 "&& echo No | | echo Yes)

ifeq ($ (use_extension), yes) all
: $ (extsql)

$ (extsql): $ (EXTENSION). SQL
    CP $< $@

DATA = $ (extsql)
endif

Pgxs: = $ (Shell pg_config--pgxs)
include $ (PGXS)

$ (sql_in): UTINYINT.SQL.IN.C
    $ (CC)-e-p $ (cppflags) $< > $@

(3) How to use
Installing the Utinyint expansion module

Test the value range of the Utinyint

Create a table using Utinyint

View data in a table

Calling functions
int type goto Utinyint type function

Utinyint type to int type function


Utinyint type to bool type function

BOOL Type Utinyint type function

Uninstalling the Utinyint extension module

2.2 unsigned tinyint encoding

(1) utinyint.h

typedef unsigned char utinyint;

The functions of Set_1_byte () and Get_1_byte () implement in Include/postgres.h
#define DATUMGETUTINYINT (X) ((utiny int) (((Datum) (x)) & 0X000000FF)) 
#define Utinyintgetdatum (x) ((Datum) (((Datum) (x) & 0x000000ff))
# Define Pg_getarg_utinyint (N)   datumgetutinyint (Pg_getarg_datum (n))
#define PG_RETURN_UTINYINT (x)   return Utinyintgetdatum (x)

extern Datum utinyint_in (Pg_function_args);
extern Datum utinyint_out (Pg_function_args);
extern Datum Utinyint_recv (Pg_function_args);
extern Datum Utinyint_send (Pg_function_args);

extern Datum Utinyint_to_i2 (Pg_function_args);
extern Datum I2_to_utinyint (Pg_function_args);
extern Datum UTINYINT_TO_I4 (Pg_function_args);
extern Datum I4_to_utinyint (Pg_function_args);
extern Datum Utinyint_to_bool (Pg_function_args);
extern Datum Bool_to_utinyint (Pg_function_args);

(2) utinyint.c

/* * utinyint_in-converts "num" to Utinyint */Datum utinyint_in (Pg_function_args) {

        Char *num = pg_getarg_cstring (0);
    Pg_return_utinyint (num, sizeof (utinyint), ' pg_atoi ');
    }/* * utinyint_out-converts utinyint to "num" */Datum Utinyint_out (Pg_function_args)
        {Utinyint arg1 = pg_getarg_utinyint (0);    Char *result = (char *) palloc (4);
        /* 3 digits, ' + ' */Pg_itoa (arg1, result);
    Pg_return_cstring (result); }/* * utinyint_recv-converts external binary format to Utinyint */Datum UTINYINT_RECV (PG

        _function_args) {StringInfo buf = (StringInfo) pg_getarg_pointer (0);
    Pg_return_utinyint ((utinyint) pq_getmsgint (buf, sizeof (Utinyint))); }/* * Utinyint_send-converts utinyint to binary format */Datum Utinyint_send (pg_func  Tion_args) {Utinyint      Arg1 = pg_getarg_utinyint (0);

        Stringinfodata buf;
        Pq_begintypsend (&BUF);
        Pq_sendint (&buf, arg1, sizeof (Utinyint));
    Pg_return_bytea_p (Pq_endtypsend (&BUF)); }/* * =================== * CONVERSION ROUTINES * =================== */Dat

        Um utinyint_to_i2 (Pg_function_args) {utinyint arg1 = pg_getarg_utinyint (0);
    Pg_return_uint16 ((uint16) arg1);

        } Datum I2_to_utinyint (Pg_function_args) {uint16 arg1 = pg_getarg_uint16 (0); if (Arg1 < 0 | | arg1 > Uchar_max) ereport (ERROR, Errcode (errcode_numeric_value_out_

        Of_range), ErrMsg ("Utinyint Out of RANGE"));
    Pg_return_utinyint ((utinyint) arg1);

        } Datum UTINYINT_TO_I4 (Pg_function_args) {utinyint arg1 = pg_getarg_utinyint (0);
    Pg_return_uint32 ((UInt32) arg1);
 } Datum   I4_to_utinyint (Pg_function_args) {UInt32 arg1 = pg_getarg_uint32 (0); if (Arg1 < 0 | | arg1 > Uchar_max) ereport (ERROR, Errcode (errcode_numeric_value_out_

        Of_range), ErrMsg ("Utinyint Out of RANGE"));
    Pg_return_utinyint ((utinyint) arg1); } Datum Utinyint_to_bool (Pg_function_args) {if (pg_getarg_utinyint (0) = = 0) pg_return_bo
        OL (FALSE);
    else Pg_return_bool (true); } Datum Bool_to_utinyint (Pg_function_args) {if (pg_getarg_bool (0) = = False) Pg_return_ut
        Inyint (0);
    else Pg_return_utinyint (1);
 }

(3) Utinyint.sql.in.c

CREATE FUNCTION utinyint_in (CString) RETURNS utinyint as ' module_pathname ' LANGUAGE c immutable STRICT;

    CREATE FUNCTION utinyint_out (utinyint) RETURNS CString as ' Module_pathname ' LANGUAGE c immutable STRICT;

    CREATE FUNCTION Utinyint_recv (internal) RETURNS utinyint as ' module_pathname ' LANGUAGE c immutable STRICT;

    CREATE FUNCTION utinyint_send (utinyint) RETURNS Bytea as ' module_pathname ' LANGUAGE c immutable STRICT;
        CREATE TYPE utinyint (INPUT = utinyint_in, OUTPUT = utinyint_out, RECEIVE = Utinyint_recv, SEND = utinyint_send, internallength = 1, passedbyvalue, ALIGNMENT = char, STORAGE = PLA

    in);
    CREATE FUNCTION utinyint_to_i2 (utinyint) RETURNS smallint as ' module_pathname ' LANGUAGE c immutable STRICT;

    CREATE CAST (utinyint as smallint) with FUNCTION Utinyint_to_i2 (utinyint) as implicit; CREATE FUNCTION i2_to_utinyint (smallint) RETURNS UtinYint as ' module_pathname ' LANGUAGE c immutable STRICT;

    CREATE CAST (smallint as Utinyint) with FUNCTION i2_to_utinyint (smallint) as assignment;
    CREATE FUNCTION UTINYINT_TO_I4 (utinyint) RETURNS integer as ' module_pathname ' LANGUAGE c immutable STRICT;

    CREATE CAST (utinyint As Integer) with FUNCTION utinyint_to_i4 (utinyint) as implicit;
    CREATE FUNCTION i4_to_utinyint (integer) RETURNS utinyint as ' module_pathname ' LANGUAGE c immutable STRICT;

    CREATE CAST (integer as Utinyint) with FUNCTION i4_to_utinyint (integer) as assignment;
    CREATE FUNCTION Utinyint_to_bool (utinyint) RETURNS bool as ' module_pathname ' LANGUAGE c immutable STRICT;

    CREATE CAST (Utinyint as bool) with FUNCTION Utinyint_to_bool (utinyint) as assignment;
    CREATE FUNCTION Bool_to_utinyint (bool) RETURNS utinyint as ' module_pathname ' LANGUAGE c immutable STRICT; CREATE CAST (bool as Utinyint) with FUNCTION Bool_to_utinyint (BOOL) as assignment; 

third, the problems encountered 3.1 The compilation was unsuccessful when I first compiled the extension package

Since the installation was compiled under Eclipse at the very beginning, it would not be successful to install the extension package to the terminal compile, only at the same time.

3.2 climits (limits.h)

List some constant values:

    Name expresses min. magnitude* char_bit number of bits for a CHAR object (byte) 8 schar_min Minimum Value for a object of type signed char-127 Schar_max Maximum value for an object of type signed char 127 UCHAR    _max Maximum value for a object of type unsigned char 255 char_min Minimum value for an object of type Char Either Schar_min or 0 Char_max Maximum value for an object of type CHAR either Schar_max or Uchar_max Mb_le N_max Maximum number of bytes in a multibyte character, for all locale 1 shrt_min Minimum value for an object o F type short int-32767 Shrt_max Maximum value for a object of type short int 32767 Ushrt_max Maximum va  Lue for a object of type unsigned short int 65535 int_min Minimum value for an object of type int-32767 Int_max Maximum value for a object of type int 32767 Uint_max Maximum value for a object of type unsigned short int 655 Long_min MinimUm value for a object of type long int-2147483647 Long_max Maximum value for a object of type long int 214 7483647 Ulong_max Maximum value for an object of type unsigned int 4294967295

Iv. Additional content 4.1 compiled installation of unsigned tinyint expansion packs

(1) Close the server first

(2) Make & make Install
Go directly to the contrib directory to find the corresponding expansion package, go to the extension package file for compilation and installation;

(3) Re-open the server
4.2 Create function

Create a function such as:

Call Function:

4.3 Installing the expansion module
The functionality of the expansion pack can only be used after the extension has been created;

If you do not use it, you can delete the extension, but you need to be cautious;

If an existing table uses the data type, the deletion is unsuccessful. v. Summary and Prospect 5.1 Summary

This course, our group through the Group Learning cooperation, the joint completion of this course design. For this sub-group cooperation, the harvest is briefly shared. We have a simple learning content, independent study and find information, so as to quickly solve the corresponding specific problems, and complex knowledge content, we need to discuss the exploration, so the determination of cooperation content needs each of us to consider carefully. In the actual use of the process, there will always be some problems, through the joint efforts to complete the course design. Our experience is summarized as follows:
(1) Through the modification of the source code, learning to extend the use of the program for the future to provide a cushion for learning.
(2) Mastered the conversion of two data types need to modify the place, the conversion between the same point and different points.
(3) The use of GP is more proficient. 5.2 Next Expansion

(1) smallint, integer, bigint convert to unsigned smallint, integer, bigint

(2) Non-symbolic tinyint and other types of data between the deletion and modification

Execute SQL:

(3) Further improvement of Pg_atoi () and Pg_itoa ()