CREATE TABLE oddity?

Nigel J. Andrews wrote:

I must be missing something but I don't know what and I can't see
anything in the create table documentation (from 7.2.1) mentioning this.

When I do, in psql:

-- in this I have tried atype as int, serial and varchar
CREATE TABLE user (
something atype
);

I get the following response:

ERROR: parser: parse error at or near "user"

It is down to the table name I'm trying to use but I don't see why
it's disallowed. Is user a special reserved word in the parser?

http://www.postgresql.org/idocs/index.php?sql-keywords-appendix.html

Jochem

On Thu, 31 Oct 2002, Jochem van Dieten wrote:

Nigel J. Andrews wrote:

I must be missing something but I don't know what and I can't see
anything in the create table documentation (from 7.2.1) mentioning this.

When I do, in psql:

-- in this I have tried atype as int, serial and varchar
CREATE TABLE user (
something atype
);

I get the following response:

ERROR: parser: parse error at or near "user"

It is down to the table name I'm trying to use but I don't see why
it's disallowed. Is user a special reserved word in the parser?

http://www.postgresql.org/idocs/index.php?sql-keywords-appendix.html

Thanks, and thanks for not pointing out my stupidity in not looking for a
reserved keywords appendix.

I guess everyone already knows that MySQL differs from the standards by
allowing user as an identifier.

I see quoting the table name does allow it to be used. However that still means
I'll have to go through the application and change it's usage unless the DBI
system automatically does that, which I doubt.

--
Nigel J. Andrews

Hi,

I need to set up a system that will be used by some 20,000 users. Most
of the applications will be web based.
I'm wondering if postgress will be able to handle 20,000 connection.
MySQL has a max connection of about 16,000. Does postgres have a max
connection limit? or does the limit depend on the hardware resources only?
I wonder if using a cluster will help (something like oracle database
cluster). I have search the mailing list for clustering, and found some
articles

http://archives.postgresql.org/pgsql-general/2000-12/msg00024.php

but this was quite long time ago, and it seems like they are still in
research.
Has anyone done postgres clustering for a production use?
It would be helpful to have a section explaining how to do this (if it's
possible).

thanks,
- reynard

I guess the main issue is if you're going to be having 20,000
simultaneous connections (unlikely).

If you're using Java then you can setup a connection pool for orders of
magnitude persistent connections less that that.

If using persistent connections in PHP or mod_perl, etc, you should
adjust apache's child lifetime so the processes will die after a few
thousand requests, thus keeping the number of connections to Postgresql
low.

The same strategy would apply if you are using any database. Even with
MySql you dont want 16,000 connections open :-)

- Ericson Smith
eric@did-it.com

Reynard-

I can't be of much help in answering the upper limit question, but I wonder
if that is really the right question.

Do you really expect all 20,000 users to be accessing the database at the
same time, or is it more like 20,000 users in the application at one time.
Usually, such a situation is handled by connection pooling so that although
many users are connected to the application, they only get allocated a db
connection as needed. The ratio will vary by application, but in our case,
we can typically support at least 20 active users per connection using
pooling.

-Nick

On 31 Oct 2002 at 10:04, Ericson Smith wrote:

I guess the main issue is if you're going to be having 20,000
simultaneous connections (unlikely).

I would suggest rather than cerating all those users as database users, write a
small user module in your application and interact with database as a single
application user. That way database load will be limited to actual concurrent
user load.

I know of lot of applications which do that. And oracle came up with virtual
user strategy to shoot this scenario.

HTH

Bye
Shridhar

--
taxidermist, n.: A man who mounts animals.

Nigel J. Andrews wrote:

Thanks, and thanks for not pointing out my stupidity in not looking for a
reserved keywords appendix.

I guess everyone already knows that MySQL differs from the standards by
allowing user as an identifier.

I see quoting the table name does allow it to be used. However that still means
I'll have to go through the application and change it's usage unless the DBI
system automatically does that, which I doubt.

Yea, you have to quote them all --- it is a pain.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073