Wrong note in the information schema section?

[Daniel Westermann (DWE)]

Hi %.

we have this note in the information schema section, e.g. in 
https://www.postgresql.org/docs/current/information-schema.html

..."This is because the SQL standard requires constraint names to be unique 
within a schema, but PostgreSQL does not enforce this restriction." 
...
PostgreSQL does enforce unique constraint names in a schema:

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# alter table t1 add constraint c1 unique (a);
ALTER TABLE
postgres=# alter table t1 add constraint c1 unique (a);
ERROR:  relation "c1" already exists

Am I reading this wrong? I know you can have the same constraint with different 
names:

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# alter table t1 add constraint c2 unique (a);
ALTER TABLE
postgres=# alter table t1 add constraint c3 unique (a);
ALTER TABLE

... but I guess this is not what the notes is supposed to tell me, correct?

Regards
Daniel

Re: Wrong note in the information schema section?

[David G. Johnston]

On Mon, Aug 30, 2021 at 5:51 AM Daniel Westermann (DWE) <
daniel.westerm...@dbi-services.com> wrote:

>
> we have this note in the information schema section, e.g. in
> https://www.postgresql.org/docs/current/information-schema.html
>
> ..."This is because the SQL standard requires constraint names to be
> unique within a schema, but PostgreSQL does not enforce this restriction."
> ...
> PostgreSQL does enforce unique constraint names in a schema:
>
> [...]
> ... but I guess this is not what the notes is supposed to tell me, correct?
>
>
Practically speaking there must be some level of scope where a duplicate
name error can occur.  All the docs say is that the schema scope is not
it.  You've demonstrated that it is the table scope where duplication of
names is detected.

David J.

Re: I came here to determine how much storage a boolean variable uses

[David G. Johnston]

On Sun, Aug 29, 2021 at 11:59 PM PG Doc comments form <
nore...@postgresql.org> wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/13/datatype.html
> Description:
>
> I'd like to see a fourth column added to Table 8-1 that contains the size
> or
> size range of the data type.


I would argue this is sufficiently provided by the pg_attribute catalog's
attlen and attalign fields, as pointed to on the same page linked below.

Maybe a note about the amount of space in the
> row that a NULL column uses vs a NOT NULL.
>
>
Implementation detail that is covered in the internals section:

https://www.postgresql.org/docs/current/storage-page-layout.html#STORAGE-TUPLE-LAYOUT

David J.

Re: I came here to determine how much storage a boolean variable uses

[Jonathan S. Katz]

On 8/30/21 11:31 AM, David G. Johnston wrote:
> On Sun, Aug 29, 2021 at 11:59 PM PG Doc comments form
> mailto:nore...@postgresql.org>> wrote:
> 
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/13/datatype.html
> 
> Description:
> 
> I'd like to see a fourth column added to Table 8-1 that contains the
> size or
> size range of the data type. 
> 
> 
> I would argue this is sufficiently provided by the pg_attribute
> catalog's attlen and attalign fields, as pointed to on the same page
> linked below.
> 
> Maybe a note about the amount of space in the
> row that a NULL column uses vs a NOT NULL.
> 
> 
> Implementation detail that is covered in the internals section:
> 
> https://www.postgresql.org/docs/current/storage-page-layout.html#STORAGE-TUPLE-LAYOUT
> 

Well, it is provided one level deeper in the data type docs, e.g.:

https://www.postgresql.org/docs/13/datatype-boolean.html

I think it's a reasonable request to consider. It potentially duplicates
the info in the data types section (i.e. one more thing to maintain),
but it does provide some convenience for scanning it across the
consolidated table.

Jonathan



OpenPGP_signature
Description: OpenPGP digital signature

Re: Wrong note in the information schema section?

[Daniel Westermann (DWE)]

>>..."This is because the SQL standard requires constraint names to be unique 
>>within a schema, but PostgreSQL does not enforce this >>restriction." 
>>..
>>PostgreSQL does enforce unique constraint names in a schema:

>>[...]
>>... but I guess this is not what the notes is supposed to tell me, correct?


>Practically speaking there must be some level of scope where a duplicate name 
>error can occur.  All the docs say is that the schema >scope is not it.  
>You've demonstrated that it is the table scope where duplication of names is 
>detected.

Thanks, David. The sentence above is still misleading, at least according to my 
understanding.

Regards
Daniel

Wrong note in the information schema section?

[David G. Johnston]

On Monday, August 30, 2021, Daniel Westermann (DWE)  wrote:

>
> >Practically speaking there must be some level of scope where a duplicate
> name error can occur.  All the docs say is that the schema >scope is not
> it.  You've demonstrated that it is the table scope where duplication of
> names is detected.
>
> Thanks, David. The sentence above is still misleading, at least according
> to my understanding.


Create a second table and add a constraint of the same name to it.

David J.

Re: Wrong note in the information schema section?

[David G. Johnston]

On Monday, August 30, 2021, David G. Johnston 
wrote:

> On Monday, August 30, 2021, Daniel Westermann (DWE) <
> daniel.westerm...@dbi-services.com> wrote:
>
>>
>> >Practically speaking there must be some level of scope where a duplicate
>> name error can occur.  All the docs say is that the schema >scope is not
>> it.  You've demonstrated that it is the table scope where duplication of
>> names is detected.
>>
>> Thanks, David. The sentence above is still misleading, at least according
>> to my understanding.
>
>
> Create a second table and add a constraint of the same name to it.
>
>

