Re: [HACKERS] New feature request: FlashBack Query
On Saturday 17 February 2007 07:49, RPK wrote: > PostgreSQL, already a mature database, needs to have more options for > recovery as compared to proprietary databases. I just worked with Oracle's > FlashBack query feature in Oracle 9i and FlashBack Table feature in 10g. > > Future versions of PostgreSQL must have similar features which enable users > to bring Table(s) and/or Database(s) to a desired Time Stamp. Check out my proposal[1] for Temporal extensions. Ultimately, creating valid time and transaction time tables would be possible through my proposal. Please check it out. [1]http://archives.postgresql.org/pgsql-hackers/2007-02/msg00540.php wt -- Warren Turkal (w00t) ---(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] Plan invalidation design
Lukas Kahwe Smith <[EMAIL PROTECTED]> writes: > I remember that there was discussion about invalidating plans who's > estimated cost turn out to be severely off when executed. That's something we might think about after the infrastructure is in place. But the question to answer is why the re-plan won't yield just the same plan as before. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] New feature request: FlashBack Query
"Chad Wagner" <[EMAIL PROTECTED]> writes: > I am sure this topic has probably been beaten to death in the past, but has > anyone talked about the advantages of Oracle's MVCC model versus > PostgreSQL's MVCC model? Yes, we've been all through that. We like ours. See the archives. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)
On 2/17/07, Brendan Jurd <[EMAIL PROTECTED]> wrote:
I just looked through the Oracle documentation, and it is
conspicuously silent on the topic of invalid format patterns. Much
like ours in fact.
On the case of the format: -MM-DD J, if J is the same date as -MM-DD
then Oracle appears to silently ignore it. But if J is not the same date as
-MM-DD then Oracle throws an error:
SQL> select to_date('2007-02-17 2454149', '-MM-DD J') from dual;
TO_DATE('
-
17-FEB-07
SQL> select to_date('2007-02-17 2454145', '-MM-DD J') from dual;
select to_date('2007-02-17 2454145', '-MM-DD J') from dual
*
ERROR at line 1:
ORA-01834: day of month conflicts with Julian date
I like your suggestion of the pattern modifier. So if a user did try
to format with '-MM-DD "Q"Q', we would throw an error telling them
that the pattern is over-constraining, and they can use this pattern
modifier (* or whatever) to single out the non-normative fields.
As for 'Q', Oracle doesn't appear to support this in terms of TO_DATE, I
believe it is for TO_CHAR only.
SQL> select to_date('2007-02-17 1', '-MM-DD Q') from dual;
select to_date('2007-02-17 1', '-MM-DD Q') from dual
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format
SQL> select to_date('1', 'Q') from dual;
select to_date('1', 'Q') from dual
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format
--
Chad
http://www.postgresqlforums.com/
Re: [HACKERS] New feature request: FlashBack Query
On 2/17/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: My understanding is that the main difference is that rollbacks are inexpensive for us, but expensive for Oracle. Talk to an Oracle DBA about their Rollback logs :0. Yes, I have seen cases where undo segments are thrashed. Generally it works well, and I agree it likely much cheaper on PostgreSQL as you would expect fewer scattered reads because the old version is inline with the rest of the data. But if I recall undo segments are cached in Oracle, usually where I see problems is where the DBA is completely incompetent and has undersized the buffer cache. Oracle does direct reads (afaik) -- so undersizing the buffer cache can be brutal. A very common mistake with Oracle was undersizing the buffer cache and oversizing the shared pool (when the shared plans are stored), and with 9i and later they tried to have the management tools suggest the ideal values or have it automatically managed by the database. Probably a step in the right direction, but I know they still have bumps to iron it. :) However, they don't have vacuum, we do. Right, and I think that is more or less because Oracle doesn't need it. Vacuum's main purpose (correct me if I am wrong) is to recover/mark rows that are no longer used, and Oracle essentially reuses the space immediately. Obviously with Oracle if you bloat out a table and delete a ton of rows then you have to rebuild the table, but that is more or less the same problem that PostgreSQL has and where vacuum full comes into play. The only benefit with the Oracle model is that you can achieve flashback, which is a very rarely used feature in my book. The disadvantages is likely overhead to perform the "rollback" and possibly more scattered reads. I can say that I have used it, and it has come in handy, but hardly worth it. The benefit with the PostgreSQL model is the likelihood of the old rows being inline with the rest of the table data, potentially reducing scattered reads. The disadvantage is vacuuming, it seems to be often overlooked -- possibly solved by defaulting autovacuum to on? (seems to be the way Oracle is heading, defaulting statistics collection to on and other management features).
Re: [HACKERS] Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)
On 2/17/07, Martijn van Oosterhout wrote: On Sat, Feb 17, 2007 at 02:41:32PM +1100, Brendan Jurd wrote: > My gut reaction at first was to go with the former approach. It's > programmatically more simple, and it's easier to explain in > documentation/error messages. But then it occurred to me that one of > the use cases for to_date is slurping date information out of textual > reports which may contain redundant date information. If a user > wanted to parse something like "2007-02-17 Q1", he would probably try > '-MM-DD "Q"Q', even though this pattern is logically > over-constraining. Would it be fair to throw an error in such a case? If that's the use case, it would seem to me reasonable to be able to mark fields for parsing but to not use them in the final calculation, like the * modifier for scanf in C. Other than that I'd follow whatever Oracle does, that seem to be the trend with those functions. I just looked through the Oracle documentation, and it is conspicuously silent on the topic of invalid format patterns. Much like ours in fact. I like your suggestion of the pattern modifier. So if a user did try to format with '-MM-DD "Q"Q', we would throw an error telling them that the pattern is over-constraining, and they can use this pattern modifier (* or whatever) to single out the non-normative fields. Anybody else want to weigh in on this? ---(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] New feature request: FlashBack Query
Chad Wagner wrote: > On 2/17/07, elein <[EMAIL PROTECTED]> wrote: >> >> For other recent time travel ideas see: >> http://www.varlena.com/GeneralBits/122.php >> Time travel is not cheap, though. >> > > > I am sure this topic has probably been beaten to death in the past, but has > anyone talked about the advantages of Oracle's MVCC model versus > PostgreSQL's MVCC model? Oracle achieves multiversioning by using > rollback/undo segments, where PostgreSQL appears to place (essentially) the > undo in the same space as the table. My understanding is that the main difference is that rollbacks are inexpensive for us, but expensive for Oracle. Talk to an Oracle DBA about their Rollback logs :0. However, they don't have vacuum, we do. Joshua D. Drake > > If I were to guess this is probably a major thing to change. Clearly there > are advantages to both, with Oracle essentially the space consumed by a > modified row is immediately available for reuse and generally there is > little row migration assuming there is enough space on the block so you > should be able to avoid updates to the index and the bloating that seems to > go along with vacuuming. > > Is there any previous discussions that folks could point out here? > -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Re: [GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > I looked into this more and I think I'm afraid the proposed solution > > actually does not work for SQL functions. For example, > > > CREATE OR REPLACE FUNCTION foo(INTEGER, INTEGER) RETURNS INTEGER AS $$ > > SET search_path To pg_catalog,public; > > SELECT mod($1,$2); > > $$ LANGUAGE sql SECURITY DEFINER; > > > If an attacker creates public.mod() to do something bad and override > > his search_path to public,pg_catalog before executing foo(), his > > attack will succeed since calling to mod() is resolved in the plan > > time thus it will be resolved to public.mod, rather than > > pg_catalog.mod. > > True, because the SQL-function code runs parse analysis for the whole > function body before executing any of it. We could fix it by doing > parse-analyze/plan/execute one statement at a time, which would make > SQL functions work more like multi-statement strings submitted by a > client application. Just a day or two ago there was someone complaining > that they couldn't create and use a temp table in the same SQL function, > due to this same behavior; and I recall similar gripes in the past. > Maybe it's time to change it. > > regards, tom lane Ok. So bottom line would be "do not use SECURITY DEFINER SQL functions unless you make every object including functions and operators into schema-qualified one". -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Plan invalidation design
Tom Lane wrote: Relcache inval casts a fairly wide net; for example, adding or dropping an index will invalidate all plans using the index's table whether or not they used that particular index, and I believe that VACUUM will also result in a relcache inval due to updating the table's pg_class row. I think this is a good thing though --- for instance, after adding an index it seems a good idea to replan to see if the new index is useful, and replanning after a VACUUM is useful if the table has changed size enough to warrant a different plan. OTOH this might mean that plans on a high-update-traffic table never survive very long because of autovacuum's efforts. If that proves to be a problem in practice we can look at ways to dial down the number of replans, but for the moment I think it's more important to be sure we *can* replan at need than to find ways to avoid replans. I remember that there was discussion about invalidating plans who's estimated cost turn out to be severely off when executed. That is probably a more reliable metric (than invalidating with every VACCUM - unless of course the amount of changed rows is considered), though it will probably put a fixed overhead on all relevant queries. So it might not be feasible. Of course this checking after a query runs longer than expected also means that at least one execution will in fact have to run slow instead of preempting this from happening at all. Also while not directly related it might be thing to keep in mind. It would also be cool to support multiple plans for different sets of parameters, since obviously the data distribution and therefore the optimal plan will potentially vary greatly with different parameters. regards, Lukas PS: I moved "Plan invalidation" to confirmed on the wishlist .. ---(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] Chatter on DROP SOMETHING IF EXISTS
Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > On Thu, Feb 08, 2007 at 01:54:13PM -0500, Tom Lane wrote: > >> I would be satisfied if the returned command tag were something else, > >> maybe "NO OPERATION". > > > "TABLE blah DID NOT EXIST" might be less confusing... > > You're confusing a command tag with a notice. In the first place, > we shouldn't assume that applications are ready to deal with > indefinitely long command tags (the backend itself doesn't think they > can be longer than 64 bytes); in the second place, they should be > constant strings for the most part so that simple strcmp()s suffice > to see what happened. Command tags are meant for programs to deal > with, more than humans. Yep. Because IF EXISTS is in a lot of object destruction commands, adding a modified tag seems very confusing, because in fact the DROP TABLE did succeed, so to give any other tag seems incorrect. I think the only option would be to use INFO instead of NOTICE, but because the output is optional based on whether the object exists, you might say NOTICE is the right level. I am afraid we might just need to live with the current behavior. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Allow SQL functions to reference parameters by name
Done. * Allow SQL-language functions to reference parameters by parameter name Currently, SQL-language functions can only refer to parameters via $1, etc --- Jim Nasby wrote: > Can someone add this to the TODO? > [ Attachment, skipping... ] > > -- > Jim Nasby[EMAIL PROTECTED] > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://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] [COMMITTERS] pgsql: Add lock matrix to documentation.
Merlin Moncure wrote: > > > This needs some revisions. The table needs to be mentioned somewhere in > > > the > > > text, so the reader knows when or why to refer to it. Also, the cryptic > > > abbreviations need to be expanded or explained. And then the concept of > > > lock "compatibility", as the table puts it, is not used anywhere else in > > > the > > > documentation. The table should be put in terms of conflicts instead. > > > > > > > Another version with expanded abbreviations is > > http://mira.sai.msu.su/~megera/pgsql/lockmatrix/c2.html, if remove > > UPDATE EXCLUSIVE. > > > > While compatibility matrix is a commonly accepted termin, I agree, that > > using conficts would be better in context of our docs. > > How about changing 'current lock mode' to 'opposing lock mode'? > 'current' kind of suggests that you are escalating your own lock. Not sure how you can say requested and opposing --- they seems odd together. I am still open to new working though: http://momjian.us/main/writings/pgsql/sgml/explicit-locking.html#TABLE-LOCK-COMPATIBILITY -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://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] [COMMITTERS] pgsql: Add lock matrix to documentation.
Peter Eisentraut wrote: > Am Donnerstag, 8. Februar 2007 16:32 schrieb Bruce Momjian: > > Log Message: > > --- > > Add lock matrix to documentation. > > This needs some revisions. The table needs to be mentioned somewhere in the > text, so the reader knows when or why to refer to it. Also, the cryptic > abbreviations need to be expanded or explained. And then the concept of > lock "compatibility", as the table puts it, is not used anywhere else in the > documentation. The table should be put in terms of conflicts instead. Done. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Add lock matrix to documentation.
OK, I have updated "Conflicting lock modes" to show as conflicts, added "Current/Requested" headings, add linked to the table from text. Here is the updated version: http://momjian.us/main/writings/pgsql/sgml/explicit-locking.html#TABLE-LOCK-COMPATIBILITY --- Oleg Bartunov wrote: > On Fri, 9 Feb 2007, Peter Eisentraut wrote: > > > Am Donnerstag, 8. Februar 2007 16:32 schrieb Bruce Momjian: > >> Log Message: > >> --- > >> Add lock matrix to documentation. > > > > This needs some revisions. The table needs to be mentioned somewhere in the > > text, so the reader knows when or why to refer to it. Also, the cryptic > > abbreviations need to be expanded or explained. And then the concept of > > lock "compatibility", as the table puts it, is not used anywhere else in the > > documentation. The table should be put in terms of conflicts instead. > > > > Another version with expanded abbreviations is > http://mira.sai.msu.su/~megera/pgsql/lockmatrix/c2.html, if remove > UPDATE EXCLUSIVE. > > While compatibility matrix is a commonly accepted termin, I agree, that > using conficts would be better in context of our docs. > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://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] New feature request: FlashBack Query
On 2/17/07, elein <[EMAIL PROTECTED]> wrote: For other recent time travel ideas see: http://www.varlena.com/GeneralBits/122.php Time travel is not cheap, though. I am sure this topic has probably been beaten to death in the past, but has anyone talked about the advantages of Oracle's MVCC model versus PostgreSQL's MVCC model? Oracle achieves multiversioning by using rollback/undo segments, where PostgreSQL appears to place (essentially) the undo in the same space as the table. If I were to guess this is probably a major thing to change. Clearly there are advantages to both, with Oracle essentially the space consumed by a modified row is immediately available for reuse and generally there is little row migration assuming there is enough space on the block so you should be able to avoid updates to the index and the bloating that seems to go along with vacuuming. Is there any previous discussions that folks could point out here?
Re: [HACKERS] New feature request: FlashBack Query
On Sat, Feb 17, 2007 at 11:48:55AM -0500, Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > RPK wrote: > >> Future versions of PostgreSQL must have similar features which enable users > >> to bring Table(s) and/or Database(s) to a desired Time Stamp. > > > We can do it with databases, we can't do it with tables. Nor should we > > do it with tables as it would require that all tables in relation are > > also flashed backed. > > AFAICT this is a request to re-instate Time Travel, which is a feature > we removed more than ten years ago because the overhead was utterly > unacceptable. And the project's idea of acceptable performance then > was orders of magnitude weaker than it is now. So it's not going to > happen, at least not in the general release. You might take a look at > contrib/spi/README.timetravel, though, for a prototype of how something > similar can be achieved without any changes to the core system. That > module is a bit unmaintained and could doubtless do with some updates > --- for starters, it should be using timestamptz instead of the old > deprecated abstime. If you're interested, feel free to work on it. > No one else has taken an interest in a long time. > For other recent time travel ideas see: http://www.varlena.com/GeneralBits/122.php Time travel is not cheap, though. --elein [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GiST Comparing IndexTuples/Datums
"Matthew Campbell" <[EMAIL PROTECTED]> writes: > revisit hash to see if we can figure it out now that we understand a little > bit about GiST, but we can't find an equivelent function in hash for the > KeyIsEQ(). > So two questions really. The first is if such a function exists for > hash. hash indexes don't have any need to compare two entries so you're unlikely to find a bit of code that does exactly that, but the HTEqualStrategyNumber member of the index's operator class is the relevant equality operator, so it's surely possible to invoke it. You could just do that directly instead of bothering with scankeys. The only reason _bt_check_unique uses a scankey is that that's the information it's already got, because btree generates a scankey for use in searching the index for the correct insertion position. There's no comparable need in hash and hence no infrastructure for it. It'd go something like Oid cmp_op = indexrel->rd_operator[HTEqualStrategyNumber-1]; RegProcedure cmp_proc = get_opcode(cmp_op); if (!RegProcedureIsValid(cmp_proc)) elog... result = DatumGetBool(OidFunctionCall2(cmp_proc, datum1, datum2)); although you'd probably want to avoid looking up the function again for each index entry, so plan on an fmgr_info call in there. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] RFC: Temporal Extensions for PostgreSQL
On Sat, Feb 17, 2007 at 11:40:44AM -0700, Warren Turkal wrote: > On Saturday 17 February 2007 09:26, Tom Lane wrote: > > "Overlapping" is not an equality relation (it fails the transitive law), > > so I'm not entirely sure what "unique" means in this context ... but I > > can promise you you can't make it work with btree. > > There is an equality relation on periods. But it wouldn't really tell you > much > useful info, as it's not normally what you're looking for with time. What he's referring to is that "overlaps" is not transitive. i.e. if A overlaps B and B overlaps C then A doesn't necessarily overlap C. However, non-overlapping intervals are stricly ordered, so if you reject overlaps from the index then new intervals can each only be inserted into one place. However, the locking required is probably non-trivial. Get unique indexes for GiST working and you're home... Have a nice day, -- Martijn van Oosterhout 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] pg_restore fails with a custom backup file
Tom Lane wrote: > Magnus Hagander <[EMAIL PROTECTED]> writes: >> I'd also like a comment from at least one other "patch reviewer" that >> the methods used are good. > > It looks reasonable as far as it goes. One thought is that pg_dump Ok. I'll run some more tests and then get it in. > really should have noticed that it was writing a broken archive. > On machines where off_t is 32 bits, can't we detect the overflow > situation? IIRC, there was a warning from pg_dump. I don't recall exactly what, and don't have the space to re-run the test on my laptop here, but I think it was from: write_msg(modulename, "WARNING: ftell mismatch with expected position -- ftell used\n"); //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] GiST Comparing IndexTuples/Datums
Good news: I think we've got GiST working (somewhat anyways), as we found gistKeyIsEQ(giststate, 0, datum, currdatum) in gistutil.c does the trick of comparing two datums. I swear most of our trouble is just finding our way around the postgres codebase, but we're getting there little by little. We've gone back to revisit hash to see if we can figure it out now that we understand a little bit about GiST, but we can't find an equivelent function in hash for the KeyIsEQ(). So two questions really. The first is if such a function exists for hash. The second is that nbtree and Neil Conways work a few years ago ( http://archives.postgresql.org/pgsql-patches/2003-09/msg00252.php) use the scan and scankey stuff, but we're having trouble understanding how these work. Is there some documentation on using these correctly (outside of just looking at nbtree code)? Thanks so much for the help folks! -Matt On 2/13/07, Teodor Sigaev <[EMAIL PROTECTED]> wrote: > indexes, then it must use operator number so-and-so for equality. But > there are lots of GiST opclasses that don't include equality at all; we > can't break that case. There is a GiST support function for equality of keys, in btree_gist it's named as gbt_*_same. Equality function has support number 7 and is used for stored keys. But the real issue in unique GiST index is unique :). First, the algorithm of insertion doesn't compare indexed keys on leaf page at all. Values on the same page are compared only when page is splitting (picksplit support method). Second, GiST implementation supports only unordered trees (btree_gist is a some kind of emulation) and it cannot guarantee that equal keys will be close in index. That's related to picksplit and gistpenalty method problem/optimization and data set. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/
Re: [HACKERS] RFC: Temporal Extensions for PostgreSQL
On Saturday 17 February 2007 09:26, Tom Lane wrote: > "Overlapping" is not an equality relation (it fails the transitive law), > so I'm not entirely sure what "unique" means in this context ... but I > can promise you you can't make it work with btree. There is an equality relation on periods. But it wouldn't really tell you much useful info, as it's not normally what you're looking for with time. wt -- Warren Turkal (w00t) ---(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] RFC: Temporal Extensions for PostgreSQL
On Saturday 17 February 2007 01:50, Hannu Krosing wrote: > Is tinterval meant to be open/closed at start and end ? I don't see the tinterval doing anything other than storing two times. wt -- Warren Turkal (w00t) ---(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] pg_restore fails with a custom backup file
Magnus Hagander <[EMAIL PROTECTED]> writes: > I'd also like a comment from at least one other "patch reviewer" that > the methods used are good. It looks reasonable as far as it goes. One thought is that pg_dump really should have noticed that it was writing a broken archive. On machines where off_t is 32 bits, can't we detect the overflow situation? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > I looked into this more and I think I'm afraid the proposed solution > actually does not work for SQL functions. For example, > CREATE OR REPLACE FUNCTION foo(INTEGER, INTEGER) RETURNS INTEGER AS $$ > SET search_path To pg_catalog,public; > SELECT mod($1,$2); > $$ LANGUAGE sql SECURITY DEFINER; > If an attacker creates public.mod() to do something bad and override > his search_path to public,pg_catalog before executing foo(), his > attack will succeed since calling to mod() is resolved in the plan > time thus it will be resolved to public.mod, rather than > pg_catalog.mod. True, because the SQL-function code runs parse analysis for the whole function body before executing any of it. We could fix it by doing parse-analyze/plan/execute one statement at a time, which would make SQL functions work more like multi-statement strings submitted by a client application. Just a day or two ago there was someone complaining that they couldn't create and use a temp table in the same SQL function, due to this same behavior; and I recall similar gripes in the past. Maybe it's time to change it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] wishlist items ..
On 2/17/07, Lukas Kahwe Smith <[EMAIL PROTECTED]> wrote: I have emailed Gregory, Pavan and Simon only 2 days ago, so I am not suprised to not haven gotten feedback yet. Oops, I haven't received the email you mentioned ? Can you resend me the same ? Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
[HACKERS] Plan invalidation design
I'm starting to think about the long-wanted plan invalidation mechanism. Here's a sketch --- anyone see any problems? * Create a new module, say src/backend/utils/cache/plancache.c, that we will put in charge of all long-lived plans --- or at least those cached by PREPARE, plpgsql, and RI triggers. I'm unsure whether we should make all SPI plans work this way or not; it's possible that doing so would change SPI's API behavior enough to break user-written code. Any thoughts on that? * plancache.c will have two basic functions: 1. Given a query's raw parse tree (that is, the raw output of gram.y), analyze and plan the query. Store both the parse tree and plan in a backend-local cache table, and return a handle for the table entry as well as the plan tree. 2. Given a handle for a previously stored query, check to see if the plan is still up to date; if not, regenerate it from the raw parse tree (note this could result in failure, eg if a column used by the query has been dropped). Then return the plan tree. We probably want to return a direct pointer to the cached plan tree instead of making a copy. This should be safe, because the executor now treats plan trees as read-only, but it does mean that when plan invalidation occurs the cached plan tree might still be in use. We'll probably need to have a notion of a reference count: so the two functions above would increment the plan's refcount and there would be a third "ReleasePlanCache" function to call when done using a plan (and, hence, these references would need to be supported by the ResourceManager mechanism). Note that the source object for caching is a raw parse tree. This should work since we already require that gram.y not look into the database during its processing; therefore, the raw tree need never be invalidated. It'd be conceptually simpler if we passed in a query string instead, but I don't think that works for PREPARE, because it might be embedded in a multi-command string. (We do probably want to pass in the original query string too, if available, because it's needed for syntax error reporting.) nodes/copyfuncs.c will need some expansion, as I don't believe it has coverage for all raw-parse-tree node types. Invalidation will be detected by having plancache.c watch for relcache invalidation events, using the existing inval.c callback mechanism. On any relcache inval, traverse the plan cache looking for plans that mention the invalidated relation in their rangetables, and mark them as needing to be regenerated before next use. (If they currently have refcount zero, we could delete the plan part of the cache entry immediately.) Relcache inval casts a fairly wide net; for example, adding or dropping an index will invalidate all plans using the index's table whether or not they used that particular index, and I believe that VACUUM will also result in a relcache inval due to updating the table's pg_class row. I think this is a good thing though --- for instance, after adding an index it seems a good idea to replan to see if the new index is useful, and replanning after a VACUUM is useful if the table has changed size enough to warrant a different plan. OTOH this might mean that plans on a high-update-traffic table never survive very long because of autovacuum's efforts. If that proves to be a problem in practice we can look at ways to dial down the number of replans, but for the moment I think it's more important to be sure we *can* replan at need than to find ways to avoid replans. Note that I'm currently intending to detect only relcache invals, not changes to functions or operators used in the plan. (Relcache inval will cover view redefinitions, though.) We could extend it to handle that later, but it looks like a lot more mechanism and overhead for not a lot of gain. AFAICS there are only three cases where there'd be a benefit: * if you redefine an immutable function, any places where its result has been pre-computed by constant-folding wouldn't get updated without inval. * if you have a SQL function that's been inlined into a plan, a change in the function definition wouldn't get reflected into the plan without inval. * if you alter a function and change its volatility property, that might possibly affect the shape of plans that use the function (for instance some optimization transformation might now be allowed or not). To my memory none of these problems have been complained of from the field. Making the cache module able to detect function-related invalidations would be a bit of work --- for example, if a function has been inlined, there is no recognizable reference to it at all in the plan tree, so we'd have to modify the planner to track such things and report them somehow. (The corresponding problem for views doesn't exist, because there is still a rangetable entry for a view after it's been expanded.) So I think this is a "maybe do someday" part, not something to do in the first release. One interest
Re: [HACKERS] New feature request: FlashBack Query
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > RPK wrote: >> Future versions of PostgreSQL must have similar features which enable users >> to bring Table(s) and/or Database(s) to a desired Time Stamp. > We can do it with databases, we can't do it with tables. Nor should we > do it with tables as it would require that all tables in relation are > also flashed backed. AFAICT this is a request to re-instate Time Travel, which is a feature we removed more than ten years ago because the overhead was utterly unacceptable. And the project's idea of acceptable performance then was orders of magnitude weaker than it is now. So it's not going to happen, at least not in the general release. You might take a look at contrib/spi/README.timetravel, though, for a prototype of how something similar can be achieved without any changes to the core system. That module is a bit unmaintained and could doubtless do with some updates --- for starters, it should be using timestamptz instead of the old deprecated abstime. If you're interested, feel free to work on it. No one else has taken an interest in a long time. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] RFC: Temporal Extensions for PostgreSQL
Hannu Krosing <[EMAIL PROTECTED]> writes: > How easy/hard would it be to create unique indexes on tinterval (unique > here meaning non-overlapping) ? "Overlapping" is not an equality relation (it fails the transitive law), so I'm not entirely sure what "unique" means in this context ... but I can promise you you can't make it work with btree. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] New feature request: FlashBack Query
RPK wrote: > PostgreSQL, already a mature database, needs to have more options for > recovery as compared to proprietary databases. I just worked with Oracle's > FlashBack query feature in Oracle 9i and FlashBack Table feature in 10g. > > Future versions of PostgreSQL must have similar features which enable users > to bring Table(s) and/or Database(s) to a desired Time Stamp. We can do it with databases, we can't do it with tables. Nor should we do it with tables as it would require that all tables in relation are also flashed backed. 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] wishlist items ..
I find this very helpful. My head is full of patches and I rarely get to look at things from this angle. --- Lukas Kahwe Smith wrote: > Lukas Kahwe Smith wrote: > > > I just wanted to bring up the wishlist todo items: > > http://developer.postgresql.org/index.php/Todo:WishlistFor83 > > I have gotten feedback from most items on the list and I have updated > the list accordingly. Maybe a few of the items can even be moved to > "Completed" already. > > Obviously all items under "Perhabs" could use some helping hands. Also > note that I have placed "Plan invalidation" under "Perhabs" since Heikki > said he is giving his other items priority and I have not gotten > feedback from Tom. > > Also note that Alvaro could use some help for "autovac" even though he > is still quite hopeful to make it on time. > > Finally Pavel is looking for feedback and testing for the SQL/PSM support. > > The 4 items I have not gotten feedback on are as follows: > * WITH RECURSIVE hierarchical queries (Gregory Stark) > * Better handling of partitioning > * Hot Updates (Pavan) > * Reuse of index tuples (Simon Riggs) > > I have emailed Gregory, Pavan and Simon only 2 days ago, so I am not > suprised to not haven gotten feedback yet. > > Overall it looks quite well! > > regards, > Lukas > > PS: Let me know if you feel I could improve this "service" in any way. > > ---(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 -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://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
[HACKERS] New feature request: FlashBack Query
PostgreSQL, already a mature database, needs to have more options for recovery as compared to proprietary databases. I just worked with Oracle's FlashBack query feature in Oracle 9i and FlashBack Table feature in 10g. Future versions of PostgreSQL must have similar features which enable users to bring Table(s) and/or Database(s) to a desired Time Stamp. -- View this message in context: http://www.nabble.com/New-feature-request%3A-FlashBack-Query-tf3245023.html#a9020502 Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)
On Sat, Feb 17, 2007 at 02:41:32PM +1100, Brendan Jurd wrote: > My gut reaction at first was to go with the former approach. It's > programmatically more simple, and it's easier to explain in > documentation/error messages. But then it occurred to me that one of > the use cases for to_date is slurping date information out of textual > reports which may contain redundant date information. If a user > wanted to parse something like "2007-02-17 Q1", he would probably try > '-MM-DD "Q"Q', even though this pattern is logically > over-constraining. Would it be fair to throw an error in such a case? If that's the use case, it would seem to me reasonable to be able to mark fields for parsing but to not use them in the final calculation, like the * modifier for scanf in C. Other than that I'd follow whatever Oracle does, that seem to be the trend with those functions. Have a nice day, -- Martijn van Oosterhout 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] wishlist items ..
Lukas Kahwe Smith wrote: I just wanted to bring up the wishlist todo items: http://developer.postgresql.org/index.php/Todo:WishlistFor83 I have gotten feedback from most items on the list and I have updated the list accordingly. Maybe a few of the items can even be moved to "Completed" already. Obviously all items under "Perhabs" could use some helping hands. Also note that I have placed "Plan invalidation" under "Perhabs" since Heikki said he is giving his other items priority and I have not gotten feedback from Tom. Also note that Alvaro could use some help for "autovac" even though he is still quite hopeful to make it on time. Finally Pavel is looking for feedback and testing for the SQL/PSM support. The 4 items I have not gotten feedback on are as follows: * WITH RECURSIVE hierarchical queries (Gregory Stark) * Better handling of partitioning * Hot Updates (Pavan) * Reuse of index tuples (Simon Riggs) I have emailed Gregory, Pavan and Simon only 2 days ago, so I am not suprised to not haven gotten feedback yet. Overall it looks quite well! regards, Lukas PS: Let me know if you feel I could improve this "service" in any way. ---(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] pg_restore fails with a custom backup file
Yoshiyuki Asaba wrote: > From: Magnus Hagander <[EMAIL PROTECTED]> > Subject: Re: [HACKERS] pg_restore fails with a custom backup file > Date: Fri, 16 Feb 2007 10:13:35 +0100 > >> On Fri, Feb 16, 2007 at 02:09:41PM +0900, Yoshiyuki Asaba wrote: >> > Does not compile on my MinGW - errors in the system headers (unistd.h, > io.h) due to changing the argument format for chsize(). The change of > off_t propagated into parts of the system headers, thus chaos was > ensured. > > I still think we need to use a pgoff_t. Will look at combining these two > approaches. Here's a patch that tries this. *needs more testing*. But built with this patch, I can dump and restore a table at the end of a 10gb database without errors. >>> I tried the attached patch. But I got the following error. >>> >>> pg_backup_archiver.o(.text+0x1fa4): In function `allocAH': >>> C:/msys/1.0/home/y-asaba/postgresql-8.2.3-patch/src/bin/pg_dump/pg_backup_archiver.c:1580: >>> undefined reference to `fseeko64' >>> ... >>> make[3]: *** [pg_dump] Error 1 >>> >>> $ uname -sr >>> MINGW32_NT-5.1 1.0.10(0.46/3/2) >>> >>> Is MINGW version too old? >> I think so. It seems this was added in version 1.24 of stdio.h in mingw >> (http://cygwin.com/cgi-bin/cvsweb.cgi/src/winsup/mingw/include/stdio.h?cvsroot=src). >> Could you try upgrading mingw and see if that helps? Or possibly >> instlaling side-by-side a different version (if they even allow that)? > > OK. I have upgraded mingw and tried to compile. regression tests > passed. So I tested pg_restore on Windows and Linux. > > $ createdb test > $ pgbench -i -s 1000 test > $ pg_dump -Fc test > out > $ createdb restore > $ pg_restore -d restore out > $ psql -c 'select max(aid) from accounts' restore > max > --- >1 > (1 row) > > pg_restore was normally completed. Thank you for your great work. I wish > that the patch will be committed. Thanks for running those tests. I need to test the msvc build as well, but I can hopefully do that quickly. (and a few tests that I didn't break unix) I'd also like a comment from at least one other "patch reviewer" that the methods used are good. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_restore fails with a custom backup file
From: Magnus Hagander <[EMAIL PROTECTED]> Subject: Re: [HACKERS] pg_restore fails with a custom backup file Date: Fri, 16 Feb 2007 10:13:35 +0100 > On Fri, Feb 16, 2007 at 02:09:41PM +0900, Yoshiyuki Asaba wrote: > > > > > Does not compile on my MinGW - errors in the system headers (unistd.h, > > > > io.h) due to changing the argument format for chsize(). The change of > > > > off_t propagated into parts of the system headers, thus chaos was > > > > ensured. > > > > > > > > I still think we need to use a pgoff_t. Will look at combining these two > > > > approaches. > > > > > > Here's a patch that tries this. > > > *needs more testing*. But built with this patch, I can dump and > > > restore a table at the end of a 10gb database without errors. > > > > I tried the attached patch. But I got the following error. > > > > pg_backup_archiver.o(.text+0x1fa4): In function `allocAH': > > C:/msys/1.0/home/y-asaba/postgresql-8.2.3-patch/src/bin/pg_dump/pg_backup_archiver.c:1580: > > undefined reference to `fseeko64' > > ... > > make[3]: *** [pg_dump] Error 1 > > > > $ uname -sr > > MINGW32_NT-5.1 1.0.10(0.46/3/2) > > > > Is MINGW version too old? > > I think so. It seems this was added in version 1.24 of stdio.h in mingw > (http://cygwin.com/cgi-bin/cvsweb.cgi/src/winsup/mingw/include/stdio.h?cvsroot=src). > Could you try upgrading mingw and see if that helps? Or possibly > instlaling side-by-side a different version (if they even allow that)? OK. I have upgraded mingw and tried to compile. regression tests passed. So I tested pg_restore on Windows and Linux. $ createdb test $ pgbench -i -s 1000 test $ pg_dump -Fc test > out $ createdb restore $ pg_restore -d restore out $ psql -c 'select max(aid) from accounts' restore max --- 1 (1 row) pg_restore was normally completed. Thank you for your great work. I wish that the patch will be committed. -- Yoshiyuki Asaba [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Re: [GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions
> > It has come to the attention of the core team of the PostgreSQL project > > that insecure programming practice is widespread in SECURITY DEFINER > > functions. Many of these functions are exploitable in that they allow > > users that have the privilege to execute such a function to execute > > arbitrary code with the privileges of the owner of the function. > > > > The SECURITY DEFINER property of functions is a special non-default > > property that causes such functions to be executed with the privileges > > of their owner rather than with the privileges of the user invoking the > > function (the default mode, SECURITY INVOKER). Thus, this mechanism is > > very similar to the "setuid" mechanism in Unix operating systems. > > > > Because SQL object references in function code are resolved at run time, > > any references to SQL objects that are not schema qualified are > > resolved using the schema search path of the session at run time, which > > is under the control of the calling user. By installing functions or > > operators with appropriate signatures in other schemas, users can then > > redirect any function or operator call in the function code to > > implementations of their choice, which, in case of SECURITY DEFINER > > functions, will still be executed with the function owner privileges. > > Note that even seemingly innocent invocations of arithmetic operators > > are affected by this issue, so it is likely that a large fraction of > > all existing functions are exploitable. > > > > The proper fix for this problem is to insert explicit SET search_path > > commands into each affected function to produce a known safe schema > > search path. Note that using the default search path, which includes a > > reference to the "$user" schema, is not safe when unqualified > > references are intended to be found in the "public" schema and "$user" > > schemas exist or can be created by other users. It is also not > > recommended to rely on rigorously schema-qualifying all function and > > operator invocations in function source texts, as such measures are > > likely to induce mistakes and will furthermore make the source code > > harder to read and maintain. > > But if we insert a set schema search_path command in an SQL function, > the caller will be affected by it. Doing reset search_path before > returning to caller might solve some of problems, but it will not > recover caller's special search_path. How do you solve the problem? I looked into this more and I think I'm afraid the proposed solution > The proper fix for this problem is to insert explicit SET search_path > commands into each affected function to produce a known safe schema > search path. actually does not work for SQL functions. For example, CREATE OR REPLACE FUNCTION foo(INTEGER, INTEGER) RETURNS INTEGER AS $$ SET search_path To pg_catalog,public; SELECT mod($1,$2); $$ LANGUAGE sql SECURITY DEFINER; If an attacker creates public.mod() to do something bad and override his search_path to public,pg_catalog before executing foo(), his attack will succeed since calling to mod() is resolved in the plan time thus it will be resolved to public.mod, rather than pg_catalog.mod. Am I missing something? -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] n-gram search function
Thanks. I'll look into this. -- Tatsuo Ishii SRA OSS, Inc. Japan > 3-gram is implemented as a contrib/pg_trgm. It currently uses GiST index, > but may be enhanced with the GiN. > > Oleg > > On Sat, 17 Feb 2007, Tatsuo Ishii wrote: > > > Hi, > > > > Is anybody working on implementing n-gram search functionality for > > text type data? tsearch2 is great for long text but it's not > > appropreate for short (10-100 bytes) text data. What I want to achieve > > is a fast partial match search using indexes, i.e. foo ~ 'bar' or foo > > LIKE '%bar%' type matching. > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] n-gram search function
Thanks. I'll look into this. -- Tatsuo Ishii SRA OSS, Inc. Japan > 3-gram is implemented as a contrib/pg_trgm. It currently uses GiST index, > but may be enhanced with the GiN. > > Oleg > > On Sat, 17 Feb 2007, Tatsuo Ishii wrote: > > > Hi, > > > > Is anybody working on implementing n-gram search functionality for > > text type data? tsearch2 is great for long text but it's not > > appropreate for short (10-100 bytes) text data. What I want to achieve > > is a fast partial match search using indexes, i.e. foo ~ 'bar' or foo > > LIKE '%bar%' type matching. > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings ---(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] RFC: Temporal Extensions for PostgreSQL
Ühel kenal päeval, R, 2007-02-16 kell 17:39, kirjutas Alvaro Herrera: > Jim C. Nasby wrote: > > My suggestion would be to focus on a period data type first and > > foremost, as that's something that could be readily used by a lot of > > folks. Of particular note, it's difficult to query tables that have > > start_time and end_time fields to define a period; it's easy to screw up > > the boundary conditions, and it's also hard to make those queries > > perform well without going to extra lengths (such as defining a 'bogus' > > GiST index on something like box(point(start,start),point(end,end)). And > > it's not possible to do that in a way that avoids floating points and > > their errors. > > FWIW there's already a type called tinterval that stores (start,end). I > don't think it's very much documented; maybe it can be extended or used > as base for a new, more complete and robust type, indexable in a more > natural way, etc etc. How easy/hard would it be to create unique indexes on tinterval (unique here meaning non-overlapping) ? Is tinterval meant to be open/closed at start and end ? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
