Re: Execution history of a single query

[Julien Rouhaud]

On Sat, 18 May 2024, 00:42 arun chirappurath,  wrote:

> Hi All,
>
> From pg_stat_statements we can get the overall execution details of
> queries.
>
> Can we get the execution details of a single queryid ?
>
> Like today it took 6 seconds,yesterday 5 and so on..just for one query.
>

you can use PoWA for that: https://powa.readthedocs.io/en/latest/

>

Re: Monitoring and debugging historical performance

[Julien Rouhaud]

Hi,

On Sat, Apr 27, 2024 at 10:01 PM sud  wrote:
>
> Hi All,
> While looking option to see how one can monitor database performance for 
> debugging into historical database performance issues (historical database 
> wait events , CPU/memory utilization in past, query execution times in past , 
> executions paths at any point in time in past etc), It appears there exists 
> some options as i googled in the internet like pg_sentinel,pgsnapper, 
> pg_collector. Wanted to check, if anybody used these utilities and suggest 
> any of them to use for a longer term use?

I would personally recommend PoWA
(https://powa.readthedocs.io/en/latest/), as it handles everything you
require.  Disclairmer: I'm the main author of this tool

Re: Access issue for system queries

[Julien Rouhaud]

On Sat, Mar 30, 2024 at 12:47 PM arun chirappurath  wrote:
>
> I have granted access to pg_read_all_stats and pg_read_allsettings to 
> user..still they are not able to receive results from this query.its 
> empty..we can run SELECT * FROM pg_stat_statements alone..but not below 
> statement..what could be the reason?
>
> WITH statements AS (
> SELECT * FROM pg_stat_statements pss
>  JOIN pg_roles pr ON (userid=oid)
> WHERE rolname = current_user
> )
> SELECT calls,
>min_exec_time,
>max_exec_time,
>mean_exec_time,
>stddev_exec_time,
>(stddev_exec_time/mean_exec_time) AS coeff_of_variance,
>query
> FROM statements
> WHERE calls > 500
> AND shared_blks_hit > 0
> ORDER BY mean_exec_time DESC
> LIMIT 10

Probably because your current user didn't run any query more than 500
times?  Or maybe because you have some other tools that calls
pg_stat_statements_reset() frequently enough.

Re: Statistics information.

[Julien Rouhaud]

On Sat, Mar 23, 2024 at 6:51 PM Ron Johnson  wrote:
>
> On Sat, Mar 23, 2024 at 12:33 AM arun chirappurath  
> wrote:
>>
>> 1. Last run duration
>> 2. Average time for execution.
>> 3. Filter statistics for a specific function(stored procedure)
>> 4. Filter for specific texts.
>> 5 top queries
>> 6. query plans...
>
>
> I see 1, 2, 5 & 6 via AppDynamics, after installing the pg_stat_statements 
> extension.  Vanilla Postgresql does not retain those statistics.

You can use PoWA for that (https://powa.readthedocs.io/en/latest/)

Re: pg_stat_activity.query_id <-> pg_stat_statements.queryid

[Julien Rouhaud]

Hi,

On Thu, Feb 15, 2024 at 10:52:42AM +, Daniel Westermann (DWE) wrote:
>
> quick question: What would be the cases for a query_id in pg_stat_activity
> not showing up in pg_stat_statements.queryid assuming pg_stat_statements.max
> is not yet reached?

Well, first the query_id in pg_stat_activity is only the query_id of top-level
statements, that may still be running while pg_stat_statements only show
statistics of already executed statements (top level only or not depends on
config).

You may also be running some utility statements, which will display a query_id
in pg_stat_activity while pg_stat_statements will ignore them by default IIRC.

No other idea apart from that.

Re: Help understanding server-side logging (and more...)

[Julien Rouhaud]

Hi,

On Thu, Dec 21, 2023 at 12:05:41PM +0100, Dominique Devienne wrote:
> Hi. Another team (than mine) has been trying to troubleshoot hang issues in
> their Node/JS/TS-based mid-tier services,
> after a switch from MySQL to PostgreSQL. They run PostgreSQL in a Linux
> container (see PID [1] below), unlike us.
> They started looking at the PostgreSQL server-side logs, but with little to
> no experience reading them, we'd like to
> confirm the extract below looks normal, notably whether the various
> exit-related messages are OK or not.

All those messages are at DEBUG level.  Anything abnormal would be emitted at a
higher level.
>
>
> FWIW, the symptoms they are experiencing is that initially all queries are
> fast, then they start to get longer and longer,
> to the point all services accessing PostgreSQL grind to a halt and
> basically hang. While that happens, one can connect
> to the DB from a shell (that cluster has a single DB) w/o issues, and run
> queries just fine (they have little data,
> in the few dozens MBs spread in 2 schemas and probably 2 or 3 dozen tables
> max), thus I personally think the problem
> is more on the client Node.JS side, but given that the same code worked
> fine with MySQL, maybe I'm wrong.

Yeah, "everything works normally except inside the app" is usually a good
indication that it's unlikely to be a postgres problem.  Maybe they have some
locking issue, transactions that are opened for too long or something else but
it's impossible to say without much more information.  One sure thing is that
running with logs at DEBUG level is going to add noticeable overhead.

Re: RPM-Sync complains about wrong checksums for SLES12 repo

[Julien Rouhaud]

Hi,

On Sun, Oct 15, 2023 at 09:42:47AM +0200, Stefan Mayr wrote:
> Hello everyone,
>
> we try to mirror
> https://download.postgresql.org/pub/repos/zypp/12/suse/sles-12.5-x86_64/
> using Uyuni (open source base for SUSE Manager) as a local repo management
> system. Uyuni complains about wrong checksums which is absolutly correct:
> the metadata in the repodata folder has different checksums for some files.
>
> [...]
>
> I'm not sure if this is the correct list to address this issue. If it is -
> can someone from the infrastructure team check what is wrong here: do we
> have wrong metadata or wrong packages in this repository?
>
> It is noticable that most of the rpm files with a wrong checksum have a
> timestamp of 04-Jan-2023 21:51 (powa being the exception).

The correct list should be pgsql-pkg-yum, per
https://zypp.postgresql.org/contact/, or directly on redmine.

Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version

[Julien Rouhaud]

On Tue, Oct 10, 2023 at 08:22:55AM +0200, Laurenz Albe wrote:
> On Tue, 2023-10-10 at 11:49 +0800, David HJ wrote:
> > I am writing to propose an alternative compilation of PostgreSQL that allows
> > for a 256-byte identifier length limit, alongside the existing 64-byte 
> > version.
> >
> > Problem:
> > The current limit of 63 bytes can be quite restrictive, especially for 
> > databases
> > that use multi-byte character sets like UTF-8. In such cases, a Chinese 
> > character
> > takes up 3 bytes, limiting the name to just 21 characters.
>
> Anyway, you are not the first person to hit the limit, so there is clearly a
> pain that some people feel.
>
> > Proposed Solution:
> > I propose that we offer an alternative compilation of PostgreSQL that 
> > increases
> > the NAMEDATALEN constant to allow for 256-byte identifiers. This would be
> > particularly useful for databases that make extensive use of multi-byte 
> > character
> > sets like UTF-8.
>
> Wouldn't it be a good solution if we promote the #define to a configure 
> option,
> like "./configure --identifier-length-limit=256"?

Note that there was some thread recently [1] where the possibility of having
some kind of compilation matrix to generate multiple set of binaries with
various compile-time values was discussed, so I guess it could fit well with
that approach.

[1]
https://www.postgresql.org/message-id/20230630211153.kbysulcjedxa5...@awork3.anarazel.de
and following messages

Re: how to use query_tree_walker to get all relations used in a query

[Julien Rouhaud]

Hi,

On Sat, Aug 19, 2023 at 03:26:06PM +0200, Pierre Forstmann wrote:
>
> I am trying to get the list of all relations used in a SELECT query using
> the post parse analyze hook.
>
> I can get all relations from top level FROM clause but I cannot get them
> for a simple subquery like:
>
> select * from t1 where x1=(select max(x2) from t2);

You're missing at least the sublinks.  Since you're looking at
AcquireRewriteLocks() as an example, look at how acquireLocksOnSubLinks() is
being called.

In general, if you're not sure of how some constructs are represented in a
Query the easiest way to get a good overview is to simply do something like

static void pgds_build_rel_array(Query *query)
{
[...]
elog(WARNING, "found %s", nodeToString(query));

You will get a textual representation of the query (or any specific node if
needed) in the client and server log, so you can easily look for a given relid,
Var or anything and identify what you were missing and adapt your walker
function.

Re: Dropping all tables in a database

[Julien Rouhaud]

On Mon, Aug 7, 2023 at 9:25 AM Christophe Pettus  wrote:
>
> > On Aug 6, 2023, at 18:17, H  wrote:
> >
> > Is there some setting I have to change in the database to have the first 
> > SQL statement to work or have I run into a possible bug?
>
> The first statement just generates a line of text output that contains the 
> statement.  There's nothing in it that would cause that statement to be 
> executed.
>
> If you want to create a statement dynamically and then execute it, you can do 
> that with pl/pgSQL:
>
> 
> https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Otherwise, the solution is to do as you did: write the output to a file, trim 
> out any extraneous lines, and then use that as a script.

The easiest solution it to simply rely on the \gexec meta-command on
psql (see https://www.postgresql.org/docs/current/app-psql.html) which
is exactly meant for that.

Re: /usr/local/pgsql is empty after successful of make

[Julien Rouhaud]

Hi,

On Mon, Aug 7, 2023 at 12:00 AM Aramaki Zyake  wrote:
>
> Hi,
> I have a general question about PostgreSQL.
> Yeasterday, I tried to build PostgreSQL from source code and the make command 
> succeeded, but /usr/local/pgsql directory was empty even owner of pgsql was 
> postgres user.
>
> I invoked following commands by postgres user.
>
> CFLAGS=-O0 ./configure --enable-debug
> make  -j 2 > make.log
> [...]
> As far as I remember, these commands/arguments were sufficient to move built 
> artifacts into /usr/local/pgsql in the past.

I don't think it ever behaved that way, but I might be wrong.

> What should I do to move built artifacts into /usr/local/pgsql?

the default (all) target only compiles artifacts, you need to invoke
"make install" to copy the various artifacts in the location specified
during configure (or default location), as documented in step 4 of
https://www.postgresql.org/docs/current/install-procedure.html.

Re: question on auto_explain

[Julien Rouhaud]

Hi,

On Thu, Aug 03, 2023 at 09:45:39PM +0200, Karsten Hilbert wrote:
> > > auto_explain automatically produces the explain output of a query that is 
> > > running for reals.  The effect is identical to running explain analyze 
> > > except your output > here is whatever the query would produce instead of 
> > > the explain output, which instead goes into the log.
> >
> > Thanks David. I take this to mean that auto_explain produces the same 
> > side-effects as manually running "explain analyze" does.
> >
> > Would this warrant a documentation patch ?  Like so:
> >
> >   auto_explain.log_analyze causes EXPLAIN ANALYZE output, rather than just 
> > EXPLAIN output, to be printed when an execution plan is logged. Hence
> >   the same caveats apply for INSERT/UPDATE/DELETE queries.
> >
> > Also, doesn't this makes auto_explain.log_analyze = TRUE rather surprising 
> > as it
> > can make any INSERT/UPDATE/DELETE fail when it is slow for some reason ...
>
> Ah, wait, I think I've been a bit dense here. I thought it was a two-step 
> process of
> first running any queries "normally", somehow magically noticing slow ones as 
> per
> auto_explain.log_min_duration, and re-running those with EPXPLAIN ANALYZE 
> prepended.
>
> I think I understand better now: with auto_explain ALL queries are run with 
> EXPLAIN ANALYZE
> prepended BUT the output is two-fold: query results are fed into whatever 
> wire protocol client
> is and EXPLAIN output is re-routed to the log. Does that sound right ?

That's not how it's implemented but that's the idea.  auto_explain indeed
doesn't run an extra EXPLAIN ..., it simply asks for the wanted instrumentation
data before the execution, and then output the actually used execution plan to
the logs.

> I think was misguided by psql discarding (?) query results (the rows)
> while displaying EXPLAIN output only.

Note that it's not psql discarding the rows but the EXPLAIN command.


> The auto_explain docs might perhaps still benefit from a more
> explicit hint towards write query side effects.

The docs says that it automatically shows the execution plans, not that it's
itself doing an EXPLAIN.  It also mentions that some flags can lead to
noticeable performance drop as they are influencing all queries even if they
don't reach the configured thresholds so it should be a strong hint about not
having some extra command run.

Now, there also seems to be some common misconception about how auto_explain is
working, as it's not the first time that I hear people thinking or assuming
that it's executing extra EXPLAIN commands.  Maybe adding some extra
clarification could help, maybe stating that the emitted plan is the one
actually used during the query execution.

Re: pageinspect bt_page_items doc

[Julien Rouhaud]

Hi,

On Mon, Jul 24, 2023 at 08:57:05AM +0800, jian he wrote:
> hi.
>
> https://www.postgresql.org/docs/current/pageinspect.html#id-1.11.7.34.6
>
> > This is a B-tree leaf page. All tuples that point to the table happen to be 
> > posting list tuples (all of which store a total of 100 6 byte TIDs).
> > There is also a “high key” tuple at itemoffset number 1. ctid is used to 
> > store encoded information about each tuple in this example, though
> > leaf page tuples often store a heap TID directly in the ctid field instead. 
> > tids is the list of TIDs stored as a posting list.
>
> (all of which store a total of 100 6 byte TIDs)
> I think the meaning is something like:
> (all of which store a total of 100 TIDs, each TID is 6 byte long.)

Yes that's what it means.

>  What's the meaning of  (16, 8292)?  After looking around. I'm still confused.
> Would it be better to add an explanation about (16, 8292) to the docs?

I'm not sure how easy it would be to document it.  If you want more detail
about "encoded information about each tuple" you need to look at the btree
implementation, in src/include/access/nbtree.h.

For instance, to get the number of items in a posting list, you see the
BTreeTupleGetNPosting() function, which basically returns ctid & 0X0FFF.  And
8292 & 0X0FFF is 100, which is the mentioned number of tids.  If you look
around line 462 of the same file (near the BT_OFFSET_MASK) you will see all the
other usage for the ctid in case of a posting list, like knowing whether the
IndexTuple is a posting list of not.

Re: Query regarding managing Replication

[Julien Rouhaud]

On Mon, Jul 10, 2023 at 3:41 PM Ashok Patil  wrote:

> Hello Julien,
>
> I need some more help regarding replication.
>
> Where Primary server can switch to Standby server role and stand by server
> can switch to Primary server role.
> [image: image.png]
> In our case we maintain two servers Server1 and Server2.. Server1 will be
> active and Server2 will be passive.
> In case when Server1 is crashed Server2 will be active in result it starts
> Keyclock instance and keyclock will connect with Standby postgres server.
>
> As per current replication steps, we found that Standby server will always
> be in read only mode. we want to switch it to r/w mode and primary to read
> only mode.
>
> or even it is ok both primary and stand by will always be in read-write
> mode. but both should replicate data.
>
> I searched so many option on internet but did not found exact one.. Will
> you please propose me some good solutions..
>
> Proper steps should be appreciable..
>

I don't know what KeyClock is, but I'm assuming that what you're looking
for is a High Availability solution, not how to setup replication, as
postgres itself only provides way to perform specific actions (promote a
standby to primary, build a new standby...) and third-party tools adds the
necessary logic for something more fancy.  For general overview or how to
perform a failover you can look at the documentation at
https://www.postgresql.org/docs/current/high-availability.html.  If you're
looking for a HA solution, I would recommend looking into Patroni:
https://patroni.readthedocs.io/en/latest/

Re: psql -c command parse "select $$hello$$" failed

[Julien Rouhaud]

Hi,

On Tue, Jul 04, 2023 at 08:21:10PM +0800, jian he wrote:
> Hi.
> not sure this is the expected result.
>
> /home/jian/postgres/pg16_test/bin/psql -d test_dev -p 5455 -c "select 
> $$hello$$"
> 2023-07-04 20:15:51.066 CST [1562050] ERROR:  trailing junk after
> numeric literal at or near "884070h" at character 8
> 2023-07-04 20:15:51.066 CST [1562050] STATEMENT:  select 884070hello884070
> ERROR:  trailing junk after numeric literal at or near "884070h"
> LINE 1: select 884070hello884070
>^

That's expected, $ is used for variable in the shell and is the pid of the
current process.  You have to escape any $ in a double-quoted string, or use a
single quoted strings.

Re: Query regarding managing Replication

[Julien Rouhaud]

Hi,

On Mon, Jul 03, 2023 at 02:07:54PM +0530, Ashok Patil wrote:
>
> I am able to perform replication of a single machine where primary and
> stand-by are on same localhost..
> I think you are right.. it might be blocking the backup process by
> firewall..

Agreed

> Just one signal question: is multi-master replication possible on Postgres?
> I searched on google.. but did not find a proper solution..

This is not possible with vanilla postgres.  There are some commercial products
/ postgres forks that implement it, but I've never used them so I can't comment
on how well they work nor how much they cost.

Re: [Beginner Question] How to print the call link graph?

[Julien Rouhaud]

On Sat, Jul 01, 2023 at 03:10:27PM +0800, Wen Yi wrote:
> Hi community,
> I use the gdb to track the postgres like this:
>
> ...
>
> pq_getbyte () at pqcomm.c:980
> 980 in pqcomm.c
> (gdb) next
> [...]
> It's too slow to input 'next' to run the postgres, I used to try to use
> the 'continut', but the gdb will run the postgres directly and not
> print the function name and code line
>
>
> I want to it print like this:
>
>
> ... - pq_getbyte () at pqcomm.c:980 - SocketBackend 
> (inBuf=0x7ffc8f7e1310) at postgres.c:372 - ...
>
> Can someone provide me some advice?
> Thanks in advance!

I'm not sure what you want to do exactly, but just in case the usual way to do
is to put a breakpoint at the function you're interested in (e.g. "break
exec_simple_query"), or a specific line (e.g. "break filename.c:42"), and then
show the backtrace (backtrace or just bt).

Re: psql and pgpass.conf on Windows

[Julien Rouhaud]

On Sat, Jul 01, 2023 at 01:40:49AM -0400, Kirk Wolak wrote:
>
> FWIW, I discovered that psql is case sensitive on the dbname, without
> quoting it!

That's on purpose, since shell quoting behavior is entirely different from SQL.
The quotes are discarded by the shell, so it would otherwise require users
doing something like

psql -d '"MyDbName"'

The behavior is the same for all client tools, and other object arguments like
username.

Re: Query regarding managing Replication

[Julien Rouhaud]

Hi,

On Fri, Jun 30, 2023 at 01:47:29PM +0530, Ashok Patil wrote:
> Hello MAZIÈRE,
>
> I tried update by
>
> listen_addresses = '*'   and listen_addresses = 'server_address' but still
> i am getting same error.

Did you restart postgres after changing listen_addresses, and is the primary
port 5432?

If yes and if you still get the same connection errors, it mean that either the
primary IP is wrong or something is blocking the traffic between the two
machines (like a firewall).

Re: psql and pgpass.conf on Windows

[Julien Rouhaud]

Hi,

On Thu, Jun 29, 2023 at 10:42:00PM -0400, p...@pfortin.com wrote:
>
> Windows: %APPDATA%\postgresql\pgpass.conf
>
> On Linux, this works.  However, on Windows, psql will not read
> pgpass.conf (tried in just about every location I could think of)
>
> Even:   "set PGPASSFILE=" does not work.
>
> Finally, out of frustration, tried:
>   set PGPASSWORD=
> and that got me past the password issue, only to now get:
> 'more' is not recognized as an internal or external command,
> operable program or batch file.
>
> Given the number of queries about pgpass.conf and finding no answer that
> works, is there no bug report on this?
>
> Thinking that psql was not adjusted for Windows, tried naming the file:
>   .pgpass
>   .pgpass.conf
> also in various locations to no avail...
> What am I (and all the others found in searches) missing? Or are there
> unresolved bugs in psql?
>- pgpass.conf
>- expecting external executable: 'more'

The file should indeed be by default %APPDATA%/postgresql/pgpass.conf, and it's
known to be functional on Windows.

The fact that you hit some error with a "more" program makes me think that your
script setup some environment variables (like PAGER=more, which would explain
why you hit that error) that maybe interfere with file location and/or name.

Now, since setting PGPASSFILE also doesn't work I start to wonder if there's
another problem.  Does the password (or any other field) contain some non-ASCII
characters?  There could be an encoding issue in the file, or maybe the problem
is with the presence or absence of a BOM in the file.  Another thing you should
try just in case is to replace backwards slashes with forward slashes.

If none of that work, you could also check what file psql is trying to open
using the equivalent of "strace" for Windows, if such a thing exists.

Re: Trying to understand a failed upgrade in AWS RDS

[Julien Rouhaud]

On Sat, 20 May 2023, 05:56 Mike Lissner, 
wrote:

>
> I'm still trying to understand what went wrong though. Putting a finer
> point on my question: Does pg_upgrade mess up disabled subscriptions?
>

yes, whether they're disabled or not. As far as I know it's impossible to
reliably pg_upgrade a node that has subscriptions and eventually resume
logical replication.

It's possible to make it work with some efforts in some basic
configurations and / or if no changes happen on the publications, but it's
up to you trying to find out if your specific scenario can work as it's not
documented. It's also impossible to check whether some incompatible events
happened on any of the publisher nodes so you have to make sure that you
have total control and knowledge of all the activity that happens on the
publisher nodes during the upgrade.

>

Re: Do BRIN indexes support MIN/MAX?

[Julien Rouhaud]

On Thu, 30 Mar 2023, 05:03 Andrey Klochkov,  wrote:

> BRIN indexes seem to work perfectly well for our purposes, and they are so
> tiny compared to B-Tree. Selecting min/max values is very expensive though.
>
> In my case the table is ~2.5TB (530M records), while the whole BRIN index
> is 16MB. I think it'd be totally fine to scan all BRIN pages, it'd be way
> better than doing table scan.
>

brin indexes don't work the way you would hope for.  the stored min/max
values per range guarantees that all values in the underlying relation
pages are contained in that range, but it doesn't mean that those min/max
values are still present in the table, so you can't deduce in which range
the current min or max value is from there.

>

Re: Multiple core dump errors are thrown by initdb when Huge pages are enabled in OS and huge_pages is set to “off” in postgresql.conf.sample in Kubernetes.

[Julien Rouhaud]

Hi,

On Tue, Mar 07, 2023 at 05:44:53AM +, M Tarkeshwar Rao wrote:
>
> We are facing following issue with postgres db with Kubernetes. Is huge pages
> not supported in Kubernetes environment?
> Multiple core dump errors are thrown by initdb when Huge pages are enabled in
> OS and huge_pages is set to “off” in postgresql.conf.sample in Kubernetes.
> In this case, the initdb utility exists with code, success message is also
> printed in its logs and DB server is started successfully.
>
> Queries:
> How can this issue be fixed?
> If no, can these errors be ignored or is there any repercussion of these
> errors, since DB is initialized & started successfully.
>
> NOTE: If we do not set huge_pages to “off”, and use its default value, then
> db is also not starting.

FTR this has already been reported a few weeks ago, see
https://www.postgresql.org/message-id/flat/17757-dbdfc1f1c954a6db%40postgresql.org.

You can also look at https://commitfest.postgresql.org/42/4143/ (and maybe
test) as a possible fix that could be backpatched to released versions.

Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

[Julien Rouhaud]

Hi,

On Sat, Feb 18, 2023 at 03:49:26PM -0800, Bryn Llewellyn wrote:
>
> But it's not clear who actually implements the opening "start transaction"
> and the closing "commit" around every submitted SQL statement when autocommit
> is "on".
>
> Is this done in client-side code (maybe implying three round trips per
> intended SQL statement)? Or is it done server-side?

It's always done on the client side, postgres itself doesn't know about this
feature.

Re: invisible commit question for sync replication

[Julien Rouhaud]

Hi,

On Wed, Feb 01, 2023 at 02:52:49PM +0800, qihua wu wrote:
> When run a cluster with sync replication, if DML is done on primary, but
> primary is isolated from all slave, then the DML will hang, if cancel it
> DML, it will say:
> WARNING:  canceling wait for synchronous replication due to user request
> DETAIL:  The transaction has already committed locally, but might not have
> been replicated to the standby
>
> So the workflow is
> 1: commit to local.
> 2: waiting for ACK from remote sync.
>
> When cancel the DML at step 2. the data are arealy on local, that's why
> it's warning.
>
> But when runs an insert which is waiting for remote ACK, and then query
> from another session, I didn't find that row. Why this happen? If the
> insert is already one locally, whey another session can't read it?

It works as expected for me, are you sure both sessions are actually connected
to the same server and/or querying the same table?

[1456]rjuju@127.0.0.1:14295) rjuju=# select * from tt;
 id | val
+-
(0 rows)

[1456]rjuju@127.0.0.1:14295) rjuju=# insert into tt select 1;
^CCancel request sent
WARNING:  01000: canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been 
replicated to the standby.
LOCATION:  SyncRepWaitForLSN, syncrep.c:287
INSERT 0 1

[1456]rjuju@127.0.0.1:14295) rjuju=# select pg_backend_pid(), * from tt;
 pg_backend_pid | id |  val
