Re: [HACKERS] Some array semantics issues

Tom Lane wrote: Given the just-committed changes to avoid having array_push/array_cat generate non-spec lower bounds unnecessarily, do you still think it's important to have a variant of array comparison that ignores lower bounds? ISTM that ignoring lower bounds is definitely something that viol

Re: [HACKERS] order by, for custom types

Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes: > Wildcards cause things not to work as they should > consider everything in [] brackets to be a possible choice and those > three: > a = 1.2.3.4 > b = 1.[2,3].3.4 > c = 1.3.3.4 > a = b, b = c, but a <> c, I was told that because of that btree wo

Re: [HACKERS] someone working to add merge?

Peter Eisentraut wrote: > Bruce Momjian wrote: > > I agree --- an implementation that needs to use a table lock is > > useless, and one with no primary key is too hard to implement and > > also near useless. > > Well, there were just a couple of people saying the opposite. > > > I have update the

Re: [HACKERS] Some array semantics issues

Greg Stark <[EMAIL PROTECTED]> writes: > [ this is back up-thread a bit ] > And changing that would make it harder to test just the contents of the array > without having to match bounds as well. That is, You couldn't say "list = > '{1,2}'" to test if the array contained 1,2. You would have to, wel

Re: [HACKERS] order by, for custom types

On 2005-11-18, at 22:53, Tom Lane wrote: Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes: I have all operators required for b-tree, gist up here, and gist index defined. But still "order by custom_type" won't work. Define "won't work" ... what happens? Wildcards cause things not to work as

Re: [HACKERS] Improving count(*)

Alvaro, > I guess there must be a query-rewriting mechanism for implementing > materialized views.  With that in place we may be able to implement this > other thing ...  Is anybody working on materialized views? I have a bundle of academic code designed to do exactly this, if any hacker wants t

Re: [HACKERS] Improving count(*)

On Fri, Nov 18, 2005 at 03:46:42PM +, Richard Huxton wrote: > Simon Riggs wrote: > >One of the major complaints is always "Select count(*) is slow". > Although there seem to have been plenty of ideas on this they all seem > to just provide a solution for the "whole table" case. It might be tha

Re: [HACKERS] Optimizer bug in 8.1.0?

Alexey Slynko <[EMAIL PROTECTED]> writes: > Any suggestions? Fix contrib/intarray to have some selectivity estimation procedures for its operators? Without any way to estimate the number of rows matching the @@ condition, the optimizer can hardly be expected to guess right...

Re: [HACKERS] PCTFree Results

Josh, Do you have an 8.1 patch for this or only the 8.0.x? On 9/22/05, Josh Berkus wrote: Folks,Well, it took a while but I finally have the results of Satoshi's PCTFreepatch back from the STP.  Bad news about the STP, see below ...Anyway, a series of DBT2 runs doesn't seem to

Re: [HACKERS] order by, for custom types

Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes: > I have all operators required for b-tree, gist up here, and gist > index defined. But still "order by custom_type" won't work. Define "won't work" ... what happens? > I have kind of wild card masks in my type definition, so b-tree won't > work

Re: [HACKERS] Bug in predicate indexes?

This is a known (although perhaps not well documented) limitation of the predicate testing logic. You do not need a cast in the query, though, only in the index's WHERE condition. O.k. cool just wanted to make sure I wasn't the only one :). Joshua D. Drake regar

Re: [HACKERS] Improving count(*)

