Re: [SQL] getting duplicate number is there a
Joel Fradkin wrote: I was using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in MSSQL. Is there something similar in postgres to ensure its not in the middle of being updated? Yep - see the SQL COMMANDS reference section under SET TRANSACTION ... You could use LOCK TABLE too. See Chapter 12 - Concurrency Control for discussion. sql = "SELECT COUNT(*) FROM tblcase WHERE LocationID = " & intLocationID & _ " and substr(casenum,length(casenum)-1,2) = '" & right(year(date),2) & "' AND clientnum = '" & _ chrClientNum & "'" I will add a select just before doing the insert to see if this helps, its not happening a bunch, but 5 6 times a day is still an issue for me. I use the count as a segment of my case number so each time a new case is entered the count goes up for that location for that year. I'd be tempted to have a case_numbers table with (year,location,max_num) and lock/read/insert to that. Makes everything explicit, and means you don't have to mess around with counts/substrings. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] getting duplicate number is there a
I actually had the same thought (a counter table, I might be able to add fields to the location table, but we have several applications case is just an example). I agree that is probably the safest way and it also fixes another issue I have been having when a user wants to transfer a case to another location. I appreciate the ideas, I could probably safely lock the numbering table as I would be afraid of locking the case table. Joel Fradkin I'd be tempted to have a case_numbers table with (year,location,max_num) and lock/read/insert to that. Makes everything explicit, and means you don't have to mess around with counts/substrings. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] getting duplicate number is there a
On Mon, May 16, 2005 at 17:36:21 -0400, Joel Fradkin <[EMAIL PROTECTED]> wrote: > I was using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in MSSQL. > > > > Is there something similar in postgres to ensure its not in the middle of > being updated? Postgres also has SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. This will prevent the current transaction from seeing the results of any transactions that were not committed before the current transaction started. In this mode updates can fail because of actions of concurrent transactions, so you need to be able to retry. Also Postgres does not do predicate locking. For some operations serializable isn't good enough. Instead you need to lock a table to prevent inserts. The common case is two simultaneous transactions that insert a record into the same table and store the count of the number of records in the table, while expecting things to look like one transaction happened before the other. (E.g. that they return distinct values for the counts.) > > > > sql = "SELECT COUNT(*) FROM tblcase WHERE LocationID = " & > intLocationID & _ > > " and substr(casenum,length(casenum)-1,2) = '" & > right(year(date),2) & "' AND clientnum = '" & _ > > chrClientNum & "'" > > > > I will add a select just before doing the insert to see if this helps, its > not happening a bunch, but 5 6 times a day is still an issue for me. > > I use the count as a segment of my case number so each time a new case is > entered the count goes up for that location for that year. > > > > Joel Fradkin > > > > Wazagua, Inc. > 2520 Trailmate Dr > Sarasota, Florida 34243 > Tel. 941-753-7111 ext 305 > > > > [EMAIL PROTECTED] > www.wazagua.com > Powered by Wazagua > Providing you with the latest Web-based technology & advanced tools. > C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc > This email message is for the use of the intended recipient(s) and may > contain confidential and privileged information. Any unauthorized review, > use, disclosure or distribution is prohibited. If you are not the intended > recipient, please contact the sender by reply email and delete and destroy > all copies of the original message, including attachments. > > > > > > > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] interesting SQL puzzle - concatenating column with itself.
> SELECT your_concat( memo_text ) FROM > (SELECT memo_id, sequence, memo_text FROM table ORDER BY memo_id, sequence > OFFSET 0) AS foo > GROUP BY memo_id I'm just curious - what's the 'OFFSET 0' for? Dmitri ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] getting duplicate number is there a
You can select "for update", so you ensure that the rows are locked for your current transaction's use exclusively. If the rows in question had been modified by another ongoing transaction, then the select will get blocked until the other transaction is finished. Cheers, Ezequiel Tolnay [EMAIL PROTECTED] Joel Fradkin wrote: I was using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in MSSQL. Is there something similar in postgres to ensure its not in the middle of being updated? sql = "SELECT COUNT(*) FROM tblcase WHERE LocationID = " & intLocationID & _ " and substr(casenum,length(casenum)-1,2) = '" & right(year(date),2) & "' AND clientnum = '" & _ chrClientNum & "'" I will add a select just before doing the insert to see if this helps, its not happening a bunch, but 5 6 times a day is still an issue for me. I use the count as a segment of my case number so each time a new case is entered the count goes up for that location for that year. Joel Fradkin ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] plpython setof
Anybody know how to return a setof from a plpython function? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?
Hi: Oracle has a pseudo-column "ROWNUM" to return the sequence number in which a row was returned when selected from a table. The first row ROWNUM is 1, the second is 2, and so on. Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we can write the following query: select * from (select RowNum, pg_catalog.pg_proc.* from pg_catalog.pg_proc) inline_view where RowNum between 100 and 200; Thanks, Dennis ---(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: [SQL] CASCADE and TRIGGER - Some weird problem
Hi, The issue is due to records in Account_message is still exists for the records which are going to be deleted from the Message table. Please check the sequence of deleting the records. When I tried to delete a record using your example, the following exception is raised. ERROR: update or delete on "message" violates foreign key constraint "account_message__msg_fkey" on "account_message" DETAIL: Key (_message_id)=(2) is still referenced from table "account_message". Regards, R.Muralidharan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Sonic Sent: Wednesday, May 04, 2005 1:01 PM To: [email protected] Subject: [SQL] CASCADE and TRIGGER - Some weird problem -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hi, have a little problem with a trigger and the ON DELETE CASCADE statement. i'm working on a db that represents Users and Messages. A message can be owned by more than a user. if i delete a user all his related objects are deleted too (ON DELETE CASCADE), but if a message that this user owns is also owned by another user, it has not to be deleted. i just put ON DELETE CASCADE statement on foreign keys and thos seem to work. then i wrote a trigger to check if each message the user owns is owned by someone else. if it's not delete it! - --- - -this is the code: CREATE TABLE OWNER( _LOGIN TEXT, PRIMARY KEY(_LOGIN) ); CREATE TABLE MESSAGE( _MESSAGE_ID TEXT, PRIMARY KEY(_MESSAGE_ID) ); CREATE TABLE ACCOUNT( _INDIRIZZO TEXT UNIQUE, _LOGIN TEXT, PRIMARY KEY(_INDIRIZZO,_LOGIN), FOREIGN KEY(_LOGIN)REFERENCES OWNER(_LOGIN) ON DELETE CASCADE); CREATE TABLE ACCOUNT_MESSAGE( _MSGTEXT, _INDIRIZZO TEXT, PRIMARY KEY(_MSG,_INDIRIZZO), FOREIGN KEY(_MSG)REFERENCES MESSAGE(_MESSAGE_ID), FOREIGN KEY(_INDIRIZZO)REFERENCES ACCOUNT(_INDIRIZZO) ON DELETE CASCADE); CREATE TABLE FOLDER( _PATH TEXT, _OWNER TEXT, PRIMARY KEY(_PATH), FOREIGN KEY(_OWNER)REFERENCES OWNER(_LOGIN) ON DELETE CASCADE); CREATE TABLE MSG_IN_FOLDER( _MSGTEXT, _FOLDER TEXT, PRIMARY KEY(_MSG,_FOLDER), FOREIGN KEY(_MSG)REFERENCES MESSAGE(_MESSAGE_ID), FOREIGN KEY(_FOLDER)REFERENCES FOLDER(_PATH) ON DELETE CASCADE); CREATE FUNCTION check_message() RETURNS trigger AS $check_MESSAGE$ BEGIN DELETE FROM MESSAGE WHERE _message_id IN ( SELECT _MSG FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER WHERE _MSG = OLD._MSG GROUP BY _MSG HAVING count(*)=1 ); RAISE NOTICE 'Value of OLD._MSG %', OLD._MSG; RETURN NULL; END; $check_MESSAGE$ LANGUAGE plpgsql; CREATE TRIGGER check_message AFTER DELETE ON MSG_IN_FOLDER FOR EACH ROW EXECUTE PROCEDURE check_message(); - - - --and these are some values: delete from OWNER; delete from ACCOUNT; delete from MESSAGE; delete from ACCOUNT_MESSAGE; delete from FOLDER; delete from MSG_IN_FOLDER; insert into OWNER (_login) values ('anna'); insert into OWNER (_login) values ('paolo'); insert into ACCOUNT values ('[EMAIL PROTECTED]', 'anna'); insert into ACCOUNT values ('[EMAIL PROTECTED]', 'paolo'); insert into MESSAGE (_message_id) values ('1'); insert into MESSAGE (_message_id) values ('2'); insert into ACCOUNT_MESSAGE values ('1', '[EMAIL PROTECTED]'); insert into ACCOUNT_MESSAGE values ('1', '[EMAIL PROTECTED]'); insert into ACCOUNT_MESSAGE values ('2', '[EMAIL PROTECTED]'); insert into FOLDER (_path, _OWNER) values ('c', 'anna'); insert into MSG_IN_FOLDER values ('1', 'c'); insert into MSG_IN_FOLDER values ('2', 'c'); select * from MESSAGE; - -- as you see there are 2 messages. message 1 is owned both by 'anna' and 'paolo'. message 2 is owned just by 'anna'. now what i want is that if i delete user 'anna' just message 2 is deleted. i guess i've done that with my trigger: ... CREATE FUNCTION check_message() RETURNS trigger AS $check_MESSAGE$ BEGIN DELETE FROM MESSAGE WHERE _message_id IN ( SELECT _MSG FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?
On Thu, May 12, 2005 at 01:07:00PM -0600, [EMAIL PROTECTED] wrote: > > Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If > so, we can write the following query: No. What is the purpose of your query? You could use ORDER BY and LIMIT..OFFSET to do what you want. I think. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(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: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
On Thu, 2005-05-12 at 14:07, [EMAIL PROTECTED] wrote:
> Hi:
>
> Oracle has a pseudo-column "ROWNUM" to return the sequence number in which a
> row was returned when selected from a table. The first row ROWNUM is 1, the
> second is 2, and so on.
>
> Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we
> can write the following query:
>
> select *
> from (select RowNum, pg_catalog.pg_proc.*
> from pg_catalog.pg_proc) inline_view
> where RowNum between 100 and 200;
You can get a functional equivalent with a temporary sequence:
create temp sequence rownum;
select *, nextval('rownum') as rownum from sometable;
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?
On Thu, May 12, 2005 at 13:07:00 -0600, [EMAIL PROTECTED] wrote: > Hi: > > Oracle has a pseudo-column "ROWNUM" to return the sequence number in which a > row was returned when selected from a table. The first row ROWNUM is 1, the > second is 2, and so on. > > Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we > can write the following query: No. > > select * > from (select RowNum, pg_catalog.pg_proc.* > from pg_catalog.pg_proc) inline_view > where RowNum between 100 and 200; You could use LIMIT and OFFSET to get the values from the table for the 100th through 200th rows (though typically you want to add an ORDER BY clause). You could have you application supply the rownum column values. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
Andrew Sullivan escreveu: On Thu, May 12, 2005 at 01:07:00PM -0600, [EMAIL PROTECTED] wrote: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we can write the following query: No. What is the purpose of your query? You could use ORDER BY and LIMIT..OFFSET to do what you want. I think. The problem is probably speed. I have done a lot of tests, and when OFFSET gets to a few thousands on a multimega-recs database, it gets very very slow... Is there any other to work around that? Alain ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] interesting SQL puzzle - concatenating column with itself.
I'm just curious - what's the 'OFFSET 0' for? Trick to fool postgres into thinking it can't rewrite out your subquery and eliminate it ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] triggering an external action
I am trying to find the best way for a database trigger to signal a client process to take an action. Specifically, I am working on the classic problem of creating and modifying system accounts based on the updates to a "person registry" database. The basic model I'm working with has triggers on my tables of interest that stick a person's unique ID into a "todo queue" table whenever modifications are made. The queue is periodically polled by a script which processes and deletes each "todo" record. The polling script goes to sleep for gradually increasing periods of time whenever it polls the queue and finds it empty. What I want is a trigger on the "todo" table that will "kick" my processing script to make it wake up and process the queue immediately. In an Oracle environment, I think I could use a database pipe to achieve more or less the behavior I'm looking for, but I can't find anything that does what I want in pgsql at the database level. I could write a trigger in C or Perl or something that would do something at the OS level, like send a signal, but when I go down that path I start having to deal with unix issues like having to elevate to root privs to send a signal to a process that isn't running as the pgsql user, etc. It is doable but gets complex quickly. Is there anything I'm missing at the database level that would help me process updates in realtime? (And if not, which of the other mailing lists would be the most appropriate place for me to discuss the best way to implement an OS-level solution?) Thanks, -jbp -- Jay Parker - UALR Computing Services - Networks Project Manager [EMAIL PROTECTED] - http://www.ualr.edu/jbparker - 501-569-3345 But I have promises to keep, And miles to go before I sleep. -Frost ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] triggering an external action
>I am trying to find the best way for a database trigger to signal a >client process to take an action. > >Specifically, I am working on the classic problem of creating and >modifying system accounts based on the updates to a "person registry" >database. > >The basic model I'm working with has triggers on my tables of interest >that stick a person's unique ID into a "todo queue" table whenever >modifications are made. The queue is periodically polled by a script >which processes and deletes each "todo" record. The polling >script goes >to sleep for gradually increasing periods of time whenever it >polls the >queue and finds it empty. > >What I want is a trigger on the "todo" table that will "kick" my >processing script to make it wake up and process the queue >immediately. > In an Oracle environment, I think I could use a database pipe to >achieve more or less the behavior I'm looking for, but I can't find >anything that does what I want in pgsql at the database level. > >I could write a trigger in C or Perl or something that would do >something at the OS level, like send a signal, but when I go down that >path I start having to deal with unix issues like having to elevate to >root privs to send a signal to a process that isn't running as >the pgsql >user, etc. It is doable but gets complex quickly. > >Is there anything I'm missing at the database level that would help me >process updates in realtime? (And if not, which of the other mailing >lists would be the most appropriate place for me to discuss >the best way >to implement an OS-level solution?) This sounds like a job for LISTEN/NOTIFY: http://www.postgresql.org/docs/8.0/static/sql-listen.html http://www.postgresql.org/docs/8.0/static/sql-notify.html //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] triggering an external action
Jay Parker wrote: I am trying to find the best way for a database trigger to signal a client process to take an action. Specifically, I am working on the classic problem of creating and modifying system accounts based on the updates to a "person registry" database. The basic model I'm working with has triggers on my tables of interest that stick a person's unique ID into a "todo queue" table whenever modifications are made. The queue is periodically polled by a script which processes and deletes each "todo" record. The polling script goes to sleep for gradually increasing periods of time whenever it polls the queue and finds it empty. What I want is a trigger on the "todo" table that will "kick" my processing script to make it wake up and process the queue immediately. In an Oracle environment, I think I could use a database pipe to achieve more or less the behavior I'm looking for, but I can't find anything that does what I want in pgsql at the database level. I could write a trigger in C or Perl or something that would do something at the OS level, like send a signal, but when I go down that path I start having to deal with unix issues like having to elevate to root privs to send a signal to a process that isn't running as the pgsql user, etc. It is doable but gets complex quickly. Is there anything I'm missing at the database level that would help me process updates in realtime? (And if not, which of the other mailing lists would be the most appropriate place for me to discuss the best way to implement an OS-level solution?) Thanks, -jbp How about LISTEN and NOTIFY, would they work for this? -- ___ 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. ___ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] ERROR: unterminated quoted string... help
Hi
I'm trying to insert encrypted data into the database and I'm noticing
error dealing with quotes. Below is the error print out...
suggestions and/or at least point me in the direction to find a solution,
Thanks,
J
INSERT INTO sample.users (user_name, first_name) VALUES
('jokers', '=ïµiF!¶6(ÖŸã?¾óˆÌ‘'-Iw‰iDÖiJÐÿ† %')
Warning: pg_query() [function.pg-query]: Query failed: ERROR:
unterminated quoted string at or near "'=ïµi" at character 68 in
/usr/local/apache2/htdocs/php/5/Theirry_DB.php on line 162
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
On Tue, 2005-05-17 at 14:48 -0300, Alain wrote: > > Andrew Sullivan escreveu: > > On Thu, May 12, 2005 at 01:07:00PM -0600, [EMAIL PROTECTED] wrote: > > > >>Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If > >>so, we can write the following query: > > > > > > No. What is the purpose of your query? You could use ORDER BY and > > LIMIT..OFFSET to do what you want. I think. > > The problem is probably speed. I have done a lot of tests, and when > OFFSET gets to a few thousands on a multimega-recs database, it gets > very very slow... is there not a similar loss of speed using ROWNUM on oracle? > ... Is there any other to work around that? if you are ordering by a unique key, you can use the key value in a WHERE clause. select ... where ukey>? order by ukey limit 100 offset 100; (the ? is placeholder for the last value of ukey returned from previous select) gnari ---(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: [SQL] triggering an external action
On 05/17/2005 01:07 PM, Bricklen Anderson wrote: How about LISTEN and NOTIFY, would they work for this? Yes, that is precisely what I need... and what I have somehow overlooked during at least a dozen passes through the docs. Sigh. Thanks for your help, -jbp -- Jay Parker - UALR Computing Services - Networks Project Manager [EMAIL PROTECTED] - http://www.ualr.edu/jbparker - 501-569-3345 But I have promises to keep, And miles to go before I sleep. -Frost ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ERROR: unterminated quoted string... help
Scott Marlowe wrote: Use a bytea field and use pg_escape_bytea() to prepare the data for insertion. Thanks Scott, I will try it now. J ---(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: [SQL] ERROR: unterminated quoted string... help
On Tue, 2005-05-17 at 13:15, Postgres Admin wrote:
> Hi
>
> I'm trying to insert encrypted data into the database and I'm noticing
> error dealing with quotes. Below is the error print out...
>
> suggestions and/or at least point me in the direction to find a solution,
>
> Thanks,
> J
>
>
>
> INSERT INTO sample.users (user_name, first_name) VALUES
> ('jokers', '=ÃÂiF!Â6(ÃÅÃÂÃËÃâ'-IwâiDÃiJÃÃâ %')
>
> Warning: pg_query() [function.pg-query]: Query failed: ERROR:
> unterminated quoted string at or near "'=ÃÂi" at character 68 in
> /usr/local/apache2/htdocs/php/5/Theirry_DB.php on line 162
Use a bytea field and use pg_escape_bytea() to prepare the data for
insertion.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
No. What is the purpose of your query? You could use ORDER BY and LIMIT..OFFSET to do what you want. I think. The problem is probably speed. I have done a lot of tests, and when OFFSET gets to a few thousands on a multimega-recs database, it gets very very slow... is there not a similar loss of speed using ROWNUM on oracle? ... Is there any other to work around that? if you are ordering by a unique key, you can use the key value in a WHERE clause. select ... where ukey>? order by ukey limit 100 offset 100; (the ? is placeholder for the last value of ukey returned from previous select) I tried that. It does not work in the generic case: 6 MegaRec, telephone listing, alphabetical order. The problem is that somewhere there is a single user with too many entries (over 1000). I even tried to filter the repetitions, but somewhere I get stuck if one guy has too mny entries (one for each phone number). I tried using both the name and the primary key (with a combined index), to get faster to the record I want, but I was not sucessfull in building a where clause. I would appreciate any help, in fact this is my primary reason for joining this list ;-) Alain ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
On Tue, May 17, 2005 at 03:43:32PM -0300, Alain wrote: > > I tried using both the name and the primary key (with a combined index), > to get faster to the record I want, but I was not sucessfull in building > a where clause. > > I would appreciate any help, in fact this is my primary reason for > joining this list ;-) Well, then, table schemas, data distribution, EXPLAIN and EXPLAIN ANALYSE output, and some statement of what you're trying to get out is likely what we need to see. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
On Tue, 2005-05-17 at 15:43 -0300, Alain wrote: > [how to solve the get next 100 records problem] I am assuming this is for a web like interface, in other words that cursors are not applicable > > [me] > > if you are ordering by a unique key, you can use the key value > > in a WHERE clause. > > > > select ... where ukey>? order by ukey limit 100 offset 100; > > > > (the ? is placeholder for the last value of ukey returned > > from previous select) > > I tried that. It does not work in the generic case: 6 MegaRec, telephone > listing, alphabetical order. The problem is that somewhere there is a > single user with too many entries (over 1000). I even tried to filter > the repetitions, but somewhere I get stuck if one guy has too mny > entries (one for each phone number). > > I tried using both the name and the primary key (with a combined index), > to get faster to the record I want, but I was not sucessfull in building > a where clause. lets say pkey is your primary key and skey is your sort key, and there exists an index on (skey,pkey) your first select is select ... from tab ORDER by skey,pkey LIMIT 100; your subsequent selects are select ... from tab WHERE skey>skey_last OR (skey=skey_last AND pkey>pkey_last) ORDER BY skey,pkey LIMIT 100 OFFSET 100; > I would appreciate any help, in fact this is my primary reason for > joining this list ;-) gnari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
your subsequent selects are select ... from tab WHERE skey>skey_last OR (skey=skey_last AND pkey>pkey_last) ORDER BY skey,pkey LIMIT 100 OFFSET 100; why offset ? you should be able to use the skey, pkey values of the last row on the page to show the next page, no need for offset then. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
On Wed, 2005-05-18 at 00:13 +0200, PFC wrote: > > your subsequent selects are > > select ... from tab WHERE skey>skey_last > >OR (skey=skey_last AND pkey>pkey_last) > > ORDER BY skey,pkey > > LIMIT 100 OFFSET 100; > > why offset ? > you should be able to use the skey, pkey values of the last row on the > page to show the next page, no need for offset then. of course you are right. the WHERE clause is supposed to replace the OFFSET. too much cut and pasting without thinking and testing. gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
Ragnar Hafstað escreveu: [how to solve the get next 100 records problem] I tried that. It does not work in the generic case: 6 MegaRec, telephone listing, alphabetical order. lets say pkey is your primary key and skey is your sort key, and there exists an index on (skey,pkey) your first select is select ... from tab ORDER by skey,pkey LIMIT 100; your subsequent selects are select ... from tab WHERE skey>skey_last OR (skey=skey_last AND pkey>pkey_last) ORDER BY skey,pkey LIMIT 100 OFFSET 100; I tied that, it is veeery slow, probably due to the OR operand :( BUT, I think that this is close to a final solution, I made some preliminary test ok. Please tell me what you think about this. Fisrt let's state that I am reading records to put on a screen (in a Table/Grid). I separated the problem is *3* parts -first select is as above: select ... from tab ORDER by skey,pkey LIMIT 100; -second method for next 100: select ... from tab WHERE skey>=skey_last ORDER BY skey,pkey LIMIT 100; but here I test for repetitions using pkey and discard them -now if I get all repetitions or the last 100 have the same skey with the second method, I use select ... from tab WHERE skey=skey_last AND pkey>pkey_last ORDER BY skey,pkey LIMIT 100; until I get an empty response, then I go back to the second method. All queries are extremely fast with 600 records and it looks like the few redundant or empty queries (but very fast) will not be a problem. What is your opinion about this (apart that it is a bit complex :) ?? Alain ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