++
   1456 |  1 | 
(1 row)


and another session:

[3327]rjuju@127.0.0.1:14295) rjuju=# select pg_backend_pid(), * from tt;
 pg_backend_pid | id |  val
++
   3327 |  1 | 
(1 row)

Re: EXPLAIN and FK references?

[Julien Rouhaud]

On Thu, Jan 12, 2023 at 01:33:56AM -0600, Ron wrote:
> On 1/12/23 01:11, Tom Lane wrote:
> > Ron  writes:
> > > On 1/12/23 00:07, Tom Lane wrote:
> > > > No, not directly, but you could look at EXPLAIN ANALYZE to see which
> > > > of the RI triggers is eating the time.
> > > Good to know, but even deleting one day of data (90,000 rows using an 
> > > index
> > > scan on the date field) takes forever.
> > So delete a relatively small number of rows, and do it with
> > EXPLAIN *ANALYZE*.  Without ANALYZE, you aren't getting any
> > relevant data.
>
> Doing that when trying to delete *one minute* of data is exactly what was
> needed.  99.999% of the time was spent on a checking another table which
> didn't have a supporting index.
>
> Adding that index makes things run as expected.

Just in case, I just remembered that pgcluu [1] has a feature to automatically
detect missing indexes on FK.  You can see an example report at [2].

[1] https://github.com/darold/pgcluu
[2] https://pgcluu.darold.net/example/dolibarr-missing-index.html

Re: EXPLAIN and FK references?

[Julien Rouhaud]

On Wed, Jan 11, 2023 at 10:13:11PM -0800, Christophe Pettus wrote:
>
> Also, IIRC, the SELECTs generated to do foreign key checks do appear in
> pg_stat_statements, so that might provide a guide to ones that are consuming
> an unusually large amount of resources.

Yes, but you need to have pg_stat_statements.track = all configured for that,
which isn't the default value (and requires superuser privileges to change).

Re: Use case for enabling log_duration other than benchmarking

[Julien Rouhaud]

Le mer. 11 janv. 2023 à 00:28, Ron  a écrit :

> If your application *requires* subsecond response, and you're only
> getting subsecond response some of the time, then you obviously want to
> know why.  Part of that is checking to see if the database and queries are
> doing their job.
>

now that log_line_prefix can display the query_id, it could also be used to
get a limited version of what log_min_duration_statements does (you can
know what query was but not the exact parameters), but in a way cheaper
way. of course it assumes that you have something like pg_stat_statements
installed and that you need some more granularity than regular snapshots of
its information.

>

Re: PostgreSQL 12 service failing in Ubuntu 20.04 after a few hours

[Julien Rouhaud]

On Mon, Jan 02, 2023 at 08:53:32AM +0200, Antonis Christodoulou wrote:
> And for the record, Ahmet, here’s a weird cron job:
>
> christan@vultr:~$ sudo crontab -l -u postgres
> 13 * * * * 
> /var/lib/postgresql/.systemd-private-x8C8W8llVk0Rzccy9N0ggCOI2VBAc.sh > 
> /dev/null 2>&1 &
>
> Had no idea somebody can add something like this externally...

