Re: [HACKERS] Proposal for debugging of server-side stored procedures
-Original Message- From: Thomas Hallgren [mailto:[EMAIL PROTECTED] Sent: 09 June 2006 16:25 To: Mark Cave-Ayland Cc: pgsql-hackers@postgresql.org Subject: Re: Proposal for debugging of server-side stored procedures Some thoughts from another Tom... Hi Tom, Thanks for the feedback :) All PL's are launched someway or another . It would probably be very simple to add the '-d' flag to the PL/Perl launcher and the '-m' flag to the PL/Python launcher and control it with module specific GUC settings. PL/Java already does this. The SQL to initialize debugging looks like this: SET pljava.vmoptions TO '- agentlib:jdwp=transport=dt_shmem,server=y,suspend=y'; This tells the VM to load in-process debugging libraries and specifies the kind of connection to be made. As soon as the first java function is called, the process is suspended and waits for a client debugger to attach. The amount of work needed to create similar solutions for perl, python, tcl, etc. is probably limited and fairly trivial. I think that Java is quite unusual in that the design of JPDA is inherently client/server based to the point where they have defined the platform to allow you interact with the JVM via a socket. Unfortunately the same can't be said for Perl/Python - as you suggest passing the parameters into the interpreter is a trivial exercise but because the debugging classes in both languages are console interactive, the only thing you could do is redirect the console output to a socket (see http://search.cpan.org/dist/perl/lib/perl5db.pl and the RemotePort option in the case of Perl). What I would like to see is some form of IDE that our developers can use, probably under Windows, that would enable them to step through and debug stored procedures on a remote server on another network. Using simple console redirection would involve parsing text output which strikes as being something that would break easily with new releases. And then of course, there is the problem of security whereby anyone could connect to the socket. For example, imagine a group of developers all debugging different functions simultaneously; if one of them connected to the wrong console socket then it could be confusing as the developer wanders why their code never stops at a breakpoint. And it would force you to write your own proprietary debugger backend for each language. That's a major thing to undertake. Have you considered the maintenance aspect of it? Not something that would make the author of a PL module scream with joy. It might be wise to also consider what debugger preferences a skilled perl/python/whatever language developer might have. A home brewed debugger in the form of a PostgreSQL add-on might not be a natural choice. Well my original thinking was that you could factor most of the code into PostgreSQL itself; the only thing that PL developers would have to is provide an API for things like step, set breakpoint, read variable, and eval. A promising option at the moment would be to implement the DBGP protocol for Perl/Python/Tcl as suggested by Lukas, mainly because it appears ActiveState have already written the modules to do this (see http://aspn.activestate.com/ASPN/Downloads/Komodo/RemoteDebugging). The only issue I can see with this is again related to security in that the debugger would not respect the ACLs within PostgreSQL which could potentially allow a user to break inside a function that wasn't his/her own. Kind regards, Mark. WebBased Ltd 17 Research Way Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 http://www.webbased.co.uk http://www.infomapper.com http://www.swtc.co.uk This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal for debugging of server-side stored procedures
-Original Message- From: Andrew Dunstan [mailto:[EMAIL PROTECTED] Sent: 09 June 2006 17:01 To: Mark Cave-Ayland Cc: 'Tom Lane'; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Proposal for debugging of server-side stored procedures (cut) Debugging embedded perl has some significant challenges. You might find it interesting to see what can be done in the most famous embedded situation: mod_perl. See http://perl.apache.org/docs/1.0/guide/debug.html using ptkdb might be nice cheers andrew Hi Andrew, Thanks for the link. This brings up another issue - what if someone wishes to debug locally using their favourite tool rather than a PostgreSQL tool? Should this be allowed even if it may circumvent PostgreSQL's user permissions on functions within the database? Kind regards, Mark. WebBased Ltd 17 Research Way Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 http://www.webbased.co.uk http://www.infomapper.com http://www.swtc.co.uk This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Latest timezone data in 8.1.4
Hi,
At work, our production 8.1.x systems were having problems with
timezones. A new code push had newer 2006 time zones that Postgres
didn't recognize.
To fix this I built a custom RPM that overwrites the timezone data
with the latest data from nih.gov.
I changed the postgres-8.1.4-1PDG.spec file as follows:
* Add the following line to the end of the Source file section:
Source99: ftp://elsie.nci.nih.gov/pub/tzdata2006g.tar.gz
* Add the following commands to the end of the %prep section
cd src/timezone/data
tar xzvf %{SOURCE99}
Some questions...
* Is this the correct way to do this?
* Can updating the timezone data be a part of the release checklist?
* Finally, is it possible to upgrade a running server with new timezone
data?
--
Paul Lindner| | | | | | | | | |
[EMAIL PROTECTED]
pgpaYwDMYHRWb.pgp
Description: PGP signature
[HACKERS] Ranges for well-ordered types
I've been interested in representing and manipulating time ranges in PostgreSQL, where a time range is defined by a start time and an end time. Time ranges are useful, for example, in representing when some predicate was known valid. Similarly, time ranges can be used to represent transaction time: the version history of the data itself. This is similar to the time travel feature in previous versions of PostgreSQL. (Tables that include both valid time and transaction time information are sometimes called bitemporal tables.) Ranges can also be useful in scheduling applications. The original use case that prompted me to explore this area was tracking what time periods teachers were assigned to schools (a valid- time table). Here's one way to represent this in PostgreSQL: create table teachers__schools_1 ( teacher text not null , school text not null , from_date date not null , to_date date not null , check (from_date = to_date) , unique (teacher, school, from_date) , unique (teacher, school, to_date) ); Each row of this table represents the time range (from from_date to to_date) during which a teacher was assigned to a particular school. (Teachers can be assigned to more than one school at a time.) The check constraint guarantees that [from_date, to_date] represents a valid closed-closed interval (where the end points are included in the range). Two unique constraints are necessary to guarantee uniqueness for each row. In my use case, it would also be desirable to apply constraints to prevent overlapping and ensure continuity-- that teachers are always at some school. This can be done using constraint triggers, though making sure all of the comparisons for four dates (beginning and end points for two ranges) are done properly can be a little daunting and prone to bugs. The problem of representing time ranges in a relational database has been worked on for quite some time. Indeed, the PostgreSQL source still includes a tinterval type, where the beginning and end points are of type abstime, though this type is no longer included in the documentation. Drafts of SQL3 included the PERIOD constructor to represent date, time, and timestamp ranges as part of SQL/Temporal, but this section of the specification was not included in the final SQL:2003 standard. At least two relatively prominent books [1][2] as well as numerous papers have been written on the subject. An aside regarding terminology: In the literature I've read, time ranges are most often referred to as intervals. However, SQL already uses INTERVAL to refer to another distinct type. As mentioned above, SQL3 used the term PERIOD for a constructor to create types with a beginning point and an end point. RANGE is a reserved keyword in SQL: 2003 (related, I believe, to windowed tables). Range also has a distinct meaning in relational calculus. I'm at a bit of a loss as to how to refer to these structures with a beginning and an end point with a term that isn't already reserved in SQL or may be in the future. Suggestions welcome :) Span? Reuse tinterval? For the time being, I'll arbitrarily continue to use range. In the first six months of 2006 alone, I've noted quite a few threads related to time ranges in the various PostgreSQL mailing lists, so it seems this issue is ripe for a solution. Just two weeks ago, Albert Hertroys started work on a vector type[3] , where he rightly notes that time ranges are just an application of a general range (or vector, to use his term) that could just as easily be used with integers, reals, points, dates, etc. For the past couple of months, I've been working with composite types and PL/pgSQL to define and manipulate date ranges and integer ranges, and see what can be abstracted out to a general range type. In the general case, a particular range value can be represented as two point values. For example, the date range [2006-01-01, 2006-05-31] (using the closed-closed interval representation) is represented by the two date point values 2006-01-01 and 2006-05-31. The interval range [3,9] is represented by the two integer point values 3 and 9. A range can be formed for any point type, where a point type is any type that's well-ordered: * the range of values is bounded (the number of values in the type is finite) * comparisons are well-defined for any two values, and * for any point p, the next point can be found using a successor function Given a well-ordered point type, common questions of ranges can be be answered, such as, for two ranges r1 and r2 * Do r1 and r2 overlap? * Does r1 meet r2? * Is the union of r1 and r2 defined, and if so, what is it? * Is the intersection of r1 and r2 defined, and if so, what is it? The way I've thought of implementing ranges in PostgreSQL is through an additional system catalog table (called
Re: [HACKERS] Ranges for well-ordered types
On 6/10/06, Michael Glaesemann [EMAIL PROTECTED] wrote: Returning to my original example, with a date_range type, the tablecould be defined as: create table teachers__schools_2(teacher text not null, school text not null, period date_range not null, primary key (teacher, school, period));The original check constraint is handled by the date_range type and the two unique constraints are replaced by a single primary keyconstraint. Constraints for overlapping and continuity are stillhandled using constraint triggers, but are easier to implement usingfunctions available to compare ranges rather than handling beginning and end points individually. I've done similar date range things by creating a composite type consisting of the lower and upper bounds, and then implementing a btree opclass where the comparator returns 0 if two ranges overlap - this allows a current btree index to enforce non-overlapping ranges, and allows indexed lookup of which range contains a particular value. Not sure whether this covers your scenario, but it works fairly well for me :) Ian
Re: [HACKERS] How to avoid transaction ID wrap
On Fri, Jun 09, 2006 at 06:20:21PM -0700, Trent Shipley wrote: VACCUM needs to be run for two reasons. 1) To recover the transaction counter. 2) To recover records marked for deletion. VACCUM needs to be run over the entire database. If the data in the database is N, then VACCUM is O(N). Roughly, VACCUM scales linearly with the size of the database. Well, you only need to vacuum the entire database once every billion transactions. Secondly, you can vacuum table by table. If you know a table will never be modified, you can VACUUM FREZZE it, then it will never need to be vacuumed again, ever (until you make changes ofcourse). Ideally, the transaction management system would be proportional to the marginal change in size of the database rather than the gross size of the database. That is VACCUM being O(N) should be replaced (or there should be an optional alternative) that scales with D, O^k(D) where any k 1 involves a tradeoff with VACCUM. That's why people suggest partitions. Then you only vacuum the partitions that are new and the old ones never need to be touched... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Ranges for well-ordered types
Ian Caulfield [EMAIL PROTECTED] writes: I've done similar date range things by creating a composite type consisting of the lower and upper bounds, and then implementing a btree opclass where the comparator returns 0 if two ranges overlap - this allows a current btree index to enforce non-overlapping ranges, and allows indexed lookup of which range contains a particular value. And how hard did you test this? Non-transitive equality is certain to confuse btree, leading to wrong answers. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Latest timezone data in 8.1.4
On Sat, Jun 10, 2006 at 05:44:37AM -0700, Paul Lindner wrote: Some questions... * Is this the correct way to do this? It's as good a way as any. I have on occasion considered linking the postgres timezone data to the system timezone data, solving this problem. * Can updating the timezone data be a part of the release checklist? That would be nice. IIRC, the changed daylight savings for australia never made any release before it actually happened. * Finally, is it possible to upgrade a running server with new timezone data? Sure, it'll take effect immediatly for new backends. For existing ones probably not. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Ranges for well-ordered types
On Jun 10, 2006, at 23:51 , Michael Glaesemann wrote: A range can be formed for any point type, where a point type is any type that's well-ordered: * the range of values is bounded (the number of values in the type is finite) * comparisons are well-defined for any two values, and * for any point p, the next point can be found using a successor function It was pointed out to me off list that I got my definition of well- ordered wrong. I was confusing the definition of well-ordered with the overall requirements that I was using to define ranges. Well-ordered is just that for any two values a and b of a given type, a b is defined. That's what I was attempting to get at in the second point above. The added requirements of having the type bounded (which is going to happen on a computer anyway) and having a successor function are still required for the range definition, but not part of the definition of well-orderedness per se. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: 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] [PATCHES] ADD/DROP INHERITS
Also a couple other thoughts: I have a bit of uneasiness about the use the first hole method for adding parents. Namely it makes the whole thing a bit unpredictable from the user's point of view. The holes aren't user visible so they have no way to know when they add a parent where in the list of parents it will appear. And when you add something to a list don't you usually expect it to appear last? It's not exactly least-surprise compliant to have it appearing in the middle of the list of parents. But perhaps it's just worth those downsides to keep DROP/ADD a noop in more cases. But on that note I'm starting to have second thoughts about the one-wayness of attislocal-1. It means if you ever drop a partition all the columns will become attislocal=1 forevermore, even if you re-add the partition. It also means if you create partitions independently and then add them they behave differently from if you create them as inherited tables. I'm thinking that in the partitioned table use case this will get in the way of dropping columns from a partitioned table. You'll essentially be forcing users to drop the column manually from every child. Maybe it would be better to set attislocal=0 if the attinhcount goes from 0-1? Otherwise if we don't allow new columns to be created in ADD INHERIT then we're forcing users to treat all their columns as locally defined. -- greg ---(end of broadcast)--- TIP 1: 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] Ranges for well-ordered types
On Jun 11, 2006, at 0:54 , Ian Caulfield wrote: I've done similar date range things by creating a composite type consisting of the lower and upper bounds, and then implementing a btree opclass where the comparator returns 0 if two ranges overlap - this allows a current btree index to enforce non-overlapping ranges, and allows indexed lookup of which range contains a particular value. As Tom already pointed out, this method leads to problems with btree indexes. I haven't heavily tested my own implementation (below), but it only returns 0 for equality, which is what btree expects. All other possible relationships between two ranges have a well-defined result of -1 or 1. I believe this should be enough to prevent any transitivity issues with btree. Michael Glaesemann grzm seespotcode net create type interval_date as ( _1 point_date , _2 point_date ); comment on type interval_date is 'The internal representation of date intervals, representing the closed-closed ' 'interval [_1,_2]'; create function interval_cmp( interval_date -- $1 i1 , interval_date -- $2 i2 ) returns integer strict immutable security definer language plpgsql as ' declare i1 alias for $1; i2 alias for $2; cmp integer; begin perform check_intervals(i1,i2); cmp := 1; if i1._1 = i2._1 and i1._2 = i2._2 then cmp := 0; else if (i1._2 i2._2) or (i1._2 = i2._2 and i1._1 i2._1) then cmp = -1; end if; end if; return cmp; end; '; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Ranges for well-ordered types
On Jun 11, 2006, at 2:34 , Michael Glaesemann wrote: On Jun 11, 2006, at 0:54 , Ian Caulfield wrote: I've done similar date range things by creating a composite type consisting of the lower and upper bounds, and then implementing a btree opclass where the comparator returns 0 if two ranges overlap - this allows a current btree index to enforce non-overlapping ranges, and allows indexed lookup of which range contains a particular value. As Tom already pointed out, this method leads to problems with btree indexes. I haven't heavily tested my own implementation (below), but it only returns 0 for equality, which is what btree expects. All other possible relationships between two ranges have a well-defined result of -1 or 1. I believe this should be enough to prevent any transitivity issues with btree. Of course, this method doesn't provide the non-overlapping constraint. That still needs to be handled by a constraint trigger. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] ADD/DROP INHERITS
Greg Stark [EMAIL PROTECTED] writes: Maybe it would be better to set attislocal=0 if the attinhcount goes from 0-1? That just moves the surprises to other cases. I think I'd prefer to err in the direction that can't cause unexpected data loss (due to columns being dropped that perhaps should not have been). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Martijn van Oosterhout kleptog@svana.org writes: Right now I'm confused though. I was under the impression the changes were going to be ripped out because it was decided to be unworkable. I think improvements can be made but I'm unsure if there's any interest... I've reverted the current patch because it clearly doesn't work well enough. There's nothing stopping you from having a better idea though. It's clear that on some platforms the cost of gettimeofday is high enough that some workaround would be good. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
Martijn van Oosterhout kleptog@svana.org writes: The interesting thing about this is that they obviously are gearing gettimeofday() to be accurate, rather than just considering it a counter that is somewhat close to real time. At the expense of speed. Not sure that that's an accurate description. What I think the kernel fuss is about is that they have to read the counter value as several separate byte read operations, and if the hardware doesn't latch the whole counter value when the first byte is pulled then they'll get bytes from several distinct states of the counter, leading to something that's not consistent or even monotonic. On non-latching hardware there's really not a lot of choice what to do. The patch is about not using that same very-slow code path on hardware that does latch. regards, tom lane ---(end of broadcast)--- TIP 1: 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] [PATCHES] ADD/DROP INHERITS
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: Maybe it would be better to set attislocal=0 if the attinhcount goes from 0-1? That just moves the surprises to other cases. Sure, but if we're not allowing new columns to be created, those surprise cases now include virtually every case. At least for partitioned tables. I think I'd prefer to err in the direction that can't cause unexpected data loss (due to columns being dropped that perhaps should not have been). I figured that was the thinking. Perhaps what's really needed is to move away from the idea of automatically deciding whether to drop child columns and never drop child columns unless the user specifies some keyword which would force them to always be dropped. It seems to me that trying to distinguish locally defined versus only inherited is too subtle a distinction and depends too much on what the user considers a local definition. What's locally defined seems to vary depending on the application. -- greg ---(end of broadcast)--- TIP 1: 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] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef()
Joshua D. Drake [EMAIL PROTECTED] writes: So could I get some further definition? There are two fairly strong reasons for NOT trying to push more logic into the backend from pg_dump: 1. It would remove the freedom we currently have to make pg_dump adapt dumps from old servers to match newer syntax/semantics. This has saved our bacon more than once in the past, so it shouldn't be given up lightly. 2. The backend functions invariably read the catalogs under SnapshotNow rules, making pg_dump unable to promise a consistent snapshot to the extent that it relies on them. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] How to avoid transaction ID wrap
Martijn van Oosterhout kleptog@svana.org writes: That's why people suggest partitions. Then you only vacuum the partitions that are new and the old ones never need to be touched... This will all work a lot better once we track XID wraparound risk on a per-table rather than per-database basis. I hope that will be done in time for 8.2. regards, tom lane ---(end of broadcast)--- TIP 1: 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] That EXPLAIN ANALYZE patch still needs work
Greg Stark [EMAIL PROTECTED] writes: There seem to be two types of overhead going on. There's the amount of time spent in gettimeofday itself which is pretty consistent. That is a fact not in evidence. The impression I had from that linux-kernel discussion was that the problematic kernel code was looping until it got consistent results from successive hardware reads. I'm not at all sure that you can make the above assertion across all varieties of clock hardware, or even all common varieties. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
MvO == Martijn van Oosterhout kleptog@svana.org writes: MvO What we want is just a monotonically increasing counter that can MvO be read quickly and consistantly, we're not majorly fussed if it MvO doesn't match real time. This puts us back to CPU cycle counters, MvO but they have drawbacks of their own. It is amazing how this discussion is mirroring discussions on the linux-kernel list. Applications have been using CPU cycle counters on Linux to avoid the gettimeofday() overhead. With reasonably recent kernels, the overhead is very low when the CPU cycle counters are usable, because gettimeofday() never actually enters kernel space. Unfortunately fewer and fewer systems seem to have usable cycle counters. As an example, dual core Athlon64/Opteron boots with the cycle counters unsynced. The kernel can compensate for that. However they also lose sync whenever clock frequency changes, and the kernel has a really hard time compensating for it. On such systems the kernel switches back to slower timers and gettimeofday() becomes a real system call. Applications should not try to use cycle counters directly on such systems. If the kernel developers manage a good workaround, gettimeofday() becomes fast again, but applications which use cycle counters most likely stay broken. Basically either gettimeofday() is fast, or the cycle counters are useless -- unless you really care about counting CPU cycles and not real time. Some CPUs like Transmetas actually get the counter thing right and count 2 every tick when running at half speed and so on. /Benny ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] bison version
Hi, I'd like to check 2 things: What's the bison version required to compile gram.y ? Trying to set up a build farm machine, it seems I can't compile with bison 2.1 ... Also where is the documentation page that gives postgresql limits (number of column/table max size of col) Many thanks -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] bison version
ohp@pyrenet.fr wrote: Hi, I'd like to check 2 things: What's the bison version required to compile gram.y ? Trying to set up a build farm machine, it seems I can't compile with bison 2.1 ... 2.1 should work fine - there are a number of boxes on the buildfarm running with that version (like sponge the FC5/ppc I own). What exact problem do you see on your platform ? Also where is the documentation page that gives postgresql limits (number of column/table max size of col) http://www.postgresql.org/docs/faqs.FAQ.html#item4.4 Stefan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bison version
ohp@pyrenet.fr wrote: Hi, I'd like to check 2 things: What's the bison version required to compile gram.y ? Trying to set up a build farm machine, it seems I can't compile with bison 2.1 ... 1.875 Also where is the documentation page that gives postgresql limits (number of column/table max size of col) FAQ. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Ranges for well-ordered types
On Sat, Jun 10, 2006 at 23:51:58 +0900, Michael Glaesemann [EMAIL PROTECTED] wrote: Each row of this table represents the time range (from from_date to to_date) during which a teacher was assigned to a particular school. (Teachers can be assigned to more than one school at a time.) The check constraint guarantees that [from_date, to_date] represents a valid closed-closed interval (where the end points are included in the range). Two unique constraints are necessary to guarantee I think you might want to reconsider your design. It works well for dates because sets of dates are made of of isolated points and such sets are both open and closed. If you are using time, I think it will be more convenient to use a closed, open representation. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: So could I get some further definition? There are two fairly strong reasons for NOT trying to push more logic into the backend from pg_dump: 1. It would remove the freedom we currently have to make pg_dump adapt dumps from old servers to match newer syntax/semantics. This has saved our bacon more than once in the past, so it shouldn't be given up lightly. 2. The backend functions invariably read the catalogs under SnapshotNow rules, making pg_dump unable to promise a consistent snapshot to the extent that it relies on them. O.k. color me stupid but what does what you said above have in any way to do with what the requirements for these functions are? Maybe I am misunderstanding the TODO (which is entirely possible due to the complete lack of documentation on the feature) but I *thought* all I was going to do was create 6 functions that could be called to get various useful information? For example, pg_get_tabledef() would be a very handy function to use for just about any abstracted API. As it stands now most (like Pear) create their own custom queries/functions to handle it but they are more often then not very innefficient. ? Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: 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] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Joshua D. Drake wrote: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: So could I get some further definition? There are two fairly strong reasons for NOT trying to push more logic into the backend from pg_dump: 1. It would remove the freedom we currently have to make pg_dump adapt dumps from old servers to match newer syntax/semantics. This has saved our bacon more than once in the past, so it shouldn't be given up lightly. 2. The backend functions invariably read the catalogs under SnapshotNow rules, making pg_dump unable to promise a consistent snapshot to the extent that it relies on them. O.k. color me stupid but what does what you said above have in any way to do with what the requirements for these functions are? Maybe I am misunderstanding the TODO (which is entirely possible due to the complete lack of documentation on the feature) but I *thought* all I was going to do was create 6 functions that could be called to get various useful information? For example, pg_get_tabledef() would be a very handy function to use for just about any abstracted API. As it stands now most (like Pear) create their own custom queries/functions to handle it but they are more often then not very innefficient. I thought the TODO item was exactly what you described: * %Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef() We have per-server-version checks in pg_dump, so I figured the idea was to use more of those functions if the exist, like we do now. It is true that you can't modify them for old versions as easily as you can if they are hardcoded in pg_dump, but we our existing functions seems to work fine. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Maybe I am misunderstanding the TODO (which is entirely possible due to the complete lack of documentation on the feature) but I *thought* all I was going to do was create 6 functions that could be called to get various useful information? For example, pg_get_tabledef() would be a very handy function to use for just about any abstracted API. As it stands now most (like Pear) create their own custom queries/functions to handle it but they are more often then not very innefficient. I thought the TODO item was exactly what you described: * %Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef() We have per-server-version checks in pg_dump, so I figured the idea was to use more of those functions if the exist, like we do now. It is true that you can't modify them for old versions as easily as you can if they are hardcoded in pg_dump, but we our existing functions seems to work fine. O.k. so now what I am getting from this thread is, the functions exist now in pg_dump but we want to pull them out of pg_dump and push them into the backend? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Joshua D. Drake wrote: Maybe I am misunderstanding the TODO (which is entirely possible due to the complete lack of documentation on the feature) but I *thought* all I was going to do was create 6 functions that could be called to get various useful information? For example, pg_get_tabledef() would be a very handy function to use for just about any abstracted API. As it stands now most (like Pear) create their own custom queries/functions to handle it but they are more often then not very innefficient. I thought the TODO item was exactly what you described: * %Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef() We have per-server-version checks in pg_dump, so I figured the idea was to use more of those functions if the exist, like we do now. It is true that you can't modify them for old versions as easily as you can if they are hardcoded in pg_dump, but we our existing functions seems to work fine. O.k. so now what I am getting from this thread is, the functions exist now in pg_dump but we want to pull them out of pg_dump and push them into the backend? That's what I thought we wanted. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Joshua D. Drake [EMAIL PROTECTED] writes: O.k. so now what I am getting from this thread is, the functions exist now in pg_dump but we want to pull them out of pg_dump and push them into the backend? That's exactly what I *don't* want to do. If you can think of a use-case for these functions outside of pg_dump, feel free to put them in the backend, but pg_dump should continue to do things as it does now. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: O.k. so now what I am getting from this thread is, the functions exist now in pg_dump but we want to pull them out of pg_dump and push them into the backend? That's exactly what I *don't* want to do. If you can think of a use-case for these functions outside of pg_dump, feel free to put them in the backend, but pg_dump should continue to do things as it does now. O.k. well my thought was just to implement the functions for the backend. I wasn't even aware of the pg_dump dependency. They would be very useful for application developers in general. So how about this. I can implement them and submit them for hopeful inclusion and I will let hackers argue about whether or not they need to also be in pg_dump ;). If we can go down this route, can we go back to my original post so that I insure that I develop something that you guys want? Secondly, is this something that I can do with SQL and SETOF or do you want them in C? *** I can guess some of these: pg_get_tabledef() : Would take a table name and return the columns and associated types pg_get_acldef(): Would take an object name and return the associated roles and permissions for the object pg_get_typedefault(): This one I am unsure about pg_get_attrdef(): This one I am unsure about pg_get_domaindef(): Would take the name of a domain constraint and return the definition pg_get_functionef(): Would take the name of a function and return its soure. However, a function can have the same name with different arguments, so I am a little unsure? So could I get some further definition? *** Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: 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] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: O.k. so now what I am getting from this thread is, the functions exist now in pg_dump but we want to pull them out of pg_dump and push them into the backend? That's exactly what I *don't* want to do. If you can think of a use-case for these functions outside of pg_dump, feel free to put them in the backend, but pg_dump should continue to do things as it does now. Oh, OK, I guess. pg_dump already uses some of those functions so I figured it should use more, but you work in that area more than I do. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Ranges for well-ordered types
On Jun 11, 2006, at 5:15 , Bruno Wolff III wrote: I think you might want to reconsider your design. It works well for dates because sets of dates are made of of isolated points and such sets are both open and closed. If you are using time, I think it will be more convenient to use a closed, open representation. Under design I proposed, closed-closed and closed-open are just two different representations of the same range: to the commonly used notation, the closed-open range [p1, p2) is equivalent to the closed- closed range [p1, next(p2)], where next() is the successor function. I agree than depending on the context, it may be better to use one representation than the other (a budget meeting that lasts from 10:00 until 11:00 meets but doesn't share any points with an early lunch meeting that starts at 11:00). Perhaps there should be probably some to_char functions to format the range in the desired form. Time (and timestamp) is a bit of a issue conceptually. The default successor function would depend on the precision of the timestamp. timestamp(0) would have a successor function of + 1 second, while timestamp(3) would have a successor function of + .001 second. In the above example, Monday's budget meeting in Tokyo from 10:00 until 11:00 could be represented with ranges of timestamp(0) with time zone as [2006-06-12 10:00:00+09, 2006-06-12 11:00:00+09) or as [2006-06-12 10:00:00+09, 2006-06-12 10:59:59+09] With timestamp(3) with time zone, that'd be [2006-06-12 10:00:00.000+09, 2006-06-12 11:00:00.000+09) or as [2006-06-12 10:00:00.000+09, 2006-06-12 10:59:59.999+09] Most people would be more comfortable with the first representation of each pair, but the two representations in each pair represent the same range. For a lot of scheduling applications, using timestamps with a precision greater that 0 probably wouldn't be very useful (and when not using integer datetimes, not all that exact). Indeed, some scheduling applications may want a precision of 1 minute, rather than 1 second, or perhaps a precision of 15 minutes, or even an hour. I see this as a limitation of the timestamp type, and perhaps a workaround could be found using check constraints and more sophisticated successor functions. For example, a first cut of a successor function for a timestamp with precision of 1 hour might use + 3600 seconds, but the difference in seconds between the top of any two hours may not necessarily be 3600 seconds in some corner cases when the calendar has changed. In those cases, the successor function would need to be sure to return the next hour, rather than the previous hour + 3600 seconds. (Perhaps the calendar has never made a change where this would be a problem, but for some arbitrary timestamp precision, for example 1 day, this could be true. I haven't done enough research yet to determine how much of a problem this is. In those cases it might be better to use dates than timestamps.) With time zones and daylight saving time, this becomes even more interesting, especially for time zone offsets that aren't integral hours (e.g., South Australia Standard Time +9:30, Iran Time +3:30, India Time +5:30). A 1 hour precision requirement would need to include the applicable time zone. There's been previous discussion of including such time zone information in the timestamp value, but as far as I know, no work has been done in that direction yet. These are interesting questions, and improvements in timestamp can make ranges even more convenient. I still see utility in ranges using the current timestamp implementation as well. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
On Sat, Jun 10, 2006 at 07:33:54PM -0400, Bruce Momjian wrote: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: O.k. so now what I am getting from this thread is, the functions exist now in pg_dump but we want to pull them out of pg_dump and push them into the backend? That's exactly what I *don't* want to do. If you can think of a use-case for these functions outside of pg_dump, feel free to put them in the backend, but pg_dump should continue to do things as it does now. Oh, OK, I guess. pg_dump already uses some of those functions so I figured it should use more, but you work in that area more than I do. Well, the argument against changing pg_dump is that it would impact the ability to use the newer version of pg_dump with older backends (which would be lacking these functions). ISTM what would be best is to add the functions to the backend, and add a TODO or comments to pg_dump indicating that it should be changed to use these functions once 8.1 is no longer supported. Or you could make pg_dump's use of this code dependent on the server version it connected to. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Ranges for well-ordered types
On Sun, Jun 11, 2006 at 10:18:11AM +0900, Michael Glaesemann wrote: On Jun 11, 2006, at 5:15 , Bruno Wolff III wrote: I think you might want to reconsider your design. It works well for dates because sets of dates are made of of isolated points and such sets are both open and closed. If you are using time, I think it will be more convenient to use a closed, open representation. Under design I proposed, closed-closed and closed-open are just two different representations of the same range: to the commonly used notation, the closed-open range [p1, p2) is equivalent to the closed- closed range [p1, next(p2)], where next() is the successor function. Why try messing aronud with a successor function when you can just use instead of = ? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane said: Joshua D. Drake [EMAIL PROTECTED] writes: O.k. so now what I am getting from this thread is, the functions exist now in pg_dump but we want to pull them out of pg_dump and push them into the backend? That's exactly what I *don't* want to do. If you can think of a use-case for these functions outside of pg_dump, feel free to put them in the backend, but pg_dump should continue to do things as it does now. ISTR we debated this some time ago and decided that it wasn't a good idea for pg_dump. I certainly agree with Tom about it. But I think there is almost certainly a good use case for these apart from pg_dump. I recall many years ago using IBMs QMF facility that would provide skeleton select for a table, and maybe it gave a create query too (it was about 20 years ago, so my memory is not perfect). I have sometimes wished we had such a thing for use in CP query construction. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Well, the argument against changing pg_dump is that it would impact the ability to use the newer version of pg_dump with older backends (which would be lacking these functions). ISTM what would be best is to add the functions to the backend, and add a TODO or comments to pg_dump indicating that it should be changed to use these functions once 8.1 is no longer supported. Or you could make pg_dump's use of this code dependent on the server version it connected to. Off list I was speaking with AndrewD and he said that he would expect that if we called pg_get_tabledef() it should return the CREATE statement for the table. With all due respect to Andrew, why? At least in my mind these functions really belong to app developers.. e.g; CREATE TABLE foo (id serial); SELECT pg_get_tabledef(foo) would return id, serial Not: CREATE TABLE foo (id serial); I mean, I can do either but I would like to get a clear definition of what we are looking for here. Maybe: pg_get_tabledef is the actual SQL and pg_get_tabledesc() is the column, datatype output? I guess I don't see the advantage of putting pg_dump -s -t in the backend. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Ranges for well-ordered types
On Sun, Jun 11, 2006 at 10:18:11 +0900, Michael Glaesemann [EMAIL PROTECTED] wrote: Time (and timestamp) is a bit of a issue conceptually. The default successor function would depend on the precision of the timestamp. And in the ideal case it doesn't exist. That is why I think a closed, open interval is a better way to go. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
