Skip site navigation (1) Skip section navigation (2)

Re: Odd on-update inconsistency

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Odd on-update inconsistency
Date: 2008-11-26 22:10:18
Message-ID: 492DC94A.6060405@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-general
Richard Huxton wrote:
> Steve Crawford wrote:
>   
>> I'm having trouble understanding something I saw in my data from
>> yesterday involving an inconsistency between values in a table and its
>> associated rule-updated log table.
>>
>> For application debugging purposes (effectiveness of web double-submit
>> suppression) we have a rule that creates an entry in a log table
>> whenever the table we are watching is updated:
>>     
>
> Ah, I think you'll find you don't. You have a rule that looks at first
> glance like it *should* add an entry to your log table.
>
> Rules rewrite the query like a macro would and OLD and NEW don't refer
> to a row but to the entire set of rows. The most common problems you'll
> see are related to:
> 1. nextval() / currval() not behaving like you'd think.
> 2. in particular with multiple-row updates or inserts
>
> See the mailing list archives for plenty of discussion, and I think the
> current manuals have a better description of rules than there used to be.
>
> For inserting to a log table you'll want a trigger.
>
>   
Hmmm. I was aware of certain issues with rules but in this case we have 
no sequences/nextval()/currval() issues and, except for period-start 
resets of certain columns, the normal update query only operates on a 
single row (increment count for a given location) - and I reverified 
that the key column really is unique.

It's not a big problem (this project ends in a month anyway). I just 
want to increase my understanding to avoid future foot-gun potential as 
I hadn't seen how our current setup would cause this type of issue. I 
guess if it's critical that it works, I'll just write a trigger but 
rules are quicker and easier.

Cheers,
Steve


In response to

pgsql-general by date

Next:From: Steve CrawfordDate: 2008-11-26 22:26:06
Subject: Re: Date math question
Previous:From: Alvaro HerreraDate: 2008-11-26 21:22:36
Subject: Re: pg_get_serial_sequence Strangeness/Unreliable?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group