Обсуждение: INSERT INTO BUG

Hi all,

I'm playing with PGSQL 7.2b3.

I have a table with 1M row, when i do

insert into t2 select * from t1;

No err, but if i try to create indexs on t2 then i have

NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
't210_pkey' for table 't210'
ERROR:  index_formtuple: data takes 136708 bytes, max is 8191

t1 and t2 have the same shema :
isds=# \d t1
           Table "t1"
 Column |     Type     | Modifiers
--------+--------------+-----------
 issn   | character(9) | not null
 ind    | character(2) |
 a      | text         |
 b      | text         |
 c      | text         |


pkey is on issn field.

Any idea ?

Linux Suze 7.1 / Kernel 2.4.16 / PG 7.2b3 / GCC 2.95.2

mordicus <mordicus@free.fr> writes:
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> 't210_pkey' for table 't210'
> ERROR:  index_formtuple: data takes 136708 bytes, max is 8191

Indexes aren't toastable, so there's a limit on how large an index
entry can be.  Sorry.

            regards, tom lane

Tom Lane wrote:
> Indexes aren't toastable, so there's a limit on how large an index
> entry can be.  Sorry.
>
> regards, tom lane
>
I know, but issn field is declared as char(9) ...

When i index table t1 no problem, the problem is only  when i index table
t2
(the two tables are the same).

thx

ok,

PG 7.2b3 / Kernel 2.4.16 /

I have a table named t210

I create a second table with the same shema :

isds=# \d t210
           Table "t210"
 Column |     Type     | Modifiers
--------+--------------+-----------
 issn   | character(9) | not null
 ind    | character(2) |
 a      | text         |
 b      | text         |
 c      | text         |
Primary key: t210_pkey
Triggers: RI_ConstraintTrigger_40018877

isds=# create table t210_tmp(issn char(9) NOT NULL,ind char(2), a text, b
text,c text);
CREATE
isds=# insert into t210_tmp select * from t210;
INSERT 0 749668
isds=# alter table t210_tmp add primary key(issn);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
't210_tmp_pkey' for table 't210_tmp'
FATAL 2:  open of /mnt/oldopt/data//pg_clog/0008 failed: No such file or
directory
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: NOTICE:  Message
from PostgreSQL backend:
        The Postmaster has informed me that some other backend
        died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your database system connection and exit.
        Please reconnect to the database system and repeat your query.
Failed.
!#

postgres@localhost:~/data > psql isds
isds=# select * from t210_tmp limit 50 offset 50000;
   issn    | ind |                      a                       |        b
       | c
-----------+-----+----------------------------------------------+------------------+---
 0430-0947 |     | Apte�cn. delo                                |
       |
 1455-0423 |     | Apteekin hyllylt�a                           |
       |
[SNIP]

isds=# select * from t210_tmp limit 50 offset 500000;
FATAL 2:  open of /mnt/oldopt/data//pg_clog/0008 failed: No such file or
directory
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

I can send a dump from the table t210 is necessary.

thx