Re: [HACKERS] \xDD patch for 7.5devel

2003-11-05 Thread Stephan Szabo
On Thu, 6 Nov 2003, Jason Godden wrote: On Thu, 6 Nov 2003 06:25 am, Markus Bertheau wrote: , 05.11.2003, 16:25, Tom Lane : +#define HEXVALUE(c) (((c)='a') ? ((c)-87) : (((c)='A') ? ((c)-55) : ((c)-'0'))) This seems excessively dependent on the assumption that the character

Re: [HACKERS] Deferrable triggers

2003-11-06 Thread Stephan Szabo
On Thu, 7 Nov 2003, Grant McLean wrote: So it would seem that if I include the clauses: on delete restrict on update restrict Then the 'deferrable' which follows is only applied to creates and not to updates or deletes. Since 'restrict' is the default, the clauses aren't adding

Re: [HACKERS] Deferrable triggers

2003-11-06 Thread Stephan Szabo
On Thu, 7 Nov 2003, Grant McLean wrote: On Fri, 2003-11-07 at 11:31, Stephan Szabo wrote: On Thu, 7 Nov 2003, Grant McLean wrote: So it would seem that if I include the clauses: on delete restrict on update restrict Then the 'deferrable' which follows is only applied

Re: [HACKERS] start of transaction (was: Re: [PERFORM] Help with

2003-11-17 Thread Stephan Szabo
On Sun, 17 Nov 2003, Greg Stark wrote: Neil Conway [EMAIL PROTECTED] writes: What does BEGIN actually do now, from a user's perspective? I think you're thinking about this all wrong. BEGIN doesn't do anything. It's not a procedural statement, it's a declaration. It declares that the block

Re: [HACKERS] logical column position

2003-11-17 Thread Stephan Szabo
On Tue, 18 Nov 2003, Peter Eisentraut wrote: Christopher Kings-Lynne writes: BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. But that is only really important if they've also used the ALTER TABLE RESHUFFLE COLUMNS

Re: [HACKERS] Anyone working on pg_dump dependency ordering?

2003-11-22 Thread Stephan Szabo
On Sat, 22 Nov 2003, Andreas Pflug wrote: Christopher Kings-Lynne wrote: There are two levels (sort of) of dependency. The first is that whole classes of objects can be dependent on whole other classes. eg. databases depend on users, or ALL FK's can be dumped after ALL tables,

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Stephan Szabo
On Wed, 26 Nov 2003, ow wrote: People might be more interested in debating this topic with you if we hadn't discussed it at length just a couple months back. There wasn't consensus then that we had to offer an escape hatch, and you've not offered any argument that wasn't made before.

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Stephan Szabo
On Wed, 26 Nov 2003, Tom Lane wrote: Quite honestly, I think they should check their foreign keys. In a partial restore situation there is no guarantee that the referenced table and the referencing table are being restored at the same time from the same dump. An override in that situation

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Stephan Szabo
On Wed, 26 Nov 2003, Tom Lane wrote: ow [EMAIL PROTECTED] writes: --- Tom Lane [EMAIL PROTECTED] wrote: Quite honestly, I think they should check their foreign keys. Generally speaking, I agree. The problem is that verification of FK constraint(s) may take too long, depending on the

Re: [HACKERS] Foreign key data type mismatches

2003-12-01 Thread Stephan Szabo
On Mon, 1 Dec 2003, Peter Eisentraut wrote: I was just annoyed to find out that a foreign key doesn't check whether the referenced column has a sufficiently similar data type, it only checks whether an = operator exists. This masks schema design errors and typos. Should this be tightened up,

Re: [HACKERS] Max number of rows in a table

2003-12-01 Thread Stephan Szabo
On Mon, 1 Dec 2003, ow wrote: --- Tom Lane [EMAIL PROTECTED] wrote: Actually you can only have 4 billion SQL commands per xid, because the CommandId datatype is also just 32 bits. I've never heard of anyone running into that limit, though. Wouldn't the above put a limit on a number

Re: [HACKERS] Encoding problem with 7.4

2003-12-03 Thread Stephan Szabo
On Wed, 3 Dec 2003, E.Rodichev wrote: On Fri, 28 Nov 2003, Tom Lane wrote: E.Rodichev [EMAIL PROTECTED] writes: /e:2createdb test test | er | SQL_ASCII - Incorrect! (3 rows) Let's note than the last line is in fact completely incorrect. What's incorrect

Re: [HACKERS] Encoding problem with 7.4

2003-12-03 Thread Stephan Szabo
On Wed, 3 Dec 2003, E.Rodichev wrote: On Wed, 3 Dec 2003, Stephan Szabo wrote: On Wed, 3 Dec 2003, E.Rodichev wrote: On Fri, 28 Nov 2003, Tom Lane wrote: E.Rodichev [EMAIL PROTECTED] writes: /e:2createdb test test | er | SQL_ASCII - Incorrect

Re: [HACKERS] Encoding problem with 7.4

2003-12-03 Thread Stephan Szabo
On Thu, 4 Dec 2003, E.Rodichev wrote: On Wed, 3 Dec 2003, Stephan Szabo wrote: Only the locale settings at initdb time matter. Changing the LC_* later is not going to change what the database does. Encoding and locale are separate (but related) and it is your responsibility to make sure

Re: [HACKERS] Encoding problem with 7.4

2003-12-04 Thread Stephan Szabo
On Thu, 4 Dec 2003, E.Rodichev wrote: On Wed, 3 Dec 2003, Stephan Szabo wrote: The locale settings depend on LC_* at initdb time only. When the postmaster starts it sets the locale based on the stored values from initdb, not on the current environment. With an SQL_ASCII database

Re: [HACKERS] index scan with functional indexes

2004-01-27 Thread Stephan Szabo
On Tue, 27 Jan 2004, Dave Cramer wrote: same answer davec=# show enable_seqscan; enable_seqscan off (1 row) davec=# explain analyze select * from url where fn_strrev(url) like '%beta12.html'; That's still an unanchored like clause, besides I think that would get

Re: [HACKERS] [SQL] 7.4 - FK constraint performance

2004-02-15 Thread Stephan Szabo
On Fri, 13 Feb 2004, Stephan Szabo wrote: On Fri, 13 Feb 2004, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Fri, 13 Feb 2004, Tom Lane wrote: I was looking at that last night. It seems like we could add a LIMIT at least in some contexts. In the case at hand, we're

Re: [HACKERS] No Timeout in SELECT..FOR UPDATE

2004-02-15 Thread Stephan Szabo
On Tue, 10 Feb 2004, Anthony Rich wrote: In other words, by the time the second process has run the SELECT...FOR UPDATE statement, it's too late!! This second process is now locked forever, waiting for the Or until statement_timeout is reached if it's set to a non-zero value.

Re: [HACKERS] [PATCHES] Foreign key type checking patch

2004-03-02 Thread Stephan Szabo
On Tue, 2 Mar 2004, Fabien COELHO wrote: Hello again, I turn the discussion to the dev list as it seems more appropriate. So about the proposed patch to warn if foreign key type do not match the target key: Stephan Szabo [EMAIL PROTECTED] writes: I'm really not sure that it makes

Re: [HACKERS] [PATCHES] Foreign key type checking patch

2004-03-02 Thread Stephan Szabo
On Tue, 2 Mar 2004, Fabien COELHO wrote: Hello Stephan, CREATE TABLE foo(fid INT4 NOT NULL PRIMARY KEY, ...); CREATE TABLE bla(fid INT2 REFERENCES foo, ...); The application will be fine till you enter fid=32767, and it inserts will fail in bla with fid=32768. Much later on.

Re: [HACKERS] LIKE and Locale

2004-03-31 Thread Stephan Szabo
On Wed, 31 Mar 2004 [EMAIL PROTECTED] wrote: I'm a little frustrated select * from mytable where mystring = 'foo'; Uses an index select * from mytable where mystring like 'foo'; Does not use an index. I know Tom is not to excited about this, but I think it is a serious problem. What

Re: [HACKERS] LIKE and Locale

2004-03-31 Thread Stephan Szabo
On Wed, 31 Mar 2004 [EMAIL PROTECTED] wrote: On Wed, 31 Mar 2004 [EMAIL PROTECTED] wrote: I'm a little frustrated select * from mytable where mystring = 'foo'; Uses an index select * from mytable where mystring like 'foo'; Does not use an index. I know Tom is not to

Re: [HACKERS] Problems Vacuum'ing

2004-04-02 Thread Stephan Szabo
On Fri, 2 Apr 2004, Alvaro Herrera wrote: On Fri, Apr 02, 2004 at 07:35:20PM -0500, Tom Lane wrote: [EMAIL PROTECTED] (Jim Seymour) writes: Again the difference: With WebObjects running, deleting rows and trying to vacuum immediately, even full, fails. Shut-down WebObjects and I

Re: [HACKERS] make == as = ?

2004-04-07 Thread Stephan Szabo
On Wed, 7 Apr 2004, Fabien COELHO wrote: From my point of view, my students come from a java first course, so they have to learn again some new syntax and new operators. Small stuff, but it can help to say same as java and go on to new concepts. Don't you want them to learn SQL? I

Re: [HACKERS] make == as = ?

2004-04-12 Thread Stephan Szabo
On Mon, 12 Apr 2004, Fabien COELHO wrote: Please see my previous e-mail about the value of international standards for educators. I read your email. I noticed that you want to educate me as an educator;-) I partially agree with your point. We have two words in French: education and

