[GENERAL] Read-only mode

[Alla]

Is it possible to run PostgreSQL in read-only mode?

For executing some maintenance procedures, I need to move database in
and out of the read-only mode

Is it possbile in PostgreSQl and if yes, how would I do it

Thanks in advance

---(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] help with a rule...

[Fabrizio Mazzoni]

Hi all...i'm trying to make a view i created in postgres editable...
I was trying with a rule like this one:

create rule test_rl as on
insert to my_view do instead
insert to my_real_table;

but it doesn't seem to work...
Basically i need that the whole view must be editable..

Thanks for your suggestions..!

Bye!

[EMAIL PROTECTED]
http://macrongolf.com
http://eteampoint.com
http://macron.com


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

[GENERAL] Re: Trigger with current user

[Nils Zonneveld]

Nicolas Kowalski wrote:
> 
> Hello.
> 
> We use PostgreSQL 7.1.2 on Debian GNU/Linux as our Intranet database. I
> am currently working on a small mailing-lists management application. I
> would like to enforce table access permissions depending on the current
> username :
> 
> - if the current user is declared as one of the lists maintainers
> ("SELECT maintainer FROM sys_mailing_lists;"), he(she) will be able to
> SELECT, INSERT, UPDATE & DELETE rows in the 'sys_mailing_members' table,
> 
> - if not he(she) will only be able to do SELECT's on
> 'sys_mailing_members'.
> 
> So, I thought using triggers. However, I am missing some elements :
> 
> - how can I get back the currently connected username ?
> 
> - when using a "BEFORE" trigger, how can I cancel the
> INSERT/UPDATE/DELETE actions to be performed if the user connected does
> not match the access permissions ?
> 

Maybe you could use the PostgreSQL user system instead of triggers and
create PostgreSQL users. For every user you can GRANT or REVOKE rights
on tables and sequences. Everything is in detail at 
http://www.postgresql.bit.nl/users-lounge/docs/7.1/admin/user-manag.html

HTH,

Nils

-- 
Alles van waarde is weerloos
Lucebert

---(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] postgres slower than grep?

[Spiros Ioannou]

Hello all,

before you start reading, have in mind that this is not post to advertise 
one db over another, I just want to know what happens, that's why I did 
the comparisons with other db.
to the point:

This is my table:

   Table "table1"
 Attribute |Type | Modifier 
---+-+--
 ClientID  | integer | 
 firstname | varchar(5)  | 
 surname   | varchar(22) | 
 area  | varchar(3)  | 
 neigh | varchar(27) | 
 dimos | varchar(50) | 
 tel   | varchar(7)  | 

The Facts:
--
The table exported to a text file is about 330MB, and contains about 5
milion lines.

The same table takes 670MB in postgres. Why? 
(I tried mysql, and it uses 340MB).

issuing the following query:
select * from table1 where tel='7485842';
takes about 1min and 40 seconds to complete. (query has one result)

explain of the above returns:
Seq Scan on table1  (cost=0.00..147835.01 rows=23 width=76)

issuing a grep in the text file takes about 25 seconds! *(for the whole grep 
to finish - worse case)*

issuing the same query in mysql takes about 25 seconds.

-to test the disk speed, I doubled the size of the text file by copying 
 it twice and the same grep query took 51 seconds (as expected)

when creating an index the query completes of course in no time

yes , i tried vacuum

Postgres is version 7.0
system is solaris 2.7
hard disk is 1rpm, ultraSCSI
cpu is UltraSparcIIi 333Mhz
physical memory is 384MB

and now the questions
-
-shouldn't postgres be at least as fast as the grep?
-shouldn't the table data file be smaller to reduce disk activity? Why is it
 double as the mysql same data file or the text file? I also noticed that 
 an index file for column "tel" is about 130MB large and the mysql's one 
 was 64MB, is this normal?



Thank you for your time,

Spiros  Ioannou 
e-mail:[EMAIL PROTECTED] 
---
Image Video & Multimedia Systems Lab.   
Department of Electrical & Computer Eng.
National Technical University of Athens

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

[GENERAL] Problem with postgres user

[Ligia Pimentel]

We have a weird problem.

When we try to update a table, we get this message




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

[GENERAL] upgrading postgres from 7.0 to 7.1

[Pieter Vissenberg]

Dear all,

I upgraded my PostgreSQL installation using Redhat's RPM.
I believe the start up script in /etc/rc.d/init.d has not
been changed.
The postmaster seems to be running smoothly (psql session
works fine).
However when I try to connect to the database via Apache
with PHP, I get an errormessage in the style of
'pconnect() failed. Is the postmaster (with -i) running
and listening to port 5432 ?'

When I run phpinfo(), Psql is still mentionned, so I did
not install any new version of PHP, or PHP-pgsql*.rpm.

Has anyone experienced the same problem? How could I solve
this?

many thanks,

Pieter Vissenberg

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

[GENERAL] write the output from a function to a file

[Vijayanath]

Hi,

Can I write a function that can write the output to the OS(Linux OS) file.

If there is a way to do it in Postgresql It would be great.

We are using Postgresql 7.1 running in Linux Redhat 7.1.

Thanks in Advance.

Vijay



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

[GENERAL] Parsing error

[Nicolas Drapeau]

Hi every one

I have a big problem, I think it's a syntaxical one, but can't solve it, 
please help !

What I am trying to do is a simple function who updates data in a table and 
if no row was updated, then add a new one with the specified parameters.
And i would like to do it using SQL and anything else.

Below is the screen shot from psql (I run postgresql-7.1.2-4PGDG ) 

[~/pgsql/test]$ psql ze_database ze_user
Welcome to psql, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help on internal slash commands
   \g or terminate with semicolon to execute query
   \q to quit
 
ze_database=> CREATE TABLE my_table (
ze_database(> p1 integer,
ze_database(> p2 integer,
ze_database(> p3 integer,
ze_database(> p4 integer);
CREATE
ze_database=> CREATE FUNCTION my_add(integer,integer,integer,integer)
ze_database-> RETURNS integer
ze_database-> AS 'INSERT INTO my_table VALUES ($1,$2,$3,$4); SELECT 1'
ze_database-> LANGUAGE 'sql';
CREATE
ze_database=> CREATE FUNCTION my_update(integer,integer,integer,integer)
ze_database-> RETURNS integer
ze_database-> AS 'SELECT CASE WHEN UPDATE my_table SET p1= $1, p2= $2 WHERE 
p3= $3 AND p4= $4 ~* \'UPDATE 0\' THEN my_add($1,$2,$3,$4) ELSE 1 END'
ze_database-> LANGUAGE 'sql';
ERROR:  parser: parse error at or near "my_table"
ze_database=>

I don't see my error, can anyone open my eyes and correct me, please help

Thanks in advance


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

[GENERAL] Postgresql revisited. Some questions about the product

[Andrew Mayo]

Some time ago I posted to comp.databases a list of requirements which
IMHO any RDBMS product must meet to be generally useful in commercial
applications.

I got some responses back regarding Postgresql but a lot of
improvements have since been made, so I am reposting the original list
of questions and wondering if anyone out there would be able to
provide up-to-date answers on them.

I think a lot of people are interested in PG given the recent Red Hat
announcement, so this is a good time to re-evaluate the product.

Questions:-

1. Does it support the full ANSI-92 SQL syntax especially left, right
outer join functionality. If not, does it even support outer joins?

2. Is there full support for declarative constraints including
primary,
unique, foreign key, and check constraints? Does it support indexes
and
if so, just b-tree or does it support bit and hash indices.

3. Does it support ALTER TABLE ... DROP , ALTER TABLE  ADD (and, a
la SQL Server 7) ALTER TABLE ... MODIFY?. (the last option can
actually change a column datatype without destroying data - very nice)

4. If there are significent SQL limitations, what are they. For
instance, MySQL fails to support correlated subqueries (can they
*really* call it an RDBMS, I wonder, given this). Does Postgresql
support this. As a general rule of thumb, would Joe Celko's "SQL for
Smarties" queries, which push standard SQL to the limits, work on
Postgresql - they wouldn't on MySQL. 

(an example of the sort of queries I mean may be found at
http://www.sys-con.com/pbdj/source/196/celko.htm)

5. How solid is the ODBC driver and can database management tasks such
as creating a database be handled programmatically through it. What
ODBC level does the driver conform to (e.g level 2, level 3).

6. Can databases be partitioned over multiple physical files. Can
multiple databases share a single file. Can a database be mounted on a
read-only medium such as a CDROM?

7. Does it run cleanly on NT or just Unix; are there any significant
limitations under NT.

8. Is there a stored procedure language?. Can Java be used as in
Oracle, for instance?. (i.e can you write stored procedures in Java?)

9. Can you easily import and export data via flat files - i.e, with
bcp-
like tools or are you on your own?

10. Does it support Unicode. If not, does it support locale-specific
collation sequences and/or sort orders. If so, can you restore
databases across locale boundaries i.e created under one locale,
restored under another (SQL Server can't do this).

11. Can you ask it to explain optimiser choices and show query
processing statistics, and/or use hints to override them.

12. Are there a reasonable range of coercion functions etc. that can
be
used in SQL (as in, for instance, SQL Server's string functions etc)

13. Are there tools to check and/or repair a corrupt database.

14. Does it support triggers. If so, are there any significant
limitations?

15. Do you have control over transaction logging e.g turn it off for
bulk copy operations etc. Can this be done programmatically.

16. Are there facilities for monitoring database activity e.g open
transactions, deadlocks etc.

17. Can you do hot backups.

18. What is the granularity of locking (page/row) or can you do what
Oracle does, where repeatable reads are possible even when
transactions
are open against a database. Can you set lock timeouts?

Without all these features it's a useful product but not a replacement
for any of the standard commercial RDBMS products, no matter how
elegant it might be. Any thoughts, PostgresGurus?

---(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] Function Won't Compile

[Eric Chacon]

I'm sure I'm doing something stupid, but this is driving me nuts...

This is the first stored procedure I've ever written in my life.

I have a database calle

jags_content

jags_content has a table in jags_content called 

update_flag

update_flag has a column of type timestamp called

content

So far, so good


I have a file with the following contents:

DROP FUNCTION update_flag_func();
CREATE FUNCTION update_flag_func()
RETURNS text
AS 'UPDATE update_flag SET content=current_timestamp;'
LANGUAGE 'plpgsql';
 
SELECT update_flag_func(); 

When I run the 'UPDATE update_flag SET content=current_timestamp' in
the SQL window (pgaccess), it works.

When I type
psql -f udt jags_content

I get an error...

DROP
CREATE
psql:udt:7: NOTICE:  plpgsql: ERROR during compile of update_flag_func
near line 1
psql:udt:7: ERROR:  parse error at or near "UPDATE"   

What obvious thing am I missing?

I just upgraded to postgres (7.1.??), I'm running Linux, and
everything else seems to be working (I can run JDBC queries, no
problem...)

Help!

Cheers,
Eric

---(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] Why is it not using the other processor?

[Alex Pilosov]

On Thu, 5 Jul 2001, Tom Lane wrote:

> Ryan Mahoney <[EMAIL PROTECTED]> writes:
> > Re: killing a process from browser, I don't think what you're trying to do 
> > is really possible.
> 
> If the client-side code were programmed to send a Cancel request to the
> backend when the user loses interest, then the right things would
> happen.  I am not sure how practical that is though; does the web server
> even find out about it when the user presses Stop in a typical browser?
> (If not, you can hardly expect Postgres to somehow intuit what happened
> two protocols away ;-).)

Webserver definitely finds out. (Socket gets closed by client). The real
question is, how does webserver signal this fact to a
CGI/mod_perl/jsp/whatever web application. For CGI, _i believe_ the
standard is that webserver will SIGHUP the application, and app can do
whatever cleanup it needs. For other interfaces, I really don't know.

-alex



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

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

[GENERAL] Large table load (40 millon rows) - WAL hassles

[simon lai]

Version: Postgres 7.1.2

A product we are developing requires frequent loading
of a large number of rows into a table.  We are using
the "copy file" command, but with WAL we are effectively
doubling the amount of disk writing we are doing.

After the rows are loaded we do a "create index".

Is there a way to turn off WAL or otherwise speeding up
the table loading process? Would db_restore be faster?

The rows are loaded in sorted order.  Does this impact
index creation negatively or positively?

We are currently working with test data but we estimate
production data to be 6 - 9 billion rows.  Is anyone
else running with these volumes?

Simon

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

http://www.postgresql.org/search.mpl

[GENERAL] Re: MVCC article

[Lee Harr]

On Mon, 2 Jul 2001 15:04:41 + (UTC), Richard Huxton <[EMAIL PROTECTED]>:
> There is a brief description of PG's MVCC in Linux Gazette that people might
> find of interest. Written by Joseph Mitchell of Great Bridge
> 
> http://www.linuxgazette.com/issue68/mitchell.html
> 


Thank you. That is a very nice article.

Do any other RDBMS have MVCC?


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

[GENERAL] Online PostgreSQL Book, Administration Section

[pgsql-general]

Hello,

We have been putting a lot of work into Part IV of the book.
We would appreciate some fresh feedback on the chapters 9 and 10.

As always, the link:

http://www.opendocspublishing.com/entry.lxp?lxpe=92


Thanks!

OpenDocs

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

[GENERAL] A free ODBC client to use with any database.

[Petarian]

Hello,
Try WinSQL at http://www.indus-soft.com/winsql. It is absolutely FREE and does
not expire. It is only one file and does not come with any bulky DLLs. If you
don't like it, simply delete the file from your harddisk.

Features:
--

* Connect to any database through ODBC
* Syntax Highlighing for SQL scrips
* Database catalog
* SQL Wizard
* Insert/Update/Delete Wizards
* Reverse engineer any table by generating CREATE TABLE statement
* Publish data to HTML
* and much more...

All this is absolutely FREE. 

Thanks.

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

http://www.postgresql.org/search.mpl

[GENERAL] Databases in Belgium

[Nico Vaes]

Hello,

I'm looking for people in Belgium, who have any experience in working with
Databases, so we can exchange views and experiences. Do you know such people
(or maybe it's you), please contact me at [EMAIL PROTECTED] .

Greetings
Nico Vaes



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

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

[GENERAL] Re: Red Hat Database Development

[Rob van Nieuwkerk]

Patrick Macdonald <[EMAIL PROTECTED]> wrote:
>
>I would like to take this opportunity to introduce myself
>and the Red Hat Database development team to the PostgreSQL
>community.  We are pure engineering team (development, 
>test and technical writing) based at the Red Hat Canada 
>engineering office in Toronto.  
>  
>We have been investigating the PostgreSQL code for several
>months and are very excited about working with the community.
>We are currently evaluating the TODO list to determine which
>items best suit our team size and skill set.  
> 
>If you have any questions, comments or concerns, feel free
>to contact me directly or through the newsgroups.  We look
>forward to becoming involved with the PostgreSQL community.
>
>Cheers,
>
>Patrick
>---
>Patrick Macdonald
>Red Hat Canada

Hi Patrick,

Good to hear this !

And I'll abuse this opportunity to ask for the thing we miss most in
PostgreSQL: database replication (just simple master-slave will do).

As a matter of fact it is the *only* thing we miss in PG.  It would be
really cool if progress could be made in this area !
(yes we know about rserv and several other things)

Friendly Greetings,
Rob van Nieuwkerk

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

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

[GENERAL] 2 gig file size limit

[Naomi Walker]

If PostgreSQL is run on a system that has a file size limit (2 gig?), where 
might cause us to hit the limit?
--
Naomi Walker
Chief Information Officer
Eldorado Computing, Inc.
602-604-3100  ext 242 


---(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] Inner Join?

[Jeff Rhys-Jones]

Hi there - we've hit a bit of a brick wall with this and I was
wondering if someone could help us out. Our MS-SQL code is this :

SELECT "Site"."Name" as "SiteName", "Site"."Description" as
"SiteDescription",
 "Site"."DefaultStyle", "Site"."PageWidth",
 "Site"."Tel", "Site"."Fax", "Site"."Email", "Site"."Web",
 "Site"."UserRegistration", "Site"."UserApprovalRequired", 
"Site"."DefaultSecurity", "Site"."GrantedSecurity",
"Site"."KeyWordMeta",
"Site"."DescriptionMeta", "Site"."WhatsNewTF", "Site"."RegIntro", 
"Site"."RegConfirm", "AppUser"."Email" as "SecurityContactEmail", 
"Site"."TimeZone", "Site"."Logo", "Site"."LogoWidth",
"Site"."LogoHeight",
"Site"."LogoBGColor", "Site"."Favicon", "Site"."PublishingTF",
"Site"."LastPublished", "Site"."MyMessageCodeField",
"Site"."ContactDisplay",
"Site"."SiteUIDCode"
FROM "Site","AppUser"
WHERE "Site"."SiteCode"=1
AND "Site"."SecurityContactCode" *= "AppUser"."UserCode"

And it's the "* =" at the very end of the table which is causing the
problem.

Our dev. guy has told me that "*=" in MS speak is INNER JOIN. 

Can anyone out there help us. I have Momjians book here but I'm stuck.

Many thanks,

Jeff

---(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] libssl.so.0, libcrypto.so.0 needed when installing RPM 7.1.2 on RH 6.2?

[Giovanni B. Stefanoni]

I downloaded postgresql RPM 7.1.2 from postgresql.org, binary version built
for Redhat 6.2.
When installing the package rpm reports a dependency error, not finding
libssl.so.0 & libcrypto.so.0.

where can i find these two libraries? None of packages in RH 6.2 CD contains
that.

thank you.





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

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

Re: [GENERAL] index skipped in favor of seq scan.

[Tom Lane]

[EMAIL PROTECTED] writes:
> On the other end of the spectrum there are many addresses with only one 
> entry.  When I use one of these addresses in the WHERE clause it takes 
> just as long as the address with 150k rows.  If the sequential scan is 
> better for 150k rows out of 800k rows, what about 1 out of 800k?  It 
> seems that when my table grew to this size the index was no longer used. 

The problem is that the 150k-duplicates value is dominating the
planner's rather inadequate statistics, and causing it to believe that
the table contains only a few values that all occur many times.  If that
were the true scenario then the use of seq scan would be the correct
choice.

This is fixed (I hope) for 7.2, but there's not much to be done about
it in current releases, unless you can avoid storing the 150k-duplicates
value.  Is that a real value, or just a dummy?  If you could replace it
with NULL then the right things would happen, because the statistics do
already distinguish NULL from regular data values.

regards, tom lane

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

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

Re: [GENERAL] [PATCH] Partial indicies again

[Martijn van Oosterhout]

On Tue, Jul 10, 2001 at 04:47:49PM +0200, Peter Eisentraut wrote:
> Martijn van Oosterhout writes:
> 
> > Well, getting closer. Maybe I should start version numbering the patches?
> 
> For one thing, you might want to post them to pgsql-patches instead.  And
> you should start generating the patches against the 7.2devel sources
> because I already suspect yours generating conflicts.

Well, Tom Lane certainly keeps pointing out problems with it. I'm pulling
out the latest CVS to see if there are going to be any problems there.

> > * Updates to the documentation as well as comments within the code. If you
> >   see a spot I missed, let me know.
> 
> FYI, it's called partial "indexes".

Actually, it seems to depend on who you ask. Both spellings are scattered
throughout the documentation and the source. According to my dictionary,
both spellings are allowed.

> > * Make it pg_dump-able. I've tried to extract the expression out the of
> >   system tables by using stringToNode and deparse_expression but it doesn't
> >   seem to work. I keep getting the error: "get_names_for_var: bogus
> >   varlevelsup 0". Anyone know what's going on? See attachment <>.
> 
> It might be an option to store the unparsed condition in the system
> catalogs, similar to what is done with the default values (see
> pg_attrdef).

See my later email. This has been fixed already.

-- 
Martijn van Oosterhout <[EMAIL PROTECTED]>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.

---(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] Re: postgres slower than grep?

[Mitch Vincent]

> 1. I find about 50% database storage overhead in this case.  That's not
completely silly, considering this is structured data, but seems a little
high.  I don't know >the internal structures well enough to really see
what's happening.

Hmm, the PG docs say to expect data stored in the database to take up %600
(or so) more space..

see: http://postgresql.bteg.net/docs/faq-english.html#4.7

> 2. Why would it be faster than grep?  This has to match structured data,
in this case varchar, and not just bytes.  It has to worry about
transactions and logs, not >just a stream of data.  Besides, in my tests it
is not *that* slow (3 sec, compared with 1/2).  Dunno what's up with your
system.

Sure, I'd expect grep to find a string in a semi-large text file faster than
PostgreSQL -- there is a hell of a lot less overhead with grep! :-)

> 3. As you said: With an index it rocks, easily beating grep.  Use an
index - it's your friend :-)

Yep yep!

-Mitch


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

[GENERAL] TCL and encoding

[Fernando Schapachnik]

Hello:
I'm not able to get a proper encoding with TCL 8.3
and Postgres 7.1.2_2.

I've tried setting the DB enconding to UNICODE and still
doesn't work.

If I use TCL 8.0 the chars get stored properly on the DB but
are not shown correctly on PgAccess (they are shown in hexa).

I've seen similar questions in the archive but haven't found
a workable solution.

Any ideas?

Thanks and regards!


Fernando P. Schapachnik
Planificación de red y tecnología
VIA NET.WORKS ARGENTINA S.A.
[EMAIL PROTECTED]
Tel.: (54-11) 4323-3381

---(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] index skipped in favor of seq scan.

[ryan . a . roemmich]

On the other end of the spectrum there are many addresses with only one 
entry.  When I use one of these addresses in the WHERE clause it takes 
just as long as the address with 150k rows.  If the sequential scan is 
better for 150k rows out of 800k rows, what about 1 out of 800k?  It 
seems that when my table grew to this size the index was no longer used. 
 If that's true is there any point in having the index?

-Original Message-
From: peter.e [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 09, 2001 4:26 PM
To: ryan.a.roemmich
Cc: pgsql-general
Subject: Re: [GENERAL] index skipped in favor of seq scan.


[EMAIL PROTECTED] writes:

> I am working with putting syslog logs into a database, I'm parsing the
> logs and using the key information for my fields.  With my test data 
of
> ~200K rows the optimizer used my b-tree index that I created for an
> oft-used where clause.  When the table grew to over 800K rows the 
index
> was no longer used.  The field in question contains IP addresses, but
> uses varchar.  The values are _not_ unique.  One particular address 
has
> 150K entries.  How can I keep my where queries speedy?

For 150k out of 800k rows, a sequential scan is definitely the better
choice.  If you can prove otherwise, please post data.

For problems with the optimizer in general you should post the schema, 
the
queries, and the explain output.

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [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] [PATCH] Partial indicies again

[Peter Eisentraut]

Martijn van Oosterhout writes:

> Well, getting closer. Maybe I should start version numbering the patches?

For one thing, you might want to post them to pgsql-patches instead.  And
you should start generating the patches against the 7.2devel sources
because I already suspect yours generating conflicts.

> * Updates to the documentation as well as comments within the code. If you
>   see a spot I missed, let me know.

FYI, it's called partial "indexes".

> * Make it pg_dump-able. I've tried to extract the expression out the of
>   system tables by using stringToNode and deparse_expression but it doesn't
>   seem to work. I keep getting the error: "get_names_for_var: bogus
>   varlevelsup 0". Anyone know what's going on? See attachment <>.

It might be an option to store the unparsed condition in the system
catalogs, similar to what is done with the default values (see
pg_attrdef).

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter


---(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] any disadvantage of PostgeSQL on Windows

[Tom Lane]

"Woo Weng Kong" <[EMAIL PROTECTED]> writes:
> Would appreciate a lot if anyone could tell me if there is any
> disadvantage/performance issue in using PostgeSQL on windows.

I wouldn't recommend such a setup for a production server; the
reliability is just not up to par.  (Use Postgres on almost any
flavor of Unix, instead.)  For development, it reportedly works
fine.

regards, tom lane

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

Re: [GENERAL] Performance tuning for linux, 1GB RAM, dual CPU?

[Philip Molter]

On Tue, Jul 10, 2001 at 07:44:34AM -0400, Adam Manock wrote:
: Hi,
: 
: I am about to put a 7.1.2 server into production on RedHat 7.1
: The server will be dedicated to PostgreSQL, running a bare minimum of 
: additional services.
: If anyone has already tuned the configurable parameters on a dual PIII w/ 
: 1GB RAM then I
: will have a great place to start for my performance tuning!
: When I'm done I'll be posting my results here for the next first timer that 
: comes along.

I have a similar system.  It's a dual PII-450MHz Xeon with 512MB of RAM
running RH7.1 and 7.1.2.  As far as performance tuning goes, here's the
relevant lines from the postgresql.conf file we're using:

  max_connections = 64 # 1-1024
  sort_mem = 8192
  shared_buffers = 192
  fsync = false

Obviously, depending on your needs, you can adjust those.  If you've
got a 1GB of RAM, I'd set everything high and not worry about it.

* Philip Molter
* DataFoundry.net
* http://www.datafoundry.net/
* [EMAIL PROTECTED]

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

http://www.postgresql.org/search.mpl

Re: [GENERAL] [PATCH] Partial indicies again

[Tom Lane]

Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> To actually be able to use ExecInsertIndexTuples, you need to create an
> EState, a ResultRelInfo and put each tuple in a TupleSlot for a while. Does
> it sound like I'm on the right track? That's quite a few changes.

Right.  I'd recommend copy.c as a model.

> Isn't someone else playing with the vacuum code for 7.2 anyway (for
> background vacuums)? We'd better make sure we don't clash.

That would be me.

> Back to other issues. pg_dump now works for partial indicies, as long as the
> pg_get_expr function is defined. To make that an internal function I have
> add it to pg_proc.h and initdb again, right?

Right.  I'd suggest sticking the source in ruleutils.c.

regards, tom lane

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

http://www.postgresql.org/search.mpl

Re: [GENERAL] Performance tuning for linux, 1GB RAM, dual CPU?

[Janning Vygen]

Am Dienstag, 10. Juli 2001 13:44 schrieb Adam Manock:
> Hi,
>
> I am about to put a 7.1.2 server into production on RedHat 7.1
> The server will be dedicated to PostgreSQL, running a bare minimum of
> additional services.
> If anyone has already tuned the configurable parameters on a dual PIII w/
> 1GB RAM then I
> will have a great place to start for my performance tuning!

i am running the same hardware and postgresql 7.1.2
but i didnt tuned it because its fast enough for my purpose. But i am very 
interested in your investigations. Could you please pm me, if you have 
something of interest?

thanks
janning

> When I'm done I'll be posting my results here for the next first timer that
> comes along.
>
> Thanks in advance,
>
> Adam
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

-- 
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf

fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de

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

http://www.postgresql.org/search.mpl

[GENERAL] editor with syntax-highlighting for PL/PGSQL

[Markus Jais]

hi,
is there a editor out there with syntax highlighting for
PL/PGSQL. preferable Vim oder Xemacs.

I am using Xemacs with sql-mode, but this is only for
generic SQL AFAIK.

thanks a lot for any hint!
markus
-- 
Markus Jais
http://www.mjais.de
[EMAIL PROTECTED]
The road goes ever on and on - Bilbo Baggins

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

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

Re: [GENERAL] Known problem with HASH index?

[Neil Conway]

> Is there a known problem with HASH type index in PostgreSQL 7.1.2
> 4PGDG on Red Hat Linux 7.1 (2.4.2 kernel)?  I can't find a lot of
> documentation, but this is what I observe:

Tom Lane has mentioned several times that index types other than BTREE 
have suffered some bit-rot over the past few years, and probably have 
problems with concurrent access -- so using a BTREE is recommended 
whenever possible. It would be cool if someone felt like taking the 
time to clean up HASH indexes though...

> [playpen]$ dropdb test; createdb test; psql -f create_table.sql
> test; psql -c "COPY clients FROM '/tmp/input.txt';" test; psql -c
> 'CREATE INDEX clients_idx ON clients USING HASH (tel);' test;
> vacuumdb test; vacuumdb --analyze test DROP DATABASE

Doing vacuumdb followed by vacuumdb --analyze is redundant; doing a 
VACUUM ANALYZE also does a regular VACUUM.

> NOTICE:  Index clients_idx: NUMBER OF INDEX' TUPLES (916864) IS NOT
> THE SAME AS HEAP' (100).
>  Recreate the index.

That's strange... Personally, I'd just switch to a BTREE, where 
presumambly this won't occur. However, I'd suggest waiting for one of 
the Postgres hackers to give you a proper answer ;-)

> [playpen]$ cat create_table.sql
> CREATE TABLE clients (
>ClientID integer,
>firstname varchar(5),
>surname varchar(22),
>area varchar(3),
>neigh varchar(27),
>dimos varchar(50),
>tel varchar(7)

The missing ');' at the end is a typo, right?

> The input file is a bit big to include, but was created using this
> brain-damaged perl script (somebody please teach me how to do
> random letter strings :-))

Grab stuff from /usr/share/dict?

>srand(time||$$);

On modern Perls, the default srand() seed is more secure than this, I 
believe. (Although it doesn't matter for your script, of course)

Cheers,

Neil


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

Re: [GENERAL] [PATCH] Partial indicies again

[Martijn van Oosterhout]

On Mon, Jul 09, 2001 at 08:22:43PM -0400, Tom Lane wrote:
> AFAIR, cnfify doesn't modify its inputs.  But watch out for the
> difference between explicit and implicit ANDing.

OK, I'm pretty sure I got it right now.

> In practice, I seem to recall that VACUUM is broken for partial indexes
> anyway, specifically because it does not pay attention to partial-ness:

OK, I don't feel too confident playing with the vacuum code, seems to be a
real quick way to destroy your database. 

To actually be able to use ExecInsertIndexTuples, you need to create an
EState, a ResultRelInfo and put each tuple in a TupleSlot for a while. Does
it sound like I'm on the right track? That's quite a few changes.

Isn't someone else playing with the vacuum code for 7.2 anyway (for
background vacuums)? We'd better make sure we don't clash.

Back to other issues. pg_dump now works for partial indicies, as long as the
pg_get_expr function is defined. To make that an internal function I have
add it to pg_proc.h and initdb again, right?

http://svana.org/kleptog/pgsql/partial-indicies.patch
http://svana.org/kleptog/pgsql/expr.c
-- 
Martijn van Oosterhout <[EMAIL PROTECTED]>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.

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

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

[GENERAL] Re: [ADMIN] problem in compilation.

[Namrata]

Hi There,


1. We had to make the following changes to the configure script, which
was not otherwise recognizing the presence of some libraries.

The modifications in the configure script are the following

A. Removal of -Wl from LDFLAGS

B. Addition of an empty main function ( int main() { ; return 0; } )
while creating
conftest.* files for the following checks
1. zlib.h
2. crypt.h dld.h endian.h fp_class.h getopt.h ieeefp.h pwd.h sys/ipc.h
sys/pstat.h
sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/types.h sys/un.h
termios.h
kernel/OS.h kernel/image.h SupportDefs.h
3. netinet/in.h
4. netinet/tcp.h
5. string.h and strings.h
6. readline/readline.h readline.h

ii. The following change has been done in src/backend/catalog/Makefile
in the
rules for generating global.bki and template1.bki files
CPP='$(CPP) -E' instead of CPP='$(CPP)'

After these changes, the compilation went through properly, but we had
problems
while invoking initdb, as given below.

+ mkdir /export/home/users/postgres/data/base/1
+ [  = yes ]
BACKEND_TALK_ARG=-Q
BACKENDARGS=-boot -C -F -D/export/home/users/postgres/data -Q
FIRSTRUN=-boot -x1 -C -F -D/export/home/users/postgres/data -Q
+ echo Creating template1 database in
/export/home/users/postgres/data/base/1
Creating template1 database in /export/home/users/postgres/data/base/1
+ [  = yes ]
+ cat /export/home/users/manoj/postgres/share/template1.bki
+ sed -e s/PGUID/1004/g
+ ./postgres -boot -x1 -C -F -D/export/home/users/postgres/data -Q
template1
DEBUG:  database system was shut down at 2001-07-09 11:28:13 IST
DEBUG:  CheckPoint record at (0, 8)
DEBUG:  Redo record at (0, 8); Undo record at (0, 8); Shutdown TRUE
DEBUG:  NextTransactionId: 514; NextOid: 16384
DEBUG:  database system is in production state
syntax error 1200 : -> \
ERROR:  pg_atoi: error in "-": can't parse "-"
+ exit_nicely
+ stty echo
+ echo

+ echo initdb failed.
initdb failed.
+ [  != yes ]
+ [ yes = yes ]
+ echo Removing /export/home/users/postgres/data.
Removing /export/home/users/postgres/data.
+ rm -rf /export/home/users/postgres/data
+ echo Removing temp file /tmp/initdb.7563.
Removing temp file /tmp/initdb.7563.
+ rm -rf /tmp/initdb.7563
+ exit 1


---

Thanks,
Namrata.

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

http://www.postgresql.org/search.mpl

[GENERAL] Performance tuning for linux, 1GB RAM, dual CPU?

[Adam Manock]

Hi,

I am about to put a 7.1.2 server into production on RedHat 7.1
The server will be dedicated to PostgreSQL, running a bare minimum of 
additional services.
If anyone has already tuned the configurable parameters on a dual PIII w/ 
1GB RAM then I
will have a great place to start for my performance tuning!
When I'm done I'll be posting my results here for the next first timer that 
comes along.

Thanks in advance,

Adam


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

http://www.postgresql.org/search.mpl