Re: [HACKERS] Concurrence GiST

[Teodor Sigaev]

Christopher Kings-Lynne wrote:
Hey Teodor,

How's this going?

I think you were looking at the same paper I was reading about GiST 
indexes.  I found the GiST source code somewhat over my head, however.

I hope you'll still working on it and haven't given up!

I hoped  begining of year will be quiet, but it's not. Our customers give to  us 
a lot of work... So I havn't a much time work with GiST. :(

Ok, I suppose that the basic papers is "Access methods for next-generation 
database systems" by Marcel Kornaker and "Concurrency and Recovery in 
Generalized Search Trees" by Kornaker, C.Mohan and Joseph M. Hellerstein.

But it seems to me it's not enough to us. When I began to work with GiST in 
pgsql I found that split operation may fails with variable-size key. Just for 
one reason: user-defined method pickSplit doesn't guarantee that size of free 
space on new page will be enough for insertion of new key. For example: page 
contains small keys which all equals and one - not (small too). We want to 
insert a big key, so pickSplit is called. It distribute equals keys to one page 
and different - to another and we want insert new key in first page - and we 
hasn't enough free space. Contrib/intarray and contrib/tsearch* modules often 
produce similar situation. For this reason, in current implementation gistSplit 
(gist.c) method is recursive, and more - it splits 'virtual' page with already 
inserted new key (look gist.c near 523 line).

As I can see in papers, it's algorithm isn't protected for a such case. So, now 
I think on two directions:
1 How to adopt paper's insertion algorithm. But without success now :(
2 More simple algorithm, but with less concurrerncy based on 'update locks' 
which described at http://www-db.stanford.edu/~ullman/dscb.html (I don't known 
who was fisrt, but I readed about it in book).
  Update lock looks as shared lock while asking and as exclusive while deducted.
  Matrix of locks:
   S  X   U
  Sy  n   y
  Xn  n   n
  Un  n   n

  So, insertion algorithm with two-phase locking:
   Find leaf to insert key (with U locking all parent pages)
   Define which parent will be changed ( let I call it P-page :) ).
   Update lock to X all pages from P-Page to leaf page.
   Release U-locks from root to P-page
   Insert and update pages from P-page to leaf
   Realese all locks.
   So, the defect of this scheme is: nobody can start (but work with other 
pages is possible) work with index while insert process locks root even if root 
locked only with U lock. And we need to add U lock in lock manager of pgsql.

So, I still thinking. If you has other thought/idea, pls, don't be quiet.



--
Teodor Sigaev  E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] dblink - custom datatypes NOW work :)

[Mark Gibson]

Joe Conway wrote:

Mark Gibson wrote:

   I've found the problem, although I'm still a bit confused by it.


I hate to do this to you now, but after some thought I think I have a 
better approach -- I'd be interested in opinions on that assessment.

The attached eliminates pgresultGetTupleDesc() entirely and instead 
depends on the TupleDesc passed as rsinfo->expectedDesc from the 
executor. What this means is that the string representation of the 
remote value (from the "out" function on the remote side, as provided 
by libpq) will get fed into the "in" function corresponding to the 
local type you assign in your SQL statement. Assuming the types on the 
two sides are the same (or at least compatible), it should work well.

Please give this a try and let me know what you think.


Fantastic, works perfectly (well I've only actually tested it with the 
'txtidx' datatype).
That's so much better than my idea, i didn't like having the oid map 
much anyway.
Oh well, I least I've learnt I little about PostgreSQL internals in the 
process.
I can get back to what I was supposed to be doing now ;)

I'm going to give this a much through testing now.

Cheers

--
Mark Gibson 
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 3: 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] [pgsql-hackers-win32] Sync vs. fsync during checkpoint

[Bruce Momjian]

Tom Lane wrote:
> The best idea I've heard so far is the one about sync() followed by
> a bunch of fsync()s.  That seems to be correct, efficient, and dependent
> only on very-long-established Unix semantics.

Agreed.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [HACKERS] No Timeout in SELECT..FOR UPDATE

[Robert Treat]

On Sunday 15 February 2004 16:36, Tom Lane wrote:
> Anthony Rich <[EMAIL PROTECTED]> writes:
> > When one process has a "row lock" on one or more rows
> > in a table, using "SELECT...FOR UPDATE" in default lock
> > mode, another process has NO WAY of aborting from the
> > same request, and reporting to the user that this record
> > is already locked, reserved, or whatever you want to call it.
>
> Not so.  See the statement_timeout parameter.
>

ISTM this is the same problem with the stacked up vacuums... and 
statement_timeout doesnt solve it.  If someone sets statement_timeout = 
 then true, there lock waiting will timeout if it hits the 
statement_timeout limit, but if the statement itself just takes longer than 
statement_timeout in the processing itself, then it also bombs out... and you 
have no way to really differentiate the two different cases.   what is needed 
i think is a lock_timeout, which times out soley for cases where the lock can 
not be aquired in a speedy manner.

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Proposed Query Planner TODO items

