Re: [GENERAL] Two Versions of PostgreSQL Installed - How to uninstall one particular version

[A. Kretschmer]

In response to Wang, Mary Y :
> Thanks Andreas for the info.  I'm working on a development server right now, 
> and currently I don't have any data loaded yet.  As matter of fact, I was 
> trying to load the database data from a dump file that generated by 
> "pg_dump".  
> Here is the thing:
> I've a /usr/bin/initdb -> this is probably from version 7.3 and other 
> postgres related executable files. 
> I've a /usr/local/pgsql/bin/initdb -> this is probably from version 8.3.8.  I 
> think all the postgres related executable files are located in this 
> directory.  I like this organization much better in 8.3.8.
> My question is what files should I delete from /usr/bin directory?  Is there 
> an automatic way to uninstall a particular version?  I want to delete the 7.3 
> version and don't want to have multiple versions of postgres running on the 
> same server.  
> If there is not an automatic way, then I can probably remove files like 
> initdb, dropdb, createdb, psql and etc from the /usr/bin directory.
> Or may be just specify the path when I run the initdb command 
> "/usr/local/pgsql/bin/initdb" (other wise it would use /usr/bin/initdb)
> 
> Any thoughts?

I'm not familiar with RH, maybe someone else can better help you. But i
think, you should use the packaging system, RPM, for software-(de)installation.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

-- 
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] Two Versions of PostgreSQL Installed - How to uninstall one particular version

[Wang, Mary Y]

Thanks Andreas for the info.  I'm working on a development server right now, 
and currently I don't have any data loaded yet.  As matter of fact, I was 
trying to load the database data from a dump file that generated by "pg_dump".  
Here is the thing:
I've a /usr/bin/initdb -> this is probably from version 7.3 and other postgres 
related executable files. 
I've a /usr/local/pgsql/bin/initdb -> this is probably from version 8.3.8.  I 
think all the postgres related executable files are located in this directory.  
I like this organization much better in 8.3.8.
My question is what files should I delete from /usr/bin directory?  Is there an 
automatic way to uninstall a particular version?  I want to delete the 7.3 
version and don't want to have multiple versions of postgres running on the 
same server.  
If there is not an automatic way, then I can probably remove files like initdb, 
dropdb, createdb, psql and etc from the /usr/bin directory.
Or may be just specify the path when I run the initdb command 
"/usr/local/pgsql/bin/initdb" (other wise it would use /usr/bin/initdb)

Any thoughts?
Mary



-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of A. Kretschmer
Sent: Thursday, February 25, 2010 11:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Two Versions of PostgreSQL Installed - How to uninstall 
one particular version

In response to Wang, Mary Y :
> Hi,
> 
> I got this error:
> "-bash-2.05b$ /usr/local/pgsql/bin/pg_ctl start server starting 
> -bash-2.05b$ FATAL:  database files are incompatible with server
> DETAIL:  The data directory was initialized by PostgreSQL version 7.3, which 
> is not compatible with this version 8.3.8."
> 
> I think I must have two versions of Postgres installed.  How do I 
> uninstall the 7.3 version?  Do I need to do a manual uninstall by 
> removing Postgres related files from /usr/bin and etc?  Or is there an 
> automatic way?  The problem is that I'm unclear what files need to be 
> removed.  I think might also need to delete a file in the /etc/init.d 
> directory.

I think, you have a problem ;-)

You have a data directory initialized by PostgreSQL version 7.3. Because of 
this, you need a running 7.3-Server to acces to this data. But you have 
installed 8.3.8.

You should now install a 7.3 - Server tu access to the data. Make a Backup of 
the data-directory before!

If the 7.3 - Server is running, you can make a Backup. You should make this 
Backup with the 8.3-Server (yes, you can have multiple versions installed on 
the same computer and the same time)


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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

-- 
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] Two Versions of PostgreSQL Installed - How to uninstall one particular version

[A. Kretschmer]

In response to Wang, Mary Y :
> Hi,
> 
> I got this error:
> "-bash-2.05b$ /usr/local/pgsql/bin/pg_ctl start
> server starting
> -bash-2.05b$ FATAL:  database files are incompatible with server
> DETAIL:  The data directory was initialized by PostgreSQL version 7.3, which 
> is not compatible with this version 8.3.8."
> 
> I think I must have two versions of Postgres installed.  How do I
> uninstall the 7.3 version?  Do I need to do a manual uninstall by
> removing Postgres related files from /usr/bin and etc?  Or is there an
> automatic way?  The problem is that I'm unclear what files need to be
> removed.  I think might also need to delete a file in the /etc/init.d
> directory.

I think, you have a problem ;-)

You have a data directory initialized by PostgreSQL version 7.3. Because
of this, you need a running 7.3-Server to acces to this data. But you
have installed 8.3.8.

You should now install a 7.3 - Server tu access to the data. Make a
Backup of the data-directory before!

If the 7.3 - Server is running, you can make a Backup. You should make
this Backup with the 8.3-Server (yes, you can have multiple versions
installed on the same computer and the same time)


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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

[GENERAL] Two Versions of PostgreSQL Installed - How to uninstall one particular version

[Wang, Mary Y]

Hi,

I got this error:
"-bash-2.05b$ /usr/local/pgsql/bin/pg_ctl start
server starting
-bash-2.05b$ FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 7.3, which is 
not compatible with this version 8.3.8."

I think I must have two versions of Postgres installed.  How do I uninstall the 
7.3 version?  Do I need to do a manual uninstall by removing Postgres related 
files from /usr/bin and etc?  Or is there an automatic way?  The problem is 
that I'm unclear what files need to be removed.  I think might also need to 
delete a file in the /etc/init.d directory.

I'm running on RHEL 3.9.

Thanks
Mary




-- 
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] How to Output PSQL Errors to a Log File?

[Wang, Mary Y]

Cool!  That worked.  Thanks.  Now I just have to see if I can migrate the 
database data from 7.1 to 8.3 successfully.
Mary

