Обсуждение: Maybe a possible bug in the partitioning code?

Hi all, again,

While I’m waiting for my devs to confirm the first table that I partitioned is working correctly, I started working on
secondof 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,
asthis 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
maybedrop it from there directly? 
—
Jay

Sent from my iPad

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
onsecond 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
maybedrop 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

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
onsecond 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
maybedrop 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

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.



On Mon, Nov 2, 2020 at 11:38 AM John Scalia <jayknowsunix@gmail.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