search

SchemaPlus / schema_plus_views

Provides support for creating and dropping views in ActiveRecord migrations. Also support for querying views.
MIT License
28 stars 16 forks source link

[PG] Created View in Wrong Schema #6

Closed sirwolfgang closed 9 years ago

sirwolfgang commented 9 years ago

Currently the view's are being created in the schema that matches my username, and not in the public schema where the rest of my rails data is location.

ronen commented 9 years ago

schema_plus_views uses rails' underlying mechanism for specifying table names. So you can specify the view's name as myview or myschema.myview and it will generate the expected SQL (pg):

CREATE VIEW "viewname" AS ...
CREATE VIEW "myschema"."myview" AS ...

If you don't explicitly specify a schema, the choice of schema is left to pg's schema path mechanism.

sirwolfgang commented 9 years ago

I have adjusted my view to be in the correct place, but it still seems to be that there is an issue if the creation of a table and the creation of a view end in different schemas.

ronen commented 9 years ago

it still seems to be that there is an issue if the creation of a table and the creation of a view end in different schemas.

Yes, that does seem odd; but I don't see how schema_plus_views is specifically contributing to that, since it's just issuing the basic SQL and leaving it up to pg to deal with the schemas.

In fact, I'll guess that if you do an ordinary rails migration to create a table you'll find that the new tables are now likewise created by default in your username schema rather than in public. I.e. the issue isn't with schema_plus_views, but rather a change to your database setup since the rails tables were created.

According to http://www.postgresql.org/docs/current/static/ddl-schemas.html, the default schema search_path is "$user",public where

The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored. The second element refers to the public schema that we have seen already.

The first schema in the search path that exists is the default location for creating new objects. That is the reason that by default objects are created in the public schema.

Accordingly I would guess that the rails tables were created when you didn't have a schema matching your username, so the tables were created in the public schema. Then you (or something) defined a schema matching your username, so new items are now created in your username schema.

sirwolfgang commented 9 years ago

It looks like that might be the case. Thanks.