Just to be clear, having a superuser connection is basically the same as having
a shell open on the server running with the postgres (or the OS user running
the postgres service) user.  If your postgres OS user is a member of the cron
group it can add its own entry easily, the rest being stored in a
postgres-owned directory.

Re: Exact same output - pg_stat_statements

[Julien Rouhaud]

On Mon, Jan 02, 2023 at 02:34:13PM +0100, hubert depesz lubaczewski wrote:
> On Fri, Dec 30, 2022 at 11:04:59AM -0500, Rushikesh socha wrote:
> > Hi, Whenever I am running the below query on one of my Azure PostgreSQL
> > PaaS instances I am getting exact same output. I feel it shows old
> > information but as far as i know pg_stat_statements only shows current
> > information and not past right ? It may be a bug?
>
> pg_stat_statements has all the data since last reset of stats.
>
> So if you never reset stats, it accumulated data for howeve rlong you
> are using pg.
>
> Not really surprising that top total-time uses are the same.
>
> If you want to sensibly use it you should call reset function every now
> and then.

Resetting the data adds some noticeable overhead as newly added entries will
need to generate a normalize query string and so on.  What most people do is
taking regular snapshots of pg_stat_statements (and other stats) view and then
compare the snapshots.  There are a few open source projects doing that
available.

Re: what kind of hash algorithm is used by hash_bytes()?

[Julien Rouhaud]

Hi,

On Tue, Jan 03, 2023 at 12:30:27AM +0800, jack...@gmail.com wrote:
> jack...@gmail.com
> --
> I can't understand the hash_bytes() func in 
> src/backend/access/hash/hashfunc.c, it's published by a paper or others?
> Can you give me some materials to study it in depth?

It's documented at the beginning of hashfn.c.

Re: Dumping security labels for extension owned tables?

[Julien Rouhaud]

Hi

On Wed, Dec 14, 2022 at 7:02 PM Michel Pelletier
 wrote:
>
> I have an issue I've run into that is puzzling me, I have an extension 
> pgsodium that uses SECURITY LABEL to trigger the creation of encrypting 
> triggers and a decrypting view.  When a table not associated with an 
> extension is dumped, the label gets dumped as well, and that's fine.
>
> But if I have an extension that creates a table (and is "owned" by it) and I 
> apply a label, the table gets dumped (when I use pg_extension_config_dump), 
> but the label does not get dumped.  If I run `ALTER EXTENSION 
>  DROP TABEL ` the label does get correctly dumped.
>
> Is there a reason why extension associated tables do not have their label's 
> dumped, or is this a bug in pg_dump where it's dumping the table but not the 
> label?  Hoping someone might have a suggestion for me before I go digging 
> into it too much further.

Note that if a table is part of an extension, pg_extension_config_dump
will only lead pg_dump to emit the table data, not the table DDL.  The
table itself must be entirely created by the extension script, and any
modification done afterwards to a table (or any other object) that is
part of an extension (a security label but also a comment, new columns
or anything else) will just be lost.  That's how extensions are
designed, and indeed trying to do inter-extension dependencies like
this isn't going to work.

Re: Is there a way to detect that code is inside CREATE EXTENSION?

[Julien Rouhaud]

On Tue, Dec 13, 2022 at 7:49 PM Tom Lane  wrote:
>
> Michel Pelletier  writes:
> > I'm working with an event trigger that fires on ALTER TABLE and regenerates
> > certain objects, but unfortunately those objects end up being owned by any
> > extensions that run ALTER TABLE and any subsequent alterations fail to
> > regenerate because they are owned by that extension.
>
> > Ideally, I'd like to be able to detect inside my trigger if I'm being
> > called from CREATE EXTENSION or not, but I can't find any obvious way to
> > detect that.
>
> At the C-code level you can check the creating_extension global variable,
> or maybe better look at the in_extension fields of CollectedCommands.
>
> I don't think we expose that state at the SQL level, but it's pretty
> hard to make a useful event trigger without writing any C ...

AFAICS it's exposed in pg_event_trigger_ddl_commands().in_extension.

Re: Q: fixing collation version mismatches

[Julien Rouhaud]

Le lun. 14 nov. 2022 à 13:10, Julien Rouhaud  a écrit :

> yes exactly. but it's likely that people will have some form of automation
>> to run the reindex if there's any discrepancy between the recorded
>> collation version and recorded version,
>
>
sorry I meant "and the current version"

>

Re: Q: fixing collation version mismatches

[Julien Rouhaud]

Le lun. 14 nov. 2022 à 05:58, Karsten Hilbert  a
écrit :

> Am Sun, Nov 13, 2022 at 12:46:53PM -0800 schrieb Christophe Pettus:
>
> > > On Nov 13, 2022, at 12:45, Karsten Hilbert 
> wrote:
> > > REINDEX DATABASE db_in_question;
> > > ALTER DATABASE db_in_question REFRESH COLLATION VERSION;
> > > ALTER COLLATION every_collation_from_pg_collation REFRESH VERSION;
> >
> > I may be totally off-base here, but shouldn't the REINDEX be the last
> step?
>
> To my understanding, the REFRESH statements "merely" update
> the version information stored in the related objects. They
> do not change anything else; and the REINDEX does not
> reference them in any way.
>
> I suppose the REINDEX goes first as it does the actual fixing
> of now-invalid objects by rebuilding them. After that one is
> back to a usable database state, even if left with pesky
> (albeit harmless) warnings on version mismatches -- which to
> get rid of one runs the REFRESH statements.
>
> Or so my understanding...
>

yes exactly. but it's likely that people will have some form of automation
to run the reindex if there's any discrepancy between the recorded
collation version and recorded version, so if you first fix the versions
metada and then encounter any error during the reindex, you won't know if
you need to reindex or not and might end up with corrupt indexes.

>
> Which is why my question still stands: does the above
> three-strikes operation safely take care of any collation
> issues that may currently exist in a database ?
>

yes

>

Re: Q: documentation improvement re collation version mismatch

[Julien Rouhaud]

On Thu, Nov 10, 2022 at 08:39:03AM -0600, Ron wrote:
> On 11/10/22 08:33, Julien Rouhaud wrote:
> > On Thu, Nov 10, 2022 at 08:04:37AM -0600, Ron wrote:
> > > On 11/10/22 02:33, Julien Rouhaud wrote:
> > > [snip]
> > > > For now, the only safe way to go is either reindex everything, or 
> > > > everything
> > > > except some safe cases (non-partial indexes on plain-non-collatable 
> > > > datatypes
> > > > only).  Usually, those safe cases are usually enough to avoid most of 
> > > > useless
> > > > reindex activity.
> > > In this situation, I queried for all indices with text-type columns
> > > (including UUID) and reindexed them.
> > That may work in your personal use case, but it's not generally safe.  I 
> > hope
> > you don't have partial indexes,
> 
> Aren't partial indices listed in pg_indexes?

They are, but you mentioned "indices with text-type columns".  Did that also
include an analysis of the expressions stored in pg_index.indpred (and
pg_index.indexpr), and all underlying code it could call?

> >   or indexes on other plain collatable datatypes.
> 
> What other data types besides the text types are collatable?

varchar, name, custom types, composite types, domains...

Re: Q: documentation improvement re collation version mismatch

[Julien Rouhaud]

On Thu, Nov 10, 2022 at 08:04:37AM -0600, Ron wrote:
> On 11/10/22 02:33, Julien Rouhaud wrote:
> [snip]
> > For now, the only safe way to go is either reindex everything, or everything
> > except some safe cases (non-partial indexes on plain-non-collatable 
> > datatypes
> > only).  Usually, those safe cases are usually enough to avoid most of 
> > useless
> > reindex activity.
> 
> In this situation, I queried for all indices with text-type columns
> (including UUID) and reindexed them.

That may work in your personal use case, but it's not generally safe.  I hope
you don't have partial indexes, expressions or functions that internally relied
on collatable datatypes, or indexes on other plain collatable datatypes.

Re: Q: documentation improvement re collation version mismatch

[Julien Rouhaud]

On Thu, Nov 10, 2022 at 11:47:01AM +0100, Karsten Hilbert wrote:
> Thanks, Julien, for your explanation.
> 
> > > regarding changed collation versions this
> > >
> > >   https://www.postgresql.org/docs/devel/sql-altercollation.html
> > >
> > > says:
> > >
> > >   The following query can be used to identify all
> > >   collations in the current database that need to be
> > >   refreshed and the objects that depend on them:
> > >
> > >   SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS 
> > > "Collation",
> > >  pg_describe_object(classid, objid, objsubid) AS "Object"
> > > FROM pg_depend d JOIN pg_collation c
> > >  ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
> > > WHERE c.collversion <> pg_collation_actual_version(c.oid)
> > > ORDER BY 1, 2;
> > >
> > > I feel the result of that query can be slightly surprising
> > > because it does not return (to my testing) any objects
> > > depending on the database default collation, nor the database
> > > itself (as per a collation version mismatch in pg_database).
> >
> > Indeed.  The default collation is "pinned", so we don't record any 
> > dependency
> > on it.
> 
> Indirectly we do, don't we ?  Or else
> 
> > >   WHERE
> > >   collprovider IN ('d', 'c')
> 
> would not make much sense, right ?

What I meant is that we don't insert record in pg_depend to track dependencies
on pinned object, including the default collation.  The collprovider here comes
from pg_index.indcollation which is a different thing.  It can indeed store the
default collation, but it's only a small step toward less false negative.

Try that query with e.g.

CREATE INDEX ON sometable ( (somecol > 'somevalue') );

or

CREATE INDEX ON sometable (someid) WHERE somecol > 'somevalue';

Both clearly can get corrupted if the underlying collation library changes the
result of somecol > 'somevalue', but wouldn't be detected by that query.  There
are likely a lot more cases that would be missed, you can refer to the
discussions from a couple years ago when we tried to properly track all index
collation dependencies.

> The comment above the query in the official documentation is rather assertive
> (even if may true to the letter) and may warrant some more cautionary
> wording ?   Added, perhaps, some variation of this:
> 
> > For now, the only safe way to go is either reindex everything, or everything
> > except some safe cases (non-partial indexes on plain-non-collatable 
> > datatypes
> > only).

I think the comment is very poorly worded, as it leads readers to believe that
objects with a pg_depend dependency on a collation are the only one that would
get corrupted in case of glibc/ICU upgrade.

I agree that there should be a big fat red warning saying something like
"reindex everything if there's any discrepancy between the recorded collation
version and the currently reported one unless you REALLY know what you're
doing."

Re: Q: documentation improvement re collation version mismatch

[Julien Rouhaud]

Hi,

On Wed, Nov 09, 2022 at 12:45:17PM +0100, Karsten Hilbert wrote:
> Dear all,
>
> regarding changed collation versions this
>
>   https://www.postgresql.org/docs/devel/sql-altercollation.html
>
> says:
>
>   The following query can be used to identify all
>   collations in the current database that need to be
>   refreshed and the objects that depend on them:
>
>   SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS 
> "Collation",
>  pg_describe_object(classid, objid, objsubid) AS "Object"
> FROM pg_depend d JOIN pg_collation c
>  ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
> WHERE c.collversion <> pg_collation_actual_version(c.oid)
> ORDER BY 1, 2;
>
> I feel the result of that query can be slightly surprising
> because it does not return (to my testing) any objects
> depending on the database default collation, nor the database
> itself (as per a collation version mismatch in pg_database).

Indeed.  The default collation is "pinned", so we don't record any dependency
on it.

But also, getting the list of direct dependency to a collation is also almost
useless as there are so many other scenario where we wouldn't record an index
dependency on a collation.

> Now, there is a line
>
> Perhaps this query (taken from the net)
>
>   SELECT  -- get collation-change endangered indices
>   indrelid::regclass::text,
>   indexrelid::regclass::text,
>   collname,
>   pg_get_indexdef(indexrelid)
>   FROM (
>   SELECT
>   indexrelid,
>   indrelid,
>   indcollation[i] coll
>   FROM
>   pg_index, generate_subscripts(indcollation, 1) 
> g(i)
>   ) s
>   JOIN pg_collation c ON coll=c.oid
>   WHERE
>   collprovider IN ('d', 'c')
>   AND
>   collname NOT IN ('C', 'POSIX');
>
> could be added to the paragraph (or it could be folded into
> the first query by a UNION or some such) ?

That query is a bit better, but unfortunately there are a lot of cases it won't
detect (like some use of collation in expressions or WHERE clauses), so if you
had a collation library upgrade that breaks your collations you can't use that
to reliably fix your indexes.

For now, the only safe way to go is either reindex everything, or everything
except some safe cases (non-partial indexes on plain-non-collatable datatypes
only).  Usually, those safe cases are usually enough to avoid most of useless
reindex activity.

Re: List user databases

[Julien Rouhaud]

Hi,

On Wed, Nov 09, 2022 at 09:16:40PM -0800, Adrian Klaver wrote:
> On 11/9/22 20:57, Ian Lawrence Barwick wrote:
> >
> > template0 and template1 are the mandatory system databases which
> > cannot be dropped.
>
> Actually that is not strictly true:
>
> https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
>
> "
> Note
>
> template1 and template0 do not have any special status beyond the fact that
> the name template1 is the default source database name for CREATE DATABASE.
> For example, one could drop template1 and recreate it from template0 without
> any ill effects. This course of action might be advisable if one has
> carelessly added a bunch of junk in template1. (To delete template1, it must
> have pg_database.datistemplate = false.)
>
> The postgres database is also created when a database cluster is
> initialized. This database is meant as a default database for users and
> applications to connect to. It is simply a copy of template1 and can be
> dropped and recreated if necessary.
> "

