Re: [HACKERS] unique index violation after pg_upgrade to PG10

On Tue, Oct 24, 2017 at 02:57:47PM -0700, Peter Geoghegan wrote: > On Tue, Oct 24, 2017 at 1:11 PM, Justin Pryzby <pry...@telsasoft.com> wrote: > > ..which I gather just verifies that the index is corrupt, not sure if > > there's > > anything else to do with it?

Re: [HACKERS] unique index violation after pg_upgrade to PG10

On Tue, Oct 24, 2017 at 02:57:47PM -0700, Peter Geoghegan wrote: > On Tue, Oct 24, 2017 at 1:11 PM, Justin Pryzby <pry...@telsasoft.com> wrote: > > ..which I gather just verifies that the index is corrupt, not sure if > > there's > > anything else to do with it?

Re: [HACKERS] unique index violation after pg_upgrade to PG10

On Tue, Oct 24, 2017 at 01:48:55PM -0500, Kenneth Marshall wrote: > I just dealt with a similar problem with pg_repack and a PostgreSQL 9.5 DB, > the exact same error. It seemed to caused by a tuple visibility issue that > allowed the "working" unique index to be built, even though a duplicate row

Re: [HACKERS] unique index violation after pg_upgrade to PG10

On Tue, Oct 24, 2017 at 12:31:49PM -0700, Peter Geoghegan wrote: > On Tue, Oct 24, 2017 at 11:48 AM, Kenneth Marshall wrote: > >> Really ? pg_repack "found" and was victim to the duplicate keys, and > >> rolled > >> back its work. The CSV logs clearly show that our application

Re: [HACKERS] unique index violation after pg_upgrade to PG10

On Tue, Oct 24, 2017 at 01:27:14PM -0500, Kenneth Marshall wrote: > On Tue, Oct 24, 2017 at 01:14:53PM -0500, Justin Pryzby wrote: > > Note: > > I run a script which does various combinations of ANALYZE/VACUUM > > (FULL/ANALYZE) > > following the upgrade, and a scrip

[HACKERS] unique index violation after pg_upgrade to PG10

I upgrade another instance to PG10 yesterday and this AM found unique key violations. Our application is SELECTing FROM sites WHERE site_location=$1, and if it doesn't find one, INSERTs one (I know that's racy and not ideal). We ended up with duplicate sites, despite a unique index. We removed

[HACKERS] per-sesson errors after interrupting CLUSTER pg_attrdef

This was briefly scary but seems to have been limited to my psql session (no other errors logged). Issue with catcache (?) I realized that the backup job I'd kicked off was precluding the CLUSTER from running, but that CLUSTER was still holding lock and stalling everything else under the sun.

Re: [HACKERS] [GENERAL] huge RAM use in multi-command ALTER of table heirarchy

postmaster) total-vm:13544792kB, anon-rss:8977764kB, file-rss:8kB So I'm hoping to encourage someone to commit the change contemplated earlier. Thanks in advance. Justin On Tue, Jul 18, 2017 at 07:26:30PM -0400, Tom Lane wrote: > Justin Pryzby <pry...@telsasoft.com> writes: > > I'

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Wed, Oct 18, 2017 at 07:22:27PM +0200, Alvaro Herrera wrote: > Do you still have those core dumps? If so, would you please verify the > database that autovacuum was running in? Just open each with gdb (using > the original postgres binary, not the one you just installed) and do > "print

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Wed, Oct 18, 2017 at 06:54:09PM +0200, Alvaro Herrera wrote: > Justin Pryzby wrote: > > > No crashes in ~28hr. It occurs to me that it's a weaker test due to not > > preserving most compilation options. > > And the previous code crashes in 45 minutes? That's sol

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Tue, Oct 17, 2017 at 09:07:40AM -0500, Justin Pryzby wrote: > On Tue, Oct 17, 2017 at 09:34:24AM -0400, Tom Lane wrote: > > Justin Pryzby <pry...@telsasoft.com> writes: > > > On Tue, Oct 17, 2017 at 12:59:16PM +0200, Alvaro Herrera wrote: > > >&g

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Tue, Oct 17, 2017 at 09:34:24AM -0400, Tom Lane wrote: > Justin Pryzby <pry...@telsasoft.com> writes: > > On Tue, Oct 17, 2017 at 12:59:16PM +0200, Alvaro Herrera wrote: > >> Anyway, can give this patch a try? > > > I've only compiled postgres once before and

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Tue, Oct 17, 2017 at 12:59:16PM +0200, Alvaro Herrera wrote: > Justin Pryzby wrote: > > > #1 0x006a52e9 in perform_work_item (workitem=0x7f8ad1f94824) at > > autovacuum.c:2676 > > cur_datname = 0x298c740 "no 1 :vartype 1184 :vartypmod -1 &g

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Tue, Oct 17, 2017 at 12:59:16PM +0200, Alvaro Herrera wrote: > Anyway, can give this patch a try? I've only compiled postgres once before and this is a production environment (althought nothing so important that the crashes are a serious concern either). Is it reasonable to wget the postgres

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Sun, Oct 15, 2017 at 02:44:58PM +0200, Tomas Vondra wrote: > Thanks, but I'm not sure that'll help, at this point. We already know > what happened (corrupted memory), we don't know "how". And core files > are mostly just "snapshots" so are not very useful in answering that :-( Is there

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Sat, Oct 14, 2017 at 08:56:56PM -0500, Justin Pryzby wrote: > On Fri, Oct 13, 2017 at 10:57:32PM -0500, Justin Pryzby wrote: > > > Also notice the vacuum process was interrupted, same as yesterday (think > > > goodness for full logs). Our INSERT s

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Fri, Oct 13, 2017 at 10:57:32PM -0500, Justin Pryzby wrote: > > Also notice the vacuum process was interrupted, same as yesterday (think > > goodness for full logs). Our INSERT script is using python > > multiprocessing.pool() with "maxtasksperchild=1", which I thin

Re: [HACKERS] SIGSEGV in BRIN autosummarize

On Fri, Oct 13, 2017 at 10:57:32PM -0500, Justin Pryzby wrote: > I don't have any reason to believe there's memory issue on the server, So I > suppose this is just a "heads up" to early adopters until/in case it happens > again and I can at least provide a stack trace. I'm ba

[HACKERS] SIGSEGV in BRIN autosummarize

I upgraded one of our customers to PG10 Tuesday night, and Wednesday replaced an BTREE index with BRIN index (WITH autosummarize). Today I see: < 2017-10-13 17:22:47.839 -04 >LOG: server process (PID 32127) was terminated by signal 11: Segmentation fault < 2017-10-13 17:22:47.839 -04 >DETAIL:

Re: [HACKERS] memory fields from getrusage()

On Sat, Sep 02, 2017 at 02:00:44PM +1200, Thomas Munro wrote: > On Sat, Sep 2, 2017 at 7:46 AM, Peter Eisentraut > <peter.eisentr...@2ndquadrant.com> wrote: > > On 6/15/17 10:58, Justin Pryzby wrote: > >> On Thu, Jun 15, 2017 at 10:29:21AM -0400, Robert Haas wrote: >

[HACKERS] PG10b2: column-list UPDATE syntax fails with single column

In testing our application with PG10beta2, I discovered that our use of UPDATE broke, apparently by this commit |commit 906bfcad7ba7cb3863fe0e2a7810be8e3cd84fbd |Author: Tom Lane |Date: Tue Nov 22 15:19:57 2016 -0500 | |Improve handling of "UPDATE ... SET (column_list)

[HACKERS] tupconvert.c API change in v10 release notes

