I think of the activity you described as TRIGGER events, more than rules.
It sound a bit complicated, like you have to avoid a series of foreign
key violations or loops or FOREIGNS where the PRIMARY side is NOT
transacted yet.
This situation still sound like a DATA problem with a value exceeding
the BOUNDS of what a field will hold.
Have you tried DEBUGGING with a simplistic version where each action is
done as separate steps? (I suppose if the rule are in place you have to
redo you database a bit to test this.) .... I guess you have,...that's
what your message says.
Is it possible to use the client to send each step as a statement, all
wrapped in a transaction?
Is it possible to write a function (stored procedure) to take care of
the combination of steps?
Peter Bense wrote:
> I want to thank everyone who has provided suggestions regarding my
> MS-Access / ODBC / Link-tables issue the past couple of days.
>
> Here's what I've found:
>
> 1. As my gut instincts had told me, there is no problem with the
> translations of booleans, at least given how I have been using them
> (with foreign-key lookups).
>
> 2. Inserts into the following view work cleanly:
>
> afl=# \d vi_tblpis_survey_receipt
> View "public.vi_tblpis_survey_receipt"
> Column | Type | Modifiers
> ---------------+----------+-----------
> ppt_id | integer |
> date_received | date |
> staff_id | integer |
> survey_type | smallint |
> is_blank | boolean |
> birth_month | smallint |
> birth_year | smallint |
> View definition:
> SELECT tblpis_survey_receipt.ppt_id,
> tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
> tblpis_survey_receipt.survey_type, tblpis_survey_receipt.is_blank,
> tblpis_survey_receipt.birth_month, tblpis_survey_receipt.birth_year
> FROM tblpis_survey_receipt
> ORDER BY tblpis_survey_receipt.insertion;
>
> 3. THINGS BREAK WHEN I APPLY CERTAIN TYPES OF RULES TO THE VIEW.
>
> The way this view is supposed to work is as follows:
> A) - A data entry person enters participant ID, survey type, date
> received, etc.
> B) - A RULE performs the following insertion:
> INSERT INTO tblpis_survey_receipt (ppt_id, date_received, staff_id,
> survey_type, is_blank, birth_month, birth_year, check_ppt, check_dob,
> check_tracking, date_inserted, date_modified)
> VALUES (new.ppt_id, new.date_received, new.staff_id, new.survey_type,
> new.is_blank, new.birth_month, new.birth_year, 'f', 'f', 'f', now(),
> now());
>
> C) - A series of checks are conducted to ensure that this data is
> valid. Basically 4 or 5 updates are run to toggle these boolean fields
> on the PostgreSQL side. If the participant ID is a valid participant
> ID, that field is toggled. Once it passes that field, the month and
> year of birth are verified. If that check is successful, then it checks
> to see whether or not there is an associated tracking record already.
> If there isn't, it passes the tracking check.
>
> All of those steps work fine on my test inserts, and the datavalues are
> toggled accordingly. So far so good.
>
> WHEN I TRY TO INSERT DATA INTO SOME OTHER TABLE FROM THAT RULE, THINGS
> BREAK!
>
> As soon as I add an INSERT statement to my rule following the UPDATEs,
> e.g.
> INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
> pre_is_blank)
> SELECT ppt_id, date_received, staff_id, is_blank
> FROM tblpis_survey_receipt
> WHERE ppt_id=new.ppt_id
> AND survey_type=1
> AND check_ppt='t'
> AND check_dob='t'
> AND check_tracking='t';
> );
>
> ... Things break upon insert.
>
> Why?
>
> Can I only perform one insert per AS ON INSERT TO?
>
> If so, this might be the cause of my problem.
>
>
> Help!
>
> ./Peter
>
> Peter T. Bense - Teradata Certified Professional
> (ptbense@gwm.sc.edu) - 803-777-9476
> Database Administrator/Webmaster
> Prevention Research Center
> University of South Carolina
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)