Обсуждение: Spurious errors relating to escaped single quotes
Hi,
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!
/////////////// part000.sql script ///////////
DROP TABLE IF EXISTS measurement CASCADE;
CREATE TABLE measurement
(
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
CREATE TABLE measurement_y2010m11
(
CHECK (logdate >= '2010-11-01' AND logdate < '2010-12-01')
) INHERITS (measurement);
CREATE TABLE measurement_y2010m12(
CHECK (logdate >= '2010-12-01' AND logdate < '2011-01-01')
) INHERITS (measurement);
CREATE TABLE measurement_y2011m01
(
CHECK (logdate >= '2011-01-01' AND logdate < '2011-02-01')
) INHERITS (measurement);
CREATE TABLE measurement_y2011m02
(
CHECK (logdate >= '2011-02-01' AND logdate < '2011-03-01')
) INHERITS (measurement);
CREATE INDEX ON measurement_y2010m11 (logdate);
CREATE INDEX ON measurement_y2010m12 (logdate);
CREATE INDEX ON measurement_y2011m01 (logdate);
CREATE INDEX ON measurement_y2011m02 (logdate);
DROP FUNCTION IF EXISTS measurement_insert_trigger() CASCADE;
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_yyyy int;
v_mm int;
v_sql text;
BEGIN
v_yyyy := extract('year' FROM NEW.logdate);
v_mm := extract('month' FROM NEW.logdate) ;
v_sql := 'INSERT INTO measurement_y' ||
v_yyyy ||
'm' ||
v_mm ||
' VALUES (' ||
NEW.city_id || ', ' ||
''\' || NEW.logdate || '\', ' ||
NEW.peaktemp || ', ' ||
NEW.unitsales ||
')';
EXECUTE v_sql;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
INSERT INTO measurement
(
city_id,
logdate,
peaktemp,
unitsales
)
VALUES
(3, '2011-01-07', 34, 4000);
TABLE measurement;
////////////// output follows ////////////////
gavin=> \c gcf_db
You are now connected to database "gcf_db" as user "gavin"
gcf_db=> \i part000.sql
psql:part000.sql:1: NOTICE: table "measurement" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
psql:part000.sql:38: NOTICE: function measurement_insert_trigger() does
not exist, skipping
DROP FUNCTION
psql:part000.sql:63: ERROR: syntax error at or near "\"
LINE 16: ''\' || NEW.logdate || '\', ' ||
^
psql:part000.sql:68: ERROR: function measurement_insert_trigger() does
not exist
INSERT 0 1
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
3 | 2011-01-07 | 34 | 4000
(1 row)
gcf_db=>
Cheers,
Gavin
Excerpts from Gavin Flower's message of jue jul 14 07:45:00 -0400 2011: > Hi, > > 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. -- Ãlvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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
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
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
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! :-(
Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
> On 15/07/11 11:25, Gavin Flower wrote:
>> I think it should be possible to put a constraint on the master table
>> to prevent rows being inserted.
> Of course, minutes after I sent the above - I realized these constraints
> are inherited, so the above is nonsense! :-(
Yeah. I think there's been some discussion of inventing a non-inherited
variety of check constraint, so that you could put something like
"CHECK (false) NO INHERIT" on the parent table. There's not really
consensus for this though.
IMO we'd be better advised to spend our time on building an explicit
partitioning mechanism to handle the common cases more simply and
efficiently, instead of continuing to add frammishes to the inheritance
mechanism.
regards, tom lane
On 16/07/11 05:21, Tom Lane wrote: > Gavin Flower<GavinFlower@archidevsys.co.nz> writes: >> On 15/07/11 11:25, Gavin Flower wrote: >>> I think it should be possible to put a constraint on the master table >>> to prevent rows being inserted. >> Of course, minutes after I sent the above - I realized these constraints >> are inherited, so the above is nonsense! :-( > Yeah. I think there's been some discussion of inventing a non-inherited > variety of check constraint, so that you could put something like > "CHECK (false) NO INHERIT" on the parent table. There's not really > consensus for this though. > > IMO we'd be better advised to spend our time on building an explicit > partitioning mechanism to handle the common cases more simply and > efficiently, instead of continuing to add frammishes to the inheritance > mechanism. > > regards, tom lane How about being able to mark individual columns, and optionally the table itself, as ABSTRACT? Though, I agree that a proper partitioning mechanism would be better. Cheers, Gavin