[GENERAL] Checking for schedule conflicts
Given the tables defined below, what's the easiest way to check for schedule
conflicts?
So far, the only way I've come up with is to create a huge, multi-dimensional
array in PHP, with a data element for every minute of all time taken up by
all events, and then check for any of these minutes to be set as I go through
all the records. (ugh!)
But, how could I do this in the database?
But I'd like to see something like
"select count(*) FROM events, sched
WHERE sched.date=$date
AND events.id=sched.events_id
...
GROUP BY date, start1 "
And here's where I get stumped. You can't group by start or end because we
need to check if they OVERLAP any other records on the same date.
Ideas?
// Sometimes, recurring events
create table events (
id serial not null primary key,
title varchar
);
// date=MMDD, start/end: HH:MM (24-hour)
create table sched (
events_id integer not null references events(id),
date integer not null,
start integer not null,
end integer not null
);
insert into events (title)
VALUES ('Tuesday Pickup');
insert into sched(events_id, date, start, end)
VALUES (1, 20050308, 0900, 1300);
insert into sched (events_id, date, start, end)
VALUES (1, 20050315, 0900, 1300);
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Hash problem
On Sat, 12 Mar 2005 02:18:55 -0500, Madison Kelly <[EMAIL PROTECTED]> wrote: > Hi all, > >I am back to trying to get the forum software called 'Ikonboard' > working under postgres (it's advertised as being supported but the > developers confirmed their pgSQL person left a while back). > >At the moment I am (well, the program is) getting trying to perform > this query: > > SELECT * FROM tf_forum_posts WHERE FORUM_ID = HASH(0x868d4e0) AND > TOPIC_ID = AND QUEUED = '0' ORDER BY POST_DATE LIMIT 2; > > which throws this error: > > ERROR: parser: parse error at or near "x868d4e0" at character 53 > >Most of the problems so far have stemmed from changes from (I think) > pgSQL 7.2 (the last version the program was actively tested on) to 7.4. > I have yet to test it under 8.0. Does this seem like an obvious syntax > error in the newer versions of pgSQL? If so, what is a valid substitution. This is a syntax "error" in Perl (assuming Ikonboard is a Perl app; it's hard to see from their homepage) of the kind you get when interpolating a hash reference where a scalar value is expected, e.g.: perl -e '%a = (); print \%a;' You'll need to find the offending section of the code and work out what's gone wrong; also "TOPIC_ID" seems to be missing a value. Possibly - and this is speculation - a preceding query has thrown an error which the app isn't dealing with (e.g. due to some change in PostgreSQL between 7.2 and 7.4, possibly a "LIMIT x,y" type clause or an attempt to insert a value which is longer than the database field etc.) and this is causing the insertion of invalid values in the above statement. Ian Barwick ---(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: [JDBC] [GENERAL] MS Access to PostgreSQL
On Fri, 2005-03-11 at 17:52 +, Brian Gunning wrote:
[rearranged quoting]
> From: [EMAIL PROTECTED]
> --- William Shatner <[EMAIL PROTECTED]> wrote:
> > I have recently migrated from MS Access to
> > PostgreSQL.Previously I had
> > a SQL command
> >
> > ResultSet aGroupResultSet =
> > aGroupPathStmt.executeQuery(
> > "SELECT \"groupID\",\"fullpath\" FROM
> > \"groups\" WHERE
> > \"fullpath\" Like '" +
> > aPath + "'");
> >
> >
> > where aPath was equal to 'folder\another folder\%'.
> >
> I don't think the problem isn't with the backslashes. They are escaped and
> seem to be working fine e.g. to insert a backslash in Access I had to use
> one escape character ('\\') whereas in PostgreSQL four backslashes ('')
> are required. The line that inserts the % is as follows...
>
> String aPath = group.getPath() + aOldGroupName + "\\%";
are the backslashes in group.getPath() + aOldGroupName escaped ?
does aGroupPathStmt.executeQuery() escape backslashes ?
did you print the SQL statement to screen or logfile to make sure
what is actually sent to executeQuery() ?
does your language support placeholders ? this kind of problems are
usually easier to deal with with them.
gnari
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Checking for schedule conflicts
On Sat, 2005-03-12 at 00:13 -0800, Benjamin Smith wrote: > Given the tables defined below, what's the easiest way to check for schedule > conflicts? > > So far, the only way I've come up with is to create a huge, multi-dimensional > array in PHP, with a data element for every minute of all time taken up by > all events, and then check for any of these minutes to be set as I go through > all the records. (ugh!) > > But, how could I do this in the database? > > But I'd like to see something like > "select count(*) FROM events, sched > WHERE sched.date=$date > AND events.id=sched.events_id > ... > GROUP BY date, start HAVING count(*) >1 " > > And here's where I get stumped. You can't group by start or end because we > need to check if they OVERLAP any other records on the same date. > > Ideas? use the OVERLAPS operator ? http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html gnari ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Partial or incomplete dates
Hello all, this is my first posting to this list. I have been using MySQL for a few years with a web application for displaying my genealogy database, a FoxPro database from The Master Genealogist from which I'm dumping a subset with a Perl script and the DBD::XBase module. I'm now in the process of developing my own genealogy program, and I have been absolutely thrilled by the prospect of using PostgreSQL for this venture. My first stumbling block is this: How do I store incomplete or partial dates in PostgreSQL? I ran a Google search on 'postgresql "partial dates"' and came up with this thread as the only significant match: http://archives.postgresql.org/pgsql-general/2003-01/msg00888.php> In MySQL, the checking on dates is very relaxed, so it's totally legal to enter a date as '1731-00-00', and let your own program logic decide that this means just the year 1731. Do I have to make my own data type to store and manipulate partial dates, or has somebody already done it? Or is there a way to use relaxed date checking in PostgreSQL? -- Leif Biberg Kristensen http://solumslekt.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Unique Indexes
Could you describe the problem you're trying to solve? It might be easier to help if we knew the ultimate purpose of what you're trying to do. Something like "I want to do this because" Sure. Lets say I have 3 tables: surnames - surnameid serial (Primary Key), surname varchar (Unique) firstnames - firstnameid serial (Primary Key), firstname varchar (Unique) users - userid serial (Primary Key), firstnameid, surnameid I enter the following firstname, surname combinations joe, smith john, doe jason, jones john, smith In the first case, the first and last names are entered into the tables with no problems since the tables are empty. After entering, I need to get the "firstnameid" and "surnameid" of the successful insertions (let's assume I can do this with no problems using some magical code I've written). Once I have them, I create a new row in the "users" table with these ids. We then move to the second case where the same thing happens. Everything is satisfied so we grab the "firstnameid" and "surnameid" from the inserts of john and doe and create a new row in the "users" table. The same situation applies to the third case. The problem comes up in the fourth case. We can insert the firstname fine, but when we try to insert "smith" into the "surnames" table, it fails because we've already entered "smith". What I'd like to do is find out the surnameid of the "smith" already in the "surnames" table. With that, I can create a new row in the "users" table with the firstnameid we have and the surnameid of the "smith" already in the "surnames" table. As I said before, I know that after I find out the row didn't insert due to the unique constraint I can use a SELECT to get the surnameid, but I'd like to avoid this 'overhead'. I'd much prefer if Postgres itself had some way to do this using something like triggers. I hope that explains everything properly. Thanks! _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Peculiar performance observation....
I have a rather peculiar performance observation and would welcome any
feedback on this.
First off, the main table (well, part of it.. it is quite large..):
Table "table1"
Column | Type |
Modifiers
+--
+-
id | integer | not null default
nextval('master.id_seq'::text)
user_id| integer |
... (skipping about 20 columns)
category | ltree[] |
somedata | text | not null
Indexes:
"table1_pkey" primary key, btree (id)
"table1_category_full_gist_idx" gist (category)
"table1_id_idx" btree (id)
"table1_fti_idx" gist (fti) WHERE ((status)::text = 'open'::text)
"table1_user_id_idx" btree (user_id)
database=> explain analyze select id from table1 where category <@ 'a.b';
QUERY
PLAN
-
-
-
Index Scan using table1_category_full_gist_idx on jobdata
(cost=0.00..113.48 rows=28 width=4) (actual time=43.814..12201.528
rows=1943 loops=1)
Index Cond: (category <@ 'a.b'::ltree)
Filter: (category <@ 'a.b'::ltree)
Total runtime: 1.258 ms
If I do this:
create table yuck (id integer, category ltree[]);
insert into yuck select id, category from table1;
create index category_idx on yuck using gist(category);
vacuum analyze yuck;
jobs=> explain analyze select id from table1 where id in (select id from
yuck where category <@ 'a.b');
QUERY PLAN
-
-
-
Nested Loop (cost=108.64..114.28 rows=1 width=52) (actual
time=654.645..1245.212 rows=1943 loops=1)
-> HashAggregate (cost=108.64..108.64 rows=1 width=4) (actual
time=654.202..690.709 rows=1943 loops=1)
-> Index Scan using category_idx on yuck (cost=0.00..108.57
rows=28 width=4) (actual time=2.046..623.436 rows=1943 loops=1)
Index Cond: (category <@ 'a.b'::ltree)
Filter: (category <@ 'a.b'::ltree)
-> Index Scan using table1_pkey on jobdata (cost=0.00..5.64 rows=1
width=52) (actual time=0.219..0.235 rows=1 loops=1943)
Index Cond: (table1.id = "outer".id)
Total runtime: 1261.551 ms
(8 rows)
In the first query, my hard disk trashes audibly the entire 12 seconds
(this is actually the best run I could get, it is usually closer to 20
seconds), the second query runs almost effortlessly.. I've tried
reindexing, even dropping the index and recreating it but nothing I do
helps at all.
Now keep in mind that I do all of my development on painfully slow
hardware in order to make any performance issues really stand out. But,
I've done this on production servers too with an equal performance
improvement noticed.
I just can't figure out why this second query is so much faster, I feel
like I must have done something very wrong in my schema design or
something to be suffering this sort of a performance loss. Any idea
what I can do about this?
Thanks as always!
- Greg
---(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] Peculiar performance observation....
Something even more peculiar (at least it seems to me..)...
If I drop the index table1_category_gist_idx, I get this:
jobs=> explain analyze select id from table1 where category <@ 'a.b'
ORDER BY category;
QUERY PLAN
-
--
Sort (cost=7568.55..7568.62 rows=28 width=52) (actual
time=4842.691..4854.468 rows=1943 loops=1)
Sort Key: category
-> Seq Scan on jobdata (cost=0.00..7567.88 rows=28 width=52) (actual
time=11.498..4800.907 rows=1943 loops=1)
Filter: (category <@ 'a.b'::ltree)
Total runtime: 4871.076 ms
(5 rows)
.. no disk thrashing all over the place..
I'm really perplexed about this one..;-(
- Greg
>I have a rather peculiar performance observation and would welcome any
>feedback on this.
>
>First off, the main table (well, part of it.. it is quite large..):
>
>
> Table "table1"
> Column | Type |
> Modifiers
>+--
>+-
> id | integer | not null default
>nextval('master.id_seq'::text)
> user_id| integer |
> ... (skipping about 20 columns)
> category | ltree[] |
> somedata | text | not null
>
>
>
>Indexes:
>"table1_pkey" primary key, btree (id)
>"table1_category_full_gist_idx" gist (category)
>"table1_id_idx" btree (id)
>"table1_fti_idx" gist (fti) WHERE ((status)::text = 'open'::text)
>"table1_user_id_idx" btree (user_id)
>
>
>database=> explain analyze select id from table1 where category <@ 'a.b';
>QUERY
>PLAN
>-
>-
>-
> Index Scan using table1_category_full_gist_idx on jobdata
>(cost=0.00..113.48 rows=28 width=4) (actual time=43.814..12201.528
>rows=1943 loops=1)
> Index Cond: (category <@ 'a.b'::ltree)
> Filter: (category <@ 'a.b'::ltree)
> Total runtime: 1.258 ms
>
>
>If I do this:
>
>create table yuck (id integer, category ltree[]);
>insert into yuck select id, category from table1;
>create index category_idx on yuck using gist(category);
>vacuum analyze yuck;
>jobs=> explain analyze select id from table1 where id in (select id from
>yuck where category <@ 'a.b');
> QUERY PLAN
>
>-
>-
>-
> Nested Loop (cost=108.64..114.28 rows=1 width=52) (actual
>time=654.645..1245.212 rows=1943 loops=1)
> -> HashAggregate (cost=108.64..108.64 rows=1 width=4) (actual
>time=654.202..690.709 rows=1943 loops=1)
> -> Index Scan using category_idx on yuck (cost=0.00..108.57
>rows=28 width=4) (actual time=2.046..623.436 rows=1943 loops=1)
> Index Cond: (category <@ 'a.b'::ltree)
> Filter: (category <@ 'a.b'::ltree)
> -> Index Scan using table1_pkey on jobdata (cost=0.00..5.64 rows=1
>width=52) (actual time=0.219..0.235 rows=1 loops=1943)
> Index Cond: (table1.id = "outer".id)
> Total runtime: 1261.551 ms
>(8 rows)
>
>
>In the first query, my hard disk trashes audibly the entire 12 seconds
>(this is actually the best run I could get, it is usually closer to 20
>seconds), the second query runs almost effortlessly.. I've tried
>reindexing, even dropping the index and recreating it but nothing I do
>helps at all.
>
>Now keep in mind that I do all of my development on painfully slow
>hardware in order to make any performance issues really stand out. But,
>I've done this on production servers too with an equal performance
>improvement noticed.
>
>I just can't figure out why this second query is so much faster, I feel
>like I must have done something very wrong in my schema design or
>something to be suffering this sort of a performance loss. Any idea
>what I can do about this?
>
>Thanks as always!
>
>- Greg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Hash problem
Hi Madison, When you migrate from PG7.2 to PG7.4 didn't you change the Perl too. When we migrate the PG, we also change the OS verison and the Perl and etc. So this may be Perl migration error. We have some in our own scripts (from the same type you posted). As Ian says, you should check the Perl code and how parameters form Perl were passed to Postgres. Kaloyan Madison Kelly wrote: Hi all, I am back to trying to get the forum software called 'Ikonboard' working under postgres (it's advertised as being supported but the developers confirmed their pgSQL person left a while back). At the moment I am (well, the program is) getting trying to perform this query: SELECT * FROM tf_forum_posts WHERE FORUM_ID = HASH(0x868d4e0) AND TOPIC_ID = AND QUEUED = '0' ORDER BY POST_DATE LIMIT 2; which throws this error: ERROR: parser: parse error at or near "x868d4e0" at character 53 Most of the problems so far have stemmed from changes from (I think) pgSQL 7.2 (the last version the program was actively tested on) to 7.4. I have yet to test it under 8.0. Does this seem like an obvious syntax error in the newer versions of pgSQL? If so, what is a valid substitution. Thanks a lot! (I'm not -quite- a n00b but I am still learning. :) ) Madison ---(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] postgres 8 settings
Hi,
- Oops, forgot to mention that!!
It is Redhat Linux and storage system is SCSI. Also it has 4 processors.
- I would say that there is no particular reason for using a particular
setting except that there were no memory constraints in my case and
they seem to work fine with Postgres 7.4.
I had set them to values that worked while I was benchmarking some data
with Postgres 7.4.
- I have 4 processes which query the database at one point in time. There
are some 5-6 tables which have approx 6-8 million rows and they are the
ones which are the major bottlenecks.
The only reason for setting fsync to false was that the updates to these
tables were taking major time and just setting fysnc to false saved a lot of
time for me. Also, I am not using pg_autovacuum but instead run vaccum for
tables as and when required. Btw, Can running pg_autovaccum as a daemon
process affect performance and is it effective??
Also, a particular query which was taking a min or less with postgres 7 is
taking 15 mins now.
Here is the query plan for the query both for postgres 7.4.6 and postgres 8:
Query plan with Postgres 7.4.6:
explain SELECT cm_AllocData1_temp.allocation, cm_QuotaState.tril_gid,
cm_QuotaState.Period, cm_Quota.tril_gid FROM cm_AllocData1_temp,
cm_QuotaState, cm_Quota WHERE ( ( ( ( ( cm_AllocData1_temp.recipient =
cm_QuotaState.SalesTeam ) AND ( ( cm_AllocData1_temp.transDate ) >= (
cm_QuotaState.StartDate ) ) ) AND ( ( cm_AllocData1_temp.transDate ) < (
cm_QuotaState.EndDate ) ) ) AND ( cm_Quota.tril_gid = cm_QuotaState.Quota
) ) AND ( cm_Quota.Model = 'N151VZWTJN0DUVPBUVZSU31JQ2OU' ) ) AND (
cm_Quota.Status = 'Enabled' )
QUERY PLAN
---
Hash Join (cost=30.69..18800.11 rows=459 width=109)
Hash Cond: (("outer".quota)::text = ("inner".tril_gid)::text)
-> Nested Loop (cost=0.00..18720.38 rows=8889 width=109)
-> Seq Scan on cm_allocdata1_temp (cost=0.00..20.00 rows=1000
width=7
8)
-> Index Scan using statesbysalesteam_i on cm_quotastate
(cost=0.00..
18.54 rows=9 width=126)
Index Cond: ((("outer".recipient)::text =
(cm_quotastate.salestea
m)::text) AND ("outer".transdate >= cm_quotastate.startdate) AND
("outer".transd
ate < cm_quotastate.enddate))
-> Hash (cost=30.65..30.65 rows=16 width=36)
-> Seq Scan on cm_quota (cost=0.00..30.65 rows=16 width=36)
Filter: (((model)::text =
'N151VZWTJN0DUVPBUVZSU31JQ2OU'::tex
t) AND ((status)::text = 'Enabled'::text))
(9 rows)
Query plan with Postgres 8:
explain SELECT cm_AllocData1_temp.allocation, cm_QuotaState.tril_gid,
cm_QuotaState.Period, cm_Quota.tril_gid FROM cm_AllocData1_temp,
cm_QuotaState, cm_Quota WHERE ( ( ( ( ( cm_AllocData1_temp.recipient =
cm_QuotaState.SalesTeam ) AND ( ( cm_AllocData1_temp.transDate ) >= (
cm_QuotaState.StartDate ) ) ) AND ( ( cm_AllocData1_temp.transDate ) < (
cm_QuotaState.EndDate ) ) ) AND ( cm_Quota.tril_gid = cm_QuotaState.Quota
) ) AND ( cm_Quota.Model = 'N151VZWTJN0DUVPBUVZSU31JQ2OU' ) ) AND (
cm_Quota.Status = 'Enabled' )
QUERY PLAN
--
Merge Join (cost=333271.40..379482.09 rows=255889 width=143)
Merge Cond: ("outer"."?column4?" = "inner"."?column7?")
Join Filter: (("outer".transdate >= "inner".startdate) AND
("outer".transdate < "inner".enddate))
-> Sort (cost=3214.64..3290.02 rows=30150 width=146)
Sort Key: (cm_allocdata1_temp.recipient)::text
-> Seq Scan on cm_allocdata1_temp (cost=0.00..971.50 rows=30150
width=146)
-> Sort (cost=330056.76..330764.27 rows=283005 width=126)
Sort Key: (cm_quotastate.salesteam)::text
-> Hash Join (cost=22.69..304429.99 rows=283005 width=126)
Hash Cond: (("outer".quota)::text = ("inner".tril_gid)::text)
-> Seq Scan on cm_quotastate (cost=0.00..274161.17
rows=5483217 width=126)
-> Hash (cost=22.65..22.65 rows=16 width=36)
-> Seq Scan on cm_quota (cost=0.00..22.65 rows=16
width=36)
Filter: (((model)::text =
'N151VZWTJN0DUVPBUVZSU31JQ2OU'::text) AND ((status)::text =
'Enabled'::text))
Just setting enable_sort and enable_hashjoin to off gives a good plan
(Settings for both are the same).
Plan with sort and hash join turned off:
Nested Loop (cost=22.67..7070120.99 rows=255889 width=143)
Join Filter: (("inner".tril_gid)::text = ("outer".quota)::text)
-> Nested Loop (cost=0.00..5285277.72 rows=4957835 width=143)
-> Seq Scan on cm_allocdata1_temp (cost=0.00..971.50 rows=30150
width=146)
-> Index Scan using statesbysalesteam_i on cm_quotastate
(c
Re: [GENERAL] partitionning
Back to the original question on this thread, and using PG 8.0.1.
Perhaps someone would like to poke holes in this (other than the need
to set up fkeys and indexes on the inherited tables...):
---
begin;
create schema partition_test;
set search_path to partition_test,public;
create table test_base ( id serial, value text, partitioner timestamp
with time zone default now());
create table test_2005_03 () inherits (test_base);
create table test_2005_04 () inherits (test_base);
create rule base_partitioner_test_2005_03 as on insert
to test_base where partitioner AT TIME ZONE 'UTC' between
'2005-03-01'::timestamp and '2005-04-01'::timestamp
do instead insert into test_2005_03 values (NEW.*);
create rule base_partitioner_test_2005_04 as on insert
to test_base where partitioner AT TIME ZONE 'UTC' between
'2005-04-01'::timestamp and '2005-05-01'::timestamp
do instead insert into test_2005_04 values (NEW.*);
insert into test_base (value) values ('first string');
insert into test_base (value, partitioner) values ('a string',
'2004-01-30 10:17:08');
insert into test_base (value, partitioner) values ('a string',
'2005-04-01 14:17:08');
explain analyze select * from test_base;
select tableoid::regclass,* from test_base;
rollback;
---
This could easily be wrapped up in a
'create_partition_by_date(base_table,partition_start,partition_length)'
function, I just haven't gotten around to that part yet. The function
could even look up the indexes and fkeys on the base table using the
INFORMATION_SCHEMA views.
One thing to note about PG not having indexes across tables, if we do
get in-memory bitmap indexes I believe that the indexes on each
inherited table would actually be combined (if the planner sees it as
a win).
Comments?
On Thu, 10 Mar 2005 12:59:35 +0100, Karsten Hilbert
<[EMAIL PROTECTED]> wrote:
> > Actually I have a strong feeling what really _ought_ to happen here is that
> > the inherited tables support in postgres, which never really worked anyways,
> > should be deprecated and eventually removed.
> Hopefully not. They are useful for other things, too.
>
> Karsten
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Partial or incomplete dates
On Sat, Mar 12, 2005 at 11:30:03 +0100, "Leif B. Kristensen" <[EMAIL PROTECTED]> wrote: > > In MySQL, the checking on dates is very relaxed, so it's totally legal > to enter a date as '1731-00-00', and let your own program logic decide > that this means just the year 1731. Do I have to make my own data type > to store and manipulate partial dates, or has somebody already done it? > Or is there a way to use relaxed date checking in PostgreSQL? There is no existing datatype like that. Probably the best thing to do is to store the year, month and day as separate integers and allow for nulls or special codes to indicate missing data. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Unique Indexes
On Sat, Mar 12, 2005 at 10:41:08 +, ILove TheSpam <[EMAIL PROTECTED]> wrote: > Lets say I have 3 tables: > > surnames - surnameid serial (Primary Key), surname varchar (Unique) > firstnames - firstnameid serial (Primary Key), firstname varchar (Unique) > users - userid serial (Primary Key), firstnameid, surnameid This is normally a bad design. Why are you doing this? If these are real people's names, you don't want enforce that there aren't two people with the same name unless you have some way to keep such combinations of people out of your problem (perhaps by making them change their names?). There doesn't seem to be any point to the firstname and surnames tables. If you want this for searching, nonunique indexes on the firstnameid and surnameid fields of the users table should work. Normally you would do this with a single user table that has an artificial primary key to identify unique people, and then information about their name and maybe some other properties of people. Checking for duplicates may be hard, because it can be hard in real like to tell if two people with the same same are really two people. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgres 8 settings
"vinita bansal" <[EMAIL PROTECTED]> writes: > Can you please provide details on the relationship between work_mem and > maintenance_work_mem (Postgres doc specifies that the value for maintenance > work_mem should be larger but I am not clear on why that should be so) Because the other way doesn't make sense. Each active backend in a system may allocate up to work_mem for each sort or hash it's currently doing (thus, possibly several times work_mem for a complex query). The operations that use maintenance_work_mem are guaranteed not to use that more than once per backend, and in most situations you'd not have many backends concurrently doing such operations anyway. So maintenance_work_mem can be a fairly large chunk of your free RAM without driving the system into swapping, but setting an equally large value of work_mem as a global default is just foolish. In some cases you might want to raise work_mem temporarily (via SET) for specific individual queries, but I wouldn't ever put a large value for it in postgresql.conf. > Am I right to say that work_mem is the same as sort_mem and > maintenance_work_mem as vauum_mem in Postgres 7.4?? Yes, although maintenance_work_mem is used for more things than vacuum_mem was used for in prior releases. > Also, since I have 32GB RAM, setting wall_buffers as 1000 should not make a > difference. The point is that it's not going to help. Why would you throw away RAM to a useless purpose when it could be getting used for something useful? Furthermore, depending on your platform, making the buffer area much larger than necessary can actually be counterproductive. You want all of Postgres' shared memory to stay "hot" enough in the eyes of the kernel that it won't get swapped out. If wal_buffers is too large, then as Postgres circularly re-uses the buffers, it will advance into memory areas that it has not touched in a good while. If the kernel decided to swap that area out, it will have to swap it back in. That's two I/O operations utterly wasted (since in fact the old contents of the buffers are no longer interesting, but the kernel has no way to know that). I don't recommend tuning Postgres by randomly twiddling values you don't understand the effects of. Bigger is not always better. Change one thing at a time and measure the results. > I am bothered about settings that might effect time taken by query execution > (updates in particular) Increasing checkpoint_segments (and possibly checkpoint_timeout) would be more likely to help than increasing wal_buffers. > Why do you think we need to create regular logs, only to handle database > crashes?? Postgres is not designed on the assumption that that's an insignificant consideration ;-) Meanwhile, you didn't answer my question: what problems are you seeing? regards, tom lane ---(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] partitionning
Hi,
On Sat, 12 Mar 2005, Mike Rylander wrote:
Back to the original question on this thread, and using PG 8.0.1.
Perhaps someone would like to poke holes in this (other than the need
to set up fkeys and indexes on the inherited tables...):
---
begin;
create schema partition_test;
set search_path to partition_test,public;
create table test_base ( id serial, value text, partitioner timestamp
with time zone default now());
create table test_2005_03 () inherits (test_base);
create table test_2005_04 () inherits (test_base);
create rule base_partitioner_test_2005_03 as on insert
to test_base where partitioner AT TIME ZONE 'UTC' between
'2005-03-01'::timestamp and '2005-04-01'::timestamp
do instead insert into test_2005_03 values (NEW.*);
create rule base_partitioner_test_2005_04 as on insert
to test_base where partitioner AT TIME ZONE 'UTC' between
'2005-04-01'::timestamp and '2005-05-01'::timestamp
do instead insert into test_2005_04 values (NEW.*);
insert into test_base (value) values ('first string');
insert into test_base (value, partitioner) values ('a string',
'2004-01-30 10:17:08');
insert into test_base (value, partitioner) values ('a string',
'2005-04-01 14:17:08');
explain analyze select * from test_base;
select tableoid::regclass,* from test_base;
rollback;
---
any ideas how we would get
select * from test_base where partitioner between a and b
to just look in the correct partition and not doing a full sweep over
the other partitions or the base.
Greetings
Christian
--
Christian Kratzer [EMAIL PROTECTED]
CK Software GmbHhttp://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] postgres 8 settings
"vinita bansal" <[EMAIL PROTECTED]> writes: > Also, a particular query which was taking a min or less with postgres 7 is > taking 15 mins now. > Here is the query plan for the query both for postgres 7.4.6 and postgres 8: It's hard to say anything about this when you only provided EXPLAIN and not EXPLAIN ANALYZE results. The rowcount estimates are obviously a lot different between 7.4 and 8.0, but are they more accurate or less so? Can't tell from EXPLAIN. > Why is this happening. Have you vacuumed and/or analyzed all of the tables used in the query? It looks like a statistical estimation problem to me. People frequently complain that a new version has horrible performance because they forgot to analyze their database after migrating ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best practices: Handling Daylight-saving time
On Fri, Mar 11, 2005 at 01:43:21PM -0500, Randall Nortman wrote: > As others have mentioned, store timestamps on the server in UTC, 1) As long as I store them as I should not need to care what they are stored as on the backend as long as I provide the proper timezone for the client location. Correct ? 2) If I then retrieve them as "... at time zone <...>" I will get the equivalent time in the time zone of the retrieving client. The same could be be achieved with "set timezone" per session. Correct ? 3) If I retrieve them without "at time zone" I will get them with the time zone that was stored in the first place, right ? 4) I could be wrong on 3, it might be that I then get times at the time zone the machine running PostgreSQL is set to - still the correct point in time but not the *source* time zone. GnuMed operates on the assumptions that 1 and 2 hold true. It does not bet itself on 3. Are we safe ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Best practices: Handling Daylight-saving time
On Sat, Mar 12, 2005 at 05:44:52PM +0100, Karsten Hilbert wrote: > On Fri, Mar 11, 2005 at 01:43:21PM -0500, Randall Nortman wrote: > > > As others have mentioned, store timestamps on the server in UTC, > > 1) As long as I store them as I should > not need to care what they are stored as on the backend as > long as I provide the proper timezone for the client location. > Correct ? > > 2) If I then retrieve them as "... at time zone <...>" I will get > the equivalent time in the time zone of the retrieving client. > The same could be be achieved with "set timezone" per session. > Correct ? Yes and Yes > 3) If I retrieve them without "at time zone" I will get them with > the time zone that was stored in the first place, right ? [...] This would be news to me. I don't think it's possible to *not* have a timezone set on a session. The server will have a default timezone based either on the local (server) system time or the setting of the timezone variable in postgresql.conf. Additionally, libpq applications will, I believe, issue a "set timezone" during initial connection setup. The manual (section 8.5.3) seems to indicate that libpq will only do that if PGTZ is set, but I seem to recall it happening without PGTZ. (But I'm not entirely sure; feel free to experiment.) Anyway, afaik, Postgres does not store the "original" timezone anywhere, and so could not possibly retrieve it. I think the only physical difference between the "timestamp" and "timestamp with time zone" types is in the system catalog; the manual states that both of them store 8 bytes and have the same range. If "timestamp with time zone" were storing anything extra, I would think the storage size would be greater or else the range smaller. Randall ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] postgresql error
hi... i get a postgres error when i've gone from RH8 to FC3 and am now using postgreSQL-7.4.7. an err msg 'an old version of database format was found. you need to upgrade the data format." searching google shows that others have seen this, but i can't find a way to correct it. i've checked the postrgres site/faq and couldn't find anything either. can someone point me to a site/steps that show what i have to do. i'm not a postgre user/guru by any means!! thanks bruce ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Best practices: Handling Daylight-saving time
Randall Nortman <[EMAIL PROTECTED]> writes: > Anyway, afaik, Postgres does not store the "original" timezone > anywhere, and so could not possibly retrieve it. I think the only > physical difference between the "timestamp" and "timestamp with time > zone" types is in the system catalog; the manual states that both of > them store 8 bytes and have the same range. There is no physical difference between the types: they are both 8-byte quantities measuring seconds since the Epoch. I think we use midnight 1/1/2000 as the Epoch rather than the traditional Unix 1/1/1970 Epoch, but otherwise it's exactly the same idea. The logical difference between the two is that timestamp with tz assumes that the Epoch is midnight UTC (which means that any particular stored value represents a very definite real-world instant), while timestamp without tz ignores the entire concept of time zones; its Epoch is midnight in an unspecified time zone. Thus, timestamp with tz can and does convert back and forth between UTC (for the stored values) and your current TimeZone setting (for display). In timestamp without tz, what you see is all there is. Personally I would always use timestamp with tz for representing actual time instants. Timestamp without tz has uses in some calendar applications, but it is inherently ambiguous as a representation of a specific instant. In particular, for the data recording application that started this thread, it'd be a horrid idea to even think of using timestamp without tz, specifically because it's incapable of dealing with things like DST jumps. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Database Link between 2 PostgreSQL db
Hello, Oracle has a concept of Database link, IE you can access data from a table in different database, even if the database is in different host, using something like SELECT * FROM [EMAIL PROTECTED]; Is there something similar in PostgreSQL? I would like to know if I can access my data on a remote PostgreSQL server from a local PostgreSQL database. Thank you in advance for any respond. RDB -- Reuben D. Budiardja Dept. Physics and Astronomy University of Tennesse, Knoxville, TN -BEGIN GEEK CODE BLOCK- Version: 3.12 GIT/M/MU/P/S d-(++) s: a-- C++(+++) UL P-- L+++> E- W+++ N+ o? K- w--- !O M- V? !PS !PE Y PGP- t+ 5 X R- tv+ b++>+++ DI D(+) G e++> h+(*) r++ y-> --END GEEK CODE BLOCK-- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Database Link between 2 PostgreSQL db
On Sat, 2005-03-12 at 15:00 -0500, Reuben D. Budiardja wrote:
> Hello,
> Oracle has a concept of Database link, IE you can access data from a table in
> different database, even if the database is in different host, using
> something like
>
> SELECT * FROM [EMAIL PROTECTED];
>
> Is there something similar in PostgreSQL? I would like to know if I can
> access
> my data on a remote PostgreSQL server from a local PostgreSQL database.
>
> Thank you in advance for any respond.
>
> RDB
You can use dblink.
> dblink - Functions to return results from a remote database
>
http://www.postgresql.org/docs/current/static/queries-table-expressions.html
example:
> SELECT *
> FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
> AS t1(proname name, prosrc text)
> WHERE proname LIKE 'bytea%';
>
Cheers,
Robby
--
/***
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | [EMAIL PROTECTED]
* 503.351.4730 | blog.planetargon.com
* PHP-PostgreSQL Hosting & Development
* open source solutions - web hosting
/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] partitionning
On Sat, 12 Mar 2005 17:39:38 +0100 (CET), Christian Kratzer
<[EMAIL PROTECTED]> wrote:
> Hi,
>
> On Sat, 12 Mar 2005, Mike Rylander wrote:
>
> > Back to the original question on this thread, and using PG 8.0.1.
> > Perhaps someone would like to poke holes in this (other than the need
> > to set up fkeys and indexes on the inherited tables...):
> >
> > ---
> >
> > begin;
> > create schema partition_test;
> > set search_path to partition_test,public;
> >
> > create table test_base ( id serial, value text, partitioner timestamp
> > with time zone default now());
> > create table test_2005_03 () inherits (test_base);
> > create table test_2005_04 () inherits (test_base);
> >
> > create rule base_partitioner_test_2005_03 as on insert
> >to test_base where partitioner AT TIME ZONE 'UTC' between
> > '2005-03-01'::timestamp and '2005-04-01'::timestamp
> >do instead insert into test_2005_03 values (NEW.*);
> >
> > create rule base_partitioner_test_2005_04 as on insert
> >to test_base where partitioner AT TIME ZONE 'UTC' between
> > '2005-04-01'::timestamp and '2005-05-01'::timestamp
> >do instead insert into test_2005_04 values (NEW.*);
> >
> > insert into test_base (value) values ('first string');
> > insert into test_base (value, partitioner) values ('a string',
> > '2004-01-30 10:17:08');
> > insert into test_base (value, partitioner) values ('a string',
> > '2005-04-01 14:17:08');
> >
> > explain analyze select * from test_base;
> > select tableoid::regclass,* from test_base;
> >
> > rollback;
> >
> > ---
>
> any ideas how we would get
>
> select * from test_base where partitioner between a and b
>
> to just look in the correct partition and not doing a full sweep over
> the other partitions or the base.
There is now way to have PG completely skip the unused partitions.
However, with an index on the "partitioner" column of each partition
table PG will basically fall of the end of the index almost
imediately, and then use an appropiate plan for the useful partitions.
It's not the same as Oracle partitioning, but without expicit Planner
work it can't be.
It should be about the same speed as one gigantic table, perhaps
faster for some queries where there are many total partitions and few
useful ones, but for my money that's not where the biggest benefit of
partitioning lies. My favorite feature of classic partitioning is
that you can load and unload large chunks of the data without
affecting everyone else; you can very easily archive unused
partitions or transfer them to a statistics server for data mining
with no impact on other writers.
Another benefit would to be the ability to put the currently
write-active partition on a very fast/expensive storage area and move
the older data to a slower/cheaper storage area using tablespaces.
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[GENERAL] Using sequence name depending on other column
I have table containing different types of documents (type A, B and C). Each document type must have separate sequential ID starting at 1 ID of first inserted record of type A must be set to 1 ID of first inserted record of type B must be also set to 1 ID of second record of type A must be set to 2 etc. I tried to implement this as CREATE SEQUENCE a_id_seq; CREATE SEQUENCE b_id_seq; CREATE SEQUENCE c_id_seq; CREATE TABLE documents ( doctype CHAR(1), id NUMERIC DEFAULT nextval(doctype ||'_dokumnr_seq'), documentcontents TEXT ); but got an error ERROR: cannot use column references in default expression Any idea how to implement this ? Andrus. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Log each DB to a diffrent file
Hi Everyone Im trying to log each DB to a different file. So I added: log_line_prefix ='%d :' To the postgresql.conf Later using syslog-ng i added a rule to redirect local0 + postgres + 'DB Name' to specific file. But sadly that didnt worked as its only work on one line queries. every select constructed from more then on line didnt obeyed the rule as the DB name was not at the start of the line. Any idea how to solve it ? If not, any other way to do it ? Thanks -- -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Cel: 972-52-8555757 Fax: 972-4-6990098 http://www.canaan.net.il -- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Moving an object (function/table, etc) from schema to schema
Is it possible to ALTER somehow an object and move it from schema to schema? It really sucks to drop an object completely for such a change (because of all the object relations forcing me to drop them first and then to recreate). -- ICQ: 1912453 AIM: VitalyB1984 MSN: [EMAIL PROTECTED] Yahoo!: VitalyBe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Moving an object (function/table, etc) from schema to
Vitaly Belman wrote: > Is it possible to ALTER somehow an object and move it from schema to > schema? It really sucks to drop an object completely for such a change > (because of all the object relations forcing me to drop them first and > then to recreate). Our TODO has: o Allow objects to be moved to different schemas -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Best practices: Handling Daylight-saving time
On Sat, Mar 12, 2005 at 12:22:38PM -0500, Randall Nortman wrote: > > Anyway, afaik, Postgres does not store the "original" timezone > anywhere, and so could not possibly retrieve it. I think the only > physical difference between the "timestamp" and "timestamp with time > zone" types is in the system catalog; the manual states that both of > them store 8 bytes and have the same range. If "timestamp with time > zone" were storing anything extra, I would think the storage size > would be greater or else the range smaller. Am I correct to assume that this could be solved with a user defined composite data type ? From the docs it so seems. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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] partitionning
On Sat, Mar 12, 2005 at 08:24:20PM +, Mike Rylander wrote: > There is now way to have PG completely skip the unused partitions. > However, with an index on the "partitioner" column of each partition > table PG will basically fall of the end of the index almost > imediately, and then use an appropiate plan for the useful partitions. > It's not the same as Oracle partitioning, but without expicit Planner > work it can't be. Note, the planner does have most of the intelligence needed to determine when it can skip a partition. It has code that can determine if the intersection of: Partition constraint:lower bound < value < upper bound Query clause:value [operator] constant Will be always false or can be true. It's just a matter of getting the clause to the right place to make it skip... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpDvgBrxq5Nq.pgp Description: PGP signature
Re: [GENERAL] postgresql error
On Sat, 12 Mar 2005 10:13:34 -0800, bruce <[EMAIL PROTECTED]> wrote: > hi... > > i get a postgres error when i've gone from RH8 to FC3 and am now using > postgreSQL-7.4.7. > > an err msg 'an old version of database format was found. you need to upgrade > the data format." > > searching google shows that others have seen this, but i can't find a way to > correct it. i've checked the postrgres site/faq and couldn't find anything > either. can someone point me to a site/steps that show what i have to do. > i'm not a postgre user/guru by any means!! You need to dump the DB from the old version of postgresql and import it back into the new version. see the pg_dump and pg_restore man pages. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] postgresql error
so.. what can i just delete and keep going forward. the old version was never really used, and we don't have an old version of the postgres app.. thanks bruce -Original Message- From: Lonni J Friedman [mailto:[EMAIL PROTECTED] Sent: Saturday, March 12, 2005 3:30 PM To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgresql error On Sat, 12 Mar 2005 10:13:34 -0800, bruce <[EMAIL PROTECTED]> wrote: > hi... > > i get a postgres error when i've gone from RH8 to FC3 and am now using > postgreSQL-7.4.7. > > an err msg 'an old version of database format was found. you need to upgrade > the data format." > > searching google shows that others have seen this, but i can't find a way to > correct it. i've checked the postrgres site/faq and couldn't find anything > either. can someone point me to a site/steps that show what i have to do. > i'm not a postgre user/guru by any means!! You need to dump the DB from the old version of postgresql and import it back into the new version. see the pg_dump and pg_restore man pages. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] partitionning
On Sun, 13 Mar 2005 00:18:39 +0100, Martijn van Oosterhout wrote: > On Sat, Mar 12, 2005 at 08:24:20PM +, Mike Rylander wrote: > > There is now way to have PG completely skip the unused partitions. > > However, with an index on the "partitioner" column of each partition > > table PG will basically fall of the end of the index almost > > imediately, and then use an appropiate plan for the useful partitions. > > It's not the same as Oracle partitioning, but without expicit Planner > > work it can't be. > > Note, the planner does have most of the intelligence needed to > determine when it can skip a partition. It has code that can determine > if the intersection of: > > Partition constraint:lower bound < value < upper bound > Query clause:value [operator] constant > > Will be always false or can be true. It's just a matter of getting the > clause to the right place to make it skip... I see. I should have qualified my statement with "*as of now* there is no way". If I felt comfortable digging into the planner I'd offer to take that on, but... Thanks for pointing out that detail! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] General query optimization howto
Hi all, is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. -- Miroslav Šulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgresql error
Shutdown postgresql, delete everything under /var/lib/pgsql/ and then start postgresql again. On Sat, 12 Mar 2005 16:51:55 -0800, bruce <[EMAIL PROTECTED]> wrote: > so.. what can i just delete and keep going forward. the old version was > never really used, and we don't have an old version of the postgres app.. > > thanks > > bruce > > > -Original Message- > From: Lonni J Friedman [mailto:[EMAIL PROTECTED] > Sent: Saturday, March 12, 2005 3:30 PM > To: [EMAIL PROTECTED] > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] postgresql error > > On Sat, 12 Mar 2005 10:13:34 -0800, bruce <[EMAIL PROTECTED]> wrote: > > hi... > > > > i get a postgres error when i've gone from RH8 to FC3 and am now using > > postgreSQL-7.4.7. > > > > an err msg 'an old version of database format was found. you need to > upgrade > > the data format." > > > > searching google shows that others have seen this, but i can't find a way > to > > correct it. i've checked the postrgres site/faq and couldn't find anything > > either. can someone point me to a site/steps that show what i have to do. > > i'm not a postgre user/guru by any means!! > > You need to dump the DB from the old version of postgresql and import > it back into the new version. see the pg_dump and pg_restore man > pages. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Using sequence name depending on other column
On Sat, Mar 12, 2005 at 23:05:41 +0200, Andrus Moor <[EMAIL PROTECTED]> wrote: > I have table containing different types of documents (type A, B and C). > > Each document type must have separate sequential ID starting at 1 > > ID of first inserted record of type A must be set to 1 > ID of first inserted record of type B must be also set to 1 > ID of second record of type A must be set to 2 > etc. Sequences aren't designed for doing this. If you aren't doing lots of updates, just lock the table and assign the next id as the current max id of that type + 1. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] partitionning
Martijn van Oosterhout writes: > Note, the planner does have most of the intelligence needed to > determine when it can skip a partition. The issue isn't really whether the planner *could* use a constraint to decide that a table need not be scanned at all. As you say, we have practically all the infrastructure needed for such inferences. To me the real reason why we don't do that is the lack of a mechanism to force the plan to be reconsidered if the constraint is dropped. Without the constraint, the plan will still run, but it may deliver wrong answers. (The closest current equivalent is the possibility of dropping an index that a plan depends on --- but at least we will fail outright if the index isn't there anymore.) In short, I won't accept any such patch until after we build a mechanism for invalidating cached plans. Which we need anyway for other reasons. So the path ahead is clear, in my mind anyway ... but this is not the next step to take. regards, tom lane ---(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] [PATCHES] A way to let Vacuum warn if FSM settings are low. [final?]
Well, I was really hoping something would end up in the log file.
The situation is that our clients - sometimes not that computer
savvy - go perhaps a year without us being involved (unless
the log monitoring scripts show something abnormal; or if the
system breaks).
The primary motivation for tweaking this file in the first place
was so that the log file would catch the situation where their
database outgrows the FSM settings before it causes a problem.
What about at least sending the output to a log file
if VERBOSE or some GUC variable is set?
Ron
Bruce Momjian wrote:
I have applied your patch with minor modifications. Applied version
attached.
I think the "pages" message:
INFO: free space map: 44 relations, 28 pages stored; 704 total pages
used
DETAIL: FSM size: 1000 relations + 2 pages = 182 kB shared memory.
should remain DEBUG2 for non-VERBOSE, and INFO for VERBOSE. The
information is pretty complex and probably of little interest to a
typical vacuum user. In fact, the new messages make the information
even less important because problems are now flagged.
I adjusted your output levels for the new messages. I realize the
"checkpoint warning" is a LOG message, but it has to be because there is
no active session when a checkpoint is being run. In the case of
VACUUM, there is an active session so I think the messages should be
sent to that session. Sending them to the logs and not to the user
seems unusual because they are the ones who asked for the VACUUM. I
realize they might not be able to change the server settings.
These new messages:
NOTICE: max_fsm_relations(1000) equals the number of relations checked
HINT: You have >= 44 relations.
Consider increasing the configuration parameter "max_fsm_relations".
NOTICE: the number of page slots needed (704) exceeds max_fsm_pages (2)
HINT: Consider increasing the configuration parameter "max_fsm_relations"
to a value over 704.
VACUUM
should be NOTICE. NOTICE is for unusual events that are not warnings,
and that fits these cases. If the administrator wants those in the
logs, he can set log_min_messages to NOTICE.
I also adjusted your output strings to more closely match our checkpoint
warning message.
Another idea would be to send the output to both the client and the logs
by default.
---
Ron Mayer wrote:
On Sun, 27 Feb 2005, Simon Riggs wrote:
On Fri, 2005-02-25 at 16:48 -0800, Ron Mayer wrote:
Getting closer?
For me, yes. [...]
The not-warnings seem a little wordy for me, but they happen when and
how I would hope for.
So, for me, it looks like a polish of final wording and commit.
Thanks for the feedback. How about I replace the grammatically poor:
LOG: max_fsm_relations(%d) is equal than the number of relations vacuum checked (%d)",
HINT: You probably have more than %d relations. You should increase max_fsm_relations. Pages needed for
max_fsm_pages may have been underestimated.
with this:
LOG: max_fsm_relations(100) equals the number of relations checked
HINT: You have >= 100 relations. You should increase max_fsm_relations.
and replace this:
LOG: max_fsm_pages(%d) is smaller than the actual number of page slots
needed(%.0f)",
HINT: You may want to increase max_fsm_pages to be larger than %.0f"
with the slightly smaller
LOG: the number of page slots needed (2832) exceeds max_fsm_pages (1601)
HINT: You may want to increase max_fsm_pages to a value over 2832.
These updated messages would fit on an 80-column display if the numbers
aren't too big. Here's 80 characters for a quick reference.
01234567890123456789012345678901234567890123456789012345678901234567890123456789
The "pages needed...underestimate" in the first message was no longer
useful anyway; since it's no longer logging fsm_pages stuff when the
max_fsm_relations condition occurred anyway
Ron
The patch now looks like:
% diff -u postgresql-8.0.1/src/backend/storage/freespace/freespace.c
postgresql-patched/src/backend/storage/freespace/freespace.c
--- postgresql-8.0.1/src/backend/storage/freespace/freespace.c2004-12-31
14:00:54.0 -0800
+++ postgresql-patched/src/backend/storage/freespace/freespace.c2005-02-27
11:54:39.776546200 -0800
@@ -705,12 +705,25 @@
/* Convert stats to actual number of page slots needed */
needed = (sumRequests + numRels) * CHUNKPAGES;
-ereport(elevel,
-(errmsg("free space map: %d relations, %d pages stored; %.0f total pages needed",
+ereport(INFO,
+(errmsg("free space map: %d relations, %d pages stored; %.0f total pages used",
numRels, storedPages, needed),
- errdetail("Allocated FSM size: %d relations + %d pages = %.0f kB shared memory.",
+ errdetail("FSM size: %d relations + %d pages = %.0f kB shared memory.",
MaxFSMRe
Re: [GENERAL] [PATCHES] A way to let Vacuum warn if FSM settings are low. [final?]
Ron Mayer wrote: > Well, I was really hoping something would end up in the log file. > > The situation is that our clients - sometimes not that computer > savvy - go perhaps a year without us being involved (unless > the log monitoring scripts show something abnormal; or if the > system breaks). > > The primary motivation for tweaking this file in the first place > was so that the log file would catch the situation where their > database outgrows the FSM settings before it causes a problem. > > What about at least sending the output to a log file > if VERBOSE or some GUC variable is set? You didn't like server_min_messages = 'notify'? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Using sequence name depending on other column
On Sun, 13 Mar 2005 02:59 pm, Bruno Wolff III wrote: > On Sat, Mar 12, 2005 at 23:05:41 +0200, > Andrus Moor <[EMAIL PROTECTED]> wrote: > > I have table containing different types of documents (type A, B and C). > > > > Each document type must have separate sequential ID starting at 1 > > > > ID of first inserted record of type A must be set to 1 > > ID of first inserted record of type B must be also set to 1 > > ID of second record of type A must be set to 2 > > etc. > If you are happy with the fact that a sequence may leave a whole in the numbers. You are probably best to no set a default value for an integer, or big integer. Then run a before trigger for each row. That trigger will assign a value to the column based on the value given for the type. Regards Russell Smith ---(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] [PATCHES] A way to let Vacuum warn if FSM settings are low. [final?]
Bruce Momjian wrote: You didn't like server_min_messages = 'notify'? I merely don't have a feeling for how much additional stuff verbose would be putting in the log files. If it's a good practice for production systems to be logging NOTIFY's I'm happy with the change. My reasoning why I thought the log file was more useful was that only an admin with access to the log files could really do anything about the message anyway. Also since the message happing occasionally is probably OK, yet if it happens a lot it's more likely worth looking into - I think the historical record of when it happened is more interesting than a one-time occurrence which is all you seen in the active session. Ron PS: I'm fine either way; and perhaps it's a good idea for me to be logging NOTIFY's anyway -- I just thought I'd explain my reasoning above. I'm sure you guys know a lot more than me what best practices would be. ---(end of broadcast)--- TIP 8: explain analyze is your friend
