Re: [BUGS] BUG #6718: Cannot delete, create or check existence of extension

[Craig Ringer]

On 07/05/2012 09:06 PM, Gary Haran wrote:
I installed it using an hstore.sql file that comes with the home brew 
(https://github.com/mxcl/homebrew/) version of postgresql on OS X. 
 Maybe we missed a step or something in the whole installation process.


We most likely upgraded our pg version at some point (we try to stay 
with the times).
If you upgrade an existing database to 9.1 and that database has contrib 
modules loaded in it, you must convert them to extensions using the 
syntax I gave previously. It's right there in the release notes under 
"Migrating to version 9.1":


http://www.postgresql.org/docs/9.1/static/release-9-1.html#AEN107367

This is a bit of a wart, in that it'd be much nicer to be able to 
transparently re-package pre-extension contrib modules into extensions. 
I know Dimitri and Tom tried to get this working, but it clearly wasn't 
possible to make it work simply and reliably. Since it's a one-off 
migration during upgrade, it isn't that big a deal.


If you need help doing this or you have further questions, please ask 
for help on the pgsql-general mailing list.


--
Craig Ringer

Re: [BUGS] BUG #6706: pg_upgrade fails when plpgsql dropped/re-created

[Bruce Momjian]

On Thu, Jul 05, 2012 at 12:21:58AM -0400, Tom Lane wrote:
> Bruce Momjian  writes:
> >>> address the points I made about reproducing the previous state in cases
> >>> where the admin removed the language or changed its permissions.
> 
> >> Well, it still does the create extension in binary mode like before ---
> >> not sure what the problem is.
> 
> > Applied and back-patched to 9.2.
> 
> I do not believe that this patch fixes the problem, and I also believe
> that it creates new problems.  Please revert.

I asked for an explaination of the problem, but received no reply:

http://archives.postgresql.org/pgsql-bugs/2012-07/msg5.php

so I assumed you were fine with it.  Please explain.  Does anyone else
understand the problem Tom is seeing?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [BUGS] BUG #6718: Cannot delete, create or check existence of extension

[Jaime Casanova]

On Thu, Jul 5, 2012 at 1:56 AM, Craig Ringer  wrote:
> On 07/05/2012 02:05 AM, gary.ha...@gmail.com wrote:
>>
>> development=# create extension hstore;
>> ERROR:  type "hstore" already exists
>
> First, thanks for the info in the report.
>
> At a guess, it has the hstore data type in it from before the extension
> system exists. You need to follow the upgrade instructions to convert it to
> an extension.

what about adding a HINT there? something like "you probably need to
use CREATE EXTENSION ... FROM unpackaged"

-- 
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

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

Re: [BUGS] BUG #6718: Cannot delete, create or check existence of extension

[Gary Haran]

I installed it using an hstore.sql file that comes with the home brew (
https://github.com/mxcl/homebrew/) version of postgresql on OS X.  Maybe we
missed a step or something in the whole installation process.

We most likely upgraded our pg version at some point (we try to stay with
the times).

Thanks for your help!


 Gary Haran
Phone: 514.909.4947
 [image: Facebook]  [image:
Twitter] [image:
LinkedIn] 



On Thu, Jul 5, 2012 at 2:56 AM, Craig Ringer  wrote:

> On 07/05/2012 02:05 AM, gary.ha...@gmail.com wrote:
>
>> development=# create extension hstore;
>> ERROR:  type "hstore" already exists
>> development=# drop extension hstore;
>> ERROR:  extension "hstore" does not exist
>> development=# CREATE EXTENSION IF NOT EXISTS hstore;
>> ERROR:  type "hstore" already exists
>>
>
> First, thanks for the info in the report.
>
> At a guess, it has the hstore data type in it from before the extension
> system exists. You need to follow the upgrade instructions to convert it to
> an extension. This isn't a bug in that Pg is working as designed, though
> it's certainly not very nice user interface.
>
> See:
>
>   
> http://www.postgresql.org/**docs/9.1/static/sql-**createextension.html
>
> specifically the "FROM old_version" clause, eg:
>
>   CREATE EXTENSION hstore FROM unpackaged;
>
> (I think that's right; I haven't used the extension system in an upgrade).
>
> If you need more help please ask on pgsql-general as this doesn't appear
> to be a bug from the information supplied. However, I think "CREATE
> EXTENSION" should emit a HINT for already-exists errors, guiding people to
> info on how to upgrade the extension.
>
> I'm assuming the database "development" was loaded from a dump from an
> older version or was binary-upgraded from an older version. If it was
> created anew on 9.1, how did the "hstore" type get loaded?
>
> --
> Craig Ringer
>

Re: [BUGS] BUG #6718: Cannot delete, create or check existence of extension

[Craig Ringer]

On 07/05/2012 10:18 PM, Jaime Casanova wrote:

On Thu, Jul 5, 2012 at 1:56 AM, Craig Ringer  wrote:

On 07/05/2012 02:05 AM, gary.ha...@gmail.com wrote:

development=# create extension hstore;
ERROR:  type "hstore" already exists

First, thanks for the info in the report.

At a guess, it has the hstore data type in it from before the extension
system exists. You need to follow the upgrade instructions to convert it to
an extension.

what about adding a HINT there? something like "you probably need to
use CREATE EXTENSION ... FROM unpackaged"

Yeah, I suggested that. It's not trivial to make sure that this is 
really because of the extension and not because the user just happened 
to create their own type called "hstore" (or whatever the 
type(s)/function(s) the ext provides) are, but I guess with a hint you 
don't have to. I'm assuming if it were simple it would've been done as 
part of extension implementation. It's certainly way beyond me.


--
Craig Ringer


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

[BUGS] Problem with pg_upgrade

[Payal Singh]

Hello,

I am trying to use pg_upgrade to upgrade data from 9.1.4 to 9.2beta2.
Although the upgrade completed successfully,  vacuumdb --all --analyze-only
gives an error. I tried upgrading two binary backups of the same production
database from different days, and both returned the exact same error . The
output I got for both trials is as follows:

First trial:


   1. postgres@sparedb1:/data/pg$ sh analyze_new_cluster.sh
   2. This script will generate minimal optimizer statistics rapidly
   3. so your system is usable, and then gather statistics twice more
   4. with increasing accuracy.  When it is done, your system will
   5. have the default level of optimizer statistics.
   6.
   7. If you have used ALTER TABLE to modify the statistics target for
   8. any tables, you might want to remove them and restore them after
   9. running this script because they will delay fast statistics
   generation.
   10.
   11. If you would like default statistics as quickly as possible, cancel
   12. this script and run:
   13. vacuumdb --all --analyze-only
   14.
   15. Generating minimal optimizer statistics (1 target)
   16. --
   17. vacuumdb: vacuuming database "functionx"
   18. vacuumdb: vacuuming database "postgres"
   19. vacuumdb: vacuuming of database "postgres" failed: ERROR:  could not
   access status of transaction 46675125
   20. DETAIL:  Could not open file "pg_clog/002C": No such file or
   directory.
   21.
   22. The server is now available with minimal optimizer statistics.
   23. Query performance will be optimal once this script completes.
   24.
   25. Generating medium optimizer statistics (10 targets)
   26. ---
   27. vacuumdb: vacuuming database "functionx"
   28. vacuumdb: vacuuming database "postgres"
   29. vacuumdb: vacuuming of database "postgres" failed: ERROR:  could not
   access status of transaction 46675125
   30. DETAIL:  Could not open file "pg_clog/002C": No such file or
   directory.
   31.
   32. Generating default (full) optimizer statistics (100 targets?)
   33. -
   34. vacuumdb: vacuuming database "functionx"
   35. vacuumdb: vacuuming database "postgres"
   36. vacuumdb: vacuuming of database "postgres" failed: ERROR:  could not
   access status of transaction 46675125
   37. DETAIL:  Could not open file "pg_clog/002C": No such file or
   directory.
   38.
   39. Done
   40. postgres@sparedb1:/data/pg$

Second Trial:


   1. postgres@sparedb1:/data/pg$ /opt/pgbrew/9.2beta2}/bin/pg_upgrade -d
   /data/pg/9.1 -D /data/pg/9.2 -b /opt/pgbrew/9.1.4/bin -B /opt/pgbrew/9
   .2beta2}/bin
   2. Performing Consistency Checks
   3. -
   4. Checking current, bin, and data directories ok
   5. Checking cluster versions   ok
   6. Checking database user is a superuser   ok
   7. Checking for prepared transactions  ok
   8. Checking for reg* system OID user data typesok
   9. Checking for contrib/isn with bigint-passing mismatch   ok
   10. Creating catalog dump   ok
   11. Checking for prepared transactions  ok
   12. Checking for presence of required libraries ok
   13.
   14. If pg_upgrade fails after this point, you must re-initdb the
   15. new cluster before continuing.
   16.
   17. Performing Upgrade
   18. --
   19. Analyzing all rows in the new cluster   ok
   20. Freezing all rows on the new clusterok
   21. Deleting new commit clogs   ok
   22. Copying old commit clogs to new server  ok
   23. Setting next transaction ID for new cluster ok
   24. Resetting WAL archives  ok
   25. Setting frozenxid counters in new cluster   ok
   26. Creating databases in the new cluster   ok
   27. Adding support functions to new cluster ok
   28. Restoring database schema to new clusterok
   29. Removing support functions from new cluster ok
   30. Copying user relation files
   31. ok
   32. Setting next OID for new clusterok
   33. Creating script to analyze new cluster  ok
   34. Creating script to delete old cluster   ok
   35.
   36. Upgrade Complete
   37. 
   38. Optimizer statistics are not transferred by pg_upgrade so,
   39. once you start the new server, consider running:
   40. analyze_new_cluster.sh
   41.
   42. Running this script will delete the old cluster's data files:
   43. delete_old_cluster.sh
   44

