Re: Is this a buggy behavior?

On 2024-03-24 11:23:22 -0700, David G. Johnston wrote: > On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer wrote: > It doesn't. Your statement > > > CREATE TABLE test1 > > ( > > c1 numeric   NULL , > > c2 varchar(36)  NOT NULL , > > CONSTRAINT test1_PK PRIMARY KEY (c1,c2)

Re: Empty materialized view

Am 24.03.2024 um 21:50 schrieb Adrian Klaver: On 3/24/24 13:36, Thiemo Kellner wrote: It does depending on the order of viewing. Namely if you viewed the 'old' empty MV in the outside session before you dropped/created the 'new' MV and committed the changes. Something like the viewing

Re: Not able to purge partition

On Sun, 24 Mar 2024 at 20:29, Laurenz Albe wrote: > On Sun, 2024-03-24 at 00:37 +0530, veem v wrote: > > > Instead, use foreign keys between the partitions. > > > > I am struggling to understand how to maintain those partitions then? > > As because we were planning to use pg_partman for creating

Re: Empty materialized view

On 3/24/24 13:11, Thiemo Kellner wrote: Am 24.03.2024 um 20:56 schrieb Erik Wienhold: Maybe you executed REFRESH in a transaction but did not commit it? While I can see the point for the refresh (but there actually is a commit), I cannot hold it valid for a create with data when the mv

Re: Empty materialized view

On 2024-03-24 19:12 +0100, Thiemo Kellner wrote: > I have created a materialized view with "with data". And I refreshed it with > "with data". The query of the mv returns records when executed outside the > mv. I would appreciate help with respect to what I miss that my mv is empty. Maybe you

Re: Empty materialized view

On 3/24/24 11:12, Thiemo Kellner wrote: Hi I have created a materialized view with "with data". And I refreshed it with "with data". The query of the mv returns records when executed outside the mv. I would appreciate help with respect to what I miss that my mv is empty. You might want to

Re: Empty materialized view

Am 24.03.2024 um 21:30 schrieb Adrian Klaver: On 3/24/24 13:11, Thiemo Kellner wrote: Confirmed in the same session that created it or in a different session? Different session, not knowing what that mattered. Excerpt of the installation protocol: … ## tenth level ## Set materialised view

Re: Empty materialized view

On 3/24/24 14:27, Thiemo Kellner wrote: Feeling quite dumb now. But then, there neither is data visible in the install session. insert data into TASK_DEPENDENCY⠒V INSERT 0 34 The above says the data was inserted. But not into the MV but into TASK_DEPENDENCY⠒V. Where and when was the

Re: Empty materialized view

On 3/24/24 13:36, Thiemo Kellner wrote: Am 24.03.2024 um 21:30 schrieb Adrian Klaver: On 3/24/24 13:11, Thiemo Kellner wrote: Confirmed in the same session that created it or in a different session? Different session, not knowing what that mattered It does depending on the order of viewing.

Re: Empty materialized view

