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

Richard Huxton <dev@archonet.com> wrote on 02/07/2007 01:33:05 AM:
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

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. Thank you.
Margaret Gillon.

Richard Huxton <dev@archonet.com> wrote on 02/07/2007 01:33:05 AM:

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

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?

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

Margaret Gillon

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

Jim Nasby <decibel@decibel.org> wrote on 02/08/2007 12:12:00 PM:
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)

Hi Jim,

I ended up using Varchar(1). According to the help there is no speed
difference in the character types, on am I misunderstanding something?

Tip: There are no performance differences between these three types, apart
from the increased storage size when using the blank-padded type. While
character(n) has performance advantages in some other database systems, it
has no such advantages in PostgreSQL. In most situations text or character
varying should be used instead.
from: http://www.postgresql.org/docs/8.2/static/datatype-character.html

Margaret Gillon

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.

Alvaro Herrera <alvherre@commandprompt.com> wrote on 02/08/2007 01:58:20

PM:

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.

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"[];
ALTER TABLE datatype ALTER COLUMN charflag SET STORAGE EXTENDED;
COMMENT ON COLUMN datatype.charflag IS 'testing';

update:
update datatype set charflag = 'A';

results:
ERROR: array value must start with "{" or dimension information
SQL state: 22P02

Margaret Gillon

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

Alvaro Herrera <alvherre@commandprompt.com> wrote on 02/08/2007 02:51:52

PM:

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

ALTER TABLE datatype ADD COLUMN charflag "char";

Got it. Thank you. Margaret Gillon.

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