Обсуждение: multiple statement 'instead of' rule

I have a repeatable issue where on an update rule (attached to a view)
not all of the statements are firing under certain conditions.  There
is no error but the rule does not seem to be completely going through
the statements. I'm not sure if this is correct behavior reading the
documentation but it smells funny.  I confirmed the statements are not
updating by
inserting sql log statements  in between the queries in the rule.
Also, by converting the rule to a rule + sql function it works 100%
correct every time.  this is on postgresql 8.1.4 on windows xp.

create or replace rule insert_fsv as on insert to frequency_service_view
  do instead
  (
    insert into frequency_operation
      select new.table_name, new.frequency, old.code where new.set =
true and old.set = false;
    delete from frequency_operation
      where table_name = old.table_name and frequency_operation.code =
old.code and
        frequency_operation.frequency = new.frequency and new.set = false;
    update operation
      set code = new.code where code = old.code and old.code != new.code;
  );

merlin

"Merlin Moncure" <mmoncure@gmail.com> writes:
> create or replace rule insert_fsv as on insert to frequency_service_view
>   do instead
>   (
>     insert into frequency_operation
>       select new.table_name, new.frequency, old.code where new.set =
> true and old.set = false;
>     delete from frequency_operation
>       where table_name = old.table_name and frequency_operation.code =
> old.code and
>         frequency_operation.frequency = new.frequency and new.set = false;
>     update operation
>       set code = new.code where code = old.code and old.code != new.code;
>   );

What is frequency_service_view?  Is it by any chance dependent on
frequency_operation?  If so, your changes to frequency_operation will
affect the behavior of OLD references.

            regards, tom lane

I call PQexecParams with the last parameter as 1 to return binary
data.  I then get this data with:

fileLengthPtr = PQgetvalue(result, rowIndex, fileLengthColumnIndex);
memcpy(&fileLength, fileLengthPtr, sizeof(fileLength));

The value being returned is of type integer.

It should have the value 7237 in binary.
It actually has the value:
fileLengthPtr[0]    0    char
fileLengthPtr[1]    0    char
fileLengthPtr[2]    28 '?'    char
fileLengthPtr[3]    69 'E'    char

Which is not 7237, it's:
fileLength    1159462912    int

Why?

On Tue, Jun 20, 2006 at 09:12:50PM -0700, Kevin Jenkins wrote:
> I call PQexecParams with the last parameter as 1 to return binary
> data.  I then get this data with:
>
> fileLengthPtr = PQgetvalue(result, rowIndex, fileLengthColumnIndex);
> memcpy(&fileLength, fileLengthPtr, sizeof(fileLength));
>
> The value being returned is of type integer.
>
> It should have the value 7237 in binary.
> It actually has the value:
> fileLengthPtr[0]    0    char
> fileLengthPtr[1]    0    char
> fileLengthPtr[2]    28 '?'    char
> fileLengthPtr[3]    69 'E'    char
>
> Which is not 7237, it's:
> fileLength    1159462912    int
>
> Why?

7237 decimal = 1c45 hex
28 decimal = 1c hex
69 decimal = 45 hex

The data looks correct once you recognize that it's in network byte
order (big endian).  You'll need to convert it to host byte order.

--
Michael Fuhr

On 6/20/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
> > create or replace rule insert_fsv as on insert to frequency_service_view
> >   do instead
> >   (
> >     insert into frequency_operation
> >       select new.table_name, new.frequency, old.code where new.set =
> > true and old.set = false;
> >     delete from frequency_operation
> >       where table_name = old.table_name and frequency_operation.code =
> > old.code and
> >         frequency_operation.frequency = new.frequency and new.set = false;
> >     update operation
> >       set code = new.code where code = old.code and old.code != new.code;
> >   );
>
> What is frequency_service_view?  Is it by any chance dependent on
> frequency_operation?  If so, your changes to frequency_operation will
> affect the behavior of OLD references.

right, actually that was a typo, was supposed to be 'create or replace
rule insert_fov as on insert to frequency_operation_view'.  I was
considering that old/new are invalid which is fine, but the problem is
in some cases the third (and sometimes second query) never fires at
all with any arguments.  I confirmed this by inserting into a log
table in between the rule queries (they never fired either).

I can prepare a test case if you think it's worth it.

Merlin