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,
depe
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 regar
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
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 w
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
28
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,
d
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
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
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
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
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
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
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 sl
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_st
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,
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
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()
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
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
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 custo
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
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
> 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 lea
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' THE
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 th
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
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: serv
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=0xe4f8
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 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
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 numbe
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
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 dow
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
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 receive
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
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
> attach
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
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
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
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
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 plea
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 exam
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
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
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
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_
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 h
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 rat
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
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 specif
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:
>
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
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
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,
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
> -+--
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_work
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
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
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 t
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 a
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 pac
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
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.
> (yo
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
> d
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 ?
>
>
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
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:
>
> DECL
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_logic
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
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)
> >
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
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'#
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
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
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
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 c
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
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_cas
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
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_TA
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 bes
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_
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 s
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.
>
> sele
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
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
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
> tha
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
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
> ca
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 con
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
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 h
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 subtra
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
> > &
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_databa
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
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 * fro
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&#
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
1 - 100 of 138 matches
Mail list logo
