Thread: Issue with CREATE EXTENSION tablefuncKreiter

Issue with CREATE EXTENSION tablefuncKreiter

From
Chris Travers
Date:
Hi all;

We have gotten a report from a user who is having issues with CREATE EXTENSION tablefunc.  I figured I would ask for additional insight and assistance at this point.

When the user tries to run CREATE EXTENSION tablefunc; the following occurs:

-bash-4.2$ dropdb ext_test
-bash-4.2$ createdb ext_test
-bash-4.2$ psql ext_test
psql (9.1.2)
Type "help" for help.

ext_test=# select version();
                                                   version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.2 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)
4.6.2 20111027 (Red Hat 4.6.2-1), 64-bit
(1 row)

ext_test=# CREATE EXTENSION tablefunc;
ERROR:  type "tablefunc_crosstab_2" already exists

This lead me to conclude that we needed to CREATE EXTENSION FROM UNPACKAGED thinking this might be an upgrade issue.  However no luck.

ext_test=# CREATE EXTENSION tablefunc FROM unpackaged;
ERROR:  function normal_rand(integer, double precision, double
precision) does not exist


What should be tried next?

Best Wishes,
Chris Travers

Re: Issue with CREATE EXTENSION tablefuncKreiter

From
Merlin Moncure
Date:
On Wed, Feb 1, 2012 at 8:52 AM, Chris Travers <chris.travers@gmail.com> wrote:
> Hi all;
>
> We have gotten a report from a user who is having issues with CREATE
> EXTENSION tablefunc.  I figured I would ask for additional insight and
> assistance at this point.
>
> When the user tries to run CREATE EXTENSION tablefunc; the following occurs:
>
> -bash-4.2$ dropdb ext_test
> -bash-4.2$ createdb ext_test
> -bash-4.2$ psql ext_test
> psql (9.1.2)
> Type "help" for help.
>
> ext_test=# select version();
>                                                    version
> -------------------------------------------------------------------------------------------------------------
>  PostgreSQL 9.1.2 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)
> 4.6.2 20111027 (Red Hat 4.6.2-1), 64-bit
> (1 row)
>
> ext_test=# CREATE EXTENSION tablefunc;
> ERROR:  type "tablefunc_crosstab_2" already exists
>
> This lead me to conclude that we needed to CREATE EXTENSION FROM UNPACKAGED
> thinking this might be an upgrade issue.  However no luck.
>
> ext_test=# CREATE EXTENSION tablefunc FROM unpackaged;
> ERROR:  function normal_rand(integer, double precision, double
> precision) does not exist
>
>
> What should be tried next?

by any chance did you create the extension in the template1 database?

merlin

Re: Issue with CREATE EXTENSION tablefuncKreiter

From
Tom Lane
Date:
Merlin Moncure <mmoncure@gmail.com> writes:
> On Wed, Feb 1, 2012 at 8:52 AM, Chris Travers <chris.travers@gmail.com> wrote:
>> ext_test=# CREATE EXTENSION tablefunc;
>> ERROR: �type "tablefunc_crosstab_2" already exists
>>
>> This lead me to conclude that we needed to CREATE EXTENSION FROM UNPACKAGED
>> thinking this might be an upgrade issue. �However no luck.
>>
>> ext_test=# CREATE EXTENSION tablefunc FROM unpackaged;
>> ERROR: �function normal_rand(integer, double precision, double
>> precision) does not exist

> by any chance did you create the extension in the template1 database?

The whole extension is not in template1, else "CREATE FROM unpackaged"
would have worked.  But evidently there are at least some conflicting
objects there.  I speculate that somebody installed a pre-9.1 version of
the extension in template1, realized their mistake, and then tried to
clean it out manually (piecemeal) instead of using the uninstall script.
And missed some things.

Anyway the solution is to connect to template1 and drop any cruft that's
lying around in it.

            regards, tom lane

Re: Issue with CREATE EXTENSION tablefuncKreiter

From
Philip Rhoades
Date:
Tom,


On 2012-02-02 02:52, Tom Lane wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Wed, Feb 1, 2012 at 8:52 AM, Chris Travers
>> <chris.travers@gmail.com> wrote:
>>> ext_test=# CREATE EXTENSION tablefunc;
>>> ERROR:  type "tablefunc_crosstab_2" already exists
>>>
>>> This lead me to conclude that we needed to CREATE EXTENSION FROM
>>> UNPACKAGED
>>> thinking this might be an upgrade issue.  However no luck.
>>>
>>> ext_test=# CREATE EXTENSION tablefunc FROM unpackaged;
>>> ERROR:  function normal_rand(integer, double precision, double
>>> precision) does not exist
>
>> by any chance did you create the extension in the template1
>> database?
>
> The whole extension is not in template1, else "CREATE FROM
> unpackaged"
> would have worked.  But evidently there are at least some conflicting
> objects there.  I speculate that somebody installed a pre-9.1 version
> of
> the extension in template1, realized their mistake, and then tried to
> clean it out manually (piecemeal) instead of using the uninstall
> script.
> And missed some things.
>
> Anyway the solution is to connect to template1 and drop any cruft
> that's
> lying around in it.

I am the user who has the situation - the background is:

- the previous installation on Fedora 14 x86_64 was PostgreSQL 8.x

- there were old (unused) versions of LedgerSMB

- during the move from Fedora 14 to Fedora 16 (x86_64), the data was
dumped out of PG 8.x and restored to PG 9.1P

I haven't done any manual messing around with template1 as far as I
know . .

Regards,

Phil.
--
Philip Rhoades

GPO Box 3411
Sydney NSW    2001
Australia
E-mail:  phil@pricom.com.au


Re: Issue with CREATE EXTENSION tablefuncKreiter

From
Tom Lane
Date:
Philip Rhoades <phil@pricom.com.au> writes:
> On 2012-02-02 02:52, Tom Lane wrote:
>> Anyway the solution is to connect to template1 and drop any cruft
>> that's lying around in it.

> I haven't done any manual messing around with template1 as far as I
> know . .

Well, the behavior you describe indicates pretty strongly that there are
some non-factory-standard objects in template1.  If the database is a
few years old, that's not exactly hard to believe --- all it takes is
one time having connected to the wrong place and created some stuff.
Just go have a look and get rid of what you find ...

            regards, tom lane

Re: Issue with CREATE EXTENSION tablefuncKreiter

From
Chris Travers
Date:


On Wed, Feb 1, 2012 at 1:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Philip Rhoades <phil@pricom.com.au> writes:
> On 2012-02-02 02:52, Tom Lane wrote:
>> Anyway the solution is to connect to template1 and drop any cruft
>> that's lying around in it.

> I haven't done any manual messing around with template1 as far as I
> know . .

Well, the behavior you describe indicates pretty strongly that there are
some non-factory-standard objects in template1.  If the database is a
few years old, that's not exactly hard to believe --- all it takes is
one time having connected to the wrong place and created some stuff.
Just go have a look and get rid of what you find ...

What about running the 9.0 tablefunc-uninstall.sql?

Best Wishes,
Chris Travers 

                       regards, tom lane