[GENERAL] How to use pg_stat_activity correctly ?
Hi All, I try to query on pg_stat_activity but the returned result is only like this: teramedv2= select * from pg_stat_activity where datname='teramedv2'; datid | datname | procpid | usesysid | usename | current_query | query_start ---+---+-+--+-+---+- 17178 | teramedv2 | 32727 | 103 | test| | 17178 | teramedv2 | 26848 | 103 | test| | 17178 | teramedv2 | 563 | 103 | test| | 17178 | teramedv2 | 26799 | 103 | test| | (4 rows) the field current_query and query_start seems to me always null ... I try to do a long run select query on the other console and see the returned result from pg_stat_activity but the returned result is only new row with datid,datname,procpid,usename fields is filled with the correct value but current_query and query_start fields is always null. Is this an expected result ? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to use pg_stat_activity correctly ?
am 10.02.2006, um 15:30:02 +0700 mailte Luki Rustianto folgendes: Hi All, I try to query on pg_stat_activity but the returned result is only like this: teramedv2= select * from pg_stat_activity where datname='teramedv2'; datid | datname | procpid | usesysid | usename | current_query | query_start ---+---+-+--+-+---+- 17178 | teramedv2 | 32727 | 103 | test| | 17178 | teramedv2 | 26848 | 103 | test| | 17178 | teramedv2 | 563 | 103 | test| | 17178 | teramedv2 | 26799 | 103 | test| | (4 rows) the field current_query and query_start seems to me always null ... I stats_command_string = on (postgresql.conf) HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] implicit cast of empty string to timestamp
Is there anything I can do to make postgres allow an implicit cast of an
empty string to a timestamp, so that a badly behaved application can do:
INSERT INTO SomeTable (timestampfield) VALUES ('')
Where timestampfield is of type typestamp. ?
From what I understand of the 'CREATE CAST' command, I can't just create
a cast that only kicks in on empty strings whilst leaving casts of other
strings as is...
Thanks
James
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] implicit cast of empty string to timestamp
am 10.02.2006, um 20:22:57 +1100 mailte James Harper folgendes:
Is there anything I can do to make postgres allow an implicit cast of an
empty string to a timestamp, so that a badly behaved application can do:
test=# select * from t1;
x
-
foo
(2 rows)
test=# select case when x='' then to_char(now(),'DD-MM- HH:MM:SS')
else x end from t1;
x
-
10-02-2006 10:02:37
foo
(2 rows)
INSERT INTO SomeTable (timestampfield) VALUES ('')
You can't insert a empty string into a timestamp, IIRC.
test=# create table t2 (id int, ts timestamp);
CREATE TABLE
test=# insert into t2 values (1, '');
ERROR: invalid input syntax for type timestamp:
test=# insert into t2 values (1, NULL);
INSERT 0 1
test=# insert into t2 values (2, now());
INSERT 0 1
test=# select* from t2;
id | ts
+
1 |
2 | 2006-02-10 10:34:33.046152
(2 rows)
test=# select coalesce(ts, now()) from t2;
coalesce
---
2006-02-10 10:35:03.426692+01
2006-02-10 10:34:33.046152+01
(2 rows)
HTH, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===Schollglas Unternehmensgruppe===
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] implicit cast of empty string to timestamp
am 10.02.2006, um 20:22:57 +1100 mailte James Harper folgendes:
Is there anything I can do to make postgres allow an implicit cast
of an
empty string to a timestamp, so that a badly behaved application can
do:
INSERT INTO SomeTable (timestampfield) VALUES ('')
You can't insert a empty string into a timestamp, IIRC.
No, and if someone tries I want to put a NULL in there.
I had hoped the following might work (syntax is from memory):
CREATE SCHEMA fnord;
SET search_path TO fnord;
CREATE FUNCTION fnord.timestamp (text) RETURNS timestamp LANGUAGE SQL AS
$$
SELECT pg_catalog.timestamp('20010101') $$;
SELECT CAST(text '20060101' AS timestamp);
SELECT CAST(text '' AS timestamp);
I had hoped that my 'timestamp' function would have overridden the
unqualified function call in the cast, but it was not to be :(
Curiously tho, \df didn't even acknowledge the 'timestamp' function in
the database, even though it was in the pg_proc's table.
Oh well.
James
---(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: [GENERAL] implicit cast of empty string to timestamp
How much trouble am I going to get into by modifying the pg_cast table
to call my function instead?
I created this function:
CREATE OR REPLACE FUNCTION pg_catalog.mssql_timestamp (text)
RETURNS timestamp
LANGUAGE SQL
AS $$
SELECT CASE WHEN $1 = '' THEN NULL ELSE pg_catalog.timestamp($1) END
$$;
And then updated the pg_cast table with this statement:
UPDATE pg_cast
SET castfunc = (SELECT Oid FROM pg_proc WHERE proname =
'mssql_timestamp') WHERE castfunc = (SELECT Oid FROM pg_proc WHERE
prosrc = 'text_timestamp')
Now the following work:
SELECT CAST(text '' AS timestamp);
SELECT CAST(char '' AS timestamp);
SELECT CAST(varchar '' AS timestamp);
But this doesn't yet:
SELECT CAST('' AS timestamp);
I can probably get it working, but is it really a good idea to be
fiddling with the pg_cast table like that?
Thanks
James
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of James Harper
Sent: Friday, 10 February 2006 20:23
To: pgsql-general@postgresql.org
Subject: [GENERAL] implicit cast of empty string to timestamp
Is there anything I can do to make postgres allow an implicit cast of
an
empty string to a timestamp, so that a badly behaved application can
do:
INSERT INTO SomeTable (timestampfield) VALUES ('')
Where timestampfield is of type typestamp. ?
From what I understand of the 'CREATE CAST' command, I can't just
create
a cast that only kicks in on empty strings whilst leaving casts of
other
strings as is...
Thanks
James
---(end of
broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] r trim of characters other than space
You need to use two single quotes around ^ (like ''^'') or use the dollar quoting approach. http://www.postgresql.org/docs/8.1/static/plpgsql-development-tips.html On 2/10/06, surabhi.ahuja [EMAIL PROTECTED] wrote: but how should i do it within a stored procedure something like: CREATE OR REPLACE FUNCTION insert(varchar(65),varchar(65),date,varchar(256)) RETURNS retval AS' DECLARE patName text; BEGIN patName := trim($1); select trim(trailing `^` from patName) INTO patName; trim(patName); this seems to be giving syntax error thanks, regards Surabhi From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thu 2/9/2006 12:49 PM To: surabhi.ahuja Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] r trim of characters other than space *** Your mail has been scanned by iiitb VirusWall. ***-*** surabhi.ahuja [EMAIL PROTECTED] writes: i want to make the following check, if it is having carets in the end, then those carets be removed. so if i get a string like abc def i should be able to get abc def Per SQL spec: regression=# select trim(trailing '^' from 'abc def'); rtrim - abc def (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] backslash separated values for a field
Hi, i have a field "x" which can be of the form abc\def\123 i.e. they are back slash separated fields is there any operator in postgres which can process a query like give me all xs where x is abc is ther any opeartor like contain, so i can form a query of the form: select * from table_name where x contains 'abc'; thanks, regards Surabhi
Re: [GENERAL] backslash separated values for a field
On Friday 10 February 2006 12:54, surabhi.ahuja wrote: is ther any opeartor like contain, so i can form a query of the form: select * from table_name where x contains 'abc'; SELECT * FROM table_name WHERE x LIKE '%abc%'; You should probably avoid using backslash as a separator because of its special meaning as an escape character. That may bite you in several ways. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Sequences/defaults and pg_dump
Bruno Wolff III [EMAIL PROTECTED] writes: On Tue, Feb 07, 2006 at 15:28:31 +0300, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: The real situation would be as the following. I want to use some algorithm to hide real number of registered users in my table user. So, I don't want to use simple sequence, when every new registered user in my system can guess what is the number of registered users simply observing his ID. So, I use following (N and M are said to be relatively prime.) The above method isn't very secure. You might be better off using a block cipher in counter mode, depending on how badly you want to keep the number of users secret. Even that won't be foolproof as the users might cooperate with each other to estimate how many of them there are. Or, just start your sequence counting at 100. Or use bigint and start it at a billion. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Dropping a database that does not exist
Hi, I've been getting duplicate databases within my server. Dropping one of them works, but when I try to drop the other, psql will say that the database does not exist. For example: db1 db1 db2 db3 db4 db4 db5 Running DROP DATABASE db1 for the first time works and I'm left with: db1 db2 db3 db4 db4 db5 Attempting to run DROP DATABASE db1 again will just give me FATAL: database db1 does not exist Same scenario for db4. I could ignore the error, but because of the duplicate database, I cannot make a dump of the server. Any one has any ideas why this is happening and how I can solve it? A similar thing happened previously, but it was with tables within a database. The only way we solved that was by dropping the database and recreating the structure, which wasn't the ideal way I wanted to use. I've got PostgreSQL 8.0.4 running on SuSE 9.3. Regards, Shiming ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Database Comparison tool?
[ please refrain from top-quoting, and try not to repeat the entire previous post; we do have archives you know ] Rick Gigger [EMAIL PROTECTED] writes: On Feb 9, 2006, at 1:03 PM, Philippe Ferreira wrote: Do a pgdump of both databases, and use the diff tool to compare the two generated files ! Is the ordering guaranteed to be the same on both boxes if you do this? For recent pg_dumps (since 8.0 I think) it should be the same as long as both DBs are actually identical. If there are different interobject dependencies, that might force different dump orders. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] implicit cast of empty string to timestamp
James Harper [EMAIL PROTECTED] writes: How much trouble am I going to get into by modifying the pg_cast table to call my function instead? You can doubtless hack it to work if you slash-and-burn hard enough. The question is why don't you fix your buggy application instead ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Dropping a database that does not exist
Tham Shiming [EMAIL PROTECTED] writes: I've been getting duplicate databases within my server. What exactly makes you think you have that? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is there a way to limit CPU usage per user
On 2/9/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Feb 10, 2006 at 11:30:04AM +0700, Luki Rustianto wrote: So how can we terminate such a long running query ? The idea is to make a crontab to periodicaly do a job to search a typical SELECT * FROM bigtable query who has run for some hours then to terminate them... Are you familiar with the statement_timeout setting? test= SET statement_timeout TO 1000; -- milliseconds SET test= SELECT some long-running query; ERROR: canceling statement due to statement timeout If that won't work then please explain in general terms what problem you're trying to solve, not how you're trying to solve it. I am also interested in being able to terminate *certain* long running queries. I didn't want to use statement_timeout because there are some queries that must run for a long time - in our case some queries that create summary tables. Other long running queries should be killed. I was able to get more granular by creating a kill_pid function in an untrusted language and selectively kill ad-hoc queries. I'd suggest having your non-killable queries run as one user. That way you can do something like SELECT * FROM pg_stat_activity WHERE usename !='some_special_user' AND query_start NOW()-INTERVAL '30 minutes'; And then if you were very brave - you could kill those queries off. I may get flamed for this, but this is how I have killed errant processes. I suspect you should pause for a long time before try to install plperlu. CREATE FUNCTION kill_pid(INTEGER) RETURNS TEXT AS $BODY$ my ($pid) = @_; my $out=system(kill -TERM $pid); return $out; $BODY$ language plperlu; REVOKE ALL ON FUNCTION kill_pid(INTEGER) FROM public; ---(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: [GENERAL] Is there a way to limit CPU usage per user
On Fri, 2006-02-10 at 09:31, Tony Wasson wrote: On 2/9/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Feb 10, 2006 at 11:30:04AM +0700, Luki Rustianto wrote: So how can we terminate such a long running query ? The idea is to make a crontab to periodicaly do a job to search a typical SELECT * FROM bigtable query who has run for some hours then to terminate them... Are you familiar with the statement_timeout setting? test= SET statement_timeout TO 1000; -- milliseconds SET test= SELECT some long-running query; ERROR: canceling statement due to statement timeout If that won't work then please explain in general terms what problem you're trying to solve, not how you're trying to solve it. I am also interested in being able to terminate *certain* long running queries. I didn't want to use statement_timeout because there are some queries that must run for a long time - in our case some queries that create summary tables. Other long running queries should be killed. I was able to get more granular by creating a kill_pid function in an untrusted language and selectively kill ad-hoc queries. I'd suggest having your non-killable queries run as one user. Could setting a global statement_timeout of say 3 milliseconds and then having known long running queries set a different statement_timeout on their own connections before running their query work? ---(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: [GENERAL] Is there a way to limit CPU usage per user
Tony Wasson [EMAIL PROTECTED] writes: I am also interested in being able to terminate *certain* long running queries. I didn't want to use statement_timeout because there are some queries that must run for a long time - in our case some queries that create summary tables. Other long running queries should be killed. You do know that statement_timeout can be changed freely via SET, right? One way to attack this would be for the clients that are issuing known long-running queries to do SET statement_timeout to boost up their allowed runtime. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is there a way to limit CPU usage per user
You do know that statement_timeout can be changed freely via SET, right? One way to attack this would be for the clients that are issuing known long-running queries to do SET statement_timeout to boost up their allowed runtime. How does this apply to autovacuum's long running vacuum commands ? Cause I have one table where the last vacuum took 15 hours :-) It's true that now after migration to 8.1 I do the vacuuming manually for that one and disabled it for autovacuum, but I still could have other tables which would vacuum in more than 5 minutes (that would be my statement_timeout for ordinary processes). Cheers, Csaba. ---(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: [GENERAL] Is there a way to limit CPU usage per user
Csaba Nagy [EMAIL PROTECTED] writes: How does this apply to autovacuum's long running vacuum commands ? Cause I have one table where the last vacuum took 15 hours :-) [ Checks code... ] No, statement_timeout only applies to commands arriving from an interactive client. This *would* be a hazard for the contrib version of autovacuum, but not for the 8.1 integrated version. (If you're using contrib autovacuum, it'd be a good idea to do ALTER USER SET statement_timeout = 0 for the user it runs as.) regards, tom lane ---(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
[GENERAL] Tool
I am developing a database which needs testing prior to developing the host program. I am looking for an easy-to-learn tool. I want to develop an interfacethat I can use toenter and display information in a similar fashion to how a user would operate the database. A GUI tool would be ideal. I am running Postgresql v 8.0 on Windows XP. Bob Pawley
Re: [GENERAL] Dropping a database that does not exist
I don't think you have duplicate databases - that would be the first time I heard that postgreSQL supports that. Are you sure you didn't create the database with a name that just doesn't print? I.e. a simple createdb test works. do another createdb test (note the space) works too, but when you list the db's you'll see test twice, although the one of them is created with a space at the end, so you can't connect to it or drop it unless you use quotes. Did you use some kind of tool that may have a done this? Most tools use quotes when creating sql statements, so if you accidentially added a space in the dialog you end up with a scenario like you describe. On Friday 10 February 2006 01:42, Tham Shiming wrote: Hi, I've been getting duplicate databases within my server. Dropping one of them works, but when I try to drop the other, psql will say that the database does not exist. For example: db1 db1 db2 db3 db4 db4 db5 Running DROP DATABASE db1 for the first time works and I'm left with: db1 db2 db3 db4 db4 db5 Attempting to run DROP DATABASE db1 again will just give me FATAL: database db1 does not exist Same scenario for db4. I could ignore the error, but because of the duplicate database, I cannot make a dump of the server. Any one has any ideas why this is happening and how I can solve it? A similar thing happened previously, but it was with tables within a database. The only way we solved that was by dropping the database and recreating the structure, which wasn't the ideal way I wanted to use. I've got PostgreSQL 8.0.4 running on SuSE 9.3. Regards, Shiming ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL]
Hello,I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on!If someone could give me a clue that wold be greatly appreciated thanksJC What are the most popular cars? Find out at Yahoo! Autos
Re: [GENERAL] Tool
Thanks I am using pgadmin for development. I'm looking for a tool with which I can develop an interface that will be used for entering and accessing information in context with the user tasks. Bob - Original Message - From: Philippe Ferreira [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Postgresql pgsql-general@postgresql.org Sent: Friday, February 10, 2006 9:30 AM Subject: Re: [GENERAL] Tool I am developing a database which needs testing prior to developing the host program. I am looking for an easy-to-learn tool. I want to develop an interface that I can use to enter and display information in a similar fashion to how a user would operate the database. A GUI tool would be ideal. I am running Postgresql v 8.0 on Windows XP. Hi, If you need a GUI to manage your Postgresql databases, you have PgAdmin... Philippe Ferreira. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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
[GENERAL] Sequence skipping values
Hello,I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on!If someone could give me a clue that wold be greatly appreciated thanksJC Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
Re: [GENERAL] Sequence skipping values
You likely roll back some transactions which insert. The sequence values don't get rolled back, once allocated, it's gone, even if you won't keep it. For concurrency/performance reasons sequence values are not transactional. Cheers, Csaba. On Fri, 2006-02-10 at 18:44, Jean-Christophe Roux wrote: Hello, I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on! If someone could give me a clue that wold be greatly appreciated thanks JC __ Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Sequence skipping values
On Fri, 2006-02-10 at 11:44, Jean-Christophe Roux wrote: Hello, I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on! If someone could give me a clue that wold be greatly appreciated thanks Have you got some kind of trigger or rule on this table that could be causing such behaviour? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Sequence skipping values
Jean-Christophe Roux wrote: Hello, I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on! If someone could give me a clue that wold be greatly appreciated thanks JC Any rules, triggers, etc. involved? How are the inserts done (insert one record into the table)? What relationships does the table have to any other tables? Anything else accessing that sequence? There is no guarantee that a sequence will be contiguous. For example, begin...insert into...rollback will not reset the sequence as other transactions could have incremented the sequence. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Tool
This looks quite interseting. Thanks Wes Bob - Original Message - From: Wes Williams To: 'Bob Pawley' ; 'Postgresql' Sent: Friday, February 10, 2006 9:14 AM Subject: RE: [GENERAL] Tool You may wish to consider creating a web interface GUI. For example: Sun's free Java Studio Creator2 http://developers.sun.com/prodtech/javatools/jscreator/index.jsp Alittletolearnupfront,butthentheGUIgoodnesscomesthrough. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Bob PawleySent: Friday, February 10, 2006 12:02 PMTo: PostgresqlSubject: [GENERAL] Tool I am developing a database which needs testing prior to developing the host program. I am looking for an easy-to-learn tool. I want to develop an interfacethat I can use toenter and display information in a similar fashion to how a user would operate the database. A GUI tool would be ideal. I am running Postgresql v 8.0 on Windows XP. Bob Pawley
Re: [GENERAL] distinct not working in a multiple join
On Thu, Feb 09, 2006 at 09:51:13PM -0500, Stephen Frost wrote: * David Rio Deiros ([EMAIL PROTECTED]) wrote: Now I have to redefine my query because I want to get the second output but keeping the group_id. Ideas and suggestions are welcome. You might want to look at 'distinct on'. I am reading about it right now. It sounds that it will make it. Thanks guys for your help, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Sequence skipping values
On Fri, Feb 10, 2006 at 09:57:31AM -0800, Steve Crawford wrote: There is no guarantee that a sequence will be contiguous. For example, begin...insert into...rollback will not reset the sequence as other transactions could have incremented the sequence. Also, deleting records won't modify the sequence (unless a trigger does so; the point is that the delete itself doesn't). If you delete the latest records in the table, the sequence will continue from where it was after the last insert (more properly, after the last call to nextval), not from the new highest value in the table. Other possibilities, though less likely, are that the sequence has non-default CACHE or INCREMENT values. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tool
I am developing a database which needs testing prior to developing the host program. I am looking for an easy-to-learn tool. I want to develop an interface that I can use to enter and display information in a similar fashion to how a user would operate the database. A GUI tool would be ideal. I am running Postgresql v 8.0 on Windows XP. If you are running on Windows I would recommend you use PG Lightning Admin, it has a MUCH better user experience that PG Admin and has a form view for dataset records, not to mention you can print, use code completion etc. http://www.amsoftwaredesign.com http://www.amsoftwaredesign.com/lightning_admin.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tool
Thanks very much Tony. I'll give it a go. Bob - Original Message - From: Tony Caduto [EMAIL PROTECTED] Cc: Postgresql pgsql-general@postgresql.org Sent: Friday, February 10, 2006 11:42 AM Subject: Re: [GENERAL] Tool I am developing a database which needs testing prior to developing the host program. I am looking for an easy-to-learn tool. I want to develop an interface that I can use to enter and display information in a similar fashion to how a user would operate the database. A GUI tool would be ideal. I am running Postgresql v 8.0 on Windows XP. If you are running on Windows I would recommend you use PG Lightning Admin, it has a MUCH better user experience that PG Admin and has a form view for dataset records, not to mention you can print, use code completion etc. http://www.amsoftwaredesign.com http://www.amsoftwaredesign.com/lightning_admin.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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: [GENERAL] Tool
EMS' Postgresql Manager Lite is also pretty intuitive. http://www.sqlmanager.net/products/postgresql/manager/download -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bob Pawley Sent: Friday, February 10, 2006 1:49 PM To: Tony Caduto Cc: Postgresql Subject: Re: [GENERAL] Tool Thanks very much Tony. I'll give it a go. Bob - Original Message - From: Tony Caduto [EMAIL PROTECTED] Cc: Postgresql pgsql-general@postgresql.org Sent: Friday, February 10, 2006 11:42 AM Subject: Re: [GENERAL] Tool I am developing a database which needs testing prior to developing the host program. I am looking for an easy-to-learn tool. I want to develop an interface that I can use to enter and display information in a similar fashion to how a user would operate the database. A GUI tool would be ideal. I am running Postgresql v 8.0 on Windows XP. If you are running on Windows I would recommend you use PG Lightning Admin, it has a MUCH better user experience that PG Admin and has a form view for dataset records, not to mention you can print, use code completion etc. http://www.amsoftwaredesign.com http://www.amsoftwaredesign.com/lightning_admin.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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 -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.4/255 - Release Date: 2/9/2006 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tool
Thanks I am using pgadmin for development. I'm looking for a tool with which I can develop an interface that will be used for entering and accessing information in context with the user tasks. Bob Hi, Why not OpenOffice Base ? http://www.openoffice.org/product/base.html Philippe Ferreira. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Limiting with a left outer join
I've been staring at this too long. Could someone give me a bit of sql help? I want to show a list of upcoming workshops, and the number of sessions (classes) offered for each workshop: workshop_id | classes_offered | review_mode | workshop_cat -+-+-+-- 3 | 0 | t | On Ground 29 | 5 | f | On Ground 30 | 0 | f | On Ground 31 | 1 | f | On Line 61 | 3 | f | On Ground 62 | 2 | f | On Ground 63 | 1 | f | On Line A class is an instance of a given workshop (location and date given). A class references a workshop. Now, I'm using a LEFT OUTER JOIN to list workshops that don't have any classes assigned yet. Those are the zeros above. Where I'm stuck is I need to apply limits to what rows to select. For example, I don't want to include classes or workshops that are in review_mode. Also, both workshops and classes can belong to domains (via link tables) so need to only look at those, too. Trying to do the class_domain join is where I'm stuck. Here's without that join, which sees to work: FROMworkshop w INNER JOIN workshop_category ON ( workshop_category.id = w.workshop_category AND w.review_mode IS FALSE ) INNER JOIN workshop_domain ON ( workshop_domain.workshop = w.id AND workshop_domain.domain = 1 ) LEFT OUTER JOIN class c ON ( c.workshop = w.id AND c.register_cutoff_time = now() AND c.review_mode IS FALSE ) The class table also has a class_domain table (like the workshop_domain). But, I'm not seeing how to make that join. This pulls all the zeros out of the results: [...] LEFT OUTER JOIN class c ON ( c.workshop = w.id AND c.register_cutoff_time = now() AND c.review_mode IS FALSE ) INNER JOIN class_domain ON ( class_domain.class = c.id AND class_domain.domain = 1 ) It's these left outer joins that always get me. What I think I need is something like: [...] LEFT OUTER JOIN class c ON ( c.workshop = w.id AND c.register_cutoff_time = now() AND c.review_mode IS FALSE AND class_domain.class = c.id AND class_domain.domain = 1 ) But, that's not part of the join, of course. How do I make a join on the class table but not effect the left outer join? Thanks, -- Bill Moseley [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [Bulk] Re: [GENERAL] Tool
Bob, If you're interested in a free java tool, you might want to look at NetBeans (now version 5). It is one of the best I have seen, and I have used a wide variety of such tools in a number of different programminglanguages. Understand, though, that you have a wide variety of options, ranging from a web interface (which may be preferred if your users will be accessing the database over a network) at one extreme to a thick client, perhaps started using Sun's Web Start, at the other. An advantage NetBeans offers is that it is easy to learn, and the user interface is quite intuitive, especially for GUI design and implementation. Eclipse is another one with a good reputation, but I don't like it so much. Its support for GUI design seems to me to bequite weak. What you haven't said in any of your posts is what programming languages you know. Nor do you indicate whether or not you have the skills required to work with an editor like emacs to create a very thin client (using a web server like Apache) with server side scripting using perl or php. It is hard to make a recommendation without knowing something about your skills. If you don't already have the skills of an intermediate Java programmer, then none of the tools mentioned in this thread will help you much. If you knew only C++ (again at least at an intermediate level), for example, the only tools I know of that would be suitable are commercial (Borland's C++ Builder and Microsoft's Visual C++). And I have no idea what the corresponding tools would be on unix/Linux (if anyone knows of such tools for Linux, I'd appreciate hearing about them as one of my computers will soon have Windows XP removed and replaced by Suse linux). Cheers, Ted - Original Message - From: Bob Pawley To: Wes Williams ; 'Postgresql' Sent: Friday, February 10, 2006 1:03 PM Subject: [Bulk] Re: [GENERAL] Tool This looks quite interseting. Thanks Wes Bob - Original Message - From: Wes Williams To: 'Bob Pawley' ; 'Postgresql' Sent: Friday, February 10, 2006 9:14 AM Subject: RE: [GENERAL] Tool You may wish to consider creating a web interface GUI. For example: Sun's free Java Studio Creator2 http://developers.sun.com/prodtech/javatools/jscreator/index.jsp Alittletolearnupfront,butthentheGUIgoodnesscomesthrough. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Bob PawleySent: Friday, February 10, 2006 12:02 PMTo: PostgresqlSubject: [GENERAL] Tool I am developing a database which needs testing prior to developing the host program. I am looking for an easy-to-learn tool. I want to develop an interfacethat I can use toenter and display information in a similar fashion to how a user would operate the database. A GUI tool would be ideal. I am running Postgresql v 8.0 on Windows XP. Bob Pawley
Re: [GENERAL] Tool
Could you suggests some tools that do not have to be super users to install it under Linux ? - Emi Thanks I am using pgadmin for development. I'm looking for a tool with which I can develop an interface that will be used for entering and accessing information in context with the user tasks. Bob Hi, Why not OpenOffice Base ? http://www.openoffice.org/product/base.html Philippe Ferreira. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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: [GENERAL] Sequences/defaults and pg_dump
On Fri, Feb 10, 2006 at 07:34:35 -0500, Doug McNaught [EMAIL PROTECTED] wrote: Bruno Wolff III [EMAIL PROTECTED] writes: Or, just start your sequence counting at 100. Or use bigint and start it at a billion. That may work if you only have access to one id number, but you don't need too many id numbers before you can start making good statistical estimates of the number of users. In one of my math classes, I was told a story about how statisticians estimnated the number of tanks produced by germany during world war II based on serial numbers found on captured tanks. This was supposedly despite the Germans doing things (leaving gaps and the like) to make this harder. And supposedly after the war, the statistical results were supposed to have been more accurate than estimates obtain via other means (such as spies). ---(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: [GENERAL] Tool
Philippe Ferreira schrieb: Thanks I am using pgadmin for development. I'm looking for a tool with which I can develop an interface that will be used for entering and accessing information in context with the user tasks. Bob Hi, Why not OpenOffice Base ? Someone should help to get the SDBC driver running with current OpenOffice and Postgres. :( ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Sequence skipping values
Hi,Thanks four your answers. Let me give more details here.The table with the id not incrementing by 1 as I expected is named topics.I have three other tables that contain rules that on insert into those tables, some fields of the table Topic should be updated.Each of those three tables contain a column that refer to topics.id as a foreign key.Those three columns contain id automatically generated by sequences and I have not observed any problemThanksJCSteve Crawford [EMAIL PROTECTED] wrote: Jean-Christophe Roux wrote: Hello, I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on! If someone could give me a clue that wold be greatly appreciated thanks JCAny rules, triggers, etc. involved? How are the inserts done (insert one record into the table)? What relationships does the table have to any other tables? Anything else accessing that sequence?There is no guarantee that a sequence will be contiguous. For example, begin...insert into...rollback will not reset the sequence as other transactions could have incremented the sequence.Cheers,Steve Yahoo! Mail Use Photomail to share photos without annoying attachments.
Re: [GENERAL] Sequence skipping values
Jean-Christophe Roux wrote: Hi, Thanks four your answers. Let me give more details here. The table with the id not incrementing by 1 as I expected is named topics. I have three other tables that contain rules that on insert into those tables, some fields of the table Topic should be updated. Each of those three tables contain a column that refer to topics.id as a foreign key. Those three columns contain id automatically generated by sequences and I have not observed any problem Start psql and on each of those tables run \d tablename. Also run select * from sequencename for any sequences involved. Post results here - I suspect some clues will be contained therein. Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Sequence skipping values
On Fri, Feb 10, 2006 at 03:27:23PM -0800, Jean-Christophe Roux wrote:
The table with the id not incrementing by 1 as I expected is named topics.
I have three other tables that contain rules that on insert into those
tables, some fields of the table Topic should be updated.
Each of those three tables contain a column that refer to topics.id as a
foreign key.
Those three columns contain id automatically generated by sequences and I
have not observed any problem
The word rules attracts attention; questions about sequences being
incremented multiple times due to rules appear in the lists regularly.
The problem is that where you might think the rule uses a value it's
really using an expression, so each time you use the value in the
rule you're evaluating the expression again. Example:
CREATE TABLE foo (id serial);
CREATE TABLE bar (id1 integer, id2 integer, id3 integer);
CREATE RULE foorule AS ON INSERT TO foo
DO ALSO INSERT INTO bar VALUES (NEW.id, NEW.id, NEW.id);
INSERT INTO foo DEFAULT VALUES;
SELECT * FROM foo;
id
1
(1 row)
SELECT * FROM bar;
id1 | id2 | id3
-+-+-
2 | 3 | 4
(1 row)
When the rule rewrote the query it didn't use
INSERT INTO bar VALUES (1, 1, 1)
but rather
INSERT INTO bar VALUES (nextval('foo_id_seq'), nextval('foo_id_seq'),
nextval('foo_id_seq'))
because NEW.id evaluates to a nextval expression, not to the result
of that expression.
If you post the table definitions as Steve requested we'll be able
to see whether the above is indeed what's happening.
--
Michael Fuhr
---(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: [GENERAL] Sequence skipping values
Hi here are the structures of the table involved:CREATE TABLE topics( topic_id int8 NOT NULL DEFAULT nextval('topics_id_seq'::text), datetime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone, topic text NOT NULL, administrator_id int8 NOT NULL, status_id int8 DEFAULT 0, last_change timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone, links int8 DEFAULT 0, releases int8 DEFAULT 0, last_administrator_id int8, CONSTRAINT topics_pk PRIMARY KEY (topic_id), CONSTRAINT topics_fk_administrator_id FOREIGN KEY (administrator_id) REFERENCES administrators (administrator_id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT topics_status_fk FOREIGN KEY (status_id) REFERENCES status_list (status_id)
MATCH
SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT) select * from topics_id_seq"topics_id";1224;1;9223372036854775807;0;1;23;f;tit is this topics_id_seq that does not increment by one unit at a time, but rather by the number of rows of the table topics.To make things shorter, I am posting only one of the three tables. The other two tables are similar and have the same kind of rulesCREATE TABLE releases( topic_id int8 NOT NULL, release_id int8 NOT NULL DEFAULT nextval('releases_id_seq'::text), datetime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone, body text NOT NULL, administrator_id int8 NOT NULL, CONSTRAINT releases_pk PRIMARY KEY (release_id), CONSTRAINT releases_fk_administrator_id FOREIGN KEY (administrator_id) REFERENCES administrators (administrator_id) MATCH
SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT releases_fk_topic_id FOREIGN KEY (topic_id) REFERENCES topics (topic_id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT) CREATE OR REPLACE RULE releases_increment_topics AS ON INSERT TO releases DO UPDATE topics SET releases = topics.releases + 1 WHERE topics.topic_id = new.topic_id;CREATE OR REPLACE RULE releases_last_administrator_id AS ON INSERT TO releases DO UPDATE topics SET last_administrator_id = new.administrator_id WHERE topics.topic_id = new.topic_id;CREATE OR REPLACE RULE releases_last_change AS ON INSERT TO releases DO UPDATE topics SET last_change = now() WHERE topics.topic_id = new.topic_id;Thanks again for your time and explanations; it is qu
ite
useful.RegardsJCRMichael Fuhr [EMAIL PROTECTED] wrote: On Fri, Feb 10, 2006 at 03:27:23PM -0800, Jean-Christophe Roux wrote: The table with the id not incrementing by 1 as I expected is named topics. I have three other tables that contain rules that on insert into those tables, some fields of the table Topic should be updated. Each of those three tables contain a column that refer to topics.id as a foreign key. Those three columns contain id automatically generated by sequences and I have not observed any problemThe word "rules" attracts attention; questions about sequences beingincremented multiple times due to rules appear in the lists regularly.The problem is that where you might think the rule uses a value it'sreally using an expr
ession,
so each time you use the "value" in therule you're evaluating the _expression_ again. Example:CREATE TABLE foo (id serial);CREATE TABLE bar (id1 integer, id2 integer, id3 integer);CREATE RULE foorule AS ON INSERT TO foo DO ALSO INSERT INTO bar VALUES (NEW.id, NEW.id, NEW.id);INSERT INTO foo DEFAULT VALUES;SELECT * FROM foo; id 1(1 row)SELECT * FROM bar; id1 | id2 | id3 -+-+- 2 | 3 | 4(1 row)When the rule rewrote the query it didn't use INSERT INTO bar VALUES (1, 1, 1)but rather INSERT INTO bar VALUES (nextval('foo_id_seq'), nextval('foo_id_seq'), nextval('foo_id_seq'))because NEW.id evaluates to a nextval _expression_, not to the resultof that _expression_.If you post the table definitions as Steve requested we'll be ableto see whether the above is indeed what's happening.--
Michael
Fuhr
Yahoo! Mail
Use Photomail to share photos without annoying attachments.
[GENERAL] Return types of a function
Hi! I have postgres installed in my machine and have a simple task to do. Step1) Fetch all the tables in the Database with name staerting with "AA" Step2) On each table you get fire a simple SQL query: select * from tablename; Step3) Right these into a flat file. I have to carry out all these steps using a prcedure/function. The function would be called by a trigger. Now i need to know how to make a postgres function for that. My problem: My experience with database is almost 5 days and I can't seem to understand the documentation, like how to catch the return value. How to run the procedure for testing without firing the trigger. So could anyone help me with that. Regards, Nalin.
Re: [GENERAL] Sequence skipping values
On Fri, Feb 10, 2006 at 08:10:36PM -0800, Jean-Christophe Roux wrote: here are the structures of the table involved: I couldn't duplicate the problem in 8.1.2 or 8.0.6 with the code you posted. I created the given tables and rules (plus guesses for the administrators and status_list tables), then inserted several records into topics, then inserted a few records into releases, then inserted a few more records into topics. The topics_id_seq sequence incremented by one each time with no gaps; that probably means my test didn't match exactly what you're doing. Does the topics table have any rules or triggers that you didn't show? Could you post a minimal but complete test case, i.e, all SQL statements that somebody could load into an empty database to reproduce the problem? If not then it might be useful to see the EXPLAIN ANALYZE output of a series of statements that exhibit the unexpected behavior. What version of PostgreSQL are you running? -- Michael Fuhr ---(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: [GENERAL] Limiting with a left outer join
On Fri, Feb 10, 2006 at 11:59:30AM -0800, Bill Moseley wrote: How do I make a join on the class table but not effect the left outer join? Are you looking for something like this? LEFT OUTER JOIN (class INNER JOIN class_domain ON ...) c ON ... -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