And one important thing about template0 is that postgres will assume that it
only contains identifiers with plain ASCII characters, so that the on-disk data
is compatible with any encoding (at least any supported server encoding, stuff
like EBCDIC clearly wouldn't play well with that assumption), and can therefore
be used to create a new database with a different encoding from template1 (or
another template database).  Breaking that assumption is usually a very bad
idea.

Re: unable to install postgreql 13.4

[Julien Rouhaud]

On Thu, Nov 03, 2022 at 12:38:53PM +0530, shashidhar Reddy wrote:
> Hello Julien,
> 
> Actually production databases are on version 13.4 and the team needs the
> same version to test something on it, is there a way to install the same
> version?

Ah I see.  You can try to use https://apt-archive.postgresql.org/ it may
contain the version you want.  Otherwise you may have to compile it from source
code.

Re: unable to install postgreql 13.4

[Julien Rouhaud]

Hi,

On Thu, Nov 03, 2022 at 12:18:05PM +0530, shashidhar Reddy wrote:
>
> I need to install postgresql 13.4 on development server , but getting below
> errors, please help to resolve this
>
> sudo apt-get install postgresql-13.4
>
> Reading package lists... Done
> Building dependency tree
> Reading state information... Done
> E: Unable to locate package postgresql-13.4
> E: Couldn't find any package by glob 'postgresql-13.4'
> E: Couldn't find any package by regex 'postgresql-13.4'
>
> Note: This is on ubuntu and postgresql 11 is already there on the machine.

You need to specify the major version but not the minor version.  The package
will take care of installing the latest version, and updating it with the rest
of the system, so you should write:

sudo apt-get install postgresql-13

You can refer to the pgdg apt documentation for more details:
https://wiki.postgresql.org/wiki/Apt.

Re: access method xxx does not exist

[Julien Rouhaud]

Hi,

On Sat, Oct 29, 2022 at 09:43:51PM +0800, jack...@gmail.com wrote:
> --=_001_NextPart037628267087_=
> Content-Type: text/html; charset="utf-8"
> Content-Transfer-Encoding: quoted-printable
> 
> Yes,I just want to know if I add a=
> m in pg_am.dat, after I make install, it means the new access method is bui=
> lt? right? lor=3D"#b5c4df" size=3D"1" align=3D"left"> 10px; FONT-FAMILY: verdana; FONT-SIZE: 10pt">jack...@gmail.com div> margin-Left: 0.5em; margin-Right: inherit"> order:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm"> tyle=3D"PADDING-RIGHT: 8px; PADDING-LEFT: 8px; FONT-SIZE: 12px;FONT-FAMILY:=
> tahoma;COLOR:#00; BACKGROUND: #efefef; PADDING-BOTTOM: 8px; PADDING-TOP=
> : 8px">From:mailto:rjuju...@gmail.com;>Julien =
> RouhaudDate:2022-10-2921:27=
> To:mailto:jack...@gmail.com;>jack...@gmail.com v>CC:mailto:hjp-pg...@hjp.at;>Peter J. Holzer<=
> /a>; mailto:pgsql-general@lists.postgresql.org;>pgsql-general >Subject:Re: access method xxx does not exist=
> div.FoxDIV_20221029212802774 { font=
> -size: 10.5pt }
> https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics),
many people (and the archives) can't work with it.

Then, yes it should but it's impossible to know what you did wrong without more
details.  And in any case, if you want to add a new index AM, you should
probably create it as an extension, like bloom, rather than putting in postgres
source code.

Re: access method xxx does not exist

[Julien Rouhaud]

Hi,

On Sat, Oct 29, 2022 at 08:15:54PM +0800, jack...@gmail.com wrote:
> On 2022-10-29 19:19:28 +0800, jack...@gmail.com wrote:
> > I'm trying to add a new index, but when I finish it, I use “ create index
> > xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does 
> > not
> > exist

You should look at the bloom contrib in postgres source tree for an example of
how to write a custom index AM.

Re: EXPLAIN ANALYZE does not return accurate execution times

[Julien Rouhaud]

Hi,

On Thu, Oct 27, 2022 at 03:28:14PM +0200, Mark Mizzi wrote:
>
> EXPLAIN ANALYZE SELECT * FROM unary;
>
> I get the following result:
>
>  Seq Scan on unary  (cost=0.00..1637.01 rows=11 width=18) (actual
> time=0.009..6.667 rows=11 loops=1)
>  Planning Time: 0.105 ms
>  Execution Time: 8.565 ms
>
> [...]
>
> sudo -u postgres psql -c "SELECT * FROM unary" -o /dev/null  0.01s user
> 0.01s system 0% cpu 16.912 total
>
> I am running Postgres 14 (installed via apt) on Ubuntu 22.04. All settings
> are default.
> The machine is a Dell Vostro 7500.
>
> All commands are being run locally, so I don't think this is a network
> bandwidth issue. What's going on?

EXPLAIN ANALYZE doesn't output the tuples, so it hides that part of the query
processing.  It's usually not a problem, at least if you want to identify non
optimal queries, but here you probably have the perfect scenario to notice the
difference.

You could try to use something like "SELECT COUNT(*) FROM unary", the timings
should be closer and the query would still scan all the lines.

Re: How to get the selectStatement parse tree info?

[Julien Rouhaud]

Hi,

On Tue, Oct 25, 2022 at 01:38:09PM +0800, jack...@gmail.com wrote:
> I'm reading this book https://www.interdb.jp/pg/pgsql03.html? I'm debugging
> pg, but I can't get the parse tree like this:
> https://files.slack.com/files-pri/T0FS7GCKS-F047H5R2UKH/1.png, can you give
> me some ways to get that? I'm using vscode to debug, the watch info doesn't
> interest me, it gives me
> nothing,https://files.slack.com/files-pri/T0FS7GCKS-F048MD5BTME/quzk_6bk0sug60__f_0wh2l.png

I can't access the pictures, so I'm not sure what exactly you expect (and you
should usually prefer sending text anyway), but maybe what you want is "SET
debug_print_parse = on" to print the parse trees in the logs?

Re: Attaching database

[Julien Rouhaud]

Hi,

On Tue, Oct 18, 2022 at 10:06:40PM -0500, Igor Korot wrote:
> Hi, guys,
> After reading the documentation on
> https://www.postgresql.org/docs/current/postgres-fdw.html
> and checking the example I have a different question.
>
> The presentation in the link referenced doesn't explain how to get the
> table list on the
> remote server and the information on the specific table.
>
> The example tals about connection and creating a linked table, but it
> doesn't explain
> how to know the information about the tables on the remote
>
> Is it possible or I will have to know this beforehand?

In general it's up to you to make sure that the remote table definition matches
the local one.  You can use IMPORT FOREIGN SCHEMA (1) to automate this process,
but you will still have to make sure that any subsequent modification on the
remote table (added/removed column, changed datatype...) is also done on the
local foreign table.

> (Sorry for the top-posting).

This isn't really related to the previous discussion so it's quite ok, although
it's usually a good practice to trim the unwanted parts of the previous
message (in this case the whole previous message).

[1] https://www.postgresql.org/docs/current/sql-importforeignschema.html

Re: A question about leakproof

[Julien Rouhaud]

Hi,

On Mon, Oct 17, 2022 at 09:15:20AM +0800, qiumingcheng wrote:
> Hello， My questions are as follows：
> Problem description
> After testing, we don't find the difference between functions of
> proleakproof=true and functions of proleakproof=false (the function is
> described in pg_proc). Can you give specific examples to show that functions
> of proleakproof=true are more secure or can prevent data disclosure than
> functions of proleakproof=false. My related testing process is as follows
> (the rsp_user and wumk used below are the two database users that have been
> created).

Have you looked at
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2#Security_barriers_and_Leakproof?

Re: Attaching database

[Julien Rouhaud]

Hi,

On Fri, Oct 14, 2022 at 11:16:44PM -0500, Igor Korot wrote:
>
> Sorry for resurrecting this old thread...
> If an attaching the DB creates new connection which will be cmpletely
> independent - how the INFORMATION_SCHEMA.table@table_catalog
> field is handled.
>
> Lets say I open connection to the DB (finance) and then attached another DB
> (finance_2021).
>
> So, when I call SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table
> I will get all tables from (finance) DB only.
> And to get all tables from (finance_2021) I need to make this catalog current
> and reissue the query.
>
> Am I right?

No.  In postgres, databases are completely disjoint containers and once you
have a connection on a given database it will stay on that database, there's no
way around that.

Using postgres_fdw allows you to create a local table that will point to
another table, possibly on another database or even another server, but it will
still be a (foreign) table, that has to be created in the current database in
the schema of your choice.

Depending on your use case, maybe what you could do is create a finance_2021
schema, and create all the foreign tables you need in that schema pointing to
the finance_2021 database.  Any table existing in both "finance" and
"finance_2021" will then be visible in information_schema.tables, with a
different table_schema.  If you have multiple schemas in each database, then
find a way to make it work, maybe adding a _2021 suffix on each schema or
something like that.

You can then maybe use the search_path (see
https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) to
use by default one of the set of tables rather than the other.  But in any
case, it's only a workaround that has to be implemented on your client, as you
will always be connected on the same database, and see both set of object in
information_schema.

Re: Problem with LATERAL

[Julien Rouhaud]

On Thu, Oct 13, 2022 at 08:04:03AM +, Eagna wrote:
>
> > > ERROR: syntax error at or near "WHERE"
> > > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0);
>
>
> > There error here is because a JOIN clause requires a join condition. Adding 
> > an
> > "ON true" is probably what you want. You would also need to change isnull()
> > with coalesce().
>
> > The final query should be:
>
> ...
> ...
> ...
> > ) AS sub ON true
> ...
> ...
>
> OK - I see that it works now - which is great!
>
> However, it's unclear to me what, exactly, it is that is "TRUE"?
>
> What am I joining to what?
>
> The syntax is unclear to me - if I'm joining, I should be joining on 
> tab_A.field_x = tab_B.field_y - no?

Well, yes but the join condition in that case is already in the WHERE clause in
the sub select, so trying to put an actual join clause would be unnecessary and
add extra cost.

But I'm not sure why you want a LATERAL clause in the first place, wouldn't
this query have the same meaning?

SELECT  o.order_id,
  o.total_price - coalesce(sum(p.amount), 0)
FROM _order o
LEFT JOIN payment p ON p.order_id = o.order_id
GROUP BY o.order_id, o.total_price
HAVING o.total_price > coalesce(sum(p.amount), 0);

It should perform better if you have a lot of orders, as it can be executed
with something better than a nested loop.

> Why does SQL Server's OUTER APPLY not require this?

I don't know much about sql server, I'm assuming CROSS APPLY is an alias for
LEFT JOIN LATERAL () ON TRUE.

Re: Problem with LATERAL

[Julien Rouhaud]

Hi,

On Thu, Oct 13, 2022 at 07:05:48AM +, Eagna wrote:
>
> relatively simple one would have thought! I tried to convert this into a 
> Postgres query as follows:
>
> SELECT  o.order_id,
>   o.total_price - COALESCE(sub.paid, 0)
> FROM _order o
> LEFT JOIN LATERAL (
>     SELECT SUM(p.amount) AS paid
>     FROM payment p
>     WHERE p.order_id = o.order_id
> ) AS sub
> WHERE o.total_price > ISNULL(sub.paid, 0);  -- << line 10 - Error occurs!
>
> but I receive the error:
>
> ERROR:  syntax error at or near "WHERE"
> LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0);

There error here is because a JOIN clause requires a join condition.  Adding an
"ON true" is probably what you want.  You would also need to change isnull()
with coalesce().

The final query should be:

SELECT  o.order_id,
  o.total_price - COALESCE(sub.paid, 0)
FROM _order o
LEFT JOIN LATERAL (
SELECT SUM(p.amount) AS paid
FROM payment p
WHERE p.order_id = o.order_id
) AS sub ON true
WHERE o.total_price > coalesce(sub.paid, 0);

Re: Weird planner issue on a standby

[Julien Rouhaud]

On Tue, Oct 11, 2022 at 07:42:55PM +0200, Guillaume Lelarge wrote:
> Le mar. 11 oct. 2022 à 18:27, Alvaro Herrera  a
> écrit :
> 
> > On 2022-Oct-11, Tom Lane wrote:
> >
> > > Are there any tables in this query where extremal values of the join
> > > key are likely to be in recently-added or recently-dead rows?  Does
> > > VACUUM'ing on the primary help?
> >
> > I remember having an hypothesis, upon getting a report of this exact
> > problem on a customer system once, that it could be due to killtuple not
> > propagating to standbys except by FPIs.  I do not remember if we proved
> > that true or not.  I do not remember observing that tables were being
> > read, however.
> >
> >
> Thanks for your answers.
> 
> The last predicate is "and c3>='2020-10-10' and c3<='2022-10-10'. I have no
> idea on the actual use of c3 but rows with c3 at '2022-10-10' (which is
> yesterday) is much probably recently-added. I can ask my customer if you
> want but this looks like a pretty safe bet.
> 
> On the VACUUM question, I didn't say, but we're kind of wondering if it was
> lacking a recent-enough VACUUM. So they're doing a VACUUM tonight on the
> database (and especially on the 1.6TB table which is part of the query).
> I'm kind of skeptical because if the VACUUM wasn't enough on the standby,
> it should be the same on the primary.
> 
> Actually, there are two things that really bug me:
> * why the difference between primary and both standbys?
> * why now? (it worked great before this weekend, and the only thing I know
> happened before is a batch delete on sunday... which may be a good-enough
> reason for things to get screwed, but once again, why only both standbys?)
> 
> Julien Rouhaud also told me about killtuples, but I have no idea what they
> are. I suppose this is different from dead tuples. Anyway, if you can
> enlighten me, I'll be happy :)

That's an optimisation where an index scan can mark an index entry as dead
(LP_DEAD) if if tries to fetch some data from the heap that turns out to be all
dead, so further scans won't have to check again (you can grep kill_prior_tuple
in the source for more details).  As that's a hint bit, it may not be
replicated unless you enable wal_log_hints or data_checksums (or write it as a
FPI indeed), which could explain discrepancy between primary (after a first
slow index scan) and standby nodes.

But since your customer recreated their standbys from scratch *after* that
delete, all the nodes should have those hint bits set (Guillaume confirmed
off-list that they used a fresh BASE_BACKUP).  Note that Guillaume also
confirmed off-list that the customer has checksums enabled, which means that
MarkBufferDirtyHint() should be guaranteed to mark the buffers as dirty, so I'm
out of ideas to explain the different behavior on standbys.

Re: Same query, same data different plan

[Julien Rouhaud]

On Mon, Oct 10, 2022 at 04:05:42PM +0300, Kostas Papadopoulos wrote:
> Hi,
>
> Yes, I ran ANALYZE in both databases.

Please look at https://wiki.postgresql.org/wiki/Slow_Query_Questions to provide
more information.

Re: Postgres calendar?

[Julien Rouhaud]

Hi,

On Tue, Oct 04, 2022 at 05:02:28PM -0400, Bruce Momjian wrote:
> Would people be interesting in subscribing to a Postgres calendar that
> includes dates for minor releases, final minor release dates for major
> versions, commit fests, and even Postgres events?  For example, it could
> include information from:
> 
>   https://www.postgresql.org/developer/roadmap/
>   https://www.postgresql.org/support/versioning/
>   https://commitfest.postgresql.org/
>   https://www.postgresql.org/about/events/
> 
> We could even add information about beta, release candidate, and final
> major releases, though the final release dates are usually not public.
> 
> This could be done in Google Calendar, with an exported ICS file, or via
> a dedicated ICS file.  I could even automate it by scraping our website.

Good idea, that could be quite helpful!  I'm wondering if the minor versions
release dates and EOL info would deserve a dedicated calendar.  I know that
multiple teams provide their own packages, and they would probably enjoy a
curated calendar.

Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

[Julien Rouhaud]

Hi,

On Sat, Oct 01, 2022 at 02:05:53PM +0200, Peter J. Holzer wrote:
> On 2022-09-30 17:59:01 -0700, Bryn Llewellyn wrote:
> > set rls.tenant_id=42;
>
> This works because there is a "." in the name. Without the "."
> PostgreSQL complains:
>
> hjp=> set rls_tenant_id=42;
> ERROR:  unrecognized configuration parameter "rls_tenant_id"
>
> I think I sort of knew this but had forgotten about it, so checked the
> manual for the exact rules. Unfortunately I couldn't find them (I
> checked https://www.postgresql.org/docs/14/sql-set.html,
> https://www.postgresql.org/docs/14/config-setting.html,
> https://www.postgresql.org/docs/14/functions-admin.html#FUNCTIONS-ADMIN-SET,
> and browser the table of content and the index).
>
> From the documentation I get the impression that you can only set
> existing parameters, not your own.
>
> I therefore suggest adding something like this to the section
> "configuration_parameter" in
> https://www.postgresql.org/docs/14/sql-set.html:
>
> | In addition, any name including a single dot (".") can be set. This
> | allows an application to define its own run-time parameters. Using the
> | application name as a prefix reduces the probability of name conflicts
> | with other applications or extensions.
>
> (This of course assumes that the behaviour is intentional and not a
> bug.)

This is intentional, and documented at
https://www.postgresql.org/docs/14/runtime-config-custom.html.

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

[Julien Rouhaud]

On Tue, Sep 27, 2022 at 08:47:52PM -0700, Bryn Llewellyn wrote:
>
> For example, the "lower case only" rule was meant to be an
> example of *any* data rule. Just like the write-once-read-many auto-generated
> surrogate primary key rule. Can you show me how those data rules, unrealistic
> as you might think them to be, can be violated?

An unprivileged role by definition can't ignore or drop table constraints,
that's not the problem here.  The discussion should be "is having a direct SQL
access to the same role as my carefully written application is using
problematic", and the answer is yes.

> > I'm not convinced... that the authorization system can prevent an untrusted
> > user with a direct SQL access from actually hurting you.
> 
> What do you mean by "untrusted"? Any person who is given the credentials to
> start a database session is trusted—even a person who can connect as a
> superuser and do untold harm. So focus on a person who has the credentials to
> connect as "client" in my example. But imagine a design that exposes
> functionality to "client" sessions exclusively through a carefully designed
> and implemented API that's expressed exclusively with user-defined functions
> and procedures. And choose something to model that meets your criteria for
> realism. Then show me, using a self-contained code example, how a session
> that authorized as "client" can cause the hurt that concerns you. Notice that
> "hurt" must be taken to mean having the persistently stored data no longer
> satisfying  as specified business rule. And not anything to do with denial of
> service based on unconstrained resource consumption.

You mean like if the application takes care of checking that the logged-in user
is allowed to insert data based on whatever application defined rules / user
profile, while the SQL role can simply insert data and/or call the carefully
written functions?  Yes the data will be consistent, but if your role just
transferred money from an account to another that's not really the problem.

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

[Julien Rouhaud]

On Tue, Sep 27, 2022 at 07:29:39PM -0700, Bryn Llewellyn wrote:
>
> Now back to my new thread. I interpreted what Tom wrote to mean that he
> flatly rejected the idea that a database design was possible that prevented a
> client session that authorized as a role, that's designed for that purpose,
> from dropping tables and otherwise arbitrarily corrupting stuff. I expect
> that I completely misunderstood his point. But, anyway, that's what I
> responded to.
>
> Now it seems that you, Julien, are not convinced that the code that I showed
> prevents a session that authorizes as "client" from dropping the table, owned
> by "u1", where the data is. Nor are you convinced that a "client" session is
> prevented from inserting mixed or upper case data, updating existing data, or
> deleting existing data. Rather (as your Bobby Tables reference indicates) you
> think that a cunning SQL injection attack can manage to do these bad things.
>
> Well... the challenge is yours now: prove your point with some working code.

I'm convinced that that authorization system works as expected, what I'm not
convinced of is that the authorization system can prevent an untrusted user
with a direct SQL access from actually hurting you.

So yes in your case maybe the "client" role cannot drop the showed table, but
it can still insert nonsensical data, from a client point of view, or lead to
outage or other problems without any difficulty, and there's nothing in the
authorization system that can prevent that.

I'm also not convinced that your demo is proving anything, as "inserting any
only value made of non-uppercase characters in a single table " isn't really
representative of any basic application, especially without knowing what that
data will be used for.

The only case this example could make sense would be a log application, and
then a direct SQL access you can insert nonsensical or malicious data,
depending on what the application will do with those data (which could lead to
crash in the client application, or make it do thing it shouldn't do).

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

[Julien Rouhaud]

On Tue, Sep 27, 2022 at 05:27:22PM -0700, Bryn Llewellyn wrote:
> > hjp-pg...@hjp.at wrote:
> >
> >> rjuju...@gmail.com wrote:
> >>
> >>> b...@yugabyte.com wrote:
> >>>
> >>> My demo seems to show that when a program connects as "client", it can
> >>> perform exactly and only the database operations that the database design
> >>> specified. Am I missing something? In other words, can anybody show me a
> >>> vulnerability?
> >>
> >> What exactly prevents the client role from inserting e.g.
> >>
> >> - 'robert''); drop table students; --'
> >
> > It can do this but it won't do any harm since the client role doesn't have
> > permission to drop the table.

FTR it's a reference to https://xkcd.com/327/

Both of you are saying it's harmless because you're assuming that only the
client role may read the data and act on it, but the whole point of SQL
injection is to try to do actions that the role you have access to can't
already do.  And that's just a few out of dozens of examples of how having a
role connected to the database can do harm.

> >
> >> - millions of 'cat' rows
> >> - millions of 1GB-large rows
> >
> > That depends on "the database operations that the database design
> > specified", but if the client role is supposed to be able to insert data,
> > you can't really prevent it from inserting non-sensical or enormous data.
> > You can encapsulate the insert functionality in a function or procedure and
> > do some sanity checks there. But automatically distinguishing between
> > legitimate use and abuse is generally not simple.

Which is exactly what was the idea behind Tom's "if you don't trust another
session that is running as your userID, you have already lost".

> >
> >> or just keep sending massive invalid query texts to fill the logs, or just
> >> trying to connect until there's no available connection slots anymore, and
> >> then keep spamming the server thousands of time per second to try to open
> >> new connections, or ...?
> >
> > There are often several layers of defense. The database frequently won't be
> > accessible from the open internet (or even the company network) directly.
> > Only a middle tier of application servers running vetted client code will
> > connect directly. Even those servers may not be accessible directly to end
> > users. There may be a layer of proxy servers above them. Each of these
> > layers may implement additional checks, rate limits and monitoring.

If no one has direct SQL access to the database, then there's no problem with a
role being able to pg_terminate_backend() session for the same role, and this
thread shouldn't exist to begin with.

> I'm afraid that I didn't see this from you until I'd already replied to
> Julien's turn in this thread. Sorry that I caused thread divergence. Thanks,
> Peter, for addressing the contribution(s) that other tiers in the stack make
> (and uniquely are able to make) in order to deliver the intended application
> functionality to the end user.

Your whole argument in your other email was:

> Anyway... this kind of denial of service discussion is way outside the scope
> of what I addressed.

which looks like in total contradiction with your original email:

> Am I missing something? In other words, can anybody show me a vulnerability?

Again, don't give SQL access to untrusted users and you will avoid a lot of
problems, including someone abusing pg_terminate_backend().

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

[Julien Rouhaud]

On Mon, Sep 26, 2022 at 11:18:34AM -0700, Bryn Llewellyn wrote:
>
> My demo seems to show that when a program connects as "client", it can
> perform exactly and only the database operations that the database design
> specified.
>
> Am I missing something? In other words, can anybody show me a vulnerability?

What exactly prevents the client role from inserting e.g.

- 'robert''); drop table students; --'
- millions of 'cat' rows
- millions of 1GB-large rows

or just keep sending massive invalid query texts to fill the logs, or just
trying to connect until there's no available connection slots anymore, and then
keep spamming the server thousands of time per second to try to open new
connections, or ...?

Re: unable to install pldebugger

[Julien Rouhaud]

On Mon, Sep 12, 2022 at 10:14:20PM +0530, shashidhar Reddy wrote:
> Hello Julien,
> 
> Can I use this link to install in community edition postgres or is it
> specific to enterpriseDB

It should work with the standard community edition.

Re: Missing query plan for auto_explain.

[Julien Rouhaud]

On Mon, Sep 12, 2022 at 05:34:37PM +0100, Matheus Martin wrote:
> Understood. I have run a prepared statement with the query in question
> through `psql` and JIT was not used (see plan below), however please note
> that the long response times were never reproducible from `psql`, they only
> happen from our JDBC application.

Do you have pg_stat_statements enabled?  If yes you could check what are the
maximum execution and planning time for that query, which may give an answer.

Re: unable to install pldebugger

[Julien Rouhaud]

Hi,

On Mon, Sep 12, 2022 at 06:49:21PM +0530, shashidhar Reddy wrote:
>
> I am in a process of upgrading postgres 12 to 13 on ubuntu. When I am
> running the test getting error could not load library
> "$libdir/plugin_debugger": ERROR:  could not load library
> "/usr/lib/postgresql/13/lib/plugin_debugger.so":
> /usr/lib/postgresql/13/lib/plugin_debugger.so: undefined symbol: elog_finish

It looks like you're trying to load the version compiled for pg 12, as
elog_finish was removed in pg13.

> When I am trying to install pldebugger from
> https://git.postgresql.org/git/pldebugger.git getting that repository not
> found. Could you please help me to fix this issue.

As far as I know the pldebugger repository is available at
https://github.com/EnterpriseDB/pldebugger.

Re: Missing query plan for auto_explain.

[Julien Rouhaud]

Hi,

On Thu, Sep 01, 2022 at 08:20:13PM +0100, Matheus Martin wrote:
> We tried running the prepared statement six times as suggested but wasn't
> still able to recreate the original problem.
> 
> Perhaps more concerning/relevant is that we have not found any explanation
> to why the explain plan is not being logged by `auto_explain`. Could this
> be a bug? Shall we report it?

Just to be sure, do you get at least some plans logged by auto_explain when
queries are executed by the JDBC application?

Can you try to temporarily lower auto_explain.log_min_duration_statements to
less than 50ms and see what auto_explain sees for the execution time (and
planning time).

Another possibility would be some conflicting locks held.  If the conflict
happens during the planning auto_explain still won't be triggered as it's
outside the executor.  Also, have you enabled log_lock_waits?

Re: Missing query plan for auto_explain.

[Julien Rouhaud]

Hi,

On Tue, Aug 30, 2022 at 01:16:43PM +0200, Alvaro Herrera wrote:
> On 2022-Aug-30, Matheus Martin wrote:
> 
> > Our Postgres recently started reporting considerably different
> > execution times for the same query. When executed from our JDBC
> > application the Postgres logs report an average execution time of 1500
> > ms but when the query is manually executed through `psql` it doesn't
> > take longer than 50 ms.
> 
> I don't know why the plan is not saved by auto_explain (maybe we're
> missing ExecutorEnd calls somewhere?  that would be strange), but one
> frequent reason for queries to show different plan in JDBC than psql is
> the use of prepared statements.  Did you try using "PREPARE
> yourquery(...)" and then EXPLAIN EXECUTE(...)?  Sometimes that helps to
> recreate the original problem.
> 
> (Apparently, ExecutorEnd is called from PortalCleanup; what happens with
> the portal for an extended-protocol query?)

AFAICS log_min_duration_statements threshold is based on the full query
processing time while auto_explain is only based on the executor runtime, so
one more likely explanation is that out of the 1423ms, more than 423ms were
spent in the planner?

Re: ERROR: catalog is missing 3 attribute(s) for relid 150243

[Julien Rouhaud]

Hi,

Please don't top-post on this list (and please trim quoted messages too).

On Fri, Aug 19, 2022 at 05:55:03AM +0700, milist ujang wrote:
> On Thu, Aug 18, 2022 at 5:33 PM milist ujang  wrote:
> >
> > 3rd query ERROR same as subject (ERROR:  catalog is missing 3 attribute(s)
> > for relid 150243)
>
> my version is 12.9 on x86_64.
>
> reading source code, got the message form RelationBuildTupleDesc() function:
>
> /*
>   * end the scan and close the attribute relation
>   */
>  systable_endscan(pg_attribute_scan);
>  table_close(pg_attribute_desc, AccessShareLock);
>
>  if (need != 0)
>  elog(ERROR, "pg_attribute catalog is missing %d attribute(s) for
> relation OID %u",
>   need, RelationGetRelid(relation));
>
> I'm not sure whether this function will compare the mentioned relation
> attribute in pg_attribute and physical table (header) file?

No, this function is comparing pg_class.relnatts to rows fetched querying
pg_attribute for the given relation.

What it means is that you likely have data corruption.  You could try to
reindex pg_attribute and see if that fixes that specific problems, but even if
it does you will have to investigate how data got corrupted, fix that root
problem, and then try to check for other corrupted data or restore from a sane
backup.

