[BUGS] pg_dump - foreign table - server name is not quoted
Hello.
I noticed what I think is a bug in pg_dump.
When dumping foreign tables, the server name is not properly quoted,
which leads to unrestorable dumps.
eg, a foreign table attached to a foreign server named "file-test" is
dumped as:
CREATE FOREIGN TABLE "test-table" (
col1 integer
)
SERVER file-test
OPTIONS (filename '/tmp/test');
Please look at the attached test case and the proposed patch.
Thank you.
--
Ronan Dunklau
create database test_dump_foreign_server;
\connect test_dump_foreign_server ;
create extension file_fdw;
create server "file-test" foreign data wrapper file_fdw;
create foreign table "test-table" (col1 integer) server "file-test" options (filename '/tmp/test');
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ec932e4..1068b6a 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -12303,7 +12303,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
/* retrieve name of foreign server and generic options */
appendPQExpBuffer(query,
- "SELECT fs.srvname, array_to_string(ARRAY("
+ "SELECT quote_ident(fs.srvname) as srvname,"
+"array_to_string(ARRAY("
" SELECT quote_ident(option_name) || ' ' || "
" quote_literal(option_value)"
" FROM pg_options_to_table(ftoptions)), ', ') AS ftoptions "
signature.asc
Description: OpenPGP digital signature
Re: [BUGS] BUG #6308: Problem w. encoding in client
Hello Craig, thanks for your answer. > Restore using PgAdmin III or using a unicode console. > This is a limitation of using a Win1252 client encoding when restoring > data that isn't restricted to Win1252 and cannot be fixed directly. That's new to me. AFAIK pg_restore looks into the dump file and sets the client encoding accordingly (In fact the dump contains the statement SET client_encoding = 'UTF8';). Is this overridden by PGCLIENTENCODING? And if so, should it be? And as we only encounter both problems in Windows7-64, it seems to me they are closely related. Regards Thomas === click:ware Informationstechnik GmbH Thomas Goerner Geschäftsführer fon: 0221 - 13 99 88-0 fax: 0221 - 13 99 88-79 Kamekestraße 19 50672 Köln t...@clickware.de www.clickware.de === Kennen Sie schon unser GasDataWarehouse - Die kostengünstige Lösung für den Austausch von Gasmessdaten? www.gasdatawarehouse.de -Ursprüngliche Nachricht- Von: Craig Ringer [mailto:ring...@ringerc.id.au] Gesendet: Sonntag, 27. November 2011 10:00 An: Thomas Goerner Cc: pgsql-bugs@postgresql.org Betreff: Re: [BUGS] BUG #6308: Problem w. encoding in client On 11/25/2011 08:21 PM, Thomas Goerner wrote: > > The following bug has been logged online: > > Bug reference: 6308 > Logged by: Thomas Goerner > Email address: t...@clickware.de > PostgreSQL version: 9.1.1 > Operating system: Windows 7 64-bit > Description:Problem w. encoding in client > Details: > > Hi, we have a problem regarding encoding with postgres 9.1.1 and Win7 > 64-bit > > Database encoding: UTF-8 > active codepage in Windows console: 1252 > PGCLIENTENCODING: Win1252 > Console font: Lucida console > > In the above configuration, the following problems occur: > > 1) > Text output from the client applications, e.g. the welcome-prompt of > psql or the help page from pg_dump --help is not displayed correctly > (especially german Umlauts and characters like "«" ). That shouldn't be happening. As a workaround, try using a unicode console (see the "chcp" command) and a unicode client encoding. The issue with mismatched chars sounds like a real bug that wants looking into. > When we restore a dump in custom format and then try to re-dump the > database, we get error messages like Zeichen 0xe28093 in Kodierung > »UTF8« hat keine Entsprechung in »Win1252« (character 0xe28093 in > UTF-8 cannot be translated to Win1252) Restore using PgAdmin III or using a unicode console. This is a limitation of using a Win1252 client encoding when restoring data that isn't restricted to Win1252 and cannot be fixed directly. If you don't mind possibly corrupted error and NOTICE messages you can just set a unicode client_encoding for your restore. -- Craig Ringer
Re: [BUGS] pg_dump - foreign table - server name is not quoted
Ronan Dunklau writes: > When dumping foreign tables, the server name is not properly quoted, > which leads to unrestorable dumps. Clearly a bug. > Please look at the attached test case and the proposed patch. I think this patch is not in keeping with typical coding practices in pg_dump. Usually we prefer to retrieve names from the server as-is, then apply fmtId() when printing them --- this is more flexible than having possibly-pre-quoted names in pg_dump's internal state. Will fix it the latter way. Thanks for the report! regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_dump - foreign table - server name is not quoted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks for the response. How will this bug be handled with regards to releases ? If the fix is going to take a while to be released, we could rename our servers to valid names but we would prefer not to. Regards, - -- Ronan Dunklau -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.18 (GNU/Linux) iQEcBAEBAgAGBQJO08ABAAoJECTYLCgFy323h48H/2J88BTXgkKGjU6XQE+xkPiA Ma9ekqsuPSYLzOv/j2lubK0P0M8CYdxgzAGQsMvhj7QZZFNhBsjBKI1fhIpxNwWw B6/T9FRPTBS7lo2iurxK2TAcdoP2Vw76q/Nwja2DQLrwUXTztfwbP+DTTB/l0CmW XF7BF5kS8wTnvqkqmQJaFPNefsoqh9OpyTroX0g+ul8cn9ZO3HwHtYDk1spQp3HQ qPNAwiJaKMwnsAGqjH0qSiqtoHrSU7RxbDcKaUslI56Ba82JFD+FUErPEtuQA/q4 uX+bFgZz1K2+96s6+0ZdSccBDLCB/1o/zXAQrmJmTLUNbs9O+iSuG1v6OUMY2p0= =QCSp -END PGP SIGNATURE- -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] objects tied to missing extension
I stumbled upon this situation when playing with extension upgrades. The problem I was having was that auto-generated datatypes were also being added to the extension and it wasn't obvious this was happening. I know this has been changed in 9.1 stable and in master. What happened was that I was able to delete the extension in the upgrade script, either by dropping a table with cascade or by outright dropping the extension. It's debatable whether or not that should be allowed, but what happens afterward is what I am more concerned about. In the same upgrade script, after dropping the extension, I created another table and now it is tied to an extension that no longer exists. The output of my psql session is below and the extension files are attached. postgres=# select version(); version --- PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.0 20110603 (Red Hat 4.6.0-10), 64-bit (1 row) postgres=# create database bug_example; CREATE DATABASE postgres=# \c bug_example You are now connected to database "bug_example" as user "postgres". bug_example=# create extension bug_example with version '1.0'; CREATE EXTENSION bug_example=# \d List of relations Schema | Name | Type | Owner ++--+-- public | table_a| table| postgres public | table_a_id_seq | sequence | postgres (2 rows) bug_example=# alter extension bug_example update to '2.0'; ALTER EXTENSION bug_example=# \d List of relations Schema | Name | Type | Owner ++--+-- public | table_b| table| postgres public | table_b_id_seq | sequence | postgres (2 rows) bug_example=# drop extension bug_example; ERROR: extension "bug_example" does not exist bug_example=# drop table table_b; ERROR: cache lookup failed for extension 17439 bug_example=# drop table table_b cascade; ERROR: cache lookup failed for extension 17439 bug_example.control Description: Binary data create table table_a ( id serial, primary key (id) ); create table table_b ( id serial, primary key (id) ); alter extension bug_example drop table table_a; alter extension bug_example drop sequence table_a_id_seq; drop table table_a cascade; --drop extension bug_example; create table table_b ( id serial, primary key (id) ); -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_dump - foreign table - server name is not quoted
Ronan Dunklau writes: > How will this bug be handled with regards to releases ? It'll be fixed in next week's releases. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] objects tied to missing extension
Phil Sorber writes: > I stumbled upon this situation when playing with extension upgrades. > The problem I was having was that auto-generated datatypes were also > being added to the extension and it wasn't obvious this was happening. > I know this has been changed in 9.1 stable and in master. I couldn't replicate any funnies with the given test case in 9.1 branch tip. (It might not work nicely if you change the upgrade script to do DROP EXTENSION, but I cannot imagine any sane reason to do that ... and we are assuming that extension script authors are responsible adults, since the scripts are generally executed with superuser permissions.) After poking in the code for awhile, I believe that the reason you had a problem when the table's rowtype is an extension member is that the deletion proceeds like this: 1. We start at table table_a, which is a legitimate drop request. 2. We recurse to its internal dependency, the rowtype table_a, and decide that that's legitimate to drop too. 3. Recursing again, findDependentObjects finds the extension, and since it's not at the outermost recursion level, decides that it ought to proceed with deleting the extension. The reason for this behavior is that we want to support deletion of dependent extensions --- that is, if some object in extension A depends on some object in extension B, and extension B is dropped with CASCADE, then extension A ought to go away too. So the decision at step 3 is not wrong for such cases. It might be that there's some corner case where we need to tighten the rules, but AFAICS it's safe as long as every directly-deletable object that's within an extension has a direct dependency on the extension. (That's enough to ensure that a DROP on the object will encounter the extension at outermost recursion level.) So the problem seems to be only due to your ALTER EXTENSION DROP command having left an incomplete set of extension dependencies behind. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] objects tied to missing extension
On Mon, Nov 28, 2011 at 3:12 PM, Tom Lane wrote: > Phil Sorber writes: >> I stumbled upon this situation when playing with extension upgrades. >> The problem I was having was that auto-generated datatypes were also >> being added to the extension and it wasn't obvious this was happening. >> I know this has been changed in 9.1 stable and in master. > > I couldn't replicate any funnies with the given test case in 9.1 branch > tip. (It might not work nicely if you change the upgrade script to do > DROP EXTENSION, but I cannot imagine any sane reason to do that ... and > we are assuming that extension script authors are responsible adults, > since the scripts are generally executed with superuser permissions.) > > After poking in the code for awhile, I believe that the reason you had a > problem when the table's rowtype is an extension member is that the > deletion proceeds like this: > > 1. We start at table table_a, which is a legitimate drop request. > 2. We recurse to its internal dependency, the rowtype table_a, and > decide that that's legitimate to drop too. > 3. Recursing again, findDependentObjects finds the extension, and since > it's not at the outermost recursion level, decides that it ought to > proceed with deleting the extension. > > The reason for this behavior is that we want to support deletion of > dependent extensions --- that is, if some object in extension A depends > on some object in extension B, and extension B is dropped with CASCADE, > then extension A ought to go away too. So the decision at step 3 is not > wrong for such cases. It might be that there's some corner case where > we need to tighten the rules, but AFAICS it's safe as long as every > directly-deletable object that's within an extension has a direct > dependency on the extension. (That's enough to ensure that a DROP on > the object will encounter the extension at outermost recursion level.) > So the problem seems to be only due to your ALTER EXTENSION DROP command > having left an incomplete set of extension dependencies behind. > > regards, tom lane > I compiled 9.1 stable head and tested it out. You are correct my example no longer works there because of the patch that stopped the auto-generated types from becoming dependencies of the extension. In fact, the cascade no longer works even if I don't remove the table or sequence from the extension. And I agree with your assertions here that allowing the extension authors to be adults is fine. However, I don't think leaving the database in a bad state is acceptable. I am still able to reproduce the "ERROR: cache lookup failed for extension x" if I use an explicit 'drop extension'. I am unsure how I can reverse the state it is now in. I assume there is some system catalog I can edit that will fix it? I think anything created after the extension is dropped should be not linked to it, or not created or maybe have the whole thing fail altogether. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] objects tied to missing extension
Phil Sorber writes: > I am still able to reproduce the "ERROR: cache lookup failed for extension > x" if I use an explicit 'drop extension'. I am unsure how I can > reverse the state it is now in. I assume there is some system catalog > I can edit that will fix it? I think you have some dangling entries in pg_depend --- manually deleting any rows that reference the missing pg_extension OID should fix it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] objects tied to missing extension
On Mon, Nov 28, 2011 at 4:10 PM, Tom Lane wrote: > Phil Sorber writes: >> I am still able to reproduce the "ERROR: cache lookup failed for extension >> x" if I use an explicit 'drop extension'. I am unsure how I can >> reverse the state it is now in. I assume there is some system catalog >> I can edit that will fix it? > > I think you have some dangling entries in pg_depend --- manually > deleting any rows that reference the missing pg_extension OID should fix > it. > > regards, tom lane > That fixes it. At least I have a workaround for current installs. Thanks. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6307: intarray extention gin index does not work with Hot standby
On Mon, Nov 28, 2011 at 6:02 PM, Simon Riggs wrote: > On Fri, Nov 25, 2011 at 6:33 PM, Tom Lane wrote: > > Maxim Boguk writes: > >> I know GIST on intarray[] do not have that problem. > >> Very likely the problem is limited to intarray[] GIN indexes only > >> (but I going to test some other not-well known GIN indexes tomorrow). > > > >> Broken FTS indexes on Hot Standby should be known years before. > > > > You might think that, but you'd be wrong :-(. > > Yes, that did sound ominous. > > > ginRedoUpdateMetapage > > is failing to restore the contents of the pending-list correctly, > > which means this is broken for all types of GIN indexes. Will fix. > > Great detective work Tom as ever, much appreciated. > > Thank you very much. Is that fix will be included to the next minor versions releases? (especially into 9.1.2)? -- Maxim Boguk Senior Postgresql DBA.
Re: [BUGS] objects tied to missing extension
Phil Sorber writes: > I compiled 9.1 stable head and tested it out. You are correct my > example no longer works there because of the patch that stopped the > auto-generated types from becoming dependencies of the extension. In > fact, the cascade no longer works even if I don't remove the table or > sequence from the extension. And I agree with your assertions here > that allowing the extension authors to be adults is fine. However, I > don't think leaving the database in a bad state is acceptable. My initial reaction was that this wouldn't be worth the trouble, but on reflection it occurred to me that the case can still be produced in HEAD without anything as obviously bogus as dropping the script's own extension. For instance, suppose the upgrade script in your example attempts to add the rowtype to the extension explicitly: alter extension bug_example add type table_a; alter extension bug_example drop table table_a; alter extension bug_example drop sequence table_a_id_seq; drop table table_a cascade; create table table_b ( id serial, primary key (id) ); With this script, the failure naturally will occur just the same way in HEAD, since the extension will get dropped due to CASCADE and then the CREATE TABLE step will add a pg_depend entry referencing the already-dropped extension. This is still a bit improbable, but it demonstrates that non-obvious mistakes in dependency management could lead to the situation happening, so it convinces me that it's worth expending a little bit of code to prevent it. Fortunately it's quite trivial to do: we can just make RemoveExtensionById refuse to delete the extension if its OID is equal to the CurrentExtensionObject state variable. Committed at http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=871dd024a6adf7766702b1cdacfb02bd8002d2bb regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [ADMIN] Repeatable crash in pg_dump (with -d2 info)
David Schnur writes: > I probably can't get a stack trace, but I was able to reproduce it with > just that function. Without the function, pg_dump works fine. I can DROP > the function, pg_dump works, then add it back again and pg_dump crashes. Hmph. I still can't reproduce this here, which seems to mean one of three things: 1. We fixed this since 8.3.15 (still doesn't seem likely). 2. It's specific to Windows. 3. It's specific to the pg_dump build you are using. I wonder whether anyone else can get this to happen on the strength of David's description? > Here are my steps: > initdb -A md5 --no-locale -E UTF8 -U testuser -D > "C:\Users\David\Desktop\testdb" -W > pg_ctl -U testuser -D "C:\Users\David\Desktop\testdb" start > CREATE DATABASE testdb OWNER testuser; > CREATE OR REPLACE FUNCTION datastore_unpack( > data_times TIMESTAMP WITH TIME ZONE[], > data_values DOUBLE PRECISION[], > OUT data_time TIMESTAMP WITH TIME ZONE, > OUT data_value DOUBLE PRECISION > ) RETURNS SETOF RECORD AS $$ > SELECT $1[rowx] AS data_time, $2[rowx] AS data_value > FROM generate_series(1, array_upper($1, 1)) AS rowx; > $$ LANGUAGE SQL STABLE; > pg_dump -v -F c -x -O -f "C:\Users\David\Desktop\dumptest" -U testuser > testdb > Here's the output from pg_dump: > pg_dump: reading schemas > pg_dump: reading user-defined functions > pg_dump: reading user-defined types > pg_dump: reading procedural languages > pg_dump: reading user-defined aggregate functions > pg_dump: reading user-defined operators > pg_dump: reading user-defined operator classes > pg_dump: reading user-defined text search parsers > pg_dump: reading user-defined text search templates > pg_dump: reading user-defined text search dictionaries > pg_dump: reading user-defined text search configurations > pg_dump: reading user-defined operator families > pg_dump: reading user-defined conversions > pg_dump: reading user-defined tables > pg_dump: reading table inheritance information > pg_dump: reading rewrite rules > pg_dump: reading type casts > pg_dump: finding inheritance relationships > pg_dump: reading column info for interesting tables > pg_dump: flagging inherited columns in subtables > pg_dump: reading indexes > pg_dump: reading constraints > pg_dump: reading triggers > pg_dump: reading dependency data > pg_dump: saving encoding = UTF8 > pg_dump: saving standard_conforming_strings = off > pg_dump: saving database definition > And here's the output from the postmaster: > LOG: statement: SET DATESTYLE = ISO > LOG: statement: SET extra_float_digits TO 2 > LOG: statement: SET synchronize_seqscans TO off > LOG: statement: BEGIN > LOG: statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE > LOG: statement: SET search_path = pg_catalog > LOG: statement: SELECT tableoid, oid, nspname, (SELECT rolname FROM > pg_catalog.pg_roles WHERE oid = nspowner) as rolname, nspacl FROM > pg_namespace > LOG: statement: SELECT tableoid, oid, proname, prolang, pronargs, > proargtypes, prorettype, proacl, pronamespace,(SELECT rolname FROM > pg_catalog.pg_roles WHERE oid = proowner) as rolname FROM pg_proc WHERE NOT > proisagg AND pronamespace != (select oid from pg_namespace where nspname = > 'pg_catalog') > LOG: statement: SELECT tableoid, oid, typname, typnamespace, (SELECT > rolname FROM pg_catalog.pg_roles WHERE oid = typowner) as rolname, > typinput::oid as typinput, typoutput::oid as typoutput, typelem, typrelid, > CASE WHEN typrelid = 0 THEN ' '::"char" ELSE (SELECT relkind FROM pg_class > WHERE oid = typrelid) END as typrelkind, typtype, typisdefined, typname[0] > = '_' AND typelem != 0 AND (SELECT typarray FROM pg_type te WHERE oid = > pg_type.typelem) = oid AS isarray FROM pg_type > LOG: statement: SELECT tableoid, oid, lanname, lanpltrusted, > lanplcallfoid, lanvalidator, lanacl, (SELECT rolname FROM > pg_catalog.pg_roles WHERE oid = lanowner) as lanowner FROM pg_language > WHERE lanispl ORDER BY oid > LOG: statement: SELECT tableoid, oid, proname as aggname, pronamespace as > aggnamespace, pronargs, proargtypes, (SELECT rolname FROM > pg_catalog.pg_roles WHERE oid = proowner) as rolname, proacl as aggacl FROM > pg_proc WHERE proisagg AND pronamespace != (select oid from pg_namespace > where nspname = 'pg_catalog') > LOG: statement: SELECT tableoid, oid, oprname, oprnamespace, (SELECT > rolname FROM pg_catalog.pg_roles WHERE oid = oprowner) as rolname, > oprcode::oid as oprcode FROM pg_operator > LOG: statement: SELECT tableoid, oid, opcname, opcnamespace, (SELECT > rolname FROM pg_catalog.pg_roles WHERE oid = opcowner) as rolname FROM > pg_opclass > LOG: statement: SELECT tableoid, oid, prsname, prsnamespace, > prsstart::oid, prstoken::oid, prsend::oid, prsheadline::oid, > prslextype::oid FROM pg_ts_parser > LOG: statement: SELECT tableoid, oid, tmplname, tmplnamespace, > tmplinit::oid, tmpllexize::oid FROM pg_ts_template > LOG: statement: SELECT tableoid, oid, dictname, dictnamespace, (SELECT > rolname FROM pg_catalog.pg_
Re: [BUGS] BUG #6307: intarray extention gin index does not work with Hot standby
Maxim Boguk writes: > Is that fix will be included to the next minor versions releases? Yes, it's in already: http://git.postgresql.org/gitweb/?p=postgresql.git regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6308: Problem w. encoding in client
On 11/28/2011 08:26 PM, Thomas Goerner wrote: Hello Craig, thanks for your answer. > Restore using PgAdmin III or using a unicode console. > This is a limitation of using a Win1252 client encoding when restoring > data that isn't restricted to Win1252 and cannot be fixed directly. That's new to me. AFAIK pg_restore looks into the dump file and sets the client encoding accordingly (In fact the dump contains the statement SET client_encoding = 'UTF8';). Is this overridden by PGCLIENTENCODING? And if so, should it be? Nope, pg_restore should be using UTF8 as the client encoding in that case. If there are any errors or notices it won't be able to emit them correctly on the terminal though, as win1252 can't represent everything in UTF8 (and IIRC pg_restore doesn't recode from client_encoding to terminal encoding anyway). If the restore its self is failing then I agree that something's not working properly, because you should be able to use a client_encoding different to your terminal encoding. I wonder if recent changes intended to get psql to pick up the terminal encoding automatically have had the unintended side-effect of overriding pg_restore's attempt to set the client_encoding? I'm rather surprised you only see this on x64. You're using the same Windows and Pg version for both x64 and x64 but only the x64 test fails? -- Craig Ringer
