Re: Cannot find hstore operator

[Paul van der Linden]

Thanks, works perfectly!

On Sun, Jan 23, 2022 at 4:22 PM Tom Lane  wrote:

> Paul van der Linden  writes:
> > Thanks for the clarification, but giving up performance is a no-go for
> us.
> > Also I have my concerns about shemaqualifying each and every use of the
> ->
> > operator, there are really a lot of them in my functions and it would
> > severely impact readability.
> > Are these the only 2 solutions possible?
>
> As of v14 you could use SQL-style function definitions, so that the
> operator is parsed at function definition time instead of runtime.
>
> regards, tom lane
>

Re: Cannot find hstore operator

[Dominique Devienne]

Hi. In 
https://www.mail-archive.com/pgsql-general@lists.postgresql.org/msg29321.html
I asked:

> On Tue, Jan 11, 2022 at 5:30 PM David G. Johnston 
>  wrote:
> > On Tuesday, January 11, 2022, Dominique Devienne > 
> > wrote:
> >> This means the template-schema name is part of the DDL for the schema,
> >> and a clone would need to use its own search-path, not the original.
> > This is your major limitation.  You are required to create new objects
> > from code and cannot leverage any kind of copy of existing objects.
>
> But how to avoid that limitation?
>
> Triggers in a schema should functions correctly, whether or not client
> sessions set the search_path, or use fully qualified object names.
> I was actually surprised that functions from the schema itself (where the
> trigger is defined), do "not bind more tightly" to the dot (.) schema,
> the "owner" schema of the trigger, compared to functions elsewhere.
>
> Perhaps there's something I'm missing around trigger and name resolution?

But didn't any answer at the time.

But Tom's answer to Paul's question seems to be related to my original
question, no?

On Sun, Jan 23, 2022 at 4:22 PM Tom Lane  wrote:
> Paul van der Linden  writes:
> > Thanks for the clarification, but giving up performance is a no-go for us.
> > Also I have my concerns about shemaqualifying each and every use of the ->
> > operator, there are really a lot of them in my functions and it would
> > severely impact readability. Are these the only 2 solutions possible?
>
> As of v14 you could use SQL-style function definitions, so that the
> operator is parsed at function definition time instead of runtime.

After re-reading
https://www.postgresql.org/docs/14/sql-createfunction.html in light of
Tom's answer,
does that mean that our `SET search_path TO {0}, 'pg_temp'`
workaround, in the trigger below,
to not depend on the search_path at runtime:
```
CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf()
 RETURNS trigger
 LANGUAGE plpgsql
 SET search_path TO owner_schema_of_fn_tg_tab, 'pg_temp'
AS $function$
BEGIN
 UPDATE AttributeInput
 SET AppCreateDate = NEW.CreateDate
 WHERE Guid = NEW.Guid;
 RETURN NEW;
END;
$function$
```
can be re-written as below?
```
CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf()
 RETURNS trigger
 LANGUAGE sql
BEGIN ATOMIC
 UPDATE AttributeInput
 SET AppCreateDate = NEW.CreateDate
 WHERE Guid = NEW.Guid;
 RETURN NEW;
END;
```
As long as owner_schema_of_fn_tg_tab is first in the search_path at
function-creation time?
Or does the v14-specific trick Tom mentioned is not available to
trigger-returning functions?
I'm kinda afraid that RETUR NEW; is specific to plpgsql...

I'm still on v12, so cannot test v14 yet. We planned to move to v14,
for lz4 and built-in uuid-creation function,
but if we could get rid of the `SET search_path` workaround in our
trigger functions, that would be even more motivation.

I'd really like my DDL statements to NOT contain schema-specific names,
to ensure proper name resolution independent of the search_path and
completely "self-contained" in the schema itself.
Is there any way to achieve that, beside our current `SET search_path`
workaround?

Thanks, --DD

Re: Cannot find hstore operator

[Ganesh Korde]

On Mon, 24 Jan 2022, 3:22 pm Dominique Devienne, 
wrote:

> Hi. In
> https://www.mail-archive.com/pgsql-general@lists.postgresql.org/msg29321.html
> I asked:
>
> > On Tue, Jan 11, 2022 at 5:30 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
> > > On Tuesday, January 11, 2022, Dominique Devienne >
> wrote:
> > >> This means the template-schema name is part of the DDL for the schema,
> > >> and a clone would need to use its own search-path, not the original.
> > > This is your major limitation.  You are required to create new objects
> > > from code and cannot leverage any kind of copy of existing objects.
> >
> > But how to avoid that limitation?
> >
> > Triggers in a schema should functions correctly, whether or not client
> > sessions set the search_path, or use fully qualified object names.
> > I was actually surprised that functions from the schema itself (where the
> > trigger is defined), do "not bind more tightly" to the dot (.) schema,
> > the "owner" schema of the trigger, compared to functions elsewhere.
> >
> > Perhaps there's something I'm missing around trigger and name resolution?
>
> But didn't any answer at the time.
>
> But Tom's answer to Paul's question seems to be related to my original
> question, no?
>
> On Sun, Jan 23, 2022 at 4:22 PM Tom Lane  wrote:
> > Paul van der Linden  writes:
> > > Thanks for the clarification, but giving up performance is a no-go for
> us.
> > > Also I have my concerns about shemaqualifying each and every use of
> the ->
> > > operator, there are really a lot of them in my functions and it would
> > > severely impact readability. Are these the only 2 solutions possible?
> >
> > As of v14 you could use SQL-style function definitions, so that the
> > operator is parsed at function definition time instead of runtime.
>
> After re-reading
> https://www.postgresql.org/docs/14/sql-createfunction.html in light of
> Tom's answer,
> does that mean that our `SET search_path TO {0}, 'pg_temp'`
> workaround, in the trigger below,
> to not depend on the search_path at runtime:
> ```
> CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf()
>  RETURNS trigger
>  LANGUAGE plpgsql
>  SET search_path TO owner_schema_of_fn_tg_tab, 'pg_temp'
> AS $function$
> BEGIN
>  UPDATE AttributeInput
>  SET AppCreateDate = NEW.CreateDate
>  WHERE Guid = NEW.Guid;
>  RETURN NEW;
> END;
> $function$
> ```
> can be re-written as below?
> ```
> CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf()
>  RETURNS trigger
>  LANGUAGE sql
> BEGIN ATOMIC
>  UPDATE AttributeInput
>  SET AppCreateDate = NEW.CreateDate
>  WHERE Guid = NEW.Guid;
>  RETURN NEW;
> END;
> ```
> As long as owner_schema_of_fn_tg_tab is first in the search_path at
> function-creation time?
> Or does the v14-specific trick Tom mentioned is not available to
> trigger-returning functions?
> I'm kinda afraid that RETUR NEW; is specific to plpgsql...
>
> I'm still on v12, so cannot test v14 yet. We planned to move to v14,
> for lz4 and built-in uuid-creation function,
> but if we could get rid of the `SET search_path` workaround in our
> trigger functions, that would be even more motivation.
>
> I'd really like my DDL statements to NOT contain schema-specific names,
> to ensure proper name resolution independent of the search_path and
> completely "self-contained" in the schema itself.
> Is there any way to achieve that, beside our current `SET search_path`
> workaround?
>
> Thanks, --DD
>

This might help.

Alter user  SET search_path TO myschema,public;

No need to set search_path every time.

Regards,
Ganesh Korde.

Re: Cannot find hstore operator

[Dominique Devienne]

On Mon, Jan 24, 2022 at 11:19 AM Ganesh Korde  wrote:
> On Mon, 24 Jan 2022, 3:22 pm Dominique Devienne,  wrote:
>> Is there any way to achieve that, beside our current `SET search_path` 
>> workaround?

> This might help.
> Alter user  SET search_path TO myschema,public;
> No need to set search_path every time.

Hi. Not really, no, I'm afraid.

I'm in charge and control the app's schemas, not the LOGIN USERs using
those schemas.
I.e. my triggers shouldn't have to rely on the session's search_path
at all, and nor how that search_path is set.
Also, the schema(s) to access are dynamic, and some clients don't set
a search_path at all.
My triggers shouldn't stop working when there's no search_path (i.e.
only pg_catalog and pg_temp are implicitly resolved).

Proposed German Translation of Code of Conduct Policy

[Umair Shahid]

The PostgreSQL Community Code of Conduct Committee has received a draft of
the German translation of the Code of Conduct Policy updated August 18,
2020 for review.

The English version of the Policy is at:
https://www.postgresql.org/about/policies/coc/

The translation was contributed by:

   - Gunnar Bluth
   - Daniel Westermann
   - Julian Markwort
   - Andreas Scherbaum

The translation was reviewed by:

   - Stefanie Janine Stölting
   - Andreas Kretschmer

The proposed translation is attached both as plain text and a PDF file to
this message.

If you have any comments or suggestions for the proposed translation,
please bring them to our attention no later than 5:00 PM UTC on Monday,
January 31, 2022. What time is that in my time zone?



Thank you!

Umair Shahid
Chair
PostgreSQL Community Code of Conduct Committee
Einführung

Das PostgreSQL-Projekt ist stolz auf die Qualität unseres Codes und unserer 
Arbeit sowie auf die technischen und professionellen Leistungen unserer 
Community. Wir erwarten von jedem Mitglied der Community, dass es sich 
professionell verhält, mit Höflichkeit und im gemeinsamen Interesse mit Respekt 
gegenüber allen Benutzern und Entwicklern handelt.