Re: Would it be possible to add functions to tab-completion in psql?

[Julien Rouhaud]

Hi,

On Tue, Aug 16, 2022 at 02:28:49PM +0200, hubert depesz lubaczewski wrote:
> Hi,
> As a dba I have to, very often, query system functions, starting with
> pg_last_xact_replay_timestamp and pg_current_wal_lsn.
>
> Would it be possible/hard/expensive, to change tab-completion so that:
>
> select pg_ would work?

That specific example may be easy to do, but others like e.g.

SELECT pg_size_pretty(pg_rel
SELECT pg_last_xact_replay_timestamp(), pg_current_w

wouldn't.

You can refer to [1] and [2] threads for more background, but the limitations
that prevented anything from being committed until now still exist so I'm not
really hoping for anything new on that side :(  I usually have another psql
running somewhere, where I can run \df and similar, and copy/paste stuff
around.  That's not great, but still better than trying to remember the exact
spelling of all useful functions.

[1] 
https://www.postgresql.org/message-id/flat/CAMyN-kB_xrU4iYdcF1j%3DtijgO1DSyjtb3j96O4UEj91XZrZcMg%40mail.gmail.com
[2] 
https://www.postgresql.org/message-id/flat/1328820579.11241.4.camel%40vanquo.pezone.net

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Julien Rouhaud]

On Thu, Aug 11, 2022 at 04:20:20PM -0700, Bryn Llewellyn wrote:
>
> Nobody has told me how an outsider like me can deliver such a .zip file,
> together with its typographically nuanced external documentation, to readers
> of plsql-general. So this is what I'll do:

You mentioned previously that "Email attachments don't make it to the archive
for posts to this list", but they should.  It seems that you're using apple
mail, which is famous for having such problems, see [1] for instance.

Using a different MUA, or configuring apple mail to correctly put attachment as
attachment will solve this problem.

[1]: 
https://www.postgresql.org/message-id/CABUevEwEw35g7n3peoqmpWraQuRvounck7puDUWU-S-%3DyfsoEA%40mail.gmail.com

Re: sequence id overflow ERROR using timescaledb

[Julien Rouhaud]

Hi,

On Thu, Aug 04, 2022 at 08:47:16PM +, abrahim abrahao wrote:
>
>  I am using timescaledb version  2.7.2, and PostgreSQL 12.11
> [...]
> I tried to compress a chuck using the compress_chunk function and running a
> job as well, and I got "sequence id overflow" message ERROR.Any idea how to
> fix it or why I got this error?Note: I compressed other chuckles without
> problem.
> [...]
>    SELECT  'set temp_file_limit =-1; SELECT compress_chunk(''' || 
> chunk_schema|| '.' || chunk_name || ''');'  
> [...]
> ERROR:  sequence id overflow
> CONTEXT:  SQL statement "SELECT public.compress_chunk( chunk_rec.oid )"
> PL/pgSQL function 
> _timescaledb_internal.policy_compression_execute(integer,integer,anyelement,integer,boolean,boolean)
>  line 35 at PERFORM
> SQL statement "CALL _timescaledb_internal.policy_compression_execute(
>         job_id, htid, lag_value::INTERVAL,
>         maxchunks, verbose_log, recompress_enabled
>       )"
> PL/pgSQL function _timescaledb_internal.policy_compression(integer,jsonb) 
> line 51 at CALL
> Time: 1113429.153 ms (18:33.429)

This error is coming from timescale, not postgres, so you should open an issue
on their repository instead.

Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?

[Julien Rouhaud]

Hi,

On Wed, Aug 03, 2022 at 07:30:31PM -0500, Ron wrote:
>
> [quote]
> |DEFERRABLE|
> |NOT DEFERRABLE|
>
>This controls whether the constraint can be deferred. A constraint that
>is not deferrable will be checked immediately after every command.
>*Checking of constraints that are deferrable can be postponed until the
>end of the transaction*[/quote]
>
> [/quote]
>
> But yet a |DEFERRABLE| FK constraint in a transaction immediately failed on
> a FK constraint violation.
>
> [quote]
> |INITIALLY IMMEDIATE|
> |INITIALLY DEFERRED|
>
>If a constraint is deferrable, this clause specifies the default time to
>check the constraint. If the constraint is|INITIALLY IMMEDIATE|, it is
>checked after each statement. This is the default. *If the constraint
>is|INITIALLY DEFERRED|**, it is checked only at the end of the
>transaction.*
>
> [/quote]
>
> INITIALLY DEFERRED solved my problem.  Why do both clauses exist?

Because a deferred constraint needs to keep references around in memory until
the constraint is evaluated.  The sooner it's done, the sooner you release that
memory and therefore can avoid, or minimize, memory-related problems.

The typical naive example for INITIALLY IMMEDIATE deferred constraint is a
primary key for which you want to do something like UPDATE ... SET pk = pk + 1

Postponing the evaluation at the end of the UPDATE command is enough, no need
to wait for the end of the transaction.

Re: a database can be created but not droped

[Julien Rouhaud]

Hi,

On Mon, Aug 01, 2022 at 11:22:33AM +0200, Matthias Apitz wrote:
>
> This is with 14.1 on Linux. I have created a new database with
>
> $ createdb -U sisis -T template0 SRP-27097
>
> I can connect to it, created tables and fill them with SQL:
>
> but I can not drop the database:
>
> $ psql -Usisis -dtestdb
> psql (14.1)
> Geben Sie »help« für Hilfe ein.
>
> testdb=# DROP DATABASE IF EXISTS  SRP-27097  WITH FORCE;
> ERROR:  syntax error at or near "-"
> ZEILE 1: DROP DATABASE IF EXISTS  SRP-27097  WITH FORCE;
>
> Why is this?

It's because createdb a command that knows how to quote identifiers and will do
automatically for you.  At the SQL level you have to properly quote identifier,
so this command will work:

DROP DATABASE IF EXISTS "SRP-27097" WITH FORCE;

Re: Feature request: psql --idle

[Julien Rouhaud]

Hi,

On Wed, Jul 27, 2022 at 02:49:45PM +0200, Wiwwo Staff wrote:
> Since changing ph_hda.conf file to give users access involves the restart
> of server, many companies I work(ed) use a bastion host, where users ssh
> to, and are allowed "somehow" use postgresql.

You mean pg_hba.conf right?  It doesn't need a restart, only a reload as
documented at https://www.postgresql.org/docs/current/auth-pg-hba-conf.html:

>The pg_hba.conf file is read on start-up and when the main server process
>receives a SIGHUP signal. If you edit the file on an active system, you will
>need to signal the postmaster (using pg_ctl reload, calling the SQL function
>pg_reload_conf(), or using kill -HUP) to make it re-read the file.

That being said, it's usually not a good idea to allow connection from all
around the world, so not all users may be able to connect from their local
machine anyway.

> What would help, is a --idle option, where psql does not exit, stays idle
> and waits for user to give a \conn command.
> Something similar to
> sqlplus /nolog
> 
> Is anything like that feasible or is there another solution/workaround?

That might be a good thing to have, as some users may want to rely on psql for
things like \h or \? to work on stuff while not being able to connect to a
remote server (and for some reason who wouldn't want to, or maybe couldn't,
install a local instance).  I would call it something like "--no-connection"
more than "--idle" though.

Re: Question about attention to pgsql-hack...@lists.postgresql.org

[Julien Rouhaud]

On Sun, Jun 26, 2022 at 01:22:49PM +0200, Laurenz Albe wrote:
> On Sun, 2022-06-26 at 10:37 +, A Z wrote:
> > I have successfully sent an (updated) email list message to 
> > 
> > pgsql-hack...@lists.postgresql.org
> > 
> > which can be viewed in the archive here:
> > 
> > https://www.postgresql.org/message-id/Rm7WBtZZpYL1NWuunuN_16EY0JcyejqV22z_JlUfvD5FYPenGDd_ZGUddwAcMNi2KNSyvqAhBRzj2JxtJoNmWAzykBQU4Z1AzBp0GPs8wZQ=@protonmail.com
> > 
> > I was wondering if anyone can or will pay some active attention to it?
> > i.e. what can I do from here to raise the message there to the attention 
> > of more people involved with pgsql-hackers, in order to get one or more 
> > active email replies in response to what I have posed/asked?
> 
> It is a long, winding e-mail, suggesting an extension for "high precision 
> arithmetic support".
> It is written in a way so that at least I cannot right away understand what 
> this is supposed
> to be, or how it does anything that "numeric" doesn't.
> 
> At the end of your e-mail, it turns out that you are looking for someone to 
> implement this
> not very well specified extension for you, for free.
> 
> I am not surprised that you got no response.  You'll have to write that 
> extension yourself
> or hire someone to do it for you (but then you should come up with a better 
> specification).

On top of that OP has already received a lot of answers in the past, it's just
that since no one wants to take care of a whole non trivial project for free
the answer is never what OP wants.

So I'm pretty sure that everyone on the list is well aware of what OP wants,
and keeping sending regular messages isn't going to help:

https://www.postgresql.org/message-id/psxp216mb0085760d0fca442a1d4974769a...@psxp216mb0085.korp216.prod.outlook.com
https://www.postgresql.org/message-id/psxp216mb0085b1c0b3e10a1cf3bcd1a09a...@psxp216mb0085.korp216.prod.outlook.com
https://www.postgresql.org/message-id/psxp216mb0085098a2d76e3c5dd4f8ae99a...@psxp216mb0085.korp216.prod.outlook.com
https://www.postgresql.org/message-id/psxp216mb0085f21467c36f05ab9427879a...@psxp216mb0085.korp216.prod.outlook.com
https://www.postgresql.org/message-id/psxp216mb0085ade313f9f48a134057f39a...@psxp216mb0085.korp216.prod.outlook.com
https://www.postgresql.org/message-id/psxp216mb0085d05d015de0c46a11be1f9a...@psxp216mb0085.korp216.prod.outlook.com

I also received 2 private emails for the same project, one of which containing
*a lot* of other people.

Re: No default for (user-specific) service file location on Windows?

[Julien Rouhaud]

On Thu, May 19, 2022 at 06:38:46PM -0400, Tom Lane wrote:
> Julien Rouhaud  writes:
> > This time with the patch.
> 
> Pushed, with some minor twiddling to make the .pgpass and .pg_service.conf
> descriptions more alike.  I figured that the .pgpass docs are fine since
> (surely) many more people have looked at those passages and not
> complained, so I made sure that the service file descriptions were worded
> comparably.

Thanks a lot!

Re: No default for (user-specific) service file location on Windows?

[Julien Rouhaud]

On Thu, May 19, 2022 at 11:53:22PM +0800, Julien Rouhaud wrote:
> On Thu, May 19, 2022 at 10:11:06AM -0400, Tom Lane wrote:
> > I do not like your proposed wording, as it seems way too dense.
> > Can't we avoid the parenthetical remarks (plural) inside a sub-clause?
> > You're asking the reader to keep track of about three levels of
> > interrupt.
> 
> Yes, I was a bit unhappy with this wording.
> 
> > Maybe just put the Windows info in a separate sentence:
> > 
> > ... located at ~/.pg_service.conf.
> > On Microsoft Windows, it is located at
> > %APPDATA%\postgresql\.pg_service.conf, where %APPDATA% means the
> > Application Data subdirectory in the user's profile.
> > In either case, the location can be overridden by setting ...
> > 
> > ISTR there's precedent elsewhere for doing it about like that.
> 
> I'm fine with it.  I still kept the default location full description in both
> places as those end up in quite different part of the documentation, which
> seems more helpful.

This time with the patch.
>From 27f69c2f44564b47a8f0caa31f9406468e9e77a9 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud 
Date: Thu, 19 May 2022 17:42:07 +0800
Subject: [PATCH v2] Document the default location of the PGSERVICEFILE on
 Windows.

Per report from Dominique Devienne.

Author: Julien Rouhaud
Discussion: 
https://postgr.es/m/CAFCRh-_mdLrh8eYVzhRzu4c8bAFEBn=rwohomfjcqotscy5...@mail.gmail.com
---
 doc/src/sgml/libpq.sgml | 18 --
 1 file changed, 12 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 40035d7656..105b1bd94b 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -7787,9 +7787,12 @@ myEventProc(PGEventId evtId, void *evtInfo, void 
*passThrough)
PGSERVICEFILE
   
   PGSERVICEFILE specifies the name of the per-user
-  connection service file.  If not set, it defaults
-  to ~/.pg_service.conf
-  (see ).
+  connection service file (see ).  If not
+  set, it defaults to ~/.pg_service.conf.  On
+  Microsoft Windows, it defaults to
+  %APPDATA%\postgresql\.pg_service.conf (where
+  %APPDATA% refers to the Application Data
+  subdirectory in the user's profile).
  
 
 
@@ -8161,9 +8164,12 @@ myEventProc(PGEventId evtId, void *evtInfo, void 
*passThrough)
   
Service names can be defined in either a per-user service file or a
system-wide file.  If the same service name exists in both the user
-   and the system file, the user file takes precedence.
-   By default, the per-user service file is located
-   at ~/.pg_service.conf; this can be overridden by
+   and the system file, the user file takes precedence.  By default, the
+   per-user service file is located at ~/.pg_service.conf.
+   On Microsoft Windows, it is located at
+   %APPDATA%\postgresql\.pg_service.conf (where
+   %APPDATA% refers to the Application Data subdirectory
+   in the user's profile).  In either case, the location can be overridden by
setting the environment variable PGSERVICEFILE.
The system-wide file is named pg_service.conf.
By default it is sought in the etc directory
-- 
2.33.1

Re: No default for (user-specific) service file location on Windows?

[Julien Rouhaud]

On Thu, May 19, 2022 at 10:11:06AM -0400, Tom Lane wrote:
> I do not like your proposed wording, as it seems way too dense.
> Can't we avoid the parenthetical remarks (plural) inside a sub-clause?
> You're asking the reader to keep track of about three levels of
> interrupt.

Yes, I was a bit unhappy with this wording.

> Maybe just put the Windows info in a separate sentence:
> 
>   ... located at ~/.pg_service.conf.
>   On Microsoft Windows, it is located at
>   %APPDATA%\postgresql\.pg_service.conf, where %APPDATA% means the
>   Application Data subdirectory in the user's profile.
>   In either case, the location can be overridden by setting ...
> 
> ISTR there's precedent elsewhere for doing it about like that.

I'm fine with it.  I still kept the default location full description in both
places as those end up in quite different part of the documentation, which
seems more helpful.

Re: No default for (user-specific) service file location on Windows?

[Julien Rouhaud]

On Thu, May 19, 2022 at 10:57:55AM +0200, Dominique Devienne wrote:
> On Thu, May 19, 2022 at 10:17 AM Julien Rouhaud  wrote:
> > > Or is it instead a lack of symmetry in the implementations of these
> > > two mechanisms?
> >
> > As far as I can see from the code it should be
> > %APPDATA%\postgresql\.pg_service.conf
> >
> > Does this one work?
>
> Yes it does! Thanks.

Ah great!

> > There is a discrepancy (apart from the documentation) as the default
> > file name is the same on Linux and Windows.
>
> The doc is explicit about Windows for the password file,
> probably should be for the service file as well IMHO.

Yeah I totally agree.  I'm attaching a patch for that.

> It's unfortunate that the naming conventions are different.
> Might need to be warned about in the doc too. My $0.02.

I agree that the discrepancy is annoying, but I don't think that we usually add
this kind of warning.  I didn't do anything about it for now.
>
> Could also be changed to try both on Windows, with or w/o the leading dot,
> but I suspect this kind of change won't be considered worth implementing :).

We can't change the default file in older branches obviously, but I'd be a bit
worried about changing the default in the next major version either as it would
add unnecessary pain for users that do know and rely on the current default.  I
guess we have to live with it, and properly document it.
>From 27853c7df7ea03b47e0a7f7b609693f8b0789723 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud 
Date: Thu, 19 May 2022 17:42:07 +0800
Subject: [PATCH v1] Document the default location of the PGSERVICEFILE on
 Windows.

Per report from Dominique Devienne.

Author: Julien Rouhaud
Discussion: 
https://postgr.es/m/CAFCRh-_mdLrh8eYVzhRzu4c8bAFEBn=rwohomfjcqotscy5...@mail.gmail.com
---
 doc/src/sgml/libpq.sgml | 12 +---
 1 file changed, 9 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 40035d7656..758e06e610 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -7788,8 +7788,11 @@ myEventProc(PGEventId evtId, void *evtInfo, void 
*passThrough)
   
   PGSERVICEFILE specifies the name of the per-user
   connection service file.  If not set, it defaults
-  to ~/.pg_service.conf
-  (see ).
+  to ~/.pg_service.conf, or
+  %APPDATA%\postgresql\.pg_service.conf (where
+  %APPDATA% refers to the Application Data
+  subdirectory in the user's profile) on Microsoft Windows (see ).
  
 
 
@@ -8163,7 +8166,10 @@ myEventProc(PGEventId evtId, void *evtInfo, void 
*passThrough)
system-wide file.  If the same service name exists in both the user
and the system file, the user file takes precedence.
By default, the per-user service file is located
-   at ~/.pg_service.conf; this can be overridden by
+   at ~/.pg_service.conf, or
+   %APPDATA%\postgresql\.pg_service.conf (where
+   %APPDATA% refers to the Application Data subdirectory
+   in the user's profile) on Microsoft Windows; this can be overridden by
setting the environment variable PGSERVICEFILE.
The system-wide file is named pg_service.conf.
By default it is sought in the etc directory
-- 
2.33.1

Re: No default for (user-specific) service file location on Windows?

[Julien Rouhaud]

Hi,

On Thu, May 19, 2022 at 3:46 PM Dominique Devienne  wrote:
>
> The doc is explicit about defaults for the password file:
> From https://www.postgresql.org/docs/current/libpq-pgpass.html
> Linux: ~/.pgpass
> Windows: %APPDATA%\postgresql\pgpass.conf
>
> But for the service file OTOH, only the Linux default is documented:
> From: https://www.postgresql.org/docs/current/libpq-pgservice.html
> Linux: ~/.pg_service.conf
> Windows: N/A
>
> I tried the obvious %APPDATA%\postgresql\pg_service.conf, with no luck.
>
> So is this a lack in the documentation for the service file?
> Or is it instead a lack of symmetry in the implementations of these
> two mechanisms?
>
> If the latter, any chance the symmetry would be restored? (with
> corresponding doc update).

As far as I can see from the code it should be
%APPDATA%\postgresql\.pg_service.conf

Does this one work?

There is a discrepancy (apart from the documentation) as the default
file name is the same on Linux and Windows.

Re: Unexpected result from ALTER FUNCTION— looks like a bug

[Julien Rouhaud]

On Tue, Apr 19, 2022 at 11:06:30PM -0400, Tom Lane wrote:
> Julien Rouhaud  writes:
> > On Wed, Apr 20, 2022 at 10:47:07AM +0800, Julien Rouhaud wrote:
> >> 
> >> AFAICT the problem is that SET / RESET part is messing with the
> >> HeapTuple, so you can't use the procForm reference afterwards.  Simply
> >> processing parallel_item before set_items fixes the problem, as in the
> >> attached.
> 
> > This time with the file.
> 
> Yeah, I arrived at the same fix.  Another possibility would be to
> make the procForm pointer valid again after heap_modify_tuple,
> but that seemed like it'd add more code for no really good reason.

Yeah I agree.  The comment you added seems enough as a future-proof security.

Re: Unexpected result from ALTER FUNCTION— looks like a bug

[Julien Rouhaud]

On Wed, Apr 20, 2022 at 10:47:07AM +0800, Julien Rouhaud wrote:
> 
> AFAICT the problem is that SET / RESET part is messing with the HeapTuple, so
> you can't use the procForm reference afterwards.  Simply processing
> parallel_item before set_items fixes the problem, as in the attached.

This time with the file.
diff --git a/src/backend/commands/functioncmds.c 
b/src/backend/commands/functioncmds.c
index 91f02a7eb2..c227fbde19 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -1472,6 +1472,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
 
procForm->prosupport = newsupport;
}
+   if (parallel_item)
+   procForm->proparallel = interpret_func_parallel(parallel_item);
if (set_items)
{
Datum   datum;
@@ -1506,8 +1508,6 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
tup = heap_modify_tuple(tup, RelationGetDescr(rel),
repl_val, 
repl_null, repl_repl);
}
-   if (parallel_item)
-   procForm->proparallel = interpret_func_parallel(parallel_item);
 
/* Do the update */
CatalogTupleUpdate(rel, >t_self, tup);

Re: Unexpected result from ALTER FUNCTION— looks like a bug

[Julien Rouhaud]

Hi,

On Tue, Apr 19, 2022 at 07:21:19PM -0700, David G. Johnston wrote:
> On Tue, Apr 19, 2022 at 7:07 PM Bryn Llewellyn  wrote:
> 
> > *SUMMARY*
> >
> > This part of the syntax diagram for "alter function":
> >
> > *ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
> > action [ … ]*
> >
> > says that the first "action" can be followed (without punctuation) by
> > zero, one, or many other actions. A semantic rule says that no particular
> > action can be specified more than once. My tests used these possible
> > actions:
> >
> > *alter function s1.f()security invokerset timezone = 'UTC'stable*
> > *parallel safe*
> > *;*
> >
> > It brings this new status:
> >
> >
> >
> > * name | type | security |proconfig
> > | volatility
> > |  parallel  
> > --+--+--+-++
> >  f
> >| func | invoker  | {TimeZone=UTC}
> >| stable | restricted*
> >
> > This is the bug.
> >
> 
> It has room for improvement from a user experience perspective.
> 
> While I haven't experimented with this for confirmation, what you are
> proposing here (set + parallel safe) is an impossible runtime combination
> (semantic rule) but perfectly valid to write syntactically.  Your function
> must either be restricted or unsafe per the rules for specifying parallel
> mode.
> 
> If this is indeed what is happening then the documentation should make note
> of it.  Whether the server should emit a notice or warning in this
> situation is less clear.  I'm doubting we would introduce an error at this
> point but probably should have when parallelism was first added to the
> system.

That's not the problem here though, as you can still end up with the wanted
result using 2 queries.  Also, the PARALLEL part is entirely ignored, so if you
wanted to mark the function as PARALLEL UNSAFE because you're also doing a SET
that would make it incompatible it would also be ignored, same if you use a
RESET clause.

AFAICT the problem is that SET / RESET part is messing with the HeapTuple, so
you can't use the procForm reference afterwards.  Simply processing
parallel_item before set_items fixes the problem, as in the attached.

Re: primary_conninfo and restore_command ?

[Julien Rouhaud]

Hi,

On Thu, Mar 10, 2022 at 02:51:16PM +0100, Luca Ferrari wrote:
> a friend of mine has shown to me a "strange" configuration of its
> physical replication server (13): he has both primary_conninfo and
> primary_slot_name, with replication slots active when queried on the
> master. So far so good, but in the configuration he has also
> restore_command to restore archived WALs from a centralized location.
> Does this make sense?
> Because if the replica cannot connect to the master, it will not start
> at all (so I guess no restore_command will be executed). On the other
> hand if the replica can connect to the primary the WALs will be
> shipped by means of streaming.
> Am I missing something?

We do have max_slot_wal_keep_size, and otherwise users could drop/create the
slot while the standby is shutdown if the situation is really bad, so it seems
sensible to me.

Re: Simple Query Doesn't Even with Data

[Julien Rouhaud]

Hi,

On Wed, Mar 09, 2022 at 08:40:45PM -0500, Scott Macri wrote:
>
> If I do a select * without a where clause I get the expected results.
> However, when I execute the following select statement I was astonished
> to see no results come back.
>
> SELECT *
> FROM public.map_table
> WHERE company_letters = 'abcdefg';

It looks like an index corruption, a REINDEX of that table should fix the
problem.

Did you update your system recently, which
may have updated your libc/libicu version, or replicated data over different OS
version (which could lead to the same problem)?  If yes, it's a known problem
and you have to reindex all indexes that uses collatable datatypes afterwards.

You can look at https://wiki.postgresql.org/wiki/Locale_data_changes for more
details about that problem.

Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

[Julien Rouhaud]

On Sun, Feb 27, 2022 at 12:04:19AM +0530, Techsupport wrote:
> Hi,
>
> Thanks for your reply.
> Is it a good practice to upgrade / update database on production on every
> year ?

It's a good practice to update all the software you rely on each time a minor
version is released, especially if those contains security fixes.  For postgres
new minor version are released at least every 3 months, and sometimes contain
security fixes.

> In my log
>  > FATAL:  the database system is starting up
>  Log showing continuously, other than that there is not PANIC log in the log
> file, which is under the log folder.

There has to be something else in your logs.  Your system can't go from normal
processing to "the database system is starting up" without showing any evidence
of some kind of problem.  Maybe in the event logs if it's not in your postgres
logs?

Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

[Julien Rouhaud]

Hi,

On Sat, Feb 26, 2022 at 07:51:45PM +0530, Techsupport wrote:
> Hi to all,
>
> We are using PostgreSQL 12.3, it is running on the Windows Server.

So, you're using a version released almost 2 years ago, missing 8 minor
releases worth of bug fixes.  In general, if you have a problem you should just
blindly update to the latest minor version, otherwise it's highly unlikely that
anyone will look at your problem.

That being said...
>
> Database data directory is configured in the network shared path.
> Unexpectedly it was disconnected and up again.
>
> PgAgent is not connected, when I check the Log, it showing
>
> FATAL:  the database system is starting up
>
> FATAL:  the database system is starting up
>
> ...
>
> Other than that, there is no other errors available.

Really?  This error is displayed if the server is doing some automatic recovery
after an unclean shutdown.  Could be an unrecoverable error like IO error on
the WALs, or just anything fixed in the last 2 years.

You should check for any message with PANIC/FATAL log level too.

> For nearly 7 hours database is not connected, after that it's getting
> connected. Why it is taking too long time to up. Totally our database has 3.5
> TB of data.

Was it 7 uninterrupted hours or did your database keeps being stopped in an
unclean way for 7 hours?  More details from the logs would help, you could also
see https://wiki.postgresql.org/wiki/Guide_to_reporting_problems.  But above
all you should consider updating to the latest minor version as soon as
possible.

Re: Additional accessors via the Extension API ?

[Julien Rouhaud]

On Sun, Feb 20, 2022 at 12:31:22PM +0200, Markur Sens wrote:
> >
> > Maybe you could rely on some old grammar hack to have something a bit 
> > similar,
> > as (expr).funcname is an alias for funcname(expr).  For instance:
> 
> Is this documented & expected behavior or it’s just happens to work?

I don't think it's documented but it's an expected behavior, see

https://github.com/postgres/postgres/blob/master/src/backend/parser/parse_func.c#L57-L88

/*
 *  Parse a function call
 *
 *  For historical reasons, Postgres tries to treat the notations tab.col
 *  and col(tab) as equivalent: if a single-argument function call has an
 *  argument of complex type and the (unqualified) function name matches
 *  any attribute of the type, we can interpret it as a column projection.
 *  Conversely a function of a single complex-type argument can be written
 *  like a column reference, allowing functions to act like computed 
columns.
 *
 *  If both interpretations are possible, we prefer the one matching the
 *  syntactic form, but otherwise the form does not matter.
 *
 *  Hence, both cases come through here.  If fn is null, we're dealing with
 *  column syntax not function syntax.  In the function-syntax case,
 *  the FuncCall struct is needed to carry various decoration that applies
 *  to aggregate and window functions.
[...]

Re: Additional accessors via the Extension API ?

[Julien Rouhaud]

Hi,

On Sun, Feb 20, 2022 at 08:07:20AM +0200, Markur Sens wrote:
> Suppose  I have defined an additional type in a PG extension.
>
> Is it possible to add custom accessors to that type -much like jsonb does-
> but use an API/hook without touching the core PG grammar & parser?

Unfortunately no.

> Hypothetical Examples:
>
> Assuming I have a TextFile type I’d like to implement syntax like:
>
> (‘/home/me/a.txt’::TextFile).firstline
> (‘/home/me/a.txt’::TextFile).lastline
> (‘/home/me/a.txt’::TextFile).countlines()
> (‘/home/me/a.txt’::TextFile).size()
> (‘/home/me/a.txt’::TextFile).datemodified()

Maybe you could rely on some old grammar hack to have something a bit similar,
as (expr).funcname is an alias for funcname(expr).  For instance:

# create function f1(int) returns text as $$
begin
return 'val: ' || $1::text;
end;
$$ language plpgsql;

# create table t as select 1 as id;

# select (5).f1, (id).f1 from t;
   f1   |   f1
+
 val: 5 | val: 1
(1 row)

I don't know if that would be enough for you needs.  Otherwise, the only option
would be tocreate an operator instead, like mytype -> 'myaccessor' or something
like that.

Re: Question on Open PostgreSQL Monitoring

[Julien Rouhaud]

Hi,

On Thu, Feb 17, 2022 at 03:52:54PM +, Lu, Dan wrote:
> 
> I am not sure if this is the right email group to ask about Open PostgreSQL
> Monitoring (https://opm.readthedocs.io/opm-core/index.html).

The correct place to ask question is one the github repositories, per
https://opm.readthedocs.io/developers/development_information.html.

> It appears there is no update to this tool since at least 2019.

The extensions and UI didn't have update since 2020, and that's because there
was no need.  Those are responsible for storing whatever perfdata is generated
by Nagios or similar and graphing them.  All the postgres-specific logic is in
the probe (https://github.com/OPMDG/check_pgactivity) which is actively
developed.

That being said, you can use a different UI on top of the extension if you
want, and multiple people had success building grafana dashboards quite easily.

> Since members from this email group has lots of expertise on PostgreSQL, I
> thought I ask if anyone knows of a better tool to replace OPM for PG?

A lot of tools are gathered at https://wiki.postgresql.org/wiki/Monitoring.
There isn't a single killer tool that's better than everything else, and you
should probably pick something that can easily be integrated in whatever
monitoring / alerting solution you're already using.

Re: PostgreSQL extensions during switchover

[Julien Rouhaud]

Hi,

On Mon, Feb 14, 2022 at 11:16:24AM +0100, Marian Pompura wrote:
> 
> Let's say:
> 
> postgresql.conf configuration file on primary server:
> 
> shared_preload_libraries = 'pg_stat_statements,'
> 
> *Contrib package is installed on primary and replication server too.
> 
> postgresql.conf configuratioj file on replication server
> 
> shared_preload_libraries = '' is empty on replication server.
> 
> In case some scenario which will include switchover or failover to
> replication server.
> How it will act ? Switchover will be successful but new primary node will
> fail at startup process because shared_preload_libraries is empty but
> extensions are created in database? Or it's better to pre-configure same
> extensions into shared_preload_libraries parameter on replication server?

Having some extension installed without the required shared_preload_libraries
setting won't prevent postgres from starting.  The only thing that won't work
is using at least part of what the extension created on the database.

So the question is whether you want to make sure you have the same feature
available after a switchover, and if you do then yes you should make sure that
you have a consistent configuration across all your nodes.

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Julien Rouhaud]

On Fri, Feb 11, 2022 at 09:07:16PM -0800, Adrian Klaver wrote:
> On 2/11/22 17:24, Bryn Llewellyn wrote:
> > > > /b...@yugabyte.com  wrote:/
> 
> > I s'pose that I can interpret this output in the light of the "miriam"
> > example by guessing than an empty LHS means "public" and that the
> > initial "X" means "execute". It looks like what follows the slash is the
> > owner of the object (a denormalization of what the "Owner" column
> > shows.)
> > 
> > *Where is this notation, "miriam=arwdDxt/miriam", explained?*
> 
> Here:
> https://www.postgresql.org/docs/current/ddl-priv.html
> 
> From:
> 
> Table 5.1. ACL Privilege Abbreviations

You might also be interested in aclexplode() function, see
https://www.postgresql.org/docs/current/functions-info.html.

Re: max_connections different between primary and standby: is it possible?

[Julien Rouhaud]

On Thu, Feb 03, 2022 at 05:39:57PM +0530, Bharath Rupireddy wrote:
> 
> Agree that the standby should atleast have the capacity that the
> primary has in terms of resources. But what I don't like about that
> code is calling RecoveryRequiresIntParameter for each parameter
> separately and crashing the server FATALly for each insufficient
> parameter. Imagine if all the parameters were set to insufficient
> values on the standby and users keep setting the reported parameter to
> the right value and restart the server. At max, 5 FATAL failure-set
> right value-restart have to be performed. Instead, it would be better
> if the server emits a single log with all the insufficient
> parameters(max_connections, max_worker_processes, max_wal_senders,
> max_prepared_transactions and max_locks_per_transaction) values and
> crashes FATALly. The users can look at the logs at once, set all the

Sure, but one failed start / inspect logs / modify configuration / start will
always by longer than just reading the docs and making sure that the
configuration is appropriate.  It also won't help if you want to modify the
settings on your primary and make sure that you won't have an incident on your
HA setup.

Re: max_connections different between primary and standby: is it possible?

[Julien Rouhaud]

Hi,

On Thu, Feb 03, 2022 at 10:36:37AM +0100, Luca Ferrari wrote:
> Hi all,
> running PostgreSQL 14, physical replication with slot, after changing
> (increasing) the max_connections on the primary, I had this message at
> a restart from the standby:
> 
> DETAIL:  max_connections = 100 is a lower setting than on the primary
> server, where its value was 300.
> 
> and the standby does not start until I raise also its max_connections.
> Why is PostgreSQL requiring the max_connections to be aligned between
> the primary and the standby?

The value needs to be at least equal as the value on the primary node, but it
can be bigger.

That's because the standby needs to have enough resources to replay the
activity from the primary, including some heavyweight locks acquisition, and
the number of locks you can hold at one time is partially based on
max_connections.

Re: what is the solution like oracle DB's datafile

[Julien Rouhaud]

On Sun, Jan 30, 2022 at 08:51:02PM +0700, Yudianto Prasetyo wrote:
> 
> thanks for other solutions in the operating system section. LVM, RAID is
> indeed one solution to this problem.
> 
> Maybe there is another solution in the postgresql database like the
> datafile in oracle DB?

As I said I don't know how datafiles in oracle are working.  All you have on
postgres is tablespaces, default tablespaces and moving relations from one
tablespaces to another.

In general, postgres doesn't try to reimplement solution to problems that are
nicely solved at the operating system level, so if those datafile are
reimplementing something similar to LVM, then no postgres doesn't have
something like that and probably doesn't want it.

Re: what is the solution like oracle DB's datafile

[Julien Rouhaud]

I already asked you once to keep the list in copy.  Don't expect me to reply if
your next email is still addressed to me only.

On Sun, Jan 30, 2022 at 08:36:50PM +0700, Yudianto Prasetyo wrote:
> 
> yes of course it can be done. but it is very inconvenient when the database
> is still running and most importantly it is not practical when having to
> move some objects to another tablespace. And this will also happen again
> when HDD 1 will be full again with data entry by other objects on HDD 1.

Then fix the problem at the operating system level.  On GNU/Linux you can use
for instance LVM to get more flexibility.  With it you can easily increase your
logical volume space without any interruption, and the database will only see a
regular disk that magically became bigger.  There is probably something similar
on the operating system you're using.  Of course, if you don't already use
something like that, you will need some maintenance window to move all data on
logical volumes.

Re: what is the solution like oracle DB's datafile

[Julien Rouhaud]

Hi,

Please keep the list in copy and don't top post here:
https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics

On Sun, Jan 30, 2022 at 08:18:15PM +0700, Yudianto Prasetyo wrote:
> 
> what is the function of adding a new tablespace if we will only fill data
> in table color and HDD1 is full? can't we fill the data in table color
> because it still refers to the old tablespace ( small_data on HDD 1 not in
> the new tablespace on HDD 2 )

You can move some of the tables and/or indexes on the new tablespace to free
some space on the old one, see e.g.:

- ALTER TABLE ... SET TABLESPACE: 
https://www.postgresql.org/docs/current/sql-altertable.html
- ALTER INDEX ... SET TABLESPACE: 
https://www.postgresql.org/docs/current/sql-alterindex.html

Re: what is the solution like oracle DB's datafile

[Julien Rouhaud]

Hi,

On Sun, Jan 30, 2022 at 05:15:33AM +0700, Yudianto Prasetyo wrote:
> 
> I'm confused when I have 2 HDD. HDD 1 is used to install the OS and
> postgresql database. when HDD 1 is full. how to increase the capacity of
> postgresql database with HDD 2 (without RAID system)?
> 
> is there any other way like oracle DB's "add datafile" which can be used to
> add capacity to another HDD?

I don't know how those datafiles are working, but with postgres the solution is
to create additional tablespaces pointing to the new drives, see
https://www.postgresql.org/docs/current/sql-createtablespace.html.

Note that a single non-partitioned table can only exist on a single tablespace,
so if you have a gigantic table that's becoming bigger than you disk, the
solution might be to partition it and store different partitions on different
tablespaces.

Re: SELECT with LIKE clause makes full table scan

[Julien Rouhaud]

Hi,

On Wed, Jan 26, 2022 at 11:07 PM Matthias Apitz  wrote:
>
> We changed two relevant Indexes to
>
> CREATE INDEX d01ort ON d01buch(d01ort bpchar_pattern_ops );
> CREATE INDEX d01ort2 ON d01buch(d01ort2 bpchar_pattern_ops );

When you said changed, did you drop the previous ones?  As Tom
mentioned, those indexes are specialized and are only useful for LIKE
'something%' queries.  It's quite likely that your existing indexes
were useful for other queries, which may not be as fast without those
indexes.  You can check in pg_stat_user_indexes if your indexes seems
to be used before actually dropping them for instance:
https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW

Re: SELECT with LIKE clause makes full table scan

[Julien Rouhaud]

Hi,

On Wed, Jan 26, 2022 at 02:34:21PM +0100, Dominique Devienne wrote:
> On Wed, Jan 26, 2022 at 1:13 PM Julien Rouhaud  wrote:
> > > > > Why is this (ignoring the Index) and what could be done?
> > > [...]
> > > create INDEX d01ort on d01buch(d01ort)  ;/*  D01ORT*/
> >
> > That index can't be used with a LIKE that has a trailing wildcard.
> 
> Really? That seems to contradict the doc, i.e. default index type is B-Tree,
> which definitely supports trailing wildcard LIKE-predicates, as
> explicitly stated in said doc:
> https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPES-BTREE
> 
> So what makes you say that? --DD

This part of the documentation you mentioned:

> However, if your database does not use the C locale you will need to create
> the index with a special operator class to support indexing of
> pattern-matching queries; see Section 11.10 below.
> 
> PS: That also contradicts the small demo I made earlier up-thread:

relname datatype is name, which has a default C collation, so you are in the
only case that natively work for btree indexes:

# select unnest(indcollation)::regcollation from pg_index where indexrelid = 
'pg_class_relname_nsp_index'::regclass;
 unnest

 "C"
 -
(2 rows)

I highly doubt that OP tables are also using C collation, so almost no one
does that.

Re: SELECT with LIKE clause makes full table scan

[Julien Rouhaud]

Hi,

On Wed, Jan 26, 2022 at 12:39:25PM +0100, Matthias Apitz wrote:
> 
> > > sisis=# explain (analyze, buffers) select * from d01buch where d01ort 
> > > like 'Z 9610%' ;
> > > QUERY PLAN
> > > ---
> > > Gather (cost=1000.00..680262.71 rows=510 width=952) (actual 
> > > time=1324.096..1349.429 rows=1 loops=1)
> > > Workers Planned: 4
> > > Workers Launched: 4
> > > Buffers: shared hit=102040 read=560674
> > > -> Parallel Seq Scan on d01buch (cost=0.00..679211.71 rows=128 width=952) 
> > > (actual time=1117.663..1315.062 rows=0 loops=5)
> > > Filter: (d01ort ~~ 'Z 9610%'::text)
> > > Rows Removed by Filter: 1055853
> > > Buffers: shared hit=102040 read=560674
> > > Planning Time: 2.028 ms
> > > Execution Time: 1349.593 ms
> > > (10 Zeilen)
> > >
> > > Why is this (ignoring the Index) and what could be done?
> [...]
> create INDEX d01ort on d01buch(d01ort)  ;/*  D01ORT*/

That index can't be used with a LIKE that has a trailing wildcard.  You need to
either create an index with text_pattern_ops operator class (see
https://www.postgresql.org/docs/current/indexes-opclass.html), or a GIN index
using pg_trgm (which will also work with non-trailing wildcards), see
https://www.postgresql.org/docs/current/pgtrgm.html.

Re: GIN index

[Julien Rouhaud]

Hi,

On Tue, Jan 25, 2022 at 02:42:14AM +, huangning...@yahoo.com wrote:
> Hi:I created a new variable-length data type, and now I want to create a GIN
> index for it. According to the rules of GIN index, I created three functions:
> extractValue, extractQuery, and compare. I made sure that the return value of
> the first two functions is the address of the array, but when using the index
> query, the GIN tuple data obtained by calling PG_GETARG_DATUM in the compare
> function is incorrect, and it is misplaced! In memory the size of the data
> header becomes something else, and the position of the first byte is not the
> header, it becomes the fourth byte. So there is a high probability that the
> function called is wrong or my return value is wrong when creating the index
> or the error is somewhere else?

It's hard to have an opinion without seeing any code extract.  Have you checked
contrib module for examples of other GIN opclasses, like pg_trgrm, or hstore
for varlena datatype with GIN support?

Note also that pgsql-hackers is probably a better mailing list for this kind of
questions.

Re: [Extern] Re: postgres event trigger workaround

[Julien Rouhaud]

Hi,

On Sat, Jan 15, 2022 at 08:36:21AM +0500, Дмитрий Иванов wrote:
> In my solution, all users don't need direct access to the schema because
> you have to use the functional API to access it. If you can manage users
> with functions, you can close the schema in the same way.
> Usually the function is executed with the permissions of the calling user,
> which requires permissions for all affected entities. However, if you
> specify the "SECURITY DEFINER" parameter at creation, the function will be
> executed with the owner's permissions. The owner of the function has no
> login permissions but has permissions on the affected entities. In this way
> you will close the schema from the roles that have rights to the role
> management functions.

Sure you can solve most problems with that.  But you can't create a database
(or a tablespace) from a function so this approach wouldn't cover all of OP's
needs, as different approach would be needed for role and db creation.