[HACKERS] HISTORY (ecpg enhancements not yet mentioned)
Hi Bruce, you might add that I did the following useful enhancement to ECPG: - EXECUTE ... INTO ...implemented - multiple row descriptor support (e.g. CARDINALITY) I don't feel that my humble contribution of a few lines is important but the improvement made really is important (n times performance if you use it). Yours Christof ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Creating unique constraints on OID
Shouldn't this work? create table test ( a int, unique (oid) ); ERROR: CREATE TABLE: column oid named in key does not exist Because this works: create table test ( a int ); CREATE alter table test add unique (oid); NOTICE: ALTER TABLE/UNIQUE will create implicit index 'test_oid_key' for table 'test' CREATE And shouldn't the last one say ALTER? -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Error while restoring database
Johann Zuschlag [EMAIL PROTECTED] writes: create function numeric_neq(numeric,float8) returns bool as ' select $1 = $2::numeric; ' language 'sql'; create operator ( leftarg=numeric, rightarg=float8, procedure=numeric_neq, commutator='', negator='', ^^ restrict=eqsel, join=eqjoinsel ); Well, there's your problem... For 7.2, I have added some error checking to the system that will prevent it accepting invalid operator names in commutator/negator parameters. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Error while restoring database
Johann Zuschlag [EMAIL PROTECTED] writes: select * from pg_operator where oid = 280343; select * from pg_operator where oid = 280344; Attached you find the results of the above selects. Okay ... are there any rows in pg_operator with OID 280346 or 280347 ? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Error while restoring database
Johann Zuschlag [EMAIL PROTECTED] writes: Okay ... are there any rows in pg_operator with OID 280346 or 280347 ? Yes, seems so. See the attachment. Again, the negator stuff never worked for numeric. Looks like these are shell operator definitions left over from commutator or negator forward references that were never satisfied. pg_dump did the right thing to not dump them. I'd say that the backend should never have accepted a shell operator def with an empty name, though, which is what you seem to have at OID 280347. Do you happen to have the exact command that you gave to create operator 280343 (numeric_neq)? I think what this really boils down to is insufficient error checking somewhere in CREATE OPERATOR. 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
[HACKERS] createlang difficulty.
Postgresql 7.1.3 I'm having a problem with createlang. Commands: [postgres@boxy postgres]$ createdb test1 Password: - Correct password CREATE DATABASE [postgres@boxy postgres]$ createlang plpgsql test1 Password: - Correct password (does not say it was incorrect, the first character is upper case) Password: - Incorrect password something psql: Password authentication failed for user 'postgres' Password: - Incorrect password something psql: Password authentication failed for user 'postgres' createlang: language installation failed [postgres@boxy postgres]$ Logs corresponding to those commands: 2001-10-22 15:15:22 [13115] DEBUG: connection: host=[local] user=postgres database=template1 2001-10-22 15:15:33 [13125] DEBUG: connection: host=[local] user=postgres database=test1 Password authentication failed for user 'postgres' Password authentication failed for user 'postgres' pg_hba.conf entry: localall crypt Now again have a look at this (quite interesting): [postgres@boxy postgres]$ dropdb test1 Password: DROP DATABASE [postgres@boxy postgres]$ createdb test1 Password: CREATE DATABASE [postgres@boxy postgres]$ createlang -l test1 Password: Procedural languages Name | Trusted? | Compiler --+--+-- (0 rows) [postgres@boxy postgres]$ createlang plpgsql test1 Password: Password: Password: Password: [postgres@boxy postgres]$ createlang -l test1 Password: Procedural languages Name | Trusted? | Compiler -+--+-- plpgsql | t| PL/pgSQL (1 row) [postgres@boxy postgres]$ I had to enter the password 4 times for it to create the language. Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] namespaces
- Original Message - From: Bill Studenmund [EMAIL PROTECTED] Sent: Friday, October 19, 2001 2:04 PM It means that when you want to use one of the built in functions (date_part, abs, floor, sqrt etc.) you don't have to prefix it with standard.. You can just say date_part(), abs(), floor(), sqrt(), etc. The only time you need to prefix a call with standard. is if you want to exclude any so-named routines in your own package. Quick question: would it be possible then create a 'system' package and 'system' (or 'master' if you will) schema (when it's implemented), move over all the system tables (pg_*) into the master schema and functions into the 'system' package, so that no name conflicts will arise when creating types, functions, tables, etc with the same names as system ones? Yes. That is part of my plan actually. :-) Hmm. I see. Then there won't be a problem of creating any DB object with the system name. In the patch I sent in last week, Yeah, I remember that one. Took me a couple of minutes to download. You know, it never hurts to compress things: then the patch would be ~10 times less in size, and you wouldn't have to worry about PINE messing up with your code in the message body... :) And that would reduce the bounce rate too. Just a kind and gentle cry to reduce the size of patches sent to my mailbox and save some bandwidth on the way :) all of the built-in functions and aggregates are in the standard package, and you can infact reference them as standard.foo. When you refer to it just foo(), and you have foo() defined in more than one package, how do you resolve this? Do you also have a notion of a global package and sub-packages? -- Serguei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] namespaces
On Sun, 21 Oct 2001, Serguei Mokhov wrote: - Original Message - From: Bill Studenmund [EMAIL PROTECTED] Sent: Friday, October 19, 2001 2:04 PM Quick question: would it be possible then create a 'system' package and 'system' (or 'master' if you will) schema (when it's implemented), move over all the system tables (pg_*) into the master schema and functions into the 'system' package, so that no name conflicts will arise when creating types, functions, tables, etc with the same names as system ones? Yes. That is part of my plan actually. :-) Oh, one reason that needs to happen is that everything needs to be in a package or a schema; for the tables where they do namespacing the schema or package is part of the primary key. Hmm. I see. Then there won't be a problem of creating any DB object with the system name. It will work, though if you start creating tables named pg_class, I think you might make your head hurt. Also, your own int4 type might not be such a good idea... In the patch I sent in last week, Yeah, I remember that one. Took me a couple of minutes to download. You know, it never hurts to compress things: then the patch would be ~10 times less in size, and you wouldn't have to worry about PINE messing up with your code in the message body... :) And that would reduce the bounce rate too. Just a kind and gentle cry to reduce the size of patches sent to my mailbox and save some bandwidth on the way :) Ok. :-) Next time I will either compress it or I'll mail in a URL. all of the built-in functions and aggregates are in the standard package, and you can infact reference them as standard.foo. When you refer to it just foo(), and you have foo() defined in more than one package, how do you resolve this? Do you also have a notion of a global package and sub-packages? There is a very simple search path system. If you are in a package (in a function that is part of a package), you look for foo in that package. If you don't find it there, you look in stadard. If it's not there, you don't find it. To look in other packages than the one you're in, you have to say which one it is. With schemas, if your package is not in master or whatever it is called, you look first in your package, then in your_schema.standard, then in master.standard. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the select limit
Thomas Lockhart [EMAIL PROTECTED] writes: I'm with Peter on this one. I'd like to *not* clutter up the code and error reporting with hints and suggestions which may or may not be to the point. We *should* have docs which list error messages and possible solutions, and throwing that info into code is a poor second choice imho. While you have a point in the abstract, a big difficulty is that the docs never track the code with any accuracy. Look at the Outputs portions of our existing reference pages. To the extent that they describe possible errors at all, the information is a sad joke: out of date in most cases, certainly incomplete in every case. Just last week I was thinking that we should rip all that stuff out, rather than pretend it is or ever will be accurate. I recommend tips when they are one line in length, have a high probability of being accurate, and are common mistakes. Anything longer and we should point to a specific section in the docs. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] createlang difficulty.
I just tried it with current sources and got: #$ aspg createlang plpgsql test Password: - bad password FATAL 1: Password authentication failed for user postgres psql: FATAL 1: Password authentication failed for user postgres createlang: external error #$ aspg createlang plpgsql test Password: - correct password Password: - correct password Password: - correct password Password: - correct password Looks OK to me. Ok, so it connects four times. From a users perspective that might be confusing. Is it possible to only prompt once for the password, just an idea I guess whoever created the program would want that to happen from a easy to use point of view. I.e. dummy proof? Thankyou. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Error while restoring database
On Sun, 21 Oct 2001 12:42:57 -0400, Tom Lane wrote: select * from pg_operator where oid = 280343; select * from pg_operator where oid = 280344; Attached you find the results of the above selects. Okay ... are there any rows in pg_operator with OID 280346 or 280347 ? Yes, seems so. See the attachment. Again, the negator stuff never worked for numeric. regards Johann Zuschlag [EMAIL PROTECTED] euro= select * from pg_operator where oid = 280346; oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin -+--+-+-+---++-+--+---++---+++-+-+- | 27 | 0 | b | f | f | 701 | 1700 | 0 | 0 | 0 | 0 | 0 | - | - | - (1 row) euro= select * from pg_operator where oid = 280347; oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin -+--+-+-+---++-+--+---++---+++-+-+- | 27 | 0 | b | f | f |1700 | 701 | 0 | 0 | 0 | 0 | 0 | - | - | - (1 row) ---(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] Error while restoring database
On Sat, 20 Oct 2001 12:36:16 -0400, Tom Lane wrote: That's what you showed us already. What I'd like to see is the original database contents, particularly select * from pg_operator where oid = 280343; select * from pg_operator where oid = 280344; so we can see why pg_dump is producing the bogus output. I'm sorry. I'm not so deep in the internals of postgreSQL. I'm just doing some psqlodbc-supports and tests. Attached you find the results of the above selects. regards Johann Zuschlag [EMAIL PROTECTED] euro= select * from pg_operator where oid = 280343; oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin -+--+-+-+---++-+--+---++---+++-+-+--- | 27 | 0 | b | t | f |1700 | 701 |16 | 280346 |280347 | 0 | 0 | numeric_neq | eqsel | eqjoinsel (1 row) euro= select * from pg_operator where oid = 280344; oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin -+--+-+-+---++-+--+---++---++++-+--- = | 27 | 0 | b | t | f |1700 | 701 |16 | 280342 |280343 | 0 | 0 | numeric_eq | eqsel | eqjoinsel (1 row) ---(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] createlang difficulty.
speedboy [EMAIL PROTECTED] writes: Ok, so it connects four times. From a users perspective that might be confusing. Is it possible to only prompt once for the password, This would require replacing the createlang shell script with a specialized C program. (Or, perhaps, adding conditional-execution capability to psql scripts ... which would be very useful but an awful lot of work.) It's unlikely to get to the top of anyone's to-do list any time soon, because the fact of the matter is that if you have Postgres configured to demand passwords for administrator connections, you're going to have lots of problems like this. createlang is not the only script that invokes multiple programs --- pg_dumpall is another example that's going to be even harder to work around. The better answer is to arrange things so that local connections don't need passwords. One fairly portable approach is to run an IDENTD daemon and use ident auth for TCP connections through 127.0.0.1; then you just say PGHOST=127.0.0.1 and you're home free. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PL/pgSQL RENAME bug?
Good day, My name is John Worsley, I'm one of the authors of the new O'Reilly PostgrSQL book. We're wrapping up the PL/pgSQL chapter's technical edit right now, but there are a couple of concerns that I was hoping someone might be able to help with. Mainly, the existing documentation on the RENAME statement seems inaccurate; it states that you can re-name variables, records, or rowtypes. However, in practice, our tests show that attempting to RENAME valid variables with: RENAME varname TO newname; ...yeilds a PL/pgSQL parse error, inexplicably. If I try the same syntax on a non-declared variable, it actually says there is no variable with that name in the current block, so...I think something odd is happening. :) I believe we have only gotten RENAME to work with either the NEW or OLD record variables when using PL/pgSQL with triggers, but the documentation suggests that this should be a general-purpose statement. Any assistance would be greatly appreciated. :) The RENAME statement seems kind of odd, since it seems that you could just as easily declare a general variable with the right name to begin with, and maybe that's why this isn't apparently documented anywhere else? I just want to make sure the documentation is both accurate and complete. Kind Regards, Jw. -- John Worsley, Command Prompt, Inc. [EMAIL PROTECTED] by way of [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the selectlimit
Not possible to accept both forms at present and issue a notice that LIMIT m,n is deprecated? If LIMIT m,n is found, internally re-write it to LIMIT m OFFSET n and press on. This should appease everyone and still allow the 'proper' form to be implemented right now. There isn't just the question of when it appears in pgsql, but when it appears in everyone else's code that depends on postgres. If you delay LIMIT..OFFSET, then I too am affected in my code. If I use it today and my code is in beta (which it is), then when it goes release, I'll have to issue a change in the future for that. Granted it's not a big thing for me, but if I have 200,000 installations, that means eventually there will have to be 200,000 upgrades when they upgrade postgres. We all know that everyone updates their software frequently and in a timely manner to keep things running smoothly, right? *cough* David Tom Lane wrote: Given the amount of noise being raised on the issue now, I think the better part of valor is to revert to the 7.1 behavior and plan to discuss it again for 7.3. But it's not like Bruce did this with no warning or discussion. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] CREATE RULE ON UPDATE/DELETE
On Sat, 20 Oct 2001, Joel Burton wrote: On Sat, 20 Oct 2001, Aasmund Midttun Godal wrote: Can a rule see the where statement in a query which it has been triggered by? or is it simply ignored?? what happens? Looking over your question, I wanted to clarify the problem a bit, so: (cleaned up example a bit from Aasmund) drop view normal; drop view dbl; drop table raw; CREATE TABLE raw (id INT PRIMARY KEY, name TEXT ); INSERT INTO raw VALUES(1, 'a'); INSERT INTO raw VALUES(2, 'b'); INSERT INTO raw VALUES(12, 'c'); INSERT INTO raw VALUES(15, 'd'); INSERT INTO raw VALUES(14, 'e'); -- set up two views: normal, a simple view, -- and dbl, which shows id * 2 -- create basic rules to allow update to both views CREATE VIEW normal AS SELECT * FROM raw; CREATE RULE normal_update AS ON UPDATE TO normal DO INSTEAD UPDATE raw SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw; CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET id = NEW.id, name = NEW.name WHERE OLD.id = id; The issue is that there are no IDs over 10 that have another ID that is exactly their value, so the first update to dbl does nothing. The second time, w/o the ID10 restriction, it finds 1(a), and double that, 2(b), and adds 10; getting confused about which record to edit. Is this the best way to interpret this? Is this a bug? Don't think so. I think the rule doesn't make any sense. NEW.id and OLD.id are probably dbl values, so saying OLD.id=id (where id is raw.id since that's the update table) isn't correct. It probably should be OLD.id=id*2 (which seems to work for me, btw) It's editing a different row than the one that's being selected. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] createlang difficulty.
I just tried it with current sources and got: #$ aspg createlang plpgsql test Password: - bad password FATAL 1: Password authentication failed for user postgres psql: FATAL 1: Password authentication failed for user postgres createlang: external error #$ aspg createlang plpgsql test Password: - correct password Password: - correct password Password: - correct password Password: - correct password Looks OK to me. Postgresql 7.1.3 I'm having a problem with createlang. Commands: [postgres@boxy postgres]$ createdb test1 Password: - Correct password CREATE DATABASE [postgres@boxy postgres]$ createlang plpgsql test1 Password: - Correct password (does not say it was incorrect, the first character is upper case) Password: - Incorrect password something psql: Password authentication failed for user 'postgres' Password: - Incorrect password something psql: Password authentication failed for user 'postgres' createlang: language installation failed [postgres@boxy postgres]$ Logs corresponding to those commands: 2001-10-22 15:15:22 [13115] DEBUG: connection: host=[local] user=postgres database=template1 2001-10-22 15:15:33 [13125] DEBUG: connection: host=[local] user=postgres database=test1 Password authentication failed for user 'postgres' Password authentication failed for user 'postgres' pg_hba.conf entry: localall crypt Now again have a look at this (quite interesting): [postgres@boxy postgres]$ dropdb test1 Password: DROP DATABASE [postgres@boxy postgres]$ createdb test1 Password: CREATE DATABASE [postgres@boxy postgres]$ createlang -l test1 Password: Procedural languages Name | Trusted? | Compiler --+--+-- (0 rows) [postgres@boxy postgres]$ createlang plpgsql test1 Password: Password: Password: Password: [postgres@boxy postgres]$ createlang -l test1 Password: Procedural languages Name | Trusted? | Compiler -+--+-- plpgsql | t| PL/pgSQL (1 row) [postgres@boxy postgres]$ I had to enter the password 4 times for it to create the language. Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Does postmaster -i...
Thomas Lockhart [EMAIL PROTECTED] writes: A bit more information: an unadorned -i fails: I believe this is fixed now. 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] [GENERAL] To Postgres Devs : Wouldn't changing the selectlimit
Thomas Lockhart [EMAIL PROTECTED] writes: I think Hiroshi's point is the same as mine: discussions of feature changes need to happen on -hackers before being implemented. Well, IIRC there *was* some discussion about this some months back, and no one particularly objected to changing it to be compatible with MySQL. That's why Bruce felt free to execute on the TODO item despite being so close to beta. Subscriptions to other mailing lists should not be required to stay up with mainstream development issues. Actually, the reason we have an argument now is the other way around: some non-hackers people complained when the change notice went by. We do have an obligation to users who don't read -hackers. Given the amount of noise being raised on the issue now, I think the better part of valor is to revert to the 7.1 behavior and plan to discuss it again for 7.3. But it's not like Bruce did this with no warning or discussion. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Index of a table is not used (in any case)
Hello PostgreSQl Users! PostSQL V 7.1.1: I have defined a table and the necessary indices. But the index is not used in every SELECT. (Therefore, the selects are *very* slow, due to seq scan on 20 million entries, which is a test setup up to now) The definitions can be seen in the annex. Does some body know the reason and how to circumvent the seq scan? Is the order of index creation relevant? I.e., should I create the indices before inserting entries or the other way around? Should a hashing index be used? (I tried this, but I got the known error Out of overflow pages) (The docu on create index says : Notes The Postgres query optimizer will consider using a btree index whenever an indexed attribute is involved in a comparison using one of: , =, =, =, The Postgres query optimizer will consider using an rtree index whenever an indexed attribute is involved in a comparison using one of: , , , , @, ~=, The Postgres query optimizer will consider using a hash index whenever an indexed attribute is involved in a comparison using the = operator. The table entry 'epoche' is used in two different indices. Should that be avoided? Any suggestions are welcome. Thank you in advance. Reiner -- Annex: == Table: -- \d wetter Table wetter Attribute | Type | Modifier ---+--+-- sensor_id | integer | not null epoche| timestamp with time zone | not null wert | real | not null Indices: wetter_epoche_idx, wetter_pkey \d wetter_epoche_idx Index wetter_epoche_idx Attribute | Type ---+-- epoche| timestamp with time zone btree \d wetter_pkey Index wetter_pkey Attribute | Type ---+-- sensor_id | integer epoche| timestamp with time zone unique btree (primary key) Select where index is used: explain select * from wetter order by epoche desc; NOTICE: QUERY PLAN: Index Scan Backward using wetter_epoche_idx on wetter (cost=0.00..3216018.59 rows=2034 width=16) EXPLAIN Select where the index is NOT used: === explain select * from wetter where epoche between '1970-01-01' and '1980-01-01' order by epoche asc; NOTICE: QUERY PLAN: Sort (cost=480705.74..480705.74 rows=203400 width=16) - Seq Scan on wetter (cost=0.00..454852.00 rows=203400 width=16) EXPLAIN -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the select
Bruce Momjian wrote: [snip] What do others think? Please reverse your change and go into beta quickly. I need more information. What do you want reversed, and are there enough votes to reverse those votes already made? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] schema support, was Package support for Postgres
On Sun, 21 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: The big one for now is how should you log into one schema or another? psql database.schema ? Each user has a default schema, which is by default the schema with the same name as the user name, or if no such schema exists, it's the DEFAULT schema (which I believe is what Oracle calls it). Then there should be something like set schema path. I don't think schemas should be a connection parameter. -- That would be my ideas anyway. I can see advantages for both; if you just connect to a database that has schemas, you get a schema with your name if it's there, and a default otherwise. But I can see definite advantages to being able to specify. Whenever you look up a function or aggregate, you give the oid of the package to look in in addition to the name (and types). Having the package id in the index provides the namespacing. Whenever you look up a type or operator, you don't have to give a package id. While I understand that package.+ is silly, anything that make operators and functions work fundamentally differently is suspicious. A common search mechanism that works for everything in packages (or subschemas, which I'd prefer) would/should/could allow you to do without those prefixes. Why? Operators are used differently than functions. That strikes me as a good reason to namespace them differently. Conceptually the main determiner of what function you want is the name, at least as far as from what I can tell from talking with all the programmers I know. Yes, we make sure the types match (are part of the primary key), but the name is the main concept. Operators, however, are more intent-based. The '+' operator means I want these two things added together. I don't care so much what types are involved, I want adding to happen. That's a difference of intent. And that's the reason that I think different namespacing rules make sense. Part of it is that I only expect a package to add operators for types it introduced. So to be considering them, you had to have done something that ties in the type in the package. Like you had to make a column in a table using it. Another take on that is that I expect the main user of (direct) function calls calling package functions will be other functions in that package, while the main users of operators will be places which have used a type from said package. Like queries pulling things out of tables using that type. So the function namespacing is a concenience/tool primarily for the package developer, while the operator and type namespacing is more a convenience for the end application developer. Also, you seem to be wanting a path-search ability that is something like the PATH environment variable. This pathing is fundamentally different; to use unix terms, it is .:... The fundamental difference is that there are no absolute paths. The searching is totally location (of routine) dependant. To add something like an absolute path would totally break the whole motivation for packages. The idea is to give a developer an area overwhich s/he has total name control, but if s/he needs built-in routines, s/he doesn't need to say standard. to get at them. If we allow something like absolute paths in the package namespacing, then we totally destroy that. Because a package developer can't be sure what pathing is going on, s/he really has no clue what packages will get found in what order. So then you have to be explicit in the name of all the functions you use (otherwise if a user essentially puts something other than . at the head of the path, then you don't get routines in your own package), or run the risk of getting all sorts of run-time errors. A feature designed to make writing packages easier now makes them harder. That strikes me as a step backwards. There is a built-in schema, master. It will have a fixed oid, probalby 9 or 11. The built-in schemas is called DEFINITION_SCHEMA. Why is it different from the DEFAULT you get when you log into a database which doesn't have a schema whose name matches your username? The only other part (which is no small one) is to add namespacing to the rest of the backend. I expect that will mean adding a schema column to pg_class, pg_type, and pg_operator. Yup. But you can replace the owner package with the schema column, because the owner property will be transferred to the schema. Not necessarily. A user other than the one who owns the schema can add a package to it. It's the same thing as why we keep track of who added a function. :-) Take care, Bill ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the select limit
(switched thread to hackers) ... If the 'tip' is localized to a few lines, usually in gram.y, I don't see a reason not to help people find the right answer. It helps them and reduces redundant bug repots. I can't imagine a reason not to do it unless it starts to make our code more complex. I'm with Peter on this one. I'd like to *not* clutter up the code and error reporting with hints and suggestions which may or may not be to the point. We *should* have docs which list error messages and possible solutions, and throwing that info into code is a poor second choice imho. - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] CREATE TABLE AS / WITHOUT OIDs?
Shouldn't there be some form of CREATE TABLE AS / WITHOUT OIDS? -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the select
Bruce Momjian wrote: (switched thread to hackers) ... If the 'tip' is localized to a few lines, usually in gram.y, I don't see a reason not to help people find the right answer. It helps them and reduces redundant bug repots. I can't imagine a reason not to do it unless it starts to make our code more complex. I'm with Peter on this one. I'd like to *not* clutter up the code and error reporting with hints and suggestions which may or may not be to the point. We *should* have docs which list error messages and possible solutions, and throwing that info into code is a poor second choice imho. Is it really clutter to add a clause and elog(). I am not advocating adding stuff like crazy, but when we see people having the same problem, it seems worth adding it. Our docs are pretty big and most people who have this type of problem are not going to know where to look in the docs. If the elog pointed them to the proper section in the docs, that would be even better, but then again, you are doing the elog at that point. What do others think? It would be good to have a specific example to discuss. FWIW, Oracle has its oerr utility which takes the arguments: oerr facility error-code So the RDBMS generates an error code with a single line message less than or equal to 76 characters in length, prefixed by the facility and error code: ORA-01034: ORACLE not available The user can then get detailed information through the oerr utility. It would be nice, when we have error codes (are they apart of the new NLS support?), we have a pgerr utility to serve the same purpose. And of course the message files shipped with Oracle contain localized messages. Example output: $oerr ora 12203 12203, 0, TNS:unable to connect to destination // *Cause: Invalid TNS address supplied or destination is not listening. // This error can also occur because of underlying network transport // problems. // *Action: Verify that the service name you entered on the command line // was correct. Ensure that the listener is running at the remote node and // that the ADDRESS parameters specified in TNSNAMES.ORA are correct. // Finally, check that all Interchanges needed to make the connection are // up and running. It would then be nice to have both a command-line version of the PostgreSQL equivalent and a web-based version on postgresql.org for users to use. Just my 2 cents, of course, Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] HISTORY (ecpg enhancements not yet mentioned)
Added. I will update the HISTORY file today or tomorrow to add newer changes than 2001-09-13. --- Hi Bruce, you might add that I did the following useful enhancement to ECPG: - EXECUTE ... INTO ...implemented - multiple row descriptor support (e.g. CARDINALITY) I don't feel that my humble contribution of a few lines is important but the improvement made really is important (n times performance if you use it). Yours Christof ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] schema support, was Package support for Postgres
Bill Studenmund writes: The big one for now is how should you log into one schema or another? psql database.schema ? Each user has a default schema, which is by default the schema with the same name as the user name, or if no such schema exists, it's the DEFAULT schema (which I believe is what Oracle calls it). Then there should be something like set schema path. I don't think schemas should be a connection parameter. -- That would be my ideas anyway. Whenever you look up a function or aggregate, you give the oid of the package to look in in addition to the name (and types). Having the package id in the index provides the namespacing. Whenever you look up a type or operator, you don't have to give a package id. While I understand that package.+ is silly, anything that make operators and functions work fundamentally differently is suspicious. A common search mechanism that works for everything in packages (or subschemas, which I'd prefer) would/should/could allow you to do without those prefixes. There is a built-in schema, master. It will have a fixed oid, probalby 9 or 11. The built-in schemas is called DEFINITION_SCHEMA. The only other part (which is no small one) is to add namespacing to the rest of the backend. I expect that will mean adding a schema column to pg_class, pg_type, and pg_operator. Yup. But you can replace the owner package with the schema column, because the owner property will be transferred to the schema. Hmmm... We probably also need a command to create operator classes, and the tables it touches would need a schema column too, and accesses will need to be schema savy. Well, that's a lot for now. Thoughts? That lot was sort of the problem with tackling this until now. ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] CREATE RULE ON UPDATE/DELETE
Joel Burton [EMAIL PROTECTED] writes: CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw; CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET id = NEW.id, name = NEW.name WHERE OLD.id = id; Surely you'd need something like CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2; (untested...) 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] [SQL] CREATE RULE ON UPDATE/DELETE
Don't think so. I think the rule doesn't make any sense. NEW.id and OLD.id are probably dbl values, so saying OLD.id=id (where id is raw.id since that's the update table) isn't correct. It probably should be OLD.id=id*2 (which seems to work for me, btw) It's editing a different row than the one that's being selected. I forgot to mention in this that I needed to made an additional change in the rule to make the ids come out correct at the end :(. The update set id=NEW.id should be id=NEW.id/2 of course... Otherwise the +10 becomes a +20. ---(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] [GENERAL] To Postgres Devs : Wouldn't changing the select limit
I am confused. While LIMIT and OFFSET may are potential SQL standard reserved words, I don't see how LIMIT #,# would ever be a standard specification. Do you see this somewhere I am missing. Again, LIMIT #,# is the only syntax we are removing. If you are confident that LIMIT #,# would never be an official SQL standard who am I to second guess that ;) I don't see that possibility anywhere either, but I just wanted to make sure. The possibility that it might become an official standard is the only objection I had against deprecating and eventual elimination of that syntax. LIMIT # OFFSET # has my vote. OK, we have received only one vote to keep LIMIT #,# working for one more release, and several to remove it so I am committing a patch now to remove LIMIT #,# and instead have them use LIMIT # OFFSET #: I've cc'd this to the hackers list. I know the discussion started on general, but if I hadn't been subscribed to *that* list I'd have never known about any of this. And noone else on hackers would either. - Thomas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Package support for Postgres
On Sat, 20 Oct 2001, Rod Taylor wrote: But what if you want a C function to set a variable which can be accessed using an SQL, perl, PLpgSQL or other function type? Shouldn't a global variable be global between all types of functions? No. Doing that requires that all languages have the same internal storage of variables. And it's more than just an int4 takes up 4 bytes. Look in the plpgsql source, at struct PLpgSQL_var. There is a fair amount of into about a variable. While we could harmonize the info storage, making globals global across all languages would also would mean breaking down a lot of the isolation between PLs. Right now they are their own independent entities. To tie them together like this would, in my opinion, make them fragilly-interconnected. My suggestion is to just add a get and a set routine in one language, and have it store the global. :-) Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] snapshots now working
I just checked: ftp://ftp.us.postgresql.org/dev/postgresql-base-snapshot.tar.gz and the snapshot has the proper file contents, showing doc/TODO with a date of October 19th. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Catalogs design question
Hi Steve, Your question about - pg_proc select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[0] = t.oid ; select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[1] = t.oid ; ... select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[7] = t.oid ; As far as I understand the proargtypes entries 0 means no further parameter. This oidvector type of proargtypes seems to have a start index of 0. As long as there are at maximum 8 parameters allowed, this looks practicable. Your question about - pg_group The pg_group column is more bulky, because the int4[] type does not have an upper limit. So, the only solution I can see is get the number of array elements of the group you want to query select array_dims(grolist) from pg_group where groname = 'your_group'; and then generate automatically a query like select u.usename from pg_user u , pg_group g where g.grolist[1] = u.usesysid and g.groname='your_group' union select u.usename from pg_user u , pg_group g where g.grolist[2] = u.usesysid and g.groname='your_group' union ... select u.usename from pg_user u , pg_group g where g.grolist[n] = u.usesysid and g.groname='your_group' ; This looks very much like another crude hack you've already complained about. Sorry, but I can't help. Two more items I do not understand: You said, the procedures to search arrays in contrib/ are slow. Maybe that's true, but usually you do not have thousands of users in a group, don't you. You said, many users cannot compile this contrib code. Yes, and they are not supposed to do so, because it's up to a system admin to do. What do I miss here? Regards, Christoph ---(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] [GENERAL] To Postgres Devs : Wouldn't changing the select
Bruce Momjian wrote: [snip] What do others think? Please reverse your change and go into beta quickly. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the select limit
I am confused. While LIMIT and OFFSET may are potential SQL standard reserved words, I don't see how LIMIT #,# would ever be a standard specification. Do you see this somewhere I am missing. Again, LIMIT #,# is the only syntax we are removing. If you are confident that LIMIT #,# would never be an official SQL standard who am I to second guess that ;) I don't see that possibility anywhere either, but I just wanted to make sure. The possibility that it might become an official standard is the only objection I had against deprecating and eventual elimination of that syntax. LIMIT # OFFSET # has my vote. OK, we have received only one vote to keep LIMIT #,# working for one more release, and several to remove it so I am committing a patch now to remove LIMIT #,# and instead have them use LIMIT # OFFSET #: I've cc'd this to the hackers list. I know the discussion started on general, but if I hadn't been subscribed to *that* list I'd have never known about any of this. And noone else on hackers would either. The discussion has moved from patches to general so our general users could comment on this. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the
Bruce Momjian wrote: I don't think that enough votes are needed to reverse the change. You broke the discussion first rule. Are you subscribed to general? We had a big discussion there and there I know the discussion and I've thought Peter's objection was suffienctly valid to reverse your change but the discussion has continued. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Creating unique constraints on OID
So the result of all this is that the behaviour of my ADD UNIQUE code is correct in this case? Peter Eisentraut [EMAIL PROTECTED] writes: Shouldn't this work? create table test ( a int, unique (oid) ); ERROR: CREATE TABLE: column oid named in key does not exist Now it does. In 7.2 you mean? Or did you just fix it then? And shouldn't the last one say ALTER? The reason that happens is that parser/analyze.c transforms the command into an ALTER TABLE step that adds a constraint (a no-op in this case) plus a CREATE INDEX step. The commandTag emitted by the last step is what psql shows. This could possibly be fixed, but it seems not worth the trouble. If it were to be changed - I really wouldn't know where to do that... 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] Index of a table is not used (in any case)
Hello PostgreSQl Users! PostSQL V 7.1.1: You should upgrade to 7.1.3 at some point... I have defined a table and the necessary indices. But the index is not used in every SELECT. (Therefore, the selects are *very* slow, due to seq scan on 20 million entries, which is a test setup up to now) The definitions can be seen in the annex. Does some body know the reason and how to circumvent the seq scan? Yes. You probably have not run 'VACUUM ANALYZE' on your large table. Is the order of index creation relevant? I.e., should I create the indices before inserting entries or the other way around? If you are inserting a great many entries, insert the data first and then create the indices - it will be much faster this way. Should a hashing index be used? (I tried this, but I got the known error Out of overflow pages) Just do the default CREATE INDEX - btree should be fine... (probably) The table entry 'epoche' is used in two different indices. Should that be avoided? It's not a problem, but just check your EXPLAIN output after the VACUUM to check that you have them right. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Creating unique constraints on OID
Christopher Kings-Lynne [EMAIL PROTECTED] writes: So the result of all this is that the behaviour of my ADD UNIQUE code is correct in this case? The AlterTable code wasn't broken; the error was in parser/analyze.c, which was prematurely rejecting the command. Peter Eisentraut [EMAIL PROTECTED] writes: Shouldn't this work? create table test ( a int, unique (oid) ); ERROR: CREATE TABLE: column oid named in key does not exist Now it does. In 7.2 you mean? Or did you just fix it then? I just fixed it moments before sending that message. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] LIMIT TODO item
[ BCC to general ] Added to TODO: * Remove LIMIT #,# and force use LIMIT and OFFSET clauses in 7.3 (Bruce) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 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] [GENERAL] To Postgres Devs : Wouldn't changing the selectlimit
On Monday 22 October 2001 10:32 pm, Tom Lane wrote: Thomas Lockhart [EMAIL PROTECTED] writes: I think Hiroshi's point is the same as mine: discussions of feature changes need to happen on -hackers before being implemented. [snip] Subscriptions to other mailing lists should not be required to stay up with mainstream development issues. Actually, the reason we have an argument now is the other way around: some non-hackers people complained when the change notice went by. We do have an obligation to users who don't read -hackers. If they want to deal with development issues, let them subscribe to hackers. Sorry, I know that's more than a little rude. But that _is_ what the hackers list is for, right? 'The developers live there' is the advertisement. As I'm subscribed to most of the postgresql lists, I sometimes miss which list it's on -- but I'll have to say that I agree with both Thomas and Bruce: the behavior needs to be fixed, AND it needs to be discussed on hackers before fixing. Given the amount of noise being raised on the issue now, I think the better part of valor is to revert to the 7.1 behavior and plan to discuss it again for 7.3. But it's not like Bruce did this with no warning or discussion. Communications breakdown either way. The warning and discussion was on general -- a bcc to hackers would have been a good thing, IMHO. But that's past. It's mighty close to beta -- is this fix a showstopper? The behavior currently is rather broken according to the results of the discussion on general. Do we really want a whole 'nother major version cycle to pass before this kludge is fixed? Six months to a year down the road? The longer this behavior is in the code, the harder it's going to be to remove it, IMNSHO. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the selectlimit
Thomas Lockhart [EMAIL PROTECTED] writes: I think Hiroshi's point is the same as mine: discussions of feature changes need to happen on -hackers before being implemented. Well, IIRC there *was* some discussion about this some months back, and no one particularly objected to changing it to be compatible with MySQL. That's why Bruce felt free to execute on the TODO item despite being so close to beta. Subscriptions to other mailing lists should not be required to stay up with mainstream development issues. Actually, the reason we have an argument now is the other way around: some non-hackers people complained when the change notice went by. We do have an obligation to users who don't read -hackers. Given the amount of noise being raised on the issue now, I think the better part of valor is to revert to the 7.1 behavior and plan to discuss it again for 7.3. But it's not like Bruce did this with no warning or discussion. [ BCC to general ] I agree. Let me reverse this to 7.1 behavior, and note in the HISTORY file that LIMIT #,# will be removed in 7.3. That way, people know it is coming and it gives them one release to fix their queries. I know Tom wanted it removed right away because it is so confusing but I think we have enough votes to keep it around, unchanged, for another release. As to whether we should emit a NOTICE every time LIMIT #,# is used, I think not, but if people want it I can add it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Index of a table is not used (in any case)
Reiner Dassing [EMAIL PROTECTED] writes: Hello PostgreSQl Users! PostSQL V 7.1.1: I have defined a table and the necessary indices. But the index is not used in every SELECT. (Therefore, the selects are *very* slow, due to seq scan on 20 million entries, which is a test setup up to now) Perennial first question: did you VACUUM ANALYZE? -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(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] [GENERAL] To Postgres Devs : Wouldn't changing the selectlimit
But that's past. It's mighty close to beta -- is this fix a showstopper? The behavior currently is rather broken according to the results of the discussion on general. Do we really want a whole 'nother major version cycle to pass before this kludge is fixed? Six months to a year down the road? The longer this behavior is in the code, the harder it's going to be to remove it, IMNSHO. We just have too many opinions here. I have put it back and noted it will be removed in 7.3. If someone else wants to propose it to be removed in 7.2 and have a vote, and do the work, and take the heat, go ahead. I am not going to do it. It is just like the grief I got over jdbc patches for 7.1. At some point it is not worth having people get upset at me over it. Basically, you have removed any desire I have to resolve this. FYI, my personal opinion is that we should keep it around for one more release because forcing people to remove it from the queries with no warning is more disruptive, I think, than the fact we don't match MySQL's syntax. Also, LIMIT #,# is no longer documented. That change will be in 7.2. Of course, that means that if someone tries MySQL's syntax, they have no documentation stating that the params are backwards. If they read the HISTORY file, they will know not to use LIMIT #,# anyway. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the select limit
Thomas Lockhart [EMAIL PROTECTED] writes: I'm with Peter on this one. I'd like to *not* clutter up the code and error reporting with hints and suggestions which may or may not be to the point. We *should* have docs which list error messages and possible solutions, and throwing that info into code is a poor second choice imho. While you have a point in the abstract, a big difficulty is that the docs never track the code with any accuracy. Look at the Outputs portions of our existing reference pages. To the extent that they describe possible errors at all, the information is a sad joke: out of date in most cases, certainly incomplete in every case. Just last week I was thinking that we should rip all that stuff out, rather than pretend it is or ever will be accurate. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Does postmaster -i...
A bit more information: an unadorned -i fails: I believe this is fixed now. Seems to be, on my Linux box. Thanks for tracking it down... - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the selectlimit
I need more information. What do you want reversed, revision 2.253 date: 2001/09/23 03:39:01; author: momjian; state: Exp; lines: +3 -3 Implement TODO item: * Change LIMIT val,val to offset,limit to match MySQL and the related description in HISTORY(Migration to 7.2). and are there enough votes to reverse those votes already made? I don't think that enough votes are needed to reverse the change. You broke the discussion fisrt rule. It was on the TODO list, and I did exactly what was listed there. What we have now is a discussion that the TODO item was wrong. I also have very few votes to just put it back to how it was in 7.1. We have votes for throwing a NOTICE that this syntax is going away, and votes to remove it completely in 7.2. We also have few votes to merely reverse the meaning of the numbers. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] createlang difficulty.
I just tried it with current sources and got: #$ aspg createlang plpgsql test Password: - bad password FATAL 1: Password authentication failed for user postgres psql: FATAL 1: Password authentication failed for user postgres createlang: external error #$ aspg createlang plpgsql test Password: - correct password Password: - correct password Password: - correct password Password: - correct password Looks OK to me. Ok, so it connects four times. From a users perspective that might be confusing. Is it possible to only prompt once for the password, just an idea I guess whoever created the program would want that to happen from a easy to use point of view. I.e. dummy proof? Uh, yes, connecting once would be ideal. It currently runs each SQL query it needs in psql and checks the exit status. Not sure how to code that in one psql session. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the
Bruce Momjian wrote: Bruce Momjian wrote: [snip] What do others think? Please reverse your change and go into beta quickly. I need more information. What do you want reversed, revision 2.253 date: 2001/09/23 03:39:01; author: momjian; state: Exp; lines: +3 -3 Implement TODO item: * Change LIMIT val,val to offset,limit to match MySQL and the related description in HISTORY(Migration to 7.2). and are there enough votes to reverse those votes already made? I don't think that enough votes are needed to reverse the change. You broke the discussion fisrt rule. regards, Hiroshi Inoue ---(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] CREATE TABLE AS / WITHOUT OIDs?
Peter Eisentraut [EMAIL PROTECTED] writes: Shouldn't there be some form of CREATE TABLE AS / WITHOUT OIDS? I thought about that, but decided it wasn't worth cluttering the parsetree representation with yet another CreateAs/SelectInto hack. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the
... Are you subscribed to general? ... Everyone thought LIMIT # OFFSET # was preferred. I think Hiroshi's point is the same as mine: discussions of feature changes need to happen on -hackers before being implemented. Subscriptions to other mailing lists should not be required to stay up with mainstream development issues. I'm recently subscribed to -general, to allow me to respond to email threads, but it has a lot of traffic and I may not stay subscribed for long... - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the selectlimit
I don't think that enough votes are needed to reverse the change. You broke the discussion first rule. Are you subscribed to general? We had a big discussion there and there was almost universal agreement that the LIMIT #,# syntax is too error-prone, and the only reason to have it was for MySQL compatibility. Of course, our syntax is backward, so is it not a compatibility but an incompatibility. Everyone thought LIMIT # OFFSET # was preferred. I don't mind reversing out all of this but I can't make everyone happy. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the selectlimit
OK, then why did Tom tell me to have the discusion on general? Don't we ask the general users about user-visible feature removal? The is not an implementation issue but a simple, What do users want? I agree it would be good on hacker too, but how do we have a discussion on both? ... Are you subscribed to general? ... Everyone thought LIMIT # OFFSET # was preferred. I think Hiroshi's point is the same as mine: discussions of feature changes need to happen on -hackers before being implemented. Subscriptions to other mailing lists should not be required to stay up with mainstream development issues. I'm recently subscribed to -general, to allow me to respond to email threads, but it has a lot of traffic and I may not stay subscribed for long... - Thomas -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Postgres 7.1.3. installation on Windows platforms
Dear all, I am trying to install PostgreSQL 7.1.3 on Win98 with APACHE and PHP (both installed and running), and am getting errors with make and make install (see below). What are the differences in installations for Win98, WinNT and Win2000? There are so many procedures around and none is working without problems. I installed Cygwin to emulate UNIX environment and Cygwin IPC to support the linker (ld.exe). I dowloaded postgresql-7.1.3.tar.gz. ./configure finished properly with un.h and tcp.h installed BUT without endian.h (is this important ???) I also copied libpostgres.a into /usr/local/lib. There are some Windows Makefiles (../src/win32.mak and ../src/makefiles/Makefile.win) - Do I need to run some and how and when. I can run postmaster -i after IMPROPER installation BUT psql does not work. Also, PHP commands of type pg_* are not recognised. I turned ON (I believe) PHP-Postgres in php.ini file residing in Windows dir by allowing extension=php_pgsql.dll. What is WRONG? Many thanks, Steven. make and make install ERRORS: gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations command.o common.o help.o input.o stringutils.o mainloop.o copy.o startup.o prompt.o variables.o large_obj.o print.o describe.o tab-complete.o -L../../../src/interfaces/libpq -lpq -L/usr/local/lib -g -lz -lcrypt -lreadline -lcygipc -o psql tab-complete.o(.text+0x2a36):tab-complete.c: undefined reference to `filename_completion_function' collect2: ld returned 1 exit status make[3]: *** [psql] Error 1 make[3]: Leaving directory `/usr/src/postgresql-7.1.3/src/bin/psql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/postgresql-7.1.3/src/bin' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/src/postgresql-7.1.3/src' make: *** [all] Error 2 -- *** Steven Vajdic (BSc/Hon, MSc) Senior Software Engineer Motorola Australia Software Centre (MASC) 2 Second Avenue, Technology Park Adelaide, South Australia 5095 email: [EMAIL PROTECTED] email: [EMAIL PROTECTED] Ph.: +61-8-8168-3543 Fax:+61-8-8168-3501 Front Office (Ph): +61-8-8168-3500 mobile: +61 (0)419 860 903 AFTER WORK email: [EMAIL PROTECTED] Home address: 6 Allawah Av., Glen Osmond SA 5064, Australia *** ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]