Postgres Proactively Drop and Recreate Temp Tables

book

Article ID: 222318

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

To prevent issues from continually re-occurring with the Portfolio Synchronization jobs, proactively drop and recreate temp tables.

Cause

Postgres does not permanently delete a column when it is deleted.

The Portfolio sync job will create and delete columns in temp tables as part of its design.

Postgres has a system limit of 1600 columns per table

After deleting a column, it is still there (but renamed) in the pg_attributes table

The only way to remove it from the column count is to DROP and CREATE the table

Environment

Release : 15.9.2, 15.9.3

Component : Portfolio Sync Job

Resolution

1- Run this query to see what tabls are impacted:

SELECT DISTINCT(attrelid),COUNT(attrelid) 
FROM pg_attribute 
WHERE attrelid IN (
SELECT oid FROM pg_class)
AND attrelid > 100000
GROUP BY attrelid
HAVING COUNT(attrelid) > 1500
ORDER BY COUNT(attrelid) DESC

 

2-  Get the table name:

-- from the attrelid ' s returned above:

SELECT relname FROM pg_class WHERE OID = 1357426296

-- returns table name for step #3

 

3- Drop and Create the table  --- you may use DB GUi client tool to generate DDL for the table before dropping it

 

-- Alternative steps using Postgres command line tools

[[email protected] work_folder]$ pg_dump -U schemaNameHere -d schemaNameHere -t temp_pfm_ca_inv -f /home/postgres/work_folder/work_folder_temp_pfm_ca_inv.sql
Password:
[[email protected] work_folder]$ ls -ltr
total 124
-rw-------. 1 postgres postgres 5304 Aug 23 15:46 work_folder_columnlist_rowcount_pre.txt
-rw-------. 1 postgres postgres 116926 Aug 23 15:56 work_folder_temp_pfm_ca_inv.sql
[[email protected] work_folder]$ psql -U schemaNameHere -d schemaNameHere
Password for user schemaNameHere:
psql (11.7)
Type "help" for help.

schemaNameHere=> DROP TABLE temp_pfm_ca_inv;
DROP TABLE
schemaNameHere=> \d+ temp_pfm_ca_inv
Did not find any relation named "temp_pfm_ca_inv".
schemaNameHere=> exit
[[email protected] work_folder]$ ls -ltr
total 124
-rw-------. 1 postgres postgres 5304 Aug 23 15:46 work_folder_columnlist_rowcount_pre.txt
-rw-------. 1 postgres postgres 116926 Aug 23 15:56 work_folder_temp_pfm_ca_inv.sql
[[email protected] work_folder]$ psql -U schemaNameHere -d schemaNameHere -f work_folder_temp_pfm_ca_inv.sql
Password for user schemaNameHere:
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COMMENT
COPY 110
ALTER TABLE
CREATE INDEX
GRANT
[[email protected] work_folder]$ ls -ltr
total 124
-rw-------. 1 postgres postgres 5304 Aug 23 15:46 work_folder_columnlist_rowcount_pre.txt
-rw-------. 1 postgres postgres 116926 Aug 23 15:56 work_folder_temp_pfm_ca_inv.sql
[[email protected] work_folder]$ psql -U schemaNameHere -d schemaNameHere
Password for user schemaNameHere:
psql (11.7)
Type "help" for help.

schemaNameHere=> \o /home/postgres/work_folder/work_folder_columnlist_rowcount_post.txt
schemaNameHere=>
schemaNameHere=> SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'temp_pfm_ca_inv';
schemaNameHere=> SELECT count(1) from temp_pfm_ca_inv;
schemaNameHere=>
schemaNameHere=> \o
schemaNameHere=>
schemaNameHere=> exit
[[email protected] work_folder]$ ls -ltr
total 132

Additional Information

The design of adding columns to temp tables is being looked as a defect:  DE61489

Attachments