[GENERAL] Recovery_target_time misinterpreted?

[Klaus Ita]

Hello PG Experts!

I have restored a Database Cluster with a recovery_target_time set to

recovery_target_time =  '2013-07-27 21:20:17.127664+00'
recovery_target_inclusive = false


now it seems the restore rather restored to some point in time (rather the
18th than the 27th). Is there an explanation for this huge gap? Is that the
last 'consistent state'?

pg version:
xaxos_ch=> select version();

version
--
 PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-5) 4.7.2, 64-bit
(1 row)


somewhat irrelevant, as my question is more general?


thank you for your help!

klaus

Re: [GENERAL] Recovery_target_time misinterpreted?

[Albe Laurenz]

Klaus Ita wrote:
> I have restored a Database Cluster with a recovery_target_time set to
> 
> recovery_target_time =  '2013-07-27 21:20:17.127664+00'
> recovery_target_inclusive = false
> 
> 
> 
> now it seems the restore rather restored to some point in time (rather the 
> 18th than the 27th). Is
> there an explanation for this huge gap? Is that the last 'consistent state'?

Maybe the log entries created during restore can answer the question.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Type to to_char(d, 'J')?

[Marc Dahn]

Dear list,

Section 9.8 of the postgres (9.1) documentation says, on the
patterns for to_char(timestamp, pattern),:

J   Julian Day (days since November 24, 4714 BC at midnight)

This leaves open the question of what's actually returned.  At least
in astronomy, it is customary to have fractional days in JDs, whereas
postgres appears to always return an integer.  Is that guaranteed
behaviour?

The reason I'm asking is that I'd like to use the expression

to_char($1, 'J')::double precision 
  + to_char($1,'')::double_precision/86400 
  - 241

to compute the modified julian date (MJD) from a postgres timestamp
in some software that may be around for longer.  If postgres at some
point decided to return fractional days, that would blow up.


If integers are guaranteed, might I suggest to change the
documentation to read


  J Chronological Julian Day (integer number of days since November 24, 
4714 BC at midnight)


Cheers,

Marc



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!

[Stephen Brearley]

Hi Adrian

Thanks for getting back to me so soon!

1) Yes, I downloaded postgresql-9.2.4-1-windows.exe one click installer from
the EDB web page. I see the page format has changed a little recently, but
it is still the same file.
2) No, I have only ever tried to installed version 9.2
3) Yes -though there were errors reported in the install log, which I did
not get the first time I installed the program. The first time I installed
Postgresql, I got it to work and connect okay even in pgAdmin III. A bit
later, I forgot the password as I was using Navicat which remembers your
password, so I tried to re-install it (then remembering the password of
course!), but could not get a successful reinstall or re-connection using
pgAdmin.
4) Attached is the only pg_hba.conf file I seem to have.
Checked for this file in:
C:\Program Files\PostgreSQL\9.2.
C:\Users\SDB\AppData\Local\Temp

-Maybe I should have a pg_hba.conf that is not a sample file?

Also, do you think there us something wrong with my uninstall, such as not
removing files or hidden windows users properly? I do not know how to remove
these. I just went to Control Panel, Programs and Features, selected
Postgresql and Uninstall.

Both the install and uninstall should work flawlessly. The only thing I can
see is that I have installed the program once before, and I have put my data
on my d: drive to separate it from the program in case of software problems,
but I got Postgresql to correctly find my data before..

Thanks
  Stephen

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com] 
Sent: 29 July 2013 21:54
To: Stephen Brearley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400,
re-installation failure PLEASE CAN SOMEONE HELP!!

On 07/29/2013 12:41 PM, Adrian Klaver wrote:
> On 07/29/2013 12:23 PM, Stephen Brearley wrote:
>> Hi Folks/Alvaro/George Weaver/Sachin Kotwal
>>
>> <<< PLEASE CAN SOMEONE HELP ME!! >>>
>
> To start the process and catch up any one not following the first 
> time, some questions:
>
> 1) Exactly what are you installing i.e the one click installer from EDB ?
>
> 2) When you reinstalled did you change versions(say 9.2 --> 9.2)?
Oops meant 9.1 --> 9.2
>
> 3) When you do the install and get the connect error does the Process 
> Manager show Postgres running?
>
> 4) What is in your pg_hba.conf file?
>
>
>
>
>


--
Adrian Klaver
adrian.kla...@gmail.com


pg_hba.conf.sample
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Snapshot backups

[Alban Hertroys]

On Jul 31, 2013, at 7:13, Tom Lane  wrote:

> Jeff Janes  writes:
>> On Tuesday, July 30, 2013, James Sewell wrote:
>>> I understand what you are saying, and I understand how the backup_label
>>> works - but I still don't understand why the pg_start and pg_stop commands
>>> are REQUIRED when doing a snapshot backup to ensure data integrity.
>>> 
>>> Surely not using them and restoring a snapshot is the same as starting
>>> after a crash, and will result in log replay to get to the latest possible
>>> consistent state?
> 
>> That's true provided that all of your data is on a single volume, and you
>> trust your "snapshot" to be free of bugs.
> 
> James stated to begin with that his data was spread across multiple
> volumes, so it's definitely not safe for him to omit
> pg_start_backup/pg_stop_backup.

(…)

> For recovery from a tar backup to work, the archived copy of pg_control
> must point to a spot in the WAL sequence that is before any changes that
> could possibly not yet appear in any archived data files.  The purpose of
> pg_start_backup/pg_stop_backup is to provide the synchronization needed to
> meet this requirement.
> 
> The comparable case isn't possible for crash recovery, assuming that the
> OS and storage hardware implement fsync() correctly, because we'll have
> fsync'd the changes to foo down to disk before updating pg_control.
> 
> Now, if you instead take a filesystem snapshot (representing some
> instantaneous state of the disk contents) and run "tar" to copy that,
> you have a good backup, because you must have a copy of pg_control that
> will tell you to re-apply any changes that are missing from the data
> files, as well as WAL files that contain the needed records.  However,
> this is only certain if all that data is on *one* filesystem, because
> otherwise you can't be sure you have mutually consistent snapshots.
> And you're vulnerable to any bugs in the filesystem's snapshot
> implementation that might give you inconsistent copies of different
> files.  (Such bugs would probably be closely related to bugs in fsync
> ... but that doesn't mean they're necessarily exactly the same.)


That begs the question what happens in case of a crash or (worse) a partial 
crash when multiple file systems are involved.

Say, one tablespace is on a ZFS volume and one is on an UFS volume and the ZFS 
code crashes. That should result in a kernel panic, of course, in which case 
every file-system is stopped at the same moment and there should be a 
consistent "snapshot". But what if it doesn't trigger a panic?

And how would file-system recovery play into this? Would a journaled 
file-system roll back every segment involved in the same interrupted 
transaction, or could this create an inconsistent snapshot where the original 
situation (sans file-system recovery) would actually have been a preferable 
state to start from?

And what happens if one of these volumes would, for example, get ejected from a 
RAID controller (because all disks in it have triggered alarms, whether genuine 
or not) and the other volume would not get ejected?
Does the database abort work or does that result in an inconsistent state?
I suppose this case usually doesn't matter much, part of the database is gone 
completely anyway, but what if the RAID controller was wrong and the disks are 
actually just fine and come back once re-inserted? That has actually happened 
several times here, caused by SATA wires vibrating loose over time (took me a 
while to discover the cause); my databases have always been on a single volume 
though, so I've never had the opportunity to run into this.

I realise these are typical "what if" scenario's, so I suppose answering these 
doesn't have a high priority. It's just that I've been wondering/worrying about 
the seeming increase of people reporting database corruption - I was just 
wondering whether issues like these might play a part in that (I'm sure large 
part of it is just more people using PG these days).

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!

[Alban Hertroys]

On Jul 31, 2013, at 12:07, "Stephen Brearley"  wrote:

> Both the install and uninstall should work flawlessly. The only thing I can
> see is that I have installed the program once before, and I have put my data
> on my d: drive to separate it from the program in case of software problems,
> but I got Postgresql to correctly find my data before..


If Postgres is having any issues starting up, details should be in the log 
file. I don't know where that file gets written on a Windows system though, 
perhaps it ends up in the Windows Event log?

What procedure did you follow to move your data to the D-drive? That seems a 
likely cause of trouble, especially if the database was running while you did 
that - you may be looking at an unrecoverable database snapshot if you didn't 
take precautions, in which case the database would refuse to start up.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!

[Stephen Brearley]

Hi Thomas

 

**Thanks for getting back to me**

 

In answer to your points:

 

1)  I could not get Postgresql to run correctly, so I assumed it could
be a bug. I checked the documentation for bug reports, and this seemed to
suggest that anything that appears to be a bug should be reported. If a
decision is made that this is not a bug, it would be helpful if someone sent
a short message to say so, and tell me to report my problem elsewhere.
Otherwise I just sit there waiting and nothing happens!!

 

5) There does not appear to be a specific 'Uninstall' option on the Windows
Start menu, so I went to Control Panel, Programs and features, PostgreSQL
9.2 Uninstall/Change option. Is this correct?

 

6) I have not performed an upgrade of Postgres. Inititially, I had no
Postgres, then I installed postgresql-9.2.4-1-windows.exe, then I forgot my
password as was using Navicat that remembers it automatically and tried to
use pgAdmin which doesn't, so I tried to reinstall (then of course
remembered my password!). I have not been able to get Postgres to work
correctly/connect since.

 

-Thank-you for confirming there are no hidden users -I spent many web
searches trying to work out how to remove/reset these (whew)!!

 

No, I did not verify that Postgres was running, but have since checked in
the Task Manager and it is running when I fire up pgAdmin. However, I should
not get any error message during the install process, and to me this clearly
suggests something is wrong, as the log I attached shows. Any ideas what is
wrong please?

 

I would not normally do anything in the Registry, as I am aware that is
asking for trouble. However, I hadn't noted that I could specify a data
location on my original install, so I checked the web and this appeared to
be the only way to do this for some programs. I edited
PostgreSQL|Installations|postgresql-9.2|Data Directory and changed the
default from the C: drive to my folder on the D: drive as
D:\_SDB\Database\RDBMS\PostgreSQL\9.2\data. This worked fine until I had the
password problems I mentioned above, and tried to do a reinstall, so I don't
think this registry edit is causing the problem. I have only recently found
the postgresql.conf option on pgAdmin.

 

Where/what is init.db? I don't have any instructions for this file. Not sure
what you mean.

 

I too am now puzzled about pg.log. I think I believed this was related to
'hidden' Windows users which you say are not now used, so please disregard
this point now.

 

I realise that new versions of Postgres go into a different file system on
the C: drive, and that I can't just move the system files across or rename
directories. However, in this case I am trying to reinstall exactly the same
version, so everything should be the same, so surely it should be okay to
move my old data out of the way, remove the folder it was in, perform the
reinstall and move the data back? Maybe it isn't. I was not aware of this
pg_dump/pg_restore, as these instructions are intended for upgrades, which
of course I would not have read, as I am trying to perform a reinstall of
the same version. Therefore I will try to perform a dump, then I guess
remove all my Postgres folders in D:, then reinstall Postgres and run
pg_restore to bring everything back in (though this doesn't explain the
errors in the installation log).

 

Regarding the password, I have typed in the same password which I used
everytime I tried to install Postgres, so I started to think there was an
install problem as listed in the installation log. The installation process
paused with a popup error message which I did not have the first time I
installed Postgres, and the log file indicates a return code other that '0'
near the end, so there is a problem there. Are there any restrictions on
characters that can be used in passwords? I used a 'complex' password
selecting one character from: # - < > ? @ \ /. If I entered the wrong
password several times and performed a reinstall, does Postgres continue to
lock me out after so many attempts, even if I perform a reinstall? Could
this be the problem, and if so, how do I get around it? 

 

Apologies this is a long response, but you have raised a number of important
points.

 

Many thanks

  Stephen

 


*

Stephen Brearley wrote on 29.07.2013 21:23:

> 1.Explain why my bug report has not been responded to

 

Because it's not a bug as you simply upgraded incorrectly.

 

> 5.Tell me how I should uninstall Postgres, if I am doing this wrong

 

You did run the "Uninstall", did you?

 

> 6.Tell me how to remove any hidden users or how I should reset/remove
directories etc.

 

There are no hidden users - especially not with 9.2 (previous versions did
create a new Windows user, but that is not the case with 9.2). Unfortunately
you failed to mention from which version you upgraded.

 

> On re-installing Postgres, I have not been able to get it to work.

>

Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!

[Stephen Brearley]

Hi George

 

Apologies to be rather grumpy, but I have been waiting a long time to get a
glimmer of help!! Feeling a bit more positive now.

 

Thanks for coming back with more specifics. I haven't done anything since
your last email, as I have tried many times before what you (generally)
suggested, but need more information to find a solution.

However, to answer your questions: 

 

1)  I have used Start:Control Panel:Programs &
Features:PostgreSQL:Uninstall/Change option to remove. Is there a special
tool that does a cleaner job?

2)  I don't know how to remove Postgres from the registry. I presume the
uninstall method I have listed above does this, but seems to leave the
following lines (presumably to keep my data intact):

  PostgreSQL:Installations:postgresql-9.2

  PostgreSQL:Services:postgresql-9.2

 

Please could you tell me how to clean Postgres from the registry, if this is
still necessary?

 

3)  I don't know how to remove the Windows postgres user, which I have
been struggling to do for ages on Vista, but can't find out how to do this.
However, Thomas Kellerer has told me that this is not necessary any more.
Which of you is correct?

4)  I do not have anything in C:\ProgramData\ pertaining to Postgres, as
the installer put all my Postgres files in C:\Program Files\PostgreSQL\9.2,
with my data set to C:\Program Files\PostgreSQL\9.2\data. I've put my data
on the D: drive and re-named it to data_old, and deleted the contents of
\data. Is this enough do you think?

5)  My machine is x32, so I have been using the 32 bit version.

 

Please can you answer my points above before I can try a reinstall?

 

Much thanks

  Stephen 

 

- Original Message - 

From: Stephen Brearley

 



 

>I need someone to please look at my error log which I posted nearly six 

>weeks ago, and tell me what is wrong.

 

Are you trying to tell us how to help you?  Or are you looking for help?

 

Since my e-mail suggesting that you work to get PostgreSQL to install 

cleanly, did you:

 

1. Unistall PostrgreSQL?  If so how?

2. Remove all traces of PostgreSQL from the registry?

3. Remove the Windows postgres user?

4. Rename your current data directory to something other than

 

 - C:\Program Files\PostgreSQL\9.2\data

 - C:\ProgramData\PostgreSQL\9.2\data

 

5. Having completed the above, try using the installer from 

http://www.enterprisedb.com/postgresql-924-installers-win64?ls=Crossover&typ
e=Crossover

to re-install PostgreSQL?

 

If you did all of the above, what happened?

 



 

George

Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!

[Stephen Brearley]

Hi Raghu

 

Thanks for offering to help, but I don't have Skype, as I am concerned about
security issues. There seem to be some other companies out there which offer
a similar service, but I haven't yet determined which would best suit. I'm
getting nagged from friends about this too..

 

Any thoughts from my subsequent posts please? I'm now thinking that because
I manually moved my data to the D: drive and re-directed the regedit
reference, the Windows Control Panel uninstall function may not have removed
everything correctly, specifically the Windows user -however Thomas Kellerer
says this is not used any more (as I understand him), but Postgres does not
recognise the password I entered during re-installation (of the same
version). So I'm still rather stuck, but I'll try to perform a pg_dump.

 

Thanks

  Stephen 

 

From: raghu ram [mailto:raghuchenn...@gmail.com] 
Sent: 31 July 2013 12:45
To: Stephen Brearley
Subject: Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400,
re-installation failure PLEASE CAN SOMEONE HELP!!

 

Hi Stephen,

 

I would like to help you on this activity. Could you please share your
SkypeID. 

 

Thanks & Regards

Raghu Ram

SkypeID: raghu.ramedb

 

On Wed, Jul 31, 2013 at 4:58 PM, Stephen Brearley
 wrote:

Hi Thomas

 

**Thanks for getting back to me**

 

In answer to your points:

 

1)  I could not get Postgresql to run correctly, so I assumed it could
be a bug. I checked the documentation for bug reports, and this seemed to
suggest that anything that appears to be a bug should be reported. If a
decision is made that this is not a bug, it would be helpful if someone sent
a short message to say so, and tell me to report my problem elsewhere.
Otherwise I just sit there waiting and nothing happens!!

 

5) There does not appear to be a specific 'Uninstall' option on the Windows
Start menu, so I went to Control Panel, Programs and features, PostgreSQL
9.2 Uninstall/Change option. Is this correct?

 

6) I have not performed an upgrade of Postgres. Inititially, I had no
Postgres, then I installed postgresql-9.2.4-1-windows.exe, then I forgot my
password as was using Navicat that remembers it automatically and tried to
use pgAdmin which doesn't, so I tried to reinstall (then of course
remembered my password!). I have not been able to get Postgres to work
correctly/connect since.

 

-Thank-you for confirming there are no hidden users -I spent many web
searches trying to work out how to remove/reset these (whew)!!

 

No, I did not verify that Postgres was running, but have since checked in
the Task Manager and it is running when I fire up pgAdmin. However, I should
not get any error message during the install process, and to me this clearly
suggests something is wrong, as the log I attached shows. Any ideas what is
wrong please?

 

I would not normally do anything in the Registry, as I am aware that is
asking for trouble. However, I hadn't noted that I could specify a data
location on my original install, so I checked the web and this appeared to
be the only way to do this for some programs. I edited
PostgreSQL|Installations|postgresql-9.2|Data Directory and changed the
default from the C: drive to my folder on the D: drive as
D:\_SDB\Database\RDBMS\PostgreSQL\9.2\data. This worked fine until I had the
password problems I mentioned above, and tried to do a reinstall, so I don't
think this registry edit is causing the problem. I have only recently found
the postgresql.conf option on pgAdmin.

 

Where/what is init.db? I don't have any instructions for this file. Not sure
what you mean.

 

I too am now puzzled about pg.log. I think I believed this was related to
'hidden' Windows users which you say are not now used, so please disregard
this point now.

 

I realise that new versions of Postgres go into a different file system on
the C: drive, and that I can't just move the system files across or rename
directories. However, in this case I am trying to reinstall exactly the same
version, so everything should be the same, so surely it should be okay to
move my old data out of the way, remove the folder it was in, perform the
reinstall and move the data back? Maybe it isn't. I was not aware of this
pg_dump/pg_restore, as these instructions are intended for upgrades, which
of course I would not have read, as I am trying to perform a reinstall of
the same version. Therefore I will try to perform a dump, then I guess
remove all my Postgres folders in D:, then reinstall Postgres and run
pg_restore to bring everything back in (though this doesn't explain the
errors in the installation log).

 

Regarding the password, I have typed in the same password which I used
everytime I tried to install Postgres, so I started to think there was an
install problem as listed in the installation log. The installation process
paused with a popup error message which I did not have the first time I
installed Postgres, and the log file indicates a return code other that '0'
near the

Re: [GENERAL] Snapshot backups

[Tom Lane]

Alban Hertroys  writes:
> That begs the question what happens in case of a crash or (worse) a partial 
> crash when multiple file systems are involved.

As long as the OS+hardware honors the contract of fsync(), everything's
fine.  If the storage system loses data that it claims to have fsync'd to
stable storage, there's not much we can do about that, except recommend
that you have a backup plan.

In practice, the more complicated your storage infrastructure is, the more
likely it is to have bugs ...

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!

[Alban Hertroys]

On Jul 31, 2013, at 14:07, "Stephen Brearley"  wrote:

> Hi Alban
> 
> Much thanks for getting back to me!
> 
> The event file gets written to (as attached):
> C:\Users\SDB\AppData\Local\Temp

That's the installation log, not the postgres log.
I suspect the postgres log is either in the Windows Event Log (available from 
the Control Panel, perhaps under Administrative Tools or something similar) or 
in a file somewhere in the Postgres installation directory, most likely in a 
directory named log. I don't know how EnterpriseDB sets things up though, 
certainly not in Windows.

Nevertheless, from the installation log it can be seen that initdb was run and 
that after that the database started up successfully.

It also shows that some module named "adminpack" failed to install. I have no 
idea what that is though, I don't have it on my UNIX system.
See:

Script output:
 Installing the adminpack module in the postgres database...
Executing 'C:\Users\SDB\AppData\Local\Temp\rad5E7BA.bat'...
Couldn't find the output file...
Failed to install the 'adminpack' module in the 'postgres' database
loadmodules.vbs ran to completion

Script stderr:
 Program ended with an error exit code

Error running cscript //NoLogo "C:\Program 
Files\PostgreSQL\9.2\installer\server\loadmodules.vbs" "postgres" "" 
"C:\Program Files\PostgreSQL\9.2" "D:\_SDB\Database\RDBMS\PostgreSQL\9.2\data" 
5432 : Program ended with an error exit code

So, whatever causes your issue is most likely due to something that happened 
after the install.

> I copied the contents of the /data directory to the D: drive, then changed
> the folder reference in the Registry. However, after this I was able to

Did you first stop the database? If not, chances are you didn't get a 
consistent copy, because the database was working on those files while you were 
copying them.
Oh, and I assume your D: drive isn't some kind of network drive or something, 
but an actual disk with NTFS on it?

> create tables in Navicat without any problems, until I tried to use pgAdmin
> when it required my password, which I had forgotten. Then I tried to
> reinstall, and started having these problems..

So all the above is from before you uninstalled/reinstalled? Or is that log 
file you attached from the reinstall?
In that case, it seems to have known about your data-directory on D: during the 
install, looking at this snippet:

Loading additional SQL modules...
Executing cscript //NoLogo "C:\Program 
Files\PostgreSQL\9.2\installer\server\loadmodules.vbs" "postgres" "" 
"C:\Program Files\PostgreSQL\9.2" "D:\_SDB\Database\RDBMS\PostgreSQL\9.2\data" 
5432
Script exit code: 2

It exits with code 2, which indicates an error. Perhaps the logs (see earlier) 
say why.

> I'm being recommended to try running pg_dump by Thomas Kellerer, then remove
> all my data folders and a reinstall. I get the feeling it is not moving my
> data to the D: drive that has caused the problem, but that the Uninstall has
> not cleaned this out correctly, as I moved my data to D:/../data_old to
> enable Postgres to reinstall and used the installation Wizard to point to my
> data location, but still get installation errors and a connection problem,
> despite using the 'correct' password.

I doubt that the uninstaller would remove your data directory, because then you 
would lose your data. You don't want that, it's generally better to leave 
decisions like that up to the administrator (you). That data is more often than 
not quite valuable ;)

> When I start pgAdmin, TaskManager shows that Postgres is running, -so I
> don't think I have an unrecoverable database snapshot?

That Postgres appears to be running is encouraging.

Something worth verifying; open a command prompt and type netstat -an and look 
whether the port you configured Postgres to run on is being listened on.

If it is, then your issue is just a connection issue. Perhaps you could 
elaborate on what you're doing to connect and what error you receive?

It's possible that you locked yourself out through the pg_hba.conf file or that 
you need to reset the password for the postgres user. But we don't know that 
yet...


> -Original Message-
> From: Alban Hertroys [mailto:haram...@gmail.com] 
> Sent: 31 July 2013 11:48
> To: Stephen Brearley
> Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400,
> re-installation failure PLEASE CAN SOMEONE HELP!!
> 
> On Jul 31, 2013, at 12:07, "Stephen Brearley" 
> wrote:
> 
>> Both the install and uninstall should work flawlessly. The only thing 
>> I can see is that I have installed the program once before, and I have 
>> put my data on my d: drive to separate it from the program in case of 
>> software problems, but I got Postgresql to correctly find my data before..
> 
> 
> If Postgres is having any issues starting up, details should be in the log
> file. I don't know where that file gets written on a Windows system tho

Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!

[hidayat365]

Hi Stephen,

As per my experience, installing postgresql on windows machine automatically 
create postgres user. When you uninstall it, the postgres user doesn't 
automatically removed, you must remove it manually.

So, when you install postgres for the second time, it will use existing 
postgres user which already exist therefore uses the password which you have 
forgotten.

Try uninstall postgresql and remove postgres user, or alternatively, reset 
postgres user password from Computer Management.

Regards,
Nur Hidayat



.
Sent from my BlackBerry®
powered by Sinyal Kuat INDOSAT

-Original Message-
From: "Stephen Brearley" 
Sender: pgsql-general-owner@postgresql.orgDate: Wed, 31 Jul 2013 12:28:57 
To: 
Cc: 
Subject: Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, 
re-installation failure PLEASE CAN SOMEONE HELP!!

Hi Thomas

 

**Thanks for getting back to me**

 

In answer to your points:

 

1)  I could not get Postgresql to run correctly, so I assumed it could
be a bug. I checked the documentation for bug reports, and this seemed to
suggest that anything that appears to be a bug should be reported. If a
decision is made that this is not a bug, it would be helpful if someone sent
a short message to say so, and tell me to report my problem elsewhere.
Otherwise I just sit there waiting and nothing happens!!

 

5) There does not appear to be a specific 'Uninstall' option on the Windows
Start menu, so I went to Control Panel, Programs and features, PostgreSQL
9.2 Uninstall/Change option. Is this correct?

 

6) I have not performed an upgrade of Postgres. Inititially, I had no
Postgres, then I installed postgresql-9.2.4-1-windows.exe, then I forgot my
password as was using Navicat that remembers it automatically and tried to
use pgAdmin which doesn't, so I tried to reinstall (then of course
remembered my password!). I have not been able to get Postgres to work
correctly/connect since.

 

-Thank-you for confirming there are no hidden users -I spent many web
searches trying to work out how to remove/reset these (whew)!!

 

No, I did not verify that Postgres was running, but have since checked in
the Task Manager and it is running when I fire up pgAdmin. However, I should
not get any error message during the install process, and to me this clearly
suggests something is wrong, as the log I attached shows. Any ideas what is
wrong please?

 

I would not normally do anything in the Registry, as I am aware that is
asking for trouble. However, I hadn't noted that I could specify a data
location on my original install, so I checked the web and this appeared to
be the only way to do this for some programs. I edited
PostgreSQL|Installations|postgresql-9.2|Data Directory and changed the
default from the C: drive to my folder on the D: drive as
D:\_SDB\Database\RDBMS\PostgreSQL\9.2\data. This worked fine until I had the
password problems I mentioned above, and tried to do a reinstall, so I don't
think this registry edit is causing the problem. I have only recently found
the postgresql.conf option on pgAdmin.

 

Where/what is init.db? I don't have any instructions for this file. Not sure
what you mean.

 

I too am now puzzled about pg.log. I think I believed this was related to
'hidden' Windows users which you say are not now used, so please disregard
this point now.

 

I realise that new versions of Postgres go into a different file system on
the C: drive, and that I can't just move the system files across or rename
directories. However, in this case I am trying to reinstall exactly the same
version, so everything should be the same, so surely it should be okay to
move my old data out of the way, remove the folder it was in, perform the
reinstall and move the data back? Maybe it isn't. I was not aware of this
pg_dump/pg_restore, as these instructions are intended for upgrades, which
of course I would not have read, as I am trying to perform a reinstall of
the same version. Therefore I will try to perform a dump, then I guess
remove all my Postgres folders in D:, then reinstall Postgres and run
pg_restore to bring everything back in (though this doesn't explain the
errors in the installation log).

 

Regarding the password, I have typed in the same password which I used
everytime I tried to install Postgres, so I started to think there was an
install problem as listed in the installation log. The installation process
paused with a popup error message which I did not have the first time I
installed Postgres, and the log file indicates a return code other that '0'
near the end, so there is a problem there. Are there any restrictions on
characters that can be used in passwords? I used a 'complex' password
selecting one character from: # - < > ? @ \ /. If I entered the wrong
password several times and performed a reinstall, does Postgres continue to
lock me out after so many attempts, even if I perform a reinstall? Could
this be the problem, and if so, how do I get around it? 

 

Apolog

Re: [GENERAL] Fastest Index/Algorithm to find similar sentences

[Beena Emerson]

On Sat, Jul 27, 2013 at 10:34 PM, Janek Sendrowski  wrote:

> Hi Sergey Konoplev,
>
> If I'm searching for a sentence like "The tiger is the largest cat
> species" for example.
>
> I can only find the sentences, which include the words "tiger, largest,
> cat, species", but I also like to have the sentences with only three or
> even two of these words.
>
> Janek
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hi,

You may use similarity functions of pg_trgm.

Example:
=# \d+ test
Table "public.test"
 Column | Type | Modifiers | Storage  | Stats target | Description
+--+---+--+--+-
 col| text |   | extended |  |
Indexes:
"test_idx" gin (col gin_trgm_ops)
Has OIDs: no

# SELECT * FROM test;
   col
-
 The tiger is the largest cat species
 The cheetah is the fastest  cat species
 The peacock is the largest bird species
(3 rows)

=# SELECT show_limit();
 show_limit

0.3
(1 row)

=# SELECT col, similarity(col, 'The tiger is the largest cat species') AS
sml
  FROM test WHERE col % 'The tiger is the largest cat species'
  ORDER BY sml DESC, col;
   col   |   sml
-+--
 The tiger is the largest cat species|1
 The peacock is the largest bird species | 0.51
 The cheetah is the fastest  cat species | 0.47
(3 rows)

=# SELECT set_limit(0.5);
 set_limit
---
   0.5
(1 row)

=# SELECT col, similarity(col, 'The tiger is the largest cat species') AS
sml
  FROM test WHERE col % 'The tiger is the largest cat species'
  ORDER BY sml DESC, col;
   col   |   sml
-+--
 The tiger is the largest cat species|1
 The peacock is the largest bird species | 0.51
(2 rows)

=# SELECT set_limit(0.9);
 set_limit
---
   0.9
(1 row)

=# SELECT col, similarity(col, 'The tiger is the largest cat species') AS
sml
  FROM test WHERE col % 'The tiger is the largest cat species'
  ORDER BY sml DESC, col;
 col  | sml
--+-
 The tiger is the largest cat species |   1
(1 row)


When you set a higher limit, you get more exact matches.


-- 
Beena Emerson

Re: [GENERAL] Fastest Index/Algorithm to find similar sentences

[Beena Emerson]

I am sorry, I just re-read your mail and realized  you have already tried
with pg_trgm.



On Wed, Jul 31, 2013 at 7:23 PM, Beena Emerson wrote:

> On Sat, Jul 27, 2013 at 10:34 PM, Janek Sendrowski  wrote:
>
>> Hi Sergey Konoplev,
>>
>> If I'm searching for a sentence like "The tiger is the largest cat
>> species" for example.
>>
>> I can only find the sentences, which include the words "tiger, largest,
>> cat, species", but I also like to have the sentences with only three or
>> even two of these words.
>>
>> Janek
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> Hi,
>
> You may use similarity functions of pg_trgm.
>
> Example:
> =# \d+ test
> Table "public.test"
>  Column | Type | Modifiers | Storage  | Stats target | Description
> +--+---+--+--+-
>  col| text |   | extended |  |
> Indexes:
> "test_idx" gin (col gin_trgm_ops)
> Has OIDs: no
>
> # SELECT * FROM test;
>col
> -
>  The tiger is the largest cat species
>  The cheetah is the fastest  cat species
>  The peacock is the largest bird species
> (3 rows)
>
> =# SELECT show_limit();
>  show_limit
> 
> 0.3
> (1 row)
>
> =# SELECT col, similarity(col, 'The tiger is the largest cat species') AS
> sml
>   FROM test WHERE col % 'The tiger is the largest cat species'
>   ORDER BY sml DESC, col;
>col   |   sml
> -+--
>  The tiger is the largest cat species|1
>  The peacock is the largest bird species | 0.51
>  The cheetah is the fastest  cat species | 0.47
> (3 rows)
>
> =# SELECT set_limit(0.5);
>  set_limit
> ---
>0.5
> (1 row)
>
> =# SELECT col, similarity(col, 'The tiger is the largest cat species') AS
> sml
>   FROM test WHERE col % 'The tiger is the largest cat species'
>   ORDER BY sml DESC, col;
>col   |   sml
> -+--
>  The tiger is the largest cat species|1
>  The peacock is the largest bird species | 0.51
> (2 rows)
>
> =# SELECT set_limit(0.9);
>  set_limit
> ---
>0.9
> (1 row)
>
> =# SELECT col, similarity(col, 'The tiger is the largest cat species') AS
> sml
>   FROM test WHERE col % 'The tiger is the largest cat species'
>   ORDER BY sml DESC, col;
>  col  | sml
> --+-
>  The tiger is the largest cat species |   1
> (1 row)
>
>
> When you set a higher limit, you get more exact matches.
>
>
> --
> Beena Emerson
>
>


-- 
Beena Emerson

Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!

[Adrian Klaver]

On 07/31/2013 04:28 AM, Stephen Brearley wrote:

Hi Thomas

**Thanks for getting back to me**


I would not normally do anything in the Registry, as I am aware that is
asking for trouble. However, I hadn’t noted that I could specify a data
location on my original install, so I checked the web and this appeared
to be the only way to do this for some programs. I edited
PostgreSQL|Installations|postgresql-9.2|Data Directory and changed the
default from the C: drive to my folder on the D: drive as
D:\_SDB\Database\RDBMS\PostgreSQL\9.2\data. This worked fine until I had
the password problems I mentioned above, and tried to do a reinstall, so
I don’t think this registry edit is causing the problem.


Actually, per Albans comment it is. The installer is seeing the data 
directory on D:\. Might try undoing that registry entry.


The basic problem is you have two instances of a Postgres cluster(one on 
C:\ and one on D:\) and one instance of Postgres running with confusing 
information on where to find the appropriate cluster(the registry entry, 
the recent install information). To make head way on this you need to 
unclutter things. Before we go down that path, how important is the data 
in the database you initially created?



--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Recovery_target_time misinterpreted?

[Klaus Ita]

2013-07-30 11:15:15 UTC <%> LOG:  starting point-in-time recovery to
2013-07-27 21:20:17.127664+00
2013-07-30 11:15:15 UTC <%> LOG:  restored log file
"00010230005C" from archive
2013-07-30 11:15:15 UTC <%> LOG:  restored log file
"00010230005A" from archive
2013-07-30 11:15:15 UTC <%> LOG:  redo starts at 230/5ACD7CC0
...
...
...
2013-07-30 14:28:45 UTC <%> LOG:  restored log file
"000102640002" from archive
2013-07-30 14:28:45 UTC <%> LOG:  unexpected pageaddr 263/C706C000 in log
file 612, segment 2, offset 442368
2013-07-30 14:28:45 UTC <%> LOG:  redo done at 264/20698A8
2013-07-30 14:28:45 UTC <%> LOG:  last completed transaction was at log
time 2013-07-18 11:42:22.121512+00
2013-07-30 14:28:45 UTC <%> LOG:  restored log file
"000102640002" from archive
cp: cannot stat
`/var/tmp/xlogs_recovered_2013-07-30/wal_files/0002.history*': No such
file or directory
mv: cannot stat `/tmp/0002.history': No such file or directory
2013-07-30 14:28:45 UTC <%> LOG:  selected new timeline ID: 2
cp: cannot stat
`/var/tmp/xlogs_recovered_2013-07-30/wal_files/0001.history*': No such
file or directory
mv: cannot stat `/tmp/0001.history': No such file or directory
2013-07-30 14:28:45 UTC <%> LOG:  archive recovery complete
2013-07-30 14:29:09 UTC <%> LOG:  autovacuum launcher started
2013-07-30 14:29:09 UTC <%> LOG:  database system is ready to accept
connections


well, that does not indicate anything for me.



On Wed, Jul 31, 2013 at 9:37 AM, Albe Laurenz wrote:

> Klaus Ita wrote:
> > I have restored a Database Cluster with a recovery_target_time set to
> >
> > recovery_target_time =  '2013-07-27 21:20:17.127664+00'
> > recovery_target_inclusive = false
> >
> >
> >
> > now it seems the restore rather restored to some point in time (rather
> the 18th than the 27th). Is
> > there an explanation for this huge gap? Is that the last 'consistent
> state'?
>
> Maybe the log entries created during restore can answer the question.
>
> Yours,
> Laurenz Albe
>

Re: [GENERAL] more fun with building 9.3beta2

[Rob Sargent]

On 07/30/2013 08:25 PM, Adrian Klaver wrote:

On 07/30/2013 07:15 PM, Rob Sargent wrote:

I'm not getting the xml2 and uuid-ossp control files delivered to the
extension directory

I've moved to a CentOS box (and dropped pam):

cat /etc/system-release
CentOS release 6.4 (Final)
uname -a
Linux co-app-jl-d001 2.6.32-358.11.1.el6.x86_64 #1 SMP Wed Jun 12
03:34:52 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux


pg_config --configure
'--prefix=/usr/local/pgsql-9.3b2' '--with-ossp-uuid' '--with-libxml'
'--with-libxslt' '--with-openssl' '--with-python'

make world says "PostgreSQL, contrib, and documentation successfully
made. Ready to install."

 From "make install" I see only plpython[2]u.control and sql shipped to
/usr/local/pgsql-9.3b2/share/extension (though pgplsql files are there
as well).

template1=# select * from pg_available_extensions()
template1-# ;
 name| default_version | comment
+-+--- 


  plpgsql| 1.0 | PL/pgSQL procedural language
  plpythonu  | 1.0 | PL/PythonU untrusted procedural 
language
  plpython2u | 1.0 | PL/Python2U untrusted procedural 
language

(3 rows)

Any hints appreciated.


http://www.postgresql.org/docs/9.3/static/install-procedure.html

If you built the world above, type instead:

gmake install-world
This also installs the documentation.



rjs






Yes.  Of course.  INSTALL is a long read. I don't recall ever having 
done that step in the past when adding these to modules in v8, v9.[1,2]. 
But I'm old and forgetful.


Thanks a ton.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] more fun with building 9.3beta2

[Adrian Klaver]

On 07/31/2013 08:08 AM, Rob Sargent wrote:

On 07/30/2013 08:25 PM, Adrian Klaver wrote:




http://www.postgresql.org/docs/9.3/static/install-procedure.html

If you built the world above, type instead:

gmake install-world
This also installs the documentation.



rjs







Yes.  Of course.  INSTALL is a long read. I don't recall ever having
done that step in the past when adding these to modules in v8, v9.[1,2].
But I'm old and forgetful.


More than likely you did what I usually do, drill down into the 
appropriate contrib module directory and run make install from there.




Thanks a ton.




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] reindex log

[Rob Sargent]

Any interest out there in having the table named before the re-index begins?

Now we see 'NOTICE:  table "pg_catalog.pg_db_role_setting" was reindexed'
I would rather see 'NOTICE:  reindexing table 
"pg_catalog.pg_db_role_setting"'


When the output stalls on a larger table, I would like to know which 
table it is.


rjs



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Roadmap for Postgres on AIX

[KimmoHintikka]

Hi,

I got this page by accident trying find more info on tests EnterpriseDB did
with PostgressSQL Power Linux. 

Anyway as I work IBM Power sales rep for Finland myself, please sent me
little bit more info on what kind of test you have in mind. (Do you just
want few core's to test it works / or bigger instance for performance
testing? For how long?)

I can see when we would have test machine available trough partner world.

If you don't want to ping your specific test scenario on public forum please
sent me a mail kimmo.hinti...@ie.ibm.com or ping on  linkedin
  

Otherwise just answer here and I get back to you. 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Roadmap-for-Postgres-on-AIX-tp5748857p5765794.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] demystifying nested loop vs. merge join query plan choice

[Sandeep Gupta]

I have two  postgres instances each with a database of same schema. The
dataset in both is ''same'' but for randomness i.e.  both contain two
tables pc(did) and tc(pid, did) that have almost
same number of rows and have been generate from same distribution.

However the query plan for the join turns out to be completely different:
on one join takes 2.3 secs while on the other it takes 7 secs.


Here are the statistics:

for database 1:
 size of tc table:  49987585
 size of pc table: 499616

join plan:

QUERY PLAN

---
 Aggregate  (cost=1534125.08..1534125.09 rows=1 width=0) (actual
time=8473.296..8473.296 rows=1 loops=1)
   ->  Merge Join  (cost=2.48..1514765.90 rows=7743672 width=0) (actual
time=0.084..8409.065 rows=998038 loops=1)
 Merge Cond: (pc.did = tc.did)
 ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..12987.04
rows=499616 width=4) (actual time=0.016..58.202 rows=499616 loops=1)
   Heap Fetches: 0
 ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..1298125.32
rows=49987616 width=4) (actual time=0.014..5141.809 rows=49997291 loops=1)
   Heap Fetches: 0
 Total runtime: 8473.337 ms
'

Query Running time:  5135


for database 2:
  size of tc table: 50012415
  size of pc table: 500384

   QUERY
PLAN

 Aggregate  (cost=35279895.52..35279895.53 rows=1 width=0) (actual
time=2501.970..2501.970 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..35276697.82 rows=1279080 width=0) (actual
time=0.038..2418.766 rows=1000834 loops=1)
 ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..15224.56
rows=500384 width=4) (actual time=0.017..109.080 rows=500384 loops=1)
   Heap Fetches: 500384
 ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..70.44
rows=3 width=4) (actual time=0.004..0.004 rows=2 loops=500384)
   Index Cond: (did = pc.did)
   Heap Fetches: 1000834
 Total runtime: 2502.017 ms

Query running time: 2090.388 ms

My question is why is the query plan so different for two datasets that are
really exactly the same. And how can i force the plan to be nested index
scan on
database 1 .


-Sandeep

Re: [GENERAL] demystifying nested loop vs. merge join query plan choice

[Pavel Stehule]

Hello

do you have same configuration?

Regards

Pavel

2013/7/31 Sandeep Gupta :
> I have two  postgres instances each with a database of same schema. The
> dataset in both is ''same'' but for randomness i.e.  both contain two tables
> pc(did) and tc(pid, did) that have almost
> same number of rows and have been generate from same distribution.
>
> However the query plan for the join turns out to be completely different: on
> one join takes 2.3 secs while on the other it takes 7 secs.
>
>
> Here are the statistics:
>
> for database 1:
>  size of tc table:  49987585
>  size of pc table: 499616
>
> join plan:
>
> QUERY PLAN
> ---
>  Aggregate  (cost=1534125.08..1534125.09 rows=1 width=0) (actual
> time=8473.296..8473.296 rows=1 loops=1)
>->  Merge Join  (cost=2.48..1514765.90 rows=7743672 width=0) (actual
> time=0.084..8409.065 rows=998038 loops=1)
>  Merge Cond: (pc.did = tc.did)
>  ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..12987.04
> rows=499616 width=4) (actual time=0.016..58.202 rows=499616 loops=1)
>Heap Fetches: 0
>  ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..1298125.32
> rows=49987616 width=4) (actual time=0.014..5141.809 rows=49997291 loops=1)
>Heap Fetches: 0
>  Total runtime: 8473.337 ms
> '
>
> Query Running time:  5135
>
>
> for database 2:
>   size of tc table: 50012415
>   size of pc table: 500384
>
>QUERY
> PLAN
> 
>  Aggregate  (cost=35279895.52..35279895.53 rows=1 width=0) (actual
> time=2501.970..2501.970 rows=1 loops=1)
>->  Nested Loop  (cost=0.00..35276697.82 rows=1279080 width=0) (actual
> time=0.038..2418.766 rows=1000834 loops=1)
>  ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..15224.56
> rows=500384 width=4) (actual time=0.017..109.080 rows=500384 loops=1)
>Heap Fetches: 500384
>  ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..70.44
> rows=3 width=4) (actual time=0.004..0.004 rows=2 loops=500384)
>Index Cond: (did = pc.did)
>Heap Fetches: 1000834
>  Total runtime: 2502.017 ms
>
> Query running time: 2090.388 ms
>
> My question is why is the query plan so different for two datasets that are
> really exactly the same. And how can i force the plan to be nested index
> scan on
> database 1 .
>
>
> -Sandeep
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] demystifying nested loop vs. merge join query plan choice

[Sandeep Gupta]

Hi Pavel,

 Yes. The postgresql.conf is exactly the same.  The have the same index and
clustering and are on the same compute node as well but running on
different ports.

-Sandeep



On Wed, Jul 31, 2013 at 3:14 PM, Pavel Stehule wrote:

> Hello
>
> do you have same configuration?
>
> Regards
>
> Pavel
>
> 2013/7/31 Sandeep Gupta :
> > I have two  postgres instances each with a database of same schema. The
> > dataset in both is ''same'' but for randomness i.e.  both contain two
> tables
> > pc(did) and tc(pid, did) that have almost
> > same number of rows and have been generate from same distribution.
> >
> > However the query plan for the join turns out to be completely
> different: on
> > one join takes 2.3 secs while on the other it takes 7 secs.
> >
> >
> > Here are the statistics:
> >
> > for database 1:
> >  size of tc table:  49987585
> >  size of pc table: 499616
> >
> > join plan:
> >
> > QUERY PLAN
> >
> ---
> >  Aggregate  (cost=1534125.08..1534125.09 rows=1 width=0) (actual
> > time=8473.296..8473.296 rows=1 loops=1)
> >->  Merge Join  (cost=2.48..1514765.90 rows=7743672 width=0) (actual
> > time=0.084..8409.065 rows=998038 loops=1)
> >  Merge Cond: (pc.did = tc.did)
> >  ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..12987.04
> > rows=499616 width=4) (actual time=0.016..58.202 rows=499616 loops=1)
> >Heap Fetches: 0
> >  ->  Index Only Scan using tc_did_idx on tc
>  (cost=0.00..1298125.32
> > rows=49987616 width=4) (actual time=0.014..5141.809 rows=49997291
> loops=1)
> >Heap Fetches: 0
> >  Total runtime: 8473.337 ms
> > '
> >
> > Query Running time:  5135
> >
> >
> > for database 2:
> >   size of tc table: 50012415
> >   size of pc table: 500384
> >
> >QUERY
> > PLAN
> >
> 
> >  Aggregate  (cost=35279895.52..35279895.53 rows=1 width=0) (actual
> > time=2501.970..2501.970 rows=1 loops=1)
> >->  Nested Loop  (cost=0.00..35276697.82 rows=1279080 width=0) (actual
> > time=0.038..2418.766 rows=1000834 loops=1)
> >  ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..15224.56
> > rows=500384 width=4) (actual time=0.017..109.080 rows=500384 loops=1)
> >Heap Fetches: 500384
> >  ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..70.44
> > rows=3 width=4) (actual time=0.004..0.004 rows=2 loops=500384)
> >Index Cond: (did = pc.did)
> >Heap Fetches: 1000834
> >  Total runtime: 2502.017 ms
> >
> > Query running time: 2090.388 ms
> >
> > My question is why is the query plan so different for two datasets that
> are
> > really exactly the same. And how can i force the plan to be nested index
> > scan on
> > database 1 .
> >
> >
> > -Sandeep
> >
>

Re: [GENERAL] demystifying nested loop vs. merge join query plan choice

[Sandeep Gupta]

details regarding buffer usage:

for database 1:

  QUERY
PLAN
---
 Aggregate  (cost=1534125.08..1534125.09 rows=1 width=0) (actual
time=9149.366..9149.366 rows=1 loops=1)
   Buffers: shared hit=137991
   ->  Merge Join  (cost=2.48..1514765.90 rows=7743672 width=0) (actual
time=0.091..9075.008 rows=998038 loops=1)
 Merge Cond: (pc.did = tc.did)
 Buffers: shared hit=137991
 ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..12987.04
