pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMaybe a possible bug in the partitioning code?
5+ messages / 3 participants
[nested] [flat]
* Maybe a possible bug in the partitioning code?
@ 2020-11-02 16:22 John Scalia <jayknowsunix@gmail.com>
2020-11-02 16:31 ` Re: Maybe a possible bug in the partitioning code? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: John Scalia @ 2020-11-02 16:22 UTC (permalink / raw)
To: pgsql-admin
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 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?
—
Jay
Sent from my iPad
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Maybe a possible bug in the partitioning code?
2020-11-02 16:22 Maybe a possible bug in the partitioning code? John Scalia <jayknowsunix@gmail.com>
@ 2020-11-02 16:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-02 16:38 ` Re: Maybe a possible bug in the partitioning code? John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-11-02 16:31 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Maybe a possible bug in the partitioning code?
2020-11-02 16:22 Maybe a possible bug in the partitioning code? John Scalia <jayknowsunix@gmail.com>
2020-11-02 16:31 ` Re: Maybe a possible bug in the partitioning code? Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-11-02 16:38 ` John Scalia <jayknowsunix@gmail.com>
2020-11-03 05:12 ` Re: Maybe a possible bug in the partitioning code? Keith <keith@keithf4.com>
0 siblings, 1 reply; 5+ messages in thread
From: John Scalia @ 2020-11-02 16:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Maybe a possible bug in the partitioning code?
2020-11-02 16:22 Maybe a possible bug in the partitioning code? John Scalia <jayknowsunix@gmail.com>
2020-11-02 16:31 ` Re: Maybe a possible bug in the partitioning code? Tom Lane <tgl@sss.pgh.pa.us>
2020-11-02 16:38 ` Re: Maybe a possible bug in the partitioning code? John Scalia <jayknowsunix@gmail.com>
@ 2020-11-03 05:12 ` Keith <keith@keithf4.com>
2020-11-03 10:38 ` Re: Maybe a possible bug in the partitioning code? John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Keith @ 2020-11-03 05:12 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Maybe a possible bug in the partitioning code?
2020-11-02 16:22 Maybe a possible bug in the partitioning code? John Scalia <jayknowsunix@gmail.com>
2020-11-02 16:31 ` Re: Maybe a possible bug in the partitioning code? Tom Lane <tgl@sss.pgh.pa.us>
2020-11-02 16:38 ` Re: Maybe a possible bug in the partitioning code? John Scalia <jayknowsunix@gmail.com>
2020-11-03 05:12 ` Re: Maybe a possible bug in the partitioning code? Keith <keith@keithf4.com>
@ 2020-11-03 10:38 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: John Scalia @ 2020-11-03 10:38 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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:
>
>
>
>
>> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-11-03 10:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-02 16:22 Maybe a possible bug in the partitioning code? John Scalia <jayknowsunix@gmail.com>
2020-11-02 16:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-02 16:38 ` John Scalia <jayknowsunix@gmail.com>
2020-11-03 05:12 ` Keith <keith@keithf4.com>
2020-11-03 10:38 ` John Scalia <jayknowsunix@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox