From brent.wood@blazemail.com Mon Dec 13 06:59:55 1999 Received: from smtp2.ihug.co.nz (tk2.ihug.co.nz [203.29.160.14]) by hub.org (8.9.3/8.9.3) with ESMTP id BAA91376 for ; Mon, 13 Dec 1999 01:59:55 -0500 (EST) (envelope-from brent.wood@blazemail.com) Received: from blazemail.com (p56-max11.wlg.ihug.co.nz [209.78.48.56]) by smtp2.ihug.co.nz (8.9.3/8.9.3/Debian/GNU) with ESMTP id TAA24622 for ; Mon, 13 Dec 1999 19:59:49 +1300 Message-ID: <385493F8.D8D17B68@blazemail.com> Date: Mon, 13 Dec 1999 19:36:40 +1300 From: Brent Wood X-Mailer: Mozilla 4.6 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Problem copying polygon data into a table Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have an text file in the format: 800|((180.87575,-45.98757),(180.87868, -45.98798),...,(Xn,Yn)) to be read into a table of: attr type id int region polygon using the command copy from '' using delimiters '|'; The polygon has about 800 vertices (& is relatively small as some of the polygons in my dataset go). Trying to copy this into the table generates the error msg: ERROR: Tuple is too big: size 12892 Does this mean that I've done summat incorrect, or that there is an undocumented limit in what can be loaded in a "copy" command, or a limit in the size (not area) of a polygon attribute? The message gives me the impression that there is a limit in the length of the string representing the polygon, so I could possibly fit more vertices by reducing the precision of each, but it still implies a limit which may render PostgreSQL unsuitable for my purposes. Any advice appreciated.... Thanks, Brent From wieck@debis.com Mon Dec 13 07:36:42 1999 Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id CAA00206 for ; Mon, 13 Dec 1999 02:36:42 -0500 (EST) (envelope-from wieck@debis.com) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m11xPvF-0003kGC; Mon, 13 Dec 99 08:29 MET Message-Id: From: wieck@debis.com (Jan Wieck) Subject: Re: [SQL] Problem copying polygon data into a table To: brent.wood@blazemail.com (Brent Wood) Date: Mon, 13 Dec 1999 08:29:29 +0100 (MET) Cc: pgsql-sql@postgreSQL.org Reply-To: wieck@debis.com (Jan Wieck) In-Reply-To: <385493F8.D8D17B68@blazemail.com> from "Brent Wood" at Dec 13, 99 07:36:40 pm X-Mailer: ELM [version 2.4 PL25] Content-Type: text > The polygon has about 800 vertices (& is relatively small as some of the > polygons in my dataset go). > > Trying to copy this into the table generates the error msg: > > ERROR: Tuple is too big: size 12892 It is a very old limitation, that one row (including the systems per row information and ALL attribute values) cannot exceed 8K - pageheader. 800 vertices is 1600 double precision floats is 12800 bytes. We actually discuss how to tackle that problem in a future release. And that far we know about a solution that could make your problem go away. What we don't have is a consensus on, and implementation of this solution. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) # From tgl@sss.pgh.pa.us Mon Dec 13 08:01:03 1999 Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA06227 for ; Mon, 13 Dec 1999 03:01:03 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id DAA08444; Mon, 13 Dec 1999 03:00:31 -0500 (EST) To: Brent Wood cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Problem copying polygon data into a table In-reply-to: Your message of Mon, 13 Dec 1999 19:36:40 +1300 <385493F8.D8D17B68@blazemail.com> Date: Mon, 13 Dec 1999 03:00:31 -0500 Message-ID: <8442.945072031@sss.pgh.pa.us> From: Tom Lane Brent Wood writes: > The polygon has about 800 vertices (& is relatively small as some of the > polygons in my dataset go). > Trying to copy this into the table generates the error msg: > ERROR: Tuple is too big: size 12892 > Does this mean that I've done summat incorrect, or that there is an > undocumented limit in what can be loaded in a "copy" command, or a limit > in the size (not area) of a polygon attribute? There is a limit, but it's hardly "undocumented" --- you're running into the infamous 8K-per-tuple limit. I believe polygons are stored with two float8's per vertex, so an 800-vertex polygon would take 16*800 bytes which matches your error message pretty nearly. You can only expect to fit maybe 500 vertexes in the standard 8K block size ... less if there's much other data in your tuples :-( This is a longstanding problem. There is discussion raging right now on the pghackers list about fixing it, and I think something may actually happen in the next release or two. In the meantime, the only reasonably simple recourse is to increase BLCKSZ (see src/include/config.h). But you can only bump it up as far as 32K, which'd be about 2K polygon vertexes; I don't know if that's enough for your purposes. regards, tom lane