Re: Spurious errors relating to escaped single quotes

On 15/07/11 08:00, Gavin Flower wrote:
> On 15/07/11 07:14, Tom Lane wrote:
>> Alvaro Herrera<alvherre(at)commandprompt(dot)com> writes:
>>> Excerpts from Gavin Flower's message of jue jul 14 07:45:00 -0400 2011:
>>>> Using pg 9.1beta3, I was found that running a function generated an
>>>> error relating to escaped single quotes, yet still produced the
>>>> answer I
>>>> expected!
>>> The errors are not spurious. The function doesn't exist now because it
>>> didn't get created. What happened is that now your inserts are going
>>> into the parent table, not the partitions as your script intended.
>> Possibly a more useful answer is "your function appears to be assuming
>> that standard_conforming_strings is OFF. As of 9.1 it's ON by default".
>>
>> regards, tom lane
> Thanks Tom (& Alvera),
>
> I checked my postgresql.conf:
> standard_conforming_strings = off
>
> I had forgotten I had changed this, and had simply reussed it from
> earlier!
>
> The silly thing is, that I had been treading up on partitioned tables
> and had come across a comment about mistakes could lead to populating
> the parent table!
>
> If I could change the sunject of thread, I would change it to start
> with 'Misleading...'.
>
> My lawyer siuggests defences of 'tiredness due ti it being late at
> night' and 'poor eyesight'! :-)
> (You can tell I've been reading groklaw.net way too much))
>
> More seriously:
> Could a hint be put in after the error message:
> 'psql:part000.sql:68: ERROR: function measurement_insert_trigger()
> does not exist'
> when the table has been partitioned, along the lines that 'this might
> cause inserts to go into the parent table, is this intended?' or some
> such? I am sure many others also get caught. This was purely an
> exercise for me, but it would be more serious in production code (yes
> I know things 'should' be tested properly first...).
>
>
> Cheers,
> Gavin
I think it should be possible to put a constraint on the master table to
prevent rows being inserted.

I was able to do this with a nasty hack:

CREATE TABLE measurement
(
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int,
CONSTRAINT nothing_allowed_in_master CHECK (city_id::text =
logdate::text)
);

But I think it would be better if the EXCLUDE' clause could take a value
'ALL' or 'EVERYTHING', to exclude everything - this would be simpler,
more universally valid (convenient columns for such a nasty hack may not
always be available), and be better documentation. If that was
implented, I could then rewrite the above as:

CREATE TABLE measurement
(
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int,
CONSTRAINT nothing_allowed_in_master EXCLUDE EVERYTHING
);

Cheers,
Gavin