Zu diesem Zweck haben wir für die Interaktion mit und innerhalb der 
Gemeinschaft, die Beteiligung an der Arbeit des Projekts und die Gemeinschaft 
insgesamt diesen Verhaltenskodex erstellt. Dieser Kodex soll alle Interaktionen 
zwischen Community-Mitgliedern abdecken, unabhängig davon, ob sie innerhalb 
oder außerhalb der postgresql.org-Infrastruktur stattfinden, solange kein 
anderer Verhaltenskodex (wie z.B. der Verhaltenskodex einer Konferenz) Vorrang 
hat.



Inklusivität und angemessenes Verhalten

Das PostgreSQL-Projekt steht jedem offen, der Interesse an der Arbeit mit 
PostgreSQL hat, unabhängig von seinem Erfahrungsstand mit der Software oder mit 
Technologie im Allgemeinen. Wir fördern die Entwicklung und Beiträge aller 
Benutzer, unabhängig ihres Hintergrunds.


Wir fördern eine durchdachte und konstruktive Diskussion über die Software und 
diese Community, ihren aktuellen Stand und mögliche Entwicklungsrichtungen. Der 
Schwerpunkt unserer Diskussionen sollte der Code und die damit verbundene 
Technologie, Community-Projekte sowie die Infrastruktur sein.


Persönliche Angriffe und negative Kommentare über persönliche Eigenschaften 
sind inakzeptabel und nicht gestattet. Beispiele für persönliche Eigenschaften 
sind unter anderem Alter, Herkunft oder Abstammung, Religion, Geschlecht und 
sexuelle Orientierung.


Weitere Verhaltensweisen, die ebenfalls gegen diesen Verhaltenskodex verstoßen, 
sind unter anderem die Androhung von Gewalt gegen eine Einzelperson oder eine 
Gruppe, Androhung von Berufs-, Gemeinschafts- oder Projektsabotage, jegliche 
Form von unerwünschter sexueller Annäherung, Verhaltensweisen, die das 
PostgreSQL-Projekt in Verruf bringen können, sowie die Weigerung, 
unangemessenes Verhalten einzustellen, wenn dazu aufgefordert wird.



Vergeltung

Es ist ebenfalls ausdrücklich verboten, gegenüber einer Person, die eine 
Beschwerde nach diesem Verhaltenskodex einreicht oder bei der Untersuchung 
einer solchen Beschwerde hilft, Vergeltungsmaßnahmen zu verüben. 
Vergeltungsmaßnahmen können unter anderem folgende Formen annehmen:


* weitere persönliche Angriffe (öffentlich oder privat);
* Handlungen, die den beruflichen Status einer Person und/oder ihren Status am 
Arbeitsplatz, bei Kunden oder in der Community untergraben;
* Handlungen, die die Privatsphäre, die körperliche Unversehrtheit, das 
Wohlbefinden, das Zuhause und/oder die Familie der betroffenen Person bedrohen.
Vergeltungsmaßnahmen werden genauso behandelt wie jeder andere Verstoß gegen 
diesen Verhaltenskodex.



Code of Conduct Committee

Das Core-Team ernennt ein Komitee für den Verhaltenskodex, welches alle 
Beschwerden entgegennimmt und untersucht, sowie eine(n) Vorsitzende(n) für 
dieses Komitee. Jedes Mitglied der Community kann sich freiwillig als Mitglied 
des Komitees melden, mit Ausnahme der Mitglieder des Core-Teams. Da das 
Core-Team das Komitee beaufsichtigt, werden Mitglieder des Core-Teams nicht im 
Komitee tätig sein, um Interessenkonflikte zu vermeiden. Die Mitgliederliste 
des Komitees wird jederzeit öffentlich zugänglich sein und kann hier eingesehen 
werden.


Die Mitgliedschaft im Komitee wird auf jährlicher Basis erneuert. Das Core-Team 
oder die/der Vorsitzende des Komitees werden den Anfang und das Ende des 
jährlichen Mitgliederauswahlverfahrens über die üblichen Kommunikationskanäle 
der Community bekannt geben.


Jedes Community-Mitglied, welches im Komitee mitarbeiten möchte, wird einen 
ersten Fragebogen zur Überprüfung durch das Core-Team und das aktuelle Komitee 
ausfüllen. Die aktuellen Komiteemitglieder prüfen die Kandidaten und führen bei 
Bedarf Vorstellungsgespräche. Das derzeitige Komitee wird Empfehlungen abgeben 
und das C

Re: Cannot find hstore operator

[David G. Johnston]

On Monday, January 24, 2022, Dominique Devienne  wrote:

>
> After re-reading
> https://www.postgresql.org/docs/14/sql-createfunction.html in light of
> Tom's answer,
> does that mean that our `SET search_path TO {0}, 'pg_temp'`
> workaround, in the trigger below,
> to not depend on the search_path at runtime:
>
>
The general trigger documentation is kind enough to point out that the sql
language cannot be used to write trigger functions.

David J.

tstzrange on large table gives poor estimate of expected rows

[Tom Dearman]

Hi,

