[GENERAL] suggestion for psql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi there, I haven't found a way to set the client encoding of a psql connection other then \encoding which is arkward for batch processing. As .psqlrc is being ignored during batch processing and the internal encoding is being set *after* -v, I have to resort to arkward solutions in order to read data via scripts. (To whom it may concern: instead of using -c you can pipe your command from stdin). Shouldn't psql honour the current locale setting (LC_CTYPE) or/and have a command line option to set the encoding? Mit freundlichem Gruß / With kind regards Holger Klawitter - -- lists at klawitter dot de -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQFBmGry1Xdt0HKSwgYRAgBYAJ4yxnCSDn3MgTvuLUs8t/bffC7HDwCeMDr2 j8np4ISSUI5AvjQE30AfvCM= =4f1P -END PGP SIGNATURE- ---(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] suggestion for psql
Holger Klawitter wrote: Shouldn't psql honour the current locale setting (LC_CTYPE) or/and have a command line option to set the encoding? The environment variable PGCLIENTENCODING serves this purpose. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] How the function written on pl/pgsql can be called
Anatoly Okishev wrote: How the function written on pl/pgsql can be called by the function written on plperl. Error is: error from function: Undefined subroutine Safe::Root2::get_national_code called. You probably need to be calling SELECT get_national_code(...) to access it via a standard query. -- Richard Huxton Archonet Ltd ---(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] ask a question
suon wrote: pgsql-general I am a new user for pgsql.I has a question is, does pgsql(7.5 for win32) has a jdbc or a odbc ? If have ,how can i take it ? thank for your replying. There is no 7.5 - version 8.0 is in beta-testing though. There are links for jdbc/odbc on the homepage of http://www.postgresql.org -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Visual Studio and Postgresql
Daron wrote: Hi, Can anyone suggest any websites that might help with connecting visual studio dot net (I would like to see it running using server explorer if possible) to postgresql. I am planning to write some client applications in VB or C#. Best place to start is with the .Net data provider group - there's a link on the left side of the website homepage: http://www.postgresql.org/ Don't forget to check their mailing lists too (links on their project page). -- Richard Huxton Archonet Ltd ---(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] suggestion for psql
Holger Klawitter wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi there, I haven't found a way to set the client encoding of a psql connection other then \encoding which is arkward for batch processing. As .psqlrc is being ignored during batch processing and the internal encoding is being set *after* -v, I have to resort to arkward solutions in order to read data via scripts. (To whom it may concern: instead of using -c you can pipe your command from stdin). set client_encoding=MyEncoding; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Question about indexes
Only a simple question I have a table with primarykey(field1,field2,field3) and I want to execure a query like select * from my_table where field1 = some_value if I define an index on field1, I increase the query performace or not? Thanks
Re: [GENERAL] OID
Firstly, please don't send email in only HTML, it tends to get them
marked as spam.
SPAM: Hit! (3.2 points) HTML-only mail, with no text version
Anyway, as to your question:
Im planning on using OID for referencing in certain part of the
system i am building as in this case using normal pk -gt; fk would
be inefficient as i have to have once table reference multi tables,
but i have concerns that if a Database is exported and reconstructed
the OID's will change making referencing impossible.br
There is no use of OIDs that cannot be better served by sequences. I
presume the issue is that in your multiple tables you may have the same
PK appear. Well, there is no reason why a sequence should be tied to a
single table. For example:
CREATE SEQUENCE my_global_counter;
CREATE table_1 ( id int4 default nextval('my_global_counter') primary key, ...
CREATE table_2 ( id int4 default nextval('my_global_counter') primary key, ...
CREATE table_3 ( id int4 default nextval('my_global_counter') primary key, ...
Is this what you're looking for?
--
Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
pgpxmNzO7G1Kk.pgp
Description: PGP signature
Re: [GENERAL] Question about indexes
On Mon, 15 Nov 2004 11:38:29 +0100, Stefano Bonnin [EMAIL PROTECTED] wrote: Only a simple question I have a table with primarykey(field1,field2,field3) and I want to execure a query like select * from my_table where field1 = some_value if I define an index on field1, I increase the query performace or not? You increase it a bit. Anyway its not worth the hassle, as increase is rather marginal, and having two indexes lessens the chance that they'll be served from cache. If you're not sure yet, just make some tests. :) Regards, Dawid ---(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] Visual Studio and Postgresql
Thank you very much, that is exactly what I was looking for. Daron Ryan. -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Monday, 15 November 2004 8:05 PM To: Daron Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Visual Studio and Postgresql Daron wrote: Hi, Can anyone suggest any websites that might help with connecting visual studio dot net (I would like to see it running using server explorer if possible) to postgresql. I am planning to write some client applications in VB or C#. Best place to start is with the .Net data provider group - there's a link on the left side of the website homepage: http://www.postgresql.org/ Don't forget to check their mailing lists too (links on their project page). -- Richard Huxton Archonet Ltd --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.788 / Virus Database: 533 - Release Date: 1/11/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.788 / Virus Database: 533 - Release Date: 1/11/2004 ---(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] relations betwee subclasses in postgersql
Hi all, I'm using postgresql 7.4.2 and could not create relationships between subclases. I hava a EER model where the superclass Person has subclasses like Customers. When I try to create a relationship between Customers and other relations the database won't let me. Is that correct and if so, when will this be fixed? Brgds Dino Vliet __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com ---(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] table configuration tweak for performance gain.
Michael Fuhr [EMAIL PROTECTED] writes: On Mon, Nov 15, 2004 at 04:25:59PM +1100, Harvey, Allan AC wrote: I created an index on the dt column, ran ANALYSE then, EXPLAIN for some queries. The returned plan was always sequential search. Could you post a query and the EXPLAIN ANALYZE output? We could probably give better advice if we could see what's happening. Also, let's see EXPLAIN ANALYZE results after setting enable_seqscan to OFF. If that doesn't force it into an indexscan, then you have got more fundamental issues (perhaps a datatype mismatch). Note that I don't recommend turning off enable_seqscan as a production solution; but it's a useful tool for debugging. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] I'm sure a good time was had by many...
Title: I'm sure a good time was had by many... A cathartic time, to be sure. However, could we at least limit the scatology to the *body* of the messages? It's very difficult explaining to one's boss as he looks over your shoulder that yes, indeed, this *is* a serious support list. Thanks. Mark Taber State of California Department of Finance Infrastructure Architecture 916.323.3104 x 2945 [EMAIL PROTECTED]
[GENERAL] Nairobi time zone fails in 7.4
Hi, In 7.3 I was able to do: ALTER DATABASE babase SET TimeZone TO 'Nairobi'; a nice Posix TZ value (FYI UTC+3 w. no daylight savings time). In 7.4 this no longer works. I see some UTC+3 time zones in the 7.4 docs, but how am I to know that they correspond to Kenyan time, especially with respect to daylight savings and other goofyness? What's the right thing to do? Thanks. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] q with psql display paging dumps out of psql
Hi, Environment: SunOS 5.7 Generic_106541-29 sun4u sparc SUNW,UltraSPARC-IIi-Engine Postgresql-7.4.6 Build config: --with-java --enable-thread-safety gcc version 3.3.1 less-381 readline-4.3 $ echo $PAGER /usr/local/bin/less $ echo $LESS -e I recently upgraded from 7.4.2 to 7.4.6 and have run into a new problem. As frequently as not, maybe even most times, when I q out of paging the output of a query in psql: Instead of just quitting that query, I get dumped straight out of psql. To add insult to injury: The command history for the current session isn't saved. (Only what was in the command history on entry.) It's really quite irritating :/. It's not repeatable. If I try to trace the psql session with truss, it doesn't do it. If I G to the end of the output and then q, it doesn't do it. I down-graded to Postgresql-7.4.5. It happened with it. I upgraded less from v332 to v381. No improvement. echo $? after it happens yields 141. There is no 141 in /usr/include/sys/errno.h. I'm guessing it's some kind of race condition. Any suggestions where I might start debugging this problem? Jim ---(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] I'm sure a good time was had by many...
Mark, There is a net.kkkOOk trying to disrupt our list with scatology. A good time is not being had by any. The kkkOOk is spoofing email headers to look like valid users. We can do little but ignore the juvenile. Rick Taber, Mark [EMAIL PROTECTED]To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: [GENERAL] I'm sure a good time was had by many... tgresql.org 11/15/2004 10:52 AM A cathartic time, to be sure. However, could we at least limit the scatology to the *body* of the messages? It's very difficult explaining to one's boss as he looks over your shoulder that yes, indeed, this *is* a serious support list. Thanks. Mark Taber State of California Department of Finance Infrastructure Architecture 916.323.3104 x 2945 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] q with psql display paging dumps out of psql
Jim Seymour wrote: echo $? after it happens yields 141. There is no 141 in /usr/include/sys/errno.h. Exit code 141 means signal 141 - 128 = 13 = SIGPIPE. I haven't finished thinking what that means in this case, though ... -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Nairobi time zone fails in 7.4
Karl O. Pinc [EMAIL PROTECTED] writes: In 7.3 I was able to do: ALTER DATABASE babase SET TimeZone TO 'Nairobi'; a nice Posix TZ value (FYI UTC+3 w. no daylight savings time). In 7.4 this no longer works. I don't believe it worked in 7.3 either, but before 7.4 we didn't really detect whether the system timezone library recognized the name or not. I think the usual spelling for this zone name is 'Africa/Nairobi'. Certainly that's what PG 8.0 will take. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Nairobi time zone fails in 7.4
On 2004.11.15 10:42 Tom Lane wrote: Karl O. Pinc [EMAIL PROTECTED] writes: In 7.3 I was able to do: ALTER DATABASE babase SET TimeZone TO 'Nairobi'; a nice Posix TZ value (FYI UTC+3 w. no daylight savings time). I think the usual spelling for this zone name is 'Africa/Nairobi'. Certainly that's what PG 8.0 will take. Thanks! That works in 7.4. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(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] q with psql display paging dumps out of psql
[EMAIL PROTECTED] (Jim Seymour) writes: I recently upgraded from 7.4.2 to 7.4.6 and have run into a new problem. As frequently as not, maybe even most times, when I q out of paging the output of a query in psql: Instead of just quitting that query, I get dumped straight out of psql. To add insult to injury: The command history for the current session isn't saved. echo $? after it happens yields 141. 141-128 = 13 = SIGPIPE. So psql is getting sigpipe'd. The question is why? It is set up to ignore SIGPIPE everywhere that it could reasonably expect to get it, in particular from writing to the pager. I'm guessing it's some kind of race condition. The timing condition involved is probably whether or not psql has finished writing all of the query result to the pager before you quit the pager. So if you retrieve a large query result and q immediately you can probably make it more reproducible. Also, I don't think we changed that stuff between 7.4.2 and 7.4.6 (though I haven't trawled the commit logs to make sure). Was your 7.4.2 installation also built with --enable-thread-safety? It seems likely that addition or removal of --enable-thread-safety would make a difference. 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] q with psql display paging dumps out of psql
Peter Eisentraut wrote: Jim Seymour wrote: echo $? after it happens yields 141. There is no 141 in /usr/include/sys/errno.h. Exit code 141 means signal 141 - 128 = 13 = SIGPIPE. I haven't finished thinking what that means in this case, though ... I would expect it means the pipe between the data and pager was inappropriately interrupted. Not that that helps a lot. -- Until later, Geoffrey ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PostGreSQL to Access Updatable recordset
Try this, Set conn = New ADODB.Connection Conn.open DNS=SAP_PG;uid=postgres Set rsE = Conn.Execute(updateSQL) Where updateSQL is your update statement. Check permissions for updates/write for the user you are connecting. - Goutam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sim Zacks Sent: Tuesday, November 09, 2004 6:53 AM To: [EMAIL PROTECTED] Subject: [GENERAL] PostGreSQL to Access Updatable recordset I have a postgresql backend with an access front end and I am trying to redefine the recordset of the form to use an ADO recordset. The problem is that the CursorType always changes to AdOpenStatic, even if I choose adOpenDynamic. If anyone has any thoughts, please let me know. The form works great for viewing, but I cannot update or insert any new records. Below is the code I am using: connectstr = DSN=SAP_PG;uid=postgres Set conn = New ADODB.Connection conn.Open (connectstr) rs.CursorLocation = adUseClient rs.Open select ProductID,ProductName,ProductTypeID,StockTypeID from Products order by ProductName, conn, adOpenDynamic, adLockOptimistic Set Me.Recordset = rs Me.Requery The conn object contains the following: Provider=MSDASQL.1;Extended Properties=DSN=SAP_PG;DATABASE=sap;SERVER=10.1.1.76;PORT=5432 ;UID=username; PWD=password;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidCol umn=0;RowVersi oning=1;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096 ;UnknownSizes= 0;MaxVarcharSize=4094;MaxLongVarcharSize=4094;Debug=0;CommLog= 0;Optimizer=1; Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=0;UnknownsAsLongVar char=0;BoolsAs Char=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;L FConversion=1; UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;Byt eaAsLongVarBin ary=0;UseServerSidePrepare=0 In answer to your upcoming questions: 1) ProductID is the primary key on the table. 2) I do not want to use linked forms, they go much slower then connecting directly through the ADO recordset. 3) I put a breakpoint right after the open statement and checked the value of rs.CursorType and that is when I saw it was adOpenStatic 4) I am using PostGreSQL 8.0beta1 5) ODBC driver 7.03.02.00 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL on Guest Host (VMWare)
Hi! Description: VMware 4.0 Main host is WinXP Pro (on FAT32) and Guest Host is WinXP Pro (on NTFS) On Guest Host - PostgreSQL 8.0-beta2-dev3 From Main host i'm trying to connect to PostgreSQL to Guest host But as a result i'm receiving next message: Connection Refused (0x274D/10061) Is the server running on host xxx.xxx.xxx.xxx and accepting TCP/IP connections on port 5432? Tell me please, what is the problem? Thanx ---(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] PostgreSQL on Guest Host (VMWare)
Give the IP Address of your connecting client in the pg_hda.conf file and restart postgres. Thanks ! - goutam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ON.KG Sent: Monday, November 15, 2004 5:06 PM To: [EMAIL PROTECTED] Subject: [GENERAL] PostgreSQL on Guest Host (VMWare) Hi! Description: VMware 4.0 Main host is WinXP Pro (on FAT32) and Guest Host is WinXP Pro (on NTFS) On Guest Host - PostgreSQL 8.0-beta2-dev3 From Main host i'm trying to connect to PostgreSQL to Guest host But as a result i'm receiving next message: Connection Refused (0x274D/10061) Is the server running on host xxx.xxx.xxx.xxx and accepting TCP/IP connections on port 5432? Tell me please, what is the problem? Thanx ---(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 Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited. ---(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] PostgreSQL on Guest Host (VMWare)
Have you configured postgresql to work with tcp/ip ? http://www.postgresql.org/docs/current/static/postmaster-start.html Succes Estienne ON.KG wrote: Hi! Description: VMware 4.0 Main host is WinXP Pro (on FAT32) and Guest Host is WinXP Pro (on NTFS) On Guest Host - PostgreSQL 8.0-beta2-dev3 From Main host i'm trying to connect to PostgreSQL to Guest host But as a result i'm receiving next message: Connection Refused (0x274D/10061) Is the server running on host xxx.xxx.xxx.xxx and accepting TCP/IP connections on port 5432? Tell me please, what is the problem? Thanx ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Oracle versus PostgreSQL
I've searched unsuccessfully on google and the archives for a technical comparison of Oracle and PostgreSQL. Is there any free and recent comparison that covers more than just the basic differences. Thanks, Nadeem signature.asc Description: This is a digitally signed message part
Re: [GENERAL] q with psql display paging dumps out of psql
Tom Lane [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Jim Seymour) writes: I recently upgraded from 7.4.2 to 7.4.6 and have run into a new problem. As frequently as not, maybe even most times, when I q out of paging the output of a query in psql: Instead of just quitting that query, I get dumped straight out of psql. To add insult to injury: The command history for the current session isn't saved. echo $? after it happens yields 141. 141-128 = 13 = SIGPIPE. So psql is getting sigpipe'd. Yeah, a couple guys on one of my IRC channels figured that out. I subsequently smacked myself on the forehead and went Doh! (Been too many years away from systems coding, I guess.) The question is why? It is set up to ignore SIGPIPE everywhere that it could reasonably expect to get it, in particular from writing to the pager. Dunno. I'm guessing it's some kind of race condition. The timing condition involved is probably whether or not psql has finished writing all of the query result to the pager before you quit the pager. So if you retrieve a large query result and q immediately you can probably make it more reproducible. I suppose anything's possible. But I usually look at the result for a bit after querying for it ;), so... Anyway, I tried it on a query that pretty reliably exhibits the problem, and no amount of waiting before hitting q seems to make any difference. By the way, I get this in the serverlog: LOG: unexpected EOF on client connection. Also, I don't think we changed that stuff between 7.4.2 and 7.4.6 (though I haven't trawled the commit logs to make sure). Was your 7.4.2 installation also built with --enable-thread-safety? Yes, my 7.4.2 install was built with --enable-thread-safety. (In fact: If you check the archives, you'll see it was I discovered a problem with building with --enable-thread-safety in 7.4.2 and created a patch to fix it.) It seems likely that addition or removal of --enable-thread-safety would make a difference. I was thinking of giving that a go, being as the only things I could see in the HISTORY that looked like they might have any relationship was thread on Solaris stuff. Sure enough, compiling without --enable-thread-safety makes the problem go away. Anything else I can try/answer for y'all? Jim ---(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] 24x7x365 high-volume ops ideas
[EMAIL PROTECTED] (Karim Nassar) writes:
On Sun, 2004-11-07 at 21:16, Christopher Browne wrote:
None of these systems _directly_ address how apps would get pointed to
the shifting servers.
snip
Something needs to be smart enough to point apps to the right place;
that's something to think about...
Seems like it would be pretty easy to be smart in PHP:
function db_connect() {
$conn = pg_connect(dbname='foo' user='dawg' password='HI!'
host='master');
if (!($conn AND (pg_connection_status($conn) == 0))) {
// problem with master
$conn = pg_connect(dbname='foo' user='dawg' password='HI!'
host='replica');
if ($conn AND (pg_connection_status($conn) == 0)) {
return $conn;
}
} else {
return $conn;
}
return NULL;
}
Whatever client-side language one uses, the technique is the same
(though the coding style might differ :P ), can be used for
persistent connections (eg: with pg_pconnect in PHP), and seems like
it could be extended to any reasonable number of database servers.
What is the problem with this? The only issue I can see is that
replica might be behind. Depending on the application, this might
not be bad. If the app MUST have the very most accurate DB, you
could remove the logic that connects to the replica, but then that
nullifies this whole conversation...
The problem is that this requires modifications to the application,
and communicating configuration changes gets that bit more
complicated.
Supposing, for instance, the code that accesses connections has
already gotten wrapped in some more-or-less arcane object class
specific to the application, it may be somewhat troublesome to make
the modification.
It would be attractive to allow the configuration change to take place
outside the application in a manner that allows the application to be
completely ignorant about it.
By the way, your db_connect() suggestion doesn't cope with the problem
where a connection is broken and the application continues to use that
broken connection. There may be a need to cope with that...
--
let name=cbbrowne and tld=cbbrowne.com in String.concat @ [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Preventing connections during vacuum and reindex
I run these commands automatically every night: vacuumdb -a -z -U postgres reindexdb -a -U postgres Is there a way to temporarily disable all other connections to Postgres until these operations are complete? The operations take a while (about 20 minutes) and connections really pile up, sometimes crashing the server. Thanks, CSN __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Schemas?
When does using schemas make sense? Is it more effective or is it just to make a database more manageable? 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 4: Don't 'kill -9' the postmaster
Re: [GENERAL] table configuration tweak for performance gain.
Tom, Michael, Thanks for your interests. My original post was scant on detail as I was unsure if I had found the right place. It appears I have, so... Version is 7.4.5 Table size these tests were carried out on:- mill2= select count(*) from history\g count 258606 (1 row) Before index:- mill2= \d history Table public.history Column |Type | Modifiers ---+-+--- pointname | character varying(32) | not null parameter | character varying(8)| not null value | double precision| not null dt| timestamp without time zone | not null snip. WARNING: skipping pg_conversion --- only table or database owner can analyze it WARNING: skipping pg_depend --- only table or database owner can analyze it ANALYZE mill2= explain select value from history where pointname = 'MILL2-SPEED' and dt now() - interval '5 minutes'\g QUERY PLAN - Seq Scan on history (cost=0.00..8276.82 rows=8982 width=8) Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND ((dt)::timestamp with time zone (now() - '00:05:00'::interval))) (2 rows) After index:- mill2= create index dtindex on history( dt )\g CREATE INDEX mill2= \d history Table public.history Column |Type | Modifiers ---+-+--- pointname | character varying(32) | not null parameter | character varying(8)| not null value | double precision| not null dt| timestamp without time zone | not null Indexes: dtindex btree (dt) snip WARNING: skipping pg_conversion --- only table or database owner can analyze it WARNING: skipping pg_depend --- only table or database owner can analyze it ANALYZE mill2= explain select value from history where pointname = 'MILL2-SPEED' and dt now() - interval '5 minutes'\g QUERY PLAN - Seq Scan on history (cost=0.00..8263.19 rows=9342 width=8) Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND ((dt)::timestamp with time zone (now() - '00:05:00'::interval))) (2 rows) don't recommend turning off enable_seqscan as a production solution On your advise I did not go there. On using BETWEEN:- mill2= select value from history where pointname = 'MILL2-SPEED' and dt between now() and now() - interval '5 minutes'\g value --- (0 rows) mill2= select value from history where pointname = 'MILL2-SPEED' and dt now() - interval '5 minutes'\g value - 85606.9 85606.9 85606.9 85606.9 85606.9 85606.9 etc. I have obviously used it wrong but cannot see how/why. Thanks again. Allan -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, 16 November 2004 2:26 To: Michael Fuhr Cc: Harvey, Allan AC; [EMAIL PROTECTED] Subject: Re: [GENERAL] table configuration tweak for performance gain. Michael Fuhr [EMAIL PROTECTED] writes: On Mon, Nov 15, 2004 at 04:25:59PM +1100, Harvey, Allan AC wrote: I created an index on the dt column, ran ANALYSE then, EXPLAIN for some queries. The returned plan was always sequential search. Could you post a query and the EXPLAIN ANALYZE output? We could probably give better advice if we could see what's happening. Also, let's see EXPLAIN ANALYZE results after setting enable_seqscan to OFF. If that doesn't force it into an indexscan, then you have got more fundamental issues (perhaps a datatype mismatch). Note that I don't recommend turning off enable_seqscan as a production solution; but it's a useful tool for debugging. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL on Guest Host (VMWare)
On 11/15/2004 5:05 PM, ON.KG wrote: Hi! Description: VMware 4.0 Main host is WinXP Pro (on FAT32) and Guest Host is WinXP Pro (on NTFS) I hope you're running the guest with fully preallocated virtual disks. Any FAT inconsistency caused by a system crash could destroy your entire guest otherwise. On Guest Host - PostgreSQL 8.0-beta2-dev3 From Main host i'm trying to connect to PostgreSQL to Guest host But as a result i'm receiving next message: Connection Refused (0x274D/10061) Is the server running on host xxx.xxx.xxx.xxx and accepting TCP/IP connections on port 5432? Tell me please, what is the problem? Windows XP has some basic firewall functionality. Could it be that this is blocking inbound access on the Guest side? Jan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] simple query question: return latest
SELECT date FROM table WHERE color = 'red' ORDER BY date DESC LIMIT 1; Don't worry about names, just quote your identifiers. They will stand out and you can use anything you want. Jerry Michael Glaesemann [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Scott, On Nov 12, 2004, at 10:00 AM, Scott Frankel wrote: color | date + red| 2004-01-19 blue | 2004-05-24 red| 2004-04-12 blue | 2004-05-24 How do I select the most recent entry for 'red'? SELECT color, MAX(date) FROM giventable WHERE color = 'red' -- omit this line if you'd like to see the latest date for each color GROUP BY color; OT hint: You might want to take a look at the list of PostgreSQL Keywords in the documentation and avoid using them (such as date) to help you avoid naming issues in the future. Hope this helps. Michael Glaesemann grzm myrealbox com ---(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 7: don't forget to increase your free space map settings
Re: [GENERAL] Last value inserted
Which means that sometimes they do not return the correct value - if you have a trigger that inserts another record you will not get the right value. MSSQL has @@IDENTITY and SCOPE_IDENTITY() to handle this case, I'm new to pgsql so I don't know if it has anything like that. Jerry Richard Huxton [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] MaRCeLO PeReiRA wrote: How can I now (for sure) with value was generated by the sequence to fill the field ID? (There is lots of users using the software at the same time, so I am not able to use the last_value() function on the sequence.) Yes you are nextval()/currval() are multi-user safe. They return the next/current value *in the current connection*. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How the function written on pl/pgsql can be called by the function written on plperl.
Anatoly Okishev wrote: How the function written on pl/pgsql can be called by the function written on plperl. Error is: error from function: Undefined subroutine Safe::Root2::get_national_code called. You probably need to be calling SELECT get_national_code(...) to access it via a standard query. -- Richard Huxton Archonet Ltd If i call this function from pl/pgsql it's worked, but i want to call this function from plperl.. Anatoly Okishev ---(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] Schemas?
Bjørn T Johansen wrote: When does using schemas make sense? Is it more effective or is it just to make a database more manageable? They are just a logical hierarchy that allows you to organize your databases in more or less manageable ways. There is no performance or other physical benefit. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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 configuration tweak for performance gain.
On Tue, Nov 16, 2004 at 10:39:10AM +1100, Harvey, Allan AC wrote: mill2= explain select value from history where pointname = 'MILL2-SPEED' and dt now() - interval '5 minutes'\g We were looking for the output from explain analyze select With EXPLAIN ANALYZE we can see how realistic the planner's estimates were. Seq Scan on history (cost=0.00..8263.19 rows=9342 width=8) Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND ((dt)::timestamp with time zone (now() - '00:05:00'::interval))) You declared dt to be TIMESTAMP WITHOUT TIME ZONE, so its index won't be used because the filter's type is TIMESTAMP WITH TIME ZONE. Try casting the filter to dt's type by using now()::TIMESTAMP (this won't be necessary in 8.0). Aside from the type issue, the planner estimates that the query will return 9342 rows, so even if it could use an index it might think a sequential scan will be faster. The output from EXPLAIN ANALYZE would tell us if that guess is correct. Please show us the output of EXPLAIN ANALYZE after you've modified the query to use now()::TIMESTAMP. If the query still does a sequential scan then execute SET enable_seqscan TO off, run EXPLAIN ANALYZE again, and show us that output as well. On using BETWEEN:- mill2= select value from history where pointname = 'MILL2-SPEED' and dt between now() and now() - interval '5 minutes'\g value --- (0 rows) a BETWEEN x AND y is equivalent to a = x AND a = y, so if x and y aren't chosen correctly then the expression will always evaluate to false: SELECT 5 BETWEEN 1 AND 10; ?column? -- t SELECT 5 BETWEEN 10 AND 1; ?column? -- f -- 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
