date:20010711


> The security issue is why I developed it.  There were complaints from people 
> who did not want to have identd running at all.
> 
> I think the feature is available in Linux, Solaris and some BSD.  It can be
> tested for by whether SO_PEERCRED is defined in sys/socket.h.

Yes, I see something similar in BSD/OS.  Manual page attached.

> 
> I don't see the need to strip mention from the comments in pg_hba.conf.  The
> situation is no different from those systems which do not have Kerberos or
> SSL available.

Yea, I guess.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026


RECV(2) BSD Programmer's Manual RECV(2)

NAME
 recv, recvfrom, recvmsg - receive a message from a socket

SYNOPSIS
 #include 
 #include 

 ssize_t
 recv(int s, void *buf, size_t len, int flags);

 ssize_t
 recvfrom(int s, void *buf, size_t len, int flags, struct sockaddr *from,
 socklen_t *fromlen);

 ssize_t
 recvmsg(int s, struct msghdr *msg, int flags);

DESCRIPTION
 The recvfrom() and recvmsg() calls are used to receive messages from a
 socket, and may be used to receive data on a socket whether or not it is
 connection-oriented.

 If from is non-null, and the socket is not connection-oriented, the
 source address of the message is filled in.  The fromlen pointer refers
 to a value-result parameter; it should initially contain the amount of
 space pointed to by from; on return that location will contain the actual
 length (in bytes) of the address returned. If the buffer provided is too
 small, the name is truncated and the full size is returned in the loca-
 tion to which fromlen points.  If from is null, the value pointed to by
 fromlen is not modified.  Otherwise, if the socket is connection-orient-
 ed, the address buffer will not be modified, and the value pointed to by
 fromlen will be set to zero.

 The recv() call is normally used only on a connected socket (see
 connect(2))  and is identical to recvfrom() with a nil from parameter.
 As it is redundant, it may not be supported in future releases.

 All three routines return the length of the message on successful comple-
 tion.  If a message is too long to fit in the supplied buffer, excess
 bytes may be discarded depending on the type of socket the message is re-
 ceived from (see socket(2)).

 If no messages are available at the socket, the receive call waits for a
 message to arrive, unless the socket is nonblocking (see fcntl(2)) in
 which case the value -1 is returned and the external variable errno set
 to EAGAIN. The receive calls normally return any data available, up to
 the requested amount, rather than waiting for receipt of the full amount
 requested; this behavior is affected by the socket-level options
 SO_RCVLOWAT and SO_RCVTIMEO described in getsockopt(2).

 The select(2) call may be used to determine when more data arrive.

 The flags argument to a recv call is formed by or'ing one or more of the
 values:

   MSG_OOB  process out-of-band data
   MSG_PEEK peek at incoming message
   MSG_WAITALL  wait for full request or error

 The MSG_OOB flag requests receipt of out-of-band data that would not be
 received in the normal data stream.  Some protocols place expedited data
 at the head of the normal data queue, and thus this flag cannot be used
 with such protocols.  The MSG_PEEK flag causes the receive operation to
 return data from the beginning of the receive queue without removing that
 data from the queue.  Thus, a subsequent receive call will return the
 same data. The MSG_WAITALL flag requests that the operation block until
 the full request is satisfied.  However, the call may still return less
 data than requested if a signal is caught, an error or disconnect occurs,
 or the next data to be received is of a different type than that re-
 turned.

 The recvmsg() call uses a msghdr structure to minimize the number of di-
 rectly supplied parameters.  This structure has the following form, as
 defined in :

 struct msghdr {
 caddr_t msg_name;  /* optional address */
 u_int   msg_namelen;/* size of address */
 struct  iovec *msg_iov; /* scatter/gather array */
 u_int   msg_iovlen; /* # elements in msg_iov */
 caddr_t msg_control;/* ancillary data, see below */
 u_int   msg_controllen; /* ancillary data buffer len */
 int msg_flags; /* flags on received message */
 };

 If msg_name is non-null, and the socket is not connection-oriented, the
 source address of

Re: [HACKERS] Possible bug in plpgsql/src/gram.y


> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Also, can someone tell my why we use malloc in plpgsql?
> 
> Plain palloc() won't do because the compiled tree for the function needs
> to outlive the current query.  However, malloc() is not cool.  Really,
> these structures ought to be built in a memory context created specially
> for each function --- then it'd be possible to reclaim the memory if the
> function is deleted or we realize we need to invalidate its compiled
> tree.
> 
> I've had this in mind to do for awhile, but haven't gotten to it.
> Do you want to put it on TODO?

Done:

* Change PL/PgSQL to use palloc() instead of malloc()

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [HACKERS] iconv?

2001-07-11 Thread Tatsuo Ishii


> Has it ever been considered to (optionally) use the iconv interface for
> character set conversion instead of rolling our own?  It seems to be a lot
> more flexible, has pluggable conversion modules (depending on the
> implementation), supports more character sets.  It seems to be available
> on quite a few systems, too.

I have not checked iconv seriously since it's not very portable among
our supported platforms.
--
Tatsuo Ishii

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [HACKERS] Possible bug in plpgsql/src/gram.y


Bruce Momjian <[EMAIL PROTECTED]> writes:
> Also, can someone tell my why we use malloc in plpgsql?

