Re: Escape handling in strings

A summary of my proposal to add a new E'' string for escape and have
non-E escapes not handle backslashes specially is at:

http://candle.pha.pa.us/cgi-bin/pgescape

Attached is a patch that emits warnings for \ and \', perhaps for 8.1.
The change to scan.l is the place this is done. The rest of the patch
is adjustments to prevent our own code from generating warnings. It
shows a good example of how users would have to change their code.

It passes all regression tests, contrib regression, and initdb runs
without warning.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I'm still really iffy about this. I think it will really hurt pgsql due
to backward compatibility :(

(If I'm understanding how the proposed change works...)

Chris

Bruce Momjian wrote:
> A summary of my proposal to add a new E'' string for escape and have
> non-E escapes not handle backslashes specially is at:
>
> http://candle.pha.pa.us/cgi-bin/pgescape
>
> Attached is a patch that emits warnings for \ and \', perhaps for 8.1.
> The change to scan.l is the place this is done. The rest of the patch
> is adjustments to prevent our own code from generating warnings. It
> shows a good example of how users would have to change their code.
>
> It passes all regression tests, contrib regression, and initdb runs
> without warning.
>
>
>
> ------------------------------------------------------------------------
>
> Index: contrib/tsearch2/expected/tsearch2.out
> ===================================================================
> RCS file: /cvsroot/pgsql/contrib/tsearch2/expected/tsearch2.out,v
> retrieving revision 1.11
> diff -c -c -r1.11 tsearch2.out
> *** contrib/tsearch2/expected/tsearch2.out 14 Sep 2004 03:58:54 -0000 1.11
> --- contrib/tsearch2/expected/tsearch2.out 16 Jun 2005 01:36:54 -0000
> ***************
> *** 47,83 ****
> '1' '2'
> (1 row)
>
> ! SELECT '\'1 2\''::tsvector;
> tsvector
> ----------
> '1 2'
> (1 row)
>
> ! SELECT '\'1 \\\'2\''::tsvector;
> tsvector
> ----------
> '1 \'2'
> (1 row)
>
> ! SELECT '\'1 \\\'2\'3'::tsvector;
> tsvector
> -------------
> '3' '1 \'2'
> (1 row)
>
> ! SELECT '\'1 \\\'2\' 3'::tsvector;
> tsvector
> -------------
> '3' '1 \'2'
> (1 row)
>
> ! SELECT '\'1 \\\'2\' \' 3\' 4 '::tsvector;
> tsvector
> ------------------
> '4' ' 3' '1 \'2'
> (1 row)
>
> ! select '\'w\':4A,3B,2C,1D,5 a:8';
> ?column?
> -----------------------
> 'w':4A,3B,2C,1D,5 a:8
> --- 47,83 ----
> '1' '2'
> (1 row)
>
> ! SELECT '''1 2'''::tsvector;
> tsvector
> ----------
> '1 2'
> (1 row)
>
> ! SELECT E'''1 \\''2'''::tsvector;
> tsvector
> ----------
> '1 \'2'
> (1 row)
>
> ! SELECT E'''1 \\''2''3'::tsvector;
> tsvector
> -------------
> '3' '1 \'2'
> (1 row)
>
> ! SELECT E'''1 \\''2'' 3'::tsvector;
> tsvector
> -------------
> '3' '1 \'2'
> (1 row)
>
> ! SELECT E'''1 \\''2'' '' 3'' 4 '::tsvector;
> tsvector
> ------------------
> '4' ' 3' '1 \'2'
> (1 row)
>
> ! select '''w'':4A,3B,2C,1D,5 a:8';
> ?column?
> -----------------------
> 'w':4A,3B,2C,1D,5 a:8
> ***************
> *** 126,138 ****
> '1'
> (1 row)
>
> ! SELECT '\'1 2\''::tsquery;
> tsquery
> ---------
> '1 2'
> (1 row)
>
> ! SELECT '\'1 \\\'2\''::tsquery;
> tsquery
> ---------
> '1 \'2'
> --- 126,138 ----
> '1'
> (1 row)
>
> ! SELECT '''1 2'''::tsquery;
> tsquery
> ---------
> '1 2'
> (1 row)
>
> ! SELECT E'''1 \\''2'''::tsquery;
> tsquery
> ---------
> '1 \'2'
> ***************
> *** 330,342 ****
> '1' & '2' & '4' & ( '5' | !'6' )
> (1 row)
>
> ! SELECT '1&(\'2\'&(\' 4\'&(\\|5 | \'6 \\\' !|&\')))'::tsquery;
> tsquery
> ------------------------------------------
> '1' & '2' & ' 4' & ( '|5' | '6 \' !|&' )
> (1 row)
>
> ! SELECT '\'the wether\':dc & \' sKies \':BC & a:d b:a';
> ?column?
> ------------------------------------------
> 'the wether':dc & ' sKies ':BC & a:d b:a
> --- 330,342 ----
> '1' & '2' & '4' & ( '5' | !'6' )
> (1 row)
>
> ! SELECT E'1&(''2''&('' 4''&(\\|5 | ''6 \\'' !|&'')))'::tsquery;
> tsquery
> ------------------------------------------
> '1' & '2' & ' 4' & ( '|5' | '6 \' !|&' )
> (1 row)
>
> ! SELECT '''the wether'':dc & '' sKies '':BC & a:d b:a';
> ?column?
> ------------------------------------------
> 'the wether':dc & ' sKies ':BC & a:d b:a
> ***************
> *** 382,388 ****
> 23 | entity | HTML Entity
> (23 rows)
>
> ! select * from parse('default', '345 qwe(at)efd(dot)r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty');
> tokid | token
> --- 382,388 ----
> 23 | entity | HTML Entity
> (23 rows)
>
> ! select * from parse('default', '345 qwe(at)efd(dot)r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty');
> tokid | token
> ***************
> *** 529,535 ****
> 1 | qwerty
> (138 rows)
>
> ! SELECT to_tsvector('default', '345 qwe(at)efd(dot)r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty');
> to_tsvector
> --- 529,535 ----
> 1 | qwerty
> (138 rows)
>
> ! SELECT to_tsvector('default', '345 qwe(at)efd(dot)r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty');
> to_tsvector
> ***************
> *** 543,549 ****
> 2
> (1 row)
>
> ! SELECT length(to_tsvector('default', '345 qwe(at)efd(dot)r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty'));
> length
> --- 543,549 ----
> 2
> (1 row)
>
> ! SELECT length(to_tsvector('default', '345 qwe(at)efd(dot)r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty'));
> length
> ***************
> *** 563,569 ****
> 'qwe' & 'skies'
> (1 row)
>
> ! select to_tsquery('default', '\'the wether\':dc & \' sKies \':BC ');
> to_tsquery
> ------------------------
> 'wether':CD & 'sky':BC
> --- 563,569 ----
> 'qwe' & 'skies'
> (1 row)
>
> ! select to_tsquery('default', '''the wether'':dc & '' sKies '':BC ');
> to_tsquery
> ------------------------
> 'wether':CD & 'sky':BC
> ***************
> *** 729,735 ****
> (1 row)
>
> drop trigger tsvectorupdate on test_tsvector;
> ! create function wow(text) returns text as 'select $1 || \' copyright\'; ' language sql;
> create trigger tsvectorupdate before update or insert on test_tsvector
> for each row execute procedure tsearch2(a, wow, t);
> insert into test_tsvector (t) values ('345 qwerty');
> --- 729,735 ----
> (1 row)
>
> drop trigger tsvectorupdate on test_tsvector;
> ! create function wow(text) returns text as 'select $1 || '' copyright''; ' language sql;
> create trigger tsvectorupdate before update or insert on test_tsvector
> for each row execute procedure tsearch2(a, wow, t);
> insert into test_tsvector (t) values ('345 qwerty');
> Index: contrib/tsearch2/sql/tsearch2.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/contrib/tsearch2/sql/tsearch2.sql,v
> retrieving revision 1.7
> diff -c -c -r1.7 tsearch2.sql
> *** contrib/tsearch2/sql/tsearch2.sql 28 Jun 2004 16:18:56 -0000 1.7
> --- contrib/tsearch2/sql/tsearch2.sql 16 Jun 2005 01:36:54 -0000
> ***************
> *** 12,23 ****
> SELECT ' 1'::tsvector;
> SELECT ' 1 '::tsvector;
> SELECT '1 2'::tsvector;
> ! SELECT '\'1 2\''::tsvector;
> ! SELECT '\'1 \\\'2\''::tsvector;
> ! SELECT '\'1 \\\'2\'3'::tsvector;
> ! SELECT '\'1 \\\'2\' 3'::tsvector;
> ! SELECT '\'1 \\\'2\' \' 3\' 4 '::tsvector;
> ! select '\'w\':4A,3B,2C,1D,5 a:8';
> select 'a:3A b:2a'::tsvector || 'ba:1234 a:1B';
> select setweight('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd zxc:81,567,222A'::tsvector, 'c');
> select strip('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd'::tsvector);
> --- 12,23 ----
> SELECT ' 1'::tsvector;
> SELECT ' 1 '::tsvector;
> SELECT '1 2'::tsvector;
> ! SELECT '''1 2'''::tsvector;
> ! SELECT E'''1 \\''2'''::tsvector;
> ! SELECT E'''1 \\''2''3'::tsvector;
> ! SELECT E'''1 \\''2'' 3'::tsvector;
> ! SELECT E'''1 \\''2'' '' 3'' 4 '::tsvector;
> ! select '''w'':4A,3B,2C,1D,5 a:8';
> select 'a:3A b:2a'::tsvector || 'ba:1234 a:1B';
> select setweight('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd zxc:81,567,222A'::tsvector, 'c');
> select strip('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd'::tsvector);
> ***************
> *** 28,35 ****
> SELECT '1 '::tsquery;
> SELECT ' 1'::tsquery;
> SELECT ' 1 '::tsquery;
> ! SELECT '\'1 2\''::tsquery;
> ! SELECT '\'1 \\\'2\''::tsquery;
> SELECT '!1'::tsquery;
> SELECT '1|2'::tsquery;
> SELECT '1|!2'::tsquery;
> --- 28,35 ----
> SELECT '1 '::tsquery;
> SELECT ' 1'::tsquery;
> SELECT ' 1 '::tsquery;
> ! SELECT '''1 2'''::tsquery;
> ! SELECT E'''1 \\''2'''::tsquery;
> SELECT '!1'::tsquery;
> SELECT '1|2'::tsquery;
> SELECT '1|!2'::tsquery;
> ***************
> *** 62,92 ****
> SELECT '1&2&4&5&6'::tsquery;
> SELECT '1&(2&(4&(5|6)))'::tsquery;
> SELECT '1&(2&(4&(5|!6)))'::tsquery;
> ! SELECT '1&(\'2\'&(\' 4\'&(\\|5 | \'6 \\\' !|&\')))'::tsquery;
> ! SELECT '\'the wether\':dc & \' sKies \':BC & a:d b:a';
>
> select lexize('simple', 'ASD56 hsdkf');
> select lexize('en_stem', 'SKIES Problems identity');
>
> select * from token_type('default');
> ! select * from parse('default', '345 qwe(at)efd(dot)r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty');
>
> ! SELECT to_tsvector('default', '345 qwe(at)efd(dot)r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty');
>
> SELECT length(to_tsvector('default', '345 qw'));
>
> ! SELECT length(to_tsvector('default', '345 qwe(at)efd(dot)r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty'));
>
>
> select to_tsquery('default', 'qwe & sKies ');
> select to_tsquery('simple', 'qwe & sKies ');
> ! select to_tsquery('default', '\'the wether\':dc & \' sKies \':BC ');
> select to_tsquery('default', 'asd&(and|fghj)');
> select to_tsquery('default', '(asd&and)|fghj');
> select to_tsquery('default', '(asd&!and)|fghj');
> --- 62,92 ----
> SELECT '1&2&4&5&6'::tsquery;
> SELECT '1&(2&(4&(5|6)))'::tsquery;
> SELECT '1&(2&(4&(5|!6)))'::tsquery;
> ! SELECT E'1&(''2''&('' 4''&(\\|5 | ''6 \\'' !|&'')))'::tsquery;
> ! SELECT '''the wether'':dc & '' sKies '':BC & a:d b:a';
>
> select lexize('simple', 'ASD56 hsdkf');
> select lexize('en_stem', 'SKIES Problems identity');
>
> select * from token_type('default');
> ! select * from parse('default', '345 qwe(at)efd(dot)r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty');
>
> ! SELECT to_tsvector('default', '345 qwe(at)efd(dot)r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty');
>
> SELECT length(to_tsvector('default', '345 qw'));
>
> ! SELECT length(to_tsvector('default', '345 qwe(at)efd(dot)r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor(at)stack(dot)net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
> /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234
> <i <b> wow < jqw <> qwerty'));
>
>
> select to_tsquery('default', 'qwe & sKies ');
> select to_tsquery('simple', 'qwe & sKies ');
> ! select to_tsquery('default', '''the wether'':dc & '' sKies '':BC ');
> select to_tsquery('default', 'asd&(and|fghj)');
> select to_tsquery('default', '(asd&and)|fghj');
> select to_tsquery('default', '(asd&!and)|fghj');
> ***************
> *** 135,141 ****
> SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
>
> drop trigger tsvectorupdate on test_tsvector;
> ! create function wow(text) returns text as 'select $1 || \' copyright\'; ' language sql;
> create trigger tsvectorupdate before update or insert on test_tsvector
> for each row execute procedure tsearch2(a, wow, t);
> insert into test_tsvector (t) values ('345 qwerty');
> --- 135,141 ----
> SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
>
> drop trigger tsvectorupdate on test_tsvector;
> ! create function wow(text) returns text as 'select $1 || '' copyright''; ' language sql;
> create trigger tsvectorupdate before update or insert on test_tsvector
> for each row execute procedure tsearch2(a, wow, t);
> insert into test_tsvector (t) values ('345 qwerty');
> Index: src/backend/parser/scan.l
> ===================================================================
> RCS file: /cvsroot/pgsql/src/backend/parser/scan.l,v
> retrieving revision 1.125
> diff -c -c -r1.125 scan.l
> *** src/backend/parser/scan.l 15 Jun 2005 16:28:06 -0000 1.125
> --- src/backend/parser/scan.l 16 Jun 2005 01:36:55 -0000
> ***************
> *** 49,54 ****
> --- 49,55 ----
>
> static int xcdepth = 0; /* depth of nesting in slash-star comments */
> static char *dolqstart; /* current $foo$ quote start string */
> + static bool warn_on_escape;
>
> /*
> * literalbuf is used to accumulate literal values when multiple rules
> ***************
> *** 64,69 ****
> --- 65,71 ----
> static void addlit(char *ytext, int yleng);
> static void addlitchar(unsigned char ychar);
> static char *litbufdup(void);
> + static void check_escape_warning(void);
>
> /*
> * When we parse a token that requires multiple lexer rules to process,
> ***************
> *** 185,190 ****
> --- 187,196 ----
> /* National character */
> xnstart [nN]{quote}
>
> + /* Quote string does not warn about escapes */
> + xestart [eE]{quote}
> + xeinside [^']*
> +
> /* Extended quote
> * xqdouble implements embedded quote, ''''
> */
> ***************
> *** 410,415 ****
> --- 416,428 ----
> }
>
> {xqstart} {
> + warn_on_escape = true;
> + token_start = yytext;
> + BEGIN(xq);
> + startlit();
> + }
> + {xestart} {
> + warn_on_escape = false;
> token_start = yytext;
> BEGIN(xq);
> startlit();
> ***************
> *** 428,441 ****
> --- 441,468 ----
> addlit(yytext, yyleng);
> }
> <xq>{xqescape} {
> + if (yytext[1] == '\'')
> + {
> + if (warn_on_escape)
> + ereport(WARNING,
> + (errcode(ERRCODE_INVALID_USE_OF_ESCAPE_CHARACTER),
> + errmsg("Invalid use of \' in a normal string"),
> + errhint("Use '' to place quotes in strings, or use E-type strings.")));
> + }
> + else
> + check_escape_warning();
> addlitchar(unescape_single_char(yytext[1]));
> }
> <xq>{xqoctesc} {
> unsigned char c = strtoul(yytext+1, NULL, 8);
> +
> + check_escape_warning();
> addlitchar(c);
> }
> <xq>{xqhexesc} {
> unsigned char c = strtoul(yytext+2, NULL, 16);
> +
> + check_escape_warning();
> addlitchar(c);
> }
> <xq>{quotecontinue} {
> ***************
> *** 810,812 ****
> --- 837,850 ----
> return c;
> }
> }
> +
> + static void
> + check_escape_warning(void)
> + {
> + if (warn_on_escape)
> + ereport(WARNING,
> + (errcode(ERRCODE_INVALID_USE_OF_ESCAPE_CHARACTER),
> + errmsg("Invalid use of escapes in a normal string"),
> + errhint("Use E-type strings for escapes, e.g. E'\\r\\n'.")));
> + warn_on_escape = false; /* warn only once per string */
> + }
> Index: src/bin/initdb/initdb.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
> retrieving revision 1.83
> diff -c -c -r1.83 initdb.c
> *** src/bin/initdb/initdb.c 30 Apr 2005 08:08:51 -0000 1.83
> --- src/bin/initdb/initdb.c 16 Jun 2005 01:36:57 -0000
> ***************
> *** 1688,1694 ****
> char **priv_lines;
> static char *privileges_setup[] = {
> "UPDATE pg_class "
> ! " SET relacl = '{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' "
> " WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n",
> "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n",
> "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n",
> --- 1688,1694 ----
> char **priv_lines;
> static char *privileges_setup[] = {
> "UPDATE pg_class "
> ! " SET relacl = E'{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' "
> " WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n",
> "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n",
> "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n",
> ***************
> *** 1952,1959 ****
>
> for (i = 0, j = 0; i < len; i++)
> {
> ! if (src[i] == '\'' || src[i] == '\\')
> result[j++] = '\\';
> result[j++] = src[i];
> }
> result[j] = '\0';
> --- 1952,1961 ----
>
> for (i = 0, j = 0; i < len; i++)
> {
> ! if (src[i] == '\\')
> result[j++] = '\\';
> + if (src[i] == '\'') /* ANSI standard, '' */
> + result[j++] = '\'';
> result[j++] = src[i];
> }
> result[j] = '\0';
> Index: src/bin/pg_dump/pg_dumpall.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
> retrieving revision 1.59
> diff -c -c -r1.59 pg_dumpall.c
> *** src/bin/pg_dump/pg_dumpall.c 18 Apr 2005 23:47:52 -0000 1.59
> --- src/bin/pg_dump/pg_dumpall.c 16 Jun 2005 01:36:57 -0000
> ***************
> *** 538,544 ****
> "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
> "spclocation, spcacl "
> "FROM pg_catalog.pg_tablespace "
> ! "WHERE spcname NOT LIKE 'pg\\_%'");
>
> if (PQntuples(res) > 0)
> printf("--\n-- Tablespaces\n--\n\n");
> --- 538,544 ----
> "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
> "spclocation, spcacl "
> "FROM pg_catalog.pg_tablespace "
> ! "WHERE spcname NOT LIKE E'pg\\_%'");
>
> if (PQntuples(res) > 0)
> printf("--\n-- Tablespaces\n--\n\n");
> Index: src/bin/psql/describe.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
> retrieving revision 1.117
> diff -c -c -r1.117 describe.c
> *** src/bin/psql/describe.c 14 Jun 2005 23:59:31 -0000 1.117
> --- src/bin/psql/describe.c 16 Jun 2005 01:36:58 -0000
> ***************
> *** 1766,1772 ****
> appendPQExpBuffer(&buf,
> "\nFROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
> " ON n.nspowner=u.usesysid\n"
> ! "WHERE (n.nspname NOT LIKE 'pg\\\\_temp\\\\_%%' OR\n"
> " n.nspname = (pg_catalog.current_schemas(true))[1])\n"); /* temp schema is first */
>
> processNamePattern(&buf, pattern, true, false,
> --- 1766,1772 ----
> appendPQExpBuffer(&buf,
> "\nFROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
> " ON n.nspowner=u.usesysid\n"
> ! "WHERE (n.nspname NOT LIKE E'pg\\\\_temp\\\\_%%' OR\n"
> " n.nspname = (pg_catalog.current_schemas(true))[1])\n"); /* temp schema is first */
>
> processNamePattern(&buf, pattern, true, false,
> Index: src/include/catalog/pg_proc.h
> ===================================================================
> RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
> retrieving revision 1.367
> diff -c -c -r1.367 pg_proc.h
> *** src/include/catalog/pg_proc.h 14 Jun 2005 21:04:41 -0000 1.367
> --- src/include/catalog/pg_proc.h 16 Jun 2005 01:37:03 -0000
> ***************
> *** 1461,1467 ****
> DESCR("greater-than-or-equal");
> DATA(insert OID = 1157 ( timestamptz_gt PGNSP PGUID 12 f f t f i 2 16 "1184 1184" _null_ _null_ _null_ timestamp_gt - _null_ ));
> DESCR("greater-than");
> ! DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID 14 f f t f i 1 1184 "701" _null_ _null_ _null_ "select (\'epoch\'::timestamptz + $1 * \'1 second\'::interval)" - _null_ ));
> DESCR("convert UNIX epoch to timestamptz");
> DATA(insert OID = 1159 ( timezone PGNSP PGUID 12 f f t f i 2 1114 "25 1184" _null_ _null_ _null_ timestamptz_zone - _null_ ));
> DESCR("adjust timestamp to new time zone");
> --- 1461,1467 ----
> DESCR("greater-than-or-equal");
> DATA(insert OID = 1157 ( timestamptz_gt PGNSP PGUID 12 f f t f i 2 16 "1184 1184" _null_ _null_ _null_ timestamp_gt - _null_ ));
> DESCR("greater-than");
> ! DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID 14 f f t f i 1 1184 "701" _null_ _null_ _null_ "select (''epoch''::timestamptz + $1 * ''1 second''::interval)" - _null_ ));
> DESCR("convert UNIX epoch to timestamptz");
> DATA(insert OID = 1159 ( timezone PGNSP PGUID 12 f f t f i 2 1114 "25 1184" _null_ _null_ _null_ timestamptz_zone - _null_ ));
> DESCR("adjust timestamp to new time zone");
> ***************
> *** 1541,1547 ****
>
> DATA(insert OID = 1215 ( obj_description PGNSP PGUID 14 f f t f s 2 25 "26 19" _null_ _null_ _null_ "select description from pg_catalog.pg_description where objoid = $1 and classoid = (select oid from pg_catalog.pg_class where relname = $2 and relnamespace = PGNSP) and objsubid = 0" - _null_ ));
> DESCR("get description for object id and catalog name");
> ! DATA(insert OID = 1216 ( col_description PGNSP PGUID 14 f f t f s 2 25 "26 23" _null_ _null_ _null_ "select description from pg_catalog.pg_description where objoid = $1 and classoid = \'pg_catalog.pg_class\'::regclass and objsubid = $2" - _null_ ));
> DESCR("get description for table column");
>
> DATA(insert OID = 1217 ( date_trunc PGNSP PGUID 12 f f t f s 2 1184 "25 1184" _null_ _null_ _null_ timestamptz_trunc - _null_ ));
> --- 1541,1547 ----
>
> DATA(insert OID = 1215 ( obj_description PGNSP PGUID 14 f f t f s 2 25 "26 19" _null_ _null_ _null_ "select description from pg_catalog.pg_description where objoid = $1 and classoid = (select oid from pg_catalog.pg_class where relname = $2 and relnamespace = PGNSP) and objsubid = 0" - _null_ ));
> DESCR("get description for object id and catalog name");
> ! DATA(insert OID = 1216 ( col_description PGNSP PGUID 14 f f t f s 2 25 "26 23" _null_ _null_ _null_ "select description from pg_catalog.pg_description where objoid = $1 and classoid = ''pg_catalog.pg_class''::regclass and objsubid = $2" - _null_ ));
> DESCR("get description for table column");
>
> DATA(insert OID = 1217 ( date_trunc PGNSP PGUID 12 f f t f s 2 1184 "25 1184" _null_ _null_ _null_ timestamptz_trunc - _null_ ));
> ***************
> *** 2185,2193 ****
> DESCR("return portion of string");
> DATA(insert OID = 878 ( translate PGNSP PGUID 12 f f t f i 3 25 "25 25 25" _null_ _null_ _null_ translate - _null_ ));
> DESCR("map a set of character appearing in string");
> ! DATA(insert OID = 879 ( lpad PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select pg_catalog.lpad($1, $2, \' \')" - _null_ ));
> DESCR("left-pad string to length");
> ! DATA(insert OID = 880 ( rpad PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select pg_catalog.rpad($1, $2, \' \')" - _null_ ));
> DESCR("right-pad string to length");
> DATA(insert OID = 881 ( ltrim PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_ ltrim1 - _null_ ));
> DESCR("trim spaces from left end of string");
> --- 2185,2193 ----
> DESCR("return portion of string");
> DATA(insert OID = 878 ( translate PGNSP PGUID 12 f f t f i 3 25 "25 25 25" _null_ _null_ _null_ translate - _null_ ));
> DESCR("map a set of character appearing in string");
> ! DATA(insert OID = 879 ( lpad PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select pg_catalog.lpad($1, $2, '' '')" - _null_ ));
> DESCR("left-pad string to length");
> ! DATA(insert OID = 880 ( rpad PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select pg_catalog.rpad($1, $2, '' '')" - _null_ ));
> DESCR("right-pad string to length");
> DATA(insert OID = 881 ( ltrim PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_ ltrim1 - _null_ ));
> DESCR("trim spaces from left end of string");
> Index: src/test/regress/expected/arrays.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/arrays.out,v
> retrieving revision 1.25
> diff -c -c -r1.25 arrays.out
> *** src/test/regress/expected/arrays.out 22 Apr 2005 21:58:32 -0000 1.25
> --- src/test/regress/expected/arrays.out 16 Jun 2005 01:37:04 -0000
> ***************
> *** 436,442 ****
> ERROR: malformed array literal: "{{1,{2}},{2,3}}"
> select '{{},{}}'::text[];
> ERROR: malformed array literal: "{{},{}}"
> ! select '{{1,2},\\{2,3}}'::text[];
> ERROR: malformed array literal: "{{1,2},\{2,3}}"
> select '{{"1 2" x},{3}}'::text[];
> ERROR: malformed array literal: "{{"1 2" x},{3}}"
> --- 436,442 ----
> ERROR: malformed array literal: "{{1,{2}},{2,3}}"
> select '{{},{}}'::text[];
> ERROR: malformed array literal: "{{},{}}"
> ! select E'{{1,2},\\{2,3}}'::text[];
> ERROR: malformed array literal: "{{1,2},\{2,3}}"
> select '{{"1 2" x},{3}}'::text[];
> ERROR: malformed array literal: "{{"1 2" x},{3}}"
> Index: src/test/regress/expected/copy2.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/copy2.out,v
> retrieving revision 1.21
> diff -c -c -r1.21 copy2.out
> *** src/test/regress/expected/copy2.out 13 May 2005 06:33:40 -0000 1.21
> --- src/test/regress/expected/copy2.out 16 Jun 2005 01:37:04 -0000
> ***************
> *** 49,55 ****
> -- various COPY options: delimiters, oids, NULL string
> COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
> COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
> ! COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
> -- check results of copy in
> SELECT * FROM x;
> a | b | c | d | e
> --- 49,55 ----
> -- various COPY options: delimiters, oids, NULL string
> COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
> COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
> ! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X';
> -- check results of copy in
> SELECT * FROM x;
> a | b | c | d | e
> ***************
> *** 176,183 ****
> col1 text,
> col2 text
> );
> ! INSERT INTO y VALUES ('Jackson, Sam', '\\h');
> ! INSERT INTO y VALUES ('It is "perfect".','\t');
> INSERT INTO y VALUES ('', NULL);
> COPY y TO stdout WITH CSV;
> "Jackson, Sam",\h
> --- 176,183 ----
> col1 text,
> col2 text
> );
> ! INSERT INTO y VALUES ('Jackson, Sam', E'\\h');
> ! INSERT INTO y VALUES ('It is "perfect".',E'\t');
> INSERT INTO y VALUES ('', NULL);
> COPY y TO stdout WITH CSV;
> "Jackson, Sam",\h
> ***************
> *** 187,193 ****
> Jackson, Sam|\h
> It is "perfect".|
> ''|
> ! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE '\\';
> "Jackson, Sam","\\h"
> "It is \"perfect\"."," "
> "",
> --- 187,193 ----
> Jackson, Sam|\h
> It is "perfect".|
> ''|
> ! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\';
> "Jackson, Sam","\\h"
> "It is \"perfect\"."," "
> "",
> Index: src/test/regress/expected/int8.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/int8.out,v
> retrieving revision 1.9
> diff -c -c -r1.9 int8.out
> *** src/test/regress/expected/int8.out 4 Oct 2004 14:42:47 -0000 1.9
> --- src/test/regress/expected/int8.out 16 Jun 2005 01:37:04 -0000
> ***************
> *** 280,286 ****
> | -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
> (5 rows)
>
> ! SELECT '' AS to_char_16, to_char(q2, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
> to_char_16 | to_char
> ------------+-----------------------------------------------------------
> | text 9999 "text between quote marks" 456
> --- 280,286 ----
> | -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
> (5 rows)
>
> ! SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
> to_char_16 | to_char
> ------------+-----------------------------------------------------------
> | text 9999 "text between quote marks" 456
> Index: src/test/regress/expected/numeric.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/numeric.out,v
> retrieving revision 1.16
> diff -c -c -r1.16 numeric.out
> *** src/test/regress/expected/numeric.out 28 Oct 2004 18:55:07 -0000 1.16
> --- src/test/regress/expected/numeric.out 16 Jun 2005 01:37:05 -0000
> ***************
> *** 1072,1078 ****
> | -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2
> (10 rows)
>
> ! SELECT '' AS to_char_20, to_char(val, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
> to_char_20 | to_char
> ------------+-----------------------------------------------------------
> | text 9999 "text between quote marks" 0
> --- 1072,1078 ----
> | -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2
> (10 rows)
>
> ! SELECT '' AS to_char_20, to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
> to_char_20 | to_char
> ------------+-----------------------------------------------------------
> | text 9999 "text between quote marks" 0
> Index: src/test/regress/expected/rowtypes.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/rowtypes.out,v
> retrieving revision 1.2
> diff -c -c -r1.2 rowtypes.out
> *** src/test/regress/expected/rowtypes.out 9 Jun 2004 19:08:20 -0000 1.2
> --- src/test/regress/expected/rowtypes.out 16 Jun 2005 01:37:05 -0000
> ***************
> *** 25,31 ****
> (Joe,"von Blow") | (Joe,d'Blow)
> (1 row)
>
> ! select '(Joe,"von""Blow")'::fullname, '(Joe,d\\\\Blow)'::fullname;
> fullname | fullname
> -------------------+-----------------
> (Joe,"von""Blow") | (Joe,"d\\Blow")
> --- 25,31 ----
> (Joe,"von Blow") | (Joe,d'Blow)
> (1 row)
>
> ! select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname;
> fullname | fullname
> -------------------+-----------------
> (Joe,"von""Blow") | (Joe,"d\\Blow")
> Index: src/test/regress/expected/timestamp.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamp.out,v
> retrieving revision 1.27
> diff -c -c -r1.27 timestamp.out
> *** src/test/regress/expected/timestamp.out 3 Jun 2004 02:08:06 -0000 1.27
> --- src/test/regress/expected/timestamp.out 16 Jun 2005 01:37:06 -0000
> ***************
> *** 1044,1050 ****
> | 05 05 17 32 01 63121
> (64 rows)
>
> ! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
> FROM TIMESTAMP_TBL;
> to_char_6 | to_char
> -----------+-------------------------------------------------
> --- 1044,1050 ----
> | 05 05 17 32 01 63121
> (64 rows)
>
> ! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
> FROM TIMESTAMP_TBL;
> to_char_6 | to_char
> -----------+-------------------------------------------------
> ***************
> *** 1358,1364 ****
> (1 row)
>
> SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> ! 'HH "\\text between quote marks\\"" YY MI SS');
> to_timestamp_6 | to_timestamp
> ----------------+------------------------------
> | Thu Jan 01 15:54:45 1998 PST
> --- 1358,1364 ----
> (1 row)
>
> SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> ! E'HH "\\text between quote marks\\"" YY MI SS');
> to_timestamp_6 | to_timestamp
> ----------------+------------------------------
> | Thu Jan 01 15:54:45 1998 PST
> Index: src/test/regress/expected/timestamptz.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamptz.out,v
> retrieving revision 1.17
> diff -c -c -r1.17 timestamptz.out
> *** src/test/regress/expected/timestamptz.out 11 Jul 2004 04:57:20 -0000 1.17
> --- src/test/regress/expected/timestamptz.out 16 Jun 2005 01:37:07 -0000
> ***************
> *** 1041,1047 ****
> | 05 05 17 32 01 63121
> (64 rows)
>
> ! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
> FROM TIMESTAMPTZ_TBL;
> to_char_6 | to_char
> -----------+-------------------------------------------------
> --- 1041,1047 ----
> | 05 05 17 32 01 63121
> (64 rows)
>
> ! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
> FROM TIMESTAMPTZ_TBL;
> to_char_6 | to_char
> -----------+-------------------------------------------------
> ***************
> *** 1427,1433 ****
> (1 row)
>
> SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> ! 'HH "\\text between quote marks\\"" YY MI SS');
> to_timestamp_6 | to_timestamp
> ----------------+------------------------------
> | Thu Jan 01 15:54:45 1998 PST
> --- 1427,1433 ----
> (1 row)
>
> SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> ! E'HH "\\text between quote marks\\"" YY MI SS');
> to_timestamp_6 | to_timestamp
> ----------------+------------------------------
> | Thu Jan 01 15:54:45 1998 PST
> Index: src/test/regress/expected/type_sanity.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/type_sanity.out,v
> retrieving revision 1.25
> diff -c -c -r1.25 type_sanity.out
> *** src/test/regress/expected/type_sanity.out 30 Apr 2005 20:31:39 -0000 1.25
> --- src/test/regress/expected/type_sanity.out 16 Jun 2005 01:37:07 -0000
> ***************
> *** 59,65 ****
> -- NOTE: as of 8.0, this check finds smgr and unknown.
> SELECT p1.oid, p1.typname
> FROM pg_type as p1
> ! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
> (SELECT 1 FROM pg_type as p2
> WHERE p2.typname = ('_' || p1.typname)::name AND
> p2.typelem = p1.oid);
> --- 59,65 ----
> -- NOTE: as of 8.0, this check finds smgr and unknown.
> SELECT p1.oid, p1.typname
> FROM pg_type as p1
> ! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
> (SELECT 1 FROM pg_type as p2
> WHERE p2.typname = ('_' || p1.typname)::name AND
> p2.typelem = p1.oid);
> Index: src/test/regress/input/copy.source
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/input/copy.source,v
> retrieving revision 1.12
> diff -c -c -r1.12 copy.source
> *** src/test/regress/input/copy.source 10 May 2005 00:16:07 -0000 1.12
> --- src/test/regress/input/copy.source 16 Jun 2005 01:37:07 -0000
> ***************
> *** 62,71 ****
> test text,
> filler int);
>
> ! insert into copytest values('DOS','abc\r\ndef',1);
> ! insert into copytest values('Unix','abc\ndef',2);
> ! insert into copytest values('Mac','abc\rdef',3);
> ! insert into copytest values('esc\\ape','a\\r\\\r\\\n\\nb',4);
>
> copy copytest to '@abs_builddir@/results/copytest.csv' csv;
>
> --- 62,71 ----
> test text,
> filler int);
>
> ! insert into copytest values('DOS',E'abc\r\ndef',1);
> ! insert into copytest values('Unix',E'abc\ndef',2);
> ! insert into copytest values('Mac',E'abc\rdef',3);
> ! insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
>
> copy copytest to '@abs_builddir@/results/copytest.csv' csv;
>
> ***************
> *** 79,87 ****
>
> --- same test but with an escape char different from quote char
>
> ! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
>
> ! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
>
> select * from copytest except select * from copytest2;
>
> --- 79,87 ----
>
> --- same test but with an escape char different from quote char
>
> ! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
>
> ! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
>
> select * from copytest except select * from copytest2;
>
> Index: src/test/regress/output/copy.source
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/output/copy.source,v
> retrieving revision 1.10
> diff -c -c -r1.10 copy.source
> *** src/test/regress/output/copy.source 10 May 2005 00:16:07 -0000 1.10
> --- src/test/regress/output/copy.source 16 Jun 2005 01:37:08 -0000
> ***************
> *** 37,46 ****
> style text,
> test text,
> filler int);
> ! insert into copytest values('DOS','abc\r\ndef',1);
> ! insert into copytest values('Unix','abc\ndef',2);
> ! insert into copytest values('Mac','abc\rdef',3);
> ! insert into copytest values('esc\\ape','a\\r\\\r\\\n\\nb',4);
> copy copytest to '@abs_builddir@/results/copytest.csv' csv;
> create temp table copytest2 (like copytest);
> copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
> --- 37,46 ----
> style text,
> test text,
> filler int);
> ! insert into copytest values('DOS',E'abc\r\ndef',1);
> ! insert into copytest values('Unix',E'abc\ndef',2);
> ! insert into copytest values('Mac',E'abc\rdef',3);
> ! insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
> copy copytest to '@abs_builddir@/results/copytest.csv' csv;
> create temp table copytest2 (like copytest);
> copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
> ***************
> *** 51,58 ****
>
> truncate copytest2;
> --- same test but with an escape char different from quote char
> ! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
> ! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
> select * from copytest except select * from copytest2;
> style | test | filler
> -------+------+--------
> --- 51,58 ----
>
> truncate copytest2;
> --- same test but with an escape char different from quote char
> ! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
> ! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
> select * from copytest except select * from copytest2;
> style | test | filler
> -------+------+--------
> Index: src/test/regress/sql/arrays.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/arrays.sql,v
> retrieving revision 1.20
> diff -c -c -r1.20 arrays.sql
> *** src/test/regress/sql/arrays.sql 22 Apr 2005 21:58:32 -0000 1.20
> --- src/test/regress/sql/arrays.sql 16 Jun 2005 01:37:08 -0000
> ***************
> *** 204,210 ****
> -- none of the following should be accepted
> select '{{1,{2}},{2,3}}'::text[];
> select '{{},{}}'::text[];
> ! select '{{1,2},\\{2,3}}'::text[];
> select '{{"1 2" x},{3}}'::text[];
> select '{}}'::text[];
> select '{ }}'::text[];
> --- 204,210 ----
> -- none of the following should be accepted
> select '{{1,{2}},{2,3}}'::text[];
> select '{{},{}}'::text[];
> ! select E'{{1,2},\\{2,3}}'::text[];
> select '{{"1 2" x},{3}}'::text[];
> select '{}}'::text[];
> select '{ }}'::text[];
> Index: src/test/regress/sql/copy2.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/copy2.sql,v
> retrieving revision 1.12
> diff -c -c -r1.12 copy2.sql
> *** src/test/regress/sql/copy2.sql 13 May 2005 06:33:40 -0000 1.12
> --- src/test/regress/sql/copy2.sql 16 Jun 2005 01:37:08 -0000
> ***************
> *** 83,89 ****
> 3000;;c;;
> \.
>
> ! COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
> 4000:\X:C:\X:\X
> 4001:1:empty::
> 4002:2:null:\X:\X
> --- 83,89 ----
> 3000;;c;;
> \.
>
> ! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X';
> 4000:\X:C:\X:\X
> 4001:1:empty::
> 4002:2:null:\X:\X
> ***************
> *** 121,133 ****
> col2 text
> );
>
> ! INSERT INTO y VALUES ('Jackson, Sam', '\\h');
> ! INSERT INTO y VALUES ('It is "perfect".','\t');
> INSERT INTO y VALUES ('', NULL);
>
> COPY y TO stdout WITH CSV;
> COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
> ! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE '\\';
>
> --test that we read consecutive LFs properly
>
> --- 121,133 ----
> col2 text
> );
>
> ! INSERT INTO y VALUES ('Jackson, Sam', E'\\h');
> ! INSERT INTO y VALUES ('It is "perfect".',E'\t');
> INSERT INTO y VALUES ('', NULL);
>
> COPY y TO stdout WITH CSV;
> COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
> ! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\';
>
> --test that we read consecutive LFs properly
>
> Index: src/test/regress/sql/int8.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/int8.sql,v
> retrieving revision 1.7
> diff -c -c -r1.7 int8.sql
> *** src/test/regress/sql/int8.sql 4 Oct 2004 14:42:48 -0000 1.7
> --- src/test/regress/sql/int8.sql 16 Jun 2005 01:37:08 -0000
> ***************
> *** 61,65 ****
> SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL;
> SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
> SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL;
> ! SELECT '' AS to_char_16, to_char(q2, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
> SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL;
> --- 61,65 ----
> SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL;
> SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
> SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL;
> ! SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
> SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL;
> Index: src/test/regress/sql/numeric.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/numeric.sql,v
> retrieving revision 1.11
> diff -c -c -r1.11 numeric.sql
> *** src/test/regress/sql/numeric.sql 28 Oct 2004 18:55:08 -0000 1.11
> --- src/test/regress/sql/numeric.sql 16 Jun 2005 01:37:08 -0000
> ***************
> *** 742,748 ****
> SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999') FROM num_data;
> SELECT '' AS to_char_18, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
> SELECT '' AS to_char_19, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
> ! SELECT '' AS to_char_20, to_char(val, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
> SELECT '' AS to_char_21, to_char(val, '999999SG9999999999') FROM num_data;
> SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999') FROM num_data;
>
> --- 742,748 ----
> SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999') FROM num_data;
> SELECT '' AS to_char_18, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
> SELECT '' AS to_char_19, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
> ! SELECT '' AS to_char_20, to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
> SELECT '' AS to_char_21, to_char(val, '999999SG9999999999') FROM num_data;
> SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999') FROM num_data;
>
> Index: src/test/regress/sql/rowtypes.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/rowtypes.sql,v
> retrieving revision 1.2
> diff -c -c -r1.2 rowtypes.sql
> *** src/test/regress/sql/rowtypes.sql 9 Jun 2004 19:08:20 -0000 1.2
> --- src/test/regress/sql/rowtypes.sql 16 Jun 2005 01:37:08 -0000
> ***************
> *** 20,26 ****
>
> select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;
>
> ! select '(Joe,"von""Blow")'::fullname, '(Joe,d\\\\Blow)'::fullname;
>
> select '(Joe,"Blow,Jr")'::fullname;
>
> --- 20,26 ----
>
> select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;
>
> ! select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname;
>
> select '(Joe,"Blow,Jr")'::fullname;
>
> Index: src/test/regress/sql/timestamp.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/timestamp.sql,v
> retrieving revision 1.13
> diff -c -c -r1.13 timestamp.sql
> *** src/test/regress/sql/timestamp.sql 5 Mar 2004 02:41:14 -0000 1.13
> --- src/test/regress/sql/timestamp.sql 16 Jun 2005 01:37:09 -0000
> ***************
> *** 186,192 ****
> SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
> FROM TIMESTAMP_TBL;
>
> ! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
> FROM TIMESTAMP_TBL;
>
> SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
> --- 186,192 ----
> SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
> FROM TIMESTAMP_TBL;
>
> ! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
> FROM TIMESTAMP_TBL;
>
> SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
> ***************
> *** 211,217 ****
> SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
>
> SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> ! 'HH "\\text between quote marks\\"" YY MI SS');
>
> SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');
>
> --- 211,217 ----
> SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
>
> SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> ! E'HH "\\text between quote marks\\"" YY MI SS');
>
> SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');
>
> Index: src/test/regress/sql/timestamptz.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/timestamptz.sql,v
> retrieving revision 1.6
> diff -c -c -r1.6 timestamptz.sql
> *** src/test/regress/sql/timestamptz.sql 5 Mar 2004 02:41:14 -0000 1.6
> --- src/test/regress/sql/timestamptz.sql 16 Jun 2005 01:37:09 -0000
> ***************
> *** 179,185 ****
> SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
> FROM TIMESTAMPTZ_TBL;
>
> ! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
> FROM TIMESTAMPTZ_TBL;
>
> SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
> --- 179,185 ----
> SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
> FROM TIMESTAMPTZ_TBL;
>
> ! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
> FROM TIMESTAMPTZ_TBL;
>
> SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
> ***************
> *** 207,213 ****
> SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
>
> SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> ! 'HH "\\text between quote marks\\"" YY MI SS');
>
> SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');
>
> --- 207,213 ----
> SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
>
> SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> ! E'HH "\\text between quote marks\\"" YY MI SS');
>
> SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');
>
> Index: src/test/regress/sql/type_sanity.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/type_sanity.sql,v
> retrieving revision 1.25
> diff -c -c -r1.25 type_sanity.sql
> *** src/test/regress/sql/type_sanity.sql 30 Apr 2005 20:31:39 -0000 1.25
> --- src/test/regress/sql/type_sanity.sql 16 Jun 2005 01:37:09 -0000
> ***************
> *** 54,60 ****
>
> SELECT p1.oid, p1.typname
> FROM pg_type as p1
> ! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
> (SELECT 1 FROM pg_type as p2
> WHERE p2.typname = ('_' || p1.typname)::name AND
> p2.typelem = p1.oid);
> --- 54,60 ----
>
> SELECT p1.oid, p1.typname
> FROM pg_type as p1
> ! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
> (SELECT 1 FROM pg_type as p2
> WHERE p2.typname = ('_' || p1.typname)::name AND
> p2.typelem = p1.oid);
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

Christopher Kings-Lynne wrote:
> I'm still really iffy about this. I think it will really hurt pgsql due
> to backward compatibility :(
>
> (If I'm understanding how the proposed change works...)

Yep, you probably are. The hurt is backward compatibility, but the gain
is greater portability with other database systems.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> Yep, you probably are. The hurt is backward compatibility, but the gain
> is greater portability with other database systems.

It's just going to break millions of PHP scripts :(

Chris

Christopher Kings-Lynne wrote:
> > Yep, you probably are. The hurt is backward compatibility, but the gain
> > is greater portability with other database systems.
>
> It's just going to break millions of PHP scripts :(

Let me give you a little longer answer. Right now we have this TODO
item:

* Allow backslash handling in quoted strings to be disabled for
portability

The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not
SQL-spec compliant, so allow such handling to be disabled. However,
disabling backslashes could break many third-party applications and
tools.

Now, if we don't address it, we might as well remove the TODO item and
say we are never going to change it, because right now, we have a plan,
and I think the longer we go the harder it will be. And if we don't
change it, it makes it quite hard for people to port applications to
PostgreSQL. Fundamental queries like:

SELECT * FROM files WHERE filename = 'C:\tmp'

do not work. When a query with a single table and single WHERE clause
isn't portable, it seems like a problem. If this was isolated to CREATE
TABLE or something, it wouldn't be a big deal.

One possible idea is to have the warning in 8.1 configurable, so you can
turn it off, and see how well things go in the community. At a minimum,
the warning will flag non-portable queries to help in porting, and folks
can use E'' for non-porable string representations.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Sorry, one more thing. :-(

Let me add that I am not 100% sold on the idea either, but using the
logic I outlined, I don't see how we can continue to do nothing about
this issue, and I am afraid delay will only make an inevitable fix
harder. Maybe we will have to wait 2-3 years before we can make a non-E
string handle backslashes literally.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> > Yep, you probably are. The hurt is backward compatibility, but the gain
> > is greater portability with other database systems.
>
> It's just going to break millions of PHP scripts :(
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> * Allow backslash handling in quoted strings to be disabled for
> portability
>
> The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not
> SQL-spec compliant, so allow such handling to be disabled. However,
> disabling backslashes could break many third-party applications and
> tools.
>
> Now, if we don't address it, we might as well remove the TODO item and
> say we are never going to change it, because right now, we have a plan,
> and I think the longer we go the harder it will be. And if we don't
> change it, it makes it quite hard for people to port applications to
> PostgreSQL. Fundamental queries like:
>
> SELECT * FROM files WHERE filename = 'C:\tmp'
>
> do not work. When a query with a single table and single WHERE clause
> isn't portable, it seems like a problem. If this was isolated to CREATE
> TABLE or something, it wouldn't be a big deal.

Why not compromise? Allow ONLY \' in normal strings? That'd deal with
the majority of compatibility issues. Or, like you say, make it a GUC :(

Chris

On Wed, 2005-06-15 at 23:13 -0400, Bruce Momjian wrote:
> Sorry, one more thing. :-(
>
> Let me add that I am not 100% sold on the idea either, but using the
> logic I outlined, I don't see how we can continue to do nothing about
> this issue, and I am afraid delay will only make an inevitable fix
> harder. Maybe we will have to wait 2-3 years before we can make a non-E
> string handle backslashes literally.

Add the code and the warning, with a GUC for turning it off the \
parsing so '\'' would be an error.

Breaking old code isn't so bad if it's followed up with a campaign from
the advocacy folks about how to do the job properly, along with a
thorough explanation as to why the change was made (compatibility with
other DBs, SQL Spec, etc.).

It probably won't be any worse than when '' was rejected for an integer
0.

> ---------------------------------------------------------------------------
>
> Christopher Kings-Lynne wrote:
> > > Yep, you probably are. The hurt is backward compatibility, but the gain
> > > is greater portability with other database systems.
> >
> > It's just going to break millions of PHP scripts :(
> >
> > Chris
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
>
--

>
> Why not compromise? Allow ONLY \' in normal strings? That'd deal with
> the majority of compatibility issues. Or, like you say, make it a GUC :(
>
> Chris
>
what is wrong on GUC?

Pavel

Rod Taylor <pg(at)rbt(dot)ca> writes:
> It probably won't be any worse than when '' was rejected for an integer
> 0.

That analogy is *SO* far off the mark that I have to object.

Fooling with quoting rules will not simply cause clean failures, which
is what you got from ''-no-longer-accepted-by-atoi. What it will cause
is formerly valid input being silently interpreted as something else.
That's bad enough, but it gets worse: formerly secure client code may
now be vulnerable to SQL-injection attacks, because it doesn't know how
to quote text properly.

What we are talking about here is an extremely significant change with
extremely serious consequences, and imagining that it is not will be
a recipe for disaster.

I also think that pgsql-patches is not the place to be discussing such
things... it needs a whole lot more visibility.

regards, tom lane

Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> what is wrong on GUC?

The idea of a GUC that allows security violations when it's set
differently than the application is expecting fills me with fear.
This is going to look the 7.3 autocommit fiasco look like a day
at the beach.

regards, tom lane

Tom Lane wrote:
> What we are talking about here is an extremely significant change with
> extremely serious consequences, and imagining that it is not will be
> a recipe for disaster.
>
> I also think that pgsql-patches is not the place to be discussing such
> things... it needs a whole lot more visibility.

OK, let me hit general with this. I sent the first to patches so people
could see the code changes in the patch.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Christopher Kings-Lynne wrote:
> > * Allow backslash handling in quoted strings to be disabled for
> > portability
> >
> > The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not
> > SQL-spec compliant, so allow such handling to be disabled. However,
> > disabling backslashes could break many third-party applications and
> > tools.
> >
> > Now, if we don't address it, we might as well remove the TODO item and
> > say we are never going to change it, because right now, we have a plan,
> > and I think the longer we go the harder it will be. And if we don't
> > change it, it makes it quite hard for people to port applications to
> > PostgreSQL. Fundamental queries like:
> >
> > SELECT * FROM files WHERE filename = 'C:\tmp'
> >
> > do not work. When a query with a single table and single WHERE clause
> > isn't portable, it seems like a problem. If this was isolated to CREATE
> > TABLE or something, it wouldn't be a big deal.
>
> Why not compromise? Allow ONLY \' in normal strings? That'd deal with
> the majority of compatibility issues. Or, like you say, make it a GUC :(

The problem with allowing just \' is that we would then not be able to
distinguish a literal \ then ' from a \'. Seems it is all or nothing.

FYI, I added a little to the web page:

Steps:

1. Change all \' to SQL-standard ''.
2. Change use of \ in strings to use E''.
3. Finally, change '' to treat \ literally.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

[switched to -hackers]

Tom Lane wrote:

>Rod Taylor <pg(at)rbt(dot)ca> writes:
>
>
>>It probably won't be any worse than when '' was rejected for an integer
>>0.
>>
>>
>
>That analogy is *SO* far off the mark that I have to object.
>
>Fooling with quoting rules will not simply cause clean failures, which
>is what you got from ''-no-longer-accepted-by-atoi. What it will cause
>is formerly valid input being silently interpreted as something else.
>That's bad enough, but it gets worse: formerly secure client code may
>now be vulnerable to SQL-injection attacks, because it doesn't know how
>to quote text properly.
>
>What we are talking about here is an extremely significant change with
>extremely serious consequences, and imagining that it is not will be
>a recipe for disaster.
>
>
>
>
All true. Conversely, there does need to be a path for us to get to
standard behaviour.

I think we're going to need to provide for switchable behaviour, as ugly
as that might be (looking briefly at scan.l it looks like the simplest
way would be a separate state for being inside standard strings, with
the choice of state being made conditionally in the {xqstart} rule).

We can't just break backwards compatibility overnight like this.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> All true. Conversely, there does need to be a path for us to get to
> standard behaviour.

Yes --- but the important word there is "path". I think we have to do
this in stages over a number of releases, to give people time to
migrate.

Assuming that the end result we want to get to is:
1. Plain '...' literals are per SQL spec: '' for embedded
quotes, backslashes are not special.
2. We add a construct E'...' that handles backslash escapes
the same way '...' literals do today.

I think what would be reasonable for 8.1 is to create the E'...'
construct --- which will not cause any backwards compatibility issues
that I can see --- document it and encourage people to migrate,
and start throwing warnings about use of \' in non-E literals.
(We could have a GUC variable to suppress the warnings; I'm of
the opinion that it would be better not to, though, because the point
is to get people out of that habit sooner rather than later.)

I would be inclined to leave things like that for a couple of release
cycles before we disable backslashes in regular literals. By the time
we do that, we should have at least flushed out the cases where
disabling backslashes will create security holes.

> I think we're going to need to provide for switchable behaviour, as ugly
> as that might be (looking briefly at scan.l it looks like the simplest
> way would be a separate state for being inside standard strings, with
> the choice of state being made conditionally in the {xqstart} rule).

I really really dislike that idea; it is a recipe for creating problems
not solving them.

The hard part in all this is to create apps that will survive the
transition gracefully. I think the only way for that is to implement
a reporting feature that lets the app know whether backslahes are
special in plain literals or not. We already have the mechanism for
that, ie read-only GUC variables with GUC_REPORT enabled (which we use
for integer datetimes, for instance). But I really believe it is
important that this be a *read only* thing not something that can be
flipped around at runtime. Anyway, the reporting variable is another
thing that should appear in 8.1.

regards, tom lane

Bruce Momjian said:

> OK, the current patch warns about two things, \' with one message, and
> any backslash in a non-E string with a different message. The \'
> message can easily be avoided in clients even in 8.0 by using '', but
> for E'', there is no way to prepare an application before upgrading to
> 8.1 because 8.0 doesn't have E''. (We can add E'' in a subrelease, but
> what percentage of users are going to upgrade to that?) This is why I
> think we need to add a GUC to allow the warning to be turned off. To
> be clear, the GUC is to control the warning, not the query behavior.
>
> We could go with the second warning only in 8.2, but that seems too
> confusing --- we should deal with the escape issue in two stages,
> rather than three.
>

So you don't agree with Tom's suggestion to implement E'' a full cycle
before removing backslash processing in standard strings? Or have I
misunderstood again?

cheers

andrew

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > All true. Conversely, there does need to be a path for us to get to
> > standard behaviour.
>
> Yes --- but the important word there is "path". I think we have to do
> this in stages over a number of releases, to give people time to
> migrate.
>
> Assuming that the end result we want to get to is:
> 1. Plain '...' literals are per SQL spec: '' for embedded
> quotes, backslashes are not special.
> 2. We add a construct E'...' that handles backslash escapes
> the same way '...' literals do today.
>
> I think what would be reasonable for 8.1 is to create the E'...'
> construct --- which will not cause any backwards compatibility issues
> that I can see --- document it and encourage people to migrate,
> and start throwing warnings about use of \' in non-E literals.
> (We could have a GUC variable to suppress the warnings; I'm of
> the opinion that it would be better not to, though, because the point
> is to get people out of that habit sooner rather than later.)

OK, the current patch warns about two things, \' with one message, and
any backslash in a non-E string with a different message. The \'
message can easily be avoided in clients even in 8.0 by using '', but
for E'', there is no way to prepare an application before upgrading to
8.1 because 8.0 doesn't have E''. (We can add E'' in a subrelease, but
what percentage of users are going to upgrade to that?) This is why I
think we need to add a GUC to allow the warning to be turned off. To be
clear, the GUC is to control the warning, not the query behavior.

We could go with the second warning only in 8.2, but that seems too
confusing --- we should deal with the escape issue in two stages, rather
than three.

> The hard part in all this is to create apps that will survive the
> transition gracefully. I think the only way for that is to implement
> a reporting feature that lets the app know whether backslahes are
> special in plain literals or not. We already have the mechanism for
> that, ie read-only GUC variables with GUC_REPORT enabled (which we use
> for integer datetimes, for instance). But I really believe it is
> important that this be a *read only* thing not something that can be
> flipped around at runtime. Anyway, the reporting variable is another
> thing that should appear in 8.1.

OK, adding.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Andrew Dunstan wrote:
> Bruce Momjian said:
>
> > OK, the current patch warns about two things, \' with one message, and
> > any backslash in a non-E string with a different message. The \'
> > message can easily be avoided in clients even in 8.0 by using '', but
> > for E'', there is no way to prepare an application before upgrading to
> > 8.1 because 8.0 doesn't have E''. (We can add E'' in a subrelease, but
> > what percentage of users are going to upgrade to that?) This is why I
> > think we need to add a GUC to allow the warning to be turned off. To
> > be clear, the GUC is to control the warning, not the query behavior.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> >
> > We could go with the second warning only in 8.2, but that seems too
> > confusing --- we should deal with the escape issue in two stages,
> > rather than three.
> >
>
> So you don't agree with Tom's suggestion to implement E'' a full cycle
> before removing backslash processing in standard strings? Or have I
> misunderstood again?

I think you misunderstood. There is no scheduled date to change the
actual behavior. The issue is whether we delay one release before
issuing a warning for backslashes in non-E strings.

I have highlighted the sentence where I say we are talking about when to
add the warning, not when to change the behavior.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK, the current patch warns about two things, \' with one message, and
> any backslash in a non-E string with a different message.

Those are two very different things. \' is easy to get around and
there's no very good reason not to send '' instead. But avoiding all
use of \anything is impossible (think \\) so a non-suppressable warning
for that would be quite unacceptable IMHO. I think it's much too early
to be throwing a warning for \anything anyway. 8.2 or so, OK, but not
in this cycle.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, the current patch warns about two things, \' with one message, and
> > any backslash in a non-E string with a different message.
>
> Those are two very different things. \' is easy to get around and
> there's no very good reason not to send '' instead. But avoiding all
> use of \anything is impossible (think \\) so a non-suppressable warning
> for that would be quite unacceptable IMHO. I think it's much too early
> to be throwing a warning for \anything anyway. 8.2 or so, OK, but not
> in this cycle.

I am concerned we are going to generate confusing if we warn about one
use of backslashes in strings but not another. I am thinking we will
just add the infrastructure for E'' in 8.1 (with the warning turned
off), and state we will warn about all backslashes in non-E strings in
8.2, and maybe go for literal strings in 8.3 or 8.4 depending on user
feedback.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Jun 17, 2005, at 12:33 PM, Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>
>> OK, the current patch warns about two things, \' with one message,
>> and
>> any backslash in a non-E string with a different message.
>>
>
> Those are two very different things. \' is easy to get around and
> there's no very good reason not to send '' instead. But avoiding all
> use of \anything is impossible (think \\) so a non-suppressable
> warning
> for that would be quite unacceptable IMHO. I think it's much too
> early
> to be throwing a warning for \anything anyway. 8.2 or so, OK, but not
> in this cycle.

I think giving users a longer period of time to make the necessary
changes to their apps is very useful. If (as I understand) we're
giving them the opportunity to use E'' strings if they want to
continue to use \ for escaping, they can get rid of the warnings now,
by using E'' strings or using '' to escape. Getting people to
migrate something such as this is difficult and will take them quite
a while, I imagine. Giving them a longer time to change their
behavior as well as reinforcing it with a warning is helpful. They
can also easily check if they've got places they've missed in
changing their code, because the warnings will be prominent in their
logs.

Michael Glaesemann
grzm myrealbox com

Note that issuing warnings due to normal DML SQL queries is much more severe
than the typical DDL warnings. Many people have queries strewn throughout the
application so updating them may be a *lot* of work. And for an app issuing
hundreds or thousands of queries per minute (or even second) a warning could
effectively be a showstopper. It could require disabling all warnings in their
config to avoid filling their disk with Postgres logs in minutes.

I would suggest this warning be disable-able with a GUC variable. Otherwise
you're effectively giving no advance warning time to those users.

If postgres keeps advancing at the pace it's advancing now I might suggest
waiting two release cycles instead of just one. Judging by the mailing list it
seems a lot of users aren't able to keep up with the Postgres development team
and are often upgrading two versions at a time.

--
greg

On Jun 17, 2005, at 4:34 PM, Greg Stark wrote:

> And for an app issuing
> hundreds or thousands of queries per minute (or even second) a
> warning could
> effectively be a showstopper. It could require disabling all
> warnings in their
> config to avoid filling their disk with Postgres logs in minutes.

Good point.

> I would suggest this warning be disable-able with a GUC variable.
> Otherwise
> you're effectively giving no advance warning time to those users.

Perhaps NOTICE would be better, at least for the first step? People
might be more comfortable with that, as using backslash escaping
isn't really going to cause problems with this particular version,
but rather for future versions.

> If postgres keeps advancing at the pace it's advancing now I might
> suggest
> waiting two release cycles instead of just one.

How's this for an idea?

Step 1 (8.1) NOTICE level (or some other level, lower than WARNING),
E'' and \' are available
Step 2 (8.2?) WARNING level, E'' and \' are available
Step 3 (8.3? 8.4?) E'' available, plain '' interpreted literally.

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann wrote:
>
> On Jun 17, 2005, at 4:34 PM, Greg Stark wrote:
>
> > And for an app issuing
> > hundreds or thousands of queries per minute (or even second) a
> > warning could
> > effectively be a showstopper. It could require disabling all
> > warnings in their
> > config to avoid filling their disk with Postgres logs in minutes.
>
> Good point.
>
> > I would suggest this warning be disable-able with a GUC variable.
> > Otherwise
> > you're effectively giving no advance warning time to those users.
>
> Perhaps NOTICE would be better, at least for the first step? People
> might be more comfortable with that, as using backslash escaping
> isn't really going to cause problems with this particular version,
> but rather for future versions.

I am thinking changing the level of the message isn't going to help
people much because it still displays and fills up the server logs.

> > If postgres keeps advancing at the pace it's advancing now I might
> > suggest
> > waiting two release cycles instead of just one.
>
> How's this for an idea?
>
> Step 1 (8.1) NOTICE level (or some other level, lower than WARNING),
> E'' and \' are available
> Step 2 (8.2?) WARNING level, E'' and \' are available
> Step 3 (8.3? 8.4?) E'' available, plain '' interpreted literally.

Right now I am thinking we would have the warning available in 8.1, but
not turn it on by default. Perhaps we can tell users to enable the
warning at some time during 8.1 so they are ready for it in 8.2.

If we get a significant must-upgrade 8.0.X release a few months before
8.1, we can tell them to change \' to '' and perhaps have the \' warning
be enabled always in 8.1.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> A summary of my proposal to add a new E'' string for escape and have
> non-E escapes not handle backslashes specially is at:
>
> http://candle.pha.pa.us/cgi-bin/pgescape
>
> Attached is a patch that emits warnings for \ and \', perhaps for 8.1.
> The change to scan.l is the place this is done. The rest of the patch
> is adjustments to prevent our own code from generating warnings. It
> shows a good example of how users would have to change their code.
>
> It passes all regression tests, contrib regression, and initdb runs
> without warning.

Updated patch, with GUC and documentation additions.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Friday 17 June 2005 08:55, Bruce Momjian wrote:
> Michael Glaesemann wrote:
> > On Jun 17, 2005, at 4:34 PM, Greg Stark wrote:
> > > And for an app issuing
> > > hundreds or thousands of queries per minute (or even second) a
> > > warning could
> > > effectively be a showstopper. It could require disabling all
> > > warnings in their
> > > config to avoid filling their disk with Postgres logs in minutes.
> >
> > Good point.
> >
> > > I would suggest this warning be disable-able with a GUC variable.
> > > Otherwise
> > > you're effectively giving no advance warning time to those users.
> >
> > Perhaps NOTICE would be better, at least for the first step? People
> > might be more comfortable with that, as using backslash escaping
> > isn't really going to cause problems with this particular version,
> > but rather for future versions.
>
> I am thinking changing the level of the message isn't going to help
> people much because it still displays and fills up the server logs.
>
> > > If postgres keeps advancing at the pace it's advancing now I might
> > > suggest
> > > waiting two release cycles instead of just one.
> >
> > How's this for an idea?
> >
> > Step 1 (8.1) NOTICE level (or some other level, lower than WARNING),
> > E'' and \' are available
> > Step 2 (8.2?) WARNING level, E'' and \' are available
> > Step 3 (8.3? 8.4?) E'' available, plain '' interpreted literally.
>
> Right now I am thinking we would have the warning available in 8.1, but
> not turn it on by default. Perhaps we can tell users to enable the
> warning at some time during 8.1 so they are ready for it in 8.2.
>

I think it is worth restating in stronger language, the potential overhead of
raising notices or warning in such a large number of queries will be an
upgrading show stopper for some people. (To the extent that for some, the
release where this is a mandatory warning will be as much a show stopper as
the release where the behavior is changed)

IMHO we need at least 1 release with a GUC to control the warning (defaulting
off initial, if people want the next release to default on, thats ok, but is
probably a waste), so that people can turn it on/off in order to debug thier
applications and make them compliant for upgrading to the next version. It
doesnt much matter to me where you put this... 8.0.x, 8.1... it's just a
question of where do you want to create a roadblock to upgrading, because the
release where you force the warning always on your going to have raised the
barrier to entry too high for some people.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
> I think it is worth restating in stronger language, the potential overhead of
> raising notices or warning in such a large number of queries will be an
> upgrading show stopper for some people. (To the extent that for some, the
> release where this is a mandatory warning will be as much a show stopper as
> the release where the behavior is changed)
>
> IMHO we need at least 1 release with a GUC to control the warning (defaulting
> off initial, if people want the next release to default on, thats ok, but is
> probably a waste), so that people can turn it on/off in order to debug thier
> applications and make them compliant for upgrading to the next version. It
> doesnt much matter to me where you put this... 8.0.x, 8.1... it's just a
> question of where do you want to create a roadblock to upgrading, because the
> release where you force the warning always on your going to have raised the
> barrier to entry too high for some people.

The GUC will always be around to turn the warning on or off, until we go
with standard SQL strings, at which point there will be no warnings
generated.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073