-Original Message-
From: Devrim GÜNDÜZ [mailto:dev...@gunduz.org] 
Sent: Monday, February 22, 2010 11:43 PM
To: Wang, Mary Y
Cc: Joshua D. Drake; pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to Output PSQL Errors to a Log File?

On Mon, 2010-02-22 at 23:32 -0800, Wang, Mary Y wrote:
> No. I dumped the 7.1 with the 7.1 version of pg_dump.
> Is it better to dump with the 8.3 version of pg_dump? 

Since you will be restoring your data to a 8.3 server, you should use
8.3 pg_dump, yes.

>  I thought I read it some where in the mailing lists.  I don't know 
> how I would be able to dump with the 8.3 version of pg_dump.  The 
> database and Postgres 7.1 is loaded on the current production box and 
> Postgres 8.3.8 is loaded on a dev server that I plan to migrate the 
> database to.

After setting tcpip_socket to true and granting access using pg_hba.conf, you 
can connect from 8.3 machine to 7.1 machine (you may need to change your 
firewall settings, etc), like:

pg_dump -h ip/hostname/of/7.1/machine dbname -f dump.file

You don't need to compile and install 8.3 to 7.1 machine.
--
Devrim GÜNDÜZ, RHCE
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer devrim~gunduz.org, 
devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org  
Twitter: http://twitter.com/devrimgunduz

-- 
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] Problems with the Windows 8.4.1 upgrade from 8.3 on non-C drive

[John Gage]

I realize that this is a feature and not a bug, but what I ran into  
confronting the same issue was the fact that the servers listen on  
different ports and when I uninstalled the older version, the newer  
version was listening on a non-standard port.  This caused some  
confusion for awhile.


I also realize that having both versions on the same port may be both  
impossible and unwise or some power of both, but is there a standard  
port that they are "competing" for in some dark server hell.



On Feb 26, 2010, at 4:57 AM, Sachin Srivastava wrote:

The one-click installers are designed in such a way that you can  
have different major versions of postgresql co-exist on the same  
machine. Thats the reason, the 8.4.1 installer doesn't realize that  
8.3 is even installed.



On 2/26/10 12:56 AM, dtrobert wrote:


Hi,
We have tried this at least with Windows 2008 64-bit server and the  
problem

is very reproducible (multiple systems):

1. Install 8.3 on some non-C drive (say E:\)
2. Run the 8.4.1 installer to upgrade this install


it appears that the 8.4.1 doesn't realize 8.3 is even installed

Result: Installation fails with a permission problem. It seems  
certain files
from 8.3 are not able to be deleted. When I manually try to delete  
them,
even with the Administrator user, it fails. I ultimately needed to  
take
ownership and then give Full Control before I was allowed to delete  
them.


There is no problem with this upgrade on the C:\ drive.

Thanks




--
Regards,
Sachin Srivastava
EnterpriseDB, the Enterprise Postgres company.

Re: [GENERAL] Problems with the Windows 8.4.1 upgrade from 8.3 on non-C drive

[Sachin Srivastava]

The one-click installers are designed in such a way that you can have 
different major versions of postgresql co-exist on the same machine. 
Thats the reason, the 8.4.1 installer doesn't realize that 8.3 is even 
installed.



On 2/26/10 12:56 AM, dtrobert wrote:

Hi,
We have tried this at least with Windows 2008 64-bit server and the problem
is very reproducible (multiple systems):

1. Install 8.3 on some non-C drive (say E:\)
2. Run the 8.4.1 installer to upgrade this install
   

it appears that the 8.4.1 doesn't realize 8.3 is even installed
 

Result: Installation fails with a permission problem. It seems certain files
from 8.3 are not able to be deleted. When I manually try to delete them,
even with the Administrator user, it fails. I ultimately needed to take
ownership and then give Full Control before I was allowed to delete them.

There is no problem with this upgrade on the C:\ drive.

Thanks
   



--
Regards,
Sachin Srivastava
EnterpriseDB , the Enterprise Postgres 
 company.

Re: [GENERAL] trouble with to_char('L')

[Bruce Momjian]

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > Where are we on this issue?
> 
> Oops I forgot it completely.
> I have a little improved version and would post it tonight.

Ah, very good.  Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +

-- 
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] trouble with to_char('L')

[Hiroshi Inoue]

Bruce Momjian wrote:

Where are we on this issue?


Oops I forgot it completely.
I have a little improved version and would post it tonight.

regards,
Hiroshi Inoue



---

Hiroshi Inoue wrote:

Tom Lane wrote:

Alvaro Herrera  writes:

Does this imply that we shouldn't allow UTF8 database on Windows at all?

That would be pretty unfortunate :-(

I think what this suggests is that there probably needs to be some
encoding conversion logic near the places we examine localeconv()
output.

Attached is a patch to the current CVS.
It uses a similar way like LC_TIME stuff does.

regards,
Hiroshi Inoue



--
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] trouble with to_char('L')

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > Right, but you are saying it is still an open issue, which says we
> > should look at it.
> 
> Sure.  Maybe put it on TODO?

OK, TODO is:

Fix locale-aware handling (e.g. monetary) for specific
server/client encoding combinations

* http://archives.postgresql.org/pgsql-general/2009-04/msg00799.php 

If someone wants to work on it, go ahead.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +

-- 
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] trouble with to_char('L')

[Tom Lane]

Bruce Momjian  writes:
> Right, but you are saying it is still an open issue, which says we
> should look at it.

Sure.  Maybe put it on TODO?

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] trouble with to_char('L')

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > Where are we on this issue?
> 
> According to my files, I complained about the extreme ugliness of the
> patch (redefining strdup for pete's sake) and the fact that it did not
> actually do things anything like the LC_TIME code as was claimed.
> Hiroshi rejected those criticisms.  I don't know where we are, but
> I don't want to see this patch applied in this form.

Right, but you are saying it is still an open issue, which says we
should look at it.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +

-- 
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] trouble with to_char('L')

[Tom Lane]

