Re: [SQL] Update counter when row SELECT'd ... ?
On Tue, 21 Mar 2006 04:33:22 +0100, Daniel CAUNE <[EMAIL PROTECTED]> wrote: I have a simple table: name, url, counter I want to be able to do: SELECT * FROM table ORDER BY counter limit 5; But, I want counter to be incremented by 1 *if* the row is included in that 5 ... so that those 5 basically move to the bottom of the list, and the next 5 come up ... I've checked CREATE TRIGGER, and that doesn't work 'ON SELECT' ... is there anything that I *can* do, other then fire back an UPDATE based on the records I've received? Thanks ... You could also have a "new" table (which gets new rows) and an "archive table", and move the rows from "new" to "archive" while selecting them, using a plpgsql set-returning function . ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] have you feel anything when you read this ?
PFC wrote: I wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. You wrote: Your client library should take care of escaping and de-escaping. We both agree as you see. Then i am asking: WHY should a client take care of de-escaping ? Why not to get his data unchanged ? I can understand why you say that for something as simple as a BYTEA, but if the value to be passed to the client is an ARRAY of geometric types or something Who said "array" ? I just want to restore _one byte_ from bytea storage. that's all. Exporting data from postgres in binary is only useful to C programmers Serious judgment ! Extremely seriuos... nonetheless C programmers could not do this. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
Scott Marlowe wrote: On Mon, 2006-03-20 at 02:06, Eugene E. wrote: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-19.html --- cut --- mysql no longer terminates data value display when it encounters a NUL byte. Instead, it displays NUL bytes as spaces. (Bug #16859) --- cut --- Everyone here realizes that this is a mysql CLIENT bug, not server side. i.e. if you're writing an application and request that binary text string, you'll get it with nuls in it, just like you put in. Now, I'm not sure that turning nulls into spaces is the best way to handle this in the client. In fact, I'm sure it's not. But this is not a server bug, it's a client bug. I was not sure about MySQL, thank you for your explaination. This ensures me that even MySQL server handles NUL-bytes properly regardless to client problems. ---(end of broadcast)--- TIP 1: 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
[SQL] Job queue, how would you implement this?
Dear all, I have implemented a job queue table where various apps can add jobs to the queue and other daemons then execute them. A basic producer-consumer pattern. Each tuple in the queue has the basic info such as job to be done, when it should be done, who should do it, a flag marking it completed. The consumer thus selects from the queue all jobs who scheduled in the past (<=CURRENT_TIMESTAMP) and are not flagged. Then flags them upon succesfull completion. I now wish to implement repeating scheduled jobs, like "every 5 minutes", "every morning at 7.00am" and so on. How could I do this? Any suggestions are welcome and if necessary I can provide you with more info. Thanks Alex. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Job queue, how would you implement this?
O Alexis Paul Bertolini έγραψε στις Mar 21, 2006 : > Dear all, > > I have implemented a job queue table where various apps can add jobs to > the queue and other daemons then execute them. A basic producer-consumer > pattern. Each tuple in the queue has the basic info such as job to be > done, when it should be done, who should do it, a flag marking it completed. > > The consumer thus selects from the queue all jobs who scheduled in the > past (<=CURRENT_TIMESTAMP) and are not flagged. Then flags them upon > succesfull completion. The consumer runs from the command line? (i.e. user-action driven?) > > I now wish to implement repeating scheduled jobs, like "every 5 > minutes", "every morning at 7.00am" and so on. How could I do this? I think the traditional UNIX "at" framework could be a good model (or replacement) of the simpler case, whereas the traditional UNIX "cron" framework could be a good model (or replacement) of the latter case. Your requirements photographically point to the above systems. Are you running on a UNIX machine? > > Any suggestions are welcome and if necessary I can provide you with more > info. > > Thanks > > Alex. > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Referential integrity broken (8.0.3), sub-select help
delete my email from the list Regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 8:29 PM To: pgsql-sql@postgresql.org Subject: [SQL] Referential integrity broken (8.0.3), sub-select help Hello, I've got 2 tables, "url" (U), and "bookmark" (B), with "bookmark" pointing to "url" via FK. Somehow I ended up with some rows in B referencing non-existent rows in U. This sounds super strange and dangerous to me, and it's not clear to me how/why PG let this happen. I'm using 8.0.3. Here are the table references I just mentioned: Table "bookmark": id SERIAL CONSTRAINT pk_bookmark_id PRIMARY KEY Table "url": url_id INTEGER CONSTRAINT fk_bookmark_id REFERENCES bookmark(id) Problem #1: Strange that PG allowed this to happen. Maybe my DDL above allows this to happen and needs to be tightened? I thought the above would ensure referential integrity, but maybe I need to specify something else? Problem #2: I'd like to find all rows in B that point to non-existent rows in U. I can do it with the following sub-select, I believe, but it's rather inefficient (EXPLAIN shows both tables would be sequentially scanned): SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u WHERE b.url_id=u.id); Is there a more efficient way to get the rows from "bookmark"? Thanks, Otis ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Power cut and performance problem
On Tue, 21 Mar 2006, Daniel Caune wrote: For example, the execution of the following query is fast as it used to be (gslog_event_id is the primary key on gslog_event): select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) while the following query is really slow (several minutes): select min(gslog_event_id) from gslog_event; (index on the primary key is taken) I'm not a hardware expert at all, but I supposed that the whole performance would be degraded when a problem occurs with RAID disks. Am I wrong? Could it be something else? Are there some tools that check the state of a PostgreSQL database? You would be correct, a hardware problem should manifest itself on both those queries. What is the explain analyze output of those two queries? It's possible you have a corrupt index on gslog_event. If that's the case, a reindex would likely remedy the problem. Is postgres logging any errors? -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: 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] Referential integrity broken (8.0.3), sub-select help
On Tue, 21 Mar 2006 [EMAIL PROTECTED] wrote: > I've got 2 tables, "url" (U), and "bookmark" (B), with "bookmark" pointing to > "url" via FK. That's not what your schema below has. Your fragment below has URL pointing to bookmark. > Somehow I ended up with some rows in B referencing non-existent rows in U. With the below, this is entirely possible, since you're only guaranteeing that URLs have valid bookmarks not the other way around. Are you sure the below is actually what you have? > This sounds super strange and dangerous to me, and it's not clear to me > how/why PG let this happen. > I'm using 8.0.3. > > Here are the table references I just mentioned: > > Table "bookmark": > id SERIAL > CONSTRAINT pk_bookmark_id PRIMARY KEY > > Table "url": > url_id INTEGER > CONSTRAINT fk_bookmark_id REFERENCES bookmark(id) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Power cut and performance problem
> BTW, I didn't complete my first thought above, which was to ask when you > last > vacuumed the DB, but then I saw that you were running autovac, so that > wasn't > likely the problem. > > BTW, if the problem is actually a raid array that is rebuilding, it should > be > (hopefullY) fixed by tomorrow morning. > An administrator is checking the raid status this morning. Anyway, I did some tests and it seems that some results are weird. For example, the execution of the following query is fast as it used to be (gslog_event_id is the primary key on gslog_event): select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) while the following query is really slow (several minutes): select min(gslog_event_id) from gslog_event; (index on the primary key is taken) I'm not a hardware expert at all, but I supposed that the whole performance would be degraded when a problem occurs with RAID disks. Am I wrong? Could it be something else? Are there some tools that check the state of a PostgreSQL database? -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Referential integrity broken (8.0.3), sub-select help
Hello, I've got 2 tables, "url" (U), and "bookmark" (B), with "bookmark" pointing to "url" via FK. Somehow I ended up with some rows in B referencing non-existent rows in U. This sounds super strange and dangerous to me, and it's not clear to me how/why PG let this happen. I'm using 8.0.3. Here are the table references I just mentioned: Table "bookmark": id SERIAL CONSTRAINT pk_bookmark_id PRIMARY KEY Table "url": url_id INTEGER CONSTRAINT fk_bookmark_id REFERENCES bookmark(id) Problem #1: Strange that PG allowed this to happen. Maybe my DDL above allows this to happen and needs to be tightened? I thought the above would ensure referential integrity, but maybe I need to specify something else? Problem #2: I'd like to find all rows in B that point to non-existent rows in U. I can do it with the following sub-select, I believe, but it's rather inefficient (EXPLAIN shows both tables would be sequentially scanned): SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u WHERE b.url_id=u.id); Is there a more efficient way to get the rows from "bookmark"? Thanks, Otis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Power cut and performance problem
> > For example, the execution of the following query is fast as it used to > > be (gslog_event_id is the primary key on gslog_event): > > > > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) > > > > > > while the following query is really slow (several minutes): > > > > select min(gslog_event_id) from gslog_event; (index on the primary key > > is taken) > > > > > > I'm not a hardware expert at all, but I supposed that the whole > > performance would be degraded when a problem occurs with RAID disks. Am > > I wrong? Could it be something else? Are there some tools that check > > the state of a PostgreSQL database? > > You would be correct, a hardware problem should manifest itself on both > those > queries. What is the explain analyze output of those two queries? It's > possible you have a corrupt index on gslog_event. If that's the case, a > reindex would likely remedy the problem. Is postgres logging any errors? > The UNIX administrator confirms that this is not a RAID problem. I truncate my table. This is not the most efficient way, but it's okay because this is a data stage table. It seems that it fixes my performance problem. As you said, perhaps the problem was more related to index corruption. Truncating data and inserting new data recreate the index and therefore fix the problem. Thanks, -- Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] plpqsql and RETURN NEXT requires a LOOP?
Title: plpqsql and RETURN NEXT requires a LOOP?
From my reading of 36.7.1 Returning from a Function
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
it appears that RETURN NEXT in a plpgsql function requires you to loop through the result set. Is this correct? If so, I would be happy to post this example to the interactive docs (which could use a RETURN NEXT example), but wanted to make sure that I wasn’t missing something more elegant or more efficient.
Best Regards,
Robert Davidson
-
CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO test VALUES ('a', 1);
INSERT INTO test VALUES ('a', 2);
INSERT INTO test VALUES ('b', 5);
INSERT INTO test VALUES ('b', 6);
CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM test WHERE textcol = pText LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;
SELECT * FROM ReturnNexting('a');
Re: [SQL] plpqsql and RETURN NEXT requires a LOOP?
In general, to do anything useful with RETURN NEXT you need a loop. However,
it doesn't need to be a loop over another resultset: you can do a computation
in a loop, returning values as you go.
Excuse the outlook-ism.
-Owen
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Davidson, Robert
Sent: Tuesday, March 21, 2006 9:51 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] plpqsql and RETURN NEXT requires a LOOP?
From my reading of 36.7.1 Returning from a Function
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
it appears that RETURN NEXT in a plpgsql function requires you to loop through
the result set. Is this correct? If so, I would be happy to post this example
to the interactive docs (which could use a RETURN NEXT example), but wanted to
make sure that I wasn't missing something more elegant or more efficient.
Best Regards,
Robert Davidson
-
CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO test VALUES ('a', 1);
INSERT INTO test VALUES ('a', 2);
INSERT INTO test VALUES ('b', 5);
INSERT INTO test VALUES ('b', 6);
CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM test WHERE textcol = pText LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;
SELECT * FROM ReturnNexting('a');
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Referential integrity broken (8.0.3), sub-select help
Hi, I mistakenly swapped the tables in my email. Here they are, corrected: Table "url": id SERIAL CONSTRAINT pk_url_id PRIMARY KEY Table "bookmark": url_id INTEGER CONSTRAINT fk_url_id REFERENCES url(id) I see my questions got chopped off from this email below, so let me restate them: Problem #1: Strange that PG allowed this to happen. Maybe my DDL above allows this to happen and needs to be tightened? I thought the above would ensure referential integrity, but maybe I need to specify something else? Problem #2: I'd like to find all rows in B that point to non-existent rows in U. I can do it with the following sub-select, I believe, but it's rather inefficient (EXPLAIN shows both tables would be sequentially scanned): SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u WHERE b.url_id=u.id); Is there a more efficient way to get the rows from "bookmark"? Thanks, Otis - Original Message From: Stephan Szabo <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Sent: Tuesday, March 21, 2006 10:08:38 AM Subject: Re: [SQL] Referential integrity broken (8.0.3), sub-select help On Tue, 21 Mar 2006 [EMAIL PROTECTED] wrote: > I've got 2 tables, "url" (U), and "bookmark" (B), with "bookmark" pointing to > "url" via FK. That's not what your schema below has. Your fragment below has URL pointing to bookmark. > Somehow I ended up with some rows in B referencing non-existent rows in U. With the below, this is entirely possible, since you're only guaranteeing that URLs have valid bookmarks not the other way around. Are you sure the below is actually what you have? > This sounds super strange and dangerous to me, and it's not clear to me > how/why PG let this happen. > I'm using 8.0.3. > > Here are the table references I just mentioned: > > Table "bookmark": > id SERIAL > CONSTRAINT pk_bookmark_id PRIMARY KEY > > Table "url": > url_id INTEGER > CONSTRAINT fk_bookmark_id REFERENCES bookmark(id) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Referential integrity broken (8.0.3), sub-select help
On Tue, 21 Mar 2006 [EMAIL PROTECTED] wrote: > I mistakenly swapped the tables in my email. Here they are, corrected: > > Table "url": > id SERIAL > CONSTRAINT pk_url_id PRIMARY KEY > > Table "bookmark": > url_id INTEGER >CONSTRAINT fk_url_id REFERENCES url(id) > > I see my questions got chopped off from this email below, so let me restate > them: > > > Problem #1: Strange that PG allowed this to happen. Maybe my DDL above > allows this to happen and needs to be tightened? I thought the above > would ensure referential integrity, but maybe I need to specify > something else? That seems like it should have worked. I don't know of any cases that'd fail without referential actions (there are some cases with actions and before triggers or rules), so if you have any leads, that'd be useful. > Problem #2: I'd like to find all rows in B that point to non-existent > rows in U. I can do it with the following sub-select, I believe, but > it's rather inefficient (EXPLAIN shows both tables would be sequentially > scanned): > > SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u > WHERE b.url_id=u.id); > > Is there a more efficient way to get the rows from "bookmark"? I think something like the following would work SELECT * FROM bookmark WHERE url_id NOT IN (SELECT id FROM url u); Raising work_mem may help get a better plan as well. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Referential integrity broken (8.0.3), sub-select help
On Tue, 2006-03-21 at 08:58, [EMAIL PROTECTED] wrote: > Hello, > > I've got 2 tables, "url" (U), and "bookmark" (B), with "bookmark" pointing to > "url" via FK. > Somehow I ended up with some rows in B referencing non-existent rows in U. > This sounds super strange and dangerous to me, and it's not clear to me > how/why PG let this happen. > I'm using 8.0.3. > > Here are the table references I just mentioned: > > Table "bookmark": > id SERIAL > CONSTRAINT pk_bookmark_id PRIMARY KEY > > Table "url": > url_id INTEGER > CONSTRAINT fk_bookmark_id REFERENCES bookmark(id) > > > Problem #1: Strange that PG allowed this to happen. Maybe my DDL above > allows this to happen and needs to be tightened? I thought the above would > ensure referential integrity, but maybe I need to specify something else? Assuming you didn't do something like turning off all triggers at some point, the other common cause of this kind of thing is bad hardware (CPU, memory, hard drive, etc...) so test your hardware. Any machine going into production as a database server should be heavily tested to ensure that it has good hardware. No database management program can be expected to overcome broken hardware or OSes. Good tools for testing are memtest86 and doing a fdisk with the badblocks option (in linux, not sure what the name is in bsd, but I'm sure it has some kind of block tester in there somewhere.) You can also write your own scripts to test a drive by writing the same semi-random byte sequence to the drive, filling it up, then reading it back and comparing them. All zeros and all ones is a good test, and there are patterns that tend to show problems. Generally, most drives that have problems will show them rather quickly in testing, with bad blocks flying by by the hundreds. But sometimes, it's just one block causing a problem. > Problem #2: I'd like to find all rows in B that point to non-existent rows in > U. I can do it with the following sub-select, I believe, but it's rather > inefficient (EXPLAIN shows both tables would be sequentially scanned): > > SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u > WHERE b.url_id=u.id); Generally a left join with not null in the where clause is more efficient: select a.id from table1 a left join table2 b on (a.id=b.aid) where b.aid is null; will show you all the rows in table1 that have no match in table2 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Using a parameter in Interval
Title: Using a parameter in Interval No matter how I try to concatenate, I can’t seem to get a parameter to be used by INTERVAL in a function: CREATE OR REPLACE FUNCTION Testing(TrailingWeeks int) RETURNS date AS $$ BEGIN RETURN current_date - INTERVAL (CAST(TrailingWeeks AS varchar) || ' weeks'); END; $$ LANGUAGE plpgsql; --select * from testing(1); ERROR: syntax error at or near "CAST" at character 34 QUERY: SELECT current_date - INTERVAL (CAST( $1 AS varchar) || ' weeks') CONTEXT: SQL statement in PL/PgSQL function "testing" near line 2 I have tried concatenating it as a declared variable (with and without apostrophes) 1 weeks And ‘1 weeks’ With no success. Any tips? Many thanks, Robert
Re: [SQL] Using a parameter in Interval
On Mar 22, 2006, at 9:52 , Davidson, Robert wrote:
ERROR: syntax error at or near "CAST" at character 34
QUERY: SELECT current_date - INTERVAL (CAST( $1 AS varchar) || '
weeks')
CONTEXT: SQL statement in PL/PgSQL function "testing" near line 2
The generally recommended way is something like:
test=# select '4'::integer * interval '1 week';
?column?
--
28 days
(1 row)
or the more SQL compliant:
test=# select cast('4' as integer) * interval '1 week';
?column?
--
28 days
(1 row)
Does that work for you?
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Using a parameter in Interval
Here's one I used to convert an int to an interval in another project: CREATE OR REPLACE FUNCTION to_interval (sec INTEGER) RETURNS INTERVAL AS $$ BEGIN RETURN (sec || ' seconds')::INTERVAL; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; select to_interval (5); to_interval - 00:00:05 (1 row) You should be able to replace ' seconds' with ' weeks' just fine. Excuse the outlook-ism, -Owen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Davidson, Robert Sent: Tuesday, March 21, 2006 4:53 PM To: pgsql-sql@postgresql.org Subject: [SQL] Using a parameter in Interval No matter how I try to concatenate, I can't seem to get a parameter to be used by INTERVAL in a function: CREATE OR REPLACE FUNCTION Testing(TrailingWeeks int) RETURNS date AS $$ BEGIN RETURN current_date - INTERVAL (CAST(TrailingWeeks AS varchar) || ' weeks'); END; $$ LANGUAGE plpgsql; --select * from testing(1); ERROR: syntax error at or near "CAST" at character 34 QUERY: SELECT current_date - INTERVAL (CAST( $1 AS varchar) || ' weeks') CONTEXT: SQL statement in PL/PgSQL function "testing" near line 2 I have tried concatenating it as a declared variable (with and without apostrophes) 1 weeks And '1 weeks' With no success. Any tips? Many thanks, Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Using a parameter in Interval
On Tue, 21 Mar 2006, Davidson, Robert wrote: > No matter how I try to concatenate, I can't seem to get a parameter to be > used by INTERVAL in a function: > > CREATE OR REPLACE FUNCTION Testing(TrailingWeeks int) RETURNS date AS $$ > BEGIN > RETURN current_date - INTERVAL (CAST(TrailingWeeks AS varchar) || ' > weeks'); > END; > $$ LANGUAGE plpgsql; > > --select * from testing(1); > > ERROR: syntax error at or near "CAST" at character 34 > QUERY: SELECT current_date - INTERVAL (CAST( $1 AS varchar) || ' weeks') > CONTEXT: SQL statement in PL/PgSQL function "testing" near line 2 > > I have tried concatenating it as a declared variable (with and without > apostrophes) > 1 weeks > And > '1 weeks' > > With no success. Any tips? You'd need a cast, not INTERVAL foo as the latter is for interval literals (and CAST... is not a valid interval literal even if the output of the concatenation looks like an interval literal). I'd go with the suggestion of using int * interval instead of concatenation in any case. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
On Mon, Mar 20, 2006 at 17:40:03 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > > Indeed. I wonder whether we shouldn't tweak the SQL string literal > parser to reject \000, because AFAICS that isn't going to do anything > useful for any datatype, and it leads to what are at best questionable > results. (bytea's processing of \000 happens somewhere further > downstream, and wouldn't be affected.) I think that makes sense. That character is effectively not allowed in text, so it shouldn't be accepted as input. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Power cut and performance problem
unsubscribe Regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Caune Sent: Tuesday, March 21, 2006 9:44 PM To: Jeff Frost Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Power cut and performance problem > > For example, the execution of the following query is fast as it used to > > be (gslog_event_id is the primary key on gslog_event): > > > > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) > > > > > > while the following query is really slow (several minutes): > > > > select min(gslog_event_id) from gslog_event; (index on the primary key > > is taken) > > > > > > I'm not a hardware expert at all, but I supposed that the whole > > performance would be degraded when a problem occurs with RAID disks. Am > > I wrong? Could it be something else? Are there some tools that check > > the state of a PostgreSQL database? > > You would be correct, a hardware problem should manifest itself on both > those > queries. What is the explain analyze output of those two queries? It's > possible you have a corrupt index on gslog_event. If that's the case, a > reindex would likely remedy the problem. Is postgres logging any errors? > The UNIX administrator confirms that this is not a RAID problem. I truncate my table. This is not the most efficient way, but it's okay because this is a data stage table. It seems that it fixes my performance problem. As you said, perhaps the problem was more related to index corruption. Truncating data and inserting new data recreate the index and therefore fix the problem. Thanks, -- Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match <> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Power cut and performance problem
unsubscribe Regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jeff Frost Sent: Tuesday, March 21, 2006 9:19 PM To: Daniel Caune Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Power cut and performance problem On Tue, 21 Mar 2006, Daniel Caune wrote: > For example, the execution of the following query is fast as it used to > be (gslog_event_id is the primary key on gslog_event): > > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) > > > while the following query is really slow (several minutes): > > select min(gslog_event_id) from gslog_event; (index on the primary key > is taken) > > > I'm not a hardware expert at all, but I supposed that the whole > performance would be degraded when a problem occurs with RAID disks. Am > I wrong? Could it be something else? Are there some tools that check > the state of a PostgreSQL database? You would be correct, a hardware problem should manifest itself on both those queries. What is the explain analyze output of those two queries? It's possible you have a corrupt index on gslog_event. If that's the case, a reindex would likely remedy the problem. Is postgres logging any errors? -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 1: 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