We have a fairly big table (22 million rows) which has a start and end
timestamp with time zone and other columns.  Some of the columns plus
start timestamp make a primary key.  The end timestamp is exactly one
day ahead of the start timestamp for each row and there are
approximately 1 rows per day, so each day there will be about
1 inserts (and many updates) where the start is -mm-dd
00:00:00.0 and the end column is one day ahead of that.  We have
created a tstzrange on start and end column and then do a query which
looks for exactly one day range:

explain (analyze, buffers) select tstzrange_test.interval_start_date
as interval_start_date
from tstzrange_test tstzrange_test
where tstzrange('2021-07-20 00:00:00.0', '2021-07-21 00:00:00.0',
'[]') @> tstzrange(tstzrange_test.interval_start_date,
tstzrange_test.interval_end_date, '[]');

In the real query this result is then grouped other columns then
joined on another table which has similar results but by the hour
instead of by the day.

The query always underestimates the number of rows:

Index Scan using tstzrange_test_ix01 on tstzrange_test
(cost=0.41..8.43 rows=1 width=8) (actual time=0.347..8.889 rows=1
loops=1)
   Index Cond: (tstzrange(interval_start_date, interval_end_date,
'[]'::text) <@ '["2021-07-20 00:00:00+00","2021-07-21
00:00:00+00"]'::tstzrange)
   Buffers: shared hit=815
 Planning Time: 0.120 ms
 Execution Time: 9.591 ms
(5 rows)

and when this is aggregated and then joined on another table it leads
to a slow query.  If I change the query so the start date is 12 hours
earlier (ie so no extra results would be found as the previous day
start date would be 24 hours earlier) it gives a better estimate and
uses a different plan:

explain (analyze, buffers) select tstzrange_test.interval_start_date
as interval_start_date
from tstzrange_test tstzrange_test
where tstzrange('2021-07-19 12:00:00.0', '2021-07-21 00:00:00.0',
'[]') @> tstzrange(tstzrange_test.interval_start_date,
tstzrange_test.interval_end_date, '[]');

 Bitmap Heap Scan on tstzrange_test  (cost=199.89..11672.03 rows=4577
width=8) (actual time=6.880..7.844 rows=1 loops=1)
   Recheck Cond: ('["2021-07-19 12:00:00+00","2021-07-21
00:00:00+00"]'::tstzrange @> tstzrange(interval_start_date,
interval_end_date, '[]'::text))
   Heap Blocks: exact=65
   Buffers: shared hit=330
   ->  Bitmap Index Scan on tstzrange_test_ix01  (cost=0.00..198.74
rows=4577 width=0) (actual time=6.866..6.866 rows=1 loops=1)
 Index Cond: (tstzrange(interval_start_date,
interval_end_date, '[]'::text) <@ '["2021-07-19
12:00:00+00","2021-07-21 00:00:00+00"]'::tstzrange)
 Buffers: shared hit=265
 Planning Time: 0.157 ms
 Execution Time: 8.186 ms
(9 rows)

I have tried increasing the states to 1 on each of the timestamp
columns and also setting up a statistics object to say that start and
end are related (dependencies).

This is how I created and  populated the table:

CREATE TABLE tstzrange_test (
interval_start_datetimestamp with time zone,
interval_end_date timestamp with time zone,
user_registration_id bigint
);

insert into tstzrange_test (interval_start_date, interval_end_date,
user_registration_id) select '2021-01-01 00:00:00.0'::timestamp with
time zone + ((psid)/1 || ' day')::interval, '2021-01-02
00:00:00.0'::timestamp with time zone + ((psid)/1 || '
day')::interval, floor(random() * (500) + 1)::int from
generate_series(1,360) as s(psid);

CREATE INDEX tstzrange_test_ix01 ON tstzrange_test USING gist
(tstzrange(interval_start_date, interval_end_date, '[]'::text));


Any help on how I can make the planner estimate better would be much
appreciated.

Thanks.

Re: Cannot find hstore operator

[Dominique Devienne]

On Mon, Jan 24, 2022 at 3:48 PM David G. Johnston
 wrote:
> On Monday, January 24, 2022, Dominique Devienne  wrote:
>>
>> After re-reading
>> https://www.postgresql.org/docs/14/sql-createfunction.html in light of
>> Tom's answer,
>> does that mean that our `SET search_path TO {0}, 'pg_temp'`
>> workaround, in the trigger below,
>> to not depend on the search_path at runtime:
>>
>
> The general trigger documentation is kind enough to point out that the sql 
> language cannot be used to write trigger functions.

OK, failr enough...

But what about:
> I'd really like my DDL statements to NOT contain schema-specific names,
> to ensure proper name resolution independent of the search_path and
> completely "self-contained" in the schema itself.
> Is there any way to achieve that, beside our current `SET search_path` 
> workaround?

Could I please get a definitive answer about this?

David, in the "clone-schema" thread, you kind of implied I shouldn't
have a set search_path in the triggers,
thus making my DDL schema-specific, but then when I ask about ways to
avoid thus, and have "static" resolution
of names in those trigger functions, I'm not getting alternatives.

Am I the only one to think that a session w/o a seach_path, which
fully qualifies table names,
should behaves exactly the same way than another session that has a
search_path and does not fully qualify table names?
Because that's the only reason I added a set search_path to our
trigger functions. The alternative being to fully-qualify
all object references in those trigger functions, making the DDL even
more "schema-specific". it feels like a catch-22...

I'm not trying to be difficult here... I'm trying to understand, FWIW. --DD

PS: Does INVOKER vs DEFINER affect name resolution? Does that apply to
trigger functions?

Re: Cannot find hstore operator

[Adrian Klaver]

On 1/24/22 08:27, Dominique Devienne wrote:

On Mon, Jan 24, 2022 at 3:48 PM David G. Johnston
 wrote:

On Monday, January 24, 2022, Dominique Devienne  wrote:


After re-reading
https://www.postgresql.org/docs/14/sql-createfunction.html in light of
Tom's answer,
does that mean that our `SET search_path TO {0}, 'pg_temp'`
workaround, in the trigger below,
to not depend on the search_path at runtime:



The general trigger documentation is kind enough to point out that the sql 
language cannot be used to write trigger functions.


OK, failr enough...

But what about:

I'd really like my DDL statements to NOT contain schema-specific names,
to ensure proper name resolution independent of the search_path and
completely "self-contained" in the schema itself.
Is there any way to achieve that, beside our current `SET search_path` 
workaround?


Could I please get a definitive answer about this?

David, in the "clone-schema" thread, you kind of implied I shouldn't
have a set search_path in the triggers,
thus making my DDL schema-specific, but then when I ask about ways to


If I am following your code and objects are schema specific so there is 
no way to avoid this.



avoid thus, and have "static" resolution
of names in those trigger functions, I'm not getting alternatives.

Am I the only one to think that a session w/o a seach_path, which
fully qualifies table names,
should behaves exactly the same way than another session that has a
search_path and does not fully qualify table names?


Objects end up being found in a schema, either you specify that schema 
when using the object or the server walks the search_path to find the 
first schema where an object with the name exists.




Because that's the only reason I added a set search_path to our
trigger functions. The alternative being to fully-qualify
all object references in those trigger functions, making the DDL even
more "schema-specific". it feels like a catch-22...

I'm not trying to be difficult here... I'm trying to understand, FWIW. --DD

PS: Does INVOKER vs DEFINER affect name resolution? Does that apply to
trigger functions?





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: tstzrange on large table gives poor estimate of expected rows

[Michael Lewis]

If interval_end_date is always 1 day ahead, why store it at all?

Dependencies on a custom stats object wouldn't do anything I don't think
because they are offset. They are 100% correlated, but not in a way that
any of the existing stat types capture as far as I can figure.

Re: Cannot find hstore operator

[David G. Johnston]

On Mon, Jan 24, 2022 at 9:25 AM Dominique Devienne 
wrote:

> On Mon, Jan 24, 2022 at 3:48 PM David G. Johnston
>  wrote:
> > On Monday, January 24, 2022, Dominique Devienne 
> wrote:
> >>
> >> After re-reading
> >> https://www.postgresql.org/docs/14/sql-createfunction.html in light of
> >> Tom's answer,
> >> does that mean that our `SET search_path TO {0}, 'pg_temp'`
> >> workaround, in the trigger below,
> >> to not depend on the search_path at runtime:
> >>
> >
> > The general trigger documentation is kind enough to point out that the
> sql language cannot be used to write trigger functions.
>
> OK, failr enough...
>
> But what about:
> > I'd really like my DDL statements to NOT contain schema-specific names,
> > to ensure proper name resolution independent of the search_path and
> > completely "self-contained" in the schema itself.
> > Is there any way to achieve that, beside our current `SET search_path`
> workaround?
>
> Could I please get a definitive answer about this?
>

There is no way to achieve that.

Objects do not know what schema they are in; and the search_path that is
present when the database initiates the SQL transaction itself (e.g.,
pg_dump, create index) provides only pg_catalog.

In short, search_path is a convenience for interactive users and external
applications.  Never rely upon it including specific schemas - or any
schema at all (besides pg_catalog).  Only if you set the search_path
yourself can the code that you write depend upon the search_path.


> David, in the "clone-schema" thread, you kind of implied I shouldn't
> have a set search_path in the triggers,
> thus making my DDL schema-specific, but then when I ask about ways to
> avoid thus, and have "static" resolution
> of names in those trigger functions, I'm not getting alternatives.
>

I rambled a bit in that email...but I did say "Sometimes limitations cannot
be avoided...".  I then proceeded to show you an alternative...way of
thinking of the problem that would work with this limitation.


> Am I the only one to think that a session w/o a seach_path, which
> fully qualifies table names,
> should behaves exactly the same way than another session that has a
> search_path and does not fully qualify table names?
>

All sessions have a search_path...but if you schema-qualify everything
(except what is in pg_catalog) that doesn't really matter.  But if the
schema-qualified name is "my_custom_schema" then, no, omitting the
schema-qualification and relying on search_path means you will get the
identical result when my_custom_schema is in the search_path but will get
an error when it is not.  Both alternatives still include a search_path.


Because that's the only reason I added a set search_path to our
> trigger functions. The alternative being to fully-qualify
> all object references in those trigger functions, making the DDL even
> more "schema-specific". it feels like a catch-22...
>

Attaching a set search_path and schema-qualifying all object references are
equally "schema-specific".  It isn't a catch-22, its two options for
interacting with a system that is designed to be schema-specific.

That is a lot of theory and generalities but I hope it helps clear things
up.

David J.

PS: Does INVOKER vs DEFINER affect name resolution?


Permissions and search_path resolution are independent.

Big variance in execution times of simple queries

[Hannes Erven]

Hi community,


I'm looking at a "SELECT * FROM pg_stat_statements" output and am 
puzzled by the huge differences between min/max_exec_time even for 
simple queries.


The most extreme example is probably the statement used by the 
application's connection health check:


SELECT 1
min=0.001, mean=0.00386, max=36.812


Other statements with huge variances include:

SET application_name=$1
min=0.002, mean=0.005, max=9.177

SELECT * FROM table WHERE id=$1  (where ID is the primary key column; 
table has 0.5M rows and is frequently vacuum analyzed)

min=0.010, mean=0.260, max=12338.665


According to the system's monitoring, there is no pressure on any 
resource (cpu/mem/io). It's 13.5-2pgdg20.04+1 on Ubuntu 20.4; the VM has 
12 cpus/16GB memory, ceph-based SSD storage (latency ~1.5ms), and runs 
on max_connections=100 with usually 25-40 processes being connected.


Is this to be expected?
Is there something I can watch out or monitor for?


Thank you for any insights...
Best regards

    -hannes

Re: Big variance in execution times of simple queries

[Tom Lane]

Hannes Erven  writes:
> I'm looking at a "SELECT * FROM pg_stat_statements" output and am 
> puzzled by the huge differences between min/max_exec_time even for 
> simple queries.

> The most extreme example is probably the statement used by the 
> application's connection health check:
> SELECT 1
> min=0.001, mean=0.00386, max=36.812

Am I right in guessing that that's the first/only command issued
in its connection?  If so, there would be catalog cache population
overhead involved here, which might explain some of the variance.
(If some but not all instances are first commands, that'd definitely
help explain the variance.  PG backends are fairly heavyweight
objects, and take a bit to come up to speed.)

> Other statements with huge variances include:

> SET application_name=$1
> min=0.002, mean=0.005, max=9.177

Could be same issue.

> SELECT * FROM table WHERE id=$1  (where ID is the primary key column; 
> table has 0.5M rows and is frequently vacuum analyzed)
> min=0.010, mean=0.260, max=12338.665

This, on the other hand, seems odd.  Maybe the query sometimes gets
blocked on a lock?  Enabling log_lock_waits might help you check.

> According to the system's monitoring, there is no pressure on any 
> resource (cpu/mem/io). It's 13.5-2pgdg20.04+1 on Ubuntu 20.4; the VM has 
> 12 cpus/16GB memory, ceph-based SSD storage (latency ~1.5ms), and runs 
> on max_connections=100 with usually 25-40 processes being connected.

Hmm ... if you're sitting on a VM rather than directly on the iron,
then there's a whole bunch of other potential reasons for irregular
performance, most of which you probably can't see from inside the VM.

regards, tom lane

Re: Proposed German Translation of Code of Conduct Policy

[Karsten Hilbert]

Ein Vorschlag:

> Inklusivität und angemessenes Verhalten
>
> Das PostgreSQL-Projekt steht jedem offen, der Interesse an
> der Arbeit mit PostgreSQL hat, unabhängig von seinem
> Erfahrungsstand mit der Software oder mit Technologie im
> Allgemeinen. Wir fördern die Entwicklung und Beiträge aller
> Benutzer, unabhängig ihres Hintergrunds.

Entweder "unabhängig von ihrem" oder besser "ungeachtet ihres".

> Im Falle einer Beschwerde von oder gegen ein Mitglied des
> CoC-Komitee oder des Core-Teams erfolgt die
> Beschwerdebearbeitung wie gewohnt, mit der Ausnahme, dass die
> Person, die in die Beschwerde involviert ist, in ihrer
> Funktion im Komitee oder dem Core-Team vom Verfahren
> ausgeschlossen wird.

What happens if all members of the committee are impeached at once ?

> Die Komitee kann feststellen, dass ein Verstoß gegen diese

"*Das* Komitee"

Regards,
Karsten Hilbert
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Undetected Deadlock

[Michael Harris]

Hello Experts

I'm hoping you will be able to help me with a tricky issue.

We've recently updated our application to PG 14.1, and in the test instance we
have started to see some alarming undetected deadlocks.

An example of what we have seen is:

 locktype | database |  relation  | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction |
pid   |  mode   | granted | fastpath |   waitstart
  |relation
--+--++--+---++---+-+---+--++-+-+-+--+---+
 relation |   529986 | 1842228045 |  |   ||
   | |   |  | 165/1941408| 2130531 |
AccessShareLock | f   | f| 2022-01-19 00:32:32.626152+01 |
st.ctr_table_efr_oa
(1 row)

 locktype | database |  relation  | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction |
pid   |mode | granted | fastpath | waitstart |
   relation
--+--++--+---++---+-+---+--++-+-+-+--+---+
 relation |   529986 | 1842228045 |  |   ||
   | |   |  | 75/2193719 | 2128603 |
AccessExclusiveLock | t   | f|   |
st.ctr_table_efr_oa
(1 row)

 locktype | database |  relation  | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction |
pid   |mode | granted | fastpath |   waitstart
  | relation
--+--++--+---++---+-+---+--++-+-+-+--+---+---
 relation |   529986 | 1842231489 |  |   ||
   | |   |  | 75/2193719 | 2128603 |
AccessExclusiveLock | f   | f| 2022-01-19
00:32:32.924694+01 | st.tpd_oa
(1 row)

   locktype| database |  relation  | page | tuple |  virtualxid  |
transactionid | classid |   objid   | objsubid | virtualtransaction |
 pid   | mode  | granted | fastpath |
waitstart   | relation
---+--++--+---+--+---+-+---+--++-+---+-+--+---+---
 relation  |   529986 | 1842231489 |  |   |  |
  | |   |  | 165/1941408|
2130531 | AccessShareLock   | t   | f|
  | st.tpd_oa

So:
  pid 2130531 waits for an AccessShareLock on relation 1842228045,
blocked by pid 2128603 which holds an AccessExclusiveLock
  pid 2128603 waits for an AccessExclusiveLock on relation 1842231489,
blocked by pid 2130531 which holds an AccessShareLock

The queries being executed by these backends are:

   pid   |  query_start  | state_change
  | wait_event_type | wait_event | state  |
  query
-+---+---+-+++-
 2128603 | 2022-01-19 00:32:32.924413+01 | 2022-01-19
00:32:32.924413+01 | Lock| relation   | active | DROP
TABLE st.tpd_oa_18929
 2130531 | 2022-01-19 00:32:32.625706+01 | 2022-01-19
00:32:32.625708+01 | Lock| relation   | active | DELETE
FROM st.ctr_table_efr_oa_19010 WHERE ropid = 44788868
(2 rows)

Both of these processes had been waiting for nearly 2 hours when I
took the above printouts. My understanding of deadlock detection is
that when a backend is waiting for a lock, it runs a deadlock
detection function once a second so it should have been detected
straight away but somehow it is not.

Are there any deadlock conditions that postgresql is not able to detect?

Note that there were a lot of other processes also waiting on relation
1842231489 - could that be confusing the deadlock detection routine?

I am also confused about the locks which are being taken out by the
DELETE query. Process 2130531 is trying to delete rows from a
partition `st.ctr_table_efr_oa_19010`, and to do so it is trying to
get an AccessShareLock on the parent table `st.ctr_table_efr_oa`. I
don't really understand why that is, and in fact if I try to reproduce
the deadlock manually by executing the relevant SQL in psql, the
DELETE never tries to take out such a lock and no deadlock occurs.

What are the circumstances where deleting rows directly from a
partition will cause it to take a lock on the p

Re: Undetected Deadlock

[Michael Harris]

My apologies,

After posting this and looking at how it appears I realised that line
wrapping makes the tables totally illegible.

Here they are again with all unnecessary columns removed and others shortened.

 locktype | database |  relation  |   pid   |mode |
granted |   waitstart   | relation
--+--++-+-+-+---+
 relation |   529986 | 1842228045 | 2130531 | AccessShareLock | f
 | 2022-01-19 00:32:32.626152+01 | st.ctr_table_efr_oa
 relation |   529986 | 1842228045 | 2128603 | AccessExclusiveLock | t
 |   | st.ctr_table_efr_oa
 relation |   529986 | 1842231489 | 2128603 | AccessExclusiveLock | f
 | 2022-01-19 00:32:32.924694+01 | st.tpd_oa
 relation |   529986 | 1842231489 | 2130531 | AccessShareLock | t
 |   | st.tpd_oa


   pid   |query_start |   state_change | wait_event_type |
wait_event |   query
-+++-++-
 2128603 | 00:32:32.924413+01 | 00:32:32.924413+01 | Lock|
relation   | DROP TABLE st.tpd_oa_18929
 2130531 | 00:32:32.625706+01 | 00:32:32.625708+01 | Lock|
relation   | DELETE FROM st.ctr_table_efr_oa_19010 WHERE ropid =
44788868

I hope this version is easier to read.

Cheers
Mike
On Tue, 25 Jan 2022 at 15:49, Michael Harris  wrote:
>
> Hello Experts
>
> I'm hoping you will be able to help me with a tricky issue.
>
> We've recently updated our application to PG 14.1, and in the test instance we
> have started to see some alarming undetected deadlocks.
>
> An example of what we have seen is:
>
>  locktype | database |  relation  | page | tuple | virtualxid |
> transactionid | classid | objid | objsubid | virtualtransaction |
> pid   |  mode   | granted | fastpath |   waitstart
>   |relation
> --+--++--+---++---+-+---+--++-+-+-+--+---+
>  relation |   529986 | 1842228045 |  |   ||
>| |   |  | 165/1941408| 2130531 |
> AccessShareLock | f   | f| 2022-01-19 00:32:32.626152+01 |
> st.ctr_table_efr_oa
> (1 row)
>
>  locktype | database |  relation  | page | tuple | virtualxid |
> transactionid | classid | objid | objsubid | virtualtransaction |
> pid   |mode | granted | fastpath | waitstart |
>relation
> --+--++--+---++---+-+---+--++-+-+-+--+---+
>  relation |   529986 | 1842228045 |  |   ||
>| |   |  | 75/2193719 | 2128603 |
> AccessExclusiveLock | t   | f|   |
> st.ctr_table_efr_oa
> (1 row)
>
>  locktype | database |  relation  | page | tuple | virtualxid |
> transactionid | classid | objid | objsubid | virtualtransaction |
> pid   |mode | granted | fastpath |   waitstart
>   | relation
> --+--++--+---++---+-+---+--++-+-+-+--+---+---
>  relation |   529986 | 1842231489 |  |   ||
>| |   |  | 75/2193719 | 2128603 |
> AccessExclusiveLock | f   | f| 2022-01-19
> 00:32:32.924694+01 | st.tpd_oa
> (1 row)
>
>locktype| database |  relation  | page | tuple |  virtualxid  |
> transactionid | classid |   objid   | objsubid | virtualtransaction |
>  pid   | mode  | granted | fastpath |
> waitstart   | relation
> ---+--++--+---+--+---+-+---+--++-+---+-+--+---+---
>  relation  |   529986 | 1842231489 |  |   |  |
>   | |   |  | 165/1941408|
> 2130531 | AccessShareLock   | t   | f|
>   | st.tpd_oa
>
> So:
>   pid 2130531 waits for an AccessShareLock on relation 1842228045,
> blocked by pid 2128603 which holds an AccessExclusiveLock
>   pid 2128603 waits for an AccessExclusiveLock on relation 1842231489,
> blocked by pid 2130531 which holds an AccessShareLock
>
> The queries being executed by these backends are:
>
>pid   |  query_start 

Re: Undetected Deadlock

[Rob Sargent]

> On Jan 24, 2022, at 10:02 PM, Michael Harris  wrote:
> 
> My apologies,
> 
> After posting this and looking at how it appears I realised that line
> wrapping makes the tables totally illegible.
> 
> Here they are again with all unnecessary columns removed and others shortened.
> 
> locktype | database |  relation  |   pid   |mode |
> granted |   waitstart   | relation
> --+--++-+-+-+---+
> relation |   529986 | 1842228045 | 2130531 | AccessShareLock | f
> | 2022-01-19 00:32:32.626152+01 | st.ctr_table_efr_oa
> relation |   529986 | 1842228045 | 2128603 | AccessExclusiveLock | t
> |   | st.ctr_table_efr_oa
> relation |   529986 | 1842231489 | 2128603 | AccessExclusiveLock | f
> | 2022-01-19 00:32:32.924694+01 | st.tpd_oa
> relation |   529986 | 1842231489 | 2130531 | AccessShareLock | t
> |   | st.tpd_oa
> 
> 
>   pid   |query_start |   state_change | wait_event_type |
> wait_event |   query
> -+++-++-
> 2128603 | 00:32:32.924413+01 | 00:32:32.924413+01 | Lock|
> relation   | DROP TABLE st.tpd_oa_18929
> 2130531 | 00:32:32.625706+01 | 00:32:32.625708+01 | Lock|
> relation   | DELETE FROM st.ctr_table_efr_oa_19010 WHERE ropid =
> 44788868
> 
> I hope this version is easier to read.
> 
> Cheers
> Mike
> On Tue, 25 Jan 2022 at 15:49, Michael Harris  wrote:
It’s definitely tidier, but on my mac Mail.app and on Thunderbird there is 
still a line wrap problem.  I don’t think that will prevent the experts from 
helping out.  I’m not one of them.