Skip site navigation (1) Skip section navigation (2)

Re: Spurious errors relating to escaped single quotes

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Pg Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Spurious errors relating to escaped single quotes
Date: 2011-07-15 08:28:55
Message-ID: 4E1FFA47.20501@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-bugs
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!  :-(

In response to

Responses

pgsql-bugs by date

Next:From: leonardoDate: 2011-07-15 15:53:01
Subject: BUG #6119: Postgres ODBC Install.msi
Previous:From: Gavin FlowerDate: 2011-07-14 23:25:33
Subject: Re: Spurious errors relating to escaped single quotes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group