Re: [HACKERS] COPY formatting
On Wed, Mar 17, 2004 at 11:02:38AM -0500, Tom Lane wrote: > Karel Zak <[EMAIL PROTECTED]> writes: > > The formatting function API can be pretty simple: > > text *my_copy_format(text *attrdata, int direction, > > int nattrs, int attr, oid attrtype, oid relation) > > This seems like it could only reasonably be implemented as a C function. Why? I said it's pseudo code. It should use standard fmgr API like every other PostgreSQL function or is it problem and I overlook something? It must to support arbitrary programming language and not C only. > I can't really imagine the average user of COPY wanting to write C in > preference to, say, an external perl script. What's the real use-case > for the feature? Don't hardcode any format to PostgreSQL, be open for others formats. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(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] Doxygen?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 17 March 2004 5:54 pm, Christopher Kings-Lynne wrote: > > 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? :) > Doxygen manuals are available on the 'net at doxygen.sourceforge.com. I'm using the '/' style, so no '@' for me. Read through them at your leisure at http://www.stack.nl/~dimitri/doxygen/manual.html This page in particular sums up all you need to know: http://www.stack.nl/~dimitri/doxygen/docblocks.html As far as a standard, I would like to actually go through and see what works before we talk of that. Personally, I am /stunned/ by the sheer volume of comments in the code, and getting all that to show up in Doxygen is a 6 month project! - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAWTgpqp6r/MVGlwwRAluNAKCm3zwp5AoY0ChTaJyki/xXRZ+2YACgosV0 RyaDbYFWYIFokmrhjpN4Y+w= =Srgo -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] COPY formatting
I deal with this daily in a cygwin environment. I wrote a simple c++ program where I hardcoded the input file name/location and output file name/location. I strip the quotation marks out where they are used for identifying text fields and change the comma's used as CSV's to pipes. I use a combination of bash scripting to execute the c++ program and then Perl to execute a stored procedure. I am new to Perl so I have not yet considered migrating it all into Perl. The dos2unix tools in cygwin always messed up the first character of the first line. I thought the real issue with the copy function and CSVs was that it did not like the use of quotations around the fields to identify text fields. For a true Windows port handling MS Excel files in their native format would be a goal I would hope. If your api could handle that then I would agree with your method. On Wed, 2004-03-17 at 22:14, Joshua D. Drake wrote: > > > > > >> > > > > That is why I suggested providing a pre-written/pre-compiled/installed > > function for CSV (call it CSV?). Advanced users could still write > > their own as people can write many other things if they know their ways. > > > > As someone who just went through a whole truckload of crap getting > delimited files parsed from MSSQL to PostgreSQL. I believe yes this > would be great thing. We ended up using plPython with the CSV module. > > Sincerely, > > Joshua Drake > > > > > ---(end of broadcast)--- > > TIP 7: don't forget to increase your free space map settings > > signature.asc Description: This is a digitally signed message part
Re: [HACKERS] COPY formatting
That is why I suggested providing a pre-written/pre-compiled/installed function for CSV (call it CSV?). Advanced users could still write their own as people can write many other things if they know their ways. As someone who just went through a whole truckload of crap getting delimited files parsed from MSSQL to PostgreSQL. I believe yes this would be great thing. We ended up using plPython with the CSV module. Sincerely, Joshua Drake ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] float8 regression test failure in head
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 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] relation_expr vs. qualified_name
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > How come half the ALTER TABLE statements use relation_expr and half use > qualified_name? relation_expr allows specification of "foo *" and "ONLY foo", and is appropriate for ALTER commands that can recurse to child tables. qualified_name is appropriate for the ones that are inherently nonrecursive. I'm entirely prepared to believe that some of them are misclassified in the grammar ... you see any problems? 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])
[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:
[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]
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
Re: [HACKERS] Topic for a student research project
--On Dienstag, März 16, 2004 17:34:25 -0800 Jonathan Gardner <[EMAIL PROTECTED]> wrote: Would you like to work with incrementally updating materialized views? I am currently deleting then reinserting rows that get updated with a pretty stupid algorithm. If you would like to investigate incremental updates, and work with me on that, it would probably be interesting and educational. I think I will be doing most of it in PlPythonU at first, then port it to C later. Sounds interesting. I have a talk on monday next week where i had to discuss my topics...do you have any documentation, sources or so that i can use for a first examination? -- Jonathan Gardner [EMAIL PROTECTED] -- Bernd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Further thoughts about warning for costly FK checks
Fabien COELHO <[EMAIL PROTECTED]> writes: > There is something I cannot visualise about the idea being discussed. What I'm imagining is a separate program that you run, and it connects to the backend and grabs schema data much like pg_dump does. (In fact the pg_dump code might possibly be useful as a starting point, at least for the data-acquisition part of it.) Then it prints out some kind of report. This would of course only be able to address problems that are apparent from static inspection of the schema. A further extension would be to give it a set of sample queries (perhaps grabbed from the postmaster log output) to see if it can spot anything wrong with the queries --- here, lack of applicable indexes would be an example of something that can only be checked when looking at a particular query. > If the tool is "separate" from postgresql as first suggested, it would > mean that it should have its own interface? It would be a new command? > What about GUI such as pgadmin3 of pgaccess? If you want a GUI, it could be a GUI, 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? > Or separate only mean that it is a "separate" function of the backend that > can be triggered by calling existing functions such as "EXPLAIN" or > "ANALYZE" or new ones such as "CHECK" or "ADVICE" or whatever. That still leaves us in the situation where only people who are capable of doing backend programming can help. I hope that a standalone program would be more understandable and could attract developers who wouldn't touch the backend. Also, you'd still have to invent an interface for it --- and the interface would be constrained by the limits of the FE/BE protocol. It would have to look like a SQL command that returns a query result, or possibly NOTICE messages, both of which are pretty confining. regards, tom lane ---(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
Dear Tom, > I like the pg_advisor idea a lot better. > > [...] > > In the third place, if we try to solve the problem by embedding checks > here and there in the backend, we'll limit ourselves to checks that can > be made with minimal impact on backend performance and complexity. And > we'll be limiting the number of people who can contribute, because > writing backend code is hard. An external tool would be a lot more > approachable IMHO. The original suggestion for pg_advisor mentioned > pluggable tests, which seems like the right kind of approach to me. There is something I cannot visualise about the idea being discussed. If the tool is "separate" from postgresql as first suggested, it would mean that it should have its own interface? It would be a new command? What about GUI such as pgadmin3 of pgaccess? Or separate only mean that it is a "separate" function of the backend that can be triggered by calling existing functions such as "EXPLAIN" or "ANALYZE" or new ones such as "CHECK" or "ADVICE" or whatever. The second idea seems more reasonnable to me, because it avoids having a separate interface. So it means that it would be a new "module" of the backend. It would also be possible to have both worlds, that is: psql> CHECK DATABASE comics; or ANALYZE comics;... WARNING: costly this, missing that AND shell> pg_advisor -h sablons -U calvin -d comics WARNING: If it is mainly in the backend, because pg_advisor will be easy to implement then;-) If all the logic is outside in a pg_advisor program, then you won't have psql/pgaccess/pgadmin3... views. Also, I'm not sure that checks would be that easy to implement outside of the backend. I think real the argument is not to have the stuff performed "on the fly", but the backend seems the proper place anyway. Anyway, from my "teacher" point a view, I think it would be great to have some place to put warnings or advices, so even if it is outside, it is better than nowhere;-) Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(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
list.c (was Re: [HACKERS] Doxygen?)
On Wednesday 17 March 2004 02:34 am, Neil Conway wrote: >> Just a heads-up: list.c and pg_list.h will be reimplemented fairly >> soon in CVS HEAD (the code isn't committed yet, but there's a >> preliminary patch I can send you if you're interested). BTW, where are you on that? I'm getting antsy to see it applied. If it's a matter of finding cycles to get the "big bang" done, maybe I could help. We could divvy up the backend tree and probably get it done in a day or so of single-minded hacking. 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] Further thoughts about warning for costly FK checks
On Wednesday 17 March 2004 17:36, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I was thinking of a GUC variable called PERFORMANCE_HINTS, which would > > throw a message if a lookup from the primary to the foreign key didn't > > have an index. > > I like the pg_advisor idea a lot better. > > In the first place, a lot of these sorts of checks don't have any clean > place to insert as a test made in-passing in regular operation. [snip] > In the second place, you don't really want notices about bad schema > design popping out during regular operation [snip] > In the third place, if we try to solve the problem by embedding checks > here and there in the backend, we'll limit ourselves [snip] Fourthly - re-checking the entire schema when you have made changes to a system is a good idea. Fifthly - this is the sort of thing that goes into the "new features" list and advocacy can talk about. People can write articles on it, all sorts. Just my tuppence-worth. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Problem on cluster initialization
Silvio Mazzaro <[EMAIL PROTECTED]> writes: > A made a tar backup copy of /var/lib/pgsql, but now (I'm still with Postgre > 7.2 on a RedHat 7.3) restoring the data and launching the daemon i receive: > postmaster successfully started > bash-2.05$ FATAL 2: The database cluster was initialized with > CATALOG_VERSION_NO 200101061, > but the backend was compiled with CATALOG_VERSION_NO 200201121. > It looks like you need to initdb. That is evidently a 7.1 database, not a 7.2 database. I'm surprised that you don't get the other version check message first --- we must have gotten the order of testing a mite confused ... anyway you need a 7.1 server. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Doxygen?
On Wed, 17 Mar 2004, Jonathan Gardner wrote: > 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. if you can write the script, and assuming nobody has objections to it, we can easily run it on the main server, directly into CVS ... doxygen is already installed for one of our other clients ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Further thoughts about warning for costly FK checks
Tom Lane <[EMAIL PROTECTED]> writes: > BTW, something that just occurred to me now: EXPLAIN is currently really > designed only for SELECTs. It would make sense to upgrade it for > INSERT/UPDATE/DELETE to list the triggers that will get fired. While > we'd have to treat user triggers as black boxes, I think it would also > be possible to "look inside" RI triggers and display the plans of the > queries that will get invoked. It seems EXPLAIN ANALYZE at least ought to be able to show the complete plan for even user triggers. -- greg ---(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] Problem on cluster initialization
Hi all! We've a problem... A made a tar backup copy of /var/lib/pgsql, but now (I'm still with Postgre 7.2 on a RedHat 7.3) restoring the data and launching the daemon i receive: postmaster successfully started bash-2.05$ FATAL 2: The database cluster was initialized with CATALOG_VERSION_NO 200101061, but the backend was compiled with CATALOG_VERSION_NO 200201121. It looks like you need to initdb. Can someone explain to me how to rebuild it? :-) No matter if i've to recompile the sources... i MUST recover the DB. Thank you for your help! Silvio Mazzaro ---(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] Doxygen?
On Wednesday 17 March 2004 12:34 am, Dave Page wrote: > > -Original Message- > > From: Jonathan Gardner [mailto:[EMAIL PROTECTED] > > Sent: 17 March 2004 01:41 > > To: [EMAIL PROTECTED] > > Subject: [HACKERS] Doxygen? > > > > I'll start posting the documentation I am generating to my > > vanity site (announcements later), but would this be > > something that the postgresql.org main site would be able to > > host? It'll be a bunch of static HTML files. > > This is not something that should be on the main site, but on > developer.postgresql.org. And yes, if there are no objections here then > I see no reason why it cannot be added. I assume you will be able to > keep it up to date? > 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. -- Jonathan Gardner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Doxygen?
I cc'ed the hackers list -- I hope you won't mind. On Wednesday 17 March 2004 02:34 am, Neil Conway wrote: > "Jonathan M. Gardner" <[EMAIL PROTECTED]> writes: > > I formatted and added some documentation for List, FastList, and > > Node. You can also browse to the list.c, pg_list.h, and nodes.h to > > see some more of my documentation. > > Just a heads-up: list.c and pg_list.h will be reimplemented fairly > soon in CVS HEAD (the code isn't committed yet, but there's a > preliminary patch I can send you if you're interested). So if you're > going to spend some time documenting stuff, choosing something else > might be a good place to start (dynahash, maybe?). > Send the patch over. I'd love to see it. I'll add some Doxygen-friendly documentation to it if you don't mind. Shouldn't this be in a branch? It sounds like it's going to be a ton of work, as the interface may change. I'm actually intensely interested in the Query struct right now as I need to understand it to get Materialized Views working right. The List concept is plain enough that I didn't waste much time documenting it -- mostly adding an extra '*' here and there to tell Doxygen that it was documentation and not just a comment. > BTW, per your comment on Lisp-ness: actually, the lispy style of > linked list implementation (cons cells) is the cause of some > performance problems in the backend. It means that both length() and > lappend() are O(n) operations; nconc() is also O(n), and equal() is a > lot slower than it could be (once length() is O(1), we can > immediately reject lists with different lengths as being > non-equal). The linked list rewrite gets rid of FastList (which is > just an ugly performance hack) and replaces the linked list > implementation with a new design that does not use cons cells. We > manage the linked list through a pointer to a separate "List" struct, > rather than merely a pointer to the head node. We also store the > length of the list in the struct. As a result, all the above > operations are now constant time (well, except for equal(), but that > is now significantly faster in the common case). > It looks like the only thing it won't do well is random accesses. That hardly ever happens in PostgreSQL, though, right? -- Jonathan Gardner [EMAIL PROTECTED] ---(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] Constraints & pg_dump
Josh Berkus <[EMAIL PROTECTED]> writes: >> Oh, why didn't you say so? Seems like the correct tool to solve that is >> a partial unique index, not a constraint at all. > Hmmm we support that?Darn, how do I miss these things. When did we > start supporting it? Bric still has a lot of users who use 7.2. Looks like it was added (back) in 7.2. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Further thoughts about warning for costly FK checks
> > Also, because of the opposition by some DBA, these checks could be disable > > by some options, but I would suggest the option to be on by default. > > I was thinking of a GUC variable called PERFORMANCE_HINTS, which would > throw a message if a lookup from the primary to the foreign key didn't > have an index. As I've looked in the code abouts these things to present some patches, there are different concepts : - message levels as DEBUG, NOTICE, WARNING, ERROR... - additional message fields as HINT, CONTEXT... I think that this is not related to level or fields. so "performance_hints" looks misleading to me. I would take a "performance_advices" or "performance_checks" as these are not used yet, and the levels may be notice/warning... and the hint field is not necessarily used. Well, this is just to talk;-) -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Further thoughts about warning for costly FK checks
Bruce Momjian <[EMAIL PROTECTED]> writes: > I was thinking of a GUC variable called PERFORMANCE_HINTS, which would > throw a message if a lookup from the primary to the foreign key didn't > have an index. I like the pg_advisor idea a lot better. In the first place, a lot of these sorts of checks don't have any clean place to insert as a test made in-passing in regular operation. I can't think of a reasonable place to do the above, for example --- the only way to do it at all would be to have the RI trigger code look at the plan it gets back to see if it's an indexscan, which is very nonmodular, and besides which the RI trigger couldn't really tell *why* the plan wasn't an indexscan; it might not be for lack of an available index. In the second place, you don't really want notices about bad schema design popping out during regular operation --- they are at best noise from the point of view of the applications using the database. What you want is something you can point at an existing database and ask for advice. In the third place, if we try to solve the problem by embedding checks here and there in the backend, we'll limit ourselves to checks that can be made with minimal impact on backend performance and complexity. And we'll be limiting the number of people who can contribute, because writing backend code is hard. An external tool would be a lot more approachable IMHO. The original suggestion for pg_advisor mentioned pluggable tests, which seems like the right kind of approach to me. BTW, something that just occurred to me now: EXPLAIN is currently really designed only for SELECTs. It would make sense to upgrade it for INSERT/UPDATE/DELETE to list the triggers that will get fired. While we'd have to treat user triggers as black boxes, I think it would also be possible to "look inside" RI triggers and display the plans of the queries that will get invoked. Not sure about the long-term usefulness of that, because Stephan keeps threatening to rewrite the RI implementation to not use normal queries ... but if it can be done without too much pain it'd be worth doing. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Constraints & pg_dump
Tom, > Oh, why didn't you say so? Seems like the correct tool to solve that is > a partial unique index, not a constraint at all. Hmmm we support that?Darn, how do I miss these things. When did we start supporting it? Bric still has a lot of users who use 7.2. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Constraints & pg_dump
Josh Berkus <[EMAIL PROTECTED]> writes: > BTW, the above is basically Bricolage's problem ... they > want only active user names to be unique. Oh, why didn't you say so? Seems like the correct tool to solve that is a partial unique index, not a constraint at all. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Further thoughts about warning for costly FK checks
Fabien COELHO wrote: > > > > Or maybe some new "ADVICE" or "ODDITY" level next to "DEBUG NOTICE > > > WARNING ERROR PANIC...", that would be hidden by default and triggered > > > by an option? > > > > But that doesn't really solve the problem, which is that there are > > conditions that it's difficult to test for on-the-fly while a schema is > > being constructed. > > Ok, I was off my shoes again, sorry. > > > A pg_advisor tool would look at the completed schema and would not have > > that problem. > > Ok. > > As a more interesting contribution to the issue, I would suggest that such > checks could be triggered by "ANALYSE". > > 1/ it is the first thing to be done on performance problems. > > 2/ it is not done too often. > > 3/ checking this stuff seems to belong to the analyse phase. >warnings about costly foreign key checks could also be repeated >on these occasions. > > Also, because of the opposition by some DBA, these checks could be disable > by some options, but I would suggest the option to be on by default. I was thinking of a GUC variable called PERFORMANCE_HINTS, which would throw a message if a lookup from the primary to the foreign key didn't have an index. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Constraints & pg_dump
Tom, > AFAIR, whether a constraint is syntactically attached to a column or is > "loose" in the table definition is not supposed to have any semantic > consequences, but I might be wrong about that too. Well, a table-level CHECK constraint can attach to more than one column, so in that way *is* different, regardless of whatever else the spec says about it. For example, table a ( col1 INT, col2 INT, CONSTRAINT less_than CHECK (col1 < col2) ); The fact that the constraint is implemented as a function shouldn't make a difference for us as long as all of the columns are named: table users ( username text, active boolean, CONSTRAINT username_is_unique CHECK cf_user_unique(username, active) ); in this case, the constraint should be triggered whenever either of the named columns is updated. BTW, the above is basically Bricolage's problem ... they want only active user names to be unique. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PATCHES] [HACKERS] Index creation takes for ever
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Where was it posted anyway? > > > Found it: > > > > > http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=200312010450.hB14ovH16330%40candle.pha.pa.us&rnum=8 > > Thanks. The original patch is much older than I thought --- I was > looking in the November/December part of the archives. > > > Personally, because frequently accessed duplicates appear more forward > > in the duplicate index, I think the sorting is only valuable when > > creating a new index. > > Yes, and that's what this does. Looking back, the original discussion > got a little confused because the TODO item about "order duplicate index > entries by tid" got brought into the mix. Actually this patch has > nothing to do with that, because it only acts during btree creation not > during index updates. > > On inspection I have no problem with the patch, only with the comments ;-) > If you like I'll revise the comments and apply. Great. Seems harmless and he showed good performance with it. I agree the discussion got confused, and that is why I kept it in my mailbox to revisit. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Constraints & pg_dump
Tom, > Is it? Our present handling of CHECK constraints cannot reasonably be > thought to support anything but row-local constraints. If they're using > a function to make an end-run around the check that prohibits subselects > in CHECK constraints, then their problems are much more serious than > whether pg_dump dumps the database in an order that manages to avoid > failure. That kind of constraint just plain does not work, because it > won't get rechecked when the implicitly referenced rows change. Hmmm ... damn, you're correct. It does seem, philosophically, like that is the appropriate topic for a constraint.However, I can see how it would be difficult to implement as one What about table-level check constraints? Seems like one of those should be able to be used to check a vertical assertion within a table. Or do we need SQL ASSERTION for this? -- -Josh Berkus Aglio Database Solutions San Francisco ---(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: [PATCHES] [HACKERS] Index creation takes for ever
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Where was it posted anyway? > Found it: > > http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=200312010450.hB14ovH16330%40candle.pha.pa.us&rnum=8 Thanks. The original patch is much older than I thought --- I was looking in the November/December part of the archives. > Personally, because frequently accessed duplicates appear more forward > in the duplicate index, I think the sorting is only valuable when > creating a new index. Yes, and that's what this does. Looking back, the original discussion got a little confused because the TODO item about "order duplicate index entries by tid" got brought into the mix. Actually this patch has nothing to do with that, because it only acts during btree creation not during index updates. On inspection I have no problem with the patch, only with the comments ;-) If you like I'll revise the comments and apply. 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] COPY formatting
Tom Lane wrote: Karel Zak <[EMAIL PROTECTED]> writes: The formatting function API can be pretty simple: text *my_copy_format(text *attrdata, int direction, int nattrs, int attr, oid attrtype, oid relation) This seems like it could only reasonably be implemented as a C function. I can't really imagine the average user of COPY wanting to write C in preference to, say, an external perl script. What's the real use-case for the feature? That is why I suggested providing a pre-written/pre-compiled/installed function for CSV (call it CSV?). Advanced users could still write their own as people can write many other things if they know their ways. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] COPY formatting
Karel Zak wrote:
Hi,
in TODO is item: "* Allow dump/load of CSV format". I don't think
it's clean idea. Why CSV and why not something other? :-)
A why not allow to users full control of the format by they own
function. It means something like:
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ FORMAT funcname ] ]
The formatting function API can be pretty simple:
text *my_copy_format(text *attrdata, int direction,
int nattrs, int attr, oid attrtype, oid relation)
-- it's pseudocode of course, it should be use standard fmgr
interface.
It's probably interesting for non-binary COPY version.
Interesting ... The alternative might be an external program to munge
CSVs and whatever other format people want to support and then call the
exisiting COPY- either in bin or contrib. I have seen lots of people
wanting to import CSVs, and that's even before we get a Windows port.
cheers
andrew
---(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] COPY formatting
Karel Zak <[EMAIL PROTECTED]> writes: > The formatting function API can be pretty simple: > text *my_copy_format(text *attrdata, int direction, > int nattrs, int attr, oid attrtype, oid relation) This seems like it could only reasonably be implemented as a C function. I can't really imagine the average user of COPY wanting to write C in preference to, say, an external perl script. What's the real use-case for the feature? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] COPY formatting
Hans-Jürgen Schönig wrote:
Karel Zak wrote:
Hi,
in TODO is item: "* Allow dump/load of CSV format". I don't think
it's clean idea. Why CSV and why not something other? :-)
A why not allow to users full control of the format by they own
function. It means something like:
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ] [ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ FORMAT funcname ] ]
The formatting
function API can be pretty simple:
text *my_copy_format(text *attrdata, int direction, int
nattrs, int attr, oid attrtype, oid relation)
-- it's pseudocode of course, it should be use standard fmgr
interface.
It's probably interesting for non-binary COPY version.
Comments?
Karel
Karel,
This seems to be an excellent idea.
People have already asked for many different formats.
Usually I recommend them to use "psql -c "COPY ..." dbname | awk ...".
Since Windows will be supported soon, it will be hard to pipe data to a
useful program (awk, sed, ...). Maybe this feature would help a lot in
this case.
Could a CSV-generating function be provided with the distribution then?
Regards,
Fernando
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] COPY formatting
Karel Zak wrote:
Hi,
in TODO is item: "* Allow dump/load of CSV format". I don't think
it's clean idea. Why CSV and why not something other? :-)
A why not allow to users full control of the format by they own
function. It means something like:
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ FORMAT funcname ] ]
The formatting function API can be pretty simple:
text *my_copy_format(text *attrdata, int direction,
int nattrs, int attr, oid attrtype, oid relation)
-- it's pseudocode of course, it should be use standard fmgr
interface.
It's probably interesting for non-binary COPY version.
Comments?
Karel
Karel,
This seems to be an excellent idea.
People have already asked for many different formats.
Usually I recommend them to use "psql -c "COPY ..." dbname | awk ...".
Since Windows will be supported soon, it will be hard to pipe data to a
useful program (awk, sed, ...). Maybe this feature would help a lot in
this case.
Regards,
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] COPY formatting
Hi,
in TODO is item: "* Allow dump/load of CSV format". I don't think
it's clean idea. Why CSV and why not something other? :-)
A why not allow to users full control of the format by they own
function. It means something like:
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ FORMAT funcname ] ]
The formatting function API can be pretty simple:
text *my_copy_format(text *attrdata, int direction,
int nattrs, int attr, oid attrtype, oid relation)
-- it's pseudocode of course, it should be use standard fmgr
interface.
It's probably interesting for non-binary COPY version.
Comments?
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(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] Doxygen?
Folks, this is what greenhorns like me have been waiting for. Great stuff. Keep the faith Zoltan - Original Message - From: "Jonathan M. Gardner" <[EMAIL PROTECTED]> To: "Bruce Momjian" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, March 17, 2004 7:50 AM Subject: Re: [HACKERS] Doxygen? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 16 March 2004 5:56 pm, Bruce Momjian wrote: > Jonathan Gardner wrote: > > I'll start posting the documentation I am generating to my vanity > > site (announcements later), but would this be something that the > > postgresql.org main site would be able to host? It'll be a bunch of > > static HTML files. > > Sure, I would like to see that. I posted some preliminary documentation at the following URL. This only contains stuff found in src/backend/node and src/include/node. This URL won't be permanent. http://www.jonathangardner.net/PostgreSQL/doxygen/ I formatted and added some documentation for List, FastList, and Node. You can also browse to the list.c, pg_list.h, and nodes.h to see some more of my documentation. Suggestions welcome. I am still new with Doxygen, so if you have any tips, I'm all ears. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAV/Upqp6r/MVGlwwRAjssAKCl7GaW36qTH5svMlQronc+FzhMYQCeOcwv Vi7AmyIS/pjYUong60sYxfE= =WSfP -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Constraints & pg_dump
Tom Lane wrote: Our present handling of CHECK constraints cannot reasonably be thought to support anything but row-local constraints. If they're using a function to make an end-run around the check that prohibits subselects in CHECK constraints, then their problems are much more serious than whether pg_dump dumps the database in an order that manages to avoid failure. That kind of constraint just plain does not work, because it won't get rechecked when the implicitly referenced rows change. Ouch. Two days ago I saw someone on IRC (I think from this list) actually advising someone to use this end-run. Maybe we need to beef up the docs on this point? cheers andrew ---(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] Further thoughts about warning for costly FK checks
> > Or maybe some new "ADVICE" or "ODDITY" level next to "DEBUG NOTICE > > WARNING ERROR PANIC...", that would be hidden by default and triggered > > by an option? > > But that doesn't really solve the problem, which is that there are > conditions that it's difficult to test for on-the-fly while a schema is > being constructed. Ok, I was off my shoes again, sorry. > A pg_advisor tool would look at the completed schema and would not have > that problem. Ok. As a more interesting contribution to the issue, I would suggest that such checks could be triggered by "ANALYSE". 1/ it is the first thing to be done on performance problems. 2/ it is not done too often. 3/ checking this stuff seems to belong to the analyse phase. warnings about costly foreign key checks could also be repeated on these occasions. Also, because of the opposition by some DBA, these checks could be disable by some options, but I would suggest the option to be on by default. Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Doxygen?
> -Original Message- > From: Jonathan Gardner [mailto:[EMAIL PROTECTED] > Sent: 17 March 2004 01:41 > To: [EMAIL PROTECTED] > Subject: [HACKERS] Doxygen? > > I'll start posting the documentation I am generating to my > vanity site (announcements later), but would this be > something that the postgresql.org main site would be able to > host? It'll be a bunch of static HTML files. This is not something that should be on the main site, but on developer.postgresql.org. And yes, if there are no objections here then I see no reason why it cannot be added. I assume you will be able to keep it up to date? Regards, Dave. ---(end of broadcast)--- TIP 8: explain analyze is your friend
