Subject: FW: 7.3b1 : DROP DOMAIN CASCADE CAN LEAVE A TABLE WITH NO COLUMNS - msg#00261

Previous Message by Date:

Re: Bug #780: Timestamp shifted by one minute

pgsql-bugs@xxxxxxxxxxxxxx writes: > Timestamp shifted by one minute [ in locally-built PG ] Do you have CFLAGS set in your environment, and if so how? This sounds quite a bit like the known problems with -ffast-math, though I have not heard this exact complaint before. If you built with -ffast-math then try without. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly

Next Message by Date:

Bug #781: Broken Indices in 7.3 Beta

Christoph Nelles (evilazrael@xxxxxxxxxxxxx) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description Broken Indices in 7.3 Beta Long Description Hello everybody! I am currently using 7.3 Beta (my data is not so important ;)) and it breaks frequently one index during UPDATEs. It´s an unique index, and there are only 500 records in the table which are updated every 6 minutes. But at some time, the update does not work anymore as Postgresql says that it "cannot insert a duplicate key in to a unique index". After reindexing the index everything will run smoothly again for a few hours. I will attach the Query, error message and the table definition below. It´s not much information i give you, as i don´t know what is relevant to you. With the Version 7.2.1 this error never occured, so it must be a bug within this beta release. Please tell me what information might be relevant to you or if you already know of this bug. Please email directly to me, as i am currently not subscribed to this particular list. I tried to send this message to the mailing-list, but somehow it never showed up there :( In the mean time i probably found the source of the problem. Probably the UPDATE colidates with an VACUUM ANALYZE of the DB which runs often at the same, as both are crontab jobs (UPDATE every 6 minutes, VACUUM every 60 minutes.) Christoph Nelles Sample Code Log excerpt and query : ERROR: Cannot insert a duplicate key into unique index bnt_planets_pkey LOG: statement: UPDATE bnt_planets SET organics=GREATEST(organics + (LEAST(colo nists, 100000000) * 0.005 * 0.5 * prod_organics / 100.0 * 3.004502250375) - (LEA ST(colonists, 100000000) * 0.005 * 0.05 * 3.004502250375), 0),ore=ore + (LEAST(c olonists, 100000000) * 0.005) * 0.25 * prod_ore / 100.0 * 3.004502250375,goods=g oods + (LEAST(colonists, 100000000) * 0.005) * 0.25 * prod_goods / 100.0 * 3.004 502250375,energy=energy + (LEAST(colonists, 100000000) * 0.005) * 0.5 * prod_ene rgy / 100.0 * 3.004502250375,colonists= LEAST((colonists + (colonists * 0.0005 * 3.004502250375)), 100000000),credits=credits * 1.001500750125 + (LEAST(colonist s, 100000000) * 0.005) * 3 * (100.0 - prod_organics - prod_ore - prod_goods - pr od_energy - prod_fighters - prod_torp) / 100.0 * 3.004502250375 WHERE (organics + (LEAST(colonists, 100000000) * 0.005 * 0.5 * prod_organics / 100.0 * 3.0045022 50375) - (LEAST(colonists, 100000000) * 0.005 * 0.05 * 3.004502250375) >= 0) ERROR: current transaction is aborted, queries ignored until end of transaction block table schema : blacknova=# \d bnt_planets Table "public.bnt_planets" Column | Type | Modifiers ---------------+------------------------+--------------------------------------- ------------------------ planet_id | integer | not null default nextval('"bnt_planets _planet_id_seq"'::text) sector_id | integer | not null default '0' name | character varying(256) | organics | bigint | not null default '0' ore | bigint | not null default '0' goods | bigint | not null default '0' energy | bigint | not null default '0' colonists | bigint | not null default '0' credits | bigint | not null default '0' fighters | bigint | not null default '0' torps | bigint | not null default '0' owner | integer | not null default '0' corp | integer | not null default '0' base | character(1) | not null default 'N' sells | character(1) | not null default 'N' prod_organics | real | not null default '20.0' prod_ore | real | not null default '20.0' prod_goods | real | not null default '20.0' prod_energy | real | not null default '20.0' prod_fighters | real | not null default '10.0' prod_torp | real | not null default '10.0' defeated | character(1) | not null default 'N' Indexes: bnt_planets_pkey primary key btree (planet_id), bnt_planets_corp_idx btree (corp), bnt_planets_owner_idx btree ("owner") Check constraints: "$1" ((base = 'Y'::bpchar) OR (base = 'N'::bpchar)) "$2" ((sells = 'Y'::bpchar) OR (sells = 'N'::bpchar)) "$3" ((defeated = 'Y'::bpchar) OR (defeated = 'N'::bpchar)) No file was uploaded with this report ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)

Previous Message by Thread:

Bug #780: Timestamp shifted by one minute

Rob Abbot (rra42@xxxxxxxxxxx) reports a bug with a severity of 1 The lower the number the more severe it is. Short Description Timestamp shifted by one minute Long Description The output of the code below is: 25/09/02 23:59:00 *NOT* 26/09/02 00:00:00 which is timeshifted backward by 1 minute from the inserted timestamp. In this case the timestamp is shifted backward to the previous day!!! Details of set-up: Dell 1650 PowerEdge, 1.13Mhz Pentium III, 512k cache, 512 MB RAM RedHat Linux 7.1 Result of SELECT version(); "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96" Details of build: (Comments below from my notes) Installed postgresql 7.2.1 from source: the default install is to /usr/local/pgsql/ unpacked in rob, got rob/postgresql-7.2.1 as 'rob' ./configure gmake (takes forever) gmake check su to root and get back to /home/rob/postgresql-7.2.1 gmake install gmake install-all-headers This bug appears similar to: http://archives.postgresql.org/pgsql-bugs/2001-04/msg00072.php I have another similar setup which works perfectly: the binary was built with the same source tree and options, however the differences between the two setups are: OS: Suse 7.1 Compiler: GCC 2.95.2 CPU: AMD Athlon 533MHz, 256MB RAM I am also running PostgreSQL on a Compaq Proliant, RedHat 6.0, PostgreSQL 7.0.3 compiled gcc egcs-2.91.66 again this does not show the timestamp problem. Best wishes, Rob Sample Code create table timetester (timefield timestamp); insert into timetester values ('2002-09-26 00:00:00.00'); select * from timetester; No file was uploaded with this report ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly

Next Message by Thread:

Bug #781: Broken Indices in 7.3 Beta

Christoph Nelles (evilazrael@xxxxxxxxxxxxx) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description Broken Indices in 7.3 Beta Long Description Hello everybody! I am currently using 7.3 Beta (my data is not so important ;)) and it breaks frequently one index during UPDATEs. It´s an unique index, and there are only 500 records in the table which are updated every 6 minutes. But at some time, the update does not work anymore as Postgresql says that it "cannot insert a duplicate key in to a unique index". After reindexing the index everything will run smoothly again for a few hours. I will attach the Query, error message and the table definition below. It´s not much information i give you, as i don´t know what is relevant to you. With the Version 7.2.1 this error never occured, so it must be a bug within this beta release. Please tell me what information might be relevant to you or if you already know of this bug. Please email directly to me, as i am currently not subscribed to this particular list. I tried to send this message to the mailing-list, but somehow it never showed up there :( In the mean time i probably found the source of the problem. Probably the UPDATE colidates with an VACUUM ANALYZE of the DB which runs often at the same, as both are crontab jobs (UPDATE every 6 minutes, VACUUM every 60 minutes.) Christoph Nelles Sample Code Log excerpt and query : ERROR: Cannot insert a duplicate key into unique index bnt_planets_pkey LOG: statement: UPDATE bnt_planets SET organics=GREATEST(organics + (LEAST(colo nists, 100000000) * 0.005 * 0.5 * prod_organics / 100.0 * 3.004502250375) - (LEA ST(colonists, 100000000) * 0.005 * 0.05 * 3.004502250375), 0),ore=ore + (LEAST(c olonists, 100000000) * 0.005) * 0.25 * prod_ore / 100.0 * 3.004502250375,goods=g oods + (LEAST(colonists, 100000000) * 0.005) * 0.25 * prod_goods / 100.0 * 3.004 502250375,energy=energy + (LEAST(colonists, 100000000) * 0.005) * 0.5 * prod_ene rgy / 100.0 * 3.004502250375,colonists= LEAST((colonists + (colonists * 0.0005 * 3.004502250375)), 100000000),credits=credits * 1.001500750125 + (LEAST(colonist s, 100000000) * 0.005) * 3 * (100.0 - prod_organics - prod_ore - prod_goods - pr od_energy - prod_fighters - prod_torp) / 100.0 * 3.004502250375 WHERE (organics + (LEAST(colonists, 100000000) * 0.005 * 0.5 * prod_organics / 100.0 * 3.0045022 50375) - (LEAST(colonists, 100000000) * 0.005 * 0.05 * 3.004502250375) >= 0) ERROR: current transaction is aborted, queries ignored until end of transaction block table schema : blacknova=# \d bnt_planets Table "public.bnt_planets" Column | Type | Modifiers ---------------+------------------------+--------------------------------------- ------------------------ planet_id | integer | not null default nextval('"bnt_planets _planet_id_seq"'::text) sector_id | integer | not null default '0' name | character varying(256) | organics | bigint | not null default '0' ore | bigint | not null default '0' goods | bigint | not null default '0' energy | bigint | not null default '0' colonists | bigint | not null default '0' credits | bigint | not null default '0' fighters | bigint | not null default '0' torps | bigint | not null default '0' owner | integer | not null default '0' corp | integer | not null default '0' base | character(1) | not null default 'N' sells | character(1) | not null default 'N' prod_organics | real | not null default '20.0' prod_ore | real | not null default '20.0' prod_goods | real | not null default '20.0' prod_energy | real | not null default '20.0' prod_fighters | real | not null default '10.0' prod_torp | real | not null default '10.0' defeated | character(1) | not null default 'N' Indexes: bnt_planets_pkey primary key btree (planet_id), bnt_planets_corp_idx btree (corp), bnt_planets_owner_idx btree ("owner") Check constraints: "$1" ((base = 'Y'::bpchar) OR (base = 'N'::bpchar)) "$2" ((sells = 'Y'::bpchar) OR (sells = 'N'::bpchar)) "$3" ((defeated = 'Y'::bpchar) OR (defeated = 'N'::bpchar)) No file was uploaded with this report ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)