Hi, I'm using DBD::Pg version 0.98 with Postgres 7.1.  I'm noticing
that quite often on an error, the $dbh->errstr method doesn't return
the full error.  For example, if I have a table with a unique key
constraint:

CREATE TABLE urls (
  url_id SERIAL PRIMARY KEY,
  msg_id integer NOT NULL REFERENCES msg_info(msg_id),
  url_link varchar(255) NOT NULL default ''
);

and I do this insert:

INSERT INTO urls (msg_id,url_link) VALUES (9,'http://www.kcilink.com/');

the second time I insert it, I get this on the psql command line:

ERROR:  Cannot insert a duplicate key into unique index urls_id_link

However, if I use a perl module to do it, like this:

  my $sth = $dbh->prepare('INSERT INTO urls (msg_id,url_link) VALUES (?,?)');
  if ($sth and $sth->execute($msgid,$url)) {
    ($urlid) = $dbh->selectrow_array("SELECT currval('urls_url_id_seq')");
  } else {
    print $dbh->errstr(),"\n";
  }

where $msgid and $url are the same values above, I get this output:

ERROR:  Cannot i

This makes it a bit difficult to distinguish between a hard error and
simply a duplicate insert error, which I can handle in this app.

Also, at other times, I get just "7" as the error message rather than
the full message, making error reporting just a bit confusing. ;-)

Do you know of any issues with $dbh->errstr that could be causing
this?  Any workarounds I might try?

Thu, 3 May 2001 15:24:31 -0400
Vivek Khera <khera@kcilink.com>
DBD::Pg errstr method doesn't return full error messages
On Thu, 3 May 2001 15:24:31 -0400, Vivek Khera wrote:
> Hi, I'm using DBD::Pg version 0.98 with Postgres 7.1.  I'm noticing
> that quite often on an error, the $dbh->errstr method doesn't return
> the full error.

Here's a patch to DBD::Pg 0.98 which fixes this:

--- dbdimp.c.orig       Tue May  1 11:46:47 2001
+++ dbdimp.c    Tue May  1 11:55:26 2001
@@ -72,18 +72,21 @@
     char *error_msg;
 {
     D_imp_xxh(h);
-    char *err, *src, *dst; 
+    char *err, *src, *dst, *end; 
     int  len  = strlen(error_msg);
 
-    err = (char *)malloc(strlen(error_msg + 1));
+    err = (char *)malloc(len + 1);
     if (!err) {
       return;
     }
+    /* Remove trailing newlines, allowing for multi-line messages */
+    for(end = error_msg + len; end > error_msg && end[-1] == '\n'; --end);
+    
     src = error_msg;
     dst = err;
 
     /* copy error message without trailing newlines */
-    while (*dst != '\0' && *dst != '\n') {
+    while (src < end){
         *dst++ = *src++;
     }
     *dst = '\0';

-- 
	Peter Haworth	pmh@edison.ioppublishing.com
"A good messenger expects to get shot."
	--Larry Wall

Fri,  4 May 2001 12:49:57 +0100
"Peter Haworth" <pmh@edison.ioppublishing.com>
Re: DBD::Pg errstr method doesn't return full error
    messages
Hello, 
I am having this little annoying problem with permisions - there is a
table, with 'select' permision, and then there is a view with ALL
permisions on it.
When I update the view, I get 'permision denied' at source table, unless
I'm using user with ability to create databases/users.

>From logs:
010504.12:27:31.774  [2179] ProcessQuery
010504.12:27:31.779  [2179] query: SELECT oid FROM "jednostka" WHERE
"brcd" = $1 FOR UPDATE OF "jednostka"
010504.12:27:31.780  [2179] parser outputs:
010504.12:27:31.784  [2179] ERROR:  jednostka: Permission denied.

but select oid FROM  "jednostka" where "brcd" = 222; works fine. for
update clause makes the difference.

What's wrong?

--
xx xxx...
Dariusz Pietrzak			http://eyck.tinet.pl
    Eyck@irc.pl        dariush@ajax.umcs.lublin.pl


Fri, 4 May 2001 14:05:19 +0200 (CEST)
Dariusz Pietrzak <dariush@ajax.umcs.lublin.pl>
Permissions and views.
Dariusz Pietrzak <dariush@ajax.umcs.lublin.pl> writes:
> but select oid FROM  "jednostka" where "brcd" = 222; works fine. for
> update clause makes the difference.

What PG version is this?

			regards, tom lane

Fri, 04 May 2001 10:06:01 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Permissions and views. 
On Lun 07 May 2001 11:29, you wrote:
> > What PG version is this?
>
> 7.0.2
>
> It is said that RULES are executed with rule's owner permissions, so how
> is it possible that different users are getting different results?

This is not true. Rules are not executed with owner permission. The 
permission needed is over the relation on which the 
SELECT/INSERT/DELETE/UPDATE has been executed.

An example: If you have permission to read (SELECT) on a table, the rule over 
that table, ON select will be executed, else NOT.

Saludos... :-)

P.D.: Check views and rules for more info.


-- 
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Mon, 7 May 2001 08:55:30 +0300
=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <martin@bugs.unl.edu.ar>
Re: Permissions and views.

> What PG version is this?
7.0.2

It is said that RULES are executed with rule's owner permissions, so how
is it possible that different users are getting different results?



Mon, 7 May 2001 10:29:11 +0200 (CEST)
Dariusz Pietrzak <dariush@ajax.umcs.lublin.pl>
Re: Permissions and views. 
=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <martin@bugs.unl.edu.ar> writes:
>> It is said that RULES are executed with rule's owner permissions, so how
>> is it possible that different users are getting different results?

> This is not true. Rules are not executed with owner permission.

Yes they are.  If you do something like

	INSERT INTO view ...

which is rewritten by a rule into INSERT INTO someplace_else,
then there are two sets of permission checks applied: the original
caller must have insert rights on the view, and the rule owner must
have insert rights on "someplace_else".

In the case at hand, I'd expect that the owner of the rule issuing
SELECT...FOR UPDATE would need to have select and update permission
on the target table.

There have been sundry bugs in this mechanism in various versions of
Postgres, which is why I asked what version.  But on reading over the
thread, there's not really enough info to know whether the system
is misbehaving or not.  We'd need to see a more complete example.

			regards, tom lane

Mon, 07 May 2001 10:44:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Permissions and views. 

Has this gotten back to the DBD Perl maintainers?


> On Thu, 3 May 2001 15:24:31 -0400, Vivek Khera wrote:
> > Hi, I'm using DBD::Pg version 0.98 with Postgres 7.1.  I'm noticing
> > that quite often on an error, the $dbh->errstr method doesn't return
> > the full error.
> 
> Here's a patch to DBD::Pg 0.98 which fixes this:
> 
> --- dbdimp.c.orig       Tue May  1 11:46:47 2001
> +++ dbdimp.c    Tue May  1 11:55:26 2001
> @@ -72,18 +72,21 @@
>      char *error_msg;
>  {
>      D_imp_xxh(h);
> -    char *err, *src, *dst; 
> +    char *err, *src, *dst, *end; 
>      int  len  = strlen(error_msg);
>  
> -    err = (char *)malloc(strlen(error_msg + 1));
> +    err = (char *)malloc(len + 1);
>      if (!err) {
>        return;
>      }
> +    /* Remove trailing newlines, allowing for multi-line messages */
> +    for(end = error_msg + len; end > error_msg && end[-1] == '\n'; --end);
> +    
>      src = error_msg;
>      dst = err;
>  
>      /* copy error message without trailing newlines */
> -    while (*dst != '\0' && *dst != '\n') {
> +    while (src < end){
>          *dst++ = *src++;
>      }
>      *dst = '\0';
> 
> -- 
> 	Peter Haworth	pmh@edison.ioppublishing.com
> "A good messenger expects to get shot."
> 	--Larry Wall
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

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

Tue, 8 May 2001 14:54:01 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DBD::Pg errstr method doesn't return full error    messages
Bruce Momjian wrote:
> 
> Has this gotten back to the DBD Perl maintainers?
> 

yes, I just want to to apply some more patches,
before releasing the next version.

Edmund

-- 
http://www.edmund-mergl.de
fon: +49 700 edemergl

Tue, 08 May 2001 21:20:35 +0200
Edmund Mergl <e.mergl@bawue.de>
Re: DBD::Pg errstr method doesn't return full error    
 messages
> Bruce Momjian wrote:
> > 
> > Has this gotten back to the DBD Perl maintainers?
> > 
> 
> yes, I just want to to apply some more patches,
> before releasing the next version.

Edmond, what about adding your code to the official PostgreSQL CVS
distribution?  Seems like it would be a good idea and help folks.

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

Sat, 12 May 2001 17:15:38 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DBD::Pg errstr method doesn't return full error     messages
Hello,
	I want next :

	a) add constraint (primary and foreign) in existing table
	b) temporary disable constraint and enable later

Is it possible in Postgresql ?

Haris Peco
snpe@infosky.net

Mon, 14 May 2001 16:35:47 +0200
snpe <snpe@infosky.net>
Contraints in postgresql ?

On Mon, 14 May 2001, snpe wrote:

> Hello,
> 	I want next :
> 
> 	a) add constraint (primary and foreign) in existing table
> 	b) temporary disable constraint and enable later
> 
> Is it possible in Postgresql ?

Sort of...
You can add foreign key constraints using ALTER TABLE ADD CONSTRAINT,
and unique constraints with CREATE UNIQUE INDEX (primary keys are
effectively unique constraints where all the columns are not null 
-- if you need to change the columns to not null that's a bit more
involved).  AFAIK, you can't really "disable" constraints, although
you can remove them (drop the index for unique.  you have to manually
drop the triggers created by the foreign key constraint) and re-add them.
However, if you've violated the constraint while it was gone, you
won't be able to re-add the constraint.



Mon, 14 May 2001 11:42:45 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Contraints in postgresql ?
> Bruce Momjian wrote:
> > 
> > Has this gotten back to the DBD Perl maintainers?
> > 
> 
> yes, I just want to to apply some more patches,
> before releasing the next version.

Edmund, what do you think about moving DBD perl into our main CVS tree?
Seems it would be a logical place for it.

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

Tue, 4 Sep 2001 23:59:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] DBD::Pg errstr method doesn't return full error
 messages