Subject: Re: Less than ideal error reporting in pg_stat_statements
To: Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@2ndquadrant.com>
References: <5601D354.5000703@BlueTreble.com>
 <CAM3SWZRo_5ZznTp=Mg5Kh2eq-LjWLVS-BXJwsHe_dS50g34Mfw@mail.gmail.com>
 <15898.1442965225@sss.pgh.pa.us>
 <CAM3SWZT41NeiK74J4PeKBA=rkML6-TR1pmj_ABf7Y6c8ihR+4A@mail.gmail.com>
 <CAM3SWZTOjkP9EJsaUcvhpHxXfdCyXbCcFuTDNT4Qct90ZMU8Hw@mail.gmail.com>
 <56030E72.9070104@BlueTreble.com>
 <CAM3SWZRr6WqO7ZZc-EfuZU97TN6wcSiXvi8mRZe6ZGY6WCz+Mw@mail.gmail.com>
 <30112.1444058156@sss.pgh.pa.us> <561296EC.805@dunslane.net>
 <20151005154032.GF8531@alvherre.pgsql> <31122.1444060230@sss.pgh.pa.us>
CC: Andrew Dunstan <andrew@dunslane.net>, Peter Geoghegan <pg@heroku.com>, Pg
 Hackers <pgsql-hackers@postgresql.org>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <561808E9.5030502@BlueTreble.com>
Date: Fri, 9 Oct 2015 13:35:21 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0)
 Gecko/20100101 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: <31122.1444060230@sss.pgh.pa.us>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:23
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 09 Oct 2015 18:35:27.1267 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY2PR11MB0678
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 10/5/15 10:50 AM, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> Andrew Dunstan wrote:
>>> FWIW, (a) and (b) but not (c) is probably the right description for my
>>> client who has been seeing problems here.
>
>> I think the fact that long IN lists are fingerprinted differently
>> according to the number of elements in the list makes the scenario
>> rather very likely -- not particularly narrow.
>
> That's certainly something worth looking at, but I think it's probably
> more complicated than that.  If you just write "WHERE x IN (1,2,3,4)",
> that gets folded to a ScalarArrayOp with a single array constant, which
> the existing code would deal with just fine.  We need to identify
> situations where that's not the case but yet we shouldn't distinguish.
>
> In any case, that's just a marginal tweak for one class of query.
> I suspect the right fix for the core problem is the one Peter mentioned
> in passing earlier, namely make it possible to do garbage collection
> without having to slurp the entire file into memory at once.  It'd be
> slower, without a doubt, but we could continue to use the existing code
> path unless the file gets really large.

To address what Peter raised up-thread, according to my client the 
process that was doing this was generating 10,000 inserts per 
transaction and sending them all as a single statement. They tried 
cutting it to 1000 inserts and it still had the problem. Each overall 
command string could have been megabytes in size. Perhaps it's not worth 
supporting that, but if that's the decision then there needs to at least 
be better error reporting around this.

I'll try to test Tom's patch next week to see what affect it has on this.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers