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

[Qingqing Zhou]

"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, we can easily find a lot of
similar problems caused by the actual parameters. For example, SeqScan vs.
IndexScan vs. BitmapIndexScan for a range query. So an improvement is
definitely needed.

> Ideas for improving this situation are welcome ... it's not an easy
> problem ...
>
IMHO basically we have two ways to get better plan: one is to have a set of
alternative plans for prepare queries. This will add some cost but PREPARE
is supposed to do only once against a lot of EXECUTE. But still, the biggest
problem is that number of plans is not controllable.

Another way is to generate a plan on the fly. What we do is to let some
REPLAN nodes sit on top of some critical plan node: at the execution, we
will compare the actual numbers we get and the estimated number we have
(mabye "rows"?), once we find that a re-plan efforts might be deserved, we
will get a new plan on the fly. In this way, I think a not-too-big patch
will do. I remember there is a paper talking about this somewhere but not
remember clearly. -- This method can handle the range query problem above,
but not for LIKE. So we may have to kludge some code to handle LIKE
especially :-(.

Regards,
Qingqing





---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Performance Issues

[Tom Lane]

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 version too.  PRIMARY KEY/UNIQUE syntax was not
supported before this patch:

1997-12-04 18:07  thomas

* src/backend/parser/: analyze.c, gram.y: Add SQL92-compliant
syntax for constraints.  Implement PRIMARY KEY and UNIQUE clauses
using indices.

and in that patch and every subsequent version, unique constraints are
associated with indexes.  In fact, we do not even *have* any
implementation method for unique constraints other than the duplicate-
entry-detection code in the btree index AM.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Performance Issues

[Christopher Kings-Lynne]

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 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] Performance Issues

[Dhanaraj M]

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, 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 is an interesting question that I have suddenly become very
curious of, if you have a primary key, obviously a unique index, is it, in
fact, use this index regardless of analyzing the table?


 


2. If 'm executing a complex query and it takes 10 seconds to return the
results -- it takes 10 seconds to execute the next time also.  I'm
wondering if there's any kind of caching that can be enabled -- so, the
next time it takes <10 seconds to return the results.
 


Not of query results. Obviously data itself might be cached. You might
want to look at memcached for this sort of thing.
   




I am looking at this string of posts and it occurs to me that he should
run analyze. Maybe I'm jumping at the wrong point.
 




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Improving ALTER TYPE support

[John Jawed]

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 type accordingly, which current means doing a drop type ...> cascade down to the function. We should allow one of the following:
Why not go all the way and work out a way to define an SRF return type as apart of the function?  e.g.CREATE OR REPLACE FUNCTION some_srf ( param1 int, param2 text )RETURNS some_srf ( col1 int, col2 numeric ) AS 
Then the "replace" function would automatically rebuild the type.--Josh BerkusPostgreSQL @ SunSan 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] Why is CVS server so slow?

[Tom Lane]

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

Yeah, I was about to say the same.  The master CVS server has been OK
(not great, but OK) for the last week or so; but I believe the folks
complaining are using anoncvs.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

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

[Tom Lane]

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

Ideas for improving this situation are welcome ... it's not an easy
problem ...

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] Why is CVS server so slow?

[Josh Berkus]

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

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Improving ALTER TYPE support

[Josh Berkus]

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:

Why not go all the way and work out a way to define an SRF return type as a 
part of the function?  e.g.

CREATE OR REPLACE FUNCTION some_srf ( param1 int, param2 text )
RETURNS some_srf ( col1 int, col2 numeric ) AS 

Then the "replace" function would automatically rebuild the type.

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

[HACKERS] Improving ALTER TYPE support

[John Jawed]

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 REPLACE on CREATE TYPE (create or replace type)2) Allow ALTER TYPE to alter the definition of the type (currently it only allows changes to schema and ownership)It
may get tricky with the first option because there'd be orphaned
dependencies on objects which reference that particular type while it's
being re-created.
I'll work on a patch according to feedback.

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

[Michael Fuhr]

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 daily interactively, and haven't noticed any issues ... 
> Bruce has, in the past, noticed an issue that if he wipes out and 
> re-checks out the source, it seems to clear up ...

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.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] SQL compliant interval implementation

[Michael Glaesemann]

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 threads, but here are some subject titles  
and approximate times that may help you find where things related to  
this have been discussed before.


November 2004
[GENERAL] Comment on timezone and interval types
October 2005
[BUGS] BUG #1993: Adding/subtracting negative time intervals
March 2006
Re: [HACKERS] [SQL] Interval subtracting


Michael Glaesemann
grzm seespotcode net




---(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] Porting MSSQL to PGSQL -- triggers

[Mischa Sandberg]

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). I know STATEMENT-level triggers 
have existed in PG for a while ... but what do people use them for???


--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

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

[Marc G. Fournier]

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, noticed an issue that if he wipes out and 
re-checks out the source, it seems to clear up ...



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED]  MSN . [EMAIL PROTECTED]
Yahoo . yscrappy   Skype: hub.orgICQ . 7615664

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] SQL compliant interval implementation

[Josh Berkus]

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 into month/year | day/week | 
hour/minute/second/etc.

I personally don't see the benefit of evaluating "1 month" = "30 days", but 
I don't see the harm either.   "days" *don't* get rolled up to months, 
which is proper partitioned behavior:

postgres=# select interval '180 days';
 interval
--
 180 days
