Re: [Postfixadmin-devel] Setup error

[Geoff S. <ge...@Qu...>]

On Thu, 5 May 2011, David Goodwin wrote:

> See below.

Thanks for getting back to me.

>>    updating to version 730 (PgSQL)...
>>
>>    CREATE LANGUAGE plpgsql
>>
>>    DEBUG INFORMATION:
>>    Invalid query: ERROR: must be owner of database postfixadmin
>>
>>    CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS
>>    $merge_quota$ BEGIN UPDATE quota SET current = NEW.current + current
>>    WHERE username = NEW.username AND path = NEW.path; IF found THEN RETURN
>>    NULL; ELSE RETURN NEW; END IF; END; $merge_quota$ LANGUAGE plpgsql;
>>
>
>
> sudo -s (or otherwise become root)
>
> su - postgres
>
> psql postfix    (assuming db name is postfix; "psql -l" will list all databases)

postgres@patronus:~$ psql postfix
psql: FATAL:  database "postfix" does not exist
postgres@patronus:~$ psql -l
                                    List of databases
      Name     |  Owner   | Encoding |  Collation  |    Ctype    |   Access 
privileges
--------------+----------+----------+-------------+-------------+-----------------------
  postfixadmin | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
=c/postgres
                                                                 : 
postgres=CTc/postgres
  template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
=c/postgres
                                                                 : 
postgres=CTc/postgres
(4 rows)

postgres@patronus:~$ psql postfixadmin
psql (8.4.7)
Type "help" for help.

> Copy and paste the above in.... if that fails, try the 'create language plpgsql' then paste the above in.

  postfixadmin=# CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER 
AS $merge_quota$ BEGIN UPDATE quota SET current = NEW.current + current 
WHERE username = NEW.username AND path = NEW.path; IF found THEN RETURN 
NULL; ELSE RETURN NEW; END IF; END; $merge_quota$ LANGUAGE plpgsql;
ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE LANGUAGE to load the language into the database.
postfixadmin=# create language plpgsql;
CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS $merge_quota$ 
BEGIN
UPDATE quota SET current = NEW.current + current WHERE username = 
NEW.username AND path = NEW.path; IF found THEN RETURN NULL; ELSE RETURN 
NEW; END IF; END; $merge_quota$ LANGUAGE plpgsql;
CREATE LANGUAGE
CREATE FUNCTION

Looks like that worked.

    updating to version 730 (PgSQL)...

    CREATE LANGUAGE plpgsql

    DEBUG INFORMATION:
    Invalid query: ERROR: language "plpgsql" already exists

    CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS
    $merge_quota$ BEGIN UPDATE quota SET current = NEW.current + current
    WHERE username = NEW.username AND path = NEW.path; IF found THEN RETURN
    NULL; ELSE RETURN NEW; END IF; END; $merge_quota$ LANGUAGE plpgsql;

    DEBUG INFORMATION:
    Invalid query: ERROR: must be owner of function merge_quota

Ergh.  Looks like I should have only run the "create language plpgsql" 
statement as user postgres and run the second query via the setup script.

I'm guessing I can undo what I did as postgres, but as I'm not very 
familiar with postgresql, I'm not sure what I should do next.

> I'm wondering if it's a case of some default setting changing....

Is there more information I can give you?

Geoff.