Can't create or drop a table in schema

Post date: Mar 20, 2014 2:37:39 PM

Problem: Create table command fails with error ERROR: relation "abc" already exists (seg4 sdw1:40004 pid=13649). drop table command fails with error relation does not exists. 

Cause of the problem: A function crashed on a segment and left a inconsistent catalog. you can check on sdw4:40004 in pg_class and you should see the table there. The question is: where else does the table exist. You can run gpcheckcat to check all catalogs.

Resolution: To fix the issue access the master in utility mode and add that table definition. then access normally and drop it. That assumes that the table exists on all other segments, just not the master.

Step 1: Run gpcheckcat - Run as gpadmin $GPHOME/bin/lib/gpcheckcat -p 5432 -A>checkcat_A.out 2>&1 &

Step 2. Look at the checkcat_A.out and log from /home/gpadmin/gpAdminLogs/gpcheckcat_20140315.log **20140315 is the date when you ran the utility.

Step 3: Shutdown the database

Step 4: Starts Greenplum Database in restricted mode $gptart -R

(only database superusers are allowed to connect).

step 5: Run gpcheckcat - Run as gpadmin $GPHOME/bin/lib/gpcheckcat -p 5432 -B>checkcat_B.out 2>&1 &

Step 6. Run gpcheckcat repair script

Step 7: Run gpcheckcat - Run as gpadmin $GPHOME/bin/lib/gpcheckcat -p 5432 -C>checkcat_C.out 2>&1 &

Step 8: Fix Catalog issue

1. Stop the database

2. Start the database (master only) $gpstart -m

3. Connect to the database in utility mode (you need to access the master in utility mode so it only creates relations on the master. Then exit and re-connect normally to drop.)

4. PGOPTIONS='-c gp_session_role=utility' psql  sachi (For help run [gpadmin@sachi ~]$ gpstart -?/PGO) will give you syntex for utility mode.

5. Create table abc. (it will create the table abc in the master only)

6. Exit the utility mode ($\q)

7. Restart the database ($gpstap -arf)

8. Connect to the database

9. Drop  table abc.

Step 9: Run gpcheckcat - Run as gpadmin $GPHOME/bin/lib/gpcheckcat -p 5432 -D>checkcat_D.out 2>&1 &

Note: When you run create table script inside an stored procedure/function in Greenplum and function fails then it rolls back only master but table definition in the segments exist. This is the reason of the above problem.