AW: [HACKERS] Please advise features in 7.1
Reason: I want to know if any of these features are scheduled. 1. Calculated fields in table definitions . eg. Create table test ( A Integer, B integer, the_sum As (A+B), ); This is currently easily done with a procedure that takes a tabletype parameter with the name the_sum returning the sum of a + b. Create table test ( A Integer, B integer ); create function the_sum (test) returns integer as ' begin; return ($1.a + $1.b); end; ' language 'plpgsql'; A select * won't return the_sum, but a select t.a, t.b, t.the_sum from test t; will do what you want. Unfortunately it only works if you qualify the column the_sum with a tablename or alias. (But I heard you mention the Micro$oft word, and they tend to always use aliases anyway) Maybe we could even extend the column search in the unqualified case ? Andreas
Re: [HACKERS] Please advise features in 7.1
At 18:00 23/11/00 +1300, John Huttley wrote: 1. Calculated fields in table definitions . eg. Can't really do this - you might want to consider a view with an insert update rule. I'm not sure how flexible rules are and you may not be able to write rules to make views functions like tables, but that is at least part of their purpose I think. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
[HACKERS] deficiency on delete and update instead rules for views
We lack a syntax that would enable us to write an on update/delete do instead rule that would efficiently map an update/delete to a table that is referenced by a view. Currently the only rule you can implement is one that uses a primary key. This has the disadvantage of needing a self join to find the appropriate rows. Andreas
AW: [HACKERS] deficiency on delete and update instead rules for views
We lack a syntax that would enable us to write an on update/delete do instead rule that would efficiently map an update/delete to a table that is referenced by a view. Currently the only rule you can implement is one that uses a primary key. This has the disadvantage of needing a self join to find the appropriate rows. One of the concepts used in other DBs is to have views with row OIDs/DBKeys: ie. views that have one primary table (but maybe have column selects, calculations and/or function calls) can still have a real row underlying each row. This then allows insert, update delete to work more easily. Doesn't really help now, but it might be useful in a future release. Imho the functionality inside the backend is probably there since old Postgres 4 could do such rules. That is why I said that syntax is missing. Btw, the insert is not a problem, the on insert do instead rules are straight forward to write, at least in the cases where other db's allow an insert on a view. (e.g. on insert to test1 do instead insert into test (a,b) values (new.a, new.b); where test1 has a few extra calculated columns) Andreas
Re: AW: [HACKERS] Please advise features in 7.1
At 12:28 PM 11/23/00 +0100, Zeugswetter Andreas SB wrote: Reason: I want to know if any of these features are scheduled. 1. Calculated fields in table definitions . eg. Create table test ( A Integer, B integer, the_sum As (A+B), ); This is currently easily done with a procedure that takes a tabletype parameter with the name the_sum returning the sum of a + b. Create table test ( A Integer, B integer ); create function the_sum (test) returns integer as ' begin; return ($1.a + $1.b); end; ' language 'plpgsql'; A select * won't return the_sum create view test2 select A, B, A+B as the_sum from test; will, though. See, lots of ways to do it! - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Please advise features in 7.1
At 06:00 PM 11/23/00 +1300, John Huttley wrote: 1. Calculated fields in table definitions . eg. Create table test ( A Integer, B integer, the_sum As (A+B), ); ... These are _extraordinarily_ useful for application development. If anyone has a way of bolting on any of these to 7.0, I'd be keen to hear from you. Create a trigger on insert/update for this case... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
[HACKERS] Import text field
Hi: I have a MS Access database with tables containing TEXT fields. I need import that info in a postgres 7 table. How to do it? If I use copy from, dont work. tia Carlos Jacobs
[HACKERS] Hung backends
Title: Hung backends Hi, I'm new to PostgreSQL and have been asked to determine the cause of what appear to be hung processes on FreeBSD after one or more frontend apps crash. I did alot of searching through the msg lists and found a few discussions that seem related, but I was unable to find a resolution in the msg archives. I noticed the last item in changes for PostgreSQL v7.0.3: Fix for crash of backend, on abort (Tom) Is this related? Our scenario is, a frontend java program creates multiple connections to PostgreSQL v7.0.2 attempting to exceed MAXBACKENDS. If the program crashes(unhandled exception) we're left with hung (or waiting processes) on FreeBSD equal to the number of successful connections (ps log below). Subsequent connection attempts are eventually rejected (when MAXBACKENDS is reached) with Sorry, too many clients already. I've waited for over an hour to see if these processes get cleaned up, but they don't. The only msgs I could dig up that seem like they _could_ be related are a discussion between Dirk Niggemann and Tom Lane in Oct/1999 (timeouts in libpq- can libpq requests block forever/a very long time? - PGTIMEOUT and PGCONNTIMEOUT) - I could be way off the mark on this one though... Thanks for any and all advice. Peter Schmidt postgres@dev-postgres:~ ps -cl -U postgres UID PID PPID CPU PRI NI VSZ RSS WCHAN STAT TT TIME COMMAND 500 1395 1 0 2 0 4040 2380 select Ss ?? 0:01.17 postgres 500 2255 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2256 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2257 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2258 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2259 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2260 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2261 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2262 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2263 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2264 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2265 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2266 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2267 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2268 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2269 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2270 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2271 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2272 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2273 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2274 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2275 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2317 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2318 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2319 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2320 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2321 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2322 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2323 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2324 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2325 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2326 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 2327 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres 500 892 890 0 10 0 1636 1412 wait S p2 0:00.37 bash 500 979 892 0 28 0 1672 1368 - T p2 0:00.02 psql 500 2385 892 0 28 0 440 264 - R+ p2 0:00.00 ps
[HACKERS] Weird backup file
I backed up my database from Postgres 6.5.3 and migrated to 7.0.2
several a few months ago. For some reason, data was lost in the
transition. I've finally pinned it down to the attached file (abridged
to point out the problem).
It looks like two things happened in the backup. First, when I move from
'G' to 'F' in the names column, I seem to lose the column called
'dsp_chan'. Second, the double quotes around the float_4 array called
'spike_hist' aren't included.
I'm not sure if the double quotes are necessary, but the missing column
is probably a problem. I added this column after the database was
created by using 'alter table ellipse_cell_proc add column dsp_chan' and
then put it in the correct position by using:
SELECT name, arm, rep, cycle, hemisphere, area, cell, dsp_chan,
spike_hist INTO xxx FROM ellipse_cell_proc;
DROP TABLE ellipse_cell_proc;
ALTER TABLE xxx RENAME TO ellipse_cell_proc;
Can anyone explain what went wrong with the backup or where I erred
adding the column?
Thanks.
-Tony
\connect - postgres
CREATE TABLE "ellipse_cell_proc" (
"name" text,
"arm" character,
"rep" int4,
"cycle" int4,
"hemisphere" character,
"area" text,
"cell" int4,
"dsp_chan" text,
"spike_hist" "_float4"
);
COPY "ellipse_cell_proc" FROM stdin;
I L 888 2 R 4 173 1
{"0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0.4","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0"}
I L 888 3 R 4 173 1
{"0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0"}
I L 888 4 R 4 173 1
{"0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0"}
I L 888 5 R 4 173 1
{"0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0"}
G R 5 2 L 4 1 1
{"1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.1","1.6","43.9","29.4","36.3","30.4","14","2.2","2.2","2.2","2.2","2.2","2.2","2.2","2.2","2.2","2.2","2.2","2.2","2.2","2.2","2.2","2.2","7","30.3","47.5","48.5","48.2","33.9","35.6","37.8","29.6","23.3","43.7","21.3","26.6","44","39.6","35.5","35.6","46.6","41.6","43","42.1","25.7","22.7","22.7","22.1","21.3","21.9","22.2","36","26.8","29.2","30.6","21.3","22.8","24.4","9","7.6","7.6","7.6","7.6","2","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","1.4","13.3","20.4","8.1","3.9","3.9","3.9","3.9","3.9","3.9","3.9","3.9","3.9","39","10","10","10","15.9","30.5"}
G R 5 3 L 4 1 1
[HACKERS] Re: Add support for xti.h
Tom Lane writes: Pete Forman wrote: The basic problem is that netinet/tcp.h is a BSD header. The correct header for TCP internals such as TCP_NODELAY on a UNIX system is xti.h. By UNIX I mean UNIX95 (aka XPG4v2 or SUSv1) or later. The 2 files which conditionally include netinet/tcp.h need also to conditionally include xti.h. I've done bit more research. xti.h was the correct place to find TCP_NODELAY in UNIX98/SUSv2. However in the Austin Group draft of the next version of POSIX and UNIX0x/SUSv3, XTI has been dropped and netinet/tcp.h officially included. I have never heard of xti.h before and am rather dubious that it should be considered more standard than tcp.h. However, if we are going to include it then it evidently must be *mutually exclusive* with including tcp.h. The $64 question is, which one ought to be included when both are available? I'd tend to go for tcp.h on the grounds of "don't fix what wasn't broken". Actually, given your description of the problem, I'm half inclined to revert the whole patch and instead make configure's test for availability of netinet/tcp.h first include netinet/in.h, so that that configure test will succeed on IRIX etc. Do you know any platforms where tcp.h doesn't exist at all? I agree with this. Back out the patch and update configure.in. I might have done that myself but I do not have enough experience with autoconf. The only platform I know of without netinet/tcp.h is Cygwin B20.1. There is a workaround in place for that. The current Cygwin 1.1 does have the header. -- Pete Forman -./\.- Disclaimer: This post is originated Western Geophysical -./\.- by myself and does not represent [EMAIL PROTECTED] -./\.- the opinion of Baker Hughes or http://www.crosswinds.net/~petef -./\.- its divisions.
[HACKERS] PostgreSQL as windows 2000 service
Hi: Wonder if any of you know how to setup a postgreSQL server as a windows 2000 service or have a URL or document on how to do it. Thank you -- Luis Magaña Gnovus Networks Software www.gnovus.com Tel. +52 (7) 4422425 [EMAIL PROTECTED]
[HACKERS] Fw: DB and Table Permissions
I sent this to the general list and got no response so I figure I can take it to the people who actually make the decisions. Is this a security bug or is it by design? - Original Message - From: "Dan Wilson" [EMAIL PROTECTED] To: "pgsql general" [EMAIL PROTECTED] Sent: Sunday, November 19, 2000 9:33 AM Subject: DB and Table Permissions Is there a reason why _any_ user can create a table on a database? Even if they do not own or have any permissions to it? I don't think that should happen. Is there a specific reason why it does? -Dan Wilson
Re: [HACKERS] Table/Column Constraints
Christopher Kings-Lynne wrote: Speaking of - I simply cannot find a standard SQL specification anywhere on the net, without buying one from ANSI. I'm forced to rely on vendor-specific docs - which are not standard in any way. Is anyone able to mail me such a thing? You may want to take a look through http://www.techstreet.com -- I searched standards for the keyword 'database', and found that many of the SQL documents were available as PDFs for $18.00 each. -- Karl DeBisschop[EMAIL PROTECTED] Learning Network Reference http://www.infoplease.com Netsaint Plugin Developer [EMAIL PROTECTED]
[HACKERS] Re: query plan optimizer bug
xuyifeng ([EMAIL PROTECTED]) wrote: it's obviously there is a query plan optimizer bug, if int2 type used in fields, the plan generator just use sequence scan, it's stupid, i am using PG7.03, this is my log file: - stock# drop table a; DROP stock# create table a(i int2, j int); CREATE stock# create unique index idx_a on a(i, j); CREATE stock# explain select * from a where i=1 and j=0; psql:test.sql:4: NOTICE: QUERY PLAN: Seq Scan on a (cost=0.00..25.00 rows=1 width=6) EXPLAIN stock# drop table a; create table a(i int, j int); CREATE stock# create unique index idx_a on a(i, j); CREATE stock# explain select * from a where i=1 and j=0; psql:test.sql:8: NOTICE: QUERY PLAN: Index Scan using idx_a on a (cost=0.00..2.02 rows=1 width=8) EXPLAIN --- This actually appears to be a bug in the auto-casting mechanism (or the parser, or something): kevin=# explain select * from a where i = 1 and j = 0; NOTICE: QUERY PLAN: Seq Scan on a (cost=0.00..25.00 rows=1 width=6) EXPLAIN kevin=# explain select * from a where i = '1' and j = '0'; NOTICE: QUERY PLAN: Index Scan using idx_a on a (cost=0.00..2.02 rows=1 width=6) EXPLAIN This behavior appears to happen for int8 as well. -- Kevin Brown [EMAIL PROTECTED] It's really hard to define what "anomalous behavior" means when you're talking about Windows.
Re: [HACKERS] syslog output from explain looks weird...
* Larry Rosenman [EMAIL PROTECTED] [001123 01:10]: * Tom Lane [EMAIL PROTECTED] [001122 22:44]: Makes sense. Here's a new patch, now the output even looks better: Nov 23 00:58:04 lerami pg-test[9914]: [2-1] NOTICE: QUERY PLAN: Nov 23 00:58:04 lerami pg-test[9914]: [2-2] Nov 23 00:58:04 lerami pg-test[9914]: [2-3] Seq Scan on upsdata (cost=0.00..2766.62 rows=2308 width=48) Nov 23 00:58:04 lerami pg-test[9914]: [2-4] [snip] Any comments from the committers crowd? (I can't commit it...) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] last built-in oid
At 11:27 24/11/00 +0800, Christopher Kings-Lynne wrote: SELECT datlastsysoid from pg_database where datname = 'dbname' But as far as I can tell, the datlastsysoid field does not exist in pg_database. If you build from CVS and do an initdb, you will find datlastsysoid should exist... Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
RE: [HACKERS] PostgreSQL as windows 2000 service
There's bound to be a better way, but in the NT resource kit there was a tool you can use to make any .exe a service. I have a bash script running under Cygwin as a service here using it. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: [EMAIL PROTECTED] WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -Original Message- From: Luis =?UNKNOWN?Q?Maga=F1a?= [mailto:[EMAIL PROTECTED]] Sent: Monday, November 20, 2000 5:24 PM To: [EMAIL PROTECTED] Subject: [HACKERS] PostgreSQL as windows 2000 service Hi: Wonder if any of you know how to setup a postgreSQL server as a windows 2000 service or have a URL or document on how to do it. Thank you -- Luis Magaña Gnovus Networks Software www.gnovus.com Tel. +52 (7) 4422425 [EMAIL PROTECTED]
[HACKERS] Enum type emulation: problem with opaque type in PL/pgSQL functions
Guys, hello.
Here is a problem.
--
-- Creating 2 new functions and new type
--
BEGIN;
CREATE FUNCTION enum_week_in (opaque)
RETURNS int2
AS '
DECLARE
invalue ALIAS for $1;
BEGIN
IF invalue= OR invalue=''0'' THEN RETURN 0; END IF;
IF invalue=''Monday'' OR invalue=''1'' THEN RETURN 1; END IF;
IF invalue=''Tuesday'' OR invalue=''2'' THEN RETURN 2; END IF;
IF invalue=''Wednesday'' OR invalue=''3'' THEN RETURN 3; END IF;
RAISE EXCEPTION ''incorrect input value: %'',invalue;
END;'
LANGUAGE 'plpgsql'
WITH (ISCACHABLE);
CREATE FUNCTION enum_week_out (opaque)
RETURNS text
AS '
DECLARE
outvalue ALIAS for $1;
BEGIN
IF outvalue=0 THEN RETURN ; END IF;
IF outvalue=1 THEN RETURN ''Monday''; END IF;
IF outvalue=2 THEN RETURN ''Tuesday''; END IF;
IF outvalue=3 THEN RETURN ''Wednesday''; END IF;
RAISE EXCEPTION ''incorrect output value: %'',outvalue;
END;'
LANGUAGE 'plpgsql'
WITH (ISCACHABLE);
CREATE TYPE enum_week (
internallength = 2,
input = enum_week_in,
output = enum_week_out,
PASSEDBYVALUE
);
COMMIT;
Well, all is ok after it, e.g. functions and type were registered in system catalog.
Now, when I try to do "SELECT enum_week_in('Monday')", I get the following:
NOTICE: plpgsql: ERROR during compile of enum_week_in near line 0
The same will occure if I
CREATE TABLE test (wday enum_week);
insert into test (wday) values ('Monday')
If I redefine the same functions with input argtype 'text'/'int2' they work fine.
I guess the problem is that PL/pgSQL doesn't handle opaque type correctly.
Any ideas ?
I don't care how but I need to emulate ENUM type, just to convert MySQL dumps to
PostgreSQL. E.g. ENUM values
stored in MySQL dump should be restorable in Postgres without any conversion.
I running PostgreSQL 7.0.3 on Linux RedHat 6.2, kernel 2.2.15, Intel Celeron CPU;
Postgres was
upgraded from 7.0.2 without changing anything in system catalog.
Thanks,
Max Rudensky.
