Re: [HACKERS] PostgreSQL port to pure Java?
That was uncalled for. Statements like this do not make the Postgres community any healthier. You don't have any benefit of pushing back Java users. Ivelin --- Jean-Michel POURE <[EMAIL PROTECTED]> wrote: > Le Mardi 09 Décembre 2003 16:15, Ivelin Ivanov a > écrit : > > I think that a co-bundle between an open source > J2EE > > container like JBoss and a scalable database like > > PostgreSQL will be a blast. > > Why not cut all trees on earth and replace them with > plastic? Before that, we > need to port mankind DNA to Windows 3.1 in order to > improve speed. > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] (Mis?)Behavior of \copy with -f and \i
Greetings... I've run into a problem with the way \copy behaves when psql is reading its input from a file using either the -f command line option or the \i command. (Not that it matters in this case, but this is PostgreSQL 7.4 on i686-pc-linux-gnu, compiled with gcc.) The following... ---8<--SNIP--- CREATE TABLE junk ( abbrev CHAR(1), name VARCHAR(80) ); \copy junk (abbrev, name) FROM STDIN WITH DELIMITER '|' NULL '' F|Foo B|Bar Z|Baz \. ---8<--SNIP--- ...works just fine when keyed directly into psql or is redirected from a file (i.e., psql < junk.sql). When using -f or \i, psql sits and waits for data and an EOF on the standard input and then proceeds to interpret the next four lines as commands. This was discussed several years ago in this thread: http://archives.postgresql.org/pgsql-hackers/2000-01/msg00361.php Peter Eisentraut declared that from that point on, stdin would be whatever stream the \copy command came from. I'd like to propose a variant on the "FROM" clause which makes good on Peter's declaration without breaking anything already using FROM STDIN and expecting it to really read from stdin. (I think this is for the better because there are lots of good uses for "psql -f foo.sql < foo.dat".) I'd be more than happy to write and test a patch if folks think this would be a good thing. I'm leaning toward "FROM -" as the syntax but am open to other ideas (i.e., "FROM HERE" or "FROM INPUT"). - Mark P.S.: I've been using Postgre(s(95)?|SQL) in its various forms for close to a decade, and what was a stable platform for lightweight storage has matured into something I'd pour a few million rows into without thinking twice about it. Thanks to everyone for all the great work! ---(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: [GENERAL][ADMIN][HACKERS]data fragmentation
Hello, An quicker option would be to use rsync (on a stopped database of course). You can rsync to a new directory (off the filesystem) and then reformat the data filesystem and move it back. J Somasekhar Bangalore wrote: Hi, I too had the same problem; There was one query which used to take a very long time. What I did was, I took a backup of the whole database. Reinstalled postgres on a different mount point and restored the data back into the new database. Now my queries are running faster. Try it. All the very best. Somasekhar -Original Message- From: Jaime Casanova [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 3:07 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [GENERAL][ADMIN][HACKERS]data fragmentation Hi, i have a theorical question. i was thought that data fragmentation can cause a loss of performance when retrieving data from a database. Some DBMS solved this with dbspaces, but postgresql doesn't support this concept. so, pgsql databases tend to suffer from data fragmentation? if yes, what is the solution you recommend? also i was thought that even when DBMS support dbspaces DELETEing records may cause data fragmentation anyway. so, can i think of DELETE statement as a double-edged sword? it is indifferent in pgsql - it doesn't support dbspaces anyway? thanks in advance, Jaime Casanova (el_vigia) _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Distributed keys / Inheritance
Here's Tom Lane's response to my original post on pgsql-sql: This seems to have considerable overlap with the problem of indexing inheritance hierarchies (so that constraints on tables with children would work as people expect). It may be that it's sufficient to solve it for inheritance cases, and not try to support the generic case of constraints applied across arbitrary sets of tables. ISTM that the latter could introduce a bunch of extra definitional and practical issues beyond what you'd have to solve to do the former. You're discussing it on the wrong list though --- try -hackers. Hadn't thought of distributed keys being related to inheritance, though now I can see how one might apply inheritance to the situations I mentioned above. Is this what you were thinking, Tom? Case 1: employees (id, name) schools (name, location) teachers (id, name, school_name) where (id, name) inherits from employees and school_name references schools(name) subs (id, name) inherits from employees managers (id, name, school_name) where (id, name) inherits from employees and school_name references schools(name) Case 3 comments (comment_id, comment) employees(id, name) comments_nonemployees (comment_id, comment, name) where (id, comment) inherits from comments comments_employees (comment_id, comment, employee_id) where (comment_id,comment) inherits from comments, employee_id references employees(id) I don't know very much about inheritance in PostgreSQL, so I'm doing a bit of reading via Google. If anyone has recommendations on good sources re: inheritance in PostgreSQL, I'd be thankful. Regards, Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Fwd: [SQL] Distributed keys
Begin forwarded message: From: Michael Glaesemann <[EMAIL PROTECTED]> Date: December 23, 2003 3:42:24 PM CST To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Subject: [SQL] Distributed keys Hello all! An area of the relational database model that is not included in the SQL standard are distributed keys and distributed foreign keys. A quick example (Case 1): employees (id, name); schools (name, location); teachers (employee_id, school_name); subs (employee_id); managers (employee_id, school_name); with constraints unique employees(id) teachers(employee_id) references employees(id) teachers(school_name) references schools(name) subs(employee_id) references employees(id) managers(employee_id) references employees(id) The idea is that employees must be in one (and only one) of either teachers, subs, or managers. Currently, this might be represented in something like (Case 2) employees (id, name, employee_type, school_name); schools (name, location); employee_types (type); with constraints employees(employee_type) references employee_types(type) employees(school_name) references schools(name) where employee_types includes "teacher", "sub", and "manager" Or it might be represented with a number of rules or triggers that perform all of the necessary checking. employees(school_name) can't have a not null constraint because if the employee is a sub, they aren't associated with a school. Using the terms "distributed key" and "foreign distributed key", in the first case employee_id is a "distributed key" in that it must occur in only one of the tables teachers, subs, or managers. Distributed keys are similar in concept to primary keys—they must be unique. This guarantees an employee_id in teachers is not found in subs or managers, an employee_id in subs is not found in managers or teachers, and an employee_id in managers is not found in subs or teachers. employees(id) is a foreign distributed key in teachers, subs, and managers (as employee_id). Foreign distributed keys are similar in concept to foreign keys in that employees(id) must be referenced by a single tuple in one of teachers, subs, or managers. Another use would be in this situation (something I'm working on right now): I want to link comments by employees by employee_id, but comments from non-employees by name (as they don't have an id). comments(id, comment); comments_nonemployees(comment_id, name); comments_employees(comment_id, employee_id); with constraints comments_nonemployees(comment_id) references comments(id) comments_employees(comment_id) references comments(id) and comments(id) must be listed in either comments_nonemployees(comment_id) or comments_employees(comment_id) I haven't looked very far into how to implement distributed keys and foreign distributed keys in PostgreSQL beyond briefly looking at the pg_constraint system table, thinking a distributed key would be something making employee_id unique in teachers(employee_id) UNION subs(employee_id) UNION managers(employee_id). A distributed key is distributed over a number of tables, rather than a single one, so there'd have to be a list of relid-attnum pairs, rather than a single relid-attnum pair, such as conrelid and conkey in pg_constraint. Here's a brief sketch of the idea: pg_distributed distname name the name of the distributed key constraint distrelid oid the relid of one of the tables involved in the distributed keys distkey int2[] a list of the attnum of the columns of the table with oid distrelid involved in the distributed key distforkey bool true if foreign distributed key distfrelid oid if a foreign distributed key, the relid of the the referenced table, else 0 distfkey int2[] if a foreign distributed key, a list of the attnum of the columns of the table with oid distfrelid referenced by the foreign distributed key, else 0 In pg_distributed, distname, distfrelid, distfkey would be the same in every tuple involved in the distributed key, while distrelid and distkey would vary. Basically a one-to-many relation. These are just some thoughts, and the first time I've looked at the PostgreSQL internals, so I wouldn't be suprised if this approach is wrongheaded. I know PostgreSQL endeavors to adhere closely to the SQL standard, and I think this is important as SQL *is* a standard. One of the things that makes PostgreSQL great in my opinion is that it goes beyond the standard in some areas where users and developers have found useful, such as the PostgreSQL rule system. I think distributed key support would extend the usefulness of PostgreSQL without hindering SQL conformance. I'm interested in hearing what others have to say, especially along the lines of implementation. I haven't seen much discussion of distributed keys on the lists (other than Josh Berkus), so perhaps there isn't much interest. Would there be any foreseeable opposition if I (and/or others) worked on this? Regards, Michael Glaesemann grzm myrealbox com
