Re: [HACKERS] Bug in renaming view columns
Take it a little further: if we extend ALTER TABLE to be able to alter view column types, would you expect CREATE OR REPLACE VIEW to stop checking that the column types didn't change? I'd argue that that's a real bad idea. If you want the view's output signature to change, you should have to use a command that indicates that's your intent. Sounds reasonable. I was just wondering if renaming columns with ALTER TABLE was intentional... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Bug in renaming view columns
Hi, Are we supposed to be able to rename view columns or not? You can't if you replace the view, but you can if you rename the column. test=# create view test as select 1 as a; CREATE VIEW test=# \d test View public.test Column | Type | Modifiers +-+--- a | integer | View definition: SELECT 1 AS a; test=# create or replace view test as select 1 as b; ERROR: cannot change name of view column a test=# alter table test rename a to b; ALTER TABLE test=# \d test View public.test Column | Type | Modifiers +-+--- b | integer | View definition: SELECT 1 AS b; Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] CLUSTER locking
Hi, Is there a way the CLUSTER command can be changed to not take an exclusive lock on the table, and instead allow reads on the old table and index, just preventing writes? Chris ---(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] CLUSTER locking
No. Committing the relfilenode swap would cut the knees off of any active scan on the old file. Could it upgrade its lock to exclusive just before doing the swap? 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] Adding MERGE to the TODO list (resend with subject)
Except you can keep trying and trying without the outermost transaction failing. But that won't provide the necessary next key locking you mentioned in your first email, will it? No, but since I can loop an infinite number of times until either the update or insert works, I don't need next key locking. BTW, the reference in MySQL: http://dev.mysql.com/doc/mysql/en/REPLACE.html Hmm...no refernce to next key locking. Maybe that's an Innodb thing... Anyway, you can see how they've implemented their algorithm. Here is docs on the DB2 merge command from which the standard was derived: http://databasejournal.com/features/db2/article.php/10896_3322041_2 Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Constraint not shown on \d ?
CREATE UNIQUE INDEX activation_code_code_key ON public.activation_code USING btree (code, id_code_pool); or ALTER TABLE activation_code ADD UNIQUE ( code, id_code_pool ); with \d command there is no difference but is different because the first command create an index deleteable with a drop index. Yes, you're right. Basically the only difference is that the latter will make it a constraint that can only be dropped with DROP CONSTRAINT. Also, if you wanted to create a unique non-btree index, partial index or expressional index, you'd have to use the former syntax Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Comments on all system objects
Hi, Is there any reason I shouldn't submit a patch that makes it so that we have comments on 100% of the catalog objects? I don't see any reason why we shouldn't do it... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Adding MERGE to the TODO list (resend with subject)
What does the MERGE command do? I have never heard of it, so I doubt someone is working on it. It is basically the SQL standard version of MySQL's REPLACE syntax. It does an update-else-insert set. However, the trick is that it uses some sort of next key locking to ensure that it cannot fail. Something that is impossible to do in PostgreSQL at the moment. Nested transactions will help, however. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Comments on all system objects
The ability to comment on all types of catalog objects or actual comments on all predefined catalog objects? Both are more or less reasonable. But I think we should have some sort of internationalization mechanism for the actual comments. One of my first commits for 7.5 was the format, I added being able to comment on about 5 new objects. I intended the latter. I guess I could clean up the functions and operators like Tom indicated, but that's all I'll do I guess. Internationalisation...hmmm...I don't know how to do that. Can you put gettext around the builtins stuff? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Adding MERGE to the TODO list (resend with subject)
I intend to release locks on subtransaction abort, so if the update fails there's room for another transaction to insert the key (which I understand should fail?). I guess there's a different locking mechanism needed; I believe nested transactions will not be enough. Except you can keep trying and trying without the outermost transaction failing. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Subtle pg_dump problem...
I have a table with a tsearch2 index on it. Now, I have all the tsearch2 stuff installed into a 'contrib' schema. I have had to change the default database schema to include the contrib schema as behind-the-scenes, tsearch2 looks for its tables, and cannot find them even if the function itself is schema-qualfified. This might well be a tsearc2 bug. Anyway, this means the table is dumped like this: SET SESSION AUTHORIZATION 'auadmin'; SET search_path = public, pg_catalog; COPY ... Which give this error upon restoring: ERROR: relation pg_ts_cfg does not exist CONTEXT: COPY food_categories, line 1: 79 102 Vegetables, Salads Legumes\N 'legum':3 'salad':2 'veget':1 It's because the search_path needs to be like this for it to work: SET search_path = public, contrib, pg_catalog; Chris ---(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] ALTER TABLE TODO items
FireBird: ALTER COLUMN column TYPE type DB2:ALTER COLUMN column SET DATA TYPE type. Oracle: MODIFY column type MSSQL: ALTER COLUMN column type constraints MySQL: Both Oracle and MSSQL Sap:MODIFY column type Given that, I'm happy with what we got ... Yeah same, I was just wondering whether it should be like SET NOT NULL, that's all. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ALTER TABLE TODO items
I tweaked things so that the clustered flag is preserved for indexes that aren't directly affected by the ALTER TYPE. It would take more work to preserve the setting for an index that is rebuilt by ALTER TYPE, and I'm not even sure that it's sensible --- the new index could have a significantly different ordering from the old. What do you think? Out of interest what happens to other column features such as the existing statistics level and the existing storage spec? I guess these might have to change when type changes?? Chris ---(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] alter table alter columns vs. domains
Is it feasible or practical to consider adding ALTER DOMAIN TYPE type? (basically following the same rules as ALTER TABLE). I was _just_ about to ask that! Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pgFoundry Open For Business
Over the next few months, we will be enabling the following features (all of which currently have some bugs) -- Code Snippets: A library to share small scripts and functions, like Roberto's old PL/pgSQL Library, but supporting multiple languages; -- lightweight personal blogs for developers -- PostgreSQL databases for each project Going to have phpPgAdmin running for that? :) Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] COPY command - CSV files
Hi Umberto, If you are interested in doing any development work on PostgreSQL, you _really_ need to work from the CVS version :) Chris Umberto Zappi wrote: Thanks to everybody has reply to my email. Stop immediatly my work in progress. Some days ago I've downloaded version 7.4.3 of postgresql and I've begin to work over without know other jobs of other developers :-o Bye Umberto Umberto Zappi wrote: I wish modify COPY command for support of CSV files (dump/load files in CSV format). I think this option is very important from import data from spreedsheet as OpenOffice/calc or M$/excel. I have found this task in TODO list, also. [...] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] [ADMIN] cast not IMMUTABLE?
(login_time at time zone 'GMT')::date but upon experimenting I see that that isn't considered immutable either :-(. Offhand I think this may be an oversight --- I can't see any reason for the various flavors of AT TIME ZONE (a/k/a timezone() function) not to be considered immutable. (Hackers, any comments?) Seems reasonable...seems like the kind of thing that wouldn't have been tested/considered... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ALTER TABLE TODO items
I left the statistic setting as-is (do you think that's wrong?) but the storage spec gets reset to whatever the default for the new type is. Seems reasonable. We could talk about doing something more complicated, such as keep the old setting if both old and new types support toasting, else reset to new default. Not sure if that'd be better or not. Yeah, I was thinking along those lines. I don't now though... What happens with ordering of operations in the ALTER TABLE statement? Like if I put an alter TYPE and a SET STORAGE in the same statement (wiht commas between), in what order will things happen? Is it deterministic? Is it documented? Are there situations where a crazy collection of 20 commands in a single ALTER TABLE will have unpredictable effects? Also, should the syntax be SET TYPE, not just TYPE? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] psql 7.3.4 disagrees with NATURAL CROSS JOIN
I have discovered a problem with psql 7.3.4 where it does not seem to like statements containing NATURAL CROSS JOIN. I have a test that I have created that will show the problem. Please have a look at it, give it a try, and let me know if there is a problem with the program or with the operator. ;) Just a note for the hackers, Jonathan (I think :) ) talked to me about this on the irc channel - we couldn't figure this one out. Seems that pg_dump produces NATURAL CROSS JOIN in the dump of a view, but the pgsql grammar does not appear to allow it. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] COPY command - CSV files
I believe this has already been implemented in CVS... Chris Umberto Zappi wrote: I wish modify COPY command for support of CSV files (dump/load files in CSV format). I think this option is very important from import data from spreedsheet as OpenOffice/calc or M$/excel. I have found this task in TODO list, also. ---(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] ALTER TABLE TODO items
Yeah, the USING is actually any arbitrary expression over the old table row. (Getting that to work was a tad tricky...) So you can view this as a full-table UPDATE operation that folds in possible column type changes. All I can say is three cheers for Tom and Rod on this one Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Multiple Xids in PGPROC?
I remember going through this. Other backends will use pg_subtrans to know what transactions are in progress. They have to do the standard lookups to find the status of the parent transaction. The backend-local list of xids is needed so the commit can clean up those subtransaction xids so that later transactions don't have to use pg_subtrans. Is there some solution whereby the common case (99.999% of transactions won't be subtransactoins) is fast, and the uncommon case of being in a subtransaction is slower? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Multiple Xids in PGPROC?
I hope not, because for many of us there will be as many (if not more) subtransactions than standard transactions. How can that possibly be true? Every statement executed in postgres is a transaction how many subtransactions are really needed and how can they be as common as normal transactions? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Xids in PGPROC?
Yup.. And some of us intend on wrapping every single statement in a subtransaction so we can rollback on an error without aborting the main transaction. Point there being main transaction. What i'm saying is that the vast majority of your transactions will be single statements. eg. single selects, single updates, etc. In fact, I would be surprised if tools like psql went very long without doing the same thing so users can recover from spelling mistakes. If the user does an explicit BEGIN, then perhaps we might, but how often does the user do an explicit BEGIN? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] OT: Open source developer survey
Hi guys, I know this is off topic, but if there are any developers with sourceforge accounts here, they might be interested in filling out this query which came throught the phpPgAdmin lists. It seems legit :) Chris Original Message Subject: [ppa-dev] FASD project: Online survey launched Date: Mon, 3 May 2004 09:13:50 +0200 From: Benno Luthiger [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Dear Open Source developer I am doing a research project on Fun and Software Development in which I kindly invite you to participate. You will find the online survey under http://fasd.ethz.ch/qsf/. The questionnaire consists of 53 questions and you will need about 15 minutes to complete it. With the FASD project (Fun and Software Development) we want to define the motivational significance of fun when software developers decide to engage in Open Source projects. What is special about our research project is that a similar survey is planned with software developers in commercial firms. This procedure allows the immediate comparison between the involved individuals and the conditions of production of these two development models. Thus we hope to obtain substantial new insights to the phenomenon of Open Source Development. With many thanks for your participation, Benno Luthiger PS: The results of the survey will be published under http://www.isu.unizh.ch/fuehrung/blprojects/FASD/. We have set up the mailing list [EMAIL PROTECTED] for this study. Please see http://fasd.ethz.ch/qsf/mailinglist_de.html for registration to this mailing list. ___ Benno Luthiger Swiss Federal Institute of Technology Zurich 8092 Zurich Mail: benno.luthiger(at)id.ethz.ch ___ --- This SF.Net email is sponsored by: Oracle 10g Get certified on the hottest thing ever to hit the market... Oracle 10g. Take an Oracle 10g class now, and we'll give you the exam FREE. http://ads.osdn.com/?ad_id=3149alloc_id=8166op=click ___ phpPgAdmin-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/phppgadmin-devel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] I need Help
I installed postgresql 7.4 in my computer, I'm using redhat 9.0 . I installed pgadmin III but I can't to conecct to the server. The port 5432 is not open. You need to set tcpip_socket = true in your postgresql.conf. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nasty security bug with clustering
I'm in the middle of reviewing (read whacking around) Rod Taylor's patch for multiple operations in ALTER TABLE, so I'm afraid that no patch in the same area is likely to apply cleanly after the dust settles :-( OK, Bruce - just ignore the patch I sent in. I'll refactor it after Tom commits. 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SET WITHOUT CLUSTER patch
Actually, it occurs to me that the SET WITHOUT CLUSTER form CAN recurse. Should I make it do that, even though the CLUSTER ON form cannot? I just thought about this. CLUSTER is more of a storage-level specification, rather than a logical one. Seems it is OK that WITOUTH CLUSTER not recurse into inherited tables, especially since the CLUSTER command does not. The patch I submitted earlier already does do recursion - I don't see why it shouldn't really. It's better than failing saying that legal grammar is in fact illegal :) Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SET WITHOUT CLUSTER patch
Uh, if the CLUSTER doesn't recurse, the WITHOUT shouldn't either, I think, and throwing an error seems fine to me, even if it isn't the same wording as a syntax error. Well, maybe - up to you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Plan for feature freeze?
Tatsuo brought up the an excellent point (that I have been saying for a long time), that the number of must-fix bugs from previous releases is shrinking, and the complexity of new features is increasing. This dictates the that length of our release process should lengthen over time. May I also make the point that I have only _just_ upgraded all our production database servers to 7.4? Unless there are really compelling new features in 7.5, I as yet see no reason to upgrade to 7.5 at any point. As Postgres gets larger and postgres databases get larger, and we properly maintain the previous versions, then the need to upgrade is gone. It doesn't matter to me if it's a 1 year or 2 year development cycle at the moment. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Clustering system catalog indexes
Is it worth us marking any system catalog indexes as clusterable by default for performance? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bitwise and/or aggregate functions?
SELECT BIT_OR(aclitem_privs(...)) AS effective_privs FROM ... WHERE aclitem_grantee(...)=... AND ... ; Is there anything in SQL2003 about such operators? If there is, we should make sure we use the correct aggregate names. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Weirdness with OIDs and JOIN ON?
Why doesn't this work: test=# select oid, relname, indisclustered from pg_index join pg_class on indexrelid=oid where indexrelid 17205; ERROR: column oid does not exist I'm _joining_ on the oid column. If I qualify it, it works: test=# select pg_class.oid, relname, indisclustered from pg_index join pg_class on indexrelid=oid where indexrelid 17205; oid | relname| indisclustered ---+--+ 17214 | child_b_key | t 17210 | parent_a_key | t (2 rows) I can't see that I've made an error in the first example - is it a Postgres bug? 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] Nasty security bug with clustering
No check is performed for being a superuser, the table owner or that it is a system table when marking an index for clustering: I'm about to submit my SET WITHOUT CLUSTER patch, so I'll fix this bug in that. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] bitwise and/or aggregate functions?
Is there anything in SQL2003 about such operators? If there is, we should make sure we use the correct aggregate names. That's a point! I thought of BIT_* because it is short and also used by mysql. Ingres has BIT_AND and BIT_OR functions, but they are not aggregates. I don't know where these standards are available online... It seems they are not available:-( Neil - can you check your SQL2003 copy to see if it mentions standard aggregates on bit types? Thanks, Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nasty security bug with clustering
I'm in the middle of reviewing (read whacking around) Rod Taylor's patch for multiple operations in ALTER TABLE, so I'm afraid that no patch in the same area is likely to apply cleanly after the dust settles :-( OK, Bruce - just ignore the patch I sent in. I'll refactor it after Tom commits. 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] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
If anything, I'd rather see the JDBC and ODBC drivers reinstated in the release. More than 56% of the PostgreSQL users (according to the poll) uses JDBC today. ODBC is merely 18% but that might change significantly when the native Win32 port is released. I might have missed something altogether here, my apologies if that's the case, but looking at the coming 7.5 release, 75% of the users will be forced to download stuff from more than one location just to get their basic stuff running. Note that MySQL doesn't bundle them, but they are CLEARLY available for download on their site on all places where MySQL itself is available for download. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
Seriously - I'd like to raise my voice in favor of installing plpgsql in template1 by default. I haven't heard any good reason not to (nor even a bad reason). It has to work with older dumps that will try to recreate pl/pgsql themselves explicitly. I offered the same opinion a while back, and was told that plpgsql is a security hole. The we can make it not have USAGE privileges to public by default?? 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
[HACKERS] Nasty security bug with clustering
No check is performed for being a superuser, the table owner or that it is a system table when marking an index for clustering: usa= alter table pg_class cluster on pg_class_oid_index; ALTER TABLE usa= select oid from pg_class where relname='pg_class_oid_index'; oid --- 16613 (1 row) usa= select * from pg_index where indexrelid=16613; indexrelid | indrelid | indkey | indclass | indnatts | indisunique | indisprimary | indisclustered | indexprs | indpred +--++--+--+-+--++--+- 16613 | 1259 | -2 | 1989 |1 | t | f | t | | (1 row) Note how I managed to mark as clustered an index on a system catalog as a non-superuser... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-advocacy] What can we learn from MySQL?
My question is, What can we learn from MySQL? I don't know there is anything, but I think it makes sense to ask the question. Questions I have are: I have already told Bruce at length about the single most common complaint in the phpPgAdmin lists and in the IRC channel: the inability to change column types. I think we should listen to the punters on that one. Also, how about a new section in the manual: PostgreSQL for MySQL users and PostgreSQL for Oracle users? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_autovacuum crashes when query fails for temp tables
I looked into this and I see a number of cases where pg_autovacuum calls send_query(), but doesn't test for a NULL return from the function. Matthew, would you look into this and submit a patch? Thanks. Does pg_autovacuum vacuum and analyze system catalog and TOAST tables properly? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_autovacuum crashes when query fails for temp tables
No, I have not heard of a 7.4.3 timeline, but we certainly want your eventual fixes in that release. Right, and along these lines there are a few other pg_autovacuum bugs that were fixed just after 7.4.2. A rollable log solution would be nice :) Syslog? :) Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_autovacuum crashes when query fails for temp
Ok, so I will change pg_autovacuum to explicitly ignore temp tables. Just to be sure, I can do this by avoiding anything found in the pg_temp schemea, or is there a better way? Is it possible that a user could or would put a non-temp table the pg_temp schemea? There's no such thing as the pg_temp schema, you will get lots of pg_temp_xxx schemas I think. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ON COMMIT DROP
Where exactly would you propose to stick it in the syntax? Good question, I don't know. Can you do it without introducing more fully-reserved words than we have already? No idea. Is there any spec or other-product precedent for it? (Offhand I can't even find CREATE TABLE AS in SQL99...) Weeell. I was just minorly annoyed at having to create table with on commit behaviour, then insert into select from instead of being able to do it in one step... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 'begin transaction' new syntax bug?
char *argstring = flatten_set_variable_args(name, args); + printf(bjm: %s %s\n, name, argstring); + fflush(stdout); Did you really mean to include that? :) Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] ON COMMIT DROP
Is there any reason why the 'ON COMMIT' behaviour feature is not available if you use CREATE TABLE AS ...? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Remove MySQL Tools from Source?
But you would have to assign the copyright to them If someone is going to make money from my code, I prefer it to be me, or at least that everyone has a chance to do so rather than just one company. Well, then for the same reason we should write a Perl script that connects to MySQl and dumps in PGSql format. I think it's silly to try and read a MySQL dump and convert it - let's just dump straight from the source. Josh - I'm kind of keen to make this happen... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Remove MySQL Tools from Source?
... on projects.postgresql.org, or similar.They really aren't doing any good in /contrib. I've already set up a category conversion tools on pgFoundry, and my idea was one project per target system. I reckon that by far the best way to do a mysql2pgsql converter is to just modify mysqldump C source code to output in postgresql format! Chris ---(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] Function to kill backend
This is a commonly requested feature by DBA's migrating from SQL Server and Oracle.In those databases, there is a GUI to monitor database requests, and potentially kill them to resolve deadlocks or runaway queries (though, in the case of SQL server, it does not work). Right now, it is very difficult for any of our GUI projects to construct such an interface due to the necessity of root shell access. Yes, MySQL can do it too. http://www.mysql.com/doc/en/KILL.html I would love to have a KILL pid command in postgres. I don't know how you would restrict it to only being able to kill postgres backends though. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Update on PITR
Is your timeline based on the assumption of doing all the work yourself? If so, how about farming out some of it? I'd be willing to contribute some effort to PITR. (It's been made clear to me that Red Hat really wants PITR in 7.5 ;-)) What is RedHat's interest in PostgreSQL? Last time I heard they weren't interested in their database product anymore. Why do they care about the PostgreSQL project? Of course, it's awesome that they are - but why? What's their plan? 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] pg_dump end comment
I like an end-of-dump marker for folks who want to check if the dump got truncated somehow. I can see how to do that for text dumps, but what about for tar or custom dumps? Wouldn't it be more effective to test for non zero return status as this handles -Fc cases, etc, which would be non-trivial to test. That assumes you are there at the time of dump... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] pg_dump 7.4 bug
If you do this sequence of events, you get a failure to restore: 1. As superuser, do this: test2=# CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler test2-# AS '$libdir/plpgsql.so', 'plpgsql_call_handler' test2-# LANGUAGE c; CREATE FUNCTION 2. Drop privs. test2=# alter user chriskl with nocreateuser; So, now we're a regular joe user. 3. pg_dump now gives this: SET SESSION AUTHORIZATION 'chriskl'; SET search_path = public, pg_catalog; -- -- TOC entry 37 (OID 853309) -- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE; Schema: public; Owner: chriskl -- CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql.so', 'plpgsql_call_handler' LANGUAGE c; 4. Now, trying to restore this as the joe user gives: test2= CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler test2- AS '$libdir/plpgsql.so', 'plpgsql_call_handler' test2- LANGUAGE c; ERROR: permission denied for language c This caused me pain in the 7.4 upgrade I just performed... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PostgreSQL and FreeBSD SoftUpdates
Hi guys, Does anyone else have this problem? We have softupdates turned on on our data dir. (Soon to be turned off due to these issues). The partition is 12GB. 'df' says that we're using 12 and a bit GB but 'du' says we're using 2GB (which we really are). It seems that perhaps softupdates is caching some stuff, or preventing something from being written properly, etc. The funny thing is that this was never a problem until we upgraded to 7.4. Has something changed in the way file writes or syncs are done? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_dump 7.4 bug
If you do this sequence of events, you get a failure to restore: This is not a pg_dump bug. Possibly ALTER USER should refuse to drop someone's superuserness if there is content in the database that depends on his superuserness, but I don't see how to enforce that. How about we allow changing owner of lanugages so I can fix this problem? Is it safe for me to just update the catalogs? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL and FreeBSD SoftUpdates
what version of FreeBSD are you using? I'm running 4.9-STABLE with softupdates on my db file system ... FreeBSD goddard.calorieking.com 4.9-STABLE FreeBSD 4.9-STABLE #2: Mon Jan 26 23:23:17 EST 2004 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/GODDARD i386 We're not 100% sure it's softupdates, but we can't see anything else that it could be. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PostgreSQL and FreeBSD SoftUpdates
Right off the top of my head, it almost sounds like a file is being held open after its been deleted ... we went through that with the new aspseek a little while back, where 170gig just disappeared overnight, but du showed hardly any disk space being used ... Does restarting the database server (not rebooting, just restarting the postmaster) free up the disk space? No - have to reboot. That's probably because of softupdates though. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] pg_dump end comment
This might seem a bit silly, but is there any chance we could add a comment at the end of pg_dump text output that says '-- End of dump'? Would make it useful for checking that you actually have a complete dump... Chris ---(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] PostgreSQL and FreeBSD SoftUpdates
'k, *shouldn't* require a reboot ... but, what I'd try is to do what you've thought .. disable softupdates and see if you can recreate ... if killing off the process auto-reclaims the space fast, then it sounds like a stale file being held open (log file being rotated improperly?) ... Log file's on a different partition... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Increasing security in a shared environment ...
The \l command should only list databases that the current user is authorized for, the \du command should only list users authorized for the current database (and perhaps only superusers should get even that much information), etc. Perhaps it is possible to set PG to do this, but that should probably be the default. This is from a PgSQL vs MySQL thread on -general ... how hard would it be make it so that a non-superuse user can't do a \l and see everyone's databases? Or, when doing a \d in a database you are able to connect to, it would only show those tables that you are authorized for? Well, you can just go SELECT * FROM pg_database; so fixing \l won't do anything. I too would like to see more security in this respect, but it will be difficult if not impossible to implement methinks... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Email addresses on developer bios site
Is there any chance we could get our email addresses obfuscated to prevent spam? Just an FYI, but just by posting, you do realize that your email address is propogated to every Usenet server in the world, as well as several search engines like Google and Gname, right? I'm well aware of that, since you, bruce and tom currently attempt to sell me Viagra several times a day... :) Email harvesting developer.postgresql.org will give them one occurance of your email address out of, most likely , several million out there that it could find :( Ok... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested transaction proposal - take N (N 2)
a c 0 0 transaction in progress, the owning backend knows whether it is a main- or a sub-transaction, other backends don't care 1 0 aborted, nobody cares whether main- or sub-transaction 0 1 committed main-transaction or - with shortcut 2 - a sub- transaction that's known committed to all active transactions 1 1 committed sub-transaction, have to look for parent in pg_subtrans This conflicts with my two-phase commit patch. I'm using the fourth state to mark transactions that have been prepared (1st. phase) but not yet committed. I think I can work around it in my code, so that you can have the fourth state. I have to keep a list of prepared transactions in memory anyway, I can use that instead. He who commits first, wins :P Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_advisor schema proof of concept
(6) possible inclusion in postgresql? - among other contributions? what about contrib/advisor? - added to template1 on default installation? maybe not for a first release? or yes? it is easier to communicate about I think we're going to want a gborg project for developing/coordinating tests anyway. Having the schema included in contrib/ might help adoption, but so would pgadmin/phpgadmin. Any client-builders reading this? What do you think? Both phpPgAdmin (me) and the pgAdmin team have added or have thought about adding some 'schema analysis' features to our products. If pg_advisor is available, I certainly won't bother and I will just recommend to people that they install it. I think it probably should live in userland... 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] pg_advisor schema proof of concept
I was thinking along the kind of missing index Tom was arguing about for RI checks, that may be helped if an appropriate index is available. I'm not sure what could be done, even with the query, in the general case. How to guess what index would help make a better plan? It depends on the optimiser itself, on what kind of indexes could be built, and so on. That's more human expect work than tool work. Also, if they have a partial index on the FK, it's not good enough! In CVS, IS NOT NULL partial indexes should be used, but in general all others still won't... Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: subversion vs cvs (Was: Re: [HACKERS] linked list rewrite)
It does have some downsides that I have found, most notibly that the size of your sources you have in your working copy are essentially doubled. There is a copy in your .svn directory that allows the offline status, diff, and revert commands to work. What's needed is a good window client like WinCVS, however... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Email addresses on developer bios site
On this page: http://developer.postgresql.org/bios.php Is there any chance we could get our email addresses obfuscated to prevent spam? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Chapter on PostreSQL in a book
I'm at a loss as to how much we should focus on these sections. Do we use what's in GBorg ? Do the hackers have any suggestions ? There are 33 DBA tools and 19 Design tools in GBorg .. are there any specific tools that are recommended ? Well, I think there are really just two major active free database administration tools: pgAdmin3 and phpPgAdmin Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] dollar quoting and pg_dump
I don't mind if it's on by default; just thinking that some people might appreciate a way to turn it off. -X disable-dollar-quoting sounds fine. Does it _have_ to be dollars? Other languages call this feature 'heretext' IIRC. 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] bug in 7.4 SET WITHOUT OIDs
Will it handle this case: usa=# create table testy (a int4) without oids; usa=# alter table testy add oid int4; No. This is DROP not ADD. What I meant is - does it handle dropping a non-system 'oid' column? ie. A user column that just happens to be named 'oid'. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] bug in 7.4 SET WITHOUT OIDs
If you have one (implying that you don't have a system OID column) then DROP COLUMN oid will drop it, but SET WITHOUT OIDS will not. Okay with you? Sounds fair. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] float8 regression test failure in head
This has not yet been fixed... Chris Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Attached are the test failures I'm currently getting. It looks like Neil didn't update expected/float8-small-is-zero.out for his recent changes (for which, shame on him). Would you get together to verify the correct regression outputs for your platform and commit the updated expected file? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] bug in 7.4 SET WITHOUT OIDs
I had a suspicion and it was confirmed: test=# create table oidtest (a int4, unique(oid)); NOTICE: CREATE TABLE / UNIQUE will create implicit index oidtest_oid_key for table oidtest CREATE TABLE test=# select oid from oidtest; oid - (0 rows) test=# alter table oidtest set without oids; ALTER TABLE test=# select oid from oidtest; ERROR: column oid does not exist test=# \d oidtest ERROR: cache lookup failed for attribute -2 of relation 765798 Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Weird behaviour with subquery
What's going on here: usa= select user_id from users_users where joindate = '2004-03-09'; ERROR: column user_id does not exist usa= select * from shop_orders where user_id in (select user_id from users_users where joindate = '2004-03-09'); [waits and waits and waits...have to cancel] ^CCancel request sent ERROR: canceling query due to user request How come using a field that doesn't exist in the subquery actually works and doesn't cause a syntax error? Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Weird behaviour with subquery
Doh - I think I understand now why this is normal behavior - sorry! Chris Christopher Kings-Lynne wrote: What's going on here: usa= select user_id from users_users where joindate = '2004-03-09'; ERROR: column user_id does not exist usa= select * from shop_orders where user_id in (select user_id from users_users where joindate = '2004-03-09'); [waits and waits and waits...have to cancel] ^CCancel request sent ERROR: canceling query due to user request How come using a field that doesn't exist in the subquery actually works and doesn't cause a syntax error? Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] bug in 7.4 SET WITHOUT OIDs
The problem appears to be that ALTER TABLE SET WITHOUT OIDS doesn't make the index on the OID column go away. I don't have a strong opinion on whether to fix this by forcing a drop of the index or by rejecting the ALTER command. Seems like we have to do one or the other though. This is actually just the simplest case of a dependency on the OID column... whatever the fix is, it has to handle the general case. Maybe it needs CASCADE/RESTRICT added? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] bug in 7.4 SET WITHOUT OIDs
Maybe it needs CASCADE/RESTRICT added? Seems like overkill, considering that this is a very marginal feature. I'm happy to decree that it works in whichever way is the easiest to implement. In that case, it seems to me that it has to be default RESTRICT. If anything depend on it, it must fail. Otherwise when you do it, it could drop views, functions, everything. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_autovacuum next steps
I think these configuration issues will become a lot easier if you make the autovacuum daemon a subprocess of the postmaster (like, say, the checkpoint process). Then you have access to a host of methods for storing state, handling configuration, etc. Yeah - why delay making it a backend process? :) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Further thoughts about warning for costly FK checks
though I'd be worried about the portability price paid to have one. Or are you concerned about whether a GUI could invoke it? I don't see why not --- the GUIs don't reimplement pg_dump, do they? Actually Tom, I think they do (where they have an export facility). How would you run pg_dump on a remote machine? (well, without building an RPC mechanism) In phpPgAdmin 2.x, such a re-implementation did exist. When we did the 3.2 rewrite, I wrote another one just for dumping tables. Then I had the much better idea of just allowing the person to specify the location of pg_dump on their server and now we stream raw pg_dump output back to the client browser. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Will auto-cluster be in 7.5?
# CLUSTER * Automatically maintain clustering on a table * Add way to remove cluster specification on a table I've done the latter - it's been sent to -patches. However, I need someone to look at the shift/reduce problem I'm getting... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Will auto-cluster be in 7.5?
This patch is done and will be applied soon. I'm a bit confused, why would you want to uncluster a table? You would want to remove the marker that says 'cluster this column in the future'. At the moment, there is no way of removing all markers from a table. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] SET WITHOUT CLUSTER patch
Hi,
I have done a patch for turning off clustering on a table entirely.
Unforunately, of the three syntaxes I can think of, all cause
shift/reduce errors:
SET WITHOUT CLUSTER;
DROP CLUSTER
CLUSTER ON NONE;
This is the new grammar that I added:
/* ALTER TABLE name SET WITHOUT CLUSTER */
| ALTER TABLE relation_expr SET WITHOUT CLUSTER
{
AlterTableStmt *n = makeNode(AlterTableStmt);
n-subtype = 'L';
n-relation = $3;
n-name = NULL;
$$ = (Node *)n;
}
Now, I have to change that relation_expr to qualified_name. However,
this causes shift/reduce errors. (Due to ALTER TABLE relation_expr SET
WITHOUT OIDS.)
Even changing the syntax to qualified_name DROP CLUSTER doesn't work
due to the existence of relation_expr DROP
What's the solution? I can't figure it out...
Chris
Index: doc/src/sgml/ref/alter_table.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.66
diff -c -r1.66 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml 9 Mar 2004 16:57:47 - 1.66
--- doc/src/sgml/ref/alter_table.sgml 18 Mar 2004 03:51:41 -
***
*** 47,52
--- 47,54
OWNER TO replaceable class=PARAMETERnew_owner/replaceable
ALTER TABLE replaceable class=PARAMETERname/replaceable
CLUSTER ON replaceable class=PARAMETERindex_name/replaceable
+ ALTER TABLE replaceable class=PARAMETERname/replaceable
+ SET WITHOUT CLUSTER
/synopsis
/refsynopsisdiv
***
*** 219,224
--- 221,235
/listitem
/varlistentry
+varlistentry
+ termliteralSET WITHOUT CLUSTER/literal/term
+ listitem
+ para
+ This form disables future xref linkend=SQL-CLUSTER
endterm=sql-cluster-title on a table.
+ /para
+ /listitem
+/varlistentry
+
/variablelist
/para
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v
retrieving revision 1.100
diff -c -r1.100 tablecmds.c
*** src/backend/commands/tablecmds.c13 Mar 2004 22:09:13 - 1.100
--- src/backend/commands/tablecmds.c18 Mar 2004 03:51:42 -
***
*** 3970,3999
rel = heap_open(relOid, AccessExclusiveLock);
- indexOid = get_relname_relid(indexName, rel-rd_rel-relnamespace);
-
- if (!OidIsValid(indexOid))
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
-errmsg(index \%s\ for table \%s\ does not exist,
- indexName,
NameStr(rel-rd_rel-relname;
-
- indexTuple = SearchSysCache(INDEXRELID,
-
ObjectIdGetDatum(indexOid),
- 0, 0, 0);
- if (!HeapTupleIsValid(indexTuple))
- elog(ERROR, cache lookup failed for index %u, indexOid);
- indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
-
/*
!* If this is the same index the relation was previously clustered on,
!* no need to do anything.
*/
! if (indexForm-indisclustered)
! {
! ReleaseSysCache(indexTuple);
! heap_close(rel, NoLock);
! return;
}
pg_index = heap_openr(IndexRelationName, RowExclusiveLock);
--- 3970,4010
rel = heap_open(relOid, AccessExclusiveLock);
/*
!* We only fetch the index if indexName is not null. A null index
! * name indicates that we're removing all clustering on this table.
*/
! if (indexName != NULL) {
! indexOid = get_relname_relid(indexName, rel-rd_rel-relnamespace);
!
! if (!OidIsValid(indexOid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
!errmsg(index \%s\ for table \%s\ does
not exist,
! indexName,
NameStr(rel-rd_rel-relname;
!
! indexTuple = SearchSysCache(INDEXRELID,
!
ObjectIdGetDatum(indexOid),
! 0, 0, 0);
! if (!HeapTupleIsValid(indexTuple))
! elog(ERROR, cache lookup failed for index %u, indexOid);
! indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
!
! /*
!* If this is the same index the relation was previously clustered on,
!* no need to do anything.
!*/
! if
Re: [HACKERS] Doxygen?
I was thinking of writing a cron job to update the CVS tree and then build the documentation (takes about 10 minutes on my computer). Then I could push it to wherever you like. Are we currently maintaining two or three branches in the code? We may want to keep them seperate. We could also maintain released postgresql documentation -- IE, one set for PostgreSQL 7.4.0, one set for PostgreSQL 7.4.1, etc... These would only have to be built once. How about posting a 'doxygen commenting guide' to the list so that we know how to comment? Also, maybe it's time we standardised code documentation? :) Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] relation_expr vs. qualified_name
How come half the ALTER TABLE statements use relation_expr and half use qualified_name? Is one more correct now? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] float8 regression test failure in head
Attached are the test failures I'm currently getting.
-bash-2.05b$ uname -a
FreeBSD mir.internal 4.9-PRERELEASE FreeBSD 4.9-PRERELEASE #0: Mon Sep
22 14:46:18 WST 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/MIR i386
Chris
parallel group (13 tests): text name char varchar oid boolean int2 int8 int4 float4
bit float8 numeric
boolean ... ok
char ... ok
name ... ok
varchar ... ok
text ... ok
int2 ... ok
int4 ... ok
int8 ... ok
oid ... ok
float4 ... ok
float8 ... FAILED
bit ... ok
numeric ... ok
test strings ... ok
test numerology ... ok
parallel group (20 tests): comments lseg time path timetz reltime circle tinterval
box abstime point polygon interval timestamp inet type_sanity date timestamptz
oidjoins opr_sanity
point... ok
lseg ... ok
box ... ok
path ... ok
polygon ... ok
circle ... ok
date ... ok
time ... ok
timetz ... ok
timestamp... ok
timestamptz ... ok
interval ... ok
abstime ... ok
reltime ... ok
tinterval... ok
inet ... ok
comments ... ok
oidjoins ... ok
type_sanity ... ok
opr_sanity ... ok
test geometry ... ok
test horology ... ok
test insert ... ok
test create_function_1... ok
test create_type ... ok
test create_table ... ok
test create_function_2... ok
test copy ... ok
parallel group (7 tests): create_operator create_aggregate vacuum triggers
create_misc inherit constraints
constraints ... ok
triggers ... ok
create_misc ... ok
create_aggregate ... ok
create_operator ... ok
inherit ... ok
vacuum ... ok
parallel group (2 tests): create_view create_index
create_index ... ok
create_view ... ok
test sanity_check ... ok
test errors ... ok
test select ... ok
parallel group (18 tests): select_distinct_on select_into update random btree_index
namespace select_having select_distinct hash_index aggregates case transactions
select_implicit union subselect arrays portals join
select_into ... ok
select_distinct ... ok
select_distinct_on ... ok
select_implicit ... ok
select_having... ok
subselect... ok
union... ok
case ... ok
join ... ok
aggregates ... ok
transactions ... ok
random ... ok
portals ... ok
arrays ... ok
btree_index ... ok
hash_index ... ok
update ... ok
namespace... ok
test privileges ... ok
test misc ... ok
parallel group (5 tests): select_views portals_p2 cluster foreign_key rules
select_views ... ok
portals_p2 ... ok
rules... ok
foreign_key ... ok
cluster ... ok
parallel group (13 tests): limit prepare copy2 domain truncate sequence rangefuncs
temp polymorphism without_oid conversion plpgsql alter_table
limit... ok
plpgsql ... ok
copy2... ok
temp ... ok
domain ... ok
rangefuncs ... ok
prepare ... ok
without_oid ... ok
conversion ... ok
truncate ... ok
alter_table ... ok
sequence ... ok
polymorphism ... ok
test stats... ok
*** ./expected/float8-small-is-zero.out Fri Sep 26 00:16:34 2003
--- ./results/float8.outThu Mar 18 11:26:20 2004
***
*** 7,12
--- 7,86
INSERT INTO FLOAT8_TBL(f1) VALUES (' -34.84');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
+ -- test for underflow and overflow
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
+ ERROR: 10e400 is out of range for type double precision
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
+ ERROR: -10e400 is out of range for type double precision
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+ -- bad input
+ INSERT INTO FLOAT8_TBL(f1) VALUES (' ');
+ ERROR:
Re: [HACKERS] Some one deleted pg_database entry how to fix it?
Thanks, first of all it wasn't my mess, but someone elses. Secondly this worked however I was unable to use the same name, some remnants of the old database must have remained in pg_database. I couldn't even reindex it with postgres -O -P Maybe try a full dump and reload now? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Custom format for pg_dumpall
Hi, Why is there no custom format dump option for pg_dumpall? What if I want to use pg_dumpall to dump all db's and blobs? Or if I want to have a huge sql dump from which I can easily exract the sql to recreate just one table? Chris ---(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] [PATCHES] log_line_info
Please don't. Declare them obsolete for 7.5 and remove them in a later release. Nah, just remove them. We've removed, added and changed so many config options and no-one's ever complained... Chris ---(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] grants
hi there i'm having troubles to find how to GRANT SELECT ON all-tables-onmydb TO specificuser There isn't any such command. You need to write a stored procedure to do it for you in a loop. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] About hierarchical_query of Oracle
Try contrib/tablefunc Chris Li Yuexin wrote: Who can tell me how to complete /oracle's / /hierarchical_query /through postgresql/ / ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] NO WAIT ...
If NOWAIT is the choice, I could live with it. If there's no objection, I will go with NOWAIT, not NO WAIT. How about WITHOUT WAIT, which is like many of our other commands? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Tablespaces
A table space parameter will be added to DDL commands which create physical database objects (CREATE DATABASE/INDEX/TABLE/SEQUENCE) and to CREATE SCHEMA. The associated routines, as well as the corresponding DROP commands will need to be updated. Adding the ability to ALTER object TABLESPACE name seems a little painful. Would people use it? Comments? How about allowing the specification on schemas and databases of different default tablespaces for TEMP, TABLE and INDEX?? Is there any point to that? Chris ---(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] API Layers within Postgres
How easy is to to get cursor access to the indexes and fine grained control of the transaction system, are their fairly clean internal APIs I can leverage. I'm not sure 'PostgreSQL' and 'fairly clean internal API' go together :P Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Tablespaces
I've been looking at implementing table spaces for 7.5. Some notes and implementation details follow. Ah sorry, other things you might need to consider: Privileges on tablespaces: GRANT USAGE ON TABLESPACE tbsp TO ...; Different disk settings for different tablespaces (since they will likely be on different disks): ALTER TABLESPACE tbsp SET random_page_cost TO 2.5; Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] Schema comparisons
I recently had to figure out what was different between the live schema and the schema in cvs at work. This was a really painful process, and it occurred to me that it wouldn't be terribly hard to write a perl program to do it (I wound up using vim and diff). Is there interest in such a tool? I could probably have one written within a day or two. Someone wrote a utility called 'pgdiff' that generated the SQL commands necessary to transform on db in to another IIRC. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] select statement against pg_stats returns
Why? You can reconstruct it with a simple ANALYZE command. Dumping and restoring would mean nailing down cross-version assumptions about what it contains, which doesn't seem real forward-looking... I seem to recall that people like that kind of thing so that the dump is really the current state of the database. Also, I believe big db's like DB2 and Oracle do such a thing. I just recall it being discussed some time ago... 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] [GENERAL] select statement against pg_stats returns
I don't think so --- we weren't trying to use it as an actual column datatype back then. 7.4 has a problem though :-( ... this is one of the damn I wish we'd caught that before release ones, since it can't easily be fixed without initdb. Reminds me that I need to get to work on making pg_upgrade viable again. Has anyone given any thought as to whether dumping and restoring pg_statistic is worthwhile? eg. some sort of ALTER TABLE..SET STATISTICS (1.0, 3.3, 'asdf',) command? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] user defined function in CHECK constraint
Hi, I've just talked to a few users on IRC who cannot restore dumps because they use user-defined functions in CHECK constraints. Any chance this will be fixed using dependencies? Or maybe it's just easy to put all ADD CHECKs at the very end? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] unqualified function calls in system_views.sql
Do these need to be fixed in backend/catalog/system_views.sql to have pg_catalog. before everything? eg. CREATE VIEW pg_rules AS SELECT N.nspname AS schemaname, C.relname AS tablename, R.rulename AS rulename, pg_get_ruledef(R.oid) AS definition FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class)) LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE R.rulename != '_RETURN'; Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] dollar quoting
Actually, I thought the way to handle it would be to duplicate the backend lexer as nearly as possible. Most of the productions would have empty bodies probably, but doing it that way would guarantee that in fact psql and the backend would lex a string the same way, which is exactly the problem we are facing here. You'd fall out of the lexer only upon detecting backslash (unless we want to put backslash command lexing into the flex code, which might or might not be a good idea), or upon detecting a ';' at parenthesis depth 0, or upon hitting end of string. In the last case the lexer state would indicate which prompt we need to give. You know what would be really sweet? If the lexing was made available as a public function. eg. So I could parse queries in phpPgAdmin before sending them to the backend, etc... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] casting zero-length strings
Yes, surely, unless someone wants to argue for reverting that change to pg_atoi. I can't see a reason for having them act inconsistently. While we are at it we should make sure these functions are all on the same page about allowing leading/trailing whitespace. I seem to recall that the spec says somewhere that both should be allowed ... but right now I do not think we allow trailing whitespace. Either way, we should make them a WARNING for 7.5, then error in 7.6. The pg_atoi change was a bit disastrous because of instant error I thought. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] No Timeout in SELECT..FOR UPDATE
(1) Re-write the SELECT...FOR UPDATE SQL code, to return with an exception or error if it cannot immediately secure the lock, OR: You could use SET STATEMENT_TIMEOUT... 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
