Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

[Thomas H.]

Have you run analyze on all the three tables since creating the
database?


yes. even a forced ANALYZE FULL after the table loads: the tables were 
TRUNCATE'd, refilled and ANALYZE FULL'ed some minutes before the tests. 
there where no UPDATEs after the INSERTs...


- thomas 




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

Re: [GENERAL] 8.1.2 postmaster died

[Shoaib Mir]

Have a look at "16.4.3. Linux Memory Overcommit" on http://www.postgresql.org/docs/8.1/interactive/kernel-resources.htmlThanks,
--Shoaib MirEnterpriseDB (www.enterprisedb.com)On 11/9/06, Talha Khan <
[EMAIL PROTECTED]> wrote:Hi Ed,I don't think its pgsql thats sending the SIGKILL its being done by the OS. The reason can only be confirmed after looking at your log files but the probability is that your OS ran out of memory and in order to protect itself it had to kill the processes that were taking a lot of memory thus ended up killing pgsql processes. Its just a hunch that i have but the reason can only be confirmed after seeing your logs.
RegardsTalha KhanOn 11/9/06, Ed Loehr <
[EMAIL PROTECTED]> wrote:
On Wednesday November 8 2006 12:30 pm, Shoaib Mir wrote:> To be specific you need to look at the syslogs to see if it> was actually some OOM killer or not.I checked all db logs + syslog, of course.  All they show is the
process receiving SIGKILL (followed by all others being shutdownas a result).Is there any circumstance at all under which pgsql itself wouldissue a sigkill to one of the backends?Ed

[GENERAL] reproducing this issue on PG 8.0.0 ERROR: index "patient_pkey" is not a btree

[surabhi.ahuja]

 hi
I am using Postgres 8.0.0 and we found this issue 
"ERROR:  index "patient_pkey" is not a btree"
I have been informed that we should shift to 
Postgres 8.0.9
 
I discussed this with my team member and they are 
asking if we can upgrade to the latest Postgres version
i.e. 8.1.5
 
I have some questions regarding this:
1.will this vesion solve the problem that I have 
mentioned?
2. If we install postgres 8.1.5 instead of Postgres 
8.0.0 I ll have to build my c++ application again right?
3. I am currently using 
postgresql-8.0-310.jdbc3.jar, for java applications. Would I have to change this 
jar as well? and if yes where can I find it?
4. the most important question is : Is there any 
test case that you ran in order to confirm that the above issue will not occur 
with PG 8.1.5. I need this test case that I can run in order to propose that we 
should upgrade to 8.1.5
5. Can you please provide a link to this Bug. I 
want to just see the proble, resolution, verification of this bug.
 
Thanks,
regards
Surabhi
 
 

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

[mike]

Have you run analyze on all the three tables since creating the
database? 

What On Thu, 2006-11-09 at 02:31 +0100, Thomas H. wrote:
> hi list.
> 
> as soon as i left-join an additional table, the query takes 24sec instead of 
> 0.2sec, although the added fields have no impact on the resultset:
> 
> 
> SELECT * FROM shop.dvds
> LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
> WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like 
> '%superman re%' OR lower(dvd_edition) LIKE '%superman re%')
> 
> Hash Left Join  (cost=8402.16..10733.16 rows=39900 width=1276) (actual 
> time=260.712..260.722 rows=2 loops=1)
>   Hash Cond: (dvds.dvd_mov_id = movies.mov_id)
>   Filter: ((lower((movies.mov_name)::text) ~~ '%superman re%'::text) OR 
> (lower((dvds.dvd_edition)::text) ~~ '%superman re%'::text))
>   ->  Seq Scan on dvds  (cost=0.00..1292.00 rows=39900 width=1062) (actual 
> time=0.036..23.594 rows=20866 loops=1)
>   ->  Hash  (cost=8194.93..8194.93 rows=82893 width=214) (actual 
> time=168.121..168.121 rows=37417 loops=1)
> ->  Seq Scan on movies  (cost=0.00..8194.93 rows=82893 width=214) 
> (actual time=0.024..131.401 rows=37417 loops=1)
> Total runtime: 264.193 ms
> 2 rows fetched
> 
> 
> now, an additional table (containing 600k records) is added through a left 
> join. all the sudden the query takes 24sec. although there are indices on 
> both dvds.dvd_ean and data_soundmedia.sm_info_ean, the planer does not make 
> use of the indices but rather chooses to do 2 seq-scans.
> 
> 
> SELECT * FROM shop.dvds
> LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
> LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean
> WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like 
> '%superman re%' OR lower(dvd_edition) LIKE '%superman re%')
> 
> Hash Left Join  (cost=317592.21..326882.92 rows=159086 width=1936) (actual 
> time=21021.023..22242.253 rows=2 loops=1)
>   Hash Cond: (dvds.dvd_mov_id = movies.mov_id)
>   Filter: ((lower((movies.mov_name)::text) ~~ '%superman re%'::text) OR 
> (lower((dvds.dvd_name)::text) ~~ '%superman re%'::text) OR 
> (lower((dvds.dvd_edition)::text) ~~ '%superman re%'::text))
>   ->  Merge Left Join  (cost=309190.05..313899.09 rows=159086 width=1722) 
> (actual time=19876.552..21902.007 rows=20866 loops=1)
> Merge Cond: ("outer"."?column20?" = "inner"."?column29?")
> ->  Sort  (cost=23027.68..23127.43 rows=39900 width=1062) (actual 
> time=507.886..520.143 rows=20866 loops=1)
>   Sort Key: (dvds.dvd_ean)::text
>   ->  Seq Scan on dvds  (cost=0.00..1292.00 rows=39900 
> width=1062) (actual time=0.047..100.415 rows=20866 loops=1)
> ->  Sort  (cost=286162.37..287781.38 rows=647601 width=660) (actual 
> time=19336.011..20328.247 rows=646633 loops=1)
>   Sort Key: (data_soundmedia.sm_info_ean)::text
>   ->  Seq Scan on data_soundmedia  (cost=0.00..31080.01 
> rows=647601 width=660) (actual time=0.074..2834.831 rows=647601 loops=1)
>   ->  Hash  (cost=8194.93..8194.93 rows=82893 width=214) (actual 
> time=177.033..177.033 rows=37417 loops=1)
> ->  Seq Scan on movies  (cost=0.00..8194.93 rows=82893 width=214) 
> (actual time=0.118..129.716 rows=37417 loops=1)
> Total runtime: 24419.939 ms
> 2 rows fetched
> 
> 
> shouldn't the planer join the additional table *after* filtering? even if it 
> does first joining then filtering, why isn't the existing index not used?
> 
> pgsql is 8.2beta2
> 
> thanks,
> thomas 
> 
> 
> 
> ---(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


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

   http://archives.postgresql.org/

[GENERAL] authentication question

[Craig White]

CentOS 4.4 which means postgresql-server-7.4.13-2.RHEL4.1

I'm starting to deal with the notion of allowing other users access
(read only) to a db.

Experimenting on my own db...

hostall main_user   192.168.2.10255.255.255.0   trust
hostall all 127.0.0.1   255.255.255.255 trust
hostall craig   192.168.2.10255.255.255.255 pam

because I want to use LDAP authentication via pam.

logs say...
Nov  8 20:18:26 srv1 postgresql: Starting postgresql service:  succeeded
Nov  8 20:18:39 srv1 postgres[21020]: PAM audit_open() failed:
Permission denied
Nov  8 20:18:39 srv1 postgres[21020]: [2-1] LOG:  pam_authenticate
failed: System error
Nov  8 20:18:39 srv1 postgres[21020]: [3-1] FATAL:  PAM authentication
failed for user "craig"

Below is pam info - if anyone can tell me how I might configure this so
I can authenticate via LDAP I would appreciate it.

Craig

# cat /etc/pam.d/postgresql
#%PAM-1.0
auth   required pam_stack.so service=system-auth
auth   required pam_nologin.so
accountrequired pam_stack.so service=system-auth
password   required pam_stack.so service=system-auth
sessionrequired pam_stack.so service=system-auth
sessionrequired pam_loginuid.so

which was cribbed from /etc/pam.d/sshd

# cat /etc/pam.d/system-auth
#%PAM-1.0
# This file is auto-generated.
# User changes will be destroyed the next time authconfig is run.
authrequired  /lib/security/$ISA/pam_env.so
authsufficient/lib/security/$ISA/pam_unix.so likeauth nullok
authsufficient/lib/security/$ISA/pam_ldap.so use_first_pass
authrequired  /lib/security/$ISA/pam_deny.so

account required  /lib/security/$ISA/pam_unix.so broken_shadow
account sufficient/lib/security/$ISA/pam_succeed_if.so uid < 100
quiet
account [default=bad success=ok
user_unknown=ignore] /lib/security/$ISA/pam_ldap.so
account required  /lib/security/$ISA/pam_permit.so

passwordrequisite /lib/security/$ISA/pam_cracklib.so retry=3
passwordsufficient/lib/security/$ISA/pam_unix.so nullok
use_authtok md5 shadow
passwordsufficient/lib/security/$ISA/pam_ldap.so use_authtok
passwordrequired  /lib/security/$ISA/pam_deny.so

session required  /lib/security/$ISA/pam_limits.so
session required  /lib/security/$ISA/pam_unix.so
session optional  /lib/security/$ISA/pam_ldap.so



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

Re: [GENERAL] Does PostgreSQL support multi-instancing?

[Christopher Browne]

After a long battle with technology, "Bill" <[EMAIL PROTECTED]>, an earthling, 
wrote:
> Is is possible to have two different versions of PostgreSQL running on
> the same computer at the same time?

Certainly.

You need separate binaries, separate data directories, separate port
configuration.

If you're up to compiling PostgreSQL from source, it's quite
straightforward to do this.  If not, you may find this sort of thing
somewhat more challenging...
-- 
"cbbrowne","@","gmail.com"
http://linuxdatabases.info/info/nonrdbms.html
Rules of  the Evil  Overlord #121.  "If I come  into possession  of an
artifact  which can only  be used  by the  pure of  heart, I  will not
attempt to use it regardless." 

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

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

Re: [GENERAL] Can PostgreSQL notify a client that a trigger has fired?

[Bill]

Jorge Godoy wrote:

> I have the impression that you're missing a lot of sections in the
> manual...  How about some time to re-read it?

I don't know about you but for me a 1500 page manual is at least two
weeks of full time reading. I have read several sections of it but I
am trying to decide if PostgreSQL should be considered for a project
and I don't have 80 hours to make the evaluation. As well indexed as
the manual is, there are some topics I cannot find without knowing the
PostgreSQL specific terminology. That's one of the great things about
newsgroups and mailing lists; you can get help with specific questions
from experienced users. I appreciate all the anwers I have received
here. They have made it possible for me to do a much better job in the
time available. My thanks to everyone.

-- 
.Bill.

---(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] planer picks a bad plan (seq-scan instead of index) when adding an additional join

[Thomas H.]

hi list.

as soon as i left-join an additional table, the query takes 24sec instead of 
0.2sec, although the added fields have no impact on the resultset:



SELECT * FROM shop.dvds
LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like 
'%superman re%' OR lower(dvd_edition) LIKE '%superman re%')


Hash Left Join  (cost=8402.16..10733.16 rows=39900 width=1276) (actual 
time=260.712..260.722 rows=2 loops=1)

 Hash Cond: (dvds.dvd_mov_id = movies.mov_id)
 Filter: ((lower((movies.mov_name)::text) ~~ '%superman re%'::text) OR 
(lower((dvds.dvd_edition)::text) ~~ '%superman re%'::text))
 ->  Seq Scan on dvds  (cost=0.00..1292.00 rows=39900 width=1062) (actual 
time=0.036..23.594 rows=20866 loops=1)
 ->  Hash  (cost=8194.93..8194.93 rows=82893 width=214) (actual 
time=168.121..168.121 rows=37417 loops=1)
   ->  Seq Scan on movies  (cost=0.00..8194.93 rows=82893 width=214) 
(actual time=0.024..131.401 rows=37417 loops=1)

Total runtime: 264.193 ms
2 rows fetched


now, an additional table (containing 600k records) is added through a left 
join. all the sudden the query takes 24sec. although there are indices on 
both dvds.dvd_ean and data_soundmedia.sm_info_ean, the planer does not make 
use of the indices but rather chooses to do 2 seq-scans.



SELECT * FROM shop.dvds
LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean
WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like 
'%superman re%' OR lower(dvd_edition) LIKE '%superman re%')


Hash Left Join  (cost=317592.21..326882.92 rows=159086 width=1936) (actual 
time=21021.023..22242.253 rows=2 loops=1)

 Hash Cond: (dvds.dvd_mov_id = movies.mov_id)
 Filter: ((lower((movies.mov_name)::text) ~~ '%superman re%'::text) OR 
(lower((dvds.dvd_name)::text) ~~ '%superman re%'::text) OR 
(lower((dvds.dvd_edition)::text) ~~ '%superman re%'::text))
 ->  Merge Left Join  (cost=309190.05..313899.09 rows=159086 width=1722) 
(actual time=19876.552..21902.007 rows=20866 loops=1)

   Merge Cond: ("outer"."?column20?" = "inner"."?column29?")
   ->  Sort  (cost=23027.68..23127.43 rows=39900 width=1062) (actual 
time=507.886..520.143 rows=20866 loops=1)

 Sort Key: (dvds.dvd_ean)::text
 ->  Seq Scan on dvds  (cost=0.00..1292.00 rows=39900 
width=1062) (actual time=0.047..100.415 rows=20866 loops=1)
   ->  Sort  (cost=286162.37..287781.38 rows=647601 width=660) (actual 
time=19336.011..20328.247 rows=646633 loops=1)

 Sort Key: (data_soundmedia.sm_info_ean)::text
 ->  Seq Scan on data_soundmedia  (cost=0.00..31080.01 
rows=647601 width=660) (actual time=0.074..2834.831 rows=647601 loops=1)
 ->  Hash  (cost=8194.93..8194.93 rows=82893 width=214) (actual 
time=177.033..177.033 rows=37417 loops=1)
   ->  Seq Scan on movies  (cost=0.00..8194.93 rows=82893 width=214) 
(actual time=0.118..129.716 rows=37417 loops=1)

Total runtime: 24419.939 ms
2 rows fetched


shouldn't the planer join the additional table *after* filtering? even if it 
does first joining then filtering, why isn't the existing index not used?


pgsql is 8.2beta2

thanks,
thomas 




---(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] [SQL] [ADMIN] Is there anyway to...

[operationsengineer1]

> While you could setup a cron job to decrement some
> counter every day, I
> think that's not the best approach. Instead, I'd run
> a query once a day
> that finds all students that are past-due and takes
> some kind of action.
> -- 
> Jim Nasby   
> [EMAIL PROTECTED]
> EnterpriseDB  http://enterprisedb.com 
> 512.569.9461 (cell)
> 
> ---(end of
> broadcast)---
> TIP 5: don't forget to increase your free space map
> settings

i'm wanting to learn something here so i'm going to
chime in.

the way i read what you are saying is that you'd have
start_date and number_days columns in your table.

each day a query would run and pull the start_date and
numbers_days columns.

the application (or postgresql function) would then
take the current date, subtract starte date and
compare it to number of days.  if it is above that
number, the code will take some sort of action.

is that about it or have i missed something?

tia...



 

Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
http://new.mail.yahoo.com

---(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] dblink / plpgsql - definition list cheats for record returning functions?

[Matthew Peter]

Is there any way to use CREATE TYPE/VIEW/TABLE defintion list instead of
manually defining the result types in the calling sql? Thanks



 
__
Sponsored Link

Talk more and pay less. Vonage can save you up to $300 a year on your phone 
bill. 
Sign up now. http://www.vonage.com/startsavingnow/

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

Re: [GENERAL] Can PostgreSQL notify a client that a trigger has fired?

[Jorge Godoy]

"Bill" <[EMAIL PROTECTED]> writes:

> Does PostgreSQL have built in mechanism I can use to conditionally
> notify a client application that a trigger has fired? What I want is
> something along the line of the following pseudo code in a trigger.
>
> if  then
>   raise client event

I have the impression that you're missing a lot of sections in the manual...
How about some time to re-read it?

http://www.postgresql.org/docs/8.1/interactive/sql-notify.html


-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(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] Can PostgreSQL notify a client that a trigger has

[Jeff Davis]

On Thu, 2006-11-09 at 00:39 +, Bill wrote:
> Does PostgreSQL have built in mechanism I can use to conditionally
> notify a client application that a trigger has fired? What I want is
> something along the line of the following pseudo code in a trigger.
> 
> if  then
>   raise client event
> 

Yes, use LISTEN and NOTIFY. Probably it's easiest to write the trigger
in PL/pgSQL.

Regards,
Jeff Davis


---(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] Can PostgreSQL notify a client that a trigger has fired?

[Bill]

Does PostgreSQL have built in mechanism I can use to conditionally
notify a client application that a trigger has fired? What I want is
something along the line of the following pseudo code in a trigger.

if  then
  raise client event

-- 
.Bill.

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

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

Re: [GENERAL] Performance monitoring

[Bill]

Jeff Davis wrote:

> On Wed, 2006-11-08 at 23:37 +, Bill wrote:
> > Can someone point me to information about performance monitoring in
> > the PostgreSQL documentation? I want to see what tools are
> > available to diagnose performance problems. Thanks.
> > 
> 
> http://www.postgresql.org/docs/8.1/static/monitoring.html
> 
> Does that help?
> 
> Regards,
>   Jeff Davis
> 
> 
> ---(end of
> broadcast)--- TIP 4: Have you searched our
> list archives?
> 
>http://archives.postgresql.org/

Thanks. I must have been blind to have missed that.

-- 
.Bill.

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

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

[GENERAL] PostgreSQL RPMs

[Leonel Nunez]

Hello :

I've seen that the 8.1.5-6 rpms are available.
Are those RPMs corrected from the announced bug ?

Thanks

Leonel


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

Re: [GENERAL] Performance monitoring

[Jeff Davis]

On Wed, 2006-11-08 at 23:37 +, Bill wrote:
> Can someone point me to information about performance monitoring in the
> PostgreSQL documentation? I want to see what tools are available to
> diagnose performance problems. Thanks.
> 

http://www.postgresql.org/docs/8.1/static/monitoring.html

Does that help?

Regards,
Jeff Davis


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

   http://archives.postgresql.org/

Re: [GENERAL] Does PostgreSQL support multi-instancing?

[Steve Crawford]

Bill wrote:
> Is is possible to have two different versions of PostgreSQL running on
> the same computer at the same time?
> 

It's easy to have two *instances* of postgresql running - they just need
to have their own data directories and be configured to not conflict
with each other (ie. listen on different ports).

Having two purely separate versions should be doable but more tricky as
you will have to build them in such a way each will see its own version
of certain libraries. Perhaps using static linking would deal with this
issue - I'll let someone else chime in on that. You will still, of
course, need to use different ports and data directories.

You could, if you prefer, set up multiple virtual servers on the same
computer and run each copy of PG on its own virtual server.

Cheers,
Steve

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

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

Re: [GENERAL] How much memory is required?

[Bill]

"Raymond O'Donnell" wrote:

> On 8 Nov 2006 at 22:48, Bill wrote:
> 
> > Is there any published information on the minimum or recommended
> > amount of memory for PostgreSQL on Windows and/or Linux. I am
> > looking
> 
> There's some useful information here:
> 
> http://www.powerpostgresql.com/PerfList
> 
> HTH,
> 
> --Ray.
> 
> --
> 
> Raymond O'Donnell
> Director of Music, Galway Cathedral, Galway, Ireland
> [EMAIL PROTECTED]
> --
> 
> 
> 
> ---(end of
> broadcast)--- TIP 1: if posting/reading
> through Usenet, please send an appropriatesubscribe-nomail
> command to [EMAIL PROTECTED] so that yourmessage can
> get through to the mailing list cleanly

Thanks. Very useful.

-- 
.Bill.

---(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] Performance monitoring

[Bill]

Can someone point me to information about performance monitoring in the
PostgreSQL documentation? I want to see what tools are available to
diagnose performance problems. Thanks.

-- 
.Bill.

---(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] How much memory is required?

[Raymond O'Donnell]

On 8 Nov 2006 at 22:48, Bill wrote:

> Is there any published information on the minimum or recommended amount
> of memory for PostgreSQL on Windows and/or Linux. I am looking 

There's some useful information here:

http://www.powerpostgresql.com/PerfList

HTH,

--Ray.

--

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
[EMAIL PROTECTED]
--



---(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] 8.1.2 locking issues

[Talha Khan]

Hi Ed,In order to get which entities are responsible for the lock that you have try using the following query. select loc.pid , cls.relname,loc.granted as lock_status from pg_locks  loc , pg_class cls where 
loc.locktype like '%sharelock%' and cls.oid=loc.relation and loc.pid in(pids of the process  from the error that are blocking each other);RegardsTalha KhanOn 11/9/06, 
Alvaro Herrera <[EMAIL PROTECTED]> wrote:
Ed L. wrote:>> We are having locking issues in 8.1.2 on HP 11.23 ia64.  I'm> trying to better understand how to conclusively identify who is> waiting on who and why.>> We have a series of "select for updates" on our 'sessions' table.
> One of those queries is stuck waiting for a "transactionid"> locktype ShareLock.  How can I tell what it's actually waiting> for?There should be tuple locks on pg_locks for the transaction that holds
the transactionid that your transaction is waiting on.--Alvaro Herrerahttp://www.CommandPrompt.com/PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---(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] Does PostgreSQL support multi-instancing?

[Steve Atkins]

On Nov 8, 2006, at 2:58 PM, Bill wrote:


Is is possible to have two different versions of PostgreSQL running on
the same computer at the same time?


Yes. Works just fine. You might end up with some fun with
applications finding the client libraries (generally better
to let them all find the new libraries, even when you're
expecting them to talk to the older server, I think) but
apart from that running different versions on different
ports seems to just work.

If you use some sort of binary package to install, rather
than installing from source all bets are off, and it likely
won't work, but that's just a packaging limitation.

Cheers,
  Steve


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

[GENERAL] TRIGGERS - access sql query_string that called it?

[Matthew Peter]

Is it possible to access the entire sql query_string that called the trigger?



 

Want to start your own business?
Learn how on Yahoo! Small Business.
http://smallbusiness.yahoo.com/r-index

---(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] RULE - special variables?

[Matthew Peter]

Do rules get special variables like triggers? Can I set variables in them like
triggers? 



 

Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
http://new.mail.yahoo.com

---(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] dblink / plpgsql - definition list cheats for record returning functions?

[Matthew Peter]

Is there any way to use CREATE TYPE or an VIEW/TABLE defintion list instead of
manually defining it?



 

Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
http://new.mail.yahoo.com

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

Re: [GENERAL] Does PostgreSQL support multi-instancing?

[Bill Moran]

In response to "Bill" <[EMAIL PROTECTED]>:

> Is is possible to have two different versions of PostgreSQL running on
> the same computer at the same time?

Yes, we have one server here that's doing it.  It's neither easy nor
difficult to set up ... it's somewhere in between ...

-- 
Bill Moran
Collaborative Fusion Inc.



IMPORTANT: This message contains confidential information and is intended only 
for the individual named. If the reader of this message is not an intended 
recipient (or the individual responsible for the delivery of this message to an 
intended recipient), please be advised that any re-use, dissemination, 
distribution or copying of this message is prohibited.  Please notify the 
sender immediately by e-mail if you have received this e-mail by mistake and 
delete this e-mail from your system.



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

   http://archives.postgresql.org/

Re: [GENERAL] How much memory is required?

[Bill Moran]

In response to "Bill" <[EMAIL PROTECTED]>:

> Is there any published information on the minimum or recommended amount
> of memory for PostgreSQL on Windows and/or Linux. I am looking at
> PostgreSQL for an embedded app and hardware cost is a consideration. I
> cannot find anything in the PostgreSQL 8.1.0 Documentation but I may
> not be looking in the right place. Thanks.

You need to estimate a lot of things before you can estimate the
memory requirements.  Off the top of my head:
*) Desired performance
*) Max # of simultaneous connections
*) Max size of dataset

-- 
Bill Moran
Collaborative Fusion Inc.



IMPORTANT: This message contains confidential information and is intended only 
for the individual named. If the reader of this message is not an intended 
recipient (or the individual responsible for the delivery of this message to an 
intended recipient), please be advised that any re-use, dissemination, 
distribution or copying of this message is prohibited.  Please notify the 
sender immediately by e-mail if you have received this e-mail by mistake and 
delete this e-mail from your system.



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

   http://archives.postgresql.org/

[GENERAL] Does PostgreSQL support multi-instancing?

[Bill]

Is is possible to have two different versions of PostgreSQL running on
the same computer at the same time?

-- 
.Bill.

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

[GENERAL] How much memory is required?

[Bill]

Is there any published information on the minimum or recommended amount
of memory for PostgreSQL on Windows and/or Linux. I am looking at
PostgreSQL for an embedded app and hardware cost is a consideration. I
cannot find anything in the PostgreSQL 8.1.0 Documentation but I may
not be looking in the right place. Thanks.

-- 
.Bill.

---(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] [solved] WAL ends before end time of backup dump

[Tom Lane]

Jeff Davis <[EMAIL PROTECTED]> writes:
> Would it make sense to throw an error if there's a recovery.conf and
> files already exist in pg_xlog? 

No; that's actually a feature in some scenarios (eg, your last few
segments didn't get archived yet).

There's no substitute for debugging your archiver script ;-)

regards, tom lane

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

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

Re: [GENERAL] 8.1.2 locking issues

[Alvaro Herrera]

Ed L. wrote:
> 
> We are having locking issues in 8.1.2 on HP 11.23 ia64.  I'm 
> trying to better understand how to conclusively identify who is 
> waiting on who and why.
> 
> We have a series of "select for updates" on our 'sessions' table.  
> One of those queries is stuck waiting for a "transactionid" 
> locktype ShareLock.  How can I tell what it's actually waiting 
> for?

There should be tuple locks on pg_locks for the transaction that holds
the transactionid that your transaction is waiting on.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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] [solved] WAL ends before end time of backup dump

[Jeff Davis]

On Tue, 2006-11-07 at 17:20 -0500, Tom Lane wrote:
> Jeff Davis <[EMAIL PROTECTED]> writes:
> > LOG:  restored log file "00010017002B" from archive
> > LOG:  record with zero length at 17/2B6EACC8
> > LOG:  redo done at 17/2B6EAC84
> 
> It looks to me like you archived this log file before it was fully
> written.  You should take a close look at your archiving procedures.

Ok, I found the problem. Completely my mistake, because I was running
through the test without being careful.

On the first recovery attempt, I left the postmaster.conf, including the
archive_command setting, in the data dir along with some archives in
pg_xlog. When I started recovery, it overwrote some of my archived
segments.

The archive script isn't supposed to overwrite existing files, but I had
a typo in the check and didn't realize it because in the normal case
everything was archiving correctly.

Would it make sense to throw an error if there's a recovery.conf and
files already exist in pg_xlog? 

Thanks for narrowing down the problem for me.

Regards,
Jeff Davis


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

[GENERAL] 8.1.2 locking issues

[Ed L.]

We are having locking issues in 8.1.2 on HP 11.23 ia64.  I'm 
trying to better understand how to conclusively identify who is 
waiting on who and why.

We have a series of "select for updates" on our 'sessions' table.  
One of those queries is stuck waiting for a "transactionid" 
locktype ShareLock.  How can I tell what it's actually waiting 
for?

TIA.
Ed

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

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

Re: [GENERAL] 8.1.2 postmaster died

[Talha Khan]

Hi Ed,I don't think its pgsql thats sending the SIGKILL its being done by the OS. The reason can only be confirmed after looking at your log files but the probability is that your OS ran out of memory and in order to protect itself it had to kill the processes that were taking a lot of memory thus ended up killing pgsql processes. Its just a hunch that i have but the reason can only be confirmed after seeing your logs.
RegardsTalha KhanOn 11/9/06, Ed Loehr <[EMAIL PROTECTED]> wrote:
On Wednesday November 8 2006 12:30 pm, Shoaib Mir wrote:> To be specific you need to look at the syslogs to see if it> was actually some OOM killer or not.I checked all db logs + syslog, of course.  All they show is the
process receiving SIGKILL (followed by all others being shutdownas a result).Is there any circumstance at all under which pgsql itself wouldissue a sigkill to one of the backends?Ed

Re: [GENERAL] Table and Field namestyle best practices?

[novnov]

Thanks that some good real world input. Not sure what it'll add up to for me
yet but good reference points.

In the db centric world I've been inhabiting for these years there are many
conventions re namestyles, they extend to table names, query names, field
names, variables, everything. I started out disliking the schemes but over
time saw the sense and adopted or munged for my own taste.

Unless some kind of prefixing is incorporated into naming conventions, a
name like employee could be a table, a query, an 'object', a field, etc. All
of my prev work is very easy to read because all names are clearly pegged.

tblOrganization is a regular data table, tlkpCity is a lookup table.

qryOrganization is a select query (usually by convention tblOrganization.*,
ie all records), qappEmployeeSalary is an append query, qdelEmployeeSalary a
delete query (usually with params).

Also, when refactoring is needed, and table/field etc needs to be renamed,
having unique names is pretty cool...and the patterns I've built up name
items in families (like the Org field name prefixes) so sometimes one can
swat a bunch of name changes at once...carefully.

These conventions have helped me navigate databases when they start getting
a signficant number of objects in them. I know many do without, but they've
been very good for me. I don't need to repro the naming conventions I've
built up over the years, but would like to keep the utility they offer
somehow.




Alexander Staubo wrote:
> 
> On Nov 8, 2006, at 18:49 , novnov wrote:
> 
>> I've been using namestyles with mixed case like OrgID. That is much  
>> more
>> readable than orgid. Probably a good convention to adopt would be  
>> to use
>> namestyles like org_id. That change I can live with.
> 
> Both are perfectly acceptable, though the mixed-case version has  
> drawbacks. ANSI SQL's case-insensitivity makes mixed-case identifiers  
> something of a second-rate citizen; "orgid" and "OrgID" are both  
> going to be resolved to the same object, unless you explicitly double- 
> quote it. Ambiguity is rarely a problem, but because there are no  
> checks for consistency, inconsistencies tend to sneak in, especially  
> in team projects; some people might type "OrgID", some "OrgId", and  
> so on.
> 
> Note that lower-case, underscore-delimited variable identifiers are  
> consistent with mainstream coding conventions for C, C++, Ruby,  
> Python and most languages in the Unix world (Java and JavaScript  
> being notable exceptions). After several years of trying to go  
> against the grain and be consistent across the board, I ended up  
> giving in and always using whatever is appropriate in the language/ 
> environment I work in.
> 
>> But another issue is the way that I've been naming foreign key  
>> references. I
>> tend to prefix field names with a table based acronym. So names like
>> OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So,  
>> if I have
>> a table tblEmployee, fk to the org table would be like EmpOrgID. I  
>> know many
>> would simply use OrgID as the fk in tblEmployee, but I have liked
>> compounding the names because is results in completely unambiguous  
>> field
>> names throughout the db. If I'm giving up the mixed case naming, I  
>> could use
>> emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best.
> 
> For column names, I recommend using whatever is natural in the  
> decribing a field, irrespective of what the field is actually  
> pointing towards. For example, a table representing an object with a  
> creator attribute pointing to a user would have a column "creator_id"  
> referencing "users (id)" -- not, say, "creator_user_id", which is  
> superfluous. The id suffix is there to tell me it's an identifier,  
> not the creator proper.
> 
> In your case, in the table "organizations" the column names would be  
> "id", "name", "city" and so on, and a table "employees" would have a  
> column "organization_id" with a foreign-key reference. This simple  
> convention translates more easily to one's mental model of a query  
> such as "select all employees where the organization name is 'foo'",  
> which becomes:
> 
>select * from employees
>join organizations on id = organization_id
>where organizations.name = 'foo'
> 
> as opposed to
> 
>select * from tblEmployees
>join Org on OrgId = EmpOrgId
>where Org.OrgName = 'foo'
> 
> or something.
> 
> I am curious as to why you need to prefix table names with "tbl" in  
> the first place.
> 
> Alexander.
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7245644
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

   ht

Re: [GENERAL] Stable sort?

[Dann Corbit]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Wednesday, November 08, 2006 7:05 AM
> To: redhog
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Stable sort?
> 
> "redhog" <[EMAIL PROTECTED]> writes:
> > My question was if the order of two
> > elements whose internal order is not affected by the current
ordering
> > clause, still may change places due to technicalities.
> 
> Postgres usually sorts using qsort(), which (on most platforms) is not
> stable in that sense.

This is (of course) what we would expect from any SQL database.

From ISO/IEC ISO/IEC 9075-2:1999 (E) Section 4.29 Cursors on page 71, we
have this:
"A cursor in the open state identifies a table, an ordering of the rows
of that table, and a position relative to that ordering. If the  does not contain an , or contains an  that does not specify the order of the rows completely, then the
rows of the table have an order that is defined only to the extent that
the  specifies an order and is otherwise
implementation-dependent. 
When the ordering of a cursor is not defined by an ,
the relative position of two rows is implementation-dependent. When the
ordering of a cursor is partially determined by an ,
then the relative positions of two rows are determined only by the
; if the two rows have equal values for the purpose of
evaluating the , then their relative positions are
implementation-dependent."

This is talking about cursors specifically, but the same ordering
principles would obviously hold throughout.

The standard does not prevent the use of a stable sort, but it also does
not require it.

Obviously, if you order by all the result columns, then the orderings
will always be identical (though this would in general be a bad idea).

---(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] "Broken" Plan? Nested Loop IN Join vs. Nested Loop/Hash Aggregate

[Tom Lane]

"Worky Workerson" <[EMAIL PROTECTED]> writes:
> -> Index Scan using ip_profiles_pkey on ip_profiles
> (cost=0.00..3.37 rows=1 width=4) (actual time=0.035..1195.567
> rows=15 loops=1)
>   Index Cond: ((ip > '1.0.0.0'::ip4) AND (ip <= '1.255.255.255))

Um ... have you ANALYZEd these tables recently?  If so, try increasing
the statistics target for them.  You aren't ever going to get decent
plans when the rowcount estimates are off by five orders of magnitude,
and for such a simple condition I see no reason why they would be
except for obsolete or inadequate stats.

regards, tom lane

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

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

Re: [GENERAL] killing autovac

[Tom Lane]

"Ed L." <[EMAIL PROTECTED]> writes:
> Can I kill -SIGINT autovac in 8.1.2 without taking down all the 
> other backends?

Should work fine.

regards, tom lane

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

Re: [GENERAL] Table and Field namestyle best practices?

[Ron Mayer]

Merlin Moncure wrote:
> looks much better than OrgID. I suggest not prefixing tables with
> 'tbl', but idx_ for indexes and fk_ for foreign keys is ok.

I've recently gotten into the habit of naming my indexes after
exactly what they index.   For example:
  create index "foo(x,y,z)" on foo(x,y,z);
and
  CREATE INDEX "tbl using gist(text_search_vec)" on tbl using 
gist(text_search_vec);

It's just as obvious as prefixing them with "idx_" and makes
EXPLAIN output a bit quicker for me to understand.

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

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

Re: [GENERAL] 8.1.2 postmaster died

[Shoaib Mir]

To be specific you need to look at the syslogs to see if it was actually some OOM killer or not.Thanks,---Shoaib MirEnterpriseDB (www.enterprisedb.com)
On 11/9/06, Talha Khan <[EMAIL PROTECTED]> wrote:
Hi Ed,You need to look through the log files for details.RegardsTalha KhanOn 11/8/06, 
Ed L. <
[EMAIL PROTECTED]> wrote:One of our 8.1.2 postmasters on HPUX 11.23 ia64 just received a
SIGKILL signal from unknown origins.  After reviewing allcommand history files for the DBA and root, I do not believeanyone manually sent it, and we have no scripts etc that woulddo that, at least that we can find or imagine.  The machine had
ample RAM available.Any ideas where could this SIGKILL would have come from?Thanks,Ed---(end of broadcast)---TIP 4: Have you searched our list archives?
   http://archives.postgresql.org/

Re: [GENERAL] Table and Field namestyle best practices?

[Merlin Moncure]

On 11/8/06, novnov <[EMAIL PROTECTED]> wrote:


I'm new to pgsql, mostly coming from MSSQL. It sounds like it's simplest to
keep field names lowercase with pgsql, so I will have to change some habits
I've developed over the years. I would like to glean whatever collective
wisdom I can here from experienced pgsql devs.

I've been using namestyles with mixed case like OrgID. That is much more
readable than orgid. Probably a good convention to adopt would be to use
namestyles like org_id. That change I can live with.


of course, it's all in the eye of the beholder, but i think org_id
looks much better than OrgID. I suggest not prefixing tables with
'tbl', but idx_ for indexes and fk_ for foreign keys is ok.  I also
think its ok to do on_xyx_delete for a delete trigger on table xyz.

some people like to name id columns 'id' and refer to that column as
xyz_id for foreign key reference, but I prefer to write out xyz_id in
all tables...it helps with natural joins and searching.  this is
basically your argument as well.

also, and this is getting into flamewar territory, but i prefer to not
pluralize tables (create table order) for conceptual reasons.  no big
deal though really.

my tables do not automatically get an 'id' column although they often
do...this is barely on topic for your post so ill leave it there ;-)

one last thing (also not really on topic), and this is very much
against the grain, but I do not do upper/lower case mixture that I see
in 95% of the sql on the web:

SELECT * FROM order WHERE

it reminds me too much of cobol, er COBOL and it makes my brain hurt.
i also passively agressively affirm my minority stand on this issue by
deliberately having lousy capitalization on the various lists.

regards,
merlin

---(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] 8.1.2 postmaster died

[Talha Khan]

Hi Ed,You need to look through the log files for details.RegardsTalha KhanOn 11/8/06, Ed L. <
[EMAIL PROTECTED]> wrote:One of our 8.1.2 postmasters on HPUX 11.23 ia64 just received a
SIGKILL signal from unknown origins.  After reviewing allcommand history files for the DBA and root, I do not believeanyone manually sent it, and we have no scripts etc that woulddo that, at least that we can find or imagine.  The machine had
ample RAM available.Any ideas where could this SIGKILL would have come from?Thanks,Ed---(end of broadcast)---TIP 4: Have you searched our list archives?
   http://archives.postgresql.org/

Re: [GENERAL] Table and Field namestyle best practices?

[Ed L.]

On Wednesday November 8 2006 11:31 am, novnov wrote:
> Yes, I've already pretty much decided to use lowercase for all
> namestyles, I mentioned that in the first post. Using
> lowercase invokes a set of other issues, which I'm asking for
> options on...namely, conventions like org_id, and emp_org_id,
> or simply using org_id as the fk pointer.

Not sure there is a "best" way, but I prefer org_id due to 
brevity and no concern for quoting.  Your emp_org_id is nice for 
views because you don't have to disambiguate columns.

---(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] Table and Field namestyle best practices?

[Alexander Staubo]

On Nov 8, 2006, at 18:49 , novnov wrote:

I've been using namestyles with mixed case like OrgID. That is much  
more
readable than orgid. Probably a good convention to adopt would be  
to use

namestyles like org_id. That change I can live with.


Both are perfectly acceptable, though the mixed-case version has  
drawbacks. ANSI SQL's case-insensitivity makes mixed-case identifiers  
something of a second-rate citizen; "orgid" and "OrgID" are both  
going to be resolved to the same object, unless you explicitly double- 
quote it. Ambiguity is rarely a problem, but because there are no  
checks for consistency, inconsistencies tend to sneak in, especially  
in team projects; some people might type "OrgID", some "OrgId", and  
so on.


Note that lower-case, underscore-delimited variable identifiers are  
consistent with mainstream coding conventions for C, C++, Ruby,  
Python and most languages in the Unix world (Java and JavaScript  
being notable exceptions). After several years of trying to go  
against the grain and be consistent across the board, I ended up  
giving in and always using whatever is appropriate in the language/ 
environment I work in.


But another issue is the way that I've been naming foreign key  
references. I

tend to prefix field names with a table based acronym. So names like
OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So,  
if I have
a table tblEmployee, fk to the org table would be like EmpOrgID. I  
know many

would simply use OrgID as the fk in tblEmployee, but I have liked
compounding the names because is results in completely unambiguous  
field
names throughout the db. If I'm giving up the mixed case naming, I  
could use

emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best.


For column names, I recommend using whatever is natural in the  
decribing a field, irrespective of what the field is actually  
pointing towards. For example, a table representing an object with a  
creator attribute pointing to a user would have a column "creator_id"  
referencing "users (id)" -- not, say, "creator_user_id", which is  
superfluous. The id suffix is there to tell me it's an identifier,  
not the creator proper.


In your case, in the table "organizations" the column names would be  
"id", "name", "city" and so on, and a table "employees" would have a  
column "organization_id" with a foreign-key reference. This simple  
convention translates more easily to one's mental model of a query  
such as "select all employees where the organization name is 'foo'",  
which becomes:


  select * from employees
  join organizations on id = organization_id
  where organizations.name = 'foo'

as opposed to

  select * from tblEmployees
  join Org on OrgId = EmpOrgId
  where Org.OrgName = 'foo'

or something.

I am curious as to why you need to prefix table names with "tbl" in  
the first place.


Alexander.

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

Re: [GENERAL] Table and Field namestyle best practices?

[novnov]

Yes, I've already pretty much decided to use lowercase for all namestyles, I
mentioned that in the first post. Using lowercase invokes a set of other
issues, which I'm asking for options on...namely, conventions like org_id,
and emp_org_id, or simply using org_id as the fk pointer.



Richard Broersma Jr wrote:
> 
>> I've been using namestyles with mixed case like OrgID. That is much more
>> readable than orgid. Probably a good convention to adopt would be to use
>> namestyles like org_id. That change I can live with.
> 
> According to recommended practice you have a choice to make. Here is what
> is described from the following link:
> http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
> 
> Quoting an identifier also makes it case-sensitive, whereas unquoted names
> are always folded to
> lower case. For example, the identifiers FOO, foo, and "foo" are
> considered the same by
> PostgreSQL, but "Foo" and "FOO" are different from these three and each
> other. (The folding of
> unquoted names to lower case in PostgreSQL is incompatible with the SQL
> standard, which says that
> unquoted names should be folded to upper case. Thus, foo should be
> equivalent to "FOO" not "foo"
> according to the standard. If you want to write portable applications you
> are advised to always
> quote a particular name or never quote it.)
> 
> Make note of the last sentence.
> 
> Regards,
> 
> Richard Broersma Jr.
> 
> ---(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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7244110
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Table and Field namestyle best practices?

[Richard Broersma Jr]

> I've been using namestyles with mixed case like OrgID. That is much more
> readable than orgid. Probably a good convention to adopt would be to use
> namestyles like org_id. That change I can live with.

According to recommended practice you have a choice to make. Here is what
is described from the following link:
http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

Quoting an identifier also makes it case-sensitive, whereas unquoted names are 
always folded to
lower case. For example, the identifiers FOO, foo, and "foo" are considered the 
same by
PostgreSQL, but "Foo" and "FOO" are different from these three and each other. 
(The folding of
unquoted names to lower case in PostgreSQL is incompatible with the SQL 
standard, which says that
unquoted names should be folded to upper case. Thus, foo should be equivalent 
to "FOO" not "foo"
according to the standard. If you want to write portable applications you are 
advised to always
quote a particular name or never quote it.)

Make note of the last sentence.

Regards,

Richard Broersma Jr.

---(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] Table and Field namestyle best practices?

[novnov]

I'm new to pgsql, mostly coming from MSSQL. It sounds like it's simplest to
keep field names lowercase with pgsql, so I will have to change some habits
I've developed over the years. I would like to glean whatever collective
wisdom I can here from experienced pgsql devs.

I've been using namestyles with mixed case like OrgID. That is much more
readable than orgid. Probably a good convention to adopt would be to use
namestyles like org_id. That change I can live with.

But another issue is the way that I've been naming foreign key references. I
tend to prefix field names with a table based acronym. So names like
OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So, if I have
a table tblEmployee, fk to the org table would be like EmpOrgID. I know many
would simply use OrgID as the fk in tblEmployee, but I have liked
compounding the names because is results in completely unambiguous field
names throughout the db. If I'm giving up the mixed case naming, I could use
emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best.

I am very curious to hear the various conventions folks here have arrived
at. I don't expect there to be consensus, but the various rationales might
help me arrive at an approach that works well for me.
-- 
View this message in context: 
http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7243332
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] 8.1.2 postmaster died

[Ed L.]

One of our 8.1.2 postmasters on HPUX 11.23 ia64 just received a 
SIGKILL signal from unknown origins.  After reviewing all 
command history files for the DBA and root, I do not believe 
anyone manually sent it, and we have no scripts etc that would 
do that, at least that we can find or imagine.  The machine had 
ample RAM available.

Any ideas where could this SIGKILL would have come from?

Thanks,
Ed

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

   http://archives.postgresql.org/

Re: [GENERAL] WAL ends before end time of backup dump

[Jeff Davis]

On Wed, 2006-11-08 at 09:39 -0500, Woody Woodring wrote:
> Which Sunday?  Could you have been bitten by some DST time shift?  There
> were 2 1ams a couple of weeks ago.
> 

Hah, interesting idea, but it wasn't that the basebackup didn't exist,
and it wasn't that I had two of them close together. I had one base
backup, and apparently one of the logs archived at that time is corrupt
(or perhaps archived too soon).

The log file has bytes throughout, but I suppose that doesn't tell me
anything because of the recycling.

It would be handy if there was a way to check whether archived log files
are good or not without trying to go through recovery. 

I still really don't know what caused this. I think it may have
something to do with a lot of activity happening during my base backup.
I am sending syslog data to the database, and I imagine a lot of syslog
data is generated by other cron scripts at that time.

Regards,
Jeff Davis


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

[GENERAL] "Broken" Plan? Nested Loop IN Join vs. Nested Loop/Hash Aggregate

[Worky Workerson]

I'm having an issue with a query plan that seems to be taking *far*
longer than it should.  I have the following schema (Note: I had to
retype everything from an isolated lab, so I'm hoping that its correct
enough to show my problem):

CREATE TABLE ip_profiles (
 ipIP4 PRIMARY KEY,
 --more columns
);

CREATE TABLE events (
 ipIP4 NOT NULL FOREIGN KEY ip_profiles (ip),
 content BYTEA,
 --more columns
);
CREATE INDEX events_ip_idx ON events USING btree (ip);

CREATE TABLE event_ip1 (
 CONSTRAINT partition_ip1 CHECK ((ip >= '1.0.0.0') AND (ip <=
'1.255.255.255'));
) INHERITS (events);

The events table is partitioned on the first octet of the ip, the same
indexes are generated across all the partitions, and constraint
exclusion is turned on.

I would like to get all the ip_profiles that have an event with a
given content string within an IP range.  My first try was the
following (again, retyped):

EXPLAIN ANALYZE SELECT count(*) FROM ip_profiles WHERE ip BETWEEN
'1.0.0.0' AND '1.255.255.255' AND ip IN (SELECT ip FROM events WHERE
ip >= '1.0.0.0' AND ip <= '1.255.255.255' AND content = 'bytes');

Aggregate (cost=2047.15..2047.16 rows=1 width=0) (actual
time=7801833.540 rows=1 loops=1)
 ->  Nested Loop IN Join (cost=1.02..2047.15 rows=1 width=0) (actual
time=7801833.505..7801833.505 rows=0 loops=1)
   Join Filter: ("outer".ip = "inner".ip)
   -> Index Scan using ip_profiles_pkey on ip_profiles
(cost=0.00..3.37 rows=1 width=4) (actual time=0.035..1195.567
rows=15 loops=1)
 Index Cond: ((ip > '1.0.0.0'::ip4) AND (ip <= '1.255.255.255))
   -> Append (cost=1.02..2043.48 rows=24 width=4) (actual
time=53.988..53.988 rows=0 loops=15)
 -> Bitmap Heap Scan on events (cost=1.02..4.96 rows=1
width=4) (actual time=0.010..0.010 rows=0 loops=15)
   Recheck Cond: ((ip > '1.0.0.0'::ip4) AND (ip <=
'1.255.255.255))
   Filter: (content='bytes'::bytea)
   -> Bitmap Index Scan on events_ip_idx
(cost=0.00..1.02 rows=3 width=0) (actual time=0.006..0.006 rows=0
loops=15)
 Index Cond: ((ip > '1.0.0.0'::ip4) AND (ip
<= '1.255.255.255))
 -> Seq Scan on events_ip1 events (cost=0.00 rows=23
width=4) (actual time=53.972..53.972 rows=0 loops=15)
   Filter: ((ip > '1.0.0.0'::ip4) AND (ip <=
'1.255.255.255) AND (content='bytes'::bytea))
Total runtime: 7801834.104 ms

Which looks like the "Nested Loop IN Join) is taking a *lot* longer
than the planner estimated and, needless to say, is unusable.  Taking
out the range condition on the ip_profiles table should lead to a
query producing identical results, right?  I did this ...

SELECT count(*) FROM ip_profiles WHERE ip in (SELECT ip FROM events
WHERE ip >= '1.0.0.0' AND ip <= '1.255.255.255' AND content = 'random
byte string');

... which results in a *mush* faster time, with a plan that looks like
(will retype fully if useful):

Nested Loop (cost=2187.94..2289.54 rows=30 width=67) (actual
time=223.681..225.693 rows=84 loops=1)
 -> Hash Aggregate
   -> Append
 -> Bitmap Heap Scan on events
 -> Seq Scan on events_ip1 events
 -> Index Scan using ip_profiles_pkey on ip_profiles
Total runtime: 226.170 ms

I realize that the immediate fix is "don't do that", but I'd like to
get to the bottom of this so that I'm not suprised in the future.  I
have seen several threads on the performance of IN (SELECT ..), but
this doesn't seem to be the culprit.  Any ideas?

Tables are recently vacuumed and analyzed, indexes were just created,
and I dropped and recreaetd the database with the same effect.  I am a
bit behind the latest, PG 8.1.3 on RHEL 4.3 x86_64, but I'd like to
know that upgrading will fix the problem before going through the
hassle of getting new stuff into the lab.  I plan on upgrading to 8.2
whenever that is "released".

Thanks!

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

[GENERAL] killing autovac

[Ed L.]

Can I kill -SIGINT autovac in 8.1.2 without taking down all the 
other backends?

Thanks,
Ed


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

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

Re: [GENERAL] Question on inserting and querying at the same time.

[Erik Jones]

Richard Huxton wrote:

Wei Weng wrote:

I have a database table that has about 90k entries, they are all
straightfoward text, and there is only one ID field that I use as
primary key for this table.

I have two threads working on this table. One of them inserting new
content constantly, (about every second) another one idles and only
wakes up when I want to query(select) the table.

My problem is that from the log it seems to me that since the insertion
is done far frequently than query, the query thread never finishes its
job. It just hangs there for a long time until I kill it by restarting
postgresql. (Even statements like EXPLAIN ANALYZE will hang)


Define "threads". If you mean a threaded client application, are you 
sure your libpq was compiled with threading enabled?


Define "hangs" please. What do the logs show?

Along with what Richard asked, what kind of query are you running?  
INSERT commands should never prevent you from doing a basic SELECT on 
the table even if they're running concurrently as INSERTs take out ROW 
EXCLUSIVE locks for the rows that they are inserting, while SELECT just 
takes out an ACCESS SHARE lock on the table, and the two do not 
conflict.  So, if by threaded you mean two different processes accessing 
the same table I'm guessing that your queries are not 'simple' and you 
should share them if you want help there, and if by threaded you are 
referring to an actual threaded application, double check how libpq was 
compiled and double check you in application threading logic.


--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


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

Re: [GENERAL] Stable sort?

[Tom Lane]

"redhog" <[EMAIL PROTECTED]> writes:
> My question was if the order of two
> elements whose internal order is not affected by the current ordering
> clause, still may change places due to technicalities.

Postgres usually sorts using qsort(), which (on most platforms) is not
stable in that sense.

regards, tom lane

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

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

Re: [GENERAL] WAL ends before end time of backup dump

[Woody Woodring]

Which Sunday?  Could you have been bitten by some DST time shift?  There
were 2 1ams a couple of weeks ago.

Woody
IGLASS Networks 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jeff Davis
Sent: Tuesday, November 07, 2006 5:38 PM
To: Tom Lane
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] WAL ends before end time of backup dump

On Tue, 2006-11-07 at 17:20 -0500, Tom Lane wrote:
> Jeff Davis <[EMAIL PROTECTED]> writes:
> > LOG:  restored log file "00010017002B" from archive
> > LOG:  record with zero length at 17/2B6EACC8
> > LOG:  redo done at 17/2B6EAC84
> 
> It looks to me like you archived this log file before it was fully 
> written.  You should take a close look at your archiving procedures.
> 

Hmm... the only way the file gets there is archive command. I didn't do any
manual moves of the segments, certainly not last Sunday at 1am (which is
when this segment was put there).

>From postgresql.conf:
archive_command = '/usr/local/pgsql/bin/archive_command.bash "%p" "%f"'

and:
---
$ cat /usr/local/pgsql/bin/archive_command.bash
#!/usr/local/bin/bash

# $1 is the full path to the file to archive # $2 is the filename

WAL=$1
WAL_NAME=$2
WAL_ARCHIVE=/path/to/archive

echo test ! -f $WAL_ARCHIVE/$WAL_NAME && gzip -c $WAL >
$WAL_ARCHIVE/$WAL_NAME.gz


Any obvious holes in there? Is gzip doing something weird? It worked with
all my other WAL segments, and I've tested the backups multiple times.

Also, the WAL segment is exactly the right size, although I suppose there's
no way for it not to be.

Regards,
Jeff Davis


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


---(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] converting Informix outer to Postgres

[Gregory S. Williamson]

Perhaps a more recent version of postgres (8.1 or mayber even look at 8.2 ...); 
lots of improvements since 7.1, IIRC in the area of joins specifically, but I 
don't know the answer to your question specifically.

HTH,

Greg Williamson (a [mostly] former Informix user, but not, alas, with such 
queries)
DBA
GlobeXplorer LLC


-Original Message-
From:   [EMAIL PROTECTED] on behalf of H.J. Sanders
Sent:   Wed 11/8/2006 4:27 AM
To: Martijn van Oosterhout; pgsql-general@postgresql.org
Cc: 
Subject:Re: [GENERAL] converting Informix outer to Postgres

Hi.

From some documentation:

In INFORMIX SQL, outer tables are defined in the FROM clause with the OUTER 
keyword :

SELECT ... FROM a, OUTER(b)
 WHERE a.key = b.akey

SELECT ... FROM a, OUTER(b,OUTER(c))
 WHERE a.key = b.akey
   AND b.key1 = c.bkey1
   AND b.key2 = c.bkey2 

PostgreSQL 7.1 supports the ANSI outer join syntax :

SELECT ... FROM cust LEFT OUTER JOIN order
 ON cust.key = order.custno

SELECT ...
  FROM cust LEFT OUTER JOIN order
 LEFT OUTER JOIN item
 ON order.key = item.ordno
ON cust.key = order.custno
 WHERE order.cdate > current date







Any help?

Henk


> -Oorspronkelijk bericht-
> Van: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Martijn van Oosterhout
> Verzonden: woensdag 8 november 2006 11:42
> Aan: [EMAIL PROTECTED]
> CC: pgsql-general@postgresql.org
> Onderwerp: Re: [GENERAL] converting Informix outer to Postgres
> 
> 
> On Tue, Nov 07, 2006 at 06:35:05PM -0500, [EMAIL PROTECTED] wrote:
> > Hi all,
> > I have been working on this Informix SQL query which has an outer join.
> > I have attached Informix query and my "supposedly" solution to this query
> > but I cannot get the same count. I appreciate for any help.
> > Thanks.
> 
> I don't know what the Informix outer join is, but is it like the SQL
> FULL OUTER JOIN? Have you tried using that?
> 
> Have a nice day,
> -- 
> Martijn van Oosterhout  http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to 
> > litigate.
> 

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


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4551ca60161213366512726&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:4551ca60161213366512726!
---






---(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] converting Informix outer to Postgres

[H.J. Sanders]

Hi.

>From some documentation:

In INFORMIX SQL, outer tables are defined in the FROM clause with the OUTER 
keyword :

SELECT ... FROM a, OUTER(b)
 WHERE a.key = b.akey

SELECT ... FROM a, OUTER(b,OUTER(c))
 WHERE a.key = b.akey
   AND b.key1 = c.bkey1
   AND b.key2 = c.bkey2 

PostgreSQL 7.1 supports the ANSI outer join syntax :

SELECT ... FROM cust LEFT OUTER JOIN order
 ON cust.key = order.custno

SELECT ...
  FROM cust LEFT OUTER JOIN order
 LEFT OUTER JOIN item
 ON order.key = item.ordno
ON cust.key = order.custno
 WHERE order.cdate > current date







Any help?

Henk


> -Oorspronkelijk bericht-
> Van: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Martijn van Oosterhout
> Verzonden: woensdag 8 november 2006 11:42
> Aan: [EMAIL PROTECTED]
> CC: pgsql-general@postgresql.org
> Onderwerp: Re: [GENERAL] converting Informix outer to Postgres
> 
> 
> On Tue, Nov 07, 2006 at 06:35:05PM -0500, [EMAIL PROTECTED] wrote:
> > Hi all,
> > I have been working on this Informix SQL query which has an outer join.
> > I have attached Informix query and my "supposedly" solution to this query
> > but I cannot get the same count. I appreciate for any help.
> > Thanks.
> 
> I don't know what the Informix outer join is, but is it like the SQL
> FULL OUTER JOIN? Have you tried using that?
> 
> Have a nice day,
> -- 
> Martijn van Oosterhout  http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to 
> > litigate.
> 

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

Re: [GENERAL] Stable sort?

[redhog]

> I don't see how it could be:
>SELECT * FROM (SELECT * FROM a ORDER BY x DESC) AS B ORDER BY x ASC;

That is a rather different query. My question was if the order of two
elements whose internal order is not affected by the current ordering
clause, still may change places due to technicalities. That is, the
normal meaning of the term "stable sort" as it applies to sorting
algorithms.

Example:

Given a subquery that returns the rows

 a| b
+
 2|1
 2|2
 1|1
 1|2

and an order by a, will the result allways be

 a| b
+
 1|1
 1|2
 2|1
 2|2

or might it sometimes end up as e.g.

 a| b
+
 1|2
 1|1
 2|1
 2|2

?


---(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] Salir de la lista

[Alcides Guillermo Alarcón Rojas]

Hola; me gustaria salir de la lista podrias inhabilitarme por favor

Saludos

_
Charla con tus amigos en línea mediante MSN Messenger: 
http://messenger.latam.msn.com/



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

Re: [GENERAL] converting Informix outer to Postgres

[Martijn van Oosterhout]

On Tue, Nov 07, 2006 at 06:35:05PM -0500, [EMAIL PROTECTED] wrote:
> Hi all,
> I have been working on this Informix SQL query which has an outer join.
> I have attached Informix query and my "supposedly" solution to this query
> but I cannot get the same count. I appreciate for any help.
> Thanks.

I don't know what the Informix outer join is, but is it like the SQL
FULL OUTER JOIN? Have you tried using that?

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


signature.asc
Description: Digital signature

Re: [GENERAL] Stable sort?

[Richard Huxton]

redhog wrote:

Is sorting in PostgreSQL stable over subqueries, that is, is

select * from (select * from A order by x) as B order by y;

equivalent with

select * from A order by y, x;


I don't see how it could be:
  SELECT * FROM (SELECT * FROM a ORDER BY x DESC) AS B ORDER BY x ASC;

--
  Richard Huxton
  Archonet Ltd

---(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] FOR ... IN

[Alban Hertroys]

Alain Roger wrote:

Hi William,



-- Function: SP_U_001(typeofarticle varchar)

-- DROP FUNCTION SP_U_001(typeofarticle varchar);

CREATE OR REPLACE FUNCTION SP_U_001(VARCHAR)
 RETURNS SETOF active_articles AS
$BODY$
DECLARE
   myrec RECORD;
   res active_articles;
/**/
BEGIN

 FOR myrec IN
   select *
   from articles, articletypes, department
   where
   articletypes.articletype_type = $1
   AND articles.articletype_id = articletypes.articletype_id
   AND articles.department_id = department.department_id
   AND articles.validity_period_end > now()
 LOOP
   IF (myrec IS NOT NULL) THEN
   res.article_type := myrec.articletypes.articletype_type;

res.article_type := myrec.articletype_type;


   res.article_author := myrec.articles.author;
   res.department_owner := myrec.department.department_name;

res.department_owner := myrec.department_name;


   res.department_picture := myrec.department.department_picture;

res.department_picture := myrec.department_picture;

etcetera.


and this is the error message i get :

ERROR:  schema "myrec" does not exist


The query results don't contain information about the tables they came 
from, so inserting a table-name in your record syntax makes myrec be 
interpreted as a schema instead of a variable.



CONTEXT:  SQL statement "SELECT  myrec.articletypes.articletype_type"
PL/pgSQL function "sp_u_001" line 17 at assignment

line 17 consists of WHERE close if you count comments, if not, i 
consists of

last line of my SELECT command ==> AND articles.validity_period_end > now()


Line 17 is your first (faulty) assignment from myrec. Line 1 is the line 
containing 'DECLARE'.


--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

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

// Integrate Your World //

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

Re: [GENERAL] Question on inserting and querying at the same time.

[Richard Huxton]

Wei Weng wrote:

I have a database table that has about 90k entries, they are all
straightfoward text, and there is only one ID field that I use as
primary key for this table.

I have two threads working on this table. One of them inserting new
content constantly, (about every second) another one idles and only
wakes up when I want to query(select) the table.

My problem is that from the log it seems to me that since the insertion
is done far frequently than query, the query thread never finishes its
job. It just hangs there for a long time until I kill it by restarting
postgresql. (Even statements like EXPLAIN ANALYZE will hang)


Define "threads". If you mean a threaded client application, are you 
sure your libpq was compiled with threading enabled?


Define "hangs" please. What do the logs show?


--
  Richard Huxton
  Archonet Ltd

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