Обсуждение: CREATE TABLE oddity?
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?
version
---------------------------------------------------------------
PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.95.2
(1 row)
psql version is the 7.2.3 install and is dynamically linked with:
libpq.so.2 => /usr/local/stow/pgsql-7.2.3/lib/libpq.so.2 (0x40014000)
libpam.so.0 => /lib/libpam.so.0 (0x4002f000)
libz.so.1 => /usr/lib/libz.so.1 (0x40037000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x40047000)
libresolv.so.2 => /lib/libresolv.so.2 (0x40074000)
libnsl.so.1 => /lib/libnsl.so.1 (0x40083000)
libdl.so.2 => /lib/libdl.so.2 (0x40099000)
libm.so.6 => /lib/libm.so.6 (0x4009d000)
libreadline.so.4 => /lib/libreadline.so.4 (0x400ba000)
libc.so.6 => /lib/libc.so.6 (0x400df000)
/lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)
libncurses.so.5 => /lib/libncurses.so.5 (0x401bd000)
Unfortunately I don't have time to investigate the source to see why I am not
allowed a table called user.
BTW, user attempting the create table is not the/a superuser and owns the
database.
--
Nigel J. Andrews
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 On Thu, 2002-10-31 at 09:39, Reynard Hilman wrote: > > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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 > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Reynard Hilman > Sent: Thursday, October 31, 2002 9:39 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] handling 20,000 connection? > > > > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
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