[HACKERS] Clarification needed
Hai, Can anyone of you help me in finding the datatype of a particular column in a table in Postgres? Thanks and Regards, M.Indira
Re: [HACKERS] Fwd: pg_dump VS alter database ... set search_path ...
On 10/9/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: Maybe my understanding is wrong - I'll be glad to hear why. Maybe at least to create special switcher for database settings? (It would remain backward compatibility...) -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Patch for Win32 blocking problem
Patch solves the problem with blocking backend in pgwin32_waitforsinglesocket() when it tries to send something to stat collector. Patch makes two thing: 1) pgwin32_waitforsinglesocket(): WaitForMultipleObjectsEx now called with finite timeout (100ms) in case of FP_WRITE and UDP socket. If timeout occurs then pgwin32_waitforsinglesocket() returns EINTR. Reason: As it follows from tests (see below) process may sleep forever in WaitForMultipleObjectsEx in case of infinite timeout. 2) pgwin32_send(): add loop around WSASend and pgwin32_waitforsinglesocket(). The reason is: for UDP socket, 'ok' result from pgwin32_waitforsinglesocket() isn't guarantee that socket is still free, it can become busy again and following WSASend call will fail with WSAEWOULDBLOCK error. Note, situations above occur only on very high load and very rare. About 1 time per several hours. Personally, I don't like 1) patch way, but I can't find better solution. To simulate the bug, I developed test suite (http://www.sigaev.ru/misc/wintest.tgz). Test runs one 'collector' and several (32 by defaults) clients, which send a lot of packets to collector. Socket library is taken from pgsql directly. Installation testing (under MinGW): % tar xzvf wintest.tgz % cd wintest % make % ./serveres Archive contains two socket.c: socket.c.orig - as it in pgsql socket.c - already patched fprintf() calls are added to pgwin32_waitforsinglesocket() and in case of socket.c.orig several clients never go out. Usually, it's needed 1-3 minutes to reproduce. Test suite works harder than pgsql, and block occurs even on uniprocessor box. It may be needed to increase number of clients to reliable reproduce the bug. Objections, comments, advices, suggestions? I intend to commit patch to all affected branches today or tomorrow if there are no objections or better ideas. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ win32.patch.gz Description: Unix tar archive ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Index Tuning Features
On Tue, 2006-10-10 at 20:17 -0400, Mark Woodward wrote: Another thing that this brings up is hints to a query. Over the years, I have run into situation where the planner wasn't great. It would be nice to try forcing different strategies on the planner and see if performance caan be improved. /*+ Not on this thread, p-l-e-a-s-e */ -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Clarification needed
Look at http://www.postgresql.org/docs/8.1/interactive/catalogs.html Specially on pg_attribute, pg_class and pg_type table. Or you can use some features in the psql. Zdenek Indira Muthuswamy napsal(a): Hai, Can anyone of you help me in finding the datatype of a particular column in a table in Postgres? Thanks and Regards, M.Indira ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Index Tuning Features
Another thing that this brings up is hints to a query. Over the years, I have run into situation where the planner wasn't great. It would be nice to try forcing different strategies on the planner and see if performance caan be improved. you can do this by setting enable_access_method type parameters. No, not generally. Usual problems include join order and wrong index, not only wrong access method. Andreas ---(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] Index Tuning Features
Thanks everybody for comments so far; this will be a useful discussion. On Tue, 2006-10-10 at 18:56 -0400, Robert Treat wrote: On Tuesday 10 October 2006 12:06, Tom Lane wrote: Similar in usage to an EXPLAIN, the RECOMMEND command would return a list of indexes that need to be added to get the cheapest plan for a particular query (no explain plan result though). Both of these seem to assume that EXPLAIN results, without EXPLAIN ANALYZE results to back them up, are sufficient for tuning. I find this idea a bit dubious, particularly for cases of marginal indexes. While I agree with Tom that generally EXPLAIN is not enough for tuning, I also know that when your dealing with queries that have run times in multiples of hours (and the corresponding hour long index builds) EXPLAIN ANALYZE just isn't an option. Anything that can be done to wheedle down your choices before you have to run EXPLAIN ANALYZE is a bonus. IMHO you need EXPLAIN, EXPLAIN ANALYZE and RECOMMEND As Robert points out, using EA can make tuning take a long time and that is the critical factor when you have a whole database/app to tune. This discussion helps me to make explicit what my thoughts had been on what an ideal index tuning process is: 1. Recommendation: Use RECOMMEND to get an 80/20 setting for a statement. As Peter suggests a user-space tool, I also imagine a tool that would automatically run RECOMMEND on all SQL statements in a workload and come up with proposals for additional indexes. We would have a first cut index design in minutes rather than days. 2. Evaluation: We can then create the potential indexes as Virtual ones and then re-run EXPLAINs to model how a whole workload would behave. We can begin to prune low-impact indexes out of the mix at this stage. Again, this can be done automatically. 3. Implementation: We re-create the new indexes as real indexes (perhaps concurrently) 4. Correction: We then run the workload and then use existing tools to spot the statements causing the most problems and manually assess them using EXPLAIN ANALYZE. Manually postulate new Virtual indexes and re-model the workload again as (2) Steps (3) and (4) have both been improved for 8.2. Steps (1) and (2) are completely new steps for 8.3 The above process can be performed without tool support, but its clear that further automation will help greatly here. I foresee that the development of both server-side and tools will take more than one release. Discussion of tool support can begin once we have agreed server-side capability. With that as a backdrop, further comments are: On Tue, 2006-10-10 at 19:15 -0400, Tom Lane wrote: Robert Treat [EMAIL PROTECTED] writes: Anything that can be done to wheedle down your choices before you have to run EXPLAIN ANALYZE is a bonus. Fair enough, but I prefer Peter's suggestion of attaching the hypothetical index definitions to EXPLAIN itself, rather than making bogus catalog entries. Something along the line of EXPLAIN statement ASSUMING INDEX fooi ON foo [ ASSUMING INDEX ... ] I do like this, though for step (2) above we would need to attach the appropriate indexes to each of the SQL statements prior to execution. Doing this for a single SQL statement is fine, but doing that for a whole workload of 1000s of statements is not very practical, hence an externally declarative approach seems better. I can imagine many other declarative approaches other than the one I proposed; it just seems pretty neat to me to use almost exactly the same syntax for a virtual index as for a real index. As I mentioned, ideally this would not be a full-strength catalog object, but I was thinking towards implementation also. Another possibility would be to use a local pg_virtual_indexes table. On Tue, 2006-10-10 at 18:06 +0200, Peter Eisentraut wrote: Simon Riggs wrote: - RECOMMEND command Similar in usage to an EXPLAIN, the RECOMMEND command would return a list of indexes that need to be added to get the cheapest plan for a particular query (no explain plan result though). This functionality also seems useful, but maybe it should be the job of a user-space tool? So from above, Yes, I see a user-space tool also, but not instead. The RECOMMEND command is the minimal server functionality required to enable an (external) automated tuning support tool to be developed. Possible architectures for this functionality include both user-space and server-space options. Much thinking has been done on this in the DB research community, with the general consensus being its easier to extend the planner to cope with postulation that it is to create an external postulation tool that acts (accurately) like the planner. DB2 advisor: An optimizer smart enough to recommend its own indexes. Gary Valentin, Michael Zuliani, Daniel C. Zilio, Guy M. Lohnman, and Alan Skelley. In The 16th International Conference on Data Engineering (ICDE'00), San Diego, CA. IEEE
Re: [HACKERS] Index Tuning Features
The above process can be performed without tool support, but its clear that further automation will help greatly here. I foresee that the development of both server-side and tools will take more than one release. Discussion of tool support can begin once we have agreed server-side capability. If it came to automated tools, wouldn't fit in this discussion to give some performance requirement limits to the RECOMMEND tool ? In a workload not all queries are real time or high priority, and such a lesser impact index can help enough sometimes to meet the requirements, compared to a high impact index which would make the query fly. Example: inserting in a table must be real time, reporting can be taken offline... So it would be nice to have a recommendation tool which can take into account the performance requirements of the individual queries, possibly making the right compromises to meat all requirements for all queries. Cheers, Csaba. ---(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] Index Tuning Features
On 10/10/06, Mark Woodward [EMAIL PROTECTED] wrote: I think the idea of virtual indexes is pretty interesting, but ultimately a lesser solution to a more fundimental issue, and that would be hands on control over the planner. Estimating the effect of an index on a query prior to creating the index is a great idea, how that is done is something different than building concensus that it should be done. Another thing that this brings up is hints to a query. Over the years, I have run into situation where the planner wasn't great. It would be nice to try forcing different strategies on the planner and see if performance caan be improved. you can do this by setting enable_access_method type parameters. Here's your hammer, all your problems are now nails. The enable_xxx setting are OK for simple queries gone wrong, but if you have a more complex query, any one of those settins may help or hinder different parts of a query, then you would be left with choosing which of them helps more than hurts the over-all query. being able to alter the query plan would help in areas where there are data patterns in a database that the ANALYZE command can't pick up because it is not designed too. Imagine you have a street map database ordered by zip, street, number. The primary order is zipcode, the secondary order is street. There is a relationship of number to street, and zip to street. The analyzer, at least the last time I checked, does not recognize these relationships. So, a search by street and number would probably use a sequential scan rather than the street index. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [DOCS] Added links to the release notes
On Fri, 2006-10-06 at 23:21 -0400, Bruce Momjian wrote: I have added links from the 8.2 release notes into our documentation. If people have additions/changes, please let me know. Very cool. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Index Tuning Features [2]
Hi, sorry for opening a new thread but I have just subscribed to the list. We have already an implementation of an index advisor for 7.4.8. This is the result of several master theses, so it's no production ready yet, but it works (with some limitations). The main idea is: 1. to run the planner on the query 2. create virtual indexes (only in the data dictionary) based on some heuristics including multi-column indexes 3. run the planner again 4. extract the used virtual indexes and store them in a new table pg_indexadvisor together with a estimation of the gain We use this in two ways: - There is a proof of concept tool for determining the index recommendations for a given workload (basically it solves the knapsack problem) - We have a more advanced approach where collecting index recommendations and chosing the right set is done continuously and automatically. There are some papers, e.g. a demo paper at VLDB'03 where we have presented this on top of DB2 but now it is integrated in pgsql. It definitely requires some work to port it to 8.2 and to make it usable for production environments. Furthermore, there are some performance bottlenecks (creating virtual indexes, calling the planner twice) but I think they can be solved. So, let me know if you are interested, Kai ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Upgrading a database dump/restore
Hi, Mark, Mark Woodward wrote: People are working it, someone even got so far as dealing with most catalog upgrades. The hard part going to be making sure that even if the power fails halfway through an upgrade that your data will still be readable... Well, I think that any *real* DBA understands and accepts that issues like power failure and hardware failure create situations where suboptimal conditions exist. :-) Stopping the database and copying the pg directory addresses this problem, upon failure, it is a simple mv bkdir pgdir, gets you started again. But when people have enough bandwith and disk space to copy the pg directory, they also have enough to create and store a bzip2 compressed dump of the database. Or did I miss something? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
[HACKERS] hstore isexists
Before we spring hstore on an unsuspecting world as a contrib module, in the interests of good English, is it too late to change isexists to simply exists? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Upgrading a database dump/restore
On Oct 11, 2006, at 7:57 AM, Markus Schaber wrote: Hi, Mark, Mark Woodward wrote: People are working it, someone even got so far as dealing with most catalog upgrades. The hard part going to be making sure that even if the power fails halfway through an upgrade that your data will still be readable... Well, I think that any *real* DBA understands and accepts that issues like power failure and hardware failure create situations where suboptimal conditions exist. :-) Stopping the database and copying the pg directory addresses this problem, upon failure, it is a simple mv bkdir pgdir, gets you started again. But when people have enough bandwith and disk space to copy the pg directory, they also have enough to create and store a bzip2 compressed dump of the database. Or did I miss something? Not necessarily. copying a directory on most modern unix systems can be accomplished by snapshotting the filesystem. In this case, you only pay the space and performance cost for blocks that are changed between the time of the snap and the time it is discarded. An actual copy of the database is often too large to juggle (which is why we write stuff straight to tape libraries). The real problem with a dump of the database is that you want to be able to quickly switch back to a known working copy in the event of a failure. A dump is the furthest possible thing from a working copy as one has to rebuild the database (indexes, etc.) and in doing so, you (1) spend the better part of a week running pg_restore and (2) ANALYZE stats change, so your system's behavior changes in hard-to- understand ways. Best regards, Theo // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] hstore isexists
Andrew Dunstan wrote: Before we spring hstore on an unsuspecting world as a contrib module, in the interests of good English, is it too late to change isexists to simply exists? Sure, we can do it, as long as we aren't worried about adding incompatibilities for existing hstore users. -- Bruce Momjian [EMAIL PROTECTED] 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] hstore isexists
It's possible to create function 'exists' and mention only it in docs. Bruce Momjian wrote: Andrew Dunstan wrote: Before we spring hstore on an unsuspecting world as a contrib module, in the interests of good English, is it too late to change isexists to simply exists? Sure, we can do it, as long as we aren't worried about adding incompatibilities for existing hstore users. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] hstore isexists
Teodor Sigaev wrote: It's possible to create function 'exists' and mention only it in docs. Good point. Will you do that, or do you want me to? Bruce Momjian wrote: Andrew Dunstan wrote: Before we spring hstore on an unsuspecting world as a contrib module, in the interests of good English, is it too late to change isexists to simply exists? Sure, we can do it, as long as we aren't worried about adding incompatibilities for existing hstore users. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] hstore isexists
Andrew Dunstan wrote: Teodor Sigaev wrote: It's possible to create function 'exists' and mention only it in docs. Good point. Will you do that, or do you want me to? May I ask you? I'm afraid that there is more incorrectness. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] hstore isexists
Teodor Sigaev wrote: Andrew Dunstan wrote: Teodor Sigaev wrote: It's possible to create function 'exists' and mention only it in docs. Good point. Will you do that, or do you want me to? May I ask you? I'm afraid that there is more incorrectness. Well, isdefined isn't incorrect, but I think there's a good case to change it to just defined, since exists and defined are the names of the corresponding perl tests on associative arrays. All the rest look ok to me. cheers andrew ---(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] Upgrading a database dump/restore
Theo Schlossnagle [EMAIL PROTECTED] writes: The real problem with a dump of the database is that you want to be able to quickly switch back to a known working copy in the event of a failure. A dump is the furthest possible thing from a working copy as one has to rebuild the database (indexes, etc.) and in doing so, you (1) spend the better part of a week running pg_restore and (2) ANALYZE stats change, so your system's behavior changes in hard-to- understand ways. Seems like you should be looking into maintaining a hot spare via PITR, if your requirement is for a bit-for-bit clone of your database. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] hstore isexists
'exists' isn't a good name for function :(. From gram.y:
col_name_keyword:
...
function_name:
IDENT { $$ = $1; }
| unreserved_keyword{ $$ = pstrdup($1); }
| func_name_keyword { $$ = pstrdup($1); }
;
So call of function named 'exists' should be in quotas:
select exists('a=1','a');
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] hstore isexists
Teodor Sigaev [EMAIL PROTECTED] writes: 'exists' isn't a good name for function :(. Yeah, that isn't going to work. Perhaps ifexists? Or just leave well enough alone. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Upgrading a database dump/restore
On Oct 11, 2006, at 9:36 AM, Tom Lane wrote: Theo Schlossnagle [EMAIL PROTECTED] writes: The real problem with a dump of the database is that you want to be able to quickly switch back to a known working copy in the event of a failure. A dump is the furthest possible thing from a working copy as one has to rebuild the database (indexes, etc.) and in doing so, you (1) spend the better part of a week running pg_restore and (2) ANALYZE stats change, so your system's behavior changes in hard-to- understand ways. Seems like you should be looking into maintaining a hot spare via PITR, if your requirement is for a bit-for-bit clone of your database. The features in 8.2 that allow for that look excellent. Prior to that, it is a bit clunky. But we do this already. However, PITR and a second machine doesn't help during upgrades so much. It doesn't allow for an easy rollback. I'd like an in-place upgrade that is supposed to work. And then I'd do: Phase 1 (confidence): clone my filesystems upgrade the clones run regression tests to obtain confidence in a flawless upgrade. drop the clones Phase 1 (abort): drop clones Phase 2 (upgrade): snapshot the filesystems upgrade the base Phase 2 (abort): rollback to snapshots Phase 2 (commit): drop the snapshots // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Clarification needed
Indira Muthuswamy wrote: Can anyone of you help me in finding the datatype of a particular column in a table in Postgres? Thanks and Regards, M.Indira You're almost in the right place, but you'd be better off asking this question in the pgsql-general or perhaps pgsql-novice. This mailing list is for talking about modifications to the actual database engine. Drew ---(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] Clarification needed
In psql, psql\d tableName Query is SELECT pg_catalog.format_type(a.atttypid, a.atttypmod) from pg_attribute a, pg_class c where a.attrelid =c.oid and c.relname='TableName' and a.attname='ColName'; Zdenek Kotala wrote: Look at http://www.postgresql.org/docs/8.1/interactive/catalogs.html Specially on pg_attribute, pg_class and pg_type table. Or you can use some features in the psql. Zdenek Indira Muthuswamy napsal(a): Hai, Can anyone of you help me in finding the datatype of a particular column in a table in Postgres? Thanks and Regards, M.Indira ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] hstore isexists
Tom Lane wrote: Teodor Sigaev [EMAIL PROTECTED] writes: 'exists' isn't a good name for function :(. Yeah, that isn't going to work. Perhaps ifexists? Or just leave well enough alone. Darn. Can't have been thinking clearly this morning. How about exist (no s)? cheers andrew ---(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] hstore isexists
Andrew Dunstan [EMAIL PROTECTED] writes: How about exist (no s)? Seems a bit ugly, but better than isexists or ifexists ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Clarification needed
you should use schemaname.tablename syntax if you are using schemaOn 10/11/06, Dhanaraj M [EMAIL PROTECTED] wrote:In psql, psql\d tableNameQuery is SELECT pg_catalog.format_type(a.atttypid, a.atttypmod) frompg_attribute a, pg_class c wherea.attrelid =c.oid andc.relname='TableName' and a.attname='ColName'; Zdenek Kotala wrote: Look at http://www.postgresql.org/docs/8.1/interactive/catalogs.html Specially on pg_attribute, pg_class and pg_type table. Or you can use some features in the psql. Zdenek Indira Muthuswamy napsal(a): Hai, Can anyone of you help me in finding the datatype of a particular column in a table in Postgres? Thanks and Regards, M.Indira ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Modification to the postgres catalog
HI... im trying to modify the pg_class table by adding a new attribute. To accomplish that, i modify the next archives:- include/pg_class.h: in this file, i modfify: FormData_pg_class struct: i add the new attribute, example a boolean...
. bool myNewAttribute; /*my new attribute */ aclitem relacl[1]; /* we declare this just for the catalog */ }then, i modify the macro CLASS_TUPLE_SIZE:
#define CLASS_TUPLE_SIZE \ (offsetof(FormData_pg_class,relhassubclass) + sizeof(bool) + sizeof(bool)) /* the last bool is my bool */and then, i modify the DATA(insert ..)) of this file by adding a t just before the _null_ value...
- include/pg_attribute.h: i add to the macros and data the new attribute of the pg_class table:...{ 1259, {myNewAttribute},16, -1, 1, 25, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
{ 1259, {relacl}, 1034, -1, -1, 26, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0 } /* this is the macro */DATA(insert ( 1259 relhassubclass 16 -1 1 24 0 -1 -1 t p c t f f t 0));
DATA(insert ( 1259 myNewAttribute 16 -1 1 25 0 -1 -1 t p c t f f t 0));DATA(insert ( 1259 relacl 1034 -1 -1 26 1 -1 -1 f x i f f f t 0)); /* el data insert */- utils/cache/relcache.c: in here, when the tables are initialized... i add the next line of code:
rel-rd_rel-myNewAttribute = true;then, i compile i everything goes well but when i execute the comand initdb -D ... i always get next message:
initializing pg_authid ... okenabling unlimited row size for system tables ... okinitializing dependencies ... okcreating system views ... okloading pg_description ... ok
creating conversions ... oksetting privileges on built-in objects ... FATAL: column relacl does not existchild process exited with exit code 1I don't know what's going on Can anyone help me please?
thanks.
Re: [HACKERS] Index Tuning Features
Mark Woodward [EMAIL PROTECTED] writes: The analyzer, at least the last time I checked, does not recognize these relationships. The analyzer is imperfect but arguing from any particular imperfection is weak because someone will just come back and say we should work on that problem -- though I note nobody's actually volunteering to do so whereas they appear to be for hints. I think the stronger argument is to say that there are some statistical properties that the analyzer _cannot_ be expected to figure out. Either because a) they're simply too complex to ever expect to be able to find automatically, b) too expensive to make it worthwhile in the general case, or c) because of some operational issue such as the data changing frequently enough that the analyzes that would be necessary to keep the statistics up to date would become excessively expensive or even be impossible to perform rapidly enough. The people arguing that hints themselves are of negative benefit are taking the argument far too far. I've never heard an Oracle DBA gripe about having to fix hints on an upgrade; they're usually the first ones to suggest hinting a poorly written query. In fact Oracle is going in the opposite direction of even relying on hints internally. Its plan stability feature depends on generating and storing hints internally associated with every query. The argument against hints is usually that the effort would be better spent elsewhere, not that hints are inherently a bad idea. We already have enable_* parameters and they are absolutely necessary for testing and experimenting to understand whether the planner is incorrect and where it has gone wrong. Hints are just a more precisely targeted version of these. There have been plenty of instances on this list where people posted 20-30 line query plans with several joins of each type where the enable_* parameters were too coarse grained to use effectively. -- Gregory Stark EnterpriseDB http://www.enterprisedb.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] Modification to the postgres catalog
Carlos Chacon [EMAIL PROTECTED] writes: HI... im trying to modify the pg_class table by adding a new attribute. - include/pg_class.h: in this file, i modfify: Did you remember to update Natts_pg_class and the Anum_ macros? then, i modify the macro CLASS_TUPLE_SIZE: #define CLASS_TUPLE_SIZE \ (offsetof(FormData_pg_class,relhassubclass) + sizeof(bool) + sizeof(bool)) /* the last bool is my bool */ Seriously ugly, should use offsetof the last attribute, ie, yours. Also, look at the uses of Natts_pg_class_fixed --- there was some cruftiness involved there in existing releases (it's gone in HEAD and I'm too lazy to look back at exactly what it was...) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features [2]
On Wed, 2006-10-11 at 14:30 +0200, Kai-Uwe Sattler wrote: sorry for opening a new thread but I have just subscribed to the list. Not at all, glad to hear about your implementation. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features [2]
On Wed, 2006-10-11 at 14:30 +0200, Kai-Uwe Sattler wrote: We have already an implementation of an index advisor for 7.4.8. It definitely requires some work to port it to 8.2 and to make it usable for production environments. Furthermore, there are some performance bottlenecks (creating virtual indexes, calling the planner twice) but I think they can be solved. I'm sure everybody would be glad to see the existing work submitted as a Work-in-Progress patch to pgsql-patches. We can then have a look at it and see what to do with it. Whatever happens your experience will be invaluable in taking this forward. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Modification to the postgres catalog
Thanks for you help...But i modify too Natts_pg_class and the Anum macro...Only I forgot mentionated it in the last mail. i put:#define Natts_pg_class_fixed 25#define Natts_pg_class 26 #define Anum_pg_class_myNewAttribute 25#define Anum_pg_class_relacl 26I really don't understand you when you said: there was somecruftiness involved there in existing releases (it's gone in HEAD and I'm too lazy to look back at exactly what it was...)...Anyway, thanks for trying to help meP.D: For anyone, i still need help Bye.On 10/11/06, Tom Lane [EMAIL PROTECTED] wrote:Carlos Chacon [EMAIL PROTECTED] writes: HI... im trying to modify the pg_class table by adding a new attribute. - include/pg_class.h: in this file, i modfify: Did you remember to update Natts_pg_class and the Anum_ macros?then, i modify the macro CLASS_TUPLE_SIZE: #define CLASS_TUPLE_SIZE \(offsetof(FormData_pg_class,relhassubclass) + sizeof(bool) + sizeof(bool)) /* the last bool is my bool */Seriously ugly, should use offsetof the last attribute, ie, yours.Also, look at the uses of Natts_pg_class_fixed --- there was somecruftiness involved there in existing releases (it's gone in HEAD and I'm too lazy to look back at exactly what it was...)regards, tom lane
Re: [HACKERS] Patch for Win32 blocking problem
Teodor Sigaev [EMAIL PROTECTED] writes: Patch solves the problem with blocking backend in pgwin32_waitforsinglesocket() when it tries to send something to stat collector. Adding the looping in pgwin32_send() seems clearly correct, since there could be multiple processes trying to send to the collector at the same time. I find the proposed patch in pgwin32_waitforsinglesocket to be a pretty ugly kluge though. Are you sure it's needed given the other fix? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Modification to the postgres catalog
Carlos Chacon [EMAIL PROTECTED] writes: But i modify too Natts_pg_class and the Anum macro...Only I forgot mentionated it in the last mail. i put: OK ... did you add a suitable initial value to each of the DATA lines in pg_class.h? Did you remember to adjust pg_class's own relnatts field appearing in the DATA line for it? You could try looking at one of the past commits that has added a column to pg_class, and make sure you touched all the places it did. 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] Index Tuning Features
Mark Woodward [EMAIL PROTECTED] writes: The analyzer, at least the last time I checked, does not recognize these relationships. The analyzer is imperfect but arguing from any particular imperfection is weak because someone will just come back and say we should work on that problem -- though I note nobody's actually volunteering to do so whereas they appear to be for hints. I think the stronger argument is to say that there are some statistical properties that the analyzer _cannot_ be expected to figure out. Either because a) they're simply too complex to ever expect to be able to find automatically, b) too expensive to make it worthwhile in the general case, or c) because of some operational issue such as the data changing frequently enough that the analyzes that would be necessary to keep the statistics up to date would become excessively expensive or even be impossible to perform rapidly enough. Well, from a purely data domain standpoint, it is impossible to charactize the exact nature of a data set without enough information to recreate it. Anything less must be designed for a fixed set of assumptions. There is no way that every specific trend can be covered by a fixed number of assumptions. The argument that all we need is better statistics completely misses the point. There will *always* be a number cases where the planner will not work optimally. I would say that a simpler planner with better hints will always be capable of creating a better query plan. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
Mark Woodward [EMAIL PROTECTED] writes: I would say that a simpler planner with better hints will always be capable of creating a better query plan. This is demonstrably false: all you need is an out-of-date hint, and you can have a worse plan. The argument against hints is not about whether someone could knock together a crappy hint facility and be able to get some use out of it. It is about how much work it would take to design a *good* hint facility that makes it easy to maintain hints that are robust in the face of data and query changes. If someone were to sit down and design and build such a thing, it'd very likely get accepted into core Postgres --- but personally, I think the equivalent amount of effort would be better spent on improving the planner and the statistics. As Josh already noted, Oracle-like hints are pretty likely to get rejected ... not only because of doubts about their true usefulness, but out of fear of falling foul of some Oracle patent or other. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Index Tuning Features
Mark Woodward [EMAIL PROTECTED] writes: I would say that a simpler planner with better hints will always be capable of creating a better query plan. This is demonstrably false: all you need is an out-of-date hint, and you can have a worse plan. That doesn't make it false, it makes it higher maintenance. Hints are understood to require maintenance. The argument against hints is not about whether someone could knock together a crappy hint facility and be able to get some use out of it. It is about how much work it would take to design a *good* hint facility that makes it easy to maintain hints that are robust in the face of data and query changes. If someone were to sit down and design and build such a thing, it'd very likely get accepted into core Postgres --- but personally, I think the equivalent amount of effort would be better spent on improving the planner and the statistics. While it is always true that something can be improved, there comes a point where work outweighs benefits. I can't say that the planner is at that point, but I think that isn't even an issue. The notion of hints would probably one of the biggest steps toward improving the planner. Like I said, it is inarguable that there will always be queries that the planner can not execute efficiently based on the statistics gathered by analze. Since that number must be greater than zero, some methodology to deal with it should be created. As Josh already noted, Oracle-like hints are pretty likely to get rejected ... not only because of doubts about their true usefulness, but out of fear of falling foul of some Oracle patent or other. Well, if it would get rejected if it looked like Oracle, assuming you would probably be one of the people rejecting it, what do you envision as not being rejected? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] hstore isexists
'exists' isn't a good name for function :(. Yeah, that isn't going to work. Perhaps ifexists? Or just leave well enough alone. Darn. Can't have been thinking clearly this morning. How about exist (no s)? Maybe 'found', 'present', or 'contains'? (no, I haven't checked for a grammar conflict) -- Korry
Re: [HACKERS] Index Tuning Features
On Wed, Oct 11, 2006 at 12:40:42PM -0400, Gregory Stark wrote: poorly written query. In fact Oracle is going in the opposite direction of even relying on hints internally. Its plan stability feature depends on generating and storing hints internally associated with every query. But IBM, whose DB2 planner and optimiser is generally regarded as way better than Oracle's (at least by anyone I know who's used both), doesn't like hints. The IBM people all say the same thing Tom has said before: that the work to design the thing correctly is better spent making the planner and optimiser parts smarter and cheaper, because out of that work you also manage not to have the DBA accidentally mess things up by simple-minded rule-based hints. (Note that I'm not trying to wade into the actual argument; I'm just pointing out that even the biggest industry people don't agree on this point.) A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(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] Clarification needed
See also information_schema and newsysviews on pgFoundry. On Wed, Oct 11, 2006 at 10:15:22AM +0200, Zdenek Kotala wrote: Look at http://www.postgresql.org/docs/8.1/interactive/catalogs.html Specially on pg_attribute, pg_class and pg_type table. Or you can use some features in the psql. Zdenek Indira Muthuswamy napsal(a): Hai, Can anyone of you help me in finding the datatype of a particular column in a table in Postgres? Thanks and Regards, M.Indira ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Index Tuning Features
On Wed, Oct 11, 2006 at 03:27:19PM -0400, Mark Woodward wrote: improving the planner. Like I said, it is inarguable that there will always be queries that the planner can not execute efficiently based on the statistics gathered by analze. Since that number must be greater than zero, some methodology to deal with it should be created. Just because I'm one of those statistics true believers, what sort of information do you think it is possible for the DBA to take into consideration, when building a hint, that could not in principle be gathered efficiently by a statistics system? It seems to me that you're claiming that DBAs can have magic knowledge. While I would be delighted to learn that my thumb in the air guesses in the past had turned out to be due to my deep knowledge of my data, I'm instead unhappily confessing that what I really, really wanted when I made those guesses was better knowledge, based on some analysis of the data. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] hstore isexists
On Wed, Oct 11, 2006 at 10:04:10AM -0400, Andrew Dunstan wrote: Teodor Sigaev wrote: It's possible to create function 'exists' and mention only it in docs. Good point. Will you do that, or do you want me to? ISTM it would be better to mention the deprecated version and explicitly state that it's deprecated. Bruce Momjian wrote: Andrew Dunstan wrote: Before we spring hstore on an unsuspecting world as a contrib module, in the interests of good English, is it too late to change isexists to simply exists? Sure, we can do it, as long as we aren't worried about adding incompatibilities for existing hstore users. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] hstore isexists
Jim C. Nasby wrote: On Wed, Oct 11, 2006 at 10:04:10AM -0400, Andrew Dunstan wrote: Teodor Sigaev wrote: It's possible to create function 'exists' and mention only it in docs. Good point. Will you do that, or do you want me to? ISTM it would be better to mention the deprecated version and explicitly state that it's deprecated. If we had had this in contrib previously with the deprecated call I would agree. But it seems like bad practice and unnecessary clutter to start off by deprecating something. cheers andrew ---(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] Index Tuning Features
On Oct 11, 2006, at 3:00 PM, Andrew Sullivan wrote: On Wed, Oct 11, 2006 at 12:40:42PM -0400, Gregory Stark wrote: poorly written query. In fact Oracle is going in the opposite direction of even relying on hints internally. Its plan stability feature depends on generating and storing hints internally associated with every query. But IBM, whose DB2 planner and optimiser is generally regarded as way better than Oracle's (at least by anyone I know who's used both), doesn't like hints. The IBM people all say the same thing Tom has said before: that the work to design the thing correctly is better spent making the planner and optimiser parts smarter and cheaper, because out of that work you also manage not to have the DBA accidentally mess things up by simple-minded rule-based hints. (Note that I'm not trying to wade into the actual argument; I'm just pointing out that even the biggest industry people don't agree on this point.) DBAs can mess things up already if they misuse the tools they are provided. Like 'rm'. Which is there, but should _RARELY_ be used on database datafiles. The argument that people _could_ use them in a bad way is silly. Of course, they could use them in a bad way, that's not an _argument_. Everyone agrees people can be stupid. However, the planner will never be perfect. I would like to see 1 out of every 500,000 queries actually benefit from a hint system (which means that 499,999 of the queries were planned perfectly fine by the planner). To fix my one query, that is crucially important to my business, it is a much more sane approach to hint the system to change its plan than it is to have to upgrade my binaries. // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.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
[HACKERS] On status data and summaries
Hello, In a possible moment of insanity, in http://archives.postgresql.org/pgsql-hackers/2006-09/msg00579.php I volunteered to try to help solve a problem Tom Lane noted: The hard part of this problem is finding a convenient way to capture status data out of the community's conversations. I observed that companies who do this well actually employ people to do that sort of thing, and that this might be a way for code morons like yours truly to make a contribution to development. I've been struggling since then, trying to figure out where to start. There are a _lot_ of discussions on -hackers, and many of them are blind alleys. Moreover, I can't summarise everything, I don't think, and still make any of those summaries sufficiently detailed to allow them to be useful. So I have a proposal. I was thinking of tracking 3 or 4 such discussions in the next release cycle, as a kind of proof of concept. I'm willing to do that, but I'd need guidance from those who are trying to produce a complicated feature, telling me that they need the support. Therefore, if someone involved in some such discussion pokes me saying, Follow this thread, please, I'll follow the thread in question (as well as follow-up discussions that come of it), and produce regular (weekly?) summaries of what I take to be the state of the collective mind, until such time as the code supporting the feature is checked in and agreed to. Then, at release time, the developers can evaluate whether the tracking produced few surprises at the end (and, perhaps, less thrash), or whether the experiment did not provide any benefit. If it does, we can see whether we can make this sort of thing scale by adding some additional volunteers to do a similar job in future. Does that seem worth doing? A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(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] hstore isexists
On Wed, Oct 11, 2006 at 04:17:19PM -0400, Andrew Dunstan wrote: Jim C. Nasby wrote: On Wed, Oct 11, 2006 at 10:04:10AM -0400, Andrew Dunstan wrote: Teodor Sigaev wrote: It's possible to create function 'exists' and mention only it in docs. Good point. Will you do that, or do you want me to? ISTM it would be better to mention the deprecated version and explicitly state that it's deprecated. If we had had this in contrib previously with the deprecated call I would agree. But it seems like bad practice and unnecessary clutter to start off by deprecating something. Sorry, I don't know the history of hstore... but if it's brand new, why are we worried about backwards compatibility? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] hstore isexists
Jim C. Nasby [EMAIL PROTECTED] writes: Sorry, I don't know the history of hstore... but if it's brand new, why are we worried about backwards compatibility? Because it's been available for awhile outside of contrib (namely, on Oleg and Teodor's own site). So there are people using it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] hstore isexists
Jim C. Nasby wrote: Sorry, I don't know the history of hstore... but if it's brand new, why are we worried about backwards compatibility? It has existed for a while, but has not previously been in contrib. cheers andrew ---(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] On status data and summaries
On Wed, Oct 11, 2006 at 04:27:41PM -0400, Andrew Sullivan wrote: Hello, In a possible moment of insanity, in http://archives.postgresql.org/pgsql-hackers/2006-09/msg00579.php I volunteered to try to help solve a problem Tom Lane noted: The hard part of this problem is finding a convenient way to capture status data out of the community's conversations. I observed that companies who do this well actually employ people to do that sort of thing, and that this might be a way for code morons like yours truly to make a contribution to development. I've been struggling since then, trying to figure out where to start. There are a _lot_ of discussions on -hackers, and many of them are blind alleys. Moreover, I can't summarise everything, I don't think, and still make any of those summaries sufficiently detailed to allow them to be useful. So I have a proposal. I was thinking of tracking 3 or 4 such discussions in the next release cycle, as a kind of proof of concept. I'm willing to do that, but I'd need guidance from those who are trying to produce a complicated feature, telling me that they need the support. Therefore, if someone involved in some such discussion pokes me saying, Follow this thread, please, I'll follow the thread in question (as well as follow-up discussions that come of it), and produce regular (weekly?) summaries of what I take to be the state of the collective mind, until such time as the code supporting the feature is checked in and agreed to. Then, at release time, the developers can evaluate whether the tracking produced few surprises at the end (and, perhaps, less thrash), or whether the experiment did not provide any benefit. If it does, we can see whether we can make this sort of thing scale by adding some additional volunteers to do a similar job in future. Does that seem worth doing? ISTM that it would be important to do that on threads/ideas that end up getting 'lost', which means you'll never get a cry for help. Though looking out for controversial threads might work... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] hstore isexists
On Wed, Oct 11, 2006 at 05:00:50PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Sorry, I don't know the history of hstore... but if it's brand new, why are we worried about backwards compatibility? Because it's been available for awhile outside of contrib (namely, on Oleg and Teodor's own site). So there are people using it. So wouldn't it make sense to document that the old functions are depricated so those existing users will stop using them? Maybe the base documentation isn't the best place for that... perhaps an UPGRADING section. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Index Tuning Features
Simon, The University of North Carolina (I think?) did some nice work on not only hypothetical indexes, but hypothetical materialized views (as well as really materialized view planner selection). Have you looked at that work? I think I forwarded the paper code to Jonah at one point ... -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Hints WAS: Index Tuning Features
Mark, First off, I'm going to request that you (and other people) stop hijacking Simon's thread on hypothetical indexes. Hijacking threads is an effective way to get your ideas rejected out of hand, just because the people whose thread you hijacked are angry with you. So please observe the thread split, thanks. Well, if it would get rejected if it looked like Oracle, assuming you would probably be one of the people rejecting it, what do you envision as not being rejected? Something better than Oracle. Since you're the one who wants hints, that's kind of up to you to define. Write a specification and make a proposal. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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] Upgrading a database dump/restore
Theo, Would you be able to help me, Zdenek Gavin in work on a new pg_upgrade? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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] Upgrading a database dump/restore
What type of help did you envision? The answer is likely yes. On Oct 11, 2006, at 5:02 PM, Josh Berkus wrote: Theo, Would you be able to help me, Zdenek Gavin in work on a new pg_upgrade? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Upgrading a database dump/restore
Theo, What type of help did you envision? The answer is likely yes. I don't know, whatever you have available. Design advice, at the very least. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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] Upgrading a database dump/restore
On Oct 11, 2006, at 5:06 PM, Josh Berkus wrote: What type of help did you envision? The answer is likely yes. I don't know, whatever you have available. Design advice, at the very least. Absolutely. I might be able to contribute some coding time as well. Testing time too. // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.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] Index Tuning Features
Andrew Sullivan wrote: Just because I'm one of those statistics true believers, what sort of information do you think it is possible for the DBA to take into consideration, when building a hint, that could not in principle be gathered efficiently by a statistics system? It seems to me that you're claiming that DBAs can have magic knowledge. Is one example is the table of addresses clustered by zip-code and indexes on State, City, County, etc? The current statistics systems at least see no correlation between these fields (since the alphabetical ordering of cities and numbering of postal codes is quite different). This makes the planner under-use the indexes because it sees no correlation and overestimates the number of pages read and the random accesses needed. However since San Francisco, CA data happens to be tightly packed on a few pages (since it shares the same few zip codes), few pages are needed and mostly sequential access could be used when querying SF data -- though the optimizer guesses most pages in the table may be hit, so often ignores the indexes. Now I'm not saying that a more advanced statistics system couldn't one-day be written that sees these patterns in the data -- but it doesn't seem likely in the near term. DBA-based hints could be a useful interim work-around. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
Tom Lane [EMAIL PROTECTED] writes: Robert Treat [EMAIL PROTECTED] writes: Anything that can be done to wheedle down your choices before you have to run EXPLAIN ANALYZE is a bonus. Fair enough, but I prefer Peter's suggestion of attaching the hypothetical index definitions to EXPLAIN itself, rather than making bogus catalog entries. Something along the line of While I do like avoiding the bogus catalog entries and attaching the declarations to the explain plan. One advantage of that is that I can see extending it to handling IGNORING INDEX foo as well which may be just as important. One disadvantage is that it doesn't let you gather any statistics related to the new index to see what the plan would really be. But indexes don't influence statistics I can hear already from the chorus. But the reason we have indexes not affecting planning is precisely because we don't want to require an analyze after creating an index before it's used. Which these bogus entries would resolve. If we had the ability to create bogus indexes it would kill two birds with one stone. You could use that as the facility for noting which multi-column combinations are interesting. You would create your proposed index, then run ANALYZE and EXPLAIN to your heart's content. When you have it set up just so then you REINDEX your index and you're set. We already have these bogus indexes incidentally, we just create the index with indisvalid=f. -- Gregory Stark EnterpriseDB http://www.enterprisedb.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] hstore isexists
Jim C. Nasby wrote: On Wed, Oct 11, 2006 at 05:00:50PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Sorry, I don't know the history of hstore... but if it's brand new, why are we worried about backwards compatibility? Because it's been available for awhile outside of contrib (namely, on Oleg and Teodor's own site). So there are people using it. So wouldn't it make sense to document that the old functions are depricated so those existing users will stop using them? Maybe the base documentation isn't the best place for that... perhaps an UPGRADING section. The source code should mention it --- that's all. -- Bruce Momjian [EMAIL PROTECTED] 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] [GENERAL] strange error when inserting via a SRF into a table with a foreign key constraint
Merlin Moncure [EMAIL PROTECTED] writes: I got the error mesage, ERROR: could not find relation 19693 among query result relations, Oooh, that's an interesting one. The stack trace from the error call is #0 errfinish (dummy=0) at elog.c:313 #1 0x356584 in elog_finish (elevel=1073815392, fmt=0xcc130 could not find relation %u among query result relations) at elog.c:939 #2 0x1ef59c in afterTriggerInvokeEvents (events=0x400ec508, firing_id=0, estate=0x40109be8, delete_ok=1 '\001') at trigger.c:2331 #3 0x1ef9dc in AfterTriggerEndQuery (estate=0x40109be8) at trigger.c:2556 #4 0x211878 in postquel_end (es=0x40105e18) at functions.c:404 #5 0x211a38 in postquel_execute (es=0x40105e18, fcinfo=0x7b03ba40, fcache=0x401049b8, resultcontext=0x4007c4e0) at functions.c:479 #6 0x211c68 in fmgr_sql (fcinfo=0x7b03ba40) at functions.c:639 #7 0x2097b4 in ExecMakeFunctionResult (fcache=0x40104280, econtext=0x40104108, isNull=0x40104932 , isDone=0x401049a0) at execQual.c:1057 ... What the heck is it doing trying to fire triggers from inside the SQL function, which is merely doing a SELECT? It looks to me like we need to rethink where the AfterTriggerBeginQuery and AfterTriggerEndQuery calls are in functions.c. I think what is happening is that control returns from the SQL function after obtaining its first result row, with the trigger stack still at level one (inside the function), and so the first AFTER INSERT event gets queued as being something inside the function. 8.0 has the same bug although the manifestation is different. Not sure about 7.4 --- this test case doesn't work for lack of generate_series. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Repair incorrect check for coercion
Tom Lane wrote: Log Message: --- Repair incorrect check for coercion of unknown literal to ANYARRAY, a bug I introduced in 7.4.1 :-(. It's correct to allow unknown to be coerced to ANY or ANYELEMENT, since it's a real-enough data type, but it most certainly isn't an array datatype. This can cause a backend crash but AFAICT is not exploitable as a security hole. Per report from Michael Fuhr. Note: as fixed in HEAD, this changes a constant in the pg_stats view, resulting in a change in the expected regression outputs. The back-branch patches have been hacked to avoid that, so that pre-existing installations won't start failing their regression tests. Does this mean if someone initdb's in a back branch, the regression tests will start failing for them? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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] On status data and summaries
Funny, sounds like what I usually do. I welcome the assistance. --- Andrew Sullivan wrote: Hello, In a possible moment of insanity, in http://archives.postgresql.org/pgsql-hackers/2006-09/msg00579.php I volunteered to try to help solve a problem Tom Lane noted: The hard part of this problem is finding a convenient way to capture status data out of the community's conversations. I observed that companies who do this well actually employ people to do that sort of thing, and that this might be a way for code morons like yours truly to make a contribution to development. I've been struggling since then, trying to figure out where to start. There are a _lot_ of discussions on -hackers, and many of them are blind alleys. Moreover, I can't summarise everything, I don't think, and still make any of those summaries sufficiently detailed to allow them to be useful. So I have a proposal. I was thinking of tracking 3 or 4 such discussions in the next release cycle, as a kind of proof of concept. I'm willing to do that, but I'd need guidance from those who are trying to produce a complicated feature, telling me that they need the support. Therefore, if someone involved in some such discussion pokes me saying, Follow this thread, please, I'll follow the thread in question (as well as follow-up discussions that come of it), and produce regular (weekly?) summaries of what I take to be the state of the collective mind, until such time as the code supporting the feature is checked in and agreed to. Then, at release time, the developers can evaluate whether the tracking produced few surprises at the end (and, perhaps, less thrash), or whether the experiment did not provide any benefit. If it does, we can see whether we can make this sort of thing scale by adding some additional volunteers to do a similar job in future. Does that seem worth doing? A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(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 -- Bruce Momjian [EMAIL PROTECTED] 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] Index Tuning Features [2]
Hi, Am 11.10.2006 um 19:39 schrieb Simon Riggs: I'm sure everybody would be glad to see the existing work submitted as a Work-in-Progress patch to pgsql-patches. Would a patch against a clean 7.4.8 source tree useful for you? Otherwise, I had to spend some time to migrate the code to 8.2... Best, Kai ---(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
[HACKERS] postgres database crashed
We have a following table xyz( id int , fname varchar(50), img image)where image is a data type we have created similar to lo.so when we executed an insert query on the following table :-insert into xyz VALUES (2541, '/home/ravi/jdbc/2_down/76.jpg', '76.jpg');It works well for nearly 2500 images but from there onwards we obtained the following error and the database crashes.this is the bt we obtained on using gdb with the postgres process#0 0x00313bbc in memcpy () from /lib/libc.so.6#1 0x0819496d in datumCopy ()#2 0x0812d202 in copyObject ()#3 0x08151c49 in eval_const_expressions_mutator ()#4 0x08150c2f in expression_tree_mutator ()#5 0x08151c49 in eval_const_expressions_mutator ()#6 0x08150ece in expression_tree_mutator ()#7 0x08151c49 in eval_const_expressions_mutator ()#8 0x08152818 in eval_const_expressions ()#9 0x081487d8 in preprocess_expression ()#10 0x08149a04 in subquery_planner ()#11 0x08149e56 in planner ()#12 0x0817c25c in pg_plan_query ()#13 0x0817c8d2 in pg_plan_queries ()#14 0x0817e135 in PostgresMain ()#15 0x0815b6e5 in ServerLoop ()#16 0x0815c459 in PostmasterMain ()#17 0x08128f48 in main ()this is the LOG obtainedLOG: server process (PID 2499) was terminated by signal 11(gdb) LOG: terminating any other active server processesLOG: all server processes terminated; reinitializingLOG: database system was interrupted at 2006-10-10 23:40:05 ISTLOG: checkpoint record is at 0/14B37B98LOG: redo record is at 0/14B37B98; undo record is at 0/0; shutdown FALSELOG: next transaction ID: 210546; next OID: 851968; next MultiXactId: 1LOG: database system was not properly shut down; automatic recovery in progressFATAL: the database system is starting upLOG: record with zero length at 0/14B37BD8LOG: redo is not requiredLOG: database system is readyLOG: transaction ID wrap limit is 1073952152, limited by database "benchmark"Can somebody suggest us what might be the cause of error and what can we do to resolve it ? __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[HACKERS] Database Auditing
I'm evaluating of use postgresql but for local law requirements is needed for the access of some kind of data (sensitive) a log of the accesses (Auditing) is a feature available in many databases but i've seen that lacks in PostgreSQL, there are already plans to implement it or patches already submitted ? If not both could someone give me some hints on how do it and where attach the code like open the file in postmaster.c intercept the query in that function etc. Thank you in advance :) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Hints WAS: Index Tuning Features
Mark, First off, I'm going to request that you (and other people) stop hijacking Simon's thread on hypothetical indexes. Hijacking threads is an effective way to get your ideas rejected out of hand, just because the people whose thread you hijacked are angry with you. So please observe the thread split, thanks. Well, if it would get rejected if it looked like Oracle, assuming you would probably be one of the people rejecting it, what do you envision as not being rejected? Something better than Oracle. Since you're the one who wants hints, that's kind of up to you to define. Write a specification and make a proposal. What is the point of writing a proposal if there is a threat of will be rejected if one of the people who would do the rejection doesn't at least outline what would be acceptable? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hints WAS: Index Tuning Features
Since you're the one who wants hints, that's kind of up to you to define. Write a specification and make a proposal. What is the point of writing a proposal if there is a threat of will be rejected if one of the people who would do the rejection doesn't at least outline what would be acceptable? Oh come on Mark, you have been here long enough to know how this works. You define what you would like to see and submit it for feedback. -Hackers submit feedback, you refine and the cyle continues till either -hackers determine it just isn't going to happen (packages), the would be hacker gives up, or a workable plan comes out of the discussion. Joshua D. Drake ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- === 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] Index Tuning Features
Greg Stark [EMAIL PROTECTED] writes: You would create your proposed index, then run ANALYZE and EXPLAIN to your heart's content. When you have it set up just so then you REINDEX your index and you're set. And when you realize you don't want it after all ... you need an exclusive lock on the table to drop it. (Yes, you would, see relcache load.) The advantage of keeping this idea all inside EXPLAIN is that there's guaranteed to be no interference with anything else. We already have these bogus indexes incidentally, we just create the index with indisvalid=f. Au contraire, that is something completely different. indisvalid=f is really the exact opposite: it's not there to the planner and it is there to the executor. As for the statistics business: really, we use the presence of an index as a hint to gather certain kinds of stats about its underlying table. If we had (ahem) statistical hints then we could gather appropriate data with or without a real associated index. That sort of feature would have additional uses, ie, being able to estimate selectivities more accurately for expressions that might not have anything to do with any of the indexes on a table. 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] [COMMITTERS] pgsql: Repair incorrect check for coercion of unknown literal to
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Note: as fixed in HEAD, this changes a constant in the pg_stats view, resulting in a change in the expected regression outputs. The back-branch patches have been hacked to avoid that, so that pre-existing installations won't start failing their regression tests. Does this mean if someone initdb's in a back branch, the regression tests will start failing for them? No. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] postgres database crashed
Ashish Goel [EMAIL PROTECTED] writes: We have a following table xyz( id int , fname varchar(50), img image) where image is a data type we have created similar to lo. ... Can somebody suggest us what might be the cause of error and what can we do to resolve it ? Incorrect code in your custom datatype, almost certainly. Check computations of memory size allocations, for example. Test it in a backend compiled with --enable-cassert. regards, tom lane ---(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] Hints WAS: Index Tuning Features
Since you're the one who wants hints, that's kind of up to you to define. Write a specification and make a proposal. What is the point of writing a proposal if there is a threat of will be rejected if one of the people who would do the rejection doesn't at least outline what would be acceptable? Oh come on Mark, you have been here long enough to know how this works. Exactly. IMHO, it is a frustrating environment. PostgreSQL is a great system, and while I completely respect the individuals involved, I think the management for lack of a better term, is difficult. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hints WAS: Index Tuning Features
Mark Woodward wrote: Since you're the one who wants hints, that's kind of up to you to define. Write a specification and make a proposal. What is the point of writing a proposal if there is a threat of will be rejected if one of the people who would do the rejection doesn't at least outline what would be acceptable? Oh come on Mark, you have been here long enough to know how this works. Exactly. IMHO, it is a frustrating environment. PostgreSQL is a great system, and while I completely respect the individuals involved, I think the management for lack of a better term, is difficult. Well that is the nature of FOSS development. If you think we are bad.. I could easily list half a dozen that are worse ;) A couple of the much larger then us. Joshua D. Drake ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- === 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Database Auditing
On 10/11/06, Marco Serantoni [EMAIL PROTECTED] wrote: I'm evaluating of use postgresql but for local law requirements is needed for the access of some kind of data (sensitive) a log of the accesses (Auditing) is a feature available in many databases but i've seen that lacks in PostgreSQL, there are already plans to implement it or patches already submitted ? If not both could someone give me some hints on how do it and where attach the code like open the file in postmaster.c intercept the query in that function etc. Thank you in advance :) we can probably come up with something. can you please give specific requirements about what type of information you have to keep track of? merlin ---(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] Hints WAS: Index Tuning Features
Clinging to sanity, [EMAIL PROTECTED] (Mark Woodward) mumbled into her beard: Mark, First off, I'm going to request that you (and other people) stop hijacking Simon's thread on hypothetical indexes. Hijacking threads is an effective way to get your ideas rejected out of hand, just because the people whose thread you hijacked are angry with you. So please observe the thread split, thanks. Well, if it would get rejected if it looked like Oracle, assuming you would probably be one of the people rejecting it, what do you envision as not being rejected? Something better than Oracle. Since you're the one who wants hints, that's kind of up to you to define. Write a specification and make a proposal. What is the point of writing a proposal if there is a threat of will be rejected if one of the people who would do the rejection doesn't at least outline what would be acceptable? If your proposal is merely let's do something like Oracle, it should be obvious why that would be rejected. There is considerable legal danger to slavish emulation. Further, since PostgreSQL isn't Oracle, slavish emulation wouldn't work anyways. If a proposal is too fuzzy to be considered a source of a specification, it should be obvious that that would be rejected. If you have an idea clear enough to turn into a meaningful proposal, put it in for the usual to and fro; that generally leads to enormous improvements. I'm not sure what a good hinting system ought to look like; what I *do* know is that a fuzzy proposal won't be much good. -- (format nil [EMAIL PROTECTED] cbbrowne acm.org) http://linuxfinances.info/info/postgresql.html The quickest way to a man's heart is through his chest, with an axe. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Subject: problem with using O_DIRECT
I tried to use O_DIRECT on Linux (SuSe) Kernel 2.6, but failed to make it run. For example, if I added the option in the open of BasicOpenFile(), I got the following error after typing psql -l, psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Any advice? Thanks, Brian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Hints WAS: Index Tuning Features
Mark Woodward [EMAIL PROTECTED] writes: What is the point of writing a proposal if there is a threat of will be rejected if one of the people who would do the rejection doesn't at least outline what would be acceptable? FWIW, I said some things about what I'd consider a good design in that other hints thread on pgsql-performance. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
