Re: [HACKERS] Package support for Postgres
>What do folks think? >Take care, >Bill Hello Bill, The community have been waiting for packages for a long time. I don't believe you did it!!! IMHO most applications do not fully benefit from the power of PostgreSQL because transactions are performed at application lever (PHP/asp/Java/Application server). Sometimes, libraries are mapped to database structure, which is nonsense when a simple view with left joins can solve a problem. Most applications should be developed/ported at PostgreSQL level using the full range of available tools (transactions, triggers, views, foreign keys, rules and off course PL/pgSQL). This is much easier and powerful. Then, all you need is to display information using a good object-oriented language (Java/PHP). With the help of packages, a lot of developers will probably release GPL libraries and PostgreSQL will become the #1 database in the world. At pgAdmin team, we were thinking of developing packages at client level. This is nonsense when reading your paper. The ability of defining context levels is a great feature. Question: how do you map package to PostgreSQL objects (tables, views, triggers)? Is there any possibility of defining templates? Can this be added to packages in the future with little impact on PostgreSQL internals? Now, we can only thank you for bringing Packages to PostgreSQL. Best regards, Jean-Michel POURE pgAdmin Team ---(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
[HACKERS] Warning of OID wraparound
Do we still need code to warn during VACUUM when you get near to OID wraparound? I know Tom has handled XID wraparound and has OID usage decreased. I have a patch to warn about OID wraparound but don't know if it is still desired. -- 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 4: Don't 'kill -9' the postmaster
[HACKERS] Recursive SQL functions
While looking to implement the ODBC replace() function (replace occurences of $2 in $1 by $3), I found that it could be expressed as: CREATE FUNCTION replace(text, text, text) RETURNS text AS ' select case when position($2 in $1) = 0 or char_length($2) = 0 then $1 else substring($1 from 1 for position($2 in $1) - 1) || $3 || replace(substring($1 from position($2 in $1) + char_length($2)), $2, $3) end; ' LANGUAGE SQL WITH (isstrict); Now this command doesn't actually work because it requires the replace() function to exist already. But it does work if one first creates a stub replace() function and then uses CREATE OR REPLACE. (So much about the claim that procedural languages are a security hole because they allow infinite loops.) I was wondering whether, as a future project, we could make this more convenient by parsing the body of the function with the binding of the function already in effect. Comments? -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Pre-forking backend
Bruce Momjian writes: > OK, let's assume we have pre-forked backends that do the accept(). One > enhancement would be for the child to connect to the last requested > database. If the accept() user wants the same database, it is already > connected, or at least its cache is loaded. If they want another one, > we can disconnect and connect to the database they request. This would > be portable for all OS's because there is no file descriptor passing. This is bad because you have hidden "connection pooling" that cannot be circumvented, and I guarantee that it will become a problem because "new connection" will no longer equal "new connection". Additionally, you're assuming a setup were any new connection will connect to a random (from the server's point of view) database. I claim these setups are not the majority. In fact, any one client application would usually only connect to exactly one database, so it might as well keep that connection open. For systems were this is not possible for some reason or where different databases or connection parameters are really required, there are already plenty of solutions available that are tuned or tunable to the situation at hand, so your solution would just get in the way. In short, you're adding a level of complexity where there is no problem. > Added to TODO: I haven't seen a consensus yet. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] EXTRACT broken
Thomas Lockhart writes: > Oh yeah. We don't have a date_part(units, time) function defined, so it > is getting converted to interval (which in other contexts *does* have > some usefulness as a "time equivalent"). You're going to have an extremely hard time convincing me of that. > We could fairly easily define a date_part() for the time and timetz data > types. I had figured that time would be cast to timestamp. Which is probably what it used to do. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Warning of OID wraparound
Bruce Momjian <[EMAIL PROTECTED]> writes: > Do we still need code to warn during VACUUM when you get near to OID > wraparound? I don't think so. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Package support for Postgres
Bill Studenmund <[EMAIL PROTECTED]> writes: > ... operators and types in > packages are in the same name space as are types and operators not in > packages. > For functions and aggregates, things are a little more complicated. First > off, there is a package called "standard" which contains all types, > aggregates, operators, and functions which aren't in a specific package. > This includes all of the standard Postgres routines, and anything created > with CREATE FUNCTION, CREATE AGGREGATE, CREATE OPERATOR, and CREATE TYPE. > Secondly, parsing is always done in terms of a specified package context. > If we are parsing an equation in a routine inside of a package, then the > package context is that package. If we are just typing along in psql, then > the package context is "standard". > When you specify a function or aggregate, you have two choices. One is to > specify a package, and a function in that package, like > "nametest.process" to specify the "process" function in the "nametest" > package. > 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). 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 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...) Also, both operators and functions normally go through ambiguity resolution based on the types of their inputs. How does the existence of a name search path affect this --- are candidates nearer the front of the search path preferred? Offhand I'm not sure if they should get any preference or not. I'd like to see schemas implemented per the spec in 7.3, so we need to coordinate all this stuff. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Monday beta?
Looks like Monday is our next beta target date. My mailbox is empty of outstanding patches except for an ecpg one I will apply tomorrow unless someone objects to it. There are some patches still being worked on, but there always will be. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TOAST and TEXT
> BTW, the postgres docs web pages says there is "no limitation" on row > size. Someone should probably update that with the info given in the > last few emails and probably integrate it in the regular doco as well. Although the field length is limited to 1GB, is there a row size limit? I don't know of one. The FAQ does say below the list: Of course, these are not actually unlimited, but limited to available disk space and memory/swap space. Performance may suffer when these values get unusually large. -- 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
Re: [HACKERS] How do I get the current time in seconds in the unix epoch?
Bill Studenmund <[EMAIL PROTECTED]> writes:
> In 7.1 I was able to get this (I thought) with
> date_part(''epoch'', timestamp ''now'') . That doesn't seem to work for me
> in last week's -current.
Indeed: in 7.1 I can do
test71=# select date_part('epoch', timestamp 'now');
date_part
1002946239
(1 row)
but current sources give
regression=# select date_part('epoch', timestamp 'now');
ERROR: Timestamp with time zone units 'epoch' not recognized
Thomas, I think you broke something.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] TOAST and TEXT
Bruce Momjian <[EMAIL PROTECTED]> writes: > Although the field length is limited to 1GB, is there a row size > limit? Sure. 1Gb per field (hard limit) times 1600 fields (also hard limit). In practice less, since TOAST pointers are 20bytes each at present, meaning you can't have more than BLCKSZ/20 toasted fields in one row. Whether this has anything to do with real applications is debatable, however. I find it hard to visualize a table design that needs several hundred columns that *all* need to be GB-sized. 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] optimizer question
Hannu Krosing wrote: > > Bruce Momjian wrote: > > > > > Hannu Krosing <[EMAIL PROTECTED]> writes: > > > > Maybe rather > > > > > > > * Use indexes for min() and max() or convert to "SELECT col FROM tab > > > > ORDER BY col DESC USING max_index_op LIMIT 1" if there is an index > > > > on tab that uses btree(col max_index_op) > > > > > > > it seems that in most other cases the rewrite would be either a > > > > misoptimisation or plain wrong. > > > > > > We would clearly need to add information to the system catalogs to allow > > > the planner to determine whether a given aggregate matches up to a given > > > index opclass. This has been discussed before. > > > > > > A more interesting question is how to determine whether such a rewrite > > > would be a win. That is NOT a foregone conclusion. Consider > > > > > > SELECT max(col1) FROM tab WHERE col2 BETWEEN 12 AND 42; > > > > > > Depending on the selectivity of the WHERE condition, we might be far > > > better off to scan on a col2 index and use our traditional max() > > > code than to scan on a col1 index until we find a row passing the > > > WHERE condition. I'm not sure whether the planner currently has > > > statistics appropriate for such estimates or not ... > > > > Yes, agreed. This would be just for limited cases. Updated to: > > > > * Use indexes for min() and max() or convert to SELECT col FROM tab ORDER > > BY col DESC LIMIT 1 if appropriate index exists and WHERE clause acceptible > > ^^^ > It would be probably a win if only exact match of > > SELECT MAX(*) FROM TAB ; > > would be rewritten if appropriate index exists. > > The appropriateness should be explicitly declared in aggregate > definition. I want to chime in here. If the ability exists to evaluate that max() or min() is appropriate, and that using the equivilent of "select select col1 from tab desc limit 1" for "select max(col1) from tab" would be a huge gain for Postgres. I know our Oracle8i can't do it, and it would be a very usefull optimization. At issue is the the "limit" clause is very very cool and not available in Oracle, and since it isn't available, one does not think to use it, and in queries where they my execute on both Postgres AND oracle, you can't use it. ---(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] pg_client_encoding
Tatsuo Ishii writes: > encoding what pg_client_encoding/alias > getdatabaseencoding > returns > > ASCII SQL_ASCII > UTF-8 UNICODE UTF_8 > MULE-INTERNAL MULE_INTERNAL > ISO-8859-1LATIN1 ISO_8859_1 > ISO-8859-2LATIN2 ISO_8859_2 > ISO-8859-3LATIN3 ISO_8859_3 > ISO-8859-4LATIN4 ISO_8859_4 > ISO-8859-5ISO_8859_5 > ISO-8859-6ISO_8859_6 > ISO-8859-7ISO_8859_7 > ISO-8859-8ISO_8859_8 > ISO-8859-9LATIN5 ISO_8859_9 > ISO-8859-10 ISO_8859_10 LATIN6 > ISO-8859-13 ISO_8859_13 LATIN7 > ISO-8859-14 ISO_8859_14 LATIN8 > ISO-8859-15 ISO_8859_15 LATIN9 > ISO-8859-16 ISO_8859_16 Why aren't you using LATINx for (some of) these as well? -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] FAQ error
Bruce Momjian writes:
> OK, new FAQ code is:
>
> $sql = "SELECT nextval('person_id_seq')";
> $newSerialID = ($conn->selectrow_array($sql))[0];
> INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
> $res = $dbh->do($sql);
This code is still incorrect for any known programming language and it's
even less clear to a person that doesn't know the programming language
it's probably trying to imitate.
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] EXTRACT broken
Peter Eisentraut <[EMAIL PROTECTED]> writes: > I had figured that time would be cast to timestamp. How would you do that? With no date available, you're short all the high-order bits ... 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] EXTRACT broken
Tom Lane writes: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > I had figured that time would be cast to timestamp. > > How would you do that? With no date available, you're short all the > high-order bits ... For the purpose of extracting the fields that time does provide, namely hour, minute, and second, it wouldn't matter. At least it gives me a much better feeling than casting to interval, which is a completely different kind of quantity. Of course, a separate date_part for time and date would make the most sense. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Package support for Postgres
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. > 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). But it seems like some of this work could be used to implement schema support. > 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. > 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. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] FAQ error
> Bruce Momjian writes:
>
> > OK, new FAQ code is:
> >
> > $sql = "SELECT nextval('person_id_seq')";
> > $newSerialID = ($conn->selectrow_array($sql))[0];
> > INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
> > $res = $dbh->do($sql);
>
> This code is still incorrect for any known programming language and it's
> even less clear to a person that doesn't know the programming language
> it's probably trying to imitate.
OK, what suggestions do you have?
--
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
Re: [HACKERS] Pre-forking backend
> Bruce Momjian writes: > > > OK, let's assume we have pre-forked backends that do the accept(). One > > enhancement would be for the child to connect to the last requested > > database. If the accept() user wants the same database, it is already > > connected, or at least its cache is loaded. If they want another one, > > we can disconnect and connect to the database they request. This would > > be portable for all OS's because there is no file descriptor passing. > > This is bad because you have hidden "connection pooling" that cannot be > circumvented, and I guarantee that it will become a problem because "new > connection" will no longer equal "new connection". Additionally, you're > assuming a setup were any new connection will connect to a random (from > the server's point of view) database. I claim these setups are not the > majority. In fact, any one client application would usually only connect > to exactly one database, so it might as well keep that connection open. > For systems were this is not possible for some reason or where different > databases or connection parameters are really required, there are already > plenty of solutions available that are tuned or tunable to the situation > at hand, so your solution would just get in the way. In short, you're > adding a level of complexity where there is no problem. Of course, there needs more work on the item. My assumption is that GUC would control this and that perhaps X requests for the same database would have to occur before such pre-loading would start. Another idea is to somehow pass the requested database name before the accept() so you could have multiple database ready to go and have the proper backend do the accept(). I realize this is all pie-in-the-sky but I think we need some connection pooling capability in the backend someday. We are fine with Apache and PHP becuase they can pool themselves but at some point we have too many clients reinventing the wheel rather than having our backend do it. Also, this relates to pre-forking backends and does not related to re-using backends, which is another nice feature we should have someday. > > Added to TODO: > > I haven't seen a consensus yet. True. I can remove it or improve it. It is actually: * Have pre-forked backend pre-connect to last requested database or pass file descriptor to backend pre-forked for matching database which mentions passing file descriptors to backends, which we have discussed and should be recorded for posterity. -- 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] Recursive SQL functions
Peter Eisentraut <[EMAIL PROTECTED]> writes: > I was wondering whether, as a future project, we could make this more > convenient by parsing the body of the function with the binding of the > function already in effect. Seems like a simple rearrangement of the code. First insert the pg_proc entry, then CommandCounterIncrement, then do the parsing/checking of the function body. Given the CCI, the new entry will be visible for the checking --- and if we error out, it rolls back just fine anyway. 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] pg_client_encoding
> > ASCII SQL_ASCII > > UTF-8 UNICODE UTF_8 > > MULE-INTERNAL MULE_INTERNAL > > ISO-8859-1 LATIN1 ISO_8859_1 > > ISO-8859-2 LATIN2 ISO_8859_2 > > ISO-8859-3 LATIN3 ISO_8859_3 > > ISO-8859-4 LATIN4 ISO_8859_4 > > ISO-8859-5 ISO_8859_5 > > ISO-8859-6 ISO_8859_6 > > ISO-8859-7 ISO_8859_7 > > ISO-8859-8 ISO_8859_8 > > ISO-8859-9 LATIN5 ISO_8859_9 > > ISO-8859-10 ISO_8859_10 LATIN6 > > ISO-8859-13 ISO_8859_13 LATIN7 > > ISO-8859-14 ISO_8859_14 LATIN8 > > ISO-8859-15 ISO_8859_15 LATIN9 > > ISO-8859-16 ISO_8859_16 > > Why aren't you using LATINx for (some of) these as well? If LATIN6 to 9 are well defined in the SQL or some other standards, I would not object using them. I just don't have enough confidence. For ISO-8859-5 to 8, and 16, I don't see well defined standards. -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