(1 row)

postgres=# select interval '1800 days';
 interval
---
 1800 days

The only issue comes when you multiply units by a decimal:

postgres=# select interval '11 months' * 0.3;
?column?

 3 mons 8 days 24:00:00

... which leads to some broken calculations:

select ( interval '11 months' * 0.3 ) / 0.3;
 ?column?
--
 10 mons 26 days 96:00:00

but anyone who deals in "fractional months" should know that they're 
approximating.  Previously, we couldn't get decimal calculations to work 
at all.

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

If I had a link, I'd send it.  But I'd have to do the same searching you're 
doing.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

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

[Rodrigo Hjort]

PG-Hackers,I got the following picture:detran=# \d sa_dut.tb_usuario    Table "sa_dut.tb_usuario" Column  |    Type | Modifiers-+-+---
 numprocesso | bigint  | not null nome    | character varying(44)   | nomemae | character varying(44)   | datanascimento  | date    |
Indexes:   "tb_usuario_pkey" PRIMARY KEY, btree (numprocesso)   "ix_usuario_11" btree (nome varchar_pattern_ops, nomemae varchar_pattern_ops)   "ix_usuario_13" btree (datanascimento, nome varchar_pattern_ops)
As I do not use C locale, I created indexes based on "varchar_pattern_ops".The issue I'm having is based on the following queries:select * from TB_USUARIO where nome like 'TATIANA CRISTINA G%';
select * from TB_USUARIO where nome like '%TATIANA CRISTINA G%';For some reasons, I'm not using text-search engines, like TSearch2, but only the LIKE operator.Here are the query plans involved:
detran=# explain analyze select count(*) as x0_0_ from sa_dut.TB_PROCESSO processo0_, sa_dut.TB_USUARIO usuario1_ where (usuario1_.NOME like 'TATIANA CRISTINA G%'  and processo0_.NUMPROCESSO=usuario1_.NUMPROCESSO);
QUERY PLAN Aggregate  (cost=11.94..11.95 rows=1 width=0) (actual time=
143.970..143.972 rows=1 loops=1)  ->  Nested Loop  (cost=0.00..11.94 rows=1 width=0) (actual time=143.935..143.949 rows=1 loops=1)    ->  Index Scan using ix_usuario_11 on tb_usuario usuario1_ (cost=0.00..6.01
 rows=1 width=8) (actual time=93.884..93.889 rows=1 loops=1)  Index Cond: (((nome)::text ~>=~ 'TATIANA CRISTINA G'::character varying) AND ((nome)::text ~<~ 'TATIANA CRISTINA H'::character varying))
  Filter: ((nome)::text ~~ 'TATIANA CRISTINA G%'::text)    ->  Index Scan using tb_processo_pkey on tb_processo processo0_  (cost=0.00..5.91 rows=1 width=8) (actual time=50.041..50.044 rows=1 loops=1)
  Index Cond: (processo0_.numprocesso = "outer".numprocesso) Total runtime: 144.176 msdetran=# explain analyze select count(*) as x0_0_ from sa_dut.TB_PROCESSO processo0_, sa_dut.TB_USUARIO usuario1_ where
(usuario1_.NOME like '%TATIANA CRISTINA G%'  and processo0_.NUMPROCESSO=usuario1_.NUMPROCESSO);QUERY PLAN-
 Aggregate  (cost=67534.55..67534.56 rows=1 width=0) (actual time=8101.957..8101.959 rows=1 loops=1)  ->  Nested Loop  (cost=0.00..67534.55 rows=1 width=0) (actual time=5404.106..8101.923 rows=1 loops=1)    ->  Seq Scan on tb_usuario usuario1_  (cost=
0.00..67528.62 rows=1 width=8) (actual time=5404.056..8101.862 rows=1 loops=1)  Filter: ((nome)::text ~~ '%TATIANA CRISTINA G%'::text)    ->  Index Scan using tb_processo_pkey on tb_processo
processo0_  (cost=0.00..5.91 rows=1 width=8) (actual time=0.034..0.037 rows=1 loops=1)  Index Cond: (processo0_.numprocesso = "outer".numprocesso) Total runtime: 8102.105 msWe use Java, and recently we made an effort in order to avoid the leading '%' on LIKE expressions.
The problem is that it wasn't solved, and then I made the following Java code to verify it.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.
Is it concerned to the JDBC Driver or PostgreSQL itself? What could be done in order to fix it?I could use static parameters, but then the queries would have to be reparsed each time on the backend, missing cache advantages.
package db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement
;import java.sql.ResultSet;import java.sql.SQLException;public class SelectLike {   public SelectLike() {   long qtd = 0L, inicio = 0L, tempo[] = {0,0,0,0};   try {   
Class.forName("org.postgresql.Driver");   } catch (ClassNotFoundException e) {   e.printStackTrace();   }   Connection con = null;   String dbURL = "jdbc:postgresql://10.15.61.6/database";
   try {   con = DriverManager.getConnection(dbURL, "user", "password");   String sql = "select count(*) as x0_0_ fromsa_dut.TB_PROCESSO processo0_, sa_dut.TB_USUARIO usuario1_ where
(usuario1_.NOME like ? and processo0_.NUMPROCESSO=usuario1_.NUMPROCESSO)";   String nome = "TATIANA CRISTINA G";   PreparedStatement ps = null;   ResultSet rs = null;
   //001 - '%NAME%' binded   if (ps != null) ps.close();   ps = con.prepareStatement(sql); 

Re: [HACKERS] SQL compliant interval implementation

[Bruce Momjian]

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
> > > other class, called day-time intervals, has an express or implied
> > > interval precision that can include any fields other than YEAR or
> > > MONTH.
> >
> > Yeah, we used to do that.  It sucked.  In fact, most developers of
> > applications which were calendar-heavy ended up using custom data types to
> > work around the SQL-spec INTERVAL limitations.  And that benefits nobody.
> >
> 
> 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?
> 
> 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.

Well, it seems to just eliminate the 30-day problem by disallowing it,
and creating two data types.  I don't see how that is better than what
we have now.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(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] SQL compliant interval implementation

[Brendan Jurd]

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, has an express or implied
> interval precision that can include any fields other than YEAR or
> MONTH.

Yeah, we used to do that.  It sucked.  In fact, most developers of
applications which were calendar-heavy ended up using custom data types to
work around the SQL-spec INTERVAL limitations.  And that benefits nobody.



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?

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.

Regards,
BJ

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] SQL compliant interval implementation

[Josh Berkus]

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
> interval precision that can include any fields other than YEAR or
> MONTH.

Yeah, we used to do that.  It sucked.  In fact, most developers of 
applications which were calendar-heavy ended up using custom data types to 
work around the SQL-spec INTERVAL limitations.  And that benefits nobody.

We modified our INTERVAL type to function in ways that calendar application 
developers (of which there are several on this list) can actually use.  
See discussion leading up to the 8.0 release.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] SQL compliant interval implementation

[Tom Lane]

"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 take you very far in this
argument, because the SQL spec itself is pretty broken in this area.
In particular, the only reason that year-month vs day-second makes
any sense is that they're pretending daylight savings doesn't exist.
If you allow for DST transitions then the day/second multiplier isn't
constant any more than the month/day multiplier is.  (And then there
are leap seconds...)

I don't especially want to get into leap-second-aware timekeeping,
because that'd practically destroy the ability to do calendar arithmetic
at all.  But DST awareness seems like a requirement to me.  We just
recently fixed "interval" to do something fairly plausible in that
area, and I don't much want to backtrack.

> It could be done without breaking existing applications; just
> implement the two new interval types, and leave the old unified
> interval in place as a deprecated type, then drop it after a few major
> releases.

Uh, not if all three types want to be called "interval", and I don't
entirely see how you maintain spec compliance without that.  In any
case, actually *dropping* support for the existing interval behavior is
a way harder sell than just adding something more spec-like; you haven't
been nearly persuasive enough to sell that one.  I don't even see an
argument here for deprecating it, because if you ask me it works better
than the SQL-spec behavior.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] SQL compliant interval implementation

[Bruce Momjian]

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 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-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
> interval precision that can include any fields other than YEAR or
> MONTH.
> ---
> 
> The reason for intervals being divided into two classes is that
> interval days and months (as distinct from datetime days and months)
> have no well-defined relationship.
> 
> The current postgres implementation uses a conversion rate of 30 days
> to the month, but this appears to be a band-aid solution to a deeper
> problem; that postgres is trying to do with a single type something
> which really should be done with two.
> 
> Imagine that I tried to implement a unified "length" type that
> contained two fields, "metres" and "hobbits", where the number of
> hobbits per metre differs from one hobbit to the next, but nonetheless
> you're allowed to perform comparison and conversion between hobbits
> and metres.  People would haul me out into the proverbial town square
> and throw rotten tomatoes at me.  And rightly so.
> 
> I think the SQL standard has the right idea.  Keep the year-months and
> the day-times separate.  Don't try to perform arithmetic or
> comparisons between the two.
> 
> I note that this has been discussed on the mailing lists a few times
> before, but I didn't see any conclusion or consensus reached.
> 
> So, the question is, are there any compelling reasons we shouldn't try
> to implement "interval" as two types?  Does the existing unified
> approach offer any significant advantages?
> 
> The only such advantage I can see is that it's easy for the user to
> specify month values and day values together when performing date
> arithmetic, for example if I wanted to add 1 month and 12 days to a
> date, under the current approach I would simply issue:
> 
> > SELECT dateval + interval '1 month 12 days';
> 
> That's nice and convenient, however, there's no reason we can't keep
> this simple under a separated approach:
> 
> > SELECT dateval + interval month '1' + interval second '12 days';
> 
> With ISO INTERVAL syntax (already listed as a TODO item) this becomes
> a bit more human-readable:
> 
> > SELECT dateval + interval '1' month + interval '12' day;
> 
> By defining some convenient numeric input functions it becomes very
> readable (not to mention incredibly easy to parse into the internal
> format, versus textual inputs)
> 
> > SELECT dateval + months(1) + days(12);
> 
> It could be done without breaking existing applications; just
> implement the two new interval types, and leave the old unified
> interval in place as a deprecated type, then drop it after a few major
> releases.
> 
> The day-time interval type would be pretty much the same as the
> existing interval, with the month field removed.  The year-month field
> would simply be a signed integer.
> 
> Thanks in advance for your comments.
> 
> Regards,
> BJ
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 

-- 
  Bruce Momjian   http://candle.pha.pa.us
  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

[HACKERS] SQL compliant interval implementation

[Brendan Jurd]

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-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
interval precision that can include any fields other than YEAR or
MONTH.
---