[markw]

On 16 Feb, Dennis Haney wrote:
> [EMAIL PROTECTED] wrote:
> 
>>On 12 Feb, Tom Lane wrote:
>>  
>>
>>http://developer.osdl.org/markw/dbt3-pgsql/62/
>>
>>This run changes default_statistics_target to 1000 and I have p_partkey,
>>l_partkey, ps_suppkey, and l_suppkey pg_stats here at 1 min intervals
>>http (no links on the web page.)  Pretty significant performance change.
>>
>>  
>>
> Why the filesystem change to ext2 at the same time?

I've been rotating filesystems occasionally.  Otherwise no specific
reason.

Mark

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

Re: [HACKERS] Proposed Query Planner TODO items

[Dennis Haney]

[EMAIL PROTECTED] wrote:

On 12 Feb, Tom Lane wrote:
 

http://developer.osdl.org/markw/dbt3-pgsql/62/

This run changes default_statistics_target to 1000 and I have p_partkey,
l_partkey, ps_suppkey, and l_suppkey pg_stats here at 1 min intervals
http (no links on the web page.)  Pretty significant performance change.
 

Why the filesystem change to ext2 at the same time?

Something went wrong when I tried to run another test with the Q21
changes overnight, so I'll have to get back to you on that one.
 



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

Re: [PATCHES] [HACKERS] dollar quoting

[Andrew Dunstan]

Bruce Momjian wrote:

Tom Lane wrote:
 

Andrew Dunstan <[EMAIL PROTECTED]> writes:
   

No, it won't.  The problem is that it should, because the backend will
see that as '42' followed by a $foo$ quote start.
   

Ok, I see what you are saying. This mismatch would only happen on 
invalid input, though. I believe that what I did will work on all legal 
input.
 

I'm unconvinced.  Even if there are not any current syntaxes in which a
numeric literal can be adjacent to a string literal (I'm not totally
sure about that), what of the future?  We should solve the problem
rather than assuming it won't bite us.
   

I think that this might be cured by having psql recognise a legal 
identifier or keyword and eating it as a word, rather than treating it 
as just another set of bytes in the stream.
 

Hm, might work ... will think about it ...
   

I am a little concerned about adding the overhead of lex to psql.  Right
now, some folks have reported that lex/yacc take a considerable amount
of processing time in the backend as part of a query, and adding that to
psql just to do $$ seems questionable.  Of course, we can alway test and
see what the overhead shows.
 

I think Tom's point is that the custom lexical recognition done by psql 
is approaching the point of being unmaintainable, and if we have to bite 
the bullet then maybe we might as well do so now.

I'd be surprised if using a flex lexer instead made a huge speed 
difference, but maybe I'm wrong. I'm more concerned that it will be 
difficult to write and maintain and keep in sync with the backend's 
lexical structure - but those are just gut feelings, and I could be way 
off base. Right now psql does just enough recognition to enable it to 
work. Making it recognise the whole sql lexical structure instead 
strikes me as being somewhat redundant - that's what the backend does. 
Maybe we could do a flex lexer somewhat along the lines of the 
minimalist approach that psql currently employs.

Anyway - I did put forward a possible non-flex way to handle the problem 
Tom saw with $$ quoting - let's see what his verdict is on that first 
;-) If he thinks it won't work, I can't see much alternative to using a 
flex-based lexer.

cheers

andrew

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

Re: [HACKERS] No Timeout in SELECT..FOR UPDATE

[Tom Lane]

Robert Treat <[EMAIL PROTECTED]> writes:
> On Sunday 15 February 2004 16:36, Tom Lane wrote:
>> Not so.  See the statement_timeout parameter.

> what is needed i think is a lock_timeout, which times out soley for
> cases where the lock can not be aquired in a speedy manner.

I didn't say that there wasn't room for improvement, I was just
responding to the OP's assertion that there is "NO WAY" to do it now.

In practice though I think that a statement_timeout solves the problem
well enough.  The sort of command you are going to be worried about is
something like a SELECT ... FOR UPDATE with a simple WHERE condition,
and if the normal (unblocked) execution time isn't very much less than
your expected maximum transaction time then you've got problems anyway.
Whatever sort of timeout you use, you'd have to set at some multiple of
the expected max transaction time unless you want to get unnecessary
failures.  So it seems to me that there's plenty of daylight there,
and relatively little need for a per-lock timeout.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Proposed Query Planner TODO items

[markw]

On 15 Feb, Tom Lane wrote:
> I wrote:
>> I see what is going on to make Q4 slow, too.  It's this:
>>   where o_orderdate >= date '1995-04-01' and o_orderdate < date '1995-04-01' + 
>> interval '3 month'
>> ...
>> As of CVS tip the issue could be eliminated by introducing
>> cross-data-type comparison operators between types date and timestamp
>> without time zone, and then making these be members of the date index
>> opclass.  I'm strongly tempted to do so ...
> 
> I have now done this, so if you care to re-sync with CVS tip you should
> find that the queries using this sort of date constraint go faster.
> (You do have indexes on all the date columns, no?)

I ran a test with the CAST you recommended for Q4 over the weekend:
http://developer.osdl.org/markw/dbt3-pgsql/68/

But it didn't seem to have much of an affect on Q4, compared to run
#66.  I'll still give the CVS tip a try.

Mark

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[HACKERS] Slow DROP INDEX

[Rod Taylor]

I have an IO congested database (PostgreSQL 7.2) with too many
(overlapping) indexes, so the obvious solution is to drop them.

DROP INDEX seems to want to take several minutes to complete, which
causes a backup of clients and me to eventually abort the process to let
all the backed up queries go through.

The real question is why does DROP INDEX take more than a couple of
seconds to complete? It is not held up by locked. The indexes are about
5GB in size. I assume PostgreSQL is trying to read the entire index off
disk prior to removal or something equally strange. Is this necessary?


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] Proposed Query Planner TODO items

[Tom Lane]

[EMAIL PROTECTED] writes:
> I ran a test with the CAST you recommended for Q4 over the weekend:
>   http://developer.osdl.org/markw/dbt3-pgsql/68/
> But it didn't seem to have much of an affect on Q4, compared to run
> #66.  I'll still give the CVS tip a try.

Hm.  Disappointing.  I can see from the EXPLAIN results that it is
picking up the additional index constraint correctly in this run.
That should have saved a good number of useless heap fetches.
[ works with the numbers a little... ]  Actually, I guess it did:
it looks like the time spent in the indexscan proper went down from
44msec to 7msec.  The problem is that the bulk of the query time is
actually going into the repeated EXISTS() sub-selects, and those didn't
get any better.

There are some other queries in the set that also have date limits of
this kind, so I still think it's worth redoing a run with CVS tip to
see if we pick up anything overall.  (You do have indexes created on
all the date columns no?)

There's probably no way to make Q4 fly without finding a way to optimize
the EXISTS into an IN-join.  I'll put that on my to-do list ... in the
meantime, if you feel like making a run to confirm that theory, try
modifying Q4 to replace

  and exists ( select * from lineitem
   where l_orderkey = o_orderkey and l_commitdate < l_receiptdate )

with

  and o_orderkey in ( select l_orderkey from lineitem
  where l_commitdate < l_receiptdate )

I think that either 7.4 or CVS tip will do better with this variant,
but it probably ought to be checked.

regards, tom lane

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Slow DROP INDEX

[Tom Lane]

Rod Taylor <[EMAIL PROTECTED]> writes:
> The real question is why does DROP INDEX take more than a couple of
> seconds to complete? It is not held up by locked.

AFAICS it shouldn't take any time to complete.  I think you're mistaken
and it is blocking on a lock (it will want exclusive lock on the table).
Everyone else will then queue up behind it.

IIRC there is not any easy way to check this in 7.2 unfortunately :-(
but you could try looking for idle-in-transaction backends.

regards, tom lane

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

Re: [PATCHES] [HACKERS] dollar quoting

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> I'd be surprised if using a flex lexer instead made a huge speed 
> difference, but maybe I'm wrong.

No, I agree --- it's unlikely to make very much difference in the real
world.  Maybe on huge query strings you could notice the difference.

> I'm more concerned that it will be 
> difficult to write and maintain and keep in sync with the backend's 
> lexical structure - but those are just gut feelings, and I could be way 
> off base. Right now psql does just enough recognition to enable it to 
> work. Making it recognise the whole sql lexical structure instead 
> strikes me as being somewhat redundant - that's what the backend does. 

Actually, I thought the way to handle it would be to duplicate the
backend lexer as nearly as possible.  Most of the productions would have
empty bodies probably, but doing it that way would guarantee that in
fact psql and the backend would lex a string the same way, which is
exactly the problem we are facing here.  You'd fall out of the lexer
only upon detecting backslash (unless we want to put backslash command
lexing into the flex code, which might or might not be a good idea),
or upon detecting a ';' at parenthesis depth 0, or upon hitting end of
string.  In the last case the lexer state would indicate which prompt
we need to give.

One unsolved issue in my mind is how to deal with multibyte characters
in non-backend-compatible encodings (the ones where some bytes of a
multibyte character can have values < 128 and thus can look like plain
ASCII characters).  We'd have to make sure that the flex lexer skips
over such bytes properly.  I've thought of a couple of kluges but
nothing I like ...

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] Replication eRServer problems

[John Li]

