On 15/07/11 08:00, Gavin Flower wrote:
On 15/07/11 07:14, Tom Lane wrote:
Alvaro Herrera<alvhe...@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