Re: [BUGS] Problem with pg_upgrade

[Craig Ringer]

On 07/05/2012 11:20 PM, Payal Singh wrote:

Hello,

I am trying to use pg_upgrade to upgrade data from 9.1.4 to 9.2beta2. 
Although the upgrade completed successfully, vacuumdb --all 
--analyze-only gives an error. I tried upgrading two binary backups of 
the same production database from different days, and both returned 
the exact same error . The output I got for both trials is as follows:


Just out of interest, if you try to use pg_ctl to start a 9.1 server 
from those backups, do they work? Can they be vaccuumed then?


If you start 9.1 on a copy of the backup then cleanly stop it again, 
does pg_upgrade then run?


--
Craig Ringer

Re: [BUGS] BUG #6715: 9.2b2 psql \ir does not understand leading ../

[Tom Lane]

Josh Kupershmidt  writes:
> On Tue, Jul 3, 2012 at 11:41 PM, Tom Lane  wrote:
>> The get_parent_directory() call reduces "foo.sql" to an empty string,
>> which seems a tad bogus --- wouldn't "." be better?  But in any case,
>> join_path_components() thinks it can process a "../" prefix of the
>> tail argument by stripping a directory name from the head argument,
>> and there's nothing there to strip.  So IMO join_path_components()
>> is flat-out broken when applied to a non-absolute head path.
>> Not sure about what a useful solution would be.

> I may not have time to look at this today, but I think this behavior
> worked fine in the last version[1] of Gurjeet's \ir patch which I
> reviewed, which had different behavior for pathname normalization than
> what got committed.
> [1] 
> http://archives.postgresql.org/message-id/BANLkTi=eW_nUH9195=9upqf7treg4uh...@mail.gmail.com

[ looks at that... ]  Well, I can certainly see why Robert got rid of
that kluge in favor of using the path-manipulation functions we already
have.  It looks like most of the existing uses of join_path_components
are using absolute paths (eg, the result of getcwd), which probably
explains why we've not previously noticed that it's not working
correctly in such cases.

On reflection I think that what we need to do is fix it so that it only
strips "../" from the tail string when there is a directory name
available to be stripped from the head string.  Otherwise just stop
trimming.

regards, tom lane

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

Re: [BUGS] BUG #6715: 9.2b2 psql \ir does not understand leading ../

[Tom Lane]

I wrote:
> On reflection I think that what we need to do is fix it so that it only
> strips "../" from the tail string when there is a directory name
> available to be stripped from the head string.  Otherwise just stop
> trimming.

Actually, there is a much simpler fix: don't let it strip ".." at all.
There is no correctness reason to do that, and as for cosmetics, callers
who care can use canonicalize_path() to remove ".." correctly.  (AFAICS,
all existing callers of join_path_components already do so anyway.)

regards, tom lane

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

Re: [BUGS] Problem with pg_upgrade

[Payal Singh]

Thank you. That worked.

Regards
Payal

On Thu, Jul 5, 2012 at 12:15 PM, Craig Ringer  wrote:

>  On 07/05/2012 11:20 PM, Payal Singh wrote:
>
> Hello,
>
>  I am trying to use pg_upgrade to upgrade data from 9.1.4 to 9.2beta2.
> Although the upgrade completed successfully,  vacuumdb --all --analyze-only
> gives an error. I tried upgrading two binary backups of the same production
> database from different days, and both returned the exact same error . The
> output I got for both trials is as follows:
>
>
> Just out of interest, if you try to use pg_ctl to start a 9.1 server from
> those backups, do they work? Can they be vaccuumed then?
>
> If you start 9.1 on a copy of the backup then cleanly stop it again, does
> pg_upgrade then run?
>
> --
> Craig Ringer
>



-- 
Payal Singh
Graduate Student
Department of Computer Science and Electrical Engineering
University of Maryland, Baltimore County

Re: [BUGS] Problem with pg_upgrade

[Tom Lane]

Payal Singh  writes:
> On Thu, Jul 5, 2012 at 12:15 PM, Craig Ringer  wrote:
>> If you start 9.1 on a copy of the backup then cleanly stop it again, does
>> pg_upgrade then run?

> Thank you. That worked.

ISTM that pg_upgrade should check that the old cluster was shut down
cleanly, ie pg_control has state = "shut down".  AFAICT from some
testing, it currently only checks that there is no postmaster.pid file,
which is easily bypassed by users who might not realize that it's not
safe to run pg_upgrade against a filesystem backup.

BTW, I also noticed while trying to test this that pg_upgrade is
currently completely broken for the case of taking PGDATAOLD or
PGDATANEW from the environment rather than switches.  This is because
the existing coding in option.c fails to set up the "pgconfig" fields
in such cases.

regards, tom lane

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

[BUGS] BUG #6720: Its often disconnecting

[balaji03]

The following bug has been logged on the website:

Bug reference:  6720
Logged by:  balaji
Email address:  balaj...@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Windows Xp
Description:

Hi,
 Am using version 1.14.1 it often disconnecting with some error report and
no proper message why its disconnecting.checked the  reports but couldnot
able to retrieve it and is there any fix i can do it..Error is PGADMIN 111
has encountered a problem and need to close.we are sorry for the
inconvenience.Please advise me.


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

[BUGS] BUG #6719: PG shouldn't filter on same criteria as index search

[blove319]

The following bug has been logged on the website:

Bug reference:  6719
Logged by:  BLove
Email address:  blove...@hotmail.com
PostgreSQL version: 9.1.3
Operating system:   Centos 5
Description:

After doing an equality search on an indexed field, PG is doing a filter on
the same value using a regex.  That appears to be a useless operation that
will only consume cpu cycles.

explain select * from transactions.subscriber_504700_txns_y2012m05 lh where
lh.account_code LIKE '34016975\_DAT\_FR\_HF';
  QUERY PLAN
 
--
 Index Scan using idx_subscriber_504700_txns_y2012m05_account_code on
subscriber_504700_txns_y2012m05 lh  (cost=0.00..17.02 rows=3 width=131)
   Index Cond: (account_code = '34016975_DAT_FR_HF'::text)
   Filter: (account_code ~~ '34016975\_DAT\_FR\_HF'::text)



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

Re: [BUGS] BUG #6719: PG shouldn't filter on same criteria as index search

[Tom Lane]

blove...@hotmail.com writes:
> After doing an equality search on an indexed field, PG is doing a filter on
> the same value using a regex.  That appears to be a useless operation that
> will only consume cpu cycles.

Getting rid of the filter expression isn't really worth the trouble.

If we had done the transformation of regexes to index conditions in a
different way, it might be practical to suppress the redundant test,
but given the way the code is structured we can't easily.

regards, tom lane

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

Re: [BUGS] BUG #6720: Its often disconnecting

[Craig Ringer]

On 07/06/2012 03:57 AM, balaj...@gmail.com wrote:

The following bug has been logged on the website:

Bug reference:  6720
Logged by:  balaji
Email address:  balaj...@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Windows Xp
Description:

Error is PGADMIN 111
has encountered a problem and need to close.we are sorry for the
inconvenience.Please advise me.
PgAdmin-III is crashing. With the amount of information you have 
provided it is impossible to guess why. You haven't said what you're 
doing when it crashes, whether you can make it crash at the same point 
every time, etc.


Honestly, for PgAdmin you're probably better off seeking help on their 
mailing list:


http://www.pgadmin.org/support/list.php

as I'm not sure how many of the PgAdmin folks read pgsql-bugs.

--
Craig Ringer

Re: [BUGS] Problem with pg_upgrade

[Bruce Momjian]

On Thu, Jul 05, 2012 at 06:28:31PM -0400, Tom Lane wrote:
> Payal Singh  writes:
> > On Thu, Jul 5, 2012 at 12:15 PM, Craig Ringer  wrote:
> >> If you start 9.1 on a copy of the backup then cleanly stop it again, does
> >> pg_upgrade then run?
> 
> > Thank you. That worked.
> 
> ISTM that pg_upgrade should check that the old cluster was shut down
> cleanly, ie pg_control has state = "shut down".  AFAICT from some
> testing, it currently only checks that there is no postmaster.pid file,
> which is easily bypassed by users who might not realize that it's not
> safe to run pg_upgrade against a filesystem backup.

I am confused.  pg_upgrade certainly starts/stops the old and new server
with pg_ctl before copying any files --- isn't that sufficent?

> BTW, I also noticed while trying to test this that pg_upgrade is
> currently completely broken for the case of taking PGDATAOLD or
> PGDATANEW from the environment rather than switches.  This is because
> the existing coding in option.c fails to set up the "pgconfig" fields
> in such cases.

Oh, good catch.  Fixed with the attached patch, and backpatched to 9.2.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/option.c b/contrib/pg_upgrade/option.c
new file mode 100644
index 43394a0..960fcda
*** a/contrib/pg_upgrade/option.c
--- b/contrib/pg_upgrade/option.c
***
*** 21,27 
  
  
  static void usage(void);
! static void check_required_directory(char **dirpath,
     char *envVarName, char *cmdLineOption, char *description);
  
  
--- 21,27 
  
  
  static void usage(void);
! static void check_required_directory(char **dirpath, char **configpath,
     char *envVarName, char *cmdLineOption, char *description);
  
  
*** parseCommandLine(int argc, char *argv[])
*** 203,216 
  	}
  
  	/* Get values from env if not already set */
! 	check_required_directory(&old_cluster.bindir, "PGBINOLD", "-b",
  			 "old cluster binaries reside");
! 	check_required_directory(&new_cluster.bindir, "PGBINNEW", "-B",
  			 "new cluster binaries reside");
! 	check_required_directory(&old_cluster.pgdata, "PGDATAOLD", "-d",
! 			 "old cluster data resides");
! 	check_required_directory(&new_cluster.pgdata, "PGDATANEW", "-D",
! 			 "new cluster data resides");
  }
  
  
--- 203,216 
  	}
  
  	/* Get values from env if not already set */
! 	check_required_directory(&old_cluster.bindir, NULL, "PGBINOLD", "-b",
  			 "old cluster binaries reside");
! 	check_required_directory(&new_cluster.bindir, NULL, "PGBINNEW", "-B",
  			 "new cluster binaries reside");
! 	check_required_directory(&old_cluster.pgdata, &old_cluster.pgconfig,
! 			 "PGDATAOLD", "-d", "old cluster data resides");
! 	check_required_directory(&new_cluster.pgdata, &new_cluster.pgconfig,
! 			 "PGDATANEW", "-D", "new cluster data resides");
  }
  
  
*** or\n"), old_cluster.port, new_cluster.po
*** 284,298 
   * user hasn't provided the required directory name.
   */
  static void
! check_required_directory(char **dirpath, char *envVarName,
! 		 char *cmdLineOption, char *description)
  {
  	if (*dirpath == NULL || strlen(*dirpath) == 0)
  	{
  		const char *envVar;
  
  		if ((envVar = getenv(envVarName)) && strlen(envVar))
  			*dirpath = pg_strdup(envVar);
  		else
  			pg_log(PG_FATAL, "You must identify the directory where the %s.\n"
     "Please use the %s command-line option or the %s environment variable.\n",
--- 284,303 
   * user hasn't provided the required directory name.
   */
  static void
! check_required_directory(char **dirpath, char **configpath,
! 		 char *envVarName, char *cmdLineOption,
! 		 char *description)
  {
  	if (*dirpath == NULL || strlen(*dirpath) == 0)
  	{
  		const char *envVar;
  
  		if ((envVar = getenv(envVarName)) && strlen(envVar))
+ 		{
  			*dirpath = pg_strdup(envVar);
+ 			if (configpath)
+ *configpath = pg_strdup(envVar);
+ 		}
  		else
  			pg_log(PG_FATAL, "You must identify the directory where the %s.\n"
     "Please use the %s command-line option or the %s environment variable.\n",

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

Re: [BUGS] BUG #6720: Its often disconnecting

[Craig Ringer]

On 07/06/2012 12:19 PM, Balaji A wrote:


  Thanks for replying and PFA screenshot and let me know if you need 
more information on this.


Please re-read my reply, which explained to you what more information 
would be needed and included a link to where you should follow this up.


I will repeat it below. If you do not understand what I am saying that 
is OK, but you need to tell me that so I can explain it a different way.


PgAdmin-III is crashing. With the amount of information you have 
provided it is impossible to guess why. You haven't said what you're 
doing when it crashes, whether you can make it crash at the same point 
every time, etc.


Honestly, for PgAdmin you're probably better off seeking help on their 
mailing list:


http://www.pgadmin.org/support/list.php

as I'm not sure how many of the PgAdmin folks read pgsql-bugs.



--
Craig Ringer