FYI, I happened across this commit comment: 3f902354b08ac788600f0ae54fcbfc1d4e3ea765 | So, let's accept the removal of the guarantee about | the output tuple's rowtype marking, recognizing that this is a API change | that could conceivably break third-party callers of tupconvert.c. (So, |

Re: [HACKERS] memory fields from getrusage()

On Thu, Jun 15, 2017 at 10:29:21AM -0400, Robert Haas wrote: > On Wed, Jun 14, 2017 at 6:28 PM, Justin Pryzby <pry...@telsasoft.com> wrote: > > On Tue, Jun 13, 2017 at 12:16:00PM -0400, Robert Haas wrote: > >> It might be worth adding platform-specific code for common platf

Re: [HACKERS] memory fields from getrusage()

On Tue, Jun 13, 2017 at 12:16:00PM -0400, Robert Haas wrote: > It might be worth adding platform-specific code for common platforms. All I care (which linux happily/happens to support) is maxrss; I was probably originally interested in this while digging into an issue with hash agg. I think it's

[HACKERS] memory fields from getrusage()

I'm interested to expose output of the remaining (memory) fields from getrusage(). postgres=# SET log_parser_stats='on'; postgres=# SELECT c.oid::regclass, usagecount FROM pg_buffercache b JOIN pg_class c USING (relfilenode) WHERE usagecount=1 ; LOG: PARSER STATISTICS DETAIL: ! system usage

[HACKERS] smallint out of range EXECUTEing prepared statement

Is this expected behavior ? ts=# SELECT * FROM t WHERE site_id=32768 LIMIT 1; (0 rows) ts=# PREPARE x AS SELECT * FROM t WHERE site_id=$1 LIMIT 1; PREPARE ts=# EXECUTE x(32768); ERROR: smallint out of range ts=# PREPARE y AS SELECT * FROM t WHERE site_id::int=$1 LIMIT 1; PREPARE ts=# EXECUTE

Re: [HACKERS] ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

On Tue, Jan 03, 2017 at 03:35:34PM -0500, Tom Lane wrote: > Justin Pryzby <pry...@telsasoft.com> writes: > > On Tue, Jan 03, 2017 at 03:18:15PM -0500, Tom Lane wrote: > >> I'm wondering if this represents some sort of out-of-sync condition > >> between the tabl

Re: [HACKERS] ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

On Tue, Jan 03, 2017 at 03:18:15PM -0500, Tom Lane wrote: > Justin Pryzby <pry...@telsasoft.com> writes: > > (gdb) bt > > #3 0x0059d5ce in ATRewriteTable (tab=, > > OIDNewHeap=, lockmode=) at > > tablecmds.c:4152 > > I'm wondering if this repres

Re: [HACKERS] ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

On Tue, Jan 03, 2017 at 02:50:21PM -0500, Tom Lane wrote: > Justin Pryzby <pry...@telsasoft.com> writes: > > On Tue, Jan 03, 2017 at 02:32:36PM -0500, Tom Lane wrote: > >> 2. Even better would be a stack trace for the call to errfinish, > >>

Re: [HACKERS] ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

On Tue, Jan 03, 2017 at 02:32:36PM -0500, Tom Lane wrote: > 3. It's pretty hard to see how you'd reach any of these places for an > ALTER COLUMN TYPE on a simple table. Has the table got rules, triggers, > default values? Could we see "\d+" output for it? I really meant to do \d+..

Re: [HACKERS] ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

On Tue, Jan 03, 2017 at 02:32:36PM -0500, Tom Lane wrote: > Justin Pryzby <pry...@telsasoft.com> writes: > >>>> I can cause the error at will on the existing table, > > That's good news, at least. > > 1. Please trigger it with "\set VERBOSITY verbose&q

Re: [HACKERS] ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

On Tue, Jan 03, 2017 at 01:40:50PM -0500, Robert Haas wrote: > On Tue, Jan 3, 2017 at 11:59 AM, Justin Pryzby <pry...@telsasoft.com> wrote: > > On Tue, Jan 03, 2017 at 11:45:33AM -0500, Robert Haas wrote: > >> > ts=# begin; drop view umts_eric_ch_switch_view, > >

Re: [HACKERS] ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

On Tue, Jan 03, 2017 at 11:45:33AM -0500, Robert Haas wrote: > > ts=# begin; drop view umts_eric_ch_switch_view, > > eric_umts_rnc_utrancell_view, umts_eric_cell_integrity_view; ALTER TABLE > > eric_umts_rnc_utrancell_metrics ALTER COLUMN PMSUMPACKETLATENCY_000 TYPE > > BIGINT USING

Re: [HACKERS] ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

On Wed, Oct 12, 2016 at 10:25:05AM -0500, Justin Pryzby wrote: > > I don't have a clear recollection how I solved this in July; possibly by > > restoring the (historic, partition) table from backup. > > > > Last week again again just now (both under 9.6), a

Re: [HACKERS] ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

this at least 4 times now, on PG95 and 9.6; 3 of those times are for the above table. Any ideas what I can do to either reproduce it or otherwise avoid it ? On Wed, Oct 12, 2016 at 10:25:05AM -0500, Justin Pryzby wrote: > We've seen this happen at least once on a 9.5 server, and twice on (the s

Re: [HACKERS] pg9.4 relpages of child tables

On Wed, Mar 18, 2015 at 12:11:22PM -0400, Tom Lane wrote: Justin Pryzby pry...@telsasoft.com writes: I believe there's been a behavior change, and not sure if it's deliberate. I don't think there's a negative consequence for our production use, but it confused me while summing relpages

[HACKERS] pg9.4 relpages of child tables

I believe there's been a behavior change, and not sure if it's deliberate. I don't think there's a negative consequence for our production use, but it confused me while summing relpages for analysis purposes, as our 9.4 customers behaved differently. Documentation indicates that in pg9.0,