Re: [GENERAL] ISO week dates

[Peter Eisentraut]

Brendan Jurd wrote:
  * add an ISO day format pattern to to_char() called 'ID', which
 starts at Monday = 1, and
  * add an ISO year field to extract() called 'isoyear'?

That seems reasonable.  Do you volunteer?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

[GENERAL] time conversion fuinctions

[Rafal Pietrak]

Hi All,

Is this the expected result? 

The question particularly apply to the last SELECT. I'd expected it to
return boolean value just like in the second example below. It returns
nothing instead, and does not rise an error either. Is this the correct
behavior?

But also, is it correct for a function date(timestamp) to return one row
of *nothing*?

postgres v8.1.4
# SELECT timestamp 'today';
  timestamp  
-
 2006-10-07 00:00:00
(1 row)

# SELECT date(timestamp 'today')  date(timestamp 'yesterday');
 ?column? 
--
 t
(1 row)

# SELECT timestamp 'infinity';
 timestamp 
---
 infinity
(1 row)

# SELECT timestamp '-infinity';
 timestamp 
---
 -infinity
(1 row)

# SELECT date(timestamp 'infinity');
 date 
--
 
(1 row)

# SELECT date(timestamp '-infinity');
 date 
--
 
(1 row)

# SELECT date(timestamp '-infinity')  date(timestamp 'infinity');
 ?column? 
--
 
(1 row)
-END--



-- 
-R

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

   http://archives.postgresql.org

Re: [GENERAL] URGENT - startup process (PID 29541) was terminated by signal 6

[Emanuele Rocca]

Hello Tom,

* Tom Lane [EMAIL PROTECTED], [2006-10-06 15:16 -0400]:
  Um, were you running with full_page_writes off?  Bad idea in 8.1 :-( ...

The manual [1] says that full_page_writes is ignored and always treated
as if it was set to on. Is it wrong?

ciao,
ema

[1] 
http://www.postgresql.org/docs/8.1/static/runtime-config-wal.html#GUC-FULL-PAGE-WRITES


signature.asc
Description: Digital signature

Re: [GENERAL] failure to connect to postgres DB via pg.el

[Andreas Seltenreich]

Martin Steffen writes:

 worked fine for some years, only that when I changed recently to a new
 linux distribution (from Suse 10 to the latest Fedora core), things broke.
[...]
   - or postgres applies tougher access restrictions on 
  the new linux distribution, and therefore rejects me

  What is strange, however, is that I can access the
  running data base as user-name using the command-line
  tool psql just fine, without being rejected.
[...]
   signal(error (Backend error: FATAL:  Ident authentication failed for user 
 \user-name\\n))

Since pg.el only supports connections via tcp, a possible explanation
of psql's differing behavior could be that pg_hba.conf specifies
different authentication methods for unix domain and network sockets.

E.g., while SuSE used the trust method for local tcp connections,
Fedora might be using the ident method.

The pg_hba.conf file is documented here:
http://www.postgresql.org/docs/current/static/client-authentication.html

regards,
andreas

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

Re: [GENERAL] URGENT - startup process (PID 29541) was

[Bruce Momjian]

Emanuele Rocca wrote:
-- Start of PGP signed section.
 Hello Tom,
 
 * Tom Lane [EMAIL PROTECTED], [2006-10-06 15:16 -0400]:
   Um, were you running with full_page_writes off?  Bad idea in 8.1 :-( ...
 
 The manual [1] says that full_page_writes is ignored and always treated
 as if it was set to on. Is it wrong?

Well, early versions of 8.1.X did honor full_page_writes, but 8.1.4
disabled it.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] time conversion fuinctions

[Tom Lane]

Rafal Pietrak [EMAIL PROTECTED] writes:
 Is this the expected result? 

timestamp_date() currently returns NULL if the timestamp is infinity.
Since we don't have any representation for infinity in the date type,
this is pretty much its only alternative other than throwing an error
(which might indeed be a saner behavior).  There is a TODO item to
support +/-infinity in dates, which would be the correct long-term
solution.

regards, tom lane

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

Re: [GENERAL] time conversion fuinctions

[brian]

Tom Lane wrote:

Rafal Pietrak [EMAIL PROTECTED] writes:

Is this the expected result? 



timestamp_date() currently returns NULL if the timestamp is infinity.
Since we don't have any representation for infinity in the date type,
this is pretty much its only alternative other than throwing an error
(which might indeed be a saner behavior).  There is a TODO item to
support +/-infinity in dates, which would be the correct long-term
solution.



long-term, indeed.

b

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

[GENERAL] performace review

[Tomi NA]

I was just reading http://www.opencrx.org/faq.htm where RDBMS engines
are one of the questions and see pgsql bashed sentence after sentence.
Can anyone offer any insight as to weather it's fact or FUD?

t.n.a.

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

Re: [GENERAL] time conversion fuinctions

[Rafal Pietrak]

On Sat, 2006-10-07 at 11:57 -0400, Tom Lane wrote:
 Rafal Pietrak [EMAIL PROTECTED] writes:
  Is this the expected result? 
 
 timestamp_date() currently returns NULL if the timestamp is infinity.
 Since we don't have any representation for infinity in the date type,

I understand, you mean 'internal representation'. Since it's quite
obvious, that 'external representation' can be '+/-infinity', just like
it's the case with the timestamp.

 this is pretty much its only alternative other than throwing an error
 (which might indeed be a saner behavior).  There is a TODO item to
 support +/-infinity in dates, which would be the correct long-term
 solution.

I see. Provided the internal representation must change to get there,
some immediate measure (like faulting the statement) might be a good
idea ideed.
-- 
-R

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

   http://archives.postgresql.org

Re: [GENERAL] performace review

[Alexander Staubo]

On Oct 7, 2006, at 20:06 , Tomi NA wrote:


I was just reading http://www.opencrx.org/faq.htm where RDBMS engines
are one of the questions and see pgsql bashed sentence after sentence.
Can anyone offer any insight as to weather it's fact or FUD?


As with any use of a database, it is useless and/or impossible to  
evaluate blanket statements about performance without also evaluating  
the application's use of the database. One person's slow join may be  
another person's fast join.


Also, note how that the FAQ says that the default distribution of  
openCRX includes some indexes, implying that the schema must be  
adapted to one's database to achieve adequate performance. The FAQ   
does not mention whether the cited scaling limits include any  
attempts at tuning PostgreSQL.


I don't see PostgreSQL being bashed sentence after sentence,  
however -- the two known limitations listed for PostgreSQL are  
slow (even for small datasets) and jokes [sic] on 3-table-joins  
-- and among the open-source databases mentioned, PostgreSQL is  
described as scaling the highest.


With a running OpenCRX installation you could turn on PostgreSQL's  
query logging to identify slow queries or bad query patterns. I'm  
sure both the open-source communities would appreciate the feedback.


Alexander.


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

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

[GENERAL] increment row number function question

[Matthew Peter]

Hello. I need a way to return an iterator result as a column eg,SELECT increment() as ii, some_col from some_tbl order by some_col desc limit 50;ii | some_col+-- 1 | zest 2 | test 3 | nest 4 | fest[...]How can I acheive those results? Thanks for any help. 
		How low will we go? Check out Yahoo! Messenger’s low  PC-to-Phone call rates.

Re: [GENERAL] increment row number function question

[Tom Lane]

Matthew Peter [EMAIL PROTECTED] writes:
 Hello. I need a way to return an iterator result as a column eg,

 SELECT increment() as ii, some_col from some_tbl order by some_col desc limit 
 50;

  ii | some_col
 +--
   1 |   zest
   2 |   test
   3 |   nest
   4 |   fest
 [...]

You'd be a whole lot better off to attach the row numbers in your
client-side code.

Even if such a function existed, it would almost certainly not do what
you want in this query.  Per SQL spec, the SELECT target list is
logically supposed to be evaluated before the ORDER BY and LIMIT steps,
which means you'd get numbers associated with the physical ordering of
the rows not their some_col ordering.

You could possibly work around that problem with a sub-select, but at
some point you need to ask yourself whether it's not simpler to do a
presentation-oriented task like this in the client.

regards, tom lane

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

Re: [GENERAL] performace review

[Jonathan Vanasco]

On Oct 7, 2006, at 3:31 PM, Alexander Staubo wrote:

I don't see PostgreSQL being bashed sentence after sentence,  
however -- the two known limitations listed for PostgreSQL are  
slow (even for small datasets) and jokes [sic] on 3-table-joins  
-- and among the open-source databases mentioned, PostgreSQL is  
described as scaling the highest.


did you notice this line
---

*** Please note that we do not recommend PostgreSQL for production  
use. While PostgreSQL may be fine for many settings this DBMS simply  
does not deliver the performance required for openCRX (PostgreSQL  
takes minutes/hours to calculate 3-table-joins even for small data  
sets).


---

that sounds to me like someone never vacuumed and analyzed their db.


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

Re: [GENERAL] Sun Java Studio Creator and PostgreSQL

[Poul Møller Hansen]

Just because it works with Derby doesn't mean it's PostgreSQL's fault. 
There's plenty of things that work in certain databases that really 
shouldn't.


In any case, you might try asking on pgsql-jdbc, since there's more 
java-heads over there. I'd also recommend turning query logging on so 
you can see exactly what commands are being sent to the server.


I did try the pgsql-jdbc 
http://archives.postgresql.org/pgsql-jdbc/2006-09/msg00078.php
But ended up in not knowing if the problem is related to the 
CachedRowSetXImpl() class

or the jdbc driver.
And I don't know how to trace the error.

Any help is much appreciated.


Poul



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

Re: [GENERAL] performace review

[Richard Broersma Jr]

 did you notice this line
 ---
 
 *** Please note that we do not recommend PostgreSQL for production  
 use. While PostgreSQL may be fine for many settings this DBMS simply  
 does not deliver the performance required for openCRX (PostgreSQL  
 takes minutes/hours to calculate 3-table-joins even for small data  
 sets).
 
 ---
 
 that sounds to me like someone never vacuumed and analyzed their db.

If you look at the db comparison chart,  I think the only row that they got 
right for every RDMS
listed was FREE vs Commericial.  They suggest that postgresql and mysql can 
only handle 20
concurrent users and this low value doesn't seems realistic to me at all.

Regards,

Richard Broersma Jr.

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

Re: [GENERAL] Sun Java Studio Creator and PostgreSQL

[Poul Møller Hansen]

Mike Chiarappa skrev:

Hello Poul,
take a look to thread:
http://forum.sun.com/jive/thread.jspa?forumID=123threadID=101711

It works for me !!! :o))

Mike


  

Thanks Mike, had solved that issue the same way :)

Don't know how it works internally, but I guess the JDBC driver is 
reporting the isolation level wrong,
as the default setting on the app-server drivers default isn't 
working, but setting it to read comitted works.



Poul



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

Re: [GENERAL] performace review

[Brandon Aiken]

Title: [GENERAL] performace review



It wouldn't surprise me if their bashing were correct, but I doubt that it's PostgreSQL's fault.

I download the db source (inside opencrx-1.9.1-core.postgresql-8.zip) and executed their three schema files, dbcreate-indexes.sql, dbcreate-views.sql, dbcreate-tables.sql. Each of the 118 tables has a three-field composite primary key of 'PRIMARY KEY (object_rid, object_oid, object_idx)'. object_rid and object_oid are both VARCHAR(200). There are *no* foreign key constraints. Each table has between 15 and 50 fields, with 25 looking about average.

Gee, why to table joins take so long? Maybe because a blind monkey created the schema? Normalized databases do tend to perform better, so I hear.

Brandon Aiken


From: [EMAIL PROTECTED] on behalf of Tomi NASent: Sat 10/7/2006 2:06 PMTo: PgSQL GeneralSubject: [GENERAL] performace review

I was just reading http://www.opencrx.org/faq.htm where RDBMS enginesare one of the questions and see pgsql bashed sentence after sentence.Can anyone offer any insight as to weather it's fact or FUD?t.n.a.---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] performace review

[Alexander Staubo]

On Oct 7, 2006, at 23:44 , Brandon Aiken wrote:

I download the db source (inside opencrx-1.9.1- 
core.postgresql-8.zip) and executed their three schema files,  
dbcreate-indexes.sql, dbcreate-views.sql, dbcreate-tables.sql.   
Each of the 118 tables has a three-field composite primary key of  
'PRIMARY KEY (object_rid, object_oid, object_idx)'.  object_rid and  
object_oid are both VARCHAR(200).  There are *no* foreign key  
constraints.  Each table has between 15 and 50 fields, with 25  
looking about average.


To be fair, there are a bunch of indexes, but the number of indexes  
seems low compared to the number of fields.


Gee, why to table joins take so long?  Maybe because a blind monkey  
created the schema?  Normalized databases do tend to perform  
better, so I hear.


*De*normalization is the traditional hack to speed up queries,  
because it reduces the need for joins.


Alexander.



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

Re: [GENERAL] increment row number function question

[Jorge Godoy]

Matthew Peter [EMAIL PROTECTED] writes:

 Hello. I need a way to return an iterator result as a column eg,
 SELECT increment() as ii, some_col from some_tbl order by some_col desc limit 
 50;

Use a sequence for the iterator. 


-- 
Jorge Godoy  [EMAIL PROTECTED]

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

Re: [GENERAL] performace review

[Joshua D. Drake]

Tomi NA wrote:
 I was just reading http://www.opencrx.org/faq.htm where RDBMS engines
 are one of the questions and see pgsql bashed sentence after sentence.
 Can anyone offer any insight as to weather it's fact or FUD?

It is 100% FUD.

Joshua D. Drake


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


-- 

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



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

Re: [GENERAL] performace review

[Brandon Aiken]

Title: Re: [GENERAL] performace review



Denormalization should reduce the number of joins and reduce the overall number of tables, yes? And the idea is to fully normalize and then back off because of physical limitations in the database you're using *with full knowledge and understanding that you're sacrificing the relational model to do it*. They obviously did not do that. They just built a denormalized database.

Look atone of the views:

CREATE OR REPLACE VIEW kernel_view_027 AS (( SELECT 0 AS object_rid, act.object_oid, act.object_idx, act0."p$$assigned_to__rid" AS "p$$object_parent__rid", act0."p$$assigned_to__oid" AS "p$$object_parent__oid", act.created_at, act.modified_at, act.created_by, act.modified_by, act.object__class, act.access_level_browse, act.access_level_update, act.access_level_delete, act."owner", act.name, act.description, act.percent_complete, act.priority, act.due_by, act.scheduled_start, act.scheduled_end, act.actual_start, act.actual_end, act.misc1, act.misc2, act.misc3, act.activity_state, act.activity_number, act.severity, act.assigned_to, act."p$$assigned_to__rid", act."p$$assigned_to__oid", ( SELECT acc.full_name FROM kernel_account acc WHERE acc.object_rid::text = act."p$$assigned_to__rid"::text AND acc.object_oid::text = act."p$$assigned_to__oid"::text AND acc.object_idx = 0) AS "p$$assigned_to_title" FROM kernel_activity act LEFT JOIN kernel_activity act0 ON act0.object_idx = 0 AND act.object_rid::text = act0.object_rid::text AND act.object_oid::text = act0.object_oid::textUNION SELECT 0 AS object_rid, act.object_oid, act.object_idx, act0."p$$sender_parent__rid" AS "p$$object_parent__rid", act0."p$$sender_parent__oid" AS "p$$object_parent__oid", act.created_at, act.modified_at, act.created_by, act.modified_by, act.object__class, act.access_level_browse, act.access_level_update, act.access_level_delete, act."owner", act.name, act.description, act.percent_complete, act.priority, act.due_by, act.scheduled_start, act.scheduled_end, act.actual_start, act.actual_end, act.misc1, act.misc2, act.misc3, act.activity_state, act.activity_number, act.severity, act.assigned_to, act."p$$assigned_to__rid", act."p$$assigned_to__oid", ( SELECT acc.full_name FROM kernel_account acc WHERE acc.object_rid::text = act."p$$assigned_to__rid"::text AND acc.object_oid::text = act."p$$assigned_to__oid"::text AND acc.object_idx = 0) AS "p$$assigned_to_title" FROM kernel_activity act LEFT JOIN kernel_activity act0 ON act0.object_idx = 0 AND act.object_rid::text = act0.object_rid::text AND act.object_oid::text = act0.object_oid::text)UNION SELECT 0 AS object_rid, act.object_oid, act.object_idx, p0."p$$party__rid" AS "p$$object_parent__rid", p0."p$$party__oid" AS "p$$object_parent__oid", act.created_at, act.modified_at, act.created_by, act.modified_by, act.object__class, act.access_level_browse, act.access_level_update, act.access_level_delete, act."owner", act.name, act.description, act.percent_complete, act.priority, act.due_by, act.scheduled_start, act.scheduled_end, act.actual_start, act.actual_end, act.misc1, act.misc2, act.misc3, act.activity_state, act.activity_number, act.severity, act.assigned_to, act."p$$assigned_to__rid", act."p$$assigned_to__oid", ( SELECT acc.full_name FROM kernel_account acc WHERE acc.object_rid::text = act."p$$assigned_to__rid"::text AND acc.object_oid::text = act."p$$assigned_to__oid"::text AND acc.object_idx = 0) AS "p$$assigned_to_title" FROM kernel_activity act JOIN kernel_activityparty p0 ON p0.object_idx = 0 AND p0."p$$object_parent__rid"::text = act.object_rid::text AND p0."p$$object_parent__oid"::text = act.object_oid::text)UNION SELECT 0 AS object_rid, act.object_oid, act.object_idx, p0."p$$party_parent__rid" AS "p$$object_parent__rid", p0."p$$party_parent__oid" AS "p$$object_parent__oid", act.created_at, act.modified_at, act.created_by, act.modified_by, act.object__class, act.access_level_browse, act.access_level_update, act.access_level_delete, act."owner", act.name, act.description, act.percent_complete, act.priority, act.due_by, act.scheduled_start, act.scheduled_end, act.actual_start, act.actual_end, act.misc1, act.misc2, act.misc3, act.activity_state, act.activity_number, act.severity, act.assigned_to, act."p$$assigned_to__rid", act."p$$assigned_to__oid", ( SELECT acc.full_name FROM kernel_account acc WHERE acc.object_rid::text = act."p$$assigned_to__rid"::text AND acc.object_oid::text = act."p$$assigned_to__oid"::text AND acc.object_idx = 0) AS "p$$assigned_to_title" FROM kernel_activity act JOIN kernel_activityparty p0 ON p0.object_idx = 0 AND p0."p$$object_parent__rid"::text = act.object_rid::text AND p0."p$$object_parent__oid"::text = act.object_oid::text;
That's *horrible*. There's typecasting on the join conditionsto convert things to text! If there's a join on it, not only should they already be the same type, there ought to be a foreign key constraint on it (even if it is a self-referencing table). The silly thing UNIONs the exact same query four 

Re: [GENERAL] performace review

[Chris Browne]

[EMAIL PROTECTED] (Richard Broersma Jr) writes:
 did you notice this line
 ---
 
 *** Please note that we do not recommend PostgreSQL for production  
 use. While PostgreSQL may be fine for many settings this DBMS simply  
 does not deliver the performance required for openCRX (PostgreSQL  
 takes minutes/hours to calculate 3-table-joins even for small data  
 sets).
 
 ---
 
 that sounds to me like someone never vacuumed and analyzed their db.

 If you look at the db comparison chart, I think the only row that
 they got right for every RDMS listed was FREE vs Commericial.
 They suggest that postgresql and mysql can only handle 20 concurrent
 users and this low value doesn't seems realistic to me at all.

For heavy load, MySQL with myisam has often started really choking at
~10 concurrent users, so that part doesn't seem ludicrously
unrealistic.  (Somewhat off?  Perhaps.  Ludicrously so?  Not.)

It's possible that the last time they tried PostgreSQL was with
version 7.1 or 7.2, and things have really changed since then.

This could also be a situation where adding a few useful indexes might
fix a lot of ills.  Better to try to help fix the problems so as to
help show that the comparisons are way off base rather than to simply
cast stones...
-- 
output = (cbbrowne @ linuxdatabases.info)
http://linuxfinances.info/info/advocacy.html
Epistemology in One Lesson
Reality ruthlessly selects out creatures that embody hypotheses too
inconsistent with reality. Our only choice is whether we participate
by being selected out, or (in Popper's great phrase) by letting our
ideas die in our stead.
-- Mark Miller

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

[GENERAL] please how to unsuscribe to list

[Mario Soto]

--
cordialmente,

Mario Soto Cordones

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

Re: [GENERAL] increment row number function question

[Matthew Peter]

Thanks for the reply. <[EMAIL PROTECTED]><[EMAIL PROTECTED]>Even if such a function existed, it would almost certainly not do whatyou want in this query.  Per SQL spec, the SELECT target list islogically supposed to be evaluated before the ORDER BY and LIMIT steps, which means you'd get numbers associated with the physical ordering of the rows not their some_col ordering.<[EMAIL PROTECTED]>Shucks. I wanted to return a set with an ordered iterator determined by the ORDER BY. Could that be done?<[EMAIL PROTECTED]><[EMAIL PROTECTED]>   regards, tom
 laneThanks again. 
		Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.

[GENERAL] Problem with a date when restoring on postgresql 7.4.9 : date/time field value out of range

[Thomas Poindessous]

Hello,

I have a problem with my postgresql 7.4.9 server.

I tried to restore a dump on the backup server (same version).

I got this error :

pg_restore: ERROR:  date/time field value out of range: 0001-02-29 
00:00:00 BC


I understand this error, but I can't understand why postgresql didn't 
refused to insert it on the main server.


The faulty script is a php script which uses postgresql 7.4.13 library 
(on another server).


I don't think this error is corrected on 7.4.13 server (so even if I 
upgrade I will get this error).


How can I do to be sure that my dump can be restored without a problem ?

TIA.

--
Thomas Poindessous



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

  http://archives.postgresql.org

[GENERAL] How to force the parser to use index scan instead of sequential scan

[ck]

Hello,

 I am using PostgreSQL 7.3.2. I have a large table in a
database , its primary key field is int8.
Almost all of my queries are written relaing to that field. That field
is B-Tree indexed. When i check the query plan i found that all the
queries are using sequential scan and index was not used. So my queries
are likely to be slow. But when i tried to convert the values given to
that field to int8 in a where condition then the parser is using the
index scan, Eg
select * from h057 where h057001 = 1142::int8
the above query uses index scan
select * from h057 where h057001 = 1142
the above query uses sequential scan.
Since there are large number of queries written and being used in the
production it is impossible to change all the queries. Please help me
by giving a suggestion to improve my query performance. I mean any
configuration level changes that helps to force the parser to use index
scan instead of sequential scan in the above case.
Thanks in advance.

ck


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

[GENERAL] predefined functions

[Rafal Pietrak]

Hi,

Is there a reason why both:

SELECT current_user;
SELECT current_database();

are correct, while neither of:

SELECT current_user();  -- syntax at '('
SELECT current_database; -- missing column

is?

This is as of postgres version 8.1.4

-- 
Rafal Pietrak [EMAIL PROTECTED]

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

[GENERAL] EXECUTE command in stored procedure

[Arunagiri K]

Dear All,How can I use EXCEUTE command in stored procedures?When using like thisEXECUTE 'SELECT INTO REGNO REG_NO FROM ADMISSION WHERE SNAME = ' || STUDNAME ;STUDNAME is a local variable;
I am getting error like thisERROR: syntax error at or near INTO at character 8How can I solve this.Regards,Arunagiri.K

[GENERAL] failure to connect to postgres DB via pg.el

[Martin Steffen]

Hi,

I recently stumbled over the following problem, recently because the setup
worked fine for some years, only that when I changed recently to a new
linux distribution (from Suse 10 to the latest Fedora core), things broke.

The set-up is as follows:


I got a postgres data base running on a linux machine (the data base works
ok, it seems, as I can connect to it using the psql ``user interface'' to
interact with it).

I want to connect, however, via emacs, in particular, I used
to use the package 

 pg.el (Version: 0.10, 2002, it seems)

for it. Now, part of the proper use of it is establishing the
connection with the data base, which is done by:

(setq myconnection (pg:connect data-base-name user-name) )

Now: this connection does no longer work, i.e., I'm refused
access, giving back the following verdict appended below the end of
the article.

Does anybody know how to repair that?  

I'm not sure where the problem lies (as said, the set-up worked with the same
version of pg.el and basically the same emacs version before). There seem two 
possible
sources:

  - either emacs/pg does no longer transmit my name properly (I 
  remember I had problems with encodings some time ago)

  - or postgres applies tougher access restrictions on 
 the new linux distribution, and therefore rejects me


 What is strange, however, is that I can access the
 running data base as user-name using the command-line
 tool psql just fine, without being rejected.



Thanks, Martin




  signal(error (Backend error: FATAL:  Ident authentication failed for user 
\user-name\\n))
  error(Backend error: %s FATAL:  Ident authentication failed for user 
\msteffen\\n)

  byte-code(. process connection pg:SM_USER pg:SM_OPTIONS pg:SM_UNUSED 
generate-new-buffer  *PostgreSQL* nil open-network-stream postgres fboundp 
set-buffer-process-coding-system binary set-buffer-multibyte vector 
cl-struct-pgcon 1 pg:send-int 4 2 pg:send accept-process-output 0 error 
pgcon-process accessing a non-pgcon pg:read-char 69 Backend error: %s 
pg:read-string 4096 82 pg:read-net-int pg:initialize-parsers pg:exec SET 
datestyle = 'ISO' throw --cl-block-pg:connect-- 5 Crypt authentication not 
supported Kerberos4 authentication not supported Kerberos5 authentication 
not supported Can't do that type of authentication: %s Problem connecting: 
expected an authentication response pg:SM_TTY user-packet-length host port 
pg:StartupPacketSize pg:PG_PROTOCOL_63_MAJOR ...] 7)




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

Re: [GENERAL] failure to connect to postgres DB via pg.el

[Martin Steffen]

 Andreas == Andreas Seltenreich [EMAIL PROTECTED] writes:



Andreas E.g., while SuSE used the trust method for local tcp
Andreas connections, Fedora might be using the ident method.

yep, that was it!

Danke, Martin


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

Re: [GENERAL] increment row number function question

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/07/06 18:47, Matthew Peter wrote:
 Thanks for the reply. Even if such a function existed, it would 
 almost certainly not do what you want in this query.  Per SQL 
 spec, the SELECT target list is logically supposed to be 
 evaluated before the ORDER BY and LIMIT steps, which means you'd 
 get numbers associated with the physical ordering of the rows not
 their some_col ordering.

 Shucks. I wanted to return a set with an ordered iterator
 determined by the ORDER BY. Could that be done? regards, tom lane

A stored procedure should do the trick, no?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is common sense really valid?
For example, it is common sense to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that common sense is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFKEPRS9HxQb37XmcRAiRrAJ4vVxbhzwwH/8vWoZHH76lI4dT9kACg5zvH
ufGPSbGV0wHCvmkCPWjiI1U=
=PUKR
-END PGP SIGNATURE-

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

Re: [GENERAL] performace review

[Jonathan Vanasco]

On Oct 7, 2006, at 6:41 PM, Chris Browne wrote:

This could also be a situation where adding a few useful indexes might
fix a lot of ills.  Better to try to help fix the problems so as to
help show that the comparisons are way off base rather than to simply
cast stones...


i'm too tight for cash to afford being wrong right now...

but I'd otherwise bet that the issue was from not vacuum analyzing

i've routinely had 3,9,12, i think even a 14 table join that would  
take forever to run...


until i realized that i added/dropped an index and forgot to run  
analyze.  then they all work within a matter of split seconds. all of  
them.


i've seen not just dramatic, but drastic , changes in performance and  
the planner's output before and after a vacuum analyze of the db.


i'm really confident thats the problem.  unfortunately, they have a  
max_db contact email, and not a postgres.  so i don't know who to  
check with to see if they ran it or not.


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

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

Re: [GENERAL] How to force the parser to use index scan instead of sequential scan

[Jaime Casanova]

On 6 Oct 2006 22:32:16 -0700, ck [EMAIL PROTECTED] wrote:

Hello,

 I am using PostgreSQL 7.3.2. I have a large table in a


that's very old... you should upgrade at least to 7.3.15


database , its primary key field is int8.
Almost all of my queries are written relaing to that field. That field
is B-Tree indexed. When i check the query plan i found that all the
queries are using sequential scan and index was not used. So my queries
are likely to be slow. But when i tried to convert the values given to
that field to int8 in a where condition then the parser is using the
index scan, Eg
select * from h057 where h057001 = 1142::int8
the above query uses index scan
select * from h057 where h057001 = 1142
the above query uses sequential scan.


that's because in 7.3 you must cast to the type of the indexed column
in order to use the indexes...

had you never seen this?

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

upgrade

--
regards,
Jaime Casanova

Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning.
  Richard Cook

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

Re: [GENERAL] How to force the parser to use index scan instead of

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/07/06 21:10, Jaime Casanova wrote:
 On 6 Oct 2006 22:32:16 -0700, ck [EMAIL PROTECTED] wrote:
[snip]
 index scan, Eg
 select * from h057 where h057001 = 1142::int8
 the above query uses index scan
 select * from h057 where h057001 = 1142
 the above query uses sequential scan.
 
 that's because in 7.3 you must cast to the type of the indexed column
 in order to use the indexes...
 
 had you never seen this?
 
 TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

And the default data type for a scalar constant is int4.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is common sense really valid?
For example, it is common sense to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that common sense is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFKGD9S9HxQb37XmcRAk+qAJ9BdmEvE8Iug641O7XBnl/AAxiUwwCfWV3V
J1hBmh26MHOcAQ+Fur6EP2U=
=GOPt
-END PGP SIGNATURE-

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

Re: [GENERAL] EXECUTE command in stored procedure

[Guy Rouillier]

Arunagiri K wrote:
 Dear All,
 
 How can I use EXCEUTE command in stored procedures?
 When using like this
 
 EXECUTE 'SELECT INTO REGNO REG_NO FROM ADMISSION WHERE SNAME = ' ||
 STUDNAME ; 
 
 STUDNAME is a local variable;
 
 I am getting error like this
 
 ERROR:  syntax error at or near INTO at character 8
 
 How can I solve this.

You don't mention which version of PostgreSQL you are using, or which
stored procedure language.  I'll assume 8.1.x and PL/pgSQL.  See section
36.6 in the documentation.  Specifically, 36.6.5 says:

SELECT INTO is not currently supported within EXECUTE.

However, if I'm understanding what you are trying to do, you shouldn't
need the EXECUTE at all.  Doing a simple SELECT INTO should work for
you.  Note also that since you are only retrieving a single value, you
can also avoid INTO altogether with something like:

REGNO := (select regno from admission where sname = myname);

-- 
Guy Rouillier

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

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

Re: [GENERAL] Hi, For the UTF-8 encoding

[stevegy]

Hi Martijn, 

I had changedmy Solaris locale setting to zh_CN.UTF-8. I modified the /etc/default/init LANG=zh_CN.UTF-8 and reboot. initdb a new database used the encoding UTF8, but the sort order is wrong on the Solaris.

I dig into the TODO list on the postgreSQL web site. The Multi-Language Supportssection I found some resource like this: http://archives.postgresql.org/pgsql-hackers/2006-09/msg00662.php

I guess the encoding of UTF-8 support is not stable right now in PostgreSQL. But, on my windows box, 8.1.4 handle the utf8 sorting good. What can I do now? waiting for some kind of patch?


-- Steve Yao





-原始邮件-发件人:"Martijn van Oosterhout" 发送时间:2006-10-04 17:42:00收件人:"stevegy" <[EMAIL PROTECTED]>抄送:pgsql-general@postgresql.org主题:Re: [GENERAL] Hi, everOn Wed, Oct 04, 2006 at 07:50:12AM +0800, stevegy wrote:

 My testing database is initdb -E UTF8. And  i guess the solaris can

 not handle the UTF-8 sorting with the LC_COLLATE="zh_CN.GB18030". But

 i need to prove this.



A locale can only handle one charset, usually the one given by "locale

charset". So if it handles sorting in GB18030 then by definition it

can't handle UTF-8.



 So i plan to change the locale of my solaris box. I have installed

 the zh_CN.UTF8 on it. I am looking for a way to change the solaris

 locale. If this changing need to re-boot machine that I should do

 this in some not busy time for the live application.



The locale is not a global setting. If you set the LANG or LC_ALL

variable, it will change the locale of any program run with that

environment variable. The default is the "C" locale.



Have a nice day,

-- 

Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/

 From each according to his ability. To each according to his ability to litigate.



	美 女 看 了 就 想 要 ( 图 )
	
	 绝 对 另 类 ! 和 老 婆 长 假 期 间 的 败 家 记 录 ( 组 图 )

Re: [GENERAL] predefined functions

[Tom Lane]

Rafal Pietrak [EMAIL PROTECTED] writes:
 Is there a reason why both:
   SELECT current_user;
   SELECT current_database();
 are correct, while neither of:
   SELECT current_user();  -- syntax at '('
   SELECT current_database; -- missing column
 is?

Yeah: current_user (without the parens) is specified by the SQL
standard, but we're not about to adopt such a brain-dead syntax
for any of the functions defined by Postgres itself --- as you
can see from the error messages, allowing a function to be called
without parens creates a word that can't be used as a column name.
So if current_database could be called without parens, we'd actually
be violating the SQL spec by reserving a word that's not reserved
per spec.

Consistency is not one of the hallmarks of the SQL standard :-(

regards, tom lane

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