Undeliverable Message - Mailing list pgsql-general

To:            ISMTP@SFRA0046@Servers[<pgsql-general-digest@hub.org>]
Cc:
Subject:       pgsql-general-digest V1 #241

Message not delivered to recipients below.  Press F1 for help with VNM
error codes.

    VNM3043:  DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST



VNM3043 -- MAILBOX IS FULL

   The message cannot be delivered because the
   recipient's mailbox contains the maximum number of
   messages, as set by the system administrator.  The
   recipient must delete some messages before any
   other messages can be delivered.
    The maximum message limit for a user's mailbox is
   10,000.  The default message limit is 1000 messages.
   Administrators can set message limits using the
   Mailbox  Settings function available in the
   Manage User menu  (MUSER).

   When a user's mailbox reaches the limit, the
   user must delete some of the messages before
   the mailbox can accept any more incoming messages.

----------------------  Original Message Follows  ----------------------


pgsql-general-digest      Tuesday, March 2 1999      Volume 01 : Number 241



Index:

Indexes created when insert blobs--problems!
NEWBIE: How do I use \copy to populate from a flat file
FW: indexing functions
Re: [GENERAL] NEWBIE: How do I use \copy to populate from a flat file
Re: [GENERAL] NEWBIE: How do I use \copy to populate from a flat file
Converting SQL.Server Stored Procedures..
A security problem (newbie)
Re: [SQL] FW: indexing functions
Re: [GENERAL] NEWBIE: How do I use \copy to populate from a flat file
Broken Pipe?
A security problem (newbie)
selects on datetime

----------------------------------------------------------------------

Date: Tue, 2 Mar 1999 16:56:18 +0800 (CST)
From: Nick Urbanik <nicku@vtc.edu.hk>
Subject: Indexes created when insert blobs--problems!

Dear folks,
we have a strange problem.  When pg_dump'ing our database with photographs
inserted as binary large objects, we found that many indexes were unable
to be dumped; the error message complained that the particular index was
not associated with any table, and pg_dump gave up.

Now are these indexes are not created by postgres 6.3.2, but they are with
6.4.2.  The index name starts with xinx followed by the oid of the BLOB.
Is this normal?  Why does pg_dump fail?  Please CC to both of my email
addresses as well as to the list.


Nick Urbanik, Dept. of Electrical & Communications Engineering
Hong Kong Institute of Vocational Education (Tsing Yi)
email: nicku@vtc.edu.hk, nicku@iohk.com
Tel:   (852) 2436 8660, (825) 2436 8674   Fax: (852) 2436 8643

------------------------------

Date: Tue, 02 Mar 1999 11:15:19 +0000
From: Brian Burton-Cundall <lists@graith.co.uk>
Subject: NEWBIE: How do I use \copy to populate from a flat file

I've used mSQL before and managed to import flat files exported from MS Access
(needs must) into that. I need to import flat files into PostGreSQL. I've
created the table using psql - POSTGRESQL interactive sql monitor:

I can see the command from the help
 \copy table {from | to} <fname>

I've got a flat file with TAB separated values that match the table but when I
use
\copy ENTRY from /u1/www/intranet/brian/ENTRY.txt

I get
Broken Pipe

and it returns to the shell prompt. Any ideas?

(I don't want to have to create 20,000+ INSERT  SQL commands or even automate
it)

Thanks
Brian
Graith Internet - http://www.graith.co.uk

------------------------------

Date: Tue, 2 Mar 1999 12:36:07 +0100
From: Bas Peters <bpeters@idc.nl>
Subject: FW: indexing functions

I posted this message a while ago, but did not receive any response, so
I give it another try. Any pointer would help.

> Is it possible to manipulate the way an index is created by using
> functions? I would like to store SGML data in text fields that include
> a lot of entities (like ü). To enable the user to search the
> ü character as u I would like to index those entities as the
> character without the diacritical marks. Is this possible?
>
> Thanks in advance,
>
> Bas Peters
> bpeters@idc.nl

------------------------------

Date: Tue, 2 Mar 1999 12:14:43 GMT
From: "Michael A. Koerber SR" <mak@ll.mit.edu>
Subject: Re: [GENERAL] NEWBIE: How do I use \copy to populate from a flat file

try adding "tics" and see if it works

\copy ENTRY from '/u1/www/intranet/brian/ENTRY.txt'

------------------------------

Date: Tue, 2 Mar 1999 08:29:40 -0500
From: "K.T." <kanet@calmarconsulting.com>
Subject: Re: [GENERAL] NEWBIE: How do I use \copy to populate from a flat file

I also think the ticks that Michael is talking about are required.  You can
usually find more information about the "copy" error in the server.log
(don't know where this is located on your system).

- -----Original Message-----
From: Brian Burton-Cundall <lists@graith.co.uk>
To: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Date: Tuesday, March 02, 1999 6:09 AM
Subject: [GENERAL] NEWBIE: How do I use \copy to populate from a flat file


>I've used mSQL before and managed to import flat files exported from MS
Access
>(needs must) into that. I need to import flat files into PostGreSQL. I've
>created the table using psql - POSTGRESQL interactive sql monitor:
>
>I can see the command from the help
> \copy table {from | to} <fname>
>
>I've got a flat file with TAB separated values that match the table but
when I
>use
>\copy ENTRY from /u1/www/intranet/brian/ENTRY.txt
>
>I get
>Broken Pipe
>
>and it returns to the shell prompt. Any ideas?
>
>(I don't want to have to create 20,000+ INSERT  SQL commands or even
automate
>it)
>
>Thanks
>Brian
>Graith Internet - http://www.graith.co.uk
>
>

------------------------------

Date: Tue, 2 Mar 1999 14:50:50 -0300
From: "Luiz Lins" <luigi@vecom.com>
Subject: Converting SQL.Server Stored Procedures..

Hi,

    I am PostGree new user and I'm setting a new system under it. I have
several Stored Procedures wich was written for Microsoft's SQL.Server.... Is
conversion possible? How do I do this?


Thanx in advance.


Luiz Lins
luigi@vecom.com

------------------------------

Date: Tue, 02 Mar 1999 18:35:22 +0000
From: Paulo da Silva <pdasilva@esoterica.pt>
Subject: A security problem (newbie)

This is a multi-part message in MIME format.
- --------------AEF67D87F27077B330AD1787
Content-Type: text/plain; charsetContent-Transfer-Encoding: 7bit

Hello,

I need to grant access to some records of ts table to
user psergio.

I created 2 views. psergio should be allowed to use select
only on one of them. But I removed access to the whole table.
So, although I grant access to va, it is denied because the
ts table is not acessible!

How can I turn around this?

TIA
- --
Paulo (pdasilva@esoterica.pt)
- --------------AEF67D87F27077B330AD1787
Content-Type: text/plain; charset nameContent-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename
CREATE TABLE ts (
    dep        text,
    descr        text
);

INSERT INTO ts VALUES ('A','aaaaaa');
INSERT INTO ts VALUES ('B','bbbbbb');
INSERT INTO ts VALUES ('B','xxxxxx');
INSERT INTO ts VALUES ('A','yyyyyy');

create view va as select * from ts where depcreate view vb as select * from ts where dep
revoke all on ts from psergio;
revoke all on va from psergio;
revoke all on vb from psergio;

grant select on va to psergio;

select * from va;
- -- Denied because ts is not acessible!!!

select * from vb;

drop table ts;
drop view va;
drop view vb;

- --------------AEF67D87F27077B330AD1787--

------------------------------

Date: Tue, 02 Mar 1999 13:36:50 -0500
From: "Gene Selkov Jr." <selkovjr@mcs.anl.gov>
Subject: Re: [SQL] FW: indexing functions

> > Is it possible to manipulate the way an index is created by using
> > functions?

Yes, it is possible:

\h create index
Command: create index
Description: construct an index
Syntax:
        CREATE [UNIQUE] INDEX indexname ON class_name [USING access_method]
( attr1 [type_class1], ...attrN | funcname(attr1, ...) [type_class] );
                                  ^^^^^^^^^^^^^^^^^^^^

You are responsible for providing the function in any acceptable form
(the only one I am familiar with is a c-coded shared object)

> > I would like to store SGML data in text fields that include
> > a lot of entities (like ü). To enable the user to search the
> > ü character as u I would like to index those entities as the
> > character without the diacritical marks. Is this possible?

Not without the transformation you apply before indexing. It could be
a user-defined function called from the CREATE INDEX instruction
above, or any filter applied externally before the data are loaded to
postgres.

If the data you are talking about are just words with character
entities, it would be a reasonable investment to write a user-defined
function. If the goal is to be able to run queries related to the
structure of the SGML documents, these will have to be split into a
number of tables representing their structure (assuming all records
adhere to the same model), and at this point, character filters can
also be applied. The records themselves will have to be stored as
blobs in a postgres database or as individual files outside.

- --Gene

------------------------------

Date: Tue, 02 Mar 1999 14:00:21 -0500
From: "Gene Selkov Jr." <selkovjr@mcs.anl.gov>
Subject: Re: [GENERAL] NEWBIE: How do I use \copy to populate from a flat file

> I also think the ticks that Michael is talking about are required.  You can
> usually find more information about the "copy" error in the server.log
> (don't know where this is located on your system).

The file name must be enclosed in single closing quotes ('\047'). And
by the way, psql (if built with readline library) allows emacs-style
command line editing and can complete file names like bash does (I am
also wondering if it's possible to improve it to complete SGL keywords
and table names). I always use completion to make sure the file exists
and is accessible:

copy ENTRY from '/u1
<TAB>w
copy ENTRY from '/u1/w
<TAB>
copy ENTRY from '/u1/www/
<TAB><TAB> to see choices
....

';<CR>

BTW, what did you mean by '\copy'?


- --Gene

------------------------------

Date: Tue, 2 Mar 1999 12:38:54 -0800
From: Chucka@freedomsystems.com
Subject: Broken Pipe?

After an upgrade to redhat 5.2 from 5.0 ,when i start psql i get the message "Broken Pipe"?

What does this mean?

chuck

------------------------------

Date: Tue, 02 Mar 1999 21:56:22 +0000
From: Paulo da Silva <pdasilva@esoterica.pt>
Subject: A security problem (newbie)

This is a multi-part message in MIME format.
- --------------633E91C2587B0EF91CA212AC
Content-Type: text/plain; charsetContent-Transfer-Encoding: 7bit

Hello,

I'm sorry if this is the 2nd post. I'm not sure if
it was sent the 1st time.

I need to grant access to some records of ts table to
user psergio.

I created 2 views. psergio should be allowed to use select
only on one of them. But I removed access to the whole table.
So, although I grant access to va, it is denied because the
ts table is not acessible!

How can I turn around this?

TIA
- --
Paulo (pdasilva@esoterica.pt)
- --------------633E91C2587B0EF91CA212AC
Content-Type: text/plain; charset nameContent-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename
CREATE TABLE ts (
    dep        text,
    descr        text
);

INSERT INTO ts VALUES ('A','aaaaaa');
INSERT INTO ts VALUES ('B','bbbbbb');
INSERT INTO ts VALUES ('B','xxxxxx');
INSERT INTO ts VALUES ('A','yyyyyy');

create view va as select * from ts where depcreate view vb as select * from ts where dep
revoke all on ts from psergio;
revoke all on va from psergio;
revoke all on vb from psergio;

grant select on va to psergio;

select * from va;
- -- Denied because ts is not acessible!!!

select * from vb;

drop table ts;
drop view va;
drop view vb;


- --------------633E91C2587B0EF91CA212AC--

------------------------------

Date: Tue, 2 Mar 1999 20:53:09 -0500
From: Scot Brady <ScotB@fine.com>
Subject: selects on datetime

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

- ------_Content-Type: text/plain;
    charset
Hello all,

I have a table w/ a field of type datetime. I would like to do something
like:
select count(jobs) for every day of a particular month and year.

my current select statement is:
Select count(job_id) from jobs where date_part('month',sub_dt)*nd
date_part('year',sub_dt)99 and date_
part('day',sub_dt)
I then execute the statement filling the placeholder with days 1..31

There has to be a better way to do this since the select has to go through
every row in the table. Any ideas?

thanks,
scot b.

- ------_Content-Type: text/html;
    charset
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV<META NAME<TITLE>selects on datetime</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE.llo all,</FONT>
</P>

<P><FONT SIZE*ve a table w/ a field of type datetime. I would like to do something like:</FONT>
<BR><FONT SIZE.lect count(jobs) for every day of a particular month and year.</FONT>
</P>

<P><FONT SIZE,urrent select statement is:</FONT>
<BR><FONT SIZE.lect count(job_id) from jobs where date_part('month',sub_dt)*nd date_part('year',sub_dt)99 and
date_</FONT>
<BR><FONT SIZE*rt('day',sub_dt)</P>

<P><FONT SIZE.n execute the statement filling the placeholder with days 1..31</FONT>
</P>

<P><FONT SIZE.re has to be a better way to do this since the select has to go through every row in the table. Any
ideas?</FONT>
</P>

<P><FONT SIZE*nks,</FONT>
<BR><FONT SIZE,ot b.</FONT>
</P>

</BODY>
</HTML>
- ------_
------------------------------

End of pgsql-general-digest V1 #241
***********************************