Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Gaetano Mendola]

Marc G. Fournier wrote:
On Sat, 6 Nov 2004, Mike Cox wrote:
1.  I tried subscribing to comp.databases.postgresql.general through my
usenet provider thinking it was a regular big 8 group.  When it wasn't
found, I sent a request to my news provider to include it.

Most modern news readers allow for multiple news server ... just point 
yours at news.postgresql.org, and you can read from there, which has 
always been the case ...
As I already wrote, the actual postgres NG is missing some lists like: 
"www",
to complete the panorama news.us.postgresql.org have a slony list that does
not exist in the archives. I think that NG is the best way to follow
the discussion and shall be at least a complete container for them and a
complete archive mirror too.

Regards
Gaetano Mendola


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Sorting based on maximum value over several columns

[Arjen van der Meijden]

Hi Greg,
Although it doesn't really seem to be a very well-structured database 
design, I think there is a solution.

If the amount of fields is low, you can just stick a CASE in the MAX like:
SELECT ...,
MAX(
CASE WHEN value1 > value2 THEN
(CASE WHEN value1 > value3 THEN value1 ELSE value3 END)
ELSE
(CASE WHEN value2 > value3 THEN value2 ELSE value3 END)
END
) as sorter
FROM yourtable
GROUP BY ...
ORDER BY sorter
But if there are more than three values it will be a very long 
CASE-statement and you're probably better off defining a FUNCTION. I'm 
not sure whether it is possible to define a function with an unspecified 
amount of inputvalues, but you can also use the table type as input type 
and work with a table record in your function.
It might yield best performance, though, to create a C-function for this.

If there is already a "max of several fields"-function in PostgreSQL, 
than you can use that of course.

Best regards,
Arjen
On 7-11-2004 1:31, Net Virtual Mailing Lists wrote:
Hello,
Lets say I have data like this:
value1|value2|value3|value4||value(N)
--|--|--|--||
100   | 200  | 300  |  400 ||
10| 20   |  |  40  ||
  | 15   |  |  16  ||
5 |  |  |  ||
Now I want to sort these based on the maximum value of the data in each
row, so for sorting purposes I would have this:
sort

400
40
16
5
Any ideas?...  I've tried several things but none of them have given me
the result I am after
Thanks as always!
- Greg
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Gary L. Burnore]

[posted and mailed to the list]

On 6 Nov 2004 01:44:34 -0800, "Robert G" <[EMAIL PROTECTED]> wrote:

>
>Mike Cox wrote:
>> REQUEST FOR DISCUSSION (RFD)
>>  unmoderated group comp.databases.postgresql.general
>>
>> This is a formal Request For Discussion (RFD) for the creation of
>> a worldwide unmoderated Usenet newsgroup
>comp.databases.postgresql.general.
>> This is not a Call for Votes (CFV); you cannot vote at this time.
>> Procedural details are below.
>
>Hello Mike. A number of us from the mailing list have been discussing
>this on the back channel (e-mail).

Gee, what a surprise, not.  Why not discuss it on the .general list
where everyone can see it?

>At least ten people that I am aware of are not in favor of this idea. 

That's fine. How many are for it?  Did you ask?

>Your efforts are appreciated, to a point, but I do prefer if you just left 
>things well enough alone. 

But that would be the wrong thing to do.

>You took this initiative without notifying the mailing list proper. 

If you're discussing it on a 'back channel' then aren't you doing the
same thing?

>I saw that you posted to the Newsgroup side, but your posts did not reach the
>e-list.

That's the problem

>It is one-sided, and many of us prefer to keep it that way.

Ah, so you want to shove your group up USENet's ass and not take
anything in return.

>A post by Andy to the mailing list was the only news we were given of
>this.

Because it's busted and he tried to fix it.

>This talk of opening up the list to the Big Eight and making a 2-way
>gateway would have a devastating effect on the way the list currently
>functions.


Then get the gateway removed. One way causes grief.  Someone reads a
post, replies and bad stuff happens.  It's broken.  Your preference
for leaving it the same will not be acceptable.


>That would probably force most of us to subscribe to the
>digest version of the list, which is not quite as good as receiving the
>individual messages posted to the list.

Which is no where NEAR as good as moving it from E-Mail to USENet if
it's that big.  But it's not MY list so I don't care.  I do care that
you're basically shoving stuff at comp.databases.* without regard for
the effects.

>
>Your proposal would probably receive more support if you would change
>the name of the proposed group to comp.databases.postgresql, as there
>is no list or gated newsgroup with that name. 

That wouldn't fix the problem.  It also wouldn't make any sense from
the USENet point of view.  

>If you draft another RFD with this change, you would get my YES vote, and 
>probably a good number
>more than if you use one of the current names. In the current form, I
>would be inclined to vote against the proposal.

So you'll be in favor of removing the mail-to-news gateway, right?


>I believe that you probably had very good intentions when you made this
>proposal. You probably did not realize the complexity of what you were
>getting into. 

Likely.  Do you?

>Please let the mailing list function as it currently does

No.  The list as it currently funcions sends messages to USENet.  That
needs to be changed.

>- a medium volume mailing list. Consider something else. If Usenet-only
>posters find your group, gatewayed to our mailing list, they will only
>have access to the general list by Usenet. That means that all
>postgresql posts will go to the general list. The posts that are
>intended for the specialized lists will also go to the general list,
>and that will make the waters even muddier. For example, if a developer
>has something specific to post about JDBC, they would post it to your
>newsgroup, and it would be gated to the general mailing list instead of
>the JDBC-specific list.

You're getting it only from one side.  From the usenet side, they'd
rightly think to post to comp.databases.postgresql.interfaces.jdbc


>After discussing the situation with my colleagues, we went ahead and
>created an alt* group for postgresql. 

Of course you did.  What an asanine thing to do. 


>This new group will probably show up on your news server within one week.

Hahahahaha.

>The group name is alt.comp.databases.postgresql.

This is what happens when people who don't understand USENet get
together and try to do something without asking about it first.  Gee,
the same thing you said he did. Funny, eh?


>If this does not meet your requirement
>for an international newsgroup for general postgresql discussion, then
>by all means, continue your quest, but please do not use any of the
>group names assigned to any of our mailing lists.

Then remove the gateway.  The groups can be created with the correct
names which would include a comp.databases.postgresql.general that
simply would no longer be receiving emails from your clique list.

>Thank you for your efforts to facilitate discussion of a fine RDMS, and
>thanks in advance for your cooperation.

So are you going to begin discussions on removing the gateway?
-- 
[EMAIL PROTECTED] dot com  
---

Re: [GENERAL] Temporarily disable rule, is this possible?

[Stephan Szabo]

[EMAIL PROTECTED] ("Net Virtual Mailing Lists") wrote:

>CREATE OR REPLACE RULE sometable_delete ON DELETE DO delete FROM cache
>WHERE tablename='sometable';
>CREATE OR REPLACE RULE sometable_insert ON INSERT DO delete FROM cache
>WHERE tablename='sometable';
>CREATE OR REPLACE RULE sometable_update ON UPDATE DO delete FROM cache
>WHERE tablename='sometable';
>
>And what I have is set of objects which for certain queries will populate
>a serialized variable into the cache table, like this:
>
>INSERT INTO cache (tablename, cache_key, datavalue) VALUES ('sometable',
>'some_md5_hash', 'serialized_data');
>
>
>Using this method it is possible for me to just do a:
>
>SELECT datavalue FROM cache WHERE tablename='sometable' AND
>cache_key='some_md5_hash';
>
>.. and if I get an empty value for datavalue then execute the query
>normally, process it, then store the serialized data back into the cache...
>
>This all works very well for this situation, it has sped up my
>application about 20 times for 95% of the requests
>
>The problem I have is there are frequently times I need to update
>sometable and not have the rule execute, specifically when I am updating
>something in the table which does not affect aggregate results.
>
>Is there a way to cause the rules to be temporarily disabled for these
>types of queries?

Not that I know of (apart from doing a remove/add).  However, possibly
if you were to add a view which had update rules to do the update on the
real table as well as the cache changes you could then update the real table
manually and not affect the cache (although you could then not guarantee
that someone wouldn't do something that would affect the cache directly).



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

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] FTP mirror problems

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
 
 
A bad link in the ftp source directory inspired me to check
the rest of it out:
 
* Main README file needs updating. The URL is given as:
 
http:/www.postgreSQL.org
 
but should be:
 
http://www.postgresql.org/
 
The copyright notice inside it is very old. There is no explanation
of the directory structure or telling people where to find things.
 
* README.cvsup is very old, possibly incorrect
 
* README.dist-split might want to mention the new "bz" and "md5" files.
 
* We are keeping some very old versions around: 1.08, 1.09, the entire 6
series. Is there any reason for this? For that matter, is there any reason
to keep around lesser versions in a tree, especially ones that were replaced
due to security bugs? Perhaps only keep the latest few versions and put a
note in the readme to point to "archives.postgresql.org" or something?
 
At the very least, a warning that some of the versions on the site have
serious security flaws would be warranted.
 
* /dev/interfaces seems to have only an old version of libpqxx
 
* /dev/contrib only contains earthdistance - why?
 
* /projects/gborg has some very old versions of some of the projects
therein. Is there a process to update these?
 
* The directory symlinked from /win32 should have a README. Perhaps
even a top-level README.WIN32 in anticipation of a lot of Windows users.
 
* The /src directory is missing 7.2.6 and 7.3.6
 
* /src/7.3.5 points to the wrong place (missing a "v")
 
* /source/7.2.5/v7.2.5 is a bogus link
 
* The use of HISTORY file is sporadic in the releases. Particularly,
all the latest branches are missing it.
 
* Why are we still using FTP mirrors? Most open source projects seem
to be using HTTP for download mirrors these days, and I for one would
find that more convenient.
 
* Specific mirror problems.
 
I went through and tested each of the ftp mirrors that appeared on the
page today. Some problems are probably transitory (e.g. timeouts) but
if anyone closer geographically (I'm in US) could test those, it would be
appreciated. No comment next to a mirror indicated that everything looked ok.
 
ftp4.ar
ftp.au
ftp2.au
ftp3.au: incorrect home directory
ftp.at
ftp.be
ftp.ba
ftp.br: connection refused
ftp2.br
ftp3.bg
ftp3.ca
ftp4.ca
ftp.cl
ftp.co
ftp2.cr
ftp.cz
ftp2.cz
ftp2.dk
ftp.ee
ftp.fr
ftp2.fr
ftp5.fr: connection refused
ftp.de
ftp2.de
ftp3.de
ftp7.de
ftp.gr
ftp2.gr
ftp.hk
ftp.hu: timeout + connection reset by peer
ftp3.hu
ftp2.is
ftp5.id: extra (old) dirs "NT_Support_Files" and "CVSup"
ftp7.id
ftp.ie
ftp2.ie
ftp2.il
ftp.it
ftp2.it
ftp5.it: connection refused
ftp6.it
ftp.jp
ftp.kr
ftp3.kr: has a ":80" in the url; when removed, works fine
ftp.lv
ftp.nl: times out
ftp.eu (listed as Netherlands, but goes against the naming scheme)
ftp2.nl
ftp4.nl
ftp.nz
ftp.no
ftp6.pl
ftp7.pl
ftp8.pl
ftp.pt: wrong home directory; usually times out too
ftp.pr
ftp6.ro
ftp.ru
ftp2.ru: incorrect login error
ftp3.ru
ftp5.ru: times out
ftp2.sk: extra (old) dirs "NT_Support_Files", "CVSup", plus many version 
symlinks
ftp4.es: times out
ftp5.es
ftp.se
ftp2.ch
ftp.tw
ftp3.tw
ftp5.tw
ftp6.tr
ftp2.uk
ftp3.us
ftp5.us: times out
ftp8.us
ftp9.us
ftp10.us: has many bogus ".message" files throughout
ftp13.us
ftp21.us
ftp22.us
ftp23.us: incorrect login error
ftp24.us
 
 
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200411071350
-BEGIN PGP SIGNATURE-
 
iD8DBQFBjm+VvJuQZxSWSsgRAsMyAJ9ZNlOGzQ1P9Z05qg8YYJSylLqXOQCg2C7D
G/mFgh99zxos0baBjuEsgIA=
=ryNl
-END PGP SIGNATURE-



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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Visual Designer in linux?

[Eric]

I tried a lot of apps.  The one I prefer is "DbDesigner4" but I don't
see support for postgresql...  Only Oracle, mySQL...

There is support for ODBC, I don't know if that will do the job if I
install Unix-ODBC and create a postgres data source.

The point is, IF I succeed to make it work with postgres, I don't know
if I will have a non-optimized database at the end... because
DBDesigner4 won't take profit of postgres features?


On Sun, 2004-11-07 at 00:29, Alvaro Herrera Munoz wrote:
> On Sat, Nov 06, 2004 at 10:32:30PM -0500, Eric wrote:
> > Is there a visual designer (open source) in linux for database?  I would
> > like to developp my data model on the computer...
> > 
> > I see "dia" with uml library but... it won't export to SQL.
> 
> You can have UML diagrams exported to SQL with some Perl or Python program
> whose URL you can find on Dia's homepage.   dia2sql or something like
> that.  It's damn easy.  Hmm ... I think I got the URL somewhere ...
> yes, you are lucky:  http://tedia2sql.tigris.org/


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

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Marc G. Fournier]

On Sun, 7 Nov 2004, Gary L. Burnore wrote:
This talk of opening up the list to the Big Eight and making a 2-way
gateway would have a devastating effect on the way the list currently
functions.

Then get the gateway removed. One way causes grief.  Someone reads a
post, replies and bad stuff happens.  It's broken.  Your preference
for leaving it the same will not be acceptable.
Not sure where anyone got the idea that the gateway is uni-directional, 
but it has been bi-directional since the day of its inception *years* ago 
...

If you draft another RFD with this change, you would get my YES vote, and 
probably a good number
more than if you use one of the current names. In the current form, I
would be inclined to vote against the proposal.
So you'll be in favor of removing the mail-to-news gateway, right?
Which won't happen ...
Please let the mailing list function as it currently does
No.  The list as it currently funcions sends messages to USENet.  That
needs to be changed.
The list will continue to function as it always has ... it sends to 
Usenet, and receives from it ...


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Visual Designer in linux?

[Martijn van Oosterhout]

There is an ODBC driver for windows that will then talk native to the
PostgreSQL database, why not use that?

Also what features do you think something might be able to use anyway
to "optimise"?

Hope this helps,

On Sun, Nov 07, 2004 at 02:49:06PM -0500, Eric wrote:
> I tried a lot of apps.  The one I prefer is "DbDesigner4" but I don't
> see support for postgresql...  Only Oracle, mySQL...
> 
> There is support for ODBC, I don't know if that will do the job if I
> install Unix-ODBC and create a postgres data source.
> 
> The point is, IF I succeed to make it work with postgres, I don't know
> if I will have a non-optimized database at the end... because
> DBDesigner4 won't take profit of postgres features?
> 
> 
> On Sun, 2004-11-07 at 00:29, Alvaro Herrera Munoz wrote:
> > On Sat, Nov 06, 2004 at 10:32:30PM -0500, Eric wrote:
> > > Is there a visual designer (open source) in linux for database?  I would
> > > like to developp my data model on the computer...
> > > 
> > > I see "dia" with uml library but... it won't export to SQL.
> > 
> > You can have UML diagrams exported to SQL with some Perl or Python program
> > whose URL you can find on Dia's homepage.   dia2sql or something like
> > that.  It's damn easy.  Hmm ... I think I got the URL somewhere ...
> > yes, you are lucky:  http://tedia2sql.tigris.org/
> 
> 
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match

-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgppyRGWlw0eX.pgp
Description: PGP signature

Re: [GENERAL] SQL question

[Greg Stark]

"Uwe C. Schroeder" <[EMAIL PROTECTED]> writes:

> Here's a question for the SQL guru's out there, which I've been trying to 
> solve for the last couple of hours. There's got to be a solution to this, but 
> somehow I can't find it.
> 
> Tables:
> 
> table1 (
>   uid int PK,
> uname varchar(64)
> )
> 
> table2 (
>   uid int FK to table1,
> xuid int FK to table 1
> )
> 
> table3 (
>   uid int FK to table1,
>   yuid int FK to table1
> )

SELECT * 
  FROM table2 
  FULL OUTER JOIN table3 ON (table2.uid = tabletable3.uid AND xuid = yuid)
  JOIN table1 USING (uid)

Or if you find it clearer

SELECT *
  FROM table1
  JOIN (table2 FULL OUTER JOIN table 3 ON (table2.uid = tabletable3.uid AND 
xuid = yuid)) USING (uid)

You might want to have an outer join (RIGHT OUTER JOIN in the first form, LEFT
OUTER JOIN in the second) if you want to list records from table1 for which
there are no matching records in table2 or table3.

This is going to be a hard query to get to be fast though.

The other alternative would be to do a self-join of table1 to table1 and then
use subqueries to check for matching table2 or table3 entries. This would be a
lose if the relationships are relatively sparse, but if you have more tables
it might end up being a win, I don't know.

Something like

SELECT table1.*,
   (select xuid from table2 where uid = child.uid) as xuid,
   (select yuid from table3 where uid = child.uid) as yuid,
   (select zuid from table4 where uid = child.uid) as zuid,
   ...
  FROM table1 
 CROSS JOIN table1 AS child


-- 
greg


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

[GENERAL] I spoke with Marc from the postgresql mailing list.

[Mike Cox]

He wants to be in the big 8.  He would like to be under the comp.databases.*
domain and will not to move it to something like postgresql.*.  

So he seems to agree with what was my original intention, which was to make
the current groups proper members of the big 8.

I also offered to let him take over the process as he understands gatewaying
and the details better than I.  I also offered to let him create the next
RFD, and hopefully he will agree to do it if he has the time.  He is the
most connected member of the postgresql team and it would be right for him
to decide how the groups should be run.

I haven't gotten a response to my email on whether he has the time to take
over the process, but I would like to let you know that I've asked Marc to
design the solution the best way he sees fit, and if he posts a revised RFD,
that one is the one to vote on.

Also, he pointed out that for those who want to get the postgresql groups
when their usenet sever doesn't carry them, the solution would be to point
their newsreaders to news.postgresql.org.

Please, those of you on the mailing list, DO cross post to news.groups. 
This is where the news providers, admins, ect. in usenet group creation
meet.  It would help tremendously if they are involved in the process.

For those who are unsure of how big 8 newsgroups are created, visit
news.groups.  They have a Guidelines for Bigh Eight Newsgroup Creation post
annually.  It explains how it works.



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

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Mike Cox]

Andy wrote:

> "Stephan Szabo" wrote:
> 
> (politely snipped)
> 
> Hi Stephan. As Robert tried to explain, this Mike Cox character is
> proposing that only the general list become an official Big-8
> newsgroup. 

No that is not what I'm proposing.  Each group MUST go through the RFD and
CFV seperately.  I started off with the most popular group first.  After It
was done, I would have started on the rest.

>That would kill all the other lists, because all the Usenet
> posters could only post to .general, meaning that the general list
> would be overwhelmed with posts that should go to the other lists.

No.  Only the general topics would go there as the others would have gone
through RFD and CFV too.

> This
> would also significantly increase Marc's moderation workload, due to
> the increased volume of traffic and the fact that he may need to start
> redirecting posts to the appropriate lists.

Marc from his email stated that he wants to be under the big 8.  I've
emailed him back saying he can control the process from here if he has the
time.  

> 
> I see that usenetserver.com is your news server. Refresh your
> newsgroups list. You'll find a new newsgroup called
> alt.comp.databases.postgresql on your group list. Your server picked it
> up, just this morning, in fact. There isn't any traffic yet, but give
> it a month or so and that will change.

Marc also pointed out that one can go to news.postgresql.org and get all the
groups!  This is a better solution then diluting the mailing list!

> 
> Usenetserver.com also operates a free text news server. The server
> address is free-text.usenetserver.com if anyone is interested.
> Retention is pretty darn good.


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

   http://archives.postgresql.org

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Gary L. Burnore]

On Sun, 07 Nov 2004 19:26:42 GMT, Stephan Szabo <[EMAIL PROTECTED]>
wrote:

>"Robert G" <[EMAIL PROTECTED]> wrote:
>>
>>Mike Cox wrote:
>>> REQUEST FOR DISCUSSION (RFD)
>>>  unmoderated group comp.databases.postgresql.general
>>>
>>> This is a formal Request For Discussion (RFD) for the creation of
>>> a worldwide unmoderated Usenet newsgroup
>>comp.databases.postgresql.general.
>>> This is not a Call for Votes (CFV); you cannot vote at this time.
>>> Procedural details are below.
>>
>>Hello Mike. A number of us from the mailing list have been discussing
>>this on the back channel (e-mail). At least ten people that I am aware
>>of are not in favor of this idea. Your efforts are appreciated, to a
>>point, but I do prefer if you just left things well enough alone. You
>>took this initiative without notifying the mailing list proper. I saw
>>that you posted to the Newsgroup side, but your posts did not reach the
>>e-list. It is one-sided, and many of us prefer to keep it that way. A
>
>I personally haven't seen too much discussion on this subject on the mailing 
>list proper, either (perhaps because the weekend traffic tends to be low) so 
>I think it's too early to be saying things like "many of us" speaking for the
>mailing list.
>
>Besides which, AFAICT the mailing list gateway is not one-sided given that
>I seem to be able to post to my usenet server giving my subscribed email
>address and have it show up on the mailing list. 


The groups aren't listed as moderated. Anyone who wants to post is
able to. Those not on the mailing list don't go through.  That's the
problem.

>As far as I know, Marc 
>does moderator checking of messages that aren't from subscribed 
>addresses which is why we get some portion of the back-dated 
>messages.  It's possible that there might be issues with falsified 
>addresses or the potentially increased volume of messages, but I 
>haven't seen anything concrete either direction on that from the person
>it would directly affect.

Fixing the problem or removing it would be best. Again, you're only
looking at it from the mailing list point of view. Frankly, that's
rude.

>>After discussing the situation with my colleagues, we went ahead and
>>created an alt* group for postgresql. This new group will probably show
>
>Why is this any better to have done without any public discussion on the 
>mailing list than the RFD in the first place?

It wasn't. It was yet anoter dumb thing to do.

>At least the RFD would move
>the list to being properly officially connect to usenet rather than making 
>a new group that will likely not attract a large percentage of the people
>that answer the -general questions.


-- 
[EMAIL PROTECTED] dot com  
---
  How you look depends on where you go.
---
Gary L. Burnore   |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
  |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
DataBasix |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
  |  ÝÛ³ 3 4 1 4 2  ݳ޳ 6 9 0 6 9 ÝÛ³
Black Helicopter Repair Svcs Division | Official Proof of Purchase
===
  Want one?  GET one!   http://signup.databasix.com
===

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

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Gary L. Burnore]

On 7 Nov 2004 20:01:51 GMT, "Andy" <[EMAIL PROTECTED]> wrote:

>"Stephan Szabo" wrote:
>
>(politely snipped)
>
>Hi Stephan. As Robert tried to explain, this Mike Cox character 

Hi Stephen, this "Andy" character isn't using a real name or address.

>is
>proposing that only the general list become an official Big-8
>newsgroup. 

That's based on his not knowing the rules. Obviously this needs to
address the entire mess.

>That would kill all the other lists, because all the Usenet
>posters could only post to .general, meaning that the general list
>would be overwhelmed with posts that should go to the other lists. This
>would also significantly increase Marc's moderation workload, due to
>the increased volume of traffic and the fact that he may need to start
>redirecting posts to the appropriate lists.
>
>I see that usenetserver.com is your news server. Refresh your
>newsgroups list. You'll find a new newsgroup called
>alt.comp.databases.postgresql on your group list. Your server picked it
>up, just this morning, in fact. There isn't any traffic yet, but give
>it a month or so and that will change.

Doubtful.  Not everyone will pick it up. Alt.net and spam sites will.
That's not what you want.

>Usenetserver.com also operates a free text news server. The server
>address is free-text.usenetserver.com if anyone is intereste
>Retention is pretty darn good.


Define pretty darn good.

-- 
[EMAIL PROTECTED] dot com  
---
  How you look depends on where you go.
---
Gary L. Burnore   |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
  |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
DataBasix |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
  |  ÝÛ³ 3 4 1 4 2  ݳ޳ 6 9 0 6 9 ÝÛ³
Black Helicopter Repair Svcs Division | Official Proof of Purchase
===
  Want one?  GET one!   http://signup.databasix.com
===

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

   http://archives.postgresql.org

Re: [GENERAL] I spoke with Marc from the postgresql mailing list.

[Marc G. Fournier]

On Sun, 7 Nov 2004, Mike Cox wrote:
Also, he pointed out that for those who want to get the postgresql 
groups when their usenet sever doesn't carry them, the solution would be 
to point their newsreaders to news.postgresql.org.
The other point I'd like to make ... we have two mail<->gateways setup, a 
second one available at news.fr.postgresql.org, to give us redundancy in 
both directions ...

The gateways have *always* been bi-directional, since their first 
inception over 4 years ago ...

Finally ... if anyone is running a news server that wishes to get the 
hierarchy directly from the source, please email me and we can discuss a 
direct link ...


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] Visual Designer in linux?

[Grant McLean]

On Sun, 2004-11-07 at 16:32, Eric wrote: 
> Is there a visual designer (open source) in linux for database?  I would
> like to developp my data model on the computer...

'Druid' is a Java app that does schema diagrams and point and click
table management stuff:

  http://druid.sourceforge.net/

I haven't used it myself, but I know people that have and think it's
pretty good.

Grant


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

   http://archives.postgresql.org

[GENERAL] index not always used when selecting on a date field

["Miquel van Smoorenburg"]

I have a database with a btree index on the 'removed' field,
which is of type 'date'. However it isn't being used:

techdb2=> explain select * from lines where removed > CURRENT_DATE;
 QUERY PLAN

 Seq Scan on lines  (cost=0.00..243.47 rows=2189 width=324)
   Filter: (removed > ('now'::text)::date)
(2 rows)

Now the weird thing is that if I select a range it is being used:

techdb2=> explain select * from lines where removed > CURRENT_DATE and removed 
< '-01-01';
 QUERY PLAN

 Index Scan using lines_removed_idx on lines  (cost=0.00..120.56 rows=33 
width=324)
   Index Cond: ((removed > ('now'::text)::date) AND (removed < 
'-01-01'::date))
(2 rows)

Why is this?

(Tested with both 7.3.2 and 7.4.6)

Mike.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Report Generation

[Ned Lilly]

My company has a GUI report writer and renderer that we will be releasing under 
an open source license shortly.  You can see some intro material at 
www.openmfg.com/solutions/openreports.php.  Please contact me off-list at 
[EMAIL PROTECTED] if you'd be interested in beta testing.
Regards,
Ned Lilly
Randy Yates wrote:
At the risk of asking an ill-formed oft-asked question that's
probably in the FAQ, is there any report generation tools that
are particularly suited for use with postgres databases?
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Marc G. Fournier]

On Sun, 7 Nov 2004, Gary L. Burnore wrote:
The groups aren't listed as moderated. Anyone who wants to post is
able to. Those not on the mailing list don't go through.  That's the
problem.
As long as the posting gets to the gateway, it gets put into the 
moderator (me) queue for approval ...


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] RFD

[Mike Cox]

> Bruno Wolff III wrote:
> 
>> I saw a post indicating a request for discussion on the creation of an
>> official big 8 newsgroup comp.databases.postgresql.general. According to
>> the notice this newsgroup already exists in google groups and is actively
>> being used. The discussion will be in news.groups.
> 

I would like to point out that it is NOT creating a different group.  It
will still be the same, and there will not be a split or anything.  What
will happen is that the postgresql group will become legitimate, and
therefore all usenet servers will carry the group.

So this is a process that should have happened when the
comp.databases.postresql.* usenet groups were originally created.  I'm
seeing a lot of support for this, except for one person (who seems to not
like me personally).  

I would welcome people to read through news.groups and follow the postgresql
threads.

P.S.  I am just posting this so people can review the RFD (request for
discussion) and please post any suggestions on improvements/likes/dislikes
so it can be made better.  As not to distract the discussions away from the
normal topic, I will post to the postgresql groups no more than once per
week until it is time for the call for votes (CFV).

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] RFD

[Mike Cox]

Bruno Wolff III wrote:

> I saw a post indicating a request for discussion on the creation of an
> official big 8 newsgroup comp.databases.postgresql.general. According to
> the notice this newsgroup already exists in google groups and is actively
> being used. The discussion will be in news.groups.

Actually, the group does exist and is spread out on some servers across the
world.  Google carries it as you mentioned, as does netfront.net.

The problem is that the postgresql groups have not gone through the offical
process that is required in order to be a comp.* group.  Because of this
many servers consider the groups "bogus" and refuse to carry them.  Having
a rogue "comp" group is considered bad, and the correct thing is either be
under an alt, such as alt.databases.postgresql.general OR go through the
official process of becoming a member of the comp.* hierarchy.

The process of going under the comp hierarchy is simple in that a Request
for Discussion is filed (RFD), and then 21 days later a call for votes
(CFV) is issued.  That is where everyone here should vote for postgresql to
be a member of the "big 8".

There are no downsides to this, and there are huge number of benefits from
what I can see.  Among them is that EVERY server worldwide will now be
carrying the postgresql group.  Many don't now because it is a "bogus"
group because it hasn't gone through the process of RFD and CFV.  A big
news server that doesn't carry it is individual.net.

This can only mean good things as the number of users who can participate in
discussions will increase as their news servers can carry the group.  


> 
> I mention this because I thought people on the advocacy list might be
> interested in this group being created as a way to help publicize
> Postgres.
> 

Thank you.  I would also like to mention that the RFD can be made better. 
Any suggestions and improvements will be made!

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Trying to get postgres to use an index

[mike]

> Well, you're joining the entire two tables, so yes, the seq scan might
> be faster.

My mistake.  When composing the email to state the problem, I accidentally
gave a wrong versionof the join query.

Here is the corrected version, which still has the sequential scan...

explain  select notificationID from NOTIFICATION n, ITEM i where n.itemID
= i.itemID andi.projectID = 12;
QUERY PLAN
--
 Hash Join  (cost=2237.54..15382.32 rows=271 width=44)
   Hash Cond: ("outer".itemid = "inner".itemid)
   ->  Seq Scan on notification n  (cost=0.00..12023.71 rows=223671 width=48)
   ->  Hash  (cost=2235.31..2235.31 rows=895 width=4)
 ->  Index Scan using item_ix_item_4_idx on item i 
 (cost=0.00..2235.31 rows=895width=4)
   Index Cond: (projectid = 12)






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

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Devin L. Ganger]

On Sat, 06 Nov 2004 11:11:09 -0800, Mike Cox <[EMAIL PROTECTED]> wrote:

>  Since we have the discussion going, someone mentioned that the group name
>  should be comp.databases.postgresql.  I think this is a good name and I'd
>  like to see what everyone thinks of it.

I think you're pursuing this backwards, Mike. You should contact the current
owner of the present mail-to-news gateway and build some sort of consensus
with *him* on what the problem and proposed solution is, before trying to
create a solution that will only muddy the waters up even farther.

This person made a choice to use Big 8 namespace on his server (and other
servers). His server, his rules. Maybe he can be brought to the table to
discuss why that isn't the easy fix he thought it was and figure out what
the best way to go is from here.

-- 
Devin L. Ganger <[EMAIL PROTECTED]>
"Aikido is based around the central precept of letting an attack take
its natural course.  You, of course, don't want to impede that natural
flow by being in its way." -- overheard on the PyraMOO

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

Re: [GENERAL] Can this be indexed?

[Brent Wood]

>
> >Net Virtual Mailing Lists wrote:
> >> Is there a way to create an index that would make this query be efficient
> >> and not perform a sequential scan?
> >>
> >> SELECT count(*) AS count,id FROM sometable GROUP BY id;
> >
> >Indexes cannot be used for retrieving results...

I'm not sure if it would improve performance at all, given the entire
table needs to be scanned anyway, but add a where clause "where id > 0"
should allow an index on id to be used.

Possibly a bit like speeding up "select max(id) from ..."

Someone who knows more about the internals of Postgis can prob comment on
the validity/idiocy of this suggestion :-)

Brent Wood

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

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Woodchuck Bill]

Mike Cox <[EMAIL PROTECTED]> wrote in
news:[EMAIL PROTECTED]: 

>  I cannot handle the volume of email that a mailing list would place
>  on my 
> inbox. 

Ever heard of a digest version?

-- 
Bill

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

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Devin L. Ganger]

On Sat, 06 Nov 2004 18:03:57 -0800, Mike Cox <[EMAIL PROTECTED]>
wrote:

>  Devin L. Ganger wrote:

> > I think you're pursuing this backwards, Mike. You should contact the
> > current owner of the present mail-to-news gateway and build some sort of
> > consensus with *him* on what the problem and proposed solution is, before
> > trying to create a solution that will only muddy the waters up even
> > farther.
> > 
> > This person made a choice to use Big 8 namespace on his server (and other
> > servers). His server, his rules. Maybe he can be brought to the table to
> > discuss why that isn't the easy fix he thought it was and figure out what
> > the best way to go is from here.
  
>  That is way beyond my technical scope I'm afraid.  I wouldn't know what the
>  correct solution would be.

No one ever said you have to do it *alone*. There are folks here who
would be more than willing to *help* you do it, but they're not going
to do it for you.

>  Russ Allbery <[EMAIL PROTECTED]>, seems very knowledgable about this,
>  and I would be pleased if you could mail the postgresql list person
>  about this discussion and Russ's email address.

Russ is a busy person; don't be so quick to volunteer his time for him
unless you're damn sure you have his permission. Even if he were not a
busy person, most people consider it to be rude to be volunteered
without their consent.

>  Basically if the mailing list-news-gateway doesn't want to be in the big 8
>  then I'm not going to continue in that process.  

Which is fair, but since you're the person who *did* kick this off, you
should probably be the person to email the owner and ask him if he would
be willing to have a conversation with you about the best way to proceed
from here.

You should also probably take a step back and contact the group mentor
list <[EMAIL PROTECTED]> and get some advice and participation
from the folks there, no matter which way you intend to pursue this.
Again, there are people who will help, but you need to be willing to run
point on this even though it's likely not going to be the slam-dunk you
thought it would be initially.

-- 
Devin L. Ganger <[EMAIL PROTECTED]>
"Aikido is based around the central precept of letting an attack take
its natural course.  You, of course, don't want to impede that natural
flow by being in its way." -- overheard on the PyraMOO

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Woodchuck Bill]

"Devin L. Ganger" <[EMAIL PROTECTED]> wrote in 
news:[EMAIL PROTECTED]:

>>  Russ Allbery <[EMAIL PROTECTED]>, seems very knowledgable about this,
>>  and I would be pleased if you could mail the postgresql list person
>>  about this discussion and Russ's email address.
> 
> Russ is a busy person; don't be so quick to volunteer his time for him
> unless you're damn sure you have his permission. Even if he were not a
> busy person, most people consider it to be rude to be volunteered
> without their consent.

Yeah, that was seriously rude.

-- 
Bill

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

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Dave Balderstone]

In article <[EMAIL PROTECTED]>, Mike Cox
<[EMAIL PROTECTED]> wrote:

> I'm pretty much done with this anyway.  It is a waste of time putting in
> anymore effort since no one seems to want it.  

You're way too impatient. Things don't happen here in time scales that
are measured in hours or days. Hang in there. You've got a good start
and some good people supporting what you want to do. 

Relax, take your time (and the advise of the wise ones here (not me...
um well, whatever)) and work through the process.

Your proposal is, in its genesis, sound. Now, evolution.

djb

---
"No, no, no... you don't understand how radio works! First, I fade my
voice out like this, then cue the organist!"

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

Re: [GENERAL] Visual Designer in linux?

[Eric]

On Sun, 2004-11-07 at 14:54, Martijn van Oosterhout wrote:
> There is an ODBC driver for windows that will then talk native to the
> PostgreSQL database, why not use that?


Because I'm on linux :p



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] dumping on 7.4.6 importing on 7.4.1

[stig erikson]

Hi.
Is it possible to dump a database on a 7.4.6 server and then restore it 
on a 7.4.1 server?

should i use the 7.4.1 pg_dump or the 7.4.6 pg_dump?
should i use any switches?
thanks
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Woodchuck Bill]

Klaas <[EMAIL PROTECTED]> wrote in
news:[EMAIL PROTECTED]: 

>> No that is not what I'm proposing.  Each group MUST go through the
>> RFD and CFV seperately.  I started off with the most popular group
>> first.  After It was done, I would have started on the rest.
> 
> Not true.  It is actually rather common for an RFD to be proposed for 
> several groups at once.  The CFV contains one voting option per group.

[comp.databases.postgresql.general added]

Russ and would probably consider waiving the vote, and creating a group for 
each of the popular lists that have a tested popularity base. He already 
said that he was in favor of one group per list. 

One question is..would creating one comp.* group for *each* of the lists 
(the way the rogue groups are currently structured) be too many PostgreSql 
Big-8 groups? Or, could it be cut down to, say, four or five groups/lists?

-- 
Bill

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] ALERT This mailing list may be voted into a newsgroup

[edward ohare]

On Fri, 5 Nov 2004 08:42:40 -0800 (PST), [EMAIL PROTECTED] (Andy M)
wrote:

>ALERT
>
>There is a person by the name of Mike Cox who's trying
>to turn this mailing list into a Big-8 newsgroup. Many
>of you know that this and most of the other postresql
>mailing lists are already gated to Google Groups and a
>small number of private news servers.


"private news servers" like Earthlink and Altopia?

This "mailing list" is already a newsgroup on many servers.  The
toothpaste is out of the tube.  You can't put it back in.

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

[GENERAL] trouble with rpmbuild on WBEL3.0/x86_64

[Marcel Gsteiger]

Hi all,

I wanted to install postgreSQL 7.4 on my ia32e box (x86_64 dual xeon PL370G4) 
running WBEL3.0.  I looked for binary RPMs but did not find any. So I tried to

rpmbuild --rebuild --define 'build9 1' postgresql-7.4.6-2PGDG.src.rpm

but during configure I get the error


checking for python... /usr/bin/python
checking Python installation directories... /usr/lib/python2.2
checking how to link an embedded Python application... no
configure: error: Python Makefile not found
Fehler: Bad exit status from /var/tmp/rpm-tmp.75919 (%build)

python-devel-2.2.3-5 is installed. On a similar i386 system (same versions of 
rpms), this error does not happen; here I could build all RPMs successfully 
(but, of course, not for x86_64).

On my x86_64 system, rh-postgresql 7.3.6-7 is still installed - I thought I 
could rebuild newer RPMS before removing the old ones. Should I remove rhdb 
first?

Did anybody succeed in building x86_64 binary rpms? Any ideas?

Regards
--Marcel


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

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Woodchuck Bill]

Mike Cox <[EMAIL PROTECTED]> wrote in news:[EMAIL PROTECTED]
berlin.de:

> Since we have the discussion going, someone mentioned that the group name
> should be comp.databases.postgresql.  I think this is a good name and I'd
> like to see what everyone thinks of it.

Much better, especially if you are only proposing a single newsgroup in the 
hierarchy. Use of the word "general" is unnecessary, and cumbersome.

-- 
Bill

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Russ Allbery]

In news.groups, Mike Cox <[EMAIL PROTECTED]> writes:

> I'm pretty much done with this anyway.  It is a waste of time putting in
> anymore effort since no one seems to want it.

Well, the problem from my perspective is that a lot of time and energy is
being spent on worrying about how to propose something or what possible
problems there might be with the group and no one (and I'm not asking you
to do this -- I understand very well where you're coming from and am
extremely sympathetic) has not taken the simple first step of just asking
Marc what the status of the groups are.

If Marc was happy with them being turned into real Big Eight groups and
the technical issues of the gatewaying were worked out with Marco or
someone else, I think everything would fall in place very simply.  You'd
get a newsgroup, you wouldn't have to care about the gateway, and we'd be
down to arguing about which mailing lists should be gated.

As is, this thread is currently full of speculation about lots of things
that may or may not be problems and would be cleared up by getting all the
involved parties in the discussion.  And everyone seems to be wanting you
to do this, which I think is a little unfair since the gateways aren't
even what you're worried about in the first place and you'd just have to
play telephone.

I really need to *not* be volunteering to do this, since I have a dozen
other things that I've already promised other people to work on, but it's
really frustrating that no one else is doing it either.

-- 
Russ Allbery ([EMAIL PROTECTED]) 

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

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Russ Allbery]

In news.groups, Devin L Ganger <[EMAIL PROTECTED]> writes:
> Mike Cox <[EMAIL PROTECTED]> wrote:

>>  Russ Allbery <[EMAIL PROTECTED]>, seems very knowledgable about this,
>>  and I would be pleased if you could mail the postgresql list person
>>  about this discussion and Russ's email address.

> Russ is a busy person; don't be so quick to volunteer his time for him
> unless you're damn sure you have his permission. Even if he were not a
> busy person, most people consider it to be rude to be volunteered
> without their consent.

No, this is fine, and this didn't bother me at all.  I'm happy to tell
Marc my opinion; I'm just going to also have to tell him that I don't have
time to do more than give my opinion right now and I don't know if there's
anyone else who's willing to do more of the footwork.

-- 
Russ Allbery ([EMAIL PROTECTED]) 

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

Re: [GENERAL] Copy command and import - MS SQL Server to Postgres

[Sim Zacks]

I know this doesn't answer your question, but have 
you considered doing it with DTS instead of BCP?
I used it recently to migrate an Access database to 
PostGreSQL and it worked great. One of the big advantages is the ability to 
transform the data as it is being converted. 
It is also built in to MSSQL Server. I have used it 
numerous times for data transformations within SQL Server and have always 
enjoyed working with it.

  ""Goutam Paruchuri"" <[EMAIL PROTECTED]> wrote in 
  message news:[EMAIL PROTECTED]...
  Iam trying to 
  import data from ms-sql server to postgres. I export the data which has 
  datetime columns in sql server using BCP. I use the following to import back 
  into postgres.
   
  copy tablename 
  from 'c:\\bcpdata\\mcfa\\tablename.txt' with delimiter as '\t' 
  
   
  I get the 
  following error !!
  invalid input 
  syntax for type timestamp: ""
   
  My input file has 
  the timestamp value like 
   
  2004-09-30 
  11:31:00.000
   
  Any clues 
  ???
   
   
  Thanks !Goutam
   Confidentiality 
  NoticeThe information contained in this e-mail is confidential and 
  intended for use only by the person(s) or organization listed in the address. 
  If you have received this communication in error, please contact the sender at 
  O'Neil & Associates, Inc., immediately. Any copying, dissemination, or 
  distribution of this communication, other than by the intended recipient, is 
  strictly prohibited.

Re: [GENERAL] Can this be indexed?

[Jerry III]

But if you do build an index over "id" then pgsql would only have to do a 
sequential scan on that index, which might be a lot faster if your table 
contains a lot of other data, won't it?

Jerry

""Ed L."" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> On Saturday November 6 2004 7:34, Net Virtual Mailing Lists wrote:
>> Is there a way to create an index that would make this query be efficient
>> and not perform a sequential scan?
>>
>> SELECT count(*) AS count,id FROM sometable GROUP BY id;
>>
>> .. I've considered creating a rule on this table which would put the
>> results of this into another table anytime it is updated, but I thought
>> there might be an easier way.
>
> Since you have no "WHERE" clause and you want to group by id, I believe
> pgsql has to scan all id values.  Those id values are only fully stored in
> the table, so I don't think so.
>
> Ed
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
> 



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

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL]

[Einar Már Björvinsson]

hi
I have been trying to use the Postgresql library (libpq.so) and processes in my program.
The scenario is like that:

I have made a small shared library where I wrapp the Postgresql functions.

I then call those library functions (my library) from my program.

The program is to behave as a deamon so it is in a endless while loop.

The program starts by opening a connection to the database, enter the infinite while loop to run some functions.

Inside the while loop it forks many childs which then call one function from the library I made.

After one round the program goes to sleep and wakes up again one minute later.

Now comes the problem:: If I call the same the same functions again, those within the while loop (not the connection call) I get an error return.

It is as if the handle to the connection is not valid any more.

I have checked the program without forking and that works fine.

So my question is what can be done to share a library within processes so they are not duplicating the library includes and so on.

regards


Einar

Re: [GENERAL] dumping on 7.4.6 importing on 7.4.1

[Tom Lane]

stig erikson <[EMAIL PROTECTED]> writes:
> Is it possible to dump a database on a 7.4.6 server and then restore it 
> on a 7.4.1 server?

Should work, but why aren't you updating the 7.4.1 server?  There were
some pretty nasty bugs fixed between .1 and .6.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Marc G. Fournier]

On Sat, 6 Nov 2004, Russ Allbery wrote:
If Marc was happy with them being turned into real Big Eight groups and
the technical issues of the gatewaying were worked out with Marco or
someone else
what 'techincal issues of the gatewaying'?

Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL]

[Tom Lane]

=?iso-8859-1?Q?Einar_M=E1r_Bj=F6rvinsson?= <[EMAIL PROTECTED]> writes:
> The program starts by opening a connection to the database, enter the =
> infinite while loop to run some functions.=20

> Inside the while loop it forks many childs which then call one function =
> from the library I made.

Are you trying to say that you are opening a database connection in the
parent process and passing it down for the children to play around with?
This is a fairly bad idea, unless you have an interlock mechanism to
ensure that only one child uses it at a time.

> It is as if the handle to the connection is not valid any more.=20

Perhaps the child is closing the connection when it's done with it.

regards, tom lane

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

Re: [GENERAL] Can this be indexed?

[Tatsuo Ishii]

> Is there a way to create an index that would make this query be efficient
> and not perform a sequential scan?
> 
> SELECT count(*) AS count,id FROM sometable GROUP BY id;
> 
> .. I've considered creating a rule on this table which would put the
> results of this into another table anytime it is updated, but I thought
> there might be an easier way.

Sure. Try to create an index on id. Another way to improve this query
is to use HashAggregate (this is new in 7.4). Sometimes it is much
faster than group-by-using-index-scan. To enable HashAggregate
you might want to increase sort_mem.
--
Tatsuo Ishii

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Gary L. Burnore]

At 12:16 AM 11/7/2004, Russ Allbery wrote:
In news.groups, Mike Cox <[EMAIL PROTECTED]> writes:
> I'm pretty much done with this anyway.  It is a waste of time putting in
> anymore effort since no one seems to want it.
Well, the problem from my perspective is that a lot of time and energy is
being spent on worrying about how to propose something or what possible
problems there might be with the group and no one (and I'm not asking you
to do this -- I understand very well where you're coming from and am
extremely sympathetic) has not taken the simple first step of just asking
Marc what the status of the groups are.
If Marc was happy with them being turned into real Big Eight groups and
the technical issues of the gatewaying were worked out with Marco or
someone else, I think everything would fall in place very simply.  You'd
get a newsgroup, you wouldn't have to care about the gateway, and we'd be
down to arguing about which mailing lists should be gated.
As is, this thread is currently full of speculation about lots of things
that may or may not be problems and would be cleared up by getting all the
involved parties in the discussion.  And everyone seems to be wanting you
to do this, which I think is a little unfair since the gateways aren't
even what you're worried about in the first place and you'd just have to
play telephone.
I really need to *not* be volunteering to do this, since I have a dozen
other things that I've already promised other people to work on, but it's
really frustrating that no one else is doing it either.
I'll volunteer.  This shouldn't be ThAT hard to fix.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Gary L. Burnore]

At 04:29 PM 11/7/2004, Marc G. Fournier wrote:
On Sun, 7 Nov 2004, Gary L. Burnore wrote:
The groups aren't listed as moderated. Anyone who wants to post is
able to. Those not on the mailing list don't go through.  That's the
problem.
As long as the posting gets to the gateway, it gets put into the moderator 
(me) queue for approval ...

Which is why the groups should be moderated.   That'd fix a major part of 
the problem. As it is now, the post will be on the usenet group but not 
necessarily in the email group.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] ALERT This mailing list may be voted into a

[Gary L. Burnore]

At 08:29 AM 11/6/2004, edward ohare wrote:
On Fri, 5 Nov 2004 08:42:40 -0800 (PST), [EMAIL PROTECTED] (Andy M)
wrote:
>ALERT
>
>There is a person by the name of Mike Cox who's trying
>to turn this mailing list into a Big-8 newsgroup. Many
>of you know that this and most of the other postresql
>mailing lists are already gated to Google Groups and a
>small number of private news servers.
"private news servers" like Earthlink and Altopia?
He's confused. :)

This "mailing list" is already a newsgroup on many servers.  The
toothpaste is out of the tube.  You can't put it back in.
It can be fixed.  

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

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Gary L. Burnore]

On Sun, 07 Nov 2004 21:16:05 GMT, Stephan Szabo <[EMAIL PROTECTED]>
wrote:

>Gary L. Burnore <[EMAIL PROTECTED]> wrote:
>>On Sun, 07 Nov 2004 19:26:42 GMT, Stephan Szabo <[EMAIL PROTECTED]>
>>wrote:
>>
>>>"Robert G" <[EMAIL PROTECTED]> wrote:

Hello Mike. A number of us from the mailing list have been discussing
this on the back channel (e-mail). At least ten people that I am aware
of are not in favor of this idea. Your efforts are appreciated, to a
point, but I do prefer if you just left things well enough alone. You
took this initiative without notifying the mailing list proper. I saw
that you posted to the Newsgroup side, but your posts did not reach the
e-list. It is one-sided, and many of us prefer to keep it that way. A
>>>
>>>I personally haven't seen too much discussion on this subject on the mailing 
>>>list proper, either (perhaps because the weekend traffic tends to be low) so 
>>>I think it's too early to be saying things like "many of us" speaking for the
>>>mailing list.
>>>
>>>Besides which, AFAICT the mailing list gateway is not one-sided given that
>>>I seem to be able to post to my usenet server giving my subscribed email
>>>address and have it show up on the mailing list. 
>>
>>
>>The groups aren't listed as moderated. Anyone who wants to post is
>>able to. Those not on the mailing list don't go through.  That's the
>>problem.
>
>As far as I know, they do go through after moderation currently. 
>
>As for "the problem," I can't figure out if you are speaking about the
>current state or the proposal.  If the former, given that the current
>state is AFAIK that the groups have no status at all, I can't see how
>not being listed as moderated has any effect. 

User makes a comment in USENet. Post gets seen on usenet servers
around the world.  Moderator chooses not to approve.  Post remains in
USENet, not in email list.  Later, someone replies to usenet post.
Moderator accepts.  Now people on list see that they didn't get to see
the post being replied to. 

If the groups are moderated, the moderator gets _ALL_ posts before
they appear in the group.


-- 
[EMAIL PROTECTED] dot com  
---
  How you look depends on where you go.
---
Gary L. Burnore   |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
  |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
DataBasix |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
  |  ÝÛ³ 3 4 1 4 2  ݳ޳ 6 9 0 6 9 ÝÛ³
Black Helicopter Repair Svcs Division | Official Proof of Purchase
===
  Want one?  GET one!   http://signup.databasix.com
===

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

Re: [GENERAL] I spoke with Marc from the postgresql mailing list.

[Gary L. Burnore]

On Sun, 7 Nov 2004 16:50:34 -0400 (AST), [EMAIL PROTECTED] ("Marc
G. Fournier") wrote:

>On Sun, 7 Nov 2004, Mike Cox wrote:
>
>> Also, he pointed out that for those who want to get the postgresql 
>> groups when their usenet sever doesn't carry them, the solution would be 
>> to point their newsreaders to news.postgresql.org.
>
>The other point I'd like to make ... we have two mail<->gateways setup, a 
>second one available at news.fr.postgresql.org, to give us redundancy in 
>both directions ...
>
>The gateways have *always* been bi-directional, since their first 
>inception over 4 years ago ...

They may be bi-directional but they're still broken.  Posts to the
usenet groups get propigated instead of sent to the moderator.  That
means they make it to the groups and MAY make it to the list.

>
>Finally ... if anyone is running a news server that wishes to get the 
>hierarchy directly from the source, please email me and we can discuss a 
>direct link ...


-- 
[EMAIL PROTECTED] dot com  
---
  How you look depends on where you go.
---
Gary L. Burnore   |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
  |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
DataBasix |  ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
  |  ÝÛ³ 3 4 1 4 2  ݳ޳ 6 9 0 6 9 ÝÛ³
Black Helicopter Repair Svcs Division | Official Proof of Purchase
===
  Want one?  GET one!   http://signup.databasix.com
===

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

[GENERAL] RFD: comp.databases.postgresql.general

[Andy]

Someone posted this official proposal to create
comp.databases.postgresql.general again. He wrote his own charter. As
far as I know, he did not consult any of the postgresql groups first.
There may be an upcoming vote on this, so please stay informed and read
news.newgroups.announce for updates.

Also see message <[EMAIL PROTECTED]> for an example of the
proponent's temperament.



"Mike Cox" wrote:
 REQUEST FOR DISCUSSION (RFD)
 unmoderated group comp.databases.postgresql.general

This is a formal Request For Discussion (RFD) for the creation of
a worldwide unmoderated Usenet newsgroup
comp.databases.postgresql.general.  This is not a Call for Votes
(CFV); you cannot vote at this time.  Procedural details are below.

RATIONALE: comp.databases.postgresql.general

comp.databases.postgresql.general exists in groups.google.com.  It
has never gone through the big eight process of RFD and CFV, therefore
is considered "bogus" and many news servers will not carry
comp.databases.postgresql.general.

comp.databases.postgresql.general is already very active, with many
people posting and reading through groups.google.com.  Having it
be an official big eight group will enable people to follow it
through their usenet servers.

CHARTER: comp.databases.postgresql.general

The comp.databases.postgresql.general unmoderated newsgroup will
provide a general discussion location for users of the open-source
PostgreSQL RDBMS.

Postgresql is the most advanced open source relational database
management system  with thousands of users.  It has won many awards
and is distributed with almost every Linux and BSD distribution.

PostgreSQL may be freely downloaded from http://www.postgresql.org.

END CHARTER.

PROCEDURE:

This is a request for discussion, not a call for votes.  In this phase
of the process, any potential problems with the proposed newsgroups
should be raised and resolved.  The discussion period will continue
for a minimum of 21 days (starting from when the first RFD for this
proposal is posted to news.announce.newgroups), after which a Call For
Votes (CFV) may be posted by a neutral vote taker if the discussion
warrants it.  Please do not attempt to vote until this happens.

All discussion of this proposal should be posted to news.groups.

This RFD attempts to comply fully with the Usenet newsgroup creation
guidelines outlined in "How to Create a New Usenet Newsgroup" and "How
to Format and Submit a New Group Proposal."  Please refer to these
documents (available in news.announce.newgroups) if you have any
questions about the process.

DISTRIBUTION:

This RFD has been posted to the following newsgroups:

news.announce.newgroups, news.groups

A pointer will be posted to the following group:

comp.databases.postgresql.general on Google Groups

Proponent: Mike Cox [EMAIL PROTECTED]


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

[GENERAL] start postmaster as root : problem

[Stefano Farina]

Hi all,
I have to develop an embedded database with PostgreSQL.
I have a Linux Distribuction  that works only in single user mode as root.
The postmaster daemon starts only if  I 'm  not  root user.
Do you have a solution for this problem ??
Thanks.

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

Re: [GENERAL] how to edit a function from psql?

[Karim Nassar]

Here is what I get:

orfs=# \df+ get_datasets

List of functions
 Result data type |Schema| Name |   Argument
data types   | Owner | Language
|  Source
code| 
Description
--+--+--+-+---+--+---+-
 refcursor| weather_data | get_datasets | refcursor, character
varying, character varying | kan4  | plpgsql  |
DECLARE
_person_ ALIAS FOR $2;
_where_  ALIAS FOR $3;
selectstring text;

BEGIN
selectstring := get_datasets_selstr(_person_, _where_);
-- RAISE NOTICE '%', selectstring;
OPEN $1 FOR EXECUTE selectstring;
RETURN $1;
END;
 |
(1 row)

orfs=# \e
CREATE FUNCTION

When I issue \e, the editor window pops up, apparently with the contents
of the query buffer. When I exit the editor, the function definition is
applied to create this function (the one in the buffer).

orfs=# \?

Query Buffer
  \e [FILE]  edit the query buffer (or file) with external editor
  \g [FILE]  send query buffer to server (and results to file or
|pipe)
  \p show the contents of the query buffer
  \r reset (clear) the query buffer
  \s [FILE]  display history or save it to file
  \w [FILE]  write query buffer to file


How did that function definition get in the query buffer? Seems that it
sure would be nice to fill it \df+...

\<.




On Thu, 2004-11-04 at 14:40, Alvaro Herrera wrote:
> On Thu, Nov 04, 2004 at 02:22:06PM -0600, Thomas F.O'Connell wrote:
> > To the best of my knowledge, this is not possible in psql.
> > 
> > You can edit individual queries with \e, but I don't think it's 
> > possible to edit functions.
> 
> It is of course possible get the definition using \df+ and then use
> CREATE OR REPLACE in conjuntion with \e to edit it at will.


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

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Kenneth Downs]

Andy wrote:

> Someone posted this official proposal to create
> comp.databases.postgresql.general again. He wrote his own charter. As
> far as I know, he did not consult any of the postgresql groups first.
> There may be an upcoming vote on this, so please stay informed and read
> news.newgroups.announce for updates.
> 
> Also see message <[EMAIL PROTECTED]> for an example of the
> proponent's temperament.
> 

I can see how this would ruffle some serious feathers.

But if I can risk getting a smack, I'd like to say that I had a bit of
trouble figuring out how to get on to this group.  The "respectable" news
server I use does not carry it, but it shows up on Google.  What's that
about?

Then I've noticed some notes here and there that you are supposed to send
some emails to a list-server if you post, to avoid messing up the mailing
list?  Is that right?  Why would I be worried about a listserv?

Finally figured out this is some kind of hybrid newsgroup/mail-list.  Is
that right?  Real question is, why I am trying to figure this out?  Why
isn't it on the news server with all of the other technical groups?

To make a long story short, the request might not have been made in the most
diplomatic way, but it would, if adopted, solve some real anomalies that
confuse newcomers to this group and its relatives.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] RFD

[Jon Bell]

In article <[EMAIL PROTECTED]>,
Mike Cox  <[EMAIL PROTECTED]> wrote:
>
>I would like to point out that it is NOT creating a different group.  It
>will still be the same, and there will not be a split or anything.  What
>will happen is that the postgresql group will become legitimate, and
>therefore all usenet servers will carry the group.

[I'm posting this from news.groups, where this discussion is being 
crossposted.  I have no connection with the postgresql groups myself; I'm 
just a longtime observer of the newsgroup-creation process.]

Just to clarify Mike's comments and make a minor correction... if this 
proposal comes to a vote and passes, it will *not* guarantee that "all" 
Usenet servers will carry the comp.databases.postgresql.general.  There 
are servers that do not keep in sync with the "official" list of groups 
maintained by the moderators of news.announce.newgroups, out of laziness 
or other reasons.  Nevertheless, it *will* cause significantly more 
servers to carry the group than before, including most or all of the 
"well-managed" ones.

And it will have no effect on the availability or functioning of the group 
on those servers that currently do carry it, except of course that you'll 
probably have more postings to read.

-- 
Jon Bell <[EMAIL PROTECTED]> Presbyterian College
Dept. of Physics and Computer ScienceClinton, South Carolina USA

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Rob Kelk]

On 4 Nov 2004 17:17:20 GMT, "Andy" <[EMAIL PROTECTED]> wrote to
news.groups:

>Someone posted this official proposal to create
>comp.databases.postgresql.general again. 

As the name says, this is a Request for Discussion, not an "official
proposal" (whatever that may be).  Discussion about this is not only
welcome, it is encouraged.

> He wrote his own charter.

Somebody had to write one.  If there's a problem with it, now is the
time for discussion.  (I can see some potential problems with it,
actually - there's no mention of what sort of posts would be on-topic or
off-topic for the proposed newsgroup, for one thing.)

> As
>far as I know, he did not consult any of the postgresql groups first.

It is difficult to consult a group that isn't carried on all servers.
That's one reason why this thread is being posted to news.groups - it's
a group that is carried on almost every Usenet server.

>There may be an upcoming vote on this, so please stay informed and read
>news.newgroups.announce for updates.

And please take part in the discussion if you have a concern that hasn't
been raised by someone else.


>Also see message <[EMAIL PROTECTED]> for an example of the
>proponent's temperament.

It looks to me like the proponent is upset about not being able to
access the existing sort-of-newsgroup, and would like the group promoted
to full newsgroup status so that it will have a larger propogation.



-- 
Rob Kelk
Personal address (ROT-13): eboxryx -ng- wxfei -qbg- pbz
Any opinions here are mine, not ONAG's.
ott.* newsgroup charters: 

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

[GENERAL] Making this group a part of the official comp.* hierarchy.

[Mike Cox]

Hi.

As most of you know, comp.databases.postgresql.general is a wonderful
resource.  What you may not know is that it has not gone through a
process that would enable it to be listed on hundreds of usenet
servers worldwide by default.

Normally groups that are under the comp.* hierarchy go through a
something called RFD and CFV.  comp.databases.postgresql.general has
not gone through this process, therefore many servers refuse to carry
_any_ comp.databases.postresql.* groups.

This is very easy to correct.  And you can participate in making it
happen.  An RFD (Request for Discussion) has been created, and is
posted to news.groups.  Here you will find information on the proposed
group.  If you read the RFD and find any thing incorrect or ways to
improve it, just post to news.groups.  Once it has been modified to be
perfect, and after a waiting period of 21 days, there will be a call
for votes (CFV).

Here is where you decide whether comp.databases.postgresql.general
should be available to _every_ usenet server in the world as part of
the offical comp.* hierarchy.  There will be a vote and if it passes,
every usenet server will carry the group.

 Currently many refuse to carry groups that have failed to go through
the process of RFD and CFV.  This means many popular servers are not
making postgresql groups available.  This includes the very popular
individual.net usenet server.  In order to make this available, and
spread the use of postgresql and knowledge of this wonderful
relational database management system, it is a very good idea to
support this.

Supporting it will insure that no matter what usenet server you are
on, you will have access to the postgresql groups.

If you have any questions, just respond to this thread.  Make sure you
cross post to news.groups as well.  They are experts in the newsgroup
creation process and they will answer any quetions on that end.

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

[GENERAL] ALERT This mailing list may be voted into a newsgroup

[Mike Cox]

Andy M wrote:

> ALERT
> 
> There is a person by the name of Mike Cox who's trying
> to turn this mailing list into a Big-8 newsgroup. 

No, I'm trying to get teh postgresql groups which are already ON usenet to
follow proper usenet guidelines.  In order to be under the comp.* hierarchy
the server MUST go through RFD and CFV.  Otherwise it is a ROGUE group!

The proper thing would have been to go through the process when they were
placed on usenet OR put them under the alt.* hierarchy.

So in essence, postgresql is under the comp.* hierarchy illegally already,
and many usenet servers that are followin proper proceedures do not carry
it. Other server that do not care about proper hierarchy or are trying to
include everything for their paying subscribers do.

By making the postgresql group legitimate, many more servers will carry the
group, and more people can participate.  Also, making the group legitimate
will increase the postgresql brand because currently it is violating usenet
rules by being placed under the comp.* hierarchy!

>Many
> of you know that this and most of the other postresql
> mailing lists are already gated to Google Groups and a
> small number of private news servers. If Mike Cox
> succeeds, this list will be available as a newsgroup
> on a great many more ISPs and NSPs, which could be a
> good or a bad thing. For one, e-mail addresses on this
> list will probably receive an increased volume of
> spam.
> 
> This person Mike wrote his own charter for this
> mailing list, without asking for help from any of the
> list subscribers. He went ahead and submitted the
> proposal to the Usenet big-wigs without consulting the
> mailing list first.

That is the Request for Discussion (RFD).  It is the offical way to discuss
the proposal.  Any changes to the charter are welcome.  The RFD requires a
charter and I included a default one because I couldn't locate the
"official" one. It is easy to submitt another RFD with the improved
charter, but first we must start the discussion, which is what I'm doing.

The RFD, is just that A Request for Discussion!!!  That means I'm doing
exactly what Andy WANTS!

Later if people dislike the idea, they can vote against it of course.  If
there seems to be a general dislike for the idea it doesn't even have to
come before a CFV (call for votes).  I just won't turn in the questionaire,
and the whole thing will die.

It would be a serious mistake, since the postgresql newsgroups are violating
usenet regulations and many proper usenet providers consider the
comp.databases.postgresql.* as BOGUS because they never went through RFD
and CFV.

> The discussion is occurring in the
> news.groups Usenet group by a bunch of Usenet techies.
> Please get involved in the discussion and express your
> views. By all means, vote, when the time comes.
> 
> If you have no access to Usenet, you could follow the
> discussion via Google Groups. Here are links to some
> of the threads--
> 
> http://shorl.com/jevukofrolelo
> http://shorl.com/hunofetaprona
> http://shorl.com/boranobrimise
> 
> Andy
> 
> 
> Proposal is below:
> 
>  REQUEST FOR DISCUSSION (RFD)
>  unmoderated group
> comp.databases.postgresql.general
> 
> This is a formal Request For Discussion (RFD) for the
> creation of
> a worldwide unmoderated Usenet newsgroup
> comp.databases.postgresql.general.
> This is not a Call for Votes (CFV); you cannot vote at
> this time.
> Procedural details are below.
> 
> RATIONALE: comp.databases.postgresql.general
> 
> comp.databases.postgresql.general exists in
> groups.google.com.  It
> has never gone through the big eight process of RFD
> and CFV, therefore
> is considered "bogus" and many news servers will not
> carry
> comp.databases.postgresql.general.
> 
> comp.databases.postgresql.general is already very
> active, with many
> people posting and reading through groups.google.com.
> Having it
> be an official big eight group will enable people to
> follow it
> through their usenet servers.
> 
> CHARTER: comp.databases.postgresql.general
> 
> The comp.databases.postgresql.general unmoderated
> newsgroup will
> provide a general discussion location for users of the
> open-source
> PostgreSQL RDBMS.
> 
> Postgresql is the most advanced open source relational
> database
> management system  with thousands of users.  It has
> won many awards
> and is distributed with almost every Linux and BSD
> distribution.
> 
> PostgreSQL may be freely downloaded from
> http://www.postgresql.org.
> 
> END CHARTER.
> 
> PROCEDURE:
> 
> This is a request for discussion, not a call for
> votes.  In this phase
> of the process, any potential problems with the
> proposed newsgroups
> should be raised and resolved.  The discussion period
> will continue
> for a minimum of 21 days (starting from when the first
> RFD for this
> proposal is posted to news.announce.newgroups), after
> which a Call For
> Votes (CFV) may be posted by a neutral vote taker if
> the discussion
> warrants it.

[GENERAL] can you use variables in PostgreSQL rules?

[Vassilev, Lubomir G.]

i am currently migrating a db from Oracle to Pg and in some
of my triggers i use variables, i.e. i have some thing like this:

 

  [Oracle version]

 

  CREATE OR REPLACE TRIGGER "USERACTION_VIEW"
INSTEAD OF INSERT ON "USERACTION_VIEW" DECLARE

    UserActionID INTEGER;

    UserActionDataID INTEGER;

begin

SELECT USERACTION_SQ.nextval into UserActionID FROM DUAL;

SELECT USERACTIONDATA_SQ.nextval into UserActionDataID FROM
DUAL;

 

INSERT INTO USERACTION

  (

   UserActionID,

   LocalID,

   UserActionTypeID,

   ActionDate

   )

  VALUES

  (

   UserActionID,

   :new.LocalID,

   :new.UserActionTypeID,

   SYSDATE

  );  

  

INSERT INTO USERACTIONDATA

  (

   UserActionDataID,

   UserActionID,

   AccountID,

   CourseRecordID,

   CourseRecordPaymentID,

   StudentID,

   CourseRecordTestID,

   CourseRecordLessonID   

   )

  VALUES

  (

   UserActionDataID,

   UserActionID,

   :new.AccountID,

   :new.CourseRecordID,

   :new.CourseRecordPaymentID,

   :new.StudentID,

   :new.CourseRecordTestID,

   :new.CourseRecordLessonID

  );    

 

end; 

 

 

so basically i have two variables here, UserActionID and
UserActionDataID. so when i am porting to PostgreSQL i will need to make this a
rule since Pg triggers only work for tables. The problem though is that i don't
know how to use variabvles in Pg rules. Any ideas? Is it possible at all?

 

Thanks.

Re: [GENERAL] start postmaster as root : problem

[Stefano Farina]

Hi all,
I have to develop an embedded database with PostgreSQL.
I have a Linux Distribuction  that works only in single user mode as 
root.
The postmaster daemon starts only if  I 'm  not  root user.

Do you have a solution for this problem ??
Thanks.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] disabling constraints

[Edmund Bacon]

[EMAIL PROTECTED] (Vivek Khera) writes:

> > "DP" == David Parker <[EMAIL PROTECTED]> writes:
> 
> DP> I would like to be able to truncate all of the tables in a schema
> DP> without worrying about FK constraints. I tried issuing a "SET
> DP> CONSTRAINTS ALL DEFERRED" before truncating, but I still get
> DP> constraint errors. Is there a way to do something like:
> 
> Why don't you truncate your tables in an order that won't violate
> FK's?  Or do you have circular references?
> 
> -- 

Because that doesn't work:

test=# create table able(id serial primary key, data text);
NOTICE:  CREATE TABLE will create implicit sequence "able_id_seq" for "serial" 
column "able.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "able_pkey" for 
table "able"
CREATE TABLE
test=# create table baker(id int references able(id) deferrable, data text);
CREATE TABLE
test=# truncate able;
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "baker" references "able" via foreign key constraint "$1".
test=# begin;
BEGIN
test=# set constraints all deferred;
SET CONSTRAINTS
test=# truncate able;
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "baker" references "able" via foreign key constraint "$1".
test=# rollback;
ROLLBACK
test=#

-- 
Remove -42 for email

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

Re: [GENERAL] RFD: comp.databases.postgresql.general

[BarB]

On 4 Nov 2004 17:17:20 GMT, "Andy" <[EMAIL PROTECTED]> wrote:

>Someone posted this official proposal to create
>comp.databases.postgresql.general again. He wrote his own charter. As
>far as I know, he did not consult any of the postgresql groups first.
>There may be an upcoming vote on this, so please stay informed and read
>news.newgroups.announce for updates.
 
Ouch, I can see why you're upset. The first recommendation to any
proponent is that he be well-known to the affected groups and that he
consult with them before proceeding. He needs the support of other
users to get those 120 votes+ to pass. I'd suggest he find additional
proponents who are better known to the group.

However all is not lost. Remember the proponent of an unmoderated
group has no more say in how the group is run than any other user. If
this needs to be a valid comp.* group, it really doesn't matter who
proposes it. Get the discussion going now. If you don't like the
charter, suggest changes. Then, if you still don't like it, vote
against it; but don't throw the baby out with the bath water. 

BarB

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

Re: [GENERAL] 24x7x365 high-volume ops ideas

[Karim Nassar]

On Wed, 2004-11-03 at 18:10, Ed L. wrote:
> unfortunately, the requirement is 100% uptime all the time, and any 
> downtime at all is a liability.  Here are some of the issues:

Seems like 100% uptime is always an issue, but not even close to
reality. I think it's unreasonable to expect a single piece of software
that NEVER to be restarted. Never is a really long time.

For this case, isn't replication sufficient? (FWIW, in 1 month I have to
answer this same question). Would this work?

* 'Main' db server up 99.78% of time
* 'Replicant' up 99.78% of time (using slony, dbmirror)
* When Main goes down (crisis, maintenance), Replicant answers for Main,
  in a read-only fashion.
* When Main comes back up, any waiting writes can now happen.
* Likewise, Replicant can be taken down for maint, then Main syncs to it
  when going back online.

Is this how it's done?

\<.


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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] how to edit a function from psql?

[Thomas F . O'Connell]

What version of postgres are you using? In postgresql-7.4.6, I get an  
empty query buffer when I try what you describe here.

-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 4, 2004, at 5:23 PM, Karim Nassar wrote:
Here is what I get:
orfs=# \df+ get_datasets

 List of functions
 Result data type |Schema| Name |
Argument
data types   | Owner | Language
|  Source
code
 | Description
--+--+-- 
+-+---+-- 
+-- 
--- 
--- 
--- 
+-
 refcursor| weather_data | get_datasets | refcursor, character
varying, character varying | kan4  | plpgsql  |
DECLARE
_person_ ALIAS FOR $2;
_where_  ALIAS FOR $3;
selectstring text;

BEGIN
selectstring := get_datasets_selstr(_person_, _where_);
-- RAISE NOTICE '%', selectstring;
OPEN $1 FOR EXECUTE selectstring;
RETURN $1;
END;
 |
(1 row)
orfs=# \e
CREATE FUNCTION
When I issue \e, the editor window pops up, apparently with the  
contents
of the query buffer. When I exit the editor, the function definition is
applied to create this function (the one in the buffer).

orfs=# \?

Query Buffer
  \e [FILE]  edit the query buffer (or file) with external editor
  \g [FILE]  send query buffer to server (and results to file or
|pipe)
  \p show the contents of the query buffer
  \r reset (clear) the query buffer
  \s [FILE]  display history or save it to file
  \w [FILE]  write query buffer to file

How did that function definition get in the query buffer? Seems that it
sure would be nice to fill it \df+...
\<.

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] technical help

[madhu v t]

  
We in Calicut UNIversity using posgresql in our examination wing.
Now we can not use our database. It gives some message as follows.

XLogFlush : request 0/D8949278 IS NOT SATISFIED. --FLUSHED ONLY TO 0/D1702530

What is this. Why this message comes. And how to overcome this problem?

Madhu VT
System Administrator

Re: [GENERAL] Visual Designer in linux?

[GreyGeek]

Eric wrote:

> Is there a visual designer (open source) in linux for database?  I would
> like to developpASCIIata model on the computer...
> 
> I see "dia" with uml library but... it won't export to SQL.
> 

If you like Qt can use Qt-Designer (assuming you have the PostgreSQL odbc
driver installed) or you can use KDevelop and merge Qt gui's with it.

I prefer Python and Boa_Constructor (which requires wxPython) and a unixODBC
DNS to PostgreSQL.  Boa creates ASCII source files which version control
nicely. It has a great runtime debugger with stops, watches, var
displays,etc.

If you use Java then JDeveloper with an ODBC line to PostgreSQL works OK,
but Python is 5-10X faster.
--
GreyGeek

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

Re: [GENERAL] index not always used when selecting on a date field

[Russell Smith]

On Mon, 8 Nov 2004 07:56 am, "Miquel van Smoorenburg" wrote:
> I have a database with a btree index on the 'removed' field,
> which is of type 'date'. However it isn't being used:
> 
> techdb2=> explain select * from lines where removed > CURRENT_DATE;
>  QUERY PLAN
> 
>  Seq Scan on lines  (cost=0.00..243.47 rows=2189 width=324)
>Filter: (removed > ('now'::text)::date)
> (2 rows)
> 
> Now the weird thing is that if I select a range it is being used:
> 
> techdb2=> explain select * from lines where removed > CURRENT_DATE and 
> removed < '-01-01';
>  QUERY PLAN
> 
>  Index Scan using lines_removed_idx on lines  (cost=0.00..120.56 rows=33 
> width=324)
>Index Cond: ((removed > ('now'::text)::date) AND (removed < 
> '-01-01'::date))
> (2 rows)
> 
> Why is this?
> 
> (Tested with both 7.3.2 and 7.4.6)
> 
> Mike.
> 
now() and CURRENT_DATE, are and cannot be planned as constants.
So the planner cannot use an index for them.

This have been covered on the list a number of times.  Until a solution is at 
hand,
you can either use constants instead of now, or create a immutable function 
that returns now.
However if you PREPARE those queries, you will not get the new time for now() 
each time you 
run the query.

This function fits in a category between STABLE and IMMUTABLE, of which there 
is currently
no type.

Regards

Russell Smith

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] I spoke with Marc from the postgresql mailing list.

[Marc G. Fournier]

On Sun, 7 Nov 2004, Gary L. Burnore wrote:
On Sun, 7 Nov 2004 16:50:34 -0400 (AST), [EMAIL PROTECTED] ("Marc
G. Fournier") wrote:
On Sun, 7 Nov 2004, Mike Cox wrote:
Also, he pointed out that for those who want to get the postgresql
groups when their usenet sever doesn't carry them, the solution would be
to point their newsreaders to news.postgresql.org.
The other point I'd like to make ... we have two mail<->gateways setup, a
second one available at news.fr.postgresql.org, to give us redundancy in
both directions ...
The gateways have *always* been bi-directional, since their first
inception over 4 years ago ...
They may be bi-directional but they're still broken.  Posts to the
usenet groups get propigated instead of sent to the moderator.  That
means they make it to the groups and MAY make it to the list.
If they make it to the gateway, they make it to the moderator and make it 
to the lists ... I know this for a fact, because I'm the moderator that 
goes through an approves them...


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Marc G. Fournier]

On Sun, 7 Nov 2004, Gary L. Burnore wrote:
User makes a comment in USENet. Post gets seen on usenet servers around 
the world.  Moderator chooses not to approve.
Unless ist Spam, moderator always approves ... I know, cause its me ...

Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Marc G. Fournier]

On Sun, 7 Nov 2004, Gary L. Burnore wrote:
At 04:29 PM 11/7/2004, Marc G. Fournier wrote:
On Sun, 7 Nov 2004, Gary L. Burnore wrote:
The groups aren't listed as moderated. Anyone who wants to post is
able to. Those not on the mailing list don't go through.  That's the
problem.
As long as the posting gets to the gateway, it gets put into the moderator 
(me) queue for approval ...

Which is why the groups should be moderated.   That'd fix a major part of the 
problem. As it is now, the post will be on the usenet group but not 
necessarily in the email group.
Unless its spam, it goes through ... I don't (nor have I ever) refused a 
post based on content other then spam ... even if its anti-PostgreSQL 
*shrug*


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] RFD: comp.databases.postgresql.general

[Marc G. Fournier]

On Thu, 4 Nov 2004, Kenneth Downs wrote:
Then I've noticed some notes here and there that you are supposed to send
some emails to a list-server if you post, to avoid messing up the mailing
list?  Is that right?  Why would I be worried about a listserv?
There are no such requirements that I'm aware of, and I setup/maintain the 
primary gateway ... in fact, I'm the one that goes through all of the 
news->mail messages and approves them to go through to the lists ...


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] Visual DATA MODEL Designer in linux?

[Eric]

Heu...

I search for a graphical DATA MODEL designer in linux to developp my
database project (to draw) it on screen instead of paper and pencil...

Thanks for answers about Database GUI developper... I will surely need
it later but for now, I want to "draw" boxes etc... on screen for my
tables and interactions.

I don't know about UML (somebody told me about it).  

I'd like DBDesigner4 at this moment but I will have to test with ODBC
because it seems that postgresql isn't native support like mysql with
this tool...

Any other suggestions welcome.

I hope this message will be more accurate then the one before :)

Thanks every body for your advices.

Eric.



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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Russ Allbery]

Marc G Fournier <[EMAIL PROTECTED]> writes:
> On Sat, 6 Nov 2004, Russ Allbery wrote:

>> If Marc was happy with them being turned into real Big Eight groups and
>> the technical issues of the gatewaying were worked out with Marco or
>> someone else

> what 'techincal issues of the gatewaying'?

There are a couple of things that would be ideal to fix.  One is that
currently the gateway isn't rewriting message IDs, which means that if
anyone else gates the same mailing lists into some other group, the posts
will conflict and posts will randomly disappear.  This isn't a huge issue,
but it would be nice to fix it, since this is a common problem.

The other one is that right now the newsgroup and the mailing list get
different traffic since posts are only gated to the mailing list if the
person is already a member.  This can cause confusion (like only half of a
thread being seen on the mailing list).  The ideal way to fix this is to
make the newsgroup moderated (which also simplifies the whole process
since then you don't need any news to mail gateway).  That way, things can
be set up so that only the messages that make it to the list make it to
the newsgroup.

-- 
Russ Allbery ([EMAIL PROTECTED]) 

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

   http://archives.postgresql.org

Re: [GENERAL] 24x7x365 high-volume ops ideas

[Christopher Browne]

A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Karim Nassar) 
wrote:
> On Wed, 2004-11-03 at 18:10, Ed L. wrote:
>> unfortunately, the requirement is 100% uptime all the time, and any 
>> downtime at all is a liability.  Here are some of the issues:
>
> Seems like 100% uptime is always an issue, but not even close to
> reality. I think it's unreasonable to expect a single piece of
> software that NEVER to be restarted. Never is a really long time.
>
> For this case, isn't replication sufficient? (FWIW, in 1 month I
> have to answer this same question). Would this work?
>
> * 'Main' db server up 99.78% of time
> * 'Replicant' up 99.78% of time (using slony, dbmirror)
> * When Main goes down (crisis, maintenance), Replicant answers for Main,
>   in a read-only fashion.
> * When Main comes back up, any waiting writes can now happen.
> * Likewise, Replicant can be taken down for maint, then Main syncs to it
>   when going back online.
>
> Is this how it's done?

The challenge lies in two places:

1.  You need some mechanism to detect that the "replica" should take
over, and to actually perform that takeover.

That "takeover" requires having some way for your application to
become aware of the new IP address of the DB host.

2.  Some changes need to take place in order to prepare the "replica"
to be treated as "master."

For instance, in the case of Slony-I, you can do a fullscale
"failover" where you tell it to treat the "main" database as being
dead.  At that point, the replica becomes the master.  That
essentially discards the former 'master' as dead.

Alternatively, there's a "MOVE SET" which is suitable for predictable
maintenance; that shifts the "master" node from one node to another;
you can take MAIN out of service for a while, and add it back, perhaps
making it the "master" again.

None of these systems _directly_ address how apps would get pointed to
the shifting servers.

A neat approach would involve making pgpool, a C-based 'connection
pool' manager, Slony-I-aware.  If it were to submit either MOVE SET or
FAILOVER, it would be aware of which DB to point things to, so that
applications that pass requests through pgpool would not necessarily
need to be aware of there being a change beyond perhaps seeing some
transactions terminated.  That won't be ready tomorrow...

Something needs to be "smart enough" to point apps to the right place;
that's something to think about...
-- 
let name="cbbrowne" and tld="linuxfinances.info" in String.concat "@" 
[name;tld];;
http://www3.sympatico.ca/cbbrowne/advocacy.html
"XFS might  (or might not)  come out before  the year 3000.  As far as
kernel patches go,  SGI are brilliant.  As far as graphics, especially
OpenGL,  go,  SGI is  untouchable.  As  far as   filing  systems go, a
concussed doormouse in a tarpit would move faster."  -- jd on Slashdot

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.

[Marc G. Fournier]

On Sun, 7 Nov 2004, Russ Allbery wrote:
There are a couple of things that would be ideal to fix.  One is that 
currently the gateway isn't rewriting message IDs, which means that if 
anyone else gates the same mailing lists into some other group, the 
posts will conflict and posts will randomly disappear.  This isn't a 
huge issue, but it would be nice to fix it, since this is a common 
problem.
We are doing that specifically for that reason ... In order to provide 
redundancy, we currently have two mail<->news gateways of the mailing 
lists in place, and the MessageIds are what prevents duplicates ...

The other one is that right now the newsgroup and the mailing list get
different traffic since posts are only gated to the mailing list if the
person is already a member.  This can cause confusion (like only half of a
thread being seen on the mailing list).  The ideal way to fix this is to
make the newsgroup moderated (which also simplifies the whole process
since then you don't need any news to mail gateway).  That way, things can
be set up so that only the messages that make it to the list make it to
the newsgroup.
ppl keep saying this, but that is not how the groups are setup ... if 
someone isn't subscribed to the list, the message goes to the mailing list 
moderator (me) to approve to the list ... the only thing that doesn't go 
to the lists is spam ...


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster