Re: [HACKERS] Project scheduling issues (was Re: Per tuple overhead,
On Sun, Jun 09, 2002 at 02:32:22AM -0300, Marc G. Fournier wrote: > Right now, Sept 1st is the "preferred date to go beta" ... when Sept 1st I agree with Bruce, Sept 1st is the deadline and right time for all discussion about shift of this date is Sept 2nd. Not now, else you never will see end of the cycle :-) Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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: [HACKERS] How can we help?
Le Samedi 8 Juin 2002 01:43, Scott Shattuck a écrit : > What is the planned status of Java support in the engine? Is there anyone > working on JVM integration at this stage and if not, how could we best > integrate with the team to take on this task? You may be interested in looking at PLjava on http://sourceforge.net/projects/pljava/ Cheers, Jean-Michel POURE ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Fri, Jun 07, 2002 at 06:48:31PM -0700, Thomas Lockhart wrote:
>
> > Proposal #4: Create to_char(INTERVAL, 'format string') Function.
> > Reason: self-evident, I think.
>
> Oh. Didn't know it wasn't already there.
I'm _sure_ that to_char() is there for interval.
testt=# select to_char('33s 15h 10m 5month'::interval, 'HH:MI:SS Month');
to_char
03:10:33 May
(1 row)
test=# select version();
version
-
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
And it's in the docs too
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Mon, 2002-06-10 at 09:58, Karel Zak wrote:
> On Fri, Jun 07, 2002 at 06:48:31PM -0700, Thomas Lockhart wrote:
> >
> > > Proposal #4: Create to_char(INTERVAL, 'format string') Function.
> > > Reason: self-evident, I think.
> >
> > Oh. Didn't know it wasn't already there.
>
> I'm _sure_ that to_char() is there for interval.
>
> testt=# select to_char('33s 15h 10m 5month'::interval, 'HH:MI:SS Month');
> to_char
>
> 03:10:33 May
> (1 row)
Does "May" make sense for an _interval _ ? (Feb 22 + May = Jul 22)?
Would not "5 months" make more sense ?
Or is it some ISO standard ?
Ditto for 15h -> 03 .
Hannu
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Timestamp/Interval proposals: Part 2
> > I'm _sure_ that to_char() is there for interval.
> >
> > testt=# select to_char('33s 15h 10m 5month'::interval, 'HH:MI:SS Month');
> > to_char
> >
> > 03:10:33 May
> > (1 row)
>
> Does "May" make sense for an _interval _ ? (Feb 22 + May = Jul 22)?
>
> Would not "5 months" make more sense ?
to_char() convert interval to 'tm' and make output like this struct,
I don't know what other is possible do with it.
> Or is it some ISO standard ?
>
> Ditto for 15h -> 03 .
HH vs. HH24
test=# select to_char('33s 15h 10m 5months'::interval, 'HH24:MI:SS Month');
to_char
15:10:33 May
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(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: [HACKERS] Missing types in C header files
I've looked in fmgr.h and there is no definition of this type there - it uses the type, but does not define it. Peter Eisentraut wrote: > > Tony Griffiths writes: > > > I've done a search of all the header files in my installation (and also > > all the source files that I compiled), and cannot find the definition of > > the PGFunction type. Does anyone have any idea of where I can find this > > definition, or of why it might be missing. > > fmgr.h > > -- > Peter Eisentraut [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Tony - Dr. Tony Griffiths Research Fellow Information Management Group, Department of Computer Science, The University of Manchester, Oxford Road, Manchester M13 9PL, United Kingdom Tel. +44 (0) 161 275 6139 Fax +44 (0) 161 275 6236 email [EMAIL PROTECTED] - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Roadmap for a Win32 port
How about a SOAP interface and a web-based front end that provides the cross platform support? My company's TIBET framework would provide a solid foundation for this kind of admin suite. In fact, we're already in the planning stages on doing just that. ss Scott Shattuck Technical Pursuit Inc. - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Bruce Momjian" <[EMAIL PROTECTED]> Cc: "PostgreSQL-development" <[EMAIL PROTECTED]> Sent: Friday, June 07, 2002 11:42 AM Subject: Re: [HACKERS] Roadmap for a Win32 port > Bruce Momjian writes: > > > GUI > > --- > > pgAdmin2 http://pgadmin.postgresql.org/pgadmin2.php?ContentID=1 > > pgaccesshttp://pgaccess.org/ > > Java admin (to be written) > > Dev-C++ admin (to be written) http://sourceforge.net/projects/dev-cpp/ > > Surely Unix folks would like a GUI as well? > > -- > Peter Eisentraut [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Use of /etc/services?
SuSE Linux 8.0 On Fri, 2002-06-07 at 07:41, Bruce Momjian wrote: > Peter Eisentraut wrote: > > Since we now have an official entry in /etc/services, shouldn't we be able > > to make use of it, by using getservbyname() if a nonnumeric port number is > > specified? > > Is any OS actually shipping us in /etc/services? > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Ettore Simone SuSE Linux srl Cel. +39 348 4904011 Via Montanara, 26 Tel. +39 059 5395 41 41051 Castelnuovo R. (MO) Fax +39 059 5332009 Via Proust, 40 Tel. +39 06 50514545 00143 Roma ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Use of /etc/services?
> Is any OS actually shipping us in /etc/services? It's right here in SuSE Linux 8.0. It was not in 7.3, so maybe it's officially included from now on. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tuplesort: unexpected end of data
On Sun, 9 Jun 2002, Tom Lane wrote: >Can't you set up a situation where the failure is reproducible, then? >On a day where you get the failure, dump the database and see if >you can load the data into a fresh database and reproduce the problem. > Ok, I will do that... >I don't necessarily believe the flaky-hardware theory, but I can't >make much progress on the bug theory without a test case to look at. > Neither I believe it because the machine is well tested (including a 24h memtest). But there is something I can't get of my mind: once a day my app "forces" PG to "play" with some 3GB of disk data in a ext2 fs. It is known that sometimes ext2 corrupts data... Thanks for the effort! :-) -- o__Bem haja, _.>/ _ NunoACHenriques (_) \(_) ~~~ http://students.fct.unl.pt/users/nuno/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Missing types in C header files
I've found the mistake - as usual it's down to me! I didn't realise that I had to include postgres.h before including geo_decls.h All now compiles ok. Tony Tony Griffiths wrote: [EMAIL PROTECTED]"> I've looked in fmgr.h and there is no definition of this type there - ituses the type, but does not define it.Peter Eisentraut wrote: Tony Griffiths writes: I've done a search of all the header files in my installation (and alsoall the source files that I compiled), and cannot find the definition ofthe PGFunction type. Does anyone have any idea of where I can find thisdefinition, or of why it might be missing. fmgr.h--Peter Eisentraut [EMAIL PROTECTED]---(end of broadcast)---TIP 6: Have you searched our list archives?http://archives.postgresql.org
Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Mon, 2002-06-10 at 10:49, Karel Zak wrote:
>
> > > I'm _sure_ that to_char() is there for interval.
> > >
> > > testt=# select to_char('33s 15h 10m 5month'::interval, 'HH:MI:SS Month');
> > > to_char
> > >
> > > 03:10:33 May
> > > (1 row)
> >
> > Does "May" make sense for an _interval _ ? (Feb 22 + May = Jul 22)?
> >
> > Would not "5 months" make more sense ?
>
> to_char() convert interval to 'tm' and make output like this struct,
My point is that to_char-ing intervals by converting them to dates is
non-intuitive.
It is really confusing to say that an interval of 5 months = "May"
and 15months == "1 March" ;(
> I don't know what other is possible do with it.
perhaps show them with the precision specified and keep data for bigger
units in biggest specified unit.
to_char('2years 1min 4sec'::interval, 'MM SS'); ==> '24mon 64sec'
to_char('2years 1min 4sec'::interval, 'MM MI SS'); ==> '24mon 1min 4sec'
> > Or is it some ISO standard ?
Does anyone know what standard says about interval formats?
annu
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tuplesort: unexpected end of data
NunoACHenriques <[EMAIL PROTECTED]> writes: > Neither I believe it because the machine is well tested (including a > 24h memtest). But there is something I can't get of my mind: once a day my > app "forces" PG to "play" with some 3GB of disk data in a ext2 fs. It is > known that sometimes ext2 corrupts data... Unless you've got things set up so that the temporary files created for the sorting step are in the ext2 partition, this doesn't seem like it could be the source of the problem. A more plausible theory is that a segment of main RAM is bad, but you happen not to use that part of RAM except under heavy load (ie, while running this daily batch job). Or it could just be a bug. If you can get a reproducible test case I'll be happy to dig into it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Mon, Jun 10, 2002 at 04:26:47PM +0200, Hannu Krosing wrote:
> > to_char() convert interval to 'tm' and make output like this struct,
>
> My point is that to_char-ing intervals by converting them to dates is
> non-intuitive.
>
> It is really confusing to say that an interval of 5 months = "May"
> and 15months == "1 March" ;(
>
> > I don't know what other is possible do with it.
>
> perhaps show them with the precision specified and keep data for bigger
> units in biggest specified unit.
>
> to_char('2years 1min 4sec'::interval, 'MM SS'); ==> '24mon 64sec'
> to_char('2years 1min 4sec'::interval, 'MM MI SS'); ==> '24mon 1min 4sec'
>
Hmmm, but it's really out of to_char(). For example 'MM' is defined
as number in range 1..12.
The to_char() convert date/time data to string and not to better formatted
interval. The right name for your request is to_interval().
TODO?
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(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
[HACKERS] ALTER TABLE... OWNER bugette
In a 7.3 dev test database, I have a table called msysconf in a schema called biblio. If I execute: ALTER TABLE biblio.msysconf OWNER TO dpage I get: ERROR: msysconf_idx is an index relation There is an index with this name on the table. Any ideas? Regards, Dave. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Roadmap for a Win32 port cross platform admin tool
Scott, I just started a java admin tool project on sf called www.sf.net/projects/jpgadmin, which should be able to handle web based interfaces, the idea being to seperate the model and view so that we can support a swing or web interface. Dave On Fri, 2002-06-07 at 18:05, Scott Shattuck wrote: > How about a SOAP interface and a web-based front end that provides the cross > platform support? My company's TIBET framework would provide a solid > foundation for this kind of admin suite. In fact, we're already in the > planning stages on doing just that. > > ss > > Scott Shattuck > Technical Pursuit Inc. > > > - Original Message - > From: "Peter Eisentraut" <[EMAIL PROTECTED]> > To: "Bruce Momjian" <[EMAIL PROTECTED]> > Cc: "PostgreSQL-development" <[EMAIL PROTECTED]> > Sent: Friday, June 07, 2002 11:42 AM > Subject: Re: [HACKERS] Roadmap for a Win32 port > > > > Bruce Momjian writes: > > > > > GUI > > > --- > > > pgAdmin2 > http://pgadmin.postgresql.org/pgadmin2.php?ContentID=1 > > > pgaccesshttp://pgaccess.org/ > > > Java admin (to be written) > > > Dev-C++ admin (to be written) > http://sourceforge.net/projects/dev-cpp/ > > > > Surely Unix folks would like a GUI as well? > > > > -- > > Peter Eisentraut [EMAIL PROTECTED] > > > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Missing types in C header files
Tony Griffiths <[EMAIL PROTECTED]> writes: > I've looked in fmgr.h and there is no definition of this type there - it > uses the type, but does not define it. Eh? typedef Datum (*PGFunction) (FunctionCallInfo fcinfo); Looks like a definition to me ... regards, tom lane ---(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: [HACKERS] [SQL] Efficient DELETE Strategies
Christoph Haller <[EMAIL PROTECTED]> writes: > Based on an entry in the mailing list from 30 Oct 2001 > about efficient deletes on subqueries, > I've found two ways to do so (PostgreSQL 7.2.1): > ... > Is there a way to put the second form (more complicated, but faster) > in one statement? > Or is there even a third way to delete, which I cannot see? The clean way to do this would be to allow extra FROM-list relations in DELETE. We already have a similar facility for UPDATE, so it's not clear to me why there's not one for DELETE. Then you could do, say, DELETE FROM onfvalue , onfvalue j WHERE j.sid= 5 AND onfvalue.lid = j.lid AND onfvalue.mid = j.mid AND onfvalue.timepoint = j.timepoint AND onfvalue.entrancetime < j.entrancetime ; If you were using two separate tables you could force this to happen via an implicit FROM-clause entry, much as you've done in your second alternative --- but there's no way to set up a self-join in a DELETE because of the lack of any place to put an alias declaration. AFAIK this extension would be utterly trivial to implement, since all the machinery is there already --- for 99% of the backend, it doesn't matter whether a FROM-item is implicit or explicit. We'd only need to argue out what the syntax should be. I could imagine DELETE FROM relation_expr [ , table_ref [ , ... ] ] [ WHERE bool_expr ] or DELETE FROM relation_expr [ FROM table_ref [ , ... ] ] [ WHERE bool_expr ] The two FROMs in the second form look a little weird, but they help to make a clear separation between the deletion target table and the merely-referenced tables. Also, the first one might look to people like they'd be allowed to write DELETE FROM foo FULL JOIN bar ... which is not any part of my intention (it's very unclear what it'd mean for the target table to be on the nullable side of an outer join). OTOH there'd be no harm in outer joins in a separate from-clause, eg DELETE FROM foo FROM (bar FULL JOIN baz ON ...) WHERE ... Actually, either syntax above would support that; I guess what's really bothering me about the first syntax is that a comma suggests a list of things that will all be treated similarly, while in reality the first item will be treated much differently from the rest. Does anyone know whether other systems that support the UPDATE extension for multiple tables also support a DELETE extension for multiple tables? If so, what's their syntax? A somewhat-related issue is that people keep expecting to be able to attach an alias to the target table name in UPDATE and DELETE; seems like we get that question every couple months. While this is clearly disallowed by the SQL spec, it's apparently supported by some other implementations (else we'd not get the question so much). Should we add that extension to our syntax? Or should we continue to resist it? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Mon, 2002-06-10 at 15:43, Karel Zak wrote:
> On Mon, Jun 10, 2002 at 04:26:47PM +0200, Hannu Krosing wrote:
>
> > > to_char() convert interval to 'tm' and make output like this struct,
> >
> > My point is that to_char-ing intervals by converting them to dates is
> > non-intuitive.
> >
> > It is really confusing to say that an interval of 5 months = "May"
> > and 15months == "1 March" ;(
> >
> > > I don't know what other is possible do with it.
> >
> > perhaps show them with the precision specified and keep data for bigger
> > units in biggest specified unit.
> >
> > to_char('2years 1min 4sec'::interval, 'MM SS'); ==> '24mon 64sec'
> > to_char('2years 1min 4sec'::interval, 'MM MI SS'); ==> '24mon 1min 4sec'
> >
>
> Hmmm, but it's really out of to_char(). For example 'MM' is defined
> as number in range 1..12.
>
> The to_char() convert date/time data to string and not to better formatted
> interval. The right name for your request is to_interval().
if there were a to_interval() then it should convert char data to
interval, like to_date(), to_number() and to_timestamp() do
actually we currently have to_char(x,t) functions for formatting the
following input types, where the second arg is always the format - and
they do take different format strings for different types (i.e. we dont
convert int or double to timestamp and then format that)
to_char | bigint, text
to_char | double precision, text
to_char | integer, text
to_char | interval, text
to_char | numeric, text
to_char | real, text
to_char | timestamp with time zone, text
to_char | timestamp without time zone, text
if our current implementation just converts interval to date it is
surely wrong, at least because the year will be which does not
exist (AFAIK, the year before 0001 was -0001)
hannu=# select to_char('33s 15h 10m 5months'::interval, '.MM.DD
HH24:MI:SS');
to_char
-
.05.00 15:10:33
(1 row)
IMHO there should be INTERVAL-specific format characters - calling
5-month period "a May" is stupid (calling 1-month period "a January" is
even stupider :)
If folks want to convert interval to datetime they can always do it by
adding an interval to some base date - doing it automatically by adding
it to non-existing base date 000-00-00 will confuse people
and it is not supported in "plain" postgresql
hannu=# select ('33s 15h 10m 5months'::interval::timestamp);
ERROR: Cannot cast type 'interval' to 'timestamp with time zone'
> TODO?
having strictly defined to_interval would be nice, but I think this
would be _another_ todo :)
Hannu
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [SQL] Efficient DELETE Strategies
On Mon, 2002-06-10 at 15:56, Tom Lane wrote: > Christoph Haller <[EMAIL PROTECTED]> writes: > > Based on an entry in the mailing list from 30 Oct 2001 > > about efficient deletes on subqueries, > > I've found two ways to do so (PostgreSQL 7.2.1): > > ... > > Is there a way to put the second form (more complicated, but faster) > > in one statement? > > Or is there even a third way to delete, which I cannot see? ... > AFAIK this extension would be utterly trivial to implement, since all > the machinery is there already --- for 99% of the backend, it doesn't > matter whether a FROM-item is implicit or explicit. We'd only need to > argue out what the syntax should be. I could imagine > > DELETE FROM relation_expr [ , table_ref [ , ... ] ] > [ WHERE bool_expr ] > > or > > DELETE FROM relation_expr [ FROM table_ref [ , ... ] ] > [ WHERE bool_expr ] What about DELETE relation_expr FROM relation_expr [ , table_ref [ , ... ] ] [ WHERE bool_expr ] or DELETE relation_expr.* FROM relation_expr [ , table_ref [ , ... ] ] [ WHERE bool_expr ] -- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] Efficient DELETE Strategies
Tom Lane wrote: > Christoph Haller <[EMAIL PROTECTED]> writes: > > DELETE FROM relation_expr [ FROM table_ref [ , ... ] ] > [ WHERE bool_expr ] > > The two FROMs in the second form look a little weird, but they help to > make a clear separation between the deletion target table and the > merely-referenced tables. Also, the first one might look to people > like they'd be allowed to write > > DELETE FROM foo FULL JOIN bar ... > > which is not any part of my intention (it's very unclear what it'd > mean for the target table to be on the nullable side of an outer join). > OTOH there'd be no harm in outer joins in a separate from-clause, eg > > DELETE FROM foo FROM (bar FULL JOIN baz ON ...) WHERE ... > > Actually, either syntax above would support that; I guess what's really > bothering me about the first syntax is that a comma suggests a list of > things that will all be treated similarly, while in reality the first > item will be treated much differently from the rest. Interesting. We could allow an alias on the primary table: DELETE FROM foo f WHERE and allow the non-alias version of the table for the join. Of course, that doesn't allow "FULL JOIN" and stuff like that. The FROM ... FROM looks weird, and there is clearly confusion over the FROM t1, t2. I wish there was another option. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Project scheduling issues (was Re: Per tuple overhead,
Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > I *really* wish ppl would stop harping on the length of the last beta > > cycle ... I will always rather delay a release due to an *known* > > outstanding bug, especially one that just needs a little bit more time to > > work out, then to release software "on time" ala Microsoft ... > > I don't think that's at issue here. No one was suggesting that we'd > force an *end* to beta cycle because of schedule issues. We ship when > we're satisfied and not before. I'm saying that I want to try to > *start* the beta test period on-time, rather than letting the > almost-beta state drag on for months --- which we did in each of the > last two cycles. Development time is productive, and beta-test time > is productive, but we're-trying-to-start-beta time is not very > productive ... Yes, this was exactly my point. By slowing down in August, we enter that "almost beta" period where there is uncertainty over what should be worked on. I know myself I am uncertain what is appropriate to work on, so I usually end up doing nothing, which is a waste. I think the only message should be "finish before the end of August". People can understand that, and it is under the control of the contributor. The message "no big patches in August" is too imprecise and leads to uncertainty. Of course, if we don't finish by the end of August, our new message may be "finish before the end of September". This brings up another point. We have delayed beta to wait for single patches in the past, usually a week at a time. When that week drags to two, and then four, we have lost development time. If we had just said "four weeks" from the start, people could have continued development, knowing they had a month, but our one-week-at-a-time strategy basically holds up the whole group waiting for single developer to finish a patch. What I am suggesting is that our small delays for beta are hurting us _if_ the delay drags longer than anticipated, and we keep pushing back the deadline. In such cases, we would be better just choosing a longer deadline from the start. Perhaps we should have delays that are a month at a time. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PostGres Doubt
Hi , I am extremely new to PostGreSql. If any one can please answer this question of mine. I want to insert/update records into the postgres database through C or perl code. The only condition is that it should be efficient. Can anybody tell me the difference between ecpg and libpq and which one should I work on for solving my problem. Thanks in advance. Vikas. _ Click below to visit monsterindia.com and review jobs in India or Abroad http://monsterindia.rediff.com/jobs ---(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: [HACKERS] Project scheduling issues (was Re: Per tuple overhead,
Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > Agreed on all accounts ... which is why this time, I want to do a proper > > branch when beta starts ... hell, from what I've seen suggested here so > > far, we have no choice ... At least then we can 'rip out' something from > > the beta tree without having to remove and re-add it to the development > > one later, hoping that they're changes haven't been affected by someone > > else's ... > > Well, let's give that a try and see how it goes. I'm a bit worried > about the amount of double-patching we'll have to do, but other projects > seem to manage to cope with multiple active branches... Yes, Marc has been advocating this, and perhaps it is time to give it a try. There are some downsides: o All committers need to know that they have to double-patch o We might have developers working on new features rather than focusing on beta testing/fixing. One interesting idea would be to create a branch for 7.4, and apply _only_ 7.4 patches to that branch. Then, when we release 7.3, we merge that branch back into the main CVS tree. That would eliminate double-patching _and_ give people a place to commit 7.4 changes. I don't think the merge would be too difficult because 7.3 will not change significantly during beta. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostGres Doubt
> -Original Message- > From: vikas p verma [mailto:[EMAIL PROTECTED]] > Sent: Monday, June 10, 2002 1:10 PM > To: [EMAIL PROTECTED] > Subject: [HACKERS] PostGres Doubt > > > Hi , > > I am extremely new to PostGreSql. If any one can please answer > this question of mine. I want to insert/update records into the > postgres database through C or perl code. The only condition is > that it should be efficient. Can anybody tell me the difference > between ecpg and libpq and which one should I work on for solving > my problem. > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ECPG is single threading. Hence, tools written in ECPG are a pain in the neck if you want multiple threads of execution. I recommend against using it for any purpose except porting a single threading project that already uses embedded SQL. The embedded SQL interface for PostgreSQL is a disaster. The libpq functions are reentrant. These will be useful for just about any project. If you are populating empty tables, then use the bulk copy interface. It is orders of magnitude faster. If you are going to completely replace the data in a table, drop the table, create the table, and use the bulk copy interface. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Will postgress handle too big tables?
Hi, We are moving to Postgres from Oracle. We have a few tables that have around 8 to 10 millions of rows and their size increases very rapidly(deletions are very less on these tables). How will Postgres hanlde very big tables like this? or would it be very slow when compared to Oracle? Do you have any case studies in this regd? Also anyone know of any perticular documentation/links that talks specifically about "migrating to Postgres from Oracle"?, Please let me know if you have kind of document that would be of great use to us. Thanks Yuva Sr. Java Developer http://www.ebates.com mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Timestamp/Interval proposals: Part 2
Karel, Hannu,
To be perfectly honest, I was looking at my 7.1 documentation (courtesy
of DOSSIER) and hadn't realized that 7.2's implementation had got as
far as a function. I had tried to_char(interval) on 7.2.1, received
what looked like gibberish in return, and assumed that it was
unimplemented.
> if there were a to_interval() then it should convert char data to
> interval, like to_date(), to_number() and to_timestamp() do
Can we put THAT on the to-do list? I find it highly inconsistent that
the function for creating intervals is "interval". Currently, I deal
with it by creating my own to_interval function in template1.
> actually we currently have to_char(x,t) functions for formatting the
> following input types, where the second arg is always the format -
> and
> they do take different format strings for different types (i.e. we
> dont
> convert int or double to timestamp and then format that)
> IMHO there should be INTERVAL-specific format characters - calling
> 5-month period "a May" is stupid (calling 1-month period "a January"
> is
> even stupider :)
I wholeheartedly agree with Hannu, here. Might I suggest:
M# - Nummber of Months - abbr (Interval)
MM# - Number of Months (interval)
Y# - Number of years - abbr (Interval)
YY# - Number of years (Interval)
D# - Number of Days (interval)
W# - Number of weeks -abbr (interval)
WW# - number of weeks (interval)
HH# - Number of hours (interval)
MI# - Number of minutes (interval)
SS# - Number of seconds (interval)
Thus allowing:
hannu=# select to_char('33s 15h 10m 5months'::interval, 'M# D# HH# MI#
SS#');
to_char
-
5 mon 0 days 15 hrs 10 min 33 sec
or:
hannu=# select to_char('33s 15h 10m 5months'::interval, 'MM# D# HH# MI#
SS#');
to_char
-
5 months 0 days 15 hrs 10 min 33 sec
This needs more polishing, of course, but you can see where I'm going
with it.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] Efficient DELETE Strategies
Hannu Krosing wrote: > What about > > DELETE relation_expr FROM relation_expr [ , table_ref [ , ... ] ] > [ WHERE bool_expr ] > > or > > DELETE relation_expr.* FROM relation_expr [ , table_ref [ , ... ] ] > [ WHERE bool_expr ] So make the initial FROM optional and allow the later FROM to be a list of relations? Seems kind of strange. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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: [HACKERS] Project scheduling issues (was Re: Per tuple overhead,
On Mon, 10 Jun 2002, Bruce Momjian wrote: > Tom Lane wrote: > > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > > Agreed on all accounts ... which is why this time, I want to do a proper > > > branch when beta starts ... hell, from what I've seen suggested here so > > > far, we have no choice ... At least then we can 'rip out' something from > > > the beta tree without having to remove and re-add it to the development > > > one later, hoping that they're changes haven't been affected by someone > > > else's ... > > > > Well, let's give that a try and see how it goes. I'm a bit worried > > about the amount of double-patching we'll have to do, but other projects > > seem to manage to cope with multiple active branches... > > Yes, Marc has been advocating this, and perhaps it is time to give it a > try. There are some downsides: > > o All committers need to know that they have to double-patch *Wrong* .. only if its a fix for a problem with -STABLE .. otherwise it *just* goes in the development tree ... > o We might have developers working on new features rather than > focusing on beta testing/fixing. Its not the developers responsibility to beta test the software, its is their responsibility to test patches as they are applied during the 'development cycle' ... and even after we've branched in the past, ppl have "fixed reported bugs" and applied such fixes to the -STABLE branch ... why would that be any different now? All we're doing is letting developers work on their projects instead of sitting on their hands waiting for a bug report ... *Plus* ... good chance that any bugs that are reports are in the -DEV branch also, so it has to be fixed regardless ... > One interesting idea would be to create a branch for 7.4, and apply > _only_ 7.4 patches to that branch. Then, when we release 7.3, we merge > that branch back into the main CVS tree. That would eliminate > double-patching _and_ give people a place to commit 7.4 changes. I > don't think the merge would be too difficult because 7.3 will not change > significantly during beta. Four words: when hell freezes over Why must you overcomplicate a process most *large* projects seem to find so simple to deal with? God, what you are proposing above requires ppl to predict what v7.3 is going to look like when its finished, so that their work on v7.4 can follow? Bruce, I think this whole thread has just about dried up now ... when v7.3 goes beta, we will branch just like other large projects do so that we don't hold up any developers until we release the software, which, based on past experiences and history, will end up being delayed ... hell, just think, we branch on the 1st of Sept, release on the 15 of October (lets say one month for beta plus a bit of delay), and are ready to go with the next beta around the 1st of January since we did't lose that 1.5mo of development time ... wow, imagine a *solid* 4 month development cycle before beta? :) Based on everything I've heard/seen in this thread, we seem to be looking at: 1. Branch on Sept 1st, regardless of almost anything 2. Once Branch created, any *partially implemented* features will get rip'd out of the -STABLE branch and only fixes to the existing, fully implement features will go in 3. Beta1 released once developers comfortable with the state of the code Now, *if*, the week before the Branch, someone submits a bit patch that in *anyway* concerns someone to apply, we can hold it off for a week and put it into the -DEV branch so that its not shelved for a couple of months, and possibly going out of date ... but that would be a judgement call at the time, nothing set in stone ... The only thing we are really "setting in stone" here is when we are branching/freezing the code for release ... ---(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: [HACKERS] Project scheduling issues (was Re: Per tuple overhead,
Marc G. Fournier wrote: > On Mon, 10 Jun 2002, Bruce Momjian wrote: > > > Tom Lane wrote: > > > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > > > Agreed on all accounts ... which is why this time, I want to do a proper > > > > branch when beta starts ... hell, from what I've seen suggested here so > > > > far, we have no choice ... At least then we can 'rip out' something from > > > > the beta tree without having to remove and re-add it to the development > > > > one later, hoping that they're changes haven't been affected by someone > > > > else's ... > > > > > > Well, let's give that a try and see how it goes. I'm a bit worried > > > about the amount of double-patching we'll have to do, but other projects > > > seem to manage to cope with multiple active branches... > > > > Yes, Marc has been advocating this, and perhaps it is time to give it a > > try. There are some downsides: > > > > o All committers need to know that they have to double-patch > > *Wrong* .. only if its a fix for a problem with -STABLE .. otherwise it > *just* goes in the development tree ... Not totally wrong. I predict >90% of patches during those first two weeks will have to be double-applied. Do you disagree? Remember, we used to branch earlier and double-apply, and it did get confusing when people forgot to double-patch. I am not saying it is impossible, but I don't want to minimize it either. > > o We might have developers working on new features rather than > > focusing on beta testing/fixing. > > Its not the developers responsibility to beta test the software, its is > their responsibility to test patches as they are applied during the > 'development cycle' ... and even after we've branched in the past, ppl > have "fixed reported bugs" and applied such fixes to the -STABLE branch > ... why would that be any different now? All we're doing is letting > developers work on their projects instead of sitting on their hands > waiting for a bug report ... Yes, good point. Developers are not testing. However, when we do need people to track down bugs and fixes, I hope they aren't too busy working on new features to help us. > *Plus* ... good chance that any bugs that are reports are in the -DEV > branch also, so it has to be fixed regardless ... > > > One interesting idea would be to create a branch for 7.4, and apply > > _only_ 7.4 patches to that branch. Then, when we release 7.3, we merge > > that branch back into the main CVS tree. That would eliminate > > double-patching _and_ give people a place to commit 7.4 changes. I > > don't think the merge would be too difficult because 7.3 will not change > > significantly during beta. > > Four words: when hell freezes over > > Why must you overcomplicate a process most *large* projects seem to find > so simple to deal with? God, what you are proposing above requires > ppl to predict what v7.3 is going to look like when its finished, so that > their work on v7.4 can follow? Only bug fixes are going into 7.3 during beta, so how much is it going to change? And I have done the double-patching, so I remember the problems. Aside from the hassle of doing everything twice, as development drifts from beta, the patches do become harder to apply. > Bruce, I think this whole thread has just about dried up now ... when v7.3 > goes beta, we will branch just like other large projects do so that we > don't hold up any developers until we release the software, which, based > on past experiences and history, will end up being delayed ... hell, just > think, we branch on the 1st of Sept, release on the 15 of October (lets > say one month for beta plus a bit of delay), and are ready to go with the > next beta around the 1st of January since we did't lose that 1.5mo of > development time ... wow, imagine a *solid* 4 month development cycle > before beta? :) Yes, it will be good. > Based on everything I've heard/seen in this thread, we seem to be looking > at: > > 1. Branch on Sept 1st, regardless of almost anything > > 2. Once Branch created, any *partially implemented* features will get >rip'd out of the -STABLE branch and only fixes to the existing, fully >implement features will go in Now, that is an interesting idea. > 3. Beta1 released once developers comfortable with the state of the code > > Now, *if*, the week before the Branch, someone submits a bit patch that in > *anyway* concerns someone to apply, we can hold it off for a week and put > it into the -DEV branch so that its not shelved for a couple of months, > and possibly going out of date ... but that would be a judgement call at > the time, nothing set in stone ... > > The only thing we are really "setting in stone" here is when we are > branching/freezing the code for release ... OK. I am making these points because the previous betas have been very disorganized, with lots of wasted time. I don't want it to happen again. We can't say we don't understand the issues. It has
Re: [HACKERS] Project scheduling issues (was Re: Per tuple overhead,
On Monday 10 June 2002 02:46 pm, Marc G. Fournier wrote: > Based on everything I've heard/seen in this thread, we seem to be looking > at: > 1. Branch on Sept 1st, regardless of almost anything > 2. Once Branch created, any *partially implemented* features will get >rip'd out of the -STABLE branch and only fixes to the existing, fully >implement features will go in > 3. Beta1 released once developers comfortable with the state of the code > Now, *if*, the week before the Branch, someone submits a bit patch that in > *anyway* concerns someone to apply, we can hold it off for a week and put > it into the -DEV branch so that its not shelved for a couple of months, > and possibly going out of date ... but that would be a judgement call at > the time, nothing set in stone ... > The only thing we are really "setting in stone" here is when we are > branching/freezing the code for release ... This seems to me to be reasonable. My only question would be 'why haven't we always done it this way' but that isn't terribly productive. I actually know the answer to my question, in fact, but that's not relevant to the future. Many large projects do this, in some form or another. FreeBSD, Debian, even the Linux kernel all follow this basic form. Historically we've concentrated our development efforts during beta to 'fixing beta problems only' -- but that model produces these extraordinarily long cycles, IMHO. In the meantime people are literally chomping at the bit to do a new feature -- to the point that one developer got rather upset that his patch wasn't being looked at and 'stomped off' in a huff. All because we were in beta-only mode. However, I do think at that point we need to look at what the patch manager (historically Bruce) can deal with realistically. Is it a job for two patch managers, one for the STABLE and one for the DEV? Only Bruce can answer whether he can realistically handle it (I personally have confidence he can). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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: [HACKERS] [SQL] Efficient DELETE Strategies
On Mon, 10 Jun 2002 09:56:27 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >Does anyone know whether other systems that support the UPDATE extension >for multiple tables also support a DELETE extension for multiple tables? >If so, what's their syntax? MSSQL seems to guess what the user wants. All the following statements do the same: (0) DELETE FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.i=t2.i) (1) DELETE t1 FROM t2 WHERE t1.i=t2.i (2a) DELETE t1 FROM t2, t1 WHERE t1.i=t2.i (2b) DELETE t1 FROM t2 INNER JOIN t1 ON t1.i=t2.i (3a) DELETE t1 FROM t2, t1 a WHERE a.i=t2.i (3b) DELETE t1 FROM t2 INNER JOIN t1 a ON a.i=t2.i (4a) DELETE a FROM t2, t1 a WHERE a.i=t2.i (4b) DELETE a FROM t2 INNER JOIN t1 a ON a.i=t2.i (5) DELETE t1 FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i) (6) DELETE a FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i) (0) is standard SQL and should always work. As an extension I'd like (1) or (2), but only one of them and forbid the other one. I'd also forbid (3), don't know what to think of (4), and don't see a reason why we would want (5) or (6). I'd rather have (7) or (8). These don't work: (7) DELETE t1 a FROM t2 WHERE a.i = t2.i "Incorrect syntax near 'a'." (8) DELETE FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i = t2.i) "Incorrect syntax near 'a'." Self joins: (2as) DELETE t1 FROM t1, t1 b WHERE 2*b.i=t1.i (4as) DELETE a FROM t1 a, t1 b WHERE 2*b.i=a.i (4bs) DELETE a FROM t1 a INNER JOIN t1 b on 2*b.i=a.i These don't work: DELETE t1 FROM t1 b WHERE 2 * b.i = t1.i "The column prefix 't1' does not match with a table name or alias name used in the query." DELETE t1 FROM t1 a, t1 b WHERE 2 * b.i = a.i "The table 't1' is ambiguous." And as if there aren't enough ways yet, I just discovered that (1) to (6) just as much work with "DELETE FROM" where I wrote "DELETE" ... Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [SQL] Efficient DELETE Strategies
Bruce Momjian <[EMAIL PROTECTED]> writes: > Hannu Krosing wrote: >> What about >> >> DELETE relation_expr FROM relation_expr [ , table_ref [ , ... ] ] >> [ WHERE bool_expr ] >> >> or >> >> DELETE relation_expr.* FROM relation_expr [ , table_ref [ , ... ] ] >> [ WHERE bool_expr ] > So make the initial FROM optional and allow the later FROM to be a list > of relations? Seems kind of strange. No, I think he's suggesting that one be able to pick out any element of the FROM-list and say that that is the deletion target. I really don't want to get into that (unless there is precedent in Oracle or someplace); it seems way too confusing to me. It would also force us to do error checking to eliminate cases that ought to just be syntactically impossible: target table not present, target is a join or subselect instead of a table, target is on wrong side of an outer join, etc. [ and in another message ] > The FROM ... FROM looks weird, and there is clearly confusion over the > FROM t1, t2. I wish there was another option. The only other thing that's come to mind is to use a different keyword (ie, not FROM) for the list of auxiliary relations. WITH might work from a simple readability point of view: DELETE FROM target WITH other-tables WHERE ... But we've already got FROM as the equivalent construct in UPDATE, so it seems weird to use something else in DELETE. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Project scheduling issues (was Re: Per tuple overhead,
Lamar Owen <[EMAIL PROTECTED]> writes: > Historically we've concentrated our development efforts during beta to > 'fixing beta problems only' -- but that model produces these > extraordinarily long cycles, IMHO. In the meantime people are > literally chomping at the bit to do a new feature -- to the point that > one developer got rather upset that his patch wasn't being looked at > and 'stomped off' in a huff. All because we were in beta-only mode. There is a downside to changing away from that approach. Bruce mentioned it but didn't really give it the prominence I think it deserves: beta mode encourages developers to work on testing, debugging, and oh yes documenting. Without that forced "non development" time, some folks will just never get around to the mop-up stages of their projects; they'll be off in new-feature-land all the time. I won't name names, but there are more than a couple around here ;-) I think our develop mode/beta mode pattern has done a great deal to contribute to the stability of our releases. If we go over to the same approach that everyone else uses, you can bet your last dollar that our releases will be no better than everyone else's. How many people here run dot-zero releases of the Linux kernel, or gcc? Anyone find them trustworthy? Anyone really eager to have to maintain old releases for several years, because no sane DBA will touch the latest release? I'm not trying to sound like Cassandra, but we've done very very well with only limited resources over the past several years. We should not be too eager to mess with a proven-successful approach. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [SQL] Efficient DELETE Strategies
Manfred Koizar <[EMAIL PROTECTED]> writes: >> If so, what's their syntax? > MSSQL seems to guess what the user wants. Gack. Nothing like treating mindless syntax variations as a "feature" list... > All the following statements do the same: > (1) DELETE t1 FROM t2 WHERE t1.i=t2.i > (2a) DELETE t1 FROM t2, t1 WHERE t1.i=t2.i > (5) DELETE t1 FROM t1 a > WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i) > (6) DELETE a FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i) So in other words, MSSQL has no idea whether the name following DELETE is a real table name or an alias, and it's also unclear whether the name appears in the separate FROM clause or generates a FROM-item all by itself. This is why they have to punt on these cases: > These don't work: > DELETE t1 FROM t1 b WHERE 2 * b.i = t1.i > "The column prefix 't1' does not match with a table name or alias name > used in the query." > DELETE t1 FROM t1 a, t1 b WHERE 2 * b.i = a.i > "The table 't1' is ambiguous." The ambiguity is entirely self-inflicted... > And as if there aren't enough ways yet, I just discovered that (1) to > (6) just as much work with "DELETE FROM" where I wrote "DELETE" ... Hm. So (1) with the DELETE FROM corresponds exactly to what I was suggesting: DELETE FROM t1 FROM t2 WHERE t1.i=t2.i except that I'd also allow an alias in there: DELETE FROM t1 a FROM t2 b WHERE a.i=b.i Given the plethora of mutually incompatible interpretations that MSSQL evidently supports, though, I fear we can't use it as precedent for making any choices :-(. Can anyone check out other systems? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Default privileges for new databases (was Re: [HACKERS] Can't import large objects in most recent cvs)
Tom, > Probably we should have temp table creation allowed to all by default. > I'm not convinced that that's a good idea for schema-creation privilege > though. Related issues: what should initdb set as the permissions for > template1? Would it make sense for newly created databases to copy > their permission settings from the template database? (Probably not, > since the owner is likely to be different.) What about copying those > per-database config settings Peter just invented? Yes. I think there should be a not optional INITDB switch: either --secure or --permissive. People usually know at the time of installation whether they're building a web server (secure) or a home workstation (permissive). Depending on the setting, this should set either a grant all or revoke all for non-db owners as default, including such things as temp table creation. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(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: [HACKERS] [SQL] Efficient DELETE Strategies
Tom, > >> If so, what's their syntax? > > > MSSQL seems to guess what the user wants. > > Gack. Nothing like treating mindless syntax variations as a "feature" > list... I vote that we stick to a strick SQL92 interpretation, here. 1) It's standard 2) Strict syntax on DELETE statements is better. Personally, I would *not* want the database to "guess what I want" in a delete statement; it might guess wrong and there go my records ... Heck, one of the things I need to research how to turn off in PostgreSQL is the "Add missing FROM-clause" feature, which has tripped me up many times. -- -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Will postgress handle too big tables?
Yuva, > Also anyone know of any perticular documentation/links that talks > specifically about "migrating to Postgres from Oracle"?, Please let me know > if you have kind of document that would be of great use to us. Please see Techdocs ( http://techdocs.postgresql.org/ ) for performance whitepapers and Oracle migration tips. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [SQL] Efficient DELETE Strategies
> -Original Message- > From: Josh Berkus [mailto:[EMAIL PROTECTED]] > Sent: Monday, June 10, 2002 3:42 PM > To: Tom Lane; Manfred Koizar > Cc: Christoph Haller; [EMAIL PROTECTED]; > [EMAIL PROTECTED] > Subject: Re: [HACKERS] [SQL] Efficient DELETE Strategies > > Tom, > > > >> If so, what's their syntax? > > > > > MSSQL seems to guess what the user wants. > > > > Gack. Nothing like treating mindless syntax variations as > a "feature" > > list... > > I vote that we stick to a strick SQL92 interpretation, here. > 1) It's standard > 2) Strict syntax on DELETE statements is better. > > Personally, I would *not* want the database to "guess what I > want" in a delete > statement; it might guess wrong and there go my records ... > > Heck, one of the things I need to research how to turn off in > PostgreSQL is > the "Add missing FROM-clause" feature, which has tripped me > up many times. Agree strongly. I would be very annoyed at any database system that guesses about what I might want. It might guess wrong and cause enormous damage. It does not have to be an update or delete for this damage to occur. It could be a report that financial decisions were based upon. If someone does get the PostgreSQL group to alter incoming statements, surely this deserves *AT LEAST* a powerful warning message. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostGres Doubt
Is libpq/PQconnectdb() reentrant? I've tried repeatedly over time and it seems to incur segfaults every single time. -d Dann Corbit wrote: >The libpq functions are reentrant. These will be useful for just about >any project. > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Project scheduling issues (was Re: Per tuple overhead,
On Monday 10 June 2002 04:11 pm, Tom Lane wrote: > Lamar Owen <[EMAIL PROTECTED]> writes: > > Historically we've concentrated our development efforts during beta to > > 'fixing beta problems only' > There is a downside to changing away from that approach. There are downsides to every approach. The question is 'Which set of downsides are we most comfortable with?' > Bruce > mentioned it but didn't really give it the prominence I think it > deserves: beta mode encourages developers to work on testing, debugging, > and oh yes documenting. Without that forced "non development" time, > some folks will just never get around to the mop-up stages of their > projects; they'll be off in new-feature-land all the time. I won't name > names, but there are more than a couple around here ;-) Well, this is the one downside the Marc's proposal. It boils down to self-discipline, though. Unfortunately not everyone is as disciplined as you seem to be in the area, Tom. I certainly cannot claim a great deal of self-discipline. BTW, that is meant as a compliment to you, Tom. > I think our develop mode/beta mode pattern has done a great deal to > contribute to the stability of our releases. If we go over to the same > approach that everyone else uses, you can bet your last dollar that our > releases will be no better than everyone else's. I'll have to agree here -- but I also must remind people that our 'dot zero' releases are typically solid, but our 'dot one' releases have not been so solid. So I wouldn't be too confident in our existing model. And I'm not so sure the model is the producer of our sterling record heretofore. I'm more of the mindset that the quality and discipline of the developers is the real reason. > How many people here > run dot-zero releases of the Linux kernel, or gcc? Anyone find them > trustworthy? Anyone really eager to have to maintain old releases for > several years, because no sane DBA will touch the latest release? We already have some of that problem due to the difficulty in upgrading. People wait and see if the features warrant the downtime and pain of upgrading. Meantime they live with security holes and bugs in our own unmaintained older releases. And dump and restore upgrades are not painless. I will admit that I've not used pg_upgrade in some time -- I understand moving from 7.1 to 7.2 is much less painful using pg_upgrade. However, pg_upgrade was released in contrib as being a 'handle with great care' utility that no sane DBA is going to touch Catch 22. So, I don't necessarily agree that we should hold up our development model as the panacea, and I'm not thoroughly convinced that the quality of our releases is related directly to the development model. I believe it is directly related to the caliber of the developers. That said, good developers can produce good quality regardless of the model used if they will discipline themselves accordingly. > I'm not trying to sound like Cassandra, but we've done very very well > with only limited resources over the past several years. We should not > be too eager to mess with a proven-successful approach. Interesting reference Why not try this one cycle and see what happens? No one is going to force anyone else to develop new features when they want to fix bugs. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] Efficient DELETE Strategies
This Hannu Krosing wrote: > DELETE relation_expr FROM relation_expr [ , table_ref [ , ... ] ] > [ WHERE bool_expr ] This in some ways is similar to Oracle where the FROM is optional in a DELETE (ie. DELETE foo WHERE ...). By omitting the first FROM, the syntax ends up mirroring the UPDATE case: DELETE foo FROM bar WHERE ... UPDATE foo FROM bar WHERE ... However I think the syntax should also support the first FROM as being optional (even though it looks confusing): DELETE FROM foo FROM bar WHERE ... thanks, --Barry ---(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: [HACKERS] [SQL] Efficient DELETE Strategies
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Hannu Krosing wrote: > >> What about > >> > >> DELETE relation_expr FROM relation_expr [ , table_ref [ , ... ] ] > >> [ WHERE bool_expr ] > >> > >> or > >> > >> DELETE relation_expr.* FROM relation_expr [ , table_ref [ , ... ] ] > >> [ WHERE bool_expr ] > > > So make the initial FROM optional and allow the later FROM to be a list > > of relations? Seems kind of strange. > > No, I think he's suggesting that one be able to pick out any element of > the FROM-list and say that that is the deletion target. I really don't > want to get into that (unless there is precedent in Oracle or > someplace); it seems way too confusing to me. It would also force us to > do error checking to eliminate cases that ought to just be syntactically > impossible: target table not present, target is a join or subselect > instead of a table, target is on wrong side of an outer join, etc. Yuck. > [ and in another message ] > > The FROM ... FROM looks weird, and there is clearly confusion over the > > FROM t1, t2. I wish there was another option. > > The only other thing that's come to mind is to use a different keyword > (ie, not FROM) for the list of auxiliary relations. WITH might work > from a simple readability point of view: > DELETE FROM target WITH other-tables WHERE ... > But we've already got FROM as the equivalent construct in UPDATE, so it > seems weird to use something else in DELETE. Yes, another keyword is the only solution. Having FROM after DELETE mean something different from FROM after a tablename is just too weird. I know UPDATE uses FROM, and it is logical to use it here, but it is just too wierd when DELETE already has a FROM. Should we allow FROM and add WITH to UPDATE as well, and document WITH but support FROM too? No idea. What if we support ADD FROM as the keywords for the new clause? Clearly this is a TODO item. I will document it when we decide on a direction. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] PG Index<->seqscan problems...
OK I know this has been long delayed but I've finished some work on the above. The coster is actually doing a fairly good job. I only recieved one submission from someone with data that replicated the problem, and was myself hard pressed to replicate the situation. It's more-or-less a fencepost error. I don't have the expertise to figure out how to make the coster more determinate in these types of situations. However as some suggested the practice of storing actual run data from query plans (esp. when using precompiled and/or stored queries) would probably help eliminate these byt adding another weight factor (IE last time we did this it took X amount of time, and we estimated Y, so lets try it this way instead). Unfortunately I'm a bit too pressed for time looking for a job to continue pursuing this research any further. Michael Loftis ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [SQL] Efficient DELETE Strategies
> Given the plethora of mutually incompatible interpretations that MSSQL > evidently supports, though, I fear we can't use it as precedent for > making any choices :-(. > > Can anyone check out other systems? MySQL: 6.4.6 DELETE Syntax DELETE [LOW_PRIORITY | QUICK] FROM table_name [WHERE where_definition] [ORDER BY ...] [LIMIT rows] or DELETE [LOW_PRIORITY | QUICK] table_name[.*] [,table_name[.*] ...] FROM table-references [WHERE where_definition] or DELETE [LOW_PRIORITY | QUICK] FROM table_name[.*], [table_name[.*] ...] USING table-references [WHERE where_definition] DELETE deletes rows from table_name that satisfy the condition given by where_definition, and returns the number of records deleted. If you issue a DELETE with no WHERE clause, all rows are deleted. If you do this in AUTOCOMMIT mode, this works as TRUNCATE. See section 6.4.7 TRUNCATE Syntax. In MySQL 3.23, DELETE without a WHERE clause will return zero as the number of affected records. If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form: mysql> DELETE FROM table_name WHERE 1>0; Note that this is much slower than DELETE FROM table_name with no WHERE clause, because it deletes rows one at a time. If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table. If you specify the word QUICK then the table handler will not merge index leaves during delete, which may speed up certain kind of deletes. In MyISAM tables, deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganise tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See section 4.5.1 OPTIMIZE TABLE Syntax and section 4.4.6.10 Table Optimisation. The first multi-table delete format is supported starting from MySQL 4.0.0. The second multi-table delete format is supported starting from MySQL 4.0.2. The idea is that only matching rows from the tables listed before the FROM or before the USING clause are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching. The .* after the table names is there just to be compatible with Access: DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id or DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id In the above case we delete matching rows just from tables t1 and t2. ORDER BY and using multiple tables in the DELETE statement is supported in MySQL 4.0. If an ORDER BY clause is used, the rows will be deleted in that order. This is really only useful in conjunction with LIMIT. For example: DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp LIMIT 1 This will delete the oldest entry (by timestamp) where the row matches the WHERE clause. The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Will postgress handle too big tables?
On Mon, 10 Jun 2002, Yuva Chandolu wrote: > We are moving to Postgres from Oracle. We have a few tables that have around > 8 to 10 millions of rows and their size increases very rapidly(deletions are > very less on these tables). How will Postgres hanlde very big tables like > this? Uh..."what big tables?" :-) Have a look back through the archives. I'm mucking about quite happily with 500 million row tables, without much difficulty. I've found that my main barrier is disk I/O. If you're doing it on a little dual-IDE disk system as I am, things just ain't so fast. I'm hoping that in the next couple of weeks I get the go-ahead to put together a system with ten or so disks (based around a 3ware Escalade IDE RAID controller) that will make trillion-row-tables quite practical. > or would it be very slow when compared to Oracle? Do you have any case > studies in this regd? It all depends entirely on the application. Really. Some applications will work just as well on Postgres as they will on Oracle; others will be almost impossible with Postgres. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
