Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gIDOg-0003mr-8P for pgsql-admin@arkaria.postgresql.org; Thu, 01 Nov 2018 13:52:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gIDOe-0003SE-Jr for pgsql-admin@arkaria.postgresql.org; Thu, 01 Nov 2018 13:52:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gIDOe-0003Rz-3E for pgsql-admin@lists.postgresql.org; Thu, 01 Nov 2018 13:52:28 +0000 Received: from st11p06im-asmtp001.me.com ([17.172.125.149]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gIDOb-0007Bf-Jx for pgsql-admin@lists.postgresql.org; Thu, 01 Nov 2018 13:52:26 +0000 Received: from process-dkim-sign-daemon.st11p06im-asmtp001.me.com by st11p06im-asmtp001.me.com (Oracle Communications Messaging Server 8.0.2.2.20180531 64bit (built May 31 2018)) id <0PHI00000OU6OM00@st11p06im-asmtp001.me.com> for pgsql-admin@lists.postgresql.org; Thu, 01 Nov 2018 13:52:23 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mac.com; s=04042017; t=1541080343; bh=4B/dVWLb8TSBuxGte5ceuHoTv6WERbFOMQaCw1kRHtE=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=aGUeYzIn3voqtdRH1SWIvzmz6rA4ENy0Sk8xXalrHVPTbigAoYrrlxLun7UJA95lo aWkVtDq8VNs7zJHBqQIGtEZSra8TKCY9vxyRHDSD9DCGUeLzDz5QcooIGlfpRg9Z/k YpHantf+HP6ewpkGZ/wjv/SFROwFNzI/9NrCyTBmMppncv2dveUSUCHI4XCwkurcmk 58sq5NxIC2hFnyp2LCTYjjpIJHvOzB/9+PoWnxGkq1vplm+NV0h1MgcCjVP0mIbj4U 56K6/+v94PVus6cmnMau9kOW2WNXfR8kKck4OizS+Ahb4huRlhe3O43FwPqoD52u1a MM/zi9fEVNZQQ== Received: from icloud.com ([127.0.0.1]) by st11p06im-asmtp001.me.com (Oracle Communications Messaging Server 8.0.2.2.20180531 64bit (built May 31 2018)) with ESMTPSA id <0PHI012SRP781340@st11p06im-asmtp001.me.com>; Thu, 01 Nov 2018 13:52:22 +0000 (GMT) X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 suspectscore=0 malwarescore=0 phishscore=0 bulkscore=0 spamscore=0 clxscore=1015 mlxscore=0 mlxlogscore=999 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1807170000 definitions=main-1811010122 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2018-11-01_09:,, signatures=0 From: Evan Bauer Message-id: <9D2383E2-2492-4F3F-BE51-D665E2AE0D86@mac.com> Content-type: multipart/alternative; boundary="Apple-Mail=_0F705084-F937-440B-9CF3-8D9E48DB2499" MIME-version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\)) Subject: Re: change bigint column to int Date: Thu, 01 Nov 2018 09:52:19 -0400 In-reply-to: Cc: pgsql-admin@lists.postgresql.org To: Mariel Cherkassky References: <117C35AF-8395-4D7F-842B-A63B7141269B@mac.com> X-Mailer: Apple Mail (2.3445.9.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_0F705084-F937-440B-9CF3-8D9E48DB2499 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Mariel, My suggestion was that you have confidence that you don=E2=80=99t find = yourself 90% through the shrink process and unexpectedly find that it = fails on an "integer out of range error=E2=80=9D like this: ares_net=3D# create table test_int (test_big bigint, test_int int); CREATE TABLE ares_net=3D# insert into test_int values (5000000000, 0); INSERT 0 1 ares_net=3D# select * from test_int; test_big | test_int=20 ------------+---------- 5000000000 | 0 (1 row) ares_net=3D# update test_int set test_int =3D test_big; ERROR: integer out of range ares_net=3D#=20 If you already know (because of domain knowledge or because of = constraints on how the data was loaded an updated) that all of your = values are in the INT range, then this is unnecessary. If you aren=E2=80=99= t sure and you need to check the value of unindexed columns (which would = require a full table scan), then do the update in a procedure that = checks that the 8-byte value value will fit into 4-byte int =E2=80=94 = and does appropriate things should it not fit. Cheers, - Evan Evan Bauer eb@evanbauer.com +1 646 641 2973 Skype: evanbauer > On Nov 1, 2018, at 08:54, Mariel Cherkassky = wrote: >=20 > I want to do it on a very big tables(100M records). Do you think that = it will be slow ? I mean if I have an AccessExclusiveLock for a lot of = time I cant just run a query and take my time until it finishes..=20 >=20 > =E2=80=AB=D7=91=D7=AA=D7=90=D7=A8=D7=99=D7=9A =D7=99=D7=95=D7=9D =D7=94=D7= =B3, 1 =D7=91=D7=A0=D7=95=D7=91=D7=B3 2018 =D7=91-13:55 =D7=9E=D7=90=D7=AA= =E2=80=AAEvan Bauer=E2=80=AC=E2=80=8F <=E2=80=AAevanbauer@mac.com = =E2=80=AC=E2=80=8F>:=E2=80=AC > Mariel, >=20 > Just run a query and make certain that all values currently stored in = the column fit in 4 bytes (+/- 2,147,483,648) before doing the ALTER. >=20 > Cheers, >=20 > Evan >=20 > Sent from my iPhone >=20 > On Nov 1, 2018, at 06:20, Mariel Cherkassky = > = wrote: >=20 >> Hi, >>=20 >> I want to change the column type from bigint to int to save space. By = doing so, does it include some risks ? As I see it, I should only run = vacuum full afterwards if I want to return space to the os right ? >>=20 --Apple-Mail=_0F705084-F937-440B-9CF3-8D9E48DB2499 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Mariel,

My = suggestion was that you have confidence that you don=E2=80=99t find = yourself 90% through the shrink process and unexpectedly find that it = fails on an "integer out of range error=E2=80=9D like this:

ares_net=3D# create table test_int (test_big bigint, test_int = int);
CREATE = TABLE
ares_net=3D# insert into test_int values (5000000000, = 0);
INSERT = 0 1
ares_net=3D# select * from test_int;
  test_big  | = test_int 
------------+----------
 5000000000 |        = 0
(1 = row)

ares_net=3D# update test_int set test_int =3D = test_big;
ERROR:  integer out of range
ares_net=3D# 

If you = already know (because of domain knowledge or because of constraints on = how the data was loaded an updated) that all of your values are in the = INT range, then this is unnecessary.  If you aren=E2=80=99t sure = and you need to check the value of unindexed columns (which would = require a full table scan), then do the update in a procedure that = checks that the 8-byte value value will fit into 4-byte int =E2=80=94 = and does appropriate things should it not fit.

Cheers,

- = Evan

Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer


On Nov 1, 2018, at 08:54, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:

I want to do it on a = very big tables(100M records). Do you think that it will be slow ? I = mean if I have an AccessExclusiveLock for a lot of time I cant just run = a query and take my time until it finishes.. 

=E2=80=AB=D7=91=D7=AA=D7=90=D7=A8=D7=99=D7=9A =D7=99=D7=95=D7=9D= =D7=94=D7=B3, 1 =D7=91=D7=A0=D7=95=D7=91=D7=B3 2018 =D7=91-13:55 =D7=9E=D7= =90=D7=AA =E2=80=AAEvan Bauer=E2=80=AC=E2=80=8F <=E2=80=AAevanbauer@mac.com=E2=80=AC=E2=80=8F>:=E2=80=AC
Mariel,

Just run a query and make certain that all values currently = stored in the column fit in 4 bytes (+/- 2,147,483,648) before doing the ALTER.

Cheers,

Evan

Sent= from my iPhone

On Nov = 1, 2018, at 06:20, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:

Hi,

I want to = change the column type from bigint to int to save space. By doing so, = does it include some risks ? As I see it, I should only run vacuum full = afterwards if I want to return space to the os right ?

=

= --Apple-Mail=_0F705084-F937-440B-9CF3-8D9E48DB2499--