I just implemented eRServer for one of my clients and found many
problems with it. 

 


 It
 crashes when using “ers_addtable” to add big tables. The problem is that
 it used pg_dump –d then tried to read the whole output in memory. I fixed
 it by reading one row at a time and inserting it to slave.
 “ers_addslave”
 crashes when creating table schema if one rows on the master were dropped.
 Apparently, when a row was dropped, the “attname” in “pg_attribute” table
 was changed to “………pg.dropped.#..” and the type of the column became “-“.
 That made the “create table” sql statement for slave wrong. I fixed this
 problem by excluding those kind of columns.
 There
 was no index created on “_ers_uniq” on slave side. It took minutes per
 transaction to delete on huge tables. After I found the problem and
 created index on the column, it only took about 5 milliseconds to delete.
 To
 handle columns with default and nextval, eRServer expected to see nextval('"sequence_name"'::text)
 and used double quote to extract sequence names. But postgres also allow nextval('sequence_name'::text)
 without double quotes. The fix was easy.
 eRServer.pm
 failed because DBI could not handle columns with types such as “text[]”
 but Pg module should work.


 

 

Hope those will be fixed in the next
version. I can also provide my fixes if needed.

 

John
Li

 

Re: [HACKERS] Slow DROP INDEX

[Rod Taylor]

On Mon, 2004-02-16 at 13:03, Tom Lane wrote:
> Rod Taylor <[EMAIL PROTECTED]> writes:
> > The real question is why does DROP INDEX take more than a couple of
> > seconds to complete? It is not held up by locked.
> 
> AFAICS it shouldn't take any time to complete.  I think you're mistaken
> and it is blocking on a lock (it will want exclusive lock on the table).
> Everyone else will then queue up behind it.

I not convinced it is waiting on a lock. The queries on that table are
very short (couple of milliseconds) -- but there are a ton of them. All
backends appear to be idle (pg_stat_activity with command shown) when we
start the drop and shortly after huge backups occur.


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

Re: [HACKERS] Proposed Query Planner TODO items

[markw]

On 16 Feb, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> I ran a test with the CAST you recommended for Q4 over the weekend:
>>  http://developer.osdl.org/markw/dbt3-pgsql/68/
>> But it didn't seem to have much of an affect on Q4, compared to run
>> #66.  I'll still give the CVS tip a try.
> 
> Hm.  Disappointing.  I can see from the EXPLAIN results that it is
> picking up the additional index constraint correctly in this run.
> That should have saved a good number of useless heap fetches.
> [ works with the numbers a little... ]  Actually, I guess it did:
> it looks like the time spent in the indexscan proper went down from
> 44msec to 7msec.  The problem is that the bulk of the query time is
> actually going into the repeated EXISTS() sub-selects, and those didn't
> get any better.
> 
> There are some other queries in the set that also have date limits of
> this kind, so I still think it's worth redoing a run with CVS tip to
> see if we pick up anything overall.  (You do have indexes created on
> all the date columns no?)
> 
> There's probably no way to make Q4 fly without finding a way to optimize
> the EXISTS into an IN-join.  I'll put that on my to-do list ... in the
> meantime, if you feel like making a run to confirm that theory, try
> modifying Q4 to replace
> 
>   and exists ( select * from lineitem
>where l_orderkey = o_orderkey and l_commitdate < l_receiptdate )
> 
> with
> 
>   and o_orderkey in ( select l_orderkey from lineitem
>   where l_commitdate < l_receiptdate )
> 
> I think that either 7.4 or CVS tip will do better with this variant,
> but it probably ought to be checked.

It looks like we have indexes on all of the date columns except
l_commitdate, which appears to be in Q4.

So I think I'll run against the CVS tip as is, again with an index on
l_commitdate, and then another test to confirm your theory.  Sound good?

Mark

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] Slow DROP INDEX

[John Li]

Based on my experience, "drop index" is waiting for a chance to place the
lock. If all those queries are within the same connection, "drop index" has
to wait until that connection disconnected.

John Li

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Rod Taylor
Sent: Monday, February 16, 2004 10:56 AM
To: Tom Lane
Cc: PostgreSQL Development
Subject: Re: [HACKERS] Slow DROP INDEX

On Mon, 2004-02-16 at 13:03, Tom Lane wrote:
> Rod Taylor <[EMAIL PROTECTED]> writes:
> > The real question is why does DROP INDEX take more than a couple of
> > seconds to complete? It is not held up by locked.
>
> AFAICS it shouldn't take any time to complete.  I think you're mistaken
> and it is blocking on a lock (it will want exclusive lock on the table).
> Everyone else will then queue up behind it.

I not convinced it is waiting on a lock. The queries on that table are
very short (couple of milliseconds) -- but there are a ton of them. All
backends appear to be idle (pg_stat_activity with command shown) when we
start the drop and shortly after huge backups occur.


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


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

Re: [HACKERS] Slow DROP INDEX

[Tom Lane]

Rod Taylor <[EMAIL PROTECTED]> writes:
> I not convinced it is waiting on a lock. The queries on that table are
> very short (couple of milliseconds) -- but there are a ton of them. All
> backends appear to be idle (pg_stat_activity with command shown) when we
> start the drop and shortly after huge backups occur.

Well, you could prove the point by attaching to the backend with a
debugger and getting a stack trace ... if it's not waiting for a lock,
that would give us some idea what it is doing ...

regards, tom lane

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Proposed Query Planner TODO items

