[GENERAL] About isolation levels
Hi, in te postgres manual says "...Postgres offers the read committed and serializable isolation levels..." but i didn't find how i select betwen this levels i.e. can i select the level? Thanks in advance ivan ...
Re: [GENERAL] v7.1 RPMs
7.1 isn't released yet, let alone the rpm's. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com - Original Message - From: "Gordan Bobic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, December 12, 2000 10:26 AM Subject: [GENERAL] v7.1 RPMs > Have the RPMs been published yet? I seem to remember somebody saying that > they should be on the web site by the last weekend, but I can't find them. > A link would be appreciated... I need some of the new features, but I'd > rather avoid working out all the strange file locations (i.e. not > /usr/local) for a RH6.2 system... > > Cheers. > > Gordan
[GENERAL] Too much traffic
I'm out of here. This list has too much traffic. Too bad it's not on usenet. /Roger, overwhelmed -Original Message- From: Jon Brace [mailto:[EMAIL PROTECTED]] Sent: den 8 december 2000 11:11 To: [EMAIL PROTECTED] Subject: [GENERAL] Loading tables into Memory In Oracle, there is a method bywhich tables can be loaded into memory as a method of speeding up queries. Is there anyway this can be done in Postgres? Does anyone know a good site outlining general methods of database optimisation? Thanks, - Jonathan Brace Junior Software Engineer Blitz The Net [EMAIL PROTECTED] www.blitzthenet.com
Re: [GENERAL] Large files on linux
>Bruce Momjian wrote: > >This has nothing to do with PostgreSQL. We don't need large files to go >over 4gig tables. We split them on our own. >Peter Eisentraut wrote: > >But, unless you do chunking, it _does_ affect dumpfile size. Someone >posted awhile back a script that did dumpchunking. Should be in the >archives. >-- >Lamar Owen >WGCR Internet Radio >1 Peter 4:11 Sorry for not being clear in my first post. The problems I am having are when dumping data, as Peter rightly guessed. I remembered reading something about this on the list, that's why i asked my questions here. Although I considered writing a script to divide the dumped data into smaller chunks, i (for other reasons) preferred to go for a cleaner solution, and that is having a system able to cope with large files. Thanks to all for your replies. Here's a brief summary of things: Linux kernel 2.4 supports large files. As mentioned, SuSE and RedHat, both at version 7.0 come with patched 2.2.x kernels that support large files. SuSE uses the ReiserFS, while RedHat uses ext2fs. Since I'm using RH 6.2, the best thing to do is to update to 7.0 and see if that helps. BTW, RH 7.0 is ready for 2.4 kernels (Thanks Trond) -- Lic. Fernan AgueroTel: (54-11) 4752-0021 Instituto de Investigaciones Biotecnologicas Fax: (54-11) 4752-9639 Universidad Nacional de General San Martin
RE: [GENERAL] Re: Unanswered questions about Postgre
No, SELECT FOR UPDATE locks the rows matching the constraint and if no rows match, none are locked. as in (somewhat oracle syntax) BEGIN SELECT 'x' INTO a FROM test WHERE y = 10 FOR UPDATE; UPDATE test SET z = 12; EXCEPTION WHEN no_data_found THEN INSERT INTO test VALUES (10, 12); END; -Original Message- From: Edward Q. Bridges [mailto:[EMAIL PROTECTED]] Sent: den 12 december 2000 17:10 To: PostgreSQL general mailing list; Roger Wernersson Subject: RE: [GENERAL] Re: Unanswered questions about Postgre On Tue, 12 Dec 2000 12:04:46 +0100, Roger Wernersson wrote: > > I can't BEGIN - SELECT FOR UPDATE - INSERT or UPDATE - COMMIT as someone > might insert after my SELECT and before my INSERT. > correct me if i'm wrong, but a select for update locks the table for an insert or an update until the end of the transaction. --e-- > Is there a right solution? > > /Roger > > -Original Message- > From: Tim Kientzle [mailto:[EMAIL PROTECTED]] > Sent: den 12 december 2000 02:10 > To: PostgreSQL general mailing list > Subject: [GENERAL] Re: Unanswered questions about Postgre > > > > Of course, people really shouldn't be inserting > > objects which already exist, ... > > On the contrary, the best way to test if > something already exists is to just try the > INSERT and let the database tell you if > it's already there. Both faster and more > reliable than doing SELECT then INSERT. > > - Tim >
Re: [GENERAL] manuals
> > > Don't fix anything. We discussed things generated via a script. This > > > is one of those things and there's a link at the top of the page to take > > > you either to the current version or the development version. > > > > But 6.5 isn't even our current version. > > > > Your point? This appears at the TOP of the page with Documentation in > large bold type: > > Documentation > Current Release Docs (ver 7.0) > > Current Development Docs > > > The two lines that begin with Current are links. Perhaps I should put > it bright blinking red text with arrows pointing to it? Why can't we put the most current release at the top? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] conversion
"Nathan Suderman" <[EMAIL PROTECTED]> writes: > how can I convert varchar to int? I can not seem to cast the type > (::int2) and there are no conversion functions in the docs > (int(varchar)). What Postgres version are you using? This works fine for me in 7.0 and later... regards, tom lane
[GENERAL] performance : index selection
Is there any way to specify an index for PostgreSQL to use for a particular query? My application has to execute a lot of queries like this : select count(*) from MyTable where IndexedField_1 = value_1 and IndexedField_2 = value_2 and ... IndexedField_n = value_n Sometimes Query Planner decides not to use any of the indexes, but that I can handle by setting enable_seqscan to off. What's worse is that sometimes a wrong index is used. I know exactly which index is the best one, because by the time I execute the query I already have results of all the primitive queries of this kind : select count(*) from MyTable where IndexedField_i = value_i; (i = 1..n) Can I somehow let the Query Planner know about it ? I am pretty sure I can't do this with 7.0.2 , but what about 7.1 ?? Thanks a lot, Dmitriy Agafonov.
Re: [GENERAL] manuals
On Tue, 12 Dec 2000, Bruce Momjian wrote: > > > > Who put 6.5 manuals on top of 7.0 on that web page? Not everyone has > > > > 72 inch monitor you know. > > > > > > > > > > I thought I fixed that, but it seems to have moved again. Vince, is > > > this the page that gets generated automatically? > > > > > > > > > > Don't fix anything. We discussed things generated via a script. This > > is one of those things and there's a link at the top of the page to take > > you either to the current version or the development version. > > But 6.5 isn't even our current version. > Your point? This appears at the TOP of the page with Documentation in large bold type: Documentation Current Release Docs (ver 7.0) Current Development Docs The two lines that begin with Current are links. Perhaps I should put it bright blinking red text with arrows pointing to it? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
Re: [GENERAL] help with referential integrity
On Mon, 11 Dec 2000, Soma Interesting wrote:
>
> I'm attempting, for the first time, to make use of referential integrity,
> but I'm getting an error.
>
> Warning: PostgreSQL query failed: ERROR: referential integrity violation -
> key referenced from LessonsPages not found in Lessons
>
> Here is the table's structures:
>
> CREATE SEQUENCE "LessonsPages_id_seq";
> CREATE TABLE "LessonsPages" (
> "id" int4 DEFAULT nextval('"LessonsPages_id_seq"') NOT NULL,
> "lid" int4 NOT NULL
> REFERENCES "Pages"
> ON DELETE CASCADE,
> "pid" int4 NOT NULL
> REFERENCES "Lessons"
> ON DELETE CASCADE,
> PRIMARY KEY ("lid", "pid")
> );
Did you really want lid referencing pages and
pid referencing lessons rather than the other
way around? It seems below you're inserting
things the other way around.
> The error comes when:
>
> INSERT INTO "Pages" (order_num) VALUES (1);
> SELECT id FROM "Pages";
> | id |
> 16
> 1 Row
>
> INSERT INTO "Lessons" (lesson_name, order_num) VALUES ('test lesson', 2);
> SELECT id FROM "Lessons";
> | id |
> 9
> 1 Row
>
> INSERT INTO "LessonsPages" (lid,pid) VALUES (9,16);
>
>
> What am I doing wrong?
>
> - - - - - - -
> - - - - -
> WARNING: Some experts believe that use of any keyboard may cause serious
> injury.
> Consult Users Guide.
>
> [EMAIL PROTECTED]
>
Re: [GENERAL] manuals
On Tue, 12 Dec 2000, Bruce Momjian wrote: > > > > I'm a newbie. > > > > I have printed out nearly 2 mb of postscript manuals without > > realizing that they are for 6.5. I'm going to use 7.0.3 > > and would like to know whether I'm foolish to rely on 6.5 manuals. > > > > I really don't feel like wasting paper and printing 7.0 manual and > > dumping 6.5. > > > > Who put 6.5 manuals on top of 7.0 on that web page? Not everyone has > > 72 inch monitor you know. > > > > I thought I fixed that, but it seems to have moved again. Vince, is > this the page that gets generated automatically? > > Don't fix anything. We discussed things generated via a script. This is one of those things and there's a link at the top of the page to take you either to the current version or the development version. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
RE: [GENERAL] Re: Unanswered questions about Postgre
On Tue, 12 Dec 2000 12:04:46 +0100, Roger Wernersson wrote: > > I can't BEGIN - SELECT FOR UPDATE - INSERT or UPDATE - COMMIT as someone > might insert after my SELECT and before my INSERT. > correct me if i'm wrong, but a select for update locks the table for an insert or an update until the end of the transaction. --e-- > Is there a right solution? > > /Roger > > -Original Message- > From: Tim Kientzle [mailto:[EMAIL PROTECTED]] > Sent: den 12 december 2000 02:10 > To: PostgreSQL general mailing list > Subject: [GENERAL] Re: Unanswered questions about Postgre > > > > Of course, people really shouldn't be inserting > > objects which already exist, ... > > On the contrary, the best way to test if > something already exists is to just try the > INSERT and let the database tell you if > it's already there. Both faster and more > reliable than doing SELECT then INSERT. > > - Tim >
Re: [GENERAL] Sql query with partial matching
At 14:58 07/12/00 -0500, Travis Bauer wrote: >How would I write an sql statement which would select all the >records from a table where some string field contains >some substring? Example: find all the records where f1 contains >the world "cool." select * from table where f1 ~ 'cool'; or case insensitive select * from table where f1 ~* 'cool'; Steve -- thorNET - Internet Consultancy, Services & Training Phone: 01454 854413 Fax: 01454 854412 http://www.thornet.co.uk
[GENERAL] Foreign Key constraint and Alter Table command
Hi. I am using postgres 7.0.can't_remember_the_rest and I am not able to add a FK via alter table command. When I try to do it I get an "Feature not implemented" error message. This is what I've been trying to do: alter table foo add constraint foo_fk foreign key (a) references foo_2 where "a" is a column name in foo. I thought I would be able to add constraints via alter table by using pgsql 7. Was I wrong? Thanks in advance, Zirbes
[GENERAL] Loading tables into Memory
In Oracle, there is a method bywhich tables can be loaded into memory as a method of speeding up queries. Is there anyway this can be done in Postgres? Does anyone know a good site outlining general methods of database optimisation? Thanks, - Jonathan Brace Junior Software Engineer Blitz The Net [EMAIL PROTECTED] www.blitzthenet.com
Re: [GENERAL] manuals
> > > Who put 6.5 manuals on top of 7.0 on that web page? Not everyone has > > > 72 inch monitor you know. > > > > > > > I thought I fixed that, but it seems to have moved again. Vince, is > > this the page that gets generated automatically? > > > > > > Don't fix anything. We discussed things generated via a script. This > is one of those things and there's a link at the top of the page to take > you either to the current version or the development version. But 6.5 isn't even our current version. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] help with referential integrity
Date: Mon, 11 Dec 2000 18:43:55 -0800
To: [EMAIL PROTECTED]
From: Soma Interesting <[EMAIL PROTECTED]>
Subject: help with referential integrity
I'm attempting, for the first time, to make use of referential integrity,
but I'm getting an error.
Warning: PostgreSQL query failed: ERROR: referential integrity violation -
key referenced from LessonsPages not found in Lessons
Here is the table's structures:
CREATE SEQUENCE "LessonsPages_id_seq";
CREATE TABLE "LessonsPages" (
"id" int4 DEFAULT nextval('"LessonsPages_id_seq"') NOT NULL,
"lid" int4 NOT NULL
REFERENCES "Pages"
ON DELETE CASCADE,
"pid" int4 NOT NULL
REFERENCES "Lessons"
ON DELETE CASCADE,
PRIMARY KEY ("lid", "pid")
);
CREATE SEQUENCE "Lessons_id_seq";
CREATE TABLE "Lessons" (
"id" int4 nextval('"Lessons_id_seq"') NOT NULL,
"page_name" char(64),
"summary_email" bool,
"order_num" int2,
PRIMARY KEY ("id")
);
CREATE SEQUENCE "Pages_id_seq";
CREATE TABLE "Pages" (
"id" int4 nextval('"Pages_id_seq"') NOT NULL,
"page_name" char(64),
"summary_email" bool,
"order_num" int2,
PRIMARY KEY ("id")
);
The error comes when:
INSERT INTO "Pages" (order_num) VALUES (1);
SELECT id FROM "Pages";
| id |
16
1 Row
INSERT INTO "Lessons" (lesson_name, order_num) VALUES ('test lesson', 2);
SELECT id FROM "Lessons";
| id |
9
1 Row
INSERT INTO "LessonsPages" (lid,pid) VALUES (9,16);
What am I doing wrong?
- - - - - - -
- - - - -
WARNING: Some experts believe that use of any keyboard may cause serious
injury.
Consult Users Guide.
[EMAIL PROTECTED]
RE: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver
I am currently using PostgreSQL with a production system using JSP's and Servlets. The system currently services 250,000 SQL hits a day. The only problem that I have had is with the database pooling. I had to write my own custom database pooling code because I wasn't able to find any that worked appropriately for my situation. This is certainly not a Postgres issue though. Postgres has performed excellently for my project. The main problem that you have in a production system is the VACUUM of the database. This will in most cases severely inhibit performance. If your system isn't 24x7, then you should be fine as you can schedule this for off times. I believe that the need to vacuum the database will be removed in the next version due to WAL functionality, but I'm sure someone else is better qualified to explain that. I just wanted to vouch for the stability of Postgres in a production environment. - Nathan Barnett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Nikhil G. Daddikar Sent: Tuesday, December 12, 2000 8:42 AM To: [EMAIL PROTECTED] Subject: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver Hi Folks, I am a fan of pgsql and have been using it for lots personal projects where stability of PgSQL was not an issue and most of the time the machine was close to me, so that I could restart the daemons whenever there was a problem. However, now I am writing an ERP solution and am thinking of using EJB along with Postgresql. What I am concerned about is the reliability and stability of Postgresql and it's JDBC driver. Is anybody using Postgresql and JDBC in a "production" environment? Has anybody faced any wierd problems? For example, when I was using the tool TableGen (generates java code from database table), I found out that some of it was not working because PgSQL's JDBC driver didn't support getImportedKeys() and getExportedKeys(). Now are there any such issues? I wouldn't like to hear sometime later that, for example, there is no support for BLOBs as well. Any personal experiences will be greatly appreciated. Thanks for your time. -ngd.
[GENERAL] v7.1 RPMs
Have the RPMs been published yet? I seem to remember somebody saying that they should be on the web site by the last weekend, but I can't find them. A link would be appreciated... I need some of the new features, but I'd rather avoid working out all the strange file locations (i.e. not /usr/local) for a RH6.2 system... Cheers. Gordan
[GENERAL] help with referential integrity
I'm attempting, for the first time, to make use of referential integrity,
but I'm getting an error.
Warning: PostgreSQL query failed: ERROR: referential integrity violation -
key referenced from LessonsPages not found in Lessons
Here is the table's structures:
CREATE SEQUENCE "LessonsPages_id_seq";
CREATE TABLE "LessonsPages" (
"id" int4 DEFAULT nextval('"LessonsPages_id_seq"') NOT NULL,
"lid" int4 NOT NULL
REFERENCES "Pages"
ON DELETE CASCADE,
"pid" int4 NOT NULL
REFERENCES "Lessons"
ON DELETE CASCADE,
PRIMARY KEY ("lid", "pid")
);
CREATE SEQUENCE "Lessons_id_seq";
CREATE TABLE "Lessons" (
"id" int4 nextval('"Lessons_id_seq"') NOT NULL,
"page_name" char(64),
"summary_email" bool,
"order_num" int2,
PRIMARY KEY ("id")
);
CREATE SEQUENCE "Pages_id_seq";
CREATE TABLE "Pages" (
"id" int4 nextval('"Pages_id_seq"') NOT NULL,
"page_name" char(64),
"summary_email" bool,
"order_num" int2,
PRIMARY KEY ("id")
);
The error comes when:
INSERT INTO "Pages" (order_num) VALUES (1);
SELECT id FROM "Pages";
| id |
16
1 Row
INSERT INTO "Lessons" (lesson_name, order_num) VALUES ('test lesson', 2);
SELECT id FROM "Lessons";
| id |
9
1 Row
INSERT INTO "LessonsPages" (lid,pid) VALUES (9,16);
What am I doing wrong?
- - - - - - -
- - - - -
WARNING: Some experts believe that use of any keyboard may cause serious
injury.
Consult Users Guide.
[EMAIL PROTECTED]
Re: [GENERAL] Sql query with partial matching
On Thu, 7 Dec 2000, Travis Bauer wrote: > How would I write an sql statement which would select all the > records from a table where some string field contains > some substring? Example: find all the records where f1 contains > the world "cool." SELECT * FROM mytable WHERE f1 LIKE '%cool%'; or use a regular expression: SELECT * FROM mytable WHERE f1 ~* 'cool'; http://www.chapelperilous.net/~bmccoy/ --- "Pascal is Pascal is Pascal is dog meat." -- M. Devine and P. Larson, Computer Science 340
[GENERAL] ilike and --enable-multibyte=KOI8
Hello, Can anybody check and confirm(or not) this: if pgsql(DEVEL, from cvs-server) is built with locale and multibyte then "select * from table where row ilike '%a%'" crashes pgsql. "select * from table where row ilike 'a%'" doesn't craches. "select * from table where row like '%a%'" doesn't craches. If pgsql is built only with locale, pgsql isn't crashed... please, give me an advice for solving this problemm. I need pgsql with both options... -- Bye Juriy Goloveshkin
Re: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver
Hi, I started using the JDBC driver and have been slamming it with production level queries, quite a number of simultaneous threads etc. I have noticed some oddities as far as large resultsets returning off the database close to each other (threads finishing around the same time) and some ArrayStoreException's being thrown. I'm checking to see if this is driver related or java related. Appears to be on the java side of things. Otherwise, I don't use any of the driver's bells and whistles and it seems to be working very nicely. George Johnson - Original Message - From: "Peter T Mount" <[EMAIL PROTECTED]> To: "Nikhil G. Daddikar" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, December 12, 2000 6:33 AM Subject: Re: [GENERAL] Reliability Stability of PgSQL & it's JDBC driver > Quoting "Nikhil G. Daddikar" <[EMAIL PROTECTED]>: > > > Hi Folks, > > > > I am a fan of pgsql and have been using it for lots personal projects > > where > > stability of PgSQL was not an issue and most of the time the machine > > was > > close to me, so that I could restart the daemons whenever there was a > > problem. However, now I am writing an ERP solution and am thinking of > > using > > EJB along with Postgresql. What I am concerned about is the reliability > > and > > stability of Postgresql and it's JDBC driver. Is anybody using > > Postgresql > > and JDBC in a "production" environment? Has anybody faced any wierd > > problems? For example, when I was using the tool TableGen (generates > > java > > code from database table), I found out that some of it was not working > > because PgSQL's JDBC driver didn't support getImportedKeys() and > > getExportedKeys(). > > I needed some example on how to retrieve this information. Also, getTables() > still has some problems, but this is partially due to what types to accept (not > everything seems to use the same names like "TABLES" etc). > > The main one is large ResultSet's can slow things down, but careful planning of > any queries can help that. > > > Now are there any such issues? I wouldn't like to > > hear > > sometime later that, for example, there is no support for BLOBs as > > well. > > Large objects have been supported since version 6.2 some 2-3 years ago, > although the BLOB interface only appeared this April. > > Do not confuse getInputStream() to indicate that large objects are not > supported. There's about 6 other ways to use them within JDBC. > > > Any personal experiences will be greatly appreciated. > > Peter > > -- > Peter Mount [EMAIL PROTECTED] > PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ > RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/ >
[GENERAL] Re: [DOCS] organization file
> hi, what kind of organization file does postgresql use? Hi. I'm not sure what you mean by "file organization". Are you asking about the file format of tables, or about the directory layout? Both are discussed in the documentation afaik. - Thomas
Re: [GENERAL] postgresql-server-7.0.3-2.i386.rpm error DOH
Sorry, I see you are logged in as root, very strange error! MC
Re: [GENERAL] postgresql-server-7.0.3-2.i386.rpm error
I also had a problem installing the 7.0.3 RPM - it was conflicting with an old version of postgresql I had to uninstall the old RPM's first before it would work, I also found Kpackage sorts some problems out NB Are you logged in as root? MC
[GENERAL] postgresql-server-7.0.3-2.i386.rpm error
Hello! I've been trying to install 7.0.3, but I'm always getting the same error, even if I try to install in another computer. What can I do? [root@localhost /tmp]# rpm -i postgresql-7.0.3-2.i386.rpm [root@localhost /tmp]# rpm -i postgresql-devel-7.0.3-2.i386.rpm [root@localhost /tmp]# rpm -i postgresql-server-7.0.3-2.i386.rpm /sbin/ldconfig: warning: can't open var/tmp/rpm-tmp.10903 (Not a directory), skipping /sbin/ldconfig: warning: can't open 1 (No such file or directory), skipping Thanks in advance! José Vilson de Mello de Farias Dígitro Tecnologia Ltda - Brasil
[GENERAL] LIKE with macaddr types
All, I want to do a LIKE on a column of type macaddr, looking (for example) for all MAC addresses with the prefix "00:80", and so on. There's no ~~ operator for macaddr though... I tried casting macaddr to text, but that's undefined apparently (WTF?) - any ideas? Regards, Phil +--+ | Phil Mayers, Network Support | | Centre for Computing Services| | Imperial College | +--+
RE: [GENERAL] Re: Unanswered questions about Postgre
How do you mean "reliable"? This raises an interesting question though. How do I _reliably_ INSERT or UPDATE a row? I can't INSERT - fail - UPDATE as someone might delete the record in between my two statements. I can't BEGIN - INSERT - fail - UPDATE - COMMIT as the failing INSERT will invalidate my transaction (stupid). I can't BEGIN - SELECT FOR UPDATE - INSERT or UPDATE - COMMIT as someone might insert after my SELECT and before my INSERT. Is there a right solution? /Roger -Original Message- From: Tim Kientzle [mailto:[EMAIL PROTECTED]] Sent: den 12 december 2000 02:10 To: PostgreSQL general mailing list Subject: [GENERAL] Re: Unanswered questions about Postgre > Of course, people really shouldn't be inserting > objects which already exist, ... On the contrary, the best way to test if something already exists is to just try the INSERT and let the database tell you if it's already there. Both faster and more reliable than doing SELECT then INSERT. - Tim
[GENERAL] Which character data type to use?
Hello everyone, For storing character data such as surname, email address which is the best data type to use? This is my guess: Char - For small columns, fixed length in record VarChar - Is this fixed length in record? If not then is it stored in an 'overflow' area which will effect performance. Maybe theres an allocated length that sets how much is stored in the record Text - Only differences seems to be that no length is specified Can each of these be indexed??? Which of these is fastest considering hard disks are so cheap I don't much care about which takes the most bytes! NB The HTML manual didn't seem to explain this in detail - can anyone help me? Cheers, MC
Re: [GENERAL] Unanswered questions about Postgre
Quoting Tom Lane <[EMAIL PROTECTED]>: > The JDBC support for over-the-wire access to large objects used to > have some bugs, but AFAIK those are cleaned up in current sources > (right Peter?) Yes except for DatabaseMetaData.getTables() but thats not directly to do with large objects. As long as things settle down here by Saturday, I'll be sorting out what's outstanding... Peter -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Large files on linux
Quoting Fernan Aguero <[EMAIL PROTECTED]>: > Dear all: > > I am having trouble with large files on a Linux box (RH 6.2). I know > there > is a limit of 2 GB on the file size, but do not know if this is kernel > related, filesystem related or both. IIRC Partly Kernel, Partly FileSystem. For large tables we split the tables at the 1Gb point. The Storage Manager then deals with which file it needs to open when it knows that the file position is beyond any one 1Gb point. NB: The source was doing this at the 2Gb point but (6.3.x or 6.4.x I think) I did some tests for another project and found that Linux didn't like files exactly 2Gb in size. After discussion here, 1Gb was chosen as a nice round figure. -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Unanswered questions about Postgre
> Yes, this was my point. We now have TOAST, but by not going the extra > mile to enable storage of binary files, we really aren't taking full > advantage of our new TOAST feature. > > I can see people saying, "Wow, you can store rows of unlimited length > now. Let me store this jpeg. Oh, I can't because it is binary!" > Well, to me it seems that, when TOAST will be available (i.e. when the long awaited, most desired, more bloated, world- conquering 7.1 version will come-out...), 90% of the work it is already done to support also column-style BLOBs... at least for web applications, that are incidentally my focus. Any web programmer worth its salt could put up a simple layer that does base64 encode/decode and use "CLOBs" (I think TOAST columns could be called that way, right?)... and he should write anyway some interface for file uploading/downloading, since its client are using a browser as their frontend. Using PHP, it's no more than a few rows of code. Granted, base64 encode can waste a LOT of space, but it looks like a columbus' egg in this scenario. Maybe base64 could also be a quick way to write a binary "patch" for TOAST so it would be binary-compatible "natively"? Or am I saying a lot of bullsììt? :-) Just wanted to share some toughts. Merry Christmas to everybody... /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED]
