RE: Remove AIX Support (was: Re: Relation bulk write facility)

2024-02-29 Thread Phil Florent
Hi,
Historically many public hospitals I work for had IBM Power hardware.
The SMT8 (8 threads/cores) capabilities of Power CPU are useful to lower Oracle 
licence & support cost. We migrate to PostgreSQL and it runs very well on 
Power, especially since the (relatively) recent parallel executions features of 
the RDBMS match very well the CPU capabilities.
We chose to run PostgreSQL on Debian/Power (Little Endian) since ppc64le is an 
official Debian port. No AIX then. Only problem is that we still need to access 
Oracle databases and it can be useful to read directly with oracle_fdw but this 
tool needs an instant client and it's not open source of course. Oracle 
provides a binary but they don't provide patches for Debian/Power Little Endian 
(strange situation...) Just to say that of course we chose Linux for PostgreSQL 
but sometimes things are not so easy... We could have chosen AIX and we still 
have a  about interoperability.
Best regards,
Phil

De : Andres Freund 
Envoyé : jeudi 29 février 2024 10:35
À : Michael Banck 
Cc : Noah Misch ; Thomas Munro ; 
Heikki Linnakangas ; Peter Smith ; 
Robert Haas ; vignesh C ; 
pgsql-hackers ; Melanie Plageman 

Objet : Re: Remove AIX Support (was: Re: Relation bulk write facility)

Hi,

On 2024-02-29 10:24:24 +0100, Michael Banck wrote:
> On Thu, Feb 29, 2024 at 12:57:31AM -0800, Andres Freund wrote:
> > On 2024-02-29 09:13:04 +0100, Michael Banck wrote:
> > > The commit message says there is not a lot of user demand and that might
> > > be right, but contrary to other fringe OSes that got removed like HPPA
> > > or Irix, I believe Postgres on AIX is still used in production and if
> > > so, probably in a mission-critical manner at some old-school
> > > institutions (in fact, one of our customers does just that) and not as a
> > > thought-experiment. It is probably well-known among Postgres hackers
> > > that AIX support is problematic/a burden, but the current users might
> > > not be aware of this.
> >
> > Then these users should have paid somebody to actually do maintenance work 
> > on
> > the AIX support,o it doesn't regularly stand in the way of implementing
> > various things.
>
> Right, absolutely.
>
> But: did we ever tell them to do that? I don't think it's reasonable for
> them to expect to follow -hackers and jump in when somebody grumbles
> about AIX being a burden somewhere deep down a thread...

Well, the thing is that it's commonly going to be deep down some threads that
portability problems cause pain.  This is far from the only time. Just a few
threads:

https://postgr.es/m/CA+TgmoauCAv+p4Z57PqgVgNxsApxKs3Yh9mDLdUDB8fep-s=1...@mail.gmail.com
https://postgr.es/m/CA+hUKGK=DOC+hE-62FKfZy=ybt5ulkrg3zczd-jfykm-ipn...@mail.gmail.com
https://postgr.es/m/20230124165814.2njc7gnvubn2a...@awork3.anarazel.de
https://postgr.es/m/2385119.1696354...@sss.pgh.pa.us
https://postgr.es/m/20221005200710.luvw5evhwf6cl...@awork3.anarazel.de
https://postgr.es/m/20220820204401.vrf5kejih6jofvqb%40awork3.anarazel.de
https://postgr.es/m/E1oWpzF-002EG4-AG%40gemulon.postgresql.org

This is far from all.

The only platform rivalling AIX on the pain-caused metric is windows. And at
least that can be tested via CI (or locally).  We've been relying on the gcc
buildfarm to be able to maintain AIX at all, and that's not a resource that
scales to many users.

Greetings,

Andres Freund




RE: Should we remove vacuum_defer_cleanup_age?

2023-04-24 Thread Phil Florent


Hi,
Not very convenient but if autovacuum is enabled isn't vacuum_defer_cleanup_age 
the way to make extensions like pg_dirtyread more effective for temporal 
queries to quickly correct human DML mistakes without the need of a complete 
restore, even if no standby is in use ? vacuum_defer_cleanup_age+pg_dirtyread 
give PostgreSQL something like "flashback query" in Oracle.
Best regards,
Phil


De : Andres Freund 
Envoyé : dimanche 23 avril 2023 00:47
À : Alvaro Herrera 
Cc : Justin Pryzby ; pgsql-hack...@postgresql.org 
; Amit Kapila 
Objet : Re: Should we remove vacuum_defer_cleanup_age?

Hi,

On 2023-04-13 13:18:38 +0200, Alvaro Herrera wrote:
> On 2023-Apr-11, Andres Freund wrote:
>
> > Updated patch attached. I think we should either apply something like that
> > patch, or at least add a  to the docs.
>
> I gave this patch a look.  The only code change is that
> ComputeXidHorizons() and GetSnapshotData() no longer handle the case
> where vacuum_defer_cleanup_age is different from zero.  It looks good.
> The TransactionIdRetreatSafely() code being removed is pretty weird (I
> spent a good dozen minutes writing a complaint that your rewrite was
> faulty, but it turns out I had misunderstood the function), so I'm glad
> it's being retired.

My rewrite of what? The creation of TransactionIdRetreatSafely() in
be504a3e974?

I'm afraid we'll need TransactionIdRetreatSafely() again, when we convert more
things to 64bit xids (lest they end up with the same bug as fixed by
be504a3e974), so it's perhaps worth thinking about how to make it less
confusing.


> > 
> > -Similarly, 
> > -and  provide protection 
> > against
> > -relevant rows being removed by vacuum, but the former provides no
> > -protection during any time period when the standby is not connected,
> > -and the latter often needs to be set to a high value to provide 
> > adequate
> > -protection.  Replication slots overcome these disadvantages.
> > +Similarly,  on its own, 
> > without
> > +also using a replication slot, provides protection against relevant 
> > rows
> > +being removed by vacuum, but provides no protection during any time 
> > period
> > +when the standby is not connected.  Replication slots overcome these
> > +disadvantages.
>
> I think it made sense to have this paragraph be separate from the
> previous one when it was talking about two separate variables, but now
> that it's just one, it looks a bit isolated.  I would merge it with the
> one above, which is talking about pretty much the same thing, and
> reorder the whole thing approximately like this
>
>
> In lieu of using replication slots, it is possible to prevent the removal
> of old WAL segments using , or by
> storing the segments in an archive using
>  or  linkend="guc-archive-library"/>.
> However, these methods often result in retaining more WAL segments than
> required.
> Similarly,  without
> a replication slot provides protection against relevant rows
> being removed by vacuum, but provides no protection during any time period
> when the standby is not connected.
>
>
> Replication slots overcome these disadvantages by retaining only the 
> number
> of segments known to be needed.
> On the other hand, replication slots can retain so
> many WAL segments that they fill up the space allocated
> for pg_wal;
>  limits the size of WAL files
> retained by replication slots.
>

It seems a bit confusing now, because "by retaining only the number of
segments ..." now also should cover hs_feedback (due to merging), but doesn't.


> Though the "However," looks a poor fit; I would do this:

I agree, I don't like the however.


I think I'll push the version I had. Then we can separately word-smith the
section? Unless somebody protests I'm gonna do that soon.

Greetings,

Andres Freund




RE: Support logical replication of DDLs

2023-04-03 Thread Phil Florent
Hi,
Sorry about the list. Since it was a question about the specifications I 
thought I had to ask it first in the general list. I will reply in the hackers 
list only for new features.

Replicating from orcl to postgres was difficult. You mentionned renaming of 
columns, the ordinal position of a column is reused with a drop/add column in 
orcl and you can wrongly think it is a renaming from an external point of view. 
Only "advantage" with orcl is that you can drop/add columns thousands of times 
if you want, not with postgres.
 From PostgreSQL to PostgreSQL it's now easier of course but difficulty is that 
we have to separate DDL things. The "+" things have to be executed first on the 
replicated db (new tables, new columns, enlargement of columns). The "-" things 
have to be executed first on the source db (dropped tables, dropped columns, 
downsize of columns). DSS and OLTP teams are different, OLTP teams cannot or 
don't want to deal with DSS concerns etc.  If replication is delayed it's not 
so trivial anway to know when you can drop a table on the replicated db for 
example. DSS team has in fact to build a system that detects a posteriori why 
the subscription is KO if something goes wrong. It can also be a human mistake, 
e.g a "create table very_important_table_to_save as select * from 
very_important_table;" and the replication is KO if the _save table is created 
in the published schema.
I had read too fast. I read the proposals and Vignesh suggestion & syntax seem 
very promising. If I understand well an existing "for all tables" / "tables in 
schema" DML publication would have be to altered with
ALTER PUBLICATION 
simple_applicative_schema_replication_that_wont_be_interrupted_for_an_rdbms_reason
 WITH (ddl = 'table:create,alter'); to get rid of the majority of possible 
interruptions.

> Additionally, there could be some additional problems to deal with
> like say if the column list has been specified then we ideally
> shouldn't send those columns even in DDL. For example, if one uses
> Alter Table .. Rename Column and the new column name is not present in
> the published column list then we shouldn't send it.
Perhaps I miss something but the names are not relevant here. The column is 
part of the publication and the corresponding DDL has to be sent, the column is 
not part of the publication and the DDL should not be sent. Dependencies are 
not based on names, it currently works like that with DML publication but also 
with views for example.
Quick test :

bas=# \dRp+

 Publication test_phil

Propriétaire | Toutes les tables | Insertions | Mises à jour | Suppressions | 
Tronque | Via la racine

--+---++--+--+-+---

postgres | f | t  | t| t| t 
  | f

Tables :

"test_phil.t1" (c1, c2)



bas=# alter table test_phil.t1 rename column c2 to c4;

ALTER TABLE



bas=# \dRp+

 Publication test_phil

Propriétaire | Toutes les tables | Insertions | Mises à jour | Suppressions | 
Tronque | Via la racine

--+---++--+--+-+---

postgres | f | t  | t| t| t 
  | f

Tables :

"test_phil.t1" (c1, c4)


"rename column" DDL has to be sent and the new name is not relevant in the 
decision to send it. If "rename column" DDL had concerned a column that is not 
part of the publication you wouldn't have to send the DDL, no matter the new 
name. Drop is not a problem. You cannot drop an existing column that is part of 
a publication without a "cascade". What could be problematic is a "add column" 
DDL and after that the column is added to the publication via "alter 
publication set". Such a case is difficult to deal with I guess. But the 
initial DDL to create a table is also not sent anyway right ?  It could be a 
known limitation.


I usually only test things in beta to report but I will try to have a look 
earlier at this patch since it is very interesting. That and the TDE thing but 
TDE is an external obligation and not a real interest. I obtained a delay but 
hopefully we will have this encryption thing or perhaps we will be obliged to 
go back to the proprietary RDBMS for some needs even if the feature is in fact 
mostly useless...

Best regards,
Phil

De : Amit Kapila 
Envoyé : lundi 3 avril 2023 06:07
À : Phil Florent 
Cc : vignesh C ; houzj.f...@fujitsu.com 
; Ajin Cherian ; 
wangw.f...@fujitsu.com ; Runqi Tian 
; Peter Smith ; Tom Lane 
; li jie ; Dilip Kumar 
; Alvaro Herrera ; Masahiko 
Sawada ; Japin Li ; rajesh 
singarapu ; Zheng Li ; PostgreSQL 
Hac

ERREUR: cache lookup failed for function 0 with PostgreSQL 15 beta 2, no error with PostgreSQL 14.4

2022-08-04 Thread Phil Florent
Hi,

A DSS developer from my company, Julien Roze, reported me an error I cannot 
explained. Is it a new behavior or a bug ?

Original query is much more complicated but here is a simplified test case with 
postgresql 14 and 15 beta 2 on Debian 11, packages from pgdg :

Ver Cluster Port Status OwnerData directory  Log file
14  main5432 online postgres /var/lib/postgresql/14/main 
/var/log/postgresql/postgresql-14-main.log
15  main5433 online postgres /var/lib/postgresql/15/main 
/var/log/postgresql/postgresql-15-main.log

psql -p 5432

select version();
   version  
 
-
 PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by 
gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 ligne)


with fakedata as (
   select 'hello' word
   union all
   select 'world' word
)
select *
from (
   select word, count(*) over (partition by word) nb from fakedata
) t where nb = 1;
 
  word  | nb 
---+
 hello |  1
 world |  1
(2 lignes)


with fakedata as (
   select 'hello' word
   union all
   select 'world' word
)
select *
from (
   select word, count(*) nb from fakedata group by word
) t where nb = 1;
 
  word  | nb 
---+
 hello |  1
 world |  1
(2 lignes)

psql -p 5433

 select version();
  version   


 PostgreSQL 15beta2 (Debian 15~beta2-1.pgdg110+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 ligne)

with fakedata as (
   select 'hello' word
   union all
   select 'world' word
)
select *
from (
   select word, count(*) over (partition by word) nb from fakedata
) t where nb = 1;
ERREUR:  cache lookup failed for function 0

with fakedata as (
   select 'hello' word
   union all
   select 'world' word
)
select *
from (
   select word, count(*) nb from fakedata group by word
) t where nb = 1;
 
 word  | nb 
---+
 hello |  1
 world |  1
(2 lignes)


Best regards,
Phil



Re: Error from the foreign RDBMS on a foreign table I have no privilege on

2022-06-10 Thread Phil Florent
Hi,
Thanks for your explanations.
Test case had no real-world logic anyway. It was just an oversight in a 
one-time use legacy migration script.
Regards,
Phil

From: Laurenz Albe 
Sent: Friday, June 10, 2022 11:17:07 AM
To: Etsuro Fujita 
Cc: Kyotaro Horiguchi ; eu...@eulerto.com 
; philflor...@hotmail.com ; 
pgsql-hackers@lists.postgresql.org 
Subject: Re: Error from the foreign RDBMS on a foreign table I have no 
privilege on

On Fri, 2022-06-10 at 17:17 +0900, Etsuro Fujita wrote:
> > I am not sure if it worth adding to the documentation.  I would never have 
> > thought
> > of the problem if Phil hadn't brought it up.  On the other hand, I was 
> > surprised
> > to learn that permissions aren't checked until the executor kicks in.
> > It makes sense, but some documentation might help others in that situation.
>
> +1 for adding such a document.
>
> > I'll gladly leave the decision to your judgement as a committer.
>
> IIRC, there are no reports about this from the postgres_fdw users, so
> my inclination would be to leave the documentation alone, for now.