Bruce Momjian  writes:
> Where are we on this issue?

According to my files, I complained about the extreme ugliness of the
patch (redefining strdup for pete's sake) and the fact that it did not
actually do things anything like the LC_TIME code as was claimed.
Hiroshi rejected those criticisms.  I don't know where we are, but
I don't want to see this patch applied in this form.

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] trouble with to_char('L')

[Bruce Momjian]

Where are we on this issue?

---

Hiroshi Inoue wrote:
> Tom Lane wrote:
> > Alvaro Herrera  writes:
> >> Does this imply that we shouldn't allow UTF8 database on Windows at all?
> > 
> > That would be pretty unfortunate :-(
> > 
> > I think what this suggests is that there probably needs to be some
> > encoding conversion logic near the places we examine localeconv()
> > output.
> 
> Attached is a patch to the current CVS.
> It uses a similar way like LC_TIME stuff does.
> 
> regards,
> Hiroshi Inoue

> Index: pg_locale.c
> ===
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/pg_locale.c,v
> retrieving revision 1.49
> diff -c -c -r1.49 pg_locale.c
> *** pg_locale.c   1 Apr 2009 09:17:32 -   1.49
> --- pg_locale.c   22 Apr 2009 21:08:33 -
> ***
> *** 386,391 
> --- 386,449 
>   free(s->positive_sign);
>   }
>   
> + #ifdef  WIN32
> + #define MAX_BYTES_PER_CHARACTER 4
> + static char *dbstr_win32(bool matchenc, const char *str)
> + {
> + int encoding = GetDatabaseEncoding();
> + boolis_ascii = true;
> + size_t  len, ilen, wclen, dstlen;
> + wchar_t *wbuf;
> + char*dst, *ibuf;
> + 
> + if (matchenc)
> + return strdup(str);
> + /* Is the str an ascii string ? */
> + for (ibuf = str; *ibuf; ibuf++)
> + {
> + if (!isascii(*ibuf))
> + {
> + is_ascii = false;
> + break;
> + }
> + }
> + /* Simply returns the strdup()ed ascii string */
> + if (is_ascii)
> + return strdup(str);
> + 
> + ilen = strlen(str) + 1;
> + wclen = ilen * sizeof(wchar_t);
> + wbuf = (wchar_t *) palloc(wclen);
> + len = mbstowcs(wbuf, str, ilen);
> + if (len == -1)
> + elog(ERROR,
> + "could not convert string to Wide characters:error 
> %lu", GetLastError());
> + 
> + dstlen = len * MAX_BYTES_PER_CHARACTER + 1;
> + dst = malloc(dstlen);
> +   
> + len = WideCharToMultiByte(CP_UTF8, 0, wbuf, len, dst, dstlen, NULL, 
> NULL);
> + pfree(wbuf);
> + if (len == 0)
> + elog(ERROR,
> + "could not convert string to UTF-8:error %lu", 
> GetLastError());
> + 
> + dst[len] = '\0';
> + if (encoding != PG_UTF8)
> + {
> + char *convstr = pg_do_encoding_conversion(dst, len, PG_UTF8, 
> encoding);
> + if (dst != convstr)
> + {
> + strlcpy(dst, convstr, dstlen);
> + pfree(convstr);
> + }
> + }
> + 
> + return dst;
> + }
> + 
> + #define strdup(str) dbstr_win32(is_encoding_match, str)
> + #endif /* WIN32 */
>   
>   /*
>* Return the POSIX lconv struct (contains number/money formatting
> ***
> *** 398,403 
> --- 456,466 
>   struct lconv *extlconv;
>   char   *save_lc_monetary;
>   char   *save_lc_numeric;
> + #ifdef  WIN32
> + char   *save_lc_ctype = NULL;
> + boollc_ctype_change = false, is_encoding_match;
> + #endif /* WIN32 */
> + 
>   
>   /* Did we do it already? */
>   if (CurrentLocaleConvValid)
> ***
> *** 413,418 
> --- 476,492 
>   if (save_lc_numeric)
>   save_lc_numeric = pstrdup(save_lc_numeric);
>   
> + #ifdef  WIN32
> + save_lc_ctype = setlocale(LC_CTYPE, NULL);
> + if (save_lc_ctype && stricmp(locale_monetary, save_lc_ctype) != 0)
> + {
> + lc_ctype_change = true;
> + save_lc_ctype = pstrdup(save_lc_ctype);
> + setlocale(LC_CTYPE, locale_monetary);
> + }
> + is_encoding_match = (pg_get_encoding_from_locale(locale_monetary) == 
> GetDatabaseEncoding());
> + #endif
> + 
>   setlocale(LC_MONETARY, locale_monetary);
>   setlocale(LC_NUMERIC, locale_numeric);
>   
> ***
> *** 437,442 
> --- 511,524 
>   CurrentLocaleConv.n_sign_posn = extlconv->n_sign_posn;
>   
>   /* Try to restore internal settings */
> + #ifdef  WIN32
> + #undef  strdup
> + if (lc_ctype_change)
> + {
> + setlocale(LC_CTYPE, save_lc_ctype);
> + pfree(save_lc_ctype);
> + }
> + #endif /* WIN32 */
>   if (save_lc_monetary)
>   {
>   setlocale(LC_MONETARY, save_lc_monetary);
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-general mailing list (pgsql-general@post

Re: [GENERAL] After each row trigger NOT seeing data changes?

[Bruce Momjian]

Tom Lane wrote:
> Karl Nack  writes:
> > I notice the row count does not reflect the newly-inserted row, which 
> > suggests that the trigger is not seeing changes made to the table. This 
> > seems to be exactly opposite of what's in the manual:
> > http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html
> > http://www.postgresql.org/docs/8.3/interactive/trigger-example.html
> 
> The reason is that you've declared the function STABLE, which causes it
> to use the calling query's starting snapshot.  So it cannot see any
> in-progress changes of the calling query.  Declare it VOLATILE (or
> let it default to that) and it will act as you expect.
> 
> I'm not sure if the cited portions of the manual ought to contain notes
> about this or not.  It seems a bit off-topic for them, but if other
> people have been bit by this, then maybe ... comments anyone?

I have applied the attached documentation patch to subtly mention this
issue.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/create_function.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v
retrieving revision 1.90
diff -c -c -r1.90 create_function.sgml
*** doc/src/sgml/ref/create_function.sgml	14 Feb 2010 01:01:35 -	1.90
--- doc/src/sgml/ref/create_function.sgml	25 Feb 2010 22:21:23 -
***
*** 289,295 
 return the same result for the same argument values, but that its
 result could change across SQL statements.  This is the appropriate
 selection for functions whose results depend on database lookups,
!parameter variables (such as the current time zone), etc.  Also note
 that the current_timestamp family of functions qualify
 as stable, since their values do not change within a transaction.

--- 289,297 
 return the same result for the same argument values, but that its
 result could change across SQL statements.  This is the appropriate
 selection for functions whose results depend on database lookups,
!parameter variables (such as the current time zone), etc.  (It is
!inappropriate for AFTER triggers that wish to
!query rows modified by the current command.)  Also note
 that the current_timestamp family of functions qualify
 as stable, since their values do not change within a transaction.


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

[GENERAL] Problems with the Windows 8.4.1 upgrade from 8.3 on non-C drive

[dtrobert]

Hi, 
We have tried this at least with Windows 2008 64-bit server and the problem
is very reproducible (multiple systems): 

1. Install 8.3 on some non-C drive (say E:\) 
2. Run the 8.4.1 installer to upgrade this install 
> it appears that the 8.4.1 doesn't realize 8.3 is even installed 

Result: Installation fails with a permission problem. It seems certain files
from 8.3 are not able to be deleted. When I manually try to delete them,
even with the Administrator user, it fails. I ultimately needed to take
ownership and then give Full Control before I was allowed to delete them. 

There is no problem with this upgrade on the C:\ drive. 

Thanks
-- 
View this message in context: 
http://old.nabble.com/Problems-with-the-Windows-8.4.1-upgrade-from-8.3-on-non-C-drive-tp27714508p27714508.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

Re: [GENERAL] Tool for determining field usage of database tables

[Scott Marlowe]

On Thu, Feb 25, 2010 at 12:56 PM, Andy Yoder  wrote:
> Thanks Allan for the input - I guess I didn't specify enough details.  I am 
> looking for some type of tool/report that is already done.  We have nearly 
> 1000 tables, over 300 functions to look at a little over a day to provide the 
> answers (all without dropping any other tasks, of course).  I had considered 
> the trigger idea, and may end of doing it anyway and just working later, but 
> thought I would check for a "ready-made" solution first.

Nothing as fine grained as per field, but there are the

pg_stat_user_tables and pg_stat_user_indexes

type system tables, which will show how much individual tables and
indexes are getting used.

-- 
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] Tool for determining field usage of database tables

[Andy Yoder]

Thanks Allan for the input - I guess I didn't specify enough details.  I am 
looking for some type of tool/report that is already done.  We have nearly 1000 
tables, over 300 functions to look at a little over a day to provide the 
answers (all without dropping any other tasks, of course).  I had considered 
the trigger idea, and may end of doing it anyway and just working later, but 
thought I would check for a "ready-made" solution first.

Andy

-Original Message-
From: Allan Kamau [mailto:kamaual...@gmail.com] 
Sent: Thursday, February 25, 2010 12:41 PM
To: Andy Yoder
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Tool for determining field usage of database tables

Writing an audit trigger for the operations you'd like to monitor
(then assign it to all your application's tables) to perform the
auditing may be one easy way of doing so, this trigger would log the
operations to some other table.

Allan.

On Thu, Feb 25, 2010 at 7:36 PM, Andy Yoder  wrote:
> Does anyone know of a tool (or a way to use the database catalogs) that can
> analyze function code/queries accessing the database to pull out a list of
> the fields used in a set of tables.  Basically we are importing a lot of
> data from another source, and we are trying to determine what percentage of
> the data we are actually using at this point .  We have hundreds of stored
> procedures, and combing through the code would not be practical.
>
>
>
> Thanks.
>
>
>
> --Andy
>
>
>
>
>
>

-- 
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] Boolean partition constraint behaving strangely

[Tom Lane]

Dominik Sander  writes:
> I have an issue with a table partitioned by one boolean column. The
> query planner only seems to skip the non matching table if expired
> (the column I use for the partition) is true.

Hm, interesting case.  The reason it's behaving asymmetrically is the
fix for this bug:
http://archives.postgresql.org/pgsql-sql/2008-01/msg00084.php

The planner forces expressions like "bool_var = true" into the
simpler forms "bool_var" or "NOT bool_var" so as to recognize
that these forms are equivalent.  However, that means that your
"expired = false" case looks like the case that was removed as
incorrect, ie

+  * Unfortunately we *cannot* use
+  *NOT A R=> B if: B => A
+  * because this type of reasoning fails to prove that B doesn't yield NULL.

It strikes me though that we could make the more limited deduction
that NOT A refutes A itself.  That would fix this case, and I think
it would cover all the cases that we would have recognized if we'd
left the clauses in boolean-comparison form.

I'll see about fixing this for the next updates.

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] Curious plperl behavior

[Jeff]

On Feb 24, 2010, at 5:10 PM, Tom Lane wrote:


Richard Huxton  writes:

On 24/02/10 21:34, Tom Lane wrote:
Hmm.  Jeff found some relevant material on perlref.  Should that  
link be
added?  Should the link(s) be more specific than telling you to  
read the
whole d*mn man page?  Neither of those pages are short, and each  
contains

a wealth of material that isn't related to this issue.


Hmm - perhaps a suggestion to google for "perl nested named  
subroutine".

That seems to give a set of relevant results. Includes perldiag,
perlref, our mailing lists and Apache's mod_perl (which makes sense).


Seems like a reasonable idea to me --- any objections?  We should
probably say "search" not "google" but otherwise seems like a fine
solution.



Some sort of extended explanation would be helpful I think.   
Admittedly, I didn't see the warning in the docs as I didn't look but  
if I did see that I'd be very curious as to what exactly is  
dangerous.  The reality is you cannot safely refer to variables  
outside the scope of the sub.  Maybe just that sentence would suffice.  
and a "for more details see the perlref documentation"


--
Jeff Trout 
http://www.stuarthamm.net/
http://www.dellsmartexitin.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] Performance comparison

[Thomas Kellerer]

Greg Smith, 25.02.2010 17:47:

Based on tests showing a similar style and magnitude regression at Sun
by Jignesh Shah, I would assume this is mainly because some of the
starting parameter changes in 8.4 detuned this particular benchmark a
bit, in favor of proving a better default for real-world users. For
example, the starting default_statistics_target was raised from 10 to
100 in 8.4. This causes a mild decrease in performance on trivial
benchmarks like this one, while potentially providing a large
improvement in the sorts of query plans seen in real applications.

That was the basic theme for the sorts of performance changes that
showed up in 8.4. Another example (not actually relevant to this
benchmark) is that the Free Space Map used to track deleted items is now
kept on disk instead of in shared memory. That's obviously less
efficient in the short term--disk write instead of just a memory
one--but it prevents all sorts of nasty worst-case scenarios you used to
run into the FSM wasn't big enough in earlier versions. Basically, the
8.4 performance related changes reduced average performance on trivial
benchmark workloads a small amount, in favor of large improvements in
the sort of situations people run into in production deployments. I
think it was the right trade-off to make.


Thanks for the detailed answer!

Regards
Thomas



 




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

[Greg Smith]

Thomas Kellerer wrote:


http://suckit.blog.hu/2009/09/29/postgresql_history



It would be interesting to know why the max. performance in the r/w 
scenario for 8.4.1 is lower compared to 8.3.7 (and if maybe 8.4.2 
fixed this)


Based on tests showing a similar style and magnitude regression at Sun 
by Jignesh Shah, I would assume this is mainly because some of the 
starting parameter changes in 8.4 detuned this particular benchmark a 
bit, in favor of proving a better default for real-world users.  For 
example, the starting default_statistics_target was raised from 10 to 
100 in 8.4.  This causes a mild decrease in performance on trivial 
benchmarks like this one, while potentially providing a large 
improvement in the sorts of query plans seen in real applications.


That was the basic theme for the sorts of performance changes that 
showed up in 8.4.  Another example (not actually relevant to this 
benchmark) is that the Free Space Map used to track deleted items is now 
kept on disk instead of in shared memory.  That's obviously less 
efficient in the short term--disk write instead of just a memory 
one--but it prevents all sorts of nasty worst-case scenarios you used to 
run into the FSM wasn't big enough in earlier versions.  Basically, the 
8.4 performance related changes reduced average performance on trivial 
benchmark workloads a small amount, in favor of large improvements in 
the sort of situations people run into in production deployments.  I 
think it was the right trade-off to make.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

[GENERAL] Tool for determining field usage of database tables

[Andy Yoder]

Does anyone know of a tool (or a way to use the database catalogs) that can 
analyze function code/queries accessing the database to pull out a list of the 
fields used in a set of tables.  Basically we are importing a lot of data from 
another source, and we are trying to determine what percentage of the data we 
are actually using at this point .  We have hundreds of stored procedures, and 
combing through the code would not be practical.

Thanks.

--Andy

Re: [GENERAL] Restricting the CREATEROLE privilege

[Alex Hunsaker]

On Thu, Feb 25, 2010 at 08:22, Wappler, Robert  wrote:
> Unfortunately, base_user inherits the connect privileges from role
> PUBLIC, regardless, whether it was created with NOINHERIT.

Yeah, IMO the documentation does not really spell out that limitation.

> How about changing the CREATEROLE privilege to be associated with a
> specific database instead of affecting all databases?

Well just on the grounds that it would break every current user of
CREATE ROLE... that's probably not going to happen.  I could imagine
there could be some syntax sugar for this.  But I don't think it would
be any nicer as you would probably need to REVOKE PUBLIC and inherit
anyway.  Not to mention I'm not sure what the semantics would be or
where it gets its 'default' permissions.  A ruff idea would be for
each database (except the connected one) REVOKE ALL on database.  Of
course feel free to flesh it out and submit a patch :).  In any event
its certainly too late for 9.0 and would not be back patched anyway...

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

