[HACKERS] Re:Sync Data
This is my first post/reply on this list, but I have been listening for a while now (I mostly read the replication ones ;-). I am interested in what developers/users are looking for in a replication/sync solution in postgresql, and contributing to that effort. I'm trying to centralize data in a unique db, like it:\ snip, cool ascii art removed Information: 1) The small DBs sync data with the big DB. What is the connection between small and big Dbs? (LAN/WAN) Is there a consistent connection between systems? 3) The big DB has a copy of all data. Just the small DBs inserts data in the big one. Are you looking for synchronous (before the insert commits) or asynchronous (after the insert commits)? Is there any chance for insert/update conflicts (insert/update same record in same table) on the big DB? The question: Does Postgres do it? Short answer, I don't think the current postgresql version will accomplish your needs, but there are some postgresql replication projects that might get you close depending on the answers to my questions above. You can find a list of the projects here. http://www.greatbridge.org/genpage?replication_top Darren Johnson Source Software Institute ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Re:Sync Data
Helo, On Thu, 31 May 2001 [EMAIL PROTECTED] wrote: Questions: 1) What is the connection between small and big Dbs? (LAN/WAN) 2) Is there a consistent connection between systems? 3) Are you looking for synchronous (before the insert commits) or asynchronous (after the insert commits)? 4) Is there any chance for insert/update conflicts (insert/update same record in same table) on the big DB? - problem : I have four small DBs that would sync data thru Internet during the night (128 kbs/s), there is no conflicts and there is nobody inserting data in small DBs while its syncing. I think it answers all the questions. Thanks. Darren Johnson Source Software Institute ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Imperfect solutions
On Thu, May 31, 2001 at 10:07:36AM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: What got me thinking about this is that I don't think my gram.y fix would be accepted given the current review process, Not to put too fine a point on it: the project has advanced a long way since you did that code. Our standards *should* be higher than they were then. and that is bad because we would have to live with no LIKE optimization for 1-2 years until we learned how to do it right. We still haven't learned how to do it right, actually. I think the history of the LIKE indexing problem is a perfect example of why fixes that work for some people but not others don't survive long. We put out several attempts at making it work reliably in non-ASCII locales, but none of them have withstood the test of actual usage. I think there are a few rules we can use to decide how to deal with imperfect solutions: You forgot * will the fix institutionalize user-visible behavior that will in the long run be considered the wrong thing? * will the fix contort new code that is written in the same vicinity, thereby making it harder and harder to replace as time goes on? The first of these is the core of my concern about %TYPE. This list points up a problem that needs a better solution than a list: you have to put in questionable features now to get the usage experience you need to do it right later. The set of prospective features that meet that description does not resemble the set that would pass all the criteria in the list. This is really a familiar problem, with a familiar solution. When a feature is added that is wrong, make sure it's marked somehow -- at worst, in the documentation, but ideally with a NOTICE or something when it's used -- as experimental. If anybody complains later that when you ripped it out and redid it correctly, you broke his code, you can just laugh, and add, if you're feeling charitable, experimental features are not to be depended on. -- Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Cache for query plans
On Wed, May 30, 2001 at 03:00:53PM -0300, Roberto Abalde wrote: Hi, I need to implement a cache for query plans as part of my BSc thesis. Does anybody know what happened to Karel Zak's patch? Hi, my patch is on my ftp and nobody works on it, but I mean it's good begin for some next work. I not sure with implement this experimental patch (but usable) to official sources. For example Jan has more complex idea about query plan cache ... but first time we must solve some sub-problems like memory management in shared memory that is transparently for starndard routines like copy query plan ... and Tom isn't sure with query cache in shared memory...etc. Too much queries, but less answers :-) Karel PS: Sorry for my english :( Do you anytime read any my mail :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] First version of multi-key index support for GiST
Teodor Sigaev [EMAIL PROTECTED] writes: The point was that in gist_tuple_replacekey (called from gistPageAddItem) key may be replaced by null value, but flag itup-t_info INDEX_NULL_MASK is not set. Ah. That's certainly a bug. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ERROR: cache lookup for proc 43030134 failed
Kovacs Zoltan [EMAIL PROTECTED] writes: Starting pg_dump, this error occured (there is no output dump, unfortunately). Getting closer, I got this: tir=# SELECT pg_get_viewdef(c.relname) AS definition FROM pg_class c offset 441 limit 1; ERROR: cache lookup for proc 4303134 failed I think you've got a view or rule that refers to a function you dropped. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Imperfect solutions
Bruce Momjian [EMAIL PROTECTED] writes: What got me thinking about this is that I don't think my gram.y fix would be accepted given the current review process, Not to put too fine a point on it: the project has advanced a long way since you did that code. Our standards *should* be higher than they were then. and that is bad because we would have to live with no LIKE optimization for 1-2 years until we learned how to do it right. We still haven't learned how to do it right, actually. I think the history of the LIKE indexing problem is a perfect example of why fixes that work for some people but not others don't survive long. We put out several attempts at making it work reliably in non-ASCII locales, but none of them have withstood the test of actual usage. I think there are a few rules we can use to decide how to deal with imperfect solutions: You forgot * will the fix institutionalize user-visible behavior that will in the long run be considered the wrong thing? * will the fix contort new code that is written in the same vicinity, thereby making it harder and harder to replace as time goes on? The first of these is the core of my concern about %TYPE. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Re: New version of contrib-intarray
Oleg Bartunov [EMAIL PROTECTED] writes: New version of contrib-intarray for postgresql version 7.1 and above is available from http://www.sai.msu.su/~megera/postgres/gist/ I'm going to be making commits later today that clean up the char* that should be Datum ugliness in GiST. I think the intarray part of these changes overlap what you've done, so we're facing a bit of a merge problem. You should have let me know that you had more stuff in the pipeline. This version is independent from our work on multi-key GiST, so if 7.1.3 is planned please use it. I do not think we should be committing such changes into the 7.1.* branch. At this point only critical bug fixes are going to go into that branch. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Imperfect solutions
On Thu, 31 May 2001, Tom Lane wrote: Indeed. You're looking at the aftermath of an imperfect fix to add foreign keys. With all due respect to Jan and Stephan, who did a great job adding the feature at all, there are still a lot of things that need to be fixed in that area. The trouble with imperfect fixes is that they Ugh yes. Actually all of the constraints seem to have this problem to some degree. Unique doesn't quite work right for updates where rows may temporarily be of the same value, check constraints using user functions can be violated if those functions do sql statements and column renames cause dump/restore to fail. Fk has at least the following (in no order and probably incomplete due to just waking up): Temp tables can shadow pk/fk tables - If we have schemas and temp tables are in their own, we can probably fix this just with fully qualifying. - Otherwise, we'd probably want to refer to the table by oid, but that would require having some way to do that in SPI or to replace the SPI calls. (Getting the name from the oid isn't sufficient, obviously) Inheritance - Plenty of discussion about this already - An additional wrinkle comes in if we allow/are going to allow users to rename base table columns in inherited tables. Alter Table Rename - Either we need to store oids or follow name changes. I'd actually prefer the latter if possible, but that requires a dependency system. (Especially if we were to go with only storing the text of check constraints.) General - For update locks are too strong? Do we need a self conflicting lock on the pk table rows? Is there some generally better way to handle this? How does this tie into the problem Jan noted before? - We probably need a way to check the entire table at once rather than per row checks. This would make alter table more reasonable for dump/restore (right now on large tables it would try each row's check separately - ugh) - Deferred constraints are broken in a few cases. Update/insert trigger on fk needs to make sure the row is still there at check time, no action trigger needs to make sure there hasn't been another row with the key values inserted. Other actions are questionable, has anyone actually figured out what the correct behavior is? I think that running actual actions immediately may be the correct thing, but in any case, they'd probably need checks like the no action trigger (what happens if the delete/insert is done within one statement due to triggers or whatever) - Match partial - Complicated. To do this completely means almost a separate implementation since stuff like the above checks wouldn't work in this case and means that we have to recognize things where the user has updated two pk rows referenced by a single fk row to distinct key values, since that's an error condition. Storage/Reporting - We really need something that stores the fk information better than what we have (we may want to see if we can generalize more constraints into the system as well, but we'd have to see) - We'll want to make dump/restores show the constraint in a better fashion. This may need the above, and we'd still need to have backward compatibility (one of the reasons switching to storing oids would be interesting) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ERROR: cache lookup for proc 43030134 failed
Kovacs Zoltan [EMAIL PROTECTED] writes: It means that the 21st line of the result is problematic, because writing 20 instead of 21 I got no problem. I think not. The current implementation of LIMIT fetches one more row than is really needed, IIRC. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Non-ASCII locales (was:Re: [HACKERS] Imperfect solutions)
Lamar Owen [EMAIL PROTECTED] writes: Looking through the archives Ifind some details, such as the function locale_is_like_safe() , and I see other details -- but a concise picture of what one can expect operating in a non-locale_is_like_safe() (which currently includes ONLY the C and POSIX locales) locale would be, As of 7.1, LIKE will always work correctly in non-C locales, because it will never try to use an index. Concise enough? What we need, and don't have, is reliable information about which locales the pre-7.1 indexing hack was actually safe in. A complicating factor is that non-C locale definitions are probably platform-specific. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Access statistics
Jan Wieck [EMAIL PROTECTED] writes: In the next couple of hours (at least tomorrow) I would be ready to commit the backend changes for table-/index-access statistics and current backend activity views. Should I apply the patches or provide a separate patch for review first? Considering that you've not offered any detailed information about what you plan to do (AFAIR), a patch for review first would be polite ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] First version of multi-key index support for GiST
Oleg Bartunov [EMAIL PROTECTED] writes: We have implemented multi-key index support for GiST. Patch is available from http://www.sai.msu.su/~megera/postgres/gist/code/7.1.2/patch_multikeygist.7.1.2.gz I have committed these changes, along with your leak patch of 5/30. 1. initdb is required. But, it's possible just to execute update update pg_am set amstrategies = 12 where amname = 'gist'; No initdb is needed --- I fixed the code instead ;-) 2. You have to recompile all gist_*_ops functions I bit the bullet and fixed all the places that were using char* where they should have been using Datum. This doesn't completely free GIST from datatype assumptions: it still assumes that all datatypes it deals with will be pass-by-reference. But it's a step forward. This means not only a recompile but code changes for any user-supplied GIST ops. I applied the appropriate changes to everything that's in contrib (including your new RTREE emulation code). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: R-Tree implementation using GiST (compatible with multi-key GiST)
Oleg Bartunov [EMAIL PROTECTED] writes: Full implementation of R-Tree using GiST is available from http://www.sai.msu.su/~megera/postgres/gist/ Committed as a contrib module. At some point we'll probably want to move this into the mainframe, but I left it as a separate package for now. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
