Content-Type: multipart/alternative;
 boundary=Apple-Mail-E13C5F59-8830-4083-8F13-9A9D4F32CB37
Content-Transfer-Encoding: 7bit
From: John Scalia <jayknowsunix@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Maybe a possible bug in the partitioning code?
Date: Tue, 3 Nov 2020 05:38:00 -0500
Message-Id: <AAE8A86F-CA20-4285-8C77-251826B91305@gmail.com>
References: 
 <CAHw75vtdBH2A_qyKTtDW=JqNkhQe4C_XYUCA1DrxwNzRqPg7zg@mail.gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-admin <pgsql-admin@postgresql.org>
In-Reply-To: 
 <CAHw75vtdBH2A_qyKTtDW=JqNkhQe4C_XYUCA1DrxwNzRqPg7zg@mail.gmail.com>
To: Keith <keith@keithf4.com>
Precedence: bulk


--Apple-Mail-E13C5F59-8830-4083-8F13-9A9D4F32CB37
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

Not at all a silly question, Keith. I know that I=E2=80=99ve also done that i=
n the past. The issue here, though is I never disconnected from the developm=
ent database, and \dt *.table_name produces nothing, while trying to rerun t=
he script still produces the error that the table_name already exists. It=E2=
=80=99s incredibly strange, as I=E2=80=99ve never had PostgreSQL fail to rol=
lback a complete transaction block. To work around it, I just changed the na=
me of the partition child table. Since this is just a dev database, I don=E2=
=80=99t 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:
>=20
> =EF=BB=BF
>=20
>=20
>> On Mon, Nov 2, 2020 at 11:38 AM John Scalia <jayknowsunix@gmail.com> wrot=
e:
>> Ok I also tried that Tom. Psql returned =E2=80=9CDid not find any relatio=
ns named =E2=80=9C=E2=80=98.temp_my_table_global=E2=80=9D
>>=20
>> Sent from my iPad
>>=20
>> > On Nov 2, 2020, at 11:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> >=20
>> > =EF=BB=BFJohn Scalia <jayknowsunix@gmail.com> writes:
>> >> While I=E2=80=99m 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 w=
as a typo in it. Now, as this code runs in a transaction block, so I don=E2=80=
=99t think anything it created should actually be present in the database, b=
ut on a subsequent rerunning, I got an error that one of the partition table=
s actually exists already. However, I cannot see it, nor drop it from the se=
rver. The error in the script is: relation =E2=80=9Ctemp_my_second_table_glo=
bal=E2=80=9D already exists, but the system cannot see it in order to drop i=
t, saying =E2=80=9Ctemp_my_second_table_global=E2=80=9D does not exist. That=
=E2=80=99s really a catch-22. Is there a system table where I can look and m=
aybe drop it from there directly?
>> >=20
>> > Kinda sounds like a confusion over search_path, ie script is creating
>> > table in some schema that's not in your interactive search path?
>> >=20
>> > In psql, try
>> >   \d *.temp_my_second_table_global
>> > to see instances of temp_my_second_table_global in all schemas.
>> >=20
>> >            regards, tom lane
>>=20
>>=20
>=20
> I know this seems a silly question but are you sure you're connecting to t=
he 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=
.
>=20
> Keith

--Apple-Mail-E13C5F59-8830-4083-8F13-9A9D4F32CB37
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Not at all a silly question, Keith. I know t=
hat I=E2=80=99ve also done that in the past. The issue here, though is I nev=
er 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=E2=80=99s incredibly strange, as I=E2=80=99ve=
 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=E2=80=99t really care if something is hanging=
 out that no one can see.<br><br><div dir=3D"ltr">Sent from my iPad</div><di=
v dir=3D"ltr"><br><blockquote type=3D"cite">On Nov 3, 2020, at 12:13 AM, Kei=
th &lt;keith@keithf4.com&gt; wrote:<br><br></blockquote></div><blockquote ty=
pe=3D"cite"><div dir=3D"ltr">=EF=BB=BF<div dir=3D"ltr"><div dir=3D"ltr"><br>=
</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">O=
n Mon, Nov 2, 2020 at 11:38 AM John Scalia &lt;<a href=3D"mailto:jayknowsuni=
x@gmail.com">jayknowsunix@gmail.com</a>&gt; wrote:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">Ok I also tried that Tom. Psql returned =E2=
=80=9CDid not find any relations named =E2=80=9C=E2=80=98.temp_my_table_glob=
al=E2=80=9D<br>
<br>
Sent from my iPad<br>
<br>
&gt; On Nov 2, 2020, at 11:31 AM, Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh=
.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrote:<br>
&gt; <br>
&gt; =EF=BB=BFJohn Scalia &lt;<a href=3D"mailto:jayknowsunix@gmail.com" targ=
et=3D"_blank">jayknowsunix@gmail.com</a>&gt; writes:<br>
&gt;&gt; While I=E2=80=99m waiting for my devs to confirm the first table th=
at I partitioned is working correctly, I started working on second of three t=
hat I need to do. So, I added the code to end of my original script, but the=
re was a typo in it. Now, as this code runs in a transaction block, so I don=
=E2=80=99t think anything it created should actually be present in the datab=
ase, 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 t=
he server. The error in the script is: relation =E2=80=9Ctemp_my_second_tabl=
e_global=E2=80=9D already exists, but the system cannot see it in order to d=
rop it, saying =E2=80=9Ctemp_my_second_table_global=E2=80=9D does not exist.=
 That=E2=80=99s really a catch-22. Is there a system table where I can look a=
nd maybe drop it from there directly?<br>
&gt; <br>
&gt; Kinda sounds like a confusion over search_path, ie script is creating<b=
r>
&gt; table in some schema that's not in your interactive search path?<br>
&gt; <br>
&gt; In psql, try<br>
&gt;&nbsp; &nbsp;\d *.temp_my_second_table_global<br>
&gt; to see instances of temp_my_second_table_global in all schemas.<br>
&gt; <br>
&gt;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; regards, tom lane<br>
<br>
<br></blockquote><div><br></div><div>I know this seems a silly question but a=
re you sure you're connecting to the same database when you run your script v=
s when you're logging in to check? Made the same mistake many times myself t=
hat led to all kinds of confusion.</div><div><br></div><div>Keith<br></div><=
/div></div>
</div></blockquote></body></html>=

--Apple-Mail-E13C5F59-8830-4083-8F13-9A9D4F32CB37--