Cannot Drop Azure Data Factory Temporary Staging Tables

Kyle Bender 21

I'm using ADF to copy data from on-prem Postgres to Azure Postgres. I'm copying to a temp table in Azure Postgres. From the temp table I then perform an Upsert using Azure Data Factory to then load the data into another Azure Postgres table for production. Two times I have had data flows timeout during a pipeline run. Both times, the Temporary staging table created by ADF have stayed in my Azure Postgres Database. These temporary staging tables cannot be dropped. Does anyone know of a way where these can be dropped?

GeethaThatipatri-MSFT 27,012 Reputation points Microsoft Employee

2022-10-05T18:40:29.12+00:00

Hi, @Kyle Bender Welcome to the Microsoft Q&A platform, and thanks for posting your question here.
I understand you are facing an issue while you are trying to drop the temporary staging table.
Temp tables are session-based, so you need to check whether the session is still there, or it automatically drops when the session ends.
Pg_stat_activity should give you the reason why you are unable to drop these tables.

Regards
Geetha
Kyle Bender 21 Reputation points

2022-10-05T18:47:40.337+00:00

Hi, @GeethaThatipatri-MSFT thank you for responding.
I don't see any current sessions open, while the temporary staging tables are still there. I used "SELECT * FROM pg_stat_activity WHERE state = 'active';"
GeethaThatipatri-MSFT 27,012 Reputation points Microsoft Employee

2022-10-06T20:27:10.983+00:00

@Kyle Bender Is the owner of the temp table different from the login user? - maybe you do not have permission to drop, you should check the permissions of your login and the login that ADF used to connect

Regards
Geetha
Kyle Bender 21 Reputation points

2022-10-06T20:33:55.663+00:00

I'm connected to the database using the admin credentials for the Azure Database for PostgreSQL single server. But, for ADF, I'm just logging into Azure as my default firstname.lastname@keyman account. How would I switch the user in the database to my default login to then drop these temp tables?
Kyle Bender 21 Reputation points

2022-10-06T20:42:32.657+00:00

What is now weird is that I deleted the connection to the database, reconnected as the database admin, and then was able to drop the temp tables. I'm not sure what would have changed from yesterday to today but the temp tables are now dropped. Would love to know the reasoning or how to make sure I'm able to do this in the future.
GeethaThatipatri-MSFT 27,012 Reputation points Microsoft Employee

2022-10-06T21:05:33.433+00:00

@Kyle Bender Good to know that it got deleted now, unless the user has full permission you should be able to delete it, please do reach out if you face similar issue.

Regards
Geetha