I understand that you are for documenting the timing of permission checks,
but not in the postgres_fdw documentation.  However, this is the only occasion
where the user might notice unexpected behavior on account of the timing of
permission checks.  Other than that, I consider this below the threshold for
user-facing documentation.

I'm ok with just doing nothing here, I just wanted it discussed in public.

Yours,
Laurenz Albe


Error from the foreign RDBMS on a foreign table I have no privilege on

2022-06-04 Thread Phil Florent
Hi,
I opened an issue with an attached code on oracle_fdw git page : 
https://github.com/laurenz/oracle_fdw/issues/534
Basically I expected to obtain a "no privilege" error from PostgreSQL when I 
have no read privilege on the postgres foreign table but I obtained an Oracle 
error instead.
Laurenz investigated and closed the issue but he suggested perhaps I should 
post that on the hackers list since it also occurs with postgres-fdw on some 
occasion (I have investigated some more, and postgres_fdw does the same thing 
when you turn on use_remote_estimate.). Hence I do...
[https://opengraph.githubassets.com/e4d1de8890f6f00ee432d365f033677636df1c545e9d4c10ad623c5de5e7553e/laurenz/oracle_fdw/issues/534]
Oracle error on a foreign table I have no privilege on · Issue #534 · 
laurenz/oracle_fdw
Hi, I noticed a behaviour I didnt expect. Not really a bug but I obtained 
an Oracle error instead of a PostgreSQL error with a foreign table I had no 
privilege on. -- superuser prodige31=*...
github.com

Best regards,
Phil


RE: Parallel plans and "union all" subquery

2020-11-23 Thread Phil Florent
Hi Greg,

The implicit conversion was the cause of the non parallel plan, thanks for the 
explanation and the workarounds. It can cause a huge difference in terms of 
performance, I will give the information to our developers.

Regards,

Phil




De : Greg Nancarrow 
Envoyé : lundi 23 novembre 2020 06:04
À : Phil Florent 
Cc : pgsql-hackers@lists.postgresql.org 
Objet : Re: Parallel plans and "union all" subquery

On Sun, Nov 22, 2020 at 11:51 PM Phil Florent  wrote:
>
>
> Hi,
>
>
> I have a question about parallel plans. I also posted it on the general list 
> but perhaps it's a question for hackers. Here is my test case :
>
>
> explain
> select count(*)
> from (select
> n1
> from drop_me
> union all
> values(1)) ua;
>
>
> QUERY PLAN
> 
> Aggregate (cost=2934739.24..2934739.25 rows=1 width=8)
> -> Append (cost=0.00..2059737.83 rows=7113 width=32)
> -> Seq Scan on drop_me (cost=0.00..1009736.12 rows=7112 width=6)
> -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=32)
> -> Result (cost=0.00..0.01 rows=1 width=4)
> JIT:
> Functions: 4
> Options: Inlining true, Optimization true, Expressions true, Deforming true
>
>
> No parallel plan, 2s6
>
>
> I read the documentation but I don't get the reason of the "noparallel" seq 
> scan of drop_me in the last case ?
>

Without debugging this, it looks to me that the UNION type resolution
isn't working as well as it possibly could in this case, for the
generation of a parallel plan. I found that with a minor tweak to your
SQL, either for the table creation or query, it will produce a
parallel plan.

Noting that currently you're creating the drop_me table with a
"numeric" column, you can either:

(1) Change the table creation

FROM:
create unlogged table drop_me as select generate_series(1,7e7) n1;
TO:
create unlogged table drop_me as select generate_series(1,7e7)::int n1;


OR


(2) Change the query

FROM:
explain
select count(*)
from (select
n1
from drop_me
union all
values(1)) ua;

TO:

explain
select count(*)
from (select
n1
from drop_me
union all
values(1::numeric)) ua;


QUERY PLAN

 Finalize Aggregate  (cost=821152.71..821152.72 rows=1 width=8)
   ->  Gather  (cost=821152.50..821152.71 rows=2 width=8)
 Workers Planned: 2
 ->  Partial Aggregate  (cost=820152.50..820152.51 rows=1 width=8)
   ->  Parallel Append  (cost=0.00..747235.71 rows=29166714 width=0)
 ->  Result  (cost=0.00..0.01 rows=1 width=0)
 ->  Parallel Seq Scan on drop_me
(cost=0.00..601402.13 rows=29166713 width=0)
(7 rows)


Regards,
Greg Nancarrow
Fujitsu Australia


Parallel plans and "union all" subquery

2020-11-22 Thread Phil Florent

Hi,


I have a question about parallel plans. I also posted it on the general list 
but perhaps it's a question for hackers. Here is my test case :


select version();
version


--
PostgreSQL 13.1 (Ubuntu 13.1-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by 
gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit


create unlogged table drop_me as select generate_series(1,7e7) n1;
SELECT 7000


explain
select count(*)
from (select
n1
from drop_me
) s;


QUERY PLAN
--
Finalize Aggregate (cost=675319.13..675319.14 rows=1 width=8)
-> Gather (cost=675318.92..675319.13 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=674318.92..674318.93 rows=1 width=8)
-> Parallel Seq Scan on drop_me (cost=0.00..601402.13 rows=29166713 width=0)
JIT:
Functions: 4
Options: Inlining true, Optimization true, Expressions true, Deforming true


Parallel plan, 1s


explain
select count(*)
from (select
n1
from drop_me
union all
select
n1
from drop_me) ua;


QUERY PLAN
--
Finalize Aggregate (cost=1640315.00..1640315.01 rows=1 width=8)
-> Gather (cost=1640314.96..1640314.99 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=1640304.96..1640304.97 rows=1 width=8)
-> Parallel Append (cost=0.00..1494471.40 rows=58333426 width=0)
-> Parallel Seq Scan on drop_me (cost=0.00..601402.13 rows=29166713 width=0)
-> Parallel Seq Scan on drop_me drop_me_1 (cost=0.00..601402.13 rows=29166713 
width=0)
JIT:
Functions: 6
Options: Inlining true, Optimization true, Expressions true, Deforming true


Parallel plan, 2s2


explain
select count(*)
from (select
n1
from drop_me
union all
values(1)) ua;


QUERY PLAN

Aggregate (cost=2934739.24..2934739.25 rows=1 width=8)
-> Append (cost=0.00..2059737.83 rows=7113 width=32)
-> Seq Scan on drop_me (cost=0.00..1009736.12 rows=7112 width=6)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=32)
-> Result (cost=0.00..0.01 rows=1 width=4)
JIT:
Functions: 4
Options: Inlining true, Optimization true, Expressions true, Deforming true


No parallel plan, 2s6


I read the documentation but I don't get the reason of the "noparallel" seq 
scan of drop_me in the last case ?


Best regards,

Phil



Re: Global temporary tables

2020-02-12 Thread Phil Florent

Hi,



I am very interested in this feature that will conform to the SQL standard and 
I read that :



Session 1:

create global temp table gtt(x integer);

insert into gtt values (generate_series(1,10));



Session 2:

insert into gtt values (generate_series(1,20));



Session1:

create index on gtt(x);

explain select * from gtt where x = 1;



Session2:

explain select * from gtt where x = 1;

??? Should we use index here?



My answer is - yes.

Just because:

- Such behavior is compatible with regular tables. So it will not

confuse users and doesn't require some complex explanations.

- It is compatible with Oracle.



There is a confusion. Sadly it does not work like that at all with Oracle. 
Their implementation is buggy in my opinion.

Here is a very simple test case to prove it with the latest version (january 
2020) :



Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production

Version 19.6.0.0.0



-- session 1

create global temporary table gtt(x integer);

Table created.



-- session 2

insert into gtt SELECT level FROM dual CONNECT BY LEVEL <= 10;

10 rows created.



-- session 1

create index igtt on gtt(x);

Index created.



-- session 2

select * from gtt where x = 9;



no rows selected



select /*+ FULL(gtt) */ * from gtt where x = 9;



 X

--

 9



What happened ? The optimizer (planner) knows the new index igtt can be 
efficient via dynamic sampling. Hence, igtt is used at execution time...but it 
is NOT populated. By default I obtained no line. If I force a full scan of the 
table with a hint /*+ FULL */ you can see that I obtain my line 9. Different 
results with different exec plans it's a WRONG RESULT bug, the worst kind of 
bugs.

Please don't consider Oracle as a reference for your implementation. I am 100% 
sure you can implement and document that better than Oracle. E.g index is 
populated and considered only  for transactions that started after the index 
creation or something like that. It would be far better than this misleading 
behaviour.

Regards,

Phil








Télécharger Outlook pour Android


From: Konstantin Knizhnik 
Sent: Monday, February 10, 2020 5:48:29 PM
To: Tomas Vondra ; Philippe BEAUDOIN 

Cc: pgsql-hackers@lists.postgresql.org ; 
Konstantin Knizhnik 
Subject: Re: Global temporary tables


Sorry, small typo in the last patch.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



RE: GROUPING SETS and SQL standard

2019-11-26 Thread Phil Florent
Thank you, it's noticed. Seems Oracle does not like too much "grouping sets". 
We discovered we had more serious "wrong results" bugs with this clause in our 
migration process. Anyway we don't have to maintain a double compatibility and 
soon it won't be a problem anymore.
Regards
Phil


De : Tom Lane 
Envoyé : mardi 26 novembre 2019 01:39
À : Phil Florent 
Cc : Pavel Stehule ; pgsql-hack...@postgresql.org 

Objet : Re: GROUPING SETS and SQL standard

Phil Florent  writes:
> A  of () (called grand total in the Standard) is 
> equivalent to grouping the entire result Table;

Yeah, I believe so.  Grouping by no columns is similar to what happens
if you compute an aggregate with no GROUP BY: the whole table is
taken as one group.  If the table is empty, the group is empty, but
there's still a group --- that's why you get one aggregate output
value, not none, from

regression=# select count(*) from dual where 0 = 1;
 count
---
 0
(1 row)

Thus, in your example, the sub-query should give

regression=# select 1 from dual where 0=1 group by grouping sets(());
 ?column?
--
1
(1 row)

and therefore it's correct that

regression=# select count(*) from (select 1 from dual where 0=1 group by 
grouping sets(())) tmp;
 count
---
 1
(1 row)

AFAICS, Oracle and SQL Server are getting it wrong.

regards, tom lane


RE: GROUPING SETS and SQL standard

2019-11-25 Thread Phil Florent

A  of () (called grand total in the Standard) is 
equivalent to grouping the entire result Table;

If I get it correctly:

select max(dummy) from dual where  0 = 1 group by grouping sets(());

and

select max(dummy) from dual where  0 = 1 ;

should have the same output.

It's the case with PostgreSQL, not with Oracle.
Hence it means it's PostgreSQL which conforms to the standard in this case.

Regards,
Phil


De : Phil Florent 
Envoyé : lundi 25 novembre 2019 22:18
À : Pavel Stehule 
Cc : pgsql-hack...@postgresql.org 
Objet : RE: GROUPING SETS and SQL standard

Hi,
Thank you, as you mentionned it's not really an interesting real life case 
anyway.
Regards,
Phil


De : Pavel Stehule 
Envoyé : lundi 25 novembre 2019 21:23
À : Phil Florent 
Cc : pgsql-hack...@postgresql.org 
Objet : Re: GROUPING SETS and SQL standard



po 25. 11. 2019 v 20:32 odesílatel Phil Florent 
mailto:philflor...@hotmail.com>> napsal:
Hi,

We are still on the process to migrate our applications from proprietary RDBMS 
to PostgreSQL.

Here is a simple query executed on various systems (real query is different but 
this one does not need any data) :


Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0



SQL> select count(*) from (select 1 from dual where 0=1 group by grouping 
sets(())) tmp;



  COUNT(*)

--

 0





select @@version;

GO



---
   
---
   --

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)

Jul 12 2019 17:43:08

Copyright (C) 2017 Microsoft Corporation

Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)



select count(*) from (select 1 as c1 where 0=1 group by grouping sets(())) tmp;

GO



---

  0



(1 rows affected)





select version();

version



PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc 
(Debian 8.3.0-6) 8.3.0, 64-bit







select count(*) from (select 1 from dual where 0=1 group by grouping sets(())) 
tmp;

count

---

 1

(1 ligne)





0 or 1, which behaviour conforms to the SQL standard ? We have a workaround and 
it's just informational.

This example has not too much sense - I am not sure if these corner cases are 
described by ANSI SQL standards.

If I add aggregate query to subquery - using grouping sets without aggregation 
function is strange, then Postgres result looks more correct

postgres=# select 1, count(*) from dual  group by grouping sets(());
┌──┬───┐
│ ?column? │ count │
╞══╪═══╡
│1 │ 1 │
└──┴───┘
(1 row)

postgres=# select 1, count(*) from dual where false group by grouping sets(());
┌──┬───┐
│ ?column? │ count │
╞══╪═══╡
│1 │ 0 │
└──┴───┘
(1 row)

SELECT count(*) from this should be one in both cases.

I am not sure, if standard describe using grouping sets without any aggregation 
function

Pavel


Regards,


Phil



RE: GROUPING SETS and SQL standard

2019-11-25 Thread Phil Florent
Hi,
Thank you, as you mentionned it's not really an interesting real life case 
anyway.
Regards,
Phil


De : Pavel Stehule 
Envoyé : lundi 25 novembre 2019 21:23
À : Phil Florent 
Cc : pgsql-hack...@postgresql.org 
Objet : Re: GROUPING SETS and SQL standard



po 25. 11. 2019 v 20:32 odesílatel Phil Florent 
mailto:philflor...@hotmail.com>> napsal:
Hi,

We are still on the process to migrate our applications from proprietary RDBMS 
to PostgreSQL.

Here is a simple query executed on various systems (real query is different but 
this one does not need any data) :


Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0



SQL> select count(*) from (select 1 from dual where 0=1 group by grouping 
sets(())) tmp;



  COUNT(*)

--

 0





select @@version;

GO



---
   
---
   --

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)

