Re: pg_stat_statements
On Wed, 12 Jan 2022 at 03:03, Julien Rouhaud wrote: > > Hi, > > On Tue, Jan 11, 2022 at 03:04:14PM +, Dirschel, Steve wrote: > > > > I'm not sure if this is the correct distribution list for this type of > > question but I'll try anyways. We have an app that uses multiple schemas. > > It will do a set schema 'schema_name' and execute queries. The queries > > executed are the same regardless of the schema the connection set. > > > > In pg_stat_statements the exact same query will get a different queryid for > > each schema that executes the query. > > > > I'm unable to determine which queryid comes from which schema the query was > > executed under. Is anyone aware of a way to determine this? > > > > Unfortunately this is a known limitation. I see this as a beneficial feature. If the same SQL is executed against different sets of tables, each with different indexes, probably different data, the performance could vary dramatically and might need different tuning on each. So having separate rows in the pg_stat_statements output makes sense. > There were some previous discussions (e.g. [1] and [2] more recently), but I > don't think there was a real consensus on how to solve that problem. To differentiate, run each schema using a different user, so you can tell them apart. -- Simon Riggshttp://www.EnterpriseDB.com/
Re: pg_stat_statements
On Wed, Jan 12, 2022 at 10:22:38AM +, Simon Riggs wrote: > On Wed, 12 Jan 2022 at 03:03, Julien Rouhaud wrote: > > > > Unfortunately this is a known limitation. > > I see this as a beneficial feature. > > If the same SQL is executed against different sets of tables, each > with different indexes, probably different data, the performance could > vary dramatically and might need different tuning on each. So having > separate rows in the pg_stat_statements output makes sense. Yes, having different rows seems like a good thing. But being unable to tell which row apply to which schema is *not* a good thing. > > There were some previous discussions (e.g. [1] and [2] more recently), but I > > don't think there was a real consensus on how to solve that problem. > > To differentiate, run each schema using a different user, so you can > tell them apart. This isn't always possible. For instance, once you reach enough schema it will be problematic to do proper pooling.
How to read an external pdf file from postgres?
Hi; I want to read an external pdf file from postgres. pdf file will exist on the disk. postgres only know the disk full path as metadata. Is there any software or extension that can be used for this? Or do we have to develop software for it? Or what is the best approach for this? I'd appreciate it if anyone with experience could make suggestions. Thanks.
Re: How to read an external pdf file from postgres?
On 12.01.22 12:16, Amine Tengilimoglu wrote: I want to read an external pdf file from postgres. pdf file will exist on the disk. postgres only know the disk full path as metadata. Is there any software or extension that can be used for this? Or do we have to develop software for it? Or what is the best approach for this? I'd appreciate it if anyone with experience could make suggestions. You could write a function in PL/Perl or PL/Python to open and read the file and process the PDF data, using some third-party module that surely exists somewhere.
postgres event trigger workaround
Hi, PG event triggers are not firing on CREATE ROLE, CREATE DATABASE, CREATE TABLESPACE by definition (would be nice if they do). Is there any workaround to react with ddl_command_start behavior on such an event? Thanks, Markus
WAL Archiving and base backup
Hello all, I've been upgrading some of our PostgreSQL instances from 9.x to 12.x and we're using a master/slave setup based on continuous archiving. I can read on https://www.postgresql.org/docs/12/continuous-archiving.html 25.3.3.2. Making an Exclusive Low-Level Backup that the exclusive method is deprecated. But then this leads me to the following problem: - Non-Exclusive Low-Level Backup requires that the functions pg_start_backup() and pg_stop_backup() be called in the *same* connection to the database - we use scripts to automate the creation of the backup and it's deployment on the slave, after a server maintenance for example - the example given in 25.3.6.1. Standalone Hot Backups still uses the exclusive (deprecated) method So I have this question, how to script the making of base backup for transfer on the slave server when the two SQL functions must be called in the same connection, in Bash for example; is this doable? Thanks for any pointers. Regards -- Issa
Re: postgres event trigger workaround
Hi, On Wed, Jan 12, 2022 at 11:57:45AM +, Zwettler Markus (OIZ) wrote: > > PG event triggers are not firing on CREATE ROLE, CREATE DATABASE, CREATE > TABLESPACE by definition (would be nice if they do). > > Is there any workaround to react with ddl_command_start behavior on such an > event? That's not possible. The limitation exists because those objects are shared objects and therefore could be created from any database in the cluster. What is your use case? Maybe you could rely on logging all DDL instead for instance.
Re: pg_stat_statements
On Wed, 12 Jan 2022 at 10:31, Julien Rouhaud wrote: > > On Wed, Jan 12, 2022 at 10:22:38AM +, Simon Riggs wrote: > > On Wed, 12 Jan 2022 at 03:03, Julien Rouhaud wrote: > > > > > > Unfortunately this is a known limitation. > > > > I see this as a beneficial feature. > > > > If the same SQL is executed against different sets of tables, each > > with different indexes, probably different data, the performance could > > vary dramatically and might need different tuning on each. So having > > separate rows in the pg_stat_statements output makes sense. > > Yes, having different rows seems like a good thing. But being unable to tell > which row apply to which schema is *not* a good thing. > > > > There were some previous discussions (e.g. [1] and [2] more recently), > > > but I > > > don't think there was a real consensus on how to solve that problem. > > > > To differentiate, run each schema using a different user, so you can > > tell them apart. > > This isn't always possible. For instance, once you reach enough schema it > will > be problematic to do proper pooling. True, perhaps we should fix SET SESSION AUTHORIZATION to be allowed by non-superusers. Then set the user and search_path at same time. I was going to suggest adding a comment to the front of each SQL that contains the schema, but that doesn't work either (and looks like a bug, but how normalization works is not documented). -- Simon Riggshttp://www.EnterpriseDB.com/
Unable to migrate from postgres-13 to 14
Hi,
I am trying to migrate my database from postgres-13 to 14 but getting
following error:
pg_restore: error: could not execute query: ERROR: function
array_append(anyarray, anyelement) does not exist
Command was: CREATE AGGREGATE "public"."mode"("anyelement") (
SFUNC = "array_append",
STYPE = "anyarray",
INITCOND = '{}',
FINALFUNC = "public"."_final_mode"
);
To work around this problem I decided to drop this aggregate in postgres-13
and create it again with the supported version of array_append in
postgres-14.
But when I tried to drop it in postgres-13 I got following error:
#drop aggregate mode("anyelement") CASCADE;
ERROR: cannot drop function mode(anyelement) because it is required by the
database system
I don't understand why the database system is using my user defined
aggregate. If it is a system defined aggregate then it should not be copied
from postgres-13 to postgres-14. It should be there in postgres-14.
Please suggest some work around.
Thanks,
Jasvant
Re: How to read an external pdf file from postgres?
What are you going to do with the data? If you want to analyze it in some way, I can't think of a better option with a Python function. Or do you just want to transfer them? There are options here too, but in this case I like Python better. -- Regards, Dmitry! ср, 12 янв. 2022 г. в 16:16, Amine Tengilimoglu : > Hi; > > I want to read an external pdf file from postgres. pdf file will > exist on the disk. postgres only know the disk full path as metadata. Is > there any software or extension that can be used for this? Or do we have to > develop software for it? Or what is the best approach for this? I'd > appreciate it if anyone with experience could make suggestions. > > Thanks. >
Re: How to read an external pdf file from postgres?
2022年1月12日(水) 20:16 Amine Tengilimoglu : > > Hi; > > I want to read an external pdf file from postgres. pdf file will exist > on the disk. postgres only know the disk full path as metadata. Is there any > software or extension that can be used for this? Or do we have to develop > software for it? Or what is the best approach for this? I'd appreciate it if > anyone with experience could make suggestions. By "read" do you mean "open the file and meaningful extract data from it"? If so, speaking from prior experience, don't. And if you really have to, make sure the source PDF is guaranteed to be in a well-defined, predictable format enforceable by contract law and/or people with sharp pointy sticks. I have successfully suppressed the memories of whatever it is I once had to do with reading data from PDFs, but though the data was eventually imported into PostgreSQL, there was a lot of mangling probably involving a Perl module (other languages are probably available) before it got anywhere near the database. Reagrds Ian Barwick -- EnterpriseDB: https://www.enterprisedb.com
Re: pg_stat_statements
On Wed, 12 Jan 2022 at 13:17, Simon Riggs wrote: > > On Wed, 12 Jan 2022 at 10:31, Julien Rouhaud wrote: > > > > On Wed, Jan 12, 2022 at 10:22:38AM +, Simon Riggs wrote: > > > On Wed, 12 Jan 2022 at 03:03, Julien Rouhaud wrote: > > > > > > > > Unfortunately this is a known limitation. > > > > > > I see this as a beneficial feature. > > > > > > If the same SQL is executed against different sets of tables, each > > > with different indexes, probably different data, the performance could > > > vary dramatically and might need different tuning on each. So having > > > separate rows in the pg_stat_statements output makes sense. > > > > Yes, having different rows seems like a good thing. But being unable to > > tell > > which row apply to which schema is *not* a good thing. > > > > > > There were some previous discussions (e.g. [1] and [2] more recently), > > > > but I > > > > don't think there was a real consensus on how to solve that problem. > > > > > > To differentiate, run each schema using a different user, so you can > > > tell them apart. > > > > This isn't always possible. For instance, once you reach enough schema it > > will > > be problematic to do proper pooling. > > True, perhaps we should fix SET SESSION AUTHORIZATION to be allowed by > non-superusers. Then set the user and search_path at same time. But SET ROLE works. -- Simon Riggshttp://www.EnterpriseDB.com/
Re: Unable to migrate from postgres-13 to 14
Jasvant Singh writes:
> pg_restore: error: could not execute query: ERROR: function
> array_append(anyarray, anyelement) does not exist
> Command was: CREATE AGGREGATE "public"."mode"("anyelement") (
> SFUNC = "array_append",
> STYPE = "anyarray",
> INITCOND = '{}',
> FINALFUNC = "public"."_final_mode"
> );
> To work around this problem I decided to drop this aggregate in postgres-13
> and create it again with the supported version of array_append in
> postgres-14.
> But when I tried to drop it in postgres-13 I got following error:
> #drop aggregate mode("anyelement") CASCADE;
> ERROR: cannot drop function mode(anyelement) because it is required by the
> database system
I think this is trying to drop the built-in pg_catalog.mode(anyelement)
aggregate. Say DROP AGGREGATE public.mode("anyelement")
(I'd be pretty cautious about using CASCADE, too, at least till
you've seen what would get dropped.)
regards, tom lane
Re: WAL Archiving and base backup
On Wed, Jan 12, 2022 at 1:22 PM Issa Gorissen wrote:
> So I have this question, how to script the making of base backup for
> transfer on the slave server when the two SQL functions must be called
> in the same connection, in Bash for example; is this doable?
>
Not sure if I understand the problem correctly but if you are asking how to
open a connection to the database and keep it open while doing something
else in bash, then you could use "coproc" for instance. I sometimes use
this function:
coproc psql -XAtF $'\t' service="$srv"
pg () {
local sql exp
sql="$1"
[ "$2" ] && { sql="$2"; exp="$1"; }
echo "$sql" >&${COPROC[1]}
read -u ${COPROC[0]} || return
[ "$exp" ] || return 0
[ "$REPLY" = "$exp" ] || return 64
return 0
}
And here is some usage
local TMOUT=1
pg BEGIN 'BEGIN;'
This sends a BEGIN command and expects the word BEGIN as reply.
if pg 'LOCK TABLE' '
LOCK TABLE some_table
IN SHARE ROW EXCLUSIVE MODE NOWAIT;
';
then
:
elif (( $? > 128 )); then # read timeout exceeded
die "Cannot lock some_table";
else
die "Unexpected error while locking some_table";
fi
In the example above a table is locked with NOWAIT. Bash's read timeout is
set to 1sec. If that's exceeded because the lock is not obtained, read
comes back with status>128.
unset TMOUT
pg '
SELECT coalesce(min(id), -1)
, coalesce(max(id), -1)
FROM some_table'\;
Now we want to read some data. So, TMOUT is unset. The REPLY variable will
have the answer.
IFS=$'\t' read mn mx <<<"$REPLY"
And this is how to split the reply into 2 bash variables, mn and mx.
At the end of the transaction then
pg 'COMMIT' 'COMMIT;'
And send \q to finish psql. If "set -e" mode is active, make sure to negate
the result.
# expecting read to fail after \q. Hence the negation.
! pg '\q'
In simpler cases, when you just want to push commands to psql, you can also
use this:
exec {PSQL}> >(psql ...)
Note there is a blank between the 2 >. This is important.
Then
echo >&$PSQL 'create table tf ();'
echo >&$PSQL 'drop table tf;'
Does this help?
Re: WAL Archiving and base backup
Thx a lot. I thought about it but was not so sure about having a complex script (compared to the very simple version when using the exclusive backup - but this this is deprecated...). I will test your option with the simpler version and post it back to it can maybe land in PostgreSQL documentation. Regards