Tom Lane wrote: > Richard Huxton writes: > > Might it be possible to apply rule-style rewriting to a clause of an > > ordinary select query? That is, is it prohibitively expensive to get PG > > to recognise > >SELECT count(*) FROM big_table > > and replace it with > >SELECT sum(summary_c

Re: [HACKERS] someone working to add merge?

On Fri, Nov 18, 2005 at 05:30:34PM +0100, Peter Eisentraut wrote: > Bruce Momjian wrote: > > I have update the TODO item to reflect this: > > > > * Add MERGE command that does UPDATE/DELETE, or on failure, INSERT > > (rules, triggers?) > > > > To implement this cleanly requires that

Re: [HACKERS] order by, for custom types

Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes: > Hi folks > > I have all operators required for b-tree, gist up here, and gist index > defined. But still "order by custom_type" won't work. I think you need to create an "operator class" for ORDER BY to work. Someone else may answer with more de

Re: [HACKERS] Improving count(*)

On 11/18/05, Merlin Moncure <[EMAIL PROTECTED]> wrote: > > In Sybase ASE (and I'm pretty sure the same is true in Microsoft SQL > > Server) the leaf level of the narrowest index on the table is scanned, > > following a linked list of leaf pages. Leaf pages can be pretty dense > > under Sybase, bec

Re: [HACKERS] Improving count(*)

Merlin Moncure wrote: > > In Sybase ASE (and I'm pretty sure the same is true in Microsoft SQL > > Server) the leaf level of the narrowest index on the table is scanned, > > following a linked list of leaf pages. Leaf pages can be pretty dense > > under Sybase, because they do use prefix compressi

Re: [HACKERS] Improving count(*)

> In Sybase ASE (and I'm pretty sure the same is true in Microsoft SQL > Server) the leaf level of the narrowest index on the table is scanned, > following a linked list of leaf pages. Leaf pages can be pretty dense > under Sybase, because they do use prefix compression. A count(*) > on a table w

Re: [HACKERS] CLUSTER and clustered indices

That sounds very much like a CLUSTERED INDEX under Sybase ASE (or the derivative Microsoft SQL Server). In those products, when you create a clustered index, the data pages are sorted according to the index sequence, and are used as the leaf pages in the index. A clustered index does not have ano

Re: [HACKERS] order by, for custom types

On Fri, 18 Nov 2005, Grzegorz Jaskiewicz wrote: Hi folks I have all operators required for b-tree, gist up here, and gist index defined. But still "order by custom_type" won't work. I have kind of wild card masks in my type definition, so b-tree won't work. But still, do I need to define b-tr

[HACKERS] order by, for custom types

Hi folks I have all operators required for b-tree, gist up here, and gist index defined. But still "order by custom_type" won't work. I have kind of wild card masks in my type definition, so b-tree won't work. But still, do I need to define b-tree index as such for "order by" to work ? Perh

Re: [HACKERS] Improving count(*)

Richard Huxton writes: > Might it be possible to apply rule-style rewriting to a clause of an > ordinary select query? That is, is it prohibitively expensive to get PG > to recognise >SELECT count(*) FROM big_table > and replace it with >SELECT sum(summary_count) FROM my_materialised_vie

Re: [HACKERS] someone working to add merge?

Bruce Momjian wrote: > I agree --- an implementation that needs to use a table lock is > useless, and one with no primary key is too hard to implement and > also near useless. Well, there were just a couple of people saying the opposite. > I have update the TODO item to reflect this: > > *

Re: [HACKERS] MERGE vs REPLACE

Bruce Momjian writes: > Oh, good point. I was thinking just about concurrent MERGEs. However, > it is more complicated than that. By definitaion you can not see > changes from other transactions while your statement is being run (even > if you increment CommandCounter), so to be atomic, you wou

Re: [HACKERS] Improving count(*)

Simon Riggs wrote: One of the major complaints is always "Select count(*) is slow". Although there seem to have been plenty of ideas on this they all seem to just provide a solution for the "whole table" case. It might be that the solution provides other benefits, but for this one case it doe

[HACKERS] Optimizer bug in 8.1.0?

Hi, I have database with two tables: test1=# \d messages Table "public.messages" Column | Type| Modifiers --+---+--- msg_id | integer | not null sections | integer[] | Indexes: "messages_pkey" PRIMARY KEY, btree (msg_id) "messages_sect_idx" gist (sec

Re: [HACKERS] Anyone want to fix plperl for null array elements?

Michael Fuhr wrote: On Thu, Nov 17, 2005 at 08:41:51PM -0500, Tom Lane wrote: I think plperl should be fixed to translate undef to NULL when returning an array, but currently it translates to an empty string: I'll take a look at this if nobody else steps up. It might just be a mino

Re: [HACKERS] Bug in predicate indexes?

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > It appears that predicate indexes won't uses indexes on int8 columns > unless they are casted: This is a known (although perhaps not well documented) limitation of the predicate testing logic. You do not need a cast in the query, though, only in th

Re: [HACKERS] Optional postgres database not so optional in 8.1

Andrew Dunstan <[EMAIL PROTECTED]> writes: > I now notice that "pg_ctl -w start" fails if the postgres db is missing. > I am not sure that changing pg_ctl to use this rather than template1 was > a good thing, and it can't be overridden. I suggest we revert that > particular change - it seems to

Re: [HACKERS] MERGE vs REPLACE

Oh, good point. I was thinking just about concurrent MERGEs. However, it is more complicated than that. By definitaion you can not see changes from other transactions while your statement is being run (even if you increment CommandCounter), so to be atomic, you would still see the row even thou

Re: [HACKERS] Optional postgres database not so optional in 8.1

Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: I now notice that "pg_ctl -w start" fails if the postgres db is missing. I am not sure that changing pg_ctl to use this rather than template1 was a good thing, and it can't be overridden. I suggest we revert that particular chang

Re: [HACKERS] [pgsql-advocacy] Call for sample databases

Thanks for the clarification. I suspected as much. :( ___ Javier Soltero Hyperic | www.hyperic.net o- 415 738 2566 | c- 415 305 8733 [EMAIL PROTECTED] ___ On Nov 17, 2005, at 6:06 PM, Christopher Kings-Lynne wrote

Re: [HACKERS] [pgsql-advocacy] Call for sample databases

Hi Chris, Hyperic would be happy to donate an exported version of our pg database for a fairly loaded environment. Just so I'm clear, there's no implication of making the schema itself open source or anything like that, right? We're perfectly cool with letting people look at it (they can

Re: [HACKERS] Optional postgres database not so optional in 8.1

I now notice that "pg_ctl -w start" fails if the postgres db is missing. I am not sure that changing pg_ctl to use this rather than template1 was a good thing, and it can't be overridden. I suggest we revert that particular change - it seems to me to confer little to no benefit, unlike the ca

Re: [HACKERS] Improving count(*)

Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > >>From here, another proposal. We have a GUC called count_uses_estimate >>that is set to off by default. If set to true, then a count(*) will use >>the planner logic to estimate number of rows in the table and return >>that as the answer,

Re: [HACKERS] Improving count(*)

Zeugswetter Andreas DCP SD schrieb: Since that costs, I guess I would make it optional and combine it with materialized views that are automatically used at runtime, and can at the same time answer other aggregates or aggregates for groups. create materialized view xx_agg enable query r

Re: [HACKERS] Improving count(*)

> > Since that costs, I guess I would make it optional and combine it with > > materialized views that are automatically used at runtime, and can at > > the same time answer other aggregates or aggregates for groups. > > create materialized view xx_agg enable query rewrite as select > > count(*

Re: [HACKERS] MERGE vs REPLACE

> Unless you have a table lock, INSERT has to be before UPDATE, think UPDATE, UPDATE (both fail), INSERT, INSERT. > > update > > if no rows updated > > insert > > if duplicate key > > update > > if no rows updated goto insert That is why you have the loop. This is not a problem with

Re: [HACKERS] Improving count(*)

Zeugswetter Andreas DCP SD schrieb: The instant someone touches a block it would no longer be marked as frozen (vacuum or analyze or other is not required) and count(*) would visit the tuples in the block making the correct decision at that time. Hmm, so the idea would be that if a block

Re: [HACKERS] Improving count(*)

> > The instant someone touches a block it would no longer be marked as > > frozen (vacuum or analyze or other is not required) and count(*) would > > visit the tuples in the block making the correct decision at that time. > > Hmm, so the idea would be that if a block no longer contained any tu

[HACKERS] delete trigger

Hello, I want to create a trigger in PostgresSQL In trigger, Before inserting the record, if data is already in the table, the trigger fire the mesaage that data is already there, and after that trigger ckeck for next insert statement. Pleae help me in this regard , kindly  reply. Regards,

Re: [HACKERS] CLUSTER and clustered indices

On Thu, 2005-11-17 at 21:57 -0300, Alvaro Herrera wrote: > Personally I'd prefer to see index-ordered heaps, where the heap is > itself an index, so the ordering it automatically kept. Agreed. (I think thats case-closed on the previous proposal.) As an aside, Index Organized Tables (IOTs) isn't