Jul 12 2019 17:43:08

Copyright (C) 2017 Microsoft Corporation

Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)



select count(*) from (select 1 as c1 where 0=1 group by grouping sets(())) tmp;

GO



---

  0



(1 rows affected)





select version();

version



PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc 
(Debian 8.3.0-6) 8.3.0, 64-bit







select count(*) from (select 1 from dual where 0=1 group by grouping sets(())) 
tmp;

count

---

 1

(1 ligne)





0 or 1, which behaviour conforms to the SQL standard ? We have a workaround and 
it's just informational.

This example has not too much sense - I am not sure if these corner cases are 
described by ANSI SQL standards.

If I add aggregate query to subquery - using grouping sets without aggregation 
function is strange, then Postgres result looks more correct

postgres=# select 1, count(*) from dual  group by grouping sets(());
┌──┬───┐
│ ?column? │ count │
╞══╪═══╡
│1 │ 1 │
└──┴───┘
(1 row)

postgres=# select 1, count(*) from dual where false group by grouping sets(());
┌──┬───┐
│ ?column? │ count │
╞══╪═══╡
│1 │ 0 │
└──┴───┘
(1 row)

SELECT count(*) from this should be one in both cases.

I am not sure, if standard describe using grouping sets without any aggregation 
function

Pavel


Regards,


Phil



GROUPING SETS and SQL standard

2019-11-25 Thread Phil Florent
Hi,

We are still on the process to migrate our applications from proprietary RDBMS 
to PostgreSQL.

Here is a simple query executed on various systems (real query is different but 
this one does not need any data) :


Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0



SQL> select count(*) from (select 1 from dual where 0=1 group by grouping 
sets(())) tmp;



  COUNT(*)

--

 0





select @@version;

GO



---
   
---
   --

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)

Jul 12 2019 17:43:08

Copyright (C) 2017 Microsoft Corporation

Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)



select count(*) from (select 1 as c1 where 0=1 group by grouping sets(())) tmp;

GO



---

  0



(1 rows affected)





select version();

version



PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc 
(Debian 8.3.0-6) 8.3.0, 64-bit







select count(*) from (select 1 from dual where 0=1 group by grouping sets(())) 
tmp;

count

---

 1

(1 ligne)





0 or 1, which behaviour conforms to the SQL standard ? We have a workaround and 
it's just informational.


Regards,


Phil



RE: Internal error XX000 with enable_partition_pruning=on, pg 11 beta1 on Debian

2018-11-03 Thread Phil Florent
Hi,
Thanks for your work, our prototype runs OK. PostgreSQL 11 and its now fully 
functional partitioning feature is our validated choice to replace a well-known 
proprietary RDBMS in 100+ public hospitals for our dss application.
Best regards
Phil


De : Amit Langote 
Envoyé : jeudi 9 août 2018 06:35
À : Tom Lane
Cc : David Rowley; Rushabh Lathia; Alvaro Herrera; Robert Haas; Phil Florent; 
PostgreSQL Hackers
Objet : Re: Internal error XX000 with enable_partition_pruning=on, pg 11 beta1 
on Debian

On 2018/08/09 13:00, Tom Lane wrote:
> Amit Langote  writes:
>> One reason why we should adapt such a test case is that, in the future, we
>> may arrange for make_partitionedrel_pruneinfo(), whose code we just fixed,
>> to not be called if we know that run-time pruning is not needed.  It seems
>> that that's true for the test added by the commit, that is, it doesn't
>> need run-time pruning.
>
> Not following your argument here.  Isn't make_partition_pruneinfo
> precisely what is in charge of figuring out whether run-time pruning
> is possible?

With the current coding, yes, it is...

> (See my point in the other thread about Jaime's assertion crash,
> that no run-time pruning actually would be possible for that query.
> But we got to the assertion failure anyway.)

The first time I'd seen that make_partition_pruneinfo *always* gets called
from create_append_plan if rel->baserestrictinfo is non-NIL, I had
wondered whether we couldn't avoid doing it for the cases for which we'll
end up throwing away all that work anyway.  But looking at the code now,
it may be a bit hard -- analyze_partkey_exprs(), which determines whether
we'll need any execution-time pruning, could not be called any sooner.

So, okay, I have to admit that my quoted argument isn't that strong.

Thanks,
Amit



RE: [Proposal] Add accumulated statistics for wait event

2018-10-29 Thread Phil Florent
Hi,

Is DBA really able to solve bottlenecks with sampling?

What I would like to say is that if we have information on the number of wait 
events and the wait time(like other DB), we can investigate more easily.

Yes you will be able to solve bottlenecks with sampling. In interactive mode, a 
1s interval is probably too large. I use 0s1 - 0s01 with my tool and it is 
normally OK. In batch mode I use 1s=>10s. If you want to visualize the results 
it's easy to use a dedicated tool and bottlenecks will clearly appear .
Since grafana is now able to connect directly to a postgresql source, I use it 
to display the information collected from pg_stat_activity and psutil ( e.g 
https://pgphil.ovh/traqueur_dashboard_02.php page is written in french but 
panels are in english)

Other DB have accumulated statistics but you can notice that sampling is also 
their most modern method.
E.g Oracle DB : 20 years ago you already had tools like "utlbstat/utlestat" . 
Then you had "statspack". Those tools were based on accumulated statistics and 
the reports were based on differences between 2 points. It was useful to solve 
major problems but it was limited and not precise enough in many cases.

The preferred feature to identify bottlenecks in the Oracle world is now ASH 
(active session history). It can help with major problems, specific problems 
AND it can identify short blockages.
Too bad it is licensed as an option of their Enterprise Edition but similar 
tools exist and they are also based on sampling of the activity.

With the "official" ASH, sampling and archiving are done internally and you 
have a circular memory zone dedicated to the feature. Hence the overhead is 
lower but that's all.

The most advanced interactive tool is called "snapper" and it is also based on 
sampling.

Best regards
Phil



De : Yotsunaga, Naoki 
Envoyé : lundi 29 octobre 2018 02:20
À : 'Phil Florent'; 'Michael Paquier'
Cc : 'Tomas Vondra'; 'pgsql-hackers@lists.postgresql.org'
Objet : RE: [Proposal] Add accumulated statistics for wait event


On Thu, Oct 4, 2018 at 8:22 PM, Yotsunaga Naoki wrote:



Hi, I understood and thought of your statistic comment once again. In the case 
of sampling, is there enough statistic to investigate?

In the case of a long SQL, I think that it is possible to obtain a sufficient 
sampling number.



However, in the case of about 1 minute of SQL, only 60 samples can be obtained 
at most.

#Because legard’s comment.

https://www.postgresql.org/message-id/1539158356795-0.post%40n3.nabble.com



Does this sampling number of 60 give information that I really want?

Perhaps it is not to miss the real problem part?

---

Naoki, Yotsunaga.


RE: [Proposal] Add accumulated statistics for wait event

2018-10-04 Thread Phil Florent
Hi,