Plain palloc() won't do because the compiled tree for the function needs
to outlive the current query.  However, malloc() is not cool.  Really,
these structures ought to be built in a memory context created specially
for each function --- then it'd be possible to reclaim the memory if the
function is deleted or we realize we need to invalidate its compiled
tree.

I've had this in mind to do for awhile, but haven't gotten to it.
Do you want to put it on TODO?

regards, tom lane

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

[HACKERS] Re: Strangeness in xid allocation / snapshot setup

"Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
>> Since we are going to treat all xids >= xmax as in-progress anyway,
>> what's wrong with reading xmax before we acquire the SInval lock?

> AFAIR, I made so to prevent following:

> 1. Tx Old is running.
> 2. Tx S reads new transaction ID in GetSnapshotData() and swapped away
>before SInval acquired.
> 3. Tx New gets new transaction ID, makes changes and commits.
> 4. Tx Old changes some row R changed by Tx New and commits.
> 5. Tx S gets snapshot data and now sees R changed by *both* Tx Old and
>Tx New *but* does not see *other* changes made by Tx New =>
>Tx S reads unconsistent data.

Hmm, but that doesn't seem to have anything to do with the way that
GetSnapshotData operates.  If Tx New has an XID >= xmax read by Tx S'
GetSnapshotData, then Tx New will be considered uncommitted by S no
matter which order we get the locks in; it hardly matters whether Tx New
manages to physically commit before we finish building the snapshot for
S.  On the other side of the coin, if Tx New's XID < xmax for S, then
*with the GetNewTransactionId change that I want* we can be sure that
Tx New will be seen running by S when it does get the SInval lock
(unless New has managed to finish before S gets the lock, in which case
it's perfectly reasonable for S to treat it as committed or aborted).

Anyway, it seems to me that the possibility of inconsistent data is
inherent in the way we handle updated rows in Read Committed mode ---
you can always get to see a row that was emitted by a transaction you
don't see the other effects of.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] docs Japanese translation

2001-07-11 Thread Tatsuo Ishii


> > We (SRA) have done the translation of PostgreSQL 7.1 docs into
> > Japanese. They can be freely available at
> > http://osb.sra.co.jp/PostgreSQL/Manual/.
> 
> How long did that take, and with howmany people? :)

It took 4 months. Two of our employees worked for that plus several
people did some checkings. It was definitely lots of effort.
--
Tatsuo Ishii

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

[HACKERS] RE: Strangeness in xid allocation / snapshot setup

2001-07-11 Thread Mikheev, Vadim


> I am trying to understand why GetSnapshotData() needs to acquire the
> SInval spinlock before it calls ReadNewTransactionId, rather than after.
> I see that you made it do so --- in the commit at
>
http://www.ca.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/storage/ipc/sh
mem.c.diff?r1=1.41&r2=1.42
> but I don't understand why the loss of concurrency is "necessary".
> Since we are going to treat all xids >= xmax as in-progress anyway,
> what's wrong with reading xmax before we acquire the SInval lock?

AFAIR, I made so to prevent following:

1. Tx Old is running.
2. Tx S reads new transaction ID in GetSnapshotData() and swapped away
   before SInval acquired.
3. Tx New gets new transaction ID, makes changes and commits.
4. Tx Old changes some row R changed by Tx New and commits.
5. Tx S gets snapshot data and now sees R changed by *both* Tx Old and
   Tx New *but* does not see *other* changes made by Tx New =>
   Tx S reads unconsistent data.

-

As for issue below - I don't remember why I decided that
it's not important and will need in some time to remember.

> Also, it seems to me that in GetNewTransactionId(), it's important
> for MyProc->xid to be set before releasing XidGenLockId, not after.
> Otherwise there is a narrow window for failure:
> 
> 1. Process A calls GetNewTransactionId.  It allocates an xid of, say,
> 1234, and increments nextXid to 1235.  Just after releasing the
> XidGenLock spinlock, but before it can set its MyProc->xid, control
> swaps away from it.
> 
> 2. Process B gets to run.  It runs GetSnapshotData.  It sees nextXid =
> 1235, and it does not see xid = 1234 in any backend's proc->xid.
> Therefore, B will assume xid 1234 has already terminated, when it
> hasn't.
> 
> Isn't this broken?  The problem would be avoided if 
> GetNewTransactionId
> sets MyProc->xid before releasing the spinlock, since then after
> GetSnapshotData has called ReadNewTransactionId, we know that 
> all older
> XIDs that are still active are recorded in proc structures.
> 
> Comments?
> 
>   regards, tom lane
> 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Possible bug in plpgsql/src/gram.y



Also, can someone tell my why we use malloc in plpgsql?


