Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Oleg Bartunov]

On Thu, 9 Aug 2007, Tom Lane wrote:


Oleg Bartunov <[EMAIL PROTECTED]> writes:

On Thu, 9 Aug 2007, Tom Lane wrote:

... behavior that people want is "here's some words, get me a weighted
result", and if the weighting improves from time to time that's OK.
We need to provide that API too.



I think I understand. It's called non-exact (approximate) matching,


Right, exactly.  I don't claim that we have a perfect solution for
approximate matching; we don't.  But I think we should provide an
API function that is defined to do approximate matching, with the
understanding that the details of its behavior will change (for the
better hopefully) from release to release.  The first version might
not be very good, but we can improve it.


Aha, never thought you'd interested in something non-exact :) 
What'd be a name of such function ?


Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Trevor Talbot]

On 8/8/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> Oleg Bartunov <[EMAIL PROTECTED]> writes:
> > On Wed, 8 Aug 2007, cluster wrote:
> >> Does anyone know where I can request an OR-version of plainto_tsquery()?
>
> > plainto_tsquery expects plain text, use to_tsquery for boolean operators.
>
> Are either of these definitions really right?  If I type "foo bar baz"
> into Google, for instance, it seems to produce some sort of weighted
> result, neither a strict AND nor a strict OR.  Google didn't get where
> they are by misjudging what the simplest search behavior should be like.

As far as I'm aware Google is normally AND -- the catch is that it
doesn't always use keywords from the page itself.  Sometimes it'll
look for search terms that appear in pages that link to the returned
one; the cached version will notify you of this in the header.
Ranking seems to make words weighted by order and proximity, but of
course Google's full ranking behavior is another matter...

http://www.google.com/intl/en/help/basics.html#and
http://www.google.com/help/cheatsheet.html

For me personally, I expect to need a search interface that accepts
"AND", "OR", and "NOT" as boolean op words, and possibly parenthetical
grouping (IOW, everything to_tsquery supports in plain english form),
with freeform words defaulting to AND.  Is this the same thing most
people need?  I doubt it.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Tom Lane]

Oleg Bartunov <[EMAIL PROTECTED]> writes:
> On Thu, 9 Aug 2007, Tom Lane wrote:
>> ... behavior that people want is "here's some words, get me a weighted
>> result", and if the weighting improves from time to time that's OK.
>> We need to provide that API too.

> I think I understand. It's called non-exact (approximate) matching, 

Right, exactly.  I don't claim that we have a perfect solution for
approximate matching; we don't.  But I think we should provide an
API function that is defined to do approximate matching, with the
understanding that the details of its behavior will change (for the
better hopefully) from release to release.  The first version might
not be very good, but we can improve it.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Tom Lane]

Oleg Bartunov <[EMAIL PROTECTED]> writes:
> On Thu, 9 Aug 2007, Tom Lane wrote:
>> Are either of these definitions really right?  If I type "foo bar baz"
>> into Google, for instance, it seems to produce some sort of weighted
>> result, neither a strict AND nor a strict OR.  Google didn't get where
>> they are by misjudging what the simplest search behavior should be like.

> we provide strict basic query language via to_tsquery(), which could be 
> a foundation for different ql. We need consensus here and we leave it for
> future.

Since we're about to push tsearch into core, I'm not very happy with a
"leave it to the future" approach.  We need to get the API right *now*.

I think that a function defined as "take these words and do an
appropriate weighted search with them" doesn't necessarily have to
specify what the weighting is.  But if the definition is "find the AND
of these words", you can't fudge that later on.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Oleg Bartunov]

On Thu, 9 Aug 2007, Tom Lane wrote:


Oleg Bartunov <[EMAIL PROTECTED]> writes:

" neither a strict AND nor a strict OR" is not a good foundation for
database text search API.


Maybe not, but the Google boys have sure done well without telling
anyone what their algorithms are.

My feeling is that if you use an API that involves explicit AND and OR
operators (to_tsquery does this if I'm not mistaken) then you should
get a result that matches those semantics exactly.  But the other


right, to_tsquery does exact and predicted matching.


behavior that people want is "here's some words, get me a weighted
result", and if the weighting improves from time to time that's OK.
We need to provide that API too.


I think I understand. It's called non-exact (approximate) matching, 
when by default you search documents containing ALL words in query, weighted
in usual way, and then append documents, which contains either words in query 
weighted by the number of words found. It's useful for rare words.




Whether plainto_tsquery() should be defined that way, I'm not sure.
Maybe there's enough historical behavior behind it that we should
stick with defining it as "strict AND of these words".  But if so,


yeah, this was requested by people to have simple search interface.


I want another function that has a fuzzier weighted definition,
because I think that'll be what most applications actually want.


Tom, approximate search is a good challenge, it's a subject of research of
people from IR world. It's what I always wanted to do, but we have to 
support our families and no company was interested in such kind of search,

unfortunately.



The OP was asking for a version that has a strict OR behavior.
I'm not sure if that's really interesting or not ...


I understood now, what he wanted. He don't want to parse search query,
he wanted simplicity of plainto_tsquery() and ability for boolean search
of to_tsquery(). As I already said, people have their own query language
wrappers on top of to_tsquery. Unfortunately, we have no consensus here.

Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Tom Lane]

Oleg Bartunov <[EMAIL PROTECTED]> writes:
> " neither a strict AND nor a strict OR" is not a good foundation for 
> database text search API.

Maybe not, but the Google boys have sure done well without telling
anyone what their algorithms are.

My feeling is that if you use an API that involves explicit AND and OR
operators (to_tsquery does this if I'm not mistaken) then you should
get a result that matches those semantics exactly.  But the other
behavior that people want is "here's some words, get me a weighted
result", and if the weighting improves from time to time that's OK.
We need to provide that API too.

Whether plainto_tsquery() should be defined that way, I'm not sure.
Maybe there's enough historical behavior behind it that we should
stick with defining it as "strict AND of these words".  But if so,
I want another function that has a fuzzier weighted definition,
because I think that'll be what most applications actually want.

The OP was asking for a version that has a strict OR behavior.
I'm not sure if that's really interesting or not ...

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Oleg Bartunov]

On Thu, 9 Aug 2007, Tom Lane wrote:


Oleg Bartunov <[EMAIL PROTECTED]> writes:

On Thu, 9 Aug 2007, Tom Lane wrote:

Are either of these definitions really right?  If I type "foo bar baz"
into Google, for instance, it seems to produce some sort of weighted
result, neither a strict AND nor a strict OR.  Google didn't get where
they are by misjudging what the simplest search behavior should be like.