The reason for intervals being divided into two classes is that
interval days and months (as distinct from datetime days and months)
have no well-defined relationship.

The current postgres implementation uses a conversion rate of 30 days
to the month, but this appears to be a band-aid solution to a deeper
problem; that postgres is trying to do with a single type something
which really should be done with two.

Imagine that I tried to implement a unified "length" type that
contained two fields, "metres" and "hobbits", where the number of
hobbits per metre differs from one hobbit to the next, but nonetheless
you're allowed to perform comparison and conversion between hobbits
and metres.  People would haul me out into the proverbial town square
and throw rotten tomatoes at me.  And rightly so.

I think the SQL standard has the right idea.  Keep the year-months and
the day-times separate.  Don't try to perform arithmetic or
comparisons between the two.

I note that this has been discussed on the mailing lists a few times
before, but I didn't see any conclusion or consensus reached.

So, the question is, are there any compelling reasons we shouldn't try
to implement "interval" as two types?  Does the existing unified
approach offer any significant advantages?

The only such advantage I can see is that it's easy for the user to
specify month values and day values together when performing date
arithmetic, for example if I wanted to add 1 month and 12 days to a
date, under the current approach I would simply issue:


SELECT dateval + interval '1 month 12 days';


That's nice and convenient, however, there's no reason we can't keep
this simple under a separated approach:


SELECT dateval + interval month '1' + interval second '12 days';


With ISO INTERVAL syntax (already listed as a TODO item) this becomes
a bit more human-readable:


SELECT dateval + interval '1' month + interval '12' day;


By defining some convenient numeric input functions it becomes very
readable (not to mention incredibly easy to parse into the internal
format, versus textual inputs)


SELECT dateval + months(1) + days(12);


It could be done without breaking existing applications; just
implement the two new interval types, and leave the old unified
interval in place as a deprecated type, then drop it after a few major
releases.

The day-time interval type would be pretty much the same as the
existing interval, with the month field removed.  The year-month field
would simply be a signed integer.

Thanks in advance for your comments.

Regards,
BJ

---(end of broadcast)---
TIP 6: explain analyze is your friend

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

[Bruce Momjian]

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:
> 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/attinhcount settings, and why, during ADD or DROP?
> 
> Never is round here ;-)
> 
> attislocal: If you set this to False, you wouldn't be able to set it
> back again. If you leave it as it is, you'd never be able to recursively
> drop a column. If you change it, you'll never be able to stop someone
> from dropping a previously defined local column.
> Proposal:  
> 1. attislocal is not touched. 
> That means if you want to create a new partition you do this:
> 
> CREATE TABLE newChild () INHERITS (template);
> 
> ... do some processing ...
> 
> ALTER TABLE newChild ADD INHERITS parent;
> 
> or this:
> 
> CREATE TABLE newChild () INHERITS (parent);
> ALTER TABLE newChild DROP INHERITS parent;
> 
> ... do some processing ...
> 
> ALTER TABLE newChild ADD INHERITS parent;
> 
> Neither of which I like.
> 
> 2. attislocal is always set False when an appropriate ADD INHERITS is
> actioned. Not ever set back again.
> 
> attinhcount changes as appropriate - up for ADDs and down for DROPs.
> 
> -- 
>   Simon Riggs 
>   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
> 

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] API changes in patch release

[Andrew Dunstan]

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 buildfarm builds the core, and nothing else. Testing of non 
core code (pgfoundry projects, uncommitted patches, unknown other stuff) 
is on a possible TODO list. It's something I want discussed at least 
informally at the Toronto conference - I hope the organizers have 
managed to find me a decent slot for a small discussion session to cover 
buildfarm issues.


Right now I do not have enough time available to do much development 
work on it, though.


cheers

andrew


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] API changes in patch release

[Magnus Hagander]

> > 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 
> > stable during patch releases.
> > 
> > Having said that, I've been in the game long enough to know that 
> > Utopia doesn't exist. You probably had a very good reason 
> to break the 
> > compatibility.
> 
> 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...

//Magnus

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

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

[Tom Lane]

"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. about a year ago I reported a case where the database allowed
> multiple records with the same p-key which were causing problems with
> dump/reload from 8.0->8.1.  It was pretty rare, but it looked like
> under certain circumstances unique constraint was not getting applied.
>  I was unable to reproduce it, though.

Yeah, I remember.

> Is it possible that this bug was the cause of that particular problem?

No, this is unrelated.  It only occurs in a query that's fetching rows
using OR'd indexscans, eg

SELECT ... WHERE indexedcol = 42 OR indexedcol = 47 OR ...;

(you can spell it "indexedcol IN (42,47,...)" with same results) and
the problem is basically incorrect detection of fetching the same row
more than once, ie, a bug in the code that's in charge of not returning
rows multiple times if query is like

SELECT ... WHERE indexedcol = 42 OR indexedcol = 42 OR ...;

This is nowhere near the unique-constraint enforcement mechanism.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] API changes in patch release

[Bruce Momjian]

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 access 
> routines to avoid running with CurrentMemoryContext".
> 
> 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 stable 
> during patch releases.
> 
> Having said that, I've been in the game long enough to know that Utopia 
> doesn't exist. You probably had a very good reason to break the 
> compatibility.

I don't think any of us realized the change would affect third-party
projects.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  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] Update on sort-compression stuff