[akp geek]

I don't have the root privileges. But I have followed the  instructions
http://www.depesz.com/index.php/2008/11/28/recovering-lost-postgresql-password/
it worked out well.

Thanks for the suggestion

Regards

On Thu, Feb 25, 2010 at 10:21 AM, paragasu  wrote:

> if you know the root password
>
> #su root
> #su postgres
> #psql
> #alter role  with password 
>
> it think that should do
>
>
>
> On Thu, Feb 25, 2010 at 11:15 PM, akp geek  wrote:
>
>> Dear all -
>>
>>  I forgot the password for the postgres user for the
>> database. Is there way I can reset the password to whatever I want. When I
>> do the psql -U postgres , it's prompting me the password. Can you please
>> help?
>>
>>
>> Regards
>>
>
>

Re: [GENERAL] postgres password change

[akp geek]

Thank you. It worked

Regards

On Thu, Feb 25, 2010 at 10:18 AM, hubert depesz lubaczewski <
dep...@depesz.com> wrote:

>  On Thu, Feb 25, 2010 at 10:15:55AM -0500, akp geek wrote:
> > Dear all -
> >
> >  I forgot the password for the postgres user for the
> > database. Is there way I can reset the password to whatever I want. When
> I
> > do the psql -U postgres , it's prompting me the password. Can you please
> > help?
>
>
> http://www.depesz.com/index.php/2008/11/28/recovering-lost-postgresql-password/
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz  /  blog:
> http://www.depesz.com/
> jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl /
> gg:6749007
>