we provide strict basic query language via to_tsquery(), which could be
a foundation for different ql. We need consensus here and we leave it for
future.


Since we're about to push tsearch into core, I'm not very happy with a
"leave it to the future" approach.  We need to get the API right *now*.


API is available right now - to_tsquery realized it. You asked about
google-like API, which I dont' know exact description, 
" neither a strict AND nor a strict OR" is not a good foundation for 
database text search API.

We intentionally realized strict programming-like query language, since
in database search we need sort of exhaustive search, not just first
1000 results in some intuitive order, depending on the day of week
(do you know google is best at sunday night :).




I think that a function defined as "take these words and do an
appropriate weighted search with them" doesn't necessarily have to
specify what the weighting is.  But if the definition is "find the AND
of these words", you can't fudge that later on.


What do you mean "appropriate weighted search" ? Does it mean just 
use AND by default ?

Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Oleg Bartunov]

On Thu, 9 Aug 2007, Tom Lane wrote:


Oleg Bartunov <[EMAIL PROTECTED]> writes:

On Wed, 8 Aug 2007, cluster wrote:

Does anyone know where I can request an OR-version of plainto_tsquery()?



plainto_tsquery expects plain text, use to_tsquery for boolean operators.


Are either of these definitions really right?  If I type "foo bar baz"
into Google, for instance, it seems to produce some sort of weighted
result, neither a strict AND nor a strict OR.  Google didn't get where
they are by misjudging what the simplest search behavior should be like.


we provide strict basic query language via to_tsquery(), which could be 
a foundation for different ql. We need consensus here and we leave it for
future. Someone could write google like ql, but I didn't see any description 
what exactly google does. Currently, people write their own search wrappers 
which implement google-like ql.


Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Tom Lane]

Oleg Bartunov <[EMAIL PROTECTED]> writes:
> On Wed, 8 Aug 2007, cluster wrote:
>> Does anyone know where I can request an OR-version of plainto_tsquery()?

> plainto_tsquery expects plain text, use to_tsquery for boolean operators.

Are either of these definitions really right?  If I type "foo bar baz"
into Google, for instance, it seems to produce some sort of weighted
result, neither a strict AND nor a strict OR.  Google didn't get where
they are by misjudging what the simplest search behavior should be like.

regards, tom lane

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

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[Oleg Bartunov]

On Wed, 8 Aug 2007, cluster wrote:


Does anyone know where I can request an OR-version of plainto_tsquery()?


plainto_tsquery expects plain text, use to_tsquery for boolean operators.



I don't understand why it doesn't exist already: In most cases, when using 
user entered keywords to search for, there should be returned some  rows even 
though not ALL keywords are matched.


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



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Crosstab Question

[Michael Glaesemann]

On Aug 8, 2007, at 19:23 , David Blewett wrote:


Since the number of questions to be pivoted will vary per
questionnaire the method should accept a number of columns
parameter. The crosstab in the tablefunc contrib module expects the
columns to be explicitly identified before hand. I suppose it could
be possible to write a simple function to wrap the crosstab_n_cols
functions to be able to return a variable number, but that seems
hackish. Are there any other alternatives?


I've done just that in the past. It's pretty straightforward and has  
worked fine.


Michael Glaesemann
grzm seespotcode net




PGP.sig
Description: This is a digitally signed message part

Re: [GENERAL] Take your postgresSql on the road, and live to tell of it.

[Scott Ribe]

You can also have a trigger that records into a log table the id & table of
each record inserted/updated/deleted, and then it's a simple matter of
merging changes from a certain point forward by searching that table and
using the values of the current records.

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



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

[GENERAL] Crosstab Question

[David Blewett]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Hi All:

I'm building an application along the lines of the questionnaire
design on varlena.com [1], with slight modifications. In my design,
there's a table called chartversionquestion that collects different
questions into a bundle to create the questionnaire.

Using a view [2] very similar to the one on varlena, I pull all the
data from the value tables into one long list (cast to text). I
would like to be able to create a crosstab from this view such that
the short question name becomes a column header, with the related
value underneath.

Since the number of questions to be pivoted will vary per
questionnaire the method should accept a number of columns
parameter. The crosstab in the tablefunc contrib module expects the
columns to be explicitly identified before hand. I suppose it could
be possible to write a simple function to wrap the crosstab_n_cols
functions to be able to return a variable number, but that seems
hackish. Are there any other alternatives?

David Blewett

[1] http://www.varlena.com/GeneralBits/110.php
[2] http://www.varlena.com/varlena/GeneralBits/Tidbits/QnA/views.sql
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGul6MZmlc6wNjtLYRCA5hAJ9fSHjNI5gQs/eqZ85bHl4ZrGWG3ACfbEnG
zo1KFvZL8LraBESRySyVryg=
=w1Z8
-END PGP SIGNATURE-

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

Re: [GENERAL] Reordering columns, will this ever be simple?

[Martijn van Oosterhout]

On Wed, Aug 08, 2007 at 11:37:11AM -0500, Scott Marlowe wrote:
> If the danger of implementing this is some subtle bug that eats my
> data, I'd just as soon do without.  It's not a feature I've ever felt
> the need for really.  And there are lots of cool features I could
> think of I'd want before this.

What I'm more interested in the possibility of rearranging the physical
order of columns at the CREATE TABLE stage to optimise access to
various fields (move fixed width fields to front) and to minimise cost
of padding of said fields.

I imagine this could provide useful savings on wide tables and
multicolumn indexes, but you have to decouple logical and physical
ordering to do it.

But this is a thoroughly dead horse, lets not beat it up again.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] backend process terminates

[Martijn van Oosterhout]

On Wed, Aug 08, 2007 at 08:50:41AM -0400, Geoffrey wrote:
> Correct on both counts.  Many of the routines are wrapper routines used 
> to assist in code portability.

That ok in programs, but shared libraries need to be careful not to use
names likely to be used by programs that use them. FWIW, this document
has lots of information about ELF shared libraries.

http://people.redhat.com/drepper/dsohowto.pdf 

There's a lot of technical stuff that you can skip, but there is a lot
of info about scopes and how they are resolved, common problems and how
to fix them.

Have a nice,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Trigger not working as expected, first row gets a null value

[Martijn van Oosterhout]

On Wed, Aug 08, 2007 at 03:20:00PM -0700, novnov wrote:
> 
> I know that would be best but it'd be a major undertaking to try to repro
> this situation. I was hoping for some hints, 'it sounds like xyz', because
> I'm pretty sure I'm just tripping over a commonplace issue.

It doesn't have to be repoducable, but the definition of the tables
involves + the code of the trigger would help. I read your description
three times and still couldn't quite work out exactly what the problem
was or what you were expecting to happen...

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Trigger not working as expected, first row gets a null value

[novnov]

I know that would be best but it'd be a major undertaking to try to repro
this situation. I was hoping for some hints, 'it sounds like xyz', because
I'm pretty sure I'm just tripping over a commonplace issue.


Rodrigo De León-2 wrote:
> 
> On Aug 8, 3:20 pm, [EMAIL PROTECTED] (novnov) wrote:
>> ... I also don't understand ...
> 
> DDL + sample data, please...
> 
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org/
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Trigger-not-working-as-expected%2C-first-row-gets-a-null-value-tf4238812.html#a12062974
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Having trouble building 64-bit pgsql 7.4.17 on HPUX ia64

[vamseelist]

Why don't you use latest version of postgresql?

On 8/8/07, Decibel! <[EMAIL PROTECTED]> wrote:
>
> On Tue, Aug 07, 2007 at 06:50:14PM +0530, Rajaram J wrote:
> > Hi
> >
> > I'm having trouble building 64-bit pgsql 7.4.17 on the latest release of
> HP-UX 11.23 on ia64.
>
> I don't believe that's supported. There was just discussion about this,
> but I can't find it in the archive right now...
> --
> Decibel!, aka Jim Nasby[EMAIL PROTECTED]
> EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)
>
>

Re: [GENERAL] Automation using postgres

[Decibel!]

On Wed, Aug 08, 2007 at 11:48:28AM -0400, Jasbinder Singh Bali wrote:
> Hi,
> 
> I my system, I am using postgres triggers to launch some unix tools and thus
> postgres not only serves the purpose of data storage but also works as an
> engine to automate the whole system. (this is about my system, talking on a
> broader level )
> 
> I just wanted to know if there's any related work in this area so that I can
> compare my system with already existing systems related to it.

I think that's pretty rare. You should write something up about what
you're doing and post it, it could be useful info.
-- 
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)


pgp46sQLvSknV.pgp
Description: PGP signature

Re: [GENERAL] Interpreting statistics collector output

[Decibel!]

On Wed, Aug 08, 2007 at 11:01:13AM -0400, Steve Madsen wrote:
> Can anyone provide a brief overview of how to go about interpreting  
> the information generated by the statistics collector?  I've looked  
> around and can't find old mailing list messages or anything in the  
> manual beyond the basics of how to query the statistics.
> 
> Cache hit rates are easy to compute, but is this useful beyond  
> knowing if Postgres has enough buffers?
> 
> Can anything useful be gleaned from looking at idx_scan /  
> idx_tup_read / idx_tup_fetch?

Yes, that will give you information about how often an index is being
used. If you see indexes where idx_scan is a small number, that's an
indication that that index isn't being used for queries and could
potentially be dropped.

Something else I like to look at is pg_stat_all_tables seq_scan and
seq_tup_read. If seq_scan is a large number and seq_tup_read/seq_scan is
also large, that indicates that you could use an index on that table.
-- 
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)


pgp35f1Zcp38S.pgp
Description: PGP signature

Re: [GENERAL] Data Mart with Postgres

[Decibel!]

On Wed, Aug 08, 2007 at 08:56:47AM -0300, Andr? Volpato wrote:
> 
> Hello everybody,
> I?m working with a small project to a client, using Postgres to
> store data in a dimensional model, fact-oriented, e.g., a Datamart.
> At this time, all I have is a populated database, with the "star
> schemma" common relations (PK?s / FK?s).
> Below is a list of the main goals of this project :
> 1. Front-end app (PHP5)
> 2. Transactional database for this app (Postgres)
> 3. Datamart in Postgres (described above)
> 4. ROLAP server that supports Postgres (Java - Mondrian)
> 5. Front-end app to manage querys to the ROLAP server (JSP - JPivot)
> Users will have web access to (1), and will be enable to create
> views using (5).
> It seems like I?m trying to reinvent the wheel, but the point here
> is that the client can?t afford to use proprietary BI solutions, nor
> proprietary OS.
> What I have read all across the internet is that Postgres does not
> support this kind of application, wich demands materialyzed views,
> built-in bitmap index, and so on. In the open-source world, I find
> those missing features with Mondrian/JPivot.
> Does anyone has ever used this structure before ? At least Mondrian
> and JPivot ?

I haven't but it's certainly possible to build a datamart without bitmap
indexes or mviews, it's just a question of performance. MViews you can
build yourself easily enough; as for bitmap indexes, IIRC you can get
those in Bizgres.
-- 
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)


pgpiSVi0CuRMG.pgp
Description: PGP signature

Re: [GENERAL] Reordering columns, will this ever be simple?

[Decibel!]

On Tue, Aug 07, 2007 at 02:28:20PM -0500, Tony Caduto wrote:
> Gregory Stark wrote:
> >"novnov" <[EMAIL PROTECTED]> writes:
> >
> >  
> >>Is there any plan to add such a capability to postgres? 
> >>
> >
> >It's been talked about. I wouldn't be surprised to see it in 8.4 but 
> >nobody's
> >said they'll be doing it yet and there are a lot of other more exciting 
> >ideas
> >too.
> >
> >  
> From a admin tool developers perspective the ability to reorder columns 
> without manually copying to a new table and all that is pretty exiting :-)

Patches welcome. :)

BTW, this is much more likely to happen if we divorce presentation order
from actual storage order, something that there is some desire to do
because it would allow the engine to automagically store things in the
optimal ordering from an alignment standpoint.
-- 
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)


pgpEp5PM6K98S.pgp
Description: PGP signature

Re: [GENERAL] Having trouble building 64-bit pgsql 7.4.17 on HPUX ia64

[Decibel!]

On Tue, Aug 07, 2007 at 06:50:14PM +0530, Rajaram J wrote:
> Hi
> 
> I'm having trouble building 64-bit pgsql 7.4.17 on the latest release of 
> HP-UX 11.23 on ia64.

I don't believe that's supported. There was just discussion about this,
but I can't find it in the archive right now...
-- 
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)


pgpD4hJirhf9J.pgp
Description: PGP signature

Re: [GENERAL] Trigger not working as expected, first row gets a null value

[Rodrigo De León]

On Aug 8, 3:20 pm, [EMAIL PROTECTED] (novnov) wrote:
> ... I also don't understand ...

DDL + sample data, please...


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL and cluster

[Magnus Hagander]

Probably the easiest way is to unregister the server using pg_ctl
unregister, and then re-register it with the other data directory.

The other option is to edit the registry under Services and change the
commandline used to start the PostgreSQL service. Note that you will
have to reboot your server if you go with this method.

Normally, you'd point out the cluster directory when you originally
install PostgreSQL and the installer will take care of it.

//Magnus

[EMAIL PROTECTED] wrote:
> Thanks, setting it up as generic service make the clustering procedure to
> work fine, but I'm still unable to relocate the data directory. We have
> copied the DATA directory in the right path, but which variables I must
> change? I've changed the variable about the path (now I'm not in the office
> and don't remember the name) in the file PostgreSQL.conf, but nothing is
> changed.
> 
> Luca
>   
> - Original Message 
>   Da: Magnus Hagander <[EMAIL PROTECTED]>
>   To: 
>   Cc: pgsql-general@postgresql.org
>   Oggetto: Re: [GENERAL] PostgreSQL and cluster
>   Data: 08/08/07 15:41
>   
>   > 
>>
>> On Wed, Aug 08, 2007 at 01:03:54PM +0200, [EMAIL PROTECTED] wrote:
>>> Hi.
>>> Anybody know if is possible to install PostgreSQL in cluster mode on a
>>> cluster formed by two windows server 2003 cluster?
>> Yes. Set it up as a generic service, and make sure you store the data
>> directory (and any other tablespaces) on disks that the service depends
> on.
>> //Magnus
>>
>> ---(end of broadcast)---
>> TIP 2: Don't 'kill -9' the postmaster
>>
>>
>>  
>  --
>  Email.it, the professional e-mail, gratis per te: http://www.email.it/f
>  
>  Sponsor:
>  Non perderti nella giungla di facili promesse, Logos ti da credito sempre!
> 
>  Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6559&d=20070808


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

Re: [GENERAL] Dump all objects under a specific schema

[Michael Glaesemann]

On Aug 8, 2007, at 14:18 , Emi Lu wrote:

Is there a way that I can only dump all objects under a specific  
schema?


Have you checked the pg_dump documentation?

http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html

In particular, the -n flag might interest you.

Michael Glaesemann
grzm seespotcode net



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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Dump all objects under a specific schema

[Aurynn Shaw]

Hi;

Hello List,

Is there a way that I can only dump all objects under a specific  
schema?


I'd like to dump all tables, views' definition and data under a  
specific schema.



pg_dump --help says that you're looking for the -n flag, so,

pg_dump -n  -f output.sql

Hope that helps,

Aurynn Shaw

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

[EMAIL PROTECTED]



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] Trigger not working as expected, first row gets a null value

[novnov]

I have a trigger function that updates a field in the update/insert table
which uses a not so simple sql statement to find the value to update with. I
am not getting the results I expect. I expect I'm falling into some classic
trigger gotcha.

The table being updated is for 'bids'; 'bids has a fkey to the parent 'item'
table.

The first time a 'bid' row is added for a particular 'item', the trigger
returns null.

Any subsequent 'bids' on an 'item' find that the trigger has been invoked
and a correct calc applied for the new rows.

If value the trigger should apply is hard coded, all table updates get the
hard coded value. So there seems to be some kind of timing issue when the
complex sql gets involved. But, why does it work after the first row is
added for the 'item'?

If I change the trigger to be invoked after insert/update, a '0' value is
always inserted...I also don't understand that.
-- 
View this message in context: 
http://www.nabble.com/Trigger-not-working-as-expected%2C-first-row-gets-a-null-value-tf4238812.html#a12061101
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Dump all objects under a specific schema

[Emi Lu]

Hello List,

Is there a way that I can only dump all objects under a specific schema?

I'd like to dump all tables, views' definition and data under a specific 
schema.



Thank you!

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

Re: [GENERAL] tsearch2: plainto_tsquery() with OR?

[cluster]

Does anyone know where I can request an OR-version of plainto_tsquery()?

I don't understand why it doesn't exist already: In most cases, when 
using user entered keywords to search for, there should be returned some 
 rows even though not ALL keywords are matched.


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

Re: [GENERAL] Running a query from the OS CLI

[Gauthier, Dave]

Yes, that works. 

The actual sql executes a stored function that returns a set of records.
Output to the CLI looks great !!!

Thanks to all !!!

-dave 

-Original Message-
From: Michael Glaesemann [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 08, 2007 2:43 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Running a query from the OS CLI


On Aug 8, 2007, at 13:13 , Gauthier, Dave wrote:

> If I have a DB called "foo"

psql --dbname "foo"

>
> ...and...
>
> I want to run "select name from table_a where name like 'john%'"

psql --dbname "foo" -c "select name from table_a where name like 'john 
%'"

>
> ...and...
>
> I want no table header "NAME" in the output

psql --dbname "foo" -c "select name from table_a where name like 'john 
%'" -t

>
> ...and...
>
> I want to do this as a one-liner from the linux command line

check.

>
> ...and...
>
> I don't want to have to deal with intermediate files or home-grown
> programs...

Does that work?

Michael Glaesemann
grzm seespotcode net

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Running a query from the OS CLI

[Steve Atkins]

On Aug 8, 2007, at 11:13 AM, Gauthier, Dave wrote:


If I have a DB called “foo”

...and...

I want to run “select name from table_a where name like ‘john%’”

...and...

I want no table header “NAME” in the output

...and...

I want to do this as a one-liner from the linux command line

...and...

I don’t want to have to deal with intermediate files or home-grown  
programs...

Something like this:

psql -A -q  -t -d foo -c “select name from table_a where name like  
‘john%’”


You may need to use -U to set a user, and there are a bunch of other
useful flags to set the output format. There are also flags and  
environment

variables you can set to set the host and port to connect to.

Depending on how your access control permissions are setup you may
need to get a password to psql, typically by using a ~/.pgpass file.  
Check

the psql man page and the main postgresql docs for the gory details.

Cheers,
  Steve

Re: [GENERAL] Running a query from the OS CLI

[Michael Glaesemann]

On Aug 8, 2007, at 13:13 , Gauthier, Dave wrote:


If I have a DB called "foo"


psql --dbname "foo"



...and...

I want to run "select name from table_a where name like 'john%'"


psql --dbname "foo" -c "select name from table_a where name like 'john 
%'"




...and...

I want no table header "NAME" in the output


psql --dbname "foo" -c "select name from table_a where name like 'john 
%'" -t




...and...

I want to do this as a one-liner from the linux command line


check.



...and...

I don't want to have to deal with intermediate files or home-grown
programs...


Does that work?

Michael Glaesemann
grzm seespotcode net



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Running a query from the OS CLI

[Dann Corbit]

See:

http://www.postgresql.org/docs/8.2/interactive/app-psql.html

 



From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gauthier, Dave
Sent: Wednesday, August 08, 2007 11:14 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Running a query from the OS CLI

 

If I have a DB called "foo" 
>>

-d dbname
--dbname dbname 

Specifies the name of the database to connect to. This is equivalent to
specifying dbname as the first non-option argument on the command line. 

<< 

...and...

I want to run "select name from table_a where name like 'john%'"

>> 

-c command
--command command 

Specifies that psql is to execute one command string, command, and then
exit. This is useful in shell scripts. 

command must be either a command string that is completely parsable by
the server (i.e., it contains no psql specific features), or a single
backslash command. Thus you cannot mix SQL and psql meta-commands with
this option. To achieve that, you could pipe the string into psql, like
this: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator
meta-command.) 

If the command string contains multiple SQL commands, they are processed
in a single transaction, unless there are explicit BEGIN/COMMIT commands
included in the string to divide it into multiple transactions. This is
different from the behavior when the same string is fed to psql's
standard input. 

<< 

 

...and...

I want no table header "NAME" in the output

>> 

-t
--tuples-only 

Turn off printing of column names and result row count footers, etc.
This is equivalent to the \t command. 

<< 

 

...and...

I want to do this as a one-liner from the linux command line

...and...

I don't want to have to deal with intermediate files or home-grown
programs...

 

Is this possible?

>> 

Read The Fine Manual.

<< 

 

 

 

Re: [GENERAL] Dealing with tsvector in fuctions for data split

[Oleg Bartunov]

On Wed, 8 Aug 2007, Francisco Reyes wrote:


Francisco Reyes writes:


ERROR:  operator does not exist: text || tsvector


what'd you expect from this operation ? In 8.2 you can cast tsvector
to text like this:

=# select textin( tsvector_out( strip( to_tsvector('1 b c'::text || 'some 
text'::text;
   ?column?
--
 '1' 'b' 'c'some text

You should think about removing positional information from tsvector
using strip() function.


In CVS HEAD standard CAST should works.

postgres=# select cast( 'a b c'::tsvector AS text);
text
-
'a' 'b' 'c'




Also, it'd be worth to show simplified version of your function, which 
demonstrates your problem.



I did include that.


The function looks like:
AS $$
DECLARE
  v_sql TEXT;
BEGIN
  v_sql := 'INSERT INTO messagecatalog_'|| 
to_char(NEW.timestampfield,'')||

  '(field1, field2) values ('
||New.field1||','||New.field2||')
')';
  EXECUTE v_sql;
  RETURN NULL;
END
$$;


In the code above field1 is text and field2 is tsvector.



Any suggestions?
Anyone else has dealt with tsvectors in a partition environment?
If sow how did you get the split function/rule to insert into the child 
table? 
---(end of broadcast)---

TIP 9: In versions below 8.0, the planner will ignore your desire to
 choose an index scan if your joining column's datatypes do not
 match



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Running a query from the OS CLI

[Gauthier, Dave]

If I have a DB called "foo" 

...and...

I want to run "select name from table_a where name like 'john%'"

...and...

I want no table header "NAME" in the output

...and...

I want to do this as a one-liner from the linux command line

...and...

I don't want to have to deal with intermediate files or home-grown
programs...

 

Is this possible?

 

 

 

 

Re: [GENERAL] PostgreSQL and cluster

[luca . ciciriello]

Thanks, setting it up as generic service make the clustering procedure to
work fine, but I'm still unable to relocate the data directory. We have
copied the DATA directory in the right path, but which variables I must
change? I've changed the variable about the path (now I'm not in the office
and don't remember the name) in the file PostgreSQL.conf, but nothing is
changed.

Luca

- Original Message 
Da: Magnus Hagander <[EMAIL PROTECTED]>
To: 
Cc: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] PostgreSQL and cluster
Data: 08/08/07 15:41

> 
> 
> 
> On Wed, Aug 08, 2007 at 01:03:54PM +0200, [EMAIL PROTECTED] wrote:
> > Hi.
> > Anybody know if is possible to install PostgreSQL in cluster mode on a
> > cluster formed by two windows server 2003 cluster?
> 
> Yes. Set it up as a generic service, and make sure you store the data
> directory (and any other tablespaces) on disks that the service depends
on.
> 
> //Magnus
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 
> 
>  
 --
 Email.it, the professional e-mail, gratis per te: http://www.email.it/f
 
 Sponsor:
 Non perderti nella giungla di facili promesse, Logos ti da credito sempre!

 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6559&d=20070808



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] import content of XLS file into PostgreSQL

[Ivan Zolotukhin]

Hello,

On 8/8/07, Roberto Mello <[EMAIL PROTECTED]> wrote:
> On 8/8/07, Ivan Zolotukhin <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > One more way to do it with mouse clicking only is OpenOffice. Get OO
> > and install PostgreSQL driver into OpenOffice Database application,
> > then you'll be able to import/export spreadsheets to and from database
> > tables and work with DB tables just like they are spreadsheets.
>
> That should be the easiest way if you're not programming-inclined. I
> don't know if others have suggested it, but you could export the XLS
> to csv (comma-separated values) and use a csv-parsing module in a
> programming language (Python has one) to grab only the values you want
> and import into PostgreSQL.

Sure. But if you use XLS one can suspect that you're not
programming-inclined :) But seriously, OO Database is a nice thing
because you'd better move all this documents that many people prepare
in spreadsheets right inside PostgreSQL. Your xls users won't even
notice the change while their data will be being stored in the DB
directly without any files.

Regards,
 Ivan

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Reordering columns, will this ever be simple?

[Scott Marlowe]

On 8/8/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> On Tue, Aug 07, 2007 at 08:15:19PM +0100, Gregory Stark wrote:
> > "novnov" <[EMAIL PROTECTED]> writes:
> >
> > > Is there any plan to add such a capability to postgres?
> >
> > It's been talked about. I wouldn't be surprised to see it in 8.4 but 
> > nobody's
> > said they'll be doing it yet and there are a lot of other more exciting 
> > ideas
> > too.
>
> Doubt it, patches to implement this have been submitted and rejected in
> the past. I don't see any reason why 8.4 would be any different.

If the danger of implementing this is some subtle bug that eats my
data, I'd just as soon do without.  It's not a feature I've ever felt
the need for really.  And there are lots of cool features I could
think of I'd want before this.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Take your postgresSql on the road, and live to tell of it.

[Scott Marlowe]

On 8/7/07, Owen Hartnett <[EMAIL PROTECTED]> wrote:
> At 2:15 PM -0700 8/7/07, Ben wrote:
> >How many users do you have? Have you considered giving each user a
> >schema in which to make their changes? It sounds like you don't
> >really have a multi-master replication issue, which makes things
> >easier.
>
> Maybe I'm not understanding the strategy, but I don't see what this
> buys me, as I have to end up with a single database schema that has
> incorporated all the changes.  If I can "record" all the SQL a user
> does from the checkpoint on, then I can "psql <" it in to the main
> database.  Once I've combined their data into the database that sits
> on the server, I don't need their database copies anymore.

I'm not sure how you're planning to do this.  PostgreSQL doesn't use
SQL statements for Point in Time Recovery, it uses WAL logs applied
against a database that's an exact physical copy of the database at a
previous time.  Are you going to write your own application that will
let you save each SQL statement before it's applied to the user's
local database?

And if so, are you then going to have an individual database for each
user?  That might work.

My way isn't all that much harder to do.  It just lets you store all
the data in one database and share it out with all the users.

So it really depends on what you want in the end.  With one database,
it would be much easier to run a query across all your data at once.
With individual databases you have very strong isolation between the
data sets.

Either way would work, each has its own advantages and disadvantages.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] array_to_set functions

[Guy Fraser]

On Tue, 2007-08-07 at 17:46 -0500, Decibel! wrote:
> On Sun, Aug 05, 2007 at 08:18:08PM +0530, Merlin Moncure wrote:
> > On 8/3/07, Guy Fraser <[EMAIL PROTECTED]> wrote:
> > > On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote:
> > > > On 8/1/07, Decibel! <[EMAIL PROTECTED]> wrote:
> > > > > David Fetter and I just came up with these, perhaps others will find
> > > > > them useful:
> > > > >
> > > > > CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF 
> > > > > anyelement LANGUAGE SQL AS $$
> > > > > SELECT $1[i] from generate_series(array_lower($1, $2), 
> > > > > array_upper($1, $2)) i
> > > > > $$;
> > > > > CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF 
> > > > > anyelement LANGUAGE SQL AS $$
> > > > > SELECT array_to_set($1, 1)
> > > > > $$;
> > > >
> > > > very nice, although IMO there is a strong justification for these
> > > > functions to be in core and written in C for efficiency (along with
> > > > array_accum, which I have hand burn from copying and pasting out of
> > > > the documentation).
> > > >
> > > > merlin
> > > >
> > > Excellent timing guys. :^)
> > >
> > > I was trying to build a function to list the items of an array, but
> > > ran into problems and was going to post what I had been working on.
> > >
> > > Your functions work great.
> > >
> > > In case you don't have the function to generate an array from a set
> > > here is one I have been using :
> > >
> > >
> > > CREATE AGGREGATE array_accum (
> > > BASETYPE = anyelement,
> > > SFUNC = array_append,
> > > STYPE = anyarray,
> > > INITCOND = '{}'
> > > );
> > 
> > I think that's what just about everyone uses.  Unfortunately the
> > reverse of the function (array_to_set above) AFAIK does not map
> > directly to the C array API.
> 
> Oh, cool, hadn't thought about using an aggregate to do this. That's
> probably faster than what I came up with.
You are welcome. I am glad someone appreciated my gesture.



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

[GENERAL] Automation using postgres

[Jasbinder Singh Bali]

Hi,

I my system, I am using postgres triggers to launch some unix tools and thus
postgres not only serves the purpose of data storage but also works as an
engine to automate the whole system. (this is about my system, talking on a
broader level )

I just wanted to know if there's any related work in this area so that I can
compare my system with already existing systems related to it.


Thanks,
Jas

[GENERAL] Interpreting statistics collector output

[Steve Madsen]

Can anyone provide a brief overview of how to go about interpreting  
the information generated by the statistics collector?  I've looked  
around and can't find old mailing list messages or anything in the  
manual beyond the basics of how to query the statistics.


Cache hit rates are easy to compute, but is this useful beyond  
knowing if Postgres has enough buffers?


Can anything useful be gleaned from looking at idx_scan /  
idx_tup_read / idx_tup_fetch?


--
Steve Madsen <[EMAIL PROTECTED]>
Light Year Software, LLC  http://lightyearsoftware.com
ZingLists: Stay organized, and share lists online.  http://zinglists.com



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Dealing with tsvector in fuctions for data split

[Francisco Reyes]

Francisco Reyes writes:


ERROR:  operator does not exist: text || tsvector

Also, it'd be worth to show simplified version of your function, which 
demonstrates your problem.



I did include that.


The function looks like:
AS $$
DECLARE
  v_sql TEXT;
BEGIN
  v_sql := 'INSERT INTO messagecatalog_'|| 
to_char(NEW.timestampfield,'')||

  '(field1, field2) values ('
||New.field1||','||New.field2||')
')';
  EXECUTE v_sql;
  RETURN NULL;
END
$$;


In the code above field1 is text and field2 is tsvector.



Any suggestions?
Anyone else has dealt with tsvectors in a partition environment?
If sow how did you get the split function/rule to insert into the child 
table? 


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Modified FIFO queue and insert rule

[Leif B. Kristensen]

On Wednesday 8. August 2007 15:12, Alban Hertroys wrote:
>Leif B. Kristensen wrote:
>> CREATE RULE placelimit AS
>> ON INSERT TO recent_places DO ALSO
>> DELETE FROM recent_places
>> WHERE
>> -- this clause doesn't work
>> -- (place_fk = NEW.place_fk AND id <> NEW.id) OR
>> id NOT IN (SELECT id FROM recent_places ORDER BY id DESC LIMIT
>> 10);
>>
>> When I try to use the commented clause above, no records are written
>> to the table at all! Why?
>
>Do you use nextval() for that id?

I use :

INSERT INTO recent_places (place_fk) VALUES ($place);

And that's the functional equivalent of using nextval() I guess.

>In that case I think you immediately delete the record after inserting
>it, as nextval gets called again in the delete statement, and thus id
> <> NEW.id.

Yeah, that sounds reasonable. 

>You should probably use a trigger (a before one maybe) instead of a
> rule.

I might consider that. But I figured that it should be trivial to modify 
Greg's example rule.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Modified FIFO queue and insert rule

[Alban Hertroys]

Leif B. Kristensen wrote:
> CREATE RULE placelimit AS
> ON INSERT TO recent_places DO ALSO
> DELETE FROM recent_places
> WHERE
> -- this clause doesn't work
> -- (place_fk = NEW.place_fk AND id <> NEW.id) OR
> id NOT IN (SELECT id FROM recent_places ORDER BY id DESC LIMIT 10);
> 
> When I try to use the commented clause above, no records are written to 
> the table at all! Why?

Do you use nextval() for that id?
In that case I think you immediately delete the record after inserting
it, as nextval gets called again in the delete statement, and thus id <>
NEW.id.

You should probably use a trigger (a before one maybe) instead of a rule.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

Re: [GENERAL] List tables in reverse dependancy order

[Pavel Stehule]

Hello,

five years ago I used

CREATE OR REPLACE FUNCTION list_user_tables_sort_depend
  (owner VARCHAR, revers BOOLEAN) RETURNS SETOF VARCHAR AS '
DECLARE tabulky VARCHAR[]; i INTEGER; opakovat BOOLEAN = ''t'';
  pom VARCHAR; exportovano VARCHAR[] = ''{}''; r RECORD;
mohu_exportovat BOOLEAN;

BEGIN SELECT ARRAY(SELECT tablename FROM pg_tables WHERE tableowner =
owner) INTO tabulky;
  WHILE opakovat LOOP
opakovat := ''f'';
FOR i IN array_lower(tabulky,1) .. array_upper(tabulky,1) LOOP
  IF tabulky[i] <>  THEN
mohu_exportovat := ''t'';
FOR r IN SELECT t.relname AS z, x.relname AS nz FROM
pg_catalog.pg_constraint d
  INNER JOIN pg_catalog.pg_class t on t.oid = d.conrelid
  INNER JOIN pg_catalog.pg_class x on x.oid = d.confrelid
WHERE d.contype = ''f'' AND t.relname = tabulky[i] LOOP
  IF NOT r.nz = ANY(exportovano) THEN
mohu_exportovat := ''f'';
  END IF;
END LOOP;
IF mohu_exportovat THEN
  pom := tabulky[i];
  exportovano := exportovano || tabulky[i];
  opakovat := ''t''; tabulky[i] := ;
END IF;
  END IF;
END LOOP;
  END LOOP;
  IF revers THEN
FOR i IN REVERSE array_upper(exportovano,1) ..
array_lower(exportovano,1) LOOP
  RETURN NEXT exportovano[i];
END LOOP;
  ELSE
FOR i IN array_lower(exportovano,1) .. array_upper(exportovano,1) LOOP
  RETURN NEXT exportovano[i];
END LOOP;
  END IF;
  RETURN;
END;
' LANGUAGE plpgsql;

I am sorry for czech variable names

Regards
Pavel Stehule


2007/8/2, Gregory Williamson <[EMAIL PROTECTED]>:
>
>
>
> I am not sure if this is the appropriate list -- please point me at the
> correct one if not.
>
>  I'm trying to create a procedure that would let me retrieve a list of
> tables and views in a database that will be used to control the order in
> which lookup data is created/loaded. So, much simplified, if table
> references table B, which in turn references table A, we want output to list
> table A, B and C in that order.
>
>  I'm sure that this exists -- the pg_dump command must use some similar
> algorithm to decide in which order to load tables, but I can't see to puzzle
> this out.
>
>  Can anyone provide me with some clues, appropriate RTFM references, etc. ?
>
>  TIA,
>
>  Greg Williamson
>  Senior DBA
>  GlobeXplorer LLC, a DigitalGlobe company
>
>  Confidentiality Notice: This e-mail message, including any attachments, is
> for the sole use of the intended recipient(s) and may contain confidential
> and privileged information and must be protected in accordance with those
> provisions. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the sender
> by reply e-mail and destroy all copies of the original message.
>
>  (My corporate masters made me say this.)
>
>

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

   http://www.postgresql.org/docs/faq

Re: [GENERAL] backend process terminates

[Geoffrey]

Martijn van Oosterhout wrote:

On Tue, Aug 07, 2007 at 07:46:45AM -0400, Geoffrey wrote:
I don't know all the idiosyncrasies of how this works, so bear with me 
on this.  The developer at the vendor indicated that he's narrowed down 
the problem to a set of wrapper routines in their code.  They are named 
OpenFile(), CloseFile() and ReadFile();  He inquired as to whether there 
might be routines in the Postgresql code with the same names that might 
be causing a conflict.  Sure enough, I searched the Postgresql source 
code and found routines with the same names.  I don't see how this could 
pose a problem though, as it is my understanding that the compiler will 
properly address this issue.


Yes, this could cause a problem. In general, when loading a library,
any external references are first resolved against the main
executable, then already loaded libraries, then the library being
loaded. It's all in the ELF standard, if you're interested.


I will be checking them out.  My compiler knowledge is a bit rusty, 
circa SVR4... ;)



As for solutions:
1. In your third party library, have the library built in such a way
that the symbols are explicitly bound to the internal library version.
There are various methods for dealing with that, it all depends on the
toolchain used to build it. I suppose this product is actually several
libraries that call eachother? Namespace would help here.


Correct on both counts.  Many of the routines are wrapper routines used 
to assist in code portability.



2. Make sure that any externally visible symbols in libraries are
always prefixed by a tag, like libpq does (almost all symbols are pq*).

Running "nm -D" over the main postgres executable and your libraries
should give you an idea of the scope of the problem.

Hope this helps,


It appears that the common routine names were causing the problem.  We 
are currently testing new versions of these libraries where they have 
renamed the common routines with unique names.


Thanks for the insights.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Reordering columns, will this ever be simple?

[Martijn van Oosterhout]

On Tue, Aug 07, 2007 at 08:15:19PM +0100, Gregory Stark wrote:
> "novnov" <[EMAIL PROTECTED]> writes:
> 
> > Is there any plan to add such a capability to postgres? 
> 
> It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's
> said they'll be doing it yet and there are a lot of other more exciting ideas
> too.

Doubt it, patches to implement this have been submitted and rejected in
the past. I don't see any reason why 8.4 would be any different.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] Modified FIFO queue and insert rule

[Leif B. Kristensen]

I found an excellent description of how to implement a fifo que in 
PostgreSQL at Greg Mullane's blog:

http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html

I have used the 'rule' approach to implement a queue that generates a 
quick-list of last selected places. The only modification I need is 
that if an item already exists in the list, a new reference should be 
written to the top, and the old reference should be deleted. But it 
seems like I'm in over my head here: 

-- short FIFO list of recently selected places
CREATE TABLE recent_places (
id SERIAL PRIMARY KEY,
place_fk INTEGER REFERENCES places ON DELETE CASCADE
);

CREATE RULE placelimit AS
ON INSERT TO recent_places DO ALSO
DELETE FROM recent_places
WHERE
-- this clause doesn't work
-- (place_fk = NEW.place_fk AND id <> NEW.id) OR
id NOT IN (SELECT id FROM recent_places ORDER BY id DESC LIMIT 10);

When I try to use the commented clause above, no records are written to 
the table at all! Why?
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Data Mart with Postgres

[André Volpato]

Hello
everybody,


I´m working with a small project to a client, using Postgres to store
data in a dimensional model, fact-oriented, e.g., a Datamart.


At this time, all I have is a populated database, with the "star
schemma" common relations (PK´s / FK´s).


Below is a list of the main goals of this project :

1. Front-end app (PHP5)

2. Transactional database for this app (Postgres)

3. Datamart in Postgres (described above)

4. ROLAP server that supports Postgres (Java - Mondrian)

5. Front-end app to manage querys to the ROLAP server (JSP - JPivot)


Users will have web access to (1), and will be enable to create views
using (5).


It seems like I´m trying to reinvent the wheel, but the point here is
that the client can´t afford to use proprietary BI solutions, nor
proprietary OS.

What I have read all across the internet is that Postgres does not
support this kind of application, wich demands materialyzed views,
built-in bitmap index, and so on. In the open-source world, I find
those missing features with Mondrian/JPivot.


Does anyone has ever used this structure before ? At least Mondrian and
JPivot ?


-- 
[]´s,

André Volpato

Re: [GENERAL] PostgreSQL and cluster

[Magnus Hagander]

On Wed, Aug 08, 2007 at 01:03:54PM +0200, [EMAIL PROTECTED] wrote:
> Hi.
> Anybody know if is possible to install PostgreSQL in cluster mode on a
> cluster formed by two windows server 2003 cluster?

Yes. Set it up as a generic service, and make sure you store the data
directory (and any other tablespaces) on disks that the service depends on.

//Magnus

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

[GENERAL] PostgreSQL and cluster

[luca . ciciriello]

Hi.
Anybody know if is possible to install PostgreSQL in cluster mode on a
cluster formed by two windows server 2003 cluster?

Luca 
 --
 Email.it, the professional e-mail, gratis per te: http://www.email.it/f
 
 Sponsor:
 Vivi i MONDIALI di ATLETICA di TOKYO da protagonista. Compra on line i
prodotti ufficiali della Nazionale Italiana FIDAL. Vestiti di azzurro
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6907&d=20070808

Re: [GENERAL] import content of XLS file into PostgreSQL

[Ivan Zolotukhin]

Hello,

One more way to do it with mouse clicking only is OpenOffice. Get OO
and install PostgreSQL driver into OpenOffice Database application,
then you'll be able to import/export spreadsheets to and from database
tables and work with DB tables just like they are spreadsheets.

Regards,
 Ivan

On 8/6/07, Alain Roger <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I would like to know what should i do to import the content (not all
> columns) of a XLS file into pgsql.
> is there something special to do ?
>
> thanks a lot,
>
> --
> Alain
> 
> Windows XP SP2
> PostgreSQL 8.1.4
> Apache 2.2.4
> PHP 5.2.1

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

   http://www.postgresql.org/docs/faq

[GENERAL] Installation problem

[luca . ciciriello]

Hi.
I\'m trying to install PostgreSQL 8.2.3 on WindowsXP SP2 Media Center (2005
edition). I\'m administrator on this machine, but at the end of the
installation process it fails reporting the following error: \"Unable to
start the service. You must have Administrator rights\". Then the
installation ends.
I\'ve already converted the file system from FAT32 to NTSC (solving another
PostgreSQL problem).
Any ideas to solve this problem?
Thanks in advance.
Luca 
 --
 Email.it, the professional e-mail, gratis per te: http://www.email.it/f
 
 Sponsor:
 Conto Arancio: 4,50% sino a marzo 2008
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6742&d=20070808

Re: [GENERAL] Comment for column in view - "legal" or just working?

[Harald Armin Massa]

Thanks, Dave.

< reminds me to run allways newest pgadmin, even on customer sites :)

having it used within pgadmin makes it "legal" enough for me to rely on it
within my application

Best wishes,

Harald
>
>
> You can in 1.8 - that will show a collection of columns under a view,
> and allow you to set the comment and default value for each.





-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

Re: [GENERAL] Comment for column in view - "legal" or just working?

[Dave Page]

Harald Armin Massa wrote:
> Using PostgreSQL 8.1.8 on Windows.
> 
> I have one named listedi in schema cust; that view has one column "name"
> 
> No I used
> 
> comment on cust.listedi.name  is
> 'MyDescriptive Name';
> 
> and the command suceeded. Using \d+ in psql also shows me that comment
> (also I find no option within pgadmin to view it :) )

You can in 1.8 - that will show a collection of columns under a view,
and allow you to set the comment and default value for each.

Regards, Dave

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] backend process terminates

[Martijn van Oosterhout]

On Tue, Aug 07, 2007 at 07:46:45AM -0400, Geoffrey wrote:
> I don't know all the idiosyncrasies of how this works, so bear with me 
> on this.  The developer at the vendor indicated that he's narrowed down 
> the problem to a set of wrapper routines in their code.  They are named 
> OpenFile(), CloseFile() and ReadFile();  He inquired as to whether there 
> might be routines in the Postgresql code with the same names that might 
> be causing a conflict.  Sure enough, I searched the Postgresql source 
> code and found routines with the same names.  I don't see how this could 
> pose a problem though, as it is my understanding that the compiler will 
> properly address this issue.

Yes, this could cause a problem. In general, when loading a library,
any external references are first resolved against the main
executable, then already loaded libraries, then the library being
loaded. It's all in the ELF standard, if you're interested.

As for solutions:
1. In your third party library, have the library built in such a way
that the symbols are explicitly bound to the internal library version.
There are various methods for dealing with that, it all depends on the
toolchain used to build it. I suppose this product is actually several
libraries that call eachother? Namespace would help here.

2. Make sure that any externally visible symbols in libraries are
always prefixed by a tag, like libpq does (almost all symbols are pq*).

Running "nm -D" over the main postgres executable and your libraries
should give you an idea of the scope of the problem.

Hope this helps,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] Comment for column in view - "legal" or just working?

[Harald Armin Massa]

Using PostgreSQL 8.1.8 on Windows.

I have one named listedi in schema cust; that view has one column "name"

No I used

comment on cust.listedi.name is 'MyDescriptive Name';

and the command suceeded. Using \d+ in psql also shows me that comment (also
I find no option within pgadmin to view it :) )

My question is:

- is this a legal usage of COMMENT, or could this "vanish" in later
releases?
 (within COMMENTs documentation there is only mentionedcolumn
.)


Best wishes,

Harald


-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!