Обсуждение: problem inserting with sequence

I have been trying to figure out why I can't insert
into a table and I think it has something to do with
the sequnce.

I am able to use able to properly insert into the
table using the the shell, but I am not able to insert
using a php script:

INSERT INTO requests (time_stamp, req_num,
recommended_by) VALUES (now(),
nextval('requests_req_num_seq'), 'foo');

Here is the schema I'm using:
DROP SEQUENCE requests_req_num_seq;
DROP TABLE requests;

CREATE SEQUENCE requests_req_num_seq INCREMENT BY 1
START WITH 1000;

CREATE TABLE requests (
    time_stamp         timestamp    PRIMARY KEY DEFAULT 'now',
    req_num            integer        DEFAULT
nextval('acq_requests_req_num_seq') NOT NULL,
    recommended_by        varchar(35)    NOT NULL
);
GRANT ALL ON requests TO wwwrun, postgres;

If I reconfigure the schema by removing the sequence
from the table, I am able to insert into the table
using both the shell and php script:
DROP SEQUENCE requests_req_num_seq;
DROP TABLE requests;

CREATE SEQUENCE requests_req_num_seq INCREMENT BY 1
START WITH 1000;

CREATE TABLE requests (
    time_stamp         timestamp    PRIMARY KEY DEFAULT 'now',
    recommended_by        varchar(35)    NOT NULL
);

INSERT INTO requests (time_stamp, recommended_by)
VALUES (now(), 'foo');

I'm a little stumped on this one. I'm running
PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC
gcc (GCC) 3.3.3 (SuSE Linux).

Thanks in advance for any help or advice.



__________________________________
Yahoo! Mail for Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail

On Thu, Jul 28, 2005 at 05:32:03AM -0700, germ germ wrote:
> I have been trying to figure out why I can't insert
> into a table and I think it has something to do with
> the sequnce.
>
> I am able to use able to properly insert into the
> table using the the shell, but I am not able to insert
> using a php script:
>
> INSERT INTO requests (time_stamp, req_num,
> recommended_by) VALUES (now(),
> nextval('requests_req_num_seq'), 'foo');

What happens when you try the insert?  We need more details than
just "it doesn't work."  If there's an error then it should be in
the postmaster logs, and it should also be available to the PHP
script.

What's different between the situation that works and the one that
doesn't?  Are you connecting as different users?  In the code you
posted I don't see any permissions being granted on the sequence,
so nextval() might be failing with "permission denied for sequence";
another possibility is that the sequence name is wrong (see below).

> Here is the schema I'm using:
> DROP SEQUENCE requests_req_num_seq;
> DROP TABLE requests;
>
> CREATE SEQUENCE requests_req_num_seq INCREMENT BY 1
> START WITH 1000;
>
> CREATE TABLE requests (
>     time_stamp         timestamp    PRIMARY KEY DEFAULT 'now',

A timestamp is a poor choice for a primary key because it's not
unique; aside from that you've defined the default to be a constant --
run "\d requests" in psql and you'll see what I mean.  See the
following section of the documentation for more info:

http://www.postgresql.org/docs/7.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

>     req_num            integer        DEFAULT
> nextval('acq_requests_req_num_seq') NOT NULL,

This sequence name doesn't match the name of the sequence you created,
at least not the one you showed.  Is there an acq_requests_req_num_seq
sequence?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

germ germ wrote:
> I have been trying to figure out why I can't insert
> into a table and I think it has something to do with
> the sequnce.
>
> I am able to use able to properly insert into the
> table using the the shell, but I am not able to insert
> using a php script:
>
> INSERT INTO requests (time_stamp, req_num,
> recommended_by) VALUES (now(),
> nextval('requests_req_num_seq'), 'foo');
>
> Here is the schema I'm using:
> DROP SEQUENCE requests_req_num_seq;
> DROP TABLE requests;
>
> CREATE SEQUENCE requests_req_num_seq INCREMENT BY 1
> START WITH 1000;
>
> CREATE TABLE requests (
>     time_stamp         timestamp    PRIMARY KEY DEFAULT 'now',
>     req_num            integer        DEFAULT
> nextval('acq_requests_req_num_seq') NOT NULL,
>     recommended_by        varchar(35)    NOT NULL
> );
> GRANT ALL ON requests TO wwwrun, postgres;

Check you server logs and it should tell you why. Actually, check the
error code in your PHP and it should tell you why.

My immediate guess - you haven't granted permission to the sequence for
user "wwwrun".

--
   Richard Huxton
   Archonet Ltd

This is the error: 2005-07-28 08:51:08 ERROR:
permission denied for sequence requests_req_num_seq

I've tried these grants, but no luck:
GRANT ALL ON FUNCTION nextval(integer) TO wwwrun,
postgres;
GRANT ALL ON FUNCTION nextval('requests_req_num_seq')
TO wwwrun, postgres;
GRANT ALL ON FUNCTION nextval() TO wwwrun, postgres;

--- Michael Fuhr <mike@fuhr.org> wrote:

> On Thu, Jul 28, 2005 at 05:32:03AM -0700, germ germ
> wrote:
> > I have been trying to figure out why I can't
> insert
> > into a table and I think it has something to do
> with
> > the sequnce.
> >
> > I am able to use able to properly insert into the
> > table using the the shell, but I am not able to
> insert
> > using a php script:
> >
> > INSERT INTO requests (time_stamp, req_num,
> > recommended_by) VALUES (now(),
> > nextval('requests_req_num_seq'), 'foo');
>
> What happens when you try the insert?  We need more
> details than
> just "it doesn't work."  If there's an error then it
> should be in
> the postmaster logs, and it should also be available
> to the PHP
> script.
>
> What's different between the situation that works
> and the one that
> doesn't?  Are you connecting as different users?  In
> the code you
> posted I don't see any permissions being granted on
> the sequence,
> so nextval() might be failing with "permission
> denied for sequence";
> another possibility is that the sequence name is
> wrong (see below).
>
> > Here is the schema I'm using:
> > DROP SEQUENCE requests_req_num_seq;
> > DROP TABLE requests;
> >
> > CREATE SEQUENCE requests_req_num_seq INCREMENT BY
> 1
> > START WITH 1000;
> >
> > CREATE TABLE requests (
> >     time_stamp         timestamp    PRIMARY KEY DEFAULT 'now',
>
>
> A timestamp is a poor choice for a primary key
> because it's not
> unique; aside from that you've defined the default
> to be a constant --
> run "\d requests" in psql and you'll see what I
> mean.  See the
> following section of the documentation for more
> info:
>
>
http://www.postgresql.org/docs/7.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
>
> >     req_num            integer        DEFAULT
> > nextval('acq_requests_req_num_seq') NOT NULL,
>
> This sequence name doesn't match the name of the
> sequence you created,
> at least not the one you showed.  Is there an
> acq_requests_req_num_seq
> sequence?
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>




____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

germ germ <super_code_monkey@yahoo.com> writes:

> This is the error: 2005-07-28 08:51:08 ERROR:
> permission denied for sequence requests_req_num_seq
>
> I've tried these grants, but no luck:
> GRANT ALL ON FUNCTION nextval(integer) TO wwwrun,
> postgres;
> GRANT ALL ON FUNCTION nextval('requests_req_num_seq')
> TO wwwrun, postgres;
> GRANT ALL ON FUNCTION nextval() TO wwwrun, postgres;

You need to grant permission on the sequence itself.

GRANT ALL ON requests_req_num_seq TO wwwrun;

I think that's tghe right syntax--see the docs...

-Doug

On Thu, Jul 28, 2005 at 07:04:54AM -0700, germ germ wrote:
> This is the error: 2005-07-28 08:51:08 ERROR:
> permission denied for sequence requests_req_num_seq
>
> I've tried these grants, but no luck:
> GRANT ALL ON FUNCTION nextval(integer) TO wwwrun,
> postgres;
> GRANT ALL ON FUNCTION nextval('requests_req_num_seq')
> TO wwwrun, postgres;
> GRANT ALL ON FUNCTION nextval() TO wwwrun, postgres;

It's not the nextval() function itself that needs permission, it's
the sequence.  Try this:

GRANT UPDATE, SELECT ON requests_req_num_seq TO wwwrun;

Granting update on a sequence allows the use of nextval() and
setval() on that sequence; granting select allows currval().

http://www.postgresql.org/docs/7.4/static/sql-grant.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

germ germ <super_code_monkey@yahoo.com> writes:
> This is the error: 2005-07-28 08:51:08 ERROR:
> permission denied for sequence requests_req_num_seq

> I've tried these grants, but no luck:
> GRANT ALL ON FUNCTION nextval(integer) TO wwwrun,
> postgres;
> GRANT ALL ON FUNCTION nextval('requests_req_num_seq')
> TO wwwrun, postgres;
> GRANT ALL ON FUNCTION nextval() TO wwwrun, postgres;

The missing permission is for the sequence, not the function.

grant all on requests_req_num_seq to ...

            regards, tom lane

Thank you all for your help. I got it working, once.

Right after I made the change and tested it,
everything worked perfect. Then I had a freak nose
bleed- (This has to be my 3rd nose bleed in my life
ever).  I frantically starting closing windows and
shells. While in my frantic state, I deleted my php
script I had spent about 30+ hours working on.  I
don't back anything up on my test server so it's gone
forever now. My stupidity for not backing up the test
server, so my loss- lesson learned and will not make
again.

....but it did work one time.

thank you again for all the help.

--- Michael Fuhr <mike@fuhr.org> wrote:

> On Thu, Jul 28, 2005 at 07:04:54AM -0700, germ germ
> wrote:
> > This is the error: 2005-07-28 08:51:08 ERROR:
> > permission denied for sequence
> requests_req_num_seq
> >
> > I've tried these grants, but no luck:
> > GRANT ALL ON FUNCTION nextval(integer) TO wwwrun,
> > postgres;
> > GRANT ALL ON FUNCTION
> nextval('requests_req_num_seq')
> > TO wwwrun, postgres;
> > GRANT ALL ON FUNCTION nextval() TO wwwrun,
> postgres;
>
> It's not the nextval() function itself that needs
> permission, it's
> the sequence.  Try this:
>
> GRANT UPDATE, SELECT ON requests_req_num_seq TO
> wwwrun;
>
> Granting update on a sequence allows the use of
> nextval() and
> setval() on that sequence; granting select allows
> currval().
>
>
http://www.postgresql.org/docs/7.4/static/sql-grant.html
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

germ germ wrote:
> Thank you all for your help. I got it working, once.
>
> Right after I made the change and tested it,
> everything worked perfect. Then I had a freak nose
> bleed- (This has to be my 3rd nose bleed in my life
> ever).  I frantically starting closing windows and
> shells. While in my frantic state, I deleted my php
> script I had spent about 30+ hours working on.  I
> don't back anything up on my test server so it's gone
> forever now. My stupidity for not backing up the test
> server, so my loss- lesson learned and will not make
> again.

Well? Inquiring minds want to know... Did it work?
Did closing windows and shells stop your nosebleed?

:)

--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________