Re: [HACKERS] LIKE, leading percent, bind parameters and indexes

"Tom Lane" <[EMAIL PROTECTED]> wrote > > Yeah. The LIKE index optimization depends on seeing a constant LIKE > pattern at plan time --- otherwise the planner doesn't know what > indexscan parameters to generate. So a bound-parameter query loses. > AFAICS the problem is not restricted to LIKE, w

Re: [HACKERS] Performance Issues

Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> Thank you for your help. I found that an implicit index is created for >> the primary key in the current version. However, it is not done in 7.x >> version. > It absolutely is created in all 7.x versions of PostgreSQL. And every other versi

Re: [HACKERS] Performance Issues

Thank you for your help. I found that an implicit index is created for the primary key in the current version. However, it is not done in 7.x version. It absolutely is created in all 7.x versions of PostgreSQL. ---(end of broadcast)--- TIP 1: if

Re: [HACKERS] Performance Issues

Thank you for your help. I found that an implicit index is created for the primary key in the current version. However, it is not done in 7.x version. Mark Woodward wrote: Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, q

Re: [HACKERS] Improving ALTER TYPE support

Makes more sense to do that, and I think it'll be cleaner to implement as well.On 5/23/06, Josh Berkus wrote:John,> I've been working on a function which returns a setof a composite type. > Everytime I've changed the structure of the returning setof, I've had to> change the typ

Re: [HACKERS] Why is CVS server so slow?

Michael Fuhr <[EMAIL PROTECTED]> writes: > On Tue, May 23, 2006 at 08:30:36PM -0300, Marc G. Fournier wrote: >> I'm logged on daily interactively, and haven't noticed any issues ... > Are you both referring to the same server? I've noticed that > anoncvs.postgresql.org (66.98.251.159) has been s

Re: [HACKERS] LIKE, leading percent, bind parameters and indexes

"Rodrigo Hjort" <[EMAIL PROTECTED]> writes: > What happens is that only the "004" block uses the index! The "002" code, > which also has no leading percent, does a sequential scan. The difference > between them is that "002" uses bind parameters. Yeah. The LIKE index optimization depends on seein

Re: [HACKERS] Why is CVS server so slow?

Michael, > Are you both referring to the same server? I've noticed that > anoncvs.postgresql.org (66.98.251.159) has been slow for a couple > of days -- it just took over five minutes to do a cvs update of > HEAD where it usually takes thirty seconds or less. Marc's been building the 8.1.4 et. a

Re: [HACKERS] Improving ALTER TYPE support

John, > I've been working on a function which returns a setof a composite type. > Everytime I've changed the structure of the returning setof, I've had to > change the type accordingly, which current means doing a drop type ... > cascade down to the function. We should allow one of the following:

[HACKERS] Improving ALTER TYPE support

I've been working on a function which returns a setof a composite type. Everytime I've changed the structure of the returning setof, I've had to change the type accordingly, which current means doing a drop type ... cascade down to the function. We should allow one of the following: 1) Add a REPLAC

Re: [HACKERS] Why is CVS server so slow?

On Tue, May 23, 2006 at 08:30:36PM -0300, Marc G. Fournier wrote: > On Tue, 23 May 2006, Simon Riggs wrote: > >The last few days the CVS server seems to be much slower than it used to > >be. No network changes here. Anything changed server side, or should I > >ask elsewhere? > > I'm logged on dail

Re: [HACKERS] SQL compliant interval implementation

On May 24, 2006, at 7:37 , Brendan Jurd wrote: I've been searching through the archives for discussions relating to intervals, but haven't come across the one you're describing. Most probably because there have been a LOT of discussions relating to intervals. I don't have links to the thread

Re: [HACKERS] Porting MSSQL to PGSQL -- triggers

Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: How expensive is this going to be, especially for huge numbers of rows? Certainly cheaper than firing a per-row trigger. I'm curious: I've never written a MSSQL trigger that did NOT use the INSERTED/DELETED pseudotables (aka NEW/OLD

Re: [HACKERS] Why is CVS server so slow?

On Tue, 23 May 2006, Simon Riggs wrote: The last few days the CVS server seems to be much slower than it used to be. No network changes here. Anything changed server side, or should I ask elsewhere? I'm logged on daily interactively, and haven't noticed any issues ... Bruce has, in the past,

Re: [HACKERS] SQL compliant interval implementation

Brendan, > Could you elaborate on how it sucked? Apart from the issue of > daylight savings which Tom has mentioned, what are these limitations > that needed to be worked around? Well, actually, the DST thing was pretty severe -- it made timestamptz unusable. That's why we partitioned interval

[HACKERS] LIKE, leading percent, bind parameters and indexes

PG-Hackers,I got the following picture:detran=# \d sa_dut.tb_usuario    Table "sa_dut.tb_usuario" Column  |    Type | Modifiers-+-+---  numprocesso | bigint   

Re: [HACKERS] SQL compliant interval implementation

Brendan Jurd wrote: > On 5/24/06, Josh Berkus wrote: > > Brendan, > > > > > There are two classes of intervals. One class, called year-month > > > intervals, has an express or implied datetime precision that includes > > > no fields other than YEAR and MONTH, though not both are required. The > >

Re: [HACKERS] SQL compliant interval implementation

On 5/24/06, Josh Berkus wrote: Brendan, > There are two classes of intervals. One class, called year-month > intervals, has an express or implied datetime precision that includes > no fields other than YEAR and MONTH, though not both are required. The > other class, called day-time intervals, h

Re: [HACKERS] SQL compliant interval implementation

Brendan, > There are two classes of intervals. One class, called year-month > intervals, has an express or implied datetime precision that includes > no fields other than YEAR and MONTH, though not both are required. The > other class, called day-time intervals, has an express or implied > interva

Re: [HACKERS] SQL compliant interval implementation

"Brendan Jurd" <[EMAIL PROTECTED]> writes: > I've been looking at the postgres interval implementation lately, and > I'm interested in putting together an improved implementation that > accords more closely with the SQL specification, in particular with: Appealing to the SQL spec isn't going to ta

Re: [HACKERS] SQL compliant interval implementation

I don't see how this makese our system any better than it does not. It just seems to eliminate the 30-day problem by not allowing it. That doesn't seem to be a step forward. --- Brendan Jurd wrote: > Hi all, > > I've been

[HACKERS] SQL compliant interval implementation

Hi all, I've been looking at the postgres interval implementation lately, and I'm interested in putting together an improved implementation that accords more closely with the SQL specification, in particular with: --- 4.6.2 Intervals There are two classes of intervals. One class, called year-mo

Re: [HACKERS] error-free disabling of individual child partition

Added to TODO: o Add ALTER TABLE tab ADD/DROP INHERITS parent pg_attribute.attislocal has to be set to 'false' for ADD, and pg_attribute.attinhcount adjusted appropriately --- Simon Riggs wrote

Re: [HACKERS] API changes in patch release

Magnus Hagander wrote: I don't think any of us realized the change would affect third-party projects. To help specifically PL/Java next time, is there any chance to get it included in the buildfarm builds? If it had been there, it would've been caught right away... Currently buildf

Re: [HACKERS] API changes in patch release

> > This change will force me to a) introduce patch level sensitive > > conditionals in the code, and b) have two PostgreSQL 8.1.n > compatible > > releases of PL/Java. One where n < 4 and another where n >= > 4. I would > > like to avoid this in the future if possible. API's should remain >

Re: [HACKERS] [GENERAL] Weird ..... (a=1 or a=2) <> (a=2 or a=1)

"Merlin Moncure" <[EMAIL PROTECTED]> writes: > On 5/19/06, Tom Lane <[EMAIL PROTECTED]> wrote: >> This only affects the 7.4 and 8.0 branches, because earlier and later >> versions of Postgres don't use this technique for detecting duplicates. >> But it's surprising we didn't find it before. > hm.

Re: [HACKERS] API changes in patch release

Thomas Hallgren wrote: > The world is not perfect and I know that you are normally very > restrictive in what is back-patched from head into bug-fix branches. The > 8.1.4 release however, did introduce a problem. You changed the API > function inv_open() with the comment "Revise large-object acc

Re: [HACKERS] Update on sort-compression stuff

On Tue, 2006-05-23 at 14:27 -0400, Tom Lane wrote: > Martijn van Oosterhout writes: > > - Test a way of storing tuples with less overhead than a HeapTuple > > header. If you could do it for in-memory sorts, that'd mean you could > > fit more tuples in memory before spilling to disk. Given the > >

Re: [HACKERS] [GENERAL] Weird ..... (a=1 or a=2) <> (a=2 or a=1)

On 5/19/06, Tom Lane <[EMAIL PROTECTED]> wrote: This only affects the 7.4 and 8.0 branches, because earlier and later versions of Postgres don't use this technique for detecting duplicates. But it's surprising we didn't find it before. hm. about a year ago I reported a case where the database a

Re: [HACKERS] Update on sort-compression stuff