[Simon Riggs]

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
> > "compression" in that case is extremely cheap, it'd be much more likely
> > to be beneficial.
> 
> I looked into this and decided that trimming the headers for the
> in-memory copies is not as attractive as all that.  The killer problem
> is that comparetup_heap() needs to be able to apply heap_getattr() to
> the stored tuples to extract sort keys.  Unless we want to support a
> variant copy of the heap_getattr() infrastructure just for sort tuples,
> it ain't gonna work.  Another issue is that we'd be increasing the
> palloc traffic for in-memory sorts, because tuplesort_gettuple() would
> have to cons up a freshly palloc'd complete tuple to hand back to the
> caller.
> 
> However, we can definitely trim a lot of overhead from what gets written
> to "tape", so I'll have a go at doing that.

If we write the tuples in compressed form and read them back in that
same form, there wouldn't be any more palloc overhead at all. The
freelists would be full of too large blocks, but that might not be such
a problem.

heap_getattr() is called by so few other places it makes sense to have a
sort specific version.

-- 
  Simon Riggs
  EnterpriseDB  http://www.enterprisedb.com


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

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

[Merlin Moncure]

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 allowed
multiple records with the same p-key which were causing problems with
dump/reload from 8.0->8.1.  It was pretty rare, but it looked like
under certain circumstances unique constraint was not getting applied.
I was unable to reproduce it, though.

Is it possible that this bug was the cause of that particular problem?

Merlin

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Update on sort-compression stuff

[Tom Lane]

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 more likely
> to be beneficial.

I looked into this and decided that trimming the headers for the
in-memory copies is not as attractive as all that.  The killer problem
is that comparetup_heap() needs to be able to apply heap_getattr() to
the stored tuples to extract sort keys.  Unless we want to support a
variant copy of the heap_getattr() infrastructure just for sort tuples,
it ain't gonna work.  Another issue is that we'd be increasing the
palloc traffic for in-memory sorts, because tuplesort_gettuple() would
have to cons up a freshly palloc'd complete tuple to hand back to the
caller.

However, we can definitely trim a lot of overhead from what gets written
to "tape", so I'll have a go at doing that.

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] [GENERAL] autovacuum "connections" are hidden

[Jim C. Nasby]

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  
> >committed
> >to the 8.1 branch, so it is included in the new release (8.1.4?)
> >
> >>Does this still obey stats_command_string?
> >
> >Yes.
> >
> >I considered having the ps display show the info, but it's not as  
> >useful
> >because you can only get the info if you have access to the process  
> >list
> >(i.e. not a remote client).

Well, there's now been 2 calls for seperate options for the autovacuum
process; this one and the ability to give it a different log level.
Perhaps what would be best is having autovac read a second set of config
options that would over-ride settings in the main postgresql.conf. This
could be a seperate GUC, or perhaps a seperate config file (which would
allow for removing all the autovac controls from postgresql.conf).

> In any event thanks a lot for the current fix, as is it's a big  
> improvement! 8^)

Ditto.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(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] file-locking and postmaster.pid

[Adis Nezirovic]

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 having two postmasters
> running in the same data directory (which WILL corrupt your data,
> quickly and irretrievably).  All it takes to cause a problem is to
> use the start script to start a postmaster, forgetting that you already
> have one running ...

I do agree with you that we should not play games with postmaster.
Better to be safe than sorry. (So, manually deleting pid file is the
only safe option). I was just suggestion (possibly dangerous)
workaround.

Btw, I do check for running postmaster, using full path (I don't wan to
kill every postmaster on the system), is this safe? Or there could be
race condition?


pgpVhuJZOzXtM.pgp
Description: PGP signature

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

[Simon Riggs]

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/attinhcount settings, and why, during ADD or DROP?

Never is round here ;-)

attislocal: If you set this to False, you wouldn't be able to set it
back again. If you leave it as it is, you'd never be able to recursively
drop a column. If you change it, you'll never be able to stop someone
from dropping a previously defined local column.
Proposal:  
1. attislocal is not touched. 
That means if you want to create a new partition you do this:

CREATE TABLE newChild () INHERITS (template);

... do some processing ...

ALTER TABLE newChild ADD INHERITS parent;

or this:

CREATE TABLE newChild () INHERITS (parent);
ALTER TABLE newChild DROP INHERITS parent;

... do some processing ...

ALTER TABLE newChild ADD INHERITS parent;

Neither of which I like.

2. attislocal is always set False when an appropriate ADD INHERITS is
actioned. Not ever set back again.

attinhcount changes as appropriate - up for ADDs and down for DROPs.

-- 
  Simon Riggs 
  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] file-locking and postmaster.pid

[Tom Lane]

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 it is smarter than the postmaster is almost
certainly wrong.  It is certainly dangerous, too, because auto-deleting
that pidfile destroys the interlock against having two postmasters
running in the same data directory (which WILL corrupt your data,
quickly and irretrievably).  All it takes to cause a problem is to
use the start script to start a postmaster, forgetting that you already
have one running ...

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] error-free disabling of individual child partition

[Tom Lane]

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?

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

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

[Adis Nezirovic]

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, PG tells me another PG is 
> running and that I either have to shut down the other instance, or delete the 
> postmaster.pid file if there really isn't an instance running. This seems 
> totally unnecessary to me. Why doesn't PG use file-locking to tell if another 
> PG is running or not? If PG holds an exclusive-lock on the pid-file and the 
> process crashes, or shuts down, then the lock(which is process-based and 
> controlled by the kernel) will be removed and another PG which tries to start 
> up can detect that. Using the existence of the pid-file as the only evidence 
> gives too many false positives IMO.

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.

