Re: [HACKERS] failed Assert() in utf8_and_iso8859_1.c
Hmm, looks like all the conversion_procs files have Assert(len 0); Surely that should be Assert(len = 0)? I also notice that I neglected to change PG_RETURN_INT32(0) to PG_RETURN_VOID() in these files. That's only cosmetic, but probably it should be done. Fixed. -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3beta and ecpg
On Thu, Sep 12, 2002 at 03:18:13PM -0400, Tom Lane wrote: Sure --- and that is exactly *not* what the backend facility does. In the backend PREPARE you supply the statement to be prepared directly in the same SQL command, not as the value of some variable. The variable will be replaced by ecpg. That's not a problem. The actual ecpg prepare function does insert the value of the variable when storing the so-called prepared statement, which of course is not prepared in reality. Now if you have a parameter in the prepared statement by just specify ? instead some value, you add a using clause during execution to set the values. And a plain ? isn't going to fly as the parameter marker, either. The backend wants to know what datatype each parameter is supposed to be. So, yes, this may be a problem we have to think about. But I could handle that by asking the backend for the datatypes before issuing the PREPARE statement and thus formulating it accordingly. Anyway, we could of course keep both ways seperate, but right now that would mean I have to disable access to the backend functions in ecpg or else the parser will be in trouble or else the parser will be in trouble. And frankly I don't really like that. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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]
My vote is tough, time to fix your SQL code. Sounds good to me, but please document it in the migration notes. No need for a surprise. Regards, Jeff ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Multicolumn foreign keys need useless unique indices?
There is a misfeature in 7.2.2 that appears when I have a foreign key that references two columns of a table. Consider following simplified example: CREATE TABLE a ( a int PRIMARY KEY, b int ); CREATE TABLE b ( aref int, bref int, FOREIGN KEY (aref, bref) REFERENCES a(a, b) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ); I get an error UNIQUE constraint matching given keys for referenced table a not found. because I have unique constraint only on the first field (which is still enough to make the whole combination unique. (b is not even unique))... So I need to add an useless(?) UNIQUE constraint to (a, b) for table a just to allow creation of multicol FOREIGN KEYs for table b. And I get NOTICE: CREATE TABLE / UNIQUE will create implicit index 'a_a_key' for table. AFAIK, the extra index only slows down my inserts - it basically contains no usable information... shouldn't the presence of _primary_key_ in multicol foreign key be enough to decide whether the whole key is unique or not? And shouldn't it be enough to find out the tuple in table 'a' corresponding newly inserted tuple in b? Or should I just write my own triggers for checking the integrity of b/bref column pair to avoid needless index creation? -- Antti Haapala ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TOAST docs
On Fri, 2002-09-13 at 00:09, Peter Eisentraut wrote: Alvaro Herrera writes: Is there some documentation on TOAST? No. Why do you need any? IIRC there were some ways to tweak when TOAST gets used, when it goes out to toastfile and when it uses compressed/non-compressed storage. I hope this is documented someplace, no ? - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multicolumn foreign keys need useless unique indices?
AFAIK, the extra index only slows down my inserts - it basically contains no usable information... Not 100% true. It will speed up cascade delete and update... shouldn't the presence of _primary_key_ in multicol foreign key be enough to decide whether the whole key is unique or not? Hmmm - thinking about it, I don't see why postgres would need the entire thing to be unique...can't think of a reason at the moment. Stephen? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] fixpoint
On Thu, 2002-09-12 at 20:33, Luciano Gerber wrote: Hi, Does anyone know any implementation of a fixpoint operator (recursive queries) for postgreSQL? I'm not sure i know about fixpoint, but you may get some help with recursive queries from connectby() function from contrib/tablefunc/ -- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Multicolumn foreign keys need useless unique indices?
AFAIK, the extra index only slows down my inserts - it basically contains no usable information... Not 100% true. It will speed up cascade delete and update... To clarify things: CREATE TABLE original ( a int PRIMARY KEY, b int ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'original_pkey' for table 'original' CREATE CREATE TABLE referencer ( aref int, bref int, FOREIGN KEY (aref, bref) REFERENCES original(a, b) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table original not found CREATE TABLE original ( a int PRIMARY KEY, b int, UNIQUE (a,b) ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'original_pkey' for table 'original' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'original_a_key' for table 'original' CREATE CREATE TABLE referencer ( aref int, bref int, FOREIGN KEY (aref, bref) REFERENCES original(a, b) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE ilmo=# \d original Table original Column | Type | Modifiers +-+--- a | integer | not null b | integer | Primary key: a_pkey Unique keys: a_a_key Triggers: RI_ConstraintTrigger_41250, RI_ConstraintTrigger_41252 ilmo=# \d referencer Table referencer Column | Type | Modifiers +-+--- aref | integer | bref | integer | Triggers: RI_ConstraintTrigger_41248 Actually nothing changes. The unique constraint doesn't add anything new - it allows NULLs in column b and requires that combination (a, b) is unique... and it definitely is because column 'a' is unique (primary key). It just creates a multicol index and adds an useless extra constraint check, while almost the same data is available in index original_a_pkey. -- Antti Haapala ---(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]
Sure it is. The float=int casts need to be made implicit, or we'll have tons of problems like this. Well, yeah. That did not seem to bother anyone last spring, when we were discussing tightening the implicit-casting rules. Shall we abandon all that work and go back to any available cast can be applied implicitly? My vote is tough, time to fix your SQL code. I personally don't think that is good. SQL users are used to using implicit casts. Other db's do handle them whereever possible. It is imho no answer to drop so many implicit casts only because of the corner cases where it does not work. What we imho really need is a runtime check that checks whether an implicit cast caused a loss of precision and abort in that case only. That is what other db's do. I thought that I voiced my opinion strong enough on this before, but I'll do it again, I think we should allow a lot more implicit casts than are now in beta. Especially in the numeric area. I don't have any strong arguments (other than other db's can do it), but this is my opinion. Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] [OT]Physical sites handling large data
Hi all, One of my friends is evaluating postgres for large databases. This is a select intensive application which is something similar to data-warehousing as far as I can see. The data is 150GB in flat files so would swell to 200GB+ with indexes. Is anybody running that kind of site? Any url? Any performance numbers/tuning tips for random selects? I would hate to put mysql there but we are evaluating that too. I would hate if postgres loses this to mysql because I didn't know few things about postgres. Secondly would it make a difference if I host that database on say, an HP-UX box? From some tests I have done for my job, single CPU HP-UX box trounces 4 way xeon box. Any suggestions in this directions? TIA.. Bye Shridhar -- Quality Control, n.:The process of testing one out of every 1,000 units coming off a production line to make sure that at least one out of 100 works. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Multicolumn foreign keys need useless unique indices?
On Fri, 2002-09-13 at 04:27, Christopher Kings-Lynne wrote: AFAIK, the extra index only slows down my inserts - it basically contains no usable information... Not 100% true. It will speed up cascade delete and update... shouldn't the presence of _primary_key_ in multicol foreign key be enough to decide whether the whole key is unique or not? Hmmm - thinking about it, I don't see why postgres would need the entire thing to be unique...can't think of a reason at the moment. Stephen? If it's not all unique, you cannot be guaranteed there is a single row with those values in the referenced table. -- Rod Taylor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org