It's the same logic with any polling system. An integration calculation using 
monte-carlo method with only a few points won't be accurate enough and can even 
be completely wrong etc.
Polling is OK to troubleshoot a problem on the fly but 2 points are not enough. 
A few seconds are needed to obtain good enough data, e.g 5-10 seconds of 
polling with a 0.1=>0.01s interval between 2 queries of the activity.
Polling a few seconds while the user is waiting is normally enough to say if a 
significant part of the waits are on the database. It's very important to know 
that. With 1 hour of accumulated statistics, a DBA will always see something to 
fix. But if the user waits 10 seconds on a particular screen and 1 second is 
spent on the database it often won't directly help.
Polling gives great information with postgreSQL 10 but it was already useful to 
catch top queries etc. in older versions.
I always check if activity is adequately reported by my tool using known cases. 
I want to be sure it will report adequately things in real-world 
troubleshooting sessions. Sometimes there are bugs in my tool, once there was 
an issue with postgres (pgstat_report_activty() was not called by workers in 
parallel index creation)

Best regards
Phil

De : Michael Paquier 
Envoyé : jeudi 4 octobre 2018 12:58
À : Phil Florent
Cc : Yotsunaga, Naoki; Tomas Vondra; pgsql-hackers@lists.postgresql.org
Objet : Re: [Proposal] Add accumulated statistics for wait event

On Thu, Oct 04, 2018 at 09:32:37AM +0000, Phil Florent wrote:
> I am a DB beginner, so please tell me. It says that you can find
> events that are bottlenecks in sampling, but as you saw above, you can
> not find events shorter than the sampling interval, right?

Yes, which is why it would be as simple as making the interval shorter,
still not too short so as it bloats the amount of information fetched
which needs to be stored and afterwards (perhaps) treated for analysis.
This gets rather close to signal processing.  A simple image is for
example, assuming that event A happens 100 times in an interval of 1s,
and event B only once in the same interval of 1s, then if the snapshot
interval is only 1s, then in the worst case A would be treated an equal
of B, which would be wrong.
--
Michael


RE: [Proposal] Add accumulated statistics for wait event

2018-10-04 Thread Phil Florent
Hi,
I am a DB beginner, so please tell me. It says that you can find events that 
are bottlenecks in sampling, but as you saw above, you can not find events 
shorter than the sampling interval, right?

If an event occurs frequently and if it is reported in pg_stat_activity, you 
will catch it again and again while sampling, no matter it duration.
Hence you just need to

  *   Sample the sessions and consider the active ones. You need to know if 
they are waiting (PostgreSQL now provides detailed wait events) or if they are 
on the CPU
  *   Optionally collect information on the system context at the time of 
sampling (CPU, memory...), it can be provided by many tools like psutil python 
library for example

If the client application itself provides information it's even more 
interesting. With something like 
program/module/action/client_info/sofar/totalwork in application_name you are 
able to focus directly on different kind of activity. It can give you 
information like  "I/O waits are meaningful for my batch activity but not for 
my OLTP activity, if my goal is to improve response time for end users I have 
to consider that."

Best regards
Phil


De : Yotsunaga, Naoki 
Envoyé : jeudi 4 octobre 2018 10:31
À : 'Michael Paquier'; Phil Florent
Cc : Tomas Vondra; pgsql-hackers@lists.postgresql.org
Objet : RE: [Proposal] Add accumulated statistics for wait event

On Thu, July 26, 2018 at 1:25 AM, Michael Paquier wrote:
> Even if you have spiky workloads, sampling may miss those, but even with 
> adding counters for each event
> you would need to query the table holding the counters at an insane frequency 
> to be able to perhaps get
> something out of it as you need to do sampling of the counters as well to 
> extract deltas.

Hi, I was wondering why PostgreSQL did not have the number of wait events and 
wait time that other databases such as Oracle had as a function, and when I was 
looking for related threads, I got to this thread.

I am a DB beginner, so please tell me. It says that you can find events that 
are bottlenecks in sampling, but as you saw above, you can not find events 
shorter than the sampling interval, right?
If this short event has occurred quite a number of times and it was a 
considerable amount of time in total, can you solve this problem with sampling?
# I have asked, but I could not understand much of the discussion above and I 
do not know if such a case can exist.
Also, I think that it can be solved by higher the sampling frequency, but the 
load will be high, right? I think this method is not very practical.

Moreover, I think that it is not implemented due to the reason that sampling is 
good as described above and because it affects performance.
How about switching the function on / off and implementing with default off?
Do you care about performance degradation during bottleneck investigation?
When investigating the bottleneck, I think that it is better to find the cause 
even at the expense of performance.
# If you can detect with high frequency sampling, I think that it depends on 
whether the sampling or the function(the number of wait events and wait time) 
is high load.

Since I am a DB beginner, I think that it is saying strange things.
I am glad if you tell me.

-
Naoki Yotsunaga



RE: [Proposal] Add accumulated statistics for wait event

2018-07-28 Thread Phil Florent
Hi,

I agree with that. PostgreSQL 10 is really great, tuning tools based on 
sampling of pg_stat_activity became accurate without any modification.

Best regards

Phil



De : Michael Paquier 
Envoyé : jeudi 26 juillet 2018 03:24
À : Phil Florent
Cc : Tomas Vondra; pgsql-hackers@lists.postgresql.org
Objet : Re: [Proposal] Add accumulated statistics for wait event

On Tue, Jul 24, 2018 at 04:23:03PM +, Phil Florent wrote:
> It loses non meaningful details and it's in fact a good point. In this
> example, sampling will definitely find the cause and won't cost
> resources.

The higher the sampling frequency, the more details you get, with the
most load on the instance.  So if you are able to take an infinity of
samples, where registering multiple times the same event for the same
backend also matters because its overall weight gets higher and it shows
up higher in profiles, then you would be able converge to the set of
results that this patch adds.  Sampling method, especially its
frequency, is something controlled by the client and not the server.
Approaches like the one proposed here push the load on the server-side,
unconditionally, for *all* backends, and this has its cost.

Even if you have spiky workloads, sampling may miss those, but even with
adding counters for each event you would need to query the table holding
the counters at an insane frequency to be able to perhaps get something
out of it as you need to do sampling of the counters as well to extract
deltas.

As Tomas has mentioned up-thread, sampling is light-weight, as-is the
current design for wait events.  Even if it is not perfect because it
cannot give exact numbers, it would find bottlenecks in really most
cases, and that's what matters.  If not, increasing the sampling
frequency makes things easier to detect as well.  What would be the
point of taking only one sample every checkpoint for example?

There may be a benefit in having counters, I don't know the answer to
that, though the point would be to make sure that there is a specific
set of workloads where it makes sense, still my gut feeling is that
sampling would be able to detect those anyway.

(I am not a computer scientist by default but a physicist, think fluid
dynamics and turbulence, and I had my load of random events and signal
analysis as well.  All that is just statistics with attempts to approach
reality, where sampling is a life-saver over exactitude of
measurements.)

Adding hooks is not acceptable to me either, those have a cost, and it
is not clear what's the benefit we can get into putting hooks in such a
place for cases other than sampling and counters...
--
Michael


RE: [Proposal] Add accumulated statistics for wait event

2018-07-24 Thread Phil Florent

Hi,


> Some case, sampling of events can not find the cause of issue. It lose detail 
> data.
> For example, some throughput issue occur(ex : disk io), but each wait point
> occurs only a few milliseconds.