i.e.

#!/bin/bash
PID=`pgrep -f /usr/bin/postmaster`;

if [[ $PID ]]; then
echo "'$PID'";
# postgres is already running
else
echo "Postmaster is not running";
# delete stale PID file
fi


pgpBL3yb1NFGM.pgp
Description: PGP signature

Re: [HACKERS] Performance Issues

[Mark Woodward]

> 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 is an interesting question that I have suddenly become very
curious of, if you have a primary key, obviously a unique index, is it, in
fact, use this index regardless of analyzing the table?


>
>> 2. If 'm executing a complex query and it takes 10 seconds to return the
>> results -- it takes 10 seconds to execute the next time also.  I'm
>> wondering if there's any kind of caching that can be enabled -- so, the
>> next time it takes <10 seconds to return the results.
>
> Not of query results. Obviously data itself might be cached. You might
> want to look at memcached for this sort of thing.


I am looking at this string of posts and it occurs to me that he should
run analyze. Maybe I'm jumping at the wrong point.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

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

[Simon Riggs]

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
> ADD/DROP COLUMN.

Behaviour would be:
- If you DROP INHERITS this simply removes the link to the parent. All
existing columns, constraints etc are retained. You can DROP inheritance
on a table that is itself a parent; its children are unaffected.
- If you ADD INHERITS this will fail if it would do the equivalent of
possibly multiple ADD COLUMNs. You can ADD inheritance onto a table that
is itself a parent; its children are unaffected.
- The table data is not scanned at all for either ADD or DROP INHERITS
- You cannot ADD INHERITS if the table being added as parent is already
one of the inheritance set of the target table (i.e. no loops)
- ADD/DROP are opposites; you can use the other one to undo an action
taken in haste, error etc
- Once DROP INHERITS has committed no changes are propagated down from
parent to former child.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

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

[Tom Lane]

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 is likely, we'd like
>> to know about it.  Also, what PG version are you talking about?

> I have experienced this with PG-8.1.3 and will provide details if I can make 
> it happen. Basically it has happened when I have had to "hard-reset" my 
> laptop due to some strange bugs in Linux which have made it hang.

If you're talking about a postmaster that's auto-started during the boot
sequence, then there is a risk depending on what start script you use.
The problem is that depending on what else runs during the system
startup, the PID assigned to the postmaster might be the same as in the
last boot cycle, or it might be different by one or two counts.  The
postmaster disregards a pidfile containing its own PID, or its parent
process' PID, or a PID not belonging to a postgres-owned process.
That covers most cases but if your start script does something like

su -l postgres -c "pg_ctl start ..."

then you have a situation where not only the parent process (pg_ctl)
but also the grandparent (a shell) is postgres-owned, and if the pidfile
PID happens to match the grandparent then you lose.  Solution is to
either not use pg_ctl here, or write "exec pg_ctl start ...", so that
there's only one postgres-owned process besides the postmaster itself.

Initscripts published by PGDG itself and by Red Hat have gotten this
right for awhile, but I suspect the word has not propagated to all
distros.

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] file-locking and postmaster.pid

[Andreas Joseph Krogh]

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 an incidence,
> > PG tells me another PG is running and that I either have to shut down the
> > other instance, or delete the postmaster.pid file if there really isn't
> > an instance running. This seems totally unnecessary to me.
>
> 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 is likely, we'd like
> to know about it.  Also, what PG version are you talking about?

I have experienced this with PG-8.1.3 and will provide details if I can make 
it happen. Basically it has happened when I have had to "hard-reset" my 
laptop due to some strange bugs in Linux which have made it hang.

> > Why doesn't PG use file-locking to tell if another
> > PG is running or not?
>
> Portability.

Ok.

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(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] New feature proposal

[Marc Munro]

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.  Then, such an add-in would
> > allocate its own shmem segment independent of the main Postgres one.
> > This totally eliminates worries about one chunk of code eating the other
> > one's memory, which otherwise we'd have to have additional mechanism to
> > deal with.
> 
> This is an interesting idea that I had not previously considered.  I
> will give it some thought.

I have give this idea some further thought and I agree; Tom's solution
is more bulletproof and is the right way to go.  My original proposal is
withdrawn.

I am going to look into the best way to implement this but my gut
feeling is that I would like the support infrastructure for this to be
in Postgres rather than in Veil.

By support infrastructure, I mean APIs to create and access new shared
memory segments, and allocate chunks of memory from those shared
segments.

I think this code is better placed in Postgres rather than in specific
add-ins because: it is functionality that could benefit many add-ins; it
can make use of existing postgres code; and it can be easily tested in
the regression suite using the buildfarm.

I don't want to start working on this without knowing there is a chance
of the patch being acceptable, so feedback is invited.

Thanks.
__
Marc


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] Further reduction of bufmgr lock contention

[Tom Lane]

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 were able to see a fairly easy 25% performance boost against 
> our dataset and I'd obv. be very keen to see this work make it into 
> 8.1.4 or 8.2.0 :)

