Re: Maybe a possible bug in the partitioning code?

On Mon, Nov 2, 2020 at 11:38 AM John Scalia <jayknowsunix(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > John Scalia <jayknowsunix(at)gmail(dot)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