[GENERAL] pg_dump problem Postgres 7.4
I cannot do a /usr/bin/pgdump with my new Postgres 7.4. When I try,
here is what I get:
---
[ ~]$ /usr/bin/pg_dump dcf_20050404 >&
/home/japsey/DCF_RAID_01/source/sql/backup/bu/dcf_20050404_`date
+%y%m%d`.dmp
audit(1115732852.025:0): avc: denied { write } for pid=11023
exe=/usr/bin/pg_dump
path=/~/DCF_RAID_01/source/sql/backup/bu/dcf_20050404_050510.dmp
dev=sda3 ino=5522308 scontext=user_u:system_r:postgresql_t
tcontext=user_u:object_r:file_t tclass=file
audit(1115732852.025:0): avc: denied { write } for pid=11023
exe=/usr/bin/pg_dump
path=/~/DCF_RAID_01/source/sql/backup/bu/dcf_20050404_050510.dmp
dev=sda3 ino=5522308 scontext=user_u:system_r:postgresql_t
tcontext=user_u:object_r:file_t tclass=file
audit(1115732852.028:0): avc: denied { search } for pid=11023
exe=/usr/bin/pg_dump name=/ dev=sda3 ino=2
scontext=user_u:system_r:postgresql_t tcontext=system_u:object_r:file_t
tclass=dir
[ ~]$
---
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] pg_dump fails on 7.4 Postgres
This may be my second posting but I think I've done it correctly this time.
At this point, I am unable to do a pg_dump using our new Rec Hat
Enterprise Linux AS 4 version of Postgres which is version 7.4.
Here's what I get when I try to do a pg_dump of our database:
---
[~]$
[ ~]$ /usr/bin/pg_dump dcf_20050404 >& /~/dcf_20050404_`date +%y%m%d`.dmp
audit(1115732852.025:0): avc: denied { write } for pid=11023
exe=/usr/bin/pg_dump path=/~/dcf_20050404_050510.dmp
dev=sda3 ino=5522308 scontext=user_u:system_r:postgresql_t
tcontext=user_u:object_r:file_t tclass=file
audit(1115732852.025:0): avc: denied { write } for pid=11023
exe=/usr/bin/pg_dump path=//~/dcf_20050404_050510.dmp
dev=sda3 ino=5522308 scontext=user_u:system_r:postgresql_t
tcontext=user_u:object_r:file_t tclass=file
audit(1115732852.028:0): avc: denied { search } for pid=11023
exe=/usr/bin/pg_dump name=/ dev=sda3 ino=2
scontext=user_u:system_r:postgresql_t tcontext=system_u:object_r:file_t
tclass=dir
[ ~]$
Here's Postgres rpm on the machine in question:
[~]$ rpm -qa | egrep postgres
postgresql-tcl-7.4.6-1.RHEL4.2
postgresql-pl-7.4.6-1.RHEL4.2
postgresql-docs-7.4.6-1.RHEL4.2
postgresql-odbc-7.3-8
postgresql-7.4.6-1.RHEL4.2
postgresql-server-7.4.6-1.RHEL4.2
postgresql-test-7.4.6-1.RHEL4.2
postgresql-libs-7.4.6-1.RHEL4.2
postgresql-jdbc-7.4.6-1.RHEL4.2
postgresql-python-7.4.6-1.RHEL4.2
postgresql-contrib-7.4.6-1.RHEL4.2
postgresql-devel-7.4.6-1.RHEL4.2
[~]$
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump fails on 7.4 Postgres
Tom Lane wrote:
"Jimmie H. Apsey" <[EMAIL PROTECTED]> writes:
At this point, I am unable to do a pg_dump using our new Rec Hat
Enterprise Linux AS 4 version of Postgres which is version 7.4.
Here's what I get when I try to do a pg_dump of our database:
[ ~]$ /usr/bin/pg_dump dcf_20050404 >& /~/dcf_20050404_`date +%y%m%d`.dmp
audit(1115732852.025:0): avc: denied { write } for pid=11023
exe=/usr/bin/pg_dump path=/~/dcf_20050404_050510.dmp
dev=sda3 ino=5522308 scontext=user_u:system_r:postgresql_t
tcontext=user_u:object_r:file_t tclass=file
Hmm, what is the SELinuxWe disabled
the SELinux protection for the postgres deamon and were able to
successfully run pg_dump on our new Red Hat Enterprise Linux AS 4
postgres. Do you have any opinion about this 'fix'?
Jim Apsey labeling for pg_dump? Try
$ ls -Z /usr/bin/pg_dump
-rwxr-xr-x root root system_u:object_r:bin_t /usr/bin/pg_dump
If you get something other than that, try "/sbin/restorecon -R /usr/bin"
as root; if that doesn't fix it, you probably need to update your
SELinux policy (RPM selinux-policy-targeted). I am not entirely sure
whether a policy RPM update automatically does the equivalent of
"/sbin/restorecon -R /", but if you don't see the right context after
an update, that's what I'd suggest.
Here's Postgres rpm on the machine in question:
postgresql-7.4.6-1.RHEL4.2
postgresql-server-7.4.6-1.RHEL4.2
I think that was what went out on the RHEL4 CD-ROMs, but why aren't you
running up2date? There are serious known bugs in that version. If
you're paying Red Hat for support, you should be using that support ;-)
regards, tom lane
Thank you once again Tom Lane. We disabled the SELinux protection for
the postgres daemon and were able to successfully run pg_dump on our
new Red Hat Enterprise Linux AS 4 postgres. Do you have any opinion
about this 'fix'? We have hired a Linux professional and he installed
AS 4 on our new Dell Server. I don't know how we keep things
up-to-date with up2date anymore.
Jim Apsey
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] LOST REFERENTIAL INTEGRITY
Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. Within that I have a table with referential integrity constraints which no longer work. I do not know how to disable referential integrity on a column in a table. I do not know how to view what Postgres thinks my referential integrity constraints are on this table. I do ...-c"\d table_with_referential_integrity" and here's what I get: [~]$ mpt -c"\d pat_emp_ins" Table "pat_emp_ins" Attribute | Type | Modifier ---+---+-- pat_id| text | not null ins_co_id | text | not null employer_id | text | not null insurance_group| text | note| text | print_note_primary | boolean | print_note_secondary | boolean | Indices: pat_emp_ins_employer_id_key, pat_emp_ins_ins_co_id_key, pat_emp_ins_pat_id_key [~ create_tables_for_database]$ And here is the SQL I used to generate this table: -- create table pat_emp_ins (pat_id text not null references patient, ins_co_id text not null references insurance_company, employer_id text not null references employer, insurance_group text, note text, print_note_primary boolean, print_note_secondary boolean, unique(pat_id,ins_co_id,employer_id)); -- Problem is, my users using my application are able to insert rows into "pat_emp_ins" table which have values for "employer_id" and/or "ins_co_id" which do not exist in the referenced tables. This seems to have happened recently but I do not know how recently. This application has been running production since 2003-11-07. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] LOST REFERENTIAL INTEGRITY
Tom Lane wrote: "Jimmie H. Apsey" <[EMAIL PROTECTED]> writes: Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. 7.1 is mighty ancient, but ... I do not know how to disable referential integrity on a column in a table. I do not know how to view what Postgres thinks my referential integrity constraints are on this table. In that version, you'd be talking about triggers on the tables, and it seems that psql's \d didn't learn to display triggers till later. You'll need to look at pg_trigger directly. For example, regression=# select version(); version -- PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3 (1 row) regression=# create table foo (f1 int primary key); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE regression=# create table bar (f2 int references foo); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE regression=# \d foo Table "foo" Attribute | Type | Modifier ---+-+-- f1| integer | not null Index: foo_pkey -- drat, no trigger display regression=# select * from pg_trigger order by oid desc limit 3; tgrelid |tgname| tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs -+--+++---++--+---+--++-++ 2913646 | RI_ConstraintTrigger_2913673 | 1655 | 17 | t | t | | 2913659 | f| f | 6 || \000bar\000foo\000UNSPECIFIED\000f2\000f1\000 2913646 | RI_ConstraintTrigger_2913671 | 1654 | 9 | t | t | | 2913659 | f| f | 6 || \000bar\000foo\000UNSPECIFIED\000f2\000f1\000 2913659 | RI_ConstraintTrigger_2913669 | 1644 | 21 | t | t | | 2913646 | f| f | 6 || \000bar\000foo\000UNSPECIFIED\000f2\000f1\000 (3 rows) regression=# Each FK constraint should have three associated triggers (two on the referencing table, one on the referenced table). You can sort out which is which by looking at the tgargs field --- note how the referencing and referenced table and field names are embedded in that. I suspect that some of these triggers got dropped or disabled. If you don't find all three triggers for some one constraint, the best bet is to drop any remaining triggers from the set and then issue ALTER TABLE ADD FOREIGN KEY to re-make a consistent trigger set. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org OH, that's very scary for me that triggers can vanish/be eliminated w/o my direct action. Yes, I do now see that the triggers on my production table have been lost. I built a test table and they appear as expected. Is there any way I can prevent this or become aware that something had done this to my production database? On my machine: [~]$ mpt -c"select version();" version - PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) [~]$ I'll now go, as suggested by you, drop triggers on the test database to see to it that it actually works as expected. Then I'll re-build the FK triggers within the test database before I do it to the production database.
Re: [GENERAL] LOST REFERENTIAL INTEGRITY
Tom Lane wrote: "Jimmie H. Apsey" <[EMAIL PROTECTED]> writes: Each FK constraint should have three associated triggers (two on the referencing table, one on the referenced table). OH, that's very scary for me that triggers can vanish/be eliminated w/o my direct action. Yes, I do now see that the triggers on my production table have been lost. I built a test table and they appear as expected. Is there any way I can prevent this or become aware that something had done this to my production database? If you are still running 7.1 you obviously do not know the meaning of the word "fear" ;-) --- it not only has lots of since-fixed bugs, but at that time we hadn't yet solved the transaction ID wraparound problem, which means your DB is guaranteed to self-destruct once you reach the 4-billion-transaction mark. I'd recommend an upgrade to 7.4.5 at your earliest convenience. regards, tom lane I have kept up-to-date our Red Hat kernels as you can probably see from the Linux 2.4.9-e.49smp kernel. Am I required to maintain my own version of Postgres alongside and compiled into Red Hat's latest and greatest kernel? If that's true, WHEW! I wonder what version of Postgres is installed in Red Hat's latest kernel of AS 3.0?
Re: [GENERAL] Verifying Referential Integrity
Geisler, Jim wrote: Message So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity. Are there any recommended methods or utilities for checking referential integrity in a PostgreSQL database? Of course, Tom Lane suggested I look at the pg_trigger table. I suppose I'll have to do this from time to time. His good point on an old database system (7.1.3) like what is included within Red Hat AS 2.1 is as he wrote, "Each FK constraint should have three associated triggers (two on the referencing table, one on the referenced table). You can sort out which is which by looking at the tgargs field --- note how the referencing and referenced table and field names are embedded in that. I suspect that some of these triggers got dropped or disabled. If you don't find all three triggers for some one constraint, the best bet is to drop any remaining triggers from the set and then issue ALTERTABLE ADD FOREIGN KEY to re-make a consistent trigger set." I did what he suggested and then re-created the offending table and altered the other offending table. Now, my database has working referential integrity between the two tables involved. Jim Apsey
Re: [GENERAL] Verifying Referential Integrity
Tom Lane wrote: "Geisler, Jim" <[EMAIL PROTECTED]> writes: So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity. What are you trying to accomplish here, and in what PG version? Are you trying to check that PG thinks that a foreign-key relationship is installed? In recent versions psql's "\d" will tell you that. If you're dealing with an old version you might have to look directly at the system catalogs. Are you not trusting that an active foreign-key relationship has been correctly enforced? Then I think you want to do some kind of JOIN query to see if you can find any rows with no master row. (You could actually do this by temporarily creating a new, redundant FK constraint; but if you are feeling that paranoid you're likely not going to trust the system's answer anyway...) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Of course, I use the most simple method of selecting all values which are not in RI_table, e.g. # select * from user_table where user_table.value not in (select RI.value from RI_table); I had to do this often when I ported from one Postgres-like database (namely Illustra) into my current Postgres database. I noticed some rows would not insert into my target table from a text file containing my source table. So, I created a table like my desired target table but without referential integrity. Then, on the table w/o RI I did the above. But, as you can see, I do things as simply as possible. With great regard for the pros out here in Postgres Land, Jim Apsey
[GENERAL] Install pgsql directory
I got a server from DELL which contained a Red Hat Linux AS 3 in the box. It contained no postgresql server which I got from an earlier link, http://archives.postgresql.org/pgsql-admin/2003-11/msg00368.php When I did rpm of everything I end up with a non-working postgresql. This may be because there is no /usr/local/pgsql directory. How/what do I need to do to get the directory structure wherein resides the database data. Thank you,
[GENERAL] subscribe missing?
When I tried to subscribe I got: Not Found The requested URL /mj/mj_wwwusr was not found on this server. Apache/1.3.33 Server at webmail.anachronic.net Port 80 Thank you, Jim Apsey
[GENERAL] Performance differences 7.1 to 7.3
Hello all, I have just loaded Postgresql 7.3.6-7 onto a new server on the recommendation of Tom Lane. It is part of Red Hat AS 3. I have Postgresql 7.1.3-5 running on Red Hat AS 2.1. I have a simple view from which I select on both systems. The 7.3.6-7 version requires 18+ seconds to do a select from a particular view. The 7.1.3-5 version requires 3+ seconds to select from the same view. On the 7.1.3-5 version I do: [EMAIL PROTECTED] ~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;" count --- 33377 (1 row) 0.000u 0.010s 0:03.55 0.2%0+0k 0+0io 332pf+0w [EMAIL PROTECTED] ~]$ And on 7.3.6-7 version I do: [EMAIL PROTECTED] ~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;" count --- 33377 (1 row) 0.010u 0.000s 0:18.38 0.0%0+0k 0+0io 362pf+0w [EMAIL PROTECTED] ~]$ Does anyone have any clues as to where I should be looking for tuning/whatever? Jim Apsey -- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Performance differences 7.1 to 7.3
I think my indexes are OK.
I have no settings for 'shared memory buffers' in postgresql.conf on
either system. Both systems have same, i.e.
#sort_mem = 512
#shared_buffers = 2*max_connections # min 16
#fsync = true
Do you, or any of you, have any other suggestions for why such a simple
"select count(*) from tpv;" should take six times as long on our latest
and greatest dual processor server with almost the latest and greatest
postgresql? View tpv is a three table join which takes 3.38 seconds on
the 'old' system and 18.09 seconds on the 'new' system.
Jim Apsey
--
Dann Corbit wrote:
-----Original Message-
From: Jimmie H. Apsey [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 14, 2004 1:18 PM
To: Dann Corbit
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Performance differences 7.1 to 7.3
On the 'old' Red Hat AS 2.1 here is the results of explain and the
query:
[ ~]$ time /usr/bin/psql mpt -c"explain select count(*) from tpv;"
NOTICE: QUERY PLAN:
Aggregate (cost=4563.87..4563.87 rows=1 width=56)
-> Nested Loop (cost=870.92..4563.01 rows=342 width=56)
-> Hash Join (cost=870.92..3869.17 rows=342 width=44)
-> Seq Scan on treatment_plan (cost=0.00..956.66
rows=14844 width=28)
-> Hash (cost=602.33..602.33 rows=24033 width=16)
-> Seq Scan on treatment_plan_header
(cost=0.00..602.33 rows=24033 width=16)
-> Index Scan using ada_code_pkey on ada_code (cost=0.00..2.01
rows=1 width=12)
EXPLAIN
0.010u 0.000s 0:00.03 33.3%0+0k 0+0io 332pf+0w
[~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;"
count
---
33439
(1 row)
0.010u 0.000s 0:03.10 0.3%0+0k 0+0io 332pf+0w
[EMAIL PROTECTED] ~]$
On the 'new' system:
[ ~]$ /usr/bin/psql mpt -c"explain select count(*) from tpv;"
QUERY
PLAN
---
Aggregate (cost=202529.15..202529.15 rows=1 width=45)
-> Nested Loop (cost=6262.46..202496.78 rows=12948 width=45)
Join Filter: ("outer".service_code = ("inner".ada_code)::text)
-> Merge Join (cost=6262.46..6754.54 rows=12933 width=36)
Merge Cond: (("outer".appointment_order =
"inner".appointment_order) AND ("outer".pat_id = "inner".pat_id))
-> Sort (cost=2335.37..2395.35 rows=23992 width=14)
Sort Key: treatment_plan_header.appointment_order,
treatment_plan_header.pat_id
-> Seq Scan on treatment_plan_header
(cost=0.00..589.92 rows=23992 width=14)
-> Sort (cost=3927.09..4016.27 rows=35672 width=22)
Sort Key: treatment_plan.appointment_order,
treatment_plan.pat_id
-> Seq Scan on treatment_plan (cost=0.00..800.60
rows=35672 width=22)
Filter: (amount IS NULL)
-> Seq Scan on ada_code (cost=0.00..10.06 rows=406 width=9)
(13 rows)
[ ~]$
Where do I go to get clues about the results of "explain"?
The command syntax:
http://www.postgresql.org/docs/current/static/sql-explain.html
A brief explanation:
http://www.freebsddiary.org/postgresql-analyze.php
A longer explanation:
http://www.postgresql.org/docs/7.4/interactive/performance-tips.html
A nuts and bolts look at how PostgreSQL performs queries:
http://candle.pha.pa.us/main/writings/pgsql/performance.pdf
<<
Re: [GENERAL] Performance differences 7.1 to 7.3
Thank you Tom, your suggestion was exactly what I needed.
Two tables in view "tpv" were being joined on a column with different
data types.
One was "text" and the other one was "varchar(10)". The 'old' system
did not complain.
The 'new' system does not allow this sloppyness on my part. When I
made both table columns
the same, i.e. "varchar(10)" the time to execute the command became
less on the 'new' system than on the 'old' system.
Then I went to postgresql.org to make a contribution but the PayPal
thing ALWAYS trips me up.
When this PayPal thing is resolved, I will make a contribution.
It would be much better if postgresql.org accepted credit cards rather
than PayPal.
Anyway, I am most grateful to this pgsql_general list for answering my
questions which are often 'stupid' and you will soon
see my contribution.
Thank you,
Jim Apsey
---
Tom Lane wrote:
"Jimmie H. Apsey" <[EMAIL PROTECTED]> writes:
On the 'old' Red Hat AS 2.1 here is the results of explain and the query:
The major problem seems to be that the old system is using a nestloop
with inner indexscan on ada_code:
-> Nested Loop (cost=870.92..4563.01 rows=342 width=56)
...
-> Index Scan using ada_code_pkey on ada_code (cost=0.00..2.01 rows=1 width=12)
where the new system is using an inner seqscan:
-> Nested Loop (cost=6262.46..202496.78 rows=12948 width=45)
Join Filter: ("outer".service_code = ("inner".ada_code)::text)
...
-> Seq Scan on ada_code (cost=0.00..10.06 rows=406 width=9)
The planner is well aware that this is a bad plan (note the much higher
cost estimate) --- I can only suppose that it is not able to select an
indexscan, most likely because of a datatype compatibility problem.
The cast to text appearing in the join condition is a tad suspicious
in this context. What are the data types of service_code and ada_code,
and why aren't they the same?
IIRC, 7.3 is a lot less cavalier than 7.1 about the semantic differences
between char(n) and varchar(n)/text comparisons. It's fairly likely
that the 7.1 plan is playing fast and loose with the comparison
semantics in order to generate an indexscan plan. 7.3 won't do that.
You need to make the column types the same to get good performance in
7.3 ... but if this is a foreign-key-reference arrangement, they ought
to be the same anyway.
regards, tom lane
Re: [GENERAL] Performance differences 7.1 to 7.3
On the 'old' Red Hat AS 2.1 here is the results of explain and the query:
[ ~]$ time /usr/bin/psql mpt -c"explain select count(*) from tpv;"
NOTICE: QUERY PLAN:
Aggregate (cost=4563.87..4563.87 rows=1 width=56)
-> Nested Loop (cost=870.92..4563.01 rows=342 width=56)
-> Hash Join (cost=870.92..3869.17 rows=342 width=44)
-> Seq Scan on treatment_plan (cost=0.00..956.66
rows=14844 width=28)
-> Hash (cost=602.33..602.33 rows=24033 width=16)
-> Seq Scan on treatment_plan_header
(cost=0.00..602.33 rows=24033 width=16)
-> Index Scan using ada_code_pkey on ada_code (cost=0.00..2.01
rows=1 width=12)
EXPLAIN
0.010u 0.000s 0:00.03 33.3%0+0k 0+0io 332pf+0w
[~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;"
count
---
33439
(1 row)
0.010u 0.000s 0:03.10 0.3%0+0k 0+0io 332pf+0w
[EMAIL PROTECTED] ~]$
On the 'new' system:
[ ~]$ /usr/bin/psql mpt -c"explain select count(*) from tpv;"
QUERY
PLAN
---
Aggregate (cost=202529.15..202529.15 rows=1 width=45)
-> Nested Loop (cost=6262.46..202496.78 rows=12948 width=45)
Join Filter: ("outer".service_code = ("inner".ada_code)::text)
-> Merge Join (cost=6262.46..6754.54 rows=12933 width=36)
Merge Cond: (("outer".appointment_order =
"inner".appointment_order) AND ("outer".pat_id = "inner".pat_id))
-> Sort (cost=2335.37..2395.35 rows=23992 width=14)
Sort Key: treatment_plan_header.appointment_order,
treatment_plan_header.pat_id
-> Seq Scan on treatment_plan_header
(cost=0.00..589.92 rows=23992 width=14)
-> Sort (cost=3927.09..4016.27 rows=35672 width=22)
Sort Key: treatment_plan.appointment_order,
treatment_plan.pat_id
-> Seq Scan on treatment_plan (cost=0.00..800.60
rows=35672 width=22)
Filter: (amount IS NULL)
-> Seq Scan on ada_code (cost=0.00..10.06 rows=406 width=9)
(13 rows)
[ ~]$
Where do I go to get clues about the results of "explain"?
Jim Apsey
Dann Corbit wrote:
I assume that the schema is identical on both systems.
After running vacuum on both systems [for each of the underlying tables
in tpv], what does explain say about the queries?
Are the shared memory buffers identical on both systems?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jimmie H. Apsey
Sent: Monday, December 13, 2004 2:43 PM
To: [EMAIL PROTECTED]
Subject: [GENERAL] Performance differences 7.1 to 7.3
Hello all,
I have just loaded Postgresql 7.3.6-7 onto a new server on the
recommendation of Tom Lane. It is part of Red Hat AS 3.
I have Postgresql 7.1.3-5 running on Red Hat AS 2.1.
I have a simple view from which I select on both systems. The 7.3.6-7
version requires 18+ seconds to do a select from a particular view.
The 7.1.3-5 version requires 3+ seconds to select from the same view.
On the 7.1.3-5 version I do:
[EMAIL PROTECTED] ~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;"
count
---
33377
(1 row)
0.000u 0.010s 0:03.55 0.2%0+0k 0+0io 332pf+0w
[EMAIL PROTECTED] ~]$
And on 7.3.6-7 version I do:
[EMAIL PROTECTED] ~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;"
count
---
33377
(1 row)
0.010u 0.000s 0:18.38 0.0%0+0k 0+0io 362pf+0w
[EMAIL PROTECTED] ~]$
Does anyone have any clues as to where I should be looking for
tuning/whatever?
Jim Apsey
--
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] A View to Share
I could not select from pg_catalog. Is it called something else in
Postgres 7.1.3?
Postgres 7.1.3 is included in RED HAT ADVANCED SERVER 2.1 which I am using.
Jim Apsey
[EMAIL PROTECTED] wrote:
This is an a view I have found somewhat useful in helping partially
automate definition of functions during database design and testing:
CREATE VIEW public.function_elements AS
SELECT
pg_catalog.pg_class.relname,
pg_attribute.attname,
typname,
pg_attribute.attnum,
cast('l_'||pg_attribute.attname as varchar) as local_variable_name,
cast('l_'||pg_attribute.attname||' ALIAS FOR $'||btrim(TO_CHAR(attnum,
'999;')) as varchar) as local_alias_declaration,
cast(pg_attribute.attname||' = l_'||pg_attribute.attname as varchar) as
equality_expression
FROM pg_attribute LEFT JOIN pg_type ON pg_type.oid =
pg_attribute.atttypid, pg_catalog.pg_class
WHERE pg_catalog.pg_attribute.attrelid = pg_class.oid
AND pg_catalog.pg_attribute.attstattarget <> 0
ORDER BY
pg_catalog.pg_class.relname,
pg_catalog.pg_attribute.attnum
~Berend Tober
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Changing DB ownership
Why would you want to do that?
Why not do it an easier way and dump the database and restore it into
your new database?
There's got to be a lot of stuff to consider when doing something as
radical as renaming a database.
I am a developer of dental computer systems using Postgres within Red
Hat Advanced Server 2.1.
Jim Apsey
Christopher Murtagh wrote:
Dumb question maybe, but how does one change database ownership? I've
tried several permutations of:
ALTER DATABASE SET ("|'| )owner("|'| ) TO ("|'| )newowner("|'| );
and I tried looking at pg_database and I *was* able to hack this (got a
clue half way through writing this email... sorry):
UPDATE pg_database SET datdba = 504 WHERE datname='chris';
And that worked as expected, but I'm worried that I might have missed
something somewhere else. Will the above UPDATE cause problems down the
road?
Any info/clue would be much appreciated. Thanks in advance.
Cheers,
Chris
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
