After briefly looking at this, I believe that "$1" as the constraint name is automatically interpreted by postgres as a unique constraint name.
I suspect that was used to avoid collisions when printing out foreign key constraint names (which were not supplied in the original). NOTE: This is not the behavior of postgres 8.0, it just makes up a name from the table/column name in the form of TABLENAME_COLUMNNAME_fkey (such as vineyard_wine_vineyard_id_fkey). I'm guessing that is how Translator should handle it. As an aside, you could use 8.0 but that seems to have a different issue that I'm going to look into. Ben On Mon, 2007-08-13 at 04:37 -0700, Brian O'Connor wrote: > Hi Jess, > > Thanks for your help. Please see my comments below... > > Jess Robinson wrote: > > >Hi Brian, > > > >On Mon, 6 Aug 2007, Brian O'Connor wrote: > > > > > > > >>Hi, > >> > >>I'm trying to use pg_dump output with SQL::Translator and I keep running > >>into > >>the same problem. I create a schema file, such as the attached file > >>wine_pg_dump.sql, using the "pg_dump -sx" command. When I try to run > >>SQL::Translator on this file I get the following error: > >> > >>sqlt -f PostgreSQL -t MySQL /tmp/wine_pg_dump.sql > wine_pg_dump.mysql > >> > >> > >> ERROR (line 198): Invalid field: Was expecting data type but found "$1" > >> FOREIGN KEY (vineyard_id) REFERENCES > >> vineyard(vineyard_id) ON DELETE CASCADE DEFERRABLE > >> INITIALLY DEFERRED;" instead > >> > >> ERROR (line 197): Invalid statement: Was expecting create, or comment on > >> table, or comment on column, or comment on other, or > >> comment, or alter, or grant, or revoke, or drop, or > >> insert, or connect, or update, or set > >>Error: translate: Error with parser 'SQL::Translator::Parser::PostgreSQL': > >>Parse failed. > >> > >>Lines 197 and 198 are: > >> > >>ALTER TABLE ONLY vineyard_wine > >> ADD CONSTRAINT "$1" FOREIGN KEY (vineyard_id) REFERENCES > >>vineyard(vineyard_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; > >> > >> > >> > > > > > >I suspect this is because the grammar for Postgres SQL parsing specifies > >\w for the names of tables, constraints etc, which does not include the > >character $. I can't seem to find documentation on the postgresql.org site > >that tells me which characters are allowed in names, anyone else see it > >anywhere? > > > > > I couldn't find any documentation on this other than: > > http://www.postgresql.org/docs/8.1/interactive/sql-createoperator.html > > but it's not really appropriate. > > >Is that some magical construct, or is it really the name of the > >constraint.. it's very strange looking.. ;) > > > > > It does look strange doesn't it! It seems to be something postgres is > doing internally when the DB is created. For example, the schema file I > use to load a table looks like: > > CREATE TABLE review_wine ( > review_wine_id serial NOT NULL, > primary key(review_wine_id), > review_id int NOT NULL, > FOREIGN KEY (review_id) REFERENCES review (review_id) ON DELETE > cascade INITIALLY DEFERRED, > wine_id int NOT NULL, > FOREIGN KEY (wine_id) REFERENCES wine (wine_id) ON DELETE cascade > INITIALLY DEFERRED, > constraint review_wine_const unique (review_id,wine_id) > ); > > When I load this in postgres and do a \d on the table I see: > > wine=# \d review_wine; > Table "public.review_wine" > Column | Type | > Modifiers > ----------------+---------+------------------------------------------------------------------------- > review_wine_id | integer | not null default > nextval('public.review_wine_review_wine_id_seq'::text) > review_id | integer | not null > wine_id | integer | not null > Indexes: > "review_wine_pkey" primary key, btree (review_wine_id) > "review_wine_const" unique, btree (review_id, wine_id) > Foreign-key constraints: > "$1" FOREIGN KEY (review_id) REFERENCES review(review_id) ON DELETE > CASCADE DEFERRABLE INITIALLY DEFERRED > "$2" FOREIGN KEY (wine_id) REFERENCES wine(wine_id) ON DELETE > CASCADE DEFERRABLE INITIALLY DEFERRED > > And when I dump using pg_dump I get: > > ... > ALTER TABLE ONLY review_wine > ADD CONSTRAINT "$1" FOREIGN KEY (review_id) REFERENCES > review(review_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; > ... > > Postgres seems to add $1 as a constraint name automatically since it > wasn't in my original schema file. Other people must be running into > this since pg_dump is producing this strange output. > > >I'll fix this if someone can point out which characters Pg does allow. > > > > > Has anyone gotten back to you? I'd really like to see this change get > checked in since I'm dead in the water without the ability to parse > pg_dump output. > > Thanks very much for your help!! > > --Brian > > >Jess > > > >PS 0.08 is the latest version, not the 0.08_X series. > > > > > > > >------------------------------------------------------------------------- > >This SF.net email is sponsored by: Splunk Inc. > >Still grepping through log files to find problems? Stop. > >Now Search log events and configuration files using AJAX and a browser. > >Download your FREE copy of Splunk now >> http://get.splunk.com/ > > > > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ ------------------------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ -- sqlfairy-developers mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
