[HACKERS] Windows Installer Bug (Probably)

[Ilias Goudaropoulos]

I am writing to you all just to inform you of a bug(probably), that I
came across while trying to install PostgreSQL 8.1.0-2 and 8.1.1-1 on
WinXP with SP2, using the PostgreSQL installer.

I had done a windows clean install and then installed straight away
PostgreSQL 8.0.4.  It was the only software installed on windows and
worked perfectly.  After a day or two, I noticed that the PostgreSQL
8.1.0-2 was released. I decided two uninstall PostgreSQL 8.0.4 and
install the new 8.1.0-2 version.

So I did, using the following unistall procedure:

1.  I unistalled PostgreSQL 8.0.4 form the control panel in the usual
manner that all windows application are uninstalled.
2.  I used the Computer Management program of the Administrative Tools
of windows and deleted the PostgreSQL user that had been created during
the installation of PostgreSQL 8.0.4.
3.  Finally, I removed all garbage left by the uninstaller in the registry.
4.  I restarted windows.

I have to stress that I did not have any other software installed on
windows at the moment and I had the Windows firewall service disabled.

So I tried to install the new version 8.1.0-2 but got an error.  I tried
at least 15 times and I always got the same error.  The error was
manifesting itself during the installation as follows:


During the Activating procedural languages installation
part, I get:

Failed to connect to the database.  Procedural
languages files are installed, but are not activated in
any databases.

When I press OK it continues and I get:

server closed the connection unexpectedly
This probably means the server terminated
abnormally before or while processing the request.

When I press OK again, it continues and I get:

Failed to connect to the 'template1' database.  Contrib
files are installed, but are not activated in any databases.

Pressing OK for a final time gives me this:

could not send startup packet: Connection reset by
peer (0x2746/10054)
**

I uninstalled the 8.1.0-2 PostgreSQL which was not working and I tried
to install again the 8.0.4, as some people in the novice and Admin 
mailing lists suggested.  The 8.0.4 installer worked fine and I had 
PostgreSQL 8.0.4 up and running in no time.


Then I uninstalled 8.0.4 and tried to install 8.1.0-2 for a last time.
The same errors appeared once again.

*
If that is not a bizzare behaviour of the installer, what is?
*

The same problem appeared when some people I know tried to install
PostgreSQL 8.1.0-2 as well. But they did not have the time nor the
inclination to make this known to the public by posting to the mailing
lists.  I have no feedback of the same people trying the 8.1.1-1
version.  I believe they have not tried it yet.

Now that PostgreSQL 8.1.1-1 is out, I decided to test my luck again.
I repeated the whole procedure and I got exactly the same erros that
manifested when trying to install 8.1.0-2.  I uninstalled and I tried
to install 8.0.4.  That went ok again and I did not have any problems.
All the problems appear whenever I try to install any version after the
8.0.4.

As someone suggested, these errors indicate a proper install where the
server crashes after install.  That is exactly what happens.

Another strange thing is that when the first error prompt appears, if I
look for any log files, I can not find any.  Maybe that is because that
error appears after the installation of the core engine.  I dont know.

But! That bizzare behaviour does not manifest itself when using the
installers of versions of PostgreSQL prior to 8.1.x.

For all versions of PostgreSQL that I have successfully installed so far
(8.0.4 and all others prior to that), I used the folder C:\Program
Files\Postgresql\ for the PostgrerSQL files and the folder D:\DBASES\
for the database files.  I tried the default folders as well but it did
not make any difference.  At any unistallation I delete the folder
D:\DBASES manually.  All other folders are deleted automatically by the
unistaller.

Can anybody please help?
It is very annoying not to be able to use the DB.



http://www.freemail.gr - äùñåÜí õðçñåóßá çëåêôñïíéêïý ôá÷õäñïìåßïõ.
http://www.freemail.gr - free email service for the Greek-speaking.

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

Re: [HACKERS] localization problem (and solution)

[Tom Lane]

Andreas Seltenreich [EMAIL PROTECTED] writes:
 I'm afraid having LC_ALL in the environment at this time would still
 do the wrong thing on setlocale(LC_ALL, ); since a LC_ALL
 environment variable overrides the other categories.

Doh, of course, I was misremembering the precedence.  So we need
LANG=C
LC_ALL unset (probably LANGUAGE too, for glibc)
others as stated

regards, tom lane

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

Re: [HACKERS] localization problem (and solution)

[Andrew Dunstan]

Tom Lane said:
 Andreas Seltenreich [EMAIL PROTECTED] writes:
 I'm afraid having LC_ALL in the environment at this time would still
 do the wrong thing on setlocale(LC_ALL, ); since a LC_ALL
 environment variable overrides the other categories.

 Doh, of course, I was misremembering the precedence.  So we need
   LANG=C
   LC_ALL unset (probably LANGUAGE too, for glibc)
   others as stated



We need to test any solution carefully on Windows, which deals with locales
very differently from *nix, and where we still have some known locale issues
(see recent discussion).

I wonder if the complained of behaviour is triggered by our recent changes
to support utf8 in pl/perl?

cheers

andrew



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

   http://archives.postgresql.org

[HACKERS] Better path-matching for package relocatability (was Re: [BUGS] horology regression test failure)

[Tom Lane]

Martin Pitt [EMAIL PROTECTED] writes:
 Tom Lane [2005-12-20 16:39 -0500]:
 We could doubtless improve make_relative_path to some extent, but the
 mess you have above seems impossible to deal with.  How is a mere
 program supposed to deduce where things were moved to, given only
 knowledge of the actual location of --bindir?  I see little if any
 pattern that would allow prediction of the corresponding --datadir,
 let alone --libexecdir or --includedir ...

 Right, with make_relative_path's current approach that seems to be
 impossible. However, in a test suite I had expected a semantics like
 $DESTDIR, i. e. instead of mangling the path somewhere in the middle,
 the test suite should just prepend the tmp_check path.

Well, more generally what we need is a better match algorithm in
make_relative_path.  After a few moment's thought I propose:

* Determine the common prefix of the compiled-in target_path and
bin_path (for typical cases this would be /usr or /usr/local;
worst case is that the common prefix is just /).  Call everything
to the right of the common prefix the tail of these paths.  The
currently expected scenario is that the tails are share and bin,
but there might be more than one directory level in them.

