Re: Data Type Size Calculation
https://www.depesz.com/2022/02/13/how-much-disk-space-you-can-save-by-using-int4-int-instead-of-int8-bigint/
Hope this link is useful.
create table testb as select 'true'::bool as b from
generate_series(1,100) i;SELECT 100
$ \dt+ testb
List of relations
Schema │ Name │ Type │ Owner │ Persistence │ Access method │ Size
│ Description
┼───┼───┼┼─┼───┼───┼─
public │ testb │ table │ depesz │ permanent │ heap │ 35 MB │
Why is that, though? From what I gather the answer is: performance. I don't
> know low-level details, but based on what I understand, processors process
> data in arch-dependent block sizes. 64bit processor works on 64 bits. And
> this means that if you want to do something on int4 value, that is part of
> 8 byte block, you have to add operation to zero the other 32 bits.
>
On Wed, Feb 16, 2022 at 12:26 AM Troy Frericks
wrote:
> For now, yes... I'm suggesting that the documentation be completed by
> adding a few sentences few extra sentences.
> Troy.
> #
>
>
> On Mon, Feb 14, 2022, 12:51 Bruce Momjian wrote:
>
>> On Fri, Feb 11, 2022 at 08:12:08PM +, PG Doc comments form wrote:
>> > The following documentation comment has been logged on the website:
>> >
>> > Page: https://www.postgresql.org/docs/13/datatype-numeric.html
>> > Description:
>> >
>> > > The actual storage requirement is two bytes for each group of four
>> decimal
>> > digits, plus three to eight bytes overhead.
>> >
>> > Please describe what 'overhead' means.
>> >
>> > I'd like to be able to calculate the data size of NUMBER(19,4). I can
>> > calculate 2 bytes per 4 digits... with 19 digits, I have 5 groups of 4
>> > digits,
>> >
>> > so the data length I seek is 5 bytes + overhead... then I'm left
>> hanging.
>> > :(
>>
>> Well, you can create it and then call pg_column_size():
>>
>> CREATE TABLE test (x NUMERIC(19,4));
>>
>> SELECT pg_column_size('test.x');
>> pg_column_size
>>
>> 7
>>
>> If you want more details, you will need to look at the source code.
>>
>> --
>> Bruce Momjian https://momjian.us
>> EDB https://enterprisedb.com
>>
>> If only the physical world exists, free will is an illusion.
>>
>>
Re: Question about role attributes docs
On Tue, Feb 15, 2022 at 1:32 PM Shinya Kato wrote: > On 2022-01-12 02:07, Laurenz Albe wrote: > > On Tue, 2022-01-11 at 16:40 +0900, Shinya Kato wrote: > >> I have a question about the documentation on ROLE. > >> > >> According to [1], INHERIT and BYPASSRLS can be specified when > >> executing > >> the CREATE ROLE command. However, there is no such description in Role > >> Attributes in [2]. Are these concepts different from Role Attributes? > >> Or > >> are they just not documented? If they need to be documented, I'll > >> create > >> a patch. > >> > >> [1] https://www.postgresql.org/docs/devel/sql-createrole.html > >> [2] https://www.postgresql.org/docs/devel/role-attributes.html > > > > I think that is indeed an omission, and adding documentation would be a > > good idea. > Thanks! I created the patch, and attached it. > > > On the other hand, a lot of that information is more or less > > a duplicate of the CREATE ROLE documentation. I wonder if the latter > > page could be removed altogether. > I think there is certainly a lot of overlap. However, I think that the > SQL commands page and the database roles page should exist separately, > and should be maintained as they are because there are parts that do not > overlap (for example, IN ROLE and ADMIN). > > -- > Regards, > > -- > Shinya Kato > Advanced Computing Technology Center > Research and Development Headquarters > NTT DATA CORPORATION May I suggest replacing the following verbiage in your patch +A role is needed to permission to inherit privileges of roles it is a member of. +(except for superusers, since those bypass all permission checks). +If not specified, INHERIT is the default, so to create such a role, use either: with clearer wording such as the following: A role can explicitly be restricted at time of creation from inheriting privileges of roles it is a member of (except for superusers, since those bypass all permission checks.) Restricting privileges is done by the NOINHERIT option. If no option is specified, INHERIT is the default. So to create a role that inherits privileges, use either: Regards, Swaha Miller Amazon Web Services
Re: Data Type Size Calculation
For now, yes... I'm suggesting that the documentation be completed by
adding a few sentences few extra sentences.
Troy.
#
On Mon, Feb 14, 2022, 12:51 Bruce Momjian wrote:
> On Fri, Feb 11, 2022 at 08:12:08PM +, PG Doc comments form wrote:
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/13/datatype-numeric.html
> > Description:
> >
> > > The actual storage requirement is two bytes for each group of four
> decimal
> > digits, plus three to eight bytes overhead.
> >
> > Please describe what 'overhead' means.
> >
> > I'd like to be able to calculate the data size of NUMBER(19,4). I can
> > calculate 2 bytes per 4 digits... with 19 digits, I have 5 groups of 4
> > digits,
> >
> > so the data length I seek is 5 bytes + overhead... then I'm left hanging.
> > :(
>
> Well, you can create it and then call pg_column_size():
>
> CREATE TABLE test (x NUMERIC(19,4));
>
> SELECT pg_column_size('test.x');
> pg_column_size
>
> 7
>
> If you want more details, you will need to look at the source code.
>
> --
> Bruce Momjian https://momjian.us
> EDB https://enterprisedb.com
>
> If only the physical world exists, free will is an illusion.
>
>
Does the POSITION() function takes into account the COLLATION... or not ?!?
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/14/functions-string.html
Description:
The doc doesn't say anything about the impact of the COLLATION on the
results of any of the string functions, so I tried some interesting tests
with POSITION():
SELECT POSITION(('ß' COLLATE "de_DE.utf8") IN 'DASS'); -- should return 3
instead of 0 !?!
SELECT POSITION(('ss' COLLATE "de_DE.utf8") IN 'daß'); -- should return 3
instead of 0 !?!
SELECT POSITION(('oe' COLLATE "fr_FR.utf8") IN 'bœuf'); -- should return 2
instead of 0 !?!
SELECT POSITION(('œ' COLLATE "fr_FR.utf8") IN 'boeuf'); -- should return 2
instead of 0 !?!
SELECT POSITION(('å' COLLATE "en_US.utf8") IN 'yeah'); -- should return 3
instead of 0 !?!
SELECT POSITION(('o' COLLATE "en_US.utf8") IN 'ångström'); -- should return
7 instead of 0 !?!
==> up to here, this seems pretty enough to conclude that POSITION() doesn't
care at all about COLLATION and always perform a byte search.
Now the great surprise comes !...
CREATE COLLATION public.case_and_accent_insensitive (
provider = icu,
locale = 'und-u-ks-level1',
deterministic = false
);
SELECT POSITION(('o' COLLATE public.case_and_accent_insensitive) IN
'ångström');
=> gives ERROR: "nondeterministic collations are not supported for substring
searches"
Does the POSITION() function pretends taking into account the COLLATION ??
or not ??
- If not, then why the hell is there this error message about
nondeterministic collations while the POSITION() doesn't care at all about
the COLLATION...
- If yes, then the first 6 lines of SQL above are returning the wrong
value... (are there any specific technical limitations here ?)
I would like to have something in the doc about that... i.e. either some
examples showing how the COLLATION is impacting the results of the
POSITION() function ; or a statement which confirms that the POSITION()
function doesn't care at all about the COLLATION (+ the advice to
systematically add ...COLLATE "C"... on one of the POSITION()'s arguments in
order to avoid the dumb "nondeterministic collations are not supported"
error, while in fine no collations at all are supported)
Thanks.
Re: Doc says nothing about wether CREATE OR REPLACE VIEW can change the COLLATION of an existing field
On Tue, Feb 15, 2022 at 8:04 AM PG Doc comments form wrote: > For this reason, I'm also filling a bug report, since postgresql's reaction > was not adapted to my request (neither an appropriate error message nor > successful execution of my SQL statement). > The bug report is valid and sufficient. We really don't want to have two threads active discussing the issue. David J.
Re: tutorial problem solution requires that problem is already fixed
On Tue, Feb 15, 2022 at 8:04 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/role-attributes.html > Description: > > I am reporting my roadblock, so it can be better documented in the future. > > I am following this tutorial: > https://www.postgresql.org/docs/14/tutorial-createdb.html > I got the error "createdb: error: connection to server on socket > "/tmp/.s.PGSQL.5432" failed: FATAL: role "joe" does not exist", so I went > to the chapter 22 "https://www.postgresql.org/docs/14/user-manag.html;, > then > "https://www.postgresql.org/docs/14/database-roles.html;. But from there > it > is already an SQL command. How am I supposed to do that if I didn't even > created the database yet? Where should I execute those? > Elsewhere you should have already learned that PostgreSQL solves the bootstrap problem by creating an initial superuser and database during the running of the "initdb" command. You connect as that user to that database to create your second user (and possibly database). Both are named "postgres" by default. Though the documentation mostly concerns itself with compiled-from-source installations; whatever package you installed usually with provide additional information as to how to work with the system immediately after installation. David J.
Doc says nothing about wether CREATE OR REPLACE VIEW can change the COLLATION of an existing field
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/sql-createview.html Description: What the doc says about CREATE OR REPLACE VIEW : "The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the *same data types*), but it may add additional columns to the end of the list. *The calculations giving rise to the output columns may be completely different*." It doesn't say wether the COLLATION is considered being part of the "data types" or not, i.e. it doesn't say wether it's possible to change the COLLATION of an existing field or not. My tests (under Postgres v. 12.4) showed that the COLLATION of an existing field can not be changed : trying to do so, postgres says nothing and seems to accept the SQL statement, but nothing is changed in the database (it silently fails). From the user perspective, I consider such a silent failure the WORST situation possible : I would prefer either 1) to get an error message saying that it's not possible to change the COLLATION of an existing field - OR even better - 2) to actually change the COLLATION of the existing field, as requested. For this reason, I'm also filling a bug report, since postgresql's reaction was not adapted to my request (neither an appropriate error message nor successful execution of my SQL statement).
tutorial problem solution requires that problem is already fixed
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/role-attributes.html Description: I am reporting my roadblock, so it can be better documented in the future. I am following this tutorial: https://www.postgresql.org/docs/14/tutorial-createdb.html I got the error "createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "joe" does not exist", so I went to the chapter 22 "https://www.postgresql.org/docs/14/user-manag.html;, then "https://www.postgresql.org/docs/14/database-roles.html;. But from there it is already an SQL command. How am I supposed to do that if I didn't even created the database yet? Where should I execute those?