We're certainly not putting any such thing into 8.1.*.  The proposed
patch for 8.2 is stalled ATM because of the problem of not having a
predictable size for the per-partition hash tables.  Fixed-size shared
memory is a harsh mistress :-(

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

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

[Tom Lane]

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 
> running and that I either have to shut down the other instance, or delete the 
> postmaster.pid file if there really isn't an instance running. This seems 
> totally unnecessary to me.

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 is likely, we'd like
to know about it.  Also, what PG version are you talking about?

> Why doesn't PG use file-locking to tell if another 
> PG is running or not?

Portability.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Further reduction of bufmgr lock contention

[Gavin Hamill]

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 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 were able to see a fairly easy 25% performance boost against 
our dataset and I'd obv. be very keen to see this work make it into 
8.1.4 or 8.2.0 :)


Cheers,
Gavin.

---(end of broadcast)---
TIP 6: explain analyze is your friend

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

[Tom Lane]

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
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
ADD/DROP COLUMN.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

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

[Simon Riggs]

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

-- 
  Simon Riggs 
  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] [ADMIN] does wal archiving block the current client

[Tom Lane]

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 archiver subprocess
should fail to start (and the PM keep trying to start it).  Not take
down the entire database.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

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

[Hannu Krosing]

Ü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 the same
> > statement, like:
> > 
> > ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent;
> > 
> > or:
> > 
> > ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD
> > new_parent;
> > 
> > That would still make it one statement, but more explicit. And it would
> > eliminate the need for parenthesis (I assume they were needed for
> > supplying more than 1 table to inherit/disinherit).
> 
> Sounds good. 
> 
> Do we need the ALTER keyword? 

Probably not.

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

For me "DROP INHERITS oldtable" sounds better than "INHERITS DROP
oldtable" , but it may be just me :)

-- 

Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com

NOTICE: This communication contains privileged or other confidential
information. If you have received it in error, please advise the sender
by reply email and immediately delete the message and any attachments
without copying or disclosing the contents.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] file-locking and postmaster.pid

[Andreas Joseph Krogh]

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 instance, or delete the 
postmaster.pid file if there really isn't an instance running. This seems 
totally unnecessary to me. Why doesn't PG use file-locking to tell if another 
PG is running or not? If PG holds an exclusive-lock on the pid-file and the 
process crashes, or shuts down, then the lock(which is process-based and 
controlled by the kernel) will be removed and another PG which tries to start 
up can detect that. Using the existence of the pid-file as the only evidence 
gives too many false positives IMO.

I'm sure there's a good reason for having it the way it is, having so many 
smart knowledgeable people working on this project. Could someone please 
explain the rationale of the current solution to me?

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] Why is CVS server so slow?

[Simon Riggs]

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)---
TIP 5: don't forget to increase your free space map settings

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

[Simon Riggs]

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
> > event it crashes...which is exactly the time you want it to keep going.
> 
> Postmaster crashes are not a problem in practice; we've been careful to
> keep the postmaster doing so little that there's no material risk of it
> failing.  If the postmaster dies it's almost certainly because someone
> killed it, and you really want the child processes to close up shop too.
> 
> (If we did want the archiver to keep running, it shouldn't have any
> PostmasterIsAlive check at all; I can't see a reason why completing
> one iteration of the outer loop is a better time to stop than any
> other time.)

This does at least solve the fast restart problem, so look on -patches
in a few minutes.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

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

[Tom Lane]

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

Postmaster crashes are not a problem in practice; we've been careful to
keep the postmaster doing so little that there's no material risk of it
failing.  If the postmaster dies it's almost certainly because someone
killed it, and you really want the child processes to close up shop too.

(If we did want the archiver to keep running, it shouldn't have any
PostmasterIsAlive check at all; I can't see a reason why completing
one iteration of the outer loop is a better time to stop than any
other time.)

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

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

[Simon Riggs]

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

Granted, that's an easy change.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

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

[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 the same
> statement, like:
> 
> ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent;
> 
> or:
> 
> ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD
> new_parent;
> 
> That would still make it one statement, but more explicit. And it would
> eliminate the need for parenthesis (I assume they were needed for
> supplying more than 1 table to inherit/disinherit).

Sounds good. 

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;

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

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

[Tom Lane]

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

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

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

[Simon Riggs]

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 given data directory.  That
> > would fix the problem without affecting anything outside the archiver.
> > Not sure what's the most portable way to do it though.
> 
> I was trying to think of a better way than using an archiver.pid file in
> pg_xlog/archive_status...

Yesterday I posted to -patches with a new archiver.pid interlock
mechanism. This will prevent server startup when the archiver is first
activated, but once running will clean up and restart again.

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. 

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

The archiver's Spartan diligence is great if postmaster does fail, but
archiver can't tell the difference between a normal shutdown and a
postmaster crash. If the postmaster sent a SIGUSR2 on normal shutdown,
we would be able to interrupt the outer loop and shutdown much faster. A
starting postmaster might then reasonably wait a little while for the
old archiver to quit before starting the new one.

What do you think?

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Performance Issues

[Dave Cramer]

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 to enforce the primary-key.

2. If 'm executing a complex query and it takes 10 seconds to  
return the results -- it takes 10 seconds to execute the next time  
also.  I'm wondering if there's any kind of caching that can be  
enabled -- so, the next time it takes <10 seconds to return the  
results.


Not of query results. Obviously data itself might be cached. You  
might want to look at memcached for this sort of thing.


Postgresql relies on the kernel buffers, and shared buffers for caching.

As someone else said postgresql is quite conservative when shipped.  
Tuning helps considerably


Dave


--
  Richard Huxton
  Archonet Ltd

---(end of  
broadcast)---

TIP 2: Don't 'kill -9' the postmaster




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

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

[Csaba Nagy]

> 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 crt_parent, ALTER INHERITS ADD
new_parent;

That would still make it one statement, but more explicit. And it would
eliminate the need for parenthesis (I assume they were needed for
supplying more than 1 table to inherit/disinherit).

Cheers,
Csaba.




---(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] error-free disabling of individual child partition

[Simon Riggs]

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 inheritance.
> 
> Don't like that at all: it seems far too error-prone.

What types of error do you think its prone to? 

Can you say what you would prefer?

As ever, not that worried about syntax, but I would like to get
agreement on a specific way forward now we're discussing this.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Performance Issues

[Richard Huxton]

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 complex query and it takes 10 seconds to return the 
results -- it takes 10 seconds to execute the next time also.  I'm 
wondering if there's any kind of caching that can be enabled -- so, the 
next time it takes <10 seconds to return the results.


Not of query results. Obviously data itself might be cached. You might 
want to look at memcached for this sort of thing.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

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

[Tom Lane]

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 seems far too error-prone.

> In what way ?

It seems like it'd be awfully easy to unintentionally disinherit a child
table from a parent.

In a situation where you're only using single inheritance, it hardly
matters of course, but for multiple inheritance it just seems like a
way to shoot yourself in the foot.  ISTM it'd be safer to have an
explicit disinherit-from-this-parent operation.

> In the first case, I'd propose following syntax

> ALTER TABLE childN ALTER INHERITS DROP (parent);
> ALTER TABLE childN ALTER INHERITS ADD (parent);

I could live with that.  Do we need the parens?

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

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

[Hannu Krosing]

Ü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 existing inheritance.
> 
> Don't like that at all: it seems far too error-prone.

In what way ?

Do you mean that it will be easy for the user to make errors, od do yuo
think that it will be hard to implement in a robust way ?

In the first case, I'd propose following syntax

ALTER TABLE childN ALTER INHERITS DROP (parent);
ALTER TABLE childN ALTER INHERITS ADD (parent);

With this syntax reparenting would need an explicit transaction and two
"ALTER TABLE ... ALTER INHERITS ..." commands, but it is (arguably)
harder to make mistakes.

-- 

Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.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] error-free disabling of individual child partition

[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 existing inheritance.

Don't like that at all: it seems far too error-prone.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Performance Issues

[Douglas McNaught]

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 unique index when
the table is created (I think it even tells you this after CREATE
TABLE). 

> 2. If 'm executing a complex query and it takes 10 seconds to return
> the results -- it takes 10 seconds to execute the next time also.  I'm
> wondering if there's any kind of caching that can be enabled -- so,
> the next time it takes <10 seconds to return the results.

All kinds of data is cached in shared memory.  Did you tune the
shared_buffers setting in postgresql.conf?  It's set quite low by
default to make sure the server can start on systems with low shared
memory limits.

The online documentation has this info and lots more--I suggest you
read it.

-Doug

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] Performance Issues

[Dhanaraj M]

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 seconds to execute the next time also.  I'm 
wondering if there's any kind of caching that can be enabled -- so, the 
next time it takes <10 seconds to return the results.


Thanks
Dhanaraj

---(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] error-free disabling of individual child partition

[Hannu Krosing]

Ü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 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.
> 
> Sounds good; an absolute rather than a relative approach. Avoids new
> keywords.

And also allows you move a partition from live to archive table in one
command. Brilliant :)

> Implementation is simpler too:
> - check that we have all required merged attributes (if any)
> - remove any inheritance that isn't on the list
> 
> If the table is already INHERITS (x) and we specify INHERITS (x) then
> its a no-op that returns success.
>  
> > > O, yes, I think we do. I can imagine that the ability to swap a table 
> > 
> > Agreed. Simon, were you testing how many ppl read to the end :-)
> 
> Just seeing who cared. DROP TABLE removes inheritance too, in a Keyser
> Soze manner.

Just fyi - I care too .

-- 

Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.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] error-free disabling of individual child partition

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

Sounds good; an absolute rather than a relative approach. Avoids new
keywords.

Implementation is simpler too:
- check that we have all required merged attributes (if any)
- remove any inheritance that isn't on the list

If the table is already INHERITS (x) and we specify INHERITS (x) then
its a no-op that returns success.
 
> > O, yes, I think we do. I can imagine that the ability to swap a table 
> 
> Agreed. Simon, were you testing how many ppl read to the end :-)

Just seeing who cared. DROP TABLE removes inheritance too, in a Keyser
Soze manner.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

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

[Zeugswetter Andreas DCP SD]

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

> 
> O, yes, I think we do. I can imagine that the ability to swap a table 

Agreed. Simon, were you testing how many ppl read to the end :-)

Andreas

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

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

[Thomas Hallgren]

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 two 
real Portal instances, the PL/Java implementation would take minutes.


Regards,
Thomas Hallgren


---(end of broadcast)---
TIP 6: explain analyze is your friend

[HACKERS] API changes in patch release

[Thomas Hallgren]

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 with CurrentMemoryContext".


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 stable 
during patch releases.


Having said that, I've been in the game long enough to know that Utopia 
doesn't exist. You probably had a very good reason to break the 
compatibility.


Kind Regards,
Thomas Hallgren


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings