Not at all a silly question, Keith. I know that I’ve also done that in the past. The issue here, though is I never disconnected from the development database, and \dt *.table_name produces nothing, while trying to rerun the script still produces the error that the table_name already exists. It’s incredibly strange, as I’ve never had PostgreSQL fail to rollback a complete transaction block. To work around it, I just changed the name of the partition child table. Since this is just a dev database, I don’t really care if something is hanging out that no one can see.
Sent from my iPad
On Nov 3, 2020, at 12:13 AM, Keith <keith@keithf4.com> wrote:
Ok I also tried that Tom. Psql returned “Did not find any relations named “‘.temp_my_table_global”
Sent from my iPad
> On Nov 2, 2020, at 11:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> John Scalia <jayknowsunix@gmail.com> writes:
>> While I’m waiting for my devs to confirm the first table that I partitioned is working correctly, I started working on second of three that I need to do. So, I added the code to end of my original script, but there was a typo in it. Now, as this code runs in a transaction block, so I don’t think anything it created should actually be present in the database, but on a subsequent rerunning, I got an error that one of the partition tables actually exists already. However, I cannot see it, nor drop it from the server. The error in the script is: relation “temp_my_second_table_global” already exists, but the system cannot see it in order to drop it, saying “temp_my_second_table_global” does not exist. That’s really a catch-22. Is there a system table where I can look and maybe drop it from there directly?
>
> Kinda sounds like a confusion over search_path, ie script is creating
> table in some schema that's not in your interactive search path?
>
> In psql, try
> \d *.temp_my_second_table_global
> to see instances of temp_my_second_table_global in all schemas.
>
> regards, tom lane
I know this seems a silly question but are you sure you're connecting to the same database when you run your script vs when you're logging in to check? Made the same mistake many times myself that led to all kinds of confusion.
Keith