My postgres version is 9.0.4 I have created a postgres dump of my database and is containing duplicate entries of custom FUNCTIONS.When i directly query my database im not seeing any duplicates entry, but dump has this duplicates.
following is the list which i taken using pg_restore -l command.
37; 1255 16402 FUNCTION public sql_dirdepth(character varying) nidhin
31; 1255 16402 FUNCTION public sql_dirdepth(character varying) nidhin
29; 1255 16403 FUNCTION public sql_getdir(character varying) nidhin
35; 1255 16403 FUNCTION public sql_getdir(character varying) nidhin
30; 1255 16404 FUNCTION public sql_subdir(character varying, integer, integer) nidhin
36; 1255 16404 FUNCTION public sql_subdir(character varying, integer, integer) nidhin
32; 1255 16405 FUNCTION public unnest(anyarray) nidhin
38; 1255 16405 FUNCTION public unnest(anyarray) nidhin
I checked the functions inside DB and im not seeing any duplicate entries there.
CDB=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------------+-------------------+-------------------------------------+--------
public | sql_dirdepth | integer | character varying | normal
public | sql_getdir | character varying | character varying | normal
public | sql_subdir | character varying | character varying, integer, integer | normal
(3 rows)
So im wondering how the pg_dump has created duplicate entries of these functions in my dump file.
The query
SELECT *
FROM pg_proc
WHERE proname || '' IN ('unnest', 'sql_dirdepth', 'sql_getdir', 'sql_subdir');
gives the below result.
I can see duplicates in it, How can I remove the duplicate functions?
| proname | pronamespace | proowner | prolang | procost | prorows | provariadic | proisagg | proiswindow | prosecdef | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults | prosrc | probin | proconfig | proacl |
|--------------|--------------|----------|---------|---------|---------|-------------|----------|-------------|-----------|-------------|-----------|-------------|----------|-----------------|------------|-------------|----------------|-------------|-------------|----------------|------------------------------------------------------------------------------|-------------------------|-----------|--------|
| unnest | 11 | 10 | 12 | 1 | 100 | 0 | f | f | f | t | t | i | 1 | 0 | 2283 | 2277 | | | | | array_unnest | | | |
| sql_getdir | 2200 | 10 | 13 | 1 | 0 | 0 | f | f | f | t | f | i | 1 | 0 | 1043 | 1043 | | | | | sql_getdir | /opt/openkaz/lib/kazsql | | |
| sql_subdir | 2200 | 10 | 13 | 1 | 0 | 0 | f | f | f | t | f | i | 3 | 0 | 1043 | 1043 23 23 | | | | | sql_subdir | /opt/openkaz/lib/kazsql | | |
| sql_dirdepth | 2200 | 10 | 13 | 1 | 0 | 0 | f | f | f | t | f | i | 1 | 0 | 23 | 1043 | | | | | sql_dirdepth | /opt/openkaz/lib/kazsql | | |
| unnest | 2200 | 10 | 14 | 100 | 1000 | 0 | f | f | f | f | t | i | 1 | 0 | 2283 | 2277 | | | | | "select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;" | | | |
| sql_subdir | 2200 | 10 | 13 | 1 | 0 | 0 | f | f | f | t | f | i | 3 | 0 | 1043 | 1043 23 23 | | | | | sql_subdir | /opt/openkaz/lib/kazsql | | |
| sql_dirdepth | 2200 | 10 | 13 | 1 | 0 | 0 | f | f | f | t | f | i | 1 | 0 | 23 | 1043 | | | | | sql_dirdepth | /opt/openkaz/lib/kazsql | | |
| unnest | 2200 | 10 | 14 | 100 | 1000 | 0 | f | f | f | f | t | i | 1 | 0 | 2283 | 2277 | | | | | "select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;" | | | |
| sql_getdir | 2200 | 10 | 13 | 1 | 0 | 0 | f | f | f | t | f | i | 1 | 0 | 1043 | 1043 | | | | | sql_getdir | /opt/openkaz/lib/kazsql | | |
That is catalog data corruption, since there are actually duplicate entries in the pg_proc catalog, even though there is a unique index that guarantees that the combination of schema name, function name and argument list are unique.
You should perform an immediate shutdown (crash the database) and take a physical backup of the data directory. This is always the first step when dealing with data corruption.
After restarting the database, take a text mode pg_dumpall of the cluster and manually remove the duplicate entries from the dump file. Restore the dump into a new cluster and remove the old one.
Once you have successfully got rid of the data corruption, you should immediately upgrade to PostgreSQL v12 on new hardware, because a version as old as 9.0 contains many known bugs that can cause all sorts of data corruption.
This is a prime example why upgrading is not just something invented by IT departments to keep themselves in a job and to annoy end users.