Running pg10.2, I have a handful of maintenance jobs run in the middle of the
night, which appear to have gotten stuck waiting on each other..
. reindex - loop around pg_repack or psql -c REINDEX $table for those which
can't
be repacked)
. vacuum - mostly runs ANALYZE, like on empty parent t
On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote:
> Justin Pryzby writes:
> > Running pg10.2, I have a handful of maintenance jobs run in the middle of
> > the
> > night, which appear to have gotten stuck waiting on each other..
>
> > ts=# SELECT granted,
On Thu, Mar 08, 2018 at 03:05:36PM -0500, Tom Lane wrote:
> Justin Pryzby writes:
> > On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote:
> >> PID 20488 is evidently waiting for PID 6471 to finish its transaction.
> >> What's that one doing?
>
> >
I (finally) realized that my script for ANALYZEing parents of table hierarchies
every month or so was looping around the same parent tables every night due to
no stats for date of last last analysis.
I guess that's deliberate/known and maybe related to relkind='p' having no
relfilenode.
Is there
On Thu, May 03, 2018 at 07:44:24AM -0700, Adrian Klaver wrote:
> On 05/03/2018 07:14 AM, Justin Pryzby wrote:
> >I (finally) realized that my script for ANALYZEing parents of table
> >hierarchies
> >every month or so was looping around the same parent tables every night due
On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote:
> On 05/03/2018 09:20 AM, Alvaro Herrera wrote:
> >>https://www.postgresql.org/docs/10/static/sql-createtable.html
> >>
> >>"A partitioned table is divided into sub-tables (called partitions), which
> >>are created using separate CREATE
On Thu, May 03, 2018 at 11:15:19AM -0700, Adrian Klaver wrote:
> On 05/03/2018 10:38 AM, Justin Pryzby wrote:
> >On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote:
> >>On 05/03/2018 09:20 AM, Alvaro Herrera wrote:
> >>>>https://www.postgresql.org/do
I recall seeing various discussions hoping that it'd been finally fixed - Just
wanted to report that this has happened now under postgres 10.4.
It looks like this is not related to: 0408e1ed599b06d9bca2927a50a4be52c9e74bb9
which is for "unexpected chunk number" (?)
Note that this is on the postgr
On Sat, May 19, 2018 at 11:08:23AM -0400, Tom Lane wrote:
> Justin Pryzby writes:
> > I'll defer fixing this for awhile in case someone wants me to save a copy of
> > the relation/toast/index. From last time, I recall this just needs the
> > right
> > combinatio
On Sat, May 19, 2018 at 11:24:57AM -0500, Justin Pryzby wrote:
> On Sat, May 19, 2018 at 11:08:23AM -0400, Tom Lane wrote:
> > Justin Pryzby writes:
> > > I'll defer fixing this for awhile in case someone wants me to save a copy
> > > of
> > > the relati
On Sat, May 19, 2018 at 02:39:26PM -0400, Tom Lane wrote:
> Hm, so was the timeout error happening every time through on that table,
> or just occasionally, or did you provoke it somehow? I'm wondering how
> your 9s timeout relates to the expected completion time.
I did not knowingly provoke it :
On Sat, May 19, 2018 at 02:39:26PM -0400, Tom Lane wrote:
> Justin Pryzby writes:
> > [pryzbyj@database ~]$ while :; do for db in `psql postgres -Atc "SELECT
> > datname FROM pg_database WHERE datallowconn"`; do for t in pg_statistic
> > pg_attrdef
Is that considered an actionable problem?
Encountered while trying to reproduce the vacuum full pg_statistic/toast_2619
bug; while running a loop around VAC FULL and more in another session:
[1]- Running { time sh -ec 'while :; do psql --port 5678
postgres -qc "VACUUM FULL pg_toa
Before I forget any more, this is a brain of issues/considerations/concerns
with our (partial) transition to partitioned tables over the last ~9 months. I
believe these are all documented behaviors, but could be seen by users as a
gratuitious/unexpected change or rough edge and the differences cou
On Sun, Jun 03, 2018 at 12:13:49PM -0700, Adrian Klaver wrote:
> On 06/01/2018 03:14 PM, Justin Pryzby wrote:
> >Before I forget any more, this is a brain of issues/considerations/concerns
Should have said brain DUMP
Keep in mind, I've phrased these as notes, maybe useful to someo
I noticed that this is accepted:
postgres=# ALTER TABLE t SET (toast.asdf=128);
ALTER TABLE
I thought since "toast" was a core namespace, it would've been rejected?
I recall having read a discussion about verifying these ... I wasn't able
to find what I was thinking of, but found this one.
https
On Mon, Jun 11, 2018 at 11:47:59AM -0400, Alvaro Herrera wrote:
> On 2018-Jun-11, Justin Pryzby wrote:
>
> > I noticed that this is accepted:
> >
> > postgres=# ALTER TABLE t SET (toast.asdf=128);
> > ALTER TABLE
> >
> > I thought since "toast&q
I'm buried and not having any good ideas how to diagnose this or what else to
send, so here it is. Feel free to tell me why I'm the one whose confused..
postgres=# SELECT pid, now(), query_start, state, query FROM pg_stat_activity
WHERE pid=27757;
pid | now |
One of our instances was killed:
| Out of memory: Kill process 27670 (postmaster) score 381 or sacrifice child
| Killed process 27670 (postmaster) total-vm:13207796kB, anon-rss:5736kB,
file-rss:0kB, shmem-rss:12421696kB
The VM has: 32GB RAM, shared_buffers=12GB, work_mem=128MB
Running only pg11
ing CLUSTER. I imagine that's
related issue. I haven't seen this in awhile (but stopped trying to reproduce
it long ago). A recently-deployed update to this maintenance script is
probably why it's now doing CLUSTER.
On Fri, May 25, 2018 at 08:49:50AM -0500, Justin Pryzby wrote:
&g
This is a "heads up" for others upgrading to v12. I found a solution for our
use case, but it'd be easy to miss this, even if you read the release notes.
I saw this and updated our scripts with pg_restore -f-
https://www.postgresql.org/docs/12/release-12.html
|In pg_restore, require specification
On Fri, Jul 24, 2020 at 09:14:04PM +0200, Pavel Stehule wrote:
> pá 24. 7. 2020 v 16:38 odesílatel Christophe Pettus
> napsal:
> > Since we already special-case parent tables for partition sets, would a
> > storage parameter that lets you either tell the planner "no, really, zero
> > is reasonabl
A couple months ago, I implemented prepared statements for PyGreSQL. While
updating our application in advance of their release with that feature, I
noticed that our query logs were several times larger.
With non-prepared statements, we logged to CSV:
|message| SELECT 1
With SQL
Hi,
On Fri, Feb 08, 2019 at 02:11:33PM -0700, PegoraroF10 wrote:
> *Well, now we have two queries which stops completelly our postgres server.
> That problem occurs on 10.6 and 11.1 versions.
> On both server the problem is the same.
> Linux logs of old crash are:*
> Feb 1 18:39:53 fx-cloudserv
On Fri, Feb 08, 2019 at 07:29:53AM -0600, Justin Pryzby wrote:
> A couple months ago, I implemented prepared statements for PyGreSQL. While
> updating our application in advance of their release with that feature, I
> noticed that our query logs were several times larger.
Previousl
On Mon, Nov 26, 2018 at 07:00:35PM -0800, Andres Freund wrote:
> The fix is easy enough, just adding a
> v_hoff = LLVMBuildZExt(b, v_hoff, LLVMInt32Type(), "");
> fixes the issue for me.
On Tue, Jan 29, 2019 at 12:38:38AM -0800, pabloa98 wrote:
> And perhaps should I modify this too?
> If that
Moving to -general list (-hackers is for development topics like proposed
patches and patch reviews and beta testing and crash reports).
On Thu, Feb 28, 2019 at 10:13:58AM -0800, Perumal Raj wrote:
> could not load library "$libdir/pg_reorg":
> ERROR: could not access file "$libdir/pg_reorg": No
On Mon, Mar 04, 2019 at 01:37:30PM -0800, Perumal Raj wrote:
> I could see bunch of functions under reorg schema.
Those functions are the ones preventing you from upgrading.
You should drop schema pg_reorg cascade.
You can run it in a transaction first to see what it will drop.
But after the upgra
On Mon, Mar 04, 2019 at 02:21:11PM -0800, Perumal Raj wrote:
> Does it mean that these functions are default and came with 9.2 ?
> I am wondering how these functions are created in the DB as the
> library($libdir/pg_reorg) is not exists in system
I don't think it's default.
But was probably insta
On Tue, Mar 05, 2019 at 08:09:12AM -0800, Perumal Raj wrote:
> Thanks Sergei/Justin for the continues update.
>
> So reorg Schema might be created as part of some scripts prior to 9.2
> Version ?
I'm guessing they were probably created in 9.2.
> These are the functions in DB not the Extension. H
On Wed, Mar 06, 2019 at 09:44:16PM -0800, Perumal Raj wrote:
> Any pointers for pg_repack schema creation ?
With recent postgres, you should use just: "CREATE EXTENSION pg_repack", which
does all that for you.
> Will there be any impact in the future , Since i used --link option ?
You probably h
I wrote a script to import CSV query logs as described here:
https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG
I also created indices for searching:
|CREATE INDEX ON postgres_log_2019_04_09_2300 ((regexp_replace(message,
'^duration: ([.0-9]+) ms.*',
Is it still impossible to distinguish whether a row was inserted vs updated ?
The latest I can see is here:
https://wiki.postgresql.org/wiki/UPSERT#RETURNING_behavior
..but I'm hopeful that the 4 year old wiki page is out of date.
Justin
On Tue, May 21, 2019 at 06:57:36PM -0700, Adrian Klaver wrote:
> On 5/21/19 6:34 PM, Justin Pryzby wrote:
> >Is it still impossible to distinguish whether a row was inserted vs updated ?
>
> You will need to be more specific.
Sorry, I mean with UPSERT / "INSERT .. ON CONFLI
On Mon, Mar 13, 2023 at 06:43:01PM +0100, Peter J. Holzer wrote:
> On 2023-03-13 09:21:18 -0800, Israel Brewster wrote:
> > I’m running a postgresql 13 database on an Ubuntu 20.04 VM that is a bit
> > more
> > memory constrained than I would like, such that every week or so the various
> > process
On Wed, Aug 25, 2021 at 11:47:57PM +0800, Sudhir Guna wrote:
> Hi All,
>
> I would like to clarify on OS upgrade for PostgreSQL.
>
> Currently we are on PostgreSQL 9.6 on RHEL 7.5. We would like to do a
> direct upgrade from RHEL 7.5 to RHEL 8.4 and then upgrade PostgreSQL 9.6 to
> PostgreSQL 11
e from postgres 9.6 to 11;
You may need to install updated PGDG packages for the RH8.
How are you planning to upgrade postgres ?
Using pg_upgrade, or pg_dump, or ??
> Postgres is installed using PGDG packages.
>
> On Thu, Aug 26, 2021 at 1:15 AM Justin Pryzby wrote:
>
> > On We
I'm sorry to report this previously reported problem is happening again,
starting shortly after pg_upgrading a customer to PG10.1 from 9.6.5.
As $subject: backends are stuck in startup for minutes at a time. I didn't
strace this time, but I believe last time I saw one was waiting in a futex.
pos
On Tue, Nov 21, 2017 at 03:45:58PM -0800, Andres Freund wrote:
> On 2017-11-21 18:21:16 -0500, Tom Lane wrote:
> > Justin Pryzby writes:
> > > As $subject: backends are stuck in startup for minutes at a time. I
> > > didn't
> > > strace this time, but
On Tue, Nov 21, 2017 at 03:40:27PM -0800, Andres Freund wrote:
> Hi,
>
> On 2017-11-21 17:09:26 -0600, Justin Pryzby wrote:
> > I'm sorry to report this previously reported problem is happening again,
> > starting shortly after pg_upgrading a customer to PG10.1 from 9
On Tue, Nov 21, 2017 at 03:40:27PM -0800, Andres Freund wrote:
> Hi,
>
> On 2017-11-21 17:09:26 -0600, Justin Pryzby wrote:
> > I'm sorry to report this previously reported problem is happening again,
> > starting shortly after pg_upgrading a customer to PG10.1 from 9
On Wed, Nov 22, 2017 at 01:27:12PM -0500, Tom Lane wrote:
> Justin Pryzby writes:
> [ in an earlier post: ]
> > BTW this is a VM run on a hypervisor managed by our customer:
> > DMI: VMware, Inc. VMware Virtual Platform/440BX Desktop Reference Platform,
> > BIOS 6.00 06/
On Wed, Nov 22, 2017 at 01:27:12PM -0500, Tom Lane wrote:
> Justin Pryzby writes:
> > On Tue, Nov 21, 2017 at 03:40:27PM -0800, Andres Freund wrote:
> >> Could you try stracing next time?
>
> > I straced all the "startup" PIDs, which were all in futex, witho
On Wed, Nov 22, 2017 at 07:43:50PM -0500, Tom Lane wrote:
> Justin Pryzby writes:
> > For starters, I found that PID 27427 has:
>
> > (gdb) p proc->lwWaiting
> > $1 = 0 '\000'
> > (gdb) p proc->lwWaitMode
> > $2 = 1 '\001'
>
>
On Sat, Nov 25, 2017 at 05:45:59PM -0500, Tom Lane wrote:
> Justin Pryzby writes:
> > We never had any issue during the ~2 years running PG96 on this VM, until
> > upgrading Monday to PG10.1, and we've now hit it 5+ times.
>
> > BTW this is a VM run on a hyper
45 matches
Mail list logo
