Re: [GENERAL] Announce: GPL Framework centered on Postgres
Kenneth Downs wrote: My company has developed an application development framework that targets PostgreSQL as its back-end, with PHP in the web layer. Is this product somehow related to AndroMDA (which is usually pronounced 'Andromeda')? http://www.andromda.org/ Greetings, Anastasios ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Announce: GPL Framework centered on Postgres
Tim Allen wrote: Kenneth Downs wrote: GPL is to spread it as far and wide as possible as fast as possible. LGPL? My concern would be, I can't use this toolkit for a closed source application if it is GPL. That may be your intent (which I actually don't have a business problem with), I was just curious as to your decision. If it turns out that nobody can release a closed source app, I will definitely reconsider and look again at LGPL, but I am not convinced you cannot do so. If you seek to provide a closed source app that is built upon Andromeda, you are required to provide the source code to Andromeda itself. However, your app is not a derivative work in the strict sense because your code is not mixed in with mine in any sense. You never modify a file, and your files and mine are actually in separate directories. I greatly appreciate your asking the question though because I'd like to make sure that people feel safe with the project. My goal is to provide the freedoms typically associated with the plain old GPL, and certainly not to restrict the creation of closed apps. I just don't want anybody closing *my* app. Then it sounds like LGPL is exactly what you want. That forbids people closing your code, but allows linking of it to closed apps. Cf Tom's comments, it's quite difficult for anyone to release code that depends on GPL'd code without incurring the terms of the GPL for their code (and that is clearly the way the FSF want it to be). But as Joshua was implying, a common business model is to release some code under GPL, which means it can be used only for GPL'd apps, and then also be willing to sell other sorts of licences for it to be used with commercial apps. If that's the sort of business model you have in mind, then GPL is probably what you want. We've been through similar discussions recently with our web application server, Whitebeam (http://www.whitebeam.org). We'd originally released this under a variant of the Mozilla licence - which I think is not unlike GPL. We started down that route because we make use of Mozillas JavaScript engine (SpiderMonkey). We did get a number of comments though, and we never managed to get our licence adopted by the OSS (quite rightly so!) The outcome of the discussion was to release the project under a BSD license. A good deal of the discussion centred around the fact that we make heavy use of Postgres and so we'd be a much more natural choice of development environment if we had a similar licence. It helped that the discussions took place during the uncertaintly around mySQL licensing coupled with Oracles buyout of the innodb company. The clincher was that Postgres+Whitebeam+Apache (1.3.29 before they changed their licence) provided a complete BSD based web development environment. The only external dependancy being SpiderMonkey which we link to under the LGPL. My suggestion would be: a) if you want to keep the option of selling/licencing your code for commercial gain, do something like mySQL and release under GPL with lots of warnings and offer people a 'commercial' licence; b) if you want to see your project used in the widest possible audience go with BSD. The BSD license does allow others to create a closed-source project from your code - but my view is that isn't too important. You'd be the natural port of call if they wanted consultancy on how to do that. Pete -- http://www.whitebeam.org http://www.yellowhawk.co.uk -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] best practice in upgrading db structure
I will ask, though, why use XML/XSL, why not use a format that lets you load the data to tables, then you do a huge number of tricks with it prior to generating the DDL, not the least of which is diff'ing current structure to see what needs to be changed. Well, XML/XSLT is indeed not the best for this purpose, but I didn't know that when I first implemented it :-) Next time I'll use something else. Anyway, our system uses a format that looks something like CSS, it is documented here: http://docs.secdat.com/index.php?gppn=Database+Specifications I agree that the CSS-like style is more readable than XML, but the other DB maintainers here had expressed their desire to have it SQL-like :-) I have no problems with XML either, but not all are happy with it... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Announce: GPL Framework centered on Postgres
Tom Lane wrote: Kenneth Downs [EMAIL PROTECTED] writes: If it turns out that nobody can release a closed source app, I will definitely reconsider and look again at LGPL, but I am not convinced you cannot do so. If you seek to provide a closed source app that is built upon Andromeda, you are required to provide the source code to Andromeda itself. However, your app is not a derivative work in the strict sense because your code is not mixed in with mine in any sense. This may well be what a sane person would think after perusing the license text, but you need to be aware that the FSF takes a much more expansive reading of that text. AFAIK those details haven't been tested yet in any court of law --- but until a reading is settled by court precedents, people tend to look to the FSF's interpretation. And the FSF is on record as saying that if code A depends on code B then B's GPL license infects A, even for pretty weak values of "depends". You should carefully read http://www.gnu.org/licenses/gpl-faq.html, which contains statements such as If the program dynamically links plug-ins, and they make function calls to each other and share data structures, we believe they form a single program, which must be treated as an extension of both the main program and the plug-ins. I don't have anything against the GPL's goals, but those goals are very clearly that the entire software universe should be GPL code. If that's not what you have in mind, then you should think twice about licensing a software component (as opposed to a standalone product that isn't meant to have other code depending on it) under GPL. regards, tom lane Tom, thanks much. That points me pretty firmly towards LGPL. I will reflect on this and likely make a change in the coming weeks. begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [Linux] Suitable 64bit
Am Mittwoch, 17. Mai 2006 06:58 schrieb Scott Venter: Can anyone advise as to which binary I should use for SUSE 10. Look here: ftp://ftp.suse.com/pub/projects/postgresql -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Announce: GPL Framework centered on Postgres
Anastasios Hatzis wrote:
Kenneth Downs wrote:
My company has developed an application development framework that
targets PostgreSQL as its back-end, with PHP in the web layer.
Is this product somehow related to AndroMDA (which is usually
pronounced 'Andromeda')?
http://www.andromda.org/
Nope, separate projects.
Theirs is java, we are php.
Theirs is windows, we are linux.
They don't mention a database, I'm sure they're using something, we
target Postgres (though our methodology is platform-neutral).
They look like a code generator, while ours uses libraries + data
dictionary on web server layer, and only generates code on the db server.
Finally, we are radically table oriented, focusing entirely on
automating software development based on a detailed database
specification which includes derived values and security. They use UML,
while we have a CSS-like way of specifying tables as in:
table customers {
module: ar;
description: customers;
column customer { primary_key: Y; uisearch: Y; }
more columns and stuff
}
begin:vcard
fn:Kenneth Downs
n:Downs;Kenneth
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [Linux] Suitable 64bit
thank you for your reply. I will take a look. -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED] Sent: Wed 5/17/2006 12:55 PM To: pgsql-general@postgresql.org Cc: Scott Venter Subject:Re: [GENERAL] [Linux] Suitable 64bit Am Mittwoch, 17. Mai 2006 06:58 schrieb Scott Venter: Can anyone advise as to which binary I should use for SUSE 10. Look here: ftp://ftp.suse.com/pub/projects/postgresql -- Peter Eisentraut http://developer.postgresql.org/~petere/ This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Age function
Hi, I believe I already mentioned something along these lines as an aside, but this time I need it to work... Look here: template1= select age(now() + '01:30:00'::interval); age -- -17:02:41.247957 (1 row) So, One and a half hour in the future is actually 17 days ago? Interesting... Either I am doing something wrong, or postgres is, I have my suspicions ;) Now, what I tried to achieve was a numeric representation for the interval. As an interval of (for example) a month is kind of hard to translate into a number (28, 29, 30 or 31 days?), I figured adding the interval to now() and calculating the age of the result. I figured that would nicely give me what I want... For the types of intervals I use (hours and minutes) this could have worked, but... well, see above. For the record, I tried this first on 7.4.7 (debian packages... shrug) and then on 8.1.3. Both show this behaviour. Regards, -- 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 6: explain analyze is your friend
Re: [GENERAL] GUI Interface
Christopher Browne wrote: Also most DBAs are not hard core OSS programmers and anyone coming from a commercial system is more than likely used to running the admin tools on windows. We have a whole department of DBAs, *none* of whom have Microsoft on their desktops. Further, the Big, Important Systems that we administer consciously hide behind firewalls such that the user interface Shall Be Usable Via SSH. Purty graphical tools just don't appear on our radar as being interesting. well, ssh has for a long time the option to forward ports to you. So this isnt really an argument pro/con gui tools :-) Personally I find psql for some and pgadmin3 for other tasks used the same time a good couple. Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Age function
template1= select age(now() + '01:30:00'::interval);
age
--
-17:02:41.247957
(1 row)
So, One and a half hour in the future is actually 17 days ago?
Interesting... Either I am doing something wrong, or postgres is, I have
my suspicions ;)
I guess the result is correct, it's 17 hours, not days, and it is
calculating the difference between the start of this day (current_date)
and the timestamp you gave as parameter to the age function. In that
case the result is correct... the argument is bigger than the start of
the day, and it is substracted from it, so the result is negative.
Now, what I tried to achieve was a numeric representation for the
interval.
For that purpose you might want something like:
dbval=# select date_part('day', justify_hours('127:30:00'::interval));
date_part
---
5
(1 row)
See:
http://www.postgresql.org/docs/8.1/static/functions-datetime.html
It would be nice to have a variant of the date_part functions which
returns a floating point result expressing the complete interval value
scaled according to the given field, but including the scaled values of
all fields. You can achieve that now by extracting separately each
field, multiplying with the proper weight and summing all that. Of
course that can be wrapped in a function, but it's not so nice as a
built-in would be...
Cheers,
Csaba.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] GUI Interface
Hi, On Wed, 17 May 2006, Tino Wildenhain wrote: Christopher Browne wrote: Also most DBAs are not hard core OSS programmers and anyone coming from a commercial system is more than likely used to running the admin tools on windows. We have a whole department of DBAs, *none* of whom have Microsoft on their desktops. Further, the Big, Important Systems that we administer consciously hide behind firewalls such that the user interface Shall Be Usable Via SSH. Purty graphical tools just don't appear on our radar as being interesting. well, ssh has for a long time the option to forward ports to you. So this isnt really an argument pro/con gui tools :-) Personally I find psql for some and pgadmin3 for other tasks used the same time a good couple. I have yet to see a graphical tool that will manage our schema and schema change scripts as efficiently and reliably as vim and cvs. We do sometimes use ssh port forwarding to run scripts,reports and schema changes on remote databases only accessible via ssh. I only use pgadmin3 if I need a quick look at a database I am not familiar with. If I need a deeper look I dump the schema and browse it with vim. 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 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] GUI Interface
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christian Kratzer Sent: 17 May 2006 15:14 To: Tino Wildenhain Cc: Christopher Browne; pgsql-general@postgresql.org Subject: Re: [GENERAL] GUI Interface I only use pgadmin3 if I need a quick look at a database I am not familiar with. If I need a deeper look I dump the schema and browse it with vim. What (if anything) would help pgAdmin do what you need? Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] GUI Interface
Hi, On Wed, 17 May 2006, Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christian Kratzer Sent: 17 May 2006 15:14 To: Tino Wildenhain Cc: Christopher Browne; pgsql-general@postgresql.org Subject: Re: [GENERAL] GUI Interface I only use pgadmin3 if I need a quick look at a database I am not familiar with. If I need a deeper look I dump the schema and browse it with vim. What (if anything) would help pgAdmin do what you need? no idea. I am perfectly happy with vim and cvs. 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Age function
Alban Hertroys wrote:
Look here:
template1= select age(now() + '01:30:00'::interval);
age
--
-17:02:41.247957
(1 row)
So, One and a half hour in the future is actually 17 days ago?
Interesting... Either I am doing something wrong, or postgres is, I have
my suspicions ;)
good-natured sarcasmYour suspicions are correct that you are doing, or
rather, understanding something incorrectly. /good-natured sarcasm
First of all, the 17 is hours, not days. Look again at the formatted
output.
Secondly, there are two forms of AGE, taking, respectively two arguments
and one argument. The latter is a shorthand for the former, assuming the
current time as the base time. So in your query is equivalent to
('2006-05-17 00:00:00') - ('2006-05-17 10:02:01.727674-04' + '01:30:00')
(my current time, 10:02:01.727674-04, is different from what yours was,
obviously)
Looking that the output from :
select
age(now() + '01:30:00'::interval),
now(),
current_date::timestamp,
current_time,
age(current_date, now() + '01:30:00'::interval),
age(now() + '01:30:00'::interval, current_date),
age(now() + '01:30:00'::interval, now())
might help, professorial pontificationas would RTFM
(http://www.postgresql.org/docs/8.1/static/functions-datetime.html;,
Table 9-26. Date/Time Functions). /professorial pontification
Regards,
Berend Tober
begin:vcard
fn:Berend Tober
n:Tober;Berend
org:Seaworthy Systems, Inc.
adr:;;22 Main Street;Centerbrook;CT;06409;USA
email;internet:[EMAIL PROTECTED]
tel;work:860-767-9061
url:http://www.seaworthysys.com
version:2.1
end:vcard
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] GUI Interface
-Original Message- From: Christian Kratzer [mailto:[EMAIL PROTECTED] Sent: 17 May 2006 15:30 To: Dave Page Cc: Tino Wildenhain; Christopher Browne; pgsql-general@postgresql.org Subject: RE: [GENERAL] GUI Interface Hi, On Wed, 17 May 2006, Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christian Kratzer Sent: 17 May 2006 15:14 To: Tino Wildenhain Cc: Christopher Browne; pgsql-general@postgresql.org Subject: Re: [GENERAL] GUI Interface I only use pgadmin3 if I need a quick look at a database I am not familiar with. If I need a deeper look I dump the schema and browse it with vim. What (if anything) would help pgAdmin do what you need? no idea. I am perfectly happy with vim and cvs. Fair 'enuff. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump index/constraint creation order
On Monday May 15 2006 11:14 am, Vivek Khera wrote: On May 14, 2006, at 12:27 AM, Ed L. wrote: While watching a 9-hour 60GB network load from 7.4.6 pg_dump into 8.1.2, I noticed the order in which indices and constraints are created appears to be their creation order. If you use the 8.1.2 pg_dump to make the dump from your 7.4.6 DB, what is the order like? I suspect it would be more to what you're expecting. In general, you should use the pg_dump corresponding to the version into which you are loading for best results. 8.1.2 has same problem (try attached script to see). Ed test.sh Description: application/shellscript ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Age function
Berend Tober wrote:
Alban Hertroys wrote:
So, One and a half hour in the future is actually 17 days ago?
Interesting... Either I am doing something wrong, or postgres is, I have
my suspicions ;)
good-natured sarcasmYour suspicions are correct that you are doing, or
rather, understanding something incorrectly. /good-natured sarcasm
No sarcasm needed, that was exactly what I was referring to ;)
First of all, the 17 is hours, not days. Look again at the formatted
output.
Yeah, I corrected myself after reading an example in the docs where
the age between two dates was calculated (as opposed to timestamps), and
figured I must've misread that as hours while they were days. That colon
pretty much nails it down as hours though. Silly me...
Secondly, there are two forms of AGE, taking, respectively two arguments
and one argument. The latter is a shorthand for the former, assuming the
current time as the base time. So in your query is equivalent to
('2006-05-17 00:00:00') - ('2006-05-17 10:02:01.727674-04' + '01:30:00')
Ok, I see now. Actually, now I read it back, the docs for age(timestamp)
say it calculates from current_date - I missed that earlier.
Looking that the output from :
(...)
might help, professorial pontificationas would RTFM
(http://www.postgresql.org/docs/8.1/static/functions-datetime.html;,
Table 9-26. Date/Time Functions). /professorial pontification
I've looked at that table so often that I figured I knew it from the top
of my head. Guess I was wrong...
Actually, IMO those tables could be a bit more readable if the borders
would be simple 1px lines and some padding is added left and right of
the cell contents.
Well, thanks for the pointers.
--
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 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] DB structure of PostGRE
Hi,my confession first..I have introduced myself this PostGRE db and I have a basic doubt?Where can I find a document about the structure of database/schema/users etc..and how is the data actually stored, like the datafiles, tablespaces. by now you must have guessed I have some knowledge of Oracle..(oops can I use thisname here??!!)Thanks for your feedback.Just a link to a document should be OK to start with, if someone is taking pain to explain, most welcome :)) Regards..--Venu
Re: [GENERAL] DB structure of PostGRE
On Wednesday 17 May 2006 12:19 pm, venu Vempati [EMAIL PROTECTED] thus communicated: -- Hi, -- my confession first..I have introduced myself this PostGRE db and I have a -- basic doubt? -- -- Where can I find a document about the structure of database/schema/users -- etc.. -- and how is the data actually stored, like the datafiles, tablespaces. -- by now you must have guessed I have some knowledge of Oracle..(oops can I -- use thisname here??!!) -- Thanks for your feedback.Just a link to a document should be OK to start -- with, if someone is taking pain to explain, most welcome :)) -- Regards.. -- --Venu -- http://www.postgresql.org/ has all sorts of documentation regarding the questions you ask. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Why won't it index scan?
Can someone help me understand why the 8.1.2 query below is
using a seq scan instead of an index scan? All relevant
columns appear to be indexed and all tables vacuum analyzed.
$ psql -c analyze verbose patient
INFO: analyzing public.patient
INFO: patient: scanned 3000 of 3353 pages, containing 117685 live rows and
5471 dead rows; 3000 rows in sample, 131533 estimated total rows
ANALYZE
$ psql -c select count(1) from patient
count
131661
(1 row)
$ psql -c analyze verbose visit
INFO: analyzing public.visit
INFO: visit: scanned 3000 of 19985 pages, containing 58520 live rows and
7045 dead rows; 3000 rows in sample, 389841 estimated total rows
ANALYZE
$ psql -c select count(1) from visit
count
389102
(1 row)
$ psql -c explain analyze select * from visit inner join patient on
patient.key = visit.patient_key where nursestation_key = '40';
QUERY PLAN
---
Merge Join (cost=27724.37..28457.01 rows=4956 width=421) (actual
time=1819.993..2004.802 rows=6 loops=1)
Merge Cond: (outer.patient_key = inner.key)
- Sort (cost=11859.31..11871.70 rows=4956 width=209) (actual
time=0.416..0.426 rows=6 loops=1)
Sort Key: visit.patient_key
- Bitmap Heap Scan on visit (cost=69.35..11555.14 rows=4956
width=209) (actual time=0.187..0.245 rows=6 loops=1)
Recheck Cond: (nursestation_key = 40)
- Bitmap Index Scan on idx_visit_nursestation_key
(cost=0.00..69.35 rows=4956 width=0) (actual time=0.158..0.158 rows=6 loops=1)
Index Cond: (nursestation_key = 40)
- Sort (cost=15865.05..16194.21 rows=131661 width=212) (actual
time=1768.501..1856.334 rows=61954 loops=1)
Sort Key: patient.key
- Seq Scan on patient (cost=0.00..4669.61 rows=131661 width=212)
(actual time=0.010..355.299 rows=131661 loops=1)
Total runtime: 2046.323 ms
(12 rows)
Table public.patient
Column |Type |
Modifiers
---+-+---
key | integer | not null default
nextval('patient_key_seq'::regclass)
...
Indexes:
pk_patient PRIMARY KEY, btree (key)
...
Table public.visit
Column |Type |
Modifiers
---+-+-
patient_key | integer | not null
nursestation_key | integer |
...
Indexes:
idx_visit_nursestation_key btree (nursestation_key)
idx_visit_patient_key btree (patient_key)
---(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] Why won't it index scan?
Ed L. wrote: Can someone help me understand why the 8.1.2 query below is using a seq scan instead of an index scan? Because the planner thinks a sequential scan would be faster than an index scan - in many situations, this is the case. See the FAQ: http://www.postgresql.org/docs/faqs.FAQ.html#item4.6 - John Burger MITRE ---(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] DB structure of PostGRE
venu Vempati [EMAIL PROTECTED] writes: Where can I find a document about the structure of database/schema/users etc.. and how is the data actually stored, like the datafiles, tablespaces. http://developer.postgresql.org/docs/postgres/storage.html regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] autovacuum connections are hidden
Trying to drop a database, this morning I ran into the not so unusual error: dropdb: database removal failed: ERROR: database test_seg1 is being accessed by other users however, when I did select * from pg_stat_activity on the pg server, it showed no connection to that db. Then I looked at the processes: tmp0% ps ax | grep test_seg1 10317 ?D 0:36 postgres: autovacuum process test_seg1 I imagine this doesn't show up because there is no connection, per se, the autovacuum process is a bon-a-fide backend process in its own right. It's just a bit confusing in this circumstance. I guess this is more of a heads up than a question. Another circumstance where this has bitten me is in doing a slony SUBSCRIBE_SET. At least in 1.1.5 the autovac daemon can deadlock with slony and cause the subscriber operation to fail. When this happened to me it was somewhat baffling at first because I had altered pg_hba.conf to forbid all non-superuser connections and there were no other connections visible at the time (in pg_stat_activity). Turning off autovacuum during the subscribe fixed this, but not without a little head-scratching (and maybe some luck). No way comes to my mind to improve the visiblity of autovac connections but I thought I would throw this out here in case anyone had any thoughts on the matter. -Casey ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Contributing code
Martijn van Oosterhout wrote: On Tue, May 16, 2006 at 08:12:05PM -0700, Don Y wrote: Hi, Is it possible to have one of my user defined data types reviewed/critiqued to see if there are things that I am not doing properly? Or, other things that I should be including? Or, should I just contribute it and hope for the best? (if so, how do I do that?) Put it on a website somewhere and link to. Interested parties could then look at it... An associate pointed out the obvious: let it run in production for a few months; that will find far more *realistic* issues than a casual inspection would! (Gee, does he distrust my code? : ). So, I'll deploy them and get feedback on which features I may need to add (some of the data types are probably a bit too exotic for most users). I figure I can always contribute them just before a release... --don ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why won't it index scan?
On Wednesday May 17 2006 10:37 am, Ed L. wrote: Can someone help me understand why the 8.1.2 query below is using a seq scan instead of an index scan? All relevant columns appear to be indexed and all tables vacuum analyzed. $ psql -c explain analyze select * from visit inner join patient on patient.key = visit.patient_key where nursestation_key = '40'; QUERY PLAN -- --- -- Merge Join (cost=27724.37..28457.01 rows=4956 width=421) (actual time=1819.993..2004.802 rows=6 loops=1) Merge Cond: (outer.patient_key = inner.key) - Sort (cost=11859.31..11871.70 rows=4956 width=209) (actual time=0.416..0.426 rows=6 loops=1) Sort Key: visit.patient_key - Bitmap Heap Scan on visit (cost=69.35..11555.14 rows=4956 width=209) (actual time=0.187..0.245 rows=6 loops=1) Recheck Cond: (nursestation_key = 40) - Bitmap Index Scan on idx_visit_nursestation_key (cost=0.00..69.35 rows=4956 width=0) (actual time=0.158..0.158 rows=6 loops=1) Index Cond: (nursestation_key = 40) - Sort (cost=15865.05..16194.21 rows=131661 width=212) (actual time=1768.501..1856.334 rows=61954 loops=1) Sort Key: patient.key - Seq Scan on patient (cost=0.00..4669.61 rows=131661 width=212) (actual time=0.010..355.299 rows=131661 loops=1) Total runtime: 2046.323 ms (12 rows) Increasing statistics target yielded index scan. How can I best find optimal statistics target to ensure 100% index scan? Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why won't it index scan?
On Wednesday May 17 2006 11:44 am, Ed L. wrote: On Wednesday May 17 2006 10:37 am, Ed L. wrote: Can someone help me understand why the 8.1.2 query below is using a seq scan instead of an index scan? All relevant columns appear to be indexed and all tables vacuum analyzed. $ psql -c explain analyze select * from visit inner join patient on patient.key = visit.patient_key where nursestation_key = '40'; QUERY PLAN -- --- -- Merge Join (cost=27724.37..28457.01 rows=4956 width=421) (actual time=1819.993..2004.802 rows=6 loops=1) Merge Cond: (outer.patient_key = inner.key) - Sort (cost=11859.31..11871.70 rows=4956 width=209) (actual time=0.416..0.426 rows=6 loops=1) Sort Key: visit.patient_key - Bitmap Heap Scan on visit (cost=69.35..11555.14 rows=4956 width=209) (actual time=0.187..0.245 rows=6 loops=1) Recheck Cond: (nursestation_key = 40) - Bitmap Index Scan on idx_visit_nursestation_key (cost=0.00..69.35 rows=4956 width=0) (actual time=0.158..0.158 rows=6 loops=1) Index Cond: (nursestation_key = 40) - Sort (cost=15865.05..16194.21 rows=131661 width=212) (actual time=1768.501..1856.334 rows=61954 loops=1) Sort Key: patient.key - Seq Scan on patient (cost=0.00..4669.61 rows=131661 width=212) (actual time=0.010..355.299 rows=131661 loops=1) Total runtime: 2046.323 ms (12 rows) Increasing statistics target yielded index scan. How can I best find optimal statistics target to ensure 100% index scan? I'm trying to understand what happened here, and I have a theory. There are 389K rows total, and 262K rows with a null indexed value. Their are 15164 non-null rows newer than those null rows. When stats target is set to 50 or less, analyze scans 15,000 rows or less. If it scans the newest rows/pages first, then is it possible it never sees any hint of the 262K null rows, and thus ends up with skewed stats that yield seq scans when idx scan is in order? If stat target is 50, analyze begins to include non-null rows in stat sample, yielding idx scans. Also, I see the most_common_vals array is not growing linearly with the stats target as the docs seem to suggest. I have 34 unique values, so with stats target = 34, I'd expect most_common_vals array to have 34 values, but it has 8. Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why won't it index scan?
Ed L. [EMAIL PROTECTED] writes: I'm trying to understand what happened here, and I have a theory. The problem is the horrid misestimation of the selectivity of nursestation_key = 40: - Bitmap Index Scan on idx_visit_nursestation_key (cost=0.00..69.35 rows=4956 width=0) (actual time=0.158..0.158 rows=6 loops=1) Index Cond: (nursestation_key = 40) When you're off by a factor of 800+ on the number of matching rows, you're going to arrive at a less than optimal plan. Increasing the stats target on visit.nursestation_key would be the solution. There are 389K rows total, and 262K rows with a null indexed value. Their are 15164 non-null rows newer than those null rows. When stats target is set to 50 or less, analyze scans 15,000 rows or less. If it scans the newest rows/pages first, then is it possible it never sees any hint of the 262K null rows, and thus ends up with skewed stats that yield seq scans when idx scan is in order? ANALYZE goes to some considerable trouble to make sure it gets an unbiased random sample. With those numbers it would see an expected 500+ of the nonnull rows; a sample containing none at all would be highly improbable. Also, I see the most_common_vals array is not growing linearly with the stats target as the docs seem to suggest. I have 34 unique values, so with stats target = 34, I'd expect most_common_vals array to have 34 values, but it has 8. To get into most_common_vals, a value has to occur more than once in the sample. Given the situation you have, it's not surprising that not all the possible values got into the stats. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] autovacuum connections are hidden
Casey Duncan [EMAIL PROTECTED] writes: however, when I did select * from pg_stat_activity on the pg server, it showed no connection to that db. Then I looked at the processes: tmp0% ps ax | grep test_seg1 10317 ?D 0:36 postgres: autovacuum process test_seg1 Hmm, autovacuum won't show up in pg_stat_activity because it never calls pgstat_report_activity(). Seems like maybe it should, though. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why won't it index scan?
On Wednesday May 17 2006 1:26 pm, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: I'm trying to understand what happened here, and I have a theory. The problem is the horrid misestimation of the selectivity of nursestation_key = 40: - Bitmap Index Scan on idx_visit_nursestation_key (cost=0.00..69.35 rows=4956 width=0) (actual time=0.158..0.158 rows=6 loops=1) Index Cond: (nursestation_key = 40) When you're off by a factor of 800+ on the number of matching rows, you're going to arrive at a less than optimal plan. Increasing the stats target on visit.nursestation_key would be the solution. Ok, makes sense. So, does this sound like we just happened to get repeatedly horribly unrepresentative random samples with stats target at 10? Are we at the mercy of randomness here? Or is there a better preventive procedure we can follow to systematically identify this kind of situation? Ed ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] ALTER SEQUENCE
Hi, It doesn't appear that there is a way to rename a sequence (ideally with a cascade action). Nor does there appear to be a way to change the owner of a sequence. Obviously, I can DROP and recreate... *but*, how prudent (foolish?) would it be just to change the entries in the system tables, instead? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PG_DIAG_STATEMENT_POSITION Where is it defined?
I have been searching through the cvs web browser for about 1/2 hour and can't find where PG_DIAG_STATEMENT_POSITION is defined. I need to know the integer value of the constant(pascal talk)/Symbol (c talk) so I can add it to my Delphi client library. Thanks in advance -- Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PG_DIAG_STATEMENT_POSITION Where is it defined?
On Wed, May 17, 2006 at 03:28:45PM -0500, Tony Caduto wrote: I have been searching through the cvs web browser for about 1/2 hour and can't find where PG_DIAG_STATEMENT_POSITION is defined. I need to know the integer value of the constant(pascal talk)/Symbol (c rgrep is your friend, or TAGS if you've set it up. src/include/postgres_ext.h Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] ALTER SEQUENCE
Don Y wrote: Hi, It doesn't appear that there is a way to rename a sequence (ideally with a cascade action). Uh, the ALTER SEQUENCE manual page says: Some variants of ALTER TABLE can be used with sequences as well; for example, to rename a sequence use ALTER TABLE RENAME. Does that help? -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG_DIAG_STATEMENT_POSITION Where is it defined?
Martijn van Oosterhout wrote: src/include/postgres_ext.h I did use grep but I am confused by what it showed, according to the docs: http://www.postgresql.org/docs/8.1/static/libpq-exec.html it should be a integer not 'P' Those symbols are used by PQresultErrorField which expects a integer value. Thanks, -- Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PG_DIAG_STATEMENT_POSITION Where is it defined?
Tony Caduto wrote:
Martijn van Oosterhout wrote:
src/include/postgres_ext.h
I did use grep but I am confused by what it showed,
according to the docs:
http://www.postgresql.org/docs/8.1/static/libpq-exec.html
it should be a integer not 'P'
Those symbols are used by PQresultErrorField which expects a integer
value.
Thanks,
I got it working, in Pascal you have to pass P as ord('P')
PQresultErrorField(fstatement,ord('P'));
It's one of those cryptic ways you can use a char as a integer in C :-)
--
Tony
---(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] Add column and specify the column position in a table
Hello, I am trying to insert one column to a specific position in a table. In mysql, I can do: . create table test(id varchar(3), name varchar(12)); . alter table test add column givename varchar(12) after id; I am looking for similar things in postgresql to add a new column to the correct position in a table. Could someone hint me please. Thanks alot! Ying Lu ---(end of broadcast)--- TIP 6: explain analyze is your friend
RES: [GENERAL] Add column and specify the column position in a table
Hi Ying: This feature seems by a mysql add tu create table command, only mysql can do dat. Alejandro Michelin Salomon ---Mensagem original- --De: [EMAIL PROTECTED] --[mailto:[EMAIL PROTECTED] Em nome de Emi Lu --Enviada em: quarta-feira, 17 de maio de 2006 18:21 --Para: pgsql-general@postgresql.org --Assunto: [GENERAL] Add column and specify the column --position in a table -- -- --Hello, -- --I am trying to insert one column to a specific position in a table. -- --In mysql, I can do: --. create table test(id varchar(3), name varchar(12)); --. alter table test add column givename varchar(12) after id; -- -- --I am looking for similar things in postgresql to add a new --column to the --correct position in a table. -- --Could someone hint me please. -- --Thanks alot! --Ying Lu -- -- -- -- -- -(end of --broadcast)--- --TIP 6: explain analyze is your friend -- -- --No virus found in this incoming message. --Checked by AVG Free Edition. --Version: 7.1.392 / Virus Database: 268.6.0/341 - Release --Date: 16/5/2006 -- -- --No virus found in this incoming message. --Checked by AVG Free Edition. --Version: 7.1.392 / Virus Database: 268.6.0/341 - Release --Date: 16/5/2006 -- -- -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.6.0/341 - Release Date: 16/5/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.6.0/341 - Release Date: 16/5/2006 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ALTER SEQUENCE
Bruce Momjian wrote: Don Y wrote: Hi, It doesn't appear that there is a way to rename a sequence (ideally with a cascade action). Uh, the ALTER SEQUENCE manual page says: Uh, the 8.0.3 man page for ALTER SEQUENCE makes no mention of this. Nor does \h ALTER SEQUENCE in psql yield any pointers. Some variants of ALTER TABLE can be used with sequences as well; for example, to rename a sequence use ALTER TABLE RENAME. Does that help? Sure! It appears to allow both the rename and change of ownership. Thanks! Obviously, the documentation doesn't agree with the code :-( But, it still leaves unanswered the question of the risk involved in just changing the name/owner in the system tables... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ALTER SEQUENCE
Don Y wrote: Bruce Momjian wrote: Don Y wrote: Hi, It doesn't appear that there is a way to rename a sequence (ideally with a cascade action). Uh, the ALTER SEQUENCE manual page says: Uh, the 8.0.3 man page for ALTER SEQUENCE makes no mention of this. Nor does \h ALTER SEQUENCE in psql yield any pointers. Some variants of ALTER TABLE can be used with sequences as well; for example, to rename a sequence use ALTER TABLE RENAME. Does that help? Sure! It appears to allow both the rename and change of ownership. Thanks! I see the documentation mention added August 1, 2005 byt Tom Lane. Obviously, the documentation doesn't agree with the code :-( But, it still leaves unanswered the question of the risk involved in just changing the name/owner in the system tables... It is best to use ALTER. The only other sure-safe way to do it is to look at the ALTER code and do the same things with the system tables. However, in most cases a system table modification works fine, but I don't recommend it for production servers. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: RES: [GENERAL] Add column and specify the column position in
This feature seems by a mysql add tu create table command, only mysql can do dat. Alejandro Michelin Salomon I think it is a very useful feature for postgresql to support it. If we have this feature supported, I do not have to recreate the table and resetup all foreign key constraints, views, triggers, etc that are based on the table. --I am trying to insert one column to a specific position in a table. -- --In mysql, I can do: --. create table test(id varchar(3), name varchar(12)); --. alter table test add column givename varchar(12) after id; -- --I am looking for similar things in postgresql to add a new --column to the --correct position in a table. --Could someone hint me please. --Thanks alot! Ying Lu ---(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] ALTER SEQUENCE
Bruce Momjian wrote: Don Y wrote: Bruce Momjian wrote: Don Y wrote: Hi, It doesn't appear that there is a way to rename a sequence (ideally with a cascade action). Uh, the ALTER SEQUENCE manual page says: Uh, the 8.0.3 man page for ALTER SEQUENCE makes no mention of this. Nor does \h ALTER SEQUENCE in psql yield any pointers. Some variants of ALTER TABLE can be used with sequences as well; for example, to rename a sequence use ALTER TABLE RENAME. Does that help? Sure! It appears to allow both the rename and change of ownership. Thanks! I see the documentation mention added August 1, 2005 byt Tom Lane. Date tag on the bottom of my man pages is 2005-01-17 -- so that explains *that*! : In general, how safe is it to use current man pages (to sidestep these sorts of issues)? Obviously, there will be things in the newer pages that reflect changes NOT present in older versions... but, will the documentation updates (i.e. this a prime example) outweigh the confusion added by documentation for not-yet-existent features/fixes/etc.?) Obviously, the documentation doesn't agree with the code :-( But, it still leaves unanswered the question of the risk involved in just changing the name/owner in the system tables... It is best to use ALTER. The only other sure-safe way to do it is to look at the ALTER code and do the same things with the system tables. However, in most cases a system table modification works fine, but I don't recommend it for production servers. One would *hope* that there was no redundant information in the tables... but, realistically, that may not be the case (efficiency hacks, etc.) For *this* problem, an obvious solution exists. And, even if it didn't, dropping the sequence, recreating it and reinitializing it wouldn't be that painful. I'm just wondering how aggressive I should be in tinkering... :-( Thanks! --don ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] FATAL: could not read statistics message
On Tue, May 16, 2006 at 07:24:43PM -0400, Sean Davis wrote: Jim C. Nasby wrote: On Tue, May 16, 2006 at 03:41:07PM -0400, Sean Davis wrote: I had cranked things up a bit from the standard install. shared_buffers = 15000 # min 16 or max_connections*2, 8KB each #temp_buffers = 1000# min 100, 8KB each #max_prepared_transactions = 50 # can be 0 or more work_mem = 1# min 64, size in KB maintenance_work_mem = 128000 # min 1024, size in KB max_stack_depth = 4096 # min 100, size in KB You didn't say anything about how much memory you have, but you need to be careful with *work_mem, as being too agressive can run the machine out of memory. I have 4Gb of memory and the machine is pretty much a devoted database server. We use it mainly for data warehousing and mining; there are rarely more than 2 active connections and never more than 5 total, so I have felt pretty comfortable with leaving work_mem pretty generous. I will likely have to change that if the machine becomes more active. Well, 10M of work_mem isn't exactly generous in that case, since you'd probably end up using no more than 100M at once. Of course if you had 100 connections and only 1G, it'd be a different story... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Add column and specify the column position in a table
Emi Lu wrote: Hello, I am trying to insert one column to a specific position in a table. In mysql, I can do: . create table test(id varchar(3), name varchar(12)); . alter table test add column givename varchar(12) after id; I am looking for similar things in postgresql to add a new column to the correct position in a table. Could someone hint me please. You can't. You can only add columns to the end of a table. Thanks alot! Ying Lu ---(end of broadcast)--- TIP 6: explain analyze is your friend -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ALTER SEQUENCE
Don Y wrote: Bruce Momjian wrote: Don Y wrote: Bruce Momjian wrote: Don Y wrote: Hi, It doesn't appear that there is a way to rename a sequence (ideally with a cascade action). Uh, the ALTER SEQUENCE manual page says: Uh, the 8.0.3 man page for ALTER SEQUENCE makes no mention of this. Nor does \h ALTER SEQUENCE in psql yield any pointers. Some variants of ALTER TABLE can be used with sequences as well; for example, to rename a sequence use ALTER TABLE RENAME. Does that help? Sure! It appears to allow both the rename and change of ownership. Thanks! I see the documentation mention added August 1, 2005 byt Tom Lane. Date tag on the bottom of my man pages is 2005-01-17 -- so that explains *that*! : In general, how safe is it to use current man pages (to sidestep these sorts of issues)? Obviously, there will be Uh, not very safe. things in the newer pages that reflect changes NOT present in older versions... but, will the documentation updates (i.e. this a prime example) outweigh the confusion added by documentation for not-yet-existent features/fixes/etc.?) Probably not. This is an edge case, and often we backpatch changes like this, though that didn't happen in this case. For example, when I find doc things to add for 8.2, I add them to 8.1 if appropriate. Obviously, the documentation doesn't agree with the code :-( But, it still leaves unanswered the question of the risk involved in just changing the name/owner in the system tables... It is best to use ALTER. The only other sure-safe way to do it is to look at the ALTER code and do the same things with the system tables. However, in most cases a system table modification works fine, but I don't recommend it for production servers. One would *hope* that there was no redundant information in the tables... but, realistically, that may not be the case (efficiency hacks, etc.) Uh, there is dependency information that might be affected by certain updates. It isn't really redundant. For *this* problem, an obvious solution exists. And, even if it didn't, dropping the sequence, recreating it and reinitializing it wouldn't be that painful. I'm just wondering how aggressive I should be in tinkering... :-( You really have to consult the code to find out. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Announce: GPL Framework centered on Postgres
On Wed, May 17, 2006 at 08:33:56AM +0100, Peter Wilson wrote: The BSD license does allow others to create a closed-source project from your code - but my view is that isn't too important. You'd be the natural port of call if they wanted consultancy on how to do that. I'd argue that if you can't compete against that then you have much bigger problems, especially since the OSS world tends to take a pretty dim view on 'hostile takeovers' of OSS code. In any case, I believe there's other licenses that protect against someone closing your code. I *thought* the MPL was one, but maybe not. Perhaps Apache's license does. In any case I'd be very careful with any license that comes out of FSF, since it's pretty clear what their views on commercial software are... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Contributing code
On Wed, May 17, 2006 at 10:46:52AM -0700, Don Y wrote: Martijn van Oosterhout wrote: On Tue, May 16, 2006 at 08:12:05PM -0700, Don Y wrote: Hi, Is it possible to have one of my user defined data types reviewed/critiqued to see if there are things that I am not doing properly? Or, other things that I should be including? Or, should I just contribute it and hope for the best? (if so, how do I do that?) Put it on a website somewhere and link to. Interested parties could then look at it... An associate pointed out the obvious: let it run in production for a few months; that will find far more *realistic* issues than a casual inspection would! (Gee, does he distrust my code? : ). So, I'll deploy them and get feedback on which features I may need to add (some of the data types are probably a bit too exotic for most users). I figure I can always contribute them just before a release... Well, in the meantime, if you plan on making it public anyway, why not put it on pgFoundry so that others can poke at it now if the wish? It wouldn't surprise me if right now someone is going I wish there was something that would let me do... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: RES: [GENERAL] Add column and specify the column position in
On Wed, May 17, 2006 at 05:48:52PM -0400, Emi Lu wrote: I think it is a very useful feature for postgresql to support it. If we have this feature supported, I do not have to recreate the table and resetup all foreign key constraints, views, triggers, etc that are based on the table. Uh, why do you have to do that? Please don't tell me you're using SELECT *... In any case, there's extensive discussion about this in the -hackers archives. IIRC, there is consensus that this would be nice to have but no one has cared enough to actually make it happen. There are some non-trivial issues since this would mean either completely re-writing the table when you do an ALTER or you'd have to be able to divorce the catalog representation of a table with the on-disk representation. Though there are other advantages to doing the later, it's non-trivial. --I am trying to insert one column to a specific position in a table. -- --In mysql, I can do: --. create table test(id varchar(3), name varchar(12)); --. alter table test add column givename varchar(12) after id; -- --I am looking for similar things in postgresql to add a new --column to the --correct position in a table. --Could someone hint me please. --Thanks alot! Ying Lu ---(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 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] ALTER SEQUENCE
On Wed, May 17, 2006 at 03:00:48PM -0700, Don Y wrote: I see the documentation mention added August 1, 2005 byt Tom Lane. Date tag on the bottom of my man pages is 2005-01-17 -- so that explains *that*! : This is a very minor reason why you should be running the most recent 8.0.x release and not 8.0.3. A much bigger reason is that there are data-loss bugs that have been fixed. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] postgres vs. oracle for very large tables
On Mon, 15 May 2006 16:00:04 -0500, Scott Marlowe wrote: 5: You're gonna need to load test this to see. You can get the oracle 10G express for free. I think that one handles multiple connections, but is limited to 2 gigs of data. The exact limitations of Oracle Database XE are as follows: Any use of the Oracle Database Express Edition is subject to the following limitations; 1. Express Edition is limited to a single instance on any server; 2. Express Edition may be installed on a multiple CPU server, but may only be executed on one processor in any server; 3. Express Edition may only be used to support up to 4GB of user data (not including Express Edition system data); 4. Express Edition may use up to 1 GB RAM of available memory. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Server/Database/Schema Definitions
When coding table and function scripts, how do you specify which server/database/schema where you want new tables and functions to reside? Every time I execute my creation scripts, it puts everything in the public schema under the PostgreSQL server. I'm new to PostgreSQL and haven't found any documentation discussing these characteristics. Do you explicitly define the server.schema.table_name in the create table statement? Should the tablespace be set to pg_default and the owner to postgres? Any help you could provide would be greatly appreciated. Thanks, Brandon ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Concatenate WAL contents
Thanks to Jim for replying us about WAL archiving! Indeed, WAL archiving has been abandoned for our needs. The content logged in WAL is more than only the modified data but the binary content of the modified table (or a part of the table). Then it is not possible to merge modifications on a common table from different databases. Because replication system implies to increase database size, we decided to serialize the write database traffic in our code. Then we deserialize the traffic logs on the multisite database in a nightly cron. We suppose that the time to serialize/deserialize is not so important than the content exanged and the size of databases. Hugues -Message d'origine- De : Jim C. Nasby [mailto:[EMAIL PROTECTED] Envoyé : mardi 16 mai 2006 00:04 À : Houssais Hugues Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] Concatenate WAL contents On Wed, May 10, 2006 at 11:55:49AM +0200, Houssais Hugues wrote: Hi, In order to unify many distant Postgres databases, we decided to use WAL archiving. The idea is to concatenate a distant WAL archive at the beginning of a local WAL archive before launching PITR restore. Does anyone know how to concatenate WAL contents, i.e. decode and encode WAL files using the address of backup files (aaa in x.aaa.backup files)? It's not possible to do multi-master WAL-based replication, because it describes table changes at a binary level. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Server/Database/Schema Definitions
On Tue, May 16, 2006 at 09:23:09AM -0500, Brandon E Hofmann wrote: When coding table and function scripts, how do you specify which server/database/schema where you want new tables and functions to reside? Every time I execute my creation scripts, it puts everything in the public schema under the PostgreSQL server. I'm new to PostgreSQL and haven't found any documentation discussing these characteristics. Do you explicitly define the server.schema.table_name in the create table statement? Should the tablespace be set to pg_default and the owner to postgres? You can do that, yes. You can also change search_path; any object not created in a specific schema will go into the first schema listed in search_path. You can set a default tablespace in postgresql.conf, at the database level, and I think at the session level as well. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PGSQL Database Recovery in Portland Oregon Area needed ASAP
Title: PGSQL Database Recovery in Portland Oregon Area needed ASAP I have a client that is running an older version of Lyris List Manager against PostGres 7.1.2, that has crashed their database beyond my ability to recover it. The error that I am getting when we try and restart the database is: /etc/init.d/postgresql start Starting postgresql service: postmaster successfully started DEBUG: database system was interrupted being in recovery at 2006-05-15 14:01:54 PDT This propably means that some data blocks are corrupted and you will have to use last backup for recovery. DEBUG: CheckPoint record at (230, 4199276628) DEBUG: Redo record at (230, 4199276628); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 159854941; NextOid: 34688714 DEBUG: database system was not properly shut down; automatic recovery in progress... DEBUG: redo starts at (230, 4199276692) [ OK ] FATAL 2: btree_split_redo: uninitialized next right page /usr/bin/postmaster: Startup proc 30311 exited with status 512 - abort I have gone through the stuff on-line concerning this error and attempted to reinitialize the database and restore the night before's backup, but apparently the backups were not quite setup correctly as I cannot get a valid restore either. I am out of my depth on this one and am willing to contract somebody to help get this database back on-line again ASAP, as the client is getting frantic. If you have proven experience doing this sort of recovery, please contact me at the number or email address below. I am not a member of the lists, so please contact me directly. Rates are negotiable, but I will have to clear them with client before we proceed. markh Mark A. Holm President InfoArch, Inc. Cell: (503) 750-9741 7456 SW Baseline, PMB#123. Office: (503) 943-3202 Hillsboro, OR 97123 Fax: (503) 591-8584 http://www.infoarch.com mailto:[EMAIL PROTECTED]
Re: [GENERAL] Server/Database/Schema Definitions
On May 16, 2006, at 10:23 AM, Brandon E Hofmann wrote: When coding table and function scripts, how do you specify which server/database/schema where you want new tables and functions to reside? Every time I execute my creation scripts, it puts everything in the public schema under the PostgreSQL server. I'm new to PostgreSQL and haven't found any documentation discussing these characteristics. Do you explicitly define the server.schema.table_name in the create table statement? Should the tablespace be set to pg_default and the owner to postgres? See this page for a discussion of search_path http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html I typically set the search path at the top of the script. But doing it like this can certainly trip you up in cases where you might make changes and do not execute the script from top to bottom (e.g. redefine a single function). If I have a specific user (or role) for a certain database or project, I like to associate the search path with the role so I don't have to remember to do it every time I work on the database. See ALTER ROLE SET syntax for details on this: http://www.postgresql.org/docs/8.1/interactive/sql-alterrole.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Add column and specify the column position in a table
the position doesn't really matter in any relational structure.. does it? On 5/17/06, Emi Lu [EMAIL PROTECTED] wrote: Hello, I am trying to insert one column to a specific position in a table. In mysql, I can do: . create table test(id varchar(3), name varchar(12)); . alter table test add column givename varchar(12) after id; I am looking for similar things in postgresql to add a new column to the correct position in a table. Could someone hint me please. Thanks alot! Ying Lu ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Contributing code
Don Y wrote: So, I'll deploy them and get feedback on which features I may need to add (some of the data types are probably a bit too exotic for most users). I figure I can always contribute them just before a release... Just before a release would actually be a bad time to contribute the code, if you want to get it accepted into PostgreSQL, as the people who would be competent to review and potentially accept it all tend to be very busy just before a release. There have been disappointed people in the past for that very reason. The earlier you publish your code, the better. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why won't it index scan?
Ed L. [EMAIL PROTECTED] writes: So, does this sound like we just happened to get repeatedly horribly unrepresentative random samples with stats target at 10? Are we at the mercy of randomness here? Or is there a better preventive procedure we can follow to systematically identify this kind of situation? I think the real issue is that stats target 10 is too small for large tables: the samples are just not large enough to support a decent numdistinct estimate, which is the critical stat for cases such as this (ie, estimating the number of hits on a value that's not in the most-common-values list). The reason the default is currently 10 is just conservatism: it was already an order of magnitude better than what it replaced (a *single* representative value) and I didn't feel I had the evidence to justify higher values. It's become clear that the default ought to be higher, but I've still got no good fix on a more reasonable default. 100 might be too much, or then again maybe not. I encourage you to play around with default_statistics_target and see what you can learn about quality of estimates vs. planning time. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Contributing code
Tim Allen [EMAIL PROTECTED] writes: Don Y wrote: So, I'll deploy them and get feedback on which features I may need to add (some of the data types are probably a bit too exotic for most users). I figure I can always contribute them just before a release... Just before a release would actually be a bad time to contribute the code, if you want to get it accepted into PostgreSQL, as the people who would be competent to review and potentially accept it all tend to be very busy just before a release. Yeah, I was about to make the same remark. The other thing we see over and over is that once some idea or code sees the light of day, there are almost always some better ideas offered by someone in the community, and thus you need to budget some time for rework in response to comments. If you're thinking of contributing something major for PG 8.2 (feature freeze this July) it's already very late to not have at least a complete design out there for public comment. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why won't it index scan?
Tom Lane [EMAIL PROTECTED] writes: Ed L. [EMAIL PROTECTED] writes: So, does this sound like we just happened to get repeatedly horribly unrepresentative random samples with stats target at 10? Are we at the mercy of randomness here? Or is there a better preventive procedure we can follow to systematically identify this kind of situation? I think the real issue is that stats target 10 is too small for large tables: the samples are just not large enough to support a decent numdistinct estimate, which is the critical stat for cases such as this (ie, estimating the number of hits on a value that's not in the most-common-values list). There's been some discussion on -hackers about this area. Sadly the idea of using samples to calculate numdistinct estimates is fundamentally on pretty shaky ground. Whereas a fixed sample size works fine for calculating distribution of values, in order to generate consistent precision for numdistinct estimates the samples will have to be a constant fraction of the table -- and unfortunately a pretty large fraction at that. So sadly I think at the mercy of randomness is pretty accurate. You'll have to raise the statistics target as the table grows and I expect you'll eventually run into some downsides of large stats targets. Some better algorithms were posted, but they would require full table scans during analyze, not just samples. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG_DIAG_STATEMENT_POSITION Where is it defined?
On Wed, May 17, 2006 at 03:58:21PM -0500, Tony Caduto wrote:
Tony Caduto wrote:
http://www.postgresql.org/docs/8.1/static/libpq-exec.html
it should be a integer not 'P'
I got it working, in Pascal you have to pass P as ord('P')
PQresultErrorField(fstatement,ord('P'));
It's one of those cryptic ways you can use a char as a integer in C :-)
Hardly cryptic, it's a fairly common tactic if you need an integer to
represent something and you don't particularly care what integer, but
the character can be more meaningful. A char *is* an integer, just only
one byte, not four.
Have a nice day,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
From each according to his ability. To each according to his ability to
litigate.
signature.asc
Description: Digital signature
