Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Mon, Jun 10, 2002 at 07:18:44PM +0200, Hannu Krosing wrote:
OK, I add to_interval() to may TODO (but it's unsure for 7.3).
> hannu=# select to_char('33s 15h 10m 5months'::interval, '.MM.DD
> HH24:MI:SS');
>to_char
> -
> .05.00 15:10:33
> (1 row)
I think, we can keep this behaviour for to_char(), the good thing
is that you can formatting interval to strings that seems like
standard time (15:10:33), etc.
The to_interval() will have another (you wanted) behaviour.
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] PostGres Doubt
Are you using crypt on the connection? Unfortunately, crypt is not reentrant. > -Original Message- > From: David Ford [mailto:[EMAIL PROTECTED]] > Sent: Monday, June 10, 2002 6:16 PM > To: Dann Corbit > Cc: vikas p verma; [EMAIL PROTECTED] > Subject: 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] PostgreSQL OLE DB Provider
Do you know any attempts to write native OLE DB provider for PostgreSQL (it would give broader support for VS Net). I would like to write such provider and I want to know if sombody tried it before. Could somebody help me with protocol issues (I have read Backend/Frontend Protocol and studied ODBC driver) Are there any other interesting issues which aren not covered with it. I would like to know how could I implement precompiled statements. Is there any way to send it without parameters to able backend to chache it for future use or it is not necessary. Are there any problems with large objects ? ---(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 Tue, 2002-06-11 at 09:34, Karel Zak wrote:
> On Mon, Jun 10, 2002 at 07:18:44PM +0200, Hannu Krosing wrote:
>
> OK, I add to_interval() to may TODO (but it's unsure for 7.3).
>
> > hannu=# select to_char('33s 15h 10m 5months'::interval, '.MM.DD
> > HH24:MI:SS');
> >to_char
> > -
> > .05.00 15:10:33
> > (1 row)
I have not checked the SQL9x standards, but it seems from reading the
following links that Interval in Oracle and MimerSQL is actually 2
distinct types (YEAR-MONTH interval and DAY-HOUR-MINUTE-SECOND interval)
which can't be mixed (it is impossible to know if 1 "month" is 28, 29,
30 or 31 days
http://otn.oracle.com/products/rdb7/htdocs/y2000.htm
http://developer.mimer.com/documentation/Mimer_SQL_Reference_Manual/Syntax_Rules4.html#1113356
> I think, we can keep this behaviour for to_char(), the good thing
> is that you can formatting interval to strings that seems like
> standard time (15:10:33), etc.
But interval _is_ _not_ point-in-time, it is a time_span_ .
It can be either good if it gives the results you want or bad if it does
give wrong results like returning 03:10:33 for the above
I would suggest that a separate to_char function would be written that
would be _specific_to_interval_ datatype - so wheb i do
to_char('33s 15h 10m'::interval, 'SS') I will get the actual length of
interval in seconds, 15*3600+10*60+33 = 54633s and not just the seconds part (33)
whereas to_char('33s 15h 10m'::interval, 'MI SS') would give
15*60+10=910 min 33 sec ('910 33')
-
Hannu
---(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 03:43:34PM +0200, Karel Zak wrote:
> On Mon, Jun 10, 2002 at 04:26:47PM +0200, Hannu Krosing wr ote:
> > 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.
And 'DD' is defined as in range 1..31...
What if I try to select '100 days'?
fduch=> SELECT to_char('100days'::interval, '-MM-DD HH24:MI:SS');
to_char
-
-00-10 00:00:00
Even more:
DDD is day of year, but
fduch=> SELECT to_char('100days'::interval, '-MM-DDD HH24:MI:SS');
to_char
--
-00-069 00:00:00
However, this works fine:
fduch=> SELECT extract(DAY from '100days'::interval);
date_part
---
100
fduch=> SELECT version();
version
-
PostgreSQL 7.2.1 on i386-portbld-freebsd4.6, compiled by GCC 2.95.3
I think, interval is too different from timestamp,
and to_char(interval) needs another format syntax and logics...
--
Fduch M. Pravking
---(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 Tue, 2002-06-11 at 04:53, Bruce Momjian wrote: > 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. I was inspired by MS Access syntax that has optional relation_expr.* : DELETE [relation_expr.*] FROM relation_expr WHERE criteria it does not allow any other tablerefs in from > Clearly this is a TODO item. I will document it when we decide on a > direction. Or then we can just stick with standard syntax and teach people to do DELETE FROM t1 where t1.id1 in (select id2 from t2 where t2.id2 = t1.id1) and perhaps even teach our optimizer to add the t2.id2 = t1.id1 part itself to make it fast AFAIK this should be exactly the same as the proposed DELETE FROM t1 FROM t2 WHERE t2.id2 = t1.id1 -- Hannu ---(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] Timestamp/Interval proposals: Part 2
On Tue, Jun 11, 2002 at 11:16:13AM +0200, Hannu Krosing wrote:
> On Tue, 2002-06-11 at 09:34, Karel Zak wrote:
> > I think, we can keep this behaviour for to_char(), the good thing
> > is that you can formatting interval to strings that seems like
> > standard time (15:10:33), etc.
>
> But interval _is_ _not_ point-in-time, it is a time_span_ .
>
> It can be either good if it gives the results you want or bad if it does
> give wrong results like returning 03:10:33 for the above
>
> I would suggest that a separate to_char function would be written that
> would be _specific_to_interval_ datatype - so wheb i do
>
> to_char('33s 15h 10m'::interval, 'SS') I will get the actual length of
>
> interval in seconds, 15*3600+10*60+33 = 54633s and not just the seconds part (33)
>
> whereas to_char('33s 15h 10m'::interval, 'MI SS') would give
>
> 15*60+10=910 min 33 sec ('910 33')
Well, If the to_char() for interval will output result that you want,
how can I output '15:10:33'?
For this I want two direffent function or anothers format marks for
to_char() like
to_char('33s 15h 10m'::interval, '#MI #SS');
---
'910 33'
but for "standard" marks (that now works like docs describe :-) will output
MI in 0..59 range.
to_char('33s 15h 10m'::interval, 'MI:SS');
---
'10:33'
IMHO it's acceptable. I don't want close the way for output formatting
in "standard" date/time ranges. We can support _both_ ways. Or not?
Thomas, you are quiet? :-)
Karel
PS. the PostgreSQL converting intervals to "standard" format too:
test=# select '33h 15m'::interval - '10h 2m 3s'::interval ;
?column?
--
23:12:57
(1 row)
test=# select '45h 15m'::interval - '10h 2m 3s'::interval ;
?column?
1 day 11:12:57
(hmm.. I unsure if this is really released 7.2, I maybe have
some pre-7.2 version now. Is this 7.2 behaviuor?)
--
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Project scheduling issues (was Re: Per tuple overhead,
On Mon, 10 Jun 2002, Tom Lane wrote: > 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 ;-) Well, in alot of ways we have control over this ... we have a very limited number of committers ... start requiring that any patches that come through, instead of "just being applied and worry about documentation later", require the documentation to be included at the same time ... would definitely save alot of headaches down the road chasing down that documentation ... I think we've actually done thta a few times in the past, where we've held up a patch waiting for the documentation, but its never been a real requirement, but I don't think its an unreasonable one ... > 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? Again, we do have alot of control over this ... the only ppl that we *really* have to worry about "not mopping up" their code are those with committers access ... everyone else has to go through us, which means that we can always "stale" a patch from a developer due to requirements for bug fixes ... ... but, quite honestly, have we ever truly had a problem with this even during development period? How many *large* OSS projects out there have? My experience(s) with FreeBSD, for an example, are that most developers take pride in their code ... if someone reports a bug, and its recreateable, its generally fixed quite quickly ... its only the "hard to recreate" bugs that take a long time to fix ... wasn't that just the case with us with the sequences bug? You yourself, if I recall, admitted that its always been there, but it obviously wasn't the easiest to re-create/trigger, else we would have had more ppl yelling about it ... once someone was able to narrow down the problem and how to re-create it consistently, it was fixed ... We've never really run "a tight ship" as far as code has gone ... Bruce has been known to helter-skelter apply patches, even a couple that I recall so obviously shouldn't have been that we beat him for it, but that has never prevented us (or even slowed us down) from having *solid* releases ... everyone that I've meet so far working on this project, IMHO, have been *passionate* about what they do ... and, in some way or another, *rely* on it being rock-solid ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Tue, Jun 11, 2002 at 12:37:09PM +0400, Fduch the Pravking wrote:
> And 'DD' is defined as in range 1..31...
> What if I try to select '100 days'?
>
> fduch=> SELECT to_char('100days'::interval, '-MM-DD HH24:MI:SS');
>to_char
> -
> -00-10 00:00:00
I already said it. The to_char() is 'tm' struct interpreter and use
standard internal PG routines for interval to 'tm' conversion. We can
talk about why 100days is converted to '10' days and months aren't
used. I agree this example seems strange. Thomas?
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 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 Mon, 10 Jun 2002, Bruce Momjian wrote: > > 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. Ya, I thought it was when you -and- Tom proposed it :) I quote from a message on June 8th: === Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > So, I we should: > > Warn people in July that beta is September 1 and all features > > have to be complete by then, or they get ripped out. > > I thought that was more or less the same thing I was proposing... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Tue, 2002-06-11 at 11:31, Karel Zak wrote:
> On Tue, Jun 11, 2002 at 12:37:09PM +0400, Fduch the Pravking wrote:
>
> > And 'DD' is defined as in range 1..31...
> > What if I try to select '100 days'?
> >
> > fduch=> SELECT to_char('100days'::interval, '-MM-DD HH24:MI:SS');
> >to_char
> > -
> > -00-10 00:00:00
>
> I already said it. The to_char() is 'tm' struct interpreter and use
> standard internal PG routines for interval to 'tm' conversion.
The point is it should _not_ do that for interval.
It does not convert to 'tm' for other types:
hannu=# select to_char(3.1415927,'0009D9');
to_char
-
0003.1
(1 row)
also, afaik there is no conversion of interval to datetime in
postgresql:
hannu=# select '25mon37d1s'::interval::timestamp;
ERROR: Cannot cast type 'interval' to 'timestamp with time zone'
> We can
> talk about why 100days is converted to '10' days and months aren't
> used. I agree this example seems strange. Thomas?
You can't convert days to months as there is no universal month length.
this is the current (correct) behaviour:
hannu=# select '25mon37d1s'::interval;
interval
2 years 1 mon 37 days 00:00:01
(1 row)
--
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] Project scheduling issues (was Re: Per tuple overhead,
Marc G. Fournier wrote: > On Mon, 10 Jun 2002, Bruce Momjian wrote: > > > > 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. > > Ya, I thought it was when you -and- Tom proposed it :) > > I quote from a message on June 8th: > > === > Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > So, I we should: > > > Warn people in July that beta is September 1 and all features > > > have to be complete by then, or they get ripped out. > > > > I thought that was more or less the same thing I was proposing... > What I thought was interesting was having the CURRENT branch keep the feature so the guy could continue development, even if we disable in STABLE. -- 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] Timestamp/Interval proposals: Part 2
On Tue, 2002-06-11 at 11:21, Karel Zak wrote:
> On Tue, Jun 11, 2002 at 11:16:13AM +0200, Hannu Krosing wrote:
> > On Tue, 2002-06-11 at 09:34, Karel Zak wrote:
>
> > > I think, we can keep this behaviour for to_char(), the good thing
> > > is that you can formatting interval to strings that seems like
> > > standard time (15:10:33), etc.
> >
> > But interval _is_ _not_ point-in-time, it is a time_span_ .
> >
> > It can be either good if it gives the results you want or bad if it does
> > give wrong results like returning 03:10:33 for the above
> >
> > I would suggest that a separate to_char function would be written that
> > would be _specific_to_interval_ datatype - so wheb i do
> >
> > to_char('33s 15h 10m'::interval, 'SS') I will get the actual length of
> >
> > interval in seconds, 15*3600+10*60+33 = 54633s and not just the seconds part (33)
> >
> > whereas to_char('33s 15h 10m'::interval, 'MI SS') would give
> >
> > 15*60+10=910 min 33 sec ('910 33')
>
> Well, If the to_char() for interval will output result that you want,
> how can I output '15:10:33'?
>
> For this I want two direffent function or anothers format marks for
> to_char() like
>
> to_char('33s 15h 10m'::interval, '#MI #SS');
> ---
> '910 33'
and it is probably easyer to implement too - no need to first collect
all possible format chars.
> but for "standard" marks (that now works like docs describe :-) will output
> MI in 0..59 range.
>
> to_char('33s 15h 10m'::interval, 'MI:SS');
> ---
> '10:33'
>
> IMHO it's acceptable. I don't want close the way for output formatting
> in "standard" date/time ranges. We can support _both_ ways. Or not?
perhaps we should do as to_char does for floats -- return ### if
argument cant be shown with given format ?
hannu=# select to_char(1000.0,'D00') as good,
hannu-#to_char(1000.0, '000D00') as bad;
good | bad
--+-
1000.00 | ###.##
(1 row)
no need to change current documented behaviour without good reason
> Thomas, you are quiet? :-)
>
> Karel
>
>
> PS. the PostgreSQL converting intervals to "standard" format too:
>
> test=# select '33h 15m'::interval - '10h 2m 3s'::interval ;
> ?column?
> --
> 23:12:57
> (1 row)
>
> test=# select '45h 15m'::interval - '10h 2m 3s'::interval ;
> ?column?
>
> 1 day 11:12:57
>
> (hmm.. I unsure if this is really released 7.2, I maybe have
> some pre-7.2 version now. Is this 7.2 behaviuor?)
Yes.
And this is still an interval, not a timestamp:
hannu=# select '4500h 15m'::interval - '10h 2m 3s'::interval ;
?column?
---
187 days 02:12:57
(1 row)
--
Hannu
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [BUGS] Bug #640: ECPG: inserting float numbers
Bruce, the attached source reproduces this on 7.2, I don't have a
later version at hand to test if it's been fixed:
createdb floattest
echo "CREATE TABLE tab1(col1 FLOAT);" | psql floattest
ecpg insert-float.pgc
gcc insert-float.c -lecpg -lpq
./a.out floattest
results in:
col1: -0.06
*!*!* Error -400: 'ERROR: parser: parse error at or near "a"' in line 21.
and in epcgdebug:
[29189]: ECPGexecute line 21: QUERY: insert into tab1 ( col1 ) values (
-6.002122251e-06A ) on connection floattest
[29189]: ECPGexecute line 21: Error: ERROR: parser: parse error at or near "a"
[29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or near
"a"' in line 21.'.
Regards, Lee Kindness.
Bruce Momjian writes:
> Has this been addressed? Can you supply a reproducable example?
> Edward Pilipczuk wrote:
> > On Monday, 22 April 2002 18:41, you wrote:
> > > Edward ([EMAIL PROTECTED]) reports a bug with a severity of 1
> > > ECPG: inserting float numbers
> > > Inserting records with single precision real variables having small value
> > > (range 1.0e-6 or less) frequently results in errors in ECPG translations
> > > putting into resulted sql statement unexpected characters => see fragments
> > > of sample code and ECPGdebug log where after value of rate variable the
> > > unexpected character '^A' appears
> > >
> > > Sample Code
> > > [ snip ]
#include
EXEC SQL INCLUDE sqlca;
int main(int argc, char **argv)
{
EXEC SQL BEGIN DECLARE SECTION;
char *db = argv[1];
float col1;
EXEC SQL END DECLARE SECTION;
FILE *f;
if( (f = fopen("ecpgdebug", "w" )) != NULL )
ECPGdebug(1, f);
EXEC SQL CONNECT TO :db;
EXEC SQL BEGIN;
col1 = -6e-06;
printf("col1: %f\n", col1);
EXEC SQL INSERT INTO tab1(col1) VALUES (:col1);
if( sqlca.sqlcode < 0 )
{
fprintf(stdout, "*!*!* Error %ld: %s\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
EXEC SQL ABORT;
EXEC SQL DISCONNECT;
return( 1 );
}
else
{
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
return( 0 );
}
}
---(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] tuplesort: unexpected end of data
Hi! A different error today: [MemoryContextAlloc: invalid request size 4294967295] This is a more often (twice a week) error and I don't understand why?... I'm verifying the machine: fsck (with bad blocks chk), ... but no hardware problems untill now. info $ pg_config --version PostgreSQL 7.2.1 $ cat /etc/redhat-release Red Hat Linux release 7.2 (Enigma) $/var/log/pgsql (excerpt) (...) Jun 11 03:06:04 srv31 postgres[13914]: [3403] ERROR: cannot open segment 1 of relation n_gram (target block 528325): No such file or directory (...) Jun 11 04:26:12 srv31 postgres[14972]: [3317] DEBUG: recycled transaction log file 002000F6 Jun 11 04:26:12 srv31 postgres[14972]: [3318] DEBUG: recycled transaction log file 002000F7 Jun 11 04:26:12 srv31 postgres[14972]: [3319] DEBUG: recycled transaction log file 002000F8 Jun 11 04:28:56 srv31 postgres[14983]: [3317] DEBUG: recycled transaction log file 002000F9 Jun 11 04:28:56 srv31 postgres[14983]: [3318] DEBUG: recycled transaction log file 002000FA Jun 11 04:28:56 srv31 postgres[14983]: [3319] DEBUG: recycled transaction log file 002000FB Jun 11 03:29:07 srv31 postgres[13913]: [3383] ERROR: MemoryContextAlloc: invalid request size 4294967295 Jun 11 03:29:07 srv31 postgres[13913]: [3384] NOTICE: Error occurred while executing PL/pgSQL function set_n_gram Jun 11 03:29:07 srv31 postgres[13913]: [3385] NOTICE: line 9 at select into variables (...) -- o__Bem haja, _.>/ _ NunoACHenriques (_) \(_) ~~~ http://students.fct.unl.pt/users/nuno/ ---(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] [BUGS] Bug #640: ECPG: inserting float numbers
OK, I have reproduced the problem on my machine:
#$ ./a.out floattest
col1: -0.06
*!*!* Error -220: No such connection NULL in line 21.
Wow, how did that "A" get into the query string:
insert into tab1 ( col1 ) values ( -6.002122251e-06A )
Quite strange. Michael, any ideas?
---
Lee Kindness wrote:
Content-Description: message body text
> Bruce, the attached source reproduces this on 7.2, I don't have a
> later version at hand to test if it's been fixed:
>
> createdb floattest
> echo "CREATE TABLE tab1(col1 FLOAT);" | psql floattest
> ecpg insert-float.pgc
> gcc insert-float.c -lecpg -lpq
> ./a.out floattest
>
> results in:
>
> col1: -0.06
> *!*!* Error -400: 'ERROR: parser: parse error at or near "a"' in line 21.
>
> and in epcgdebug:
>
> [29189]: ECPGexecute line 21: QUERY: insert into tab1 ( col1 ) values (
>-6.002122251e-06A ) on connection floattest
> [29189]: ECPGexecute line 21: Error: ERROR: parser: parse error at or near "a"
> [29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or near
>"a"' in line 21.'.
>
> Regards, Lee Kindness.
>
> Bruce Momjian writes:
> > Has this been addressed? Can you supply a reproducable example?
> > Edward Pilipczuk wrote:
> > > On Monday, 22 April 2002 18:41, you wrote:
> > > > Edward ([EMAIL PROTECTED]) reports a bug with a severity of 1
> > > > ECPG: inserting float numbers
> > > > Inserting records with single precision real variables having small value
> > > > (range 1.0e-6 or less) frequently results in errors in ECPG translations
> > > > putting into resulted sql statement unexpected characters => see fragments
> > > > of sample code and ECPGdebug log where after value of rate variable the
> > > > unexpected character '^A' appears
> > > >
> > > > Sample Code
> > > > [ snip ]
>
> #include
>
> EXEC SQL INCLUDE sqlca;
>
> int main(int argc, char **argv)
> {
> EXEC SQL BEGIN DECLARE SECTION;
> char *db = argv[1];
> float col1;
> EXEC SQL END DECLARE SECTION;
> FILE *f;
>
> if( (f = fopen("ecpgdebug", "w" )) != NULL )
> ECPGdebug(1, f);
>
> EXEC SQL CONNECT TO :db;
> EXEC SQL BEGIN;
>
> col1 = -6e-06;
> printf("col1: %f\n", col1);
> EXEC SQL INSERT INTO tab1(col1) VALUES (:col1);
> if( sqlca.sqlcode < 0 )
> {
> fprintf(stdout, "*!*!* Error %ld: %s\n", sqlca.sqlcode,
>sqlca.sqlerrm.sqlerrmc);
> EXEC SQL ABORT;
> EXEC SQL DISCONNECT;
> return( 1 );
> }
> else
> {
> EXEC SQL COMMIT;
> EXEC SQL DISCONNECT;
> return( 0 );
> }
> }
--
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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Bug #640: ECPG: inserting float numbers
Bruce, after checking the libecpg source i'm fairly sure the problem is due to the malloc buffer that the float is being sprintf'd into being too small... It is always allocated 20 bytes but with a %.14g printf specifier -6e-06 results in 20 characters: -6.0e-06 and the NULL goes... bang! I guess the '-' wasn't factored in and 21 bytes would be enough. Patch against current CVS (but untested): Index: src/interfaces/ecpg/lib/execute.c === RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/lib/execute.c,v retrieving revision 1.36 diff -r1.36 execute.c 703c703 < if (!(mallocedval = ECPGalloc(var->arrsize * 20, stmt->lineno))) --- > if (!(mallocedval = ECPGalloc(var->arrsize * 21, >stmt->lineno))) 723c723 < if (!(mallocedval = ECPGalloc(var->arrsize * 20, stmt->lineno))) --- > if (!(mallocedval = ECPGalloc(var->arrsize * 21, >stmt->lineno))) Lee. Bruce Momjian writes: > > OK, I have reproduced the problem on my machine: > > #$ ./a.out floattest > col1: -0.06 > *!*!* Error -220: No such connection NULL in line 21. > > Wow, how did that "A" get into the query string: > > insert into tab1 ( col1 ) values ( -6.002122251e-06A ) > > Quite strange. Michael, any ideas? > > Lee Kindness wrote: > Content-Description: message body text > > > Bruce, the attached source reproduces this on 7.2, I don't have a > > later version at hand to test if it's been fixed: > > > > createdb floattest > > echo "CREATE TABLE tab1(col1 FLOAT);" | psql floattest > > ecpg insert-float.pgc > > gcc insert-float.c -lecpg -lpq > > ./a.out floattest > > > > results in: > > > > col1: -0.06 > > *!*!* Error -400: 'ERROR: parser: parse error at or near "a"' in line 21. > > > > and in epcgdebug: > > > > [29189]: ECPGexecute line 21: QUERY: insert into tab1 ( col1 ) values ( >-6.002122251e-06A ) on connection floattest > > [29189]: ECPGexecute line 21: Error: ERROR: parser: parse error at or near "a" > > [29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or >near "a"' in line 21.'. > > > > Regards, Lee Kindness. ---(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] Project scheduling issues (was Re: Per tuple overhead,
Lamar Owen wrote: > On Monday 10 June 2002 04:11 pm, Tom Lane wrote: > > 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. If that's a pattern, then we should discourage people from using odd dot-releases. My opinion? With each release we ship improvements and new functionality people have long waited for. Think about vacuum, toast, referential integrity. People need those things and have great confidence in our releases. The willingness to upgrade their production systems to dot zero releases is the biggest compliment users can make. Everything that endangers that quality is bad(tm). Our develop/beta mode pattern keeps people from diving into the next bigger thing, distracting them from the current beta or release candidate. I don't think that would do us a really good job. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] Bug #640: ECPG: inserting float numbers
Bruce, this error and the one in your earlier post are not indicative of the bug, but rather of the connection failing - looking at the created ecpgdebug file should confirm this. I have since compiled 7.3 with the patch locally and cannot recreate the bug (after messing around with the HBA cfg file - I was getting the same error as you). My command line (with 7.3 sitting in /database/pgsql-test on port 5433 and LD_LIBRARY_PATH setup): /database/pgsql-test/bin/ecpg insert-float.pgc gcc insert-float.c -I/database/pgsql-test/include -L/database/pgsql-test/lib -lecpg -lpq ./a.out floattest@localhost:5433 Regards, Lee Kindness. Bruce Momjian writes: > I am now getting this error: > #$ ./a.out floattest > col1: -0.06 > *!*!* Error -220: No such connection NULL in line 21. > I will wait for Michael to comment on this. > > --- > > Lee Kindness wrote: > > Lee Kindness writes: > > > and the NULL goes... bang! I guess the '-' wasn't factored in and 21 > > > bytes would be enough. Patch against current CVS (but untested): > > > > Ooops, a context diff is below... > > > > Index: src/interfaces/ecpg/lib/execute.c > > === > > RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/lib/execute.c,v > > retrieving revision 1.36 > > diff -c -r1.36 execute.c > > *** src/interfaces/ecpg/lib/execute.c 2002/01/13 08:52:08 1.36 > > --- src/interfaces/ecpg/lib/execute.c 2002/06/11 11:45:35 > > *** > > *** 700,706 > >break; > > #endif /* HAVE_LONG_LONG_INT_64 */ > >case ECPGt_float: > > ! if (!(mallocedval = ECPGalloc(var->arrsize * 20, >stmt->lineno))) > >return false; > > > >if (var->arrsize > 1) > > --- 700,706 > >break; > > #endif /* HAVE_LONG_LONG_INT_64 */ > >case ECPGt_float: > > ! if (!(mallocedval = ECPGalloc(var->arrsize * 21, >stmt->lineno))) > >return false; > > > >if (var->arrsize > 1) > > *** > > *** 720,726 > >break; > > > >case ECPGt_double: > > ! if (!(mallocedval = ECPGalloc(var->arrsize * 20, >stmt->lineno))) > >return false; > > > >if (var->arrsize > 1) > > --- 720,726 > >break; > > > >case ECPGt_double: > > ! if (!(mallocedval = ECPGalloc(var->arrsize * 21, >stmt->lineno))) > >return false; > > > >if (var->arrsize > 1) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tuplesort: unexpected end of data
NunoACHenriques <[EMAIL PROTECTED]> writes: > A different error today: > [MemoryContextAlloc: invalid request size 4294967295] This could be a variant of the same problem: instead of getting a zero tuple length from the sort temp file, we're reading a -1 tuple length. Still no way to tell if it's a hardware glitch or a software bug. (If the latter, presumably the code is getting out of step about its read position in the temp file --- but how?) 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
[HACKERS] Native Win32/OS2/BeOS/NetWare ports
Hello together i've seen a lot of discussion about a native win32/OS2/BEOS port of PostgreSQL. During the last months i've ported PostgreSQL over to Novell NetWare and i've changed the code that I use pthreads instead of fork() now. I had a lot of work with the variables and cleanup but mayor parts are done. I would appreciate if we could combine this work. My plan was to finish this port, discuss the port with other people and offer all the work to the PostgreSQL source tree, but now i'm jumping in here because of all the discussions. What i've done in detail: - i've defined #USE_PTHREADS in pg_config.h to differentiate between the forked and the threaded backend. - I've added several parts in postmaster.c so all functions are based on pthreads now. - I've changed the signal handling because signals are process based - I've changed code in ipc.c to have a clean shutdown of threads - I've written some functions to switch the global variables. The globals are controled with POSIX semaphores. - I've written a new implementation of shared memory and semaphores- With pthreads I don't need real shared memory any more and i'm using POSIX semaphores now - Several minor changes. There is still some more work to do like fixing memory leaks or handling bad situations, but in general it's functional on NetWare. BTW: Is it possible to add some lines on the PostgreSQL webpage that there is a first beta of PostgreSQL for NetWare available and to offer a binary download for the NetWare version? Ulrich Neumann -- This e-mail is virus scanned Diese e-mail ist virusgeprueft ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Tue, Jun 11, 2002 at 06:22:55AM -0700, Thomas Lockhart wrote:
> > > fduch=> SELECT to_char('100days'::interval, '-MM-DD HH24:MI:SS');
> > > -
> > > -00-10 00:00:00
> > I already said it. The to_char() is 'tm' struct interpreter and use
> > standard internal PG routines for interval to 'tm' conversion. We can
> > talk about why 100days is converted to '10' days and months aren't
> > used. I agree this example seems strange. Thomas?
>
> Not sure why 100 is becoming 10, except that the formatting string is
> specifying a field width of two characters (right?). And for intervals,
Oops. Yes, you are right it's %02d. I forgot it. Sorry :-)
> years and months are not interchangable with days so values do not
> overflow from days to months fields.
>
> I played around with to_char(interval,text) but don't understand the
> behavior either.
OK. And what is wanted behavior?
DD = day
## = error
1) '30h 10m 15s' 'HH MI SS' ---> '06 10 15'
'30h 10m 15s' 'HH MI SS DD' ---> '06 10 15 1'
2) '30h 10m 15s' 'HH MI SS'---> '30 10 15'
'30h 10m 15s' 'HH MI SS DD' ---> '30 10 15 ##'
3) '30h 10m 15s' 'HH MI SS'---> '30 10 15'
'30h 10m 15s' 'HH MI SS DD' ---> '06 10 15 1'
4) use both 1) and 2) but with different marks like
'HH' and '#HH' (or other special prefix)
5) '2week' 'DD'---> '14'
6) '2week' 'HH'---> '00'
7) '2week' 'HH'---> '336'
8) '2week' 'DD HH' ---> '14 00'
9) ???
I unsure what is best, Please, mark right outputs or write examples.
-- for all is probably right idea use '' in output
if input is not possible convert to wanted format (like current
float to_char() behavior).
BTW:
test=# select date_part('hour', '30h 10m 15s'::interval);
date_part
---
6
test=# select date_part('day', '30h 10m 15s'::interval);
date_part
---
1
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Bug found: fmgr_info: function : cache lookup failed
If you double-alias a column in a query (yeah, stupid, I know, but I did it by mistake and others will too!), then the dreaded "fmgr_info: function : cache lookup failed" message is kicked out. For example: select * from company c, references r where r.company_id=c.company.id; Note that c.company.id references column id in table company twice! Hope that this finds someone looking at the error handling in the parser! Should be chucked out as a syntax error. Brad ---(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] Bug found: fmgr_info: function : cache lookup failed
Bradley Kieser <[EMAIL PROTECTED]> writes: > If you double-alias a column in a query (yeah, stupid, I know, but I did > it by mistake and others will too!), then the dreaded "fmgr_info: > function : cache lookup failed" message is kicked out. For example: > select * from company c, references r where r.company_id=c.company.id; Can you provide a *complete* example? Also, what version are you using? I tried this in 7.2.1: test72=# create table company (id int); CREATE test72=# create table refs(company_id int); CREATE test72=# select * from company c, refs r where r.company_id=c.company.id; ERROR: No such attribute or function 'company' regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostGres Doubt
On Mon, 10 Jun 2002, Dann Corbit wrote: > If you are going to completely replace the data in a table, drop the > table, create the table, and use the bulk copy interface. Actually, that's a bad habit to get into. Views disappear, as do triggers or constraints. Better to 'truncate table' or 'delete from table'. I know, I had a bear of a time with a nightly drop table;create table;copy data in script that I forgot about and built a nice new app on views. worked fine, came in the next morning, app was down... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Will postgress handle too big tables?
also, remember that for the cost of a single CPU oracle license you can build a crankin' postgresql server... memory and I/O are way more important than CPU power btw. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Mac OS X shutdown
Hello, is there any news about the Mac OS X shutdown issue? It was discussed in a few April-May/2002 messages with the Subject "Mac OS X: system shutdown prevents checkpoint". In short, during a regular system shutdown on Mac OS X the postmaster is not terminated gracefully, leading to troubles at the successive startup. All OS X release I know of, up to the latest one (10.1.5), are prone to this inconvenient. Thanks, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] New string functions; initdb required
I've just committed changes which implement three SQL99 functions and operators. OVERLAY() allows substituting a string into another string, SIMILAR TO is an operator for pattern matching, and a new variant of SUBSTRING() accepts a pattern to match. Regression tests have been augmented and pass. Docs have been updated. The system catalogs were updated, so it is initdb time. Details from the cvs log below... - Thomas Implement SQL99 OVERLAY(). Allows substitution of a substring in a string. Implement SQL99 SIMILAR TO as a synonym for our existing operator "~". Implement SQL99 regular expression SUBSTRING(string FROM pat FOR escape). Extend the definition to make the FOR clause optional. Define textregexsubstr() to actually implement this feature. Update the regression test to include these new string features. All tests pass. Rename the regular expression support routines from "pg95_xxx" to "pg_xxx". Define CREATE CHARACTER SET in the parser per SQL99. No implementation yet. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Majordomo aliases
OK, I *really* need to get my majordomo account fixed up to keep from stalling posts from my various accounts to the various lists. I think that I can enter some aliases etc to allow this; where do I find out how? Searching the -hackers archives brought no joy since the obvious keywords show up in every stinkin' mail message ever run through the mailing list :/ Any help would be appreciated... - Thomas ---(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] New string functions; initdb required
I've just committed changes which implement three SQL99 functions and operators. OVERLAY() allows substituting a string into another string, SIMILAR TO is an operator for pattern matching, and a new variant of SUBSTRING() accepts a pattern to match. Regression tests have been augmented and pass. Docs have been updated. The system catalogs were updated, so it is initdb time. Details from the cvs log below... - Thomas Implement SQL99 OVERLAY(). Allows substitution of a substring in a string. Implement SQL99 SIMILAR TO as a synonym for our existing operator "~". Implement SQL99 regular expression SUBSTRING(string FROM pat FOR escape). Extend the definition to make the FOR clause optional. Define textregexsubstr() to actually implement this feature. Update the regression test to include these new string features. All tests pass. Rename the regular expression support routines from "pg95_xxx" to "pg_xxx". Define CREATE CHARACTER SET in the parser per SQL99. No implementation yet. ---(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] Native Win32/OS2/BeOS/NetWare ports
> Hello together > > i've seen a lot of discussion about a native win32/OS2/BEOS port of > PostgreSQL. > > During the last months i've ported PostgreSQL over to Novell NetWare > and i've > changed the code that I use pthreads instead of fork() now. > > I had a lot of work with the variables and cleanup but mayor parts are > done. > > I would appreciate if we could combine this work. Very nice... I have patches for QNX6 which also involved redoing shared memory and sempahores stuff. It would make very good sense to intergate, especially since you managed to do something very close to what I wanted :) > My plan was to finish this port, discuss the port with other people and > offer all the work > to the PostgreSQL source tree, but now i'm jumping in here because of > all the discussions. > > What i've done in detail: > - i've defined #USE_PTHREADS in pg_config.h to differentiate between > the forked and the > threaded backend. > - I've added several parts in postmaster.c so all functions are based > on pthreads now. > - I've changed the signal handling because signals are process based Careful here. On certain systems (on many, I suspect) POSIX semantics for signals is NOT default. Enforcing POSIX semantics requires certain compile time switches which will also change behavior of various functions. > - I've changed code in ipc.c to have a clean shutdown of threads > - I've written some functions to switch the global variables. The > globals are controled with > POSIX semaphores. > - I've written a new implementation of shared memory and semaphores- > With pthreads I don't > need real shared memory any more and i'm using POSIX semaphores now POSIX semaphores for what? I assume by the conext that you're talking about replacing SysV semaphores which are used to control access to shared memory. If that is the case, POSIX semaphores are not the best choice really. POSIX mutexes would be okay, but on SMP systems spinlocks (hardware TAS based macros or POSIX spinlocks) would probably be better anyway. Note that on most platforms spinlocks are used for that and SysV semaphores were just a 'last resort' which had unacceptable performance and so I guess it was not used at all. Do you have your patch somewhere online? -- igor ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Majordomo aliases
On Tue, 11 Jun 2002, Thomas Lockhart wrote: > OK, I *really* need to get my majordomo account fixed up to keep from > stalling posts from my various accounts to the various lists. > > I think that I can enter some aliases etc to allow this; where do I find > out how? Searching the -hackers archives brought no joy since the > obvious keywords show up in every stinkin' mail message ever run through > the mailing list :/ > > Any help would be appreciated... You can always subscribe to a list and do aset nomail Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Majordomo aliases
There's fairly extensive help available from the list 'bot itself. Try sending a message with help help set to [EMAIL PROTECTED] (There are a bunch of other help topics but I'm guessing "set" is most likely the command you need.) A low-tech solution would be to subscribe all your addresses and then set all but one to "nomail". Not sure if there's a better way. 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] Majordomo aliases
On Tue, 11 Jun 2002, Tom Lane wrote: > There's fairly extensive help available from the list 'bot itself. > Try sending a message with > help > help set > to [EMAIL PROTECTED] (There are a bunch of other help topics > but I'm guessing "set" is most likely the command you need.) > > A low-tech solution would be to subscribe all your addresses and then > set all but one to "nomail". Not sure if there's a better way. The better way *was* loophole, but it's gone. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New string functions; initdb required
Thomas Lockhart <[EMAIL PROTECTED]> writes: > I've just committed changes which implement three SQL99 functions and > operators. I'm getting gcc -O1 -Wall -Wmissing-prototypes -Wmissing-declarations -g -I../../../../src/include -c -o regexp.o regexp.c regexp.c: In function `textregexsubstr': regexp.c:314: warning: unused variable `result' The code seems to be rather undecided about whether it intends to return NULL or an empty string --- would you make up your mind and remove the other case entirely? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] New string functions; initdb required
Also, you neglected to add PLACING to the gram.y keyword category lists. (Perhaps someone should whip up a cross-checking script to verify that everything known to keywords.c is listed exactly once in those gram.y lists.) 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
Karel, > The to_interval() will have another (you wanted) behaviour. Please, please, please do not use to_interval for text formatting of intervals. It's very inconsistent with the naming of other conversion functions, and will confuse the heck out of a lot of users. As well as messing up my databases, which have to_interval as a replacement for the problematically named "interval" function. -- -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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timestamp/Interval proposals: Part 2
On Tue, Jun 11, 2002 at 09:36:39AM -0700, Josh Berkus wrote: > Karel, > > > The to_interval() will have another (you wanted) behaviour. > > Please, please, please do not use to_interval for text formatting of > intervals. It's very inconsistent with the naming of other conversion > functions, and will confuse the heck out of a lot of users. As well as > messing up my databases, which have to_interval as a replacement for the > problematically named "interval" function. Yes, agree. It wasn't well-advised. It will probably to_char() with special 'interval' behaviour or format marks. But I still don't know how behaviour is right. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timestamp/Interval proposals: Part 2
> > fduch=> SELECT to_char('100days'::interval, '-MM-DD HH24:MI:SS');
> > -
> > -00-10 00:00:00
> I already said it. The to_char() is 'tm' struct interpreter and use
> standard internal PG routines for interval to 'tm' conversion. We can
> talk about why 100days is converted to '10' days and months aren't
> used. I agree this example seems strange. Thomas?
Not sure why 100 is becoming 10, except that the formatting string is
specifying a field width of two characters (right?). And for intervals,
years and months are not interchangable with days so values do not
overflow from days to months fields.
I played around with to_char(interval,text) but don't understand the
behavior either.
- Thomas
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Timestamp/Interval proposals: Part 2
> > I already said it. The to_char() is 'tm' struct interpreter and use > > standard internal PG routines for interval to 'tm' conversion. > The point is it should _not_ do that for interval. I use the tm structure to hold this structured information. I *think* that Karel's usage is just what is intended by my support routines, though I haven't looked at it in quite some time. Let me know if you want me to look Karel... - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] New string functions; initdb required
> Also, you neglected to add PLACING to the gram.y keyword category lists. OK. I'm also tracking down what seems to be funny business in the regex pattern caching logic, so will have a couple of things to fix sometime soon. - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Referential integrity problem postgresql 7.2 ?
On Tue, 11 Jun 2002, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > As a related side note. The other part of the original patch (the NOT > > EXISTS in the upd/del no action trigger) was rejected. For match > > full and match unspecified the same result can be reached by doing another > > query which may be better than the subquery. Do you think that'd be > > better? > > No opinion offhand; can you show examples of the alternatives you have > in mind? [guessing that -bugs is probably not appropriate anymore, moving to -hackers] An additional query of the form... SELECT 1 FROM ONLY WHERE pkatt= [AND ...] to the upd/del no action triggers. Right now in either deferred constraints or when multiple statements are run in a function we can sometimes raise an error where there shouldn't be one if a pk row is modified and a new pk row that has the old values is added. The above should catch this (and in fact the first versions of the patch that I did which were only sent to a couple of people who were having problems did exactly that). When I did the later patch, I changed it to a NOT EXISTS() subquery because for match partial, the new row might not need to exactly match, but the details of how it needs to match are based on what matching rows there are in the fk table. I'm not sure in general how else (apart from doing a lower level scan of the table) how to tell if another unrelated row with the same values has been added to the table between the point of the action that caused this trigger to be added to the queue and the point the trigger runs. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New string functions; initdb required
Thomas, > I've just committed changes which implement three SQL99 functions and > operators. OVERLAY() allows substituting a string into another string, > SIMILAR TO is an operator for pattern matching, and a new variant of > SUBSTRING() accepts a pattern to match. Way cool! Thank you ... this replaces several of my custom PL/pgSQL functions. How is SIMILAR TO different from ~ ? -- -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] New string functions; initdb required
On Tue, Jun 11, 2002 at 11:08:11AM -0700, Josh Berkus wrote: > Thomas, > > > I've just committed changes which implement three SQL99 functions and > > operators. OVERLAY() allows substituting a string into another string, > > SIMILAR TO is an operator for pattern matching, and a new variant of > > SUBSTRING() accepts a pattern to match. > > Way cool! Thank you ... this replaces several of my custom PL/pgSQL > functions. > > How is SIMILAR TO different from ~ ? >From the part of Thomas's email you snipped: Implement SQL99 SIMILAR TO as a synonym for our existing operator "~". So the answer is "not at all" Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] New string functions; initdb required
Thomas Lockhart wrote: > I've just committed changes which implement three SQL99 functions and > operators. OVERLAY() allows substituting a string into another string, > SIMILAR TO is an operator for pattern matching, and a new variant of TODO item marked as done: * -Add SIMILAR TO to allow character classes, 'pg_[a-c]%' -- 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] Analyze on large changes...
Tom Lane wrote:
> I tried to repeat this:
>
> regression=# begin;
> BEGIN
> regression=# create table foo (f1 int);
> CREATE
> regression=# insert into foo [ ... some data ... ]
>
> regression=# analyze foo;
> ERROR: ANALYZE cannot run inside a BEGIN/END block
>
> This seems a tad silly; I can't see any reason why ANALYZE couldn't be
> done inside a BEGIN block. I think this is just a hangover from
> ANALYZE's origins as part of VACUUM. Can anyone see a reason not to
> allow it?
The following patch allows analyze to be run inside a transaction.
Vacuum and vacuum analyze still can not be run in a transaction.
--
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
Index: src/backend/commands/analyze.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/analyze.c,v
retrieving revision 1.35
diff -c -r1.35 analyze.c
*** src/backend/commands/analyze.c 24 May 2002 18:57:55 - 1.35
--- src/backend/commands/analyze.c 11 Jun 2002 21:38:51 -
***
*** 156,170
elevel = DEBUG1;
/*
-* Begin a transaction for analyzing this relation.
-*
-* Note: All memory allocated during ANALYZE will live in
-* TransactionCommandContext or a subcontext thereof, so it will all
-* be released by transaction commit at the end of this routine.
-*/
- StartTransactionCommand();
-
- /*
* Check for user-requested abort. Note we want this to be inside a
* transaction, so xact.c doesn't issue useless WARNING.
*/
--- 156,161
***
*** 177,186
if (!SearchSysCacheExists(RELOID,
ObjectIdGetDatum(relid),
0, 0, 0))
- {
- CommitTransactionCommand();
return;
- }
/*
* Open the class, getting only a read lock on it, and check
--- 168,174
***
*** 196,202
elog(WARNING, "Skipping \"%s\" --- only table or database
owner can ANALYZE it",
RelationGetRelationName(onerel));
relation_close(onerel, AccessShareLock);
- CommitTransactionCommand();
return;
}
--- 184,189
***
*** 211,217
elog(WARNING, "Skipping \"%s\" --- can not process indexes,
views or special system tables",
RelationGetRelationName(onerel));
relation_close(onerel, AccessShareLock);
- CommitTransactionCommand();
return;
}
--- 198,203
***
*** 222,228
strcmp(RelationGetRelationName(onerel), StatisticRelationName) == 0)
{
relation_close(onerel, AccessShareLock);
- CommitTransactionCommand();
return;
}
--- 208,213
***
*** 283,289
if (attr_cnt <= 0)
{
relation_close(onerel, NoLock);
- CommitTransactionCommand();
return;
}
--- 268,273
***
*** 370,378
* entries we made in pg_statistic.)
*/
relation_close(onerel, NoLock);
-
- /* Commit and release working memory */
- CommitTransactionCommand();
}
/*
--- 354,359
Index: src/backend/commands/vacuum.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.226
diff -c -r1.226 vacuum.c
*** src/backend/commands/vacuum.c 24 May 2002 18:57:56 - 1.226
--- src/backend/commands/vacuum.c 11 Jun 2002 21:38:59 -
***
*** 110,117
/* non-export function prototypes */
- static void vacuum_init(VacuumStmt *vacstmt);
- static void vacuum_shutdown(VacuumStmt *vacstmt);
static List *getrels(const RangeVar *vacrel, const char *stmttype);
static void vac_update_dbstats(Oid dbid,
TransactionId vacuumXID,
--- 110,115
***
*** 178,190
* user's transaction too, which would certainly not be the desired
* behavior.
*/
! if (IsTransactionBlock())
elog(ERROR, "%s cannot run inside a BEGIN/END block", stmttype);
/* Running VACUUM from a function would free the function context */
! if (!MemoryContextContains(QueryContext, vacstmt))
elog(ERROR, "%s cannot be executed from a function", stmttype);
!
/*
* Send info
Re: [HACKERS] Analyze on large changes...
Bruce Momjian wrote: > Tom Lane wrote: > > I tried to repeat this: > > > > regression=# begin; > > BEGIN > > regression=# create table foo (f1 int); > > CREATE > > regression=# insert into foo [ ... some data ... ] > > > > regression=# analyze foo; > > ERROR: ANALYZE cannot run inside a BEGIN/END block > > > > This seems a tad silly; I can't see any reason why ANALYZE couldn't be > > done inside a BEGIN block. I think this is just a hangover from > > ANALYZE's origins as part of VACUUM. Can anyone see a reason not to > > allow it? > > The following patch allows analyze to be run inside a transaction. > Vacuum and vacuum analyze still can not be run in a transaction. One change in this patch is that because analyze now runs in the outer transaction, I can't clear the memory used to support each analyzed relation. Not sure if this is an issue. -- 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] New string functions; initdb required
> TODO item marked as done: > * -Add SIMILAR TO to allow character classes, 'pg_[a-c]%' Darn. Will have to be more careful next time ;) - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New string functions; initdb required
> > How is SIMILAR TO different from ~ ? > >From the part of Thomas's email you snipped: >Implement SQL99 SIMILAR TO as a synonym for our existing operator "~". > So the answer is "not at all" Right. I'm not certain about the regex syntax defined by SQL99; I used the syntax that we already have enabled and it looks like we have a couple of other variants available if we need them. If someone wants to research the *actual* syntax specified by SQL99 that would be good... - Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Schemas and template1
There was discussion of how template1's "public" schema should behave. I think the only solution is to make template1's public schema writable only by the super-user. This way, we can allow utility commands to connect to template1, but they can't change anything or add their own tables. As part of createdb, the new database will have to have it's public schema changed to world-writable. -- 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]
Re: [HACKERS] PATCH SSL_pending() checks in libpq/fe-misc.c
Would you send over a context diff, diff -c?
---
Jack Bates wrote:
>
> Hello:
>
> I took a look at the SSL code in libpq/fe-misc.c and noticed what I
> think is a small problem. A patch is included at the bottom of this
> email against anoncvs TopOfTree this evening.
>
> The SSL library buffers input data internally. Nowhere in libpq's code
> is this buffer being checked via SSL_pending(), which can lead to a
> condition where once in a while a socket appears to "hang" or "lag".
> This is because select() won't see bytes buffered by the library. A
> condition like this is most likely to occur when the library's read
> buffer has been filled previously and another read is to be performed.
> If the end of the backend's transmission was less than one SSL frame
> payload away from the last byte returned in the previous read, this will
> likely hang. Trust me that I learned of this most painfully...
>
> I am looking deeper at how to enable non-blocking SSL sockets in libpq.
> As Tom Lane states, this is primarily a matter of checking SSL error
> codes, particularly for SSL_WANT_READ and SSL_WANT_WRITE, and reacting
> appropriately. I'll see about that as I have more free time.
>
> Even though I'm doing this, I tend to agree with Tom that SSH tunnels
> are a really good way to make the whole SSL problem just go away.
>
> My quick patch to perform the SSL_pending() checks:
>
> ===
> RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-misc.c,v
> retrieving revision 1.70
> diff -r1.70 fe-misc.c
> 350a351
> > * -or- if SSL is enabled and used, is it buffering bytes?
> 361a363,371
> > /* Check for SSL library buffering read bytes */
> > #ifdef USE_SSL
> > if (conn->ssl && SSL_pending(conn->ssl) > 0)
> > {
> > /* short-circuit the select */
> > return 1;
> > }
> > #endif
> >
> 784a795,797
> > * If SSL enabled and used and forRead, buffered bytes short-circuit the
> > * call to select().
> > *
> 801a815,823
> >
> > /* Check for SSL library buffering read bytes */
> > #ifdef USE_SSL
> > if (forRead && conn->ssl && SSL_pending(conn->ssl) > 0)
> > {
> > /* short-circuit the select */
> > return 0;
> > }
> > #endif
>
> _Of_course_ I am just fine with this patch being under a Berkeley-style
> license and included in PostgreSQL.
>
> Cheers.
>
> --
>
> Jack Bates
> Portland, OR, USA
> http://www.floatingdoghead.net
>
> Got privacy?
> My PGP key: http://www.floatingdoghead.net/pubkey.txt
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
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]
Re: [HACKERS] Schemas and template1
> As part of createdb, the new database will have to have it's public > schema changed to world-writable. I have to admit that much of the schema related discussion has been over my head, but I think what I understand you to be saying here is that the default would be to allow anybody to create tables in any database that they connect to, in the same way that they currently can (with pg <= 7.2.1). (If that's not the case, you can ignore the rest of the message.) What value do users get from being able to create temp tables in any database? Don't _most_ people expect databases (from any vendor) to be writable only by the owner? I have to confess that I was surprised when I discovered that others could create tables in my PG database (although I don't have much exposure to other flavors of databases). ISTM that the best default is to have it not world writable, but that will tend to cause some consternation when people transition to 7.3 and discover (as I did) that the current pg_restore may hit snags on a non-world writable DB in certain circumstances. If I put data into a database and want to allow anybody to read it and don't want to worry about administering accounts for hundreds of users, I might create an account that anybody can use to connect. I would be unhappy if someone was able to expand that permission into something like creating tables and filling them so much that it causes problems for me. (As I said, this is all predicated on my understanding at the beginning, so if I've misunderstood this issue then perhaps this wouldn't be a problem for me.) -ron > > -- > 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] > ---(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] Schemas and template1
Ron Snyder wrote: > > As part of createdb, the new database will have to have it's public > > schema changed to world-writable. > > I have to admit that much of the schema related discussion has been over my > head, but I think what I understand you to be saying here is that the > default would be to allow anybody to create tables in any database that they > connect to, in the same way that they currently can (with pg <= 7.2.1). > > (If that's not the case, you can ignore the rest of the message.) The issue I was raising is the creation of tables in the default 'public' schema, which is the one used by users who don't have a schema matching their name. I was saying that template1 should prevent creation of tables by anyone but the superuser. As far as temp tables, I think we should enable that for all non-template1 databases. (In fact, what happens if you create a database while a temp table exists in template1. Seems it would not be cleaned up in the new database.) -- 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] Mac OS X shutdown
We've got an OSX machine set up now, however we haven't had time to look into the problem yet. Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of David Santinoli > Sent: Tuesday, 11 June 2002 12:48 AM > To: [EMAIL PROTECTED] > Subject: [HACKERS] Mac OS X shutdown > > > > Hello, > is there any news about the Mac OS X shutdown issue? > It was discussed in a few April-May/2002 messages with the Subject > "Mac OS X: system shutdown prevents checkpoint". In short, during a > regular system shutdown on Mac OS X the postmaster is not terminated > gracefully, leading to troubles at the successive startup. > All OS X release I know of, up to the latest one (10.1.5), are prone to > this inconvenient. > > Thanks, > David > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas and template1
Bruce Momjian <[EMAIL PROTECTED]> writes: > As part of createdb, the new database will have to have it's public > schema changed to world-writable. That ain't gonna happen, unfortunately. CREATE DATABASE runs in some database other than the target one, so it's essentially impossible for the newly-created DB to contain any internal state that's different from the template DB. Next idea please? 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] Analyze on large changes...
Bruce Momjian <[EMAIL PROTECTED]> writes: > One change in this patch is that because analyze now runs in the outer > transaction, I can't clear the memory used to support each analyzed > relation. Not sure if this is an issue. Seems like a pretty serious (not to say fatal) objection to me. Surely you can fix that. 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] Analyze on large changes...
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > One change in this patch is that because analyze now runs in the outer > > transaction, I can't clear the memory used to support each analyzed > > relation. Not sure if this is an issue. > > Seems like a pretty serious (not to say fatal) objection to me. Surely > you can fix that. OK, suggestions. I know CommandCounterIncrement will not help. Should I do more pfree'ing? -- 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
Re: [HACKERS] Schemas and template1
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > As part of createdb, the new database will have to have it's public > > schema changed to world-writable. > > That ain't gonna happen, unfortunately. CREATE DATABASE runs in some > database other than the target one, so it's essentially impossible for > the newly-created DB to contain any internal state that's different > from the template DB. Next idea please? Yes, there was an even bigger problem with my argument. If someone wanted to make public no-write, and have all created databases inherit from that, it wouldn't work because it would clear that on creation. How about if we hard-wire template1 as being no-write to public somewhere in the code, rather than in the db tables? -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Analyze on large changes...
Bruce Momjian <[EMAIL PROTECTED]> writes: >> Seems like a pretty serious (not to say fatal) objection to me. Surely >> you can fix that. > OK, suggestions. I know CommandCounterIncrement will not help. Should > I do more pfree'ing? No, retail pfree'ing is not a maintainable solution. I was thinking more along the lines of a MemoryContextResetAndDeleteChildren() on whatever the active context is. If that doesn't work straight off, you might have to create a new working context and switch into it before calling the analyze subroutine --- then deleting that context would do the trick. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas and template1
Bruce Momjian <[EMAIL PROTECTED]> writes: > How about if we hard-wire template1 as being no-write to public > somewhere in the code, rather than in the db tables? Seems pretty icky :-( It occurs to me that maybe we don't need to worry. The main reason why we've offered the advice "don't fill template1 with junk" in the past is that it was so hard to clear out the junk without zapping built-in entries. But now, you really have to work hard at it to shoot yourself in the foot that way. If you created junk in template1.public, no sweat: \c template1 postgres DROP SCHEMA public; CREATE SCHEMA public; -- don't forget to set its permissions appropriately (This assumes we get DROP SCHEMA implemented in time for 7.3, but I think we can build that based on Rod's pg_depend stuff.) (Which I really really gotta review and apply soon.) I'm of the opinion that template1 and public are not very special at the moment; the C-level code doesn't think either of them are special, which is why you can drop and recreate them if you have to. We should try not to re-introduce any low-level specialness. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Schemas and template1
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > How about if we hard-wire template1 as being no-write to public > > somewhere in the code, rather than in the db tables? > > Seems pretty icky :-( > > It occurs to me that maybe we don't need to worry. The main reason why > we've offered the advice "don't fill template1 with junk" in the past > is that it was so hard to clear out the junk without zapping built-in > entries. But now, you really have to work hard at it to shoot yourself > in the foot that way. If you created junk in template1.public, no > sweat: > \c template1 postgres > DROP SCHEMA public; > CREATE SCHEMA public; > -- don't forget to set its permissions appropriately > (This assumes we get DROP SCHEMA implemented in time for 7.3, but > I think we can build that based on Rod's pg_depend stuff.) (Which > I really really gotta review and apply soon.) > > I'm of the opinion that template1 and public are not very special > at the moment; the C-level code doesn't think either of them are > special, which is why you can drop and recreate them if you have to. > We should try not to re-introduce any low-level specialness. It is strange we have to allow template1 open just for client stuff. I would really like to lock it down read-only. I guess we can tell admins to lock down public in template1, and all newly created databases will be the same. -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Analyze on large changes...
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >> Seems like a pretty serious (not to say fatal) objection to me. Surely
> >> you can fix that.
>
> > OK, suggestions. I know CommandCounterIncrement will not help. Should
> > I do more pfree'ing?
>
> No, retail pfree'ing is not a maintainable solution. I was thinking
> more along the lines of a MemoryContextResetAndDeleteChildren() on
> whatever the active context is. If that doesn't work straight off,
> you might have to create a new working context and switch into it
> before calling the analyze subroutine --- then deleting that context
> would do the trick.
OK, how is this?
--
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
Index: src/backend/commands/analyze.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/analyze.c,v
retrieving revision 1.35
diff -c -r1.35 analyze.c
*** src/backend/commands/analyze.c 24 May 2002 18:57:55 - 1.35
--- src/backend/commands/analyze.c 12 Jun 2002 03:59:45 -
***
*** 156,170
elevel = DEBUG1;
/*
-* Begin a transaction for analyzing this relation.
-*
-* Note: All memory allocated during ANALYZE will live in
-* TransactionCommandContext or a subcontext thereof, so it will all
-* be released by transaction commit at the end of this routine.
-*/
- StartTransactionCommand();
-
- /*
* Check for user-requested abort. Note we want this to be inside a
* transaction, so xact.c doesn't issue useless WARNING.
*/
--- 156,161
***
*** 177,186
if (!SearchSysCacheExists(RELOID,
ObjectIdGetDatum(relid),
0, 0, 0))
- {
- CommitTransactionCommand();
return;
- }
/*
* Open the class, getting only a read lock on it, and check
--- 168,174
***
*** 196,202
elog(WARNING, "Skipping \"%s\" --- only table or database
owner can ANALYZE it",
RelationGetRelationName(onerel));
relation_close(onerel, AccessShareLock);
- CommitTransactionCommand();
return;
}
--- 184,189
***
*** 211,217
elog(WARNING, "Skipping \"%s\" --- can not process indexes,
views or special system tables",
RelationGetRelationName(onerel));
relation_close(onerel, AccessShareLock);
- CommitTransactionCommand();
return;
}
--- 198,203
***
*** 222,228
strcmp(RelationGetRelationName(onerel), StatisticRelationName) == 0)
{
relation_close(onerel, AccessShareLock);
- CommitTransactionCommand();
return;
}
--- 208,213
***
*** 283,289
if (attr_cnt <= 0)
{
relation_close(onerel, NoLock);
- CommitTransactionCommand();
return;
}
--- 268,273
***
*** 370,378
* entries we made in pg_statistic.)
*/
relation_close(onerel, NoLock);
-
- /* Commit and release working memory */
- CommitTransactionCommand();
}
/*
--- 354,359
Index: src/backend/commands/vacuum.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.226
diff -c -r1.226 vacuum.c
*** src/backend/commands/vacuum.c 24 May 2002 18:57:56 - 1.226
--- src/backend/commands/vacuum.c 12 Jun 2002 03:59:54 -
***
*** 110,117
/* non-export function prototypes */
- static void vacuum_init(VacuumStmt *vacstmt);
- static void vacuum_shutdown(VacuumStmt *vacstmt);
static List *getrels(const RangeVar *vacrel, const char *stmttype);
static void vac_update_dbstats(Oid dbid,
TransactionId vacuumXID,
--- 110,115
***
*** 160,165
--- 158,165
void
vacuum(VacuumStmt *vacstmt)
{
+ MemoryContext anl_context,
+ old_context;
const char *stmttype = vacstmt->vacuum ? "VACUUM" : "ANALYZE";
List *vrl,
*cur;
***
*** 178,190
* user's transaction too, which would certainly not be the desired
* behavior.
*/
! if (IsTransactionBlock())
elog(ERRO