Re: [GENERAL] select issue with order v8.1

[Terry]

On Wed, Feb 24, 2010 at 10:30 PM, Scott Marlowe  wrote:
> On Wed, Feb 24, 2010 at 8:50 PM, Terry  wrote:
>> Hello,
>>
>> I have an application that is doing something stupid in that it is
>> tacking on its own order clause at the end of the statement I am
>> providing.
>>
>> For example, I am putting this statement in:
>> select 
>> ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text
>> from clients_event_log limit 100
>>
>> It is tacking on ORDER BY ev_id.  The problem is that isn't per the
>> syntax.  Can anyone think of anything clever to get around this stupid
>> application doing what it is doing?  For example, anything I can do
>> beside limit?
>>
>> I appreciate the thoughts!
>
> You could either wrap it in a subselect or make a view.
>
> select * from (select
> ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text
> from clients_event_log limit 100) as a
>
> and an order by tacked on the end of that is ok.
>

This and the previous poster's advice both worked.  Thank you.
However, I am having another issue where the application is not
viewing a 'serial' data type as a number.  Clearly none of this is a
postgres issue.  Stupid programming.

-- 
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] Restricting the CREATEROLE privilege

[Wappler, Robert]

On 2010-02-25, Alex Hunsaker wrote:
 
> You could create a base role that does not have connect privileges on
> the other databases.  Then just inherit from that role.  Something
like:
> CREATE ROLE base_user; REVOKE CONNECT ON database from base_user; ...
> 
> CREATE ROLE my_user inherit base_user;
> 

Unfortunately, base_user inherits the connect privileges from role
PUBLIC, regardless, whether it was created with NOINHERIT.

> You could also go the other route and default deny connect databases
> and explicitly allow connect.
>

That other way round seems to be the only solution for now, first revoke
the CONNECT privileges from PUBLIC and then grant them to individual
roles.

How about changing the CREATEROLE privilege to be associated with a
specific database instead of affecting all databases?

-- 
Robert...
 


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

[paragasu]

if you know the root password

#su root
#su postgres
#psql
#alter role  with password 

it think that should do


On Thu, Feb 25, 2010 at 11:15 PM, akp geek  wrote:

> Dear all -
>
>  I forgot the password for the postgres user for the
> database. Is there way I can reset the password to whatever I want. When I
> do the psql -U postgres , it's prompting me the password. Can you please
> help?
>
>
> Regards
>

Re: [GENERAL] Missing clog, PITR

[Patryk Sidzina]

> > 1) how do the clogs relate to wal shipping based replication? Clearly
> > the master doesn't need that clog but the slave does.
> >   
> They should just be kept in sync.  There's some useful background on 
> this topic at 
> http://old.nabble.com/control-the-number-of-clog-files-and-xlog-files-td19173165.html
> 

The thing is they are in sync but somehow the slave needs more clogs
than the master, otherwise it won't start up.


> > 3) is there a faster way to debug this problem? Clogs fill slowly. It
> > takes about a month on a very busy production server for a clog to be
> > removed by master DB.
> >   
> 
> You could create a bunch of transactions and then freeze things, 
> following the ideas in the reference I suggested above.

Thanks for the info, I will try it as soon as I can.

> > More info:
> > PostgreSQL 8.2.14 64-bit (though this happened in older versions also)
> > pg_standby from PostgreSQL 8.3.6
> >   
> 
> There was a bug in this area fixed in 8.2.10:  
> http://www.postgresql.org/docs/8.2/static/release-8-2-10.html
> 
> "Fix potential miscalculation of datfrozenxid (Alvaro)
> 
> *
> 
>   This error may explain some recent reports of failure to remove
>   old pg_clog data."
> 
> If you were running this database with a version before that, I wonder 
> if maybe there's still some junk left behind from that old, buggy 
> version that's causing your issues.  You might try doing some manual 
> VACUUM or VACUUM FREEZE work to remove any lingering issues and then 
> re-create your standby systems afterwards.  I'm not quite familiar 
> enough with this specific bug to suggest a clearer resolution path, or 
> if in fact this is the same issue you're seeing.  It sure seems possible 
> they're related though.


We run VACUUM on the master db every night (and VACUUM FULL on weekends)
and test the standby db using LVM snapshots. If our test generates the
missing clog error we have to recreate the standby from scratch. So I
doubt there is any junk left over, but I will look into this bug more
carefully anyway. Again, thank you for your suggestions.


-- 
Patryk Sidzina


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

[hubert depesz lubaczewski]

On Thu, Feb 25, 2010 at 10:15:55AM -0500, akp geek wrote:
> Dear all -
> 
>  I forgot the password for the postgres user for the
> database. Is there way I can reset the password to whatever I want. When I
> do the psql -U postgres , it's prompting me the password. Can you please
> help?

http://www.depesz.com/index.php/2008/11/28/recovering-lost-postgresql-password/

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

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

[GENERAL] postgres password change

[akp geek]

Dear all -

 I forgot the password for the postgres user for the
database. Is there way I can reset the password to whatever I want. When I
do the psql -U postgres , it's prompting me the password. Can you please
help?


Regards

Re: [GENERAL] Restricting the CREATEROLE privilege

[Alex Hunsaker]

On Thu, Feb 25, 2010 at 01:26, Wappler, Robert  wrote:
> Good Morning,
> is there a way to limit the CREATEROLE privilege to a specific database?
> I currently set up an automated integration test environment. This includes a
> database owned by a specific user which should have all degrees of freedom
> for installing whatever database schemas are in the current revision as well 
> as
> creating roles used by the test cases to access the database.

You could create a base role that does not have connect privileges on
the other databases.  Then just inherit from that role.  Something
like:
CREATE ROLE base_user;
REVOKE CONNECT ON database from base_user;
...

CREATE ROLE my_user inherit base_user;

You could also go the other route and default deny connect databases
and explicitly allow connect.

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

[GENERAL] Putting index entries to XLog

[Carsten Kropf]

Hi *,
I have encountered a problem while implementing an index structure. I don't 
have any access to the RM (I don't know, how to set it up properly) using my 
index. However, when I don't have the situation of working with a temporary 
table, I need to put entries using XLogInsert(...). The problem for me is, that 
based on the fact, that I don't have the access to the RM, I can't put proper 
entries to the xlog facility. How can I achieve that my index structure can be 
stored properly at least?
I'm getting lots of errors, when I just put a XLogRecPtr with data {1,1} as LSN 
of the page. On the other hand when doing XLogInsert calls to work with the 
component properly I don't know which data to put in there. When I'm using some 
kind of RM_ID (like RM_GIST_ID or something like that) I'm getting problems 
because I don't know which code to insert there. I decided to take RM_GIST_ID 
and an invalid code (so it won't call any of the xlog gist implementations upon 
recovery) which, however failed, too. Unfortunately my task is to implement a 
certain structure with PostgreSQL, so all of my current work actually depend on 
this action.
I hope that somebody will be able to help me according to this issue.
Thanks in advance

Best regards
Carsten Kropf
-- 
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] C function to create tsquery not working

[Pavel Stehule]

2010/2/25 Ivan Sergio Borgonovo :
> On Thu, 11 Feb 2010 20:11:54 +0100
> Ivan Sergio Borgonovo  wrote:
>
>> I'm still having trouble making this work:
>>
>> http://pgsql.privatepaste.com/14a6d3075e
>
> I tried to play with
> item->operator.left
> to see if reshuffling the expression could make any difference.
> item->operator.left = 2 * lexeme - 2 (1 + i)
> or
> item->operator.left = lexemes
>
> But the result seems pretty indifferent to what I put in
> operator.left.
> That makes me think the error is here.
>
> But I still get those 2 kind of error:
> ERROR:  unrecognized operator type: 50 (first run)
> or
> ERROR:  stack depth limit exceeded
>
> Just at the 3rd returned row, just for certain queries (see previous
> email).
>
> It doesn't look as if I palloced too few memory, I tried to allocate
> 3x the memory I estimated and I still get the errors.
>
> The function is actually returning correct results, so it seems the
> tsquery object is well formed.
>
> But still it looks like infix() is trying to read more operators
> than the one I thought I've put in... but just for certain queries,
> and just at the 3rd row returned.
>
> Should I use something different than palloc? Should I return the
> query differently? Am I supposed to free something?

use --enable-assert configure flag?

you can use memory in bad context. So you are alloc good memory, but
when you leave function, then complete memory context is freeed and
you can have a problem.

Regards
Pavel Stehule


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

-- 
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] C function to create tsquery not working

[Ivan Sergio Borgonovo]

On Thu, 11 Feb 2010 20:11:54 +0100
Ivan Sergio Borgonovo  wrote:

> I'm still having trouble making this work:
> 
> http://pgsql.privatepaste.com/14a6d3075e

I tried to play with
item->operator.left
to see if reshuffling the expression could make any difference.
item->operator.left = 2 * lexeme - 2 (1 + i)
or
item->operator.left = lexemes

But the result seems pretty indifferent to what I put in
operator.left.
That makes me think the error is here.

But I still get those 2 kind of error:
ERROR:  unrecognized operator type: 50 (first run)
or
ERROR:  stack depth limit exceeded

Just at the 3rd returned row, just for certain queries (see previous
email).

It doesn't look as if I palloced too few memory, I tried to allocate
3x the memory I estimated and I still get the errors.

The function is actually returning correct results, so it seems the
tsquery object is well formed.

But still it looks like infix() is trying to read more operators
than the one I thought I've put in... but just for certain queries,
and just at the 3rd row returned.

Should I use something different than palloc? Should I return the
query differently? Am I supposed to free something?


-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
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] Global Temp Table

[A. Kretschmer]

In response to Shameem Ahamed :
> Hi,
> 
> I want to create a global temp table in database, which can be
> accessed from any session to the database. Is it possible ?.

No.

> 
> I tried with create global temp table , but the table created exist
> only in that specific session. No other session is able to access the
> table.
> 
> How can i sort it out ?.

Use a regular table instead.

Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

-- 
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] SELECT only those values of table B which have a value in table A

[Stefan Schwarzer]

SELECT
COALESCE(r.name, ) AS name, 
d.year_start AS year,
SUM(d.value * d_nom.value) / SUM(d_nom.value) AS value  
FROM
pop_total_access_water AS d
RIGHT JOIN
countries_view AS c ON c.id = d.id_country
RIGHT JOIN
pop_total AS d_nom ON d_nom.id_country = d.id_country
RIGHT JOIN
subregions_view AS r ON r.id = c.sreg_id
WHERE
(d.year_start = 1990 ) AND
(d_nom.year_start = 1990 ) AND
d_nom.value <> 0
GROUP BY
r.name, d.year_start
ORDER BY 1,2

Now, I would like to use from table "d_nom" only these countries
which have a value in table "d" as well.


Without thinking much (so this may be completely wrong for some  
reason/additional
changes may be needed), why do you use a right join to the d_nom  
table?

An ordinary inner join should give you only these countries which have
their rows in the d table.


Thanks for that. Ok, tried that, didn't work. But gave me the go into  
the right direction. Stripped down the query to the absolute  
necessary, on a country basis, and realized that I have in some tables  
NULL values. So, the correct SQL is then (with the added line:  
(d.value IS NOT NULL )):


SELECT
COALESCE(r.name, ) AS name, 
d.year_start AS year,
SUM(d.value * d_nom.value) / SUM(d_nom.value) AS value  
FROM
pop_total_access_water AS d
RIGHT JOIN
countries_view AS c ON c.id = d.id_country
RIGHT JOIN
pop_total AS d_nom ON d_nom.id_country = d.id_country
RIGHT JOIN
subregions_view AS r ON r.id = c.sreg_id
WHERE
(d.year_start = 1990 ) AND
(d.value IS NOT NULL ) AND
(d_nom.year_start = 1990 ) AND
d_nom.value <> 0
GROUP BY
r.name, d.year_start
ORDER BY 1,2

