Re: Spurious errors relating to escaped single quotes

On 15/07/11 11:25, Gavin Flower wrote:
> On 15/07/11 08:00, Gavin Flower wrote:
>> On 15/07/11 07:14, Tom Lane wrote:
>>> Alvaro Herrera<alvherre@commandprompt.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
>
Of course, minutes after I sent the above - I realized these constraints
are inherited, so the above is nonsense!  :-(