pl/pgsql errors when multi-dimensional arrays are used
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/arrays.html
Description:
I have PostgreSQL 13. Let's declare the type below, then use it in
pl/pgsql:
create type typ1 as (
fld1 int[][]
);
do $$
declare
a typ1;
begin
a.fld1 = '{{121,122,123,124}}'; -- OK
(1)
a.fld1[1] = '{221,222,223,224}';-- fails
(2)
a.fld1[1][1] = 321; -- OK
(3)
a.fld1[1][2] = 322; -- OK
unless line (1) is removed
end;
$$;
In line (2) the plql reports ERROR: invalid input syntax for type integer:
"{221,222,223,224}"
When lines (1) and (2) are removed, psql reports ERROR: array subscript out
of range
Is this expected behavior? Why?
Rafal
Re: pl/pgsql errors when multi-dimensional arrays are used
try:
a.fld1[1] = '[221,222,223,224]';
чт, 29 апр. 2021 г. в 10:35, PG Doc comments form :
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/13/arrays.html
> Description:
>
> I have PostgreSQL 13. Let's declare the type below, then use it in
> pl/pgsql:
>
> create type typ1 as (
> fld1 int[][]
> );
>
> do $$
> declare
>a typ1;
> begin
> a.fld1 = '{{121,122,123,124}}'; -- OK
> (1)
> a.fld1[1] = '{221,222,223,224}';-- fails
> (2)
> a.fld1[1][1] = 321; --
> OK (3)
> a.fld1[1][2] = 322; --
> OK unless line (1) is removed
> end;
> $$;
>
> In line (2) the plql reports ERROR: invalid input syntax for type integer:
> "{221,222,223,224}"
> When lines (1) and (2) are removed, psql reports ERROR: array subscript
> out
> of range
>
> Is this expected behavior? Why?
>
> Rafal
>
Re: pl/pgsql errors when multi-dimensional arrays are used
This also returns ERROR: invalid input syntax for type integer:
"[221,222,223,224]"
On 4/29/2021 9:48 AM, KraSer wrote:
try:
a.fld1[1] = '[221,222,223,224]';
чт, 29 апр. 2021 г. в 10:35, PG Doc comments form
mailto:nore...@postgresql.org>>:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/arrays.html
Description:
I have PostgreSQL 13. Let's declare the type below, then use it in
pl/pgsql:
create type typ1 as (
fld1 int[][]
);
do $$
declare
a typ1;
begin
a.fld1 = '{{121,122,123,124}}'; -- OK
(1)
a.fld1[1] = '{221,222,223,224}'; -- fails
(2)
a.fld1[1][1] = 321; -- OK
(3)
a.fld1[1][2] = 322; -- OK unless line (1)
is removed
end;
$$;
In line (2) the plql reports ERROR: invalid input syntax for type
integer:
"{221,222,223,224}"
When lines (1) and (2) are removed, psql reports ERROR: array
subscript out
of range
Is this expected behavior? Why?
Rafal
Re: pl/pgsql errors when multi-dimensional arrays are used
Hi
čt 29. 4. 2021 v 12:33 odesílatel Rafal Dabrowa
napsal:
> This also returns ERROR: invalid input syntax for type integer:
> "[221,222,223,224]"
>
> On 4/29/2021 9:48 AM, KraSer wrote:
> > try:
> > a.fld1[1] = '[221,222,223,224]';
> >
> > чт, 29 апр. 2021 г. в 10:35, PG Doc comments form
> > mailto:nore...@postgresql.org>>:
> >
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/13/arrays.html
> > Description:
> >
> > I have PostgreSQL 13. Let's declare the type below, then use it in
> > pl/pgsql:
> >
> > create type typ1 as (
> > fld1 int[][]
> > );
> >
> > do $$
> > declare
> >a typ1;
> > begin
> > a.fld1 = '{{121,122,123,124}}'; -- OK
> > (1)
> > a.fld1[1] = '{221,222,223,224}'; -- fails
> > (2)
> > a.fld1[1][1] = 321; -- OK
> >(3)
> > a.fld1[1][2] = 322; -- OK unless line (1)
> > is removed
> > end;
> > $$;
> >
> > In line (2) the plql reports ERROR: invalid input syntax for type
> > integer:
> > "{221,222,223,224}"
> > When lines (1) and (2) are removed, psql reports ERROR: array
> > subscript out
> > of range
> >
> > Is this expected behavior? Why?
> >
> > Rafal
> >
>
Postgres's arrays don't allow any modification that creates some gap in
the array. Next - Postgres's arrays are multidimensional arrays, and these
arrays are not an arrays or arrays.
So your line (1) cannot work.
You can write
a.fld1[1][1:4] := '{221,222,223,224}';
Case (3) fails, because this operation on empty array creates gap on
position 1,1.
you can use an function array_fill
DO
postgres=# do $$
declare a int[];
begin
a := array_fill(null::int, array[2,2]);
a[1][2] := 322;
raise notice 'a=%', a;
end;
$$;
NOTICE: a={{NULL,322},{NULL,NULL}}
Regards
Pavel
Foreign Keys being able to reference same table not spelled out in documentation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/ddl-constraints.html Description: Hello, In Section 5.4.5 of the documentation regarding Foreign Key Constraints it is stated "A foreign key constraint specifies that the values [...] must match the values appearing in some row of another table." But referencing the same table is allowed and necessary for tree-like structures. For comparison, the MariaDB documentation states "The parent and the child table [...] can be the same table." While the SQLite-documentation is not clear on whether this would be allowed or not, it doesn't spell out that both tables need to be different either. MSSQL documentation states "FOREIGN KEY constraints can reference another column in the same table, and is referred to as a self-reference." I always urge my students to use the documentation as reference instead of blindly googling and the task they had was building a tree-like data-structure (specifically a heap, enforcing the heap-property via CHECK) and they were thoroughly confused, that the concept of foreign-keys would extend in this manner, as it was a little bit hidden in the lecture. As they didn't find it to be allowed by documentation, they thought it wouldn't be. Hopefully this can be added somewhere.
Re: pl/pgsql errors when multi-dimensional arrays are used
PG Doc comments form writes:
> I have PostgreSQL 13. Let's declare the type below, then use it in
> pl/pgsql:
> create type typ1 as (
> fld1 int[][]
> );
I think you have a fundamental misunderstanding of how multidimensional
arrays work in Postgres. There's no separate type for 2-D vs 1-D arrays;
that is, the extra pair of brackets you wrote above is just noise.
What matters is what you put into the array at runtime, and the syntax
you use to do it.
> a.fld1 = '{{121,122,123,124}}'; -- OK
> (1)
Fine, you stored a 2-D array into fld1.
> a.fld1[1] = '{221,222,223,224}';-- fails
> (2)
This fails on semantic grounds because a non-slice assignment or fetch
of an int array element must store or retrieve an int. You're trying
to store an array slice, which requires that you use [m:n] subscript
notation. It'd be correct to write either of
a.fld1[1:4] = '{221,222,223,224}';
a.fld1[1:] = '{221,222,223,224}';
which unfortunately plpgsql doesn't support in released versions
(that's fixed for v14 though).
regards, tom lane
Re: Foreign Keys being able to reference same table not spelled out in documentation
PG Doc comments form writes: > In Section 5.4.5 of the documentation regarding Foreign Key Constraints it > is stated > "A foreign key constraint specifies that the values [...] must match the > values appearing in some row of another table." > But referencing the same table is allowed and necessary for tree-like > structures. Fair point, and I notice that the term "self-referential foreign key" appears nowhere in our docs, which seems like an oversight. However, I think changing this first introductory sentence to something like "the same or another table" would be a mistake. It would confuse novices' mental model of what's happening, in service of a relatively seldom-used corner case. I'm inclined to propose adding an example a little further down, as per the attached draft patch. This wouldn't help people who stop reading after the section's first sentence, but we can't cover everything in the first sentence. regards, tom lane diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 7d587b226c..1afd272ff0 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -936,6 +936,30 @@ CREATE TABLE t1 ( in the usual way. + +foreign key +self-referential + + + +Sometimes it is useful for the other table of a +foreign key constraint to be the same table; this is called +a self-referential foreign key. For +example, if you want rows of a table to represent nodes of a tree +structure, you could write + +CREATE TABLE tree ( +node_id integer PRIMARY KEY, +parent_id integer REFERENCES tree, +name text, +... +); + +A top-level node would have NULL parent_id, +but non-NULL parent_id entries would be +constrained to reference valid rows of the table. + + A table can have more than one foreign key constraint. This is used to implement many-to-many relationships between tables. Say
[DOC] pg_stat_replication_slots representation style inconsisitant
Hi When reading the manual doc, I found an inconsistent representation style for the newly added pg_stat_prefetch_recovery view in PG14 at [1]. Same problem seems to be reported by Noriyoshi Shinoda who also proposed a patch to fix the problem at[2]. I tried to fix the problem in a way different than Noriyoshi Shinoda which I think is more consistent with the existing specific view introduction in the same page. Please take the attached patch as your reference. [1] https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-PG-STAT-SUBSCRIPTION [2] https://www.postgresql.org/message-id/TU4PR8401MB1152945E4FD45E99C3F52B94EE4F9%40TU4PR8401MB1152.NAMPRD84.PROD.OUTLOOK.COM Regards, Tang 0001-pg_stat_prefetch_recovery_doc.patch Description: 0001-pg_stat_prefetch_recovery_doc.patch
RE: [DOC] pg_stat_replication_slots representation style inconsisitant
> When reading the manual doc, I found an inconsistent representation style for > the newly added pg_stat_prefetch_recovery view in PG14 at [1]. > Same problem seems to be reported by Noriyoshi Shinoda who also proposed > a patch to fix the problem at[2]. > I tried to fix the problem in a way different than Noriyoshi Shinoda which I > think > is more consistent with the existing specific view introduction in the same > page. > Please take the attached patch as your reference. I noticed one more thing. It seems the column " stats_reset | timestamp with time zone " is not listed in the pg_stat_prefetch_recovery view. Should we add this column too ? Best regards, houzj
RE: [DOC] pg_stat_replication_slots representation style inconsisitant
On Friday, April 30, 2021 12:38 PM, Hou, Zhijie/侯 志杰 wrote >I noticed one more thing. >It seems the column " stats_reset | timestamp with time zone " is not >listed in the pg_stat_prefetch_recovery view. >Should we add this column too ? Indeed, column added. Thanks. Regards, Tang 0001-pg_stat_prefetch_recovery_doc_v2.patch Description: 0001-pg_stat_prefetch_recovery_doc_v2.patch
RE: [DOC] pg_stat_prefetch_recovery representation style inconsisitant
Sorry, wrong title name. I fixed the view representation of "pg_stat_prefetch_recovery". Fix it. Regards, Tang
