This won't work if the table name has uppercase characters
kingmatusevich opened this issue · 15 comments
Have a simple database with a table, named "bills" and run the standard
./mockd-mac.dms postgres -d [dbname] -t bills
and you will get all the data filled in and it works. But if your table is named Bills instead the following will happen:
Command
Run ./mockd-mac.dms postgres -d [dbname] -t Bills
OR
Run ./mockd-mac.dms postgres -d [dbname] -t "Bills"
OR
Run ./mockd-mac.dms postgres -d [dbname] -t bills
What occurred
In all cases, the error will be exactly:
Cannot extracting the column info, error from the database: pq: relation "bills" does not exist
What you expected to occur
I expected the table to get a new row.
Table or Database structure
DDL:
-- DDL generated by Postico 1.5.4
-- Not all database features are supported. Do not use for backup.
-- Table Definition ----------------------------------------------
CREATE TABLE "Bills" (
"CPRLink" integer,
"companyId" integer,
"Comprobante" character varying(30) NOT NULL,
"Agrupador" character varying(200),
"CAENro" bigint,
"CAEVto" character varying(10),
"Codigo" character varying(10),
"Nombre" character varying(255),
"CondIVA" character varying(255),
"CUIT" character varying(30),
"Domicilio" character varying(255),
"Localidad" character varying(255),
"Provincia" character varying(255),
"Fecha" timestamp with time zone,
"FechaVenc" timestamp with time zone,
"SaldoAnterior" character varying(255),
"TotalSIVA" character varying(255),
"IVA" character varying(255),
"DGR" character varying(255),
notified boolean NOT NULL DEFAULT false,
"Total" character varying(255),
"PagoMinimo" character varying(255),
"createdAt" timestamp with time zone DEFAULT 'now'::text::timestamp(3) with time zone,
"updatedAt" timestamp with time zone DEFAULT 'now'::text::timestamp(3) with time zone,
CONSTRAINT bills_pkey PRIMARY KEY ("CPRLink", "companyId")
);
-- Indices -------------------------------------------------------
CREATE UNIQUE INDEX bills_pkey ON "Bills"("CPRLink" int4_ops,"companyId" int4_ops);
MockD Version
v1.3
Database Details
Postgres 10
Platform Details
OS X Mojave
Any other relevant information
Hey thanks for the issue , I think I know where the issue is , I will get that fixed ..
Little busy at the moment will get back to this repository by April and will delivery it ..
As workaround maybe try renaming the table and rename it back after it's mocked..
Thanks again for letting us know
If you have an idea of where the issue is and give me a hint, I might be willing to create a PR
Pull request are welcome :)
Its at this two place.
https://github.com/pivotal-legacy/mock-data/blob/7a6a689ec64879bcc1a08ab5c62df926d43fa07b/db/postgres/sql.go#L51
https://github.com/pivotal-legacy/mock-data/blob/7a6a689ec64879bcc1a08ab5c62df926d43fa07b/db/postgres/sql.go#L68
The query now transalates to
SELECT a.attname,
pg_catalog.Format_type(a.atttypid, a.atttypmod),
COALESCE((SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), '')
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'Bills'::regclass
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
when it should be
SELECT a.attname,
pg_catalog.Format_type(a.atttypid, a.atttypmod),
COALESCE((SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), '')
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '"Bills"'::regclass
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
fixed on v2.0
Hmmm, sure?
I am getting this:
...
time="2020-02-27 19:58:35" level=info msg="Total numbers of tables to mock: 11" file="worker.go:99"
time="2020-02-27 19:58:35" level=debug msg="Removing constraints for table: \"public\".\"MediaType\"" file="constraintsBackup.go:96"
time="2020-02-27 19:58:35" level=debug msg="Extracting constraint info for table: \"public\".\"MediaType\"" file="sql.go:310"
time="2020-02-27 19:58:37" level=debug msg="Table: \"public\".\"MediaType\"" file="worker.go:168"
time="2020-02-27 19:58:37" level=debug msg="Copy Statement: COPY \"public\".\"MediaType\"(MediaTypeId,Name) FROM STDIN WITH CSV DELIMITER '$' QUOTE e'\\x01'" file="worker.go:169"
time="2020-02-27 19:58:37" level=debug msg="Data: 1245568$Cs8LulX40gtQAOrZw60YsbBinH8dZArbbNxtBIFMdokYTqOzG7JK8oZWMqtA27hgASAQS5sRUdBUcz1lT4yPfLneNfqGSqA0qYluJTrwwcM3jp7vwbIh9E8a" file="worker.go:170"
time="2020-02-27 19:58:37" level=fatal msg="Error during committing data: ERROR #42703 column \"mediatypeid\" of relation \"MediaType\" does not exist" file="worker.go:171"
Note the ... \"public\".\"MediaType\"(MediaTypeId,Name) FROM ... and then later column \"mediatypeid\" of relation \"MediaType\".
If I rename that column to be all lowercase, it goes further and does fail with the next column.
Maybe something slipped through?
(I am on Windows, using PostgreSQL 12 as the DB server)
Update: Changing all table and column names to lowercase (really lowercasing the whole .sql of the database) fixed the problem for now.
If I read the code correctly, this is happening here somewhere: https://github.com/pivotal-gss/mock-data/blob/0fa82557d024ea6ba9355b63ca7a6bd1eba9ff56/sql.go#L312-L347
Actually, the first subquery can be reduced to this which gives the same error message for my example:
SELECT * FROM pg_catalog.pg_class c WHERE c.oid = 'MediaType' :: regclass
Thanks for reporting, I forgot about the uppercase columns, will have this fixed maybe tomorrow :)
Nice.
I think this might fix my query:
SELECT * FROM pg_catalog.pg_class c WHERE c.oid = '"MediaType"' :: regclass;
Adding the quotes via https://www.postgresql.org/message-id/6F38FA239E74854F980A3984B70AB0D603AF90%40ex01.stev.local
Oh lol, now I also understand your prev comment: #6 (comment)
Nice, thanks @janpio , looks good :) I will have this merged.
There is another place where this fails, I will add a commit to your pull request in few minutes ...
Nice, thanks.
I am currently failing to set up a Go toolchain in WSL/Ubuntu under Windows to be able to test instantly, so I might have to wait for your release ;)
I have opened a pull request on your branch (janpio#1) if you can accept and merge it, it will show on your pull request.
There was a issue with your fix ( highlighted below ) and now it works
-- Database
postgres=# \c testme
psql (12.1, server 11.3)
You are now connected to database "testme" as user "postgres".
testme=# create table "uppercaseTable" ( "UpperCaseCOlumnInt" int, "UpperCaseColumnString" varchar2);
-- Table
testme=# create table "uppercaseTable" ( "UpperCaseCOlumnInt" int, "UpperCaseColumnString" varchar);
CREATE TABLE
testme=#
testme=# \d "uppercaseTable"
Table "public.uppercaseTable"
Column | Type | Collation | Nullable | Default
-----------------------+-------------------+-----------+----------+---------
UpperCaseCOlumnInt | integer | | |
UpperCaseColumnString | character varying | | |
testme=#
-- Current failure
$ go run *.go t -t uppercaseTable -d testme -u postgres -q
INFO[2020-02-27 19:39:54] Version of the database: PostgreSQL 11.3 on x86_64-apple-darwin, compiled by Apple LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit
INFO[2020-02-27 19:39:54] The flavour of postgres is: postgres
INFO[2020-02-27 19:39:54] The database that will be used by mock program is: testme
INFO[2020-02-27 19:39:54] Starting mocking of table: uppercaseTable
INFO[2020-02-27 19:39:54] Extracting the tables in the database: testme
INFO[2020-02-27 19:39:54] Beginning the mocking process for the tables
INFO[2020-02-27 19:39:54] Extracting the columns and datatype information
Extracting column information from tables 100% [==================================================] (1/1) [0s:0s]
INFO[2020-02-27 19:39:54] Saving all the backup files to the path: /Users/faisal/mock/20200227193954
INFO[2020-02-27 19:39:54] Total numbers of tables to mock: 1
FATA[2020-02-27 19:39:54] Error during committing data: ERROR #42703 column "uppercasecolumnint" of relation "uppercaseTable" does not exist
exit status 1
- with your pull request it fails with
INFO[2020-02-27 19:53:58] Total numbers of tables to mock: 1
FATA[2020-02-27 19:53:58] Encountered error when getting constraints for table "public"."uppercaseTable" from database, err: ERROR #42602 invalid name syntax
exit status 1
- Updated the pull request and now it works
$ go run *.go t -t uppercaseTable -d testme -u postgres -q
INFO[2020-02-27 19:45:56] Version of the database: PostgreSQL 11.3 on x86_64-apple-darwin, compiled by Apple LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit
INFO[2020-02-27 19:45:56] The flavour of postgres is: postgres
INFO[2020-02-27 19:45:56] The database that will be used by mock program is: testme
INFO[2020-02-27 19:45:56] Starting mocking of table: uppercaseTable
INFO[2020-02-27 19:45:56] Extracting the tables in the database: testme
INFO[2020-02-27 19:45:56] Beginning the mocking process for the tables
INFO[2020-02-27 19:45:56] Extracting the columns and datatype information
Extracting column information from tables 100% [==================================================] (1/1) [0s:0s]
INFO[2020-02-27 19:45:56] Saving all the backup files to the path: /Users/faisal/mock/20200227194556
INFO[2020-02-27 19:45:56] Total numbers of tables to mock: 1
Mocking Table "public"."uppercaseTable" 100% [==================================================] (10/10) [0s:0s]
INFO[2020-02-27 19:45:56] Completed loading mock data to 1 tables
INFO[2020-02-27 19:45:56] Found 0 violation of PRIMARY KEYS, attempting to fix them
INFO[2020-02-27 19:45:56] Found 0 violation of UNIQUE KEYS, attempting to fix them
INFO[2020-02-27 19:45:56] Found 0 violation of FOREIGN KEYS, attempting to fix them
INFO[2020-02-27 19:45:56] Attempting to recreating all the constraints
INFO[2020-02-27 19:45:56] Successfully completed running the table sub command
testme=# select * from "uppercaseTable";
UpperCaseCOlumnInt | UpperCaseColumnString
--------------------+-----------------------
-2190675 | o
-4319549 | N
2906083 | y
-4856697 | 6
-7317097 | r
1874238 | v
-6174149 | e
5538931 | M
-4567867 | 2
5144169 | k
(10 rows)
Once you merge I will run the build script for a new release
@janpio a new release is published (https://github.com/pivotal-gss/mock-data/releases/tag/v2.2), thanks again.
Thanks @faisaltheparttimecoder, I can already confirm that v2.2 fixes the problem I reported above.
I am now getting a foreign constraint problem, but that is most probably unrelated and I will investigate.
@janpio , yup that is a known issue ( we do try to fix as much as we can, but we do skip some scenario's) , which we have trouble when loading custom data
https://github.com/pivotal-gss/mock-data#known-issues
If you want control of data being loaded to that foriegn key column, you may want to check on custom command (https://github.com/pivotal-gss/mock-data/wiki/Sub-command:-Custom).
Also you may open a new issue, if you find anything.