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(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
>
Of course, minutes after I sent the above - I realized these constraints
are inherited, so the above is nonsense! :-(