0

I have created a new postgres server and when I trying to execute this sql command:

 DROP TABLE IF EXISTS table_3;
 CREATE TABLE table_3 AS
    SELECT 
       t1.id,
       t1.fild_2,
       t1.fild_3,
       t1.fild_4,
       t1.fild_5,
       t2.fild_6,
       t2.fild_7,
       t2.fild_8
  FROM table1 t1
       RIGHT JOIN  table2 t2 
       ON t1.id = t2.id

table1 and table2 contains around 300 000 records I got an error like this:

ERROR:  could not extend file "base/367684/370837.16": wrote only 4096 of 8192 bytes at block 2182494
HINT:  Check free disk space.
SQL state: 53100

Any ideas ?

Regards, Arrmlet

Arrmlet
  • 69
  • 2
  • 6
  • 2
    perhaps you should check the free disk space? `HINT: Check free disk space.` ?? – Mr.P Dec 22 '20 at 10:33
  • That's not a "memory" issue, it's a disk space issue. The hard disk where your Postgres database is stored is full. –  Dec 22 '20 at 10:52
  • @a_horse_with_no_name so I need to clean up disk space? Or what? – Arrmlet Dec 22 '20 at 10:55
  • Problem with disk space https://stackoverflow.com/questions/14346371/postgresql-find-total-disk-space-used-by-a-database – mbielecki1 Dec 22 '20 at 11:13

1 Answers1

1

Your join conditions are probably not correct. You can calculate how many rows there are by using:

select sum(t2.cnt * coalesce(t1.cnt, 1))
from (select t2.id, count(*) as cnt
      from table2 t2
      group by t2.id
     ) t2 left join
     (select t1.id, count(*) as cnt
      from table1 t1
      group by t1
     ) t1
     on t1.id = t2.id;

My guess is that the number of rows is way larger than you expect. If that is the case, then running out of free space is merely a symptom of a badly formed query.

However, if this version of the query runs out of space, then the issue really is the free space.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786