postgres: Foreign Keys with same name cause compilation errors

[eduacostam]

After executing the sql script defined in https://github.com/vattle/sqlboiler#features--examples into an empty database, and executing sqlboiler postgres, running go test ./models/ fails with the following message:

models\jets.go:345: (*Jet).PilotG redeclared in this block
        previous declaration at models\jets.go:326
models\jets.go:350: (*Jet).Pilot redeclared in this block
        previous declaration at models\jets.go:331
models\jets.go:364: (*Jet).PilotG redeclared in this block
        previous declaration at models\jets.go:345
models\jets.go:369: (*Jet).Pilot redeclared in this block
        previous declaration at models\jets.go:350
models\jets.go:383: (*Jet).PilotG redeclared in this block
        previous declaration at models\jets.go:364
models\jets.go:388: (*Jet).Pilot redeclared in this block
        previous declaration at models\jets.go:369
models\jets.go:477: jetL.LoadPilot redeclared in this block
        previous declaration at models\jets.go:403
models\jets.go:551: jetL.LoadPilot redeclared in this block
        previous declaration at models\jets.go:477
models\jets.go:625: jetL.LoadPilot redeclared in this block
        previous declaration at models\jets.go:551
models\jets.go:748: (*Jet).SetPilot redeclared in this block
        previous declaration at models\jets.go:700
models\jets.go:748: too many errors
FAIL   /test/models [build failed]

I can also see, in almost all structs, that there is lots of duplicated entries. For example, the jetR struct is defined as followed, which is clearly wrong.

// jetR is where relationships are stored.
type jetR struct {
	Pilot *Pilot
	Pilot *Pilot
	Pilot *Pilot
	Pilot *Pilot
}

Another clear example is the pilotR struct, which not only has duplicated attributes, but it also misses the Jets JetSlice attribute.

// pilotR is where relationships are stored.
type pilotR struct {
	Jets      JetSlice
	Jets      JetSlice
	Jets      JetSlice
	Jets      JetSlice
	Languages LanguageSlice
	Languages LanguageSlice
	Languages LanguageSlice
	Languages LanguageSlice
}

My Go version: go version go1.7.4 windows/amd64 running postgres 9.5 on an Ubuntu VM.

Thanks!

[nullbio]

Hmm, this only appears to be affecting Postgres. We're looking into it. Thanks for letting us know.

[eduacostam]

Sure thing!

[aarondl]

The crux of the issue is that mysql uses names to uniquely identify constraint names and postgres doesn't care. There's a duplicate key name in there (pilots_fkey) and so our postgres driver query gets stupefied. I do have a fix that's almost ready, but won't be able to get it into dev until tonight. Thanks for your patience and we've added this schema to our integration tests to ensure nothing like this happens in the future.

[eduacostam]

That's great! do you want me to send a PR to fix the schema in the README or you got that fix in dev as well?

[aarondl]

It'll be in my commits :) Thanks for offering.

[jgrossophoff]

I still seem to have this issue for tables that are referenced by multiple foreign keys with the same name.
Is it necessary to have unique foreign keys?

[aarondl]

@jgrossophoff This should have fixed that sort of thing. It's possible there's still some other edges that the query isn't saving us from. A few questions:

1. Are you running sqlboiler 2.1.5 where this was fixed? It was quite a recent fix after all.
2. Is there any naming confilcts other than keys in your schema? Ssharing your schema or creating a subset of it that reproduces the error will allow me to fix this very quickly.
3. I'm assuming, but just to be clear: Postgres, not MySQL right?

[jgrossophoff]

@aarondl

1. Yes. I also built the dev branch but got the same result.
2. I can share the schema.
   This is the referenced table.

                                        Table "public.measurement_types"
       Column        |           Type           |                           Modifiers                            
---------------------+--------------------------+----------------------------------------------------------------
 id                  | integer                  | not null default nextval('measurement_types_id_seq'::regclass)
 num                 | integer                  | not null
 description         | text                     | not null
 deduction           | text                     | not null
 sum_factor          | numeric(16,8)            | default 1
 project_leader_only | boolean                  | default false
 created             | timestamp with time zone | default now()
 deleted             | timestamp with time zone | 
Indexes:
    "measurement_types_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "material_mass_measurement_types" CONSTRAINT "material_mass_measurement_types_measurement_type_id_fkey" FOREIGN KEY (measurement_type_id) REFERENCES measurement_types(id)
    TABLE "service_add_mass_measurement_types" CONSTRAINT "service_add_mass_measurement_types_measurement_type_id_fkey" FOREIGN KEY (measurement_type_id) REFERENCES measurement_types(id)
    TABLE "service_mass_measurement_types" CONSTRAINT "service_measurement_types_measurement_type_id_fkey" FOREIGN KEY (measurement_type_id) REFERENCES measurement_types(id)

The three referencing tables are many-to-many join tables. This is the schema of one of them:

Table "public.service_mass_measurement_types"
       Column        |  Type   | Modifiers 
---------------------+---------+-----------
 service_id          | integer | not null
 measurement_type_id | integer | not null
Indexes:
    "service_measurement_types_pkey" PRIMARY KEY, btree (service_id, measurement_type_id)
    "service_measurement_types_measurement_type_id_idx" btree (measurement_type_id)
    "service_measurement_types_service_id_idx" btree (service_id)
Foreign-key constraints:
    "service_measurement_types_measurement_type_id_fkey" FOREIGN KEY (measurement_type_id) REFERENCES measurement_types(id)
    "service_measurement_types_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)

And this is the services table:

                                   Table "public.services"
          Column          |   Type   |                       Modifiers                       
--------------------------+----------+-------------------------------------------------------
 id                       | integer  | not null default nextval('services_id_seq'::regclass)
 revision_id              | integer  | not null
 service_category_id      | integer  | not null
 description              | text     | not null
 mass_unit                | text     | not null
 mass_unit_price          | bigint   | default 0
 add_mass_label           | text     | default ''::text
 add_mass_measurement     | text     | default ''::text
Indexes:
    "services_pkey" PRIMARY KEY, btree (id)
    "services_service_category_id_idx" btree (service_category_id)
Foreign-key constraints:
    "services_revision_id_fkey" FOREIGN KEY (revision_id) REFERENCES revisions(id)
    "services_service_category_id_fkey" FOREIGN KEY (service_category_id) REFERENCES service_categories(id)
Referenced by:
    TABLE "material_mass_measurement_types" CONSTRAINT "material_mass_measurement_types_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)
    TABLE "report_services" CONSTRAINT "report_services_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)
    TABLE "service_add_mass_measurement_types" CONSTRAINT "service_add_mass_measurement_types_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)
    TABLE "service_mass_measurement_types" CONSTRAINT "service_measurement_types_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)

(Some columns removed)
A snippet from the generated code:

type measurementTypeR struct {                                                                                                           
          Services ServiceSlice                                                                                                            
          Services ServiceSlice                                                                                                            
          Services ServiceSlice                                                                                                            
 }

And all Service* methods on the MeasurementType type are declared 3 times.

3. Yes, I'm running Postgres 9.5.5

Thank you very much in advance for all the effort!

[aarondl]

Closing this in favor of #92, not same issue.