* Try to match the tail of the bin_path to the end of the actual binary
location (my_exec_path without the executable's name).

* If match, take everything to the left of the match in my_exec_path,
and append the tail of target_path to produce the result.

* If no match, use target_path as-is, same as now.

I think this would get right all of the cases the current code gets
right, and more generally would work when we need to substitute N
levels of directory names instead of just one.  It may still be a
few bricks shy of a load, however.  Any thoughts?

regards, tom lane

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

Re: [HACKERS] localization problem (and solution)

[Tom Lane]

Andrew Dunstan [EMAIL PROTECTED] writes:
 We need to test any solution carefully on Windows, which deals with locales
 very differently from *nix, and where we still have some known locale issues

Right, of course.  I was thinking that this change might actually bring
the Windows and Unix code closer together --- at least for LC_MESSAGES
it seems it would do so.

If I prepare a patch, do you want to test it on Windows before it goes
in, or is it easier just to commit and then test CVS tip?

regards, tom lane

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

Re: [HACKERS] localization problem (and solution)

[Andrew Dunstan]

Tom Lane said:
 Andrew Dunstan [EMAIL PROTECTED] writes:
 We need to test any solution carefully on Windows, which deals with
 locales very differently from *nix, and where we still have some known
 locale issues

 Right, of course.  I was thinking that this change might actually bring
 the Windows and Unix code closer together --- at least for LC_MESSAGES
 it seems it would do so.

 If I prepare a patch, do you want to test it on Windows before it goes
 in, or is it easier just to commit and then test CVS tip?



Can't do anything for cvs tip until the md5 mess is fixed.

I don't have much time to spare for testing till at least next week - maybe
someone else does.

cheers

andrew




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

[HACKERS] pgxs/windows

[Andrew Dunstan]

... seems to be behaving oddly:

dllwrap -o rainbow.dll --def rainbow.def rainbow.o
c:/PROGRA~1/POSTGR~1/8.1/lib/pgxs/src/MAKEFI~1/../../src/utils/dllinit.o
-Lc:/PROGRA~1/POSTGR~1/8.1/bin -lpostgres
c:\mingw\bin\..\lib\gcc-lib\mingw32\3.2.3\..\..\..\..\mingw32\bin\ld.exe:
cannot find -lpostgres

should we be pointing to the bin directory or the lib directory?

ISTR we've seen something like this before.

any thoughts?

cheers

andrew




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

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

[HACKERS] Function call with offset and limit

[REYNAUD Jean-Samuel]

Hi all,

We need to find a solution for a strange problem. 
We have a plpgsql FUNCTION which performs an heavy job (named
test_func). 

CREATE or replace function test_func(z int) returns integer as $$
declare
tst integer;
begin
--
-- Large jobs with z
--
tst :=  nextval('test_truc');
return tst;
end;
$$ LANGUAGE plpgsql;


So I made this test:

test=# select setval('test_truc',1);
 setval

  1
(1 row)

test=#  select currval('test_truc') ;
 currval
-
   1
(1 row)

test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
 idkeyword |   test_func
---+-
  5001 |   5002
(1 row)

test=# select currval('test_truc') ;
 currval
-
5002
(1 row)


This demonstrates that the function is called 5001 times though only one
row is returned. Problem is that this heavy job is performed much, much
more than needed.

But, If I do:
test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
My function is called only once.

Is there any work around ?


Thanks
-- 
REYNAUD Jean-Samuel [EMAIL PROTECTED]
Elma


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

[HACKERS] replicating tsearch2 across versions of postgres

[Dave Cramer]

I am getting the following error(s) when replicating tsearch2 from v 7.4.x to v 8.1.0remoteWorkerThread_1: copy from stdin on local node - PGRES_FATAL_ERROR ERROR:  function "spell_init(text)" does not [EMAIL PROTECTED] CONTEXT:  COPY pg_ts_dict, line 1, column dict_init: "spell_init(text)"the problem is that the function signatures have changedspell_init(text) is now spell_init(internal) so the function can't be found on the destination node.My solution is to not replicate the internal tsearch2 tables. Is this going to be problematic ? Is it possible to add the old signatures back for backward compatibility ? Something like a tsearch2-compat lib ?Dave

[HACKERS] where is the output

[ohp]

hi all,

Not sure it's the right group, but I've spent the afternoon googling and
trying on this.

In PHP (Apache Module)

I try pg_exec(COPY blah TO STDOUT WITH blah);
It runs  for ever...

How can I get the output of COPY in PHP?

Copy_from is not an option because the goal is to get a CSV file.
Writing and re-reading a file doesn't seem to be an option either because
of safe_mode.

Any php gurus out there?

Regards,

-- 
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges+33-5-61-50-97-01 (Fax)
31190 AUTERIVE   +33-6-07-63-80-64 (GSM)
FRANCE  Email: ohp@pyrenet.fr
--
Make your life a dream, make your dream a reality. (St Exupery)

---(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] where is the output

[Martijn van Oosterhout]

On Wed, Dec 21, 2005 at 07:16:28PM +0100, ohp@pyrenet.fr wrote:
 In PHP (Apache Module)
 
 I try pg_exec(COPY blah TO STDOUT WITH blah);
 It runs  for ever...
 
 How can I get the output of COPY in PHP?

You need to use the API functions for copy. In C they are:

PQgetCopyData
PQputCopyData
PQputCopyEnd

I'm sure PHP has these functions, or perhaps the older versions:

PQgetline
PQputline
PQendcopy

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpGxacZJby5Y.pgp
Description: PGP signature

Re: [HACKERS] replicating tsearch2 across versions of postgres

[Tom Lane]

Dave Cramer [EMAIL PROTECTED] writes:
 Is it possible to add the old signatures back for backward  
 compatibility ? Something like a tsearch2-compat lib ?

The old signatures were security holes.  We are not going to put them
back.  I would suggest changing the functions on the 7.4 machine to the
new signatures --- see the 7.4.8 release notes.

regards, tom lane

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

Re: [HACKERS] Automatic function replanning

[Bruce Momjian]

Trent Shipley wrote:
 On Saturday 2005-12-17 16:28, Lukas Smith wrote:
  Bruce Momjian wrote:
 * Flush cached query plans when the dependent objects change,
   when the cardinality of parameters changes dramatically, or
   when new ANALYZE statistics are available
 
  Wouldn't it also make sense to flush a cached query plan when after
  execution it is determined that one or more assumptions that the cached
  query plan was based on was found to be off? Like the query plan was
  based on the assumption that a particular table would only return a hand
  full of rows, but in reality it returned a few thousand.
 
  regards,
  Lukas
 
 
 Proposed rewrite
 
 * Mark query plan for flush (opportunistic replan) when:
   ** dependent objects change,
   ** cardinality of parameters changes sufficiently (per planner 
 parameter)
   ** when new ANALYZE statistics are available and per planner parameter 
 differ 
 sufficiently from prior statistics.

Frankly, I think any new ANALYZE statistics should just invalidate the
plan.  I don't think it is worth trying to determine if they changed
sufficiently or not --- you might as we just replan.

 * Mark plan as tried when parameters of returned set out of statistical 
 control, create alternate plan hill-climbing to statical control.
   ** Too many/too few rows relative to plan expectations
   *** Auto-sample for better statistics?
   ** History of plan shows throughput time for result set varies 
 excessively 
 (need more execution stability, possibly at expense of median optimality).

This is a new idea, that you are remembering bad plans.  I am unsure how
we would track that information.  It gets into the area of having the
optimizer change its behavior based on previous runs, and I am not sure
we have ever agreed to get into that kind of behavior.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (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 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] Function call with offset and limit

[Jim C. Nasby]

Have you tried

SELECT *, test_func(idkeyword)
FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1)
;

?

This should probably have been on -general, btw.

On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote:
 Hi all,
 
 We need to find a solution for a strange problem. 
 We have a plpgsql FUNCTION which performs an heavy job (named
 test_func). 
 
 CREATE or replace function test_func(z int) returns integer as $$
 declare
 tst integer;
 begin
 --
 -- Large jobs with z
 --
 tst :=  nextval('test_truc');
 return tst;
 end;
 $$ LANGUAGE plpgsql;
 
 
 So I made this test:
 
 test=# select setval('test_truc',1);
  setval
 
   1
 (1 row)
 
 test=#  select currval('test_truc') ;
  currval
 -
1
 (1 row)
 
 test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
  idkeyword |   test_func
 ---+-
   5001 |   5002
 (1 row)
 
 test=# select currval('test_truc') ;
  currval
 -
 5002
 (1 row)
 
 
 This demonstrates that the function is called 5001 times though only one
 row is returned. Problem is that this heavy job is performed much, much
 more than needed.
 
 But, If I do:
 test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
 My function is called only once.
 
 Is there any work around ?
 
 
 Thanks
 -- 
 REYNAUD Jean-Samuel [EMAIL PROTECTED]
 Elma
 
 
 ---(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
 

-- 
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 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [pgadmin-hackers] Client-side password encryption

[Bruce Momjian]

Andreas Pflug wrote:
 Martijn van Oosterhout wrote:
 
  
  So it's only an issue if you have a policy of removing old versions of
  libpq on upgrades... I'm not sure what's best practice on windows in
  this area.
 
 When removing the application (in this case: pgsql), you'd remove that 
 old lib as well if it's the only app using it. If you have another 
 application installed, the deinstaller should observe this, and keep the 
 version.
 
 
 I'm voting +1 for lib name versions.

If you add a version number to the Win32 libpq name, you have to update
any command-line compile tools that mention libpq after an upgrade.  The
Unix linker knows about version numbers, but the Win32 linker doesn't,
so adding version numbers does add quite a bit of chaos to the Win32
compile world.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (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 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] [pgadmin-hackers] Client-side password encryption

[Jaime Casanova]

On 12/21/05, Bruce Momjian pgman@candle.pha.pa.us wrote:
 Andreas Pflug wrote:
  Martijn van Oosterhout wrote:
 
  
   So it's only an issue if you have a policy of removing old versions of
   libpq on upgrades... I'm not sure what's best practice on windows in
   this area.
 
  When removing the application (in this case: pgsql), you'd remove that
  old lib as well if it's the only app using it. If you have another
  application installed, the deinstaller should observe this, and keep the
  version.
 
 
  I'm voting +1 for lib name versions.

 If you add a version number to the Win32 libpq name, you have to update
 any command-line compile tools that mention libpq after an upgrade.  The
 Unix linker knows about version numbers, but the Win32 linker doesn't,
 so adding version numbers does add quite a bit of chaos to the Win32
 compile world.


win32 compile world *is* a chaos... it's very frustating when you try
to run a program and it fails because a library (when you actually has
the library, at least _a_ version of the library)...

IMHO, adding version numbers to the name of library for windows is a
the cleanest thing you can do...

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


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

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

[HACKERS] problem with nasty latin2 sorting

[Anna Domachowska]

Hello,
I've got a problem with sorting polish words in postgresql 7.4.2

For example, I've got couple of polish cities in my table 'x', results 
of sorting are:

(select city from x order by city asc)

Bydgoszcz
Gdańsk
Iława
Łódź
Żarnów
Malbork
Warszawa

So Żarnów is placed in wrong place, because it should be found after 
letter Z ??


What's wrong? Is there any patch to fix this? Does anybody had similar 
problem?
I belive that postgres was compiled with --enable-locale, and the 
right locale is set.


Anna Domachowska

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

Re: [HACKERS] where is the output

[Michael Fuhr]

On Wed, Dec 21, 2005 at 07:16:28PM +0100, ohp@pyrenet.fr wrote:
 Not sure it's the right group, but I've spent the afternoon googling and
 trying on this.

 In PHP (Apache Module)

pgsql-php might be more appropriate, or possibly a PHP mailing list.

 I try pg_exec(COPY blah TO STDOUT WITH blah);
 It runs  for ever...
 
 How can I get the output of COPY in PHP?

At the libpq layer you'd use PQgetCopyData(); the only place I see
a call to that function in the PHP 5.1.1 source code is in the
pg_copy_to() function, which is for copying a table into an array.

 Copy_from is not an option because the goal is to get a CSV file.
 Writing and re-reading a file doesn't seem to be an option either because
 of safe_mode.

Could you use pg_copy_to() to read the table into an array and write
each row of the array as CSV?  I'm not sure if the following is the
best way or even entirely correct, but it appears to work in PHP 5.1.1,
even with safe_mode enabled:

  $rows = pg_copy_to($conn, tablename);
  $fh = fopen(php://output, w);
  foreach ($rows as $row) {
fputcsv($fh, explode(\t, rtrim($row)));
  }
  fclose($fh);

According to the PHP documentation fputcsv() is new as of 5.1.0RC1;
for earlier versions you could probably find a module to generate
CSV output.

-- 
Michael Fuhr

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

Re: [HACKERS] replicating tsearch2 across versions of postgres

[Dave Cramer]

Thanks, that might be easier than first thought.

Dave
On 21-Dec-05, at 2:04 PM, Tom Lane wrote:


Dave Cramer [EMAIL PROTECTED] writes:

Is it possible to add the old signatures back for backward
compatibility ? Something like a tsearch2-compat lib ?


The old signatures were security holes.  We are not going to put them
back.  I would suggest changing the functions on the 7.4 machine to  
the

new signatures --- see the 7.4.8 release notes.

regards, tom lane




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

  http://archives.postgresql.org

Re: [HACKERS] problem with nasty latin2 sorting

[Greg Stark]

Anna Domachowska [EMAIL PROTECTED] writes:

 So ¯arnów is placed in wrong place, because it should be found after letter
 Z ??
 
 What's wrong? Is there any patch to fix this? Does anybody had similar 
 problem?
 I belive that postgres was compiled with --enable-locale, and the right
 locale is set.

What does show lc_collate say?
What does \l show in psql?

what do you get on your machine if you save that same list in a file and run
sort on it on the commandline set (with LC_COLLATE set to the same value as
above)?

-- 
greg


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

Re: [HACKERS] Automatic function replanning

[Bruce Momjian]

Rick Gigger wrote:
 It seems to me like there are two classes of problems here:
 
 1) Simply invalidating plans made with out of date statistics.
 2) Using run-time collected data to update the plan to something more  
 intelligent.
 
 It also seems like #1 would be fairly straightforward and simple  
 whereas #2 would be much more complex.  #1 would do me a world of  
 good and probably other people as well.  Postgres's query planning  
 has always been fine for me, or at least I have always been able to  
 optimize my queries when I've got a representative data set to work  
 with.  Query plan caching only gets me when the query plan is created  
 before the statistics are present to create a good plan.
 
 Just one users 2 cents.

Agreed.  I just can't add #2 unless we get more agreement from the
group, because it has been a disputed issue in the past.

---


 
 - Rick Gigger
 
 
 On Dec 19, 2005, at 12:00 PM, Jim C. Nasby wrote:
 
  On Sat, Dec 17, 2005 at 01:07:10PM -0500, Bruce Momjian wrote:
  Jim C. Nasby wrote:
  Is cardinality the only thing we'd need to worry about? My idea was
  actually to track the amount of work normally required by a  
  stored query
  plan, and if a query uses that plan but requires a very different  
  amount
  of work it's a good indication that we either need to replan or  
  store
  multiple plans for that query. Though if we're certain that  
  cardinality
  is the only thing that could make a cached plan go bad it would
  certainly simplify things greatly.
 
  This gets into another area of re-optimizing when the executor finds
  that the actual tables don't match the optimizer estimates.  I  
  think we
  decided that was too hard/risky, but invalidating the plan might  
  help,
  though I am thinking re-planning might just generate the same plan as
  before.  I think something would need to have happened since the last
  planning, like ANALYZE or something.
 
  Well, in the stored plan case, presumably what's changed is one of the
  bound parameters. And if we want to be sophisticated about it, we  
  won't
  just throw out the old plan; rather we'll try and figure out what
  parameter it is that's wanting a different plan.
 
  Updated TODO:
 
 * Flush cached query plans when the dependent objects change,
   when the cardinality of parameters changes dramatically, or
   when new ANALYZE statistics are available
 
  Probably worth pointing to this therad in the TODO...
  -- 
  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 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
 
 
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (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 5: don't forget to increase your free space map settings

Re: [HACKERS] status of concurrent VACUUM patch ...

[Hannu Krosing]

Ühel kenal päeval, K, 2005-12-21 kell 09:50, kirjutas Hannu Krosing:
 Ühel kenal päeval, T, 2005-12-20 kell 17:18, kirjutas Tom Lane:
  =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes:
   i was just wondering about the status of hannu's concurrent vacuum patch.
   are there any plans to integrate this
  
  I still don't trust it (assuming that you're thinking of the same patch
  I am).
 
 What could I do to increase your trust in it ?
 
 Could you think of any specific case it could break ? Or any specific
 tests to check for it ?
 
 I attach the version of the patch for 8.1.0 for anyone interested in
 checking it.

It was pointed out to me that I did'nt.

So here it is:

---
Hannu

Index: src/backend/access/transam/twophase.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/twophase.c,v
retrieving revision 1.16
diff -c -r1.16 twophase.c
*** src/backend/access/transam/twophase.c	29 Oct 2005 00:31:50 -	1.16
--- src/backend/access/transam/twophase.c	21 Dec 2005 23:09:31 -
***
*** 279,284 
--- 279,286 
  	gxact-proc.pid = 0;
  	gxact-proc.databaseId = databaseid;
  	gxact-proc.roleId = owner;
+ 	gxact-proc.inVacuum = false;
+ 	gxact-proc.nonInVacuumXmin = InvalidTransactionId;
  	gxact-proc.lwWaiting = false;
  	gxact-proc.lwExclusive = false;
  	gxact-proc.lwWaitLink = NULL;
Index: src/backend/access/transam/xact.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.215
diff -c -r1.215 xact.c
*** src/backend/access/transam/xact.c	15 Oct 2005 02:49:09 -	1.215
--- src/backend/access/transam/xact.c	21 Dec 2005 23:09:32 -
***
*** 1507,1512 
--- 1507,1514 
  		LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
  		MyProc-xid = InvalidTransactionId;
  		MyProc-xmin = InvalidTransactionId;
+ 		MyProc-inVacuum = false;	/* must be cleared with xid/xmin */
+ 		MyProc-nonInVacuumXmin = InvalidTransactionId; /* this too */
  
  		/* Clear the subtransaction-XID cache too while holding the lock */
  		MyProc-subxids.nxids = 0;
***
*** 1740,1745 
--- 1742,1749 
  	LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
  	MyProc-xid = InvalidTransactionId;
  	MyProc-xmin = InvalidTransactionId;
+ 	MyProc-inVacuum = false;	/* must be cleared with xid/xmin */
+ 	MyProc-nonInVacuumXmin = InvalidTransactionId; /* this too */
  
  	/* Clear the subtransaction-XID cache too while holding the lock */
  	MyProc-subxids.nxids = 0;
***
*** 1902,1907 
--- 1906,1913 
  		LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
  		MyProc-xid = InvalidTransactionId;
  		MyProc-xmin = InvalidTransactionId;
+ 		MyProc-inVacuum = false;	/* must be cleared with xid/xmin */
+ 		MyProc-nonInVacuumXmin = InvalidTransactionId; /* this too */
  
  		/* Clear the subtransaction-XID cache too while holding the lock */
  		MyProc-subxids.nxids = 0;
Index: src/backend/access/transam/xlog.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.222
diff -c -r1.222 xlog.c
*** src/backend/access/transam/xlog.c	29 Oct 2005 00:31:50 -	1.222
--- src/backend/access/transam/xlog.c	21 Dec 2005 23:09:34 -
***
*** 5294,5300 
  	 * StartupSUBTRANS hasn't been called yet.
  	 */
  	if (!InRecovery)
! 		TruncateSUBTRANS(GetOldestXmin(true));
  
  	if (!shutdown)
  		ereport(DEBUG2,
--- 5294,5300 
  	 * StartupSUBTRANS hasn't been called yet.
  	 */
  	if (!InRecovery)
! 		TruncateSUBTRANS(GetOldestXmin(true, false));
  
  	if (!shutdown)
  		ereport(DEBUG2,
Index: src/backend/catalog/index.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/index.c,v
retrieving revision 1.261
diff -c -r1.261 index.c
*** src/backend/catalog/index.c	15 Oct 2005 02:49:12 -	1.261
--- src/backend/catalog/index.c	21 Dec 2005 23:09:34 -
***
*** 1427,1433 
  	else
  	{
  		snapshot = SnapshotAny;
! 		OldestXmin = GetOldestXmin(heapRelation-rd_rel-relisshared);
  	}
  
  	scan = heap_beginscan(heapRelation, /* relation */
--- 1427,1434 
  	else
  	{
  		snapshot = SnapshotAny;
! 		/* okay to ignore lazy VACUUMs here */
! 		OldestXmin = GetOldestXmin(heapRelation-rd_rel-relisshared, true);
  	}
  
  	scan = heap_beginscan(heapRelation, /* relation */
Index: src/backend/commands/vacuum.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.317
diff -c -r1.317 vacuum.c
*** src/backend/commands/vacuum.c	15 Oct 2005 02:49:16 -	1.317
--- src/backend/commands/vacuum.c	21 Dec 2005 23:09:35 -
***
*** 36,41 
--- 36,42 
  #include executor/executor.h
  #include 

Re: [HACKERS] [pgadmin-hackers] Client-side password encryption

[Martijn van Oosterhout]

On Wed, Dec 21, 2005 at 02:51:46PM -0500, Bruce Momjian wrote:
 If you add a version number to the Win32 libpq name, you have to update
 any command-line compile tools that mention libpq after an upgrade.  The
 Unix linker knows about version numbers, but the Win32 linker doesn't,
 so adding version numbers does add quite a bit of chaos to the Win32
 compile world.

The funny thing about it is that the UNIX linker doesn't know about
version numbers at all. It just looks for a liblibname.so (no
version) which is symlinked to the actual version to use. Thus just by
changing a few symlinks you can control which library version is linked
in. Delete the .so and the linker won't find the library anymore (and
fall back to the .a lib) though runtime users still will find it
because they *do* have the version number, which is extracted from the
library itself.

I'm often impressed by the way UNIX is highly configurable yet
trivially transparant at the same time. The chances that anything
remotely similar would work on windws seems slim at best.
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpJVFX2JqvDG.pgp
Description: PGP signature

Re: [HACKERS] problem with nasty latin2 sorting

[Tom Lane]

Anna Domachowska [EMAIL PROTECTED] writes:
 I've got a problem with sorting polish words in postgresql 7.4.2
 ...
 I belive that postgres was compiled with --enable-locale, and the 
 right locale is set.

How sure are you of that?  This sure sounds like a wrong-locale problem
to me.  Try show lc_collate and show server_encoding.

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

[HACKERS] Unsplitting btree index leaf pages

[Simon Riggs]

When we discussed online REINDEX recently we focused on the REINDEX
command itself rather than look at alternative approaches.

One reason to REINDEX is because of index page splits getting things out
of sequence and generally bloating the index.

When we VACUUM, each index is scanned in logical order.

While we scan, if we found two adjacent pages, both of which have less
than (say) 40% rows, we could re-join or unsplit those pages together.
The index blocks are fully locked during the read anyway and there is no
MVCC problem with moving index rows between blocks. All we have to do is
to lock both blocks, having locked them in the correct order.

The rows would always be moved to the lowest physical block id, so that
data would naturally migrate towards the start of the index file. Blocks
would then be marked half-dead just as if they had just had their last
index row removed by the vacuum.

We could start the scan by locking block 1 AND block2, then scan forward
always holding 2 locks as we go. That way we would not need to unlock
and relock the blocks to lock two blocks. The concurrency loss would not
be that great, but we would gain the ability to unsplit the two blocks
into one.

If we do this, we would could possibly avoid the need to full REINDEX
entirely.

If this method checks out we could do one of:
- make VACUUM do this always
- add an option for REINDEX: CLEAN/COMPRESS/VACUUM etc to do this upon
command only

Best Regards, Simon Riggs


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

Re: [HACKERS] Unsplitting btree index leaf pages

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 While we scan, if we found two adjacent pages, both of which have less
 than (say) 40% rows, we could re-join or unsplit those pages together.

Curiously enough, this has been thought of before.  It is not as easy
as you think, or it would have been done the first time around.
nbtree/README explains why:

: We consider deleting an entire page from the btree only when it's become
: completely empty of items.  (Merging partly-full pages would allow better
: space reuse, but it seems impractical to move existing data items left or
: right to make this happen --- a scan moving in the opposite direction
: might miss the items if so.  We could do it during VACUUM FULL, though.)

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] Re: Which qsort is used

[Manfred Koizar]

On Sat, 17 Dec 2005 00:03:25 -0500, Tom Lane [EMAIL PROTECTED]
wrote:
I've still got a problem with these checks; I think they are a net
waste of cycles on average.  [...]
 and when they fail, those cycles are entirely wasted;
you have not advanced the state of the sort at all.

How can we make the initial check adavance the state of the sort?
One answer might be to exclude the sorted sequence at the start of the
array from the qsort, and merge the two sorted lists as the final
stage of the sort.

Qsorting N elements costs O(N*lnN), so excluding H elements from the
sort reduces the cost by at least O(H*lnN).  The merge step costs O(N)
plus some (=50%) more memory, unless someone knows a fast in-place
merge.  So depending on the constant factors involved there might be a
usable solution.

I've been playing with some numbers and assuming the constant factors
to be equal for all the O()'s this method starts to pay off at
  H  for N
  20   100
 130  1000
800010
Servus
 Manfred

---(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] Automatic function replanning

[Jim C. Nasby]

On Wed, Dec 21, 2005 at 05:43:38PM -0500, Bruce Momjian wrote:
 Rick Gigger wrote:
  It seems to me like there are two classes of problems here:
  
  1) Simply invalidating plans made with out of date statistics.
  2) Using run-time collected data to update the plan to something more  
  intelligent.
  
  It also seems like #1 would be fairly straightforward and simple  
  whereas #2 would be much more complex.  #1 would do me a world of  
  good and probably other people as well.  Postgres's query planning  
  has always been fine for me, or at least I have always been able to  
  optimize my queries when I've got a representative data set to work  
  with.  Query plan caching only gets me when the query plan is created  
  before the statistics are present to create a good plan.
  
  Just one users 2 cents.
 
 Agreed.  I just can't add #2 unless we get more agreement from the
 group, because it has been a disputed issue in the past.

Well, how about this, since it's a prerequisit for #2 and would be
generally useful anyway:

Track normal resource consumption (ie: tuples read) for planned queries
and record parameter values that result in drastically different
resource consumption.

This would at least make it easy for admins to identify prepared queries
that have a highly variable execution cost.
-- 
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 3: Have you checked our extensive FAQ?

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

Re: [HACKERS] Improving planning of outer joins

[Christopher Kings-Lynne]

I'm not sure whether we'd need any additional planner knobs to control
this.  I think that the existing join_collapse_limit GUC variable should
continue to exist, but its effect on left/right joins will be the same as
for inner joins.  If anyone wants to force join order for outer joins more
than for inner joins, we'd need some other control setting, but I don't
currently see why that would be very useful.

Does this seem like a reasonable agenda, or am I thinking too small?

regards, tom lane

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




---(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] [pgadmin-hackers] Client-side password encryption

[Christopher Kings-Lynne]

IIRC the whole point of this exercise was to avoid passing the password
to the server in the first place.  Unless you are talking about a PHP
md5() password of course ...





---(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] catalog corruption bug

[Jeremy Drake]

We have encountered a very nasty but apparently rare bug which appears to
result in catalog corruption.  I have not been able to pin down an exact
sequence of events which cause this problem, it appears to be a race
condition of some sort.  This is what I have been able to figure out so
far.

* It appears to be related to temp table creation/deletion.
* It requires at least 2 clients be connected simultaneously.
* It seems to be related to the autovacuum (not sure, just a theory).

I will attempt to explain the circumstances leading up to the problem, and
then show the symptoms.

We are working on a project which uses postgresql to store data which has
been decomposed into a large number of rows in stored procedures
(plpgsql/plperl).  The implementation we have been working with recently
has used temp tables to store intermediate stages of this decomposition so
that we can run multiple queries over it in the course of adding it to our
final tables without having to regenerate the set each time.  We were
running a timing test for a load of data which would result in tens of
millions of rows.  This load creates temp tables with on commit drop and
also explitly drops them.  It appears to do so at a rate of approximately
10 per second (also transactions are being created/committed at that same
rate).  This works fine.  While this load was running we were
working on some testing code to determine whether it might be better to
create the temp table with on commit delete rows instead and use a
plpgsql function to create the temp table with an EXCEPTION
duplicate_table block to handle when the table has already been created
for this connection.  We wrote the function at first on a postgres 8.0 box
which was not running the load, but when we were attempting to determine
what the error code thrown was we noticed that the SQLSTATE variable was
not available in 8.0 and copied the function onto the 8.1 box (which was
running the load) to try it out.  We ran this function a couple times to
get the error, and then had it catch the duplicate_table exception.  We
got the function working, and when we looked at the status of our load we
found that it had died with a message saying

ERROR:  pg_class entry for relid 7502381 vanished during vacuuming

We found this interesting, figuring it was a bug in postgres.  Googling
the non-variable pieces of that message turned up nothing relevant, so we
set about trying to reproduce it.

During the course of doing so, we restarted our load several times and
called the function.  We later put the calling of the function into a loop
in bash calling psql (so we could disconnect/reconnect) to speed up the
finding of the problem.

These are some of the interesting errors which we got while doing this
(all from the server log):

ERROR:  duplicate key violates unique constraint
pg_class_relname_nsp_index
CONTEXT:  SQL statement CREATE TEMP TABLE foo (a integer, b integer) ON
COMMIT
DELETE ROWS
PL/pgSQL function temp_table_test line 2 at SQL statement
ERROR:  relation foo does not exist
ERROR:  duplicate key violates unique constraint
pg_class_relname_nsp_index
CONTEXT:  SQL statement CREATE TEMP TABLE foo (a integer, b integer) ON
COMMIT
DELETE ROWS
PL/pgSQL function temp_table_test line 2 at SQL statement



ERROR:  relation foo does not exist
FATAL:  cache lookup failed for relation 7600066
LOG:  server process (PID 20942) exited with exit code 1
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited abnormally
and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat 
your command.


We also managed to get an error which was more bothersome than the
mysterious disappearing/reappearing temp tables.

ERROR:  relation windowpos does not exist
ERROR:  type windowpos already exists
ERROR:  cache lookup failed for relation 794218

Later:
ERROR:  relation windowpos already exists
ERROR:  catalog is missing 14 attribute(s) for relid 7577269
ERROR:  catalog is missing 14 attribute(s) for relid 7577269
ERROR:  catalog is missing 14 attribute(s) for relid 7577269
ERROR:  catalog is missing 14 attribute(s) for relid 7577269
ERROR:  catalog is missing 14 attribute(s) for relid 7577269
ERROR:  catalog is missing 14 attribute(s) for relid 7577269
ERROR:  catalog is missing 14 attribute(s) for relid 7577269
ERROR:  catalog is missing 14 attribute(s) for relid 7577269




