[SQL] Implementing queue semantics (novice)
Hi, since I am new to writing stored procedures I'd like to ask first bevore I do a mistake. I want to implement some kind of queue (fifo). There are n users/processes that add new records to a table and there are m consumers that take out these records and process them. It's however possible for a consumer to die or loose connection while records must not be unprocessed. They may rather be processed twice. This seems to me as a rather common problem. But also with atomicy-holes to fall into. How is this commonly implemented? I can imagine an 'add' and a 'get' function together with one aditional 'processed' timestamp-column? Thanks for helping me do the right. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Problems with HAVING
Hello, My problem is that I want to select the row with max(date) but also limited with where clauses. Select test.name from test where test.name = foo.name having max(test.date) This is a subquery and is part bigger query. How I can select the row with the max query. ERROR: argument of HAVING must be type boolean, not type date This is the error I receive. As far as I know I can't use agregate functions in where clause. Thank you in advance. Kaloyan Iliev
Re: [SQL] Implementing queue semantics (novice)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The name for what you're looking to build is a concurrent batch processing system. Here's a basic one. - -- adding processes BEGIN; INSERT INTO queue (queue_id, processing_pid, processing_start, ~ processing_status, foreign_id) VALUES (DEFAULT, NULL, NULL, ~ (SELECT queue_status_id FROM queue_status WHERE name = 'pending'), ~ foreign_id); COMMIT; - -- removing processes BEGIN; SELECT queue_id, foreign_id FROM queue WHERE processing_status = (SELECT queue_status_id FROM queue_status ~ WHERE name = 'pending') ORDER BY queue_id LIMIT 1 FOR UPDATE; UPDATE queue SET processing_pid = ?, ~ processing_start = now(), ~ processing_status = (SELECT queue_status_id FROM queue_status WHERE ~ name = 'active') WHERE id = ?; COMMIT; - -- client code does whatever it's going to do here BEGIN; SELECT 1 FROM queue WHERE queue_id = ? AND processing_pid = ? FOR UPDATE; - -- confirm that it exists DELETE FROM queue WHERE queue_id = ? INSERT INTO queue_history (queue_id, processing_pid, processing_start, ~ processing_complete, processing_status, foreign_id) VALUES (queue_id, processing_pid, processing_start, now(), ~ (SELECT queue_status_id FROM queue_status WHERE name = 'done'), ~ foreign_id); COMMIT; - -- a seperate process reaps orphaned entries should processing fail. BEGIN; SELECT queue_id, processing_pid FROM queue WHERE now() - processing_start > 'some reasonable interval'::interval AND processing_status = (SELECT queue_status_id FROM queue_status WHERE ~ name = 'active' FOR UPDATE; - -- for each entry, check to see if the PID is still running UPDATE queue SET ~ processing_pid = NULL, ~ processing_start = NULL, ~ processing_status = (SELECT id FROM queue_status WHERE name = 'pending') WHERE id = ?; COMMIT; There are more complicated approaches available. If you plan to have multiple machines processing, you probably want to add a processing_node entry too. KÖPFERL Robert wrote: | Hi, | | since I am new to writing stored procedures I'd like to ask first bevore I | do a mistake. | | I want to implement some kind of queue (fifo). There are n users/processes | that add new records to a table and there are m consumers that take out | these records and process them. | It's however possible for a consumer to die or loose connection while | records must not be unprocessed. They may rather be processed twice. | | This seems to me as a rather common problem. But also with atomicy-holes to | fall into. | How is this commonly implemented? | | | I can imagine an 'add' and a 'get' function together with one aditional | 'processed' timestamp-column? | | | | Thanks for helping me do the right. | | ---(end of broadcast)--- | TIP 4: Don't 'kill -9' the postmaster - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFB5U3kgfzn5SevSpoRAoesAKCAZkr61I5knCw9tIr8rlO0xri7YACgifrn N01nXZY8UKmIlTnGkngHKUo= =UXRk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] failed to find conversion function from "unknown" to text
Dear Gurus, Version: 8.0.0rc4 The scene below might look funny but it's essential for our project: [local]:tir=# select 'a' as asdf; asdf -- a (1 row) [local]:tir=# select case 'a' when 'a' then 1 else 2 end as asdf; ERROR: failed to find conversion function from "unknown" to text This worked up to v7.4.6, and couldn't see it in the "Migration" part of the HISTORY file. Question: is there a way to tell the server to convert unknown to something (text or varchar, don't really care), or to write such a "conversion function"? A similar conversion for name to varchar conversion would also be highly desirable for easier migration. Right now I'm writing wrapper functions for all our functions where params might be names. If it's written in the doc, could you please tell me the page? TIA, -- G. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Syntax error while altering col-type
Hi, I am perplexed. I tried to change the type of a column using the syntax I found in the [ALTER TABLE] section: ALTER TABLE "Mailboxes" ALTER COLUMN "Status" TYPE int4; This shuld be no problem since the current type acutally is int4 and the names are copy'n'pasted. The server responds as follows: ERROR: syntax error at or near "TYPE" at character 47 It seems like it doesn't like the "type". My compiler-experience tells me that the column identifier is somehow broken. But it exists! What went wrong? ---(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] Syntax error while altering col-type
On Wed, Jan 12, 2005 at 06:02:10PM +0100, KÖPFERL Robert wrote: > ALTER TABLE "Mailboxes" ALTER COLUMN "Status" TYPE int4; > > This shuld be no problem since the current type acutally is int4 and the > names are copy'n'pasted. The server responds as follows: > > ERROR: syntax error at or near "TYPE" at character 47 That's exactly the error you'd get on a pre-8.0 system that doesn't support altering a column's type. Are you looking at 8.0 documentation but running a 7.x server? What does "SELECT version();" show? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] Problems with HAVING
On Wed, Jan 12, 2005 at 06:38:51PM +0200, Kaloyan Iliev Iliev wrote: > My problem is that I want to select the row with max(date) but also > limited with where clauses. If you don't mind using a non-standard feature then try SELECT DISTINCT ON (not just DISTINCT, but DISTINCT ON): http://www.postgresql.org/docs/7.4/static/sql-select.html#SQL-DISTINCT -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] Syntax error while altering col-type
while you weren't looking, KÖPFERL Robert wrote: > ERROR: syntax error at or near "TYPE" at character 47 What version are you running? To my knowledge, altering the type of a column is a new feature to 8.0. /rls -- :wq ---(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] failed to find conversion function from "unknown" to text
On Wed, Jan 12, 2005 at 05:52:42PM +0100, Sz?cs Gábor wrote: > Question: is there a way to tell the server to convert unknown to something > (text or varchar, don't really care), or to write such a "conversion > function"? You ought to be able to cast (e.g. "SELECT case 'a'::text. . .) A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] failed to find conversion function from "unknown" to text
=?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <[EMAIL PROTECTED]> writes: >[local]:tir=# select case 'a' when 'a' then 1 else 2 end as asdf; >ERROR: failed to find conversion function from "unknown" to text > This worked up to v7.4.6, and couldn't see it in the "Migration" part of the > HISTORY file. This is an unintended side effect of the change to evaluate CASE constructs more efficiently. I'll fix it. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Problems with HAVING
Kaloyan Iliev Iliev wrote: >select test.name >from test >where test.name = foo.name >having max(test.date) I don't think you use the "having" clause like you've done there. I think you want to be doing something more like: select test.name from test where test.name = foo.name and test.date in (select max(date) from test); But I may have misinterpreted you. . . Cheers, Sam ---(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] Problems with HAVING
On Wed, Jan 12, 2005 at 10:11:21AM -0700, Michael Fuhr wrote: > On Wed, Jan 12, 2005 at 06:38:51PM +0200, Kaloyan Iliev Iliev wrote: > > > My problem is that I want to select the row with max(date) but also > > limited with where clauses. > > If you don't mind using a non-standard feature then try SELECT > DISTINCT ON (not just DISTINCT, but DISTINCT ON): Sorry, I probably didn't read your message closely enough. If you just want a single record then ORDER BY ... LIMIT 1 might be more appropriate. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Recursive query to be used in another result ?
I need some help on the following problem. I have an account info table that has a hierarchy of accounts. The grouping goes from end user to organization to reseller. I have a PL/SQL function written that gives me the tree, so I just whittle it down by selecting the one id for a reseller: select account_id from roll_account(1186) where hierarchy_type_id = 2 The problem is I want to have that account_id number (1186) to be selected from my main query. (IE: select account_id from account_info) Can anyone offer some solutions to this problem? Bradley Miller NUVIO CORPORATION Phone: 816-444-4422 ext. 6757 Fax: 913-498-1810 http://www.nuvio.com [EMAIL PROTECTED]
[SQL] Problems with Quotes
Hi, I have a PL/SQL function which breaks up a comma-separated list of values stored in one column, and uses that (along with other data) to make a new table. My problem is that some of the incoming data is quoted e.g. "value1, value2, value3" Meaning that when I split on the commas, I end up with: "value1 value2 value3" I've tried using the replace() function to get rid of the ", but I can't figure out how to use it without throwing an error. I tried replace(col_name, '\"', '') and several other permutations but to no avail, do I need to use something like an ASCII character code in order to get rid of a quote? If so which one, and if not, is there a better solution? Many thanks. Kieran # The information contained in this email and any subsequent correspondence is private and is intended solely for the intended recipient(s). For those other than the intended recipient(s) any disclosure, copying, distribution, or any action taken or omitted to be taken in reliance on such information is prohibited and may be unlawful. # ---(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] Problems with Quotes
On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:
I've tried using the replace() function to get rid of the ", but I
can't figure out how to use it without throwing an error. I tried
replace(col_name, '\"', '')
and several other permutations but to no avail, do I need to use
something like an ASCII character code in order to get rid of a quote?
If so which one, and if not, is there a better solution?
Try '"' as in
select replace('this "is" it', '"', '');
replace
this is it
(1 row)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Problems with Quotes
I tried that. It starts spitting out the rest of the script to STDIN until it
gets to the next " (which is being used to quote a table name about 100 lines
further on" at which point it throws an error, and dies.
It seems it really wants me to escape it somehow, but neither '\"' or ''"'
seems to work.
-Original Message-
From: John DeSoi [mailto:[EMAIL PROTECTED]
Sent: 12 January 2005 18:18
To: Kieran Ashley
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Problems with Quotes
On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:
> I've tried using the replace() function to get rid of the ", but I
> can't figure out how to use it without throwing an error. I tried
>
> replace(col_name, '\"', '')
>
> and several other permutations but to no avail, do I need to use
> something like an ASCII character code in order to get rid of a quote?
> If so which one, and if not, is there a better solution?
>
Try '"' as in
select replace('this "is" it', '"', '');
replace
this is it
(1 row)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
#
The information contained in this email and any subsequent
correspondence is private and is intended solely for the
intended recipient(s). For those other than the intended
recipient(s) any disclosure, copying, distribution, or any
action taken or omitted to be taken in reliance on such
information is prohibited and may be unlawful.
#
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Problems with HAVING
Thanks for the replay first. Yes I use "having" like I have written. I can't use your query because in subquery I must write again the whole WHERE clause. But in the other mail in the tread there is the solution:) Thanks again Kaloyan Sam Mason wrote: Kaloyan Iliev Iliev wrote: select test.name from test where test.name = foo.name having max(test.date) I don't think you use the "having" clause like you've done there. I think you want to be doing something more like: select test.name from test where test.name = foo.name and test.date in (select max(date) from test); But I may have misinterpreted you. . . Cheers, Sam ---(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: [SQL] Problems with HAVING
Thank You, That's what I need. I know it was something simple but... Now it works perfectly. Thank's again. Kaloyan Iliev Michael Fuhr wrote: On Wed, Jan 12, 2005 at 10:11:21AM -0700, Michael Fuhr wrote: On Wed, Jan 12, 2005 at 06:38:51PM +0200, Kaloyan Iliev Iliev wrote: My problem is that I want to select the row with max(date) but also limited with where clauses. If you don't mind using a non-standard feature then try SELECT DISTINCT ON (not just DISTINCT, but DISTINCT ON): Sorry, I probably didn't read your message closely enough. If you just want a single record then ORDER BY ... LIMIT 1 might be more appropriate. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Problems with Quotes
Kieran Ashley wrote:
I tried that. It starts spitting out the rest of the script to STDIN until it gets to
the next " (which is being used to quote a table name about 100 lines further
on" at which point it throws an error, and dies.
It seems it really wants me to escape it somehow, but neither '\"' or ''"'
seems to work.
Perhaps you are forgetting to double up on your quote chars?
THis seems to work for me:
e.g.
test=# create or replace function bar(text)
test-# returns text
test-# language 'plpgsql'
test-# as 'begin return replace($1, ''"'', ); end;';
CREATE FUNCTION
test=# select bar('hello');
bar
---
hello
(1 row)
test=# select bar('hello "world"');
bar
-
hello world
(1 row)
test=#
-Original Message-
From: John DeSoi [mailto:[EMAIL PROTECTED]
Sent: 12 January 2005 18:18
To: Kieran Ashley
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Problems with Quotes
On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:
I've tried using the replace() function to get rid of the ", but I
can't figure out how to use it without throwing an error. I tried
replace(col_name, '\"', '')
and several other permutations but to no avail, do I need to use
something like an ASCII character code in order to get rid of a quote?
If so which one, and if not, is there a better solution?
Try '"' as in
select replace('this "is" it', '"', '');
replace
this is it
(1 row)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
#
The information contained in this email and any subsequent
correspondence is private and is intended solely for the
intended recipient(s). For those other than the intended
recipient(s) any disclosure, copying, distribution, or any
action taken or omitted to be taken in reliance on such
information is prohibited and may be unlawful.
#
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems with Quotes
On Wed, Jan 12, 2005 at 11:46:53AM -0700, Edmund Bacon wrote: > Perhaps you are forgetting to double up on your quote chars? If that's the problem then 8.0's dollar quoting will simplify the situation: CREATE OR REPLACE FUNCTION foo(text) RETURNS text AS $$ SELECT replace($1, '"', ''); $$ LANGUAGE sql; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems with Quotes
Ah! Fantastic.
Thank you so much. I'm still not entirely sure _why_ that works, but it
does... so I can go home now! ;)
Thanks again!
-Original Message-
From: Edmund Bacon [mailto:[EMAIL PROTECTED]
Sent: 12 January 2005 18:47
To: pgsql-sql@postgresql.org
Cc: Kieran Ashley
Subject: Re: [SQL] Problems with Quotes
Kieran Ashley wrote:
> I tried that. It starts spitting out the rest of the script to STDIN until
> it gets to the next " (which is being used to quote a table name about 100
> lines further on" at which point it throws an error, and dies.
>
> It seems it really wants me to escape it somehow, but neither '\"' or ''"'
> seems to work.
>
>
Perhaps you are forgetting to double up on your quote chars?
THis seems to work for me:
e.g.
test=# create or replace function bar(text)
test-# returns text
test-# language 'plpgsql'
test-# as 'begin return replace($1, ''"'', ); end;';
CREATE FUNCTION
test=# select bar('hello');
bar
---
hello
(1 row)
test=# select bar('hello "world"');
bar
-
hello world
(1 row)
test=#
> -Original Message-
> From: John DeSoi [mailto:[EMAIL PROTECTED]
> Sent: 12 January 2005 18:18
> To: Kieran Ashley
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Problems with Quotes
>
>
> On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:
>
>
>>I've tried using the replace() function to get rid of the ", but I
>>can't figure out how to use it without throwing an error. I tried
>>
>>replace(col_name, '\"', '')
>>
>>and several other permutations but to no avail, do I need to use
>>something like an ASCII character code in order to get rid of a quote?
>> If so which one, and if not, is there a better solution?
>>
>
>
>
> Try '"' as in
>
> select replace('this "is" it', '"', '');
>replace
>
> this is it
> (1 row)
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
> #
>
> The information contained in this email and any subsequent
> correspondence is private and is intended solely for the
> intended recipient(s). For those other than the intended
> recipient(s) any disclosure, copying, distribution, or any
> action taken or omitted to be taken in reliance on such
> information is prohibited and may be unlawful.
>
> #
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
--
Edmund Bacon <[EMAIL PROTECTED]>
#
The information contained in this email and any subsequent
correspondence is private and is intended solely for the
intended recipient(s). For those other than the intended
recipient(s) any disclosure, copying, distribution, or any
action taken or omitted to be taken in reliance on such
information is prohibited and may be unlawful.
#
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Problems with Quotes
On Jan 12, 2005, at 2:00 PM, Kieran Ashley wrote: I'm still not entirely sure _why_ that works, but it does... so I can go home now! ;) You should look at section 37.2.1 in the current docs. 8.0 has a new dollar quoting feature which makes this easier to deal with. http://www.postgresql.org/docs/7.4/static/plpgsql-development-tips.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