[Tom Lane]

[EMAIL PROTECTED] writes:
> It looks like we have indexes on all of the date columns except
> l_commitdate, which appears to be in Q4.

> So I think I'll run against the CVS tip as is, again with an index on
> l_commitdate, and then another test to confirm your theory.  Sound good?

Sure, it's only cycles ;-).  I am not certain that an index on
commitdate would help any, but it's worth trying.

regards, tom lane

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

Re: [HACKERS] Replication eRServer problems

[Peter Eisentraut]

John Li wrote:
> I just implemented eRServer for one of my clients and found many
> problems with it.

We do not maintain erserver.  You need to talk to the people that you 
got it from, for example here:

http://gborg.postgresql.org/project/erserver/projdisplay.php


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] ISAM driver for PostgreSQL

[Merlin Moncure]

Does anybody think there might be some interest in an ISAM driver for
PostgreSQL?  I've written a functional alpha that allows PostgreSQL to
be a drop in (or as easy as reasonably possible) replacement for an ISAM
file system driving a COBOL application.  It is a STL based thin wrapper
around libpq that generates queries on the fly from traditional ISAM
(read, write, start, etc.).  It was made to be generic, allowing porting
to various COBOL vendors' compilers with minimal effort.  Error
conditions are returned as traditional COBOL error codes.  The main
drawback to the system is that performance is highly sensitive to
network latency (as are most COBOL applications).

Unlike traditional hybrid COBOL which use ESQL, this system could run
native COBOL code with reasonable performance in any COBOL application
which allows linking to an external ISAM file system.  This is very much
a work in progress, built strictly for my own needs; but could be made
to be useful in a more general sense.  I'm thinking about cleaning up
the code and setting up a project on sourceforge.

PostgreSQL is uniquely suited for this purpose because of its
portability and rich syntax.  A good example is postgres's array type
which maps very elegantly to COBOL's OCCURS syntax.

Regards,
Merlin



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Replication eRServer problems

[Devrim GUNDUZ]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Hi,

On Mon, 16 Feb 2004, John Li wrote:

> I just implemented eRServer for one of my clients and found many problems
> with it.

TDMSoft has patched eRServer a bit:

http://www.tdmsoft.com/tr/PostgreSQL/download/

Maybe these could help to solve your problems.

Regards,
- -- 
Devrim GUNDUZ  
[EMAIL PROTECTED]   [EMAIL PROTECTED] 
http://www.TDMSoft.com
http://www.gunduz.org

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFAMTg4tl86P3SPfQ4RAmF9AJ0Uk2JMUnhFFPTSAxiquRINniGJvQCgy++N
mzPIrxh0hPuGvbnBSxJErcw=
=cD6P
-END PGP SIGNATURE-


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

Re: [HACKERS] Proposed Query Planner TODO items

[markw]

On 16 Feb, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> It looks like we have indexes on all of the date columns except
>> l_commitdate, which appears to be in Q4.
> 
>> So I think I'll run against the CVS tip as is, again with an index on
>> l_commitdate, and then another test to confirm your theory.  Sound good?
> 
> Sure, it's only cycles ;-).  I am not certain that an index on
> commitdate would help any, but it's worth trying.

http://developer.osdl.org/markw/dbt3-pgsql/70/

Those are results from a pull from CVS I did this morning.
I reverted Q4 (removed the CAST), but the extra WHERE constraints are
still in Q21.

Mark

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

   http://archives.postgresql.org

Re: [HACKERS] [PATCHES] update i386 spinlock for hyperthreading

[Simon Riggs]

>Kenneth Marshall would like me to post this:
> I agree that in order to manage today's large memory machines, we
> need to have less contention in our buffer management strategies.
> The two main main choke points are in the buffer hash table routines
> and in the buffer management linked lists. Unfortunately most of the
> code depends on holding the bufmgr lock on entry which eliminates
> many chances for parallelism.
> 
> The number of buffer pools should at the very minimum be equal to
> the number of processors in the system. This can allow us to greatly
> reduce the number of cache-sync cycles if each processor has its
> own lock structures for T1-cpuN, T2-cpuN. Now when we allocate a new
> buffer, preferentially grab a buffer from the cpu specific queue
before
> looking in the other queues. Now we have already decreased the amount
> of contention by approximately (1/numberCPUs).
> 
> The next item to address is the buf_table concurrency. It appears that
> the same code that was used in the hash index update by Tom Lane could
> be used to split the buf_table accesses into a per-bucket access using
> a per-bucket lock and not a global lock. Modifying the current
dyn_hash
> search and update code would make it look effectively like Mr. Lane's
> new hash index code.
> 
> The final issue is the churn in the MRU/LRU positions on the buffer
> management lists. Currently, we always remove a buffer from the list
> (T1, T2,...) and then add it to the new list in the MRU position. On
> a busy system, for a given query mix a subset of the buffers will be
> very busy and compete for the MRU position. What we want to do is
> avoid moving a buffer near the top of the list for some definition
> of top. One idea, is to have a "per-CPU per-T* counter" which is
> incremented as buffers are added to the MRU position. The key is to
> store the counter value in the header. Now when we access the buffer
> in the list, if the counter is within a value (settable by a GUC)
> the buffer is not moved. This would reduce the MRU churn for the
> busy buffers near the top of the lists.
> 
> These ideas are very similar to your own speculations. I hope that
> their slightly different slant can contribute to this discussion.
> Thank you for your time.
> 
> Yours truly,
> Kenneth Marshall
> 


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

Re: [HACKERS] Slow DROP INDEX

[Simon Riggs]

>Rod Taylor
> On Mon, 2004-02-16 at 13:03, Tom Lane wrote:
> > Rod Taylor <[EMAIL PROTECTED]> writes:
> > > The real question is why does DROP INDEX take more than a couple
of
> > > seconds to complete? It is not held up by locked.
> >
> > AFAICS it shouldn't take any time to complete.  I think you're
mistaken
> > and it is blocking on a lock (it will want exclusive lock on the
table).
> > Everyone else will then queue up behind it.
> 
> I not convinced it is waiting on a lock. The queries on that table are
> very short (couple of milliseconds) -- but there are a ton of them.
All
> backends appear to be idle (pg_stat_activity with command shown) when
we
> start the drop and shortly after huge backups occur.

Could it be waiting on a lock held on something other than the target
table itself?

A system catalog table, either for access right or optimization? Maybe
the drop index lock is the same as the drop table lock (and perhaps it
shouldn't be).

May not be the case...but the answer should be interesting.

Hope it helps, Simon Riggs


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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PATCHES] [HACKERS] dollar quoting

[Christopher Kings-Lynne]

Actually, I thought the way to handle it would be to duplicate the
backend lexer as nearly as possible.  Most of the productions would have
empty bodies probably, but doing it that way would guarantee that in
fact psql and the backend would lex a string the same way, which is
exactly the problem we are facing here.  You'd fall out of the lexer
only upon detecting backslash (unless we want to put backslash command
lexing into the flex code, which might or might not be a good idea),
or upon detecting a ';' at parenthesis depth 0, or upon hitting end of
string.  In the last case the lexer state would indicate which prompt
we need to give.
You know what would be really sweet?  If the lexing was made available 
as a public function.  eg. So I could parse queries in phpPgAdmin before 
sending them to the backend, etc...

Chris

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PATCHES] [HACKERS] dollar quoting

[Tom Lane]

Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> You know what would be really sweet?  If the lexing was made available 
> as a public function.  eg. So I could parse queries in phpPgAdmin before 
> sending them to the backend, etc...

Parsing is a whole nother ball of wax besides lexing.  I wasn't planning
to put *that* into psql.  Remember the only thing psql really wants from
this is to detect where end-of-statement is ...

regards, tom lane

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

Re: [PATCHES] [HACKERS] dollar quoting

[Bruce Momjian]

Tom Lane wrote:
> Andrew Dunstan <[EMAIL PROTECTED]> writes:
> >> No, it won't.  The problem is that it should, because the backend will
> >> see that as '42' followed by a $foo$ quote start.
> 
> > Ok, I see what you are saying. This mismatch would only happen on 
> > invalid input, though. I believe that what I did will work on all legal 
> > input.
> 
> I'm unconvinced.  Even if there are not any current syntaxes in which a
> numeric literal can be adjacent to a string literal (I'm not totally
> sure about that), what of the future?  We should solve the problem
> rather than assuming it won't bite us.
> 
> > I think that this might be cured by having psql recognise a legal 
> > identifier or keyword and eating it as a word, rather than treating it 
> > as just another set of bytes in the stream.
> 
> Hm, might work ... will think about it ...

I am a little concerned about adding the overhead of lex to psql.  Right
now, some folks have reported that lex/yacc take a considerable amount
of processing time in the backend as part of a query, and adding that to
psql just to do $$ seems questionable.  Of course, we can alway test and
see what the overhead shows.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: 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: [PATCHES] [HACKERS] dollar quoting

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> I am a little concerned about adding the overhead of lex to psql.  Right
> now, some folks have reported that lex/yacc take a considerable amount
> of processing time in the backend as part of a query, and adding that to
> psql just to do $$ seems questionable.  Of course, we can alway test and
> see what the overhead shows.

That's not the question to ask --- the question is whether a flex lexer
will be faster or slower than the hand-made lexing code that psql is
currently using.  Lexer generation is a well-understood art, and you
have to be pretty tense to beat out flex with hand-made code.  It's the
same tradeoff as trying to write better assembly code than a compiler
does.  Look at the lexing loop in psql/mainloop.c (that series of
if-tests starting at about line 250) and ask yourself if that's really
going to beat out the state-machine implementation flex uses --- which
looks to be about two table lookups per character, plus a switch()
executed at the end of every token.  I'll bet on flex being faster.

