Re: [BUGS] [INTERFACES] libecpg (8.0 and CVS) hits a gcc bug on powerpc and amd64 (crash)
Patch committed. Thanks. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1546: Temp table isn't deleted at the end of a
On Tue, 15 Mar 2005, Oliver Siegmar wrote: > Here's a demonstration of the bug: > > CREATE FUNCTION testfunction() > RETURNS void > AS ' > BEGIN > CREATE TEMP TABLE testtable (field int4) ON COMMIT DROP; > > INSERT INTO testtable (field) VALUES (1); > > -- DROP TABLE testtable; > > RETURN; > END; > ' LANGUAGE 'plpgsql'; > > > database=# SELECT testfunction(); > > testfunction > -- > > (1 row) > > database=# SELECT testfunction(); > ERROR: relation with OID 29308882 does not exist > CONTEXT: SQL statement "INSERT INTO testtable (field) VALUES (1)" > PL/pgSQL function "testfunction" line 4 at SQL statement Given the error message, this seems to be the whole plpgsql caches query plans but we don't invalidate those plans when there are schema changes. In all currently released versions you pretty much need to use EXECUTE on any queries where the table may go away, for example, any use of temp tables. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1546: Temp table isn't deleted at the end of a
On Fri, 18 Mar 2005, Oliver Siegmar wrote: > On Friday 18 March 2005 14:29, Stephan Szabo wrote: > > Given the error message, this seems to be the whole plpgsql caches query > > plans but we don't invalidate those plans when there are schema changes. > > I already tried to execute the 'CREATE TEMP TABLE' statement using EXECUTE to > avoid cache problems - same problem! You have to EXECUTE the insert as well. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Possible temp table bug in PostgreSQL 7.4.7 / 8.0.1
Uh, have you read the FAQ item about plpgsql and temporary tables? --- Oliver Siegmar wrote: > Hello, > > I've probably found a temp table bug in PostgreSQL (tested with 7.4.7 and > 8.0.1 on Linux x86). > > > Here's a demonstration of the bug: > > CREATE FUNCTION testfunction() > RETURNS void > AS ' > BEGIN > CREATE TEMP TABLE testtable (field int4) ON COMMIT DROP; > > INSERT INTO testtable (field) VALUES (1); > > -- DROP TABLE testtable; > > RETURN; > END; > ' LANGUAGE 'plpgsql'; > > > database=# SELECT testfunction(); > > testfunction > -- > > (1 row) > > database=# SELECT testfunction(); > ERROR: relation with OID 29308882 does not exist > CONTEXT: SQL statement "INSERT INTO testtable (field) VALUES (1)" > PL/pgSQL function "testfunction" line 4 at SQL statement > > > No transaction has been started manually. > > If I drop the temporary testtable manually within the PL/pgSQL function, > everything runs fine. Bug or feature? ;-) > > > Cheers, > Oliver > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1549: initdb doesn't work
Daniel van Eeden wrote: > > The following bug has been logged online: > > Bug reference: 1549 > Logged by: Daniel van Eeden > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0.1 > Operating system: Debian GNU/Linux for SPARC (sarge) > Description:initdb doesn't work > Details: > > creating template1 database in /opt/postgresql/data/base/1 ... FATAL: > semctl(10977333, 16, SETVAL, 536) failed: Invalid argument > child process exited with exit code 1 > initdb: removing data directory "/opt/postgresql/data" That is a problem with semaphores in your kernel. Have you modified your postgresql.conf from the default? If so, you should read the PostgreSQL documentation on how to modify your kernel for larger settings. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1549: initdb doesn't work
Bruce Momjian writes: > Daniel van Eeden wrote: >> creating template1 database in /opt/postgresql/data/base/1 ... FATAL: >> semctl(10977333, 16, SETVAL, 536) failed: Invalid argument > That is a problem with semaphores in your kernel. Have you modified > your postgresql.conf from the default? If so, you should read the > PostgreSQL documentation on how to modify your kernel for larger > settings. But it's failing at semctl() not semget(). So it seems he hasn't run out of semaphores. I wonder if SEMVMX is unusually small on his setup (ie, less than 536). My Linux semctl man page says that that case would return ERANGE not EINVAL, but ... 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: [BUGS] BUG #1541: Unusually long INSERT times after fresh clean/CREATE TABLES
On 2005-03-14, Tom Lane <[EMAIL PROTECTED]> wrote: > The 8.0 planner is intentionally sensitive to the current actual > physical sizes of tables. It sounds like you've managed to get it to > plan something on the assumption that the tables are tiny and keep > using that plan after they aren't tiny any more. The old planner had > the same kind of issue but it was far easier to hit, so "revert that > change" isn't an answer that I'm particularly interested in. On the irc channel we were just investigating a very similar case to this (which will show up as a bug report here in due course, though not from me). It turns out that the scenario above is trivial to hit in 8.0 using referential constraints; RI triggers cache their plans, and on 8.0 the RI query is planned as a seqscan if the tables are freshly created. (On 7.4 the plan is an index scan, thanks to the default 1000 rows / 10 pages stats.) What this basically means is that you can't do even a modest size insert of new data into a fresh schema without having to either trick the planner (e.g. enable_seqscan=false before the first insert) or defer the addition of constraints until after the data load (which can be quite inconvenient in some cases). -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Roy Badami wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1518
> Logged by: Roy Badami
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.0.1
> Operating system: Solaris 9
> Description:Conversions to (undocumented) SQL year-month and
> day-time interval types silently discard data
> Details:
I have finally found time to research your issues:
> Conversions to the (undocumented) SQL year-month and day-time intervals
Yes, I noticed that. Once I outline is behavior we need to revisit
that.
> silently discard data, instead of raising an exception.
Yep, noticed that too.
Looking at your examples, it looks terrible, but after researching it,
it isn't too bad, so let me lay out the information and we can decide
how to handle it.
First, the fundamental issue with intervals is that they are not tied to
a particular date, meaning there is no way to map a specific number of
days to a number of months. (Some days are also 23 or 25 hours but that
variability seems to be considered acceptable.)
This is why the interval data type store both seconds and months.
I ran a few tests using constants, which is clearer:
test=> select (current_timestamp - 'epoch'::timestamp)::interval;
interval
---
12860 days 19:24:13.854829073
(1 row)
Notice it shows only days and time, not any years or months because it
doesn't actually know how many years or months.
> Note, the following examples intentinally use non-standard interval syntax,
> since SQL standard interval syntax appears to be broken...
>
> radius=# create table foo (year_month interval year to month);
> CREATE TABLE
> radius=# insert into foo values ('1 year 1 month');
> INSERT 19963 1
> radius=# select * from foo;
> year_month
> --
> 1 year 1 mon
> (1 row)
>
> -- correct
Should this be "mon" or "month"?
> radius=# insert into foo values ('1 hour 1 minute');
> INSERT 19964 1
>
> -- should be an error, I think?
The problem is that an interval restriction controls storage, but does
not invalidate input. The only good way to do that is with CHECK and
"date_trunc() != val".
In fact, the query below shows that the time information that is outside
the requested range is not even stored:
test=> select (current_timestamp - 'epoch'::timestamp)::interval year
to month::interval;
interval
--
00:00:00
(1 row)
> radius=# select * from foo;
> year_month
> --
> 1 year 1 mon
> 00:00:00
> (2 rows)
>
> -- but instead the interval has been replaced by a zero interval
What is confusing here is that instead of printing nothing, it prints a
zero time. Should it print something different, perhaps "0 mons"?
> radius=# create table bar (day_time interval day to second);
> CREATE TABLE
> radius=# insert into bar values ('1 hour 1 minute');
> INSERT 19968 1
> radius=# select * from bar;
> day_time
> --
> 01:01:00
> (1 row)
>
> -- correct
>
> radius=# insert into bar values ('1 year 1 month');
> INSERT 19969 1
>
> -- should be an error, I think?
>
> radius=# select * from bar;
> day_time
> --
> 01:01:00
> 00:00:00
> (2 rows)
>
> -- but instead has been converted to a zero interval
Again, the interval is zero so it prints zero time.
Does this help?
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1541: Unusually long INSERT times after fresh clean/CREATE TABLES
Andrew - Supernews <[EMAIL PROTECTED]> writes: > It turns out that the scenario above is trivial to hit in 8.0 using > referential constraints; RI triggers cache their plans, and on 8.0 the RI > query is planned as a seqscan if the tables are freshly created. (On 7.4 > the plan is an index scan, thanks to the default 1000 rows / 10 pages stats.) Hm. One thing we could do is to throw in some default values when we see the table has exactly zero pages --- perhaps ye olde traditional 1000/10, or possibly something else, but anyway not exactly 0/0. The reason I thought we didn't need to do this sort of hack anymore is that pg_dump loads the tables first and then creates the RI constraints. What exactly is the common case where the wrong thing happens? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Roy Badami wrote: > > The following bug has been logged online: > > Bug reference: 1517 > Logged by: Roy Badami > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0.1 > Operating system: Solaris 9 > Description:SQL interval syntax is accepted by the parser, but the > interpretation is bogus > Details: > > The parser accepts SQL interval syntax, but then silently ignores it, > treating it as a zero interval. > > radius=# select date '2005-01-01' + interval '1' month; > ?column? > - > 2005-01-01 00:00:00 > (1 row) > > radius=# select timestamp '2005-01-1 00:00:00' + interval '1' minute; > ?column? > - > 2005-01-01 00:00:00 > (1 row) Well, that certainly belongs in the 'bizarre' category. It should not accept that syntax. It should require the 'month' or 'minute' to be in single quotes. This is wrong: test=> select date '2005-01-01' + interval '1' month; ?column? - 2005-01-01 00:00:00 (1 row) This is right: test=> select date '2005-01-01' + interval '1 month'; ?column? - 2005-02-01 00:00:00 (1 row) In fact when the 'month' is outside the quotes, it modifies the 'interval', like this: test=> select date '2005-01-01' + interval '1 year' year to month; ?column? - 2006-01-01 00:00:00 (1 row) and in fact the '1' is taken to be 1 second: test=> select date '2005-01-01' + interval '1'; ?column? - 2005-01-01 00:00:01 (1 row) So, in fact these work just fine: test=> select date '2005-01-01' + interval '1' second; ?column? - 2005-01-01 00:00:01 (1 row) test=> select date '2005-01-01' + interval '1' hour to second; ?column? - 2005-01-01 00:00:01 (1 row) Do we need help in this area? Yes. Where? I don't know. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Bruce Momjian writes: > Well, that certainly belongs in the 'bizarre' category. It should not > accept that syntax. It should require the 'month' or 'minute' to be in > single quotes. No, it shouldn't; read the SQL spec. AFAICS the syntax select interval '1' month is perfectly spec-compliant. The variant select interval '1 month' is *not* per-spec, it is a Postgres-ism. Tom Lockhart was working on this stuff shortly before he decided that raising horses was a more interesting use of his spare time. It doesn't look like he ever quite finished. I tried several back versions of Postgres to see if it had ever operated correctly and the answer seems to be "no" :-( ... although we have managed to fail in more than one way over the years ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
