Thread: Limit for number of Joins in a View? Version 8.1.4 on Redhat 9

MargaretGillon@chromalloy.com wrote:
> I am wondering what the limit is on the number of joins in a View or a
> Select.
>
> Background: I have many tables with similar flags such as Active,
> Inactive, High, Medium, Low. I am storing the flags in a flag table and
> then putting an int4 foreign key to the flag in the data tables. Some data
> tables may have up to 15 flags, as well as 30 or 40 other foreign keys.
> They're all left outer joins. Is this a problem for a view?

No real problem, but I suspect you'd be better off with a simpler setup:

CREATE TABLE has_some_flags(
...
   priority_flag  char,
...
   CONSTRAINT valid_priority_flag CHECK
     (priority_flag IN ('A','I','H','M','L'))
)

Or, perhaps better for your particular case:

CREATE DOMAIN priority_flag char
   CONSTRAINT valid_pri_flag CHECK (VALUE IN ('A','I','H','M','L'));
CREATE TABLE has_flags(pri_flag priority_flag);

The main question would be whether your flags are going to change - if
not, they're more like a type and use the CHECK constraint. On the other
hand, if you're updating them regularly then you'll want to use joins.
--
   Richard Huxton
   Archonet Ltd

MargaretGillon@chromalloy.com wrote:
> Richard,
> I have a few questions on the domain.
>
> 1) How do I add a domain to an existing table? Can I add it to an existing
> column or do I need to make a new column with the domain and copy the
> existing data into it?

Try ALTER TABLE ... ALTER COLUMN col TYPE ...
   http://www.postgresql.org/docs/8.1/static/sql-altertable.html
That basically does all the creating/copying for you.

> 2) What happens to the domain and tables using it if I have to modify the
> domain?

You don't. That's why I said "if you don't change it". Now, you can of
course create a new domain and do the ALTER TABLE thing above. But, if
you plan on changing flags at all regularly, you'll want to use foreign
keys.

--
   Richard Huxton
   Archonet Ltd

On Feb 7, 2007, at 10:05 AM, MargaretGillon@chromalloy.com wrote:
> I was using the flag table to keep the flags consistent between all
> the tables in the database that might use them. I didn't know about
> CREATE DOMAIN which will do what I want perfectly

Note that DOMAIN support unfortunately isn't perfect; for example,
plpgsql doesn't enforce domain constraints (IIRC there's some other
bugs as well). So you should probably do a test to make sure
everything you'll be doing with domains will work before you re-code
everything.

Also, I suggest using "char" instead of just char. "char" is a
special data type that's limited to storing a single character; the
advantage is that it's much smaller and faster than a char.

If you do end up back at using foreign keys, I suggest using either a
smallint or "char"... the savings across the number of fields you're
looking at would start to add up, especially if you start putting a
decent number of rows in the table.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Jim Nasby <decibel@decibel.org> writes:
> Note that DOMAIN support unfortunately isn't perfect; for example,
> plpgsql doesn't enforce domain constraints (IIRC there's some other
> bugs as well).

Fixed in 8.2 ... not that there aren't necessarily bugs left, but the
above as a blanket statement is obsolete.

            regards, tom lane

MargaretGillon@chromalloy.com wrote:
> >Jim Nasby <decibel@decibel.org> wrote on 02/08/2007 12:12:00 PM:

> > If you do end up back at using foreign keys, I suggest using either a
> > smallint or "char"... the savings across the number of fields you're
> > looking at would start to add up, especially if you start putting a
> > decent number of rows in the table.
>
> I ended up using Varchar(1). According to the help there is no speed
> difference in the character types, on am I misunderstanding something?

The "char" type (including quotes) is a very different animal from all
those character types the manual you quote is talking about.  "char" is
a single byte, while varchar(1) and all the rest are a single character,
meaning there can be multiple bytes in presence of a multibyte encoding;
so Postgres is forced to use a variable-length structure to store it.
"char" has no such requirement.  It's used in the system catalogs as a
"poor man's enum", for example in pg_class.relkind.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

MargaretGillon@chromalloy.com wrote:

> Using PGADMINIII I added a column of this type to my database but when I
> try to update it I get an error.
>
> column:
> ALTER TABLE datatype ADD COLUMN charflag "char"[];

You added an array of "char", which is not the same.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 2/9/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jim Nasby <decibel@decibel.org> writes:
> > Note that DOMAIN support unfortunately isn't perfect; for example,
> > plpgsql doesn't enforce domain constraints (IIRC there's some other
> > bugs as well).
>
> Fixed in 8.2 ... not that there aren't necessarily bugs left, but the
> above as a blanket statement is obsolete.


biggest limitation of domains is IMO a lack of an array type.

merlin