[HACKERS] using a function on result of subselect
I have the following problem I want to use using a function on result of subselect: I create the following function: hannu=# create or replace function pg_fields(pg_user) returns text as ' hannu'# tup = args[0] hannu'# return tup[usename] + : + str(tup[usesysid]) hannu'# ' LANGUAGE 'plpython'; CREATE FUNCTION And it runs fine straight on table/view: hannu=# select pg_fields(pg_user) from pg_user; pg_fields postgres:1 hannu:100 (2 rows) But I am unable to run it on a subselect, whatever I do: hannu=# hannu=# select pg_fields(pg_user) from (select * from pg_user) as pg_user; ERROR: Cannot pass result of sub-select or join pg_user to a function hannu=# hannu=# select pg_fields(pg_user) from (select pg_user from pg_user) as pg_user; ERROR: You can't use relation names alone in the target list, try relation.*. hannu=# select pg_fields(pg_user) from (select pg_user.* from pg_user) as pg_user; ERROR: Cannot pass result of sub-select or join pg_user to a function I there a way to: a) tell PostgreSQL that the funtion can take any row type as an argument or b) to cast the result of subquery to a known row type -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] dynamic sql with parameters in interactive queries
hi postgresql supports dynamic sql with parameters in SQL function bodies, but not in interactive queries. why? when i wrote a dynamic sql with parameters, ODBC just filled the values of parameters into query string and sent it to server as a static query string. i think it's not right solution to dynamic sql with parameters. thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PQnotifies() in 7.3 broken?
Jeroen T. Vermeulen writes: On Mon, Dec 16, 2002 at 05:41:06PM +0100, Jeroen T. Vermeulen wrote: Speaking of which, what if user relies on sizeof(PGnotify::relname)? ^ code Yes, a change in the size of relname makes this binary incompatible and the user code changes may not be just a simple recompile - It all depends on what it being used for! Lee. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Suggestion; WITH VACUUM option
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: How hard would it be to add a WITH (VACUUM) option to UPDATE and DELETE queries? This option would cause the regular vacuum activity -- purging the dead tuple and its index references -- to be done immediately, as part of the statement, instead of being deferred. Easy? Hard? Insane? What do you think? Impossible. You can't vacuum a tuple until the last open transaction that can see it is gone. It is therefore *impossible* for a transaction to vacuum away its own detritus; until the transaction commits, you can't even start to wonder whether other open transactions see it or not. Vacuuming has to be done later, and that being the case, I don't see any real advantage to altering the background vacuum design we have. This does raise an interresting question, and I understand that it is *impossible* to do with PostgreSQL as it currently exists, however, let me just toss this out there: Suppose you do this: update largetable set foo=bar; Lets also assume that largetable has tens of millions of rows. I have databases like this, and I sometimes do operations like this. I have found it more efficient to break up the update into a series of: update largetable set foo=bar where somefield a; vacuum update largetable set foo=bar where somefield b; vacuum update largetable set foo=bar where somefield c; vacuum update largetable set foo=bar where not foo = bar; vacuum On some of my databases a statement which updates all the rows is unworkable in PostgreSQL, on Oracle, however, there is no poblem. For my use, it is a pain in the neck to deal with, but not unworkable. For some other users, it may be a bigger problem. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Password security question
Christopher Kings-Lynne wrote: Hi guys, Just a thought - do we explicitly wipe password strings from RAM after using them? I just read an article (by MS in fact) that illustrates a cute problem. Imagine you memset the password to zeros after using it. There is a good chance that the compiler will simply remove the memset from the object code as it will seem like it can be optimised away... Just wondering... Chris Could you post that link? That seems wrong, an explicit memset certainly changes the operation of the code, and thus should not be optimized away. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Password security question
On Tue, 2002-12-17 at 10:49, mlw wrote: Christopher Kings-Lynne wrote: Hi guys, Just a thought - do we explicitly wipe password strings from RAM after using them? I just read an article (by MS in fact) that illustrates a cute problem. Imagine you memset the password to zeros after using it. There is a good chance that the compiler will simply remove the memset from the object code as it will seem like it can be optimised away... Just wondering... Chris Could you post that link? That seems wrong, an explicit memset certainly changes the operation of the code, and thus should not be optimized away. I'd like to see the link too. I can imagine that it would be possible for it to optimize it away if there wasn't an additional read/write access which followed. In other words, why do what is more or less a no-op if it's never accessed again. -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Big 7.4 items
So if this gets added to the 7.3 branch, will there be documentation accompanying it? -tfo In article [EMAIL PROTECTED], [EMAIL PROTECTED] (Bruce Momjian) wrote: OK, I just talked to Patrick on the phone, and he says Neil Conway is working on merging the code into 7.3, and adding missing pieces like logging table creation. So, it seems PITR is moving forward. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Password security question
Greg Copeland wrote: On Tue, 2002-12-17 at 10:49, mlw wrote: Christopher Kings-Lynne wrote: Hi guys, Just a thought - do we explicitly wipe password strings from RAM after using them? I just read an article (by MS in fact) that illustrates a cute problem. Imagine you memset the password to zeros after using it. There is a good chance that the compiler will simply remove the memset from the object code as it will seem like it can be optimised away... Just wondering... Chris Could you post that link? That seems wrong, an explicit memset certainly changes the operation of the code, and thus should not be optimized away. I'd like to see the link too. I can imagine that it would be possible for it to optimize it away if there wasn't an additional read/write access which followed. In other words, why do what is more or less a no-op if it's never accessed again. It has been my experience that the MSC optimizer uses a patented Heisenberg optimizer. :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Update on replication
I just got my copy of SysAdmin Magazine and was surprised to see an article about Usogres -- The PostgreSQL Replication Tool. I don't remember seeing it mentioned on this or the General list. Though I just started reading the article and don't have a firm grasp on it yet, I do remember a discussion of replication using this technique - described in the first two paragraphs. Fyi, Rod -- Open Source Software - Sometimes you get more than you paid for... ---(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] Password security question
http://msdn.microsoft.com/library/en-us/dncode/html/secure10102002.asp ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Coerce to Domain
As suggested, I intend to create a 'CoerceToDomain' node in the expression tree rather than attempting to apply the create the constraints tests immediately. 1. Create a new function in the executor to handle domain coercions: ExecEvalCoerceToDomain() 2. Move coerce_type_constraints to the executor (builds expression tree of constraints) under the name ExecCoerceTypeConstraints(). 3. On initial pass, CoerceToDomain will have a 'raw' expression tree (simple arg of data to coerce). After passing through ExecCoerceTypeConstraints a 'cooked' expression tree will contain the constraint tests. Subsequent tuples will simply use the pre-cooked tree. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[HACKERS] disabled, deferred triggers
Folks, As you may know, we don't check the tgenabled status of deferred triggers (e.g. AFTER triggers) -- they are added to the deferred queue and executed regardless. This is pretty clearly a bug, but when it's been mentioned before, there were three possible fixes suggested: (1) check tgenabled when the trigger is fired (and we're considering adding it to the deferred event queue) (2) check tgenabled when the deferred event is activated (3) check tgenabled at both times -- if #1 AND #2 is true, actually invoke the deferred trigger I think we should implement (1), for the following reasons: - it's simpler: KISS, if nothing else. Adding it to the deferred queue and then delaying the is it enabled? decision to a later point in time adds complexity for no gain in useful functionality. - less functional: in theory, someone could twiddle the tgenabled bit for individual firings of the trigger, to only add some of the firings of the trigger to the event queue. At the least with the current implementation of deferred triggers, this wouldn't be possible AFAICS. Any comments? If no has a problem with (1), I'll send in a patch implementing it to -patches. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(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] Big 7.4 items
I meant he is merging it into HEAD, not the 7.3 CVS. Sorry for the confusion. --- Thomas O'Connell wrote: So if this gets added to the 7.3 branch, will there be documentation accompanying it? -tfo In article [EMAIL PROTECTED], [EMAIL PROTECTED] (Bruce Momjian) wrote: OK, I just talked to Patrick on the phone, and he says Neil Conway is working on merging the code into 7.3, and adding missing pieces like logging table creation. So, it seems PITR is moving forward. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Coerce to Domain
Rod Taylor [EMAIL PROTECTED] writes: 3. On initial pass, CoerceToDomain will have a 'raw' expression tree (simple arg of data to coerce). After passing through ExecCoerceTypeConstraints a 'cooked' expression tree will contain the constraint tests. Uh ... why? The cooked tree should be stored in pg_constraint, no? What's the point of redoing the parse analysis phase? BTW, this should be a lot easier to do cleanly (ie, without memory leaks) now that there's a notion of expression state trees. You can attach the information loaded from pg_constraint to the expression state node for the CoerceToDomain node (being careful to copy it into estate-es_query_cxt). Maybe that was already apparent to you ... 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: [HACKERS] Coerce to Domain
On Tue, 2002-12-17 at 18:15, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: 3. On initial pass, CoerceToDomain will have a 'raw' expression tree (simple arg of data to coerce). After passing through ExecCoerceTypeConstraints a 'cooked' expression tree will contain the constraint tests. Uh ... why? The cooked tree should be stored in pg_constraint, no? What's the point of redoing the parse analysis phase? Ok.. Cooked was the wrong word. The individual constraints will not be retested, but the group as a whole will be prepared (discovered?, looked up?). node for the CoerceToDomain node (being careful to copy it into estate-es_query_cxt). Maybe that was already apparent to you ... Somewhat apparent given your recent commits. I've not looked to find out exactly what it is yet, but I'm guessing recent changes to EvalPlanQual() will tell me. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Update on replication
On Tue, 17 Dec 2002, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: On Tue, 17 Dec 2002, Alvaro Herrera wrote: What about asynchronous (triggered?) replication? Is something like rserv or dbmirror going to be moved to main? From what I've been able to tell *so far*, Postgres-R is going to preclude the ability for either to work ... Why do you say that? If it can't coexist with other solutions, then it surely will not be accepted, but I can't think of any reason why it would preclude other approaches. Okay, if this is the case, that does change things somewhat, but Bruce seems to indiate that co-existance will be a problem: On Tue, 17 Dec 2002, Bruce Momjian wrote: The other concern is how does integrating Postgres-R affect the ability to investigate other solutions? As I said, I don't doubt taht there are aspects of Postgres-R that would benefit the server as a whole, and those bits-n-pieces should be looked at on an individual basis, but to just slap it in completely and hope that it doesn't cause problems for alternative solutions is kinda irresponsible ... It certainly will cause problems with other replication solutions. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Coerce to Domain
Rod Taylor [EMAIL PROTECTED] writes: Somewhat apparent given your recent commits. I've not looked to find out exactly what it is yet, but I'm guessing recent changes to EvalPlanQual() will tell me. Don't look at EvalPlanQual() ... you'll just get confused ;-). It's a mess, and not related to constraints. The code around init_fcache and ExecEvalFunc might be more enlightening for your immediate purposes. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Password security question
On Tue, 2002-12-17 at 11:11, Ken Hirsch wrote: http://msdn.microsoft.com/library/en-us/dncode/html/secure10102002.asp ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Thanks. Seems I hit the nail on the head. ;) -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Update on replication
Update on replication: We have several things happening with Postgres-R replication: o Someone is porting the 7.2-based Postgres-R code to 7.3 o Darren and I are in discussion with the Spread folks, attempting to get a more BSD-friendly license from them o People are evaluating the Postgres-R approach and comparing it to more traditional 2-phase commit replication. With these things moving forward, we will be in a much better position to get synchronous replication integrated in PostgreSQL. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Update on replication
I just got my copy of SysAdmin Magazine and was surprised to see an article about Usogres -- The PostgreSQL Replication Tool. I don't remember seeing it mentioned on this or the General list. Though I just started reading the article and don't have a firm grasp on it yet, I do remember a discussion of replication using this technique - described in the first two paragraphs. Glad to hear that. Usogres was developed in Japan and pretty popular ammong Japanese PostgreSQL community. BTW, there is a commercial product called QueryMaster, which takes similar approach to Usogres. It copies the input query and distribute to multiple PostgreSQL servers. As long as one of a server is working, users even do not notice some of them are failing. -- Tatsuo Ishii ---(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] Update on replication
On Wed, 18 Dec 2002, Tatsuo Ishii wrote: I just got my copy of SysAdmin Magazine and was surprised to see an article about Usogres -- The PostgreSQL Replication Tool. I don't remember seeing it mentioned on this or the General list. Though I just started reading the article and don't have a firm grasp on it yet, I do remember a discussion of replication using this technique - described in the first two paragraphs. Glad to hear that. Usogres was developed in Japan and pretty popular ammong Japanese PostgreSQL community. BTW, there is a commercial product called QueryMaster, which takes similar approach to Usogres. It copies the input query and distribute to multiple PostgreSQL servers. As long as one of a server is working, users even do not notice some of them are failing. How come these solutions are such well kept secrets? I've heard of neither in relation to past discussions about replication, or have I just missed them? :( ---(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] Update on replication
On Tue, 17 Dec 2002, Greg Copeland wrote: There are a couple of links to it from PostgreSQL's site, but you sorta have to look and hunt around. I've requested higher visibility but for whatever reason it seemed to be snubbed rather quickly. I do agree, GBorg needs MUCH higher visibility! Dave is pretty much ready to put teh portal in place (http://wwwdevel.postgresql.org) which has several links to GBorg right off the main page ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Update on replication
There are a couple of links to it from PostgreSQL's site, but you sorta have to look and hunt around. I've requested higher visibility but for whatever reason it seemed to be snubbed rather quickly. I do agree, GBorg needs MUCH higher visibility! Yes - I would love to move phpPgAdmin back to GBorg to attract some actual interested developers, but I'm not sure it would... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Update on replication
o People are evaluating the Postgres-R approach and comparing it to more traditional 2-phase commit replication. Not that the Postgres-R approach can replace 2-phase commit methods. 2PC is still needed for support with external transaction managers (XA drivers for JDBC). -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Update on replication
On Tue, 2002-12-17 at 21:33, Greg Copeland wrote: I do agree, GBorg needs MUCH higher visibility! I'm just curious: why do we need GBorg at all? Does it offer anything that SourceForge, or a similar service does not offer? Especially given that (a) most other OSS projects don't have a site for related projects (unless you count something like CPAN, which is totally different) (b) GBorg is completely unknown to anyone outside the PostgreSQL community and even to many people within it... Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Update on replication
On Tue, 2002-12-17 at 20:55, Neil Conway wrote: On Tue, 2002-12-17 at 21:33, Greg Copeland wrote: I do agree, GBorg needs MUCH higher visibility! I'm just curious: why do we need GBorg at all? Does it offer anything that SourceForge, or a similar service does not offer? Especially given that (a) most other OSS projects don't have a site for related projects (unless you count something like CPAN, which is totally different) (b) GBorg is completely unknown to anyone outside the PostgreSQL community and even to many people within it... Part I can answer, part I can not. Since I'm not the one that pushed the projects to that site, I can't answer that part of the equation. Addressing the part of your question that I think I can, I do like the concept of one-stop-shopping for all PostgreSQL needs. All Things ProgreSQL is a pretty neat concept. Of course, it rather defeats the whole purpose if no one, including potential developers, have no idea it exists. -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Update on replication
On Tue, Dec 17, 2002 at 12:56:45PM -0500, Bruce Momjian wrote: Update on replication: We have several things happening with Postgres-R replication: o Someone is porting the 7.2-based Postgres-R code to 7.3 You mean 7.4devel? With these things moving forward, we will be in a much better position to get synchronous replication integrated in PostgreSQL. What about asynchronous (triggered?) replication? Is something like rserv or dbmirror going to be moved to main? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Porque francamente, si para saber manejarse a uno mismo hubiera que rendir examen... ¿Quién es el machito que tendría carnet? (Mafalda) ---(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] Update on replication
Alvaro Herrera wrote: On Tue, Dec 17, 2002 at 12:56:45PM -0500, Bruce Momjian wrote: Update on replication: We have several things happening with Postgres-R replication: o Someone is porting the 7.2-based Postgres-R code to 7.3 You mean 7.4devel? Sorry, right. With these things moving forward, we will be in a much better position to get synchronous replication integrated in PostgreSQL. What about asynchronous (triggered?) replication? Is something like rserv or dbmirror going to be moved to main? I think eventually we will have some async replication in the main server, probably using PITR logs in some way. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Password security question
Ken Hirsch wrote: http://msdn.microsoft.com/library/en-us/dncode/html/secure10102002.asp Well, OK, that isn't as bizarre as one could have expected. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] malloc in xlog.c
I see a few malloc's in backend/access/transam/xlog.c that don't check to see if malloc returns NULL/failure. I think there should be at least an Assert() in there. Also, seems we use malloc() a few other places where palloc should be used, like variable.c. Is that correct? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] MySQL 4.1 Features
Looks like they've caught up on a lot of our features. I have to say I appreciate them adding SERIAL as an alias for AUTO_INCREMENT. Perhaps we should return the favour? :) (BTW, sorry to ppl who aren't interested, but I think it's important to see what other db's are doing. Also, someone asked me what kind of subselects they support.) The fact that they have GIS, subselects and prepared statements now is interesting. D.1.1 Changes in release 4.1.0 Faster binary protocol used with prepared statements. In CREATE TABLE foo (a int not null primary key) the PRIMARY word is now optional. In CREATE TABLE the attribute SERIAL is now an alias for BIGINT NOT NULL AUTO_INCREMENT UNIQUE. SELECT ... FROM DUAL is an alias for SELECT (To be compatible with some other databases). If once creates a too long CHAR/VARCHAR it's now automatically changed to TEXT or BLOB; One will get a warning in this case. One can specify the different BLOB/TEXT types with the syntax BLOB(length) and TEXT(length). MySQL will automatically change it to one of the internal BLOB/TEXT types. CHAR BYTE is an alias for CHAR BINARY. VARCHARACTER is an alias for VARCHAR. New operators integer MOD integer and integer DIV integer. SERIAL DEFAULT VALUE is an alias for AUTO_INCREMENT. TRUE and FALSE are added as alias for 0 and 1. Aliases are now forced in derived tables, as per SQL-99 Fixed SELECT .. LIMIT 0 to return proper row count for SQL_CALC_FOUND_ROWS. One can specify many temporary directories to be used in a round-robin fashion with: --tmpdir=dirname1:dirname2:dirname3. Subqueries: SELECT * from t1 where t1.a=(SELECT t2.b FROM t2). Derived tables: SELECT a from t1, (select * from t2) WHERE t1.a=t2.a Character sets to be defined per column, table and database. Unicode (UTF8) support. BTREE index on HEAP tables. Faster embedded server. One can add a comment per column in CREATE TABLE. SHOW FULL COLUMNS FROM table_name shows column comments. ALTER DATABASE. Support for GIS (Geometrical data). SHOW WARNINGS; Shows warnings from the last command. One can specify a column type for a colum in CREATE TABLE ... SELECT by defining the column in the CREATE part. CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar; expr SOUNDS LIKE expr same as SOUNDEX(expr)=SOUNDEX(expr). VARIANCE(expr) returns the standard variance of expr Chris ---(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] Suggestion; WITH VACUUM option
On Tue, 17 Dec 2002, mlw wrote: update largetable set foo=bar; Lets also assume that largetable has tens of millions of rows. [..] On some of my databases a statement which updates all the rows is unworkable in PostgreSQL, on Oracle, however, there is no poblem. .. provided you have a lot of rollback space, which is essentially what the datafile growth here is providing. Matthew. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] malloc in xlog.c
Bruce Momjian [EMAIL PROTECTED] writes: I see a few malloc's in backend/access/transam/xlog.c that don't check to see if malloc returns NULL/failure. I think there should be at least an Assert() in there. It'll dump core just fine without the help of an Assert ;-). I don't see that an Assert adds much of anything. In practice those mallocs are done during startup, so it seems very unlikely that they could fail, anyway. Also, seems we use malloc() a few other places where palloc should be used, like variable.c. Is that correct? Those are correct because of guc.c's conventions. Note the comments. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] python interface
I think the python interface in /interfaces/python should be moved to gborg. It already has its own web site: http://www.druid.net/pygresql/ and there is also another one, pyPgSQL, at: http://pypgsql.sourceforge.net/ It would be good to get both of them listed in the gborg interfaces section. They don't need to move their web sites there. They can just provide a link to their main site from gborg. Also, other interface authors should consider adding a page for themselves on gborg too so there is one place people can look for PostgreSQL interfaces. I have CC'ed both python interface authors. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Update on replication
Roderick A. Anderson wrote: I just got my copy of SysAdmin Magazine and was surprised to see an article about Usogres -- The PostgreSQL Replication Tool. I don't remember seeing it mentioned on this or the General list. Though I just started reading the article and don't have a firm grasp on it yet, I do remember a discussion of replication using this technique - described in the first two paragraphs. I saw Usogres when I was first in Japan. Interesting in that is intercepts queries and passes them two different servers. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] MySQL 4.1 Features
At 02:49 PM 18/12/2002 +0800, Christopher Kings-Lynne wrote: Looks like they've caught up on a lot of our features. Am I missing something here, or is 4.1 vapourware? Based on mysql.com, 3.23.45a is current production, and 4.0.5a is current beta. I am happy to believe I missed something, but if not, I think we should either compare our plans for 7.4 with 4.1, or 3.2/4.0 with 7.3. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MySQL 4.1 Features
Am I missing something here, or is 4.1 vapourware? Based on mysql.com, 3.23.45a is current production, and 4.0.5a is current beta. I am happy to believe I missed something, but if not, I think we should either compare our plans for 7.4 with 4.1, or 3.2/4.0 with 7.3. OK, fair enough. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Update on replication
Marc G. Fournier wrote: On Tue, 17 Dec 2002, Alvaro Herrera wrote: What about asynchronous (triggered?) replication? Is something like rserv or dbmirror going to be moved to main? From what I've been able to tell *so far*, Postgres-R is going to preclude the ability for either to work ... Vadim is currently reviewing the code, and based on his assessment of whether or not that is the case, I'm going to be pushing for postgres-r to be its own project/fork of PostgreSQL, like RedHat Database ... How is Postgres-R going to prevent async replication from also being adopted in CVS? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Update on replication
On Tue, 17 Dec 2002, Alvaro Herrera wrote: What about asynchronous (triggered?) replication? Is something like rserv or dbmirror going to be moved to main? From what I've been able to tell *so far*, Postgres-R is going to preclude the ability for either to work ... Vadim is currently reviewing the code, and based on his assessment of whether or not that is the case, I'm going to be pushing for postgres-r to be its own project/fork of PostgreSQL, like RedHat Database ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] python interface
Bruce Momjian kirjutas K, 18.12.2002 kell 00:10: I think the python interface in /interfaces/python should be moved to gborg. It already has its own web site: http://www.druid.net/pygresql/ and there is also another one, pyPgSQL, at: http://pypgsql.sourceforge.net/ And the active third one, psycopg at: http://initd.org/software/initd/psycopg And an old, seemingly abandoned one at: http://www.advogato.org/proj/python-postgresql/ But it would be nice if there were still some rpm's built by default for those, even from gborg. It would be good to get both of them listed in the gborg interfaces section. They don't need to move their web sites there. They can just provide a link to their main site from gborg. Also, other interface authors should consider adding a page for themselves on gborg too so there is one place people can look for PostgreSQL interfaces. I have CC'ed both python interface authors. -- Hannu Krosing [EMAIL PROTECTED] ---(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] Update on replication
ooops, sorry, that was what I meant (its been one of those days *grin*) On Tue, 17 Dec 2002, Mikheev, Vadim wrote: From what I've been able to tell *so far*, Postgres-R is going to preclude the ability for either to work ... Vadim is currently reviewing the code, Not the code, just Darren' pdf (slide show -:() and discussion in hackers' list. _ Sector Data, LLC, is not affiliated with Sector, Inc., or SIAC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Update on replication
Not the code, just Darren' pdf (slide show -:() and discussion in hackers' list. You might want to read this paper. Its not very long, and will give you much more insite on the postgres-r work. http://www.cs.mcgill.ca/~kemme/papers/vldb00.html Darren ---(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] Update on replication
Marc G. Fournier [EMAIL PROTECTED] writes: On Tue, 17 Dec 2002, Alvaro Herrera wrote: What about asynchronous (triggered?) replication? Is something like rserv or dbmirror going to be moved to main? From what I've been able to tell *so far*, Postgres-R is going to preclude the ability for either to work ... Why do you say that? If it can't coexist with other solutions, then it surely will not be accepted, but I can't think of any reason why it would preclude other approaches. 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] Update on replication
When I said: It certainly will cause problems with other replication solutions. I meant it would cause other solutions to be less desirable, meaning. as you said, affect the ability to investigate other solutions? With a working solution, others may be less likely to investigate because Postgres-R will be our official solution. (I believe that was your major negative point.) However, with the hooks already there, people may be _more_ likely to investigate solutions, so there is really no way to know. --- Marc G. Fournier wrote: On Tue, 17 Dec 2002, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: On Tue, 17 Dec 2002, Alvaro Herrera wrote: What about asynchronous (triggered?) replication? Is something like rserv or dbmirror going to be moved to main? From what I've been able to tell *so far*, Postgres-R is going to preclude the ability for either to work ... Why do you say that? If it can't coexist with other solutions, then it surely will not be accepted, but I can't think of any reason why it would preclude other approaches. Okay, if this is the case, that does change things somewhat, but Bruce seems to indiate that co-existance will be a problem: On Tue, 17 Dec 2002, Bruce Momjian wrote: The other concern is how does integrating Postgres-R affect the ability to investigate other solutions? As I said, I don't doubt taht there are aspects of Postgres-R that would benefit the server as a whole, and those bits-n-pieces should be looked at on an individual basis, but to just slap it in completely and hope that it doesn't cause problems for alternative solutions is kinda irresponsible ... It certainly will cause problems with other replication solutions. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Update on replication
On Tue, 17 Dec 2002, Bruce Momjian wrote: I meant it would cause other solutions to be less desirable, meaning. as you said, affect the ability to investigate other solutions? With a working solution, others may be less likely to investigate because Postgres-R will be our official solution. (I believe that was your major negative point.) However, with the hooks already there, people may be _more_ likely to investigate solutions, so there is really no way to know. Okay, but if we are just adding hooks to allow Postgres-R to tie in, can't those hooks be done in such a way as to be loadable, not compiled in? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Update on replication
Marc G. Fournier wrote: On Tue, 17 Dec 2002, Alvaro Herrera wrote: What about asynchronous (triggered?) replication? Is something like rserv or dbmirror going to be moved to main? From what I've been able to tell *so far*, Postgres-R is going to preclude the ability for either to work ... Vadim is currently reviewing the code, and based on his assessment of whether or not that is the case, I'm going to be pushing for postgres-r to be its own project/fork of PostgreSQL, like RedHat Database ... How is Postgres-R going to prevent async replication from also being adopted in CVS? As far as I know, all trigger based async replication solutions have a limitation. They do not handle high load (and probably cannot handle large objects. am I correct?). I think we should move to other async replication soltions, such as PostgreSQL-R or (not yet existing) transaction log based replications. -- Tatsuo Ishii ---(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] Update on replication
Marc G. Fournier wrote: Just checked on Usogres, and it appears to be relatively up to date, in that it is known to work up to 7.2.1: http://usogres.good-day.net/working.php3 Searching Google for QueryMaster finds a few Japanese sites, but I can't read Japanese :( Tatsuo, can you help? Use Altavista: http://world.altavista.com/ They have a Japense/English translation. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 7.3.1 stamped
I have prepared the 7.3 CVS branch in preparation of a 7.3.1 release soon. Please check it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 7.3.1 documentation updates
I have not been aggressive about backpatching documentation improvements into 7.3.1. Is that something I should check? As I remember, we didn't update the official docs for minor releases. Is that still true? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3.1 documentation updates
On Tue, 17 Dec 2002, Bruce Momjian wrote: I have not been aggressive about backpatching documentation improvements into 7.3.1. Is that something I should check? As I remember, we didn't update the official docs for minor releases. Is that still true? They say hydergine helps the memory. The idocs were never updated to current version until around the .1 release since there were usually discrepencies and it was a pain to update them. Vince. -- Fast, inexpensive internet service 56k and beyond! http://www.pop4.net/ http://www.meanstreamradio.com http://www.unknown-artists.com Internet radio: It's not file sharing, it's just radio. ---(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] 7.3.1 stamped
Could you put a note in HISTORY about the incompatability with pre-7.3 SSL clients? --Nate ---(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] 7.3.1 stamped
Nathan Mueller wrote: Could you put a note in HISTORY about the incompatability with pre-7.3 SSL clients? I believe that pre7-3 SSL clients will work in 7.3.1, or am I wrong? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Update on replication
On Tue, 2002-12-17 at 22:00, Bruce Momjian wrote: I think gborg allows us to collect all relivant projects in one place. Yes, but so would a webpage with a list of URLs, or a freshmeat/google/dmoz directory, or an SF foundry, or [ any number of other mechanisms for collecting groups of related websites ]. (Not to mention that you're assuming that GBorg includes all relevant projects -- it doesn't do that now, nor is it likely to in the future.) Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Update on replication
Neil Conway wrote: On Tue, 2002-12-17 at 22:00, Bruce Momjian wrote: I think gborg allows us to collect all relivant projects in one place. Yes, but so would a webpage with a list of URLs, or a freshmeat/google/dmoz directory, or an SF foundry, or [ any number of other mechanisms for collecting groups of related websites ]. (Not to mention that you're assuming that GBorg includes all relevant projects -- it doesn't do that now, nor is it likely to in the future.) Good point. I put my stuff on there because it was PostgreSQL-specific, and it had nice features. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.3.1 stamped
I believe that pre7-3 SSL clients will work in 7.3.1, or am I wrong?
In 7.3 the SSL protocol switched from SSLv2 to TLSv1. If the server
method is switched to SSLv23_method it will be backwords compatable with
pre-7.3 clients without sacrificing the added security of TLSv1 for
newer stuff. There's been a lot of other changes to the SSL code between
7.2 and 7.3, but I've tested this out and haven't found any problems.
I've included a patch to src/backend/libpq/be-secure.c if you're
interested.
--Nate
--- be-secure.c 13 Dec 2002 18:06:44 - 1.3
+++ be-secure.c 18 Dec 2002 04:16:19 -
@@ -587,7 +587,7 @@
{
SSL_library_init();
SSL_load_error_strings();
- SSL_context = SSL_CTX_new(TLSv1_method());
+ SSL_context = SSL_CTX_new(SSLv23_method());
if (!SSL_context)
{
postmaster_error(failed to create SSL
context: %s,
---(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] 7.3.1 stamped
I am confused. How can we switch back to SSLv23_method and still be
compatible with TLSv1_method. Does SSLv23_method support both?
The SSL author didn't like SSLv23_method (especially SSLv2) and I am not
confident to question his decision. We will just have to break backward
compatibility with pre-7.3 clients. No one else has mentioned it as a
problem, and in fact most have probably already upgraded to 7.3, so we
should be OK.
---
Nathan Mueller wrote:
I believe that pre7-3 SSL clients will work in 7.3.1, or am I wrong?
In 7.3 the SSL protocol switched from SSLv2 to TLSv1. If the server
method is switched to SSLv23_method it will be backwords compatable with
pre-7.3 clients without sacrificing the added security of TLSv1 for
newer stuff. There's been a lot of other changes to the SSL code between
7.2 and 7.3, but I've tested this out and haven't found any problems.
I've included a patch to src/backend/libpq/be-secure.c if you're
interested.
--Nate
--- be-secure.c 13 Dec 2002 18:06:44 - 1.3
+++ be-secure.c 18 Dec 2002 04:16:19 -
@@ -587,7 +587,7 @@
{
SSL_library_init();
SSL_load_error_strings();
- SSL_context = SSL_CTX_new(TLSv1_method());
+ SSL_context = SSL_CTX_new(SSLv23_method());
if (!SSL_context)
{
postmaster_error(failed to create SSL
context: %s,
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(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] 7.3.1 stamped
I am confused. How can we switch back to SSLv23_method and still be compatible with TLSv1_method. Does SSLv23_method support both? SSLv23 understands SSLv2, SSLv3 and TLSv1. When used in a client it uses SSLv2 but tells the server it can understand the other ones too. Check out the SSL_CTX_new manpage for a lot more details. The SSL author didn't like SSLv23_method (especially SSLv2) and I am not confident to question his decision. We will just have to break backward compatibility with pre-7.3 clients. No one else has mentioned it as a problem, and in fact most have probably already upgraded to 7.3, so we should be OK. I agree, TLSv1 is a lot better but there's no point in breaking backwords compatibility when you don't have to. Also, given my problems with 7.3's SSL I'd be surprised if a lot of people who use it have made the switch. --Nate ---(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] 7.3.1 stamped
Nathan Mueller wrote: I am confused. How can we switch back to SSLv23_method and still be compatible with TLSv1_method. Does SSLv23_method support both? SSLv23 understands SSLv2, SSLv3 and TLSv1. When used in a client it uses SSLv2 but tells the server it can understand the other ones too. Check out the SSL_CTX_new manpage for a lot more details. The SSL author didn't like SSLv23_method (especially SSLv2) and I am not confident to question his decision. We will just have to break backward compatibility with pre-7.3 clients. No one else has mentioned it as a problem, and in fact most have probably already upgraded to 7.3, so we should be OK. I agree, TLSv1 is a lot better but there's no point in breaking backwords compatibility when you don't have to. Also, given my problems with 7.3's SSL I'd be surprised if a lot of people who use it have made the switch. Well, we break backward compatibility so people can't use SSL2 to connect to the server. Backward compatibility to a broken protocol isn't what I would call secure. Is that accurate? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3.1 stamped
Well, we break backward compatibility so people can't use SSL2 to connect to the server. Backward compatibility to a broken protocol isn't what I would call secure. Is that accurate? I suppose. As long as the incompatibilty is mentioned in HISTORY I'm fine. --Nate ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3.1 stamped
Nathan Mueller wrote: Well, we break backward compatibility so people can't use SSL2 to connect to the server. Backward compatibility to a broken protocol isn't what I would call secure. Is that accurate? I suppose. As long as the incompatibilty is mentioned in HISTORY I'm fine. Uh, I didn't get it in there because we were still discussing it while I was packaging 7.3.1. Sorry. I can mention it in 7.3.2. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