It loses non meaningful details and it's in fact a good point. In this example, 
sampling will definitely find the cause and won't cost resources.

Being as precise as possible to define a wait event is very useful but knowing 
precisely the duration of each event is less useful in terms of tuning.


Example of sampling + group by/order by percentage of activity :


./t -d 5 -o "application_name, wait_event_type" -o "application_name, 
wait_event, wait_event_type"
traqueur 2.05.00 - performance tool for PostgreSQL 9.3 => 11
INFORMATION, no connection parameters provided, connecting to dedicated 
database ...
INFORMATION, connected to dedicated database traqueur
INFORMATION, PostgreSQL version : 11
INFORMATION, sql preparation ...
INFORMATION, sql execution ...
 busy_pc | distinct_exe | application_name | wait_event_type
-+--+--+-
 206 | 8 / 103  | mperf|
  62 | 2 / 31   | mperf| LWLock
  20 | 3 / 10   | mperf| IO
  12 | 1 / 6| mperf| Client
(4 rows)

 busy_pc | distinct_exe | application_name |  wait_event   | 
wait_event_type
-+--+--+---+-
 206 | 8 / 103  | mperf|   |
  62 | 2 / 31   | mperf| WALWriteLock  | LWLock
  14 | 1 / 7| mperf| DataFileImmediateSync | IO
  12 | 1 / 6| mperf| ClientRead| Client
   2 | 1 / 1| mperf| DataFileWrite | IO
   2 | 1 / 1| mperf| DataFileRead  | IO
   2 | 1 / 1| mperf| WALInitWrite  | IO


No need to know the exact duration of each event to identify the 
bottleneck(s)...


Best regards

Phil




De : Tomas Vondra 
Envoyé : mardi 24 juillet 2018 17:45
À : pgsql-hackers@lists.postgresql.org
Objet : Re: [Proposal] Add accumulated statistics for wait event



On 07/24/2018 12:06 PM, MyungKyu LIM wrote:
>   2018-07-23 16:53 (GMT+9), Michael Paquier wrote:
>> On Mon, Jul 23, 2018 at 04:04:42PM +0900, 임명규 wrote:
>>> This proposal is about recording additional statistics of wait events.
>
>> I have comments about your patch.  First, I don't think that you need to
>> count precisely the number of wait events triggered as usually when it
>> comes to analyzing a workload's bottleneck what counts is a periodic
>> *sampling* of events, patterns which can be fetched already from
>> pg_stat_activity and stored say in a different place.
>
> Thanks for your feedback.
>
> This proposal is not about *sampling*.
> Accumulated statistics of wait events information is useful for solving
> issue. It can measure accurate data.
>
> Some case, sampling of events can not find the cause of issue. It lose detail 
> data.
> For example, some throughput issue occur(ex : disk io), but each wait point
> occurs only a few milliseconds.
> In this case, it is highly likely that will not find the cause.
>

I think it's highly likely that it will find the cause. The idea of
sampling is that while you don't measure the timing directly, you can
infer it from the frequency of the wait events in the samples. So if you
see the backend reports a particular wait event in 75% of samples, it
probably spent 75% time waiting on it.

I'm not saying sampling is perfect and it certainly is less convenient
than what you propose.

regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



RE: Re: [Proposal] Add accumulated statistics for wait event

2018-07-24 Thread Phil Florent
Hi,

I am skeptical about accumulated statistics.

pg_stat_activity now gives necessary information about wait events. It can be 
easily be used with a polling system that sleeps most of the time to limit the 
overhead. Measuring the duration of individual wait events is not necessary to 
know the repartition of the charge.

You can aggregate the results of the polling by application, query, wait events 
or whatever you want.

I wrote a script for that that can be used interactively or in batch mode to 
produce reports but many solutions exist .

Best regards

Phil





De : MyungKyu LIM 
Envoyé : mardi 24 juillet 2018 12:10
À : Alexander Korotkov; pgsql-hack...@postgresql.org
Cc : Woosung Sohn; DoHyung HONG
Objet : RE: Re: [Proposal] Add accumulated statistics for wait event

> On Mon, Jul 23, 2018 at 10:53 AM Michael Paquier  wrote:
>> What's the performance penalty?  I am pretty sure that this is
>> measurable as wait events are stored for a backend for each I/O
>> operation as well, and you are calling a C routine within an inlined
>> function which is designed to be light-weight, doing only a four-byte
>> atomic operation.

> Yes, the question is overhead of measuring durations of individual wait 
> events.  It has been proposed before, and there been heated debates about 
> that (see threads [1-3]).  It doesn't seem
> to be a conclusion about this feature.  The thing to be said for sure:
> performance penalty heavily depends on OS/hardware/workload.  In some cases 
> overhead is negligible, but in other cases it appears to be huge.

Thanks for good information.
I agree. Performance penalty is exist.
But wait stats are demandable and useful. In some cases, it is worth 
sacrificing performance and using it.

So, what do you think about developing as extension? I have another concept 
proposal.
2. This feature can be implemented as extension if some hooks were provided in 
following functions,
 - pgstat_report_wait_start
 - pgstat_report_wait_end
This feature can be turned on/off by on-line config when necessary.

Best regards,
MyungKyu, Lim




RE: Internal error XX000 with enable_partition_pruning=on, pg 11 beta1 on Debian

2018-07-16 Thread Phil Florent
I get it. Thank you for this precision.

Regards

Phil


De : David Rowley 
Envoyé : lundi 16 juillet 2018 07:48
À : Phil Florent
Cc : Tom Lane; Robert Haas; Amit Langote; PostgreSQL Hackers
Objet : Re: Internal error XX000 with enable_partition_pruning=on, pg 11 beta1 
on Debian

On 16 July 2018 at 16:56, Phil Florent 
mailto:philflor...@hotmail.com>> wrote:

I should post that in the general section but I am confused by the sentence "A 
parent partition is always going to have a lower relid than its children"

It's a little confusing since RelOptInfo has a relid field and so does 
RangeTblEntry. They both have completely different meanings.  RelOptInfo's 
relid is a number starting at 1 and continues in a gapless sequence increasing 
by 1 with each RelOptInfo.  These relids are completely internal to the server 
and don't appear in the system catalog tables.  RangeTblEntry's relid is what's 
in pg_class.oid.

I was talking about RelOptInfo's relid.

Using relids starting at 1 is quite convenient for allowing direct array 
lookups in various data structures in the planner. However it's also required 
to uniquely identify a relation as a single table may appear many times in a 
query, so trying to identify them by their oid could be ambiguous.  Also, some 
RTEKinds don't have storage, e.g a VALUES() clause.

--
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


RE: Internal error XX000 with enable_partition_pruning=on, pg 11 beta1 on Debian

2018-07-15 Thread Phil Florent
Hi,


I should post that in the general section but I am confused by the sentence "A 
parent partition is always going to have a lower relid than its children"


In the code, relid has many meanings and not only "oid of a class" as in 
https://www.postgresql.org/message-id/20910.734...@sss.pgh.pa.us ?
PostgreSQL: Re: relid and 
relname
www.postgresql.org
Michael Fuhr  writes: > On Thu, Mar 24, 2005 at 
11:01:23PM -0300, Edson Vilhena de Carvalho wrote: >> Can anyone tell me what 
is a relid, a relname and


