Re: [HACKERS] 9.5.4: Segfault (signal 11) while running ALTER TABLE

[Devrim Gündüz]

Hi Tom,

On Tue, 2016-08-30 at 08:18 -0400, Tom Lane wrote:
> The above isn't ever likely to work for any large value of "work",
> because the function would be confused about what the table rowtype
> is.  I thought we had adequate defenses in there to throw an error
> if you try to access a table that's in the middle of being altered,
> but apparently this case isn't covered.
> 
> Why didn't they just do
> ALTER TABLE foo1 ALTER COLUMN id TYPE INTEGER USING newid;
> ?  The intermediate function sure seems like the hard way.

Just got a reply from them. They had some historical reasons for this, but
apparently it was not needed, so they'll change their procedures based on your
suggestion.

However, they also reported that they did not have this problem in their
staging environment. I assume that staging has less resources than the prod
machine. What could cause this problem that has more maintenance_work_mem and
shared_buffers than staging one?

Thanks!

Regards,
-- 
Devrim GÜNDÜZ
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] 9.5.4: Segfault (signal 11) while running ALTER TABLE

[Tom Lane]

Devrim =?ISO-8859-1?Q?G=FCnd=FCz?=  writes:
> They wanted to change id column from uuid to int, so created this func first:

> CREATE FUNCTION foofunc_id_uuidtoint(chartoconvert uuid) RETURNS integer
> LANGUAGE sql IMMUTABLE STRICT
> AS $_$
> SELECT newid FROM foo1 WHERE tempuuid = $1 LIMIT 1;
> $_$;

> and ran this:

> ALTER TABLE foo1 ALTER COLUMN id TYPE INTEGER USING
> foofunc_id_uuidtoint(tempuuid);

> This command crashed postmaster.

The above isn't ever likely to work for any large value of "work",
because the function would be confused about what the table rowtype
is.  I thought we had adequate defenses in there to throw an error
if you try to access a table that's in the middle of being altered,
but apparently this case isn't covered.

Why didn't they just do
ALTER TABLE foo1 ALTER COLUMN id TYPE INTEGER USING newid;
?  The intermediate function sure seems like the hard way.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers