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-14 23:25:33
Message-ID: 4E1F7AED.2030201@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-bugs
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







In response to

Responses

pgsql-bugs by date

Next:From: Gavin FlowerDate: 2011-07-15 08:28:55
Subject: Re: Spurious errors relating to escaped single quotes
Previous:From: Gavin FlowerDate: 2011-07-14 20:00:45
Subject: Re: Spurious errors relating to escaped single quotes

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