Thread: 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 7/28/05, germ germ <super_code_monkey@yahoo.com> wrote:
where is this sequence 'acq_requests_req_num_seq' created. You have created only 'requests_req_num_seq'
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,
where is this sequence 'acq_requests_req_num_seq' created. You have created only 'requests_req_num_seq'
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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
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. _______________________________