And your error is actually because the name of the unique index backing the
constraint is a problem, not the name of the constraint itself.  Try naming
a check constraint.

David J.

Re: Wrong note in the information schema section?

[Pantelis Theodosiou]

On Tue, Aug 31, 2021 at 6:53 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, August 30, 2021, David G. Johnston 
> wrote:
>
>> On Monday, August 30, 2021, Daniel Westermann (DWE) <
>> daniel.westerm...@dbi-services.com> wrote:
>>
>>>
>>> >Practically speaking there must be some level of scope where a
>>> duplicate name error can occur.  All the docs say is that the schema >scope
>>> is not it.  You've demonstrated that it is the table scope where
>>> duplication of names is detected.
>>>
>>> Thanks, David. The sentence above is still misleading, at least
>>> according to my understanding.
>>
>>
>> Create a second table and add a constraint of the same name to it.
>>
>>
>
> And your error is actually because the name of the unique index backing
> the constraint is a problem, not the name of the constraint itself.  Try
> naming a check constraint.
>
> David J.
>
>

As the rest of the paragraph explains, name duplication is checked at table
level for all constraints and at schema level for index-based constraints
(UNIQUE, PRIMARY KEY, EXCLUDE):

> > ... However, this extra freedom does not exist for index-based
constraints (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the
associated index is named the same as the constraint, and index names must
be unique across all relations within the same schema.

So, adding two index-based constraints (UNIQUE or PK or EXCLUDE) with same
name fails, whether they are in the same table or different ones in the
same schema.
Adding two constraints (whatever type) with same name in the same table
fails.
Adding two or more constraints with same name in different tables of the
same schema succeeds as long as none or only one is index-based.

Best regards,
Pantelis Theodosiou

Re: Wrong note in the information schema section?

[Daniel Westermann (DWE)]

 
On Monday, August 30, 2021, David G. Johnston  
wrote:
On Monday, August 30, 2021, Daniel Westermann (DWE) 
 wrote:

>>>Practically speaking there must be some level of scope where a duplicate 
>>>name error can occur.  All the docs say is that the schema >>>scope is not 
>>>it.  You've demonstrated that it is the table scope where duplication of 
>>>names is detected.

>>Thanks, David. The sentence above is still misleading, at least according to 
>>my understanding.

>Create a second table and add a constraint of the same name to it.
 

>And your error is actually because the name of the unique index backing the 
>constraint is a problem, not the name of the constraint >itself.  Try naming a 
>check constraint.

Thanks, now it makes sense:

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# create table t2 ( a int );
CREATE TABLE
postgres=# alter table t1 add constraint chk1 check ( a > 1 );
ALTER TABLE
postgres=# alter table t2 add constraint chk1 check ( a > 1 );


Regards
Daniel

Re: Wrong note in the information schema section?

[Pantelis Theodosiou]

On Tue, Aug 31, 2021 at 7:43 AM Pantelis Theodosiou 
wrote:

>
>
> On Tue, Aug 31, 2021 at 6:53 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Monday, August 30, 2021, David G. Johnston 
>> wrote:
>>
>>> On Monday, August 30, 2021, Daniel Westermann (DWE) <
>>> daniel.westerm...@dbi-services.com> wrote:
>>>

 >Practically speaking there must be some level of scope where a
 duplicate name error can occur.  All the docs say is that the schema >scope
 is not it.  You've demonstrated that it is the table scope where
 duplication of names is detected.

 Thanks, David. The sentence above is still misleading, at least
 according to my understanding.
>>>
>>>
>>> Create a second table and add a constraint of the same name to it.
>>>
>>>
>>
>> And your error is actually because the name of the unique index backing
>> the constraint is a problem, not the name of the constraint itself.  Try
>> naming a check constraint.
>>
>> David J.
>>
>>
>
> As the rest of the paragraph explains, name duplication is checked at
> table level for all constraints and at schema level for index-based
> constraints (UNIQUE, PRIMARY KEY, EXCLUDE):
>
> > > ... However, this extra freedom does not exist for index-based
> constraints (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the
> associated index is named the same as the constraint, and index names must
> be unique across all relations within the same schema.
>
> So, adding two index-based constraints (UNIQUE or PK or EXCLUDE) with same
> name fails, whether they are in the same table or different ones in the
> same schema.
> Adding two constraints (whatever type) with same name in the same table
> fails.
> Adding two or more constraints with same name in different tables of the
> same schema succeeds as long as none or only one is index-based.
>
> Best regards,
> Pantelis Theodosiou
>

I should add that the above is in the CREATE TABLE page, not in
(information-schema page):

>  Constraint Naming

> The SQL standard says that table and domain constraints must have names
that are unique across the schema containing the table or domain. PostgreSQL is
laxer: it only requires constraint names to be unique across the
constraints attached to a particular table or domain. However, this extra
freedom does not exist for index-based constraints (UNIQUE, PRIMARY KEY,
and EXCLUDE constraints), because the associated index is named the same as
the constraint, and index names must be unique across all relations within
the same schema.