Re: [HACKERS] PostgreSQL configuration

2004-04-12 Thread Stephan Szabo
On Mon, 12 Apr 2004, Bruce Momjian wrote: [EMAIL PROTECTED] wrote: The only other idea I can think of is to create a new pg_path.conf file. It would have the same format as postgresql.conf, but contain information about /data location, config file location, and perhaps pg_xlog

Re: [HACKERS] make == as = ?

2004-04-13 Thread Stephan Szabo
On Tue, 13 Apr 2004, Fabien COELHO wrote: Your Java students would be lulled into a false sense of understanding out of the belief that == in PostgreSQL would work exactly like == in Java ... when it wouldn't work the same in corner cases. For the class I have in mind, there are no

Re: [HACKERS] make == as = ?

2004-04-13 Thread Stephan Szabo
Dear Stephan, For the class I have in mind, there are no corner cases, just concepts and basic practice. They are not going to be db developers, not even computer So no string comparisons? I know that's a mostly unused corner case and all, but... ;) They survive to the idea that

Re: [HACKERS] Lexing with different charsets

2004-04-13 Thread Stephan Szabo
On Wed, 14 Apr 2004, Tatsuo Ishii wrote: I've spent some more time reading specs today. Together with Peter E's explanataion (Thanks!) I think I've got a farily good understanding of the parts talking about locales now. My next question is about lexing. The spec says that one can use

