Thread: add primary key doesn't block?
Hi, I'm testing the migration procedure for a client, we want to migrate from 8.3.6 to 8.4 When we perform a test migration of the data we found some errors on the logs, one of them is this one: """ mic=3D# ALTER TABLE tcom_invitacion ADD primary key (id_invitacion); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "tcom_invitacion_pkey" for table "tcom_invitacion" ERROR: concurrent insert in progress """ actually, no indexes were created on this table... and when i tried to add the PK manually after migration i get this same error (not always, it seems this time the index is being created but this time i did it inside a transaction)... doesn't the index creation of the index block the table so, that message should not appear? --=20 Atentamente, Jaime Casanova Soporte y capacitaci=C3=B3n de PostgreSQL Asesor=C3=ADa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> When we perform a test migration of the data we found some errors on
> the logs, one of them is this one:
> """
> mic=# ALTER TABLE tcom_invitacion ADD primary key (id_invitacion);
> NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "tcom_invitacion_pkey" for table "tcom_invitacion"
> ERROR: concurrent insert in progress
> """
Can you provide a reproducible test case?
regards, tom lane
Jaime Casanova wrote: > Hi, > > I'm testing the migration procedure for a client, we want to migrate > from 8.3.6 to 8.4 > > When we perform a test migration of the data we found some errors on > the logs, one of them is this one: > > """ > mic=# ALTER TABLE tcom_invitacion ADD primary key (id_invitacion); > NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index > "tcom_invitacion_pkey" for table "tcom_invitacion" > ERROR: concurrent insert in progress > """ How was your migration done? pg_dump of data _and_ schema, then `psql -f' to restore it to the 8.4 database? Some other way? Was the dump file edited? Were any other users connected to the DB being restored to at the time? If you try the restore again to a fresh database (create a new one just for testing) does the issue happen again? If so, can you potentially grant one of the Pg team access to a spare server with the data and problem DB on it, or supply them with a dump for testing? Can you supply the full log, or at least some more context? > doesn't the index creation of the index block the table so, that > message should not appear? CREATE INDEX (without CONCURRENTLY) takes a lock on the table that blocks INSERT/UPDATE/DELETE (but not SELECT), yes. -- Craig Ringer
On Thu, Jan 21, 2010 at 12:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jaime Casanova <jcasanov@systemguards.com.ec> writes: >> When we perform a test migration of the data we found some errors on >> the logs, one of them is this one: > >> """ >> mic=3D# ALTER TABLE tcom_invitacion ADD primary key (id_invitacion); >> NOTICE: =C2=A0ALTER TABLE / ADD PRIMARY KEY will create implicit index >> "tcom_invitacion_pkey" for table "tcom_invitacion" >> ERROR: =C2=A0concurrent insert in progress >> """ > > Can you provide a reproducible test case? > trying to do so, gives me an error when moving the affected table from the migration test database to my machine: """ ERROR: invalid byte sequence for encoding "UTF8": 0xed6261 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY tcom_invitacion, line 543881 """ both servers had client_encoding and server_encoding in UTF8 and if i try this from the real server i get no error, so i guess this was somehow network corruption i failed to say that i make the migration directly via the network: $PG8.4DIR/bin/pg_dump -C -h ip_old_server dbname | $PG8.4DIR/bin/psql --=20 Atentamente, Jaime Casanova Soporte y capacitaci=C3=B3n de PostgreSQL Asesor=C3=ADa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157