> In this bit of code in src/pl/plpgsql/src/gram.y in the current CVS
> sources, curname_def is defined as PLpgSQL_expr * but it is is
> allocated the space required for a PLpgSQL_var.  This looks like a
> bug.
> 
> Ian
> 
>   | decl_varname K_CURSOR decl_cursor_args decl_is_from 
>K_SELECT decl_cursor_query
>   {
>   PLpgSQL_var *new;
>   PLpgSQL_expr *curname_def;
>   charbuf[1024];
>   char*cp1;
>   char*cp2;
> 
>   plpgsql_ns_pop();
> 
>   new = malloc(sizeof(PLpgSQL_var));
>   memset(new, 0, sizeof(PLpgSQL_var));
> 
>   curname_def = 
>malloc(sizeof(PLpgSQL_var));
>   memset(curname_def, 0, 
>sizeof(PLpgSQL_var));
> 
> ---(end of broadcast)---
> TIP 3: 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
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Possible bug in plpgsql/src/gram.y



Confirmed.  I found a second problem in the file too, very similar. 
Patch applied.

> In this bit of code in src/pl/plpgsql/src/gram.y in the current CVS
> sources, curname_def is defined as PLpgSQL_expr * but it is is
> allocated the space required for a PLpgSQL_var.  This looks like a
> bug.
> 
> Ian
> 
>   | decl_varname K_CURSOR decl_cursor_args decl_is_from 
>K_SELECT decl_cursor_query
>   {
>   PLpgSQL_var *new;
>   PLpgSQL_expr *curname_def;
>   charbuf[1024];
>   char*cp1;
>   char*cp2;
> 
>   plpgsql_ns_pop();
> 
>   new = malloc(sizeof(PLpgSQL_var));
>   memset(new, 0, sizeof(PLpgSQL_var));
> 
>   curname_def = 
>malloc(sizeof(PLpgSQL_var));
>   memset(curname_def, 0, 
>sizeof(PLpgSQL_var));
> 
> ---(end of broadcast)---
> TIP 3: 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
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026


Index: src/pl/plpgsql/src/gram.y
===
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.22
diff -c -r1.22 gram.y
*** src/pl/plpgsql/src/gram.y   2001/07/11 18:54:18 1.22
--- src/pl/plpgsql/src/gram.y   2001/07/12 01:15:05
***
*** 332,338 
{
PLpgSQL_rec *new;
  
!   new = malloc(sizeof(PLpgSQL_var));
  
new->dtype  = 
PLPGSQL_DTYPE_REC;
new->refname= $1.name;
--- 332,338 
{
PLpgSQL_rec *new;
  
!   new = malloc(sizeof(PLpgSQL_rec));
  
new->dtype  = 
PLPGSQL_DTYPE_REC;
new->refname= $1.name;
***
*** 374,381 
new = malloc(sizeof(PLpgSQL_var));
memset(new, 0, sizeof(PLpgSQL_var));
  
!   curname_def = 
malloc(sizeof(PLpgSQL_var));
!   memset(curname_def, 0, 
sizeof(PLpgSQL_var));
  
new->dtype  = 
PLPGSQL_DTYPE_VAR;
new->refname= $1.name;
--- 374,381 
new = malloc(sizeof(PLpgSQL_var));
memset(new, 0, sizeof(PLpgSQL_var));
  
!   curname_def = 
malloc(sizeof(PLpgSQL_expr));
!   memset(curname_def, 0, 
sizeof(PLpgSQL_expr));
  
new->dtype  = 
PLPGSQL_DTYPE_VAR;
new->refname= $1.name;



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

[HACKERS] Possible bug in plpgsql/src/gram.y

2001-07-11 Thread Ian Lance Taylor


In this bit of code in src/pl/plpgsql/src/gram.y in the current CVS
sources, curname_def is defined as PLpgSQL_expr * but it is is
allocated the space required for a PLpgSQL_var.  This looks like a
bug.

Ian

| decl_varname K_CURSOR decl_cursor_args decl_is_from 
K_SELECT decl_cursor_query
{
PLpgSQL_var *new;
PLpgSQL_expr *curname_def;
charbuf[1024];
char*cp1;
char*cp2;

plpgsql_ns_pop();

new = malloc(sizeof(PLpgSQL_var));
memset(new, 0, sizeof(PLpgSQL_var));

curname_def = 
malloc(sizeof(PLpgSQL_var));
memset(curname_def, 0, 
sizeof(PLpgSQL_var));

---(end of broadcast)---
TIP 3: 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: [HACKERS] Re: SOMAXCONN (was Re: Solaris source code)

2001-07-11 Thread Nathan Myers

On Wed, Jul 11, 2001 at 12:26:43PM -0400, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Tom Lane writes:
> >> Right.  Okay, it seems like just making it a hand-configurable entry
> >> in config.h.in is good enough for now.  When and if we find that
> >> that's inadequate in a real-world situation, we can improve on it...
> 
> > Would anything computed from the maximum number of allowed connections
> > make sense?
> 
> [ looks at code ... ]  Hmm, MaxBackends is indeed set before we arrive
> at the listen(), so it'd be possible to use MaxBackends to compute the
> parameter.  Offhand I would think that MaxBackends or at most
> 2*MaxBackends would be a reasonable value.
>
> Question, though: is this better than having a hardwired constant?
> The only case I can think of where it might not be is if some platform
> out there throws an error from listen() when the parameter is too large
> for it, rather than silently reducing the value to what it can handle.
> A value set in config.h.in would be simpler to adapt for such a platform.

The question is really whether you ever want a client to get a
"rejected" result from an open attempt, or whether you'd rather they 
got a report from the back end telling them they can't log in.  The 
second is more polite but a lot more expensive.  That expense might 
really matter if you have MaxBackends already running.

I doubt most clients have tested either failure case more thoroughly 
than the other (or at all), but the lower-level code is more likely 
to have been cut-and-pasted from well-tested code. :-)

Maybe PG should avoid accept()ing connections once it has MaxBackends
back ends already running (as hinted at by Ian), so that the listen()
parameter actually has some meaningful effect, and excess connections 
can be rejected more cheaply.  That might also make it easier to respond 
more adaptively to true load than we do now.

> BTW, while I'm thinking about it: why doesn't pqcomm.c test for a
> failure return from the listen() call?  Is this just an oversight,
> or is there a good reason to ignore errors?

The failure of listen() seems impossible.  In the Linux, NetBSD, and 
Solaris man pages, none of the error returns mentioned are possible 
with PG's current use of the function.  It seems as if the most that 
might be needed now would be to add a comment to the call to socket() 
noting that if any other address families are supported (besides 
AF_INET and AF_LOCAL aka AF_UNIX), the call to listen() might need to 
be looked at.  AF_INET6 (which PG will need to support someday)
doesn't seem to change matters.

Probably if listen() did fail, then one or other of bind(), accept(),
and read() would fail too.

Nathan Myers
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] vacuum problems

2001-07-11 Thread Mark



We increased shared memory in the linux kernel, which decreased the vacuumdb 
time from 40 minutes to 14 minutes on a 450 mhz processor.  We calculate that 
on our dual 1ghz box with ghz ethernet san connection this will go down to 
under 5 minutes.  This is acceptable to us.  Sorry about the unnecessary post.

On Wednesday 11 July 2001 09:16, Mark wrote:
> Quick rundown of our configuration:
> Red Hat 7.1 (no changes or extras added by us)
> Postgresql 7.1.2 and CVS HEAD from 07/10/2001
> 3.8 gb database size
>
> I included two pgsql versions because this happens on both.
>
> Here's the problem we're having:
>
> We run a vacuumdb from the server on the entire database.  Some large
> tables are vacuumed very quickly, but the vacuum process hangs or takes
> more than a few hours on a specific table (we haven't let it finish
> before).  The vacuum process works quickly on a table (loginhistory) with
> 2.8 million records, but is extremely slow on a table (inbox) with 1.1
> million records (the table with 1.1 million records is actually larger in
> kb size than the other table).
>
> We've tried to vacuum the inbox table seperately ('vacuum inbox' within
> psql), but this still takes hours (again we have never let it complete, we
> need to use the database for development as well).
>
> We noticed 2 things that are significant to this situatoin:
> The server logs the following:
>
>
> DEBUG:  --Relation msginbox--
> DEBUG:  Pages 129921: Changed 26735, reaped 85786, Empty 0, New 0; Tup
> 1129861: Vac 560327, Keep/VTL 0/0, Crash 0, UnUsed 51549, MinLen 100,
> MaxLen 2032; Re-using: Free/Avail. Space 359061488/359059332;
> EndEmpty/Avail. Pages 0/85785. CPU 11.18s/5.32u sec.
> DEBUG:  Index msginbox_pkey: Pages 4749; Tuples 1129861: Deleted 76360.
> CPU 0.47s/6.70u sec.
> DEBUG:  Index msginbox_fromto: Pages 5978; Tuples 1129861: Deleted 0.
> CPU 0.37s/6.15u sec.
> DEBUG:  Index msginbox_search: Pages 4536; Tuples 1129861: Deleted 0.
> CPU 0.32s/6.30u sec.
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
> DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
>
> the last few lines (XLogWrite .) repeat for ever and ever and ever. 
> With 7.1.2 this never stops unless we run out of disk space or cancel the
> query. With CVS HEAD this still continues, but the log files don't consume
> all disk space, but we still have to cancel it or it might run forever.
>
> Perhaps we need to let it run until it completes, but we thought that we
> might be doing something wrong or have some data (we're converting data
> from MS SQL Server) that isn't friendly.
>
> The major issue we're facing with this is that any read or write access to
> the table being vacuumed times out (obviously because the table is still
> locked).  We plan to use PostgreSQL in our production service, but we can't
> until we get this resolved.
>
> We're at a loss, not being familiar enough with PostgreSQL and it's source
> code.  Can anyone please offer some advice or suggestions?
>
> Thanks,
>
> Mark
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PATCHES] Re: [HACKERS] [PATCH] Re: Setuid functions

2001-07-11 Thread Mark Volpe


Good point. Would the issue be resolved by either:

- Only allowing the database superuser to use this mechanism?
- Allowing it only in trigger functions? (That way a user has to actually own
one of the tables)

Mark

Peter Eisentraut wrote:
> 
> Bruce Momjian writes:
> 
> > > Peter might be referring to this:
> > >
> > > http://fts.postgresql.org/db/mw/msg.html?mid=1022775
> > >
> > > There was some discussion afterward, but I don't think a definite conclusion
> > > was reached.
> >
> > But I see Tom Lane saying he doesn't see a security issue:
> >
> >   http://fts.postgresql.org/db/mw/msg.html?mid=1022758
> >
> > I don't pretend to understand it.  Just tell me what to do with the
> > patch.  :-)
> 
> The problem with setuid functions in general is that a database user can
> effectively re-grant privileges to which he has no grant privileges.
> E.g.,
> 
> user1=> create table table1 (id int, secret_content text);
> user1=> grant select on test to user2;
> 
> /* made up the syntax */
> user2=> create function testfunc (int) returns text as '
> user2'>   begin
> user2'> set authorization definer;
> user2'> return select secret_content from table1 where id = $1;
> user2'>   end;' as 'plpgsql';
> 
> user3=> select * from table1 where id = 5;
> (fails)
> user3=> select testfunc(5);
> (succeeds)
> 
> Tom has a point that as soon as user2 has the select privilege, he can
> make a private copy of table1 and send it to user3.
> 
> But if you take this attitude you might as well get rid of the
> fine-grained privilege system, you'd just need 'select to public'.  Also,
> there may be other security or at least auditing mechanisms to supervise
> the communication between user2 and user3.  Or maybe user2 and user3 are
> just pseudo-users implementing some sort of "least privilege" paranoid
> design.
> 
> At least we should discuss whether we'd eventually like to have grantable
> privileges, and if so, how this would fit in.
> 
> --
> Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PATCHES] Re: [HACKERS] [PATCH] Re: Setuid functions

