Thread: Alter domain type / avoiding table rewrite
So I have a situation where I would like to modify a field that is currently a domain type over a varchar(9)
Specifically:
CREATE DOMAIN old_type AS varchar(9)
This isn't ideal, let's just say.. legacy.
I wish to modify this type.. ideally to a text type with a length constraint.. or even just a slightly larger varchar(12) would suffice..
CREATE DOMAIN new_type AS text;
ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <= 12)) NOT VALID;
ALTER TABLE target ALTER
COLUMN value SET DATA TYPE new_type;
But it seems impossible to achieve either without a full table rewrite.
This seems to boil down to DOMAIN types not being considered as binary compatible..
I've tried using a custom CAST..
CREATE CAST (old_type AS new_type) WITHOUT FUNCTION AS IMPLICIT;
But that blows up, with:
WARNING: cast will be ignored because the source date is a domain
ERROR: domain data types must not Be marked binary compatible
So I'm a little stuck at this point.
I feel like - if I can prove that the binary representation of both domains are truly identical - I might be forced to modify the system tables as a work around.. that scares me on a production system.
Is there a way around this that i'm not seeing?
I'm on PostgreSQL 9.6.2
Tim Kane <tim.kane@gmail.com> writes:
> So I have a situation where I would like to modify a field that is
> currently a domain type over a varchar(9)
> Specifically:
> CREATE DOMAIN old_type AS varchar(9)
> I wish to modify this type.. ideally to a text type with a length
> constraint.. or even just a slightly larger varchar(12) would suffice..
> CREATE DOMAIN new_type AS text;
> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <=
> 12)) NOT VALID;
> ALTER TABLE target ALTER
> COLUMN value SET DATA TYPE new_type;
> But it seems impossible to achieve either without a full table rewrite.
No, that's not going to work: coercing to a domain that has any
constraints is considered to require a rewrite.
You could cast down to varchar(9) without a rewrite, and you could cast
from there to varchar(12) without a rewrite, and it should work to do that
in one step.
If you really want a domain in there, I'd try creating the domain without
any constraint, then doing the ALTER TABLE, then adding the constraint
with ALTER DOMAIN. But TBH, that "new_type" is going to be a huge
performance drag compared to plain varchar(12). I'd only recommend
using a domain when there is no other way to get the check you need.
PG just doesn't support domains very well (especially before the work
I did for v12...)
regards, tom lane
On 4/16/19 7:12 AM, Tom Lane wrote: > Tim Kane <tim.kane@gmail.com> writes: >> So I have a situation where I would like to modify a field that is >> currently a domain type over a varchar(9) >> Specifically: >> CREATE DOMAIN old_type AS varchar(9) > >> I wish to modify this type.. ideally to a text type with a length >> constraint.. or even just a slightly larger varchar(12) would suffice.. >> CREATE DOMAIN new_type AS text; >> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <= >> 12)) NOT VALID; > >> ALTER TABLE target ALTER >> COLUMN value SET DATA TYPE new_type; >> But it seems impossible to achieve either without a full table rewrite. > > No, that's not going to work: coercing to a domain that has any > constraints is considered to require a rewrite. > > You could cast down to varchar(9) without a rewrite, and you could cast > from there to varchar(12) without a rewrite, and it should work to do that > in one step. I suspect the OP wants the type to text with a CHECK constraint to allow for increasing the length of field values in the future by just changing the CHECK setting. If that is the case would changing the type to text and then adding a CHECK NOT VALID work without too much pain? > > If you really want a domain in there, I'd try creating the domain without > any constraint, then doing the ALTER TABLE, then adding the constraint > with ALTER DOMAIN. But TBH, that "new_type" is going to be a huge > performance drag compared to plain varchar(12). I'd only recommend > using a domain when there is no other way to get the check you need. > PG just doesn't support domains very well (especially before the work > I did for v12...) > > regards, tom lane > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 4/16/19 7:16 AM, Adrian Klaver wrote:
> On 4/16/19 7:12 AM, Tom Lane wrote:
>> Tim Kane <tim.kane@gmail.com> writes:
>>> So I have a situation where I would like to modify a field that is
>>> currently a domain type over a varchar(9)
>>> Specifically:
>>> CREATE DOMAIN old_type AS varchar(9)
>>
>>> I wish to modify this type.. ideally to a text type with a length
>>> constraint.. or even just a slightly larger varchar(12) would suffice..
>>> CREATE DOMAIN new_type AS text;
>>> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <=
>>> 12)) NOT VALID;
>>
>>> ALTER TABLE target ALTER
>>> COLUMN value SET DATA TYPE new_type;
>>> But it seems impossible to achieve either without a full table rewrite.
>>
>> No, that's not going to work: coercing to a domain that has any
>> constraints is considered to require a rewrite.
>>
>> You could cast down to varchar(9) without a rewrite, and you could cast
>> from there to varchar(12) without a rewrite, and it should work to do
>> that
>> in one step.
>
> I suspect the OP wants the type to text with a CHECK constraint to allow
^ to change
> for increasing the length of field values in the future by just changing
> the CHECK setting. If that is the case would changing the type to text
> and then adding a CHECK NOT VALID work without too much pain?
>
>
>>
>> If you really want a domain in there, I'd try creating the domain without
>> any constraint, then doing the ALTER TABLE, then adding the constraint
>> with ALTER DOMAIN. But TBH, that "new_type" is going to be a huge
>> performance drag compared to plain varchar(12). I'd only recommend
>> using a domain when there is no other way to get the check you need.
>> PG just doesn't support domains very well (especially before the work
>> I did for v12...)
>>
>> regards, tom lane
>>
>>
>>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 4/16/19 4:22 AM, Tim Kane wrote: > So I have a situation where I would like to modify a field that is > currently a domain type over a varchar(9) > > Specifically: > CREATE DOMAIN old_type AS varchar(9) > > This isn't ideal, let's just say.. legacy. > > > I wish to modify this type.. ideally to a text type with a length > constraint.. or even just a slightly larger varchar(12) would suffice.. > > CREATE DOMAIN new_type AS text; > ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <= > 12)) NOT VALID; > > ALTER TABLE target ALTER > COLUMN value SET DATA TYPE new_type; > > > But it seems impossible to achieve either without a full table rewrite. But the column only has -- at most -- 9 characters of data in it. Won't the CHECK constraint instantly fail? (ISTM that you should add the check constraint AFTER modifying the length and updating your data.) -- Angular momentum makes the world go 'round.
On 4/16/19 7:19 AM, Ron wrote:
> On 4/16/19 4:22 AM, Tim Kane wrote:
>> So I have a situation where I would like to modify a field that is
>> currently a domain type over a varchar(9)
>>
>> Specifically:
>> CREATE DOMAIN old_type AS varchar(9)
>>
>> This isn't ideal, let's just say.. legacy.
>>
>>
>> I wish to modify this type.. ideally to a text type with a length
>> constraint.. or even just a slightly larger varchar(12) would suffice..
>>
>> CREATE DOMAIN new_type AS text;
>> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE)
>> <= 12)) NOT VALID;
>>
>> ALTER TABLE target ALTER
>> COLUMN value SET DATA TYPE new_type;
>>
>>
>> But it seems impossible to achieve either without a full table rewrite.
>
> But the column only has -- at most -- 9 characters of data in it. Won't
> the CHECK constraint instantly fail? (ISTM that you should add the
> check constraint AFTER modifying the length and updating your data.)
>
Not sure how?:
create table check_test (id integer, fld_1 varchar(12));
CREATE TABLE
test=> insert into check_test values (1, '123456789'), (2, '');
INSERT 0 2
test=> select length(fld_1) from check_test ;
length
--------
9
0
(2 rows)
The lengths would be less then or equal to 12.
Also the NOT VALID will push the check into the future:
https://www.postgresql.org/docs/9.6/sql-altertable.html
"... If the constraint is marked NOT VALID, the potentially-lengthy
initial check to verify that all rows in the table satisfy the
constraint is skipped. The constraint will still be enforced against
subsequent inserts or updates (that is, they'll fail unless there is a
matching row in the referenced table, in the case of foreign keys; and
they'll fail unless the new row matches the specified check
constraints). But the database will not assume that the constraint holds
for all rows in the table, until it is validated by using the VALIDATE
CONSTRAINT option."
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> I suspect the OP wants the type to text with a CHECK constraint to allow
> for increasing the length of field values in the future by just changing
> the CHECK setting. If that is the case would changing the type to text
> and then adding a CHECK NOT VALID work without too much pain?
I don't think we really support NOT VALID on domain constraints do we?
In any case, the point remains that domains are pretty inefficient
compared to native types like varchar(12); partly because the system
can't reason very well about arbitrary check constraints as compared
to simple length constraints, and partly because the whole feature
just isn't implemented very completely or efficiently. So you'll be
paying *a lot* for some hypothetical future savings.
(Having said that, you're already paying a fair chunk of that
overhead with your existing domain type, so maybe it's not bothering
you. But I'm worried that going from domain-without-check-constraint
to domain-with-check-constraint is going to bite you.)
regards, tom lane
On 4/16/19 7:42 AM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> I suspect the OP wants the type to text with a CHECK constraint to allow >> for increasing the length of field values in the future by just changing >> the CHECK setting. If that is the case would changing the type to text >> and then adding a CHECK NOT VALID work without too much pain? > > I don't think we really support NOT VALID on domain constraints do we? I was not clear. I was thinking the OP could modify your suggestion. Instead of: old_type --> varchar(9) --> varchar(12) doing: old_type --> varchar(9) --> text --> CHECK ((length(VALUE) <= 12)) NOT VALID > > In any case, the point remains that domains are pretty inefficient > compared to native types like varchar(12); partly because the system > can't reason very well about arbitrary check constraints as compared > to simple length constraints, and partly because the whole feature > just isn't implemented very completely or efficiently. So you'll be > paying *a lot* for some hypothetical future savings. > > (Having said that, you're already paying a fair chunk of that > overhead with your existing domain type, so maybe it's not bothering > you. But I'm worried that going from domain-without-check-constraint > to domain-with-check-constraint is going to bite you.) > > regards, tom lane > -- Adrian Klaver adrian.klaver@aklaver.com
On 4/16/19 9:28 AM, Adrian Klaver wrote: > On 4/16/19 7:19 AM, Ron wrote: >> On 4/16/19 4:22 AM, Tim Kane wrote: >>> So I have a situation where I would like to modify a field that is >>> currently a domain type over a varchar(9) >>> >>> Specifically: >>> CREATE DOMAIN old_type AS varchar(9) >>> >>> This isn't ideal, let's just say.. legacy. >>> >>> >>> I wish to modify this type.. ideally to a text type with a length >>> constraint.. or even just a slightly larger varchar(12) would suffice.. >>> >>> CREATE DOMAIN new_type AS text; >>> ALTER DOMAIN new_type ADD CONSTRAINT check_len CHECK ((length(VALUE) <= >>> 12)) NOT VALID; >>> >>> ALTER TABLE target ALTER >>> COLUMN value SET DATA TYPE new_type; >>> >>> >>> But it seems impossible to achieve either without a full table rewrite. >> >> But the column only has -- at most -- 9 characters of data in it. Won't >> the CHECK constraint instantly fail? (ISTM that you should add the check >> constraint AFTER modifying the length and updating your data.) >> > > Not sure how?: > > create table check_test (id integer, fld_1 varchar(12)); > CREATE TABLE > test=> insert into check_test values (1, '123456789'), (2, ''); > INSERT 0 2 > > test=> select length(fld_1) from check_test ; > > length > > -------- > > 9 > > 0 > > (2 rows) > > The lengths would be less then or equal to 12. But there's no CHECK constraint. > > Also the NOT VALID will push the check into the future: > > https://www.postgresql.org/docs/9.6/sql-altertable.html > > "... If the constraint is marked NOT VALID, the potentially-lengthy > initial check to verify that all rows in the table satisfy the constraint > is skipped. The constraint will still be enforced against subsequent > inserts or updates NOT VALID is the part that obviates my concern. -- Angular momentum makes the world go 'round.
On 4/16/19 9:42 AM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> I suspect the OP wants the type to text with a CHECK constraint to allow >> for increasing the length of field values in the future by just changing >> the CHECK setting. If that is the case would changing the type to text >> and then adding a CHECK NOT VALID work without too much pain? > I don't think we really support NOT VALID on domain constraints do we? > > In any case, the point remains that domains are pretty inefficient > compared to native types like varchar(12); partly because the system > can't reason very well about arbitrary check constraints as compared > to simple length constraints, and partly because the whole feature > just isn't implemented very completely or efficiently. So you'll be > paying *a lot* for some hypothetical future savings. Domains are great for maintaining data type consistency across many tables/columns. Normalization can obviate much of that need, and denormalization increases it. > (Having said that, you're already paying a fair chunk of that > overhead with your existing domain type, so maybe it's not bothering > you. But I'm worried that going from domain-without-check-constraint > to domain-with-check-constraint is going to bite you.) > > regards, tom lane > > -- Angular momentum makes the world go 'round.
Thanks everyone..
It seems that the first step:
old_type --> varchar(9)
still requires a table rewrite, while the reverse direction does not.
I'm curious about the performance implication of domain types, i expect that cost is only at insert/update time? I guess we've been wearing that cost up until now.
Adrian is correct - the intention for the DOMAIN with CHECK approach was to allow flexibility moving forward, as the data set is particularly large...
I'm now thinking that since promotion to a larger size is a non-issue, and domain type seems to be not quite the panacea I hoped, then the use of varchar(n) is perhaps not so terrible!
Thanks for the advice/suggestions/discussion :)
On 4/16/19 9:18 AM, Tim Kane wrote: > Thanks everyone.. > > It seems that the first step: > > old_type --> varchar(9) > > still requires a table rewrite, while the reverse direction does not. Hmm: CREATE DOMAIN old_type AS varchar(9); create table rewrite_test (id integer, fld_1 old_type); insert into rewrite_test values (1, '123456789'), (2, '123'); select ctid from rewrite_test; ctid ------- (0,1) (0,2) alter table rewrite_test alter COLUMN fld_1 set data type varchar(9); select ctid from rewrite_test; ctid ------- (0,1) (0,2) update rewrite_test set fld_1 = '1' where id =2; select ctid from rewrite_test; ctid ------- (0,1) (0,3) Where are you seeing the rewrite in your case? > > > I'm curious about the performance implication of domain types, i expect > that cost is only at insert/update time? I guess we've been wearing that > cost up until now. > > Adrian is correct - the intention for the DOMAIN with CHECK approach was > to allow flexibility moving forward, as the data set is particularly > large... > > I'm now thinking that since promotion to a larger size is a non-issue, > and domain type seems to be not quite the panacea I hoped, then the use > of varchar(n) is perhaps not so terrible! > > Thanks for the advice/suggestions/discussion :) > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, 16 Apr 2019 at 18:04, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Where are you seeing the rewrite in your case?
I’m unfamiliar with ctid (which I now plan to read up on)… I’ve been looking at relfilenode
I’ve observed that relfilenode changes when altering from old_type à varchar(9) and the operation takes 6 seconds on this data set.
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
(1 row)
PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id set data type varchar(9);
ALTER TABLE
Time: 6605.454 ms
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
(1 row)
And then the other way… from varchar(9) à old_type
refilenode does not change, and the operation takes 0.3ms
PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id set data type execid_t;
ALTER TABLE
Time: 1.360 ms
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
(1 row)
Time: 0.331 ms
Apologies if this formats badly :-/ transcribing between devices not well suited to email.
Tim
On 4/17/19 2:14 AM, Tim Kane wrote: > > > On Tue, 16 Apr 2019 at 18:04, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > > > Where are you seeing the rewrite in your case? > > > > I’m unfamiliar with ctid (which I now plan to read up on)… I’ve been > looking at /relfilenode____/ > > I’ve observed that relfilenode changes when altering from /old_type > //à varchar(9) /and the operation takes 6 seconds on this data set.____ The table definition and the size of the data set would help with interpreting the below. > > __ > > __ > > PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where > relname='test';____ > > relfilenode____ > > -------------____ > > 20669469 <tel:20669469>____ > > (1 row)____ > > __ __ > > PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id > set data type varchar(9);____ > > ALTER TABLE____ > > Time: 6605.454 ms____ > > > PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where > relname='test';____ > > relfilenode____ > > -------------____ > > 20671802 <tel:20671802>____ > > (1 row) > > __ __ > > And then the other way… from /varchar(9) //à old_type____/ > > refilenode does not change, and the operation takes 0.3ms____ > > __ __ > > PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id > set data type execid_t;____ > > ALTER TABLE____ > > Time: 1.360 ms____ > > PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where > relname='test';____ > > relfilenode____ > > -------------____ > > 20671802 <tel:20671802>____ > > (1 row)____ > > __ __ > > Time: 0.331 ms____ > > __ > > > Apologies if this formats badly :-/ transcribing between devices not > well suited to email. > > Tim > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
The table definition and the size of the data set would help with
interpreting the below.
The below example shows the issue on a table with just a single field. I can demonstrate the problem wether there are 100 records or a million records.
In every case:
Altering the type from a domain of varchar(9) to a raw varchar(9) results in a full table rewrite (as identified by relfilenode).
Altering the type from a raw varchar(9) to a domain of varchar(9) occurs for free, with no change to relfilenode.
The timing of each ALTER operation appears to back this up.
postgres@[local]=# create domain old_type as varchar(9);
CREATE DOMAIN
postgres@[local]=# create table test (values old_type);
CREATE TABLE
postgres@[local]=# with data as (select generate_series(1,1000000), md5(random()::text))
postgres@[local]-# insert into test select substring(md5, 1, 9) from data;
INSERT 0 1000000
Time: 4097.162 ms
postgres@[local]=# \d test
Table "alpha_core.test"
Column | Type | Modifiers
--------+----------+-----------
values | old_type |
postgres@[local]=# \dD old_type
List of domains
Schema | Name | Type | Modifier | Check
------------+----------+----------------------+----------+-------
alpha_core | old_type | character varying(9) | |
(1 row)
postgres@[local]=# select count(*) from test;
count
---------
1000000
(1 row)
postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20689856
(1 row)
postgres@[local]=# alter table test alter COLUMN values set data type varchar(9);
ALTER TABLE
Time: 993.271 ms
postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20691283
(1 row)
postgres@[local]=# alter table test alter COLUMN values set data type old_type;
ALTER TABLE
Time: 21.569 ms
postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20691283
(1 row)
postgres@[local]=# drop table test;
DROP TABLE
postgres@[local]=# create domain old_type as varchar(9);
CREATE DOMAIN
postgres@[local]=# create table test (values old_type);
CREATE TABLE
postgres@[local]=# with data as (select generate_series(1,1000000), md5(random()::text))
postgres@[local]-# insert into test select substring(md5, 1, 9) from data;
INSERT 0 1000000
Time: 4097.162 ms
postgres@[local]=# \d test
Table "alpha_core.test"
Column | Type | Modifiers
--------+----------+-----------
values | old_type |
postgres@[local]=# \dD old_type
List of domains
Schema | Name | Type | Modifier | Check
------------+----------+----------------------+----------+-------
alpha_core | old_type | character varying(9) | |
(1 row)
postgres@[local]=# select count(*) from test;
count
---------
1000000
(1 row)
postgres@[local]=# select relfilenode from pg_class where relname='test';
-------------
20689856
(1 row)
postgres@[local]=# alter table test alter COLUMN values set data type varchar(9);
ALTER TABLE
Time: 993.271 ms
postgres@[local]=# select relfilenode from pg_class where relname='test';
-------------
20691283
(1 row)
postgres@[local]=# alter table test alter COLUMN values set data type old_type;
ALTER TABLE
Time: 21.569 ms
postgres@[local]=# select relfilenode from pg_class where relname='test';
-------------
20691283
(1 row)
postgres@[local]=# drop table test;
DROP TABLE
On 4/17/19 8:34 AM, Tim Kane wrote: > > > On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > > > The table definition and the size of the data set would help with > interpreting the below. > > > > > The below example shows the issue on a table with just a single field. I > can demonstrate the problem wether there are 100 records or a million > records. > > In every case: > Altering the type from a domain of varchar(9) to a raw varchar(9) > results in a full table rewrite (as identified by relfilenode). > Altering the type from a raw varchar(9) to a domain of varchar(9) > occurs for free, with no change to relfilenode. > > The timing of each ALTER operation appears to back this up. > > I stand corrected. The logs back it up also. See log entries inline below. > > > > postgres@[local]=# create domain old_type as varchar(9); > CREATE DOMAIN > > postgres@[local]=# create table test (values old_type); > CREATE TABLE > > postgres@[local]=# with data as (select generate_series(1,1000000), > md5(random()::text)) > postgres@[local]-# insert into test select substring(md5, 1, 9) from data; > INSERT 0 1000000 <tel:0%201000000> > Time: 4097.162 ms > > postgres@[local]=# \d test > Table "alpha_core.test" > Column | Type | Modifiers > --------+----------+----------- > values | old_type | > > postgres@[local]=# \dD old_type > List of domains > Schema | Name | Type | Modifier | Check > ------------+----------+----------------------+----------+------- > alpha_core | old_type | character varying(9) | | > (1 row) > > postgres@[local]=# select count(*) from test; > count > --------- > 1000000 <tel:1000000> > (1 row) > > > > postgres@[local]=# select relfilenode from pg_class where relname='test'; > relfilenode > ------------- > 20689856 <tel:20689856> > (1 row) > > > postgres@[local]=# alter table test alter COLUMN values set data type > varchar(9); > ALTER TABLE > Time: 993.271 ms aklaver-2019-04-17 09:06:47.854 PDT-0LOG: statement: alter table test alter COLUMN values set data type varchar(9); aklaver-2019-04-17 09:06:47.884 PDT-38177DEBUG: rewriting table "test" > > > postgres@[local]=# select relfilenode from pg_class where relname='test'; > relfilenode > ------------- > 20691283 <tel:20691283> > (1 row) > > postgres@[local]=# alter table test alter COLUMN values set data type > old_type; > ALTER TABLE > Time: 21.569 ms aklaver-2019-04-17 09:07:46.027 PDT-0LOG: statement: alter table test alter COLUMN values set data type old_type; aklaver-2019-04-17 09:07:46.027 PDT-38178DEBUG: building index "pg_toast_668193_index" on table "pg_toast_668193" serially > > > postgres@[local]=# select relfilenode from pg_class where relname='test'; > relfilenode > ------------- > 20691283 <tel:20691283> > (1 row) > > postgres@[local]=# drop table test; > DROP TABLE > -- Adrian Klaver adrian.klaver@aklaver.com