Thread: escape string syntax and pg_dumpall
I just ran pg_dumpall from today's CVS against a 14 April server - I got:
pg_dumpall: query failed: ERROR: type "e" does not exist
pg_dumpall: query was: SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation, spcacl FROM
pg_catalog.pg_tablespaceWHERE spcname NOT LIKE E'pg\_%'
That comes from v1.62 of pg_dumpall.c :
res = executeQuery(conn, "SELECT spcname, " "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
"spclocation, spcacl " "FROM pg_catalog.pg_tablespace "
"WHEREspcname NOT LIKE E'pg\\_%'");
----------------------------
revision 1.62
date: 2005/06/26 03:03:48; author: momjian; state: Exp; lines: +2 -2
Add E'' syntax so eventually normal strings can treat backslashes
literally.
Add GUC variables:
"escape_string_warning" - warn about backslashes in non-E strings "escape_string_syntax" - supports E''
syntax? "standard_compliant_strings" - treats backslashes literally in ''
Update code to use E'' when escapes are used.
----------------------------
My version of the server predates those GUC settings - could the code
be assuming that my server can cope with something it can't?
(Normally I would just upgrade everything - and I bet it will work -
but, I think we still recommend that people use new pg_dumpall to
dump their old server when upgrading..)
Cheers,
Patrick
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> I just ran pg_dumpall from today's CVS against a 14 April server - I got:
> pg_dumpall: query failed: ERROR: type "e" does not exist
> pg_dumpall: query was: SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation, spcacl FROM
pg_catalog.pg_tablespaceWHERE spcname NOT LIKE E'pg\_%'
> That comes from v1.62 of pg_dumpall.c :
> res = executeQuery(conn, "SELECT spcname, "
> "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
> "spclocation, spcacl "
> "FROM pg_catalog.pg_tablespace "
> "WHERE spcname NOT LIKE E'pg\\_%'");
This query needs to be version-dependent, Bruce ...
regards, tom lane
On 2005-07-18, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> That comes from v1.62 of pg_dumpall.c : > >> res = executeQuery(conn, "SELECT spcname, " >> "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, " >> "spclocation, spcacl " >> "FROM pg_catalog.pg_tablespace " >> "WHERE spcname NOT LIKE E'pg\\_%'"); > > This query needs to be version-dependent, Bruce ... It's not even correct as it stands - if you want to match a literal _ using LIKE then you would need E'pg\\\\_%' there. Would NOT LIKE 'pg!_%' ESCAPE '!' be better? -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews <andrew+nonews@supernews.com> writes:
>>> "WHERE spcname NOT LIKE E'pg\\_%'");
> It's not even correct as it stands - if you want to match a literal _
> using LIKE then you would need E'pg\\\\_%' there.
Good point!
> Would NOT LIKE 'pg!_%' ESCAPE '!' be better?
Seems like a fine solution --- I was about to object that ESCAPE
doesn't go back as far as PG 7.0, but neither does pg_tablespace,
so that seems OK.
Or perhaps better, use a regular regex: spcname !~ '^pg_'. The
majority of the comparable cases in psql's describe.c do it this
way, and they seem more readable to me ...
regards, tom lane
On Mon, Jul 18, 2005 at 03:01:31PM -0400, Tom Lane wrote: > Andrew - Supernews <andrew+nonews@supernews.com> writes: > >>> "WHERE spcname NOT LIKE E'pg\\_%'"); > > > It's not even correct as it stands - if you want to match a literal _ > > using LIKE then you would need E'pg\\\\_%' there. > > Good point! > > > Would NOT LIKE 'pg!_%' ESCAPE '!' be better? > > Seems like a fine solution --- I was about to object that ESCAPE > doesn't go back as far as PG 7.0, but neither does pg_tablespace, > so that seems OK. The ESCAPE version which you commited works, thanks! > Or perhaps better, use a regular regex: spcname !~ '^pg_'. The > majority of the comparable cases in psql's describe.c do it this > way, and they seem more readable to me ... Likewise.. then again your fix already works.. Cheers, Patrick