Hi all,
I have a question regarding rules, in particular when the where clause is
evaluated. Here my case: I store output from metalog also in postgresql
(8.0.4). The table is pretty straight forward: counter, date, program,
message. As I only want to keep a few rows from the logs permanently, I
would like to copy the interesting data with a rule into a table which
doesn't get cleaned regularly. For example, I'd like to keep the
connection time of the pppd (i.e., DSL connection time).The relevant sql
command is:

to_number(split_part(message, ' ', 3)

I already played around a lot but didn't succeed to get the rule working
properly. Here, the short version which narrows the problem:

the table logtest has the columns counter, date, program, message. The
following rule exists:

CREATE RULE pppd_time AS ON INSERT TO logtest where
new.program='pppd' DO INSERT INTO pppd_time (date, uptime) VALUES (now(),
to_number(split_part((new.message)::text, ' '::text, 3), '999D9'::text));

to copy the connection time to table pppd_time as a number. The intention
is, that only if the program equals to 'pppd' the rule shall be applied.

But inserting a message from another program leads to an error:

metalogsql=# insert into logtest (date, program, message) values (now(), 'yppd', 'Hello World');
ERROR: invalid input syntax for type numeric: " "
leading to no insertion into logtest and pppd_times.

whereas, if there is a third element which can be reformatted
to a number, the error doesn't occur:
metalogsql=# insert into logtest (date, program, message) values (now(), 'yppd', 'Hello World 999.9');
INSERT 411528 1
Only an entry in logtest occurs, none in pppd_times which is correct.

To me, it seems that the rule action is evaluated first (i.e., it is
checked whether an insert into pppd_times is feasible) and after that it
is checked, whether the rule shall be applied at all. I would have expect
it the other way around: first, execute the where clause to check whether
the rule shall be applied and then check on the insert into the new table.

Is there a work around to avoid the check on the action first?

Thanks and best regards
Klaus

Klaus Dahlke <klaus.dahlke (AT) gmx (DOT) de> wrote:
Could you post the CREATE TABLE statements for the tables you use?
Then I'll try to reproduce the problem and see if I can find anything.

Yours,
Laurenz Albe

On Thu, 28 Sep 2006 06:37:13 +0000, Laurenz Albe wrote:

Hi Laurenz,
thanks for your help, here are the statements:

metalogsql=# create sequence logtest_seq;
CREATE SEQUENCE
metalogsql=# create table logtest (id integer default nextval('logtest_seq'), date timestamp, program varchar(12), message varchar(256));
CREATE TABLE
metalogsql=# create table pppd_time (date timestamp, uptime numeric(8,2));
CREATE TABLE
metalogsql=# CREATE OR REPLACE RULE pppd_time AS ON INSERT TO logtest where program='pppd' DO INSERT INTO pppd_time (date, uptime) VALUES (now(), to_number(split_part((new.message)::text, ' '::text, 3), '999D9'::text));
CREATE RULE
metalogsql=# insert into logtest (date, program, message) values (now(), 'yppd', 'Hello World');
ERROR: invalid input syntax for type numeric: " "
metalogsql=# select * from logtest;
id | date | program | message
----+------+---------+---------
(0 rows)

metalogsql=# select * from pppd_time;
date | uptime
------+--------
(0 rows)

metalogsql=# insert into logtest (date, program, message) values (now(), 'yppd', 'Hello World 123.4');
INSERT 411906 1
metalogsql=# select * from logtest;
id | date | program | message
----+---------------------------+---------+-------------------
1 | 2006-09-28 21:24:47.22529 | yppd | Hello World 123.4
(1 row)

metalogsql=# select * from pppd_time;
date | uptime
------+--------
(0 rows)

metalogsql=# insert into logtest (date, program, message) values (now(), 'pppd', 'Hello World 567.8');
INSERT 411907 1
metalogsql=# select * from logtest;
id | date | program | message
----+----------------------------+---------+-------------------
1 | 2006-09-28 21:24:47.22529 | yppd | Hello World 123.4
2 | 2006-09-28 21:25:30.162185 | pppd | Hello World 567.8
(2 rows)

metalogsql=# select * from pppd_time;
date | uptime
----------------------------+--------
2006-09-28 21:25:30.162185 | 567.80
(1 row)

As you see, the first insert statement should go only into table logtest
(where clause not fulfilled), but it fails at the type conversion to
numeric called in the rule and then the entire statement aborts.

In the online documentation it is written:
For ON INSERT rules, the original query (if not suppressed by INSTEAD) is
done before any actions added by rules.

so even for the first insert statement I would expect to have an entry
in the logtest.


Thanks and Best regards,
Klaus

Klaus Dahlke <klaus.dahlke (AT) gmx (DOT) de> wrote:
I tried your example on an 8.1.4 server and get no error there,
a row is inserted in logtest, no row in pppd_time.

On an 8.0.4 server I can reproduce your problem.

I investigated on this, and the problem seems to be a difference in
how the two versions parse the following query:

SELECT
now(),
to_number(split_part(('Hello World')::text, ' '::text, 3), '999D9'::text)
WHERE 'pppd'::text='yppd'::text;

8.0.4 returns an error, 8.1.4 returns an empty result set.

Now I may be wrong, but I'd say that both behaviours are correct.
I could not find anything conclusive in the SQL standard, but then
I am no language lawyer. You might ask in pgsql-general.

When your rule is executed, the INSERT statement is internally transformed
into something like:

INSERT INTO pppd_time (date, uptime) VALUES
(now(),
to_number(split_part((NEW.message)::text, ' '::text, 3), '999D9'::text))
WHERE NEW.program='pppd';

This is syntactically wrong SQL, but see
http://www.postgresql.org/docs/8.1/s....html#AEN34081

My suggestion is that you avoid the problem, be it a bug or not, by
using an expression that does not produce an error, no matter what the
inserted message is. You can depend on it that the INSERT into pppd_time
will not take place except when the condition evaluates to TRUE.

Now that is an entirely different thing:

The INSERT into logtest will take place first, as you say. But the
subsequent error will cause the transaction to be rolled back, thereby
annihilating the first insert.

Yours,
Laurenz Albe

On Fri, 29 Sep 2006 10:32:40 +0000, Laurenz Albe wrote:

Hi Laurenz,
many thanks for looking into it! So, I have to make up my mind to either
upgrade or to play abit around with altering the rule. My current work
around is to have two columns in pppd_time for the time. One is of type
varchar, the other one is numeric. The first rule 'copies' the time in
form of a string from logtest into the pppd_time. pppd_time has also a
rule simply updating that row by converting the time string into a number.
It works for the time being but just having one column is much more
elegant.

Thanks again,
Klaus

Klaus Dahlke <klaus.dahlke (AT) gmx (DOT) de> wrote:
Why don't you use

to_number('0' || split_part((new.message)::text, ' '::text, 3), '999D9')

or something similar? It seems to work for me (do some testing), and that
way you can retain your original setup.

Yours,
Laurenz Albe

On Mon, 02 Oct 2006 07:56:38 +0000, Laurenz Albe wrote:

I gave it a try, unfortunately no success. Still the error 'ERROR:
invalid input syntax for type numeric: " "' occurs.

Anyhow, I am quite happy for the time being. After an upgrade to the next
version (haven't actually decided when) I will revisit the rules.

Cheers,
Klaus

Klaus Dahlke <klaus.dahlke (AT) gmx (DOT) de> wrote:
Really?

You get the error when you

select to_number('0' || split_part(('Hello World')::text,
' '::text, 3), '999D9');

That would be strange.
Do I miss something here?

Yours,
Laurenz Albe

On Thu, 05 Oct 2006 08:00:39 +0000, Laurenz Albe wrote:

mea culpa, did something wrong in the test. I gave a try again, used the
proper message examples and program examples, and having 'to_number('0'

Thanks again,
Klaus