Re: [HACKERS] Snaptshot appears fine to me ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Right now, from what I can tell, the snapshot looks great to me: That looks up-to-date to me too, but where did you get it from? The copy I pulled just now from ftp://ftp.us.postgresql.org/dev/postgresql-snapshot.tar.gz has still got the problem: $ ls -l postgresql-snapshot total 974 -rw-r--r-- 1 tglusers 1189 Jan 25 2001 COPYRIGHT drwxr-xr-x 2 tglusers 1024 Oct 14 04:00 ChangeLogs/ -rw-r--r-- 1 tglusers 3567 Apr 9 2001 GNUmakefile.in -rw-r--r-- 1 tglusers 132330 Sep 16 04:00 HISTORY -rw-r--r-- 1 tglusers34643 Apr 7 2001 INSTALL -rw-r--r-- 1 tglusers 1432 Feb 10 2001 Makefile -rw-r--r-- 1 tglusers 1928 May 11 04:00 README -rw-r--r-- 1 tglusers 586 Aug 27 04:00 aclocal.m4 drwxr-xr-x 2 tglusers 1024 Oct 14 04:00 config/ -rwxr-xr-x 1 tglusers 249316 Sep 15 04:00 configure* -rw-r--r-- 1 tglusers35852 Sep 15 04:00 configure.in drwxr-xr-x 41 tglusers 1024 Oct 14 04:00 contrib/ drwxr-xr-x 4 tglusers 1024 Oct 14 04:00 doc/ -rw-r--r-- 1 tglusers 738 May 11 04:00 register.txt drwxr-xr-x 15 tglusers 1024 Oct 14 04:00 src/ regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] namespaces, was Package support for Postgres
On Sat, 13 Oct 2001, Bill Studenmund wrote:
> On Sat, 13 Oct 2001, Tom Lane wrote:
>
> > I also wonder how the fixed, single-level namespace search path you
> > describe interacts with the SQL rules for schema search. (I don't
> > actually know what those rules are offhand; haven't yet read the schema
> > parts of the spec in any detail...)
>
> Should be independent. The searching only happens when you are not in the
> "standard" package, and you give just a function name for a function.
> The searching would only happen in the current schems. If
> you give a schema name, then I'd expect PG to look in that schema, in
> standard, for that function. If you give both a schema and package name,
> then PG would look in that package in that schema.
My description of namespaces seems to have caused a fair bit of confusion.
Let me try again.
The ability of the package changes to automatically check standard when
you give an ambiguous function name while in a package context is a
convenience for the procedure author. Nothing more.
It means that when you want to use one of the built in functions
(date_part, abs, floor, sqrt etc.) you don't have to prefix it with
"standard.". You can just say date_part(), abs(), floor(), sqrt(), etc.
The only time you need to prefix a call with "standard." is if you want to
exclude any so-named routines in your own package.
I've attached a copy of a package I wrote as part of testing package
initializers and package global variables. It is an adaptation of the
Random package described in Chapter 8 of _Oracle8 PL/SQL Programming_ by
Scott Urman. Other than adapting it to PostgreSQL, I also tweaked the
RandMax routine to give a flat probability.
Note the use of date_part() in the BODY AS section, and the use of rand()
in randmax(). Both of these uses are the ambiguous sort of function naming
which can trigger the multiple searching. Since they are in plpgsql code,
they get parsed in the context of the random package. So when each of them
gets parsed, parse_func first looks in the random package. For rand(), it
will find the rand() function and use it. But for date_part(), since there
isn't a date_part function in the package, we use the one in standard.
If we didn't have this ability, one of the two calls would need to have
had an explicit package with it. There are two choices (either "standard."
would be needed for date_part(), or "random." for rand()), but I think
both would lead to problems. Either choice makes the syntax heavy, for
little gain. Also, if we scatter the package name throughout the package,
if we ever want to change it, we have more occurences to change.
Does that make more sense?
Take care,
Bill
create or replace package random as
declare v_seed 'float8', v_Multiplier 'float8', v_incriment 'float8'
language 'plpgsql'
body as '
begin
v_Multiplier := 22695477;
v_incriment := 1;
v_seed := date_part(''epoch'', timestamp ''now'');
return NULL;
end;
'language 'plpgsql'
function changeseed (float8) returns float8 as '
begin
v_seed := $1;
return 0;
end;
' language 'plpgsql'
function rand () returns float8 as '
begin
v_seed := (v_multiplier * v_seed + v_incriment) % ( 2::float8 ^ 32);
return (v_seed/(2^16)) % 32768::float8;
end;
' language 'plpgsql'
function randmax (float8) returns float8 as '
begin
return rand() * $1 / 32768 + 1;
end;
' language 'plpgsql';
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] schema support, was Package support for Postgres
On Sat, 13 Oct 2001, Tom Lane wrote: > Bill Studenmund <[EMAIL PROTECTED]> writes: > > The other choice is to just give the function's name. The first place > > Postgres will look is in the package context used for parsing. If it's not > > there (and that context wasn't "standard"), then it will look in > > "standard". > > Hmm. How does/will all of this interact with SQL-style schemas? > > The reason I'm concerned is that if we want to retain the present > convention that the rowtype of a table has the same name as the table, > I think we are going to have to make type names schema-local, just > like table names will be. And if type names are local to schemas > then so must be the functions that operate on those types, and therefore > also operators (which are merely syntactic sugar for functions). Ahhh... There's the operators == sugar comment. I agree with you above; types and functions need to be schema-specific. > This seems like it will overlap and possibly conflict with the decisions > you've made for packages. It also seems possible that a package *is* > a schema, if schemas are defined that way --- does a package bring > anything more to the table? I'm repeating myself a little. :-) Packages aren't schemas. What they bring to the table is they facilitate making stored procedures (functions). You can have twelve different developers working on twenty different packages, with no fear of name conflicts. The package names will have to be different, so there can be functions with the same names in different pacakges. This ability isn't that important in small development projects, but is really important for big ones. Think about big db applications, like Clarify. Any project with multiple procedure authors. Without something like packages, you'd need to spend a lot of effort coordinating names & such so that they didn't conflict. With packages, it's rather easy. Also, I think PostgreSQL can challenge the commercial databases for these applications. But to do so, changing over to PG will need to be easy. Having packages there will greatly help. > I'd like to see schemas implemented per the spec in 7.3, so we need to > coordinate all this stuff. For the most part, I think packages and schemas are orthogonal. I'm taking a cue from Oracle here. Oracle considers packages to be a schema-specific object. Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Snaptshot appears fine to me ...
On Mon, 15 Oct 2001, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > Right now, from what I can tell, the snapshot looks great to me: > > That looks up-to-date to me too, but where did you get it from? > The copy I pulled just now from > ftp://ftp.us.postgresql.org/dev/postgresql-snapshot.tar.gz > has still got the problem: That should be ftp2.us.postgresql.org. But it should be irrelevant very soon as I think we're ready to make the switchover now. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Does "postmaster -i"...
Thomas Lockhart <[EMAIL PROTECTED]> writes: > ... work for you with code built from the cvs tip? I did an update and > build tonight and see > myst$ postmaster -i > postgres: invalid option -- r Hmm. I was fooling with postmaster.c & postgres.c last night. I didn't think I touched parameter parsing --- and my test setup does use -i --- but I'll take another look :-( regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Fw: Re: [HACKERS] Is there no "DESCRIBE ;" on PGSQL? help!!!
Sorry about that message. My mailer had a bad reply format. -- ICQ: 15605359 Bicho =^..^= First, they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Gandhi. Por que no pensaran los hombres como los animales? Pink Panther ---$B5$8!BN$N0lCW(B $B=k$54($5$bH`4_$^$G!#(B $B%"%s(B $B%"%s(B $B%"%s(B $B$H$C$F$bBg9%$-(B ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Fw: Re: [HACKERS] Is there no "DESCRIBE ;" on PGSQL? help!!!
-- Forwarded message --
Date: Fri, 19 Oct 2001 08:22:46 -0600
From: David Eduardo Gomez Noguera <[EMAIL PROTECTED]>
To: "Ron de Jong" <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED]
Subject: Re: [HACKERS] Is there no "DESCRIBE ;" on PGSQL? help!!!
on psql, do \? there are a lot of commands that let you do it:
\l (this list databases)
dabicho=# \l
List of databases
Database | Owner | Encoding
-+--+---
agenda | dabicho | SQL_ASCII
cele| dabicho | SQL_ASCII
dabicho | dabicho | SQL_ASCII
diccionario | dabicho | SQL_ASCII
imagenes| dabicho | SQL_ASCII
libros | dabicho | SQL_ASCII
mp3 | dabicho | SQL_ASCII
postgres| postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(10 rows)
mp3=# \d(this list tables on the current db)
List of relations
Name | Type | Owner
+--+-
album | table| dabicho
album_id_seq | sequence | dabicho
artista| table| dabicho
artista_id_seq | sequence | dabicho
dirpath| table| dabicho
dirpath_id_seq | sequence | dabicho
genero | table| dabicho
genero_id_seq | sequence | dabicho
mp3| table| dabicho
mp3_id_seq | sequence | dabicho
pga_forms | table| dabicho
pga_layout | table| dabicho
pga_queries| table| dabicho
pga_reports| table| dabicho
pga_schema | table| dabicho
pga_scripts| table| dabicho
mp3=# \d mp3 (this describes a table (mp3)
Table "mp3"
Attribute | Type|Modifier
---+---+
id| integer | not null default nextval('"mp3_id_seq"'::text)
fk_dirpath_id | integer | not null
filename | character varying | not null
titulo| text | not null default 'unknown'
fk_artista_id | integer | not null default 1
fk_album_id | integer | not null default 1
comentario| text | not null default 'none'
year | integer | default 2001
genero| smallint | not null default 1
Indices: mp3_fk_dirpath_id_key,
mp3_pkey
with \pset you can set output format.
mp3=# \pset expanded
Expanded display is on.
mp3=# \d mp3
Table "mp3"
-[ RECORD 1 ]-
Attribute | id
Type | integer
Modifier | not null default nextval('"mp3_id_seq"'::text)
-[ RECORD 2 ]-
Attribute | fk_dirpath_id
Type | integer
Modifier | not null
-[ RECORD 3 ]-
there are many combinations
mp3=# \pset border 2
Border style is 2.
mp3=# \d mp3
Table "mp3"
+---+---++
| Attribute | Type|Modifier|
+---+---++
| id| integer | not null default nextval('"mp3_id_seq"'::text) |
| fk_dirpath_id | integer | not null |
| filename | character varying | not null |
| titulo| text | not null default 'unknown' |
| fk_artista_id | integer | not null default 1 |
| fk_album_id | integer | not null default 1 |
| comentario| text | not null default 'none'|
| year | integer | default 2001 |
| genero| smallint | not null default 1 |
+---+---++
Indices: mp3_fk_dirpath_id_key,
mp3_pkey
pretty much the same, and fairly human readable to me. (although not everything sorted
in columns, i guess you could do querys to the system tables to get that, or use awk
to get the bits you want =) )
I just this the postgres team has don an excelent work so far.
Reply.
> Any idea to get a human readable list with column descriptions like
> type,size,key,default,null.
> It would be nice if it would look simular to the mysql variant:
>
> mysql> describe employee;
> +---+--+--+-+-++
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-++
> | Id| int(11) | | PRI | NULL| auto_increment |
> | FirstName | char(30) | | MUL | ||
> | La
Re: [HACKERS] schema support, was Package support for Postgres
Gunnar =?iso-8859-1?q?R=F8nning?= <[EMAIL PROTECTED]> writes: > Hmm. But if we had schema support can't we just package those procedures > into a schema with a given name ? Maybe my stored procedures needs some other > resources as well that should not conflict with other packages, like temp > tables or such. It then seems to me that using schemas can solve everything > that packages do and more ? Yeah. I am wondering whether we couldn't support Oracle-style packages as a thin layer of syntactic sugar on top of schemas. I am concerned about the prospect that "foo.bar" might mean either "object bar in schema foo" or "object bar in package foo". regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Does "postmaster -i"...
> We do "optind = 1" > in SSDataBase, but maybe on your platform, we need to do more than that > to point getopt at the correct arglist. Any ideas? Ah ... I betcha your platform needs optreset = 1. Fix coming ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Does "postmaster -i"...
> Ah ... I betcha your platform needs optreset = 1. Fix coming ... I've just committed this. Please update and let me know if it helps. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Is there no "DESCRIBE ;" on PGSQL? help!!!
On Fri, 19 Oct 2001, Ron de Jong wrote: > Any idea to get a human readable list with column descriptions like > type,size,key,default,null. > It would be nice if it would look simular to the mysql variant: > > mysql> describe employee; > +---+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +---+--+--+-+-++ > | Id| int(11) | | PRI | NULL| auto_increment | > | FirstName | char(30) | | MUL | || > | LastName | char(30) | | | || > | Infix | char(10) | YES | | NULL|| > | Address1 | char(30) | YES | | NULL|| > | PostCode | char(10) | YES | | NULL|| > | Town | int(11) | YES | | NULL|| > +---+--+--+-+-++ Easily done -- look at the \d commands in psql or \h to get help in psql. This is a FAQ -- STFW. BTW, the -hackers list is for tricky questions requiring experienced developer help, or for discussion among the gurus. Please post general questions to pgsql-general or pgsql-novice and re-post to pgsql-hackers only if you get no response w/in a week. HTH, -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] ecpg - GRANT bug
Michael Meskes wrote: > On Tue, Oct 16, 2001 at 10:27:42AM +0100, Lee Kindness wrote: > > And the patch below corrects a pet peeve I have with ecpg, all errors > > and warnings are output with a line number one less than reality... > > I wish I knew where this comes from. I've been trying to track this bug down > for years now, but have yet to find the reason. Okay, didn't check for quite > some time now, but the first time I committed a fix was March 1998. But > somehow I still haven't found all problems it seems. I somewhat got the impression that using C++ style comments (//) are related to worse the problem. But I must confess I didn't dig deep enough to contribute anything substancial. Perhaps the problem is a misunderstanding of ecpg and cpp. I was confused by the blank lines following or preceding a #line statement every time I looked at it. This should be not necessary. While talking about warnings: ecpg warns about NULLIF being not implemented yet. But actually it works (for me). Christof ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Package support for Postgres
On Thu, 18 Oct 2001, Peter Eisentraut wrote: > Bill Studenmund writes: > > > Honestly, I do not understand why "global variables" have been such a sore > > point for you. > > My point is that the proposed "package support" introduces two features > that are a) independent, and b) already exist, at least in design. > Schemas are already planned as a namespace mechanism. Global variables in > PLs already exist in some PLs. Others can add it if they like. There > aren't any other features introduced by "package support" that I can see > or that you have explicitly pointed out. Then my explanations didn't click. Please let me try again. The main feature of package support is that it greatly facilitates developing large, complicated db applications. Like ones which require multiple full-time developers to develop. I think PostgreSQL has the internals to run these apps, and it should provide a developement environment to encourage them. That's what packages are about. I have never developed an application that large. But I have talked to our DBAs who have worked with such things in Oracle, and a few who have worked on (developed) such large applications. They all have agreed that something akin to packages is needed to make it work. The seperate namespaces (for function names and for variables) mean that different programmers don't have to coordinate the names of functions. Or that the names have to have some de-ambiguating prefix to make them different. All that has to happen is that different packages have different names. When you through in the idea of developers releasing libraries (packages) on the net, the minimality of coordination is even more important. The fact (for PostgreSQL i.e. this implimentation) that types and operators aren't namespaced off means that they effectively leak into the enclosing database (or schema when we have them) so that making and supporting new types can be the aim/result of the package. For comaprison with other languages, packages strike me as comparable to libraries (in C) or modules (say in Perl or Python). Neither libraries nor modules realy do anything that can't be achieved otherwise in the language. Yet they are a prefered method of developing code, especially reused code. When you're making a program/application, you don't need to concern yourself with (many) details about the code; you use the module and that's it. Likewise here, an application developer/integrator need only load a module, and then all the routines in it are available. You don't for instance have to worry if the routines have names which overlap ones you were using, or ones used worse yet by another set of routines you want to use. I think Jean-Michael's comments were right. While I'm not sure if things will be as overwhelming as he predicted, packages (even as implimented in my patch) will help people develop code libraries for PostgreSQL. And that will make PostgreSQL applications easier. Also, as I've come to understand what schemas are and aren't, I've realized that they can be readily leveraged to help with schema support. Schemas, at least according to the SQL92 spec I have looked at (I'd love to see a later spec), are namespaces only for tables and views (and character sets and a number of other things which PostreSQL doesn't support). They don't touch on functions. Sure, PostgreSQL could decide to do something with functions, but if we do, we're improvising, and I see no reason to improvise differently than other DBMSs have done. There may be one, but I don't see it. Also, as I understand schemas (which could be wrong), there is a difference in emphasis between schemas and packages. Schemas are a way to partition your database, so that different parts of an application see only a subsection of the whole database. You can have some parts only able to access one or another schema, while other parts can access multiple schemas. Packages however are designed to help you build the tools to make the applications work (providing toolchests of code for instance). It's like schemas are a more top-down design element, and packages are bottom-up. Where I see the interaction is that we want to have different schemas have schema-specific functions, we just have a package implicitly assosciated with each schema which contains the traditional functions and aggregates (and types and operators) of that schema. > So the two questions I ask myself are: > > 1. Are package namespaces "better" than schemas? The answer to that is > no, because schemas are more standard and more general. See above; I never said packages were better than schemas (nor worse). I said they were different parts of the puzzle. I think they are both important and valuable. > 2. Are global variables via packages "better" than the existing setups? > My answer to that is again no, because the existing setups respect > language conventions, maintain the separation of the backend and the > language handlers, and of cour
Re: [HACKERS] Snaptshot appears fine to me ...
> > Right now, from what I can tell, the snapshot looks great to me: > > postgresql# ls -lt > total 486 > drwxrwxrwx 15 pgsql pgsql 512 Oct 15 04:04 src > drwxrwxrwx 43 pgsql pgsql1024 Oct 15 04:04 contrib > drwxrwxrwx 4 pgsql pgsql 512 Oct 15 04:04 doc > drwxrwxrwx 2 pgsql pgsql 512 Oct 15 04:04 config > -rwxr-xr-x 1 pgsql pgsql 249153 Oct 14 04:00 configure > -rw-r--r-- 1 pgsql pgsql 35886 Oct 13 04:01 configure.in > -rw-r--r-- 1 pgsql pgsql 132689 Oct 13 04:01 HISTORY > -rw-r--r-- 1 pgsql pgsql 700 Oct 2 10:21 register.txt > -rw-r--r-- 1 pgsql pgsql 34643 Oct 1 13:46 INSTALL > -rw-r--r-- 1 pgsql pgsql3464 Sep 17 19:00 GNUmakefile.in > -rw-r--r-- 1 pgsql pgsql 566 Aug 26 18:28 aclocal.m4 > -rw-r--r-- 1 pgsql pgsql1928 May 10 21:46 README > -rw-r--r-- 1 pgsql pgsql1432 Feb 9 2001 Makefile > -rw-r--r-- 1 pgsql pgsql1189 Jan 24 2001 COPYRIGHT > > All fresh dates ... It is not the dates on the files. What does doc/TODO show. I ftp'ed from ftp.us.postgresql.org and got a September 13th date in the TODO file. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Compiling on Solaris with Sun compiler
Peter Eisentraut writes: > Lee Kindness writes: > > For your information I've attached the man page for the Sun C > > compiler, which explicitly lists the -h and -R flags. > I didn't read much farther than > acc (SPARC only) is not intended to be used directly on > Solaris 2.x. ;-) Touche, but the man page for the front-end (plain old cc) doesn't list options and only refers to the acc man page ;) Onto another Solaris compilation issue... After a simple './configure' on a stock Solaris 2.6 box the compilation of interfaces/ecpg/lib/execute.c fails due to the macro definition of 'gettext' to ''. This macro is invoked on the prototype of gettext() in libintl.h (included via locale.h). A './configure --enable-nls' is needed. To properly fix the problem either: 1. Don't include or use locale functions in execute.c unless --enable-locale has been specified. 2. In execute.c the include for locale.h whould be moved above that of postgres_fe.h 3. Replace '#define gettext' in c.h with something more unique (PG_gettext perhaps?) Regards, Lee Kindness. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Package support for Postgres
On Sat, 13 Oct 2001, Peter Eisentraut wrote:
> Bill Studenmund writes:
>
> > So what are packages? In Oracle, they are a feature which helps developers
> > make stored procedures and functions.
>
> I think you have restricted yourself too much to functions and procedures.
> A package could/should also be able to contain views, tables, and such.
I disagree. Views and tables are the purview of schemas, which as I
mentioned to Tom, strike me as being different from packages. Packages
basically are modules which make life easier for functions (and types and
aggregates and operators).
If we really want to make tables and views and triggers part of packages,
we can. My big concern is that it then makes pg_dump harder. I'll go into
that more below.
> > They provide a name space for functions local to the package,
>
> Namespacing is the task of schemas. I think of packages as a bunch of
> objects that can be addressed under a common name (think RPMs).
Regrettablely Oracle beat you to it with what "packages" are in terms of
Oracle, and I suspect also in the minds of many DBAs.
I also think that you and Tom have something different in mind about the
namespacing in packages. It is purely a convenience for the package
developer; whenever you want to use a function built into the database,
you _don't_ have to type "standard." everywhere. Think what a PITA it
would be to have to say "standard.abs(" instead of "abs(" in your
functions! I'm sorry if my explanation went abstract quickly & making that
unclear.
> But it seems like some of this work could be used to implement schema
> support.
I think the big boost this will have to schema support is that it shows
how to make a far-reaching change to PostgreSQL. :-) It's an internal
schema change and more, just as schema support will be.
> > session-specific package variables,
>
> I think this is assuming a little too much about how a PL might operate.
> Some PLs already support this in their own language-specific way, with or
> without packages. Thus, I don't think packages should touch this.
> Actually, I think you could easily set up session variables in the package
> initializer function.
I agree that some PLs might do things their own way and so package
variables won't be as useful. If these variables are not appropriate to a
PL, it can ignore them.
PL/pgSQL is a counter-example, though, showing that something needs to be
done. It is not set up to support global variables; each code block
generates its own namespace, and removes it on the way out. Thus I can
not see a clean way to add package global variables to say the
initialization routine - this routine's exit code would need to not
destroy the context. That strikes me as a mess.
> > The last component of a package are the functions usable for type
> > declarations. They are declared as:
> > BEFORE TYPE FUNCTION
> >
> > They are useful as the normal functions in a package are declared after
> > the types are declared, so that they can use a type newly-defined in a
> > package.
>
> I think it would make much more sense to allow the creation of objects in
> the CREATE PACKAGE command in any order. PostgreSQL has not so far had a
> concept of "functions suitable for type declarations" and we shouldn't add
> one.
I think you misread me slightly. BEFORE TYPE FUNCTION functions are
"usable" for type declarations, not "suitable" for them. Also, I didn't
say one key clause, "in this package". The main difference is when in the
creation of the package the functions are created; they get created before
the types, rather than after.
This concept is new to PostgreSQL because PostgreSQL has never before
chained creations together like this.
Thinking about it though it would be feasable to scan the list of types in
the package, and see if there are references to functions declared in that
package, and if so to create them before the types get declared. That
would remove the need for BEFORE TYPE FUNCTION and also make pg_dump a
little simpler.
Take care,
Bill
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PQstatus() detect change in connection...
> Matthew Hagerty <[EMAIL PROTECTED]> writes: > > but I also want to check the connection *before* submitting a query... If you mean directly before the query, then forget it, as Tom already said :-) > This strikes me as utterly pointless. You'll need to be able to recover > from query failure anyway, so what's the value of testing beforehand? > Send the query and see if it works or not. I see a value in checking connection status before you start doing loads of local work after a long idle time, that results in a query. In this situation I guess it is good enough to send an empty query even if it takes a little. In our projects we recv 0 bytes from the socket every x seconds during long idle periods to detect connection problems early. While it is not 100% reliable (since it does not transfer anything over the network) it does detect some common error situations. I am not 100% sure, but I think PQstatus could be patched to do that. Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Is there no "DESCRIBE ;" on PGSQL? help!!!
Any idea to get a human readable list with column descriptions liketype,size,key,default,null.It would be nice if it would look simular to the mysql variant:mysql> describe employee;+---+--+--+-+-++| Field | Type | Null | Key | Default | Extra |+---+--+--+-+-++| Id | int(11) | | PRI | NULL | auto_increment || FirstName | char(30) | | MUL | | || LastName | char(30) | | | | || Infix | char(10) | YES | | NULL | || Address1 | char(30) | YES | | NULL | || PostCode | char(10) | YES | | NULL | || Town | int(11) | YES | | NULL | |+---+--+--+-+-++ Cheers, Ron.
Re: [HACKERS] pg_sorttemp files
Thus spake Tom Lane > [EMAIL PROTECTED] (D'Arcy J.M. Cain) writes: > > I have tons of old files with names like base/db/pg_sorttemp.##. I > > assume that they are temporary sorting files but somehow they never got > > cleared out. Is it safe to delete these from a running system. The files > > are months old. > > The first is the PID of the backend that made them. If there is no > such backend anymore according to ps, it's safe to zap 'em. I'd rely on > that much more than the mod date. Thanks. I wasn't sure about that PID thing but I have now run a script that got rid of them all. > BTW, if you are seeing unreclaimed sorttemp files in a recent release > (7.0 or later), I'd like to know about it. That shouldn't happen, > short of a backend crash anyway... Well, I had over 6,000 of these files. This database is about a year old. I haven't seen all that many backend crashes in that time. I guess I better keep a close eye on them. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] schema support, was Package support for Postgres
* Bill Studenmund <[EMAIL PROTECTED]> wrote: | | Packages aren't schemas. What they bring to the table is they facilitate | making stored procedures (functions). You can have twelve different | developers working on twenty different packages, with no fear of name | conflicts. The package names will have to be different, so there can be | functions with the same names in different pacakges. Hmm. But if we had schema support can't we just package those procedures into a schema with a given name ? Maybe my stored procedures needs some other resources as well that should not conflict with other packages, like temp tables or such. It then seems to me that using schemas can solve everything that packages do and more ? | For the most part, I think packages and schemas are orthogonal. I'm taking | a cue from Oracle here. Oracle considers packages to be a schema-specific | object. What is really the difference functionality wise of making a subschema and package ? In both cases you deal with the namespace issues. -- Gunnar Rønning - [EMAIL PROTECTED] Senior Consultant, Polygnosis AS, http://www.polygnosis.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_sorttemp files
[EMAIL PROTECTED] (D'Arcy J.M. Cain) writes: >> BTW, if you are seeing unreclaimed sorttemp files in a recent release >> (7.0 or later), I'd like to know about it. That shouldn't happen, >> short of a backend crash anyway... > Well, I had over 6,000 of these files. This database is about a year old. > I haven't seen all that many backend crashes in that time. I guess I better > keep a close eye on them. Wow! Did you happen to note how many distinct PIDs were accounted for? That would give us some idea of how many lossage events there were. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Does "postmaster -i"...
... > All three of these cases work just fine for me. Maybe some platform > dependency has snuck in? Hard to see how though. It looks like the > failure is occurring when the postmaster launches the xlog startup > subprocess. The building of the argument list for that subprocess is > fixed and not dependent on what you give to the postmaster (see > SSDataBase in postmaster.c). No ideas at all; this stuff has always worked for me (and everyone else). Let's wait and see if anyone else notices a problem; in the meantime I have a workaround by giving it more than one argument per one of the examples. I *haven't* blown away my tree and done a build from scratch, but istm that is not something that would magically get things working. Hmm. I'll try a distclean and see if that helps; maybe configure has changed a bit and the cache is messing me up?? - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] schema support, was Package support for Postgres
On 19 Oct 2001, Gunnar [iso-8859-1] Rønning wrote: > * Bill Studenmund <[EMAIL PROTECTED]> wrote: > | > | Packages aren't schemas. What they bring to the table is they facilitate > | making stored procedures (functions). You can have twelve different > | developers working on twenty different packages, with no fear of name > | conflicts. The package names will have to be different, so there can be > | functions with the same names in different pacakges. > > Hmm. But if we had schema support can't we just package those procedures > into a schema with a given name ? Maybe my stored procedures needs some other > resources as well that should not conflict with other packages, like temp > tables or such. It then seems to me that using schemas can solve everything > that packages do and more ? Assuming that schema support covers functions (which Tom, I, evidently you, and Oracle think it should but which isn't mentioned at least in SQL92), you could do that. And if you're adding tables, you probably should. But a lot of times you don't need to go to the effort of namespacing off a whole new schema, and I can think of some cool things to do when you don't. One example is a large, complicated db app with multiple programmers. For each general area of the app, you can create a package. That way you modularize the code into more managable pieces. But since the are all in the same schema, they can maintain/interact with the same tables. So that's an arguement for packages/subschemas. > | For the most part, I think packages and schemas are orthogonal. I'm taking > | a cue from Oracle here. Oracle considers packages to be a schema-specific > | object. > > What is really the difference functionality wise of making a subschema and > package ? In both cases you deal with the namespace issues. A matter of what is subspaced. I'd assume that a subschema namespaces off everything a schema does. A package however only namespaces off functions and aggregates. Packages, at least as I've implimented them, do *not* namespace off types nor operators they contain. Technically, the package oid is a key in the name index for pg_proc and pg_aggregate, while it is not for pg_type and pg_operator. I admit, I took a minor liberty here. Oracle packages do have types, but Oracle types are not as rich as PostgreSQL's So when I was translating packages, I made the types in them match PostgreSQL's. Also, since I'd added aggregates and types, adding operators seemed like a reasonable thing. Both from the point of view of the parser (they are all done about the same way), and from the point of utility. PostgreSQL's ability to add types is really cool, and the ability to add operators makes new types convenient to use. If packages could add types and support functions but not operators, that'd seem lame. The reason that packages don't namespace off types and operators is I think it makes them more useful. Think about the complex number example in the programmer's guide. I can think of scientific applications which could use them. But having to say package.complex for the type would be combersome. And even worse, having to say package.+ or package.- would be bad. And package.* might be ambiguous to the parser! So that's why I made pacakges not be subschemas. Packages were designed to help with writing stored procedures, and to do it well. :-) Take care, Bill ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] schema support, was Package support for Postgres
On 19 Oct 2001, Gunnar [iso-8859-1] Rønning wrote: > * Tom Lane <[EMAIL PROTECTED]> wrote: > | > | Yeah. I am wondering whether we couldn't support Oracle-style packages > | as a thin layer of syntactic sugar on top of schemas. I am concerned > | about the prospect that "foo.bar" might mean either "object bar in > | schema foo" or "object bar in package foo". > > Agreed, and in Sybase you may declare a procedure in a schema(or > database which is the Sybase term). If you want it global you declare it > in the "master" schema. Oh cool. I knew that Oracle used "standard" for the name of the built-in package, but I didn't know a name for the built-in schema. "master" sounds good. Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] schema support, was Package support for Postgres
On Fri, 19 Oct 2001, Tom Lane wrote: > Yeah. I am wondering whether we couldn't support Oracle-style packages > as a thin layer of syntactic sugar on top of schemas. I am concerned > about the prospect that "foo.bar" might mean either "object bar in > schema foo" or "object bar in package foo". See my note to Gunnar for why I think packages should be inside of schemas rather than renamed schemas. Types and expecially operators would be much more useful to the enclosing schema that way (I think). Yes, there is an ambiguity between schema foo and package foo. I can think of a few ways to deal with this. 1) Do whatever Oracle does, assuming it's not grotesque. Yes, I've said that a lot. But I think PostgreSQL can really take some applications away from the commercial DBMSs, and Oracle is #1 in that market. So Oracle represents Prior Art of least surprise. :-) 2) If there is both a schema named foo and a package named foo, then foo.bar should always take foo to be the schema. If we let a package in the local schema named foo be found before the schema foo, then we would get different results in said schema and another one (which didn't have a package named foo in it). 3) Don't let schemas and packages have the same name. I actually believe this is what Oracle does, though I haven't checked. I _have_ checked that packages and tables can't have the same name, and built that into the packages patches. I think requiring schemas to have names different from tables and packages is a good thing, and would reduce ambiguity. As an aside the reason I suspect this is what Oracle does is that Oracle has a system table which contains a list of named objects. Tables and packages show up as entries in this table, and I'd expect schemas would too. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Error while restoring database
Hi, Version 7.1.3, Linux 2.2.18 Following procedure: 1. pg_dump dbname > outfile Everything is fine. 2. Recreating the database on another system (same Versions) psql dbname < infile I get once: ERROR: parser: parse error at or near "," The rest works fine. Debug -d2 shows that recreating an operator fails. There was never a problem creating this operator before and it worked fine. It just fails during restore. It seem the function numeric_neq, which is created later (after the second operator) is missing. So pg_dump doesn't seem to dump the functions before the operators. DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: CREATE FUNCTION "numeric_eq" (numeric,double precision) RETURNS b DEBUG: ProcessUtility: CREATE FUNCTION "numeric_eq" (numeric,double precision) DEBUG: query: select $1 = $2::numeric; DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: CREATE OPERATOR <> (PROCEDURE = numeric_neq , LEFTARG = numeric , RIGHTARG = double precision , COMMUTATOR = <> , NEGATOR = , RESTRICT = eqsel , JOIN = eqjoinsel ); ERROR: parser: parse error at or near "," DEBUG: AbortCurrentTransaction DEBUG: StartTransactionCommand DEBUG: query: CREATE OPERATOR = (PROCEDURE = numeric_eq , It's not real problem for me. I think it happened while playing with pgadmin, changing a function call in an operator. But still, shouldn't pg_dump look after it? Any ideas how to fix this? regards Johann Zuschlag [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] schema support, was Package support for Postgres
On Tue, 16 Oct 2001, Bill Studenmund wrote: > I still think that schemas and packages are different, but I now think > they are interrelated. And that it shouldn't be too hard to leverage the > package work into schema support. Still a lot of work, but the package > work has shown how to go from one to two in a number of ways. :-) > > First off, do you (Tom) have a spec for schema support? I think that would > definitly help things. I found an on-line copy of the SQL92 spec, and I've been looking at it. I think it wouldn't be _that_ much more work to add shema support to what I've done for packages. Not trivial, but certainly not double the work. But I have some questions. The big one for now is how should you log into one schema or another? psql database.schema ? Here's a plan for schema support. But first let me review what packages have. Right now (in my implimentation), packages have added a "standard" package (oid 10) which contains all of the built-in procedures, aggregates, types, and operators. Whenever you use the normal CREATE commands, you add a procedure, aggregate, operator, or type in the "standard" package. There is a new table, pg_package, which lists the name of each installed package and its owner. "standard" is owned by PGUID. packages are referenced by the oid of the row describing the package in this table. Whenever you look up a function or aggregate, you give the oid of the package to look in in addition to the name (and types). Having the package id in the index provides the namespacing. Whenever you look up a type or operator, you don't have to give a package id. Whenever you call the parser to parse a command, you pass it the package context (oid) in which the parsing takes place. If you are typing in commands in psql, that package id is 10, or "standard". Likewise for sql or plpgsql routines not in a package. If you are in an sql or plpgsql routine which is in a package, the package's oid is passed in. That's what has package routines look in the package first. The parser also notes if you gave a package id or not (package.foo vs foo). If you were in a package context and were not exact (foo in a procedure in a package for instance), then all of the places which look up functions will try "standard" if they don't find a match. There is a table, pg_packglobal, which contains package globals for the different PLs. It contains 5 columns. The first three are the package oid, the language oid, and a sequence number. They are indexed. The two others are variable name and variable type (of PostgreSQL type name and text respectively). PLs for which these variables don't make sense are free to ignore them. Extending this for schema support. Executive summary: all of the above becomes the infrastructure to let different schemas have schema-private functions and aggregates. We add a new table, pg_schema, which lists the schemas in this database. It would contain a name column, an owner column, something to indicate character set (?), and other stuff I don't know of. Schemas are referenced internally by the oid of the entry in this table. There is a built-in schema, "master". It will have a fixed oid, probalby 9 or 11. The "master" schema will own the "standard" package oid 10, which contains all of the built-in functions, and ones added by create function/etc. Each new schema starts life with a "standard" package of its own. This package is the one which holds functions & aggregates made with normal commands (create function, create aggregate) when you're logged into that schema. pg_package grows two more columns. One references the schema containing the package. The other contains the oid of the "parent" package. The idea is this oid is the next oid to look in when you are doing an inexact oid search. It's vaguely like ".." on a file system. For master.standard, this column is 0, indicating no further searching. For say foo.standard (foo is a schema), it would be the oid of master.standard (10). Likewise for a package baz in the master schema, it would be master.standard. For a package in a schema, it would be the oid of the "standard" package of the schema. As an example, say the foo schema had a package named bup. For baz.bup, this column would have the oid of baz.standard. Right now I'm in the process of redoing the parser changes I made so that the scanner doesn't need to recognize package names. When this is done, the parser will be able to deal with schema.function and package.function. Oh, also schema.table.attr too. schema.package.function won't be hard, but it will be messy. The only other part (which is no small one) is to add namespacing to the rest of the backend. I expect that will mean adding a schema column to pg_class, pg_type, and pg_operator. Hmmm... We probably also need a command to create operator classes, and the tables it touches would need a schema column too, and accesses will need to be schema savy. Well, that's a lot for now. Thoughts?
Re: [HACKERS] Error while restoring database
"Johann Zuschlag" <[EMAIL PROTECTED]> writes: > DEBUG: query: CREATE OPERATOR <> (PROCEDURE = numeric_neq , > LEFTARG = numeric , > RIGHTARG = double precision , > COMMUTATOR = <> , > NEGATOR = , > RESTRICT = eqsel , > JOIN = eqjoinsel ); > ERROR: parser: parse error at or near "," Hmm, so what happened to the NEGATOR link? You have not given us enough information to understand what, if anything, needs to be fixed ... do you still have the original database to look at? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] ecpg - GRANT bug
On Fri, Oct 19, 2001 at 09:37:59AM +0200, Christof Petig wrote: > I somewhat got the impression that using C++ style comments (//) are related > to worse the problem. But I must confess I didn't dig deep enough to contribute > anything substancial. Perhaps the problem is a misunderstanding of ecpg and > cpp. If I find some time I will dig into it, but that looks like a longshot right now. > While talking about warnings: ecpg warns about NULLIF being not implemented > yet. But actually it works (for me). Fixed. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] permissions problem at hub.org !
Marc, I've noticed (and not only me) significant slowdown of search at fts.postgresql.org. >From our logs: ... Sun Aug 19 03:32:00 EDT 2001 /usr/local/mailware/CORE/bin/vacuum_analyze: /usr/local/pgsql/bin/psql: No such file or directory ... Sun Oct 14 03:32:00 EDT 2001 /usr/local/mailware/CORE/bin/vacuum_analyze: /usr/local/pgsql/bin/psql: Permissi on denied Please, fix permissions asap ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Is there no "DESCRIBE ;" on PGSQL? help!!!
psql \d command. > Any idea to get a human readable list with column descriptions like > type,size,key,default,null. > It would be nice if it would look simular to the mysql variant: > > mysql> describe employee; > +---+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +---+--+--+-+-++ > | Id| int(11) | | PRI | NULL| auto_increment | > | FirstName | char(30) | | MUL | || > | LastName | char(30) | | | || > | Infix | char(10) | YES | | NULL|| > | Address1 | char(30) | YES | | NULL|| > | PostCode | char(10) | YES | | NULL|| > | Town | int(11) | YES | | NULL|| > +---+--+--+-+-++ > > Cheers, Ron. > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] compiling on Solaris 8 x86
Patch applied. Thanks. Patch attached. autoconf run.
---
> Hi!
>
> Without this patch I couldn't compile PostgreSQL on Solaris 8 x86 using
> Sun's compiler. May be it will be usefull for someone else?
>
> Regards
> Denis Ustimenko
> Oldham
>
> ---
> denis@tracer$ diff configure.orig configure
> 744c744
> < i?86-*-solaris) need_tas=yes; tas_file=solaris_i386.s ;;
> ---
> > i?86-*-solaris*) need_tas=yes; tas_file=solaris_i386.s ;;
>
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Index: configure.in
===
RCS file: /cvsroot/pgsql/configure.in,v
retrieving revision 1.145
diff -c -r1.145 configure.in
*** configure.in2001/10/13 04:23:50 1.145
--- configure.in2001/10/19 15:03:22
***
*** 116,122
case $host in
*-*-hpux*)need_tas=yes; tas_file=hpux.s ;;
sparc-*-solaris*) need_tas=yes; tas_file=solaris_sparc.s ;;
! i?86-*-solaris) need_tas=yes; tas_file=solaris_i386.s ;;
*)need_tas=no; tas_file=dummy.s ;;
esac
AC_LINK_FILES([src/backend/port/tas/${tas_file}], [src/backend/port/tas.s])
--- 116,122
case $host in
*-*-hpux*)need_tas=yes; tas_file=hpux.s ;;
sparc-*-solaris*) need_tas=yes; tas_file=solaris_sparc.s ;;
! i?86-*-solaris*) need_tas=yes; tas_file=solaris_i386.s ;;
*)need_tas=no; tas_file=dummy.s ;;
esac
AC_LINK_FILES([src/backend/port/tas/${tas_file}], [src/backend/port/tas.s])
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] schema support, was Package support for Postgres
* Tom Lane <[EMAIL PROTECTED]> wrote: | | > resources as well that should not conflict with other packages, like temp | > tables or such. It then seems to me that using schemas can solve everything | > that packages do and more ? | | Yeah. I am wondering whether we couldn't support Oracle-style packages | as a thin layer of syntactic sugar on top of schemas. I am concerned | about the prospect that "foo.bar" might mean either "object bar in | schema foo" or "object bar in package foo". Agreed, and in Sybase you may declare a procedure in a schema(or database which is the Sybase term). If you want it global you declare it in the "master" schema. -- Gunnar Rønning - [EMAIL PROTECTED] Senior Consultant, Polygnosis AS, http://www.polygnosis.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Catalogs design question
Yes, we inherited these arrays from Berkeley and haven't had any need to remove them. Are you trying to do things that the other interfaces like ODBC and JDBC don't handle? The group array is a hack but the pg_proc array would be hard to replace becauseit acts as part of the unique key used for cache lookups. --- > Hello all!! > > > I'm developer of a interface for PostgreSQL for the Borland Kylix > and Delphi tools (http://www.vitavoom.com). I've run into the following > problems with catalogs: > > - pg_group: the grolist field is an array. How can I make a query > that tell me the usernames of a group ? > - pg_proc: the proargtypes field is an array. How can I make a query > that will link those types to the pg_types catalog ??? > > This catalog design seems a very crude hack to make the things > working for me. Can't those relations be separated in another table ? Or > maybe a function that can search for a value in array, and make a wroking > reference for an array > element in a relation (something like "select typname from pg_type, pg_group > where oid > in grolist"). > I also quote the PotgreSQL user manual > (http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/arrays.html): > > "Tip: Arrays are not lists; using arrays in the manner described in the > previous paragraph is often a sign of database misdesign. The array field > should generally be split off into a separate table. Tables can obviously be > searched easily." > > Best Regards, > Steve Howe > > > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Detecting glibc getopt?
I have traced down the postmaster-option-processing failure that Thomas reported this morning. It appears to be specific to systems running glibc: the problem is that resetting optind to 1 is not enough to put glibc's getopt() subroutine into a good state to process a fresh set of options. (Internally it has a "nextchar" pointer that is still pointing at the old argv list, and only if the pointer points to a null character will it wake up enough to reexamine the argv pointer you give it.) The reason we see this now, and didn't see it before, is that I rearranged startup to set the ps process title as soon as possible after forking a subprocess --- and at least on Linux machines, that "nextchar" pointer is pointing into the argv array that's overwritten by init_ps_display. While I could revert that change, I don't want to. The idea was to be sure that a postmaster child running its authentication cycle could be identified, and I still think that's an important feature. So I want to find a way to make it work. Looking at the source code of glibc's getopt, it seems there are two ways to force a reset: * set __getopt_initialized to 0. I thought this was an ideal solution since configure could check for the presence of __getopt_initialized. Unfortunately it seems that glibc is built in such a way that that symbol isn't exported :-(, even though it looks global in the source. * set optind to 0, instead of the more usual 1. This will work, but it requires us to know that we're dealing with glibc getopt and not anyone else's getopt. I have thought of two ways to detect glibc getopt: one is to assume that if getopt_long() is available, we should set optind=0. The other is to try a runtime test in configure and see if it works to set optind=0. Runtime configure tests aren't very appealing, but I don't much care for equating HAVE_GETOPT_LONG to how we should reset optind, either. Opinions anyone? Better ideas? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Platform dependency in timestamp parsing
Using current sources, the following sequence: set DateStyle TO 'Postgres'; set TimeZone TO 'PST8PDT'; select '2001-09-22T18:19:20'::timestamp(2); produces timestamptz -- Sat Sep 22 11:19:20 2001 PDT on my HPUX box, and evidently also on your machine because that's what's in the timestamptz expected file. However, on a LinuxPPC machine I get timestamptz -- Sat Sep 22 18:19:20 2001 PDT ie, the value after 'T' is interpreted as local time not GMT time. Question 1: which behavior is correct per spec? I'd have expected local time myself, but I'm not sure where this is specified. Question 2: where to look for the reason for the difference in the code? I'm a tad surprised that the HP box behaves more like yours does than the LinuxPPC box ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Platform dependency in timestamp parsing
> Using current sources, the following sequence: > set DateStyle TO 'Postgres'; > set TimeZone TO 'PST8PDT'; > select '2001-09-22T18:19:20'::timestamp(2); > produces... (snip) ... > on my HPUX box, and evidently also on your machine because that's > what's in the timestamptz expected file. However, on a LinuxPPC > machine I get ... (snip) ... > ie, the value after 'T' is interpreted as local time not GMT time. > Question 1: which behavior is correct per spec? I'd have expected > local time myself, but I'm not sure where this is specified. It should be local time. > Question 2: where to look for the reason for the difference in the > code? I'm a tad surprised that the HP box behaves more like > yours does than the LinuxPPC box ... Me too :) It is a one line fix in datetime.c, on or about line 918. It needs a "tmask = 0;" for the new DTK_ISO_TIME case so that the "feature bitmask" is not altered by the "T" in the string. When it is altered, it thinks that a time zone was already specified, so does not try to determine one. Before: thomas=# select timestamp '2001-10-19T16:47'; 2001-10-19 09:47:00-07 After: thomas=# select timestamp '2001-10-19T16:47'; 2001-10-19 16:47:00-07 I have patches... - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Detecting glibc getopt?
Thomas Lockhart <[EMAIL PROTECTED]> writes: > (I still see the symptom btw; did a make distclean and configure after > updating my tree) Yeah, it's still busted; my first try was wrong. I have confirmed the "optind = 0" fix works on my LinuxPPC machine, but we need to decide how to autoconfigure that hack. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Platform dependency in timestamp parsing
I've applied patches; all regression tests pass and the 'yyy-mm-ddThh:mm:ss' is now handled correctly afaict. There is an ongoing issue regarding precision and rounding for cases with large interval spans. I've patched the tree with a possible solution involving counting significant figures before rounding, but I don't think it is the right one. Especially since it involves a log10() call :( - Thomas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Detecting glibc getopt?
(I still see the symptom btw; did a make distclean and configure after updating my tree) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Catalogs design question
Hello all!! I'm developer of a interface for PostgreSQL for the Borland Kylix and Delphi tools (http://www.vitavoom.com). I've run into the following problems with catalogs: - pg_group: the grolist field is an array. How can I make a query that tell me the usernames of a group ? - pg_proc: the proargtypes field is an array. How can I make a query that will link those types to the pg_types catalog ??? This catalog design seems a very crude hack to make the things working for me. Can't those relations be separated in another table ? Or maybe a function that can search for a value in array, and make a wroking reference for an array element in a relation (something like "select typname from pg_type, pg_group where oid in grolist"). I also quote the PotgreSQL user manual (http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/arrays.html): "Tip: Arrays are not lists; using arrays in the manner described in the previous paragraph is often a sign of database misdesign. The array field should generally be split off into a separate table. Tables can obviously be searched easily." Best Regards, Steve Howe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Does "postmaster -i"...
> ... work for you with code built from the cvs tip? I did an update and > build tonight and see A bit more information: an unadorned "-i" fails: myst$ postmaster -i postgres: invalid option -- r Usage: postgres -boot [-d] [-D datadir] [-F] [-o file] [-x num] dbname -d debug mode -D datadir data directory -F turn off fsync -o file send debug output to file -x num internal use DEBUG: startup process 7172 exited with status 1; aborting startup But no arguments succeeds: myst$ postmaster DEBUG: database system was shut down at 2001-10-19 13:38:20 UTC DEBUG: checkpoint record is at 0/1191A4 DEBUG: redo record is at 0/1191A4; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 98; next oid: 16557 DEBUG: database system is ready DEBUG: fast shutdown request DEBUG: shutting down DEBUG: database system is shut down And multiple arguments succeeds (without damaging the other arguments): myst$ postmaster -i -p 12000 DEBUG: database system was shut down at 2001-10-19 13:39:08 UTC DEBUG: checkpoint record is at 0/1191E4 DEBUG: redo record is at 0/1191E4; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 98; next oid: 16557 DEBUG: database system is ready DEBUG: fast shutdown request DEBUG: shutting down DEBUG: database system is shut down I've done a "make clean all install", and did not see this symptom earlier (I've been building and running quite often the last few days with updated cvs sources). - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] permissions problem at hub.org !
use /usr/local/bin/psql Vince. On Fri, 19 Oct 2001, Oleg Bartunov wrote: > Marc, > > I've noticed (and not only me) significant slowdown of search at > fts.postgresql.org. > > >From our logs: > > ... > Sun Aug 19 03:32:00 EDT 2001 > /usr/local/mailware/CORE/bin/vacuum_analyze: /usr/local/pgsql/bin/psql: No such > file or directory > ... > Sun Oct 14 03:32:00 EDT 2001 > /usr/local/mailware/CORE/bin/vacuum_analyze: /usr/local/pgsql/bin/psql: Permissi > on denied > > Please, fix permissions asap > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ODBC SQLBindParameter and UNICODE strings
Thanks for that. I'll have to work around this by extracting all character variable data and 'hard coding' this into the SQL statement before I SQLExecute() the statement. I had to do the same for ... Oracle (sorry for swearing). Do you (or anyone else for that matter) know if/when UNICODE binding will be implemented in the Psqlodbc driver? Andy. building the character data into the SQL statement for "Hiroshi Inoue" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Andy Hallam wrote: > > > > Apologies if you think this mail is a little long-winded but I want to be as > > clear as possible on this. > > > > PostgreSQL - 7.1.3 (installed on Linux 2.4.2-2) > > PSQLODBC.DLL - 07.01.0007 > > Visual C++ - 6.0 > > > > I have a C++ app running on WINDOWS2000 and I am trying to use > > SQLBindParamater with a unicode (wchar_t) variable. > > > > I installed postgreSQL using the following arguments: > > > > ./configure --enable-multibyte=UNICODE --enable-unicode-conversion --enable- > > odbc > > > > [snip] > > > > > As you can see I can succesfully use an ASCII character string for an INPUT > > parameter when binding but not a UNICODE character string. > > Surely PostgreSQL supports binding of UNICODE character strings ? > > Unfortunately no. Psqlodbc driver doesn't support UNICODE(UCS-2) > binding currently. --enable-multibyte=UNICODE means the sever side > support of UTF-8(not UCS-2) encoding. > > regards, > Hiroshi Inoue > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Does "postmaster -i"...
Thomas Lockhart <[EMAIL PROTECTED]> writes: > ... work for you with code built from the cvs tip? I did an update and > build tonight and see > myst$ postmaster -i > postgres: invalid option -- r I just rebuilt from cvs tip, and I don't see any such problem... anyone else? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] permissions problem at hub.org !
hasn't been in /usr/local/pgsql/bin since Aug 18th ... hub# ls -lt `which psql` -rwxr-xr-x 1 root wheel 108472 Aug 16 08:55 /usr/local/bin/psql On Fri, 19 Oct 2001, Oleg Bartunov wrote: > Marc, > > I've noticed (and not only me) significant slowdown of search at > fts.postgresql.org. > > From our logs: > > ... > Sun Aug 19 03:32:00 EDT 2001 > /usr/local/mailware/CORE/bin/vacuum_analyze: /usr/local/pgsql/bin/psql: No such > file or directory > ... > Sun Oct 14 03:32:00 EDT 2001 > /usr/local/mailware/CORE/bin/vacuum_analyze: /usr/local/pgsql/bin/psql: Permissi > on denied > > Please, fix permissions asap > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] permissions problem at hub.org !
On Fri, 19 Oct 2001, Vince Vielhaber wrote: > > use /usr/local/bin/psql ok. > > > Vince. > > On Fri, 19 Oct 2001, Oleg Bartunov wrote: > > > Marc, > > > > I've noticed (and not only me) significant slowdown of search at > > fts.postgresql.org. > > > > >From our logs: > > > > ... > > Sun Aug 19 03:32:00 EDT 2001 > > /usr/local/mailware/CORE/bin/vacuum_analyze: /usr/local/pgsql/bin/psql: No such > > file or directory > > ... > > Sun Oct 14 03:32:00 EDT 2001 > > /usr/local/mailware/CORE/bin/vacuum_analyze: /usr/local/pgsql/bin/psql: Permissi > > on denied > > > > Please, fix permissions asap > > > > > > ---(end of broadcast)--- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to [EMAIL PROTECTED] so that your > > message can get through to the mailing list cleanly > > > > > Vince. > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Does "postmaster -i"...
Thomas Lockhart <[EMAIL PROTECTED]> writes: > A bit more information: an unadorned "-i" fails: > myst$ postmaster -i > postgres: invalid option -- r > But no arguments succeeds: > myst$ postmaster > And multiple arguments succeeds (without damaging the other arguments): > myst$ postmaster -i -p 12000 All three of these cases work just fine for me. Maybe some platform dependency has snuck in? Hard to see how though. It looks like the failure is occurring when the postmaster launches the xlog startup subprocess. The building of the argument list for that subprocess is fixed and not dependent on what you give to the postmaster (see SSDataBase in postmaster.c). Hmm... I wonder if the argument list itself is good, and the parsing is what's broken. We're using getopt() for that, and there's an ugliness in that getopt has static state that has to be reset (since it's already been used once to parse the postmaster's arglist). We do "optind = 1" in SSDataBase, but maybe on your platform, we need to do more than that to point getopt at the correct arglist. Any ideas? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