The reason the lexer shows up in the backend is that it has to grovel
over every individual character of a query.  For sufficiently large
query strings that's gonna take awhile no matter how you do it.

But in any case, the argument for moving to flex is not about
performance, it is about making the code more understandable and more
certain to agree with the backend lexer.

regards, tom lane

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

Re: [PATCHES] [HACKERS] dollar quoting

[Christopher Kings-Lynne]

Parsing is a whole nother ball of wax besides lexing.  I wasn't planning
to put *that* into psql.  Remember the only thing psql really wants from
this is to detect where end-of-statement is ...
Forgive my lameness, but I've never truly figured out where parsing ends 
and lexing begins.  Anyone care to illuminate me on the difference?

Chris

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [PATCHES] [HACKERS] dollar quoting

[Tom Lane]

Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
>> Parsing is a whole nother ball of wax besides lexing.

> Forgive my lameness, but I've never truly figured out where parsing ends 
> and lexing begins.  Anyone care to illuminate me on the difference?

The theoretical answer is that you can do lexing with a finite-state
machine, but parsing generally requires a stack, because it supports
nested constructs.  Lexers don't have any way to describe nested
constructs --- a series of tokens is the only level of abstraction there
is.

The practical answer is that you do one with flex and the other with
bison ;-).  If you can do it with flex, and not cheat by implementing
your own state stack, it's lexing.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] casting zero-length strings

[Neil Conway]

Chris KL recently pointed out to me that we currently don't raise an
error when attempting to cast a zero-length string to a float:

nconway=# select ''::float8;
 float8

  0
(1 row)

nconway=# select ''::float4;
 float4

  0
(1 row)

Similarly for oid:

nconway=# select ''::oid;
 oid
-
   0
(1 row)

Whereas int and numeric reject zero-length strings:

nconway=# select ''::int;
ERROR:  invalid input syntax for integer: ""
nconway=# select ''::numeric;
ERROR:  invalid input syntax for type numeric: ""

So, should we fix oid and float?

I'm leaning toward "yes", for the sake of consistency and
sanity. However, we were bitten by backward-compatibility concerns
when we made a similar change to the "int" input rules during the 7.3
cycle, so I'm open to other suggestions.

-Neil


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] [PATCHES] log session end - again

[Bruce Momjian]

Patch applied.  Thanks.

I modified the doc wording a bit --- patch attached.

---


Andrew Dunstan wrote:
> 
> You can find it here.
> 
> http://archives.postgresql.org/pgsql-patches/2004-02/msg00072.php
> 
> I know Neil was reviewing it and had a minor doc style quibble, as well 
> as the question he raised on -hackers about psql tab completion.
> 
> cheers
> 
> andrew
> 
> Bruce Momjian wrote:
> 
> >Andrew Dunstan wrote:
> >  
> >
> >>Based on Larry's idea, I had in mind to provide a third escape in the 
> >>log_line_info string (in addition to the %U and %D that I had previously 
> >>done) of %S for sessionid, which would look something like this: 
> >>402251fc.713f
> >>
> >>I will start redoing this feature when the log_disconnections patch is 
> >>dealt with.
> >>
> >>
> >
> >Andrew, I can't find the log_disconnections patch.  I know I saw it, but
> >I can't find it now.  Would you resent it please?
> >
> >  
> >
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073


Search for:

Search:

Format:  Results/page:
 


pgsql-announce 

*Users Lists*
pgsql-admin 
pgsql-advocacy 
pgsql-benchmarks 
pgsql-bugs 
pgsql-chat 
pgsql-docs 
pgsql-cygwin 
pgsql-general 
pgsql-interfaces 
pgsql-jdbc 
pgsql-jobs 
pgsql-novice 
pgsql-odbc 
pgsql-performance 
pgsql-php 
pgsql-ports 
pgsql-sql 
pgsql-www 

*Developer Lists*
pgsql-committers 
pgsql-hackers 
pgsql-hackers-win32 
pgsql-patches 

*Regional Lists*
pgsql-de-allgemein 
pgsql-fr-generale 
pgsql-tr-genel 

*Project Lists*
pgadmin-hackers 
pgadmin-support 

*User Groups*
San Francisco 




Re: log session end - again



* *From*: *Andrew Dunstan mailto:[EMAIL PROTECTED]>>*
* *To*: *"Patches (PostgreSQL)" mailto:[EMAIL PROTECTED]>>*
* *Subject*: *Re: log session end - again*
* Date: Tue, 03 Feb 2004 20:03:42 -0500




Peter Eisentraut wrote:

Andrew Dunstan wrote:


This patch brings up to date what I did last year (now unfortunately
bitrotted) to allow the logging of the end of a session, enabled by
the config setting "log_session_end - true". It produces lines like
these:



If we log "session" end, shouldn't we also log "session" start,
rather than "connection" start? It seems there should be some
symmetry here, also for the configuration parameter names.




OK, this version of the patch uses the config parameter name
"log_disconnections" for the sake of symmetry, and changes the message
wording slightly accordingly.

cheers

