Creating a DB for another user (or...) (repost attempt)

Is there a way to either assign superuser priviledges/ownership
to that user or to create the database for him?

alter user "name" createdb;

Are you certain? From the 6.5 docs it says that this will:

Give a user the ability to create new databases

Are you saying that I temporarily let the user create dbs, create the
db and the remove the ability to create dbs?

BTW, this isn't a system user so how would he run createdb?

Regards,
--
matthew rice <matt@starnix.com> starnix inc.
tollfree: 1-87-pro-linux thornhill, ontario, canada
http://www.starnix.com professional linux services & products

On Fri, Aug 25, 2000 at 02:18:23PM -0400, Matthew Rice wrote:

BTW, this isn't a system user so how would he run createdb?

createdb is just a shell script that runs the postgresql command:

CREATE DATABASE <new db name> ;

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

HI!

I have such problem:
Is the amount of tables limited in Postgresql7.0?
Has anybody tried to use EFFECTIVELY a database
with 10000 tables at all?

Thx for help.

Marcin Inkielman wrote:

HI!

I have such problem:
Is the amount of tables limited in Postgresql7.0?
Has anybody tried to use EFFECTIVELY a database
with 10000 tables at all?

Thx for help.

Are you really sure you NEED a database with 10000 tables ?

Jurgen

Marcin Inkielman wrote:

On Sat, 26 Aug 2000, Jurgen Defurne wrote:

Date: Sat, 26 Aug 2000 07:36:25 +0200
From: Jurgen Defurne <defurnj@glo.be>
To: Marcin Inkielman <marn@wsisiz.edu.pl>
Cc: postgreSQL general mailing list <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] table count limitation

Marcin Inkielman wrote:

HI!

I have such problem:
Is the amount of tables limited in Postgresql7.0?
Has anybody tried to use EFFECTIVELY a database
with 10000 tables at all?

Thx for help.

Are you really sure you NEED a database with 10000 tables ?

yes ;)

I suggest you read this message which also came up today on the mailing
list :

Patrick,

Any time your design is heading in this direction, take a good hard look
at
it. Proper organization with the appropriate indexes is the way to go.

With tens of hundreds of tables, how will you decide which to use?
How will you write your queries? Customize them for the different
tables?
Will you be generating a lot of data, thereby creating a lot of tables?
How
long will they take to create and populate?

With fewer, large tables you are appending data at the end, and
maintaining
indexes. An inherently simpler operation. Queries are written to a known

design and structure. You will, admittedly, have large index files, but
you
will not have hundreds to thousands of tables, each with indexes.

The Fishcart ecommerce system, which can be implemented in PostgreSQL,
has
only 20 tables, four of which have any degree of traffic.

A proprietary system done in here in Halifax for the employer's
association
has about 16 core tables, two of them are regularly updated, the rest
contain
relatively static information on members, rates, tax rates, piers, etc.

Rethink your design, talk it over with the fencepost, draw little
pictures,
ask "what if", do some rough data storage calculations -- but the
general rule
of thumb, with proper normalization, is "fewer is better".

Regards - Miles Thompson

Patrick Goodwill wrote:

Hi!

I'm writing a system which i could logically separate it into

hundreds,

perhaps thousands, of tables, or it could put it all into one big

table.

Since each tables could probably only grow to 10s of MBs in size, from

a

design, speed, and scalability perspective, is it perferable to split

up

the tables (as I've currently programmed it) or to put everything into

one

gigantic, multi-GB table? This is for the web, so transaction speed

is

important.

Patrick.

Jurgen

On Sun, 27 Aug 2000, Jurgen Defurne wrote:

Any time your design is heading in this direction, take a good hard look
at
it. Proper organization with the appropriate indexes is the way to go.

With tens of hundreds of tables, how will you decide which to use?
How will you write your queries? Customize them for the different
tables?
Will you be generating a lot of data, thereby creating a lot of tables?
How
long will they take to create and populate?

With fewer, large tables you are appending data at the end, and
maintaining
indexes. An inherently simpler operation. Queries are written to a known

design and structure. You will, admittedly, have large index files, but
you
will not have hundreds to thousands of tables, each with indexes.

The Fishcart ecommerce system, which can be implemented in PostgreSQL,
has
only 20 tables, four of which have any degree of traffic.

A proprietary system done in here in Halifax for the employer's
association
has about 16 core tables, two of them are regularly updated, the rest
contain
relatively static information on members, rates, tax rates, piers, etc.

Rethink your design, talk it over with the fencepost, draw little
pictures,
ask "what if", do some rough data storage calculations -- but the
general rule
of thumb, with proper normalization, is "fewer is better".

Regards - Miles Thompson

Thank you for the comments!

I think You are right in most situations, however do you think that "fewer
is better" in situation like this:

I have to construct a catalogue of several types of products (10000
types). Each type is defined by a different set of features.

If I do it like You suggest I have to create a table:

CREATE TABLE products(
product_id int4,
product_type_id int4,
feature_id int4,
value text/float/...
)

of course, it is relatively simple to describe any number of products
having different types in this table.

however... how may I select a set of product having the same type using
this table EFFECTIVELY. For example:w
I have to select:
- all PC with PII/450Mhz and 128MB
or
- all red Renault Megane / 1600GL

Note that each product is described by several rows in the table (each
type of products is characterised by other number of features) and I dont
have to compare (select) products having other types (i.e. cars and
computers in 1 querry).

If i could have 10000 tables - one table for each type of products this
queries would be a lot simplier - don't you think?

PS. sorry for my English - I hope I was understood

--
Marcin Inkielman

Marcin ... comments below

Marcin Inkielman wrote:

On Sun, 27 Aug 2000, Jurgen Defurne wrote:

Any time your design is heading in this direction, take a good hard look

at

it. Proper organization with the appropriate indexes is the way to go.

With tens of hundreds of tables, how will you decide which to use?
How will you write your queries? Customize them for the different
tables?
Will you be generating a lot of data, thereby creating a lot of tables?
How
long will they take to create and populate?

With fewer, large tables you are appending data at the end, and
maintaining
indexes. An inherently simpler operation. Queries are written to a known

design and structure. You will, admittedly, have large index files, but
you
will not have hundreds to thousands of tables, each with indexes.

The Fishcart ecommerce system, which can be implemented in PostgreSQL,
has
only 20 tables, four of which have any degree of traffic.

A proprietary system done in here in Halifax for the employer's
association has about 16 core tables, two of them are regularly updated,

the rest

contain relatively static information on members, rates, tax rates,

piers, etc.

Rethink your design, talk it over with the fencepost, draw little
pictures, ask "what if", do some rough data storage calculations -- but

the

general rule of thumb, with proper normalization, is "fewer is better".

Regards - Miles Thompson

Thank you for the comments!

I think You are right in most situations, however do you think that "fewer
is better" in situation like this:

I have to construct a catalogue of several types of products (10000
types). Each type is defined by a different set of features.

If I do it like You suggest I have to create a table:

CREATE TABLE products(
product_id int4,
product_type_id int4,
feature_id int4,
value text/float/...
)

of course, it is relatively simple to describe any number of products
having different types in this table.

however... how may I select a set of product having the same type using
this table EFFECTIVELY. For example:w
I have to select:
- all PC with PII/450Mhz and 128MB
or
- all red Renault Megane / 1600GL

Note that each product is described by several rows in the table (each
type of products is characterised by other number of features) and I dont
have to compare (select) products having other types (i.e. cars and
computers in 1 querry).

Each table you would create for a different type of product can be replaced
with a single column, say "product_type", which you could use to broadly
classify your items: cars, boats, computers, appliances, etc.

Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as
many as you need, for the different attributes of each item. So "descrip1" may
contain what you may describe as the brand, or the make, or the model, or the
processor type of an item in your user interface.

The difficult questions to answer are: How many types of products? How many
meaningful attributes for each? Base your table design on the answers, and I'd
bet you start to run out at about 6 or so. How fine grained do you want to
make your search?

For a car this may mean: make, model, model type, colour, year, price
For a boat: power/sail/other - and then you get into questions which are
particular for each, and can't be answered by a "one kind suits all" form.
For sail you would have questions of rig, size, construction material, age;
for power length, seats, inboard/outboard/ etc.

Classifications are difficult, as people tend to think of similar things in
different ways.

Don't rule out the possibility of a text field which can be searched once a
primary selection is made. To continue with the car example, once a set of 20
or has been returned, your user may then search for "leather upholstery", but
then at least only 20 text fields are being scanned, not 20,000. If that
expression isn't in the general field, well that's unfortunate.

You could take this further, and have a "product_type" table. For each major
item, you would have a standard set of attributes you could use to populate
pick lists, so that users would not ask for porcelain sailboats with PIII
processors of the Ming dynasty. These same lists would be used in the
maintenace forms you use to add items, so you have a consistency.

This table could also contain the labels, the applicable descriptions for each
product_type. When a user selected a particular product you could fetch the
descriptive text for each field and load it into your interface as the
descriptor for the field. Contact managers do this all the time - the "user
definable" fields have standard names and the user just supplies the label.

This saves a lot of work - one or two standard forms displayed with "custom"
labels, standard queries which run when you click on "Submit", and standard
results forms. (I'm calling them forms, they could be web pages.) Even
background colours and logos could change according to product type.

If i could have 10000 tables - one table for each type of products this
queries would be a lot simplier - don't you think?

No, and I hope my explaination shows why.

PS. sorry for my English - I hope I was understood

You're doing great. I had a siimlar discussion a short while ago with someone
who wanted to do classified ads for agricultural products: livestock, feed,
tractors, etc. Thinking out a good design for your database is hard work.

Here's another tip: Ask what you want to get FROM the database, and how you
will ask for it. Quite often the range of possible inputs is large and
confusing, but usually we ask for very particular groupings.

Hope this has helped - Miles Thompson

PS You are in Poland? What part? My wife's grandparents emigrated from around
Lodz in 1904~06 and settled in Winnipeg, Manitoba in Western Canada. /mt

On Sun, 27 Aug 2000, Miles Thompson wrote:

Each table you would create for a different type of product can be replaced
with a single column, say "product_type", which you could use to broadly
classify your items: cars, boats, computers, appliances, etc.

don't you think that this is exectly the 2nd column in my table I
previousely defined?

CREATE TABLE products(
product_id int4,
product_type_id int4,
feature_id int4,
value text/float/...
)

Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as
many as you need, for the different attributes of each item. So "descrip1" may
contain what you may describe as the brand, or the make, or the model, or the
processor type of an item in your user interface.

that's a possibility, however remember that I don't know right now how
many descriptors I will have for each product type, and how many types I
will have. Also, if I use a general form for a descriptor I will have to
chose a text field and it is not very effective to examine that type of
fields if they contains numbers, dates, etc... Indexes put on the
descriptor columns wont be very effective as one column will describe
different products features - dont you think?

The difficult questions to answer are: How many types of products?

That's exactly I don't know! I hope to to exceed 10000 ;)

How many
meaningful attributes for each? Base your table design on the answers, and I'd
bet you start to run out at about 6 or so. How fine grained do you want to
make your search?

I agreed. 6 up to 10 should suffice.

You could take this further, and have a "product_type" table. For each major
item, you would have a standard set of attributes you could use to populate
pick lists, so that users would not ask for porcelain sailboats with PIII
processors of the Ming dynasty. These same lists would be used in the
maintenace forms you use to add items, so you have a consistency.

in fact, the problem I presented is only a part of bigger one... I have to
construct a full system of product classification. Of course I will have
to construct a table of product types and a table of product type
descriptors.

This table could also contain the labels, the applicable descriptions for each
product_type. When a user selected a particular product you could fetch the
descriptive text for each field and load it into your interface as the
descriptor for the field.

that is exactly my system is doing now... ;)

Contact managers do this all the time - the "user
definable" fields have standard names and the user just supplies the label.

This saves a lot of work - one or two standard forms displayed with "custom"
labels, standard queries which run when you click on "Submit", and standard
results forms. (I'm calling them forms, they could be web pages.) Even
background colours and logos could change according to product type.

If i could have 10000 tables - one table for each type of products this
queries would be a lot simplier - don't you think?

My question sent to the mailing list was if it is possible to work with
postgres+a lot of tables. In fact I am planning to use this tables only
for selects purpose just like multi-cross tables in M$ Access and
internally to use a table of product types, a table of descriptors of
product types (feature vectors) and table of products as defined above.

You're doing great.

Thx ;))

I had a siimlar discussion a short while ago with someone
who wanted to do classified ads for agricultural products: livestock, feed,
tractors, etc. Thinking out a good design for your database is hard work.

I am not sure if SQL is the best language to use with that types of
problems, I use it because SQL-dbases are the most developped and
reliable, however I think that a hierarchical db should be more
appropriate.

PS You are in Poland? What part? My wife's grandparents emigrated from around
Lodz in 1904~06 and settled in Winnipeg, Manitoba in Western Canada. /mt

I live near Warsaw. L�d� (Lodz - if your e-mail reader do not support
iso-8859-2 ;) ) is ~100km from here. You must be a very happy man if your
wife is partially from Poland. Polish women are really beautifull! ;))

regards,

Marcin Inkielman

On Sun, 27 Aug 2000, Miles Thompson wrote:

Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as
many as you need, for the different attributes of each item. So "descrip1" may

Now someone once said to me "If you find your labeling your fields x1, x2,
x3... then your design is wrong." His next step would be to ask for your
ER diagram :-)

Have you tried inheritance? i.e you will have a product, inheriting from
that you will have computers, boats, cars etc. Perhaps from boats you
could have sail boats, motor boats etc as you store different attributes.

As postgresql is an OORBMS it should be able to handle it without any
nasty squash into the relational model tricks.

I've never used an OO database in production mind you...perhaps there is
some catch I'm not aware of.

Andrew

Andrew,

As I was reading Marcin's reply to my message of earlier today I began to think of
how PGSQL has been extended. My thinking was purely relational, an "objectified"
approach would probably be worth considering.

andrew@ugh.net.au wrote:

On Sun, 27 Aug 2000, Miles Thompson wrote:

Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as
many as you need, for the different attributes of each item. So "descrip1" may

Now someone once said to me "If you find your labeling your fields x1, x2,
x3... then your design is wrong." His next step would be to ask for your
ER diagram :-)

Yeah! I used those because they were general - attributes for outboard motors would
be quite different than those for antique glass or sports cars.

Have you tried inheritance? i.e you will have a product, inheriting from
that you will have computers, boats, cars etc. Perhaps from boats you
could have sail boats, motor boats etc as you store different attributes.

As postgresql is an OORBMS it should be able to handle it without any
nasty squash into the relational model tricks.

Agreed, but won't you then have a mess of tables, different forms, different reports
to contend with? What's the difference between having this and a product type field?
I could be wrong, but let's say we take Marcin's earlier table description, and call
it item.

Then we subclass it to boats ...
boats = extends item

and then we subclass boats ...
sailboats = extends boats
motorboats = extends boats
runabouts= extends motorboats
trawlers = extends motorboats

I think we end up with a hell of a mess, and the parent class is 3 levels up for
trawlers and runabouts. I've not worked with object-relational or object-oriented
databases, so I could be missing something, and I'd be willing to be proven wrong.

We're also back to Marcin's original question - Is there a limit on the number of
tables? I don't know, but I'm very uncomfortable with a new set of tables created
for every variation in a product or item type.

I've never used an OO database in production mind you...perhaps there is
some catch I'm not aware of.

I'd love to hear from someone who has done one.

Regards - Miles Thompson

Marcin ..

Marcin Inkielman wrote:

On Sun, 27 Aug 2000, Miles Thompson wrote:

Each table you would create for a different type of product can be replaced
with a single column, say "product_type", which you could use to broadly
classify your items: cars, boats, computers, appliances, etc.

don't you think that this is exectly the 2nd column in my table I
previousely defined?

CREATE TABLE products(
product_id int4,
product_type_id int4,
feature_id int4,
value text/float/...
)

Yes - apologies!

Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as
many as you need, for the different attributes of each item. So "descrip1" may
contain what you may describe as the brand, or the make, or the model, or the
processor type of an item in your user interface.

that's a possibility, however remember that I don't know right now how
many descriptors I will have for each product type, and how many types I
will have. Also, if I use a general form for a descriptor I will have to
chose a text field and it is not very effective to examine that type of
fields if they contains numbers, dates, etc... Indexes put on the
descriptor columns wont be very effective as one column will describe
different products features - dont you think?

My thinking there was that you would use whatever datatype was appropriate, and I'd
guess that most of them would be character fields, except for date information. So
descrip1 could be used for "Make" for cars, appliances, camera's, lenses, etc.; or
for "Fabric" for clothing, linens, antique garments etc. Wether you want to use
numbers to encode this information, or descriptive text, is up to you.

The difficult questions to answer are: How many types of products?

That's exactly I don't know! I hope to to exceed 10000 ;)

That's why I think a single table, with appropriate fields will be more powerful and
flexible. <g>

How many
meaningful attributes for each? Base your table design on the answers, and I'd
bet you start to run out at about 6 or so. How fine grained do you want to
make your search?

I agreed. 6 up to 10 should suffice.

You could take this further, and have a "product_type" table. For each major
item, you would have a standard set of attributes you could use to populate
pick lists, so that users would not ask for porcelain sailboats with PIII
processors of the Ming dynasty. These same lists would be used in the
maintenace forms you use to add items, so you have a consistency.

in fact, the problem I presented is only a part of bigger one... I have to
construct a full system of product classification. Of course I will have
to construct a table of product types and a table of product type
descriptors.

This table could also contain the labels, the applicable descriptions for each
product_type. When a user selected a particular product you could fetch the
descriptive text for each field and load it into your interface as the
descriptor for the field.

that is exactly my system is doing now... ;)

Good, forward thinking -- a little pain now for a lot of gain later on.

