Thread: [SQL] TRUNCATE TABLE corrupts pg_class.relfilenode = pg_attrdef.pg_attrdef
[SQL] TRUNCATE TABLE corrupts pg_class.relfilenode = pg_attrdef.pg_attrdef
From
Sebastien FLAESCH
Date:
Hi all, Testing with Postgresql 9.6rc1 (but also detected with prior versions): It is normal that a TRUNCATE TABLE statement changes the table/sequence relation in pg_attrdef? test1=> create table mytab ( pkey serial, name varchar(10) ); test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('mytab'); adsrc ------------------------------------- nextval('mytab_pkey_seq'::regclass) (1 row) test1=> truncate table mytab; TRUNCATE TABLE test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('mytab');adsrc ------- (0 rows) test1=> select adrelid, adsrc from pg_attrdef where adsrc like '%mytab%'; adrelid | adsrc ---------+------------------------------------- 6904163 | nextval('mytab_pkey_seq'::regclass) (1 row) Are we mis-using the condition (p.relfilenode = a.adrelid) in the first SELECT? How can we easily check if a table is defined with a SERIAL type? Thanks! Seb
Re: [SQL] TRUNCATE TABLE corrupts pg_class.relfilenode =pg_attrdef.pg_attrdef
From
Sebastien FLAESCH
Date:
We will test with 9.6.3 too. Seb On 05/22/2017 12:51 PM, Sebastien FLAESCH wrote: > Hi all, > > Testing with Postgresql 9.6rc1 (but also detected with prior versions): > > It is normal that a TRUNCATE TABLE statement changes the table/sequence relation in pg_attrdef? > > test1=> create table mytab ( pkey serial, name varchar(10) ); > test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('mytab'); > adsrc > ------------------------------------- > nextval('mytab_pkey_seq'::regclass) > (1 row) > > test1=> truncate table mytab; > TRUNCATE TABLE > test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('mytab'); > adsrc > ------- > (0 rows) > > test1=> select adrelid, adsrc from pg_attrdef where adsrc like '%mytab%'; > adrelid | adsrc > ---------+------------------------------------- > 6904163 | nextval('mytab_pkey_seq'::regclass) > (1 row) > > > Are we mis-using the condition (p.relfilenode = a.adrelid) in the first SELECT? > > How can we easily check if a table is defined with a SERIAL type? > > Thanks! > Seb > >
Re: [SQL] TRUNCATE TABLE corrupts pg_class.relfilenode =pg_attrdef.pg_attrdef
From
Sebastien FLAESCH
Date:
Seems that the correct join condition should be: (pg_class.oid = pg_attrdef.adrelid) I don't know why we have used pg_class.relfilenode... So this should be the correct SELECT statement: test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.oid = a.adrelid) where lower(p.relname) = lower('mytab'); adsrc ------------------------------------- nextval('mytab_pkey_seq'::regclass) (1 row) Can someone confirm? Thanks! Seb On 05/22/2017 12:51 PM, Sebastien FLAESCH wrote: > Hi all, > > Testing with Postgresql 9.6rc1 (but also detected with prior versions): > > It is normal that a TRUNCATE TABLE statement changes the table/sequence relation in pg_attrdef? > > test1=> create table mytab ( pkey serial, name varchar(10) ); > test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('mytab'); > adsrc > ------------------------------------- > nextval('mytab_pkey_seq'::regclass) > (1 row) > > test1=> truncate table mytab; > TRUNCATE TABLE > test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('mytab'); > adsrc > ------- > (0 rows) > > test1=> select adrelid, adsrc from pg_attrdef where adsrc like '%mytab%'; > adrelid | adsrc > ---------+------------------------------------- > 6904163 | nextval('mytab_pkey_seq'::regclass) > (1 row) > > > Are we mis-using the condition (p.relfilenode = a.adrelid) in the first SELECT? > > How can we easily check if a table is defined with a SERIAL type? > > Thanks! > Seb > >
Re: [SQL] TRUNCATE TABLE corrupts pg_class.relfilenode =pg_attrdef.pg_attrdef
From
Sebastien FLAESCH
Date:
Obviously pg_class.relfilenode can change when truncating tables: test1=> select relfilenode from pg_class where relname = 'mytab'; relfilenode ------------- 6904188 (1 row) test1=> truncate table mytab; TRUNCATE TABLE test1=> select relfilenode from pg_class where relname = 'mytab'; relfilenode ------------- 6904189 (1 row) Seb On 05/22/2017 01:07 PM, Sebastien FLAESCH wrote: > Seems that the correct join condition should be: > > (pg_class.oid = pg_attrdef.adrelid) > > I don't know why we have used pg_class.relfilenode... > > So this should be the correct SELECT statement: > > test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.oid = a.adrelid) where lower(p.relname) = lower('mytab'); > adsrc > ------------------------------------- > nextval('mytab_pkey_seq'::regclass) > (1 row) > > > Can someone confirm? > > Thanks! > Seb > > > On 05/22/2017 12:51 PM, Sebastien FLAESCH wrote: >> Hi all, >> >> Testing with Postgresql 9.6rc1 (but also detected with prior versions): >> >> It is normal that a TRUNCATE TABLE statement changes the table/sequence relation in pg_attrdef? >> >> test1=> create table mytab ( pkey serial, name varchar(10) ); >> test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('mytab'); >> adsrc >> ------------------------------------- >> nextval('mytab_pkey_seq'::regclass) >> (1 row) >> >> test1=> truncate table mytab; >> TRUNCATE TABLE >> test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('mytab'); >> adsrc >> ------- >> (0 rows) >> >> test1=> select adrelid, adsrc from pg_attrdef where adsrc like '%mytab%'; >> adrelid | adsrc >> ---------+------------------------------------- >> 6904163 | nextval('mytab_pkey_seq'::regclass) >> (1 row) >> >> >> Are we mis-using the condition (p.relfilenode = a.adrelid) in the first SELECT? >> >> How can we easily check if a table is defined with a SERIAL type? >> >> Thanks! >> Seb >> >> > > >
Sebastien FLAESCH <sf@4js.com> writes: > It is normal that a TRUNCATE TABLE statement changes the table/sequence relation in pg_attrdef? It is normal for TRUNCATE, as well as several other kinds of DDL, to change relfilenode. > test1=> select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('mytab'); This query is simply wrong; the join column should be p.oid. See the system catalog definitions, https://www.postgresql.org/docs/current/static/catalogs.html regards, tom lane