> > I wonder if you got into this state by adding primary keys concurrently
> > somehow. That should of course not be allowed, but of course there is
> > no UNIQUE constraint on conrelid itself, so the normal unique-ification
> > code in btree does not fire for this situation.
>
> I thought maybe Simon's changes to reduce lock levels in ALTER TABLE
> had gone too far, but simple experiment shows that ALTER ADD PRIMARY KEY
> commands still block each other (and then the second one fails as
> expected). So there isn't an obvious hole here.
>
> Given that the OIDs are different, it seems more likely that this is the
> result of a primary key being dropped and then re-created, and later
> somehow the commit state of the original row got reverted.
Yes, sure - sorry for the misleading phrasing. These two rows are not completely identical - their OIDs are different
andreference to the supporting index is different:
# select oid,ctid,xmin,xmax,conrelid,contype,conindid from pg_constraint where conrelid::int+0=50621;
oid | ctid | xmin | xmax | conrelid | contype | conindid
--------+---------+---------+------+----------+---------+----------
301952 | (6,136) | 4883898 | 0 | 50621 | p | 301951
300466 | (7,1) | 4786734 | 0 | 50621 | p | 300465
(2 rows)
Of course, only one index exists - the one referenced by indexed row in pg_constraint. So, yes - it looks like this
‘phantom’row wasn’t properly deleted.
This table is recreated in two steps - first, a script is executed via psql, which drops and recreate table structure
usingslightly weird PL/PgSQL fragment:
do $$
declare
begin
begin
execute 'drop table this_table';
exception
when undefined_table then null;
end;
begin
execute 'create table this_table
(
part_id NUMERIC(20),
restart_id CHARACTER VARYING(250),
restart_info BYTEA
)';
exception
when duplicate_table then null;
end;
end $$ language 'plpgsql';
Script invocation is wrapped into begin; .. commit; command. The script doesn't create PK.
And then Java application performs table modification (in separate transaction):
alter table this_table add key character varying(4000);
alter table this_table add session_binary bytea;
alter table this_table add insert_time timestamp;
alter table this_table add constraint this_table_pk primary key (key);
create index this_table_insert_time on this_table(insert_time);
These two steps are repeated on patch reinstallation, so this table was for sure dropped and recreated multiple times.
Thanks,
Alexey Makhmutov