Thread: Hanging creating of function
Can anyone explain why the first one never completes, but the second one does? (the first one just keeps running, I
canceledafter ~1 min)
PG version: 8.1 final
-- tblname param has type varchar
create or replace function getcolstring (tblname varchar) returns varchar as $$
declare
table_columns varchar := '';
column_name record;
begin
for column_name in select pga.attname from pg_attribute pga, pg_class pgc
where pga.attrelid = pgc.relfilenode and pgc.relname = tblname and pga.attnum > 0 loop
table_columns := table_columns || column_name.attname || ',';
end loop;
-- chop the last ','
table_columns := substr(table_columns,1,(length(table_columns)-1));
return table_columns;
end;
$$
language plpgsql;
-- tblname param has type text
create or replace function getcolstring (tblname text) returns varchar as $$
declare
table_columns varchar := '';
column_name record;
begin
for column_name in select pga.attname from pg_attribute pga, pg_class pgc
where pga.attrelid = pgc.relfilenode and pgc.relname = tblname and pga.attnum > 0 loop
table_columns := table_columns || column_name.attname || ',';
end loop;
-- chop the last ','
table_columns := substr(table_columns,1,(length(table_columns)-1));
return table_columns;
end;
$$
language plpgsql;
/Mikael
On Wed, Nov 09, 2005 at 07:38:53PM +0100, Mikael Carneholm wrote: > Can anyone explain why the first one never completes, but the second > one does? (the first one just keeps running, I canceled after ~1 min) > > PG version: 8.1 final Both functions create fine here in 8.1.0 on FreeBSD 6.0/i386 and Solaris 9/sparc. What client are you using? If not psql, have you tried with psql? What platform are you on? -- Michael Fuhr
"Mikael Carneholm" <Mikael.Carneholm@WirelessCar.com> writes:
> Can anyone explain why the first one never completes, but the second
> one does?
They both work fine for me ...
regards, tom lane
Postgres machine OS: RHEL release 2.1 (Panama)
PGAdminIII client OS: WinXP Pro 2002 SP2
Clients: psql, PGAdminIII Query tool
dfol=> select version();
version
-------------------------------------------------------------
PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC 2.96
The original versions looked like this (same behaviour though):
create or replace function dfol.getcolstring (tblname varchar) returns varchar as $$
declare
table_columns text;
column_name record;
begin
-- skapa kolumnnamns-strängarna
for column_name in select pga.attname from pg_attribute pga, pg_class pgc
where pga.attrelid = pgc.relfilenode and pgc.relname = tblname and pga.attnum > 0 loop
table_columns := table_columns || column_name.attname || ',';
end loop;
-- kapa av sista kommatecknet
table_columns := substring(table_columns from 1 for length(table_columns)-1);
return table_columns;
end;
$$
language plpgsql;
create or replace function dfol.getcolstring (tblname text) returns varchar as $$
declare
table_columns text;
column_name record;
begin
-- skapa kolumnnamns-strängarna
for column_name in select pga.attname from pg_attribute pga, pg_class pgc
where pga.attrelid = pgc.relfilenode and pgc.relname = tblname and pga.attnum > 0 loop
table_columns := table_columns || column_name.attname || ',';
end loop;
-- kapa av sista kommatecknet
table_columns := substring(table_columns from 1 for length(table_columns)-1);
return table_columns;
end;
$$
language plpgsql;
-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: den 9 november 2005 19:56
To: Mikael Carneholm
Cc: 'Pgsql-General (E-mail)
Subject: Re: [GENERAL] Hanging creating of function
On Wed, Nov 09, 2005 at 07:38:53PM +0100, Mikael Carneholm wrote:
> Can anyone explain why the first one never completes, but the second
> one does? (the first one just keeps running, I canceled after ~1 min)
>
> PG version: 8.1 final
Both functions create fine here in 8.1.0 on FreeBSD 6.0/i386 and
Solaris 9/sparc. What client are you using? If not psql, have
you tried with psql? What platform are you on?
--
Michael Fuhr
On Wed, Nov 09, 2005 at 08:19:32PM +0100, Mikael Carneholm wrote: > Postgres machine OS: RHEL release 2.1 (Panama) > PGAdminIII client OS: WinXP Pro 2002 SP2 > Clients: psql, PGAdminIII Query tool The function creation hangs even with psql? Where did you run psql from -- the Windows box, the Linux box, or both? How did you get the code into psql -- cut-and-paste, typed manually, read with \i, read with -f, or redirected standard input? Could you try different ways and see if some work and some don't? How repeatable is the problem? Have you made many attempts and seen one way always fail and the other way always succeed? When the create hangs, if you query pg_locks in another session does it show any locks where granted is false? Do you see any unusual messages in the server's logs? -- Michael Fuhr
psql was run from the linux box, read using both \i and -f. The strange this is, it seems to work now...yesterday I was even thrown out by the server due to some internal error. Postgis-1.0.4 and the (pg-)admin81 module are also installed, if that has something to do with it. Could also be due to some bad memory stick or something - I don't know. Oh well, it works now, and that's what's most important for me as I'm now able to carry on with my project. /Mikael -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org] Sent: den 10 november 2005 16:46 To: Mikael Carneholm Cc: 'Pgsql-General (E-mail) Subject: Re: [GENERAL] Hanging creating of function On Wed, Nov 09, 2005 at 08:19:32PM +0100, Mikael Carneholm wrote: > Postgres machine OS: RHEL release 2.1 (Panama) > PGAdminIII client OS: WinXP Pro 2002 SP2 > Clients: psql, PGAdminIII Query tool The function creation hangs even with psql? Where did you run psql from -- the Windows box, the Linux box, or both? How did you get the code into psql -- cut-and-paste, typed manually, read with \i, read with -f, or redirected standard input? Could you try different ways and see if some work and some don't? How repeatable is the problem? Have you made many attempts and seen one way always fail and the other way always succeed? When the create hangs, if you query pg_locks in another session does it show any locks where granted is false? Do you see any unusual messages in the server's logs? -- Michael Fuhr