Re: Faster way of estimating database size
On Fri, Jun 15, 2018 at 10:29:02AM +1000, Sam Saffron wrote: > SELECT pg_database.datname, pg_database_size(pg_database.datname) as > size FROM pg_database Consider reading and using approach shown in https://www.depesz.com/2018/02/17/which-schema-is-using-the-most-disk-space/ Best regards, depesz
How to get connection details from psql -> \e
Hi, I'm trying to work on some extension to vim when invoked as \e from psql. To make it fully work, I need to know connection details that psql was using while it invoked \e. Is it possible to do in any way, or if not, any chance it could be added to wishlist for next versions of Pg? Best regards, depesz
Re: How to get connection details from psql -> \e
On Mon, Aug 13, 2018 at 10:00:56AM -0400, Tom Lane wrote: > The only likely reason I can guess at is that you want vim to make its > own connection to the database for some purpose like autocompletion. That's precisely what I'm looking for. > That's a cute idea, but from a security standpoint it sounds like a > disaster in the making. There isn't any secure way to pass down e.g. > a password (neither command line nor environment variables can be > trusted not to be visible to other users), and even if there was > I'm not sure people would be really happy with handing out their > database credentials to any random piece of code psql invokes. I think that passing all-but-password would be good enough. At the very least for my usecases, as I tend to use pgpass and other non-interactive authentications. Best regards, depesz
Re: How to get connection details from psql -> \e
On Mon, Aug 13, 2018 at 03:32:21PM +0200, Laurenz Albe wrote: > hubert depesz lubaczewski wrote: > > I'm trying to work on some extension to vim when invoked as \e from > > psql. > > > > To make it fully work, I need to know connection details that psql was > > using while it invoked \e. > > > > Is it possible to do in any way, or if not, any chance it could be added > > to wishlist for next versions of Pg? > > Do you mean something like \set? Something like this, but automatically sent "somehow" to editor when I run \e. Best regards, depesz
pglogical replication dies
Hi, I tried to setup upgrade using pglogical - from 9.5 to 11. I did set it up, and after subscription, initial data sync seems to work fine, but then replication dies. In logs on subscriber, every 3 minutes I see: db=,user= LOG: background worker "pglogical apply 114610049:2875150205" (PID 28020) exited with exit code 1 On provider there are more messages. Every 3 minutes I get: LOG: replication connection authorized: user=some_user SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, compression=off) LOG: starting logical decoding for slot "pgl_somedb_provider_subscription" DETAIL: streaming transactions committing after 4AA0/5B43630, reading WAL from 4AA0/55743C0 LOG: logical decoding found consistent point at 4AA0/55743C0 DETAIL: There are no running transactions. LOG: could not receive data from client: Connection reset by peer LOG: unexpected EOF on standby connection And that's all. If it matters there are ~ 3000 tables in the replication set, and we don't replicate sequences (figured it will be simpler to just sync them once on switch to new server). How to debug/diagnose the problem? Best regards, depesz
Re: Add column with default value in big table - splitting of updates can help?
On Thu, Jan 30, 2020 at 04:51:02PM +0100, Durumdara wrote: > Is there any advance when I split updates? I'm in one transaction. In this case - no. The benefit of split updates is when you're not in single transaction. Why would you want to have it all done in single transaction? Best regards, depesz
Re: Declare variable from other variable
On Wed, Feb 05, 2020 at 02:42:42PM +0200, Raul Kaubi wrote: > Thanks, it worked! > > By the way, what does this "**j"* mean there..? (this does not mean > multiply there?) it's normal multiplication. Your "j" variable is integer. So, '1 month'::interval * j is some number of months. > And what if, I would like to declare v_to_date also, so that v_to_date is > always + 1 month compared to v_date_from..? v_to_date := v_from_date + '1 month'::interval; ? Best regards, depesz
Re: Error handling: Resume work after error
On Wed, Feb 05, 2020 at 04:17:09PM +0200, Raul Kaubi wrote: > Let's say I want my procedure/function to resume work after specific error > ( duplicate_table ). > Is it possible to resume work after error..? > EXCEPTION > > WHEN duplicate_table then ..; Sure you can: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Best regards, depesz
Re: POLL: Adding transaction status to default psql prompt
On Thu, Feb 06, 2020 at 03:54:48AM +0100, Vik Fearing wrote: > Please answer +1 if you want or don't mind seeing transaction status by > default in psql or -1 if you would prefer to keep the current default. +1 depesz
Re: Use of perl modules in plperl ?
On Wed, Mar 04, 2020 at 06:03:22AM -0500, stan wrote:
> I am trying to examine the values of $_TD->{new}. I thought the easiest way
> to see what this structure looked like was to use the Dumper functionality.
> To do so I need to include the appropriate Perl module, which I would think
> would be done like this:
> use Data::Dumper qw(Dumper);
plperl is trusted, and you can't use "use" in it.
More on this:
https://www.postgresql.org/docs/current/plperl-trusted.html
You have two options:
1. Switch to pl/PerlU
2. set plperl.on_init to whatever "use" you need, examples:
https://www.postgresql.org/docs/current/plperl-under-the-hood.html#PLPERL-CONFIG
Best regards,
depesz
Re: Need to trace the logs
On Thu, Oct 15, 2020 at 05:25:11PM +0530, nandha kumar wrote: > Hi Team, > We are using a postgresql database with 9.6.1 version, any way to > trace the last modified object, procedure in PostgreSQL. well, you can log all queries, and extract the info from logs. If by "object" you mean table/row - you can do it with triggers. Best regards, depesz
Re: Hot backup in PostgreSQL
On Thu, Oct 22, 2020 at 08:42:08AM +0200, W.P. wrote: > Hi there, > > how to do "hot backup" (copying files) while database running? > > Not using pg_dump. > > Is there some equivalent of Oracle (I knew v8) "ALTER DATABASE BEGIN > BACKUP", which froze writes to database files, pushing everything to redo > files? There are many ways to do it. To be able to suggest proper solution we'd need to know: 1. what is the problem with pg_dump? 2. what is the exact problem you're solving (clearly it's not only "having backup", as this is done using pg_dump without any problem). depesz
Re: Hot backup in PostgreSQL
On Thu, Oct 22, 2020 at 09:45:36AM +0200, W.P. wrote: > > There are many ways to do it. To be able to suggest proper solution we'd > > need to know: > > 1. what is the problem with pg_dump? > Time (I guess a bit, but copying files could be done using rsync, so much > faster). Is it *really* too slow for you? Please note that you can easily make it much faster by doing -Fd -j $( nproc ). > > 2. what is the exact problem you're solving (clearly it's not only > > "having backup", as this is done using pg_dump without any problem). > Maybe this is old way, but at some point of time I was doing Oracle 8 > backups just by copying files. > Also I guess, restore using copy files should be much faster than using psql > / pg_restore. You might want to read this: https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP Best regards, depesz
Re: Last updated time for a Schema of the table
On Mon, Nov 02, 2020 at 03:23:06PM +0530, paras paliya wrote: > I need the last updated time or alter time of the table. Like changing the > column name or adding/deleting a column. Is there any table or view from > which I can get? PostgreSQL doesn't keep that information. You can: 1. set log_statement to ddl (or more) to log all schema changes 2. use event triggers to do whatever you want at the time of change. Best regards, depesz
Re: Tools showing table partitions as tables in listings of tables
On Wed, Dec 09, 2020 at 12:29:43PM +, Niels Jespersen wrote: > A small irritation point is that some tools decide that partitions under a > table are to be shown in a list of tables, sometimes > drowning the main table in a sea of partitions. While this doesn't answer your question directly, but when I had this problem, I simply moved partitions to separate schema. So, let's say I have table plans in schema public. All its partitions are in "plans" schema. Of course naming can be done differently, but the thing is that you can do it, and it doesn't cause any issues with queries - as long as you use main table and not directly partitions. Best regards, depesz
Re: Raise exception without using plpgsql?
On Thu, Dec 17, 2020 at 03:32:09PM +0100, Joel Jacobson wrote: > Hi, > Is there a way to raise an exception with a message, > without having to add your own plpgsql helper-function? > Currently this is what I have: Well, you can: DO $$ begin raise notice 'zz'; END; $$; It's cheating though, as it still needs plpgsql. Best regards, depesz
Re: How to generate random string for all rows in postgres
On Mon, Jan 04, 2021 at 02:14:11PM +0100, Hassan Akefirad wrote:
> I have foo table and would like to set bar column to a random string. I've
> got the following query:
> update foo
> set bar = array_to_string(
> array(select string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz',
> round(random() * 30)::integer, 1), '')
> from generate_series(1, 9)), '');
> But it generates the random string once and reuse it for all rows. I asked
> people on SO and one of the giants answered ([1]here):
Hi,
first of all - there is no need to use array_to_string(array( ... ))
just bar = (select string_agg).
it will not work, for the reasons you said, but it's better not to
overcomplicate stuff.
For your case, I think I'd simply make a function for generating random
strings:
CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
SELECT string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz',
round(random() * 30)::integer, 1), '') FROM generate_series(1, $1);
$$ language sql;
And then use it like this:
update foo set bar = random_string(9)
I know it's not perfect, but:
1. it works
2. your query becomes easier to read/understand
3. as a side benefit you will get function for other use cases :)
Best regards,
depesz
Re: postgres optimization
On Thu, Jan 14, 2021 at 04:58:32PM +0530, Atul Kumar wrote: > I will be grateful if you can help me in understanding the basics and > slowly in depth optimization, by understanding explain plan. Consider reading https://www.depesz.com/tag/unexplainable/ depesz
Re: cant connect to localhost:5432 (but unix socket ok)
On Thu, Feb 04, 2021 at 02:20:10PM +, Joao Miguel Ferreira wrote: > My database is not listening on TCP/localhost, desptite it is listening on > the unix socket. How can I investigate this? > I could have done something that is out of my understanding because I have > been loading some big pg_dumpall files that might contain > administrative changes that I am not fully aware of. There are couple of potential issues: 1. it might listen on different port than 5432. What does "show port;" show in psql? 2. it could be that there is a firewall (weird, but possible) 3. it could be that it's listening on another address(es) than 127.0.0.1 / ::1 - what is output of "show listen_addresses;"? 4. Verify that it really does listen on something. As root run: ss -ntlp | grep postgres depesz > Here are some details about the connection issue: > root@deb10tp:~# id > uid=0(root) gid=0(root) groups=0(root) > root@deb10tp:~# su postgres > postgres@deb10tp:/root$ id > uid=116(postgres) gid=126(postgres) groups=126(postgres),125(ssl-cert) > postgres@deb10tp:/root$ cd / > postgres@deb10tp:/$ psql -h localhost > psql: could not connect to server: Connection refused > Is the server running on host "localhost" (::1) and accepting > TCP/IP connections on port 5432? > could not connect to server: Connection refused > Is the server running on host "localhost" (127.0.0.1) and accepting > TCP/IP connections on port 5432? > postgres@deb10tp:/$ psql > psql (11.9 (Debian 11.9-0+deb10u1)) > Type "help" for help. > postgres=# > Thank you > Joao
How to I select value of GUC that has - in its name?
Hi,
question from IRC, but I couldn't find an answer.
I can set custom guc with - in name, but I can't figure out how to
select it.
Without minus, it works great:
=$ psql -X -c 'show custom.guc'
ERROR: unrecognized configuration parameter "custom.guc"
=$ psql -X -c "alter user depesz set custom.guc = '123'"
ALTER ROLE
=$ psql -X -c 'show custom.guc'
custom.guc
123
(1 row)
If I'd try to set variable with - in name:
$ alter user depesz set custom.bad-guc = '1a';
ERROR: syntax error at or near "-"
LINE 1: alter user depesz set custom.bad-guc = '1a';
^
$ alter user depesz set custom."bad-guc" = '1a';
ALTER ROLE
$ select * from pg_db_role_setting where setrole = 'depesz'::regrole;
setdatabase │ setrole │setconfig
─┼─┼─
0 │ 16384 │ {application_name=xxx,custom.guc=123,custom.bad-guc=1a}
(1 row)
OK. Looks like it's set. But I can't show it (this is after reconnect):
$ show custom."bad-guc";
ERROR: unrecognized configuration parameter "custom.bad-guc"
$ show "custom"."bad-guc";
ERROR: unrecognized configuration parameter "custom.bad-guc"
$ show "custom.bad-guc";
ERROR: unrecognized configuration parameter "custom.bad-guc"
I know I can simply not use dashes in names, but if I can *set* it, how
can I get the value back?
depesz
Re: How to I select value of GUC that has - in its name?
On Tue, Feb 09, 2021 at 02:49:19PM +, Edward Macnaghten wrote: > > $ alter user depesz set custom.bad-guc = '1a'; > > ERROR: syntax error at or near "-" > > LINE 1: alter user depesz set custom.bad-guc = '1a'; > Have you tried enclosing "bad-guc" in double quotes? I'm sorry, but have you read my mail? I did. On set and on reading. Best regards, depesz
Re: How to I select value of GUC that has - in its name?
On Tue, Feb 09, 2021 at 07:41:02AM -0800, Adrian Klaver wrote: > The only way I found so far is: > select setconfig[array_position(setconfig, 'custom.bad-guc=1a')] from > pg_db_role_setting where setrole = 'aklaver'::regrole; Yeah, but this will require some parsing if I don't know what the value is. Of course, it's not impossible, I can: with x as ( select unnest(setconfig) as u from pg_db_role_setting where setrole = 'depesz'::regrole ) select regexp_replace(u, '^[^=]*=', '') from x where u like 'custom.bad-guc=%'; but I find it curious that I can set the guc using normal-ish SET, but can't get it using SHOW or even select current_setting() depesz
Re: How to I select value of GUC that has - in its name?
> Sure enough,
> regression=> show custom."bad-guc";
> ERROR: unrecognized configuration parameter "custom.bad-guc"
> regression=> show custom."bad_guc";
> custom.bad_guc
>
> 1a
> (1 row)
> So that's where the setting went.
Oh, that's interesting. Unfortuantley it can also lead to problems:
alter user depesz set custom.bad_guc='2b';
$ select * from pg_db_role_setting where setrole = 'depesz'::regrole;
setdatabase │ setrole │ setconfig
─┼─┼───
0 │ 16384 │
{application_name=xxx,custom.guc=123,custom.bad-guc=1a,custom.bad_guc=2b}
(1 row)
And now I can get:
$ show custom."bad_guc";
custom.bad_guc
2b
(1 row)
But the bad-guc is no longer available.
> (Fortunately, ALTER USER SET with a custom GUC is superuser-only,
> so there's no need to worry about security issues here. But we
> should eliminate surprises.)
Yeah. Realistically I wouldn't use variable names with - in them, but some
people clearly are trying.
Thanks, and best regards,
depesz
Re: Slow index creation
On Tue, Feb 16, 2021 at 07:30:23PM +0100, Paul van der Linden wrote: > Hi, > I have 2 functions: > CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS > $func$ > DECLARE > retVal text; > BEGIN > SELECT > CASE > WHEN a='v1' AND b='b1' THEN 'r1' > WHEN a='v1' THEN 'r2' > ... snip long list containing various tests on a,b and c > WHEN a='v50' THEN 'r50' > END INTO retval; > RETURN retVal; > END > $func$ LANGUAGE PLPGSQL IMMUTABLE; If this function was converted to SQL function it could be faster, as it could be inlined. > CREATE FUNCTION bar(r text, geom geometry) RETURNS int AS > $func$ > DECLARE > retVal int; > BEGIN > SELECT > CASE > WHEN r='r1' AND st_area(geom)>100 THEN 1 > WHEN r='r1' THEN 2 > ... snip long list containing various tests on r and st_area(geom) > WHEN r='r50' THEN 25 > END INTO retval; > RETURN retVal; > END > $func$ LANGUAGE PLPGSQL IMMUTABLE; First thing that I notice is that it seems (from the code and comment) that you run st_area(geom) multiple times. If that's really the case - why don't you cache it in some variable? declare v_area float := st_area( geom ); begin ... and then use v_area instead of st_area(geom) depesz
Re: Slow index creation
On Wed, Feb 17, 2021 at 08:40:17PM +0100, Paul van der Linden wrote: > The st_area calculation is done mostly once or sometimes twice for each geom, > and I suspect that can't explain the factor 20 slower. > Creating an index with only one st_area calculation is also done rather > quickly. In this case, make small test case, like 1000 rows, or something like this. run create index without where, and then remake the functions with profiling info, for example using this approach: https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/ and check for yourself what takes this time, and if it's something you can fix. Best regards, depesz
Re: Slow index creation
On Thu, Feb 18, 2021 at 10:24:25AM -0700, Michael Lewis wrote: > [1]https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/ > > Thanks for this reference. I enjoy your blog, but haven't made the time to > read all the archives somehow. Stuff doesn't stick very > well when it isn't yet "needed" info besides. > I have seen overhead from 'raise notice' in small functions that are > sometimes called many thousands of times in a single query, but > hadn't done the test to verify if the same overhead still exists for raise > debug or another level below both client_min_messages > and log_min_messages. Using your examples, I saw about .006 ms for each call > to RAISE DEBUG with a client/log_min as notice/warning. Sure, this overhead is definitely possible, but kinda besides the point - there will be some slowdowns in other places, and it will be good to track them. That's why I suggested to do it on small sample of data. Best regards, depesz
Interesting fail when migrating Pg from Ubuntu Bionic to Focal
Hi,
I know it's going to be most likely due to glibc and locales, but I found
interesting case that I can't figure out how to fix.
We have pg 12.6 on bionic. Works. Added focal replica (binary).
Replicates OK, but then fails when I try to pg_dump -s.
Error is:
pg_dump: error: query failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: error: query was: SELECT p.tableoid, p.oid, p.proname, p.prolang,
p.pronargs, p.proargtypes, p.prorettype, (SELECT pg_catalog.array_agg(acl ORDER
BY row_n) FROM (SELECT acl, row_n FROM
pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner)))
WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner)))
AS init(init_acl) WHERE acl = init_acl)) as foo) AS proacl, (SELECT
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner)))
WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner))) AS
permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rproacl, NULL AS initproacl,
NULL AS initrproacl, p.pronamespace, (SELECT rolname FROM pg_catalog.pg_roles
WHERE oid = p.proowner) AS rolname FROM pg_proc p LEFT JOIN pg_init_privs pip
ON (p.oid = pip.objoid AND pip.classoid = 'pg_proc'::regclass AND pip.objsubid
= 0) WHERE p.prokind <> 'a'
AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_proc'::regclass
AND objid = p.oid AND deptype = 'i')
AND (
pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog')
OR EXISTS (SELECT 1 FROM pg_cast
WHERE pg_cast.oid > 16383
AND p.oid = pg_cast.castfunc)
OR EXISTS (SELECT 1 FROM pg_transform
WHERE pg_transform.oid > 16383 AND
(p.oid = pg_transform.trffromsql
OR p.oid = pg_transform.trftosql))
OR p.proacl IS DISTINCT FROM pip.initprivs)
Based on https://wiki.postgresql.org/wiki/Locale_data_changes
I wrote:
SELECT 'reindex index ' || indexrelid::regclass::text
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') \gexec
and let it run (80k+ indexes).
Afterwards - same problem.
So I did "reindex system".
And the query still fails.
I tried running simple "select * from table", for each of:
- pg_catalog.pg_roles
- pg_catalog.pg_proc
- pg_catalog.pg_init_privs
- pg_catalog.pg_depend
- pg_catalog.pg_namespace
- pg_catalog.pg_cast
- pg_catalog.pg_transform
and it worked, so I'm kinda at loss here.
I have test system, can test anything. Any idea on what could be the reason,
and if the system is fixable afterwards?
Ah, one more thing - straced backend when it was doing its thing. Last 50 lines:
15:01:31.229198 futex(0xfffe49b8cb3c, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229233 futex(0xfffe49b8c41c, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229268 futex(0xfffe49b8cdfc, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229303 futex(0xfffe49b8ce00, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229338 futex(0xfffe49b8ce08, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229379 futex(0xfffe49b8dde4, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229415 futex(0xfffe49b91524, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229450 futex(0xfffe49b8998c, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229485 futex(0xfffe49b8cf9c, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229521 futex(0xfffe49b882e4, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229556 futex(0xfffe49b8e548, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229596 futex(0xfffe49b8d75c, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229634 futex(0xfffe49b8cfe8, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229670 futex(0xfffe49b8dfe8, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229705 futex(0xfffe49b8dff0, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229740 futex(0xfffe49b8dff8, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229775 futex(0xfffe49b8ddf0, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229810 futex(0xfffe49b8e0a8, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229845 futex(0xfffe49b8e200, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229881 futex(0xfffe49b8e534, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229925 futex(0xfffe49b8e734, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229961 futex(0xfffe49b8e7dc, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.12>
15:01:31.229998 futex(0xfffe49b8e924, FUTEX_WAKE_PRIVATE, 2147483647) = 0
<0.0
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Thu, Mar 03, 2022 at 04:04:28PM +0100, hubert depesz lubaczewski wrote: > and it worked, so I'm kinda at loss here. based on some talk on IRC, I was able to get stack trace from fail: (gdb) bt #0 0xfffe4a36e4d8 in ?? () #1 0xbe03ffb8 in ExecProcNode (node=0xe4f87cf8) at ./build/../src/include/executor/executor.h:242 #2 ExecMergeJoin (pstate=0xe4f87da8) at ./build/../src/backend/executor/nodeMergejoin.c:656 #3 0xbe03ffb8 in ExecProcNode (node=0xe4f87ae8) at ./build/../src/include/executor/executor.h:242 #4 ExecMergeJoin (pstate=0xe4f876c8) at ./build/../src/backend/executor/nodeMergejoin.c:656 #5 0xbe039b1c in ExecProcNode (node=0xe4f876c8) at ./build/../src/include/executor/executor.h:242 #6 ExecHashJoinImpl (parallel=false, pstate=0xe4f87408) at ./build/../src/backend/executor/nodeHashjoin.c:262 #7 ExecHashJoin (pstate=0xe4f87408) at ./build/../src/backend/executor/nodeHashjoin.c:591 #8 0xbe01ed5c in ExecProcNode (node=0xe4f87408) at ./build/../src/include/executor/executor.h:242 #9 ExecutePlan (execute_once=, dest=0xfffe49be0828, direction=, numberTuples=0, sendTuples=, operation=CMD_SELECT, use_parallel_mode=, planstate=0xe4f87408, estate=0xe4ce4180) at ./build/../src/backend/executor/execMain.c:1632 #10 standard_ExecutorRun (queryDesc=0xe4e9da70, direction=, count=0, execute_once=) at ./build/../src/backend/executor/execMain.c:350 #11 0x7d1981fc in pgss_ExecutorRun (queryDesc=0xe4e9da70, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/pg_stat_statements/pg_stat_statements.c:893 #12 0x7d182688 in explain_ExecutorRun (queryDesc=0xe4e9da70, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/auto_explain/auto_explain.c:320 #13 0xbe1754c8 in PortalRunSelect (portal=portal@entry=0xe4d476e0, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0xfffe49be0828) at ./build/../src/backend/tcop/pquery.c:938 #14 0xbe176a64 in PortalRun (portal=portal@entry=0xe4d476e0, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0xfffe49be0828, altdest=altdest@entry=0xfffe49be0828, completionTag=completionTag@entry=0xc89001f8 "") at ./build/../src/backend/tcop/pquery.c:779 #15 0xbe172a18 in exec_simple_query ( query_string=query_string@entry=0xe4c6c7b0 "SELECT\np.tableoid,\n p.oid,\np.proname,\np.prolang,\np.pronargs,\np.proargtypes,\n p.prorettype,\n(\nSELECT\n", ' ' , "pg_catalog.array_agg(acl ORDER BY row_n)\nF"...) at ./build/../src/backend/tcop/postgres.c:1215 #16 0xbe1736ac in PostgresMain (argc=, argv=argv@entry=0xe4cc9018, dbname=, username=) at ./build/../src/backend/tcop/postgres.c:4271 #17 0xbe0fa768 in BackendRun (port=0xe4cb88e0, port=0xe4cb88e0) at ./build/../src/backend/postmaster/postmaster.c:4510 #18 BackendStartup (port=0xe4cb88e0) at ./build/../src/backend/postmaster/postmaster.c:4193 #19 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1725 #20 0xbe0fb74c in PostmasterMain (argc=, argv=) at ./build/../src/backend/postmaster/postmaster.c:1398 #21 0xbde8c8a8 in main (argc=5, argv=0xe4c65ea0) at ./build/../src/backend/main/main.c:228 And one more thing - if it matters - it's on aarch64 architecture (graviton ec2 in AWS) Best regards, depesz
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Thu, Mar 03, 2022 at 04:11:56PM +0100, hubert depesz lubaczewski wrote: > On Thu, Mar 03, 2022 at 04:04:28PM +0100, hubert depesz lubaczewski wrote: > > and it worked, so I'm kinda at loss here. > > based on some talk on IRC, I was able to get stack trace from fail: Based on the stack trace I was able to get it to break using simple query: select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p; I took a simple look at ranges of oid/prowner, and they look fine: =# select min(proowner), max(proowner), count(*) from pg_proc; min │ max │ count ─┼─┼─── 10 │ 10 │ 2970 (1 row) 16:38:34 db: postgres@postgres, pid:1991057 =# select min(oid), max(oid), count(*) from pg_roles; min │max│ count ─┼───┼─── 10 │ 310235824 │ 244 (1 row) Also, as I didn't mention it before: it's Pg 12.9. Best regards, depesz
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Thu, Mar 03, 2022 at 05:39:21PM +0100, hubert depesz lubaczewski wrote: > On Thu, Mar 03, 2022 at 04:11:56PM +0100, hubert depesz lubaczewski wrote: > > On Thu, Mar 03, 2022 at 04:04:28PM +0100, hubert depesz lubaczewski wrote: > > > and it worked, so I'm kinda at loss here. > > > > based on some talk on IRC, I was able to get stack trace from fail: > > Based on the stack trace I was able to get it to break using simple > query: > select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = > p.proowner) from pg_proc p; > > I took a simple look at ranges of oid/prowner, and they look fine: > > =# select min(proowner), max(proowner), count(*) from pg_proc; > min │ max │ count > ─┼─┼─── > 10 │ 10 │ 2970 > (1 row) > > 16:38:34 db: postgres@postgres, pid:1991057 > =# select min(oid), max(oid), count(*) from pg_roles; > min │max│ count > ─┼───┼─── > 10 │ 310235824 │ 244 > (1 row) > > > Also, as I didn't mention it before: it's Pg 12.9. OK. Traced it back to JIT. With JIT enabled: =# show jit; jit ─ on (1 row) =# explain select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p; QUERY PLAN ── Seq Scan on pg_proc p (cost=0.00..156507.84 rows=63264 width=128) SubPlan 1 -> Index Scan using pg_authid_oid_index on pg_authid (cost=0.14..2.36 rows=1 width=64) Index Cond: (oid = p.proowner) JIT: Functions: 8 Options: Inlining false, Optimization false, Expressions true, Deforming true (7 rows) =# select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. 16:42:44 canvas test, cluster 999, standalone db: @, pid:2014255 ?!> But when I disable jit - query works fine. versions of things that I think are relevant: =$ dpkg -l | grep -E 'llvm|clang|gcc|glibc' ii gcc4:9.3.0-1ubuntu2 arm64GNU C compiler ii gcc-10-base:arm64 10.3.0-1ubuntu1~20.04 arm64GCC, the GNU Compiler Collection (base package) ii gcc-9 9.3.0-17ubuntu1~20.04 arm64GNU C compiler ii gcc-9-base:arm64 9.3.0-17ubuntu1~20.04 arm64GCC, the GNU Compiler Collection (base package) ii libgcc-9-dev:arm64 9.3.0-17ubuntu1~20.04 arm64GCC support library (development files) ii libgcc-s1:arm6410.3.0-1ubuntu1~20.04 arm64GCC support library ii libllvm9:arm64 1:9.0.1-12 arm64Modular compiler and toolchain technologies, runtime library Best regards, depesz
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Fri, Mar 04, 2022 at 02:09:52PM -0500, Tom Lane wrote: > arm64, eh? I wonder if that's buggier than the Intel code paths. > > I tried and failed to reproduce this on Fedora 35 on aarch64, > but that has what I think is a newer LLVM version: I have suspicion that it also kinda depends on number of rows in there. When I deleted some schemas, which included some functions, the problem disappeared. I wasn't able to pinpoint specific thing, though, and when I called the pg_proc + pg_roles query for each separate row - it worked flawlessly. Best regards, depesz
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Fri, Mar 04, 2022 at 05:03:14PM -0500, Tom Lane wrote: > hubert depesz lubaczewski writes: > > On Fri, Mar 04, 2022 at 02:09:52PM -0500, Tom Lane wrote: > >> I tried and failed to reproduce this on Fedora 35 on aarch64, > >> but that has what I think is a newer LLVM version: > > > I have suspicion that it also kinda depends on number of rows in there. > > When I deleted some schemas, which included some functions, the problem > > disappeared. > > I wasn't able to pinpoint specific thing, though, and when I called the > > pg_proc + pg_roles query for each separate row - it worked flawlessly. > > Mmm ... it might have just been that the planner chose not to use > JIT when it thought there were fewer rows involved. Did you check > with EXPLAIN that these cut-down cases still used JIT? Hi, I tore these boxes down, so can't check immediately, but I think I remember that you're right - single-row queries didn't use JIT. Best regards, depesz
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Sun, Mar 06, 2022 at 11:10:00AM -0500, Tom Lane wrote: > > I tore these boxes down, so can't check immediately, but I think > > I remember that you're right - single-row queries didn't use JIT. Got focal box up. Loaded schema for Pg. Initially select didn't break anything, but when I tuned down jit_above_cost so that it will kick in - got fails immediately. > FWIW, I went to the trouble of installing Ubuntu Focal on my > raspberry pi to see if I could duplicate this, and I couldn't. > However, what you get from a fresh install now is > $ dpkg -l | grep libllvm > ii libllvm10:arm641:10.0.0-4ubuntu1 > arm64Modular compiler and toolchain technologies, runtime library > not 9.0.1. I also found that Fedora 31/aarch64 is still downloadable, and > that does contain LLVM 9 ... and I could not reproduce it there either. Soo... plot thickens. Looks that pg 12 supplied by pgdg required libllvm9: =$ apt-cache show postgresql-12 | grep -E '^(Package|Version|Depends):' Package: postgresql-12 Version: 12.9-2.pgdg20.04+1 Depends: ..., libllvm9 (>= 1:9~svn298832-1~), ... Package: postgresql-12 Version: 12.9-0ubuntu0.20.04.1 Depends: ..., libllvm10 (>= 1:9~svn298832-1~), ... Package: postgresql-12 Version: 12.2-4 Depends: ..., libllvm10 (>= 1:9~svn298832-1~), ... Newer pg12 (12.10) from pgdg also depends on llvm9. Perhaps changing the deb to use/depend-on llvm10 would solve the problem, for now I'm not sure how to do it. Reached to Christoph about it. Best regards, depesz
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Mon, Mar 07, 2022 at 12:22:26PM -0500, Tom Lane wrote: > Neither of those configurations fail for me, so either > it's been fixed since 12.9, or (more likely) there is > something to your test case beyond what you've mentioned. Upgraded to 12.10 from pgdg, same problem. > (I guess a long-shot possibility is that my raspberry pi 3B+ > is sufficiently different hardware from your box as to not > see the problem. Doubt that though.) My "hardware" is AWS EC2 graviton instance (c6g.2xlarge). > Can you create a self-contained test case? I tried, but it seems that number of functions/users is a factor, and I wasn't able to make simple test that would replicate the issue. I guess I will just disable JIT on arm for now, and be done with it. Best regards, depesz
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Mon, Mar 07, 2022 at 09:54:22AM -0800, Andres Freund wrote: > > Initially select didn't break anything, but when I tuned down > > jit_above_cost so that it will kick in - got fails immediately. > Could you set jit_debugging_support=on and show a backtrace with that? Here you go: Program received signal SIGSEGV, Segmentation fault. 0xfffe7587b6c8 in deform_0_8 () (gdb) bt #0 0xfffe7587b6c8 in deform_0_8 () #1 0xfffe7587b5f4 in evalexpr_0_7 () #2 0xb67e6f7c in ExecEvalExprSwitchContext (isNull=0xf7956217, econtext=0xf6014608, state=0xf6014ab0) at ./build/../src/include/executor/executor.h:316 #3 ExecProject (projInfo=0xf6014aa8) at ./build/../src/include/executor/executor.h:350 #4 ExecScan (node=, accessMtd=0xb68051b8 , recheckMtd=0xb6805248 ) at ./build/../src/backend/executor/execScan.c:239 #5 0xb67ded5c in ExecProcNode (node=0xf60143b8) at ./build/../src/include/executor/executor.h:242 #6 ExecutePlan (execute_once=, dest=0xf5fc15e8, direction=, numberTuples=0, sendTuples=, operation=CMD_SELECT, use_parallel_mode=, planstate=0xf60143b8, estate=0xf5e01360) at ./build/../src/backend/executor/execMain.c:1632 #7 standard_ExecutorRun (queryDesc=0xf5ef4130, direction=, count=0, execute_once=) at ./build/../src/backend/executor/execMain.c:350 #8 0xa87751fc in pgss_ExecutorRun (queryDesc=0xf5ef4130, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/pg_stat_statements/pg_stat_statements.c:893 #9 0xa875f688 in explain_ExecutorRun (queryDesc=0xf5ef4130, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/auto_explain/auto_explain.c:320 #10 0xb69354c8 in PortalRunSelect (portal=portal@entry=0xf5e83ef0, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0xf5fc15e8) at ./build/../src/backend/tcop/pquery.c:938 #11 0xb6936a64 in PortalRun (portal=portal@entry=0xf5e83ef0, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0xf5fc15e8, altdest=altdest@entry=0xf5fc15e8, completionTag=completionTag@entry=0xf7956848 "") at ./build/../src/backend/tcop/pquery.c:779 #12 0xb6932a18 in exec_simple_query (query_string=query_string@entry=0xf5d9af90 "select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;") at ./build/../src/backend/tcop/postgres.c:1215 #13 0xb69336ac in PostgresMain (argc=, argv=argv@entry=0xf5df6f18, dbname=, username=) at ./build/../src/backend/tcop/postgres.c:4271 #14 0xb68ba768 in BackendRun (port=0xf5df0910, port=0xf5df0910) at ./build/../src/backend/postmaster/postmaster.c:4510 #15 BackendStartup (port=0xf5df0910) at ./build/../src/backend/postmaster/postmaster.c:4193 #16 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1725 #17 0xb68bb74c in PostmasterMain (argc=, argv=) at ./build/../src/backend/postmaster/postmaster.c:1398 #18 0xb664c8a8 in main (argc=5, argv=0xf5d94ea0) at ./build/../src/backend/main/main.c:228 > > > FWIW, I went to the trouble of installing Ubuntu Focal on my > > > raspberry pi to see if I could duplicate this, and I couldn't. > > > However, what you get from a fresh install now is > > > $ dpkg -l | grep libllvm > > > ii libllvm10:arm641:10.0.0-4ubuntu1 > > > arm64Modular compiler and toolchain technologies, runtime library > > > not 9.0.1. I also found that Fedora 31/aarch64 is still downloadable, and > > > that does contain LLVM 9 ... and I could not reproduce it there either. > > > > Soo... plot thickens. > > > > Looks that pg 12 supplied by pgdg required libllvm9: > What are the libllvm package versions on which you can reproduce the crash? Hmm .. not sure I fully understand. Here are all packages that I think are relevant: root@c999-pg1302:~# dpkg -l | grep -iE 'postgresql-12|llvm|clang' ii libllvm9:arm64 1:9.0.1-12 arm64Modular compiler and toolchain technologies, runtime library ii postgresql-12 12.9-2.pgdg20.04+1 arm64The World's Most Advanced Open Source Relational Database ii postgresql-12-dbgsym 12.9-2.pgdg20.04+1 arm64debug symbols for postgresql-12 ii postgresql-12-pg-collkey 0.5.1-1insops1 arm64ICU collation function wrapper for PostgreSQL 12 ii postgresql-12-postgis-2.5 2.5.5+dfsg-1.pgdg20.04+2 arm64Geographic objects support for PostgreSQL 12 ii postgresql-12-postgis-2.5-scripts 2.5.5+dfsg-1.pgdg20.04+2 all Geographic objects support for PostgreSQL 12 -- SQL scripts ii postgresql-12-repack
Re: Postgres query
On Fri, Mar 11, 2022 at 10:02:39AM +, Ian Dauncey wrote:
> Can anyone assist in shedding some light here.
> We getting this query popping up in our postgresql log file at the same time
> as the connections to the databases starts increasing.
> Not sure what is initiating this query, but we get around a hundred per
> second until we restart our applications.
> Any help will be appreciated.
> "select $1[s], s - pg_catalog.array_lower($1,1) + 1
> from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
>pg_catalog.array_upper($1,1),1) as g(s)"
The query simply unpacks given array.
For example, assuming array $1 is '{5,10,15}' it will yield:
?column? │ ?column?
──┼──
5 │1
10 │2
15 │3
(3 rows)
basically old way to achieve unpacking of array, these days normally it would
be called like:
$ select * from unnest('{5,10,15}'::int4[]) with ordinality;
unnest │ ordinality
┼
5 │ 1
10 │ 2
15 │ 3
(3 rows)
What is running it it's hard to say, the query doesn't strike me as
something that any db driver would call on its own.
depesz
Re: Postgres Crash Issue
On Tue, Mar 15, 2022 at 07:20:57AM +, Sankar, Uma (Uma) wrote: > Hi All, > > We have a database crash issue last Friday and it's a patroni HA-based > Postgres database running, we have checked the pg logs and it shows > error as shared memory corruptions. Can someone please check the > attached logs and share your suggestions and feedback on the crash > issues. Thanks in advance. It wasn't crash. Logs show: #v+ 2022-03-11 08:58:42.956 UTC [27799] LOG: received immediate shutdown request 2022-03-11 08:58:42.956 UTC [17115] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.956 UTC [17115] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.957 UTC [14618] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.957 UTC [14618] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.957 UTC [14618] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.957 UTC [16396] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.957 UTC [16396] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.957 UTC [16396] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.957 UTC [12873] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.957 UTC [12873] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.957 UTC [12873] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.958 UTC [16564] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.958 UTC [16564] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.958 UTC [16564] HINT: In a moment you should be able to reconnect to the database and repeat your command. #v- and #v+ 2022-03-11 08:58:45.969 UTC [17354] LOG: consistent recovery state reached at A/E1836620 2022-03-11 08:58:45.969 UTC [17347] LOG: database system is ready to accept read only connections 2022-03-11 08:59:01.234 UTC [17347] LOG: received fast shutdown request 2022-03-11 08:59:01.234 UTC [17347] LOG: aborting any active transactions 2022-03-11 08:59:01.234 UTC [17412] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.234 UTC [17415] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.234 UTC [17393] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.235 UTC [17399] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.235 UTC [17408] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.236 UTC [17394] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.236 UTC [17389] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.237 UTC [17400] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.237 UTC [17378] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.238 UTC [17362] LOG: shutting down 2022-03-11 08:59:01.254 UTC [17347] LOG: database system is shut down 2022-03-11 08:59:01.419 UTC [17444] LOG: database system was shut down in recovery at 2022-03-11 08:59:01 UTC 2022-03-11 08:59:01.419 UTC [17444] LOG: entering standby mode 2022-03-11 08:59:01.420 UTC [17444] LOG: redo starts at A/E17CB690 #v- Something/someone stopped the pg, twice, ~ 20 seconds apart. What/how/why - can't tell, but "received ... shutdown request" is not crash. It is someone/something doing "pg_ctl stop" depesz
Re: [External]Re: Postgres Crash Issue
On Tue, Mar 15, 2022 at 01:30:25PM +, Menon, Deepak (Deepak) wrote: > Hi Depesz, > > Then what does this message mean ? > > 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded > this server process to roll back the current transaction and exit, because > another server process exited abnormally and possibly corrupted shared > memory. When someone issues pg_ctl -m immediate stop (which is the immediate shutdown). pg basically shutd backends in their head. This, in turn, means that all other have to exit to avoid shared memory corruption. Sometimes such exit happens before pg_ctl will kill next backend, sometimes not. So you sometimes can get such messages. depesz
Re: [External]Re: Postgres Crash Issue
On Tue, Mar 15, 2022 at 01:38:04PM +, Menon, Deepak (Deepak) wrote: > Thanks Depesz. Is there anyway to check the source of the command as this HA > setup is managed by using Patroni >From pg perspective it got immediate stop request. There is no way to tell why/how. depesz
Order of rows in statement triggers NEW/OLD tables
Hi, when defining statement triggers on update I can use: REFERENCING OLD TABLE AS xxx NEW TABLE as YYY these "pseudo" tables contain rows that were before and after. Is the order guaranteed? Can I assume that "first" row returned by select from xxx, will be older version of first row returned by select from yyy? depesz
Re: PLPGSQL - extra column existence in trigger
On Sat, May 07, 2022 at 07:41:44AM -0700, David G. Johnston wrote: > No. I’d probably approach this by generically converting the NEW record to > json and working with that. Non-existent object keys return null when > accessed. One note - in my tests working with hstore was significantly faster than json. It could have changed since I wrote it, but you might want to check it out: https://www.depesz.com/2021/04/21/getting-value-from-dynamic-column-in-pl-pgsql-triggers/ depesz
Re: operator does not exist: text = bytea
On Wed, Jul 20, 2022 at 03:02:13PM +0530, Karthik K L V wrote: > *Caused by: org.postgresql.util.PSQLException: ERROR: operator does not > exist: text = bytea Hint: No operator matches the given name and argument > types. You might need to add explicit type casts. Position: 1037* > Could you please let me know how I can resolve this issue without making > changes to the query? I don't think it's possible. bytea is basically array of bytes. text on the other hand is array of characters. Do the bytes "\xbf\xf3\xb3\x77" equal text "żółw"? They actually kinda do, if we assume the bytes are text in encoding Windows-1252 - in which case the bytes mean "żółw". But if we'd assume the encoding to be, for example, iso8859-1, then the same sequence of bytes means "¿ó³w" That's why you can't compare bytes to characters. You have to either convert bytes to text using convert or convert_from functions, or change text into bytea using convert_to. In some cases you can simply cast text to bytea: $ select 'depesz'::text::bytea; bytea \x64657065737a (1 row) which will work using current server encoding, afair. depesz
Re:
On Tue, Jul 26, 2022 at 10:48:47AM -0700, Adrian Klaver wrote: > On 7/26/22 9:29 AM, Ron wrote: > > On 7/26/22 10:22, Adrian Klaver wrote: > > > On 7/26/22 08:15, Rama Krishnan wrote: > > > > Hi Adrian > > > > > > > > > > > > > What is size of table? > > > > > > > > I m having two Database example > > > > > > > > 01. Cricket 320G > > > > 02.badminton 250G > > > > > > So you are talking about an entire database not a single table, correct? > > > > In a private email, he said that this is what he's trying: > > Pg_dump -h endpoint -U postgres Fd - d cricket | aws cp - > > s3://dump/cricket.dump > > > > It failed for obvious reasons. > From what I gather it did not fail, it just took a long time. Not sure > adding -j to the above will improve things, pretty sure the choke point is > still going to be aws cp. It's really hard to say what is happening, because the command, as shown wouldn't even work. Starting from Pg_dump vs. pg_dump, space between `-` and `d`, "Fd" as argument, or even the idea that you *can* make -Fd dumps to stdout and pass it to aws cp. depesz
Re: Allow user to connect to replicas only
On Thu, Aug 04, 2022 at 03:42:00PM +0200, Wiwwo Staff wrote: > Is there any way to create a user allowed to connect to a/any read replica > only, as in "not allowed to connect to primary"? Sure. Modify pg_hba.conf on primary to disallow connections as this user. Best regards, depesz
Would it be possible to add functions to tab-completion in psql?
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? Best regards, depesz
Re: Would it be possible to add functions to tab-completion in psql?
On Tue, Aug 16, 2022 at 07:42:27AM -0500, Ron wrote: > On 8/16/22 07:28, 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? > > It does, in the psql 9.6 and 12 that I installed from it's native > repositories, and the psql 12 installed from RHEL 7/8 native repositories. Hmm .. I'm on Pg 16, linked with readline, and pressing tab after select pg_ just produces new prompt: https://asciinema.org/a/A8w16KhXF7bK4iz7hE7iyyo0D Can you please show me this working, with "which psql", and "ldd $( which psql )" using asciinema? Best regards, depesz
Re: Would it be possible to add functions to tab-completion in psql?
On Tue, Aug 16, 2022 at 09:55:34PM +0800, Julien Rouhaud wrote: > 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 While I would appreciate having full tab-completion on every level, I wouldn't be happy with just first-level. Basically I feel that "because we can't get it perfect" it obscuring the fact that imperfect would be very useful. Best regards, depesz
Re: Would it be possible to add functions to tab-completion in psql?
On Tue, Aug 16, 2022 at 08:51:49AM -0500, Ron wrote: > On 8/16/22 08:01, hubert depesz lubaczewski wrote: > > On Tue, Aug 16, 2022 at 07:42:27AM -0500, Ron wrote: > > > On 8/16/22 07:28, 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? > > > It does, in the psql 9.6 and 12 that I installed from it's native > > > repositories, and the psql 12 installed from RHEL 7/8 native repositories. > > Hmm .. I'm on Pg 16, linked with readline, and pressing tab after > > Pg *16*?? Yes. I use dev pg on daily basis. > > select pg_ > > just produces new prompt: > > > > https://asciinema.org/a/A8w16KhXF7bK4iz7hE7iyyo0D > > > > Can you please show me this working, with "which psql", and "ldd $( > > which psql )" using asciinema? > > $ alias psql12 > alias psql12='/usr/lib/postgresql/12/bin/psql -p5433' > > $ psql12 > psql (12.11 (Ubuntu 12.11-1.pgdg18.04+1)) > Type "help" for help. > > postgres=# select * from pg_ > Display all 130 possibilities? (y or n) Please note that this example is not really relevant to what I asked about. First of all, I asked about `select pg_`, and not `select * from pg_`, second this tab completion lists tables and views that have names starting with pg_*. And I asked about functions. I know we can tab-complete relations. But we can't functions. Best regards, depesz
Re: Would it be possible to add functions to tab-completion in psql?
On Tue, Aug 16, 2022 at 10:10:55AM -0400, Tom Lane wrote: > There is nothing principled about assuming that the first word > after SELECT is a function name. It'd be even less principled to > provide tab completion only for function names beginning with > "pg_". So this idea seems like a wart rather than something > anybody would think is a nice improvement. While I understand that there is nothing that would suggest it, is there any reason why providing a thing that can legally be there would be bad idea? I understand that someone might want to enter pg_or_not_pg (column name from some table), or perhaps pg.some_column_name - but what is the harm of providing pg_* functions for pg_? Specifically, I'd ask what is the harm of increasing what tab completion can do by a lot - for example, make it tab-complete fields from all tables. And make it possible to tab-complete column name anywhere in where clause. But function name in select would be (for me(!)) great first step, and I can't really see the drawbacks, aside from using developer time to work on it. Best regards, depesz
Re: Postgres question
On Wed, Aug 17, 2022 at 10:32:26AM +0100, ajay venki wrote: > I am new to PostgreSQL and i have a general question to clarify. is this > the right forum or the mail address to post my questions? Yes, this is the right place. Best regards, depesz
Re: Is it possible to keep indexes on different disk location?
On Thu, Aug 18, 2022 at 08:39:27AM +0200, W.P. wrote: > Is it possible to move DB tables etc to this internal storage (sure > connection) and put only indexes on USB HDD? Sure. There is a thing called tablespace, which is basically, directory where files for db objects reside. You can specify it both when making new objects (https://www.postgresql.org/docs/current/sql-createtable.html, https://www.postgresql.org/docs/current/sql-createtable.html) and you can also move object between tablespaces ( (https://www.postgresql.org/docs/current/sql-altertable.html, https://www.postgresql.org/docs/current/sql-altertable.html). Just keep in mind that moving object means that it will get locked, data copied to new device, and then removed from old, and unlocked. > And will it help in case of losing connection to USB disk? (DB recoverable > instead of total crash)? Well, you need *all* data from all tablespaces to have fully working db. Best regards, depesz
Re: Unable to Create or Drop Index Concurrently
On Thu, Aug 18, 2022 at 01:57:48PM +0800, Abdul Qoyyuum wrote: > Hi list, > > We have a running Master-Slave High Availability set up. Naturally, we > can't run any changes on read-only databases on slave, so we have to do it > on the master node. > > When trying to run the following command: > > create index concurrently idx_cash_deposit_channel_id_batch_id on > cash_deposit (channel_id, batch_id); > > > Waiting for a long time, and my connection dropped. When checking the > table, we get the index as INVALID > > Indexes: > "pk_cash_deposit" PRIMARY KEY, btree (id) > "idx_cash_deposit_channel_id_batch_id" btree (channel_id, batch_id) > INVALID > > And when dropping the invalid index, also takes a long time, my connection > timed out, then when logging back in and check the table, it hasn't dropped. This means that you have some very long transactions. To make/drop index concurrently, all transactions that have started before you started create/drop, have to finish. You can see your oldest transactions by doing: select * from pg_stat_activity where xact_start is not null order by xact_start Best regards, depesz
Re: CREATE SUBSCRIPTION not picking up .pgpass while psql does
On Wed, Aug 31, 2022 at 04:03:31PM +0300, Kristjan Mustkivi wrote: > Hello, > > I do not understand why CREATE SUBSCRIPTION does not pick up .pgpass > (when psql does): > > root@pg.newtest:/# psql 'host=pg.oldtest port=5432 user=pg_replication > dbname=oldtest' > oldtest=# \q > > root@pg.newtest:/# psql newtest postgres > newtest=# CREATE SUBSCRIPTION sub_pgupgrade CONNECTION > 'host=pg.oldtest port=5432 user=pg_replication dbname=oldtest' > PUBLICATION pub_pgupgrade; > ERROR: could not connect to the publisher: connection to server at > "pg.oldtest" (x.x.x.x), port 5432 failed: fe_sendauth: no password > supplied > newtest=# > > oldtest is 11.15 (Debian 11.15-1.pgdg90+1) > newtest is 14.5 (Debian 14.5-1.pgdg110+1) > > .pgpass is under /root (home inside the docker container) with 0600 > permissions and owned by user "postgres". Even providing > passfile=/root/.pgpass in the connstring does not work. How/where you provide it? Why would you assume that postgres (running from user postgres presumably) would look for pgpass in /root/.pgpass? postgres should have it in ~postgres/.pgpass with proper ownership. depesz
Re: CREATE SUBSCRIPTION not picking up .pgpass while psql does
On Wed, Aug 31, 2022 at 04:26:22PM +0300, Kristjan Mustkivi wrote: > And as said, the psql utility has no problems finding the .pgass where > it is. If I lie to it about the pgpass location i.e by giving > passfile=/root/.pgpassx it will ask for password. of course it doesn't have problem, because you run it as root. put subscription is run by pg backend, which runs as postgres user, not root! also - no pg tool cares about "passfile" env variable, so it is just ignored. Best regards, depesz
Re: How to check if checkpoint is finished in sql script?
On Mon, Sep 05, 2022 at 04:55:28PM +0800, Yi Sun wrote: > How to check if the checkpoint is finished in sql script please? We know > that the log file will show it, but we want to check it in sql then can > easily be used by ansible, thanks Well, if the command "checkpoint" finished, and returned, and there is no error - then checkpoint has finished. Best regards, depesz
Re: [BeginnerQuestion]Why these is 6 rows in my SELECT statement?
On Thu, Sep 08, 2022 at 04:37:00PM +0800, BeginnerC wrote: > Hello community, > I am checking my pg_stat_activity view,but something confused me. > Just like this: > > postgres=# SELECT wait_event_type, wait_event FROM pg_stat_activity; > wait_event_type | wait_event > -+- > Activity| AutoVacuumMain > Activity| LogicalLauncherMain > | > Activity| BgWriterHibernate > Activity| CheckpointerMain > Activity| WalWriterMain > (6 rows) > > As you can see, the third row is empty,but I don't know why. > Can anyone provide some solution to me? > Thanks in advance! Please do: \pset null '-- null --' SELECT wait_event_type, wait_event FROM pg_stat_activity; it will most likely show -- null -- in these fields. which means values in these columns are null.SELECT wait_event_type, wait_event FROM pg_stat_activity; You can find more about this/these backends by doing: select * from pg_stat_activity where wait_event_type is null \gx depesz
How to handle logical replication 12->14, when our max_replication_slots gets overrun by inactive sync workers
Hi, I reported a bug aobut it earlier, and from what I know it has been fixed, but new release will come later. For now I have this situation: 1. max_replication_slots is 50 2. database to replicate has 67 schemas, and ~ 26k tables. 3. schemas are split into 5 slots 4. pg14 side has max_sync_workers_per_subscription = 2 we start replication. within an hour all 50 replication slots on pg12 are used. 5 by our pg14 upgrade slots, and the other *45* by sync workers, which are generally active = false. at this moment all sync traffic dies (seen in network traffic data). we tried to kill inactive sync workers - didn't help. I tried to set max_sync_workers_per_subscription = 0, to avoid using these extra workers, but it just seems to make slots sit there. 1 table in each slot changed status to 'r', but all other are at 'i'. Currently I'm running a test where all tables are in single slot, with 2 max_sync_workers_per_subscription but it will take "a while" to get it to working state. Is there anything we can do now? I seem to have a case where the problem is 100% repeatable, so if anyone has ideas I can test them fully. Best regards, depesz
Re: trouble writing plpgsql
On Thu, Dec 22, 2022 at 11:37:22AM -, haman...@t-online.de wrote: > I want to make a function to parsetext and return key-value pairs > create or replace function extractinfo (text) returns table (key char[1], val > text) Please don't use char datatype: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_char.28n.29 > language plpgsql as $$ > > I first tried > declare > xx record; > begin > > xx.key = ; xx.val = ; > return next xx: > This is not possible because xx needs to know its structure before the fields > can be assiged to. > Could I declare xx as having these fields in the first place, do Ineedto > create a type for key, val > first? select '...' as key, '...' as val into record; depesz
Re: Exact same output - pg_stat_statements
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. Best regards, depesz
Re: Exact same output - pg_stat_statements
On Tue, Jan 03, 2023 at 01:52:17PM +0800, Julien Rouhaud wrote: > 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. Why do you assume that the cost of normalization query string will be in any way measurable? Especially in comparison do extra cost of doin math on potentially thousands of rows? Best regards, depesz
Re: Disallow execution of shell commands from psql
On Tue, Jan 10, 2023 at 07:01:24PM +0100, Wiwwo Staff wrote: > Hi! > Happy new (gregorian calendar) year! > > Somehow related to the proposal of having a `psql --idle` option, is there > a way to disallow the command `\!` (and anything of the likes in psql? > > Sure, I can set the SHELL env var at run-time, but I still want to have > postgres user to be a normal user, with its shell etc, which means it can > change this SHELL setting somewhere. As far as I know, it's not possible. Why is that a problem though? \! will run command as the user that ran psql. So it's not a security issue. What's the problem then? Best regards, depesz
Re: Best Open Source OS for Postgresql
On Tue, Jan 31, 2023 at 09:02:53AM +0100, Giovanni Biscontini wrote: > Hello everyone, > we're looking for a Open Source alternative to Rhel for our VM server > dedicated to Postgresql (14->15) installations. We're testing Alma, Rocky, > and Oracle distributions as they're compatible with Rhel package systems. > Can you share your experience on a similar choice? > Thanks in advance and best regards, Giovanni It will be os that you know. I, for one, love all things debian based, so I tend to use debian or ubuntu. It helps that the Pg apt repo is absolutely AMAZING. depesz
Re: Best Open Source OS for Postgresql
On Tue, Jan 31, 2023 at 01:18:08PM +0100, Marc Millas wrote: > Did you check postgis debian repo? ?? Not sure why: 1. you ask me that 2. you ask me that off list but no, i haven't. depesz
Re: Best Open Source OS for Postgresql
On Tue, Jan 31, 2023 at 02:09:40PM +0100, Marc Millas wrote: > if you do check the debian postgis repo, you ll find that its NOT possible > to choose a postgis version. > its possible for postgis 2.4 and 2.5, then ALL 3.x versions are > inaccessible but one, that did change from time to time. > (you MUST ask for postgis 3 without being able to ask for 3.0 or 3.1 or... > its like asking for postgres 9 without .5 or .6) > 2 of my customers reverse to a RH family linux because they have been > unable to install the requested postgres/postgis version on debian. > when I did ask the team, the reply was: we cannot package for all cross > possibilities (ie. 5 postgres x 6 postgis, less some impossibilities > according to postgis matrix) While this is definitely a factor that has to be considered, OP never said anything about postgis. So, while it is perfectly possible that they do, and they have to stick to some version that is not available in debian/ubuntu, it kinda seems like a jump to assume that. Best regards, depesz
Re: A Small psql Suggestion
On Tue, Jan 31, 2023 at 11:17:16AM -0500, Raymond Brinzer wrote: > Greetings, > > There is (for me) a small speed bump in psql. I think it's worth > mentioning, minor though it is, because psql is such a polished tool > generally, and because it's something which affects me many, many times a > day. > > As it is, \d is a shortcut for \dtmvs. What I actually want to see, on a > regular basis, are my relations: \dtmv. Most of the time, the sequences > are clutter. If my habits are like most people's in this (and I suspect > they are), excluding sequences from \d would optimize for the common case. Perhaps just add this yourself? \set d '\\dtmv' and then :d or just bind \dtmv to some key like f1 or something like this? Best regards, depesz
Re: How to create directory format backup
On Wed, Feb 08, 2023 at 05:00:10PM +0200, Andrus wrote: > Hi! > > Creating backup in directory format using > > pg_dump -f "sba" -Fdirectory --jobs=32 --verbose sba > > throws error > > pg_dump: error: could not stat file "sba/282168.data.gz": value too > large > > How to fix it ? > > Server is Postgres 12 running in Debian Linux 10 under WSL > Client is pg_dump from Postgres 15 running in Windows 11 Looks like your filesystem on client is having limits on file sizes. Use better filesystem, or just dump on linux, it's filesystems usually don't hit these limits. Best regards, depesz
Re: Getting the exact SQL from inside an event trigger
On Thu, Mar 02, 2023 at 11:12:37AM +, Joe Wildish wrote: > We are using event triggers to capture DDL for subsequent replay on a logical > replica. This might be a bit different answer from what you expect, but have you seen pgl_ddl_deploy project? Best regards, depesz
Re: PL/pgSQL doesn't support variables in queries?
On Wed, May 03, 2023 at 10:25:55PM +1000, J.A. wrote: > Heya folks :) > > ms-sql person here migrating over to pgsql. One of the first thing's I > noticed with pgsql (or more specifically, PL/pgSQL) is that it doesn't > support "variables" in a query? > > for example, here's some T-SQL: > > DECLARE @fkId INTEGER Sure it does. There is nothing relating to "@" character, though. You can easily find examples in docs: https://www.postgresql.org/docs/current/plpgsql-structure.html depesz
How can I change replication slot's restart_lsn from SQL?
Hi,
I'm working on a workaround for a bug in Pg
(https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
I want to create replication slot, and advance is manually, keeping it
always a bit lagging behind real replication slot.
I can create slot, no problem:
select pg_create_logical_replication_slot('depesz', 'test_decoding');
and then I can, theoretically, advance it to whatever position with
select * from pg_replication_slot_advance('depesz', '...');
*BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
was.
How can I advance restart_lsn of a slot?
Generally my idea is to get lsn from real replication slot, subtract,
let's say 1GB from it, and advance my "fake slot" to this value, this
keeping always buffer of 1GB in case the bug with removed wal happened
again.
I will be doing this on Pg12, which can limit my options, but perhaps
there is some way to do it via plain(ish) SQL ?
Best regards,
depesz
Re: How can I change replication slot's restart_lsn from SQL?
On Tue, May 16, 2023 at 04:23:02PM +0200, hubert depesz lubaczewski wrote:
> Hi,
> I'm working on a workaround for a bug in Pg
> (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
> I want to create replication slot, and advance is manually, keeping it
> always a bit lagging behind real replication slot.
>
> I can create slot, no problem:
> select pg_create_logical_replication_slot('depesz', 'test_decoding');
> and then I can, theoretically, advance it to whatever position with
> select * from pg_replication_slot_advance('depesz', '...');
>
> *BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
> was.
>
> How can I advance restart_lsn of a slot?
>
> Generally my idea is to get lsn from real replication slot, subtract,
> let's say 1GB from it, and advance my "fake slot" to this value, this
> keeping always buffer of 1GB in case the bug with removed wal happened
> again.
>
> I will be doing this on Pg12, which can limit my options, but perhaps
> there is some way to do it via plain(ish) SQL ?
Hi,
if doing it via SQL is not an option, is there any way to have
replication slot and modify it's restart_lsn, regardless of whether it
will b e physical, or logical, and what tool shoudl I use.
I tried with pg_recvlogical, and read
https://www.postgresql.org/docs/current/protocol-replication.html but
I don't see how I can make restart_lsn advance to anything.
Best regards,
depesz
Re: How can I change replication slot's restart_lsn from SQL?
On Wed, May 17, 2023 at 08:16:41AM +0200, Laurenz Albe wrote:
> On Tue, 2023-05-16 at 19:35 +0200, hubert depesz lubaczewski wrote:
> > > I'm working on a workaround for a bug in Pg
> > > (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
> > > I want to create replication slot, and advance is manually, keeping it
> > > always a bit lagging behind real replication slot.
> > >
> > > I can create slot, no problem:
> > > select pg_create_logical_replication_slot('depesz', 'test_decoding');
> > > and then I can, theoretically, advance it to whatever position with
> > > select * from pg_replication_slot_advance('depesz', '...');
> > >
> > > *BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
> > > was.
> > >
> > > How can I advance restart_lsn of a slot?
> > >
> > > Generally my idea is to get lsn from real replication slot, subtract,
> > > let's say 1GB from it, and advance my "fake slot" to this value, this
> > > keeping always buffer of 1GB in case the bug with removed wal happened
> > > again.
> > >
> > > I will be doing this on Pg12, which can limit my options, but perhaps
> > > there is some way to do it via plain(ish) SQL ?
> >
> > if doing it via SQL is not an option, is there any way to have
> > replication slot and modify it's restart_lsn, regardless of whether it
> > will b e physical, or logical, and what tool shoudl I use.
> >
> > I tried with pg_recvlogical, and read
> > https://www.postgresql.org/docs/current/protocol-replication.html but
> > I don't see how I can make restart_lsn advance to anything.
>
> You could shutdown the server and edit the file in "pg_replslot" with
> a hex editor. Not very convenient, and you'd have to study the source
> to understand the format of the file.
OK. Shutting down production server every 5 minutes to advance the slot
isn't really an option.
How do "normal" wal consumers update restart_lsn? Is there a reason why
it can't be done using pg_rec(eive|v)* ?
Best regards,
depesz
Re: syntax pb
On Tue, May 30, 2023 at 05:53:30PM +0200, Marc Millas wrote: > Thanks Adrian, but if the query becomes more complex, for example with a > few joins more, then even casting doesn't work. > This comes from a prod environment and even casting NULLs (which is more > than strange, BTW) generates absurd errors. > Too my understanding it looks like the parser did not parse the select > distinct as we think he does. Show *real* example that doesn't work, with schema. Clearly your example can be easily made to work. Best regards, depesz
Re: Is there a bug in psql? (SELECT ''';)
On Wed, May 31, 2023 at 03:17:14PM +0800, Wen Yi wrote:
> Hi team,
> when I learn the postgres, I try to store the ' into the database,
>
> but something unexpected happend.
>
>
> postgres=# CREATE TABLE test (str varchar);
> CREATE TABLE
> postgres=# INSERT INTO test values (''');
> postgres'# SELECT * FROM test;
In here, in prompt, you can see that it's waiting for you to end your
'-qutoed string.
You seem to expect
INSERT INTO test values (''');
to do something, which it won't as it's not full query.
What do you think this should be doing?
Best regards,
depesz
Re: How to manipulate field in New record
On Wed, Jun 07, 2023 at 02:12:58PM +0200, Lorusso Domenico wrote: > Hello, > Looking for a global solution I've write e trigger function that as > optional parameter (argv[0]) receive the name of a specific parameter. > > My need is to get the filed from NEW and OLD record manipulate and set back > the field with new value. > > Problem 1: how to get and set this field > Problem 2: the field has Composit type Do this in pl/perl, pl/python, pl/tcl or anything like this. Or use one of methods shown here: https://www.depesz.com/2021/04/21/getting-value-from-dynamic-column-in-pl-pgsql-triggers/ depesz
Re: Problem perhaps after upgrading to pgadmin4 7.4
On Thu, Jul 13, 2023 at 01:20:03PM +0200, Carl Erik Eriksson wrote: > If I enter a query like select count(*) from table_1I get a correct > response from the server > If I enter select * from table_1 I get an error message that I do not > understand: > Error Message:missing FROM-clause entry for table "rel" > LINE 8: ...ER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid > PSQL tool (PGadmin on my Mac) > However if I enter the same from the PSQL tool I get the correct output. > > But that output goes to my screen and I have found no way of directing it to > a file on my Mac. > > Any suggestions? Either getting rid of the error message and getting the > output to my screen within the Query tool where I can then dump the output to > a file > OR a way of running my query using the PSQL tool and redirecting the output > to a file on my computer. > > An amateur who is up a creek without any paddle.. Which pg version you're on? Generally, try updating to newest pgadmin, from git repo (https://github.com/pgadmin-org/pgadmin4/), there is/was a bug that caused problems when using pg < 11 : https://github.com/pgadmin-org/pgadmin4/commit/732bcc2b4d91f0126cf69d69c14df199bc4106bc Best regards, depesz
Re: psql \du no more showing "member of" column
On Fri, Oct 13, 2023 at 01:39:17PM +0200, Luca Ferrari wrote: > at least, as it is shown by `psql -E`. > I wonder why this information has been removed, I'm not able to find > this in the documentation. Release notes show: https://why-upgrade.depesz.com/show?from=15.4&to=16&keywords=%5Cdu > Add psql command \drg to show role membership details (Pavel Luzanov) > The Member of output column has been removed from \du and \dg because this > new command displays this informaion in more detail. Best regards, depesz
Re: Postgresql went crazy and flooded all the SSD
On Mon, Nov 06, 2023 at 01:11:31PM +0200, Gabriel Dodan wrote: > Not sure exactly what happened but Postgresql flooded all the available SSD > space and obviously crashed. It has written a lot of data in the pg_wal > folder. Most likely it was caused by replication. The postgresql instance > that crashed was a subscriber and publisher at the same time. Even so, it > was not a heavy write replication, I mean the data that needed to be > replicated was rarely updated. Has anyone else encountered such an issue? You might want to read https://www.depesz.com/2023/06/18/why-is-my-wal-directory-so-large/ Best regards, depesz
Re: Password forgotten
On Tue, Apr 23, 2024 at 08:14:15AM -0400, Arbol One wrote: > Hello. > In my Debian box, after entering this command to psql-16, *psql -h localhost > -U postgres* You might want to read https://www.depesz.com/2008/11/28/recovering-lost-postgresql-password/ Best regards, depesz
Re: \dt shows table but \d says the table doesn't exist ?
On Fri, May 03, 2024 at 04:58:26PM -0400, David Gauthier wrote: > Soo... what am I missing ? > owner is "cron_user". \dt shows cron_user is the owner of the table. Magnus already helped you, but you might want to check this: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names and generally read the whole "Don't Do This" wiki page. Best regards, depesz
Re: Does trigger only accept functions?
On Tue, Jun 11, 2024 at 12:47:14AM +0530, veem v wrote: > to be called from ~50 triggers? or any other better approach exists to > handle this? pgaudit extension? Or just write all the changes to single table? Or use dynamic queries that will build the insert based on the name of table the event happened on? Or pass arguments? Best regards, depesz
Re: Does trigger only accept functions?
On Wed, Jun 12, 2024 at 12:19:55AM +0530, veem v wrote: > CREATE OR REPLACE FUNCTION log_deletes() > RETURNS TRIGGER AS $$ > BEGIN > IF TG_TABLE_NAME = 'source_table1' THEN > INSERT INTO delete_audit1 ( col1, col2, col3) > VALUES (OLD.col1, OLD.col2, OLD.col3); > ELSIF TG_TABLE_NAME = 'source_table2' THEN > INSERT INTO delete_audit2 ( col4, col5, col6) > VALUES (OLD.col4, OLD.col5, OLD.col6); > -- Add more conditions for other tables > ELSE > RAISE EXCEPTION 'Audit table not defined for %', TG_TABLE_NAME; > END IF; > RETURN OLD; > END; > $$ LANGUAGE plpgsql; No, I meant building dynamic queries and then EXECUTE-ing, like docs show: https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Best regards, depesz
Re: Does trigger only accept functions?
On Wed, Jun 12, 2024 at 12:50:27AM +0530, veem v wrote: > My apology, if interpreting it wrong way. It doesn't make much difference > though, but do you mean something like below? if you really have totally different structures across all tables, and you don't want to use pgaudit (which is the best solution), and you don't want to have custom function per table, then i'd use hstore datatype, and store all deleted rows, regardless of where they came from, in single log table (potentially partitioned). Something like: create table deleted_rows ( id int8 generated always as identity primary key, source_schema text, source_table text, deleting_user text, deleted_at timestamptz, deleted_row hstore ); create function log_deletes() returns trigger as $$ DECLARE BEGIN INSERT INTO deleted_rows (source_schema, source_table, deleting_user, deleted_at, deleted_row) VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, CURRENT_USER, now(), hstore(OLD) ); return OLD; END; $$ language plpgsql; and then just: create trigger x after delete on tablex for each row execute function log_deletes(); or something like this, if I made any typos. Best regards, depesz
Re: psql help
On Fri, Jul 05, 2024 at 03:54:56AM +, Murthy Nunna wrote:
> Sorry, there is no problem with the following statement and the environment
> variable. It works fine. But it terminates only one PID due to LIMIT 1. I
> want to terminate all pids that meet this criteria. If I remove LIMIT 1,
> pg_terminate_backend(pid) will not work as it expects only one pid at a time.
> So, the question is how to rewrite this psql so it loops through all pids one
> pid at a time? Thanks in advance for your help.
>
> SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity
> WHERE pid IN (select unnest(pg_blocking_pids(pid)) from pg_stat_activity
> where cardinality(pg_blocking_pids(pid)) > 0)
> and usename = 'DBUSER_10'
> and now() - state_change >= interval $a'${TIMEOUT_MINS}'$a
> order by now() - state_change >= interval
> $a'${TIMEOUT_MINS}'$a desc limit 1;
Did you try?
I don't see any reason why it wouldn't work with just 'limit 1' removed.
Best regards,
depesz
Re: SELECT is faster on SQL Server
On Fri, Mar 19, 2021 at 12:58:10PM +0200, Frank Millman wrote: > On 2021-03-19 12:00 PM, Pavel Stehule wrote: > > In this query the most slow operation is query planning. You try to do > tests on almost empty tables. This has no practical sense. > You should test queries on tables with size similar to production size. > > Sorry about that. I hope this one is better. Same query, different data set. For starters, I'm not really sure it makes sense to optimize a query that runs in 3.5 miliseconds! Having said that, after putting the plan on explain.depesz.com, I got: https://explain.depesz.com/s/xZel Which shows that ~ 50% of time was spent in scan on ar_totals and sorting it. You seem to have some really weird indexed on ar_totals created (mixed of nulls ordering). Why don't you start with simple: create index q on ar_totals (ledger_row_id, tran_date) where deleted_id = 0; But, again - either you're overthinking performance of a query that can run over 200 times per second on single core, or you're testing it with different data than the one that is really a problem. Best regards, depesz
Re: where clauses including timstamptz and intervals
On Fri, Apr 09, 2021 at 07:24:54AM +, Niels Jespersen wrote:
> Hello all
>
> Are these two queries exactly eqivalent? The table is partitioned on
> r_time, which is a timestamptz. The explain plans are not exactly the
> same. The first wants to scan a partition more than the latter.
>
> select f.xx from f
> where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
> and f.r_time < ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month');
>
> select f.xx from f
> where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
> and f.r_time < ('2020-11-01 00:00:00+00'::timestamptz);
It depends on timezone.
For example, in my timezone:
$ select ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month'),
'2020-11-01 00:00:00+00'::timestamptz;
?column?│ timestamptz
┼
2020-11-01 02:00:00+01 │ 2020-11-01 01:00:00+01
(1 row)
Please note that there is 1 hour difference.
The reason is that somewhere in there we change time due to daylight
savings.
If I'd set timezone to UTC, suddently it's the same:
$ set timezone=UTC;
SET
$ select ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month') =
'2020-11-01 00:00:00+00'::timestamptz;
?column?
──
t
(1 row)
As usual - time is more complicated than one could expect.
Best regards,
depesz
Re: replace inside regexp_replace
On Mon, Jun 21, 2021 at 02:27:22PM +0100, Oliver Kohll wrote: > It half works, i.e. it removes the brackets but doesn't seem to process the > inner replace. It's as if the select were just > select regexp_replace( > 'here is [[my text]] to replace and [[some more]]', > E'\\[\\[(.*?)\\]\\]', > E'\\1', > 'g' > ); > I've a feeling I'm missing something fundamental, any idea what? \1 works only if it's argument to regexp_replace. And regexp_replace can't call any other functions. What you could do is: $ select string_agg(x[1] || replace(x[2], ' ', '_') || x[3], '') from regexp_matches( 'here is [[my text]] to replace and [[some more]] and maybe [[a bit longer]] too', '(.*?)\[\[(.*?)\]\](.*?)', 'g') x; string_agg ─ here is my_text to replace and some_more and maybe a_bit_longer (1 row) Or just use plperl, pl/python, or anything like this. Best regards, depesz
Re: number of wal file is not reduced.
On Mon, Jul 05, 2021 at 03:18:14PM +0530, Atul Kumar wrote: > I have postgres 9.6 running server on centos 7, the number of wal file > in pg_xlog directory is above 4000 so to save some disk space, I > thought of reducing to 100. ... > Please suggest when the number of wal files will be reduced to approx 100. It's hard to say for sure. There are potential issues that might cause pg to keep more wal files than configured. Generally these kinds of things are easier solved in more interactive medium - like IRC, or Slack. But, to give you some start, check if: 1. archiving doesn't fail 2. archiving doesn't lag 3. there are no prepared transactions 4. there are no lagging replication slots Best regards, depesz
Re: The Curious Case of the Table-Locking UPDATE Query
On Mon, Jul 05, 2021 at 08:22:39PM -0300, Emiliano Saenz wrote: > We have a huge POSTGRES 9.4 database in the production environment (several > tables have more than 100.000.00 registers). Last two months we have had > problems with CPU utilization. Debugging the locks (on pg_locks) we notice > that sometimes simple UPDATE (by primary key) operation takes out > ACCESS_EXCLUSIVE_LOCK mode over these huge tables so POSTGRES DB collapses > and it generates excessive CPU consumption. My question is, How is it > possible that UPDATE operation takes out ACCESS_EXCLUSIVE_LOCK mode? > More information, this system never manifests this behavior before and we > don't make software changes on last 2 years To be able to help we will need pg_stat_activity data for the for backend that has this lock, and pg_locks information for it too. And, please, send text, and not screenshot. Best regards, depesz
Re: The Curious Case of the Table-Locking UPDATE Query
On Thu, Jul 08, 2021 at 02:35:33PM -0300, Emiliano Saenz wrote: > Attach the files. The pg_locks file doesn't show any access exclusive locks on any table? =$ awk -F, 'NR==1 || $13 == "AccessExclusiveLock"' pg_locks.csv Locktype,Database,Relation,Page,Tuple,Virtualxid,Transactionid,Classid,Objid,Objsubid,Virtualtransaction,Pid,Mode,Granted,Fastpath tuple,248043888,248044255,213071,39,NULL,NULL,NULL,NULL,NULL,198/814,21038,AccessExclusiveLock,f,f tuple,248043888,248044255,213071,39,NULL,NULL,NULL,NULL,NULL,34/90197,21187,AccessExclusiveLock,t,f tuple,248043888,248044255,213071,39,NULL,NULL,NULL,NULL,NULL,132/957,21007,AccessExclusiveLock,f,f As you can see all the AccessExclusive locks are on tuples (rows). Best regards, depesz
Re: looping over multirange segments?
On Wed, Jul 14, 2021 at 04:19:48PM -0700, Ben Chobot wrote:
> I'm really, really liking the multirange types in PG14. Thank you for making
> them! Unfortunately I am struggling with how to loop over the segments of a
> multirange. There doesn't seem to be a way to convert them to arrays, and I
> can't just use plpgsql's FOREACH on one. Am I missing something obvious? It
> seems like a reasonable thing to want to do.
>
> FWIW, my actual end goal is to take something like this:
>
> select int8range(1,10)::int8multirange - int8range(4,6)::int8multirange;
> ?column?
>
> {[1,4),[6,10)}
>
> ...and turn it into this:
>
> select ???;
> int8range │ ?column?
> ───┼──
> [1,4) │ 0
> [6,10) │ 0
There isn't anything nice now. There was, for a brief moment, unnest()
over multiranges, but it got reverted. While it's not there, you can use
regexps to split it. I know it's ugly, but it should work, for now.
For example you can:
select * from regexp_matches(_YOUR_MULTIRANGE_::text, '[\[(][^\])]+[\])]', 'g');
I wrote more, including explanation, and ready-to-use function, in here:
https://www.depesz.com/2021/07/15/how-to-get-list-of-elements-from-multiranges/
depesz
Re: psql's default database on connect (our internal ref. SRP-30861)
On Fri, Aug 06, 2021 at 08:53:22AM +0200, Matthias Apitz wrote: > What do I uderstand wrong? Please check https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS Specifically: > dbname : The database name. Defaults to be the same as the user name. > In certain contexts, the value is checked for extended > formats; see Section 33.1.1 for more details on those. > user : PostgreSQL user name to connect as. Defaults to be the same > as the operating system name of the user running the > application. depesz
Getting pg_stat_database data takes significant time
Hi, We have servers where there is single app db, but one that contains MANY schema/tables. This is on Pg 12.6. Simple query like: select * from pg_stat_database where datname = 'app_name' can take up to 800ms! #v+ QUERY PLAN ══ Subquery Scan on d (cost=0.00..2.52 rows=2 width=216) (actual time=883.623..883.631 rows=1 loops=1) -> Append (cost=0.00..2.39 rows=2 width=68) (actual time=0.019..0.025 rows=1 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=0.00..0.02 rows=1 width=68) (actual time=0.003..0.003 rows=0 loops=1) -> Result (cost=0.00..0.01 rows=1 width=68) (actual time=0.002..0.003 rows=0 loops=1) One-Time Filter: NULL::boolean -> Bitmap Heap Scan on pg_database (cost=1.24..2.35 rows=1 width=68) (actual time=0.016..0.020 rows=1 loops=1) Recheck Cond: (datname = 'app_name'::name) Heap Blocks: exact=1 -> Bitmap Index Scan on pg_database_datname_index (cost=0.00..1.24 rows=1 width=0) (actual time=0.009..0.010 rows=1 loops=1) Index Cond: (datname = 'app_name'::name) Planning Time: 0.294 ms Execution Time: 883.684 ms (12 rows) #v- I checked and it looks that the problem is with pg_stat_get_db_* functions that are used in this view. For example: #v+ =# explain (analyze on, buffers on) SELECT pg_stat_get_db_temp_bytes(7436115) AS temp_bytes; QUERY PLAN Result (cost=0.00..0.01 rows=1 width=8) (actual time=465.152..465.153 rows=1 loops=1) Planning Time: 0.017 ms Execution Time: 465.175 ms (3 rows) #v- Is there anything we could do to make it faster? The problem is that on certain servers this query takes up to 10% of total query time (as reported by pg_stat_statements). This query is being called, quite a lot, by monitoring software, and disabling it is not really an option. It is called every 15 seconds. So not extremely often, but the total_time adds up "nicely". Best regards, depesz
Re: Getting pg_stat_database data takes significant time
On Wed, Aug 11, 2021 at 06:52:15PM +0530, Vijaykumar Jain wrote: > Just taking a shot, as I have seen in some previous issues? Ignore is not > relevant. > > Can you run vacuum on pg_class and check the query again , or do you see > pg_class bloated ? pg_class is large, but vacuuming it didn't help for time of query on pg_stat_database. vacuum output: #v+ =# vacuum verbose analyze pg_class ; INFO: vacuuming "pg_catalog.pg_class" INFO: scanned index "pg_class_oid_index" to remove 3632 row versions DETAIL: CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.06 s INFO: scanned index "pg_class_relname_nsp_index" to remove 3632 row versions DETAIL: CPU: user: 0.16 s, system: 0.17 s, elapsed: 0.46 s INFO: scanned index "pg_class_tblspc_relfilenode_index" to remove 3632 row versions DETAIL: CPU: user: 0.08 s, system: 0.01 s, elapsed: 0.10 s INFO: "pg_class": removed 3632 row versions in 662 pages DETAIL: CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.09 s INFO: index "pg_class_oid_index" now contains 1596845 row versions in 11879 pages DETAIL: 3632 index row versions were removed. 852 index pages have been deleted, 835 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: index "pg_class_relname_nsp_index" now contains 1596845 row versions in 64591 pages DETAIL: 3627 index row versions were removed. 588 index pages have been deleted, 574 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: index "pg_class_tblspc_relfilenode_index" now contains 1596845 row versions in 12389 pages DETAIL: 3632 index row versions were removed. 941 index pages have been deleted, 918 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "pg_class": found 1226 removable, 59179 nonremovable row versions in 1731 out of 56171 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1556677295 There were 42246 unused item identifiers. Skipped 0 pages due to buffer pins, 13921 frozen pages. 0 pages are entirely empty. CPU: user: 0.62 s, system: 0.19 s, elapsed: 0.94 s. INFO: analyzing "pg_catalog.pg_class" INFO: "pg_class": scanned 3 of 56171 pages, containing 853331 live rows and 0 dead rows; 3 rows in sample, 1597749 estimated total rows VACUUM Time: 2687.170 ms (00:02.687) #v- > The other option would be gdb backtrace I think that would help. backtrace from what? It doesn't *break*, it just takes strangely long time. I could envision attaching gdb to pg process and getting backtrace, but when? before running the query? after? depesz
Re: Getting pg_stat_database data takes significant time
On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > 1. this depends on reading the stats file; that's done once per > transaction. So if you run the query twice in a transaction, the second > time will take less time. You can know how much time is spent reading > that file by subtracting both times. Yeah. I noticed. Looks like loading the stats file is the culprit. But does that mean that the whole stats file has to be read at once? I just need stats on db, not on relations? > 2. EXPLAIN (VERBOSE) will tell you which functions are being called by > the query. One of those loops across all live backends. Is that > significant? You could measure by creating an identical view but > omitting pg_stat_db_numbackends. Does it take the same time as the > view? If not, then you know that looping around all live backends is > slow. Even `select datid, datname, xact_commit, xact_rollback from pg_stat_database;` takes (now), a second. Second call in the same connection, different txn, 0.8s. Second call in the same transaction as first - 0.053ms. So it definitely suggests that loading the stats file is the problem. > If the problem is (1) then you could have less tables, so that the file > is smaller and thus faster to read, but I don't think you'll like that > answer; and if the problem is (2) then you could reduce max_connections, > but I don't think you'll like that either. max_connections would be related, if I understand correctly, if I had faster starts without pg_stat_db_numbackends. And I don't - takes basically the same time. > I suspect there's not much you can do, other than patch the monitoring > system to not read that view as often. Once every 15 seconds doesn't seem to be too often, but perhaps I can do something about it... Best regards, depesz
Re: Getting pg_stat_database data takes significant time
On Thu, Aug 12, 2021 at 09:08:27AM -0400, Alvaro Herrera wrote: > On 2021-Aug-11, hubert depesz lubaczewski wrote: > > > On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > > > 1. this depends on reading the stats file; that's done once per > > > transaction. So if you run the query twice in a transaction, the second > > > time will take less time. You can know how much time is spent reading > > > that file by subtracting both times. > > > > Yeah. I noticed. Looks like loading the stats file is the culprit. But > > does that mean that the whole stats file has to be read at once? I just > > need stats on db, not on relations? > > As I recall there is one file per database containing everything > pertaining to that database, and you cannot read it partially. > > Maybe you can use stats_temp_directory to put these files in faster > or less busy storage -- a RAM disk perhaps? The fie is 120MB, and is stored in tmpfs, which I assume, on Linux, is ram disk. depesz
Re: Getting pg_stat_database data takes significant time
On Thu, Aug 12, 2021 at 11:32:15AM +0200, Magnus Hagander wrote: > Which database are you connected to? If you just want to look at the > global stats, it might help to be connected to a database that is > *not* the one with all the tables in -- e.g. connect to "postgres" and > query pg_stat_database looking for values on a different database? In > this case it would open files for "global", for "database postgres" > and "shared relations" only and skip the file for your db with many > objects. I think. I'm connected to the db I need data about, and I need data from virtualy all columns of pg_stat_database. depesz
Re: Getting pg_stat_database data takes significant time
On Thu, Aug 12, 2021 at 06:20:23PM +0200, Magnus Hagander wrote: > On Thu, Aug 12, 2021 at 4:38 PM hubert depesz lubaczewski > wrote: > > > > On Thu, Aug 12, 2021 at 11:32:15AM +0200, Magnus Hagander wrote: > > > Which database are you connected to? If you just want to look at the > > > global stats, it might help to be connected to a database that is > > > *not* the one with all the tables in -- e.g. connect to "postgres" and > > > query pg_stat_database looking for values on a different database? In > > > this case it would open files for "global", for "database postgres" > > > and "shared relations" only and skip the file for your db with many > > > objects. I think. > > > > I'm connected to the db I need data about, and I need data from virtualy > > all columns of pg_stat_database. > Try connecting to a different database, while still querying all columns. Damn, this is huge difference: =$ time psql -d postgres -c "select * from pg_stat_database where datname = 'appdb'" -qAtX > /dev/null real0m0.058s user0m0.026s sys 0m0.013s =$ time psql -d appdb -c "select * from pg_stat_database where datname = 'appdb'" -qAtX > /dev/null real0m0.466s user0m0.031s sys 0m0.008s Not sure if I can make the monioring software use different connection for this one particular query, though. Will check with them. Thanks. Best regards, depesz
Can we get rid of repeated queries from pg_dump?
Hi,
I have following case: local pg_dump (v15) connecting to remote
PostgreSQL (v12).
I'm trying to get just schema (pg_dump -s). It's taking very long, which
is kinda OK given that there is long distance and latency, but I got
curious and checked queries that the pg_dump was running (select * from
pg_stat_activity where application_name = 'pg_dump').
And I noticed that many of these queries repeat many times.
The ones that I noticed were:
SELECT pg_catalog.format_type('2281'::pg_catalog.oid, NULL)
around the time that
SELECT
proretset,
prosrc,
probin,
provolatile,
proisstrict,
prosecdef,
lanname,
proconfig,
procost,
prorows,
pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,
pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,
pg_catalog.pg_get_function_result(p.oid) AS funcresult,
proleakproof,
array_to_string(protrftypes, ' ') AS protrftypes,
proparallel,
prokind,
prosupport,
NULL AS prosqlbody
FROM
pg_catalog.pg_proc p,
pg_catalog.pg_language l
WHERE
p.oid = '60188'::pg_catalog.oid
AND l.oid = p.prolang
was called too.
It seems that for every function, pg_dump is getting it's data, and then
runs format_type on each parameter/output type? I'm mostly guessing
here, as I didn't read the code.
Wouldn't it be possible to get all type formats at once, and cache them
in pg_dump? Or at the very least reuse already received information?
Unfortunately it seems I can't run pg_dump closer to the db server, and
the latency of queries is killing me.
It's been 15 minutes, and pg_dump (called: pg_dump -v -s -f schema.dump,
with env variables configuring db connection) hasn't written even single
byte to schema.dump)
depesz
Re: Can we get rid of repeated queries from pg_dump?
On Thu, Aug 26, 2021 at 10:02:07AM -0400, Tom Lane wrote: > hubert depesz lubaczewski writes: > > It seems that for every function, pg_dump is getting it's data, and then > > runs format_type on each parameter/output type? I'm mostly guessing > > here, as I didn't read the code. > > Wouldn't it be possible to get all type formats at once, and cache them > > in pg_dump? Or at the very least reuse already received information? > Send a patch ... Yeah, that's not going to work, my C skills are next-to-none :( I guess I'll have to wait till someone else will assume it's a problem, someone with skills to do something about it. Best regards, depesz
Re: Can we get rid of repeated queries from pg_dump?
On Thu, Aug 26, 2021 at 10:20:29AM -0400, Tom Lane wrote: > hubert depesz lubaczewski writes: > > On Thu, Aug 26, 2021 at 10:02:07AM -0400, Tom Lane wrote: > >> hubert depesz lubaczewski writes: > >>> Wouldn't it be possible to get all type formats at once, and cache them > >>> in pg_dump? Or at the very least reuse already received information? > > >> Send a patch ... > > > Yeah, that's not going to work, my C skills are next-to-none :( > > I guess I'll have to wait till someone else will assume it's a problem, > > someone with skills to do something about it. > > Well, you could move it forward by doing the legwork to identify which > queries are worth merging. Is it really sane to do a global "select Sure. On it. Will report back when I'll have more info. Best regards, depesz
