[GENERAL] PGCLIENTENCODING behavior of current CVS source
hi, I'm using CVS source built postgres, may be one day later then the main site, but found one problem: I've set PGCLIENTENCODING environment before, for easy of typing, like export PGCLIENTENCODING=GBK in my .profile, but after I upgrade my postgresql to current CVS, I found problem, the database initialized using: initdb --locale=zh_CN.utf8 ... the database connected is UNICODE encoded, but when I use psql to loging to one of my database, it response: psql: FATAL: invalid value for parameter "client_encoding": "GBK" but when I remove the PGCLIENTENCODING setting: unset PGCLIENTENCODING, now I can login, but when I do a: DHY_JJG=# \dt ERROR: invalid byte sequence for encoding "UNICODE": 0xed but, after: DHY_JJG=# \encoding gbk DHY_JJG=#\dt woule be ok. the LANG setting is zh_CN.gbk, I guess it's a localization problem. may be the encoding of thos po files. because while using psql -E we see the query contain the locale string in AS clause, but don't know the best way to fix that, may be use UNICODE to encode those po files? regards Laser ---(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] OID's
Michael Glaesemann zei: > > OIDS are a system level implementation. They are no longer required > (you can make tables without OIDS) and they may go away someday. Out of curiosiry: how will we handle blobs once the OID's are gone? ---(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] OID's
Am Dienstag, 16. November 2004 10:01 schrieb Joolz: > Michael Glaesemann zei: > > OIDS are a system level implementation. They are no longer required > > (you can make tables without OIDS) and they may go away someday. > > Out of curiosiry: how will we handle blobs once the OID's are gone? They won't go away. This is one reason. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID's
On Nov 16, 2004, at 6:42 PM, Peter Eisentraut wrote: Am Dienstag, 16. November 2004 10:01 schrieb Joolz: Michael Glaesemann zei: OIDS are a system level implementation. They are no longer required (you can make tables without OIDS) and they may go away someday. Out of curiosiry: how will we handle blobs once the OID's are gone? They won't go away. This is one reason. Peter, You sound pretty certain. I can imagine there might be a way to handle BLOBs without OIDs. I'm not saying that I know what it is, but I recognize the possibility. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] How to clear linux file cache?
Hi, i am testing a few queries in my postgresql DB. The first query after reboot is always slower because of an empty OS page/file cache. I want to test my queries without any files in the linux kernel cache, just to know what would be the worst execution time. At the moment i stop postgresql and do something like find / -name foo for a few minutes and start postgresql afterwards. I search manuals with "apropos page cache" and found sync but it doesnt clear the cache, just writes dirty pages back to disk. I googled for "linux clear page cache" but did not found what i need. (of course i searched the archives, too) So how do i easily empty all page/file caches on linux (2.4.24)? Sorry for beeing a little bit OT, but i think my question still relates to postgresql. kind regards, janning ---(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] OID's
Am Dienstag, 16. November 2004 10:52 schrieb Michael Glaesemann: > You sound pretty certain. I can imagine there might be a way to handle > BLOBs without OIDs. I'm not saying that I know what it is, but I > recognize the possibility. There are certainly ways to handle this. But no one has seriously proposed getting rid of OIDs and presented a plan for fixing all the other holes that move would leave. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] OID's
On Tue, 16 Nov 2004 08:01 pm, Joolz wrote: > Michael Glaesemann zei: > > > > OIDS are a system level implementation. They are no longer required > > (you can make tables without OIDS) and they may go away someday. > > Out of curiosiry: how will we handle blobs once the OID's are gone? > I would guess bytea would become the standard for blob use. The size is limited to about 1G compressed, but I would guess most people don't store 2G files in there DB at the moment, or have that much ram to be able to handle a value that big. > > ---(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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Transaction in different DB
Hi all I have to develop an embedded database using memory FLASH and memory RAM Some tables must be saved in RAM and some tables in FLASH ( RAM and FLASH are two different mounted directories in the file system). I saw it's possible to save different databases in different directories with CREATE DATABASE options. It's possible to save only some tables in different directories?? If I want to work with two different databases, it's possible to do a single transaction on two different databases ?? Thanks Stefano Farina __ Tiscali Adsl 2 Mega Free: naviga gratis tutto l'anno! Supera tutti i limiti di velocita' con Tiscali Adsl 2 Mega Free. Sei libero da costi fissi e, se ti abboni entro il 25 novembre, navighi gratis fino al 31 dicembre 2004 e non paghi il costo di adesione. http://abbonati.tiscali.it/adsl/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Transaction in different DB
Am Dienstag, 16. November 2004 11:25 schrieb [EMAIL PROTECTED]: > It's possible to save only some tables in different directories?? That will be possible in 8.0. > If I want to work with two different databases, it's possible to do a > single transaction on two different databases ?? No. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] OID's
Peter Eisentraut wrote: There are certainly ways to handle this. But no one has seriously proposed getting rid of OIDs and presented a plan for fixing all the other holes that move would leave. Right; I certainly have no intention of trying to remove OIDs any time soon. However, I _will_ be proposing that we set default_with_oids to false by default in 8.1, per previous discussion on pgsql-hackers. Among other things, this will mean that CREATE TABLE will not include OIDs by default: if you want OIDs on a particular table, you can either specify WITH OIDS explicitly or change the default_with_oids configuration parameter. -Neil ---(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] How the function written on pl/pgsql can be called by the function written on plperl.
In 8.0beta: http://candle.pha.pa.us/main/writings/pgsql/sgml/plperl-database.html and in 7.4.x: http://www.postgresql.org/docs/7.4/interactive/plperl-database.html On Tue, 16 Nov 2004 10:19:01 +0500, Anatoly Okishev <[EMAIL PROTECTED]> wrote: > > 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 > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID's
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 A little bit OT, but: is there a way of removing duplicate rows in a table without OIDs? Mit freundlichem Gruß / With kind regards Holger Klawitter - -- lists klawitter de -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQFBmeVA1Xdt0HKSwgYRAklNAJ4l0KtMVF2Tkhx5ZgyPR38LHXd/LACeNk4q mwf/f5rI7VdckPfgfUotnSc= =qpV0 -END PGP SIGNATURE- ---(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] OID's
On Nov 16, 2004, at 8:32 PM, Holger Klawitter wrote: A little bit OT, but: is there a way of removing duplicate rows in a table without OIDs? One method that I believe works (haven't tried it in a while): BEGIN; CREATE TEMP TABLE foo_temp AS SELECT DISTINCT bar, bat, baz FROM foo; TRUNCATE foo; INSERT INTO TABLE foo (bar, bat, baz) SELECT bar, bat, baz FROM foo_temp; DROP TABLE foo_temp; COMMIT; There are others. Googling would probably reveal some. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID's
> On Tue, 16 Nov 2004 08:01 pm, Joolz wrote: > > Michael Glaesemann zei: > > > > > > OIDS are a system level implementation. They are no longer required > > > (you can make tables without OIDS) and they may go away someday. > > > > Out of curiosiry: how will we handle blobs once the OID's are gone? > > > I would guess bytea would become the standard for blob use. The size > is limited to about 1G compressed, but I would guess most people don't > store 2G files in there DB at the moment, or have that much ram to be > able to handle a value that big. Bytea cannot be a replacement of large objects Besides the 1G limit of bytea, storing 1G requires over 2G RAM is a serious problem. To be honest I don't understand why people hate OIDs. Most of problems with OID just come from the fact that it's a 32bit. Once extending it 64bit, all problems would go away. However using OIDs with large object is not a very good idea IMO. I think using user specified key for large objects would be better. -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] PL/pgsql - getting row from record
Hi, I'm sure this has been asked before, but my searches haven't come up with an example. I'm writing a generic trigger function called from different tables in plpgsql that needs to get the value of the primary key column from OLD. I can find out the pkey column name by querying the system tables, but the only way I've found of getting the value of that column is by creating a temporary table and using FOR ... EXECUTE to get the value back out. My code for doing that is below, but it seems mighty ugly. So, two questions: 1. Is there a better way of getting arbitrary columns from a record? 2. If not, will I run into problems with the name of the temporary table if two processes call this function at the same time, or are temp names unique to the particular session? CREATE TEMPORARY TABLE get_colval_from_record_tmp AS SELECT OLD.*; FOR srow IN EXECUTE ''SELECT '' || col || '' AS scol FROM get_colval_from_record_tmp'' LOOP colval := srow.scol; END LOOP; DROP TABLE get_colval_from_record_tmp; Thanks for the help, Matt BTW: I noticed on http://developer.postgresql.org/todo.php there's an item "Allow PL/pgSQL to name columns by ordinal position, e.g. rec.(3)". That'd do me fine, but I guess I'd better ask on the dev list as to when this might happen :) ---(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] ANN: DBManager 3.0
Hi, We are pleased to announce the awaited DBManager Professional 3.0. This version is a new GUI with lots of new features. DBManager is a client application to manage your databases for Windows environments. Some of the features in this version are - Manage all objects in the database server: MySQL 5, PostgreSQL 8, SQLite 3, Xbase, Interbase, Firebird, MSAccess(*), MSSQL Server(*), Sybase(*), Oracle(*) - New and improved design, Multi Document TAB Interface compatible with Visual Studio .NET - Create, build, import and export queries - Create PHP and ASP(*) scripts for the web to any database flavour(*) (PHP/native and ODBC/ASP) - Import structure and data from a variety of source including: Text Files, XML, MSAccess, MSExcel, Dbase III/Clipper/FoxPro, Paradox, ODBC, etc - Export structure and data to MSAccess, MSExcel, Text Files (CSV/Formatted), HTML, XML, SQL Dumps - User and Privilege (*) Manager - Task Builder (*) to automate processes - Diagram Designer (*) to create and mantain your database structure - New Table Designer - New Query Editor - Report and Form Builders (*) to create reports and Forms in HTML format - Monitor (*) the Activities of the Server, Database or Table with a simple click - DBManager Console to type your commands - Database Comparer (*) to keep your databases updated - and much, much more. (*) Available only in the Enterprise Edition If you want to know more about DBManager or download the new version please go to http://www.dbtools.com.br. Best Regards, Crercio O. Silva / DBTools Software ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Usenet Vote Information
SUCK MY WHITE COCK! SWALLOW MY FUCKING CUM! LICK AROUND THE CRACK OF MY ASSHOLE! LICK MY FORESKIN CHEESE! BLACK NIGGERS EAT WATERMELON! Joolz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 24x7x365 high-volume ops ideas
Hi Chris and Karim,
I haven't been following this thread, so excuse me if I suggest something
that has already been tossed out.
Solaris allows multiple IP addresses to be assigned to a single NIC. I
just looked at the man page for Linux ifconfig but didn't see quickly how
to do this. If Linux doesn't allow this, the same thing can be
accomplished using multiple NICs per server.
We reserve a special IP for the DB server. This IP can be assigned to the
NIC of the machine currently hosting the database. If you want apps to
connect to a different server, remove the IP from one machine and reassign
it to the other. This special DB IP is assigned on top of the regular IP
for the machine.
Newly connecting apps are never the wiser, but existing connections must be
terminated.
machine A: (DB server)
ifconfig hme0 192.1.1.1 up
ifconfig hme0:1 192.1.1.100 up #special DB IP
To change DB server from machine A to B:
MachineB: (New DB server)
ifconfig hme0 192.1.1.2 up
ifconfig hme0:1 192.1.1.100 up
Machine A:
ifconfig hme0:1 down
Hope this helps,
Rick
Chris Browne
<[EMAIL PROTECTED]> To:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: Re: [GENERAL] 24x7x365
high-volume ops ideas
tgresql.org
11/15/2004 05:20 PM
[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.
>
>> 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.
---
[GENERAL] LICK MY BALLS and ASSHOLE and COCK
LICK MY BALLS and ASSHOLE and COCK Mike Cox Joolz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] SWALLOW CUM from my COCK HOLE
SWALLOW CUM from my COCK HOLE Mike Cox Joolz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] FUCK YOUR MOTHERS CUNT
FUCK YOUR MOTHERS CUNT Mike Cox Joolz [EMAIL PROTECTED] ---(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] OID's
Hi, > > > > OIDS are a system level implementation. They are no longer required > > > > (you can make tables without OIDS) and they may go away someday. What about tableoids? Are they from the same generator as row oids (and hence may suffer wrap-around)? Or are they unique across the db? I ask because I'm currently using them to join a single table to rows in arbitrary tables, something like: +-+ | JOINME | +-+ | foreign_oid | | foreign_id | | ... | +-+ Where foreign_oid is the tableoid of the table and foreign_id is the (serial, not oid) id of the row in that table. I dunno if it's bad design, but it's darn handy. M ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] CUM ON MY ASSHOLE
CUM ON MY ASSHOLE Mike Cox Joolz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 24x7x365 high-volume ops ideas
Hi, On Tue, 2004-11-16 at 14:17, [EMAIL PROTECTED] wrote: > Hi Chris and Karim, > > I haven't been following this thread, so excuse me if I suggest something > that has already been tossed out. > > Solaris allows multiple IP addresses to be assigned to a single NIC. I > just looked at the man page for Linux ifconfig but didn't see quickly how > to do this. If Linux doesn't allow this, the same thing can be > accomplished using multiple NICs per server. > > We reserve a special IP for the DB server. This IP can be assigned to the > NIC of the machine currently hosting the database. If you want apps to > connect to a different server, remove the IP from one machine and reassign > it to the other. This special DB IP is assigned on top of the regular IP > for the machine. > > Newly connecting apps are never the wiser, but existing connections must be > terminated. Yes, linux can do it as well. But either case beware the arp cache :-) There is sqlrelay which could do the switching as well without forcing the apps to reconnect. Regards Tino ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PL/pgsql - getting row from record
Matt wrote: So, two questions: 1. Is there a better way of getting arbitrary columns from a record? Not really. 2. If not, will I run into problems with the name of the temporary table if two processes call this function at the same time, or are temp names unique to the particular session? Temporary tables are session-specific. Really, you'd be better off using one of the more interpreted languages for this specific task though. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] OID's
On Tue, 2004-11-16 at 12:32, Holger Klawitter wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
>
> A little bit OT, but:
> is there a way of removing duplicate rows in a table without OIDs?
>
ALTER TABLE ... ADD COLUMN myoid int;
CREATE TEMP SEQUENCE myoidsequence;
UPDATE ... SET myoid=nextval('myoidsequence');
then do the usual
SELECT a.* FROM ... a, ... b WHERE a.something=b.something AND
a.myoid
[GENERAL] FUCK MY WIFES CUNT and TITS
FUCK MY WIFES CUNT and TITS Mike Cox Joolz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] TIT-FUCK my wife's CUNT and ASSHOLE
TIT-FUCK my wife's CUNT and ASSHOLE Mike Cox Joolz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] BLACK NIGGERS are SPOOKS and JIGGABOOS
BLACK NIGGERS are SPOOKS and JIGGABOOS Mike Cox Joolz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] NIGGERS SUCK COCK and SWALLOW CUM LOADS
NIGGERS SUCK COCK and SWALLOW CUM LOADS Mike Cox Joolz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] AFRICAN NIGGERS FUCK CUNTS
AFRICAN NIGGERS FUCK CUNTS Mike Cox Joolz [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Hey Mike Cox, FUCK YOU BITCH!
All this fucking crap is all your fault! You're such a fucking asshole! Go fuck yourself up the anus with a broomstick, BITCH! FUCK YOU! MOTHER FUCKER! Goutam Paruchuri [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PL/pgsql - getting row from record
Thanks for the quick response! > Really, you'd be better off using one of the more interpreted languages > for this specific task though. Was afraid someone would say that. Time to polish up those tarnished perl skills ;) Matt ---(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] Blame this fucking shit on Mike Cox
This is all the fault of that penis breath Mike Cox. FUCK HIM! Tino Wildenhain [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] FUCK THAT SCUMBAG MIKE COX
He is responsible for all this Usenet c Tino Wildenhain [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] MIKE COX is a CUM SUCKING FAGGOT
Mike Cox is responsible for all this Usenet crap! Mike Rylander [EMAIL PROTECTED] ---(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] OID's
Peter Eisentraut zei: > Am Dienstag, 16. November 2004 10:01 schrieb Joolz: >> Michael Glaesemann zei: >> > OIDS are a system level implementation. They are no longer >> required >> > (you can make tables without OIDS) and they may go away someday. >> >> Out of curiosiry: how will we handle blobs once the OID's are >> gone? > > They won't go away. This is one reason. Thanks. Btw what's going on on the list??? I seems someone is posting a lot of obscene nonsense, using my email address. I saw this happen before with someone else. Is there anything I can do about it? Thanks again! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How to handle larger databases?
I am currently creating a database with less than 20 simple tables (only SQL 92 types, simple constraints, no PostgreSQL specific stuff, no stored procedures...) Unfortunately, some of those tables will contain up to 4 Million entries, making the size of the entire database 700-1000MB. In order to maintain good query times (hopefully <1-3 seconds) I would like to ask for some tips on how to manage and organize such a database. Like what should I do and what should I avoid? Where and how should I use indexes and all that stuff? I know there are much larger PostgreSQL databases around. How do they manage good query times? Thanks a lot Matt P.S. The test system is a normal Win 2000 PC, the target machines will be IA-32 based Linux machines. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] You are all ungrateful CUNTS!
All that I'm doing for you fucking people. Be grateful! Mike Cox Mike Cox [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Show me FUCKING RESPECT!
BITCHES! All that I'm doing for you fucking people. Be grateful! Mike Cox Mike Cox [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] EAT SHIT AND FUCK YOURSELVES
EAT SHIT AND FUCK YOURSELVES Mike Cox Mike Cox [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OID's
On Nov 16, 2004, at 11:44 PM, Joolz wrote: Btw what's going on on the list??? I seems someone is posting a lot of obscene nonsense, using my email address. net.kook I saw this happen before with someone else. Is there anything I can do about it? Filter aggressively and be patient. Not a happy situation, but the best we can do. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Last value inserted
--- Jerry III <[EMAIL PROTECTED]> wrote: > 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. If you are new to PostgreSQL, as you say, then why are you so sure of this? Perhaps you may profit from looking a little more at how currval() works. > 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 > __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] How to deal with almost recurring data?
I am creating a database which is supposed to contain many data entries (events) that differ only in the date they occur. So let's say event 1 occurs every Monday, Tuesday and Sunday between January 1st and May 30th 2005. How do I store and manage such data in a meaningful way? The simple idea would be to store the event itself in one table and have another table containing all the dates (all Mondays, Tuesdays and Sundays between 2005-01-01 and 2005-05-30) plus a foreign key to event_ID => (date, event_id). The problem is that we are dealing with several tenthousand events, resulting in several million single dates if I stored it in the described manner. That is why I would like to know if there is a better way to store and manage such information? Thanks Matt ---(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] OID's
--- Joolz <[EMAIL PROTECTED]> wrote: > > Thanks. > > Btw what's going on on the list??? I seems someone > is posting a lot > of obscene nonsense, using my email address. I saw > this happen > before with someone else. Is there anything I can do > about it? This is the down side of free will. Human cleverness can be used for bad purposes as well as good. Those who do things like this gain gratification from the responses that they get from others. If we ignore them (as everyone else on the list appears to be doing) they will go away eventually. > > Thanks again! > > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] SUCK MY PENIS
Suck my big penis! Mike Cox Mike Cox [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PGCLIENTENCODING behavior of current CVS source
Weiping <[EMAIL PROTECTED]> writes: > [ database encoding is unicode ] > now I can login, but when I do a: > DHY_JJG=# \dt > ERROR: invalid byte sequence for encoding "UNICODE": 0xed > but, after: > DHY_JJG=# \encoding gbk > DHY_JJG=#\dt > woule be ok. This is a risk no matter what encoding is used in the client-side .po files; as long as it's different from the current client_encoding, there is a potential for mis-conversion and other problems. I don't see a simple solution. In this particular case, it would help if psql's describe commands didn't assume they could send localized column headers to the server --- but I don't think that solves all related issues. BTW, Peter, it seems like this may be a good argument for keeping the client and server .po files separate. They might need different encodings. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] out of disk space
Hi, I know that the disk space of the database server should never run out but it can happen. Which is the worst case? May the database be corrupted? Mage -- http://mage.hu ---(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] How to deal with almost recurring data?
I would say it all depends on what you want to do with the data. If you want to look up all the possible occurences for an event, it might be useful to have the simple solution you described. If you will only look up the next n occurences starting from a given date, you might be better off storing the rule to derive the dates, and then calculate them in your software, but this will make your software more complicated for sure (dealing with date arithmetics can be incredibly complex, I've been there). I would go with the simple solution as long as there is enough space in the DB to store all the occurences... HTH, Csaba. On Tue, 2004-11-16 at 15:53, [EMAIL PROTECTED] wrote: > I am creating a database which is supposed to contain many data entries > (events) that differ only in the date they occur. > > So let's say event 1 occurs every Monday, Tuesday and Sunday between > January 1st and May 30th 2005. > > How do I store and manage such data in a meaningful way? > > The simple idea would be to store the event itself in one table and have > another table containing all the dates (all Mondays, Tuesdays and Sundays > between 2005-01-01 and 2005-05-30) plus a foreign key to event_ID => > (date, event_id). > > The problem is that we are dealing with several tenthousand events, > resulting in several million single dates if I stored it in the described > manner. > > That is why I would like to know if there is a better way to store and > manage such information? > > Thanks > > Matt > > ---(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] How to clear linux file cache?
Janning Vygen <[EMAIL PROTECTED]> writes: > So how do i easily empty all page/file caches on linux (2.4.24)? Probably the closest you can easily get is to put the Postgres data files on their own partition, and unmount/remount that partition before running yuour tests. Unmounting will sync and throw away all cached file data for that partition. -Doug ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to deal with almost recurring data?
[EMAIL PROTECTED] wrote: I am creating a database which is supposed to contain many data entries (events) that differ only in the date they occur. So let's say event 1 occurs every Monday, Tuesday and Sunday between January 1st and May 30th 2005. How do I store and manage such data in a meaningful way? The simple idea would be to store the event itself in one table and have another table containing all the dates (all Mondays, Tuesdays and Sundays between 2005-01-01 and 2005-05-30) plus a foreign key to event_ID => (date, event_id). The problem is that we are dealing with several tenthousand events, resulting in several million single dates if I stored it in the described manner. This seems like a reasonable approach. Don't forget that PostgreSQL is designed to store large amounts of data. Of course, if there is some pattern to the dates then you might be better off storing the pattern rather than the individual dates. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Last value inserted
On Tue, 16 Nov 2004, Jeff Eckermann wrote:
> --- Jerry III <[EMAIL PROTECTED]> wrote:
>
> > 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.
>
> If you are new to PostgreSQL, as you say, then why are
> you so sure of this? Perhaps you may profit from
> looking a little more at how currval() works.
He's correct. One thing that currval will not help with is a
case where more than one row has been inserted by a statement
(whether due to the base statement or triggers).
A somewhat absurd example:
---
create table q1(a serial, b int);
create function f1() returns trigger as 'begin if (random() >
0.5) then insert into q1 default values; end if; return NEW; end;'
language 'plpgsql';
create trigger q1_f1 after insert on q1 for each row execute
procedure f1();
insert into q1(b) values (3);
select currval('q1_a_seq');
select * from q1;
I got a currval of 3 which was the last row inserted, but that was from
the trigger, not the row created by my insert so it didn't have the
correct b value.
---(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] Last value inserted
Jeff Eckermann <[EMAIL PROTECTED]> writes:
> --- Jerry III <[EMAIL PROTECTED]> wrote:
>> 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.
> If you are new to PostgreSQL, as you say, then why are
> you so sure of this? Perhaps you may profit from
> looking a little more at how currval() works.
Jerry's correct, although the concern is more theoretical than real IMHO.
What he's imagining is a situation where you do, say,
INSERT INTO foo ...;
SELECT currval('foo_id_seq');
and there is an ON INSERT trigger on foo that directly or indirectly
does a nextval('foo_id_seq'). Execution of the trigger will then
advance the sequence beyond what was used to generate the inserted row,
and the subsequent currval() will return the wrong answer (or at least
not the answer you wanted). Note there is no race condition here; it's
just one process involved.
The reason I think this is mostly a theoretical issue is that I don't
see any good reason for such a trigger to be doing a nextval on the
table's ID sequence. The trigger is certainly not going to insert
additional rows in foo --- if it did that would lead to infinite
recursion of the trigger. So ISTM this scenario is really not
interesting. If it did happen it would represent a bug in your
application design, no more and no less. For instance, using the same
sequence to feed IDs for multiple tables would be a bug if you had a
trigger that did an insert on one of them as a consequence of an insert
on another.
regards, tom lane
---(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] PGCLIENTENCODING behavior of current CVS source
I hate to ask for a restriction of the 1st ammendment, but the life of many colleagues and mine would be much easier if those racists and idiots would not waste our bandwidth.Nobody but them is -to my beliefs- interested in their flaming each other. Please knock them off the board. with kind regards gÃnter strubinsky <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PGCLIENTENCODING behavior of current CVS source
Am Dienstag, 16. November 2004 16:11 schrieb Tom Lane: > This is a risk no matter what encoding is used in the client-side .po > files; as long as it's different from the current client_encoding, > there is a potential for mis-conversion and other problems. I don't > see a simple solution. Here's the news: Not only the server encoding and the server locale have to match. The same is true on the client side. In particular, in order to avoid errors from the PO files, your LC_CTYPE and your PGCLIENTENCODING need to be compatible. -- 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] 24x7x365 high-volume ops ideas
Tino Wildenhain wrote: Hi, On Tue, 2004-11-16 at 14:17, [EMAIL PROTECTED] wrote: Hi Chris and Karim, I haven't been following this thread, so excuse me if I suggest something that has already been tossed out. Solaris allows multiple IP addresses to be assigned to a single NIC. I just looked at the man page for Linux ifconfig but didn't see quickly how to do this. If Linux doesn't allow this, the same thing can be accomplished using multiple NICs per server. ifconfig device: ipaddress up For linux. Sincerely, Joshua D. Drake We reserve a special IP for the DB server. This IP can be assigned to the NIC of the machine currently hosting the database. If you want apps to connect to a different server, remove the IP from one machine and reassign it to the other. This special DB IP is assigned on top of the regular IP for the machine. Newly connecting apps are never the wiser, but existing connections must be terminated. Yes, linux can do it as well. But either case beware the arp cache :-) There is sqlrelay which could do the switching as well without forcing the apps to reconnect. Regards Tino ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Oracle versus PostgreSQL
On Mon, 2004-11-15 at 16:03, Nadeem Bitar wrote:
> 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.
>
I don't think so. FWIW there is a new article on techdocs that
interviews Mark Stossberg about a recent project where he converted from
Oracle/tcl to PostgreSQL/Perl. In the article is a link to a feature
comparison between the two databases, you might want to check it out
though there isn't a whole lot of technical detail in it.
Aside from that, I'd suggest google on "openacs postgresql oracle" and
possibly "compiere postgresql oracle" for information on two
applications that have looked at porting from oracle to postgresql.
Beyond that, feel free to post here with specific questions if you have
any, there are a number of oracle converts floating about.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How to clear linux file cache?
Am Dienstag, 16. November 2004 16:39 schrieb Doug McNaught: > Janning Vygen <[EMAIL PROTECTED]> writes: > > So how do i easily empty all page/file caches on linux (2.4.24)? > > Probably the closest you can easily get is to put the Postgres data > files on their own partition, and unmount/remount that partition > before running yuour tests. Unmounting will sync and throw away all > cached file data for that partition. very good idea. i will do it like this. thanks. janning ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Fwd: Problem installing Postgresql on MDK10.0
Sorry, but no reply on [EMAIL PROTECTED] -- Forwarded message -- From: Alejandro D. Burne <[EMAIL PROTECTED]> Date: Mon, 15 Nov 2004 15:00:58 +0100 Subject: Problem installing Postgresql on MDK10.0 To: [EMAIL PROTECTED] I'll be trying to install postgres 8.0b1/b4; when I make the config a message appear: "configure: error: readline library not found" $ rpm -qa | grep readline libreadline4-4.3-7mdk libreadline4-devel-4.3-7mdk Then I try; $ ./configure --with-libraries=/lib --with-includes=/usr/include/readline (where the libs are) I read a message from Tom Lane talking about termcap, it's installed too. The same problem occurs with zlib. I fix it skipping both libs with ./configure --without-readline --without-zlib but... Alejandro ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] How to clear linux file cache?
The world rejoiced as [EMAIL PROTECTED] (Janning Vygen) wrote: > So how do i easily empty all page/file caches on linux (2.4.24)? You can empty them of PostgreSQL-related data by filling them with something else. You might head to some directory that has more data in it than you have memory, and "cat" the files... -- "cbbrowne","@","linuxfinances.info" http://www.ntlug.org/~cbbrowne/sap.html "As far as Saddam Hussein being a great military strategist, he is neither a strategist, nor is he schooled in the operational arts, nor is he a tactician, nor is he a general, nor is he as a soldier. Other than that, he's a great military man, I want you to know that." -- General Norman Schwarzkopf, 2/27/91 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] 24x7x365 high-volume ops ideas
Thanks Joshua, I just played with it and it is more flexible that Solaris. Linux allows to be four characters, but Solaris only allows low numbers. I like this: ifconfig eth0:dbms 192.1.1.100 up Tino's right about the ARP tables. Gotta watch that one, especially with lower grade switches. Rick "Joshua D. Drake" <[EMAIL PROTECTED]To: Tino Wildenhain <[EMAIL PROTECTED]> .com>cc: [EMAIL PROTECTED], Chris Browne <[EMAIL PROTECTED]>, [EMAIL PROTECTED], [EMAIL PROTECTED] 11/16/2004 11:08 Subject: Re: [GENERAL] 24x7x365 high-volume ops ideas AM Tino Wildenhain wrote: >Hi, > >On Tue, 2004-11-16 at 14:17, [EMAIL PROTECTED] wrote: > > >>Hi Chris and Karim, >> >>I haven't been following this thread, so excuse me if I suggest something >>that has already been tossed out. >> >>Solaris allows multiple IP addresses to be assigned to a single NIC. I >>just looked at the man page for Linux ifconfig but didn't see quickly how >>to do this. If Linux doesn't allow this, the same thing can be >>accomplished using multiple NICs per server. >> >> ifconfig device: ipaddress up For linux. Sincerely, Joshua D. Drake >>We reserve a special IP for the DB server. This IP can be assigned to the >>NIC of the machine currently hosting the database. If you want apps to >>connect to a different server, remove the IP from one machine and reassign >>it to the other. This special DB IP is assigned on top of the regular IP >>for the machine. >> >>Newly connecting apps are never the wiser, but existing connections must be >>terminated. >> >> > >Yes, linux can do it as well. But either case beware the arp cache :-) >There is sqlrelay which could do the switching as well without >forcing the apps to reconnect. > >Regards >Tino > > >---(end of broadcast)--- >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL (See attached file: jd.vcf) jd.vcf Description: Binary data ---(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] How to deal with almost recurring data?
Thanks for the answer. If I go that way, though, I am afraid I will have very long query times. And I have no idea how to handle large databases of that kind without losing performance. If I store the pattern - is there a way to still perform the query in the database? Or do I have to do that in my software? (Matching the resultset to the pattern) Thanks Matt --- Ursprüngliche Nachricht --- Datum: 16.11.2004 16:42 Von: Richard Huxton <[EMAIL PROTECTED]> An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] How to deal with almost recurring data? > [EMAIL PROTECTED] wrote: > > I am creating a database which is supposed to contain many data entries > > (events) that differ only in the date they occur. > > > > So let's say event 1 occurs every Monday, Tuesday and Sunday between > > January 1st and May 30th 2005. > > > > How do I store and manage such data in a meaningful way? > > > > The simple idea would be to store the event itself in one table and have > > another table containing all the dates (all Mondays, Tuesdays and Sundays > > between 2005-01-01 and 2005-05-30) plus a foreign key to event_ID => > > (date, event_id). > > > > The problem is that we are dealing with several tenthousand events, > > resulting in several million single dates if I stored it in the described > > manner. > > This seems like a reasonable approach. Don't forget that PostgreSQL is > designed to store large amounts of data. > > Of course, if there is some pattern to the dates then you might be > better off storing the pattern rather than the individual dates. > > -- >Richard Huxton >Archonet Ltd > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] How to deal with almost recurring data?
Thanks The standard case is: I enter a date and 2 parameters of the event and I get the result set. Even though with Java date arithmetics are quite simple and I could therefore extend my software in that fashion, I would much rather perform all queries entirely in the database. So in case I go about it the way I described, I will have 4 million entries in the table that stores the dates and the references to the event. Now - how do I handle that database if I want to maintain very good query times (<1-3 seconds)? I have no idea how to handle a 1GB database in terms of query performance, like what tricks to use, what to do and what to avoid. Thanks Matt --- Ursprüngliche Nachricht --- Datum: 16.11.2004 16:32 Von: Csaba Nagy <[EMAIL PROTECTED]> An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] How to deal with almost recurring data? > I would say it all depends on what you want to do with the data. > If you want to look up all the possible occurences for an event, it > might be useful to have the simple solution you described. If you will > only look up the next n occurences starting from a given date, you might > be better off storing the rule to derive the dates, and then calculate > them in your software, but this will make your software more complicated > for sure (dealing with date arithmetics can be incredibly complex, I've > been there). I would go with the simple solution as long as there is > enough space in the DB to store all the occurences... > > HTH, > Csaba. > > On Tue, 2004-11-16 at 15:53, [EMAIL PROTECTED] wrote: > > I am creating a database which is supposed to contain many data entries > > (events) that differ only in the date they occur. > > > > So let's say event 1 occurs every Monday, Tuesday and Sunday between > > January 1st and May 30th 2005. > > > > How do I store and manage such data in a meaningful way? > > > > The simple idea would be to store the event itself in one table and have > > another table containing all the dates (all Mondays, Tuesdays and Sundays > > between 2005-01-01 and 2005-05-30) plus a foreign key to event_ID => > > (date, event_id). > > > > The problem is that we are dealing with several tenthousand events, > > resulting in several million single dates if I stored it in the described > > manner. > > > > That is why I would like to know if there is a better way to store and > > manage such information? > > > > Thanks > > > > Matt > > > > ---(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 > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PGCLIENTENCODING behavior of current CVS source
Title: default Signature No problem, I replied to save typing the email addresses and forgot to remove the subject! You are fine + I apologize! I just got sooo tired of the 'professionalism' that seems to take over and you were 'the lucky winner' whom I abused as the address template.. I've been in this list (on and off) since more than 3 years now. I was either off or this is the first time that people forget their basic communication skills (assuming they had those skills ever) gs Weiping wrote: guenter strubinsky wrote: I hate to ask for a restriction of the 1st ammendment, but the life of many colleagues and mine would be much easier if those racists and idiots would not waste our bandwidth.Nobody but them is -to my beliefs- interested in their flaming each other. Please knock them off the board. pardon, but what's the problem of my post, or it's yours? -- With kind regards   gÃnter strubinsky email: [EMAIL PROTECTED] Web: http://www.itcons.us "They who would give up an essential liberty for temporary security, deserve neither liberty or security" Benjamin Franklin This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message may contain confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail <>
Re: [GENERAL] How to deal with almost recurring data?
When I've said date arithmetics, I was referring to Java date arithmetics... I've been there, and I can tell you that it's not pleasant to have the application go haywire 2 times a year on sommer/winter time switching, and this is not the only thing which can go wrong - and you'll only see it after half a year when it makes your production system unusable... that said, I managed finally to get it right, but don't expect that Java date arithmetics will just work. If you go for storing rules instead of dates, that will mean that you will have to program around your queries, either in the client or in some procedural language on the server. Java would be easier here I guess, but make sure you test every possible date/hour/second for the next 5 years :-) Regarding speed for the solution storing dates, I guess it could work fine if you have the right indexes... but I don't know too much about this, I'm just pretty sure it will be up to your requirements if you tune it properly. Cheers, Csaba. On Tue, 2004-11-16 at 18:38, [EMAIL PROTECTED] wrote: > Thanks > The standard case is: I enter a date and 2 parameters of the event and I > get the result set. > Even though with Java date arithmetics are quite simple and I could > therefore extend my software in that fashion, I would much rather perform > all queries entirely in the database. > So in case I go about it the way I described, I will have 4 million > entries in the table that stores the dates and the references to the > event. > Now - how do I handle that database if I want to maintain very good query > times (<1-3 seconds)? > I have no idea how to handle a 1GB database in terms of query performance, > like what tricks to use, what to do and what to avoid. > Thanks > > Matt > > > --- UrsprÃngliche Nachricht --- > Datum: 16.11.2004 16:32 > Von: Csaba Nagy <[EMAIL PROTECTED]> > An: [EMAIL PROTECTED] > Betreff: Re: [GENERAL] How to deal with almost recurring data? > > > I would say it all depends on what you want to do with the data. > > If you want to look up all the possible occurences for an event, it > > might be useful to have the simple solution you described. If you will > > only look up the next n occurences starting from a given date, you > might > > be better off storing the rule to derive the dates, and then calculate > > them in your software, but this will make your software more > complicated > > for sure (dealing with date arithmetics can be incredibly complex, I've > > been there). I would go with the simple solution as long as there is > > enough space in the DB to store all the occurences... > > > > HTH, > > Csaba. > > > > On Tue, 2004-11-16 at 15:53, [EMAIL PROTECTED] wrote: > > > I am creating a database which is supposed to contain many data > entries > > > (events) that differ only in the date they occur. > > > > > > So let's say event 1 occurs every Monday, Tuesday and Sunday between > > > January 1st and May 30th 2005. > > > > > > How do I store and manage such data in a meaningful way? > > > > > > The simple idea would be to store the event itself in one table and > have > > > another table containing all the dates (all Mondays, Tuesdays and > Sundays > > > between 2005-01-01 and 2005-05-30) plus a foreign key to event_ID => > > > (date, event_id). > > > > > > The problem is that we are dealing with several tenthousand events, > > > resulting in several million single dates if I stored it in the > described > > > manner. > > > > > > That is why I would like to know if there is a better way to store > and > > > manage such information? > > > > > > Thanks > > > > > > Matt > > > > > > ---(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 > > > ---(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] Fwd: Problem installing Postgresql on MDK10.0
"Alejandro D. Burne" <[EMAIL PROTECTED]> writes: > I'll be trying to install postgres 8.0b1/b4; when I make the config a > message appear: > "configure: error: readline library not found" > $ rpm -qa | grep readline > libreadline4-4.3-7mdk > libreadline4-devel-4.3-7mdk Try looking at the config.log output in the part where it's trying to find readline (should be near the end); the link error messages may be enlightening. If not, try "ldd" on the readline library file to see what its dependencies are --- the best theory I have is that you are missing some library it requires. (It ain't necessarily termcap.) Of course if you are this would indicate a packaging mistake for libreadline, because it should have had an RPM dependency on whatever it needs. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PGCLIENTENCODING behavior of current CVS source
guenter strubinsky <[EMAIL PROTECTED]> writes: > ... I've been in this list > (on and off) since more than 3 years now. I was either off or this is > the first time that people forget their basic communication skills > (assuming they had those skills ever) None of this is coming from actual list members. As far as I can tell, it's just one net.kook who has decided to try to disrupt the list by forging bogus mail that appears to come from list members (at least to the list 'bot it looks that way --- you can tell the stuff is phony by comparing the Received: headers to real mail from those people). Ignore it, and the child will eventually get bored and go away. As long as people keep rising to the bait, though, he'll probably keep trolling. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] question about temp table in function
I'm working on a plpgsql function that creates and populates a temporary table. I would like the function to first drop the temp table, if it already exists. I'm not sure how to accomplish this, though. My first inclination was to simply wrap the 'drop table' command in an exception handling block and ignore the exception if the table does not exist. I'm not sure what error condition to catch, though, so rather than specifying a "WHEN" condition, I just had something like the following: begin execute 'drop table my_temp'; exception -- do nothing end; That didn't work; apparently the "WHEN" condition is necessary. What condition should I be trapping for? Is there a better way to accomplish this? I thought about querying pg_tables, and seeing if a record exists. Would that be a better approach? Thanks, Tim ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] question about temp table in function
On Tue, Nov 16, 2004 at 01:39:45PM -0600, Timothy Perrigo wrote: > begin > execute 'drop table my_temp'; > exception > -- do nothing > end; > > That didn't work; apparently the "WHEN" condition is necessary. What > condition should I be trapping for? I assume you're using one of the 8.0 betas; earlier versions of PostgreSQL didn't have exception handling. Appendix A of the documentation shows the conditions you can trap. Here's a way to find out what exception you need without having to search the entire list: CREATE OR REPLACE FUNCTION foo() RETURNS BOOLEAN AS $$ BEGIN DROP TABLE my_temp; RETURN TRUE; END; $$ LANGUAGE plpgsql; \set VERBOSITY verbose SELECT foo(); ERROR: 42P01: table "my_temp" does not exist CONTEXT: SQL statement "DROP TABLE my_temp" PL/pgSQL function "foo" line 2 at SQL statement LOCATION: DropErrorMsgNonExistent, utility.c:144 The error code is 42P01, which Appendix A shows as UNDEFINED TABLE. The exception-handling block would therefore be: BEGIN DROP TABLE my_temp; EXCEPTION WHEN undefined_table THEN NULL; END; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] I'm about to release the next postgresql RFD.
That would be the perfect solution for us, but apparantly not for the rest of the world. Since they are offering us 4 and saying we can apply for the other 16 in 6 months, I would take that and not try to take an all or nothing attitude. More of a work with the newsgroup people then against them attitude. Personally, I will be very happy if we get approved. I switched to the newsgroup from the maillist as soon as I found out about it, and my news server doesn't carry this list so I have to access it through news.postgresql.org ""Gary L. Burnore"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > At 01:54 AM 11/14/2004, Sim Zacks wrote: > >BTW, in Outlook Express if you are posting to the news.postgresql.org server > >it will not send a message to news.groups. So this message will not get > >there unless someone puts it there. > > > >If 4 are official and 16 are unofficial, why would that bother you? > > Because the groups already exist on many servers. Make them all legit and > gate them all. Problem solved. > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] How to overload POSITION
Hello,
I'm working on a datatype which I call dnaseq. The basic stuff like
input/output functions, comparison operators, etc., work well.
I have overloaded the CHARACTER_LENGTH function without problems:
CREATE OR REPLACE FUNCTION character_length(dnaseq)
RETURNS integer
AS 'dnaseq','dnaseq_charlen'
LANGUAGE 'C' IMMUTABLE STRICT;
Now, I want to overload the POSITION(lftval IN rgtval) function, but how
do I do that? I've tried
CREATE OR REPLACE FUNCTION position(dnaseq,dnaseq)
RETURNS integer
AS 'dnaseq'
LANGUAGE 'C' IMMUTABLE STRICT;
But now I get:
ERROR: syntax error at or near "(" at character 36
LINE 1: CREATE OR REPLACE FUNCTION position(dnaseq,dnaseq)
I assume this is because the POSITION function uses "IN" to separate
arguments, in stead of a comma. Is there a way to overload it?
--
Greetings from Troels Arvin, Copenhagen, Denmark
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Fwd: Problem installing Postgresql on MDK10.0
--On Dienstag, November 16, 2004 17:39:12 +0100 "Alejandro D. Burne" <[EMAIL PROTECTED]> wrote: Sorry, but no reply on [EMAIL PROTECTED] -- Forwarded message -- From: Alejandro D. Burne <[EMAIL PROTECTED]> Date: Mon, 15 Nov 2004 15:00:58 +0100 Subject: Problem installing Postgresql on MDK10.0 To: [EMAIL PROTECTED] I'll be trying to install postgres 8.0b1/b4; when I make the config a message appear: running MDK 10.0, but no problem here (and never had). "configure: error: readline library not found" $ rpm -qa | grep readline libreadline4-4.3-7mdk libreadline4-devel-4.3-7mdk Then I try; $ ./configure --with-libraries=/lib --with-includes=/usr/include/readline (where the libs are) Try running "ldconfig" and look if /lib contains valid links to libreadline.so.4(.3). Also try ldconfig -p to examine the linker cache then. /lib looks like this on my MDK 10.0 installation then: libreadline.so -> libreadline.so.4.3 libreadline.so.4 -> libreadline.so.4.3 libreadline.so.4.1 -> ../lib/libreadline.so.4 libreadline.so.4.2 -> ../lib/libreadline.so.4 libreadline.so.4.3 [...] -- Bernd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID's
Matt <[EMAIL PROTECTED]> writes: > I ask because I'm currently using them to join a single table to rows in > arbitrary tables, something like: > > +-+ > | JOINME | > +-+ > | foreign_oid | > | foreign_id | > | ... | > +-+ > > Where foreign_oid is the tableoid of the table and foreign_id is the > (serial, not oid) id of the row in that table. > > I dunno if it's bad design, but it's darn handy. How do you make use of this? It seems like you would need your code to know which foreign_oid referred to which table to actually perform the join. -- greg ---(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] How to overload POSITION
Troels Arvin <[EMAIL PROTECTED]> writes:
> I assume this is because the POSITION function uses "IN" to separate
> arguments, in stead of a comma. Is there a way to overload it?
Double-quote "position" in the create function command --- it's a
keyword.
Be aware also of the argument order gotcha. Per gram.y:
| POSITION '(' position_list ')'
{
/* position(A in B) is converted to position(B, A) */
FuncCall *n = makeNode(FuncCall);
n->funcname = SystemFuncName("position");
n->args = $3;
n->agg_star = FALSE;
n->agg_distinct = FALSE;
$$ = (Node *)n;
}
Come to think of it, you'll also need to create your function in the
pg_catalog schema, because that's implied by SystemFuncName(). Which
means it won't get dumped by pg_dump. How wedded are you to being able
to say "IN"?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How to overload POSITION
On Tue, 16 Nov 2004 15:56:00 -0500, Tom Lane wrote: > [... cut advice ...] Thanks. > How wedded are you to being able to say "IN"? It's only a would-be-nice-to-have. My dnaseq data type exploits the fact that DNA sequences are made from a very small alphabet (four characters), so strings can be compressed/packed 4:1 while still being directly usable. A POSITION(dnaseq IN dnaseq) would mean that the dnaseq-values don't have to be converted to text before being searched. But I can live with my existing DNASEQ_POSITION(dnaseq,dnaseq) solution (which works directly on the dnaseq packed strings). (I will also try to create a specialized index for long strings, hopefully using some substring array algorithmics - but that's another story.) -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] question about temp table in function
On Nov 16, 2004, at 2:11 PM, Michael Fuhr wrote: \set VERBOSITY verbose SELECT foo(); ERROR: 42P01: table "my_temp" does not exist CONTEXT: SQL statement "DROP TABLE my_temp" PL/pgSQL function "foo" line 2 at SQL statement LOCATION: DropErrorMsgNonExistent, utility.c:144 The error code is 42P01, which Appendix A shows as UNDEFINED TABLE. The exception-handling block would therefore be: BEGIN DROP TABLE my_temp; EXCEPTION WHEN undefined_table THEN NULL; END; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ That's exactly what I needed. I didn't think to set the verbosity to get the error code. Thanks! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostGreSQL to Access Updatable recordset
On second thought, I am now confused. The data is updatable when using linked tables, just not when using ADO recordsets connected to forms. I tried connected to PostGreSQL using DAO, and I got the recordset back, but it was also non-updatable. For the time being I am using Linked Tables, even though it is much slower to open the forms using that methodology. -- Sim Zacks IT Manager Compulab to email me it is sim "the at sign" compulab "the dot" co "the dot" il "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > hat pretty much solves my problem. Also I found that Access doesn'r support > ODBC updateable cursors until at least Access 2002 (we're using Access > 2000), though I thought I had figured out how to get around that and it > didn't work. > I assumed the ODBC driver did support updateable cursors simply because > there is an option in the driver settings called "Updateable cursor" > > Thank you for your clarification. > Sim Zacks > IT Manager > Compulab > to email me use: sim "the at sign" compulab "the dot" co "the dot" il > > > "Alvaro Herrera" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > On Tue, Nov 09, 2004 at 01:53:18PM +0200, Sim Zacks wrote: > > > 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. > > > > Postgres does not support updatable cursors yet. Maybe that's why the ADO > > driver changes the cursor type from AdOpenDynamic to AdOpenStatic. > > > > -- > > Alvaro Herrera (<[EMAIL PROTECTED]>) > > "Nunca se desea ardientemente lo que solo se desea por razón" (F. > Alexandre) > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > > > > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [webmaster] Question
Ok :))
I am developing a Windows application, under NT 4 version.
I am connecting to database throught ODBC (I had installed postgreSQL
drivers)
My database version... mmm... I am not sure, but I think 7.4.2 version
I am programing in Visual FoxPro 8.0, but this information is not important
I think.
In sql i can do something like this::
begin transaction
Update where clave= @clave
IF @@error= 0 and @@rowcount= 0
Insert ... ( @clave, )
commit transaction
I don't know wether "@@rowcount" exists in postgreSQL
Thanks you in general and in special to this "someone" that will be happy to
give me some help.
>From Palma de Mallorca
Fernando
- Original Message -
From: "Robert Treat" <[EMAIL PROTECTED]>
To: "Fernando Fernández" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, November 12, 2004 4:11 PM
Subject: Re: [webmaster] Question
On Fri, 2004-11-12 at 07:34, Fernando Fernández wrote:
> Hi :))
> I am Fernando, from Mallorca - Spain
>
> I am using PostgreSQL for my databases. Everything is going all right,
> but now i need in my application to know how many records has updated an
> update command.
>
> I know I can do something like:
> SELECT COUNT(*) AS xCount FROM ...
> IF xCount = 0
> INSERT ...
> ELSE
> UPDATE ...
> ENDIF
>
> but I would like do something like:
> UPDATE ...
> IF records_updated = 0
> INSERT
> ENDIF
>
> is it possible?
Yes, this is certainly possible, but there's no way we can explain it to
you without knowing some details of your work environment. Please send
an email to either [EMAIL PROTECTED] or
[EMAIL PROTECTED] that includes some details of your
programming environment (pl language, php, C, whatever; database
version; any driver layers involved) and someone will be happy to give
you some more detailed help.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Data from Internet direct into PostgeSQL Database??
You can have a cron job download the file to your local machine and then use the copy command to process it into your tables. I don't think that copy would be able to handle an Internet location, though I could be wrong. "chris" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi! > > Is there any Way or have somebody a Function to do something like this > copy skurs from 'http://ichart.finance.yahoo.com/table.csv?s=TKAG.DE&d=10&e=15&f=2004&g=d&a= 3&b=5&c=1991&ignore=.csv'; > > Would be great if you could help me! > > THX > Christian Meier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] ERROR: column "id" does not exist
Hy i have a problem with the following statement: SELECT MAX(id) FROM apconfig; if i send this statement i got the following error: ERROR: column "id" does not exist Can anyone explain me this error? There is the column id, and there are also entries in it. thx ---(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] PostGreSQL to Access Updatable recordset
hat pretty much solves my problem. Also I found that Access doesn'r support ODBC updateable cursors until at least Access 2002 (we're using Access 2000), though I thought I had figured out how to get around that and it didn't work. I assumed the ODBC driver did support updateable cursors simply because there is an option in the driver settings called "Updateable cursor" Thank you for your clarification. Sim Zacks IT Manager Compulab to email me use: sim "the at sign" compulab "the dot" co "the dot" il "Alvaro Herrera" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Tue, Nov 09, 2004 at 01:53:18PM +0200, Sim Zacks wrote: > > 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. > > Postgres does not support updatable cursors yet. Maybe that's why the ADO > driver changes the cursor type from AdOpenDynamic to AdOpenStatic. > > -- > Alvaro Herrera (<[EMAIL PROTECTED]>) > "Nunca se desea ardientemente lo que solo se desea por razón" (F. Alexandre) > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] I spoke with Marc from the postgresql mailing list.
"Marc G. Fournier" <[EMAIL PROTECTED]> wrote: > On Sat, 13 Nov 2004, Gary L. Burnore wrote: > >> This isn't about encouraging ISP's to carry your lists. It's about >> fixing a problem caused by your list's gateways. > > A problem that you perceive, yet nobody else seems to ... very isolated > problem ... of coufse, you *could* set the group to moderated on your news > server, with approprait moderator address setup, and be done with it ... > in fact, I even offered you a direct feed so that time delays were at a > minimum ... *shrug* > > Russ has already commented that he a) understands what we are doing > (which, apparently you don't, but that's okay) and b) knows of other > groups doing similar gatewaying ... I'd have no problem with it if you either didn't use Big-8 names or else kept your gateway groups restricted to your own server. But these groups are propagating to *other* servers which is a clear violation of the spirit of USENET. If these groups are not added to the "official" checkgroups list (either through the normal voting process or by fiat from Russ et al.) then I intend to request that my ISP remove them from our server. -- Wayne Brown (HPCC #1104) | "When your tail's in a crack, you improvise [EMAIL PROTECTED] | if you're good enough. Otherwise you give | your pelt to the trapper." "e^(i*pi) = -1" -- Euler | -- John Myers Myers, "Silverlock" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Data from Internet direct into PostgeSQL Database??
Hi! Is there any Way or have somebody a Function to do something like this copy skurs from 'http://ichart.finance.yahoo.com/table.csv?s=TKAG.DE&d=10&e=15&f=2004&g=d&a=3&b=5&c=1991&ignore=.csv'; Would be great if you could help me! THX Christian Meier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] I spoke with Marc from the postgresql mailing list.
[EMAIL PROTECTED] (Brian {Hamilton Kelly}) writes:
>(Personally, I blame the original authors of Netscape for incorporating a
>news "reader" that could access multiple servers. Before then, most
>readers of news had no option other than to read ALL their desired
>newsgroups from ONE server, that of their university/employer/ISP. Since
>that capability appeared in Netscape, and other browser/newsreaders,
>there has been a proliferation of *really* private newsgroups, such as
>borland.*; even microsoft.* was originally only available from one
>"site".)
I've been involved in private newsgroup hierarchies since about
1996. They exist, they're useful, and they're important. I've written
code to let me use my own newsreader with multiple servers for just such a
reason.
I firmly believe that there should be a better standard for
newsgroup names that includes (the|a) server you can access it through.
- Tim Skirvin ([EMAIL PROTECTED])
--
http://www.killfile.org/~tskirvin/ Skirv's Homepage < <*>
http://www.killfile.org/~tskirvin/software/ Skirv's Software
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Data from Internet direct into PostgeSQL Database??
Of course you would only want a cron job if you wanted to automate the process. Otherwise you would just download the file and save it and then use the copy command. "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > You can have a cron job download the file to your local machine and then use > the copy command to process it into your tables. I don't think that copy > would be able to handle an Internet location, though I could be wrong. > > > "chris" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > Hi! > > > > Is there any Way or have somebody a Function to do something like this > > copy skurs from > 'http://ichart.finance.yahoo.com/table.csv?s=TKAG.DE&d=10&e=15&f=2004&g=d&a= > 3&b=5&c=1991&ignore=.csv'; > > > > Would be great if you could help me! > > > > THX > > Christian Meier > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID's
> How do you make use of this? It seems like you would need your code to know > which foreign_oid referred to which table to actually perform the join. Sorry, wasn't very clear about what it does: select * from mytable t left join joinme j on t.id = j.foreign_id and t.tableoid = j.foreign_oid; I use it for 'PostIt note' type data that I want to be able to stick to any other row in the DB. Keeping the referential integrity is a bit of extra work, but I'm working on it :) If you're going the other way, yes, you'll need to find out what tables are joined to your postit first. But that's easy with: select foreign_oid::regclass from joinme where... But back to my original question: are those tableoid's going to suddenly wrap around? M ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Problems importing Unicode
I have batch files with entries such as INSERT INTO country VALUES (248,'ALA','AX','Åland Islands'); INSERT INTO country VALUES (384,'CIV','CI','Côte d\'Ivoire'); I tried to execute them using "pgsql \i filename.sql" Unfortunately, I keep getting an error message: "ERROR: invalid byte sequence for encoding "UNICODE": 0xc56c" How can that be possible? My database is set to encoding "UNICODE" and so are the batchfiles. Why does that not work? Thanks Matt ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ERROR: column "id" does not exist
On Mon, Nov 15, 2004 at 01:44:47PM +0100, Sandro Daehler wrote: > SELECT MAX(id) FROM apconfig; > > if i send this statement i got the following error: > > ERROR: column "id" does not exist > > Can anyone explain me this error? There is the column id, and there are > also entries in it. The column might have a different case or possibly leading or trailing spaces, in which case you'll have to quote it verbatim. What does \d apconfig show? If that doesn't show the problem then try this: SELECT quote_ident(attname) FROM pg_attribute WHERE attrelid = 'apconfig'::regclass; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] How to suppress echo while executing batch files?
I have to execute fairly large batch files (200-300MB) with several million INSERT commands. When I do that from pgsql using the \i switch, I get the echo INSERT 0 1 for every single insert statement of my batch file. Since I assume that this slows down the whole process I would like to know if there is a way to suppress that echo and only get error messages and such? Thanks Matt ---(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] Problems importing Unicode
> I have batch files with entries such as > > INSERT INTO country VALUES (248,'ALA','AX','Åland Islands'); > INSERT INTO country VALUES (384,'CIV','CI','Côte d\'Ivoire'); > > I tried to execute them using "pgsql \i filename.sql" > > Unfortunately, I keep getting an error message: > "ERROR: invalid byte sequence for encoding "UNICODE": 0xc56c" > > How can that be possible? > My database is set to encoding "UNICODE" and so are the batchfiles. > > Why does that not work? I bet your batch file is not encoded in UNICODE (UTF-8). -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How to suppress echo while executing batch files?
On Wed, Nov 17, 2004 at 02:17:16AM +0100, [EMAIL PROTECTED] wrote: > I have to execute fairly large batch files (200-300MB) with several million > INSERT commands. Can these files be rewritten to use COPY instead of INSERT? Performance might improve. > When I do that from pgsql using the \i switch, I get the echo > INSERT 0 1 > > for every single insert statement of my batch file. > > Since I assume that this slows down the whole process I would like to know > if there is a way to suppress that echo and only get error messages and > such? \set QUIET -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] table configuration tweak for performance gain.
Michael, I don't mind at all. 3.244 ms compared to 15706.179 ms. A sizeable difference. Screen scraps follow. Allan mill2=> set enable_seqscan=off\g SET mill2=> explain analyse select count(*) from history where pointname = 'MILL2-SPEED' and dt > (now() - interval '5 minutes')::timestamp\g QUERY PLAN -- Aggregate (cost=34815.05..34815.05 rows=1 width=0) (actual time=2.267..2.275 rows=1 loops=1) -> Index Scan using dtindex on history (cost=0.00..34783.32 rows=12690 width=0) (actual time=1.931..1.931 rows=0 loops=1) Index Cond: (((pointname)::text = 'MILL2-SPEED'::text) AND (dt > ((now() - '00:05:00'::interval))::timestamp without time zone)) Total runtime: 3.244 ms (4 rows) mill2=> set enable_seqscan=on\g SET mill2=> select count(*) from history where pointname = 'MILL2-SPEED' and dt > (now() - interval '5 minutes')::timestamp\g count --- 0 (1 row) mill2=> explain analyse select count(*) from history where pointname = 'MILL2-SPEED' and dt > (now() - interval '5 minutes')::timestamp\g QUERY PLAN -- Aggregate (cost=10163.50..10163.50 rows=1 width=0) (actual time=15705.388..15705.395 rows=1 loops=1) -> Seq Scan on history (cost=0.00..10131.77 rows=12690 width=0) (actual time=15705.286..15705.286 rows=0 loops=1) Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND (dt > ((now() - '00:05:00'::interval))::timestamp without time zone)) Total runtime: 15706.179 ms (4 rows) > -Original Message- > From: Michael Fuhr [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 17 November 2004 12:19 > To: Harvey, Allan AC > Cc: Tom Lane > Subject: Re: [GENERAL] table configuration tweak for performance gain. > > > On Wed, Nov 17, 2004 at 09:39:11AM +1100, Harvey, Allan AC wrote: > > > The solution then was:- > > an index of the right columns. > > explicit, exact type casting ( I'm a casual ingres user, type > > casting is something I never need or think you can do) > > PostgreSQL 8.0 will allow cross-type index usage, making the explicit > cast unnecessary. > > > turning off enable_seqscan for specific queries seemed > to help a bit too. > > Performance in general might improve if you address the planner's > reasons for chosing an inefficient plan. Even though you're satisifed > with performance now, would you mind posting the output of "EXPLAIN > ANALYZE select ..." with enable_seqscan on and then with it off? > If nothing else, an analysis might be educational for others. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > ---(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.
On Wed, Nov 17, 2004 at 12:41:20PM +1100, Harvey, Allan AC wrote: > mill2=> explain analyse select count(*) from history where pointname = > 'MILL2-SPEED' and dt > (now() - interval '5 minutes')::timestamp\g How typical is this query? How many distinct values does pointname have? Have you considered adding an index on pointname or a multicolumn index on pointname and dt? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] PostGreSQL to Access Updatable recordset
Thank you for your response, however that is not what I am trying to do. I would like to be able to add records and modify data directly in the form. What I was trying to do was grab an ADO recordset and attach it to the form, which works fine, but then the data is not updatable. I found a MS article which states that before Access 2002 ADO recordsets for ODBC sources could not be updated. Linked tables works for updating, and while I didn't want to use them, I am using those for the forms that need to be updateable and dynamic recordsets for the ones that don't. Sim ""Goutam Paruchuri"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] I spoke with Marc from the postgresql mailing list.
On Monday, in article
<[EMAIL PROTECTED]>
[EMAIL PROTECTED] "Tim Skirvin" wrote:
> [EMAIL PROTECTED] (Brian {Hamilton Kelly}) writes:
>
> >(Personally, I blame the original authors of Netscape for incorporating a
> >news "reader" that could access multiple servers. Before then, most
> >readers of news had no option other than to read ALL their desired
> >newsgroups from ONE server, that of their university/employer/ISP. Since
> >that capability appeared in Netscape, and other browser/newsreaders,
> >there has been a proliferation of *really* private newsgroups, such as
> >borland.*; even microsoft.* was originally only available from one
> >"site".)
>
> I've been involved in private newsgroup hierarchies since about
> 1996. They exist, they're useful, and they're important. I've written
> code to let me use my own newsreader with multiple servers for just such a
> reason.
>
> I firmly believe that there should be a better standard for
> newsgroup names that includes (the|a) server you can access it through.
Hmm; what about RFC1738:
3.7. NNTP
The nntp URL scheme is an alternative method of referencing news
articles, useful for specifying news articles from NNTP servers (RFC
977).
A nntp URL take the form:
nntp://://
where and are as described in Section 3.1. If :
is omitted, the port defaults to 119.
The is the name of the group, while the is the numeric id of the article within that newsgroup.
Note that while nntp: URLs specify a unique location for the article
resource, most NNTP servers currently on the Internet today are
configured only to allow access from local clients, and thus nntp
URLs do not designate globally accessible resources. Thus, the news:
form of URL is preferred as a way of identifying news articles.
Mind you, IME there are few browsers masquerading as newsreaders that
understand the form of URL, so the likelihood of
finding one that bothers to interpret nntp: is minimal.
--
Brian {Hamilton Kelly} [EMAIL PROTECTED]
"I don't use Linux. I prefer to use an OS supported by a large multi-
national vendor, with a good office suite, excellent network/internet
software and decent hardware support."
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Transactions in different DB
Hi all I have to develop an embedded database using memory FLASH and memory RAM Some tables must be saved in RAM and some tables in FLASH ( RAM and FLASH are two different mounted directories in the file system). I saw it's possible to save different databases in different directories with CREATE DATABASE options. It's possible to save only some tables in different directories?? If I want to work with two different databases, it's possible to do a single transaction on two different databases ?? Thanks Stefano Farina
Re: [GENERAL] Lost databases
Martijn van Oosterhout wrote: Let's start with the version of postgresql you're using. Next, were you running VACUUM FULL regularly? It sounds like XID wraparound to me, no idea how to solve that. Got backups? On Wed, Nov 10, 2004 at 12:56:33PM +0200, Liviu BURCUSEL wrote: Hi ! I'm in the ultimate state of dispair. Over the night some of my databases just disapeared. But they did not dissaper totally. I can still connect to them but I cannot dump them. When doing a "select * from pg_database" I see only 2 databases I hardly ever use (they are used once in a couple of mounths). Template0 and template1 are missing. All the databases that I currently use dissapeared! Please help me to restore the integrity of the system. I'm in dense fog here :( Liviu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html Only VACUUM FULL solves XID wraparound problem? I mean VACUUM FULL locks the entire table. How can I test if I am close to XID wraparround? Kostis ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] DBManager 3.0
Nice interface, but its crippleware. Anything that may be of use is locked out from testing exactly what they would tell me. I tried to run reports. Need to purchase. Tried to diagram but not sure what that did for me other than tell me what tables I already had. could see some benefit for building a complex db but not needed to test this software. Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: COS [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 6:25 AM To: PostgreSQL List; MySQL List Subject: ANN: DBManager 3.0 Hi, We are pleased to announce the awaited DBManager Professional 3.0. This version is a new GUI with lots of new features. DBManager is a client application to manage your databases for Windows environments. Some of the features in this version are - Manage all objects in the database server: MySQL 5, PostgreSQL 8, SQLite 3, Xbase, Interbase, Firebird, MSAccess(*), MSSQL Server(*), Sybase(*), Oracle(*) - New and improved design, Multi Document TAB Interface compatible with Visual Studio .NET - Create, build, import and export queries - Create PHP and ASP(*) scripts for the web to any database flavour(*) (PHP/native and ODBC/ASP) - Import structure and data from a variety of source including: Text Files, XML, MSAccess, MSExcel, Dbase III/Clipper/FoxPro, Paradox, ODBC, etc - Export structure and data to MSAccess, MSExcel, Text Files (CSV/Formatted), HTML, XML, SQL Dumps - User and Privilege (*) Manager - Task Builder (*) to automate processes - Diagram Designer (*) to create and mantain your database structure - New Table Designer - New Query Editor - Report and Form Builders (*) to create reports and Forms in HTML format - Monitor (*) the Activities of the Server, Database or Table with a simple click - DBManager Console to type your commands - Database Comparer (*) to keep your databases updated - and much, much more. (*) Available only in the Enterprise Edition If you want to know more about DBManager or download the new version please go to http://www.dbtools.com.br. Best Regards, Crercio O. Silva / DBTools Software -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID's
If you have a primary key you can self join the table on the rows that would define a table as duplicate and delete the one with a higher primary key field. If the table is related to other tables (ie the pk is an fk in another table) you have to make sure you update all the rows to point to the new key. If there is no pkey, then you would do a select distinct into a temp table as was suggested by Michael in the post above mine "Holger Klawitter" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 A little bit OT, but: is there a way of removing duplicate rows in a table without OIDs? Mit freundlichem Gruß / With kind regards Holger Klawitter - -- lists klawitter de -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQFBmeVA1Xdt0HKSwgYRAklNAJ4l0KtMVF2Tkhx5ZgyPR38LHXd/LACeNk4q mwf/f5rI7VdckPfgfUotnSc= =qpV0 -END PGP SIGNATURE- ---(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 8: explain analyze is your friend
[GENERAL] Controlling maximal on-disk size of a table
I have a table that is essentially a log where new entries are streaming in continually and from time to time I throw old entries away to keep the table from growing. I understand that in addition to issue a DELETE FROM log WHERE date < xxx I also need to issue a VACUUM log so that new entries will use the space of deleted entries. Now I want to reserve a certain amount of disk storage to hold the log table. So I first let the table (plus its index, its toast table and toast index) grow until it is about to reach the maximum size. Then a daemon continually deletes old entries and vacuums the table so the on-disk usage stays more or less constant from now on, at least this is the idea. Of course I would like to keep as much history as possible, given the available space. Also the log may sometimes be quiet and sometimes quite busy, also the size of the text entries may vary quite a bit. Now to make a good guess about when to issue the next delete, I need to estimate how much of the on-disk usage is accounted for by deleted entries. I can of course count the number of entries, estimate the bytes needed for storage by averaging the length of the text column, adding the size of the fixed columns and compare that to the on-disk size to conclude how much space is still available. As for the index I assume it is has a fixed size per row. But these queries are expensive because the log may easily contain millions of entries with an on disk size in the range of a few GB, and must be repeated quite often to prevent sudden bursts of new entries from overflowing the log. Is there a better way to get at the current "free space" inside of a table/index? __ Mit WEB.DE FreePhone mit hoechster Qualitaet ab 0 Ct./Min. weltweit telefonieren! http://freephone.web.de/?mc=021201 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
