Undeliverable Message

To: ISMTP(at)SFRA0046@Servers[<pgsql-general-digest(at)hub(dot)org>]
Cc:
Subject: pgsql-general-digest V1 #263

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

VNM3043: DE_VOLDER Fabrice(at)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 Wednesday, March 24 1999 Volume 01 : Number 263

Index:

COPY with default values won't work?
Re: [GENERAL] COPY with default values won't work?
Re: [GENERAL] COPY with default values won't work?
Re: [GENERAL] COPY with default values won't work?
Re: [GENERAL] COPY with default values won't work?
Re: [GENERAL] COPY with default values won't work?
Re: [GENERAL] COPY with default values won't work?
Re: [GENERAL] COPY with default values won't work?
[none]
PAM module for Postgres?
Re: [GENERAL] COPY with default values won't work?
Re: [GENERAL] COPY with default values won't work?
Re: [INTERFACES] Problem using Having in a sub-query wit the Count function.
Re: [INTERFACES] Problem using Having in a sub-query wit the Count function.
MkLinux & ODBC compile error
JDBC driver?

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

Date: Wed, 24 Mar 1999 01:15:37 -0400
From: Charles Tassell <ctassell(at)isn(dot)net>
Subject: COPY with default values won't work?

I'm trying to copy data into the following table:

CREATE SEQUENCE seq_account_type_ndx;

CREATE TABLE accounts (
Account_Type_NDX int4 not null default
nextval('seq_account_type_ndx'),
Account_Name Text
);

Using this as a datafile:
\N|Box
\N|NetSurfer120
\N|eMailer
\N|eMailerLite

I've tried writing the code in C using libpq, using the copy command as the
postgres super user, or using \copy as my normal user. NONE will work with
the "not null" in there, and if I remove it, it just inserts a null value
into account_type_ndx, without using the default. I've also tried
switching the default to a number (ie default 12) instead of the nextval of
the sequence, with no better luck.

Here is the copy command I tend to use:
COPY accounts from stdin USING delimiters '|'
or \COPY accounts from '/tmp/datafile.txt' USING delimiters '|'

Any ideas?

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

Date: Wed, 24 Mar 1999 12:24:22 +0700
From: Vadim Mikheev <vadim(at)krs(dot)ru>
Subject: Re: [GENERAL] COPY with default values won't work?

This is standard behaviour. DEFAULT is for INSERT only,
when attribute is not specified in INSERT' target list.

Vadim

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

Date: Wed, 24 Mar 1999 01:48:22 -0400
From: Charles Tassell <ctassell(at)isn(dot)net>
Subject: Re: [GENERAL] COPY with default values won't work?

@#$#!! Any way to make COPY use default, or shove a lot of data in with a
single INSERT query? According to older messages in the mailing list, it
is *possible*, but I can't get it to work.

My problem is that I might be using this to put a few thousand entries in
the db every night, and when I last attempted this using a few thousand
insert statements, it was awfully slow (on the order of taking HOURS)

BTW: Thanks for the quick response. I send the mail, answer some messages,
and get a response. Quicker than a Microsoft $0.95/min help line. :-)

At 01:24 AM 3/24/99, you wrote:
>This is standard behaviour. DEFAULT is for INSERT only,
>when attribute is not specified in INSERT' target list.
>
>Vadim

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

Date: Wed, 24 Mar 1999 14:31:14 +0700
From: Vadim Mikheev <vadim(at)krs(dot)ru>
Subject: Re: [GENERAL] COPY with default values won't work?

Charles Tassell wrote:
>
> @#$#!! Any way to make COPY use default, or shove a lot of data in with a
> single INSERT query? According to older messages in the mailing list, it
> is *possible*, but I can't get it to work.
>
> My problem is that I might be using this to put a few thousand entries in
> the db every night, and when I last attempted this using a few thousand
> insert statements, it was awfully slow (on the order of taking HOURS)

@#$#!!
- -:)

Use BEGIN/END to insert a few thousand rowes in SINGLE transaction.

Vadim

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

Date: Wed, 24 Mar 1999 03:05:46 -0500
From: "K.T." <kanet(at)calmarconsulting(dot)com>
Subject: Re: [GENERAL] COPY with default values won't work?

Or instead of eating tons of memory inserting all those record in one
transaction (you might want to commit every hundred or so...), copy then
issue an update to set the default values.
- -----Original Message-----
From: Charles Tassell <ctassell(at)isn(dot)net>
To: Vadim Mikheev <vadim(at)krs(dot)ru>
Cc: pgsql-general(at)postgreSQL(dot)org <pgsql-general(at)postgreSQL(dot)org>
Date: Wednesday, March 24, 1999 12:45 AM
Subject: Re: [GENERAL] COPY with default values won't work?

>@#$#!! Any way to make COPY use default, or shove a lot of data in with a
>single INSERT query? According to older messages in the mailing list, it
>is *possible*, but I can't get it to work.
>
>My problem is that I might be using this to put a few thousand entries in
>the db every night, and when I last attempted this using a few thousand
>insert statements, it was awfully slow (on the order of taking HOURS)
>
>BTW: Thanks for the quick response. I send the mail, answer some messages,
>and get a response. Quicker than a Microsoft $0.95/min help line. :-)
>
>At 01:24 AM 3/24/99, you wrote:
>>This is standard behaviour. DEFAULT is for INSERT only,
>>when attribute is not specified in INSERT' target list.
>>
>>Vadim
>
>

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

Date: Wed, 24 Mar 1999 15:04:45 +0700
From: Vadim Mikheev <vadim(at)krs(dot)ru>
Subject: Re: [GENERAL] COPY with default values won't work?

"K.T." wrote:
>
> Or instead of eating tons of memory inserting all those record in one
^^^^^^^^^^^^^^^^^^^^^
I think this was fixed ~ 1.5-2 years ago...

> transaction (you might want to commit every hundred or so...), copy then
> issue an update to set the default values.

Oh, no. Remember that Postgres is non-overwriting storage system.

Vadim

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

Date: Wed, 24 Mar 1999 12:23:24 +0300 (MSK)
From: Oleg Broytmann <phd(at)sun(dot)med(dot)ru>
Subject: Re: [GENERAL] COPY with default values won't work?

Hello!

On Wed, 24 Mar 1999, Charles Tassell wrote:
> My problem is that I might be using this to put a few thousand entries in
> the db every night, and when I last attempted this using a few thousand
> insert statements, it was awfully slow (on the order of taking HOURS)

DROP INDEX
BEGIN WORK
COPY .......
END
CREATE INDEX

Oleg.
- ----
Oleg Broytmann http://members.xoom.com/phd2/ phd2(at)earthling(dot)net
Programmers don't die, they just GOSUB without RETURN.

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

Date: Wed, 24 Mar 1999 12:25:16 +0300 (MSK)
From: Oleg Broytmann <phd(at)sun(dot)med(dot)ru>
Subject: Re: [GENERAL] COPY with default values won't work?

On Wed, 24 Mar 1999, Vadim Mikheev wrote:
> > Or instead of eating tons of memory inserting all those record in one
> I think this was fixed ~ 1.5-2 years ago...

I have the same problem in 6.4.2. I splitted COPY into small chunks
(about 500 rows) to overcome this.
I beleive Jan fixed this in 6.5-beta a month ago.

> Vadim
>

Oleg.
- ----
Oleg Broytmann http://members.xoom.com/phd2/ phd2(at)earthling(dot)net
Programmers don't die, they just GOSUB without RETURN.

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

Date: Wed, 24 Mar 1999 12:37:46 +0200 (EET)
From: Ivan Obuhov <ivan(at)cris(dot)crimea(dot)ua>
Subject: [none]

subscribe
end

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

Date: Wed, 24 Mar 1999 02:41:48 -0800
From: Matthew Hixson <hixson(at)frozenwave(dot)com>
Subject: PAM module for Postgres?