Thanks a lot!

Stef

smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Global Temp Table

[Shameem Ahamed]

Hi,

I want to create a global temp table in database, which can be accessed from 
any session to the database. Is it possible ?.

I tried with create global temp table , but the table created exist only in 
that specific session. No other session is able to access the table.

How can i sort it out ?.

Regards,
Shameem


  Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! 
http://downloads.yahoo.com/in/internetexplorer/

-- 
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] SELECT only those values of table B which have a value in table A

[Michal Politowski]

On Thu, 25 Feb 2010 09:34:19 +0100, Stefan Schwarzer wrote:
> Hi there,
> 
> I have tables with values for each country of the world.
> 
> Now, there is the possibility for the user to generate regional
> (Europe, Africa...) or subregional (Western Europe, Central
> Europe...) aggregations on-the-fly. In some cases, these
> aggregations need to be calculated by using the population of each
> region too, as a denominator, which looks like this:
> 
> 
>   SELECT
>   COALESCE(r.name, ) AS name, 
>   d.year_start AS year,
>   SUM(d.value * d_nom.value) / SUM(d_nom.value) AS value  
>   FROM
>   pop_total_access_water AS d
>   RIGHT JOIN
>   countries_view AS c ON c.id = d.id_country
>   RIGHT JOIN
>   pop_total AS d_nom ON d_nom.id_country = d.id_country
>   RIGHT JOIN
>   subregions_view AS r ON r.id = c.sreg_id
>   WHERE
>   (d.year_start = 1990 ) AND
>   (d_nom.year_start = 1990 ) AND
>   d_nom.value <> 0
>   GROUP BY
>   r.name, d.year_start
>   ORDER BY 1,2
> 
> Now, I would like to use from table "d_nom" only these countries
> which have a value in table "d" as well.

Without thinking much (so this may be completely wrong for some 
reason/additional
changes may be needed), why do you use a right join to the d_nom table?
An ordinary inner join should give you only these countries which have
their rows in the d table.

-- 
Michał Politowski
Talking has been known to lead to communication if practiced carelessly.

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

[Wappler, Robert]

On 2010-02-22, beulah prasanthi wrote:
 
> Helo
>  I am working on spring project with postgres 8.4
> i wrote a function in postgrees which i am passing the
> argument email email[] array
> From front end we need to insesrt data into that emailarray
> .so i used java.arraylist.util
> while i am running i got the following error Please help me
> 
> error:
> org.postgresql.util.PSQLException: Cannot cast an instance of
> java.util.ArrayList to type Types.ARRAY
>

The array support in JDBC is imo nothing better than horrible. However,
the way to go is:
Connection c = getConnection();
Array arr = c.createArrayOf("text", email);

PreparedStatement p = c.prepareStatement(...);
p.setArray(index, arr);

Also notice, that there is no nice way to test array data in frameworks
like DBUnit and others.


-- 
Robert...
 


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

[GENERAL] SELECT only those values of table B which have a value in table A

[Stefan Schwarzer]

Hi there,

I have tables with values for each country of the world.

Now, there is the possibility for the user to generate regional  
(Europe, Africa...) or subregional (Western Europe, Central Europe...)  
aggregations on-the-fly. In some cases, these aggregations need to be  
calculated by using the population of each region too, as a  
denominator, which looks like this:



SELECT
COALESCE(r.name, ) AS name, 
d.year_start AS year,
SUM(d.value * d_nom.value) / SUM(d_nom.value) AS value  
FROM
pop_total_access_water AS d
RIGHT JOIN
countries_view AS c ON c.id = d.id_country
RIGHT JOIN
pop_total AS d_nom ON d_nom.id_country = d.id_country
RIGHT JOIN
subregions_view AS r ON r.id = c.sreg_id
WHERE
(d.year_start = 1990 ) AND
(d_nom.year_start = 1990 ) AND
d_nom.value <> 0
GROUP BY
r.name, d.year_start
ORDER BY 1,2

Now, I would like to use from table "d_nom" only these countries which  
have a value in table "d" as well. What happens now is that, if my  
table "d" has for example 2 countries with values and 2 countries  
without values, but table d_nom has for all4 countries values, my end  
result would be only 50% of the real value, which is wrong. I have to  
multiply and to divide through the same amount of countries.


Could anyone give me a hint how this would work? Do I need to add a  
subquery?


Thanks a lot!

Stef

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Performance comparison

[Thomas Kellerer]

Greg Smith, 25.02.2010 03:13:

Martijn van Oosterhout wrote:

I remember a while back someone posted a graphs showing a scalability
of postgresql for various versions (I think 8.0 to 8.4). I've tried to
find this image again but havn't been able to locate it. Does anyone
here remember?


http://suckit.blog.hu/2009/09/29/postgresql_history



It would be interesting to know why the max. performance in the r/w scenario 
for 8.4.1 is lower compared to 8.3.7 (and if maybe 8.4.2 fixed this)

Thomas



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

[GENERAL] Restricting the CREATEROLE privilege

[Wappler, Robert]

Good Morning,
is there a way to limit the CREATEROLE privilege to a specific database?
I currently set up an automated integration test environment. This includes a
database owned by a specific user which should have all degrees of freedom
for installing whatever database schemas are in the current revision as well as
creating roles used by the test cases to access the database.

I do not want that user to create roles, which are then available in every
database.

--

Robert...



 


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

[Martijn van Oosterhout]

On Wed, Feb 24, 2010 at 09:13:36PM -0500, Greg Smith wrote:
> Martijn van Oosterhout wrote:
>> I remember a while back someone posted a graphs showing a scalability
>> of postgresql for various versions (I think 8.0 to 8.4). I've tried to
>> find this image again but havn't been able to locate it. Does anyone
>> here remember?
>>   
>
> http://suckit.blog.hu/2009/09/29/postgresql_history

Yes, that's the one, thank you.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature