Thread: need an information on PostgreSQL
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] [Phone: +370 98 63207] [E-mail: mailto:d.doroshenko@omnitel.net]
On Thu, 9 Nov 2000, Denis A. Doroshenko wrote: > 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. > 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. > 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. > 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.
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.) > 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. > 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. > does PostgreSQL support object naming as "[[[server.]dbname.] > owner.]objectname"? Nope. > how could i restrict a user from looking what tables are in database > as well as what their structures are. You can't. > 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. > 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. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> 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...)
> 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 ;-)
> 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