Hello,
I'm searching for a PAM module that is capable of authenticating users
against information contained in a PostgreSQL database. Does something like
this exist arleady? This is to be used on a Linux system.
Any info would be greatly appreciated.
Thanks,
-M@

- --
Matthew Hixson - CIO
FroZenWave Communications
http://www.frozenwave.com

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

Date: Wed, 24 Mar 1999 07:08:34 -0500 (EST)
From: "Brett W. McCoy" <bmccoy(at)lan2wan(dot)com>
Subject: Re: [GENERAL] COPY with default values won't work?

On Wed, 24 Mar 1999, Charles Tassell wrote:

> @#$#!! Any way to make COPY use default, or shove a lot of data in with a
> single INSERT query? According to older messages in the mailing list, it
> is *possible*, but I can't get it to work.
>
> My problem is that I might be using this to put a few thousand entries in
> the db every night, and when I last attempted this using a few thousand
> insert statements, it was awfully slow (on the order of taking HOURS)

You could write a perl script to read the data column by column frm the
text file and insert it into the databse using the Pg module.

Brett W. McCoy
http://www.lan2wan.com/~bmccoy/
- -----------------------------------------------------------------------
Quantum Mechanics is God's version of "Trust me."

- -----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GAT dpu s:-- a C++++ UL++++$ P+ L+++ E W++ N+ o K- w--- O@ M@ !V PS+++
PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ G++ e>++ h+(---) r++ y++++
- ------END GEEK CODE BLOCK------

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

Date: Wed, 24 Mar 1999 15:14:39 +0200
From: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
Subject: Re: [GENERAL] COPY with default values won't work?

At 07:15 +0200 on 24/03/1999, Charles Tassell wrote:

>
> I'm trying to copy data into the following table:
>
> CREATE SEQUENCE seq_account_type_ndx;
>
> CREATE TABLE accounts (
> Account_Type_NDX int4 not null default
> nextval('seq_account_type_ndx'),
> Account_Name Text
> );
>
> Using this as a datafile:
> \N|Box
> \N|NetSurfer120
> \N|eMailer
> \N|eMailerLite
>
> I've tried writing the code in C using libpq, using the copy command as the
> postgres super user, or using \copy as my normal user. NONE will work with
> the "not null" in there, and if I remove it, it just inserts a null value
> into account_type_ndx, without using the default. I've also tried
> switching the default to a number (ie default 12) instead of the nextval of
> the sequence, with no better luck.
>
> Here is the copy command I tend to use:
> COPY accounts from stdin USING delimiters '|'
> or \COPY accounts from '/tmp/datafile.txt' USING delimiters '|'
>
> Any ideas?

I thought the above would work, too, but apparently it doesn't.

So, two possible solutions:

A) Update with the sequence after you have copied.

1) Create the table without the NOT NULL.
2) Make the copy
3) Use
UPDATE accounts
SET Account_Type_NDX 4) Vacuum.

B) Copy into a separate table and insert.

1) Create the table, including the NOT NULL and everything.
2) Create a temporary table, with all the same fields, without NOT NULL.
3) Copy into the temporary table.
4) Use:
INSERT INTO accounts ( Account_Name )
SELECT Account_Name FROM temp_accounts;
5) Drop the temp_accounts table.

Variation: Create the temp_accounts table without the Account_Type_NDX
field. It's null anyway. Have your copy files without the "\N|" part.
Saves the transfer of three bytes per row and the insertion of a null
value per row. Makes things a wee bit faster.

My personal favourite is plan (B), because it allows building the table
with the "NOT NULL" constraint, and does not require you to remember the
name of the sequence. The general principle here is:

1) Look at your table and decide which fields should be inserted from
an external data source, and which from an internal data source
(these are usually the fields that have a default value).

2) Create a temporary table that contains only the fields that need to
be fed externally.

3) Copy your data into that table. The copy files need not have any
NULL value unless it truely stands for "no value here".

4) Insert into your real table using a SELECT statement. The INSERT
clause should include only the names of "external source" fields.
This will cause the internal ones to be filled from the default
source.

This method allows also the use of functions and stuff when populating the
table.

Herouth

- --
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

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

Date: Wed, 24 Mar 1999 14:25:06 +0100
From: Ordini <sferac(at)bo(dot)nettuno(dot)it>
Subject: Re: [INTERFACES] Problem using Having in a sub-query wit the Count function.

bug: HAVING IN SUBQUERIES
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This is a known bug.
Jose'

> > We are trying to execute a query that has several sub-queries embedded
> > in it. Below is a snippet of the sql code.
> >
> > "Select ordnum from ordinace where dept> > (Select ordnum from squareview where square> > ordnum from keywordview where keyword> > Gathering' group by ordnum having count(ordnum)
> >
> > The two tables in the sub-queries (squareview and keywordview) or
> > views created between two tables.
> > There are roughly about 20000 records in the keywordview view.
> >
> > When we execute the query, failing at the keywordview subquery, saying
> > the aggregate function in the having clause must appear on the right
> > side. (?) When we take the having clause out, and strictly have the
> > group by, it takes 30secs to 3mins. to return with the valid
> > recordset.
> >
> > The funny thing is, as a stand alone query on it's own, the
> > keywordview query works fine. It's very quick and has no problem with
> > the having clause.
> >
> > I was wondering if anyone else has either had this problme using
> > aggregate functions with the having clause in a subquery, or could
> > anyone give me any information of successfully executing something
> > similar to this.
> >
> > Any infomation would be appreciated.
> >
> > Thanks in advance,
> >
> > Steve
> > steve(at)ctlno(dot)com

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

Date: Wed, 24 Mar 1999 09:54:49 -0500
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: [INTERFACES] Problem using Having in a sub-query wit the Count function.

Matthew <matt(at)ctlno(dot)com> writes:
>> "Select ordnum from ordinace where dept>> (Select ordnum from squareview where square>> ordnum from keywordview where keyword>> Gathering' group by ordnum having count(ordnum)

I wonder whether the parser could be getting confused by the multiple
distinct uses of the same name "ordnum" in this query? In other words,
maybe you'd have better luck if the inner queries read something like

select
k.ordnum from keywordview k where k.keywordGathering' group by k.ordnum having count(k.ordnum)

Without that, it might be thinking that count(ordnum) refers to the
ordnum in the outer select.

If that is it, it's probably a bug, but I'm not sure what the standard
says about how to interpret ambiguous names in this context...

regards, tom lane

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

Date: Wed, 24 Mar 1999 16:50:55 -0500
From: Mark <mlundquist(at)bvsdps(dot)com>
Subject: MkLinux & ODBC compile error

I have successfully compiled & run PostgreSQL on MkLinux many times. I
added the --with-odbc arguement and now I get an error when I compile.

misc.c:100: request for member `overflow_arg_area' in something not a structure
or union
misc.c:111: warning: passing arg 3 of `vfprintf' from incompatible pointer type
misc.c:94: warning: `args' might be used uninitialized in this function
misc.c:100: warning: `__ptr' might be used uninitialized in this function
gmake[2]: *** [misc.o] Error 1
gmake[2]: Leaving directory
`/usr/src/pgsql/postgresql-6.4.2/src/interfaces/odbc
'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/usr/src/pgsql/postgresql-6.4.2/src/interfaces'
gmake: *** [all] Error 2

Has anyone seen this and can help me (or anyone even reading this)?

MkLinux DR3 (RedHat Linux 5.1)
PostgreSQL 6.4.2

- -Mark

- -----------------------------------------------------------------
Mark Lundquist Bell Atlantic Video Services
mlundquist(at)bvsdps(dot)com 1880 Campus Commons Drive
Reston, VA 20191
- -----------------------------------------------------------------

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

Date: Wed, 24 Mar 1999 14:35:48 -0800
From: "Lynn Gabbay" <lynn(at)mitv(dot)mit(dot)edu>
Subject: JDBC driver?

hi,
is there a native JDBC driver for postgresql?

- -l

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

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