4 ms·
> Due to the typical approach of pluralizing for tables and using singular names for columns, this kind of conflict is very unlikely. In PostgreSQL, where a ta
by saurik 13y ago
> Due to the typical approach of pluralizing for tables and using singular names for columns, this kind of conflict is very unlikely.
In PostgreSQL, where a table is really a type definition (you can effectively think of a table as a struct), using plural names for tables is against the recommended practice; this will be much more common.
Though, in a way, the conflict is still rare for a different but highly related reason, an implicit column which I sadly don't know enough about ActiveRecord (having never used it) to know if this bug could be used on.
select product from product;
This will return records, each with one field whose value is a record of type product that contains the value of all fields in the row of the table being scanned; effectively you are returning the row as a field.
(And now you can see an example where thinking of the table type name as "plural" leads to weird semantics: PostgreSQL prefers thinking of them as classes of object from which you are pulling a subset of instances.)
You can also compare this record if you can get a tuple into the ActiveRecord query; alternatively, if ActiveRecord doesn't specify the type of its parameter values (something a lot of drivers I've seen don't automatically do for string input), you could also pass an unknown-typed text value that happens to be comma-separated and surrounded in parenthesis and it will be coerced to a compatible record type.
Again, I don't know if the issue with ActiveRecord requires the column to be defined in the model or something (the description was really brief and didn't explain the underlying bug), but maybe someone else knows.
- rcahearn 13y agoPlural table names seem to be all over the PostgreSQL docs. Who recommends against using plural names for tables?
- saurik 13y agoI have seen this recommendation many times in PostgreSQL circles (well, I've also seen it sometimes in general SQL circles, but it is much rarer) due to PostgreSQL being an OODBMS: "tables" are just "types" in PostgreSQL, which with plural table names leads to awkward situations where you might be casting a value--a value that represents a single user--to the type "users". Or you write a function that returns a single user: it is confusing to have the return type of that function be "users". The "setof" type operator also makes a little more sense if the type argument is singular.
- icambron 13y agoIn the absence of instructions telling it to do something different, AR relies on the convention that instances of a class Foo are stored in a table called "foos", regardless of what database system you're using. That cuts down on the amount of configuration you have to do, and you wouldn't normally override it unless you were applying AR to a preexisting schema. Edit: removing speculation
- saurik 13y agoOK, and I then imagine the idea is that the kind of person using ActiveRecord doesn't "design their database with the idea of multiple potential consumers, only one of which might be ActiveRecord"? Fair enough. Regardless, the primary question that my post was leading up to stands: a table with the name "foos" will still have what is effectively an implicit column with the name "foos" that is a record value of type "foos".
- icambron 13y agoPeople pick their tools per-project, so I would put it as "if you need to build a database with multiple consumers, AR will be a less valuable choice for any of them, and you are thus much less likely to use it for that project". Right tool, right job, etc. On your primary question, since I've now played with "select foos from foos" on my PG database: I doubt it. When you do a query like Foo.where(foos: 'value').to_a or Foo.find_by_foos('value'), what you're basically doing is a "select foos.* from foos where foos='value'". If foos doesn't actually exist as an explicit column, that doesn't actually work: PG::UndefinedColumn: ERROR: column foos.foos does not exist So it's not like people have been writing queries like that expecting that the implicit column will do something (which I'm not sure would make sense anyway). Unless I'm misunderstanding something, it remains the case that you'd have to have explicitly created a column with the same name as your table to trigger this bug with otherwise working code. Edit: Of course, maybe there are more ways to trigger this bug than the ones provided in the email, and maybe they include queries where you might be relying on an implicit column for something else, such as selecting foos from foos. I'm just saying the cases listed in the email don't seem to be affected by the implicit column.