Contact managers do this all the time - the "user
definable" fields have standard names and the user just supplies the label.

This saves a lot of work - one or two standard forms displayed with "custom"
labels, standard queries which run when you click on "Submit", and standard
results forms. (I'm calling them forms, they could be web pages.) Even
background colours and logos could change according to product type.

If i could have 10000 tables - one table for each type of products this
queries would be a lot simplier - don't you think?

My question sent to the mailing list was if it is possible to work with
postgres+a lot of tables. In fact I am planning to use this tables only
for selects purpose just like multi-cross tables in M$ Access and
internally to use a table of product types, a table of descriptors of
product types (feature vectors) and table of products as defined above.

You're doing great.

Thx ;))

I had a siimlar discussion a short while ago with someone
who wanted to do classified ads for agricultural products: livestock, feed,
tractors, etc. Thinking out a good design for your database is hard work.

I am not sure if SQL is the best language to use with that types of
problems, I use it because SQL-dbases are the most developped and
reliable, however I think that a hierarchical db should be more
appropriate.

That's really the structure we're imposing here, isn't it.

PS You are in Poland? What part? My wife's grandparents emigrated from around
Lodz in 1904~06 and settled in Winnipeg, Manitoba in Western Canada. /mt

I live near Warsaw. L�d� (Lodz - if your e-mail reader do not support
iso-8859-2 ;) ) is ~100km from here. You must be a very happy man if your
wife is partially from Poland. Polish women are really beautifull! ;))

Well, I'm pretty fond of her!

Later - Miles

Marcin Inkielman wrote:

On Sun, 27 Aug 2000, Jurgen Defurne wrote:

I think You are right in most situations, however do you think that "fewer
is better" in situation like this:

I have to construct a catalogue of several types of products (10000
types). Each type is defined by a different set of features.

If I do it like You suggest I have to create a table:

CREATE TABLE products(
product_id int4,
product_type_id int4,
feature_id int4,
value text/float/...
)

of course, it is relatively simple to describe any number of products
having different types in this table.

however... how may I select a set of product having the same type using
this table EFFECTIVELY. For example:w
I have to select:
- all PC with PII/450Mhz and 128MB
or
- all red Renault Megane / 1600GL

Note that each product is described by several rows in the table (each
type of products is characterised by other number of features) and I dont
have to compare (select) products having other types (i.e. cars and
computers in 1 querry).

If i could have 10000 tables - one table for each type of products this
queries would be a lot simplier - don't you think?

PS. sorry for my English - I hope I was understood

--
Marcin Inkielman

I was in the same situation few years ago and I used something like
this:

Table "types" describes all types
CREATE TABLE types(
type_id int4,
type_name text
)

Table "features" describes all features for every type, including type
(meaning database type - int or float or date or text etc.) in order to
make a check during writing data about products
CREATE TABLE features(
feature_id int4,
type_id int4 references types (type_id),
feature_type text,
feature_name text
)

Table "products" contains all products
CREATE TABLE products(
product_id int4,
product_name text,
type_id int4 references types (type_id)
)

Table "products_features" contains data about values of features of
every product
CREATE TABLE products_features(
product_id int4 references products (product_id),
feature_id int4 references features (feature_id),
value text
)

Such schema requere some triggers and frontend procedures, e.g. to check
correspondence of value in "products_features" and type of this feature
described in "features".
And this schema allows to make a selects like you wrote, for example

- all PC with PII/450Mhz and 128MB

select * from products p, products_features pf1, products_features pf2
where pf1.product_id=p.product_id
and pf2.product_id=p.product_id
and pf1.feature_id=<id of feature "processor type"> and
pf1.value='PII/450'
and pf2.feature_id=<id of feature "memory amount"> and pf2.value='128';

--
Sincerely yours,
Yury

Be aware of one thing: having a seperate table for each product type makes
it extremely difficult to scale your application. In your plan, everytime
you add a new product type you have to create a new table. Which means if
you have any sort of general "search all product types for this
attribute" type search, you have to rewrite the sql for that search every
time, etc. Of course with postgres you can get around some of this by
making a root_object type table with attributes that all products share
and then have your individual tables inherit from it so you can at least
user postgres's inheritance to help your searches.

I still think that finding some way to do this with fewer tables is your
best bet, but I don't know the specifics of your situation.

-----------------------------------------
Water overcomes the stone;
Without substance it requires no opening;
This is the benefit of taking no action.
Lao-Tse

Brian Knox
Senior Systems Engineer
brian@govshops.com

On Sun, 27 Aug 2000, Marcin Inkielman wrote: