[GENERAL] Postgres Library natively available for Mac OSX Intel?
Hello! I would like to know if somebody already has a Mac OSX Intel 10.4.5 pg-Library (for C, C++, Objective C) or knows how to compile it? Or even better, a fat-library (powerpc, i386) would be even better :-) Thanks for any information, regards Philipp Ott ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] configure: error: file 'tclConfig.sh' is required for Tcl
hi! I configure postgresql-8.1.3 on fedora core 4 with ./configure --with-perl --with-python --enable-nls --with-openssl --enable-debug --enable-cassert --with-tcl and being root. I've got this error: [snip configure output] checking for tclsh... /usr/bin/tclsh checking for tclConfig.sh... no configure: error: file 'tclConfig.sh' is required for Tcl --- I've veryfied tcl'installation with [EMAIL PROTECTED] pgsql]# rpm -qa | grep tcl tcl-8.4.9-3 but in directories /usr/lib/tcl8.4, /usr/share/tcl8.4, there ISN'T the file tclConfig.sh. How can i do? I've tried to uninstall tcl and re-install it with yum, but the result is the same...i still have not that 'tclConfig.sh' file!! help me please..! Tery-- Teresa NovielloChiedersi Sempre:Avro' il tempo di rifarlo?
Re: [GENERAL] configure: error: file 'tclConfig.sh' is required for Tcl
Hi! Being a Debian-user I haven't really got a clue about Fedora Core, but have you tried installing the tcl-devel-package? Kind regards Markus Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Teresa Noviello Gesendet: Freitag, 31. März 2006 10:16 An: pgsql-general@postgresql.org Betreff: [GENERAL] configure: error: file 'tclConfig.sh' is required for Tcl [...] I've veryfied tcl'installation with [EMAIL PROTECTED] pgsql]# rpm -qa | grep tcl tcl-8.4.9-3 but in directories /usr/lib/tcl8.4, /usr/share/tcl8.4, there ISN'T the file tclConfig.sh. [...] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] configure: error: file 'tclConfig.sh' is required for Tcl
I've installed tcl-devel-package!!! NOW IT WORKS!... (in my honest opinion it had to work also without it, but that's another story!) THANKS!!! TeryOn 3/31/06, Markus Wollny [EMAIL PROTECTED] wrote: Hi!Being a Debian-user I haven't really got a clue about Fedora Core, but have you tried installing the tcl-devel-package?Kind regards MarkusVon: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Im Auftrag von Teresa Noviello Gesendet: Freitag, 31. März 2006 10:16An: pgsql-general@postgresql.orgBetreff: [GENERAL] configure: error: file 'tclConfig.sh' is required for Tcl [...]I've veryfied tcl'installation with[EMAIL PROTECTED] pgsql]# rpm -qa | grep tcltcl-8.4.9-3but in directories /usr/lib/tcl8.4, /usr/share/tcl8.4, there ISN'T the file tclConfig.sh.[...]-- Teresa NovielloChiedersi Sempre:Avro' il tempo di rifarlo?
Re: [GENERAL] FAQ 1.1
Bruce Momjian schrieb: Douglas McNaught wrote: Michael Talbot-Wilson [EMAIL PROTECTED] writes: How, really, do people pronounce PostgreSQL? Postgres The first sentence of the FAQ is: PPostgreSQL is pronounced IPost-Gres-Q-L/I, and is also sometimes referred to as just IPostgres/I. Is that unclear? Maybe it would be better to write it like that in logos and stuff: PostGresQL or something like that ;) ++Tino ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] FAQ 1.1
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tino Wildenhain Sent: 31 March 2006 09:51 To: Bruce Momjian Cc: Douglas McNaught; Michael Talbot-Wilson; pgsql-general@postgresql.org Subject: Re: [GENERAL] FAQ 1.1 Bruce Momjian schrieb: Douglas McNaught wrote: Michael Talbot-Wilson [EMAIL PROTECTED] writes: How, really, do people pronounce PostgreSQL? Postgres The first sentence of the FAQ is: PPostgreSQL is pronounced IPost-Gres-Q-L/I, and is also sometimes referred to as just IPostgres/I. Is that unclear? Maybe it would be better to write it like that in logos and stuff: PostGresQL or something like that ;) Given the tendency people have to remove the capitalised bits to get 'postgre', we'd probably end up with 'ostres' :-) /D ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to concat strings so that trailing spaces remain
If you think that trailing spaces are significant data, you should probably be using VARCHAR not CHAR datatype. I have existing database where there are only CHAR columns, no any VARCHAR column. I'm not sure will my appl work if I change all char columns to varchar columns . Is it reasonable to change all occurences of CHAR to VARCHAR in database ? Are the following clauses exactly same for fields of type CHAR(10): WHERE r1.c1::VARCHAR(10) || r1.c2::VARCHAR(10) || r1.c3::VARCHAR(10) = r2.c1::VARCHAR(10) || r2.c2::VARCHAR(10) || r2.c3::VARCHAR(10) and WHERE r1.c1=r2.c1 and r1.c2=r2.c2 and r1.c3=r2.c3 and WHERE (r1.c1, r1.c2,r1.c3) = (r2.c1, r2.c2,r2.c3) Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PANIC: heap_update_redo: no block
Alex bahdushka [EMAIL PROTECTED] wrote (@)2006-03-18 23:30:33.035 MST[3791]PANIC: heap_update_redo: no block According to the discussion in pgsql-hackers, to finish this case, did you turn off the full_page_writes parameter? I hope the answer is yes ... Regards, Qingqing ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] How to delete all operators
Hi, I have a database with operators and functions in plpgsql. To update the data to the latest version I would like to drop all operators. There might be some, that I don't know. I don't have access to the database, but have to write a script, that will update the data. Is there any way to drop all operators (given they are all in the schema public) in a script? Something like (pseudocode): Drop all operators in schema public Thanks in advance for answers -- E-Mails und Internet immer und überall! 11 PocketWeb, perfekt mit GMX: http://www.gmx.net/de/go/pocketweb ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] pgsql continuing network issues
Hey all, I wrote a few days ago regarding networking issues I'm having. At this point, I'm at my wit's end and am hoping someone can help me. I am running postgres 8.1.0 on Windows XP w\SP2. I currently have PGSQL configured to accept connections from my local IP, 192.168.0.100 using the listen_addresses directive. If I try to connect to the database from the same machine using the psql program or using NPGSQL (.NET pgsql provider) it simply hangs. Nothing happens at all. I don't get errors in my event viewer, nor do I get any errors in the textfile log postgresql maintains. IF I change the listen_addresses directive to localhost, I can connect fine and everything works EXCEPT I get the message NOTICE: Unknown win32 socket error code: 10107 repeatedly anytime I do something. This would be acceptable, except that I need to be able to access this database at client sites. Can anyone help me or direct me to someone who can? I'm trying to push postgres to my clients, because it seems like a great RDBMs, but if I can't even get a server to work, I'm going to have to switch to something else. Thank you, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Performance Killer 'IN' ?
Joshua D. Drake wrote: Kai Hessing wrote: 1.) 21.5 seconds 2.) 363.7 seconds But it is still a significant difference. Can you provide an explain analyze of each query? There isn't an explain analyze of the first variant, because there are just 2000 SQL-Updates. The explain analyze for just one of the commands (UPDATE xyz SET status=-6 WHERE phon='xyz1' AND status-1;) is: -- Index Scan using phon_phon_idx on phon (cost=0.00..5193.83 rows=530 width=148) (actual time=0.146..0.146 rows=0 loops=1) Index Cond: ((phon)::text = 'xyz'::text) Filter: (status -1) Total runtime: 0.387 ms 1 Datensätze (means data sets) Laufzeit gesamt: 16.682 ms (means running time) -- The second one (UPDATE xyz WHERE id IN (xyz1, xyz2, ) AND status-1;) returns: -- Seq Scan on phon (cost=0.00..1573304.58 rows=105931 width=148) (actual time=369563.565..369563.565 rows=0 loops=1) Filter: phon)::text = 'xyz1) OR ((phon)::text = 'xyz2'::text) OR ((phon)::text = 'xyz3'::text) OR ((phon)::text = 'xyz4'::text) OR ... [all the 2000 entries from the IN clause] Total runtime: 369566.954 ms 667 Datensätze (means data sets) Laufzeit gesamt: 370,179.246 ms (means running time) -- Hope, that helps. Have a nice weekend. Being back to work on monday ;) *greets* Kai ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How to use result column names in having cause
CREATE TEMP TABLE foo( bar integer ); SELECT 123 AS x FROM foo GROUP BY 1 HAVING x AVG(bar) causes ERROR: column x does not exist Why ? How to make this working ? In real application I have long expression instead of 123 and do'nt want repeat this expression in HAVING clause. In VFP this select works OK. Andrus. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to use result column names in having cause
On 3/31/06, Andrus [EMAIL PROTECTED] wrote: CREATE TEMP TABLE foo( bar integer ); SELECT 123 AS x FROM foo GROUP BY 1 HAVING x AVG(bar) causes ERROR: column x does not exist Why ? How to make this working ? In real application I have long expression instead of 123 and do'nt want repeat this expression in HAVING clause. You have to repeat the expression. AS changes the output name, it can't be used either in the where clause or any other limiting factor like 'having': test=# create table t1(a int); test=# insert into t1(a) values (1); test=# SELECT a AS x from t1 where x=1; ERROR: column x does not exist -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] how to use pg_dump in windows xp
hi i have to create a database script file using pg_dump ,but i do not understant how to run it under windows enviornment plz hep me and replay mee soon...
Re: [GENERAL] how to use pg_dump in windows xp
am 31.03.2006, um 18:08:55 +0530 mailte deepak pal folgendes: hi i have to create a database script file using pg_dump ,but i do not understant how to run it under windows enviornment plz hep me and replay mee soon... execute it via psql, start psql and then \i your_script, or psql -f your_script. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to use result column names in having cause
In real application I have long expression instead of 123 and do'nt want repeat this expression in HAVING clause. You have to repeat the expression. AS changes the output name, it can't be used either in the where clause or any other limiting factor like 'having': Doc about HAVING condition says: Each column referenced in condition must unambiguously reference a grouping colum HAVING x AVG(bar) unambiguously references to a grouping column x Is this bug ? It is very tedious to repeat same column expression in a multiple times: one time in column expression, and n times in having clause. Are there plans to fix this? Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to use result column names in having cause
Here is my problematic query which runs OK in other DBMS. Only way to run this in Postgres is to duplicate reatasum expression two times in HAVING clause, right ? Andrus. SELECT 'z' as doktyyp, r1.dokumnr, r1.kuluobjekt as objekt, r1.rid2obj, r1.rid3obj, r1.rid4obj, r1.rid5obj, r1.rid6obj, r1.rid7obj, r1.rid8obj, r1.rid9obj, dok.tasumata, dok.raha, CASE WHEN ( sum(r2.reasumma)-AVG(dok.doksumma-dok.tasumata)= avg(r1.reasumma) AND avg(r1.reasumma)=0) OR ( sum(r2.reasumma)-AVG(dok.doksumma-dok.tasumata) avg(r1.reasumma) AND avg(r1.reasumma)0) THEN avg(r1.reasumma) ELSE sum(r2.reasumma)-AVG(dok.doksumma-dok.tasumata) END as reatasum FROM dok JOIN reakoond r1 USING (dokumnr) JOIN reakoond r2 USING (dokumnr) where ( r1.kuluobjekt::VARCHAR(10)||r1.rid2obj::VARCHAR(10)|| r1.rid3obj::VARCHAR(10)||r1.rid4obj::VARCHAR(10)|| r1.rid5obj::VARCHAR(10)|| r1.rid6obj::VARCHAR(10)||r1.rid7obj::VARCHAR(10)|| r1.rid8obj::VARCHAR(10)||r1.rid9obj::VARCHAR(10))= ( r2.kuluobjekt::VARCHAR(10)||r2.rid2obj::VARCHAR(10)|| r2.rid3obj::VARCHAR(10)||r2.rid4obj::VARCHAR(10)|| r2.rid5obj::VARCHAR(10)|| r2.rid6obj::VARCHAR(10)||r2.rid7obj::VARCHAR(10)|| r2.rid8obj::VARCHAR(10)||r2.rid9obj::VARCHAR(10) ) group by 1,2,3,4,5,6,7,8,9,10,11,12,13 having (reatasum0 AND avg(r1.reasumma)=0) OR (reatasum0 AND avg(r1.reasumma)0) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to use result column names in having cause
On 3/31/06, Andrus [EMAIL PROTECTED] wrote: In real application I have long expression instead of 123 and do'nt want repeat this expression in HAVING clause. You have to repeat the expression. AS changes the output name, it can't be used either in the where clause or any other limiting factor like 'having': Doc about HAVING condition says: Each column referenced in condition must unambiguously reference a grouping colum HAVING x AVG(bar) unambiguously references to a grouping column x Is this bug ? It is very tedious to repeat same column expression in a multiple times: one time in column expression, and n times in having clause. But you're not referencing x, you're trying to use AVG(bar) in your expression. I assume it's this way because the standard says so.. one of the more knowledgable list members will be able to confirm/deny this. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgsql continuing network issues
On Friday 31 March 2006 02:55 am, David Bernal wrote: Hey all, I wrote a few days ago regarding networking issues I'm having. At this point, I'm at my wit's end and am hoping someone can help me. I am running postgres 8.1.0 on Windows XP w\SP2. I currently have PGSQL configured to accept connections from my local IP, 192.168.0.100 using the listen_addresses directive. If I try to connect to the database from the same machine using the psql program or using NPGSQL (.NET pgsql provider) it simply hangs. Nothing happens at all. I don't get errors in my event viewer, nor do I get any errors in the textfile log postgresql maintains. IF I change the listen_addresses directive to localhost, I can connect fine and everything works EXCEPT I get the message NOTICE: Unknown win32 socket error code: 10107 repeatedly anytime I do something. This would be acceptable, except that I need to be able to access this database at client sites. Can anyone help me or direct me to someone who can? I'm trying to push postgres to my clients, because it seems like a great RDBMs, but if I can't even get a server to work, I'm going to have to switch to something else. Thank you, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Would it be possible to post the contents of your postgresql.conf and pg_hba.conf files? -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] unsubscribe from pgsql-de-allgemein
unsubscribe from pgsql-de-allgemein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgsql continuing network issues
Adrian Klaver wrote: On Friday 31 March 2006 02:55 am, David Bernal wrote: Hey all, I wrote a few days ago regarding networking issues I'm having. At this point, I'm at my wit's end and am hoping someone can help me. I am running postgres 8.1.0 on Windows XP w\SP2. I currently have PGSQL configured to accept connections from my local IP, 192.168.0.100 using the listen_addresses directive. Why don't you try this: set listen_addresses to * like so: listen_addresses = '*' Then set your pg_hba.conf to this: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # IPv4 local connections: hostall all 127.0.0.1/32 trust hostall all 192.168.0.100/32 trust For this example I put trust for the method to rule out any password issues, if you are confident you have no password issues change the method to md5 or whatever your choice is. You should for sure change the listen_addresses to * because you may have two NICs in the server or PC and somehow the external connections are connecting to the other nic. I always setup my win32 and Unix servers this way and have never had problems. Hope this helps, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to use result column names in having cause
On Friday 31 March 2006 08:30, chris smith wrote:
On 3/31/06, Andrus [EMAIL PROTECTED] wrote:
In real application I have long expression instead of 123 and do'nt
want repeat this expression in HAVING clause.
You have to repeat the expression. AS changes the output name, it
can't be used either in the where clause or any other limiting factor
like 'having':
Doc about HAVING condition says:
Each column referenced in condition must unambiguously reference a
grouping colum
HAVING x AVG(bar) unambiguously references to a grouping column x
Is this bug ? It is very tedious to repeat same column expression in a
multiple times: one time in column expression, and n times in having
clause.
But you're not referencing x, you're trying to use AVG(bar) in your
expression.
I assume it's this way because the standard says so.. one of the more
knowledgable list members will be able to confirm/deny this.
Yes, this behavior is driven by the sql standards. There is actually a very
nice paper on this subject if you are interested
http://web.onetel.com/~hughdarwen/TheThirdManifesto/Importance-of-Column-Names.pdf
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] configure: error: file 'tclConfig.sh' is required for Tcl
Teresa Noviello [EMAIL PROTECTED] writes: I've installed tcl-devel-package!!! NOW IT WORKS!... (in my honest opinion it had to work also without it, but that's another story!) Why do you think that? In general, the point of a foo-devel package is to carry the files needed to build (as opposed to just run) programs that use the foo package. That exactly describes what you're doing here, and that's why you needed tcl-devel. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to use result column names in having cause
On Fri, 31 Mar 2006, Andrus wrote: In real application I have long expression instead of 123 and do'nt want repeat this expression in HAVING clause. You have to repeat the expression. AS changes the output name, it can't be used either in the where clause or any other limiting factor like 'having': Doc about HAVING condition says: Each column referenced in condition must unambiguously reference a grouping colum HAVING x AVG(bar) unambiguously references to a grouping column x IIRC technically the query is invalid, because group by isn't supposed to run on the output of select entries (as I think is stated by Each grouping column reference shall unambiguously reference a column of the table resulting from the from clause.) and I'd guess this is a side effect of allowing group by to work on the table resulting from the select list as well. I think the SQL way of writing this is to use a subselect and do two levels (ie, generate a subselect that gives the table you want to group and use it in the from clause of the outer query that does the grouping). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [Slightly OT] data model books/resources?
On Thursday 30 March 2006 03:03, Aaron Glenn wrote:
Anyone care to share the great books, articles, manifestos, notes,
leaflets, etc on data modelling they've come across? Ideally I'd like
to find a great college level book on data models, but I haven't come
across one that even slightly holds definitive resource-type status.
I've heard that Relational Database Design (ISBN: 0123264251) is good for
college level introductory material, though the book I generally recommend
most is Practical Issues in Database Management (ISBN: 0201485559)
Feel free to reply off list to keep the clutter down - I'd be happy to
summarize responses for the list.
We're all about clutter :-)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance Killer 'IN' ?
On 3/31/06, Kai Hessing [EMAIL PROTECTED] wrote: The second one (UPDATE xyz WHERE id IN (xyz1, xyz2, ) AND status-1;) returns: -- Seq Scan on phon (cost=0.00..1573304.58 rows=105931 width=148) (actual time=369563.565..369563.565 rows=0 loops=1) Just a shot in the dark: does the plan stay the same, when you remove the ' AND status -1' ? -- marko ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Performance Killer 'IN' ?
Kai Hessing [EMAIL PROTECTED] writes: Index Scan using phon_phon_idx on phon (cost=0.00..5193.83 rows=530 width=148) (actual time=0.146..0.146 rows=0 loops=1) ... Seq Scan on phon (cost=0.00..1573304.58 rows=105931 width=148) (actual time=369563.565..369563.565 rows=0 loops=1) You need to look into the discrepancy between estimated and actual row counts. (I suppose the reason you're showing 0 rows here is that you already did these UPDATEs and so none of the rows in question pass the status filter --- but how many rows are there matching the phon index conditions?) Perhaps a larger statistics target for the phon column would be a good idea. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to use result column names in having cause
chris smith [EMAIL PROTECTED] writes: I assume it's this way because the standard says so.. Right. From a logical point of view, the HAVING clause has to be evaluated before the output expressions are computed, so it doesn't make any sense to expect the output expressions to be available in HAVING. An example of why this must be so is SELECT x, 1/avg(y) FROM TAB GROUP BY x HAVING avg(y) 0 If the HAVING clause isn't executed first this may fail with zero-divide errors. The real bug here IMHO is that we don't enforce the same rule for GROUP BY. Allowing GROUP BY 1 to reference an output column is a violation of the spec, which I think we adopted basically because some other DBMSes do it too, but it's just as semantically nonsensical as doing it in HAVING would be. It's a wart on the language that we can't really get rid of because of backwards-compatibility considerations, but we're highly unlikely to add more such warts. BTW, if you're really intent on not writing your big expression twice, use a sub-select: SELECT x FROM (SELECT big_expr AS x FROM ...) AS ss GROUP BY ... HAVING x ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgsql continuing network issues
On Mar 31, 2006, at 2:55 AM, David Bernal wrote: Hey all, I wrote a few days ago regarding networking issues I'm having. At this point, I'm at my wit's end and am hoping someone can help me. I am running postgres 8.1.0 on Windows XP w\SP2. I currently have PGSQL configured to accept connections from my local IP, 192.168.0.100 using the listen_addresses directive. If I try to connect to the database from the same machine using the psql program or using NPGSQL (.NET pgsql provider) it simply hangs. Nothing happens at all. I don't get errors in my event viewer, nor do I get any errors in the textfile log postgresql maintains. What IP address are you trying to connect to? If you're trying to connect to 127.0.0.1 and it's listening on 192.168.0.100 then that won't work. IF I change the listen_addresses directive to localhost, I can connect fine and everything works EXCEPT I get the message NOTICE: Unknown win32 socket error code: 10107 repeatedly anytime I do something. This would be acceptable, except that I need to be able to access this database at client sites. 10107 is WSASYSCALLFAILURE. A pretty generic message, but I've seen it when some broken software has inserted itself into the IP stack. Can anyone help me or direct me to someone who can? I'm trying to push postgres to my clients, because it seems like a great RDBMs, but if I can't even get a server to work, I'm going to have to switch to something else. Can you connect to TCP port 5432 using telnet, or somesuch? If not, are you running any sort of crapware on the windows box that will break your network stack (personal firewall or antivirus are some of the buzzwords there - I'd look at your personal firewall settings first). Cheers, Steve ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [Slightly OT] data model books/resources?
Robert Treat wrote: On Thursday 30 March 2006 03:03, Aaron Glenn wrote: Anyone care to share the great books, articles, manifestos, notes, leaflets, etc on data modelling they've come across? Ideally I'd like to find a great college level book on data models, but I haven't come across one that even slightly holds definitive resource-type status. I've heard that Relational Database Design (ISBN: 0123264251) is good for college level introductory material, though the book I generally recommend most is Practical Issues in Database Management (ISBN: 0201485559) Feel free to reply off list to keep the clutter down - I'd be happy to summarize responses for the list. We're all about clutter :-) I also highly suggest: Database in Depth : Relational Theory for Practitioners (Paperback) by C.J. Date It is a great, pratical book that isn't a snore. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Recovery with pg_xlog
You can usually get the directory name by doingSELECT dataname, oid FROM pg_databases;To get the file name do:SELECT relname, relfilenode FROM pg_class WHERE relname = 'tblName';The relfilenode will tell you what numbed file belongs to the talbe. It's not always OID, but it often is (don't just assume it will be the OID). J.-- Jason C. Leach PGP Key: 0x62DDDF75 Keyserver: gpg.mit.edu
Re: [GENERAL] PANIC: heap_update_redo: no block
On 3/31/06, Qingqing Zhou [EMAIL PROTECTED] wrote: Alex bahdushka [EMAIL PROTECTED] wrote (@)2006-03-18 23:30:33.035 MST[3791]PANIC: heap_update_redo: no block According to the discussion in pgsql-hackers, to finish this case, did you turn off the full_page_writes parameter? I hope the answer is yes ... If by off you mean full_page_writes = on then yes. Thanks for all your help! ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] giving users access to specific databases
Hi, I'm hoping someone can tell me how to go about this, or if a solution is even possible with my current set up. I realize this question may go beyond pure postgres topics and have to do more with how my hosting company has their servers configures, but this group seemed like my best option for help. If anyone has suggestions on what other groups might be helpful to post this question to, I would really appreciate it. I do my database application work on a shared Linux server provided by my hosting company. It appears they have just one installation of postgres on the server and that all of their customers on that server are able to create databases and users under their account. I'm not quite sure how they have enabled specific user accounts for access to my specific part of the server, but I do know that when I log in I have access to a cpanel interface, email configuration, all of my files on the server, access to create new postgres databases and a link to phpPgAdmin. The difficulty I have is that in order to give another of the developers access to a specific database, I must give them the username and password to my entire account on their server. I've spent weeks corresponding with them on how I can give individual access to just a specific database. We finally tried installing a separate instance of phpPgAdmin in my web directory. I can get access to my databases through this, but it turns out I still need to use my main username and password to access the databases since we realized they needed to tighten security so other people on the server couldn't get access to my databases. Below is a copy of the last email I got from the hosting company. If anyone has any suggestions on what would fix this problem I would really appreciate it. I'm not set on having to use phpPgAdmin, but I would like some sort of graphical interface available so less-technical users can update information in the database without having to learn sql. Subject: Separate login for phpPgAdmin The only way with phpPgAdmin is to allow you access to all databases including those owned by you. This can cause a security issue where others would have access to your database as well. Unlike phpmyadmin, phpPgAdmin does not seem to allow you to login to a database with a database username unless the above option is enabled and therefore creating a security risk. The way Postgres is set to label owners it uses your CPANEL login as the owner which is different than how MySql tracks the databases. Due to this you would not be able to use phpPgAdmin with other users. Thanks in advance, Courtenay ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg 8.1.2 performance issue
On Sunday March 26 2006 9:16 am, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: I see what appear to be many single transactions holding RowExclusiveLocks for sometimes 40-50 seconds while their query shows IDLE in transaction. ... I'm thinking that means the client is simply tweaking a row and then failing to commit the change for 40-50 seconds. Is that consistent? That's what it sounds like to me. You might consider logging all commands from these clients for awhile so you can check that theory. This indeed appears to be locking problem from within Apache::Session where it deletes a row from the DB but fails to commit the change for an extended period while another transaction waits on the same row. Not sure how/why that's happening in the client code, but it's not a DB issue. Thanks, Ed ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgsql continuing network issues
What IP address are you trying to connect to? If you're trying to connect to 127.0.0.1 and it's listening on 192.168.0.100 then that won't work. I'm trying to connect to 192.168.0.100 10107 is WSASYSCALLFAILURE. A pretty generic message, but I've seen it when some broken software has inserted itself into the IP stack. Right, I ran a program to check the stack, and it actually didn't find anything. Can you connect to TCP port 5432 using telnet, or somesuch? If not, are you running any sort of crapware on the windows box that will break your network stack (personal firewall or antivirus are some of the buzzwords there - I'd look at your personal firewall settings first). I CAN telnet to 192.168.0.100 5432. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgsql continuing network issues
You should for sure change the listen_addresses to * because you may have two NICs in the server or PC and somehow the external connections are connecting to the other nic. I tried this as well, but it didn't change anything. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] best practice in upgrading db structure
On Mar 28, 2006, at 8:40 PM, Robert Treat wrote: Depends on how much data you need to modify. For small tables, I stick with ALTER TABLE because it's a lot cleaner/easier. For larger tables, you might want to CREATE TABLE AS SELECT ..., or maybe copy out and copy back in. This seems backwards to me. On larger tables I tend to favor alter table for adding/dropping columns since the table doesn't need to be rewritten, and on smaller tables I'd be more likely to use CTAS (although even then still pretty unlikely) It depends on what exactly you're doing. For example, if you're adding a new field and have to populate it with data, you end up rewriting the entire table, but in a way that leads to considerable bloat. Of course if you can get away without re-writing the entire table you absolutely want to go that route. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] updategram in pg
No, but you should be able to build something similar using a
language that understands XML. You could then pass something like
this to the database:
SELECT updategram.update('big-ole XML string');
If you wanted to get even more fancy, you could create a daemon that
would accept connections as if it was a postmaster and handle in-line
XML requests however MS does.
If you decide to go this route please consider starting a project on
pgFoundry, as I'm sure others would find this useful.
On Mar 29, 2006, at 2:08 AM, SunWuKung wrote:
Is there a way to store update/insert logic in the data instead of
writing db or application procedures to do that?
I am looking for something along the lines of MS updategrams like
this:
ROOT xmlns:updg=urn:schemas-microsoft-com:xml-updategram
updg:sync
updg:before/updg:before
updg:after updg:returnid=x y
HumanResources.Shift updg:at-identity=x Name=Day-Evening
StartTime=1900-01-01 11:00:00.000
EndTime=1900-01-01 19:00:00.000
ModifiedDate=2004-01-01 00:00:00.000 /
HumanResources.Shift updg:at-identity=y Name=Evening-Night
StartTime=1900-01-01 19:00:00.000
EndTime=1900-01-01 03:00:00.000
ModifiedDate=2004-01-01 00:00:00.000 /
/updg:after
/updg:sync
/ROOT
Thanks for the help.
Balázs
---(end of
broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Jim C. Nasby, Database Architect[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 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] best practice in upgrading db structure
On Mar 29, 2006, at 3:25 AM, Csaba Nagy wrote: Could somebody explain me, or point me to a resource where I can find out what is the recommended practice when a live db needs to be replaced with a new version of it that has a slightly different structure? Our development infrastructure includes a development data base cluster, with one data base per developer, and a staging data base with a largish deployment of the current production data base version. The developers are free to test whatever data base modifications they need on their private development data base. We have a setup_db script, which creates the data base structure + initial data. The script is based on an XML file which is processed by XSLT to generate the actual schema for Oracle/Postgres. So the developers usually recreate Have you considered releasing that creation code? I know there's lots of places that have a need for stuff like this, and having used a similar system before I know how powerful it can be. Unfortunately the company I worked for was too paranoid to release the database creation tool we used. :( Had they, maybe you wouldn't have had to write one from scratch. -- Jim C. Nasby, Database Architect[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: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL x Sybase
A commonly overlooked comparison for always on systems is to compare what sorts of operations you can do to databases without needing to restart the server or drop tables, lock out users etc. We use Sybase Adaptive Server Anywhere 8 here and the thing that annoys me about it is exactly this. You can't modify any database objects (AFAICT) while there are other connections. I have to script things and schedule them to run in the middle of the night. There Enterprise product is apparently a completely different animal, so it is probably better. - Ian ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] about partitioning
dear all, i created a master table and a sub table which inherits the main table. and then i made a trigger and a function that want to keep the master table empty. but the trigger didn't work anyway.when i inserted data into the table news,both the master table and the sub table were inserted. why? i just want the empty master table,any good ideas? lots of thanks for all. here r DDls: - --master table£» CREATE TABLE public.news ( id SERIAL, title VARCHAR(100) NOT NULL, content VARCHAR NOT NULL, author VARCHAR(50) NOT NULL, date DATE DEFAULT now(), CONSTRAINT news_pkey PRIMARY KEY(id) )WITHOUT OIDS; --rule£» CREATE RULE news_current_partition AS ON INSERT TO public.news DO INSTEAD (INSERT INTO news_001 (title, content, author) VALUES (new.title, new.content, new.author)); --trigger£» CREATE TRIGGER news_triggers BEFORE INSERT ON public.news FOR EACH ROW EXECUTE PROCEDURE public.deny_insert(); --function£» CREATE OR REPLACE FUNCTION public.deny_insert () RETURNS trigger AS $body$ BEGIN RETURN NULL; END; $body$ LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; --sub table£» CREATE TABLE public.news_001 ( CONSTRAINT news_001_date_check CHECK ((date = '2006-03-29'::date) AND (date '2006-04-28'::date)) ) INHERITS (public.news) WITHOUT OIDS; CREATE INDEX news_001_index ON public.news_001 USING btree (id); -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [Slightly OT] data model books/resources?
On Mar 30, 2006, at 2:03 AM, Aaron Glenn wrote:Anyone care to share the great books, articles, manifestos, notes,leaflets, etc on data modelling they've come across? Ideally I'd liketo find a great college level book on data models, but I haven't comeacross one that even slightly holds "definitive resource"-type status.Feel free to reply off list to keep the clutter down - I'd be happy tosummarize responses for the list.Thanks,aaron.glennI've found Database Modeling Essentials by Simsion and Witt (ISBN: 0-12-644551-6) to be a good resource.--Thomas F. O'ConnellDatabase Architecture and ProgrammingCo-FounderSitening, LLChttp://www.sitening.com/3004 B Poston AvenueNashville, TN 37203-1314615-260-0005 (cell)615-469-5150 (office)615-469-5151 (fax)
Re: [GENERAL] [Slightly OT] data model books/resources?
On Thursday 30 March 2006 03:03, Aaron Glenn wrote: Anyone care to share the great books, articles, manifestos, notes, leaflets, etc on data modelling they've come across? Ideally I'd like to find a great college level book on data models, but I haven't come across one that even slightly holds definitive resource-type status. I've heard that Relational Database Design (ISBN: 0123264251) is good for college level introductory material, though the book I generally recommend most is Practical Issues in Database Management (ISBN: 0201485559) Feel free to reply off list to keep the clutter down - I'd be happy to summarize responses for the list. We're all about clutter :-) Well then, in that case, can I add to the clutter by asking a question about IT training? I was just asked today, by a vice president in the company I'm working with, to train one of his staff to become a database programmer and administrator. I have taught software engineering using UML, and programming in Java and C++. I have not taught database programming and administration, although I have done some of each for some of my own applications. My Question? Can the folk in this group help me develop a reading list and a list of competencies for this fellow to master? While I can easily develop a list of books dealing with databases in general and SQL in particular, it is not so easy to separate the wheat from the chaff, and I do not want to waste a pile of money on evaluating the range of books that are available. I'd therefore like accounts of books to avoid, and why, as well as books that are essential in any respectable collection, and why. I'm interested both in text books, with exercises, and reference books (both theoretical and practical). Thanks Ted ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] How to use viewsrules to dynamically choose which table to update
I'm still relatively new to Postgres (at least when it comes to
clever stuff - especially rules) so I hope I've missed something here.
Basically I'm still trying to combine multiple databases with
identical schemas into one schema, adding a column to each table to
indicate which schema it came from. (I'm prototyping an app in Ruby
on Rails so I want to have only one set of model classes, instead of
5). So I have views defined like this:
SELECT 'schema1'::varchar(10), * from schema1.table1
UNION ALL
SELECT 'schema2'::varchar(10), * from schema2.table1
etc...
These tables are all from a data feed we pay for, and is updated
nightly. It is separate from my application database.
Now, I want to take advantage of Rails' unit tests on these tables,
because I need to simulate changes in the data feed. So I thought
maybe I could add rules to the views, so Rails can load its test
fixtures into the model I defined and not realise it is feeding
multiple back-end tables.
This is my effort in a test database, so you can see what I'm trying
to do:
CREATE SCHEMA english;
CREATE TABLE english.names (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE SCHEMA french;
CREATE TABLE french.names (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE VIEW names AS
SELECT ('english'::character varying)::character varying(20)
AS language, * FROM english.names;
UNION ALL
SELECT ('french'::character varying)::character varying(20)
AS language, * FROM french.names;
CREATE RULE insert_english AS
ON INSERT TO names
WHERE (((new.language)::character varying(20))::text =
(('english'::character varying)::character varying
(20))::text)
DO INSTEAD INSERT INTO english.names (name) VALUES (new.name);
CREATE RULE insert_french AS
ON INSERT TO names
WHERE (((new.language)::character varying(20))::text =
(('french'::character varying)::character varying(20))::text)
DO INSTEAD INSERT INTO french.names (name) VALUES (new.name);
(Please forgive any mistakes above - I cobbled it together from a
backup file)
Now if I some french names and some english names into the relvant
tables, the view works fine on SELECT, but on INSERT I get this error:
ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
Which suggests that what I want to do is impossible. Does anyone
know of a way to do this? If I can do it in the database I can
probably save hours of hacking the unit tests in Rails.
Thanks
Ashley
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] How to use viewsrules to dynamically choose which table to update
On Sat, Apr 01, 2006 at 12:04:26AM +0100, Ashley Moran wrote:
I'm still relatively new to Postgres (at least when it comes to
clever stuff - especially rules) so I hope I've missed something here.
Basically I'm still trying to combine multiple databases with
identical schemas into one schema, adding a column to each table to
indicate which schema it came from. (I'm prototyping an app in Ruby
on Rails so I want to have only one set of model classes, instead of
5). So I have views defined like this:
SELECT 'schema1'::varchar(10), * from schema1.table1
UNION ALL
SELECT 'schema2'::varchar(10), * from schema2.table1
etc...
These tables are all from a data feed we pay for, and is updated
nightly. It is separate from my application database.
Now, I want to take advantage of Rails' unit tests on these tables,
because I need to simulate changes in the data feed. So I thought
maybe I could add rules to the views, so Rails can load its test
fixtures into the model I defined and not realise it is feeding
multiple back-end tables.
This is my effort in a test database, so you can see what I'm trying
to do:
CREATE SCHEMA english;
CREATE TABLE english.names (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE SCHEMA french;
CREATE TABLE french.names (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE VIEW names AS
SELECT ('english'::character varying)::character varying(20)
AS language, * FROM english.names;
UNION ALL
SELECT ('french'::character varying)::character varying(20)
AS language, * FROM french.names;
CREATE RULE insert_english AS
ON INSERT TO names
WHERE (((new.language)::character varying(20))::text =
(('english'::character varying)::character varying
(20))::text)
DO INSTEAD INSERT INTO english.names (name) VALUES (new.name);
Wow. That's confusing. How about using table partitioning for this?
http://www.postgresql.org/docs/current/static/ddl-partitioning.html
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] pg_hba.conf errors
Hi, Does anyone know if there were any updates to this issue? http://archives.postgresql.org/pgsql-hackers/2003-06/msg00195.php As it is now our web server running PHP attempts to connect to the DB using SSL (which is off), the server rejects the connection, logs it, then the client successfully connects without SSL. There isn't a real problem per se, but I would like to get rid of the error messages: Mar 31 15:07:16 db1 postgres[16474]: [4-1] FATAL: no pg_hba.conf entry for host , user , database , SSL on Thanks for your time, Brad ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgsql continuing network issues
IF I change the listen_addresses directive to localhost, I can connect fine and everything works EXCEPT I get the message NOTICE: Unknown win32 socket error code: 10107 repeatedly anytime I do something. This would be acceptable, except that I need to be able to access this database at client sites. 10107 is WSASYSCALLFAILURE. A pretty generic message, but I've seen it when some broken software has inserted itself into the IP stack. For those who prefer enlgish, the text for that message is A system call that should never fail has failed.. I'd say that indicates something is not healthy about your machine :) Usually when these things happen, I'd recommend looking at firewall, antivirus or antispyware products. And try to actually remove them, not just disable it. Failing that, look at virus or spyware - it is windows after all :-) I CAN telnet to 192.168.0.100 5432. That, however, makes it look like it might be something different. *Might* - it cna still be a fw or such. It could be an issue with DNS resolving, considering you definitly have problems looking up localhost (which really should always work). Can you ping to arbitrary dns names? Or specifically to the DNS name you're trying to connect to? //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to use viewsrules to dynamically choose which table to update
Hi David
On Apr 01, 2006, at 12:13 am, David Fetter wrote:
Wow. That's confusing.
You're telling me! :D
How about using table partitioning for this?
http://www.postgresql.org/docs/current/static/ddl-partitioning.html
Thanks for your suggestion. I've had a look at partitioning but the
problem is this:
Every night we get a new set of data relating to cars (makes, models,
prices, options etc) and the same for vans, and one day bikes, wagons
and caravans. Each has an identical schema, so I want to treat all
the data as coming from the same source. BUT - the primary keys used
in the tables are not unique across all the datasets. So for
example, as Ford Focus (car) might have the same ID as an Iveco Daily
(van). I think this alone precludes table partitioning (nice feature
though! I bet it's useful for really heavyweight databases).
Now the current application is written in C# and NHibernate (yuk) so
to avoid duplicating the already sprawling code and configuration, I
had the idea of creating a view to UNION all the data from the
different datasets and prepend a column to distinguish which class of
vehicle it relates to. So a row would be identified ('car', 203) to
distinguish it from the van/bike/spaceship with id 203.
The rewrite I'm proposing will be in Ruby. Unit tests in Ruby on
Rails have a habit of just chucking data at the table it thinks wants
it. So I figured I could reverse the behaviour of the view to let me
insert data into the individual tables, and my app would not realise
it was using multiple tables to fulfil the query (data in or out)
using a single model class.
Maybe I will need to do something really arcane - I could perhaps
dynamically generate classes in my app to use to load the test data,
but that would involved poring over the Rails source to see how
everything works. I was hoping there would be a nice simple (oh I
laugh now) way of doing things in Postgres itself. Right now, I
don't know which approach is more mind-bending!
Regards
Ashley
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] giving users access to specific databases
On 4/1/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I'm hoping someone can tell me how to go about this, or if a solution is even possible with my current set up. I realize this question may go beyond pure postgres topics and have to do more with how my hosting company has their servers configures, but this group seemed like my best option for help. If anyone has suggestions on what other groups might be helpful to post this question to, I would really appreciate it. I do my database application work on a shared Linux server provided by my hosting company. It appears they have just one installation of postgres on the server and that all of their customers on that server are able to create databases and users under their account. I'm not quite sure how they have enabled specific user accounts for access to my specific part of the server, but I do know that when I log in I have access to a cpanel interface, email configuration, all of my files on the server, access to create new postgres databases and a link to phpPgAdmin. Do you get the option to create a new database user? You could create a new user and give that user access to your database. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to use viewsrules to dynamically choose which
On Sat, 1 Apr 2006, Ashley Moran wrote:
I'm still relatively new to Postgres (at least when it comes to
clever stuff - especially rules) so I hope I've missed something here.
Basically I'm still trying to combine multiple databases with
identical schemas into one schema, adding a column to each table to
indicate which schema it came from. (I'm prototyping an app in Ruby
on Rails so I want to have only one set of model classes, instead of
5). So I have views defined like this:
SELECT 'schema1'::varchar(10), * from schema1.table1
UNION ALL
SELECT 'schema2'::varchar(10), * from schema2.table1
etc...
These tables are all from a data feed we pay for, and is updated
nightly. It is separate from my application database.
Now, I want to take advantage of Rails' unit tests on these tables,
because I need to simulate changes in the data feed. So I thought
maybe I could add rules to the views, so Rails can load its test
fixtures into the model I defined and not realise it is feeding
multiple back-end tables.
This is my effort in a test database, so you can see what I'm trying
to do:
CREATE SCHEMA english;
CREATE TABLE english.names (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE SCHEMA french;
CREATE TABLE french.names (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE VIEW names AS
SELECT ('english'::character varying)::character varying(20)
AS language, * FROM english.names;
UNION ALL
SELECT ('french'::character varying)::character varying(20)
AS language, * FROM french.names;
CREATE RULE insert_english AS
ON INSERT TO names
WHERE (((new.language)::character varying(20))::text =
(('english'::character varying)::character varying
(20))::text)
DO INSTEAD INSERT INTO english.names (name) VALUES (new.name);
CREATE RULE insert_french AS
ON INSERT TO names
WHERE (((new.language)::character varying(20))::text =
(('french'::character varying)::character varying(20))::text)
DO INSTEAD INSERT INTO french.names (name) VALUES (new.name);
What should it do if you try to insert something that is neither french
nor english? I think an unconditional instead nothing rule might work
to supplement the two conditional ones if doing nothing is okay, but I
haven't tried.
(Please forgive any mistakes above - I cobbled it together from a
backup file)
Now if I some french names and some english names into the relvant
tables, the view works fine on SELECT, but on INSERT I get this error:
ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
Which suggests that what I want to do is impossible. Does anyone
know of a way to do this? If I can do it in the database I can
probably save hours of hacking the unit tests in Rails.
Thanks
Ashley
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] How to use viewsrules to dynamically choose which table to update
On Apr 01, 2006, at 12:57 am, Stephan Szabo wrote: What should it do if you try to insert something that is neither french nor english? I think an unconditional instead nothing rule might work to supplement the two conditional ones if doing nothing is okay, but I haven't tried. Wahey! The empty unconditional insert fixed it. I never cease to be amazed with the stuff postgres can do when it's properly trained. If the data is not english or french, (or in the real case, a car, van or bike etc) it's garbage and can be discarded, so I'm happy with this. It will only used for internal testing anyway. Thank you both for your time reading my very cryptic question :) Ashley ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] about partitioning
On 4/1/06, fufay [EMAIL PROTECTED] wrote: dear all, i created a master table and a sub table which inherits the main table. and then i made a trigger and a function that want to keep the master table empty. but the trigger didn't work anyway.when i inserted data into the table news,both the master table and the sub table were inserted. why? i just want the empty master table,any good ideas? lots of thanks for all. here r DDls: - --master table； CREATE TABLE public.news ( id SERIAL, title VARCHAR(100) NOT NULL, content VARCHAR NOT NULL, author VARCHAR(50) NOT NULL, date DATE DEFAULT now(), CONSTRAINT news_pkey PRIMARY KEY(id) )WITHOUT OIDS; --rule； CREATE RULE news_current_partition AS ON INSERT TO public.news DO INSTEAD (INSERT INTO news_001 (title, content, author) VALUES (new.title, new.content, new.author)); --trigger； CREATE TRIGGER news_triggers BEFORE INSERT ON public.news FOR EACH ROW EXECUTE PROCEDURE public.deny_insert(); --function； CREATE OR REPLACE FUNCTION public.deny_insert () RETURNS trigger AS $body$ BEGIN RETURN NULL; END; $body$ LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; --sub table； CREATE TABLE public.news_001 ( CONSTRAINT news_001_date_check CHECK ((date = '2006-03-29'::date) AND (date '2006-04-28'::date)) ) INHERITS (public.news) WITHOUT OIDS; Since the fields don't exist in news_001, it has to store them somewhere - in the table it inherits from. Inheritence is meant to be used to change something in the substructure/child table/whatever. If that object isn't in the child, it has to go back to the parent to work out what to do (in your case, store the entry). -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] WAL Archiving frequency
Hi,I've setup WAL file archiving to my SAN storage over the network (using an NFS mount to the file server).I've noticed that it's archiving the 16 MB wal files about once every minute. Is this normal? There's a huge number of files in my wal_archives directory now.Is there a parameter to tell it not to archive so frequently? Or is this nothing to be too concerned about?Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] PostgreSQL x Sybase
Thanks Mark, You gave me very good tips.. The main reason of my question is that we have a ERP software house client with many customers running PostgreSQL and one of their big customers is trying go buy another ERP system that uses Sybase instead of PostgreSQL. Now I´m sure it´s only a political issue for the customer, because I talked to them about the system behaviour and the answer from the users was unanimous: it´s nice, we´re happy and we don´t know why the boss would like to change the system. Anyway, my idea was to make a document with some strong reasons they should not change to the Sybase ERP system, comparing only the database features. Many thanks! Reimer - Original Message - From: Mark Aufflick [EMAIL PROTECTED] To: Reimer [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Thursday, March 30, 2006 10:48 AM Subject: Re: [GENERAL] PostgreSQL x Sybase I'm not aware of one, but I would also be very interested. Certainly Postgres is much more standards compliant. In terms of developer features, Postgres is also much more advanced. Sybase also has some unusual limitations in wierd areas like the maximum number of bytes of index per row. Sybase would traditionally beat the pants of Postgres when it comes to database replication but I have not had the opportunity to test out Slony. I don't know how they would stack up in terms of scalability. I've never reached the limits of either, having gotten as far as a million or so rows per table in postgres (on cheap hardware) and 10s of millions in sybase (on multi-cpu sun hardware). The traditional wisdom would be that Sybase is very very fast on large datasets. As Postgres (and Oracle) have improved and Sybase has somwhat stagnated, this standard wisdom may not be as true as it used to be. Not knowing your reason for asking, I would imagine the biggest two questions to come up by decision makers would be speed and stability under a large load and replication options. Of course if you have existing code, porting issues will come into it. A commonly overlooked comparison for always on systems is to compare what sorts of operations you can do to databases without needing to restart the server or drop tables, lock out users etc. Sybase is not particularly expensive (compared to say Oracle), but the support is not world class and new features come less often than in MS Powerpoint. If you google sybase versus oracle you will get a lot of results - this might be a good way to start a sybase weakness/strength list. Then you could insert the postgres features/weaknesses into the same list. You would want to add postgres unique strengths like open source, standards compliance, support community etc. On 3/29/06, Reimer [EMAIL PROTECTED] wrote: Hi, Does anyone know of any recent comparisons of postgres vs Sybase? Thanks in advance! Reimer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_hba.conf errors
Bradley W. Dutton [EMAIL PROTECTED] writes: Does anyone know if there were any updates to this issue? http://archives.postgresql.org/pgsql-hackers/2003-06/msg00195.php That was fixed in 7.4, see sslmode connection option and PGSSLMODE environment variable. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] WAL Archiving frequency
Brendan Duddridge [EMAIL PROTECTED] writes: I've noticed that it's archiving the 16 MB wal files about once every minute. Is this normal? There's a huge number of files in my wal_archives directory now. Is there a parameter to tell it not to archive so frequently? Or is this nothing to be too concerned about? If you've got a reasonably active database, 16MB/minute is not an unlikely rate of WAL generation. You can probably slow it down a bit by increasing the checkpoint-spacing parameters, but you can't just say don't generate so much WAL please. Either you want a log or you don't. Basically, you have to take base backups often enough that the total number of WAL archive files needed stays under your threshold of pain... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] WAL Archiving frequency
Thanks Tom, I just didn't know if that was normal or not. I actually saw 4 WAL files / minute today. I set checkpoint_segments = 128, so maybe that will help. Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Mar 31, 2006, at 8:47 PM, Tom Lane wrote: Brendan Duddridge [EMAIL PROTECTED] writes: I've noticed that it's archiving the 16 MB wal files about once every minute. Is this normal? There's a huge number of files in my wal_archives directory now. Is there a parameter to tell it not to archive so frequently? Or is this nothing to be too concerned about? If you've got a reasonably active database, 16MB/minute is not an unlikely rate of WAL generation. You can probably slow it down a bit by increasing the checkpoint-spacing parameters, but you can't just say don't generate so much WAL please. Either you want a log or you don't. Basically, you have to take base backups often enough that the total number of WAL archive files needed stays under your threshold of pain... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster smime.p7s Description: S/MIME cryptographic signature
[GENERAL] pgpool ABORT + no transaction warning
A while back, I posted about seeing a number of warnings from pgpool: http://archives.postgresql.org/pgsql-admin/2005-03/msg00305.php A typical pair of statements in my postgres logs looks like this: WARNING: there is no transaction in progress STATEMENT: ABORT Tatsuo Ishii declared that these were harmless, and I took (and still take) his word for it. At some point in the past with my old configuration (postgres 8.1.3 / pgpool 2.5.1) I did something that prevented the warnings from showing up. On a system running postgres 8.1.3, I recently upgraded pgpool from 2.5.1 to 3.0.1. When I upgraded pgpool, I began seeing the warnings again after a long window of not having seen them. My configuration files for pgpool and postgres didn't change during the upgrade, so I'm wondering what I might've been doing to avoid polluting my logs with them previously and why they are showing up again. pgpool itself seems to be working fine after the upgrade, so as long as the warnings are harmless, it's not a big deal, but I'd like a clean method of preventing log noise if one exists. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax) ---(end of broadcast)--- TIP 6: explain analyze is your friend