Martijn van Oosterhout writes: > - Test a way of storing tuples with less overhead than a HeapTuple > header. If you could do it for in-memory sorts, that'd mean you could > fit more tuples in memory before spilling to disk. Given the > "compression" in that case is extremely cheap, it'd be much m

Re: [HACKERS] [GENERAL] autovacuum "connections" are hidden

On Mon, May 22, 2006 at 02:45:30PM -0700, Casey Duncan wrote: > > On May 22, 2006, at 2:37 PM, Alvaro Herrera wrote: > > >Jim C. Nasby wrote: > >>Moving to -hackers > > > >You forgot to actually do it apparently? Yup, I are SMRT. > >Sorry about posting the patch to -general, BTW. Anyway it was

Re: [HACKERS] file-locking and postmaster.pid

On Tue, May 23, 2006 at 01:36:41PM -0400, Tom Lane wrote: > This is exactly what you should NOT do. > > A start script that thinks it is smarter than the postmaster is almost > certainly wrong. It is certainly dangerous, too, because auto-deleting > that pidfile destroys the interlock against hav

Re: [HACKERS] error-free disabling of individual child partition

On Tue, 2006-05-23 at 13:17 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > - ADD/DROP are opposites; you can use the other one to undo an action > > taken in haste, error etc > > It's not going to be that easy. What exactly will happen to the child > table's attislocal/attin

Re: [HACKERS] file-locking and postmaster.pid

Adis Nezirovic <[EMAIL PROTECTED]> writes: > Well, maybe you could tweak postgres startup script, add check for post > master (either 'pgrep postmaster' or 'ps -axu | grep [p]ostmaster'), and > delete pid file on negative results. This is exactly what you should NOT do. A start script that thinks

Re: [HACKERS] error-free disabling of individual child partition

Simon Riggs <[EMAIL PROTECTED]> writes: > - ADD/DROP are opposites; you can use the other one to undo an action > taken in haste, error etc It's not going to be that easy. What exactly will happen to the child table's attislocal/attinhcount settings, and why, during ADD or DROP?

Re: [HACKERS] file-locking and postmaster.pid

On Tue, May 23, 2006 at 05:23:16PM +0200, Andreas Joseph Krogh wrote: > Hi all. > > I've experienced several times that PG has died somehow and the > postmaster.pid > file still exists 'cause PG hasn't had the ability to delete it upon proper > shutdown. Upon start-up, after such an incidence,

Re: [HACKERS] Performance Issues

> Dhanaraj M wrote: >> I have the following doubts. >> >> 1. Does postgres create an index on every primary key? Usually, queries >> are performed against a table on the primary key, so, an index on it >> will be very useful. > > Yes, a unique index is used to enforce the primary-key. Well, here

Re: [HACKERS] error-free disabling of individual child partition

On Tue, 2006-05-23 at 11:31 -0400, Tom Lane wrote: > At that point it seems like it'd read more naturally the other way > round: > > ALTER TABLE childN DROP INHERITS old_parent; > ALTER TABLE childN ADD INHERITS new_parent; > > although I'm not sure if this would create a parser conflict against

Re: [HACKERS] file-locking and postmaster.pid

Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > On Tuesday 23 May 2006 17:54, Tom Lane wrote: >> The postmaster does check to see whether the PID mentioned in the file >> is still alive, so it's not that easy for the above to happen. If you >> can provide details of a scenario where a failure i

Re: [HACKERS] file-locking and postmaster.pid

On Tuesday 23 May 2006 17:54, Tom Lane wrote: > Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > > I've experienced several times that PG has died somehow and the > > postmaster.pid file still exists 'cause PG hasn't had the ability to > > delete it upon proper shutdown. Upon start-up, after such

Re: [HACKERS] New feature proposal

On Fri, 2006-05-19 at 12:35 -0700, Marc Munro wrote: > On Fri, 2006-05-19 at 14:44 -0400, Tom Lane wrote: > > This could all be solved in a cleaner, more bulletproof way if you > > simply require such add-ins to be preloaded into the postmaster process > > using the existing preload_libraries hook.

Re: [HACKERS] Further reduction of bufmgr lock contention

Gavin Hamill <[EMAIL PROTECTED]> writes: > It's been about a month since the last activity on bufmgr as documented > on the hackers list and I was just concerned that this issue had been > filed as an interesting toy at the time, but now left for the circular > filing cabinet :) > Tom + Simon w

Re: [HACKERS] file-locking and postmaster.pid

Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > I've experienced several times that PG has died somehow and the > postmaster.pid > file still exists 'cause PG hasn't had the ability to delete it upon proper > shutdown. Upon start-up, after such an incidence, PG tells me another PG is > runni