In fact, I want to be sure I can say to the developers they will always be able 
to create tables and partitions in any order :

create table child1(c1 int, c2 int);

create table midparent1(c1 int, c2 int) partition by list(c2);

alter table midparent1 attach partition child1 for values in (1);

create table child2 partition of midparent1 for values in (2);

create table topparent(c1 int, c2 int) partition by list(c1);

alter table topparent attach partition midparent1 for values in (1);

select relname, relkind, oid from pg_class where relname in ('topparent', 
'midparent1', 'child1', 'child2') order by oid asc;

  relname   | relkind |  oid
+-+
 child1 | r   | 123989
 midparent1 | p   | 123992
 child2 | r   | 123995
 topparent  | p   | 123998
(4 lignes)


Regards
Phil



Internal error XX000 with enable_partition_pruning=on, pg 11 beta1 on Debian

2018-06-08 Thread Phil Florent
Hi,


I obtained an XX000 error testing my DSS application with PostgreSQL 11 beta 1.

Here is a simplified version of my test, no data in the tables :


-- 11
select version();
version

---

PostgreSQL 11beta1 (Debian 11~beta1-2.pgdg+1) on x86_64-pc-linux-gnu, compiled 
by gcc (Debian 7.3.0-19) 7.3.0, 64-bit
(1 ligne)

-- connected superuser -- postgres
create user a password 'a';
create schema a authorization a;
create user b password 'b';
create schema b authorization b;
create user c password 'c';
create schema c authorization c;
create user z password 'z';
create schema z authorization z;

-- connected a
create table t1(k1 timestamp, c1 int);
create view v as select k1, c1 from t1;
grant usage on schema a to z;
grant select on all tables in schema a to z;

-- connected b
create table t2(k1 timestamp, c1 int) partition by range(k1);
create table t2_2016 partition of t2 for values from ('2016-01-01') to 
('2017-01-01');
create table t2_2017 partition of t2 for values from ('2017-01-01') to 
('2018-01-01');
create table t2_2018 partition of t2 for values from ('2018-01-01') to 
('2019-01-01');
create view v as select k1, c1 from t2;
grant select on all tables in schema b to z;
grant usage on schema b to z;


-- connected c
create table t3(k1 timestamp, c1 int) partition by range(k1);
create table t3_2016 partition of t3 for values from ('2016-01-01') to 
('2017-01-01');
create table t3_2017 partition of t3 for values from ('2017-01-01') to 
('2018-01-01');
create table t3_2018 partition of t3 for values from ('2018-01-01') to 
('2019-01-01');
create view v as select k1, c1 from t3;
grant select on all tables in schema c to z;
grant usage on schema c to z;

-- connected z
create view v as
select k1, c1 from
(select * from a.v
UNION ALL
select * from b.v
UNION ALL
select * from c.v) vabc ;

explain analyze select * from v where v.k1 > date '2017-01-01';
ERREUR:  XX000: did not find all requested child rels in append_rel_list
EMPLACEMENT : find_appinfos_by_relids, prepunion.c : 2643

set enable_partition_pruning=off;
SET

explain analyze select * from v where v.k1 > date '2017-01-01';
QUERY PLAN
---
Append  (cost=0.00..272.30 rows=4760 width=12) (actual time=0.217..0.217 rows=0 
loops=1)
   ->  Seq Scan on t1  (cost=0.00..35.50 rows=680 width=12) (actual 
time=0.020..0.020 rows=0 loops=1)
 Filter: (k1 > '2017-01-01'::date)
   ->  Seq Scan on t2_2016  (cost=0.00..35.50 rows=680 width=12) (actual 
time=0.035..0.035 rows=0 loops=1)
 Filter: (k1 > '2017-01-01'::date)
   ->  Seq Scan on t2_2017  (cost=0.00..35.50 rows=680 width=12) (actual 
time=0.016..0.016 rows=0 loops=1)
 Filter: (k1 > '2017-01-01'::date)
   ->  Seq Scan on t2_2018  (cost=0.00..35.50 rows=680 width=12) (actual 
time=0.015..0.015 rows=0 loops=1)
 Filter: (k1 > '2017-01-01'::date)
   ->  Seq Scan on t3_2016  (cost=0.00..35.50 rows=680 width=12) (actual 
time=0.040..0.040 rows=0 loops=1)
 Filter: (k1 > '2017-01-01'::date)
   ->  Seq Scan on t3_2017  (cost=0.00..35.50 rows=680 width=12) (actual 
time=0.016..0.016 rows=0 loops=1)
 Filter: (k1 > '2017-01-01'::date)
   ->  Seq Scan on t3_2018  (cost=0.00..35.50 rows=680 width=12) (actual 
time=0.016..0.016 rows=0 loops=1)
 Filter: (k1 > '2017-01-01'::date)
Planning Time: 0.639 ms
Execution Time: 0.400 ms

set enable_partition_pruning=on;
SET

explain analyze select * from v where v.k1 > date '2017-01-01';
ERREUR:  XX000: did not find all requested child rels in append_rel_list
EMPLACEMENT : find_appinfos_by_relids, prepunion.c : 2643



-- 10
select version();
  version


PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by 
gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 ligne)


-- connected superuser -- postgres
create user a password 'a';
create schema a authorization a;

create user b password 'b';
create schema b authorization b;

create user c password 'c';
create schema c authorization c;

create user z password 'z';
create schema z authorization z;

-- connected a
create table t1(k1 timestamp, c1 int);
create view v as select k1, c1 from t1;
grant usage on schema a to z;
grant select on all tables in schema a to z;

-- connected b
create table t2(k1 timestamp, c1 int) partition by range(k1);
create table t2_2016 partition of t2 for values from ('2016-01-01') to 
('2017-01-01');
create table t2_2017 partition of t2 for values from ('2017-01-01') to 
('2018-01-01');
create table 

RE: pgstat_report_activity() and parallel CREATE INDEX (was: Parallel index creation & pg_stat_activity)

2018-03-29 Thread Phil Florent
Thanks, ran the original test and it works great after the patch.

Phil



De : Robert Haas <robertmh...@gmail.com>
Envoyé : jeudi 22 mars 2018 18:17
À : Peter Geoghegan
Cc : Andres Freund; Phil Florent; PostgreSQL Hackers
Objet : Re: pgstat_report_activity() and parallel CREATE INDEX (was: Parallel 
index creation & pg_stat_activity)

On Wed, Mar 7, 2018 at 8:53 PM, Peter Geoghegan <p...@bowt.ie> wrote:
> On Thu, Mar 1, 2018 at 2:47 PM, Peter Geoghegan <p...@bowt.ie> wrote:
>> No. Just an oversight. Looks like _bt_parallel_build_main() should
>> call pgstat_report_activity(), just like ParallelQueryMain().
>>
>> I'll come up with a patch soon.
>
> Attached patch has parallel CREATE INDEX propagate debug_query_string
> to workers. Workers go on to use this string as their own
> debug_query_string, as well as registering it using
> pgstat_report_activity(). Parallel CREATE INDEX pg_stat_activity
> entries will have a query text, too, which addresses Phil's complaint.

Committed.  Thanks for the patch.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company