Bruce Momjian writes:

> > Peter might be referring to this:
> >
> > http://fts.postgresql.org/db/mw/msg.html?mid=1022775
> >
> > There was some discussion afterward, but I don't think a definite conclusion
> > was reached.
>
> But I see Tom Lane saying he doesn't see a security issue:
>
>   http://fts.postgresql.org/db/mw/msg.html?mid=1022758
>
> I don't pretend to understand it.  Just tell me what to do with the
> patch.  :-)

The problem with setuid functions in general is that a database user can
effectively re-grant privileges to which he has no grant privileges.
E.g.,

user1=> create table table1 (id int, secret_content text);
user1=> grant select on test to user2;

/* made up the syntax */
user2=> create function testfunc (int) returns text as '
user2'>   begin
user2'> set authorization definer;
user2'> return select secret_content from table1 where id = $1;
user2'>   end;' as 'plpgsql';

user3=> select * from table1 where id = 5;
(fails)
user3=> select testfunc(5);
(succeeds)

Tom has a point that as soon as user2 has the select privilege, he can
make a private copy of table1 and send it to user3.

But if you take this attitude you might as well get rid of the
fine-grained privilege system, you'd just need 'select to public'.  Also,
there may be other security or at least auditing mechanisms to supervise
the communication between user2 and user3.  Or maybe user2 and user3 are
just pseudo-users implementing some sort of "least privilege" paranoid
design.

At least we should discuss whether we'd eventually like to have grantable
privileges, and if so, how this would fit in.

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter

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

Re: [HACKERS] Re: Encrypting pg_shadow passwords

2001-07-11 Thread Nathan Myers

On Wed, Jul 11, 2001 at 01:24:53PM +1000, Michael Samuel wrote:
> The crypt authentication currently used offers _no_ security. ...
> Of course, SSL *if done correctly with certificate verification* is the
> correct fix.  If no certificate verification is done, you fall victim to
> a man-in-the-middle attack.

It seems worth noting here that you don't have to depend on
SSL authentication; PG can do its own authentication over SSL
and avoid the man-in-the-middle attack that way.  

Of course, PG would have to do its authentication properly, e.g. 
with the HMAC method.  That seems better than depending on SSL 
authentication, because SSL certification seems to be universally
misconfigured.

Nathan Myers
[EMAIL PROTECTED]

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

Re: [HACKERS] I can't keep up


> I must say, I am having more trouble keeping up with the email traffic. 
> It is taking days just to catch up to current emails because some of the
> emails need special attention.  I am going as fast as I can.  I think I
> have one more week of emails to go through before I am current. 
> Hopefully today...

I deal with one email and 10 more show up!  I know I am not helping by
sending out emails myself.  I guess this is how we progress so quickly.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 3: 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

[HACKERS] Strangeness in xid allocation / snapshot setup

Hi Vadim,

I am trying to understand why GetSnapshotData() needs to acquire the
SInval spinlock before it calls ReadNewTransactionId, rather than after.
I see that you made it do so --- in the commit at
http://www.ca.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/storage/ipc/shmem.c.diff?r1=1.41&r2=1.42
but I don't understand why the loss of concurrency is "necessary".
Since we are going to treat all xids >= xmax as in-progress anyway,
what's wrong with reading xmax before we acquire the SInval lock?

Also, it seems to me that in GetNewTransactionId(), it's important
for MyProc->xid to be set before releasing XidGenLockId, not after.
Otherwise there is a narrow window for failure:

1. Process A calls GetNewTransactionId. It allocates an xid of, say,
1234, and increments nextXid to 1235. Just after releasing the
XidGenLock spinlock, but before it can set its MyProc->xid, control
swaps away from it.

2. Process B gets to run. It runs GetSnapshotData. It sees nextXid =
1235, and it does not see xid = 1234 in any backend's proc->xid.
Therefore, B will assume xid 1234 has already terminated, when it
hasn't.

Isn't this broken? The problem would be avoided if GetNewTransactionId
sets MyProc->xid before releasing the spinlock, since then after
GetSnapshotData has called ReadNewTransactionId, we know that all older
XIDs that are still active are recorded in proc structures.

Comments?

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[HACKERS] I can't keep up


I must say, I am having more trouble keeping up with the email traffic. 
It is taking days just to catch up to current emails because some of the
emails need special attention.  I am going as fast as I can.  I think I
have one more week of emails to go through before I am current. 
Hopefully today...

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] LOG PgSql ..


gabriel writes:

> Is there a way to config the postmaster
> to log in a file all connections and querys to each database?

http://www.de.postgresql.org/users-lounge/docs/7.1/postgres/runtime-config.html#LOGGING

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[HACKERS] iconv?


Has it ever been considered to (optionally) use the iconv interface for
character set conversion instead of rolling our own?  It seems to be a lot
more flexible, has pluggable conversion modules (depending on the
implementation), supports more character sets.  It seems to be available
on quite a few systems, too.

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter


---(end of broadcast)---
TIP 3: 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: [JDBC] Re: [HACKERS] JDBC adaptor issue


> Actually the problem is worse than I thought.  Not only do all the 
> get/setXXXStream methods assume the datatype is a BLOB, but also the 
> get/setBytes methods.  This means that it isn't possible to support 
> bytea as the binary datatype without also breaking some backward 
> compatability.
> 
> In looking at the CVS log, it appears that the stream methods were only 
> introduced in the 7.1 JDBC driver, since 7.1 has only been out 
> (production) a few months, the number of people affected will be 
> smaller, the setBytes() method that assumed a blob was there in 7.0, so 
> it is likely more people will be impacted by any change there.

If you are looking for votes, you can break backward compatibility here.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [HACKERS] Re: SOMAXCONN (was Re: Solaris source code)


Tom Lane writes:

> The other concern I had could be addressed by making the listen
> parameter be MIN(MaxBackends, PG_SOMAXCONN) where PG_SOMAXCONN
> is set in config.h --- but now we could make the default value
> really large, say 1.  The only reason to change it would be
> if you had a kernel that barfed on large listen() parameters.

We'll never find that out if we don't try it.  If you're concerned about
cooperating with other listen()ing processes, set it to MaxBackends * 2,
if you're not, set it to INT_MAX and watch.

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[HACKERS] LOG PgSql ..

2001-07-11 Thread gabriel



hello all.
Is there a way to config the postmaster
to log in a file all connections and querys to each database?

thanks...

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] Re: [GENERAL] Re: Multiple Indexing, performance impact


> At 05:56 PM 22-06-2001 -0400, Bruce Momjian wrote:
> >> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >> Since 64 is already too much to let 7.1 fit in SHMMAX = 1MB, I think
> >> the original rationale for using 64 is looking pretty broken anyway.
> >> Comments?
> >
> >BSD/OS has a 4MB max but we document how to increase it by recompiling
> >the kernel.  Maybe if we fail the startup we can tell them how to
> >decrease the buffers in postgresql.conf file.  Seems quite clear.
> >
> 
> Why is SHMMAX so low on some O/Ses? What are the advantages?
> 
> My guess is it's a minimum vs median/popular situation. Get the same thing
> looking at the default www.kernel.org linux kernel settings vs the Redhat
> kernel settings.
> 
> I'd personally prefer the popular situation. But would that mean the
> minimum case can't even boot up to recompile? Maybe the BSD guys should
> ship with two kernels then. FreeBSD esp, since it's easy to recompile the
> kernel, just do two, during installation default to "Regular", with an
> option for "Tiny".
> 
> It's more fair that the people trying the extraordinary (16MB 386) should
> be the ones doing the extra work.

I think the problem is that with a default-sized kernel, the little guys
couldn't even boot the OS.  Also, some of the OS's hard-wire things into
the kernel for performance reasons.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Re: SOMAXCONN (was Re: Solaris source code)


> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Don't we have maxbackends configurable at runtime.
> 
> Not after postmaster start, so passing it to the initial listen()
> shouldn't be a problem.
> 
> The other concern I had could be addressed by making the listen
> parameter be MIN(MaxBackends, PG_SOMAXCONN) where PG_SOMAXCONN
> is set in config.h --- but now we could make the default value
> really large, say 1.  The only reason to change it would be
> if you had a kernel that barfed on large listen() parameters.

Sounds good to me.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

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

Re: [HACKERS] Re: Encrypting pg_shadow passwords


> That is not true.  The internet happily allows for active attacks.  In
> fact, active attacks are easier on the internet than passive ones.
> 
> My concern is, that by having something that we proclaim to be secure, we
> need for it to really be secure.
> 
> An HMAC would be a better alternative to the current crypt scheme, as
> it would provide integrity, without the overhead of having privacy.
> 
> Of course, HMAC would require the postgres protocol to talk in "packets",
> as it can't accept the data as being valid until it verifies the MAC. I'm
> not familiar with the protocol yet.
> 
> I suggest these authentication options:
> 
> * password - The current meaning of password, but with passwords hashed
>   using md5crypt() or something. (The usual crypt unneccessarily limits
>   passwords to 8 characters)

Once I do crypting of pg_shadow/double-crypt for 7.2, we don't need
password anymore.  It is around only for very old clients and for
secondary password files but wWe will not need that workaround with
double-crypt.

> * HMAC - Wrap all postgres data in an HMAC (I believe this requires an
>   plaintext-like password on the server as does crypt and the double
>   crypt scheme)

No, double-crypt has the passwords stored encrypted.

> * Public Key (RSA/DSA) - Use public key cryptography to negotiate a
>   connection. (When I'm not busy, I may decide to do this myself)

SSL?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

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

Re: [HACKERS] Re: SOMAXCONN (was Re: Solaris source code)


Bruce Momjian <[EMAIL PROTECTED]> writes:
> Don't we have maxbackends configurable at runtime.

Not after postmaster start, so passing it to the initial listen()
shouldn't be a problem.

The other concern I had could be addressed by making the listen
parameter be MIN(MaxBackends, PG_SOMAXCONN) where PG_SOMAXCONN
is set in config.h --- but now we could make the default value
really large, say 1.  The only reason to change it would be
if you had a kernel that barfed on large listen() parameters.

Have we beat this issue to death yet, or is it still twitching?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Re: Encrypting pg_shadow passwords


> Also, I think we should add to the client API the ability to only accept
> certain authentication schemes, to avoid active attacks tricking your
> software from sending the HMAC password in cleartext.

This is an interesting point.  We have kept 'password' authentication
around for secondary password files and for very old clients, but now
see that having it around can be a security problem because you can ask
the client to send you cleartext passwords.

Comments?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Re: SOMAXCONN (was Re: Solaris source code)


> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Tom Lane writes:
> >> Right.  Okay, it seems like just making it a hand-configurable entry
> >> in config.h.in is good enough for now.  When and if we find that
> >> that's inadequate in a real-world situation, we can improve on it...
> 
> > Would anything computed from the maximum number of allowed connections
> > make sense?
> 
> [ looks at code ... ]  Hmm, MaxBackends is indeed set before we arrive
> at the listen(), so it'd be possible to use MaxBackends to compute the
> parameter.  Offhand I would think that MaxBackends or at most
> 2*MaxBackends would be a reasonable value.

Don't we have maxbackends configurable at runtime.  If so, any constant
we put in config.h will be inaccurate.  Seems we have to track
maxbackends.


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

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

Re: [HACKERS] Re: SOMAXCONN (was Re: Solaris source code)

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Tom Lane writes:
>> Right.  Okay, it seems like just making it a hand-configurable entry
>> in config.h.in is good enough for now.  When and if we find that
>> that's inadequate in a real-world situation, we can improve on it...

> Would anything computed from the maximum number of allowed connections
> make sense?

[ looks at code ... ]  Hmm, MaxBackends is indeed set before we arrive
at the listen(), so it'd be possible to use MaxBackends to compute the
parameter.  Offhand I would think that MaxBackends or at most
2*MaxBackends would be a reasonable value.

Question, though: is this better than having a hardwired constant?
The only case I can think of where it might not be is if some platform
out there throws an error from listen() when the parameter is too large
for it, rather than silently reducing the value to what it can handle.
A value set in config.h.in would be simpler to adapt for such a platform.

BTW, while I'm thinking about it: why doesn't pqcomm.c test for a
failure return from the listen() call?  Is this just an oversight,
or is there a good reason to ignore errors?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] varchar vs. text

2001-07-11 Thread Jan Wieck


Ross J. Reedstrom wrote:
> On Wed, Jul 11, 2001 at 09:56:27AM -0400, Jan Wieck wrote:
> > Rachit Siamwalla wrote:
> > >
> > > Is there any good reason to use VARCHAR over TEXT for a string field? ie.
> > > performance hits, etc.
> > >
> > > Other than running into the row size limit problem, are there any large
> > > storage / performance penalties of using TEXT for virtually all strings?
> > 
> > Er  -  what kind of "row size limit"? I remember vaguely that
> > there was something the like in ancient releases, but  forgot
> > the specific restrictions.
> 
> 
> 
> Very good Jan. Yes, PostgreSQL certainly develops on Internet time, and
> while TOAST may seem ancient news to you, it was only in the 7.1 release
> (2001-04-13). Three months is a little early to start the 'Problem? What
> problem?' campaign. Especially since some of the client libs (OBDC)
> just caught up, last week. :-)

You're absotulely right, including the whistling ;-)

I just couldn't resist, was too temping. And since I was sure
there'll be more informative responses either way, why not?


Jan

-- 

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #


_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Re: SOMAXCONN (was Re: Solaris source code)


Tom Lane writes:

> Right.  Okay, it seems like just making it a hand-configurable entry
> in config.h.in is good enough for now.  When and if we find that
> that's inadequate in a real-world situation, we can improve on it...

Would anything computed from the maximum number of allowed connections
make sense?

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] vacuum problems

2001-07-11 Thread Mark



Quick rundown of our configuration:
Red Hat 7.1 (no changes or extras added by us)
Postgresql 7.1.2 and CVS HEAD from 07/10/2001
3.8 gb database size

I included two pgsql versions because this happens on both.

Here's the problem we're having:

We run a vacuumdb from the server on the entire database.  Some large tables 
are vacuumed very quickly, but the vacuum process hangs or takes more than a 
few hours on a specific table (we haven't let it finish before).  The vacuum 
process works quickly on a table (loginhistory) with 2.8 million records, but 
is extremely slow on a table (inbox) with 1.1 million records (the table with 
1.1 million records is actually larger in kb size than the other table).

We've tried to vacuum the inbox table seperately ('vacuum inbox' within 
psql), but this still takes hours (again we have never let it complete, we 
need to use the database for development as well).

We noticed 2 things that are significant to this situatoin:
The server logs the following:


DEBUG:  --Relation msginbox--
DEBUG:  Pages 129921: Changed 26735, reaped 85786, Empty 0, New 0; Tup 
1129861: Vac 560327, Keep/VTL 0/0, Crash 0, UnUsed 51549, MinLen 100,
MaxLen 2032; Re-using: Free/Avail. Space 359061488/359059332;
EndEmpty/Avail. Pages 0/85785. CPU 11.18s/5.32u sec.
DEBUG:  Index msginbox_pkey: Pages 4749; Tuples 1129861: Deleted 76360.
CPU 0.47s/6.70u sec.
DEBUG:  Index msginbox_fromto: Pages 5978; Tuples 1129861: Deleted 0.
CPU 0.37s/6.15u sec.
DEBUG:  Index msginbox_search: Pages 4536; Tuples 1129861: Deleted 0.
CPU 0.32s/6.30u sec.
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG:  XLogWrite: new log file created - consider increasing WAL_FILES

the last few lines (XLogWrite .) repeat for ever and ever and ever.  With 
7.1.2 this never stops unless we run out of disk space or cancel the query.  
With CVS HEAD this still continues, but the log files don't consume all disk 
space, but we still have to cancel it or it might run forever.

Perhaps we need to let it run until it completes, but we thought that we 
might be doing something wrong or have some data (we're converting data from 
MS SQL Server) that isn't friendly.

The major issue we're facing with this is that any read or write access to 
the table being vacuumed times out (obviously because the table is still 
locked).  We plan to use PostgreSQL in our production service, but we can't 
until we get this resolved.

We're at a loss, not being familiar enough with PostgreSQL and it's source 
code.  Can anyone please offer some advice or suggestions?

Thanks,

Mark

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Re: SOMAXCONN (was Re: Solaris source code)


Ian Lance Taylor <[EMAIL PROTECTED]> writes:
> But I wouldn't worry about it, and I wouldn't worry about Nathan's
> suggestion for making the limit configurable, because Postgres
> connections don't spend time on the queue.  The postgres server will
> be picking them off as fast as it can.  If the server can't pick
> processes off fast enough, then your system has other problems;

Right.  Okay, it seems like just making it a hand-configurable entry
in config.h.in is good enough for now.  When and if we find that
that's inadequate in a real-world situation, we can improve on it...

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Possible feature?


Mike Mascari writes:

> MESSAGE ON INDEX i_employees IS
> 'An employee with a matching Social Security number already exists';
>
> Then, when the UNIQUE constraint of the index is violated, instead of
> the message:
>
> 'Cannot insert a duplicate key into a unique index i_test1'
>
> the client application would receive:
>
> 'An employee with a matching Social Security number already exists'

I think what you're after is

TRY
  BEGIN
INSERT ...
  END
CATCH SQLCODE 12345 -- made up
  BEGIN
RAISE 'your message here'
  END

I'm positive people would kill for that kind of feature.

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] varchar vs. text

2001-07-11 Thread Rod Taylor


Still can't index those large toasted items -- not that I want to.
One interesting aspect is versioning of text documents where you want
them to be UNIQUE in regards to book development otherwise you have
the same document with 2 or more entries (more than a single version
number).  Poor example;  I know.
--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

- Original Message -
From: "Ross J. Reedstrom" <[EMAIL PROTECTED]>
To: "Jan Wieck" <[EMAIL PROTECTED]>
Cc: "Rachit Siamwalla" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Wednesday, July 11, 2001 10:56 AM
Subject: Re: [HACKERS] varchar vs. text


> On Wed, Jul 11, 2001 at 09:56:27AM -0400, Jan Wieck wrote:
> > Rachit Siamwalla wrote:
> > >
> > > Is there any good reason to use VARCHAR over TEXT for a string
field? ie.
> > > performance hits, etc.
> > >
> > > Other than running into the row size limit problem, are there
any large
> > > storage / performance penalties of using TEXT for virtually all
strings?
> >
> > Er  -  what kind of "row size limit"? I remember vaguely that
> > there was something the like in ancient releases, but  forgot
> > the specific restrictions.
>
> 
>
> Very good Jan. Yes, PostgreSQL certainly develops on Internet time,
and
> while TOAST may seem ancient news to you, it was only in the 7.1
release
> (2001-04-13). Three months is a little early to start the 'Problem?
What
> problem?' campaign. Especially since some of the client libs (OBDC)
> just caught up, last week. :-)
>
> What Jan is so innocently not saying is described here:
>
> http://www.ca.postgresql.org/projects/devel-toast.html
>
> Jan not only solved the 'row size limit', he did it in a more
general
> way, solving lots of the follow on problems that come from putting
large
> fields into a table. Details at the above URL.
>
> Ross
>
> ---(end of
broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] varchar vs. text

2001-07-11 Thread Ross J. Reedstrom

On Wed, Jul 11, 2001 at 09:56:27AM -0400, Jan Wieck wrote:
> Rachit Siamwalla wrote:
> >
> > Is there any good reason to use VARCHAR over TEXT for a string field? ie.
> > performance hits, etc.
> >
> > Other than running into the row size limit problem, are there any large
> > storage / performance penalties of using TEXT for virtually all strings?
> 
> Er  -  what kind of "row size limit"? I remember vaguely that
> there was something the like in ancient releases, but  forgot
> the specific restrictions.

Very good Jan. Yes, PostgreSQL certainly develops on Internet time, and
while TOAST may seem ancient news to you, it was only in the 7.1 release
(2001-04-13). Three months is a little early to start the 'Problem? What
problem?' campaign. Especially since some of the client libs (OBDC)
just caught up, last week. :-)

What Jan is so innocently not saying is described here:

http://www.ca.postgresql.org/projects/devel-toast.html

Jan not only solved the 'row size limit', he did it in a more general
way, solving lots of the follow on problems that come from putting large
fields into a table. Details at the above URL.

Ross

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] libpq autoconf scripts?