Re: [GENERAL] ERROR: could not access status of transaction

[Stuart Grimshaw]

On 12/23/06, Stuart Grimshaw <[EMAIL PROTECTED]> wrote:

berble=# select * from headlines ;
ERROR:  could not access status of transaction 1668180339
DETAIL:  could not open file "pg_clog/0636": No such file or directory

Using Postgres 8.1.5 from Debian unstable, I got the above error, and
I have no idea what it means?


Is there any more info I can give you guys to help sort this out?

--
-S

Sports Photography in South Yorkshire & Derbyshire
http://www.stuartgrimshaw.co.uk

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] ERROR: could not access status of transaction

[Stuart Grimshaw]

berble=# select * from headlines ;
ERROR:  could not access status of transaction 1668180339
DETAIL:  could not open file "pg_clog/0636": No such file or directory

Using Postgres 8.1.5 from Debian unstable, I got the above error, and
I have no idea what it means?

--
-S

Sports Photography in South Yorkshire & Derbyshire
http://www.stuartgrimshaw.co.uk

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Enableing contrib modules on Debian

[Stuart Grimshaw]

On 10/25/06, Tom Lane <[EMAIL PROTECTED]> wrote:

"Stuart Grimshaw" <[EMAIL PROTECTED]> writes:
> I've installed the postgresql-contrib8.1 deb package to get at the
> earthdistance function, but it doesn't seem to be recognised, even
> after a restart of the server.

> Is there a final step I need to take after using apt-get to install?

Yeah, you have to run the SQL script included in the module to define
its functions within a particular database.  Look for earthdistance.sql
among the files installed by the .deb (it's likely under /usr/share).


Thanks Tom, the .sql is stored in /usr/share/postgres/8.1/contrib

All sorted now.

--
-S

Sports Photography in South Yorkshire & Derbyshire
http://www.stuartgrimshaw.co.uk

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Enableing contrib modules on Debian

[Stuart Grimshaw]

Hi,

I've installed the postgresql-contrib8.1 deb package to get at the
earthdistance function, but it doesn't seem to be recognised, even
after a restart of the server.

Is there a final step I need to take after using apt-get to install?

--
-S

Sports Photography in South Yorkshire & Derbyshire
http://www.stuartgrimshaw.co.uk

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] ERROR: checkpoint request failed

[Stuart Grimshaw]

When I try and create a new database I get the following error:

ERROR:  checkpoint request failed
HINT:  Consult the server log for details.

but the error log (/var/log/postgresql/postgresql-8.1-main.log) just
repeats the same error message.

This is Postgres 8.1, from Debian Unstable debs. There are other
databases on the same server, I somehow managed to create them ( a few
months ago)

--
-S

Sports Photography in South Yorkshire & Derbyshire
http://www.stuartgrimshaw.co.uk

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Stored procedure doesn't return expected result.

[Stuart Grimshaw]

On 2/26/06, Andreas Kretschmer <[EMAIL PROTECTED]> wrote:
> Stuart Grimshaw <[EMAIL PROTECTED]> schrieb:
>
> > I'm writing a script to clean up some data in a table, the data I'm
> > using as the source is held in emails, so I've written a perl script
> > to extract the info. Unfortunatly this email doesn't contain the
> > client id, so I've written a stored procedure to extract it.
> >
> > create or replace function get_client_id(text) returns integer as $$
> > SELECT intclientid FROM client WHERE vchname = '$1';
>  ^  ^
>
> remove the '
>
> test=# select * from foo1;
>  x | i
> ---+---
>  a | 1
>  b | 2
> (2 rows)
>
> test=# create or replace function get_i(varchar) returns int as $$
> select i from foo1 where x = $1;$$ language sql;
> CREATE FUNCTION
> test=# select get_i('a');
>  get_i
> ---
>  1
> (1 row)

That's got it. Obviously it understands that $1 is a string and not a
column name.

Thanks very much.

--
-S
http://www.makepovertyhistory.org/

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Stored procedure doesn't return expected result.

[Stuart Grimshaw]

I'm writing a script to clean up some data in a table, the data I'm
using as the source is held in emails, so I've written a perl script
to extract the info. Unfortunatly this email doesn't contain the
client id, so I've written a stored procedure to extract it.

create or replace function get_client_id(text) returns integer as $$
SELECT intclientid FROM client WHERE vchname = '$1';
$$ LANGUAGE SQL;

However, when I do this:

select get_client_id('Stuart Grimshaw');

I get no results, yet:

SELECT intclientid FROM client WHERE vchname = 'Stuart Grimshaw';

Gives me the result I would expect:

 intclientid
-
   3

What am I doing wrong in the stored procedure?

--
-S
http://www.makepovertyhistory.org/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match