Here is the temp table function we were testing:

CREATE OR REPLACE FUNCTION temp_table_test() RETURNS boolean AS $$
BEGIN
CREATE TEMP TABLE foo (a integer, b integer) ON COMMIT DELETE ROWS;
RETURN true;
EXCEPTION WHEN duplicate_table THEN
RETURN false;
END;
$$ LANGUAGE plpgsql;


And our bash command line for stressing:
for i in 

Re: [HACKERS] [pgadmin-hackers] Client-side password encryption

[Bruce Momjian]

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
 On Wed, Dec 21, 2005 at 02:51:46PM -0500, Bruce Momjian wrote:
  If you add a version number to the Win32 libpq name, you have to update
  any command-line compile tools that mention libpq after an upgrade.  The
  Unix linker knows about version numbers, but the Win32 linker doesn't,
  so adding version numbers does add quite a bit of chaos to the Win32
  compile world.
 
 The funny thing about it is that the UNIX linker doesn't know about
 version numbers at all. It just looks for a liblibname.so (no
 version) which is symlinked to the actual version to use. Thus just by
 changing a few symlinks you can control which library version is linked
 in. Delete the .so and the linker won't find the library anymore (and
 fall back to the .a lib) though runtime users still will find it
 because they *do* have the version number, which is extracted from the
 library itself.

Yes, important distinction.  Thanks for the clarification.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (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 5: don't forget to increase your free space map settings

[HACKERS] to_char and i18n

[Manuel Sugawara]

Now that Oracle supports i18n dependant behavior in its to_char
formatting functions (at least for its 10g release) I was wondering if
a patch to support this in PostgreSQL will get accepted. I was hoping
to work on this now that I have some spare time.

Regards,
Manuel.


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

Re: [HACKERS] Better path-matching for package relocatability (was Re:

[Bruce Momjian]

Tom Lane wrote:
 Well, more generally what we need is a better match algorithm in
 make_relative_path.  After a few moment's thought I propose:
 
 * Determine the common prefix of the compiled-in target_path and
 bin_path (for typical cases this would be /usr or /usr/local;
 worst case is that the common prefix is just /).  Call everything
 to the right of the common prefix the tail of these paths.  The
 currently expected scenario is that the tails are share and bin,
 but there might be more than one directory level in them.
 
 * Try to match the tail of the bin_path to the end of the actual binary
 location (my_exec_path without the executable's name).
 
 * If match, take everything to the left of the match in my_exec_path,
 and append the tail of target_path to produce the result.
 
 * If no match, use target_path as-is, same as now.
 
 I think this would get right all of the cases the current code gets
 right, and more generally would work when we need to substitute N
 levels of directory names instead of just one.  It may still be a
 few bricks shy of a load, however.  Any thoughts?

Sounds fine.  When I did the original code, I was very conservative
about where I would look in the fear I might hit something strange.  Now
that we have used this code in product with little problem, having it be
more aggressive seems logical.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (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 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] catalog corruption bug

[Tom Lane]

Jeremy Drake [EMAIL PROTECTED] writes:
 We have encountered a very nasty but apparently rare bug which appears to
 result in catalog corruption.

How much of this can you reproduce on 8.1.1?  We've fixed a few issues
already.

 This was built from the gentoo ebuild version 8.1.0

I'd be even more interested if you can reproduce it on a non-gentoo
machine.  Gentoo is not noted for stability.

regards, tom lane

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

Re: [HACKERS] to_char and i18n

[Qingqing Zhou]

Manuel Sugawara masm@fciencias.unam.mx wrote
 Now that Oracle supports i18n dependant behavior in its to_char
 formatting functions (at least for its 10g release) I was wondering if
 a patch to support this in PostgreSQL will get accepted. I was hoping
 to work on this now that I have some spare time.


Can you give a small introduction of i18n and what's your plan in 
PostgreSQL?

Regards,
Qingqing 



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

Re: [HACKERS] Automatic function replanning

[Bruce Momjian]

Jim C. Nasby wrote:
 On Wed, Dec 21, 2005 at 05:43:38PM -0500, Bruce Momjian wrote:
  Rick Gigger wrote:
   It seems to me like there are two classes of problems here:
   
   1) Simply invalidating plans made with out of date statistics.
   2) Using run-time collected data to update the plan to something more  
   intelligent.
   
   It also seems like #1 would be fairly straightforward and simple  
   whereas #2 would be much more complex.  #1 would do me a world of  
   good and probably other people as well.  Postgres's query planning  
   has always been fine for me, or at least I have always been able to  
   optimize my queries when I've got a representative data set to work  
   with.  Query plan caching only gets me when the query plan is created  
   before the statistics are present to create a good plan.
   
   Just one users 2 cents.
  
  Agreed.  I just can't add #2 unless we get more agreement from the
  group, because it has been a disputed issue in the past.
 
 Well, how about this, since it's a prerequisit for #2 and would be
 generally useful anyway:
 
 Track normal resource consumption (ie: tuples read) for planned queries
 and record parameter values that result in drastically different
 resource consumption.
 
 This would at least make it easy for admins to identify prepared queries
 that have a highly variable execution cost.

We have that TODO already:

* Log statements where the optimizer row estimates were dramatically
  different from the number of rows actually found?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (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 6: explain analyze is your friend

Re: [HACKERS] to_char and i18n

[Tom Lane]

Manuel Sugawara masm@fciencias.unam.mx writes:
 Now that Oracle supports i18n dependant behavior in its to_char
 formatting functions (at least for its 10g release) I was wondering if
 a patch to support this in PostgreSQL will get accepted.

I thought to_char already had i18n behavior.  What exactly are you
thinking of changing?

regards, tom lane

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

Re: [HACKERS] to_char and i18n

[Manuel Sugawara]

Qingqing Zhou [EMAIL PROTECTED] writes:

 Can you give a small introduction of i18n and what's your plan in
 PostgreSQL?

i18n == Internationalization (maybe I should say l10n ==
localization). This means that to_char functions might lead to
different results depending on the i18n settings. For instance,
nowadays, select to_char(now(), 'dd-mon-yy') returns 21-dec-05
regardless of the i18n settings. This should lead 21-dic-05 in the
es_MX localization. This also applies to the concurrency symbol,
thousand separator, etc.

(Some time ago I proposed an--incomplete--patch and it was rejectd by
Karel arguing that to_char functions should behave *exactly* the same
way that they do in Oracle.)

Regards,
Manuel.



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

Re: [HACKERS] to_char and i18n

[Manuel Sugawara]

Tom Lane [EMAIL PROTECTED] writes:

 I thought to_char already had i18n behavior.  What exactly are you
 thinking of changing?

The modifiers that are suitable to localize. Month and day names comes
to mind and maybe others, I'm not sure what the state of the code is,
but I can say that, at least, the 'month' and 'day' modifiers does not
behave in a localized way.

Regards,
Manuel.


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

Re: [HACKERS] to_char and i18n

[Tom Lane]

Manuel Sugawara masm@fciencias.unam.mx writes:
 (Some time ago I proposed an--incomplete--patch and it was rejectd by
 Karel arguing that to_char functions should behave *exactly* the same
 way that they do in Oracle.)

That is the accepted plan for to_char ... of course, if Oracle changes
to_char every so often, it'll get more interesting to decide what to do.

regards, tom lane

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

Re: [HACKERS] to_char and i18n

[Qingqing Zhou]

Manuel Sugawara masm@fciencias.unam.mx wrote

 i18n == Internationalization (maybe I should say l10n ==
 localization).

Good hint, I got it :-) Just like a crossword puzzle. 18 means there are 18 
characters between 'i' and 'n' ...

Regards,
Qingqing 



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

Re: [HACKERS] to_char and i18n

[Bruce Momjian]

Qingqing Zhou wrote:
 
 Manuel Sugawara masm@fciencias.unam.mx wrote
 
  i18n == Internationalization (maybe I should say l10n ==
  localization).
 
 Good hint, I got it :-) Just like a crossword puzzle. 18 means there are 18 
 characters between 'i' and 'n' ...

Huh?  I don't understand.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (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 5: don't forget to increase your free space map settings

Re: [HACKERS] to_char and i18n

[Tom Lane]

Manuel Sugawara masm@fciencias.unam.mx writes:
 Tom Lane [EMAIL PROTECTED] writes:
 I thought to_char already had i18n behavior.  What exactly are you
 thinking of changing?

 The modifiers that are suitable to localize. Month and day names comes
 to mind and maybe others, I'm not sure what the state of the code is,
 but I can say that, at least, the 'month' and 'day' modifiers does not
 behave in a localized way.

Can we spell the names differently but keep to the same field widths?
I can see where it might cause problems to change the widths --- other
than that, no objection.

regards, tom lane

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

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

Re: [HACKERS] to_char and i18n

[Manuel Sugawara]

Tom Lane [EMAIL PROTECTED] writes:

 Can we spell the names differently but keep to the same field widths?
 I can see where it might cause problems to change the widths --- other
 than that, no objection.

Quite impossible. But if someone is relaying in the current behavior
of to_char she might set lc_time accordingly as this parameter is not
used in the code, AFAIK

Regards,
Manuel.

---(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] to_char and i18n

[Gavin Sherry]

On Wed, 21 Dec 2005, Tom Lane wrote:

 Manuel Sugawara masm@fciencias.unam.mx writes:
  (Some time ago I proposed an--incomplete--patch and it was rejectd by
  Karel arguing that to_char functions should behave *exactly* the same
  way that they do in Oracle.)

 That is the accepted plan for to_char ... of course, if Oracle changes
 to_char every so often, it'll get more interesting to decide what to do.

There's some functionality in 10g which PostgreSQL does not have:

TZD - returns the short timezone string with daylight saving information,
eg: PDT

TZM - timezone offset minutes part

TZH - timezone offset hours part

TZR -  timezone region (US/Pacific, for example)

RR/ - accept 'rounded' years, eg 99-1-1 = 1999-1-1 (kind of pointless)

FF - specify how many digits to the right of the decimal place to display,
when looking at factions of seconds. Eg: HH:MM:SS.FF3 would produce
15:56:22.123

X - the local radix character. Eg: HH:MM:SSXFF would produce 15:56:22.123

E - Era name (like, Japanese Imperial) (kind of pointless)
EE - Full era name

DS - Locale formatted short date. For example, DD/MM/ for the Brits,
MM/DD/ for the Yanks

DL - Locale formatted long date. Eg: fmDay, dd. Month  in Germany

SCC - Like 'CC', but will carry a - (minus) for BC dates (I'm not sure if
this implies that Oracle wants BC dates to be marked 'BC'. I don't have
an Oracle system around at the moment to check though :-()

TS - Locale formatted short time.

YEAR - Year in words

SYEAR - Year in words, prefixed by minus sign for BC dates

S - , prefixed by minus sign for BC dates

Gavin

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

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

Re: [HACKERS] to_char and i18n

[Manuel Sugawara]

Gavin Sherry [EMAIL PROTECTED] writes:

 There's some functionality in 10g which PostgreSQL does not have:

Good to know. I'm not an Oracle expert, actually I knew this reading
an article in a past issue of the Oracle's magazine about i18n;
essentially they were talking about how easy was for an Oracle
database to get i18n as each parameter in the to_char functions will
behave accordingly to the i18n settings.

Regards,
Manuel.



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

Re: [HACKERS] Re: Which qsort is used

[Martijn van Oosterhout]

On Thu, Dec 22, 2005 at 01:43:34AM +0100, Manfred Koizar wrote:
 Qsorting N elements costs O(N*lnN), so excluding H elements from the
 sort reduces the cost by at least O(H*lnN).  The merge step costs O(N)
 plus some (=50%) more memory, unless someone knows a fast in-place
 merge.  So depending on the constant factors involved there might be a
 usable solution.

But where are you including the cost to check how many cells are
already sorted? That would be O(H), right? This is where we come back
to the issue that comparisons in PostgreSQL are expensive. The cpu_cost
in the tests I saw so far is unrealistically low.

 I've been playing with some numbers and assuming the constant factors
 to be equal for all the O()'s this method starts to pay off at
 H  for N
 20   100  20%
130  1000  13%
   800010   8%

Hmm, what are the chances you have 10 unordered items to sort and
that the first 8% will already be in order. ISTM that that probability
will be close enough to zero to not matter...

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpg6RoCjt5SA.pgp
Description: PGP signature