andrew

Index: doc/src/sgml/runtime.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.235
diff -c -r1.235 runtime.sgml
*** doc/src/sgml/runtime.sgml   27 Jan 2004 16:51:43 -  1.235
--- doc/src/sgml/runtime.sgml   2 Feb 2004 19:08:42 -
***
*** 1825,1830 
--- 1825,1844 
  
  
   
+   log_disconnec

Re: [PATCHES] [HACKERS] dollar quoting

[Bruce Momjian]

Christopher Kings-Lynne wrote:
> > Parsing is a whole nother ball of wax besides lexing.  I wasn't planning
> > to put *that* into psql.  Remember the only thing psql really wants from
> > this is to detect where end-of-statement is ...
> 
> Forgive my lameness, but I've never truly figured out where parsing ends 
> and lexing begins.  Anyone care to illuminate me on the difference?

The simplistic answer is the lexing breaks a string up into
words/tokens, and parsing matches those tokens against patterns and
fires actions.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] casting zero-length strings

[Tom Lane]

Neil Conway <[EMAIL PROTECTED]> writes:
> Chris KL recently pointed out to me that we currently don't raise an
> error when attempting to cast a zero-length string to a float:
> Whereas int and numeric reject zero-length strings:

> So, should we fix oid and float?

Yes, surely, unless someone wants to argue for reverting that change
to pg_atoi.  I can't see a reason for having them act inconsistently.

While we are at it we should make sure these functions are all on the
same page about allowing leading/trailing whitespace.  I seem to recall
that the spec says somewhere that both should be allowed ... but right
now I do not think we allow trailing whitespace.

regards, tom lane

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

Re: [HACKERS] casting zero-length strings

[Christopher Kings-Lynne]

Yes, surely, unless someone wants to argue for reverting that change
to pg_atoi.  I can't see a reason for having them act inconsistently.
While we are at it we should make sure these functions are all on the
same page about allowing leading/trailing whitespace.  I seem to recall
that the spec says somewhere that both should be allowed ... but right
now I do not think we allow trailing whitespace.
Either way, we should make them a WARNING for 7.5, then error in 7.6. 
The pg_atoi change was a bit disastrous because of instant error I thought.

Chris

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

Re: [HACKERS] No Timeout in SELECT..FOR UPDATE

[Simon Riggs]

>Robert Treat
> On Sunday 15 February 2004 16:36, Tom Lane wrote:
> > Anthony Rich <[EMAIL PROTECTED]> writes:
> > > When one process has a "row lock" on one or more rows
> > > in a table, using "SELECT...FOR UPDATE" in default lock
> > > mode, another process has NO WAY of aborting from the
> > > same request, and reporting to the user that this record
> > > is already locked, reserved, or whatever you want to call it.
> >
> > Not so.  See the statement_timeout parameter.
> >
> 
>what is
> needed
> i think is a lock_timeout, which times out soley for cases where the
lock
> can
> not be aquired in a speedy manner.

Perhaps another way is to specify that you do not wish to wait at all.

Oracle and DB2, possibly others, allow the use of the NOWAIT operator,
applied to a preceding LOCK statement, which acts just as it says. If it
encounters a lock, it returns immediately. This then returns control
immediately to the application, so that it can report to the user to get
further instructions. My understanding is that implementing that might
require some fairly basic changes to the internal locking API - maybe
not too complex, but it might cause many changes; I'd vote for it, but
don't hold your breath...

Alternatively, don't use the SELECT..FOR UPDATE metaphor, try another
design that doesn't require this style of locking. Application level
locking can get you round many problems - the database can't do
everything.

Best regards, Simon Riggs




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

Re: [PATCHES] [HACKERS] dollar quoting

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I am a little concerned about adding the overhead of lex to psql.  Right
> > now, some folks have reported that lex/yacc take a considerable amount
> > of processing time in the backend as part of a query, and adding that to
> > psql just to do $$ seems questionable.  Of course, we can alway test and
> > see what the overhead shows.
> 
> That's not the question to ask --- the question is whether a flex lexer
> will be faster or slower than the hand-made lexing code that psql is
> currently using.  Lexer generation is a well-understood art, and you
> have to be pretty tense to beat out flex with hand-made code.  It's the
> same tradeoff as trying to write better assembly code than a compiler
> does.  Look at the lexing loop in psql/mainloop.c (that series of
> if-tests starting at about line 250) and ask yourself if that's really
> going to beat out the state-machine implementation flex uses --- which
> looks to be about two table lookups per character, plus a switch()
> executed at the end of every token.  I'll bet on flex being faster.
> 
> The reason the lexer shows up in the backend is that it has to grovel
> over every individual character of a query.  For sufficiently large
> query strings that's gonna take awhile no matter how you do it.
> 
> But in any case, the argument for moving to flex is not about
> performance, it is about making the code more understandable and more
> certain to agree with the backend lexer.

If we go with lex/flex for psql, I would still like someone to test
performance to see that we aren't taking a big hit.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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