Re: [HACKERS] Further reduction of bufmgr lock contention

Tom Lane wrote: I've been looking into Gavin Hamill's recent report of poor performance with PG 8.1 on an 8-way IBM PPC64 box. [...] Hullo again :) I'm unfamiliar with postgres development practices, so this is more a request for information than anything else. It's been about a month sinc

Re: [HACKERS] error-free disabling of individual child partition

Simon Riggs <[EMAIL PROTECTED]> writes: > Do we need the ALTER keyword? That isn't used anywhere apart from > manipulating columns. i.e. > ALTER TABLE childN INHERITS DROP old_parent; > ALTER TABLE childN INHERITS ADD new_parent; At that point it seems like it'd read more naturally the other way

Re: [HACKERS] error-free disabling of individual child partition

On Tue, 2006-05-23 at 18:19 +0300, Hannu Krosing wrote: > For me "DROP INHERITS oldtable" sounds better than "INHERITS DROP > oldtable" , but it may be just me :) Agreed, so proposal is now ALTER TABLE childN DROP INHERITS old_parent; ALTER TABLE childN ADD INHERITS new_parent; Going once; going

Re: [HACKERS] [ADMIN] does wal archiving block the current client

Simon Riggs <[EMAIL PROTECTED]> writes: > My recent patch will prevent server startup, so if you do a fast restart > to bounce the server and change parameters you'll have to keep the > server down while the archiver completes (or you kill it). BTW, I was not planning on having it do that. The ar

Re: [HACKERS] error-free disabling of individual child partition

Ühel kenal päeval, T, 2006-05-23 kell 15:59, kirjutas Simon Riggs: > On Tue, 2006-05-23 at 16:29 +0200, Csaba Nagy wrote: > > > ALTER TABLE childN ALTER INHERITS DROP (parent); > > > ALTER TABLE childN ALTER INHERITS ADD (parent); > > > > Wouldn't it be possible to allow the ADD/DROP to happen in

[HACKERS] file-locking and postmaster.pid

Hi all. I've experienced several times that PG has died somehow and the postmaster.pid file still exists 'cause PG hasn't had the ability to delete it upon proper shutdown. Upon start-up, after such an incidence, PG tells me another PG is running and that I either have to shut down the other in

[HACKERS] Why is CVS server so slow?

The last few days the CVS server seems to be much slower than it used to be. No network changes here. Anything changed server side, or should I ask elsewhere? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)---

Re: [HACKERS] [ADMIN] does wal archiving block the current client

On Tue, 2006-05-23 at 11:09 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > On Tue, 2006-05-23 at 10:53 -0400, Tom Lane wrote: > >> I think we just need a PostmasterIsAlive check in the per-file loop. > > > ...which would mean the archiver would not outlive postmaster in the >

Re: [HACKERS] [ADMIN] does wal archiving block the current client connection?

Simon Riggs <[EMAIL PROTECTED]> writes: > On Tue, 2006-05-23 at 10:53 -0400, Tom Lane wrote: >> I think we just need a PostmasterIsAlive check in the per-file loop. > ...which would mean the archiver would not outlive postmaster in the > event it crashes...which is exactly the time you want it to

Re: [HACKERS] [ADMIN] does wal archiving block the current client

On Tue, 2006-05-23 at 10:53 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > This doesn't quite get to the nub of the problem: archiver is designed > > to keep archiving files, even in the event that the postmaster explodes. > > It will keep archiving until they're all gone. >

Re: [HACKERS] error-free disabling of individual child partition

On Tue, 2006-05-23 at 16:29 +0200, Csaba Nagy wrote: > > ALTER TABLE childN ALTER INHERITS DROP (parent); > > ALTER TABLE childN ALTER INHERITS ADD (parent); > > Wouldn't it be possible to allow the ADD/DROP to happen in the same > statement, like: > > ALTER TABLE childN ALTER INHERITS DROP crt_p

Re: [HACKERS] [ADMIN] does wal archiving block the current client connection?

Simon Riggs <[EMAIL PROTECTED]> writes: > This doesn't quite get to the nub of the problem: archiver is designed > to keep archiving files, even in the event that the postmaster explodes. > It will keep archiving until they're all gone. I think we just need a PostmasterIsAlive check in the per-fi

Re: [HACKERS] [ADMIN] does wal archiving block the current client

