[GENERAL] PostgreSQL Authors wanted
Hi, beehive KG is considering publishing a quarterly online technical journal on PostgreSQL. If you would want to write for us please visit this page for more details:. http://www.beehive-eu.com/PostgreSQLAuthors.html Thanks and regards, Mark ---(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
[GENERAL] UNION with ORDER BY -allowed?
It's not quite clear (to me at least) whether I can have a UNION and an ORDER BY in a SELECT statement. What I want to do is:- SELECT col1, col2, col5, col6 FROM table WHERE col2 = 'X' UNION SELECT col3, col4, col5, col6 FROM table WHERE col4 = 'X' ORDER BY coalesce(col1, col3) Is this valid syntax allowed by postgresql? (I'm not at the system where postgresql is installed at the moment so I can't just try it) col1 and col3 are both DATE columns. col2 and col4 are both varchar(1). I want the ORDER BY to order the result of the UNION. -- Chris Green ([EMAIL PROTECTED]) Never ascribe to malice, that which can be explained by incompetence. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] UNION with ORDER BY -allowed?
wrap the whole statement in another select select col1, col2, col5, col6 from ( SELECT col1, col2, col5, col6 FROM table WHERE col2 = 'X' UNION SELECT col3, col4, col5, col6 FROM table WHERE col4 = 'X' ) as t order by coalesce(col1, col3); John Sidney-Woollett Chris Green wrote: It's not quite clear (to me at least) whether I can have a UNION and an ORDER BY in a SELECT statement. What I want to do is:- SELECT col1, col2, col5, col6 FROM table WHERE col2 = 'X' UNION SELECT col3, col4, col5, col6 FROM table WHERE col4 = 'X' ORDER BY coalesce(col1, col3) Is this valid syntax allowed by postgresql? (I'm not at the system where postgresql is installed at the moment so I can't just try it) col1 and col3 are both DATE columns. col2 and col4 are both varchar(1). I want the ORDER BY to order the result of the UNION. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_restore taking 4 hours!
Rodrigo Carvalhaes a écrit : Hi! I am using PostgreSQL with a proprietary ERP software in Brazil. The database have around 1.600 tables (each one with +/- 50 columns). My problem now is the time that takes to restore a dump. My customer database have arount 500mb (on the disk, not the dump file) and I am making the dump with pg_dump -Fc, my dumped file have 30mb. To make the dump, it's taking +/- 1,5 hours BUT to restore (using pg_restore ) it it takes 4 - 5 hours!!! I have notice that fac and one way to improve the restore prefomances, is to avoid build indexes and checking the foreign key in the same step than the restore. So, as it is not possible to disable indexes and Foreign key, you have to drop them and recreate them once the restore step has finished. To do that you should have a script to recreate the indexes and the Foreign Key afterward. Our machine it's a Dell Server Power Edge 1600sc (Xeon 2,4Ghz, with 1GB memory, 7200 RPM disk). I don't think that there is a machine problem because it's a server dedicated for the database and the cpu utilization during the restore is around 30%. Looking on the lists arquives I found some messages about this and Tom Lane was saying that then you have a lot of convertions the dump can delay too much. 90% of the columns on my database are char columns and I don't have large objects on the database. The restore is delaying too much because the conversion of the char columns ? How can I have a better performance on this restore? I need to find a solution for this because I am convincing customers that are using SQL Server, DB2 and Oracle to change to PostgreSQL but this customers have databases of 5GB!!! I am thinking that even with a better server, the restore will take 2 days! My data: Conectiva Linux 10 , Kernel 2.6.8 PostgreSQL 7.4.6. postgresql.conf modified parameters (the other parameters are the default) tcpip_socket = true max_connections = 30 shared_buffers = 3 sort_mem = 4096 vacuum_mem = 8192 max_fsm_pages = 2 max_fsm_relations = 1000 Regards, Rodrigo Carvalhaes ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Need Help and suggestion.
Artistic-HO- IT-Department wrote: Hi I have a user table in which number of users are created in a database rather creating postgresql user. We are having only one database user, through which we connect database. OK Like Mr. A add intry into table and data is copied into audit table. Mr. B edit entry into table and data is copied into audit table. Mr. C delete entry into table but it add data into audit table that Mr. B delete it(As it was the person who edit last). It saved the whole row that was deleted. How can i make trigger on database that will add userid into audit table that Mr. C has deleted the entry. Create a session table and store your user-name there when you connect. Then the trigger can access the session table for the user-name. You can use the backend process-id as a key for the table: SELECT pg_backend_pid() HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] ISO week and extract(dow from source)
From the 7.4.2 manual, Date/Time Functions and operators, 9.8.1. EXTRACT, date_part: week: (The ISO-8601 week starts on Monday.) dow: The day of the week (0 - 6; Sunday is 0) Why is Sunday dow 0, if the iso week starts on Monday and the iso week is used in extract(week from source)? Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Poor Performance with Distinct Subqueries with EXISTS and EXCEPT
Now, since I'm actually interested in unique domain names rather than unique users, I need to get all the unique domain names corresponding to users who have acted on a message. That's what the part of the query after the EXCEPT is. I don't understand this part at all. What does it mean ? I may be mistaken, but you may be doing the same thing twice : you're basically writing : SELECT DISTINCT X WHERE Y EXCEPT SELECT DISTINCT X WHERE NOT Y Is this not a way to get an empty result set ? Let's re-take your query from the start. At each step you should explain analyze the query to check if it runs smoothly. 1. You want the messages which have no actions. Rather than a subselect, I'd use a LEFT JOIN : untested syntax : SELECT m.id FROM message m LEFT JOIN message_action ma ON m.id=ma.messages_id WHERE ma.messages_id IS NULL; On my machine, I have a zones table with 3000 rows and a cities table with 2 million rows, each place having a zone_id : EXPLAIN ANALYZE SELECT z.zone_id FROM geo.zones z LEFT JOIN geo.cities c ON c.zone_id=z.zone_id WHERE c.id IS NULL; Merge Left Join (cost=0.00..142063.06 rows=3663 width=4) (actual time=8726.203..8726.203 rows=0 loops=1) Merge Cond: (outer.zone_id = inner.zone_id) Filter: (inner.id IS NULL) - Index Scan using zones_pkey on zones z (cost=0.00..99.10 rows=3663 width=4) (actual time=15.027..43.987 rows=3663 loops=1) - Index Scan using cities_zones_idx on cities c (cost=0.00..116030.55 rows=2073935 width=8) (actual time=25.164..5823.496 rows=2073935 loops=1) Total runtime: 8726.327 ms (6 lignes) 8 seconds, this gives you an idea with that many records. You should check your indexes are used ! Now you have the messages which have no actions, you must get the user email domains : SELECT split_part( u.email, '@', 2 ) as domain FROM users u, message m LEFT JOIN message_action ma ON m.id=ma.messages_id WHERE u.id=m.user_id AND ma.messages_id IS NULL; Can you time this query ? Are the indexes used ? Now, let's remove the duplicates : SELECT split_part( u.email, '@', 2 ) as domain FROM users u, message m LEFT JOIN message_action ma ON m.id=ma.messages_id WHERE u.id=m.user_id AND ma.messages_id IS NULL GROUP By domain; GROUP BY is faster than DISTINCT (in some cases). How does it go ? On Wed, 1 Dec 2004 00:11:35 -0600, Thomas F.O'Connell [EMAIL PROTECTED] wrote: I'm trying to do some research and reporting for an email application by domain name. This has led to a confounding attempt to do any of the legwork in SQL via postgres. Here is my foundational query: SELECT DISTINCT split_part( u.email, '@', 2 ) FROM user AS u, message AS m WHERE u.id = m.user_id AND NOT EXISTS ( SELECT 1 FROM message_action ma WHERE ma.message_id = m.id ) EXCEPT SELECT DISTINCT split_part( u.email, '@', 2 ) FROM user AS u, message AS m WHERE u.id = m.user_id AND EXISTS ( SELECT 1 FROM message_action ma WHERE ma.message_id = m.id ) This is designed to give me unique domain names for all users who have not committed an action on an email. The way I figure this needed to work was to get all members joined to messages that didn't have an action. That's the part of the query before the EXCEPT. The EXISTS checks for an action on the message. This query performs abysmally for even small numbers of users and messages (each on the order of 1-10 thousand). Honestly, I have not gotten it to finish for even these small cases. In one situation on a development database, it filled up $PGDATA/base. This is another instance of my surprising myself with my ability to generate slow-running queries where I don't fully understand the interaction between postgres and what I think I'm asking of postgres in SQL. I'd love to deepen my knowledge of joins in general, so if anyone has any suggestions for improvements on the EXISTS checks, then I'm anxious to learn alternatives. One thing I don't fully understand is why all the scans are Seq Scans. These IDs are all integer primary/foreign keys (with indexes). Here's an example of a plan for a user with no messages: QUERY PLAN -- SetOp Except (cost=479.01..479.02 rows=1 width=24) (actual time=176.258..176.258 rows=0 loops=1) - Sort (cost=479.01..479.02 rows=2 width=24) (actual time=176.253..176.253 rows=0 loops=1) Sort Key: split_part - Append (cost=239.48..479.00 rows=2 width=24) (actual time=176.230..176.230
Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes
Tom Lane wrote: Maksim Likharev [EMAIL PROTECTED] writes: in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp table in 7.4.5 GET DIAGNOSTICS returns 0 Hmm. I'm not sure if that's a bug or an improvement. The command did not return any rows to plpgsql, so in that sense row_count = 0 is correct, but I can see why you feel you've lost some capability. Anyone else have an opinion about this? Well, from the manuals: The currently available status items are ROW_COUNT, the number of rows processed by the last SQL command sent down to the SQL engine Nothing there about rows being returned. And by analogy: A PERFORM statement sets FOUND true if it produces (and discards) a row, false if no row is produced. If you've FOUND rows then presumably ROW_COUNT should be non-zero. So set it if rows aren't returned I'd opine. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Displaying more than six digits from a real number
--- Tom Lane [EMAIL PROTECTED] escreveu: Clodoaldo Pinto [EMAIL PROTECTED] writes: The 7.4.2 manual says the precision of a real number is 6 decimal digits. Is it possible to force the display of more than six decimal digits of a real number Perhaps the extra_float_digits runtime parameter is what you want? (You are of course aware that there aren't really more than six useful digits there... or if you're not, maybe you should stay away from float and use numeric.) That is exactly what I wanted. Yes, I'm aware. But I wonder how is the 7th digit right? At least with the few tests I did it was never wrong. I will have to change that column to float8 but for now it is good enough. Thanks --- Bruno Wolff III [EMAIL PROTECTED] escreveu: I think you can use the extra_float_digits GUC variable to do what you want. Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] md5 checksum mismatch
Bill Kurland wrote: I've downloaded several versions of postgresql from several mirrors. On none of them did the md5 checksums from http://www.gtsm.com/postgres_sigs.html match the md5 checksum from the postgresql-*.tar.gz source file I downloaded. I can't imagine that all these file are corrupted, yet I don't see what I could be doing wrong. Just tested one: ftp://ftp2.uk.postgresql.org/sites/ftp.postgresql.org/src/7.4.6/postgresql-7.4.6.tar.bz2 Does indeed have MD5 checksum of f0ea2b372a7bdaf2613e92176ebf5e0f This matches what's in the .md5 file and is listed on Greg's page. Note that you can't use md5sum --check file.md5 but manually comparing the sums all seems OK. -- Richard Huxton Archonet Ltd ---(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: [GENERAL] Pl/Perl w/ Postgres 8.0 on Windows
Hi all, I am throughly enjoying using Postgres 8.0 on Windows, so I can develop my Windows client against Postgres. I would like to write some pl/perl functions, and I'm a little stumped. Now comes the sad confession - I installed PG from the Windows installer rather than building it. Can I still build pl/perl against it? Better yet, is there a pl/perl binary for Windows? Well, aren't you in luck today :-) The MSI install *always* installs the plperl DLL. IF you have installed ActiveState perl, it even gives you the option to enable it in template1 durnig the install. So you need to: 1) Install ActiveState Perl 5.8, if you haven't already (that's what it's compiled against) 2) Run createlang for PL/Perl, similar as you'd do on Unix. (2) can be replaced with uninstall/reinstall of the MSI if you haven't started using your database yet. The option to enable the two plperl variants should be ungrayed if it finds activeState Perl 5.8 in the system path. //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Poor Performance with Distinct Subqueries with EXISTS and EXCEPT
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= [EMAIL PROTECTED] writes: I may be mistaken, but you may be doing the same thing twice : you're basically writing : SELECT DISTINCT X WHERE Y EXCEPT SELECT DISTINCT X WHERE NOT Y Is this not a way to get an empty result set ? No, because some X values may appear in rows where Y, and also in rows where NOT Y. The DISTINCTs are wastes of time, though, because EXCEPT implies elimination of duplicates. regards, tom lane ---(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: [GENERAL] Poor Performance with Distinct Subqueries with EXISTS and EXCEPT
I wasn't sure whether EXCEPT would create a unique set from among the results of both queries. As in, if the first part of the query (before the EXCEPT clause), without the DISTINCT, yielded yahoo.com yahoo.com would the query reduce that to a single yahoo.com regardless of whether it showed up in the EXCEPT clause? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Dec 2, 2004, at 10:26 AM, Tom Lane wrote: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= [EMAIL PROTECTED] writes: I may be mistaken, but you may be doing the same thing twice : you're basically writing : SELECT DISTINCT X WHERE Y EXCEPT SELECT DISTINCT X WHERE NOT Y Is this not a way to get an empty result set ? No, because some X values may appear in rows where Y, and also in rows where NOT Y. The DISTINCTs are wastes of time, though, because EXCEPT implies elimination of duplicates. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Changing column type from oid to int4
Did somebody ever try to change the type to a compatible type? I plan to change a column from oid to int (once it holded a lob, now some sort of id) with modifying pg_attribute: update pg_attribute set atttypid=(select oid from pg_type where typname='int4') where attname='whatever' and attrelid=(select oid from pg_class where relname='a_table_name'); It seems to work fine, but I was not sure so I rolled back the change. So, has anyone ever tried this? Regards, Mario Weilguni ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_restore taking 4 hours!
Thierry Missimilly wrote: Rodrigo Carvalhaes a écrit : Hi! I am using PostgreSQL with a proprietary ERP software in Brazil. The database have around 1.600 tables (each one with +/- 50 columns). My problem now is the time that takes to restore a dump. My customer database have arount 500mb (on the disk, not the dump file) and I am making the dump with pg_dump -Fc, my dumped file have 30mb. To make the dump, it's taking +/- 1,5 hours BUT to restore (using pg_restore ) it it takes 4 - 5 hours!!! I have notice that fac and one way to improve the restore prefomances, is to avoid build indexes and checking the foreign key in the same step than the restore. So, as it is not possible to disable indexes and Foreign key, you have to drop them and recreate them once the restore step has finished. To do that you should have a script to recreate the indexes and the Foreign Key afterward. There are a couple of things you can do. 1. Turn off Fsync for the restore 2. Restore in three phases: 1. Schema without constraints or indexes 2. Restore data 3. Apply rest of schema with constraints and indexes 3. Increase the number of transaction logs. Sincerely, Joshua D. Drake Our machine it's a Dell Server Power Edge 1600sc (Xeon 2,4Ghz, with 1GB memory, 7200 RPM disk). I don't think that there is a machine problem because it's a server dedicated for the database and the cpu utilization during the restore is around 30%. Looking on the lists arquives I found some messages about this and Tom Lane was saying that then you have a lot of convertions the dump can delay too much. 90% of the columns on my database are char columns and I don't have large objects on the database. The restore is delaying too much because the conversion of the char columns ? How can I have a better performance on this restore? I need to find a solution for this because I am convincing customers that are using SQL Server, DB2 and Oracle to change to PostgreSQL but this customers have databases of 5GB!!! I am thinking that even with a better server, the restore will take 2 days! My data: Conectiva Linux 10 , Kernel 2.6.8 PostgreSQL 7.4.6. postgresql.conf modified parameters (the other parameters are the default) tcpip_socket = true max_connections = 30 shared_buffers = 3 sort_mem = 4096 vacuum_mem = 8192 max_fsm_pages = 2 max_fsm_relations = 1000 Regards, Rodrigo Carvalhaes ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK
hi all, i've a new install of pgsql8b5 running on OSX 10.3.6. i can readily start it from the command line with: sudo -u testuser sh -c nohup /usr/local/pgsql/bin/postmaster -n -i -h 10.0.0.6 -D /var/data/pgsql -c config_file=/etc/pgsql/postgresql.conf /dev/null /var/devlogs/postgres.log after which it behaves as i'd expect =) however, if i place an identical startup string in my OSX's StartupItem for pgsql reboot, pgsql does not start on boot. immediately after, i can launch ... but not on system start. i've turned debugging (debug5, i think i got 'em all ...) on, and my /var/devlogs/postgres.log after startup only shows: LOG: logger shutting down DEBUG: proc_exit(0) DEBUG: shmem_exit(0) DEBUG: exit(0) system kernel logs show nothing of obvious consequence ... any suggestions as to how to track down the no-start-on-startup problem? thx! richard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] (b)trim anomalies
OK, I have documented that the second parameter to btrim() is optional,
and default to a space. Patch attached and applied.
---
Thomas F. O'Connell wrote:
Not entirely.
Per http://www.postgresql.org/docs/7.4/static/functions-string.html,
btrim is documented as taking two arguments. There is a single-argument
version that exists that trims whitespace if only the first argument is
given (i.e., the characters to trim are omitted). This latter version
is nowhere documented as far as I can tell.
I'm also curious why, despite its place in the ANSI standard, \df seems
to reveal no information about trim.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 27, 2004, at 9:23 PM, Bruce Momjian wrote:
Thomas F.O'Connell wrote:
I was just wondering why the btrim syntax that takes only a single
argument is not documented in 9.4 in the documentation (I checked both
7.4 and 8.0 docs).
This is in a 7.4.5 installation:
pg=# \df btrim
List of functions
Result data type | Schema | Name | Argument data types
--++---+-
bytea| pg_catalog | btrim | bytea, bytea
text | pg_catalog | btrim | text
text | pg_catalog | btrim | text, text
(3 rows)
Is it ever documented anywhere that the single-argument version of
btrim can be used to remove whitespace from the left and right of a
given string? Is this version not supposed to be user-facing for some
reason?
Also, in this post:
http://archives.postgresql.org/pgsql-sql/2002-01/msg00053.php
Bruce sort of explains that TRIM is an ANSI word but doesn't fully
explain why it doesn't show up in a \df listing:
pg=# \df trim
List of functions
Result data type | Schema | Name | Argument data types
--++--+-
(0 rows)
This one's more a curiosity thing.
Because TRIM is an ANSI standard, we document TRIM (BOTH, ...) but not
btrim. The parser does the translation:
| TRIM '(' BOTH trim_list ')'
{
/* various trim expressions are defined in SQL92
* - thomas 1997-07-19
*/
FuncCall *n = makeNode(FuncCall);
n-funcname = SystemFuncName(btrim);
n-args = $4;
n-agg_star = FALSE;
n-agg_distinct = FALSE;
$$ = (Node *)n;
Does that answer your questions?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania
19073
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/func.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.225
diff -c -c -r1.225 func.sgml
*** doc/src/sgml/func.sgml 1 Dec 2004 19:32:12 - 1.225
--- doc/src/sgml/func.sgml 2 Dec 2004 17:12:57 -
***
*** 1070,1081
/row
row
!
entryliteralfunctionbtrim/function(parameterstring/parameter
typetext/type, parametercharacters/parameter
typetext/type)/literal/entry
entrytypetext/type/entry
entry
Remove the longest string consisting only of characters
! in parametercharacters/parameter from the start and end of
! parameterstring/parameter.
/entry
entryliteralbtrim('xyxtrimyyx', 'xy')/literal/entry
entryliteraltrim/literal/entry
--- 1070,1082
/row
row
!
entryliteralfunctionbtrim/function(parameterstring/parameter
typetext/type
!optional, parametercharacters/parameter
typetext/type/optional)/literal/entry
entrytypetext/type/entry
entry
Remove the longest string consisting only of characters
! in parametercharacters/parameter (or spaces if not supplied)
! from the start and end of parameterstring/parameter.
/entry
entryliteralbtrim('xyxtrimyyx', 'xy')/literal/entry
entryliteraltrim/literal/entry
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
Re: [GENERAL] (b)trim anomalies
Nice. Thanks, Bruce. If I felt as if I could speak a little more
authoritatively about why it had been missing, I would've offered to
document it.
Now it is a psql/postgres internals issue that causes \df trim not to
reveal anything?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Dec 2, 2004, at 11:14 AM, Bruce Momjian wrote:
OK, I have documented that the second parameter to btrim() is optional,
and default to a space. Patch attached and applied.
---
Thomas F. O'Connell wrote:
Not entirely.
Per http://www.postgresql.org/docs/7.4/static/functions-string.html,
btrim is documented as taking two arguments. There is a
single-argument
version that exists that trims whitespace if only the first argument
is
given (i.e., the characters to trim are omitted). This latter version
is nowhere documented as far as I can tell.
I'm also curious why, despite its place in the ANSI standard, \df
seems
to reveal no information about trim.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 27, 2004, at 9:23 PM, Bruce Momjian wrote:
Thomas F.O'Connell wrote:
I was just wondering why the btrim syntax that takes only a single
argument is not documented in 9.4 in the documentation (I checked
both
7.4 and 8.0 docs).
This is in a 7.4.5 installation:
pg=# \df btrim
List of functions
Result data type | Schema | Name | Argument data types
--++---+-
bytea| pg_catalog | btrim | bytea, bytea
text | pg_catalog | btrim | text
text | pg_catalog | btrim | text, text
(3 rows)
Is it ever documented anywhere that the single-argument version of
btrim can be used to remove whitespace from the left and right of a
given string? Is this version not supposed to be user-facing for
some
reason?
Also, in this post:
http://archives.postgresql.org/pgsql-sql/2002-01/msg00053.php
Bruce sort of explains that TRIM is an ANSI word but doesn't fully
explain why it doesn't show up in a \df listing:
pg=# \df trim
List of functions
Result data type | Schema | Name | Argument data types
--++--+-
(0 rows)
This one's more a curiosity thing.
Because TRIM is an ANSI standard, we document TRIM (BOTH, ...) but
not
btrim. The parser does the translation:
| TRIM '(' BOTH trim_list ')'
{
/* various trim expressions are defined in SQL92
* - thomas 1997-07-19
*/
FuncCall *n = makeNode(FuncCall);
n-funcname = SystemFuncName(btrim);
n-args = $4;
n-agg_star = FALSE;
n-agg_distinct = FALSE;
$$ = (Node *)n;
Does that answer your questions?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square,
Pennsylvania
19073
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania
19073
Index: doc/src/sgml/func.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.225
diff -c -c -r1.225 func.sgml
*** doc/src/sgml/func.sgml 1 Dec 2004 19:32:12 - 1.225
--- doc/src/sgml/func.sgml 2 Dec 2004 17:12:57 -
***
*** 1070,1081
/row
row
!
entryliteralfunctionbtrim/function(parameterstring/
parameter typetext/type, parametercharacters/parameter
typetext/type)/literal/entry
entrytypetext/type/entry
entry
Remove the longest string consisting only of characters
! in parametercharacters/parameter from the start and end
of
! parameterstring/parameter.
/entry
entryliteralbtrim('xyxtrimyyx', 'xy')/literal/entry
entryliteraltrim/literal/entry
--- 1070,1082
/row
row
!
entryliteralfunctionbtrim/function(parameterstring/
parameter typetext/type
!optional, parametercharacters/parameter
typetext/type/optional)/literal/entry
entrytypetext/type/entry
entry
Remove the longest string consisting only of characters
! in parametercharacters/parameter (or spaces if not
supplied)
! from the start and end of
Re: [GENERAL] Changing column type from oid to int4
Mario Weilguni [EMAIL PROTECTED] writes: Did somebody ever try to change the type to a compatible type? I plan to change a column from oid to int (once it holded a lob, now some sort of id) with modifying pg_attribute: update pg_attribute set atttypid=(select oid from pg_type where typname='int4') where attname='whatever' and attrelid=(select oid from pg_class where relname='a_table_name'); It seems to work fine, but I was not sure so I rolled back the change. You'd have to look at indexes and constraints involving the column, too. Another point is the pg_depend linkage between the column and the datatype. We short-circuit this for built-in types, so it's not really an issue when switching from one built-in datatype to another, but you'd have to fix it if you were say changing to a domain type. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK
OpenMacNews [EMAIL PROTECTED] writes: sudo -u testuser sh -c nohup /usr/local/pgsql/bin/postmaster -n -i -h 10.0.0.6 -D /var/data/pgsql -c config_file=/etc/pgsql/postgresql.conf /dev/null /var/devlogs/postgres.log Hmm, isn't this letting postmaster stderr disappear into the bit bucket? Try adding 21 after the /var/devlogs/postgres.log so you can see if anything interesting shows up. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] (b)trim anomalies
Thomas F. O'Connell wrote: Nice. Thanks, Bruce. If I felt as if I could speak a little more authoritatively about why it had been missing, I would've offered to document it. Now it is a psql/postgres internals issue that causes \df trim not to reveal anything? It doesn't reveal with \df because we are mapping the ANSI-standard syntax of TRIM to the Oracle-compatible function of btrim. That mapping is not something psql can see. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] List archives not being updated?
The list archives at http://archives.postgresql.org/ don't appear to have been updated since Tue 30 Nov. Is something amiss? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
[GENERAL] problem with multiple invocations of a deferred constraint trigger
Hello all,
I have two tables (simplified):
create table clin_episode (
pk serial primary key,
started timestamp with time zone,
fk_clin_narrative integer
unique
default null
);
create table clin_narrative (
pk serial primary key,
fk_episode integer
not null
references clin_episode(pk),
narrative text
not null
);
The idea behind this is that clin_narrative stores ALL
narrative of a medical record. Clin_episode stores episodes
during which care was received. Such episodes do have a name
(such as knee pain Nov/04) but such episode names are part
of the medical record narrative and should be stored in
clin_narrative, too.
Now, obviously I got a cyclic referential integrity problem
here, eg. narrative pointing to episodes which point back to
narrative. No problem, I added a deferred constraint trigger
like this (don't worry about the health issue stuff, it isn't
related to the problem):
*-
-- an episode not linked to a health issue must have a
-- name (at least when the transaction ends ...)
\unset ON_ERROR_STOP
drop trigger tr_standalone_epi_needs_name on clin_episode;
drop function trf_standalone_epi_needs_name();
\set ON_ERROR_STOP 1
create function trf_standalone_epi_needs_name() returns opaque as '
declare
msg text;
narr_pk integer;
narr_fk_episode integer;
begin
-- debug ...
raise notice ''%'', TG_OP;
-- *if* we have a name it must belong to us ...
-- (eg. check for cyclic referential integrity violations)
if NEW.fk_clin_narrative is not null then
select into narr_pk, narr_fk_episode cn.pk, cn.fk_episode
from clin_narrative cn
where cn.pk = NEW.fk_clin_narrative
limit 1;
if narr_fk_episode NEW.pk then
msg := ''trf_standalone_epi_needs_name: clin_narrative
row [''
|| narr_pk || ''] does not belong to episode [''
|| NEW.pk || ''] and cannot thus name that
episode'';
raise exception ''%'', msg;
end if;
return NULL;
end if;
-- if linked to a health issue we do not have to have a name of our own
...
if NEW.fk_health_issue is not null then
return NULL;
end if;
msg := ''trf_standalone_epi_needs_name: episodes not linked to a health
issue must point to a clin_narrative row at the end of the transaction'';
raise exception ''%'', msg;
end;
' language 'plpgsql';
-- the trick is to defer the trigger ...
create constraint trigger tr_standalone_epi_needs_name
after insert or update
on clin_episode
initially deferred
for each row
execute procedure trf_standalone_epi_needs_name()
;
*-
This all works. However, to actually insert data I do the
following:
- insert into clin_episode with fk_clin_narrative=NULL
- insert into clin_narrative with fk_encounter = currval('clin_episode_pk_seq')
- update clin_episode with set fk_clin_narrative =
currval('clin_narrative_pk_seq')
What I end up with is the trigger being queued up for checking
at the end of transaction TWICE. This is quite logical since I
did an INSERT and then an UPDATE and the trigger fires on
both.
However, the first trigger invocation (on INSERT) correctly
raises an exception since at that time the NEW row did not yet
have a suitable fk_clin_narrative.
What is the proper solution here ?
- Try to deal with INSERT and UPDATE somehow separately ? The
problem I see with this is that *actually* I want to check the
state of the row at the *latest* UDPATE in a transaction
*only* and there might possibly be several UPDATEs in a tx.
- Don't check whether *NEW* has a fk_clin_narrative at trigger
execution time (eg. at the end of the transaction) but
rather explicitely SELECT fk_clin_narrative from the table
where pk = NEW.pk ? IOW check the actual state of the row
(which may have undergone several UPDATEs) at the end of the
transaction instead of the NEW row at trigger queue up
time. Would I then not run into trouble when
trying to insert more than one episode within the same
transaction ?
I'd be happy if someone had some help on this.
(Will post full schema details if needed.)
Karsten
GnuMed i18n coordinator
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---(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: [GENERAL] Indexes?
I believe that it is better to have a concatenated key of (toDate,FromDate). The reason the toDate should come first is that for more recent records, finding curDates less than toDate is much more selective than finding curDates greater than fromDate. Actually I'm not sure if fromDate is that helpful either as part of the concatenated key (it probably depends) but definitely not by itself. If your usual query is someEarlyHistoricalDate between toDate and fromDate, then the concatenated key should be (fromDate,toDate) instead. If toDate is sometimes not known, I would use some fixed date far in the future rather than a null. Vincent - Original Message - From: Bjørn T Johansen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 10:11 PM Subject: [GENERAL] Indexes? I have a table where I need to use ..where curdate between fromDate and toDate. Is it best to have two indexes, one for FromDate and one for toDate or just one index for both the fields? Regards, BTJ -- --- Bjørn T Johansen [EMAIL PROTECTED] --- Someone wrote: I understand that if you play a Windows CD backwards you hear strange Satanic messages To which someone replied: It's even worse than that; play it forwards and it installs Windows --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Indexes?
On Thu, Dec 02, 2004 at 07:11:29 +0100, Bjørn T Johansen [EMAIL PROTECTED] wrote: I have a table where I need to use ..where curdate between fromDate and toDate. Is it best to have two indexes, one for FromDate and one for toDate or just one index for both the fields? Assuming that curdate is something like the date when the query is being run and that FromDate and toDate are columns in the table you are searching, then you probably want indexes on each column. A combined index scan wouldn't be useful. An index scan on either FromDate or toDate might be useful depending on the distribution of values in those columns and the value of curdate. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] List archives not being updated?
Joshua? Everything looks fine on the main server itself ... and John grab'd his update this morning around 3am, so rsyncd on our side is working as well ... problems on your end? On Thu, 2 Dec 2004, Michael Fuhr wrote: The list archives at http://archives.postgresql.org/ don't appear to have been updated since Tue 30 Nov. Is something amiss? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] Adding Reply-To: listname to Lists configuration ...
Bruno Wolff III [EMAIL PROTECTED] writes: It is also possible for mailing list software to handle this preference for you (by not sending copies to addresses on the list that appear in the recipient headers), but I don't know if the software in use has that capability. I've noticed some lists starting to do this. The only reason I notice is because they appear totally broken for me and anyone else sorting the messages into folders depending on whether they arrive via a mailing list. When I read the list I read it in a separate group from my personal mail. When someone Cc's me I get two copies, one in my personal mail and one in the list folder. That's fine with me, it integrates well with the order in which I read my mail and with my settings to purge list mail but archive personal mail. But For lists where the list software has started implementing this broken behaviour the behaviour I see is that the list folder is just incomplete. It randomly misses some messages and not others depending on whether the sender Cc'd me in the headers. A lot of work seems to be going into making list manager software work around limitations of broken mail readers. In the process they're making it really hard to make mail readers that aren't broken work properly. (On that note I would dearly love to get rid of the stupid [GENERAL] [HACKERS] etc tags? Filtering on subject is a dumb way to filter your mail, there are perfectly good headers inserted by the list manager that don't get confused by cross-posts and personal followups and so on. I have procmail rules that remove the tags when they match the list name but postgres's lists' tags don't so they slip past.) -- greg ---(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: [GENERAL] data integrity and inserts
The second is shorthand for the first. you get to choose the index name in the first one. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department [EMAIL PROTECTED] Phone: (253) 798-3549 Pager: (253) 754-0002 Scott Frankel [EMAIL PROTECTED] 12/01/04 10:48 AM 1. CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); CREATE UNIQUE INDEX uidx_thename ON names(the_name); vs. 2. CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE); Is the UNIQUE constraint in the second solution merely short-hand for the explicit index declaration of the first solution? Or is there a functional difference between them that I should choose between? Thanks again! Scott On Dec 1, 2004, at 10:11 AM, Scott Frankel wrote: I want to ensure data integrity when inserting into a table, preventing multiple entries of identical rows of data. Does this call for using a trigger? How would triggers perform a query to test if data already exists in the table? (The doco outlines how triggers perform tests on NEW data inserted into a table; but I haven't found anything on data already extant.) Thanks in advance! Scott sample table: CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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: [GENERAL] Indexes?
On Wed, Dec 01, 2004 at 23:16:48 -0800, Vincent Hikida [EMAIL PROTECTED] wrote: I believe that it is better to have a concatenated key of (toDate,FromDate). The reason the toDate should come first is that for more recent records, finding curDates less than toDate is much more selective than finding curDates greater than fromDate. Actually I'm not sure if fromDate is that helpful either as part of the concatenated key (it probably depends) but definitely not by itself. I combined index won't be very useful for the kind of search he is doing. And not having an index on FromDate could hurt in some cases depending on the distribution of values. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [HACKERS] Adding Reply-To: listname to Lists configuration ...
On Thu, Dec 02, 2004 at 01:59:07AM -0500, Greg Stark wrote: (On that note I would dearly love to get rid of the stupid [GENERAL] [HACKERS] etc tags? Filtering on subject is a dumb way to filter your mail, I am with you on this. I find the tag useless and annoying. -- Alvaro Herrera ([EMAIL PROTECTED]) Some men are heterosexual, and some are bisexual, and some men don't think about sex at all... they become lawyers (Woody Allen) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Indexes?
On Thu, Dec 02, 2004 at 07:11:29AM +0100, Bjørn T Johansen wrote: I have a table where I need to use ..where curdate between fromDate and toDate. Is it best to have two indexes, one for FromDate and one for toDate or just one index for both the fields? You could try it both ways and use EXPLAIN ANALYZE to see which results in a faster plan, if that's what you mean by best. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] Adding Reply-To: listname to Lists
On Thu, 2 Dec 2004, Greg Stark wrote: (On that note I would dearly love to get rid of the stupid [GENERAL] [HACKERS] etc tags? Filtering on subject is a dumb way to filter your mail, there are perfectly good headers inserted by the list manager that don't get confused by cross-posts and personal followups and so on. I have procmail rules that remove the tags when they match the list name but postgres's lists' tags don't so they slip past.) you can issue a 'set noprefix' for your subscription for this ... see 'help set' for all available per user settings that are available ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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: [GENERAL] List archives not being updated?
Marc G. Fournier [EMAIL PROTECTED] writes: Joshua? Everything looks fine on the main server itself ... and John grab'd his update this morning around 3am, so rsyncd on our side is working as well ... problems on your end? It looks like the issue is that the toplevel web pages haven't had a December-2004 link added. Maybe the rsync is failing to capture copies of those files? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Select Database
Hi All!
How could I select another database without new connection?
For example, in PHP+MySQL we have mysql_select_db('database_name');
Thanx
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] data integrity and inserts
Ian Harding [EMAIL PROTECTED] writes: The second is shorthand for the first. you get to choose the index name in the first one. IIRC you can force the index name in the second case too, by using the fully unabbreviated CONSTRAINT syntax: ..., CONSTRAINT indexname UNIQUE(colname), ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] data integrity and inserts
The first way also makes it possible to put the constraint on multiple fields: create unique index uidx_abc on my_table(col_a, col_b, col_c); On Dec 2, 2004, at 1:51 PM, Ian Harding wrote: The second is shorthand for the first. you get to choose the index name in the first one. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department [EMAIL PROTECTED] Phone: (253) 798-3549 Pager: (253) 754-0002 Scott Frankel [EMAIL PROTECTED] 12/01/04 10:48 AM 1. CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); CREATE UNIQUE INDEX uidx_thename ON names(the_name); vs. 2. CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE); Is the UNIQUE constraint in the second solution merely short-hand for the explicit index declaration of the first solution? Or is there a functional difference between them that I should choose between? Thanks again! Scott On Dec 1, 2004, at 10:11 AM, Scott Frankel wrote: I want to ensure data integrity when inserting into a table, preventing multiple entries of identical rows of data. Does this call for using a trigger? How would triggers perform a query to test if data already exists in the table? (The doco outlines how triggers perform tests on NEW data inserted into a table; but I haven't found anything on data already extant.) Thanks in advance! Scott sample table: CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] List archives not being updated?
On Thu, 2 Dec 2004, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: Joshua? Everything looks fine on the main server itself ... and John grab'd his update this morning around 3am, so rsyncd on our side is working as well ... problems on your end? It looks like the issue is that the toplevel web pages haven't had a December-2004 link added. Maybe the rsync is failing to capture copies of those files? Nope, doesn't look like anything has been pulled across :( The requested URL /pgsql-admin/2004-12/index.php was not found on this server. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] Adding Reply-To: listname to Lists configuration ...
On Thu, Dec 02, 2004 at 01:59:07AM -0500, Greg Stark wrote: (On that note I would dearly love to get rid of the stupid [GENERAL] [HACKERS] etc tags? Filtering on subject is a dumb way to filter your mail, there are perfectly good headers inserted by the list manager that don't get confused by cross-posts and personal followups and so on. I have procmail rules that remove the tags when they match the list name but postgres's lists' tags don't so they slip past.) I absolutely agree and I've implemented a quick fix using my procmail recipes:- :0 fh * ^TOpostgres | sed 's/\[GENERAL\]//' :0 A: postgres I now get to see more of the subject without the distraction of [GENERAL] stuck in the middle of it! :-) -- Chris Green ([EMAIL PROTECTED]) Never ascribe to malice, that which can be explained by incompetence. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] List archives not being updated?
Marc G. Fournier wrote: Joshua? Taking a look now. Looks like we are having a minor DNS issue which is causing the DNS lookup to srv5 to fail. Sincerely, Joshua D. Drake Everything looks fine on the main server itself ... and John grab'd his update this morning around 3am, so rsyncd on our side is working as well ... problems on your end? On Thu, 2 Dec 2004, Michael Fuhr wrote: The list archives at http://archives.postgresql.org/ don't appear to have been updated since Tue 30 Nov. Is something amiss? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK
hi tom,
-- On Thursday, December 2, 2004 12:33:48 PM PST -0500 Tom Lane
[EMAIL PROTECTED] wrote:
OpenMacNews [EMAIL PROTECTED] writes:
sudo -u testuser sh -c nohup /usr/local/pgsql/bin/postmaster -n -i -h
10.0.0.6 -D /var/data/pgsql -c config_file=/etc/pgsql/postgresql.conf
/dev/null /var/devlogs/postgres.log
Hmm, isn't this letting postmaster stderr disappear into the bit bucket?
entirely possible, and probably probable.
(it actually was 'in there' at one point, per the distro's included startup
script ... damn that copy-n-paste!)
Try adding 21 after the /var/devlogs/postgres.log so you can see
if anything interesting shows up.
ok, did that, and 'simplified' my cmd as much as possible ...
here's the exact c/p from my current script:
sudo -u testuser sh -c /usr/local/pgsql/bin/postmaster -i -h 10.0.0.6 -D
/var/data/pgsql -c config_file=/etc/pgsql/postgresql.conf
/var/devlogs/postgres.log 21
which i've tried to make 'as similar as possible' to the distro's example
script:
sudo -u $PGUSER sh -c ${DAEMON} -D '${PGDATA}' $PGLOG 21
given my additions of:
-n do not reinitialize shared memory after abnormal exit
-i enable TCP/IP connections
-h HOSTNAME host name or IP address to listen on
, and the spec'd config file,
mine, all in all, _looks_ ok to me.
with the aforementioned startup string, here's the tail from my
'/var/devlogs/postgres.log' immediately after a reboot, b4 starting postmaster
from the cmd line:
LOCATION: PostmasterMain, postmaster.c:644
DEBUG: 0: -
LOCATION: PostmasterMain, postmaster.c:646
DEBUG: 0: invoking IpcMemoryCreate(size=2547712)
LOCATION: CreateSharedMemoryAndSemaphores, ipci.c:87
DEBUG: 0: max_safe_fds = 917, usable_fds = 951, already_open = 73
LOCATION: set_max_safe_fds, fd.c:360
LOG: 0: logger shutting down
LOCATION: SysLoggerMain, syslogger.c:361
DEBUG: 0: proc_exit(0)
LOCATION: proc_exit, ipc.c:95
DEBUG: 0: shmem_exit(0)
LOCATION: shmem_exit, ipc.c:126
DEBUG: 0: exit(0)
LOCATION: proc_exit, ipc.c:113
whereas the output starting *successfully* by executing the startup script from
the cmd line is just:
LOCATION: PostmasterMain, postmaster.c:644
DEBUG: 0: -
LOCATION: PostmasterMain, postmaster.c:646
DEBUG: 0: invoking IpcMemoryCreate(size=2547712)
LOCATION: CreateSharedMemoryAndSemaphores, ipci.c:87
DEBUG: 0: max_safe_fds = 917, usable_fds = 951, already_open = 73
LOCATION: set_max_safe_fds, fd.c:360
note, of course, _no_ 'proc exit'.
thoughts?
richard
---(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: [GENERAL] List archives not being updated?
Joshua D. Drake wrote: Marc G. Fournier wrote: Joshua? Taking a look now. Looks like we are having a minor DNS issue which is causing the DNS lookup to srv5 to fail. Sincerely, Joshua D. Drake Everything looks fine on the main server itself ... and John grab'd his update this morning around 3am, so rsyncd on our side is working as well ... problems on your end? As a temporary solution I put srv5 in the hosts file. So as long as the ip doesn't change on Marc's end we should be good in about 30 minutes. Sincerely, Joshua D. Drake On Thu, 2 Dec 2004, Michael Fuhr wrote: The list archives at http://archives.postgresql.org/ don't appear to have been updated since Tue 30 Nov. Is something amiss? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(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: [GENERAL] List archives not being updated?
On Thu, 2 Dec 2004, Michael Fuhr wrote: The list archives at http://archives.postgresql.org/ don't appear to have been updated since Tue 30 Nov. Is something amiss? Fix. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(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: [GENERAL] Pl/Perl w/ Postgres 8.0 on Windows
Thanks to both of you gentlemen. I actually had to restart Postgres after installing ActiveState Perl before I could successfully run createlang and add the language. Seems to be working now, though. Cheers, Eric Magnus Hagander wrote: Hi all, I am throughly enjoying using Postgres 8.0 on Windows, so I can develop my Windows client against Postgres. I would like to write some pl/perl functions, and I'm a little stumped. Now comes the sad confession - I installed PG from the Windows installer rather than building it. Can I still build pl/perl against it? Better yet, is there a pl/perl binary for Windows? Well, aren't you in luck today :-) The MSI install *always* installs the plperl DLL. IF you have installed ActiveState perl, it even gives you the option to enable it in template1 durnig the install. So you need to: 1) Install ActiveState Perl 5.8, if you haven't already (that's what it's compiled against) 2) Run createlang for PL/Perl, similar as you'd do on Unix. (2) can be replaced with uninstall/reinstall of the MSI if you haven't started using your database yet. The option to enable the two plperl variants should be ungrayed if it finds activeState Perl 5.8 in the system path. //Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] List archives not being updated?
On Thu, 2 Dec 2004, Joshua D. Drake wrote: Marc G. Fournier wrote: Joshua? Taking a look now. Looks like we are having a minor DNS issue which is causing the DNS lookup to srv5 to fail. 'k, let me know how it goes ... just checked UUnet's name server: # nslookup svr5.postgresql.org ns.uunet.ca Server: ns.uunet.ca Address: 142.77.1.1 Name:svr5.postgresql.org Address: 66.98.251.16 and it resolves fine :( Sincerely, Joshua D. Drake Everything looks fine on the main server itself ... and John grab'd his update this morning around 3am, so rsyncd on our side is working as well ... problems on your end? On Thu, 2 Dec 2004, Michael Fuhr wrote: The list archives at http://archives.postgresql.org/ don't appear to have been updated since Tue 30 Nov. Is something amiss? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK
On Thu, Dec 02, 2004 at 12:43:57PM -0800, OpenMacNews wrote: given my additions of: -n do not reinitialize shared memory after abnormal exit -i enable TCP/IP connections -h HOSTNAME host name or IP address to listen on Why don't you use postgresql.conf for this, rather than modifying the start script? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No necesitamos banderas No reconocemos fronteras (Jorge González) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK
OpenMacNews [EMAIL PROTECTED] writes: LOG: 0: logger shutting down LOCATION: SysLoggerMain, syslogger.c:361 I should have twigged to that before --- if you're running the syslogger, then nothing except very early startup messages is going to go to stderr. Look in wherever you told it to put the log output. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] List archives not being updated?
'k, let me know how it goes ... just checked UUnet's name server: # nslookup svr5.postgresql.org ns.uunet.ca Server: ns.uunet.ca Address: 142.77.1.1 Name:svr5.postgresql.org Address: 66.98.251.16 and it resolves fine :( Yeah it is definately a local issue here. Sincerely, Joshua D. Drake Sincerely, Joshua D. Drake Everything looks fine on the main server itself ... and John grab'd his update this morning around 3am, so rsyncd on our side is working as well ... problems on your end? On Thu, 2 Dec 2004, Michael Fuhr wrote: The list archives at http://archives.postgresql.org/ don't appear to have been updated since Tue 30 Nov. Is something amiss? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] List archives not being updated?
On Thu, 2 Dec 2004, Joshua D. Drake wrote: Joshua D. Drake wrote: Marc G. Fournier wrote: Joshua? Taking a look now. Looks like we are having a minor DNS issue which is causing the DNS lookup to srv5 to fail. Sincerely, Joshua D. Drake Everything looks fine on the main server itself ... and John grab'd his update this morning around 3am, so rsyncd on our side is working as well ... problems on your end? As a temporary solution I put srv5 in the hosts file. So as long as the ip doesn't change on Marc's end we should be good in about 30 minutes. erk, still like to know why DNS is amiss though :( But, that IP is static, so isn't going to change any time soon ... are you having issues with any of the other DNS? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Select Database
On 12/2/2004 4:39 AM, ON.KG wrote:
Hi All!
How could I select another database without new connection?
For example, in PHP+MySQL we have mysql_select_db('database_name');
You can't. An existing session cannot change the database connected to.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Upcoming Changes to News Server ...
On Wed, Dec 01, 2004 at 06:25:53PM +, Woodchuck Bill wrote: Jan, Gary may be blunt at times, but try to understand things from his perspective. He is posting to Usenet. He expects his replies to appear on Usenet. You are accustomed to your way of writing and reading messages. He is accustomed to his way. Perhaps a bit overstated, his point is that if one or more comp.databases.postgresql.* Big Eight newsgroups are created, and they are gated to these mailing lists, it would open the doors for potentially many more participants from the Usenet side. Some of these participants will be just as confused and annoyed about why they are receiving so many e-mails after they post to Usenet. New posters will probably not even know that the groups were gated to the lists, so they expect unmoderated responses to Usenet, not messages from people they don't know, or messages from a moderator's auto-robot. FWIW mutt (the MUA) has both a mailing list and a newsgroup, there is no passing of messages from one to the other at all. There are quite a few people (me included) who frequent both the newsgroup and the mailing list. It all seems to work quite well. In the case of mutt the mailing list tends to get the more specialised mutt related discussion whereas the newsgroup gets more beginners and general questions. -- Chris Green ([EMAIL PROTECTED]) Never ascribe to malice, that which can be explained by incompetence. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK
hi tom, LOG: 0: logger shutting down LOCATION: SysLoggerMain, syslogger.c:361 I should have twigged to that before --- if you're running the syslogger, then nothing except very early startup messages is going to go to stderr. Look in wherever you told it to put the log output. i thought i was, in that the startup script was 'dumping' to /var/devlogs/postgres.log. also, given my logging section from my conf file: ## ## ERROR REPORTING AND LOGGING # log_destination = 'stderr' # relevant when logging to stderr: redirect_stderr = true log_directory = '/var/devlogs' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # relevant when logging to syslog: syslog_facility = 'LOCAL0' syslog_ident = 'postgres' client_min_messages = debug5 log_min_messages =debug5 log_error_verbosity = verbose log_min_error_statement = debug5 there's been no trace of any output to any 'postgresql-%Y-%m-%d_%H%M%S.log' files. while stumbling around, though, i noticed that after an un-successful startup (i.e., no pgsql launched), there, nonetheless, WAS a pgsql pid file in my process dir. odd ... so i deleted it, rebooted, and - voila! pgsql is up running ... and there are now dated log files, as well. despite being able to start/stop pgsql from cmd line at will, *something* in my system is not removing the pid file. although i've seen nothing pid-related in my logs, preceding my startup file launch cmd with a pid check/delete: if [ -f /var/run/postgresql.pid ]; then rm -rf /var/run/postgresql.pid fi (launch cmd) seems to have done the trick. i can now reboot w/ pgsql launch on start without fail. so, (a) i'll now hunt-n-destroy why i'm having a lingering pid file lying around, and why a restart-launch chokes on an existing pid, but not a cmd-line launch? (b) i might suggest that such a check be placed in the example startup script for safety's sake ... although you'd have to check for the defined pid path+file, of course. thx! for your guidance =) cheers, richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Rules
HI, Planning on witting a rule for a view, and i was wondering if anyone could suggest a good Internet resource? thx begin:vcard fn:Jamie Deppeler n:Deppeler;Jamie org:Once;Development adr:;;46 Roseneath Street;North Geelong;Vic;3215;Australia email;internet:[EMAIL PROTECTED] title:Database Admin tel;work:+61 3 52278 6699 url:http://www.doitonce.net.au version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Adding Reply-To: listname to Lists configuration ...
Chris Green [EMAIL PROTECTED] wrote: On Thu, Dec 02, 2004 at 01:59:07AM -0500, Greg Stark wrote: (On that note I would dearly love to get rid of the stupid [GENERAL] [HACKERS] etc tags? ... [snip] I absolutely agree I hate the damn things with a passion. and I've implemented a quick fix using my procmail recipes:- :0 fh * ^TOpostgres | sed 's/\[GENERAL\]//' :0 A: postgres I like this better (for if you're on more than one pgsql list): :0 fh * [EMAIL PROTECTED] |perl -p -e 's/\[(ADMIN|GENERAL|HACKERS)\] //og' Plus it has the added advantage of dealing with things like, oh, say Subject: Re: [GENERAL] [HACKERS] ... ;) I now get to see more of the subject without the distraction of [GENERAL] stuck in the middle of it! :-) Yup :) Jim ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Rules
Have you looked at the html docs? It looks like 34.2. Views and the Rule System might be what you are looking for, but I didn't check it closely. On Thursday 02 December 2004 05:34 pm, Jamie Deppeler saith: HI, Planning on witting a rule for a view, and i was wondering if anyone could suggest a good Internet resource? thx -- Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(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: [GENERAL] data integrity and inserts
On Thu, Dec 02, 2004 at 14:20:35 -0600, Timothy Perrigo [EMAIL PROTECTED] wrote: The first way also makes it possible to put the constraint on multiple fields: create unique index uidx_abc on my_table(col_a, col_b, col_c); You can do that with unique constraints as well. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK
OpenMacNews [EMAIL PROTECTED] writes: although i've seen nothing pid-related in my logs, preceding my startup file launch cmd with a pid check/delete: if [ -f /var/run/postgresql.pid ]; then rm -rf /var/run/postgresql.pid fi (launch cmd) seems to have done the trick. i can now reboot w/ pgsql launch on start without fail. In that case it's a problem in your launch script. The postmaster doesn't even know that such a file exists; it keeps its lock file in the data directory. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start;
hi tom, In that case it's a problem in your launch script. The postmaster doesn't even know that such a file exists; it keeps its lock file in the data directory. well, h. the launch script is currently simplified (for testing) to just the pid-checking-if-stmt + the single line launch cmd. there's honestly not much left to have a problem with ... note that my cmd line refers to the conf file, which has the external PID id'd in it: external_pid_file = '/var/run/postgresql.pid' i've set it up to be (eventually) watched by a watchdog app ... so, wouldn't (a) the postmaster know abt the PID file, and (b) check for its existence? or am i misunderstanding the purpose/use of the external pid? cheers, richard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK
OpenMacNews [EMAIL PROTECTED] writes:
note that my cmd line refers to the conf file, which has the external
PID id'd in it:
external_pid_file = '/var/run/postgresql.pid'
Oh, now you tell us ;-)
Still, I'm not sure what could be the problem. The only code that
reacts to that setting is in postmaster.c:
/*
* Write the external PID file if requested
*/
if (external_pid_file)
{
FILE *fpidfile = fopen(external_pid_file, w);
if (fpidfile)
{
fprintf(fpidfile, %d\n, MyProcPid);
fclose(fpidfile);
/* Should we remove the pid file on postmaster exit? */
}
else
write_stderr(%s: could not write external PID file \%s\: %s\n,
progname, external_pid_file, strerror(errno));
}
I suppose that the fopen might have failed (maybe the original pid file
wasn't writable by the postmaster??), but why wouldn't it have printed
an error message and kept going?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start;
hi, note that my cmd line refers to the conf file, which has the external PID id'd in it: external_pid_file = '/var/run/postgresql.pid' Oh, now you tell us ;-) heh. sorry -- just thought it was SOP. in case you haven't noticed, i'm at that 'wunnerful' ramp-up stage that i dunno what i dunno ... or ... er ... or know what i should know ... or somesuch ... =8-D write_stderr(%s: could not write external PID file \%s\: %s\n, progname, external_pid_file, strerror(errno)); } simple enuf ... I suppose that the fopen might have failed (maybe the original pid file wasn't writable by the postmaster??), just checked -- looks ok. PID is properly 'owned operated' by the postmaster superuser defined in the launch command but why wouldn't it have printed an error message and kept going? that's the rub. i'd expect to see it in the logs, as well. i just did a simple experiment. disable PIFfile check/delete in startup script stop postgres delete PIDfile (if still there) reboot --- postgres launches OK verify PIDfile exists ... it does --- can start/stop pgsql at will @ cmd line stop postgres touch PIDfile (if _not_ there) reboot -- NO launch, nothing in the logs verify PIDfile exists ... it does --- can start/stop pgsql at will @ cmd line reboot -- still NO launch, nothing in the logs verify PIDfile still exists ... it does --- can start/stop pgsql at will @ cmd line stop postgres delete PIDfile reboot -- back to normal all reproducible. imho, it's acting like the cmd line launch is working with a different PID file ... somethin's wonky. so, (1) i have a workaround for the moment via the script check (couldn't hurt, really, to add the check to the startup script ...) (2) since i've been appropriately mangling my system while getting this all running, i think it may be time for a wipe-n-reinstall ... who knows what i've done to myself? as you've mentioned, i wonder if i've an odd permission on a process or log dir somehwere ... cheers, richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK
OpenMacNews [EMAIL PROTECTED] writes: stop postgres touch PIDfile (if _not_ there) reboot -- NO launch, nothing in the logs verify PIDfile exists ... it does But who is it owned by, and with what permissions? If you do the touch as some other user than the postmaster runs as, it's very plausible the postmaster can't write the file. (That doesn't yet explain why it goes south afterward, but first we need to understand the conditions that make it fail.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start;
hi, But who is it owned by, and with what permissions? same owner as postmaster, 0644 or 0600 If you do the touch as some other user than the postmaster runs as, it's very plausible the postmaster can't write the file. (That doesn't yet explain why it goes south afterward, but first we need to understand the conditions that make it fail.) yup. agreed. postmaster launched as 'testuser', pidfile touched as: sudo -u testuser touch /var/run/postgresql.pid resulting in: -rw-r--r-- 1 testuser testuser 4 Dec 2 14:07 postgresql.pid fwiw, i've got a clean build under way on another box: pgsql, prereqs and dir hierarchy will all be 'fresh'. we'll see if it's me (betcha! there's been a LOT going on on _this_ box ... more than pgsql) or the code ... richard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start;
(From someone else who doesn't know what doesn't know, ... :-/) (B (Bsudo -u testuser sh -c "nohup /usr/local/pgsql/bin/postmaster [...] (B... (B (B note that my cmd line refers to the conf file, which has the external (B PID id'd in it: (B (B external_pid_file = '/var/run/postgresql.pid' (B ... (B just checked -- looks ok. PID is properly 'owned operated' by the (B postmaster (B superuser defined in the launch command (B (BWho owns /var/run? What group? Does testuser have permission to delete (Bfiles there? (May need to add testuser to the wheel or admin group?) (B (BAnother thought, try su -c instead of sudo? (B (B(See warning on first line. It's been a while since I've mucked that (Bdeep in the Mac OS X configurations, and my box is still on 10.2, so I'm (Bprobably just blowing smoke.) (B (B-- (BJoel Rees [EMAIL PROTECTED] (Bdigitcom, inc. $B3t<02q
Re: [GENERAL] Rules
Planning on witting a rule for a view, and i was wondering if anyone could suggest a good Internet resource? http://www.postgresql.org (Sorry, couldn't resist.) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] relation does not exist error
I'm evaluationg PostgreSQL 8.0 beta 5 for a new development project. I've created a user 'simon' and a database named 'WMSDV'. I've created some tables (see below) and assigned arwdRxt privledges to the 'simon' user. However, when i use psql, I am unable to select these tables. I receive a 'relation 'tabename' does not exist error. I'm completely new to PostgreSQL, and this problem has me stumped. Can anyone point me in the right direction? Sw. WMSDV=# \dt List of relations Schema | Name| Type | Owner +---+---+--- public | Customers | table | simon public | Persons | table | simon (2 rows) WMSDV=# select * from Customers; ERROR: relation customers does not exist WMSDV=# select * from public.Customers; ERROR: relation public.customers does not exist WMSDV=# ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] relation does not exist error
On Fri, Dec 03, 2004 at 11:01:16AM +0800, Simon Wittber wrote: List of relations Schema | Name| Type | Owner +---+---+--- public | Customers | table | simon public | Persons | table | simon (2 rows) WMSDV=# select * from Customers; ERROR: relation customers does not exist See the Identifiers and Key Words section in the SQL Syntax of the PostgreSQL documentation. Pay particular attention to what it says about case sensitivity, case folding, and quoting of identifiers. -- 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: [GENERAL] relation does not exist error
Simon Wittber [EMAIL PROTECTED] writes: WMSDV=# \dt List of relations Schema | Name| Type | Owner +---+---+--- public | Customers | table | simon public | Persons | table | simon (2 rows) WMSDV=# select * from Customers; ERROR: relation customers does not exist Identifiers get mashed to lower case unless you quote them: SELECT * FROM Customers; -Doug ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] table inheritance and DB design
Greetings. I am trying to create a database, which allows me to store appointment information. The key here is that I don't know what resourceswill be associated with an appointment, but they will all have a unique id. So, I want to have an Appointment table, a Resource table and a many-to-many relation let's call it AppRes, which associates an appointment with a resource. Now, I want to have several tables, say Car and Driver, which INHERIT from the Resource table. I also want AppRes table can enforce a ref. constraint on the Resource table. So, in the future I can add a Room table and be able to associate its recordswith an appointments via AppRes just by making the Room table inherit from the Resource table. I like this idea a lot, but I noticed that the current version of postgresonly enforcesFK constraints on the top-level table Resource, and notonits children. So, I cannot insert a record in a Car table and then reference it from the AppRes table, because postgres throws an error saying that this record does not exist in the Resource table. So, my first question is whenFK constraints willbe"fixed" toinclude children tables? My second question is if there is a design, which will allow me to add different types of resources (Cars, Drivers, Rooms, etc) and have FK constraints enforced in AppRes table? Thanks. Alec Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. Learn more.
Re: [GENERAL] Rules
Planning on witting a rule for a view, and i was wondering if anyone could suggest a good Internet resource? http://www.postgresql.org (Sorry, couldn't resist.) But here is a simple working example of making a view updatable: CREATE TABLE consumable ( consumable_pk serial NOT NULL, consumable_type_pk integer NOT NULL, manufacturer_pk integer NOT NULL, part_number character varying(18) NOT NULL, quantity_on_hand integer, reorder_quantity integer ) WITHOUT OIDS; CREATE VIEW consumables AS SELECT manufacturer.manufacturer_pk, consumable.consumable_pk, manufacturer.manufacturer, consumable_type.consumable_type, consumable.part_number, consumable.quantity_on_hand, reorder_quantity, CASE WHEN (reorder_quantity consumable.quantity_on_hand) THEN (reorder_quantity - consumable.quantity_on_hand) ELSE 0 END AS requisition_quantity FROM ((consumable LEFT JOIN consumable_type USING (consumable_type_pk)) LEFT JOIN manufacturer USING (manufacturer_pk)) ORDER BY manufacturer.manufacturer, consumable_type.consumable_type, consumable.part_number; CREATE RULE consumables_rd AS ON DELETE TO consumables DO INSTEAD NOTHING; CREATE RULE consumables_ri AS ON INSERT TO consumables DO INSTEAD NOTHING; CREATE RULE consumables_ru AS ON UPDATE TO consumables DO INSTEAD UPDATE consumable SET part_number = new.part_number, quantity_on_hand = new.quantity_on_hand, reorder_quantity = new.reorder_quantity WHERE ((consumable.consumable_pk = old.consumable_pk) AND (consumable.manufacturer_pk = old.manufacturer_pk)); ---(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: [GENERAL] relation does not exist error
Wow, 4 responses in 10 minutes to my newbie question. Thanks guys. I'm coming from a SQL Server background, so quoting table names didn't immediately spring to mind. I can see support will not be a problem. 10 out of 10. Sw. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] table inheritance and DB design
I am trying to create a database, which allows me to store appointment information. ... Now, I want to have several tables, say Car and Driver, which INHERIT from the Resource table. I also want AppRes table can enforce a ref. constraint on the Resource table. So, in the future I can add a Room table and be able to associate its records with an appointments via AppRes just by making the Room table inherit from the Resource table. I like this idea a lot, but I noticed that the current version of postgres When I first read in the documentation about inheritance, I was pretty excited, too, So, my first question is when FK constraints will be fixed to include children tables? But after testing out some design alternatives, I really didn't like the way it worked. And in researching for help (as you are now), I learned that the unusual behavior (or at least the behavior that seems weird to me) regarding relational integrity and uniquness constraints as been around for a while, and some people actually think is is SUPPOSED to work that way ... My second question is if there is a design, which will allow me to add different types of resources (Cars, Drivers, Rooms, etc) and have FK constraints enforced in AppRes table? I found that I could do what I want using standard normalization techniques, foreign key relationships, and on insert triggers. The tables that you propose to inherit from Resources should just be typical many-to-many relations that associate key values from Resources to Appointments. Each of these tables will have foreign key references to a mutually-exlusive subset of the rows in Resource depending on what resource type the rows represent. Resource will have a serial type primary key, and each of the psuedo-inherited tables will have a before insert trigger that does an insert into Resource and then takes the new serial primary key value from the row added to Resource and uses that value in its own foreign key reference to the Resource table in one column and assigns a foreign key reference in its other column to the row in the Appointment table. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] table inheritance and DB design
On Thu, Dec 02, 2004 at 10:53:37PM -0500, Berend Tober wrote: I learned that the unusual behavior (or at least the behavior that seems weird to me) regarding relational integrity and uniquness constraints as been around for a while, and some people actually think is is SUPPOSED to work that way ... Who would that be? Because I've always thought that most people (everyone?) think of it as a bug that nobody has bothered to fix. Not that the fix is easy, mind you ... -- Alvaro Herrera ([EMAIL PROTECTED]) Porque francamente, si para saber manejarse a uno mismo hubiera que rendir examen... ¿Quién es el machito que tendría carnet? (Mafalda) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] general questions on Postgresql and deployment on win32 platform
I have gone through the documentation that come with version 8 beta 4 and I have a number of questions. (1) backup/restore I notice that in the documentation, it seems to suggest that an online backup, made via pg_start_backup() and pg_stop_backup() functions would back up all databases running on the server, rather than any specific instance. If that's the case, is it correct to state that WAL is instance specific rather than database specific? Does it also mean that I must back up and restore all the databases (or database cluster in Postgresql Speak) even if I am only interested in 1 database? (2) WAL location I can't seem to find a way to move WAL log to a different location other than data/pg_xlog. On *nix platform, it's a simple matter of creating a symbolic link. But on win32, there is no equivalent. However, even under *nix system, I believe symbolic link can only be created for directories on the same hard drive. This seems less than optimal. Typically, one would place database files on RAID 5 drives (to maximize random access speed) and log files on mirrored drives (to maximize sequential access speed). (3) Trigger Is it correct to state that old and new keywords are only available to PL/pgSQL function (but not a SQL function) and to row level trigger? Is there anyway to refer to old and new rows for statement level trigger? The visibility rule is also quite confusing. Can someone please clarify it in terms of before/after and row/statement level triggers (i.e., 4 possible permutations). (4) Process/thread on win32 platform On win32 platform, a process simply provides environment for a thread to run (whereas a thread is more like a *nix process, win32 has something called fibre that is more lightweight than thread). Typically, it's not recommended to start a new process per connection for DBMS running on win32 lest its performance would suffer. For example, SQL Server actually spawns threads to manage connections rather than processes. Does the Postgresql on win32 platform use process or thread when a connection is made? I notice that the configuration file lets you specify maximum number of connections. If I have a very high number of persistent concurrent users (thus preventing connection pooling from helping much), would performance suffer significantly? Thanks for any responses. ---(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
[GENERAL] psql connection timeout
Is there a way to control how long psql waits for a connection? If the host IP address is mistyped, psql (8.0b4) hangs for over a minute on OS X. Thanks, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] PostgresSQL Beta 5 Install Problem on Windows Server 2003
The following occured during the installation of Beta-5 on Windows 2003 Standard Server. After clicking 'Finish' and files copying... Failed to connect to the database. Procedural languages files are installed, but are not activated in any databases ok server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request. ok Failed to connect to the database. Contrib files are installed, but are not activated in any databases. ok server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request. ok I have tried the searching mailing lists and archives, but no solutions found. Perhaps I missed it? Can anyone help? Gary ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was: comp.databases.postgresql.*)
Marc G. Fournier From: wrote: Mike Cox [EMAIL PROTECTED] writes: Marc G. Fournier From: wrote: The pgsql.* hierarchy is a not a private one, it is a public one carried by several of the large usenet servers. Doesn't private denote a hierarchy in its own domain such as microsoft.*, and gnu.*? If I used an incorrect term, I'll be happy to change it. Not sure what general opinion is here, so hopefully someone else will jump in, but to me 'private' means 'not accessible to the public' ... Hopefully someone like Russ will tell us the correct term for domains like microsoft.* and gnu.*. Those on the mailing lists, or in pgsql.*, visit news.groups to read the RFD and make your opinions and voice heard! It is important to shape it into something that will enhance and benfit users. The charter and the RFD should go through a trial by fire to make it excellent. Give me your criicizm, suggestions,etc. I can handle it! RFDs are generally, by tradition, discussed in news.groups. That way those who are interested can participate without being off-topic in the mailing lists and pgsql.* hierarchies. I'm trying to balance being respectfull of the mailing lists and pgsql.* groups by informing them of what is happening, but also of not filling their lists needlessly with RFD talk. ;-) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was: comp.databases.postgresql.*)
Marc G. Fournier From: wrote: Mike Cox [EMAIL PROTECTED] writes: REQUEST FOR DISCUSSION (RFD) unmoderated group comp.databases.postgresql This is a formal Request For Discussion (RFD) for the creation of the worldwide unmoderated Usenet newsgroup comp.databases.postgresql. This is not a Call for Votes (CFV); you cannot vote at this time. Procedural details are below. CHANGES: The changes from the previous RFD are: 1. The removal of the following groups from the RFD: unmoderated group comp.databases.postgresql.admin unmoderated group comp.databases.postgresql.hackers unmoderated group comp.databases.postgresql.novice unmoderated group comp.databases.postgresql.sql 2. The proposed comp.databases.postgresql.general group was renamed to comp.databases.postgresql. 3. The charter has been changed to allow discussion of all topics that were in the separate groups. 4. The comp.databases.postgresql.general group will not be gated to any other group or mailing list. 5. The rationale was changed to reflect the removal of the bogus PostgreSQL groups from the comp.databases.* hierarchy. Wern't these these the same changes as were between the 1st and 2nd RFDs? No. The 2nd RFD added 4 groups and the official charters from the postgresql website. To provide a Big Eight newsgroup for users of the PostgreSQL Relational Database Management System. Currently there is a mailing list gated to a private hierarchy. The pgsql.* hierarchy is a not a private one, it is a public one carried by several of the large usenet servers. Doesn't private denote a hierarchy in its own domain such as microsoft.*, and gnu.*? If I used an incorrect term, I'll be happy to change it. *Announcements of new versions of PostgreSQL, PostgreSQL related software, and documentation. *PostgreSQL performance, benchmarking and related topics. *Discussions pertaining to the administration, compilation and installation of PostgreSQL. *Assisting beginners in using the PostgreSQL Relational Database Management system. Help answer basic questions. *SQL related matters including normalization, and theory as it applies to PostgreSQL. *General discussions of PostgreSQL. *PostgreSQL Promotional ideas, etc. *Programming using PostgreSQL. Stored Proceedures, Server-Side functions written in C, PL/pgSQL,PL/Perl, and other languages. *Discussions of PostgreSQL interfaces, including JDBC and ODBC. *Discussions of the Contrib packages. Is there a reason why this is broken down into specific areas of discussion, or is this group *restricted* to just these? If not, are you sure you haven't missed anything? Wouldn't a more general: This group is meant to discuss all aspects of the PostgreSQL RDBMS PostgreSQL development, and bug reports must be discussed in the mailing lists because the devopers are there. The PostgreSQL comp. group does have a well defined, and broad discussion scope. That being said, maybe you missed the line in the charter that reads: *General discussions of PostgreSQL. Many informed individuals from news.groups and private emails from PostgreSQL users told me to include a detailed charter. I followed that advice, using the broad input for what they wanted to see in a PostgreSQL charter. be in line with the purpose of the group? This RFD has been posted to the following newsgroups: news.announce.newgroups, news.groups, comp.databases, comp.unix.bsd.freebsd.misc,comp.os.linux.misc Any reason not to include pgsql.general? Or is that an audience you don't want included in the discussion? Of course not. They should participate because of the wonderful benefits the big 8 comp group will bring to the community. The group will enable usenet PostgreSQL users to participate effectively in PostgreSQL discussions. It will bring greater exposure to the mailing lists as we *will* post a pointer weekly about the existance of the mailing lists and the highly specialized pgsql.* groups. That way users will not wonder why there isn't a postgresql big 8 group. MySQL has an RFD in news.groups, so it will be represented in usenet's big 8. PostgreSQL needs that presence too. There is pent up demand for it. The pgsql.* hierarchy/mailing list is correctly focused on making the mailing list experience wonderful. In the same spirt, the comp.databases.postgresql group will make the usenet experience excellent. Those who prefer Usenet are under-served because the mailing-list/pgsql.* gateway does not provide a seemless usenet experience. Many feel that getting emails in reply to a usenet post does not capture what usenet should be like. The issues of having to wait for their posts to make it to the pgsql.* lists and hierarchy are also a concern. Those interested should visit news.groups and follow the passionate discussions on these issues. There is also the issue of having to ask their news providers to carry the pgsql.* hierarchy. With a big 8 postgreql
Re: [GENERAL] psql connection timeout
On Thu, Dec 02, 2004 at 11:32:34PM -0500, John DeSoi wrote: Is there a way to control how long psql waits for a connection? If the host IP address is mistyped, psql (8.0b4) hangs for over a minute on OS X. Try setting the PGCONNECT_TIMEOUT environment variable: env PGCONNECT_TIMEOUT=5 psql -h bogushost If you always want to use a particular timeout then set the environment variable in your shell's startup script. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes
On my opinion row_count should not be null due to the last operation produced some records, copied to the temp table. Basically it's irrelevant for me whether it fixed or not, I'll have to port this code fast and going to use select count. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 5:53 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes Maksim Likharev [EMAIL PROTECTED] writes: consider following code: CREATE OR REPLACE FUNCTION rowcount_test() RETURNS bigint AS ' DECLARE base_hits bigint; BEGIN CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data; GET DIAGNOSTICS base_hits = ROW_COUNT; RETURN base_hits; END; ' LANGUAGE PLPGSQL VOLATILE; in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp table in 7.4.5 GET DIAGNOSTICS returns 0 Hmm. I'm not sure if that's a bug or an improvement. The command did not return any rows to plpgsql, so in that sense row_count = 0 is correct, but I can see why you feel you've lost some capability. Anyone else have an opinion about this? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Select Database
Thanx
JW On 12/2/2004 4:39 AM, ON.KG wrote:
Hi All!
How could I select another database without new connection?
For example, in PHP+MySQL we have mysql_select_db('database_name');
JW You can't. An existing session cannot change the database connected to.
JW Jan
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Is there a way to view a rewritten query?
I'm trying to set up some complex rules and having a hard time figuring out why they won't work. Being able to see the query after all the rules have been applied would be a huge help, but I can't seem to find a way to do this. log_statement only shows the original. debug_print_rewritten works, but displays it in such a way as to be virtually unusable. someone please tell me there's a simple way of doing this cause i'm ready to take a hammer to my computer. thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start
hi joel, just checked -- looks ok. PID is properly 'owned operated' by the postmaster superuser defined in the launch command Who owns /var/run? What group? Does testuser have permission to delete files there? (May need to add testuser to the wheel or admin group?) good points =) already done, tho ... % ls -ald /var/run drwxrwxr-x 29 root daemon 986 Dec 2 20:53 /var/run % niutil -read / /groups/daemon name: daemon gid: 1 passwd: * users: root testuser Another thought, try su -c instead of sudo? afaik, shouldn't make a diff, as testuser is in /etc/sudoers ... thx! Kobe, Japan --- *there's* the beef ... :p cheers, richard ---(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
[GENERAL] reclaiming diskspace bloat w/near-zero downtime
I need to reclaim the diskspace from a heavily updated 7.3.4 table which has
grown 95% bloated to 20gb of disk (indices and toast included), and I need
to reclaim it while the table continues to get updates, and without
interrupting/delaying access more than a few seconds. This is an
explanation of what I've tried so far along with some observations and a
request for other ideas to try.
Some constraints on my efforts: Stopping the cluster for any reason is
pretty undesirable, as is vacuum full. Upgrading to 7.4.6 via slony is an
option, but I'm curious if there is an easier way.
I assume this bloat is basically due to the garbage generation outpacing the
garbage collection. The bloat does not appear to be in indices, so the
index bloat issue does not seem to be in play much here. The table gets
about 5% INSERTs, 95% UPDATEs, and 0% DELETEs, and UPDATEs are not changing
indexed values, so maybe that makes sense that indices are not bloated.
The UPDATEs to the table consist of appending text to a text colum (UPDATE
foo SET value = 'new text' || value). I had max_fsm_pages at 4M (for upto
32GB of disk?) and 8K (8000 tables, etc), both far more than I needed
initially, but it still bloated. [SIDEBAR: Vacuum + fsm is not working as
I expected; it is clearly not reclaiming space resulting from the UPDATEs.
If I UPDATE 1 rows and then run vacuum, I was expecting/hoping that a
subsequent UPDATE of 9000 rows would largely reuse the space reclaimed from
the 1-row UPDATE.]
Anyway, I need to reclaim the space without stopping the cluster and without
blocking access to the table for more than a few seconds. I seem to have
found a way to do that for any table ('foo') that doesn't have fkey/func
dependencies:
create table foo_slim as exact schema of foo
begin
alter table foo rename foo_bloated
create view foo as select from foo_slim union all foo_bloated
create rules:
on insert to foo: insert into foo_slim
on delete to foo: delete from foo_bloated or foo_slim
on update to foo: if in foo_bloated,
insert new values into foo_slim
delete from foo_bloated
commit;
Once that's done, then the data begins to trickle into the new, slim table,
and the xfer can be speeded up by doing no-op updates on the foo view or a
select-for-update function on the foo_bloated table. Once all the data has
migrated over to foo_slim, the initial state can be restored with:
begin
drop view foo
alter table foo_slim rename to foo
commit;
and then foo_bloated can be dropped. That seems to work well enough.
But how to do it on a table with foreign keys and triggers, etc? I was
wondering if I could use the same basic approach but manually reconstitute
the oid linkages so that the triggers and functions stayed intact even
while renaming/dropping/etc, but sounds a little dicey. Any other ideas?
Thanks,
Ed
---(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: [GENERAL] Indexes?
Well, then it's decided to try with two indexes... Thx... :) BTJ Bruno Wolff III wrote: On Wed, Dec 01, 2004 at 23:16:48 -0800, Vincent Hikida [EMAIL PROTECTED] wrote: I believe that it is better to have a concatenated key of (toDate,FromDate). The reason the toDate should come first is that for more recent records, finding curDates less than toDate is much more selective than finding curDates greater than fromDate. Actually I'm not sure if fromDate is that helpful either as part of the concatenated key (it probably depends) but definitely not by itself. I combined index won't be very useful for the kind of search he is doing. And not having an index on FromDate could hurt in some cases depending on the distribution of values. ---(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 8: explain analyze is your friend
[GENERAL] pgFoundary?
I've seen references to pgFoundary on the mailling lists, but I can't seem to find it anywhere. Does it actually exist? Is it the predecessor to gborg? Speaking of gborg, how come it isn't accepting projects right now? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pgFoundary?
http://pgfoundry.org/ You had an extra 'a'. :) I'm not sure what the status of gborg is, at the moment. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Dec 3, 2004, at 12:28 AM, Jim C. Nasby wrote: I've seen references to pgFoundary on the mailling lists, but I can't seem to find it anywhere. Does it actually exist? Is it the predecessor to gborg? Speaking of gborg, how come it isn't accepting projects right now? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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: [GENERAL] Indexes?
Perhaps I'm missing something but let's say that the index has the following: toDate fromDate 1992-03-02 1991-01-23 1992-04-03 1990-06-13 1993-05-03 1991-01-22 ... ... ... 2004-12-01 2003-02-22 2005-03-04 2003-02-22 (a) 2005-03-05 2004-12-15 (b) 2005-03-05 2004-06-18 (c) 2007-04-12 2005-06-18 (d) Let's say that there are a million entries where the toDate is less than today 2004-12-02. That is less than (a) in the index. From the index then only a, b, c, and d should be scanned further. a and c would be picked based on the index values because 2004-12-02 is between the from and end date. However, b and d would be excluded immediately because the the from date is greater than 2004-12-02 and would save the optimizer from even reading the table for these index entries because the fromDate is in the index. This may be a somewhat extreme example but my experience is in most systems old historical data makes up the bulk of the data and newer data is a much smaller amount. In addition most people are interested in data from the most recent month. Of course I may be mistaken about the data distribution. Vincent - Original Message - From: Bruno Wolff III [EMAIL PROTECTED] To: Vincent Hikida [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, December 02, 2004 12:10 PM Subject: Re: [GENERAL] Indexes? On Wed, Dec 01, 2004 at 23:16:48 -0800, Vincent Hikida [EMAIL PROTECTED] wrote: I believe that it is better to have a concatenated key of (toDate,FromDate). The reason the toDate should come first is that for more recent records, finding curDates less than toDate is much more selective than finding curDates greater than fromDate. Actually I'm not sure if fromDate is that helpful either as part of the concatenated key (it probably depends) but definitely not by itself. I combined index won't be very useful for the kind of search he is doing. And not having an index on FromDate could hurt in some cases depending on the distribution of values. ---(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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgFoundary?
Then I guess my next question is: why isn't it linked to from http://postgresql.org ? On Fri, Dec 03, 2004 at 12:35:23AM -0600, Thomas F.O'Connell wrote: http://pgfoundry.org/ You had an extra 'a'. :) I'm not sure what the status of gborg is, at the moment. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Dec 3, 2004, at 12:28 AM, Jim C. Nasby wrote: I've seen references to pgFoundary on the mailling lists, but I can't seem to find it anywhere. Does it actually exist? Is it the predecessor to gborg? Speaking of gborg, how come it isn't accepting projects right now? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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 -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
