[BUGS] select cash_out('2'); crashes backend on 7.0.2
Hello,
I was just experimenting, trying to see if I could find a function that
would format a numeric value like 'money' with Postgres 7.0.2. Here's
what happened:
##
cascade=> select cash_out(2);
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
##
The same thing happened with Postgres 6.5.3. Here's my full version:
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
I'm sure if what I tried is even valid input, but I'm guessing this is
not a desired result in any case. :)
Thanks for the great software and good luck with this!
A frequent Postgres user,
-mark
personal website } Summersault Website Development
http://mark.stosberg.com/{ http://www.summersault.com/
[BUGS] possible mis-handling of nulls in views in 7.0.2
Hello,
I'm running Postgres 7.0.2 and have run into a curious situation. I
got a back a null value in a select on VIEW that is defined as not
allowing that column to be null. Here's a screenshot:
marvel=> \d shipments;
View= shipments
Query = SELECT "web_data"."shipment_id", "web_data"."order_id",
"web_data"."customer_id", "web_data"."purchase_order_num", "web_data"."actual_ship
_date", "web_data"."pro_num", "sum"("web_data"."qt_ordered") AS
"qt_ordered", "sum"("web_data"."qt_shipped") AS "qt_shipped" FROM
"web_data" WHERE (
"web_data"."shipment_id" NOTNULL) GROUP BY "web_data"."shipment_id",
"web_data"."order_id", "web_data"."customer_id", "web_data"."actual_ship_date",
"web_data"."pro_num", "web_data"."purchase_order_num";
+--+--+---+
| Field | Type| Length|
+--+--+---+
| shipment_id | varchar()|
32 |
| order_id | varchar()|
100 |
| customer_id | varchar()|
10 |
| purchase_order_num | varchar()|
100 |
| actual_ship_date | date |
4 |
| pro_num | varchar()|
100 |
| qt_ordered | float8 |
8 |
| qt_shipped | float8 |
8 |
+--+--+---+
marvel=>
### Notice that the shipment_id is NOTNULL
### now watch:
marvel=> select * from shipments where shipment_id is null;
shipment_id|order_id|customer_id|purchase_order_num|actual_ship_date|pro_num|qt_ordered|qt_shipped
---++---+--++---+--+--
|| | ||
| |
(1 row)
#
It returns a row with a null shipment id!
I'm not sure what's happening here-- I tried to simplify this to a
simple case, but I couldn't reproduce the bug. Oddly, this null row
doesn't seem to appear in the table web_data that the view references. I
think it's easy enough to work around, but I'm curious what might be
happening here.
Thanks,
-mark
personal website } Summersault Website Development
http://mark.stosberg.com/{ http://www.summersault.com/
---(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: [BUGS] possible mis-handling of nulls in views in 7.0.2
Tom, I tried to build a simple test case and I couldn't reproduce either. I'm still actively working with that database, though-- If I run into it again, and can reproduce a condensed case, I'll definitely submit it. It sounds like views are being improved a good deal in 7.1, so perhaps my oddity would be repaired anyway. Thanks for checking it out. -mark http://mark.stosberg.com/ Tom Lane wrote: > > Mark Stosberg <[EMAIL PROTECTED]> writes: > >I'm running Postgres 7.0.2 and have run into a curious situation. I > > got a back a null value in a select on VIEW that is defined as not > > allowing that column to be null. > > I think this is an artifact of the curious (not to say broken) > implementation of views pre-7.1. However, it's hard to tell for sure > because I can't reproduce your problem. Are you sure you are running > 7.0.2 and not something older? Can you provide a self-contained > example? My test went like this: > > play=> select version(); > version > -- > PostgreSQL 7.0.2 on hppa2.0-hp-hpux10.20, compiled by gcc 2.95.2 > (1 row) > > play=> create table foo (f1 int, f2 int); > CREATE > play=> insert into foo values(1,2); > INSERT 873546 1 > play=> insert into foo values(1,3); > INSERT 873547 1 > play=> insert into foo values(2,4); > INSERT 873548 1 > play=> insert into foo values(2,5); > INSERT 873549 1 > play=> create view v2 as select f1,sum(f2) from foo where f1 notnull group by f1; > CREATE 873571 1 > play=> select * from v2 ; > f1 | sum > +- > 1 | 5 > 2 | 9 > (2 rows) > > play=> select * from v2 where f1 isnull; > f1 | sum > +- > (0 rows) > > regards, tom lane -- http://mark.stosberg.com/ ---(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
[BUGS] Using nulls with earthdistance operator crashes backend
Hello! Here's now to reproduce my bug: * Start with Postgres 7.1.2 (specifically: PostgreSQL 7.1 on i386--freebsd4.3, compiled by GCC 2.95.3 ) * Install earthdistance operator from the contrib directory. * try this: cascade=> select null <@> '1,1'::point; ## The result I get: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ### I expected simply for "null" to be returned as the result. I can work around this by including an extra step to make sure that my data is not null before it's passed off a SQL statement like this. Thanks! -mark http://mark.stosberg.com/ ---(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: [BUGS] Using nulls with earthdistance operator crashes backend
I'll give it a shot and post the patch or let you know I'm stuck. :) Thanks! -mark Tom Lane wrote: > > Mark Stosberg <[EMAIL PROTECTED]> writes: > > * Install earthdistance operator from the contrib directory. > > * try this: > > cascade=> select null <@> '1,1'::point; > > > ## The result I get: > > pqReadData() -- backend closed the channel unexpectedly. > > Probably the earthdistance functions are not NULL-safe and need to be > marked "isStrict" in CREATE FUNCTION. Would you be willing to do the > legwork on working up a patch for that? > > 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]) -- http://mark.stosberg.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Using nulls with earthdistance operator crashes backend (patch)
> Tom Lane wrote:
> >
> > Mark Stosberg <[EMAIL PROTECTED]> writes:
> > > * Install earthdistance operator from the contrib directory.
> > > * try this:
> > > cascade=> select null <@> '1,1'::point;
> >
> > > ## The result I get:
> > > pqReadData() -- backend closed the channel unexpectedly.
> >
> > Probably the earthdistance functions are not NULL-safe and need to be
> > marked "isStrict" in CREATE FUNCTION. Would you be willing to do the
> > legwork on working up a patch for that?
Tom,
Here's a patch using "isstrict":
--- earthdistance.sql.in.orgThu Aug 16 17:08:19 2001
+++ earthdistance.sql.inThu Aug 16 17:09:01 2001
@@ -3,7 +3,8 @@
DROP FUNCTION geo_distance (point, point);
CREATE FUNCTION geo_distance (point, point) RETURNS float8
- AS 'MODULE_PATHNAME' LANGUAGE 'c';
+ AS 'MODULE_PATHNAME' LANGUAGE 'c'
+ WITH (isstrict);
SELECT geo_distance ('(1,2)'::point, '(3,4)'::point);
#
Now when I run the "crasher" SQL above, I get one empty row back:
sumsault_test=# select null <@> '1,1'::point;
?column?
--
(1 row)
#
I look forward to seeing you at the Open Source Database Summit!
-mark
. . . . . . . . . . . . . . . . . . . . . . . . . .
Mark Stosberg Principal Developer
[EMAIL PROTECTED] Summersault, LLC
v: 765-939-9301 ext 223website development
. . . . . http://www.summersault.com/ . . . . . . .
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] 7.3b2 initdb fails with semaphore error
Hello, I just tried installing 7.3b2 on FreeBSD 4.6.2 and received the following error: oot@asana> su -l pgsql -c initdb The files belonging to this database system will be owned by user "pgsql". This user must also own the server process. The database cluster will be initialized with locale C. creating directory /usr/local/pgsql/data... ok creating directory /usr/local/pgsql/data/base... ok creating directory /usr/local/pgsql/data/global... ok creating directory /usr/local/pgsql/data/pg_xlog... ok creating directory /usr/local/pgsql/data/pg_clog... ok creating template1 database in /usr/local/pgsql/data/base/1... IpcSemaphoreCreate: semget(key=1, num=17, 03600) failed: No space left on device This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter (currently 32). The PostgreSQL Administrator's Guide contains more information about configuring your system for PostgreSQL. initdb failed. ### I think the bug here is that this message advertises that I can change the "max_connections" parameter to address this, but there does not appear to be a way to do this. The max_connections option is located on my system at /usr/local/share/postgresql/postgresql.conf.sample (prior to initdb) However, initdb appears to only copy the file and not actually use its parameters. If it /is/ possible to adjust max_connections prior or during the "initdb" stage, I think there is a documentation bug-- the location of that documentation should be spit out along with the above error message, along with being accessible through "initdb --help" and perhaps elsewhere. I look forward to another great release. Thanks! -mark http://mark.stosberg.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] 7.3b2 initdb fails with semaphore error (solved)
On Tue, 1 Oct 2002, Tom Lane wrote: > Mark Stosberg <[EMAIL PROTECTED]> writes: > > I think the bug here is that this message advertises that I can change > > the "max_connections" parameter to address this, but there does not > > appear to be a way to do this. The max_connections option is located > > on my system at /usr/local/share/postgresql/postgresql.conf.sample > > (prior to initdb) > > Hmm ... actually, I don't think the max_connections parameter is used > during standalone operation. It looks like the code uses a hardwired > value of "16". We could reduce that (there's probably no good reason > why it's not "1"), but I suspect your SEMMAX parameter is so small > it will fail anyway :-( > > Would you try changing "16" to "1" in InitCommunication() in > src/backend/utils/init/postinit.c, and see if that helps on your > setup? Tom, I tried this change, and was able to successfully "initdb" after that, and then run "psql" after that. I'm running this installation on a home machine for light use, so I may not need 16 backends anyway. If you're correct that there is no need to have more than 1 backend during "initdb", then perhaps this could be turned into a patch. My simple patch is below: -mark http://mark.stosberg.com/ --- postinit.c.orig Wed Oct 2 12:56:13 2002 +++ postinit.c Wed Oct 2 12:56:42 2002 @@ -176,7 +176,7 @@ * postmaster. Create private "shmem" and semaphores. Setting * MaxBackends = 16 is arbitrary. */ - CreateSharedMemoryAndSemaphores(true, 16, 0); + CreateSharedMemoryAndSemaphores(true, 1, 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
[BUGS] suggestion: fix 'now' -> CURRENT_TIMESTAMP
Hello,
I'm in the process of migrating dozens of databases from 7.1 to 8.0.
It's been a great opportunity to become familiar with all the things
that have changed in the meantime.
Of of those things is the meaning 'now', as documented in the 7.4
release notes:
http://www.postgresql.org/docs/8.0/interactive/release-7-4.html
( Search for 'now' on the page to find the related docs. ).
When dumping from 7.1 and restoring into 8.0, working code is being
created in the cases I'm looking at, because these construct is put in
the dump file, and then imported verbatim:
date("timestamp"('now'::text))
"timestamp"('now'::text)
This these mean the exact same thing as:
CURRENT_DATE
CURRENT_TIMESTAMP
( But not the same thing as a bare 'now' ).
Why not make the translation on the fly, since using 'now' and
timestamp() are not recommended practices anyway ?
I have seen that PostgreSQL has already taken the liberty to rewrite
"serial" and other schema constructions when they are dumped or
imported, so I see no problem with rewriting code to equivalent, but
better style.
For now I'm doing find & replace on the dump files as a workaround.
Mark
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] suggestion: fix 'now' -> CURRENT_TIMESTAMP
On 2005-09-23, Tom Lane <[EMAIL PROTECTED]> wrote:
> Mark Stosberg <[EMAIL PROTECTED]> writes:
>> Why not make the translation on the fly, since using 'now' and
>> timestamp() are not recommended practices anyway ?
>
> Because we can't retroactively fix 7.1.
That fact hadn't escaped me.
I was thinking that in *8*.1, the parsing of "CREATE TABLE" could be
altered to recognize the old syntax and improve it on the fly.
Since the meaning is identical, it seems like a reasonable improvement
to me.
This kind of rewriting is apparently already happening, because when I declare
a column as "serial", it's immediately translated into a different
representation.
test=# create table t (c1 serial);
test=# \d t
Table "public.t"
Column | Type | Modifiers
+-+---
c1 | integer | not null default nextval('public.t_c1_seq'::text)
Mark
---(end of broadcast)---
TIP 6: explain analyze is your friend
[BUGS] BUG #5734: autovacuum_enabled input should be validated, standardized.
The following bug has been logged online: Bug reference: 5734 Logged by: Mark Stosberg Email address: m...@summersault.com PostgreSQL version: 9.0.1 Operating system: FreeBSD Description:autovacuum_enabled input should be validated, standardized. Details: The "autovacuum_enabled" storage parameter claims to be a boolean type: http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETAB LE-STORAGE-PARAMETERS ... but it fails to behave a normal boolean. Normally, you could set a boolean with a value of false, 'off' or 'f', but you would always get back a value of 'f' With this value, there is no translation. I think this kind of boolean should be handled like a standard PostgreSQL boolean. I noticed because the Slony code base has a hardcoded check for "autovacuum_enabled=off", when a false value could also be stored as autovacuum_enabled=f We should be able to rely on this value being always returned as "autovacuum_enabled='f'" just a normal boolean would. Mark -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