On Fri, 2006-05-19 at 17:27 +0100, Simon Riggs wrote: > On Fri, 2006-05-19 at 12:03 -0400, Tom Lane wrote: > > Simon Riggs <[EMAIL PROTECTED]> writes: > > > OK, I'm on it. > > > > What solution have you got in mind? I was thinking about an fcntl lock > > to ensure only one archiver is active in a

Re: [HACKERS] Performance Issues

On 23-May-06, at 10:24 AM, Richard Huxton wrote: Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used t

Re: [HACKERS] error-free disabling of individual child partition

> ALTER TABLE childN ALTER INHERITS DROP (parent); > ALTER TABLE childN ALTER INHERITS ADD (parent); Wouldn't it be possible to allow the ADD/DROP to happen in the same statement, like: ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent; or: ALTER TABLE childN ALTER INHERITS DROP

Re: [HACKERS] error-free disabling of individual child partition

On Tue, 2006-05-23 at 09:37 -0400, Tom Lane wrote: > "Zeugswetter Andreas DCP SD" <[EMAIL PROTECTED]> writes: > >>> We don't need a disinherit do we? > > > I propose: ALTER TABLE childN INHERITS (); > > Thus I also think, that the list should be complete, and is not an > > addition to existing inh

Re: [HACKERS] Performance Issues

Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used to enforce the primary-key. 2. If 'm executing a comp

Re: [HACKERS] error-free disabling of individual child partition

Hannu Krosing <[EMAIL PROTECTED]> writes: > Ühel kenal päeval, T, 2006-05-23 kell 09:37, kirjutas Tom Lane: >>> I propose: ALTER TABLE childN INHERITS (); >>> Thus I also think, that the list should be complete, and is not an >>> addition to existing inheritance. >> >> Don't like that at all: it

Re: [HACKERS] error-free disabling of individual child partition

Ühel kenal päeval, T, 2006-05-23 kell 09:37, kirjutas Tom Lane: > "Zeugswetter Andreas DCP SD" <[EMAIL PROTECTED]> writes: > >>> We don't need a disinherit do we? > > > I propose: ALTER TABLE childN INHERITS (); > > Thus I also think, that the list should be complete, and is not an > > addition to

Re: [HACKERS] error-free disabling of individual child partition

"Zeugswetter Andreas DCP SD" <[EMAIL PROTECTED]> writes: >>> We don't need a disinherit do we? > I propose: ALTER TABLE childN INHERITS (); > Thus I also think, that the list should be complete, and is not an > addition to existing inheritance. Don't like that at all: it seems far too error-prone

Re: [HACKERS] Performance Issues

Dhanaraj M <[EMAIL PROTECTED]> writes: > I have the following doubts. > > 1. Does postgres create an index on every primary key? Usually, > queries are performed against a table on the primary key, so, an index > on it will be very useful. To enforce the primary key constraint, PG creates a uniq

[HACKERS] Performance Issues

I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seco

Re: [HACKERS] error-free disabling of individual child partition

Ühel kenal päeval, T, 2006-05-23 kell 10:51, kirjutas Simon Riggs: > On Tue, 2006-05-23 at 09:49 +0200, Zeugswetter Andreas DCP SD wrote: > > > > table of another table. I propose a TODO item to allow this: > > > > > > > > ALTER TABLE childN INHERITS ( parent1, ... ); > > > > > > We don't

Re: [HACKERS] error-free disabling of individual child partition

On Tue, 2006-05-23 at 09:49 +0200, Zeugswetter Andreas DCP SD wrote: > > > table of another table. I propose a TODO item to allow this: > > > > > > ALTER TABLE childN INHERITS ( parent1, ... ); > > > > We don't need a disinherit do we? > > I propose: ALTER TABLE childN INHERITS (); > Thus I als

Re: [HACKERS] error-free disabling of individual child partition

> > table of another table. I propose a TODO item to allow this: > > > > ALTER TABLE childN INHERITS ( parent1, ... ); > > We don't need a disinherit do we? I propose: ALTER TABLE childN INHERITS (); Thus I also think, that the list should be complete, and is not an addition to existing inh

Re: [HACKERS] Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Tom Lane wrote: I think the hard part of this task is designing the API for access to the rowsets from triggers. My preference would be something similar to two Portal instances (the NEW and OLD). I could then map it in the same way that I map the result of a query. If the API actually used tw

[HACKERS] API changes in patch release

The world is not perfect and I know that you are normally very restrictive in what is back-patched from head into bug-fix branches. The 8.1.4 release however, did introduce a problem. You changed the API function inv_open() with the comment "Revise large-object access routines to avoid running