Postgres Pro
Downloads
Home   >   mailing lists

Thread: CREATE TABLE oddity?

CREATE TABLE oddity?

From
"Nigel J. Andrews"
Date:

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

Re: CREATE TABLE oddity?

From
Jochem van Dieten
Date:
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

Re: CREATE TABLE oddity?

From
"Nigel J. Andrews"
Date:
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

handling 20,000 connection?

From
Reynard Hilman
Date:
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

Re: handling 20,000 connection?

From
Ericson Smith
Date:
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)

Re: handling 20,000 connection?

From
"Nick Fankhauser"
Date:
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)
>

Re: handling 20,000 connection?

From
"Shridhar Daithankar"
Date:
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.

Re: CREATE TABLE oddity?

From
Bruce Momjian
Date:
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