The following bug has been logged on the website:
Bug reference: 15198
Logged by: Feike Steenbergen
Email address: feikesteenbergen@gmail.com
PostgreSQL version: 10.4
Operating system: CentOS Linux release 7.5.1804 (Core)
Description:
We recently ran into a surprise when vetting our schema's:
One of our tables had column with a DEFAULT pointing to nextval('table').
perhaps an example will clarify things:
bugtest=# CREATE TABLE demo(i int default nextval('demo') PRIMARY KEY);
CREATE TABLE
bugtest=# ALTER TABLE demo ADD COLUMN j int default nextval('demo_pkey');
ALTER TABLE
bugtest=# \d demo
Table "public.demo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------
i | integer | | not null | nextval('demo'::regclass)
j | integer | | | nextval('demo_pkey'::regclass)
Indexes:
"demo_pkey" PRIMARY KEY, btree (i)
bugtest=# INSERT INTO demo (i, j) VALUES (1,1);
INSERT 0 1
bugtest=# INSERT INTO demo (i, j) VALUES (DEFAULT, DEFAULT);
ERROR: 42809: "demo" is not a sequence
LOCATION: init_sequence, sequence.c:1139
I would expect when setting a default when specifying nextval,
that only sequences are allowed to be specified, but - as shown above -
tables or indexes are also accepted during creation of the default.
I'm unsure whether fixing this is desirable, as a pg_dump/restore
would not work for those databases that have their defaults pointing
to things other than tables.
The following query helped us identify all of these issues we had,
which was luckily only 1:
select distinct
refobjid::regclass::text,
attname,
pg_get_expr(adbin, adrelid)
from
pg_depend
join
pg_attrdef on (refobjid=adrelid AND refobjsubid=adnum)
join
pg_attribute on (refobjid=attrelid AND adnum=attnum)
cross join lateral
regexp_replace(pg_get_expr(adbin, adrelid), 'nextval\(''(.*)''::.*',
'\1')
as next_relation(next_relname)
join
pg_class pc on (next_relname = pc.oid::regclass::text)
where
pc.relkind != 'S';
refobjid | attname | pg_get_expr
----------+---------+--------------------------------
demo | i | nextval('demo'::regclass)
demo | j | nextval('demo_pkey'::regclass)
(2 rows)
regards,
Feike
