Re: [HACKERS] A modest proposal: get rid of GUC's USERLIMIT

2004-11-13 Thread Andrew McMillan
On Thu, 2004-11-11 at 23:05 -0500, Bruce Momjian wrote:
 Andrew McMillan wrote:
 -- Start of PGP signed section.
  On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote:
   Andrew McMillan [EMAIL PROTECTED] writes:
When tracking down gnarly problems in heavily multi-user applications
enabling higher log levels at selective points has the potential to help
_a lot_ with diagnostic detail, without smothering you in _every_
detail.
   
   Sure.  As I pointed out in the other thread, if you want to allow an app
   to do this, you can make available a SECURITY DEFINER function that
   performs the desired SET on its behalf.  By setting execute permissions
   on the function and/or including restrictions in the function's code,
   you can make this as tight or as loose a loophole as you like.  So it's
   certainly possible to do what you want in any case.  I think the issue
   at hand is what's appropriate to provide as hard-wired functionality.
  
  That sounds excellent - I hadn't realised that this workaround would be
  possible, and indeed with this in place that will provide even better
  control over the facility.
 
 OK, here is one vote for the ALTER USER/remove USERLIMIT croud, and you
 were the person who originally mentioned the problem.  You don't think
 the function creation is hard.  Perhaps that's the way to go then.

Yes, I agree - it seems good.

Also, I don't see that this function would need to be written under
stress as ISTR you suggested elsewhere - any analysis like this is
going to be following on from review of other statistics - I think it
would normally be a well-planned process.

Cheers,
Andrew.

-
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201  MOB: +64(272)DEBIAN  OFFICE: +64(4)499-2267
  How many things I can do without! -- Socrates
-



signature.asc
Description: This is a digitally signed message part


Re: [HACKERS] A modest proposal: get rid of GUC's USERLIMIT

2004-11-11 Thread Andrew McMillan
On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote:
 Andrew McMillan [EMAIL PROTECTED] writes:
  When tracking down gnarly problems in heavily multi-user applications
  enabling higher log levels at selective points has the potential to help
  _a lot_ with diagnostic detail, without smothering you in _every_
  detail.
 
 Sure.  As I pointed out in the other thread, if you want to allow an app
 to do this, you can make available a SECURITY DEFINER function that
 performs the desired SET on its behalf.  By setting execute permissions
 on the function and/or including restrictions in the function's code,
 you can make this as tight or as loose a loophole as you like.  So it's
 certainly possible to do what you want in any case.  I think the issue
 at hand is what's appropriate to provide as hard-wired functionality.

That sounds excellent - I hadn't realised that this workaround would be
possible, and indeed with this in place that will provide even better
control over the facility.

Regards,
Andrew.
-
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201  MOB: +64(272)DEBIAN  OFFICE: +64(4)499-2267
   The secret of being a bore is to say everything -- Voltaire
-



signature.asc
Description: This is a digitally signed message part


Re: [HACKERS] [PATCHES] CVS should die

2004-11-06 Thread Andrew McMillan
On Fri, 2004-11-05 at 15:37 -0500, Tom Lane wrote:
 
 One of the reasons I'm disinclined to move is that none of the proposed
 alternatives seem especially, um, mature.  AFAIK this project has never
 had CVS lose any data in the eight years we've used it.  I'd want a
 comparable level of trust in any replacement SCM, and I haven't got it.

A very sane reason.  I've lost my share of stuff with SVN in trialling
it, but we are switching our company over to Arch, which seems to offer
significantly more benefits.  From our trialling of it, I think it has a
more robust and mature repository structure too.

Watching the PostgreSQL team developing I would think that Arch would
provide much better support for the developers than SVN would. 

Switching to Arch is more work, but it also offers a lot more benefits -
including the opportunity for individuals to maintain their own trees,
and be able to work out which patchsets from someone else's tree have
not been applied.  If anything is going to become the open-source
BitKeeper it will be this, I think.

Cheers,
Andrew.

-
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201  MOB: +64(272)DEBIAN  OFFICE: +64(4)499-2267
 Planning an election?  Call us!
-



signature.asc
Description: This is a digitally signed message part


Re: [HACKERS] LinuxTag wrapup

2004-07-05 Thread Andrew McMillan
On Sun, 2004-07-04 at 13:11 +0200, Andreas Pflug wrote:
 
 That's right, and initially they will only serve MySQL, but it will be 
 extendable to support any db system. It will be GPL (or licenseable, but 
 since it's a tool and not a platform IMHO GPL is ok).
 If things work out as they seem, I'd contribute the pgsql stuff.

The fact that it is written in Kylix might make this harder.  I was
looking at it last night, after your post, to see if I could package it
for Debian, but that Kylix requirement just kind of killed any ideas I
had in that direction, since I've never been able to get the environment
to even install on a Debian system :-(

Regards,
Andrew McMillan.

-
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201  MOB: +64(272)DEBIAN  OFFICE: +64(4)499-2267
Whereof one cannot speak, thereon one must remain silent. -- Wittgenstein
-


signature.asc
Description: This is a digitally signed message part


Re: [HACKERS] casting for dates

2001-09-26 Thread Andrew McMillan

On Thu, 2001-09-27 at 08:30, Vince Vielhaber wrote:
 
 I'm trying to use an integer from a table to add/subtract time in months.
 IOW:
 
 create table foo(nummonths int);
 
 select now() - nummonths months;

newsroom=# select now() - interval( text(3) || ' months');
?column?

 2001-06-27 08:56:27+12
(1 row)


Crude, but hey: it works :-)

Cheers,
SAndrew.
-- 

Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7217MOB: +64(21)635-694OFFICE: +64(4)499-2267


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] Link to bug webpage

2001-08-21 Thread Andrew McMillan

Vince Vielhaber wrote:
 
 What who thinks of what has actually become irrelevant.  The following
 is clear:
 
 o No tool will replace the mailing lists
 o The mailing lists are where discussion will be held
 o Many/most maintainers have no desire to update bug reports

If anyone is interested, I am willing to undertake to be the link between
the bugs mailing list and a bugs database.  This should allow developers to
continue to deal with the mailing list, just CCing a special e-mail address
whenever a bug was fixed.  I would then take care of finding the
appropriate bug(s) in the database and marking them as fixed.

There are two large, well-used bugs databases that I am aware of with
somewhat different strengths:
 - The Debian Bug Tracking System
 - Bugzilla
there are a gazillion others, of course, but let's just consider those two
for the moment.

In some ways the Debian bug tracking system is a closer fit to the way
PostgreSQL currently works, since it drives into a mailing list, bug
submission is via e-mail and bug control is via e-mail as well.

Bugzilla is probably a closer fit in reality, since it is more focused
around bugs for a single application.  If Bugzilla were installed I'm sure
some functionality could be added into it along the lines of the Debian BTS
too.

Regards,
Andrew.
-- 
_
Andrew McMillan, e-mail: Andrew @ catalyst . net . nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(21)635-694,  Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] Postgresql log analyzer

2001-08-21 Thread Andrew McMillan

Gilles DAROLD wrote:
 
 Hi all,
 
 Here is a first draft generated by a log analyzer for postgres I've wrote today:
 
 http://www.samse.fr/GPL/log_report/
 
 In all this html report there is what I'm able to extract minus the statistics.
 
 I need to know what people want to see reported to have a powerfull log analyzer,

I like what you have there so far.

For my own use I would like to see the ability to turn some of these off,
and also perhaps a summary page that you would click through to the more
detailed reports.

The 'query' page is kind of complicated too.  Would it be possible to put
that into a table layout as well?
+---+
|select...  |
+++++---+
|stat|stat|stat|stat ...|   |
+++++---+

sort of layout.

It would be nice to see an EXPLAIN on the query page, but you would want
this to be an option, I guess.  I imagine you could do this by getting the
EXPLAIN at log analysis time if it isn't in the logs.

Cheers,
Andrew.
-- 
_
Andrew McMillan, e-mail: Andrew @ catalyst . net . nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(21)635-694,  Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] Incomplete idea about views and INSERT...RETURNING

2001-07-22 Thread Andrew McMillan

Tom Lane wrote:
 
 While this all seems good at first glance, I am wondering just how
 useful it really would be in practice.  The problem is: how do you know
 which rows to return in the RETURNS query?  If you don't qualify the
 selection then you'll get all the rows in the view, which is surely not
 what you want.  You could restrict the select with clauses like WHERE
 col1 = NEW.col1, but this is not necessarily going to be efficient, and
 what's worse it only works for columns that are supplied by the initial
 insert into the view.  For example, suppose an underlying table has a
 SERIAL primary key that's generated on the fly when you insert to it.
 The RETURNS query has no way to know what that serial number is, and so
 no way to select the right row.  It seems like the rule author is up
 against the very same problem that we wanted INSERT RETURNING to solve.
 
 So I'm still baffled, unless someone sees a way around that problem.
 
 Could we get away with restricting INSERT RETURNING to work only on
 inserts directly to tables (no ON INSERT DO INSTEAD allowed)?  Or is
 that too much of a kluge?

Isn't it likely that the person writing the RULE would want to internally use an
INSERT ... RETURNING query and that the RETURNS ... should either use values from
that, or use a SELECT clause keyed on values from that?

Cheers,
Andrew.
-- 
_
   Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(27)246-7091, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709

---(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: [HACKERS] SAPDB Open Souce

2001-04-29 Thread Andrew McMillan

Bruce Momjian wrote:
 
 I downloaded it.  The directories are two characters in length, the
 files are numbers, and it is a mixture of C++, Python, and Pascal.  Need
 I say more.  :-)

OK, I'll bite: you need to say more.

What is it like at handling transactions?  What sort of full-text indexing does it
have?  Can I have transactions within transactions?  What sort of tools are
available for managing database extents?  How compliant is it with the various SQL
standards?  How does performance compare with PostgreSQL and others?  Does it have
an extensible type system?  Does it have an 'internal' language to compare with
PL/SQL or PL/PGSQL?  How well does it scale on SMP systems?  Can I perform a single
query across multiple databases?  What performance monitoring tools does it come
with?

Hell, in a statement like that you don't even indicate if those directories are
so-named within the source code, or in an installed data environment.  Whichever
environment they do apply to, however, I'm sure there are good systems in place for
dealing with them.  And of course C++, Python and Pascal are all languages with
plenty of proponents, so there's no problem with those.

Your statement is so light on utility that it persuades me to download it for myself
and try it - but that is presumably exactly the effect you were after, wasn't it?

Regards,
Andrew.
-- 
_
   Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(21)635-694, Fax: +64(4)499-5596, Office: +64(4)499-2267xtn709

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] --tuning compile and runtime option (?)

2001-04-10 Thread Andrew McMillan

Bruce Momjian wrote:
 
 Well, again, I will write a performance tuning article this month, which
 hopefyully will help people.
 
 My recommendation on shared memory is that if you have a machine that is
 going to be used only for PostgreSQL, the shared memory should be
 increased to the point where you are not seeing any swap page-ins during
 normal use.  I know you have the kernel buffer cache for all unused
 memory, but those pages are copied in and out of the PostgreSQL buffer
 cache for processing, which can be an expensive operation.
 
 Now how do you automate something to increase shared memory until there
 are no page swap-ins under normal use.  I think the administrator will
 have to be involved because a script has no idea what a normal load
 looks like.  The best we could do is to monitor swap-ins as part of the
 running server and report to the administrator that there is extra
 memory around that could be used for shared memory.

Brilliant.  Thanks for that - it's exactly the sort of information / statistics
stuff that it is useful to know.

I use Progress RDBMS on a few sites.  On a Progress database I get this sort of
information which can help me tune things:


  Activity  - Sampled at 04/11/01 12:32 for 892:23:25.

  EventTotal  Per SecEventTotal  Per Sec
   Commits 50518  0.0   Undos24  0.0
Record Updates 72407  0.0Record Reads 121294681 37.7
Record Creates 37065  0.0  Record Deletes 19807  0.0
 DB Writes 25720  0.0DB Reads   1551040  0.4
 BI Writes 14701  0.0BI Reads 14534  0.0
 AI Writes 0  0.0
  Record Locks645952  0.2Record Waits 0  0.0
   Checkpoints62  0.0 Buffers Flushed 13102  0.0

  Rec Lock Waits0 %BI Buf Waits  0 %AI Buf Waits  0 %
  Writes by APW 0 %Writes by BIW 0 %Writes by AIW 0 %
  Buffer Hits  16 %
  DB Size  96 MB   BI Size3192 KAI Size   0 K
  FR chain  0 blocks   RM chain  1 blocks
  Shared Memory 29864 KSegments  1

  8 Servers, 7 Users (0 Local, 7 Remote, 0 Batch),0 Apws



Or, for a more reasonable length of sample:

  Activity  - Sampled at 04/11/01 12:42 for 0:09:26.

  EventTotal  Per SecEventTotal  Per Sec
   Commits14  0.0   Undos 0  0.0
Record Updates 7  0.0Record Reads 90488159.8
Record Creates 1  0.0  Record Deletes 0  0.0
 DB Writes38  0.0DB Reads  1636  2.8
 BI Writes 5  0.0BI Reads 0  0.0
 AI Writes 0  0.0
  Record Locks69  0.1Record Waits 0  0.0
   Checkpoints 0  0.0 Buffers Flushed 0  0.0

  Rec Lock Waits0 %BI Buf Waits  0 %AI Buf Waits  0 %
  Writes by APW 0 %Writes by BIW 0 %Writes by AIW 0 %
  Buffer Hits  99 %
  DB Size  96 MB   BI Size3192 KAI Size   0 K
  FR chain  0 blocks   RM chain  1 blocks
  Shared Memory 29864 KSegments  1

  8 Servers, 9 Users (0 Local, 9 Remote, 0 Batch),0 Apws


I find this is quite a straightforward and useful set of statistics.  Just having
this sort of functionality easily available gets me used to the sorts of numbers I
can expect in different hardware environments.  It is then simple to conduct basic
tuning by running reports (or other operations) and seeing the sorts of numbers you
get for the sample period.

Of course Progress has a bunch more stuff you can tune, including separate processes
for asynchronously writing database pages, or their after-image and before-image
files.  I don't have any databases that get that arcane though, hence the APW, BIW
and AIW statistics are zero above.

Regards,
Andrew.
-- 
_
   Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] Speaking of Indexing... (Text indexing)

2001-04-10 Thread Andrew McMillan

Poet/Joshua Drake wrote:
 
 Good day,
 
 I've been experimenting a bit with Full Text Indexing in PostgreSQL. I
 have found several conflicting sites various places on the net pertaining
 to whether or not PostgreSQL supports FTI, and I was hoping I could find
 an authoritative answer here - I tried searching the website's archives,
 but the search seems to be having some problems.
 
 At any rate, I am running a CVS snapshot of 7.1, and I have been trying to
 create a full text index on a series of resumes. Some of these exceed 8k
 in size, which is no longer a storage problem of course with 7.1, but I
 seem to have run into the wicked 8k once again. Specifically:
 
 ERROR:  index_formtuple: data takes 9344 bytes, max is 8191
 
 Furthermore, after trying to just index on a 8191-character long substring
 of the resume, I run into the following:
 
 ERROR:  btree: index item size 3948 exceeds maximum 2713
 
 The only way I could actually get the index created was to substring the
 body of the resumes down to 2k. I also later tried using HASH rather than
 BTREE, which worked, but none of these solutions really appreciably
 increased performance in the way we were hoping.
 
 Are these known and accepted limitations of the current 7.1
 implementation, or am I doing something terribly wrong? ;)
 On Tue, 10 Apr 2001, Thomas Lockhart wrote:

You need to use the 'contrib' code for full-text indexing.  The indexing you are
trying to do with that is just using the whole content of the string as the index
value.  Close to useless.

The contrib code is in contrib/fulltextindex.

I have a hacked version of that which changes it to keyword indexing, if you're
interested.

Regards,
Andrew.
-- 
_
   Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

---(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: [HACKERS] Re: xlog loose ends, continued

2001-03-13 Thread Andrew McMillan

Tom Lane wrote:
 
 "Mikheev, Vadim" [EMAIL PROTECTED] writes:
  That'll be true in any case, unless we refuse to start up at all upon
  detecting xlog corruption (which doesn't seem like the way to fly).
  Not sure what we can do about that.
 
  What I would refuse in the event of log corruption is continuing
  normal database operations.
 
 Hmm.  We could do that if we had some notion of a read-only operating
 mode, perhaps.  But we don't have one now and I don't want to add it
 for 7.1.  Can we agree to look at this more for 7.2?

I'd love to see PostgreSQL have a read-only mode of some kind that let
enquiry function against a possibly otherwise corrupted database,
without the stress of worrying that you might be making things worse.

I know other DB servers that have this sort of thing, and it has been a
life-saver for me on occasion to allow critical information to be
extracted before you nuke it all and start over.

Cheers,
Andrew.
-- 
_
       Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

---(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: [HACKERS] How to shoot yourself in the foot: kill -9 postmaster

2001-03-07 Thread Andrew McMillan

Vadim Mikheev wrote:
 
 Nevertheless, subj is rised. BTW, does anybody know results of kill -9
 in Oracle/Informix/etc? Just curious -:)

Progress has no problem with it that I have ever seen.

Regards,
Andrew.
-- 
_
   Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster



Re: [HACKERS] mailing list messages

2001-03-06 Thread Andrew McMillan

Bruce Momjian wrote:
 
 I wonder if the new Tips at the bottom of email messages can be enabled
 for users during their first 30 days of mailing list subscription, then
 not appear?

What about having some basic _PostgreSQL_ tips in there?  This would be
especially cute for -novice, I think.

We must all be able to come up with 100 or so little one or two liners
about PostgreSQL can't we?

Just a thought,
Andrew.
-- 
_
   Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

---(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: [HACKERS] Upper limit on number of buffers?

2000-12-31 Thread Andrew McMillan

Michael J Schout wrote:
 
 On Sun, 24 Dec 2000, Joe Conway wrote:
 
 On redhat 6.2 I know that you can use /etc/sysctl.conf to do this as well.
 
 Just add this to /etc/sysctl.conf.
 
 kernel.shmall = 134217728
 kernel.shmmax = 134217728
 
 After this, your tunables will be restored every time that the system boots.

I can confirm that that applies under Debian as well, although there is
also some stuff in the startup script for Debian to configure files max
specifically for PostgreSQL.

Cheers,
Andrew.
-- 
_
   Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267



Re: [HACKERS] Why vacuum?

2000-12-14 Thread Andrew McMillan

Tim Allen wrote:
 
 On Thu, 14 Dec 2000, Christopher Kings-Lynne wrote:
 
  Plenty of other databases need to be 'vacuumed'.  For instance, if you have
  an ms access database with 5 MB of data in it, and then delete all the data,
  leaving only the forms, etc - you will be left with a 5MB mdb file still!
 
  If you then run 'Compact Database' (which is another word for 'vacuum'), the
  mdb file will be reduced down to 500k...
 
 Ooh... Hope MS Access isn't going to be taken seriously as a benchmark
 here :-). The same is also true of MapInfo, by the way, but I'm not
 holding that up as a benchmark either ;-).

:-)

I think that the non-overwriting storage manager actually bought a lot
more for PostgreSQL than it does for MS Access.

In earlier versions of PostgreSQL it was possible to "time travel" your
database and so run your query agains the database as it was at a
particular time / date.  This advanced feature turns out to be useful in
very few situations, and is very expensive in terms of storage.

Still, "if it works, don't fix it" also applies.  The PostgreSQL storage
manager is quite efficient as it is now, and most of us do have quiet
periods when we can safely vacuum the database, which is why it has had
to wait until now.

This will be quite a big change for 7.2, and getting the performance
right will no doubt challenge these hackers whom we are all greatly
indebted to.

Cheers,
Andrew.
-- 
_____
   Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267



Re: [HACKERS] Import text field

2000-11-24 Thread Andrew McMillan

Carlos Jacobs wrote:
 
 Hi:
 I have a MS Access database with tables containing TEXT fields.
 I need import that info in a postgres 7 table.
 How to do it?
 If I use copy from, dont work.

I have a perl program which will import this sort of multi-line CSV data
that is not handled by the COPY ... DELIMITER ... sort of mechanism in
PostgreSQL.

E-mail me privately if you want a copy.

Regards,
Andrew.
-- 
_
Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267



Re: [HACKERS] Full text indexing (Question/request)

2000-10-17 Thread Andrew McMillan

Bruce Momjian wrote:
 
 See contrib/fulltextindex.

An easy answer, but not a very good solution in the real world.

contrib/fulltextindex requires you to jump through hoops in developing
queries to retrieve your data.  It's also very space-inefficient in that
a table with a fulltextindex on a field needs another table with a
complete set of values for that field, as well as any substrings of that
field, and then it wants two indexes on that table.  Add that up!

It would be nice to see a true index which was full text.  It would be
nice to see a true index which allowed an individual field to index to
many entries through a function interface.  This would straightforwardly
allow people to create their own simple functions to perform full-text,
keyword or other indexing schemes quite simply.

It naively appears to me that the function interface is moving closer to
achieving this with the enhancements in 7.1 to the use of setof()
returns combined with the earlier enhancement to indexing on function
results.

If a function fulltextindex(text) returned a setof() the substrings in
its text argument, how hard will it be to index on that return value and
allow WHERE field=fulltextindex('substring') to use that index?

Of course such a fulltextindex() function would have to know not to do
any processing on the string when called in the second situation.  Is it
possible for functions to do this sort of trick?  It seems a bit beyond
the pale!

I would _love_ to see full-text or keyword indexing natively in
PostgreSQL.

Regards,
Andrew.
-- 
_
Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267