Re: [HACKERS] License question

2004-04-22 Thread Stephan Szabo
On Thu, 22 Apr 2004, Shachar Shemesh wrote: Tom Lane wrote: You can take some Postgres pieces and use them in a project with a different overall license, but those pieces are still under BSD license. But that's not the BSD license. [...] The BSD license, in contrast to PostgreSQL's,

Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?

2004-04-23 Thread Stephan Szabo
On Fri, 23 Apr 2004, Robert Treat wrote: On Fri, 2004-04-23 at 05:22, Dennis Bjorklund wrote: On Fri, 23 Apr 2004, Shachar Shemesh wrote: When I ask about non-standard complience of Pg (turning unquoted identifiers to lowercase instead of uppercase, violating the SQL standard, and

Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?

2004-04-23 Thread Stephan Szabo
On Fri, 23 Apr 2004, Shachar Shemesh wrote: Stephan Szabo wrote: I've tried just changing the parser to unconditionally casefold to upper. First thing that happens is that initdb breaks. In addition, you have potential issues with comparisons against the catalog's versions of standard

Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?

2004-04-23 Thread Stephan Szabo
On Fri, 23 Apr 2004, Stephan Szabo wrote: On Fri, 23 Apr 2004, Shachar Shemesh wrote: Stephan Szabo wrote: I've tried just changing the parser to unconditionally casefold to upper. First thing that happens is that initdb breaks. In addition, you have potential issues with comparisons

Re: [HACKERS] Do we prefer software that works or software that looks good?

2004-04-24 Thread Stephan Szabo
On Sat, 24 Apr 2004, Shachar Shemesh wrote: Tom Lane wrote: So what I'm holding out for is a design that lets me continue to see the current behavior if I set a GUC variable that says that's what I want. This seems possible (not easy, but possible) if we are willing to require the choice

Re: [HACKERS] Do we prefer software that works or software that looks good?

2004-04-24 Thread Stephan Szabo
On Sat, 24 Apr 2004, Shachar Shemesh wrote: Stephan Szabo wrote: Are we going to get rid of the current behavior entirely? I doubt that will be a good idea. You want to let applications created for previous versions of PostgreSQL continue to work. The idea, I think, is to have either a DB

Re: [HACKERS] Do we prefer software that works or software that

2004-04-25 Thread Stephan Szabo
On Sat, 24 Apr 2004, Stephan Szabo wrote: On Sat, 24 Apr 2004, Shachar Shemesh wrote: Stephan Szabo wrote: Things like don't worry about the catalog entries don't fly when your standard functions are defined and looked up there. Answer above. Okay, under that world view

Re: [HACKERS] An Index Scanning Solution question

2004-05-20 Thread Stephan Szabo
On Thu, 20 May 2004, Bruno Wolff III wrote: On Wed, May 19, 2004 at 15:17:01 +0200, Atesz [EMAIL PROTECTED] wrote: I'd like to ask why the index scaning can't move on an index in multi-order directions (For exapmle: 1.column: forward, 2.column: backward and 3.column: forward again)?

Re: [HACKERS] Reconstructing FKs in pg_dump

2002-09-26 Thread Stephan Szabo
On Thu, 26 Sep 2002, Tom Lane wrote: Patrick Welche's recent problems (see pgsql-general) point out that the old CREATE CONSTRAINT TRIGGER syntax that optionally omits a FROM table clause doesn't work anymore --- the system *needs* tgconstrrelid to be set in an RI constraint trigger record,

Re: [HACKERS] Reconstructing FKs in pg_dump

2002-09-26 Thread Stephan Szabo
On Thu, 26 Sep 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: However, if we are going to put that kind of knowledge into pg_dump, it would only be a small further step to have it dump these triggers as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot

Re: [HACKERS] 7.2.3?

2002-09-28 Thread Stephan Szabo
On Sat, 28 Sep 2002, Bruce Momjian wrote: I have seen no discussion on whether to go ahead with a 7.2.3 to add several serious fixes Tom has made to the code in the past few days. Are we too close to 7.3 for this to be worthwhile? Certainly there will be people distributing 7.2.X for some

[HACKERS] Request for suggestions

2002-10-09 Thread Stephan Szabo
I've been working on kludging a working for update barrier style lock (*) for reads using HeapTupleSatisfiesDirty to test accessibility to make the foreign keys work better. I'm fairly close to getting a testable kludge for the fk/noaction cases for people to check real sequences against (since

[HACKERS] (Followup) Request for suggestions

2002-10-09 Thread Stephan Szabo
I wasn't particularly clear (sorry, wrote the message 1/2 right before bed, 1/2 right after getting up) so I'm going to followup with details and hope that I'm more awake. A little background just in case there are people that haven't looked. Right now, foreign key checks always default to

Re: [HACKERS] Damn slow query

2002-10-09 Thread Stephan Szabo
On Wed, 9 Oct 2002, Magnus Naeslund(f) wrote: Hello, i've got this query that's really slow... Figure this: testdb= select now() ; select gid from bs where gid not in ( select x from z2test ); select now(); Per FAQ suggestion, try something like select gid from bs where not exists (select

Re: [HACKERS] foreign key problem with pg_dump under 7.3b2

2002-10-15 Thread Stephan Szabo
On 15 Oct 2002, John Halderman wrote: I'm currently using 7.3b2 for test and development. I ran into a problem using a dumped schema from pg_dump. After importing the dumped schema, any delete or update involving a foreign key results in a relation 0 does not exist error. I noticed that all

Re: [HACKERS] Strange cost computation?

2002-10-30 Thread Stephan Szabo
On 29 Oct 2002, Ives Landrieu wrote: Hi, Can anybody explain the following results when using EXPLAIN, one time with enable_seqscan=on and one time with enable_seqscan=off. What I don't understand is that the nodes created are the same (index scan, seq scan), but the costs differ. Enable

Re: [HACKERS] Strange cost computation?

2002-10-30 Thread Stephan Szabo
On Wed, 30 Oct 2002, Stephan Szabo wrote: On 29 Oct 2002, Ives Landrieu wrote: Hi, Can anybody explain the following results when using EXPLAIN, one time with enable_seqscan=on and one time with enable_seqscan=off. What I don't understand is that the nodes created are the same

Re: [HACKERS] float output precision questions

2002-10-30 Thread Stephan Szabo
On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: In C this is possible: #include stdio.h #include stdlib.h int main(void) { double v; char a[30]; v=1.79769313486231571e+308; printf( Stored double number: %25.18g\n,v); sprintf(a,%25.18g,v);

Re: [HACKERS] Request for supported platforms

2002-10-26 Thread Stephan Szabo
On 26 Oct 2002, Doug McNaught wrote: Tom Lane [EMAIL PROTECTED] writes: Doug McNaught [EMAIL PROTECTED] writes: make[3]: Entering directory `/home/doug/src/pgsql/src/backend/utils/mb/conversion_procs/ascii_and_mic' gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic

Re: [HACKERS] float output precision questions

2002-10-29 Thread Stephan Szabo
On Tue, 29 Oct 2002, Peter Eisentraut wrote: Pedro Miguel Frazao Fernandes Ferreira writes: Is there a way to set query output precision to maximum precision ? For the type of application I mentioned this is crucial. People want to get the 'same' numbers, from querys or dumps, as they

Re: [HACKERS] float output precision questions

2002-10-30 Thread Stephan Szabo
On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: Stephan Szabo wrote: On Tue, 29 Oct 2002, Peter Eisentraut wrote: Pedro Miguel Frazao Fernandes Ferreira writes: Is there a way to set query output precision to maximum precision ? For the type of application I

Re: [HACKERS] float output precision questions

2002-10-30 Thread Stephan Szabo
On Wed, 30 Oct 2002, Pedro M. Ferreira wrote: Stephan Szabo wrote: On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: I understand that if people insert a value of 1.1 in a double, they want to get 1.1 without knowing that in fact the stored number is 1.10009

Re: [HACKERS] float output precision questions

2002-10-30 Thread Stephan Szabo
On Wed, 30 Oct 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Wed, 30 Oct 2002, Pedro M. Ferreira wrote: I looked at some of these emails and it seemed to me that the problem was that Tom did'nt want a parameter that would force people to know about printf number

Re: [HACKERS] [ADMIN] Security question : Database access control

2002-10-22 Thread Stephan Szabo
On Tue, 22 Oct 2002, Igor Georgiev wrote: edit *pg_hba.conf * # Allow any user on the local system to connect to any # database under any username, but only via an IP connection: host all 127.0.0.1 255.255.255.255trust #

Re: [ADMIN] [HACKERS] Security question : Database access control

2002-10-25 Thread Stephan Szabo
On Fri, 25 Oct 2002, Igor Georgiev wrote: Next your going to ask what will stop root from stopping your PostgreSQL, compiling a second copy with authentication disabled and using your data directory as it's source :) He he somebody can blow up ur home with C4, but this don't stop you

Re: [HACKERS] [SQL] Problem: Referential Integrity Constraints lost

2002-11-06 Thread Stephan Szabo
On Wed, 6 Nov 2002, Achilleus Mantzios wrote: Hi i think a hit a major problem on 7.2.1. I run 3 systems with postgresql 7.2.1. Its a redhat 7.1 for development, a redhat 7.3 for production and a FreeBSD 4.6.1RC2 for testing. After long runs (with periodic (daily) vacuum analyze's) i

Re: [HACKERS] PL/PgSQL buglet / doc error

2002-11-07 Thread Stephan Szabo
On 7 Nov 2002, Neil Conway wrote: The 7.3 docs for the PL/PgSQL return statement say: RETURN with an expression is used to return from a PL/pgSQL function that does not return a set. [...] If you have declared the function to return void, then the expression can be omitted, and will

[HACKERS] Question about row visibility after locks

2002-11-08 Thread Stephan Szabo
I've been wondering (and probably should look through the code, but figured asking would be faster) if there's any guarantee that I'll see rows inserted by a transaction I'm waiting on in the middle of a query. Basically, if I've got a select that's running using HeapTupleSatisfiesDirty so that

Re: [HACKERS] Question about row visibility after locks

2002-11-09 Thread Stephan Szabo
On Sat, 9 Nov 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Basically, if I've got a select that's running using HeapTupleSatisfiesDirty so that I can see uncommitted rows and I block in the middle for another transaction (waiting to see if it commits the row I'm looking

Re: [HACKERS] [GENERAL] PostgreSQL JDBC and sub-select

2002-11-09 Thread Stephan Szabo
On Sat, 9 Nov 2002, snpe wrote: Hello, I work with JDeveloper and PostgreSQL JDBC and I have one problem. I get error : sub-SELECT in FORM must have an alias I can't change SQL command, but it is internal JDeveloper command Is it SQL standard (must have alias) or PostgreSQL specific ?

Re: [HACKERS] RI_FKey_check: foreign key constraint blocks parallel

2002-11-13 Thread Stephan Szabo
On Wed, 13 Nov 2002, Peter Schindler wrote: But, if a lot of inserts happens into the child table and there is a mix of short and long running transactions, the likelihood of blocking is very high, even the inserts are independent and everything is ok (prim. key etc.). This is even more

Re: [HACKERS] create or replace view

2002-11-14 Thread Stephan Szabo
On Thu, 14 Nov 2002, Scott Shattuck wrote: It might just be me but it seems that this discussion is missing the point if we believe this request is about saving some characters. I don't think it is. I think it's about being able to write simple SQL scripts that don't produce errors when you

Re: [HACKERS] create or replace view

2002-11-14 Thread Stephan Szabo
On Thu, 14 Nov 2002, snpe wrote: Problem is when I want change view (or functions) with a lot of dependecies I must drop and recreate all dependent views (or functions) - I want add only one column in view I don't know if solution hard for that. Well, doing create or replace as a

Re: [HACKERS] create or replace view

2002-11-15 Thread Stephan Szabo
On Fri, 15 Nov 2002, Zeugswetter Andreas SB SD wrote: Problem is when I want change view (or functions) with a lot of dependecies I must drop and recreate all dependent views (or functions) - I want add only one column in view I don't know if solution hard for that. I do not see how

Re: [HACKERS] RI_FKey_check: foreign key constraint blocks parallel

2002-11-15 Thread Stephan Szabo
On Fri, 15 Nov 2002, Manfred Koizar wrote: On Wed, 13 Nov 2002 14:22:51 -0800 (PST), Stephan Szabo [EMAIL PROTECTED] wrote: Right now, I know that it has a hole that lets through invalid data Stephan, your patch has been posted to -general (Subject: Re: [GENERAL] Help..Help

Re: [HACKERS] RI_FKey_check: foreign key constraint blocks parall

2002-11-15 Thread Stephan Szabo
On Fri, 15 Nov 2002, Mikheev, Vadim wrote: Just wonder how are you going to implement it - is it by using some kind of read-locks, ie FK transaction locks PK to prevent delete (this is known as pessimistic approach)? About two years ago we discussed with Jan optimistic approach with using

Re: [HACKERS] Ideas needed: How to create and store collation tables

2002-11-18 Thread Stephan Szabo
On Mon, 18 Nov 2002, Peter Eisentraut wrote: A collation table essentially consists of a mapping 'character code - weight' for every character in the set and some additional considerations for one-to-many and many-to-one mappings, plus a few feature flags. How would a user go about creating

Re: [HACKERS] Optimizer boolean syntax

2002-11-21 Thread Stephan Szabo
On Thu, 21 Nov 2002, Daniele Orlandi wrote: Are those two syntaxes eqivalent ? select * from users where monitored; select * from users where monitored=true; If the answer is yes, the optimimer probably doesn't agree with you :) That depends on the definition of equivalent. They

Re: [HACKERS] Optimizer boolean syntax

2002-11-21 Thread Stephan Szabo
On Thu, 21 Nov 2002, Christopher Kings-Lynne wrote: Are those two syntaxes eqivalent ? select * from users where monitored; select * from users where monitored=true; If the answer is yes, the optimimer probably doesn't agree with you :) That depends on the definition of

Re: [HACKERS] Optimizer boolean syntax

2002-11-21 Thread Stephan Szabo
On Thu, 21 Nov 2002, Christopher Kings-Lynne wrote: col isn't of the general form indexkey op constant or constant op indexkey which I presume it's looking for given the comments in indxpath.c. I'm not sure what the best way to make it work would be given that presumably we'd

Re: [HACKERS] updating on views

2002-11-26 Thread Stephan Szabo
On Wed, 27 Nov 2002, XiaojingLi wrote: I'd like to bother you with something about updating views. At present, postgresql7.3¬°¬°doesn't support update on views.When I look up It does if you provide the rules for what it means to do the update (and there are some wierd issues with delete,

Re: [HACKERS] 7.4 Wishlist

2002-11-30 Thread Stephan Szabo
On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote: Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? What would you guys do? Even if it isn't feasible right now... Hmm, mine would probably be fixing foreign keys

Re: [HACKERS] 7.4 Wishlist

2002-12-01 Thread Stephan Szabo
On 30 Nov 2002, Neil Conway wrote: On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote: check constraints with subselects. Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re

Re: [HACKERS] 7.4 Wishlist

2002-12-01 Thread Stephan Szabo
On Sun, 1 Dec 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On 30 Nov 2002, Neil Conway wrote: Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate

Re: [HACKERS] 7.4 Wishlist

2002-12-02 Thread Stephan Szabo
On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote: Stephan Szabo [EMAIL PROTECTED] wrote: If you use a scalar subquery, yes, but I think a subselect in from would help, maybe something like (if you want the total count) select table_name.id, sum(sum_col)||'/'||t2.count from table_name

Re: [HACKERS] 7.4 Wishlist

2002-12-02 Thread Stephan Szabo
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote: Now convert this query so that it only evaluates the date_part thing ONCE: That's not a good idea as long as t.stamp varies from row to row. ;) Perhaps once per row, maybe... :) select t.id, date_part('days',now()-t.stamp) from table_name t

Re: [HACKERS] 7.4 Wishlist

2002-12-02 Thread Stephan Szabo
On Tue, 3 Dec 2002, Bruce Momjian wrote: Magnus Naeslund(f) wrote: Good! Now convert this query so that it only evaluates the date_part thing ONCE: select t.id, date_part('days',now()-t.stamp) from table_name t where date_part('days',now()-t.stamp) 20; I hope you all are

Re: [HACKERS] 7.4 - TODO : alter table drop foreign key

2002-12-05 Thread Stephan Szabo
On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems to work for me on my 7.3b2 system with alter table table drop constraint constraint name;

Re: [HACKERS] 7.4 - TODO : alter table drop foreign key

2002-12-05 Thread Stephan Szabo
On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 8:20, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems

Re: [HACKERS] 7.4 - TODO : alter table drop foreign key

2002-12-05 Thread Stephan Szabo
On Thu, 5 Dec 2002, Dan Langille wrote: Found the solution: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; Actually there are three triggers for the constraint. You may have dangling triggers on the other table of the constraint. It's one on the table the constraint's

Re: [HACKERS] 7.4 - TODO : alter table drop foreign key

2002-12-05 Thread Stephan Szabo
On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 9:02, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: Found the solution: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; Actually there are three triggers for the constraint. You may have

Re: [HACKERS] 7.4 - TODO : alter table drop foreign key

2002-12-05 Thread Stephan Szabo
On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 9:31, Stephan Szabo wrote: When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so I think that might be why we're talking past each other here. Technically the syntax in question is: ALTER TABLE table ADD

Re: [HACKERS] Postgres syscalls

2002-12-12 Thread Stephan Szabo
On Fri, 13 Dec 2002, [iso-8859-1] Diego T. wrote: Hello I'm an Italian student of computer science at University of Rome La Sapienza. I've to analyze some daemons which run under root privileges with a tool developed by my departement. This tool intercepts critical syscalls, like Execve,

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)

2003-01-01 Thread Stephan Szabo
On Wed, 1 Jan 2003, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I see the values being stored on constriant creation, but not being used anywhere: I believe the values that actually get inspected at runtime are the tgdeferrable and tginitdeferred

Re: [HACKERS] Autocommit off and transaction isolation level

2003-01-02 Thread Stephan Szabo
On Thu, 2 Jan 2003, Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: Now setting autocommit=off the set transaction isolation level command does not show any effect: billing=# set autocommit to off; SET billing=# set transaction isolation level serializable; SET SET does

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable

2003-01-02 Thread Stephan Szabo
On 2 Jan 2003, Rod Taylor wrote: I think I initially forgot those options, and Stephans patch seems to be everything required -- though the psql display is a little more cluttered. IIRC, theoretically only initially immediate deferrable actually needs to specify both clauses (initially

Re: [HACKERS] UNION result

2003-01-14 Thread Stephan Szabo
On Wed, 15 Jan 2003, Tatsuo Ishii wrote: Does anybody know: select 1.0 union select 1; or select 1 union select 1.0; should return 1 or 1.0? Hmm, I think (but am not sure) that the spec bit in SQL92 that addresses this is 9.3 Set operation result data types based on the text in 7.10

Re: [HACKERS] UNION result

2003-01-15 Thread Stephan Szabo
On Wed, 15 Jan 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Hmm, I think (but am not sure) that the spec bit in SQL92 that addresses this is 9.3 Set operation result data types based on the text in 7.10 query expression. It seems to say to me that should always

Re: [HACKERS] UNION result

2003-01-15 Thread Stephan Szabo
On Wed, 15 Jan 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: It seems to me that the spec has a fairly hardwired notion of what types should come out given the sql types. The biggest problems that I can see are that it doesn't extend well to an extensible type system

Re: [mail] Re: [HACKERS] Win32 port patches submitted

2003-01-21 Thread Stephan Szabo
On Tue, 21 Jan 2003, Al Sutton wrote: I would back keeping the windows specific files, and if anything moving the code away from using the UNIX like programs. My reasoning is that the more unix tools you use for compiling, the less likley you are to attract existing windows-only developers

Re: [HACKERS] SET NULL on NOT NULL field

2003-01-27 Thread Stephan Szabo
On Tue, 28 Jan 2003, Christopher Kings-Lynne wrote: I just noticed you can do this: create table blah ( a not null references test on delete set null ) Should that be prevented? It shouldn't be too hard to test for really... Maybe, although I don't think the spec prevents it. In

Re: [HACKERS] SET NULL on NOT NULL field

2003-01-28 Thread Stephan Szabo
On Tue, 28 Jan 2003, Bruno Wolff III wrote: On Mon, Jan 27, 2003 at 21:23:01 -0800, Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 28 Jan 2003, Christopher Kings-Lynne wrote: I just noticed you can do this: create table blah ( a not null references test on delete set null

Re: [HACKERS] location of the configuration files

2003-02-13 Thread Stephan Szabo
On Thu, 13 Feb 2003, mlw wrote: Robert Treat wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system.

Re: [HACKERS] location of the configuration files

2003-02-13 Thread Stephan Szabo
On Thu, 13 Feb 2003, mlw wrote: Stephan Szabo wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's

[HACKERS] Foreign key quandries

2003-02-28 Thread Stephan Szabo
Going through the issues in doing dirty reads in foreign keys I've come up with a few cases that I'm fairly uncertain about how to handle with regards to deadlocks and figured I should ask for advice because I think I'm missing something painfully obvious, but don't get large enough blocks of

Re: [HACKERS] [GENERAL] division by zero

2003-03-10 Thread Stephan Szabo
On Mon, 10 Mar 2003, Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] writes: The only other solution is a #ifdef win32 around places that potentially use integers in the divisor and do some nasty hacking. Well, it seems to me that we have two different issues to worry about: 1. There

Re: [HACKERS] Another naive question, inheritance and foreign key

2003-03-19 Thread Stephan Szabo
On Wed, 19 Mar 2003, Shridhar Daithankar[EMAIL PROTECTED] wrote: Just stumbled upon this. Is it correct to conclude that foreign keys are not inherited from this text? Yes. If you want more info, check out the archives. ---(end of

Re: [HACKERS] Deadlock situation using foreign keys (reproduceable)

2002-04-11 Thread Stephan Szabo
On Thu, 11 Apr 2002, Mario Weilguni wrote: As promised here's an example of deadlock using foreign keys. create table lang ( id integer not null primary key, name text ); insert into lang values (1, 'English'); insert into lang values (2, 'German'); create table country ( id

Re: [HACKERS] Foreign Key woes -- 7.2 and ~7.3

2002-04-16 Thread Stephan Szabo
On Tue, 16 Apr 2002, Rod Taylor wrote: b=# create table stuff (stuff_id serial unique); NOTICE: CREATE TABLE will create implicit sequence 'stuff_stuff_id_seq' for SERIAL column 'stuff.stuff_id' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'stuff_stuff_id_key' for table

Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE

2002-04-16 Thread Stephan Szabo
On Tue, 16 Apr 2002, Michael Loftis wrote: I know I know, replying to myself is bad and probably means I'm going insane but thought of one other thing... Realistically the system should choos *ANY* index over a sequential table scan. Above a fairly low number of records any indexed query

Re: [HACKERS] Odd(?) RI-trigger behavior

2002-04-18 Thread Stephan Szabo
On Thu, 18 Apr 2002, Tom Lane wrote: This particular test involves a table with a foreign-key reference to itself, ie, it's both PK and FK. What apparently is happening is that the two RI triggers are now being fired in a different order than before. While either of them would have

  1   2   3   4   5   6   >