[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Question on creating keys on partitioned tables
From:       Siddharth Jain <siddhsql () gmail ! com>
Date:       2023-03-31 17:35:21
Message-ID: CAPqV3pRPDM06rGQxaWWeY7XmwdtLOqOzizqo-DRkJWErX8dy1g () mail ! gmail ! com
[Download RAW message or body]

On Fri, Mar 31, 2023 at 9:07 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Siddharth Jain <siddhsql@gmail.com> writes:
> > I think the two are equivalent. If not, could you please explain why?
>
> Well, they're formally equivalent if you require there to be only one
> X value per partition (ie, PARTITION BY LIST with only one listed value
> per partition); if there's more, they're not the same thing.
>

Yes. This is what I have. I think I understand now. Is following correct?

when a PK is created on (X,Y) on the parent table what happens internally
is that the command is run individually on each of the child tables.
nothing more. nothing less.



>
> Neither one guarantees that Y is globally unique.  We have no mechanism
> for enforcing uniqueness across partitions except for partition key
> columns.
>
>                         regards, tom lane
>

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Fri, Mar 31, 2023 at 9:07 AM Tom Lane &lt;<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">Siddharth Jain &lt;<a \
href="mailto:siddhsql@gmail.com" target="_blank">siddhsql@gmail.com</a>&gt; \
writes:<br> &gt; I think the two are equivalent. If not, could you please explain \
why?<br> <br>
Well, they&#39;re formally equivalent if you require there to be only one<br>
X value per partition (ie, PARTITION BY LIST with only one listed value<br>
per partition); if there&#39;s more, they&#39;re not the same \
thing.<br></blockquote><div><br></div><div><font color="#ff0000">Yes. This is what I \
have. I think I understand now. Is following correct?</font></div><div><font \
color="#ff0000"><br></font></div><div><font color="#ff0000">when a PK is created on \
(X,Y) on the parent table what happens internally is that the command is run \
individually on each of the child tables. nothing more. nothing \
less.</font></div><div><br></div><div>  </div><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"> <br>
Neither one guarantees that Y is globally unique.   We have no mechanism<br>
for enforcing uniqueness across partitions except for partition key<br>
columns.<br>
<br>
                                    regards, tom lane<br>
</blockquote></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]