[GENERAL] Duplicated IDs
Hi, I am having regurlarly duplicated rows with the same primary key. => select id, created_at, updated_at from tracks where created_at = '2014-07-03 15:09:16.336488'; id | created_at | updated_at ++ 331804 | 2014-07-03 15:09:16.336488 | 2014-07-03 15:37:55.253729 331804 | 2014-07-03 15:09:16.336488 | 2014-07-03 15:37:55.253801 => select id from tracks where id = 331804; id (0 rows) => delete from tracks where created_at = '2014-07-03 15:09:16.336488' and updated_at = '2014-07-03 15:37:55.253801'; ERROR: update or delete on table "tracks" violates foreign key constraint "fk_sources_on_track_id" on table "sources" DETAIL: Key (id)=(331804) is still referenced from table "sources". All colums are identical except the updated_at. PostgreSQL version is 9.3 and fsync is true. Any idea how I can clean my table? And then how I can stop this? Cheers, Alexis
Re: [GENERAL] Duplicated IDs
Hi, Thanks for your detailed response Alban. Primary key definition: "tracks_pkey" PRIMARY KEY, btree (id) select version(): PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit Ubuntu trusty with kernel 3.13.0-29-generic. I have a autovacuum process running and configuration is by default: ps aux | grep vacuum | grep -v grep postgres 587 0.0 0.0 1174304 3720 ?Ss juil.30 0:05 postgres: autovacuum launcher process I tried to vacuum manualy. I tried reindex: => reindex index tracks_pkey; ERROR: failed to find parent tuple for heap-only tuple at (38802,116) in table "tracks Here are more information about storage (raid 1): lshw -class disk -class storage *-storage description: Serial Attached SCSI controller produit: SAS2008 PCI-Express Fusion-MPT SAS-2 [Falcon] fabriquant: LSI Logic / Symbios Logic identifiant matériel: 0 information bus: pci@:01:00.0 nom logique: scsi0 version: 03 bits: 64 bits horloge: 33MHz fonctionnalités: storage pm pciexpress vpd msi msix bus_master cap_list rom configuration: driver=mpt2sas latency=0 ressources: irq:16 portE/S:2000(taille=256) mémoire:c514-c514 mémoire:c510-c513 mémoire:c540-c54f *-disk:0 NON-RÉCLAMÉ description: ATA Disk produit: ST1000NM0033-9ZM fabriquant: Seagate identifiant matériel: 0.0.0 information bus: scsi@0:0.0.0 version: GA04 numéro de série: Z1W0HGB9 configuration: ansiversion=5 *-disk:1 description: SCSI Disk produit: Virtual Disk fabriquant: Dell identifiant matériel: 1.0.0 information bus: scsi@0:1.0.0 nom logique: /dev/sda version: 1028 taille: 931GiB (999GB) capacité: 931GiB (999GB) fonctionnalités: 15000rpm partitioned partitioned:dos configuration: ansiversion=6 sectorsize=512 signature=000e59a7 *-disk:2 NON-RÉCLAMÉ description: ATA Disk produit: ST1000NM0033-9ZM fabriquant: Seagate identifiant matériel: 0.1.0 information bus: scsi@0:0.1.0 version: GA04 numéro de série: Z1W0HFYZ configuration: ansiversion=5 *-storage description: SATA controller produit: 6 Series/C200 Series Chipset Family SATA AHCI Controller fabriquant: Intel Corporation identifiant matériel: 1f.2 information bus: pci@:00:1f.2 version: 04 bits: 32 bits horloge: 66MHz fonctionnalités: storage msi pm ahci_1.0 bus_master cap_list configuration: driver=ahci latency=0 ressources: irq:43 portE/S:3048(taille=8) portE/S:3054(taille=4) portE/S:3040(taille=8) portE/S:3050(taille=4) portE/S:3020(taille=32) mémoire:c5204000-c52047ff Cheers, Alexis. 2014-08-09 12:35 GMT+02:00 Alban Hertroys : > On 09 Aug 2014, at 11:38, Alexis Bernard wrote: > > > Hi, > > > > I am having regurlarly duplicated rows with the same primary key. > > > > => select id, created_at, updated_at from tracks where created_at = > '2014-07-03 15:09:16.336488'; > >id | created_at | updated_at > > ++ > > 331804 | 2014-07-03 15:09:16.336488 | 2014-07-03 15:37:55.253729 > > 331804 | 2014-07-03 15:09:16.336488 | 2014-07-03 15:37:55.253801 > > > > => select id from tracks where id = 331804; > > id > > > > (0 rows) > > > > First of all, what is the definition of that primary key? What exact version of PG are you using?: select version(); What exact OS is this on? What kind of storage? To me it looks like you may have run into transaction wrap-around or a > corrupted index. > > Before you do anything, make a backup. > > Theorising that the issue here indeed is transaction wrap-around, what > you’re seeing may be data from older transactions that has become newer > because your current transaction txid is lower (due to the wraparound) than > the txid of the transactions those rows belong(ed) to. If those > transactions were committed, then you’re possibly seeing deleted or updated > rows that are still around. TX wraparound can occur if you do not vacuum > frequently enough and another thing that vacuum does is mark old rows > obsolete so that the DB can reclaim the space they use. Seeing data from > rows that are no longer there or that has been modified since seems to fit > the bill here. > > Hence the question: When did you last (auto-)vacuum this table? Did you > perhaps turn autovacuum off? Did it fall behind? > > If you have been vacuuming and the issue is a corrupt index: Does
Re: [GENERAL] Duplicated IDs
Hi,
Thanks for your help Alban !
Alexis.
2014-08-09 18:46 GMT+02:00 Alban Hertroys :
> On 09 Aug 2014, at 13:24, Alexis Bernard wrote:
>
> > Primary key definition: "tracks_pkey" PRIMARY KEY, btree (id)
> > select version(): PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled
> by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit
> > Ubuntu trusty with kernel 3.13.0-29-generic.
> >
> > I have a autovacuum process running and configuration is by default: ps
> aux | grep vacuum | grep -v grep
> > postgres 587 0.0 0.0 1174304 3720 ?Ss juil.30 0:05
> postgres: autovacuum launcher process
> >
> > I tried to vacuum manualy.
>
> What’s the output of: VACUUM VERBOSE tracks;
> Anything that looks abnormal?
>
>
INFO: vacuuming "public.tracks"
INFO: scanned index "tracks_pkey" to remove 25777 row versions
DETAIL: CPU 0.03s/0.12u sec elapsed 0.87 sec.
INFO: scanned index "index_tracks_on_permalink" to remove 25777 row
versions
DETAIL: CPU 0.07s/0.40u sec elapsed 5.30 sec.
INFO: "tracks": removed 25777 row versions in 5374 pages
DETAIL: CPU 0.00s/0.14u sec elapsed 0.66 sec.
INFO: index "tracks_pkey" now contains 1657345 row versions in 4594 pages
DETAIL: 17739 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "index_tracks_on_permalink" now contains 1657345 row versions
in 6143 pages
DETAIL: 25715 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "tracks": found 17560 removable, 1657349 nonremovable row versions
in 35586 out of 35586 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 64749 unused item pointers.
0 pages are entirely empty.
CPU 0.14s/0.86u sec elapsed 7.30 sec.
INFO: vacuuming "pg_toast.pg_toast_17228"
INFO: index "pg_toast_17228_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_17228": found 0 removable, 0 nonremovable row versions in
0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
> What do below queries return? They’re from the manual at:
> http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html
>
> SELECT c.oid::regclass as table_name,
>greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
> FROM pg_class c
> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
> WHERE c.relkind IN ('r', 'm');
>
>
table_name | age
+--
pg_statistic | 37049759
pg_type| 37049759
artists| 37042311
pg_authid | 37049759
pg_attribute | 37049759
pg_proc| 37049759
pg_class | 37049759
pg_user_mapping| 37049759
pg_constraint | 37049759
pg_inherits| 37049759
pg_index | 37049759
pg_operator| 37049759
pg_opfamily| 37049759
pg_opclass | 37049759
pg_am | 37049759
pg_amop| 37049759
pg_amproc | 37049759
pg_language| 37049759
pg_largeobject_metadata| 37049759
pg_database| 37049759
pg_rewrite | 37049759
pg_trigger | 37049759
pg_event_trigger | 37049759
pg_description | 37049759
pg_cast| 37049759
pg_enum| 37049759
pg_namespace | 37049759
pg_conversion | 37049759
pg_depend | 37049759
explorers | 36847481
pg_db_role_setting | 37049759
pg_tablespace | 37049759
pg_pltemplate | 37049759
pg_auth_members| 37049759
pg_shdepend| 37049759
pg_shdescription | 370497
Re: [GENERAL] Duplicated IDs
The vacuum full tracks just finished and I still have the duplicated IDs.
2014-08-12 9:17 GMT+02:00 Alexis Bernard :
> Hi,
>
> Thanks for your help Alban !
>
> Alexis.
>
>
> 2014-08-09 18:46 GMT+02:00 Alban Hertroys :
>
> On 09 Aug 2014, at 13:24, Alexis Bernard wrote:
>>
>> > Primary key definition: "tracks_pkey" PRIMARY KEY, btree (id)
>> > select version(): PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu,
>> compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit
>> > Ubuntu trusty with kernel 3.13.0-29-generic.
>> >
>> > I have a autovacuum process running and configuration is by default: ps
>> aux | grep vacuum | grep -v grep
>> > postgres 587 0.0 0.0 1174304 3720 ?Ss juil.30 0:05
>> postgres: autovacuum launcher process
>> >
>> > I tried to vacuum manualy.
>>
>> What’s the output of: VACUUM VERBOSE tracks;
>> Anything that looks abnormal?
>>
>>
> INFO: vacuuming "public.tracks"
> INFO: scanned index "tracks_pkey" to remove 25777 row versions
> DETAIL: CPU 0.03s/0.12u sec elapsed 0.87 sec.
> INFO: scanned index "index_tracks_on_permalink" to remove 25777 row
> versions
> DETAIL: CPU 0.07s/0.40u sec elapsed 5.30 sec.
> INFO: "tracks": removed 25777 row versions in 5374 pages
> DETAIL: CPU 0.00s/0.14u sec elapsed 0.66 sec.
> INFO: index "tracks_pkey" now contains 1657345 row versions in 4594 pages
> DETAIL: 17739 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "index_tracks_on_permalink" now contains 1657345 row versions
> in 6143 pages
> DETAIL: 25715 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "tracks": found 17560 removable, 1657349 nonremovable row versions
> in 35586 out of 35586 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 64749 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.14s/0.86u sec elapsed 7.30 sec.
> INFO: vacuuming "pg_toast.pg_toast_17228"
> INFO: index "pg_toast_17228_index" now contains 0 row versions in 1 pages
> DETAIL: 0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "pg_toast_17228": found 0 removable, 0 nonremovable row versions in
> 0 out of 0 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> VACUUM
>
>
>> What do below queries return? They’re from the manual at:
>> http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html
>>
>> SELECT c.oid::regclass as table_name,
>>greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
>> FROM pg_class c
>> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
>> WHERE c.relkind IN ('r', 'm');
>>
>>
> table_name | age
> +--
> pg_statistic | 37049759
> pg_type| 37049759
> artists| 37042311
> pg_authid | 37049759
> pg_attribute | 37049759
> pg_proc| 37049759
> pg_class | 37049759
> pg_user_mapping| 37049759
> pg_constraint | 37049759
> pg_inherits| 37049759
> pg_index | 37049759
> pg_operator| 37049759
> pg_opfamily| 37049759
> pg_opclass | 37049759
> pg_am | 37049759
> pg_amop| 37049759
> pg_amproc | 37049759
> pg_language| 37049759
> pg_largeobject_metadata| 37049759
> pg_database| 37049759
> pg_rewrite | 37049759
> pg_trigger | 37049759
> pg_event_trigger | 37049759
> pg_description | 37049759
> pg_cast
Re: [GENERAL] Duplicated IDs
I re-tried both vacuum and reindex: => vacuum full verbose tracks; INFO: vacuuming "public.tracks" INFO: "tracks": found 0 removable, 1662221 nonremovable row versions in 34274 pages DETAIL: 0 dead row versions cannot be removed yet. CPU 0.27s/0.68u sec elapsed 2.44 sec. VACUUM => reindex index tracks_pkey; ERROR: could not create unique index "tracks_pkey" DETAIL: Key (id)=(793935) is duplicated. 2014-08-12 20:21 GMT+02:00 John R Pierce : > On 8/12/2014 12:32 AM, Alexis Bernard wrote: > >> The vacuum full tracks just finished and I still have the duplicated IDs. >> > > vacuum full does a reindex, the reindex should have errored out on the > duplicate ID's. > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
