Re: [HACKERS] Index Tuning Features

* Andrew Sullivan: > 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? Some statistics are ve

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

[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 accep

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, >> j

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

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 do

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 >>

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

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 s

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 th

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

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 ob

[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

[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 bu

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..

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 > > > > I

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 datat

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=107381539

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 a

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 t

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

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 S

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 version

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.o

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-no

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

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 ... -

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

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 > > > > I volunteered to try to help solve a problem Tom Lane noted: "The > hard part of this problem is finding

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)---

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.

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. Wi

[HACKERS] On status data and summaries

Hello, In a possible moment of insanity, in 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."

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

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 depre

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

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 m

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.

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, whos

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 h

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 ma

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 wh

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 p

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

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

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     

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 (crea

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

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 C

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 -- th

[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

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 where  a.attrelid =c.oid andc.relname='TableName' and a.attna

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] 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 a

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.htm

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 mail

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

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:

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 { $$

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 datab

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

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: [EM

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

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

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

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

[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?

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

[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 pl

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

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 "prio

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 capabil

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

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 sett

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

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 b

[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 timeou