Tom Lane wrote:
> Philip suggested to me off-list that the initial error may have been the
> VACUUM FULL (xid 32902872) creating duplicate moved copies of a single
> valid row. That seems plausible because VACUUM FULL suppresses
> duplicate-index checks, and it's real hard to see any other way tha
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Philip Warner wrote:
>> Item 7 -- Length: 168 Offset: 3920 (0x0f50) Flags: USED
>> XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872
>> Block Id: 0 linp Index: 7 Attributes: 34 Size: 36
>> infomask: 0x2913
>> (HASNULL|HASVARWIDTH|HASOID|XM
Philip Warner wrote:
> Item 7 -- Length: 168 Offset: 3920 (0x0f50) Flags: USED
> XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872
> Block Id: 0 linp Index: 7 Attributes: 34 Size: 36
> infomask: 0x2913
> (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
> t_b
Oops. Minor change. Last two fields are updated by rules.
Tom Lane wrote:
> Philip Warner <[EMAIL PROTECTED]> writes:
>
>> aView_update_r1 AS
>> ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f1 = new.f1
>> WHERE brokenTable.id = new.id
>> aView_update_r2 AS
>> ON UPDATE TO a
Tom Lane wrote:
> Philip Warner <[EMAIL PROTECTED]> writes:
>
>> aView_update_r1 AS
>> ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f1 = new.f1
>> WHERE brokenTable.id = new.id
>> aView_update_r2 AS
>> ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f2 = new.f2
>> WH
Philip Warner <[EMAIL PROTECTED]> writes:
> aView_update_r1 AS
> ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f1 = new.f1
> WHERE brokenTable.id = new.id
> aView_update_r2 AS
> ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f2 = new.f2
> WHERE brokenTable.id = new.id
Tom Lane wrote:
> OK, I'm a bit confused by the obfuscation here. The table with the
> duplicates is xxx, or qqq?
Possibly less obscure version:
public | tg_update_anotherTable_date | "trigger"
| | mail | plpgsql |
Declare
uid bigint;
Begin
Tom Lane wrote:
> OK, I'm a bit confused by the obfuscation here. The table with the
> duplicates is xxx, or qqq?
Yes, xxx is the broken table. The two rewrite rules map updates on a
view to an underlying table (the broken one).
Updates on the view occur very frequently. Perhaps 400,000 per day?
Philip Warner <[EMAIL PROTECTED]> writes:
> public | tg_update_qqq_date | "trigger"
> | | mail | plpgsql |
> Declare
> uid bigint;
> Begin
> uid = (select owner_id from yyy m where m.f1 = NEW.f1);
> if (uid <> 0 and not uid i
Tom Lane wrote:
>> Updates happen regularly from many sources, but the procedure that does
>> the most updates is a trigger. Do you want to see that?
>>
>
> Please.
>
public | tg_update_qqq_date | "trigger"
| | mail | plpgsql |
Declare
uid
Philip Warner <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> The thing that is striking though is
>> that the xmin/cmin values are all the same, indicating that all six
>> tuples were inserted by the same command. That seems pretty odd. Can
>> you show us the procedure by which rows are inserte
Tom Lane wrote:
> Philip Warner <[EMAIL PROTECTED]> writes:
>
>> mail=# set enable_indexscan=off;
>> mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613;
>>xmin | xmax | cmin | cmax | ctid
>> --+--+--+--+-
>> 32902771 |0
Philip Warner <[EMAIL PROTECTED]> writes:
> mail=# set enable_indexscan=off;
> mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613;
>xmin | xmax | cmin | cmax | ctid
> --+--+--+--+-
> 32902771 |0 | 20 | 32902872 | (0,7)
Alvaro Herrera wrote:
> Do the triggers involved have EXCEPTION clauses? (I assume they are
> written in PL/pgSQL -- are there any in other languages?)
Triggers that update this table are in pl/pgsql, and can *raise*
exceptions (using RAISE) if that is what you mean. They do not handle
them -- is t
Tom Lane wrote:
> For completeness, could we also see ctid in that query?
mail=# set enable_indexscan=off;
mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613;
xmin | xmax | cmin | cmax | ctid
--+--+--+--+-
32902771 |0 | 2
Philip Warner wrote:
> # set enable_indexscan=off;
> # SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;
>xmin | xmax | cmin | cmax
> --+--+--+--
> 32902771 |0 | 20 | 32902872
> 32902771 |0 | 20 | 32902872
> 32902771 |0
Philip Warner <[EMAIL PROTECTED]> writes:
> # set enable_indexscan=off;
> # SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;
>xmin | xmax | cmin | cmax
> --+--+--+--
> 32902771 |0 | 20 | 32902872
> 32902771 |0 | 20 | 32902872
>
Michael Fuhr wrote:
> On Thu, Apr 06, 2006 at 08:12:31AM -0400, Alvaro Herrera wrote:
>
>> Please do a
>>
>> SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;
>>
# set enable_indexscan=off;
# SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;
xmin | xmax | cmin | cma
On Thu, Apr 06, 2006 at 08:12:31AM -0400, Alvaro Herrera wrote:
> Please do a
>
> SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;
>
> if you still have that particular manifestation.
Also, you'll probably need to set enable_indexscan to off prior to
running the above query.
--
Michael
Philip Warner wrote:
> We have an intermittent bug that occurs on a table which is updated several
> times per second. The bug occurs every few days/weeks. It is usually
> preceeded by a "tuple concurrently updated" messages, but I could not swear
> it is always preceeded by it.
>
> The result of
The following bug has been logged online:
Bug reference: 2379
Logged by: Philip Warner
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.7
Operating system: FreeBSD
Description:Duplicate pkeys in table
Details:
We have an intermittent bug that occurs on a tab
21 matches
Mail list logo