Am 24.03.2024 um 22:06 schrieb Adrian Klaver: The view session is on auto commit. (It's sole purpose to query stuff and not to have explicitly terminate transactions do to syntax errors and so on.) Autocommit will only affect actions in that session, it will not make the other sessions

Re: Empty materialized view

Am 24.03.2024 um 20:56 schrieb Erik Wienhold: Maybe you executed REFRESH in a transaction but did not commit it? While I can see the point for the refresh (but there actually is a commit), I cannot hold it valid for a create with data when the mv actually is created (confirmed by being

Re: Empty materialized view

On 3/24/24 13:58, Thiemo Kellner wrote: Am 24.03.2024 um 21:50 schrieb Adrian Klaver: On 3/24/24 13:36, Thiemo Kellner wrote: It does depending on the order of viewing. Namely if you viewed the 'old' empty MV in the outside session before you dropped/created the 'new' MV and committed the

Re: Statistics information.

On Sat, Mar 23, 2024 at 6:51 PM Ron Johnson wrote: > > On Sat, Mar 23, 2024 at 12:33 AM arun chirappurath > wrote: >> >> 1. Last run duration >> 2. Average time for execution. >> 3. Filter statistics for a specific function(stored procedure) >> 4. Filter for specific texts. >> 5 top queries >>

Re: Not able to purge partition

On Sun, Mar 24, 2024 at 12:38 AM veem v wrote: > On Sat, 23 Mar 2024 at 23:08, Laurenz Albe > wrote: > >> On Sat, 2024-03-23 at 22:41 +0530, veem v wrote: >> > 1)As we see having foreign key defined is making the detach partition >> run >> > for minutes(in our case 5-10minutes for 60 million

Re: Is this a buggy behavior?

Am 24.03.2024 um 15:54 schrieb Erik Wienhold: This is required by the SQL standard: columns of a primary key must be NOT NULL. Postgres automatically adds the missing NOT NULL constraints when defining a primary key. You can verify that with \d test1 in psql. To me, this behaviour, while

Re: Not able to purge partition

On Sun, 2024-03-24 at 00:37 +0530, veem v wrote: > > Instead, use foreign keys between the partitions. > > I am struggling to understand how to maintain those partitions then? > As because we were planning to use pg_partman for creating and dropping > partitions automatically without much hassle.

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:17 schrieb Tom Lane: To do that, we'd have to remember that you'd said NULL, which we don't: the word is just discarded as a noise clause. Considering that this usage of NULL isn't even permitted by the SQL standard, that seems like a bit too much work. If I understood

Re: Is this a buggy behavior?

Am 24.03.24 um 16:28 schrieb Thiemo Kellner: Am 24.03.2024 um 16:17 schrieb Tom Lane: To do that, we'd have to remember that you'd said NULL, which we don't: the word is just discarded as a noise clause. Considering that this usage of NULL isn't even permitted by the SQL standard, that

Re: Is this a buggy behavior?

I wrote: > Do you come from sqlite? That allows NULL in primary key columns > without an explicit NOT NULL constraint. And that's also possible in Postgres with UNIQUE constraints if you're looking for that behavior. -- Erik

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:35 schrieb sud: On Sun, Mar 24, 2024 at 8:47 PM Tom Lane > wrote: Do you specifically mean that 'null'  keyword is just not making any sense here in postgres. But even if that is the case , i tried inserting nothing (hoping "nothing" is "null"

Re: Is this a buggy behavior?

Am 24.03.24 um 16:41 schrieb Thiemo Kellner: Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer: the null-able constraint addition to a column is pointless because by default all columns are nullable. definition as a primary key adds the not null constraint. While this is certainly true,

Re: Is this a buggy behavior?

On 2024-03-24 16:28 +0100, Thiemo Kellner wrote: > Am 24.03.2024 um 16:17 schrieb Tom Lane: > > > To do that, we'd have to remember that you'd said NULL, which we > > don't: the word is just discarded as a noise clause. Considering > > that this usage of NULL isn't even permitted by the SQL

Re: Is this a buggy behavior?

On 3/24/24 08:28, Thiemo Kellner wrote: Am 24.03.2024 um 16:17 schrieb Tom Lane: To do that, we'd have to remember that you'd said NULL, which we don't: the word is just discarded as a noise clause.  Considering that this usage of NULL isn't even permitted by the SQL standard, that seems like

Re: Is this a buggy behavior?

On 3/24/24 08:28, Thiemo Kellner wrote: > Sure, my example has lots more side effect than silently do the right thing. I think the point is that it's not really doing anything "silently." You are asking for a PRIMARY KEY constraint on a column, and it's giving it to you. One of the effects

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:44 schrieb Andreas Kretschmer: postgres=# create table bla(i int null primary key); CREATE TABLE postgres=# \d bla     Table "public.bla"  Column |  Type   | Collation | Nullable | Default +-+---+--+-  i  | integer

Re: Is this a buggy behavior?

Am 24.03.2024 um 17:15 schrieb Christophe Pettus: I think the point is that it's not really doing anything "silently." You are asking for a PRIMARY KEY constraint on a column, and it's giving it to you. One of the effects (not even really a side-effect) of that request is that the column

Re: Is this a buggy behavior?

> On Mar 24, 2024, at 09:32, Thiemo Kellner wrote: > Am 24.03.2024 um 17:15 schrieb Christophe Pettus: >> I think the point is that it's not really doing anything "silently." You >> are asking for a PRIMARY KEY constraint on a column, and it's giving it to >> you. One of the effects (not

Is this a buggy behavior?

Hello All, Create a table and composite primary key. But to my surprise it allowed me to have the composite primary key created even if one of the columns was defined as nullable. But then inserting the NULL into that column erroring out at the first record itself , stating "not null constraint"

Re: Is this a buggy behavior?

On 2024-03-24 15:25 +0100, sud wrote: > Create a table and composite primary key. But to my surprise it allowed me > to have the composite primary key created even if one of the columns was > defined as nullable. But then inserting the NULL into that column erroring > out at the first record

Re: Is this a buggy behavior?

Thiemo Kellner writes: > Am 24.03.2024 um 15:54 schrieb Erik Wienhold: >> This is required by the SQL standard: columns of a primary key must be >> NOT NULL. Postgres automatically adds the missing NOT NULL constraints >> when defining a primary key. You can verify that with \d test1 in psql.

Re: Is this a buggy behavior?

On Sun, Mar 24, 2024 at 8:47 PM Tom Lane wrote: > Thiemo Kellner writes: > > Am 24.03.2024 um 15:54 schrieb Erik Wienhold: > >> This is required by the SQL standard: columns of a primary key must be > >> NOT NULL. Postgres automatically adds the missing NOT NULL constraints > >> when defining

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer: the null-able constraint addition to a column is pointless because by default all columns are nullable. definition as a primary key adds the not null constraint. While this is certainly true, I do not see why the information that a not

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:39 schrieb Erik Wienhold: And that's also possible in Postgres with UNIQUE constraints if you're looking for that behavior. Sort of the distinction between PK and UQ.

Re: Is this a buggy behavior?

Am 24.03.2024 um 17:43 schrieb Christophe Pettus: The situation is much more like the customer saying, "I understand that the standard paint for this car is red, but I wish it painted blue instead." Not in the least. Declaring the column to be NULL is explicitly requesting the car be blue.

Empty materialized view

Hi I have created a materialized view with "with data". And I refreshed it with "with data". The query of the mv returns records when executed outside the mv. I would appreciate help with respect to what I miss that my mv is empty. You might want to have a look at the code attached. Kind

Re: Is this a buggy behavior?

On 2024-03-24 21:05:04 +0530, sud wrote: > Do you specifically mean that 'null'  keyword is just not making any sense > here > in postgres. But even if that is the case , i tried inserting nothing (hoping > "nothing" is "null" in true sense), This is a strange hope. > but then too it failed in

Re: Is this a buggy behavior?

On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer wrote: > > It doesn't. Your statement > > > CREATE TABLE test1 > > ( > > c1 numeric NULL , > > c2 varchar(36) NOT NULL , > > CONSTRAINT test1_PK PRIMARY KEY (c1,c2) > > ) ; > > creates the table with both columns being defined as NOT NULL: > >