Обсуждение: SQL functions - bug?

I realized the following facts using SQL language functions:

There is a function "function_y(...)" which returns int4; a table z and
two functions:

CREATE FUNCTION function_x1() RETURNS int4 AS '
select function_y(any_of_fields_of_table_z) from z;
' LANGUAGE 'SQL';

This calls function_y(...) only with the first row of the query output of
the select statement. Instead of this,

CREATE FUNCTION function_x2() RETURNS int4 AS '
select function_y(z_field_any) from z;
select 1;
' LANGUAGE 'SQL';

works properly (the important thing for me is to call function_y with
all rows of the select query). So, the second workaround is OK, but in my
opinion function_x1() also should call function_y(...) for as many rows as
exist in the output. Is this a bug?

I am using 7.0 (8th May).

Regards,
Zoltan

Hello,

I know this is off topic but I thought you might like to know that
the following book explains how to compile, install and secure
postgresql.

It can be purchased from http://www.linuxports.com/ (just click on the
books) and 20% goes back to the OpenSource Documentation Fund.


THe Original Announcement:


This email is to announce the new OpenDocs Publication:
Securing and Optimizing Linux: RedHat Edition
Including with the book are the Linux Central CD's of:
RedHat 6.2RedHat 6.2 Powertools
The retail price for the book is 49.95 but if you preorder it
no the price is only 39.95!. And of course since it is an
OpenDocs Publication a percentage of the Gross Profits go
to the Open Source Documentation Fund.

Below is an abbreviated Table of Contents:

Linux Installation
Descriptions of programs packages we must uninstall for securities reasons
Descriptions of programs that must be uninstalled after installation of the server
Linux General Security
Linux General Optimization
Linux Kernel
Linux TCP/IP Network Management
Linux IPCHAINS
Linux Masquerading and Forwarding
Linux Compiler functionality
Linux sXid
Linux Logcheck
Linux PortSentry
Linux OpenSSH Client/Server
Linux SSH2 Client/Server
Linux Tripwire 2.2.1
Linux Tripwire ASR 1.3.1
Linux GnuPG
Set Quota on your Linux system
Linux DNS and BIND Server    
Linux Sendmail Server (includes 8.10.1)
Linux IMAP & POP Server
Enable IMAP or POP via the tcp-wrappers inetd super server
Linux OPENSSL Server
Linux FreeS/WAN VPN
Linux OpenLDAP Server
Linux PostgreSQL Database Server
Linux Squid Proxy Server
Linux MM - Shared Memory Library for Apache
Linux Apache Web Server
Linux Webalizer
Linux FAQ-O-Matic
Linux Webmail IMP
Linux Samba Server
Linux FTP Server
Linux Backup and Restore
Tweaks, Tips and Administration tasks
Obtaining Requests for Comments (RFCs)

Thanks!

books@opendocspublishing.com


-- 
--
<COMPANY>CommandPrompt    - http://www.commandprompt.com    </COMPANY>
<PROJECT>OpenDocs, LLC.    - http://www.opendocs.org    </PROJECT>
<PROJECT>LinuxPorts     - http://www.linuxports.com     </PROJECT>
<WEBMASTER>LDP        - http://www.linuxdoc.org    </WEBMASTER>
--
Instead of asking why a piece of software is using "1970s technology," 
start asking why software is ignoring 30 years of accumulated wisdom. 
--

Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu> writes:
> There is a function "function_y(...)" which returns int4; a table z and
> two functions:

> CREATE FUNCTION function_x1() RETURNS int4 AS '
> select function_y(any_of_fields_of_table_z) from z;
> ' LANGUAGE 'SQL';

> This calls function_y(...) only with the first row of the query output of
> the select statement. Instead of this,

> CREATE FUNCTION function_x2() RETURNS int4 AS '
> select function_y(z_field_any) from z;
> select 1;
> ' LANGUAGE 'SQL';

> works properly (the important thing for me is to call function_y with
> all rows of the select query). So, the second workaround is OK, but in my
> opinion function_x1() also should call function_y(...) for as many rows as
> exist in the output. Is this a bug?

The only bug I could see in function_x1() is that perhaps the system
should raise an error if the final select of the function tries to yield
more than one tuple, rather than just stopping its evaluation after one
tuple.  (In effect, there's an implicit LIMIT 1 on that select.)

You've declared a function returning int4; that is to say, *one* int4
per call.  No more.  The behavior you are after requires a different
declaration:

regression=# CREATE FUNCTION function_x1() RETURNS SETOF int4 AS '
regression'# select f1 from int4_tbl;
regression'# ' LANGUAGE 'SQL';
CREATE
regression=# select function_x1(); ?column?
-------------          0     123456    -123456 2147483647-2147483647
(5 rows)

Functions returning sets have a lot of restrictions on them, some of
which you will no doubt find out the hard way :-(.  But the basic
feature works.
        regards, tom lane

Hi people.

For a few days, i've been experiencing some problems.
There's a cron-scheduled vacuum, performed every night.
But, on the morning, my DBs can't be accessed. Error
message says "DB busy, too many connected" (about so).
And, ps ax|grep post shows a lot of backends, waiting
for an unlock that never comes.
I'm using RH 5.2, with glibc 2.1.2, and gcc 2.95.2.
This problem appeared after upgrading to Pg 7.0 RC1
(i'm now using Pg 7.0.1, but am still experiencing the
same trouble). I've compiled the source tarball:
[PostgreSQL 7.0.1 on i686-pc-linux-gnu, compiled by gcc 2.95.2]
(Kernel is 2.2.15)

To have more information, I've finally modified my
cron, adding the verbose parameter. So I've got this:
0 4 * * * psql greffe -c 'vacuum verbose analyze'

The output I got this morning through email can be found
at the end of this email. It shows something wrong happened.

Does anyone have a hint to help me find the origin of a
problem I appear to be the only one to experience on this
mailing-list ?

TIA.
Regards,
Fabrice Scemama


Subject:        Cron <root@ximmo> psql greffe -c 'vacuum verbose analyze'  Date:        5 Jun 2000 03:00:01 -0000
From:       root@ximmo.ftd.fr (Cron Daemon)    To:        gesnet@ximmo.ftd.fr
 




NOTICE:  --Relation pg_type--
NOTICE:  Pages 4: Changed 0, reaped 2, Empty 0, New 0; Tup 212: Vac 10,
Keep/VTL 1/0,
Crash 0, UnUsed 0, MinLen 105, MaxLen 109; Re-using: Free/Avail. Space
8440/400;
EndEmpty/Avail. Pages 0/1. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_type_typname_index: Pages 4; Tuples 212: Deleted 10.
CPU
0.00s/0.00u sec.
NOTICE:  Index pg_type_oid_index: Pages 2; Tuples 212: Deleted 10. CPU
0.00s/0.00u
sec.
NOTICE:  Rel pg_type: Pages: 4 --> 3; Tuple(s) moved: 1. CPU 0.00s/0.00u
sec.
NOTICE:  Index pg_type_typname_index: Pages 4; Tuples 212: Deleted 1.
CPU 0.00s/0.01u
sec.
NOTICE:  Index pg_type_oid_index: Pages 2; Tuples 212: Deleted 1. CPU
0.00s/0.00u
sec.
NOTICE:  --Relation pg_attribute--
NOTICE:  Pages 78: Changed 2, reaped 3, Empty 0, New 0; Tup 5856: Vac
156, Keep/VTL
16/0, Crash 0, UnUsed 0, MinLen 98, MaxLen 98; Re-using: Free/Avail.
Space
15816/15816; EndEmpty/Avail. Pages 0/3. CPU 0.00s/0.04u sec.
NOTICE:  Index pg_attribute_relid_attnum_index: Pages 31; Tuples 5856:
Deleted 156.
CPU 0.00s/0.01u sec.
NOTICE:  Index pg_attribute_relid_attnam_index: Pages 72; Tuples 5856:
Deleted 156.
CPU 0.00s/0.01u sec.
NOTICE:  Rel pg_attribute: Pages: 78 --> 76; Tuple(s) moved: 16. CPU
0.00s/0.00u sec.
NOTICE:  Index pg_attribute_relid_attnum_index: Pages 31; Tuples 5856:
Deleted 16.
CPU 0.01s/0.00u sec.
NOTICE:  Index pg_attribute_relid_attnam_index: Pages 72; Tuples 5856:
Deleted 16.
CPU 0.01s/0.00u sec.
NOTICE:  --Relation pg_proc--
NOTICE:  Pages 26: Changed 0, reaped 0, Empty 0, New 0; Tup 1083: Vac 0,
Keep/VTL
0/0, Crash 0, UnUsed 0, MinLen 177, MaxLen 229; Re-using: Free/Avail.
Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_proc_proname_narg_type_index: Pages 24; Tuples 1083.
CPU
0.00s/0.00u sec.
NOTICE:  Index pg_proc_oid_index: Pages 6; Tuples 1083. CPU 0.00s/0.01u
sec.
NOTICE:  --Relation pg_class--
NOTICE:  Pages 3: Changed 0, reaped 1, Empty 0, New 0; Tup 184: Vac 10,
Keep/VTL 1/0,
Crash 0, UnUsed 14, MinLen 106, MaxLen 144; Re-using: Free/Avail. Space
2996/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_class_relname_index: Pages 4; Tuples 184: Deleted 10.
CPU
0.00s/0.00u sec.
NOTICE:  Index pg_class_oid_index: Pages 2; Tuples 184: Deleted 10. CPU
0.00s/0.00u
sec.
NOTICE:  --Relation pg_indexes--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_group--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_group_sysid_index: Pages 2; Tuples 0. CPU 0.00s/0.00u
sec.
NOTICE:  Index pg_group_name_index: Pages 2; Tuples 0. CPU 0.00s/0.00u
sec.
NOTICE:  --Relation pg_database--
NOTICE:  Pages 1: Changed 0, reaped 1, Empty 0, New 0; Tup 9: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 1, MinLen 81, MaxLen 91; Re-using: Free/Avail. Space
7376/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_attrdef--
NOTICE:  Pages 3: Changed 0, reaped 0, Empty 0, New 0; Tup 67: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 159, MaxLen 672; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_attrdef_adrelid_index: Pages 2; Tuples 67. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_relcheck--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_relcheck_rcrelid_index: Pages 2; Tuples 0. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_trigger--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 1: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 152, MaxLen 152; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_trigger_tgrelid_index: Pages 2; Tuples 1. CPU
0.00s/0.00u sec.
NOTICE:  Index pg_trigger_tgconstrrelid_index: Pages 2; Tuples 1. CPU
0.00s/0.00u
sec.
NOTICE:  Index pg_trigger_tgconstrname_index: Pages 2; Tuples 1. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_inherits--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_inherits_relid_seqno_index: Pages 2; Tuples 0. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_index--
NOTICE:  Pages 2: Changed 0, reaped 0, Empty 0, New 0; Tup 61: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 164, MaxLen 164; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_index_indexrelid_index: Pages 2; Tuples 61. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_statistic--
NOTICE:  Pages 88: Changed 48, reaped 43, Empty 0, New 0; Tup 4380: Vac
4008,
Keep/VTL 94/0, Crash 0, UnUsed 17, MinLen 64, MaxLen 868; Re-using:
Free/Avail. Space
325144/325144; EndEmpty/Avail. Pages 0/43. CPU 0.01s/0.00u sec.
NOTICE:  Index pg_statistic_relid_att_index: Pages 42; Tuples 4380:
Deleted 4008. CPU
0.00s/0.06u sec.
NOTICE:  Rel pg_statistic: Pages: 88 --> 46; Tuple(s) moved: 3999. CPU
0.02s/0.09u
sec.
NOTICE:  Index pg_statistic_relid_att_index: Pages 42; Tuples 4380:
Deleted 3999. CPU
0.00s/0.05u sec.
NOTICE:  --Relation pg_operator--
NOTICE:  Pages 9: Changed 0, reaped 0, Empty 0, New 0; Tup 559: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 116, MaxLen 116; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_operator_oprname_l_r_k_index: Pages 8; Tuples 559. CPU
0.00s/0.00u
sec.
NOTICE:  Index pg_operator_oid_index: Pages 4; Tuples 559. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_opclass--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 30: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 68, MaxLen 68; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_opclass_name_index: Pages 2; Tuples 30. CPU
0.00s/0.00u sec.
NOTICE:  Index pg_opclass_deftype_index: Pages 2; Tuples 30. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_am--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 4: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 148, MaxLen 148; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_am_name_index: Pages 2; Tuples 4. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_amop--
NOTICE:  Pages 2: Changed 0, reaped 0, Empty 0, New 0; Tup 182: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 46, MaxLen 46; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_amop_strategy_index: Pages 2; Tuples 182. CPU
0.00s/0.00u sec.
NOTICE:  Index pg_amop_opid_index: Pages 2; Tuples 182. CPU 0.00s/0.00u
sec.
NOTICE:  --Relation pg_amproc--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 51: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 46, MaxLen 46; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_language--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 3: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 79, MaxLen 84; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_language_oid_index: Pages 2; Tuples 3. CPU 0.00s/0.00u
sec.
NOTICE:  Index pg_language_name_index: Pages 2; Tuples 3. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_aggregate--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 45: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 100, MaxLen 107; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_aggregate_name_type_index: Pages 2; Tuples 45. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_ipl--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_inheritproc--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_rewrite--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 5: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 903, MaxLen 1236; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_rewrite_rulename_index: Pages 2; Tuples 5. CPU
0.00s/0.00u sec.
NOTICE:  Index pg_rewrite_oid_index: Pages 2; Tuples 5. CPU 0.00s/0.00u
sec.
NOTICE:  --Relation pg_listener--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_listener_relname_pid_index: Pages 2; Tuples 0. CPU
0.00s/0.00u sec.
NOTICE:  --Relation pg_description--
NOTICE:  Pages 9: Changed 0, reaped 0, Empty 0, New 0; Tup 1103: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 42, MaxLen 110; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_description_objoid_index: Pages 6; Tuples 1103. CPU
0.01s/0.00u
sec.
NOTICE:  --Relation pg_shadow--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 5: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 76, MaxLen 76; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_user--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_rules--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_views--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation pg_tables--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation photos--
NOTICE:  Pages 550: Changed 0, reaped 3, Empty 0, New 0; Tup 25062: Vac
48, Keep/VTL
0/0, Crash 0, UnUsed 0, MinLen 108, MaxLen 216; Re-using: Free/Avail.
Space
13132/6432; EndEmpty/Avail. Pages 0/2. CPU 0.04s/0.37u sec.
NOTICE:  Index photos_idx3: Pages 170; Tuples 25062: Deleted 48. CPU
0.01s/0.03u sec.
NOTICE:  Index photos_idx5: Pages 75; Tuples 25062: Deleted 48. CPU
0.01s/0.01u sec.
NOTICE:  Index photos_idx4: Pages 52; Tuples 25062: Deleted 48. CPU
0.01s/0.02u sec.
NOTICE:  Index photos_idx6: Pages 61; Tuples 25062: Deleted 48. CPU
0.00s/0.02u sec.
NOTICE:  Index photos_idx2: Pages 63; Tuples 25062: Deleted 48. CPU
0.00s/0.04u sec.
NOTICE:  Rel photos: Pages: 550 --> 549; Tuple(s) moved: 15. CPU
0.04s/0.00u sec.
NOTICE:  Index photos_idx3: Pages 170; Tuples 25062: Deleted 15. CPU
0.01s/0.02u sec.
NOTICE:  Index photos_idx5: Pages 75; Tuples 25062: Deleted 15. CPU
0.00s/0.03u sec.
NOTICE:  Index photos_idx4: Pages 52; Tuples 25062: Deleted 15. CPU
0.01s/0.02u sec.
NOTICE:  Index photos_idx6: Pages 61; Tuples 25062: Deleted 15. CPU
0.00s/0.03u sec.
NOTICE:  Index photos_idx2: Pages 63; Tuples 25062: Deleted 15. CPU
0.00s/0.03u sec.
NOTICE:  --Relation i__rules--
NOTICE:  Pages 2: Changed 0, reaped 0, Empty 0, New 0; Tup 80: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 108, MaxLen 377; Re-using: Free/Avail. Space
0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index i__rules_pkey: Pages 2; Tuples 80. CPU 0.00s/0.01u sec.
NOTICE:  --Relation annonces--
NOTICE:  Pages 4794: Changed 0, reaped 2473, Empty 0, New 0; Tup 92283:
Vac 512,
Keep/VTL 0/0, Crash 0, UnUsed 7885, MinLen 188, MaxLen 904; Re-using:
Free/Avail.
Space 450872/330448; EndEmpty/Avail. Pages 0/476. CPU 0.24s/5.23u sec.
NOTICE:  Index annonces_idx3: Pages 855; Tuples 92283: Deleted 512. CPU
0.07s/0.14u
sec.
NOTICE:  Index annonces_idx2: Pages 1383; Tuples 92283: Deleted 512. CPU
0.18s/0.22u
sec.
NOTICE:  Index annonces_idx: Pages 441; Tuples 92283: Deleted 512. CPU
0.03s/0.20u
sec.
NOTICE:  Index annonces_pkey: Pages 605; Tuples 92283: Deleted 512. CPU
0.08s/0.22u
sec.
NOTICE:  Rel annonces: Pages: 4794 --> 4765; Tuple(s) moved: 402. CPU
0.61s/0.18u
sec.
NOTICE:  Index annonces_idx3: Pages 855; Tuples 92283: Deleted 402. CPU
0.08s/0.08u
sec.
NOTICE:  Index annonces_idx2: Pages 1383; Tuples 92283: Deleted 402. CPU
0.07s/0.13u
sec.
NOTICE:  Index annonces_idx: Pages 441; Tuples 92283: Deleted 402. CPU
0.01s/0.11u
sec.
NOTICE:  Index annonces_pkey: Pages 606; Tuples 92283: Deleted 402. CPU
0.05s/0.08u
sec.
NOTICE:  --Relation photos_totem--
NOTICE:  Pages 197: Changed 0, reaped 0, Empty 0, New 0; Tup 15307: Vac
0, Keep/VTL
0/0, Crash 0, UnUsed 0, MinLen 96, MaxLen 100; Re-using: Free/Avail.
Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.01s/0.07u sec.
NOTICE:  Index photos_totem_idx2: Pages 55; Tuples 15307. CPU
0.02s/0.01u sec.
NOTICE:  Index photos_totem_idx: Pages 102; Tuples 15307. CPU
0.01s/0.00u sec.
NOTICE:  Index photos_totem_pkey: Pages 33; Tuples 15307. CPU
0.00s/0.02u sec.
NOTICE:  --Relation photos_bannies--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0,
Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index photos_bannies_idx: Pages 2; Tuples 0. CPU 0.00s/0.00u
sec.
NOTICE:  Index photos_bannies_pkey: Pages 2; Tuples 0. CPU 0.00s/0.00u
sec.
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is
impossible.
Terminating.

Fabrice Scemama <fabrices@ximmo.ftd.fr> writes:
> [ vacuum appears to be coredumping ]

That's odd ... not so much that vacuum could be failing, which is
probably a garden-variety bug; but it sounds like the postmaster is
failing to do the system restart that it should do after one of the
backends fails.  Is there anything showing up in the postmaster log
when this happens?

As for tracking the immediate vacuum problem, the failed backend
should have left a core dump file in the database directory
(.../data/base/DBNAME/core).  Can you get a backtrace from that
with gdb?  Something likegdb path/to/postgres path/to/corebtquit
should do.
        regards, tom lane

No core dump could be found within the data/base/*
directories. The cron is executed by user root,
but on my system root is a PostgreSQL superuser.

Tom Lane wrote:
> 
> Fabrice Scemama <fabrices@ximmo.ftd.fr> writes:
> > [ vacuum appears to be coredumping ]
> 
> That's odd ... not so much that vacuum could be failing, which is
> probably a garden-variety bug; but it sounds like the postmaster is
> failing to do the system restart that it should do after one of the
> backends fails.  Is there anything showing up in the postmaster log
> when this happens?
> 
> As for tracking the immediate vacuum problem, the failed backend
> should have left a core dump file in the database directory
> (.../data/base/DBNAME/core).  Can you get a backtrace from that
> with gdb?  Something like
>         gdb path/to/postgres path/to/core
>         bt
>         quit
> should do.
> 
>                         regards, tom lane

Fabrice Scemama <fabrices@ximmo.ftd.fr> writes:
> No core dump could be found within the data/base/* directories.

Hm.  When I've seen a backend crash without leaving a core file,
it's usually because the backend ran up against the system's
per-process memory limit.  (Many kernels seem to choose not to
drop a core file when they kill a process for memory exhaustion.)
You could check that theory by watching the process's memory usage
with "top".

Anything in the postmaster log?
        regards, tom lane

On Mon, 5 Jun 2000, Fabrice Scemama wrote:

> No core dump could be found within the data/base/*
> directories. The cron is executed by user root,
> but on my system root is a PostgreSQL superuser.

Well, the problem here might be in the fact that root usually has its
ulimit -c (the maximum size of core files created) set to 0.  This
effectively disables core dumping.  You can check this by issuing
ulimit -c

as root.  Setting it to a reasonably large size via
ulimit -c 1000000

just before the vacuumdb command should resolve it.  Running postmaster as
root is a dangerous idea from a security point of view, but it's well
documented so you probably know what you are doing ;-)

Orbis

-- 
Rostislav Opocensky <orbis@pictus.org> <orbis@unreal.cz> +420 411 825144
Unreal Technology sro., Dobrin 118, 41301 Roudnice n. L. +420 411 825111