Re: Using subselects in INSERTs?

Thanks.

Yeah, there was a small change. Between the versions I tested (7.3.2 and
7.3.4), the RULE was implemented, although I had thought they were using
the same dumps. I didn't notice that when I first posted. (It was a late
night, you'll have to excuse my sleepy eyes.)

Someone posted an interesting workaround that I'll probably use for the time
being, as we have several servers set up at different client sites that I'd
rather not patch. I'll try out the patch locally, though, and I'll be
upgrading the servers after the next PostgreSQL release. (7.3.5 or 7.4, I
assume.)

Thanks for looking into this, though.

J

Tom Lane wrote:

> J Smith <dark_panda(at)hushmail(dot)com> writes:
>> I managed to trace the problem back to a RULE that was created while I
>> must have been asleep at the wheel. The problem goes away when I disable
>> the RULE or replace the subquery with an actual value.
>
> I dug into this and found that the misbehavior occurs when the
> sub-SELECT that is present in the INSERT:
>
>> INSERT INTO clip (program_id, clip_name) VALUES (
>> (SELECT program_id FROM program WHERE program_code = '9531443001'),
>> 'Canada: A Diverse Culture');
>
> is inserted to replace "new.program_id" in the RULE:
>
>> CREATE RULE program_clip_insert_only_1 AS ON INSERT TO clip WHERE
>> ((SELECT count(*) AS count FROM clip WHERE clip.program_id =
>> new.program_id) >= 1) DO INSTEAD NOTHING;
>
> As far as I can tell, this problem has existed for a long time; it is
> certainly not new in 7.3.4. (I see the same failure in 7.2.4 as 7.3.4.)
> Are you sure you weren't changing your application at the same time you
> updated?
>
> I've applied the attached patch to the 7.3 branch, if you want to use
> it.
>
> regards, tom lane
>
>