Thread: Bug: Importing files of different sizes via psql -f

Bug: Importing files of different sizes via psql -f

From
Hans Plum
Date:
=========================================================================
POSTGRESQL BUG REPORT TEMPLATE
=========================================================================


Your name : Hans Plum
Your email address : plum@giub.uni-bonn.de


System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium II

Operating System (example: Linux 2.0.26 ELF) : Linux version 2.4.10

PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.1.3

Compiler used (example: gcc 2.95.2) : gcc version 2.95.3
20010315


Please enter a FULL description of your problem:
------------------------------------------------
I have tried to add a table to a database by importing the file by using
"-f" option for psql (psql -U -f file db).
If I use a file, a bit smaller than 900 byte - like 30_parser_error.sql
- it worked. Every other file, bigger than this one - like
"31_parser_error.sql", I used, did not work. There was always a error
message of the parser, saying that at the end of the file is an error -
see error message of 31_parser_error.sql in detail a bit further down.

Files and Error Messages of PSQL:

-rw-r--r-- 1 root root 890 Jan 21 16:38
30_parser_error.sql

# psql -U postgres -f 30_parser_error.sql ghl
psql:30_parser_error.sql:30: NOTICE: CREATE TABLE will create implicit
sequence 'ghl_einricht30_id_seq' for SERIAL column 'ghl_einricht30.id'
psql:30_parser_error.sql:30: NOTICE: CREATE TABLE/UNIQUE will create
implicit index 'ghl_einricht30_id_key' for table 'ghl_einricht30'
CREATE
#


-rw-r--r-- 1 root root 932 Jan 21 17:30
31_parser_error.sql

# psql -U postgres -f 31_parser_error.sql ghl
psql:31_parser_error.sql:32: ERROR: parser: parse error at or near "("
#

First, I thought, it would be a SQL syntax error, like some spaces,
tabs, commas, ... but I could not find one. When I search in the FAQs, I
could find a message, somebody described a pretty similar behaviour of
psql, but with a much smaller sql statement (but not using the import
function).


Bug, somebody else reported in January 2002:
psql bug: copy paste statements looses tab character
http://archives.postgresql.org/pgsql-sql/2002-01/msg00220.php

The only difference, I could observe, was the size of the file. It might
be a problem of the size of a buffer - I have imported larger files via
psql -f with large INSERT statements and it worked. Now, I am not sure
if it is a problem of the file size.

Now, I send you both files, I used, so that you can reproduce the error.


Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Within a PostgreSQL database environment, just try to import the 2 files
from the command line.

My examples:

# psql -U postgres -f 30_parser_error.sql ghl

# psql -U postgres -f 31_parser_error.sql ghl

Find attached both files.


If you know how this problem might be fixed, list the solution below:
----------------------------------------------------------------------

Maybe I did not recognize a SQL problem or I missed to change the size
of a special buffer, I have not read about.


Thank you for solving the problem,
Hans PlumCREATE TABLE ghl_einricht30 (

   id SERIAL,

-- Identifikation eines Datensatzes zu einer Person

   lieferant_name VARCHAR (255),
   lieferant_vorname VARCHAR (255),
   lieferant_email VARCHAR (255),

-- Recherchem�glichekeiten f�r einen Datensatz

   schlagwort1 VARCHAR (255),
   schlagwort2 VARCHAR (255),
   schlagwort3 VARCHAR (255),
   element VARCHAR (255), /* [Feuer, Wasser, Luft, Erde] */
   zielgruppe VARCHAR (255), /* [jedermann, Wirtschaft, Wissenschaftler, Studenten, Sch�ler/Lehrer, Journalisten] */

-- R�umliche Recherche und Attributierung

   titel VARCHAR (255), /* dient als Beschreibungsfeld f�r jeden objekttyp */
   x REAL,
   y REAL,
   ortsbeschr_detail VARCHAR (255), /* zur Geokodierung eines nicht durch Adressen zu refenzierenden Punktes */

-- Adressangaben f�r Veranstaltung und Einrichtung

   strasse VARCHAR (255),
   hausnr VARCHAR (10)
);
CREATE TABLE ghl_einricht35 (

   id SERIAL,

-- Identifikation eines Datensatzes zu einer Person

   lieferant_name VARCHAR (255),
   lieferant_vorname VARCHAR (255),
   lieferant_email VARCHAR (255),

-- Recherchem�glichekeiten f�r einen Datensatz

   schlagwort1 VARCHAR (255),
   schlagwort2 VARCHAR (255),
   schlagwort3 VARCHAR (255),
   element VARCHAR (255), /* [Feuer, Wasser, Luft, Erde] */
   zielgruppe VARCHAR (255), /* [jedermann, Wirtschaft, Wissenschaftler, Studenten, Sch�ler/Lehrer, Journalisten] */

-- R�umliche Recherche und Attributierung

   titel VARCHAR (255), /* dient als Beschreibungsfeld f�r jeden objekttyp */
   x REAL,
   y REAL,
   ortsbeschr_detail VARCHAR (255), /* zur Geokodierung eines nicht durch Adressen zu refenzierenden Punktes */

-- Adressangaben f�r Veranstaltung und Einrichtung

   strasse VARCHAR (255),
   hausnr VARCHAR (10),
   plz INTEGER (5),
   ort VARCHAR (255)
);

Re: Bug: Importing files of different sizes via psql -f

From
Stephan Szabo
Date:
On Tue, 22 Jan 2002, Hans Plum wrote:

> Maybe I did not recognize a SQL problem or I missed to change the size
> of a special buffer, I have not read about.

The problem is probably your usage of INTEGER (5) in the second one.
Change that to just integer and you'll probably be okay.