need an information on PostgreSQL

need an information on PostgreSQL

Post by Denis A. Doroshenk » Fri, 10 Nov 2000 17:39:56



Hello,

i'm a newbie to pgsql (and SQL as well) so please be patient :-)

first, i would like to know all limitations (or call it parameters) of
PostgreSQL 7 (i use 7.0.2). that includes for example, number of
columns in a row, number of rows in a table, number of triggers,
indexes per table. the only limitations i've seen in various sources is
a row size (currently -- 8k) and indexning length of text (something
about 2k). i like many things in PostgreSQL and such information would
help me to evaluate the possibility to use it in bigger projects...

does PoestgreSQL support stored procedures? and will it do, if
it isn't. i could not find any reference on this in docs/htmls...
BTW, about "will it support", is there some roadmap for PostgreSQL?
i've looked at TODO, but hmm :-) it's more detailed than i need...

does PostgreSQL support object naming as "[[[server.]dbname.]
owner.]objectname"?

how could i restrict a user from looking what tables are in database
as well as what their structures are. i know i could restrict
select/update/alter/delete/whatever, but can i hide the tables from
a user? also, can PostgreSQL hide some particular columns from user?
the reason for these questions is security, surely. for example i
would create special user (supervisor) and give him all access, after
that i would hide all tables/indexes/whatever and create views,
joining the real tables in any order i like. thus i could install the
database giving nothing away about its internal structure...
sure, i may not use such features, but it would be very good to have
them...

also, could anybody point me to the latest SQL standard (SQL92)?

advTHANKSance.

P.S. why whole world pronounces 'SQL' like 'esquel', while, AFAIK,
IBM proposed it to be pronounced as 'seequel'? :-)

--
Denis A. Doroshenko -- VAS/IN group engineer
[Address: Omnitel Ltd., T.Sevcenkos 25, Vilnius 2600, Lithuania]

 
 
 

need an information on PostgreSQL

Post by Stephan Sza » Sat, 11 Nov 2000 01:34:20



> Hello,

> i'm a newbie to pgsql (and SQL as well) so please be patient :-)

> first, i would like to know all limitations (or call it parameters) of
> PostgreSQL 7 (i use 7.0.2). that includes for example, number of
> columns in a row, number of rows in a table, number of triggers,
> indexes per table. the only limitations i've seen in various sources is
> a row size (currently -- 8k) and indexning length of text (something
> about 2k). i like many things in PostgreSQL and such information would
> help me to evaluate the possibility to use it in bigger projects...

All of the below IIRC... :)
The total row size must be under 8k, so that limits the number columns
to a row by minimum size requirements (you can't fit 10k ints for
example), but otherwise I don't know of anything for columns.
I don't know of a number of row in table limit, the system breaks
up heap files to get around filesystem limits, probably disk space and
performance related (make sure you have indexes and are using queries
that use them).  The length of views and procedure text is limited
since a format of those need to fit into a database row.  The
text length limit for indexes is approximately 2700 bytes and that
might be the procedure length.

Quote:> does PoestgreSQL support stored procedures? and will it do, if
> it isn't. i could not find any reference on this in docs/htmls...
> BTW, about "will it support", is there some roadmap for PostgreSQL?
> i've looked at TODO, but hmm :-) it's more detailed than i need...

Sort of, it has user defined functions, but pl functions in 7.0
can't really intelligibly return sets of rows (at least without
jumping through hoops).  But functions that act on data are there.
Also in 7.0, functions don't take null parameters very well.

Quote:> does PostgreSQL support object naming as "[[[server.]dbname.]
> owner.]objectname"?

Not yet.  Schemas have been talked about, but I haven't heard to much
about supporting server.dbname.

Quote:> how could i restrict a user from looking what tables are in database
> as well as what their structures are. i know i could restrict
> select/update/alter/delete/whatever, but can i hide the tables from
> a user? also, can PostgreSQL hide some particular columns from user?

Not really.  Also in 7.0 you can't prevent someone who can connect
from creating database objects.

Some of these are fixed or changed in current sources and will be part
of 7.1.

 
 
 

need an information on PostgreSQL

Post by Peter Eisentra » Sat, 11 Nov 2000 01:44:05


Quote:Denis A. Doroshenko writes:
> first, i would like to know all limitations (or call it parameters) of
> PostgreSQL 7 (i use 7.0.2). that includes for example, number of
> columns in a row, number of rows in a table, number of triggers,
> indexes per table.

See FAQ.  (Anything not mentioned there is relatively unlimited.)

Quote:> does PoestgreSQL support stored procedures? and will it do, if
> it isn't. i could not find any reference on this in docs/htmls...

We have user-defined functions, but they can only return one value, which
is unlike what most people expect.

Quote:> BTW, about "will it support", is there some roadmap for PostgreSQL?
> i've looked at TODO, but hmm :-) it's more detailed than i need...

Mostly, we just implement what we feel like.  Stored procedure support is
high on many people's lists though.

Quote:> does PostgreSQL support object naming as "[[[server.]dbname.]
> owner.]objectname"?

Nope.

Quote:> how could i restrict a user from looking what tables are in database
> as well as what their structures are.

You can't.

Quote:> also, could anybody point me to the latest SQL standard (SQL92)?

The latest standard is SQL99 and you can buy it from your local ISO
approved standardization organization.  But reading it is not a pleasant
experience; you better buy a book.

Quote:> P.S. why whole world pronounces 'SQL' like 'esquel', while, AFAIK,
> IBM proposed it to be pronounced as 'seequel'? :-)

Who cares about IBM?  If they wanted to have it pronounced 'seequel' they
should have named it 'seequel'. :-)

Oh, and on the "whole world" scale I think "ess-queue-ell" (or local
tongue variations) wins hand down.

--

 
 
 

need an information on PostgreSQL

Post by Tom La » Sat, 11 Nov 2000 02:12:42



> The total row size must be under 8k, so that limits the number columns
> to a row by minimum size requirements (you can't fit 10k ints for
> example), but otherwise I don't know of anything for columns.

There is a limit of 1600 columns per table.  (This is driven by the
fact that tuple header + null-values bitmap must fit into 255 bytes;
we could relax it by increasing t_hoff from uint8 to uint16, but I've
never yet heard anyone complain about it...)

Quote:> I don't know of a number of row in table limit, the system breaks
> up heap files to get around filesystem limits, probably disk space and
> performance related (make sure you have indexes and are using queries
> that use them).

AFAIK there's no direct limit on number of rows, although you would
see interesting misbehavior from count() with more than 2G rows, since
count() returns an int4 result.  Table size is limited to either 2G or
4G blocks (not sure if we are careful to do BlockNumber arithmetic
unsigned or not), so either 16 or 32 terabytes per table at the default
blocksize of 8K, up to 64/128TB at blocksize 32K.

As you say, performance issues are probably going to cause more of a
problem than these theoretical limits, at least for a few more years ;-)

Quote:> The length of views and procedure text is limited
> since a format of those need to fit into a database row.  The
> text length limit for indexes is approximately 2700 bytes and that
> might be the procedure length.

That was the procedure-text length limit in 6.5, but 7.0 doesn't keep
any indexes on procedure bodies, so in 7.0 you can have procedures
approaching 8K.

Row-length-related limits, including that one, should be largely solved
by 7.1's TOAST feature, although you'll still see a limit on number of
columns per table.  TOAST only helps on column types that can be
sizable; for example, TOAST can't do anything with a float8 column,
and so you still won't be able to put >1000 float8 columns in one
table...

                        regards, tom lane

 
 
 

1. Postgresql goes down need to restart (redhat postgresql service script) lock files removal avoid 2 postmasters

Hi,=20
Thank you Tom.
I have been looking at the postgresql service startup scripts in Redhat=20
written by Lamar Owen et al.

I would like to understand what is the role of the following files that are=
=20
created during startup of Postgresql on my Redhat Linux box.

1.  /var/lib/pgsql/data/postmaster.pid

2.  /var/run/postmaster.pid (for redhat 7.3/Postgresql 7.2)=20
    /var/run/postmaster5432.pid (for redhat 9.0/Postgresql 7.3.2)

3.  /var/lock/subsys/postgresql

and=20

4.  /tmp/.s.PGSQL.5432.lock (and associated link to the directory in that=
=20
directory).

I notice that the file=20

1. /var/lib/pgsql/data/postmaster.pid contains the pid of the=20
/usr/bin/postmaster process. Interestingly Lamar does not rm this file on=
=20
stop().

2. /var/run/postmaster.pid contains the pid of a postgres stats process

3. the  /tmp/.s.PGSQL.5432.lock file has the pid of the /usr/bin/postmaster=
=20
process.

Why do I care?

My goal is to use   DJ Bernsteins daemonstools to make sure that my Postgre=
sql=20
process goes back up unattended if it goes down for some reason. So I will =
be=20
substituting daemontools for the postgresql service script.
Thus I want to know what lock files to remove to make sure all is ok. I als=
o=20
want to follow Tom Lanes's advice and not shoot myself in the foot by=20
creating two different postmaster processes working the same database!!!!

Thank you all for your help!!!

Mitchell Laks

---------------------------(end of broadcast)---------------------------

2. running an Index Server Query from VB5

3. Where can I get Users and Roles information in PostgreSQL

4. DB Speed - Need Some Good Ideas

5. FW: Cygwin PostgreSQL Information and Suggestions

6. RDO 2.0, NT Problem

7. Need book on PHP and PostgreSQL

8. SQL Server Developer Edition

9. Need Postgresql ODBC Driver

10. PostgreSQL x Oracle / BBS in Python - PostgreSQL ?

11. Urgent need of (paid) PostgreSQL support in New York City area

12. I need ODBC for PostgreSQL

13. Help needed with PostgreSQL