INITDB analysis of Postgres--from the failure of one plugin upgrade

Our company has developed a database product based on Postgres, needless to say we have changed the source code of OSS, and have also integrated and written some plugins. Therefore, when PostgreSQL and related plugins are upgraded, we also need to reflect the upgrade to our products, which is the background.

This time the problem occurred when we upgraded PostgreSQL plugin ORAFCE (3.2.0-->3.6.0). As usual, we will upgrade the plugin, organize the source code to compile, OK compile pass.

However, when we were testing, the program was initdb when the error stopped. The error is as follows:

  [[email protected] data]$ initdb-d ' pwd ' The files belonging to this database system would be owned by user "Postgres". This user must also own the server process. The database cluster is initialized with locale "en_US. UTF-8 ". The default database encoding have accordingly been set to "UTF8". The default text search configuration is set to "中文版". (15541) Data page checksums is disabled. (18153) Fixing permissions on existing Directory/data ... ok (15516) Creating subdirectories ... ok (15516) selecting defaul T max_connections ... 100selecting Default Shared_buffers ...  128MBselecting Dynamic shared Memory implementation ... posixcreating configuration files ... ok (15516) Creating template1 Database in/data/base/1 ... ok (15516) initializing Pg_authid ... ok (15516) initializing dependencies ... ok (15516) creat ing information schema ... Fatal:relation "Information_schema.columns" does not exist (10414) ... (omitted below)  

The crowd was puzzled. Just upgrade the plug-in, how can it lead to the creation of INITDB?

I noticed this line in the error:

creating information schema ... FATAL: relation "information_schema.columns" does not exist (10414)

Seems pretty familiar? I looked at the latest upgrade file for ORAFCE. The following SQL text is found in the file:

-- Oracle system viewscreate view oracle.user_tab_columns as    select table_name,           column_name,           data_type,           coalesce(character_maximum_length, numeric_precision) AS data_length,           numeric_precision AS data_precision,           numeric_scale AS data_scale,           is_nullable AS nullable,           ordinal_position AS column_id,           is_updatable AS data_upgraded,           table_schema    from information_schema.columns;

Seems to be on the, really is the escalation of trouble? Should not ah, the community should not be so stupid ah, again just a plug-in, how can affect Postgres? This time has not been imported plug-in, how can there be this error?

Confused.

Because the PostgreSQL code in our product also has our own added code, in order to determine the source of the error, I first took a copy of PostgreSQL source, in the contrib directory to put the latest ORAFCE code. and make the world.

In the Run INITDB command:

(以上省略)copying template1 to postgres ... ok (15516)syncing data to disk ... ok (15516)WARNING: enabling "trust" authentication for local connectionsYou can change this by editing pg_hba.conf or using the option -A, or--auth-local and --auth-host, the next time you run initdb.Success. You can now start the database server using:    pg_ctl -D /data -l logfile start

It worked! Description is not an issue with OSS.

Well, that's the problem with our fix code. However initdb this piece of code is not maintained by our team, I do not know what our product has changed. OK, look at the difference code.

Result differential code look, INITDB.C file we have modified two lines of code, look, but also just improve the code, and this time the problem has nothing to do. Convoluted....

Well, since it's initdb out of the question, I'll look at Initdb's code and think about it. Run over and look at the code that throws the error message. Together with their own finishing. Probably figure out why.

What is the specific reason to sell a xiaoguanzi first. Let's take a look at what the PostgreSQL will do when Initdb (the indentation represents the call relationship):

Main ensures that the buffering behavior of stdout and stderr matches the behavior in interactive use set application-specific locale and service catalog process command-line options sync_only mode password and server recognition Certificate related setup_pgdata () Set Pgdata environment setup_bin_paths () get the path to the Postgres command and determine if it matches the Initdb version set_info_version () The version number required to extract the information pattern Etup_data_file_paths () Check initialization script and profile template setup_locale_encoding () Set encoding Method Setup_text_search () set text according to encoding Search mode Do_sync mode initialize_data_directory create_data_directory () Create database directory Pgdata Create_xlog_or_symli NK () Create pg_xlog directory under Pgdata create additional subdirectories Create and configure postgresql.conf file Bootstrap_template1 () call Postgres Create in bootstrap mode The template database Template1 (the following configuration is for the template database template1) Setup_auth () sets the template1 password table setup_depend () to establish the system table Pg_depend, which describes A dependency between database Objects setup_sysviews () Run script System_views.sql Create system View ★setup_description () Create system tables Pg_description and PG_SHDESCRI Ption setup_collation () Creates a system table Pg_collation, which describes the available collations, essentially from a SQL name to the map of the operating system locale classification Setup_conversion () run the script con Version_create.sql Creating a system table Pg_conversIon, which describes the encoding conversion process setup_dictionary () runs the script Snowball_create.sql creates some additional directories setup_privileges () sets the default for some of the postgres built-in object Permissions Setup_schema () Run script Information_schema.sql create mode information_schema★load_plpgsql () load Plpgsql server-side programming language VAC uum_db () Cleanup template1 make_template0 () copy copies a copy as Template0 Make_postgres () According to the good template1, copy copies a copy as P Ostgres tells the user Initdb execution ends

The above is the implementation of the INITDB process. It's very clear and simple. The most important of these is the Initialize_data_directory () function. It is this function that builds up the basic elements of the database step by step. So the problem is in the "inside" of the function.

A smart friend might have seen it, and I played two "★" on it. Yes, there is no mistake, it is the problem of these two pieces of code, oh no, exactly is the problem of SQL.

below.

Our adorable developers have added two lines to System_views.sql (asking if they want to use the ORAFCE feature but do not want to create it manually and use it "transparently"):

CREATE EXTENSION orafce;SET search_path TO DEFAULT;

We know that the initialization of ORAFCE refers to the Information_schema.columns view, which is created in information_schema.sql .

And by the above Initdb execution process can see: system_views.sql execution order before Information_schema.sql, also said:
When we initialize the ORAFCE, Information_schema.columns does not exist, so we will report the above error when we execute the system_views.sql.

Know the reason is very good change, as long as the two lines to ensure that the addition of SQL code after the creation of INFORMATION_SCHEMA can ~

Above

INITDB analysis of Postgres--from the failure of one plugin upgrade