rows=499616 width=4) (actual time=0.017..58.237 rows=499616 loops=1)
   Heap Fetches: 0
   Buffers: shared hit=1369
 ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..1298125.32
rows=49987616 width=4) (actual time=0.015..5301.727 rows=49997291 loops=1)
   Heap Fetches: 0
   Buffers: shared hit=136622
 Total runtime: 9149.414 ms
(12 rows)


for database  2:

 QUERY PLAN


 Aggregate  (cost=35279895.52..35279895.53 rows=1 width=0) (actual
time=2386.865..2386.865 rows=1 loops=1)
   Buffers: shared hit=2235978 read=208446
   ->  Nested Loop  (cost=0.00..35276697.82 rows=1279080 width=0) (actual
time=0.049..2292.338 rows=1000834 loops=1)
 Buffers: shared hit=2235978 read=208446
 ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..15224.56
rows=500384 width=4) (actual time=0.016..108.407 rows=500384 loops=1)
   Heap Fetches: 500384
   Buffers: shared hit=6 read=3579
 ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..70.44
rows=3 width=4) (actual time=0.003..0.004 rows=2 loops=500384)
   Index Cond: (did = pc.did)
   Heap Fetches: 1000834
   Buffers: shared hit=2235972 read=204867
 Total runtime: 2386.914 ms
(12 rows)




On Wed, Jul 31, 2013 at 3:16 PM, Sandeep Gupta wrote:

> Hi Pavel,
>
>  Yes. The postgresql.conf is exactly the same.  The have the same index
> and clustering and are on the same compute node as well but running on
> different ports.
>
> -Sandeep
>
>
>
> On Wed, Jul 31, 2013 at 3:14 PM, Pavel Stehule wrote:
>
>> Hello
>>
>> do you have same configuration?
>>
>> Regards
>>
>> Pavel
>>
>> 2013/7/31 Sandeep Gupta :
>> > I have two  postgres instances each with a database of same schema. The
>> > dataset in both is ''same'' but for randomness i.e.  both contain two
>> tables
>> > pc(did) and tc(pid, did) that have almost
>> > same number of rows and have been generate from same distribution.
>> >
>> > However the query plan for the join turns out to be completely
>> different: on
>> > one join takes 2.3 secs while on the other it takes 7 secs.
>> >
>> >
>> > Here are the statistics:
>> >
>> > for database 1:
>> >  size of tc table:  49987585
>> >  size of pc table: 499616
>> >
>> > join plan:
>> >
>> > QUERY PLAN
>> >
>> ---
>> >  Aggregate  (cost=1534125.08..1534125.09 rows=1 width=0) (actual
>> > time=8473.296..8473.296 rows=1 loops=1)
>> >->  Merge Join  (cost=2.48..1514765.90 rows=7743672 width=0) (actual
>> > time=0.084..8409.065 rows=998038 loops=1)
>> >  Merge Cond: (pc.did = tc.did)
>> >  ->  Index Only Scan using pc_did_idx on pc
>>  (cost=0.00..12987.04
>> > rows=499616 width=4) (actual time=0.016..58.202 rows=499616 loops=1)
>> >Heap Fetches: 0
>> >  ->  Index Only Scan using tc_did_idx on tc
>>  (cost=0.00..1298125.32
>> > rows=49987616 width=4) (actual time=0.014..5141.809 rows=49997291
>> loops=1)
>> >Heap Fetches: 0
>> >  Total runtime: 8473.337 ms
>> > '
>> >
>> > Query Running time:  5135
>> >
>> >
>> > for database 2:
>> >   size of tc table: 50012415
>> >   size of pc table: 500384
>> >
>> >QUERY
>> > PLAN
>> >
>> 
>> >  Aggregate  (cost=35279895.52..35279895.53 rows=1 width=0) (actual
>> > time=2501.970..2501.970 rows=1 loops=1)
>> >->  Nested Loop  (cost=0.00..35276697.82 rows=1279080 width=0)
>> (actual
>> > time=0.038..2418.766 rows=1000834 loops=1)
>> >  ->  Index Only Scan using pc_did_idx on pc
>>  (cost=0.00..15224.56
>> > rows=500384 width=4) (actual time=0.017..109.080 rows=500384 loops=1)
>> >Heap Fetches: 500384
>> >  ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..70.44
>> > rows

Re: [GENERAL] demystifying nested loop vs. merge join query plan choice

[Tom Lane]

Sandeep Gupta  writes:
> details regarding buffer usage:
> [ 100% buffer hit rate ]

Your database is evidently fully cached in memory.  If that's the
operating mode you expect, you need to change the planner's cost
parameters, in particular reduce random_page_cost to equal seq_page_cost.
There is plenty of material about this on the PG wiki or in the
pgsql-performance archives.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Implicitly casting integer to bigint (9.1)

[Wells Oliver]

I have about 20 functions that all accept integer value inputs.

I want to have views which call these functions using various SUMs of
integers e.g.

select myfunction(sum(foo), sum(bar)) where foo and bar are integer types.

This doesn't really work, you get:

ERROR:  function aggregates.stat_avg(bigint, bigint) does not exist

Integer is definitely the right type to use for the underlying table. Do I
really need to have an explicit cast to bigint in these views? Seems
tedious.

-- 
Wells Oliver
wellsoli...@gmail.com

Re: [GENERAL] Implicitly casting integer to bigint (9.1)

[David Johnston]

Wells Oliver-2 wrote
> I have about 20 functions that all accept integer value inputs.
> 
> I want to have views which call these functions using various SUMs of
> integers e.g.
> 
> select myfunction(sum(foo), sum(bar)) where foo and bar are integer types.
> 
> This doesn't really work, you get:
> 
> ERROR:  function aggregates.stat_avg(bigint, bigint) does not exist
> 
> Integer is definitely the right type to use for the underlying table. Do I
> really need to have an explicit cast to bigint in these views? Seems
> tedious.

Define your aggregate functions to accept biginteger (either instead of or
in addition to integer).  The issue isn't that foo and bar are integers but
that the sum of integers is a biginteger.  In fact most of the core
aggregate functions (like count(*)) output bigintegers since it minimizes
the possibility of overflow.  Unless you have a measured reason to optimize
at integer you should just declare integer-like inputs as biginteger since
all smaller sized types do automatically get upgraded as necessary - but
obviously you cannot automatically downgrade.

Also, you would technically have to cast the "bigint" to "integer" in order
to get the view to work:

SELECT myfunction(sum(foo)::integer, sum(bar)::integer);

and just hope the sums are small enough.

David J.

Note that by habit I use integer much too often but I haven't actually
explored the downsides to abolishing integer (except in tables, and maybe
even then) and using biginteger everywhere.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Implicitly-casting-integer-to-bigint-9-1-tp5765831p5765833.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general