Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
On Tue, 23 Oct 2001 17:16:06 +0200, you wrote: CREATE OR DROP VIEW Is this for real? If I were a database server I would say to the client please make up your mind :-) Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
CREATE OR DROP VIEW Is this for real? If I were a database server I would say to the client please make up your mind :-) I meant DROP IF EXISTS and then CREATE. This is more simple to implement than CREATE OR REPLACE. Best regards, Jean-Michel POURE ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] HISTORY file
I find the HISTORY file to be distressingly poor to peruse. Reasons: A large proportion of the items don't convey any useful information. Examples: | PLpgSQL fix for SELECT... FOR UPDATE (Tom) What did this fix? Does SELECT FOR UDPATE now work whereas it didn't use to? = SELECT ... FOR UPDATE now works in PL/pgSQL | Fix for PL/pgSQL PERFORM returning multiple rows (Tom) What did this fix? Can you return multiple rows now or does it merely give an error message that you cannot where it used to crash? | Fix for inherited CHECK constraints (Stephan Szabo) ditto | PL/pgSQL Allow IS and FOR in cursors (Bruce) If I didn't happen to know exactly what this meant, I wouldn't have a clue. | Allow NULL to appear at beginning/end based on ORDER BY (Tom) It doesn't allow, it just does. | Pltcl add spi_lastoid capability ([EMAIL PROTECTED]) Capability = command, function, type, ...? | Allow column renaming in views ALTER VIEW foo RENAME COLUMN -- huh? | New option to output SET SESSION AUTHORIZATION commands (Peter E) Option to what to output where? | New postgresql.conf option to enable/disable col = NULL comparisons This is not correct. | Cachability fixes (Thomas, Tom) I don't think cachability as such was fixed, or even changed. The item probably related to some iscacheable pg_proc entries which were temporarily broken. The categories Bug Fixes, Enhancements, Types, Performance, Interfaces, Source Code could be split better, and they're not used very consistently. An example from each category that doesn't fit: Bug Fixes: Disallow access to pg_statistic for non-super user (Tom) This was not a bug, but a consequence of a change. Enhancements: Fix TCL COPY TO/FROM (ljb) If it is fixed then it was broken before. Types: New function bit_length() (Peter E) No comment. Performance: Dynahash portability improvements (Tom) Interfaces: Obviously, anything done in the interfaces is also either a bug fix or an enhancement. And what exactly constitutes an interface is not clear to me. Source code: Remove OID's from some system tables (Tom) Maybe this is an enhancement. Some changes are must know, because they are incompatible, such as | Load pg_hba.conf only on startup and SIGHUP (Bruce) This should be made clear somewhere. Finally, | Remove configure --enable-pltcl-utf option There was never such an option in a previous release. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bug (?) with RULEs with WHERE
Kovacs Zoltan [EMAIL PROTECTED] writes: foo=# CREATE TABLE a(foo integer); CREATE foo=# CREATE TABLE b(foo integer); CREATE foo=# CREATE VIEW c AS SELECT foo FROM a; CREATE foo=# CREATE RULE d AS ON INSERT TO c WHERE new.foo=5 DO INSTEAD SELECT foo FROM b; CREATE foo=# INSERT INTO c VALUES (5); ERROR: Cannot insert into a view without an appropriate rule You didn't provide a rule covering the new.foo5 case. In practice, you *must* have an unconditional INSTEAD rule present for any view operation you want to allow. It can be DO INSTEAD NOTHING, and then you can do all your useful work in conditional rules, but the unconditional rule must be there. Else the system thinks that perhaps the insert into the view would really happen. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] consistent naming of components
Robert Dyas [EMAIL PROTECTED] writes: [ rename and move just about everything in sight ] Sorry, but I don't think this is going to happen. We'd be breaking a heck of a lot of user applications, startup scripts, etc to achieve (IMHO) very little of value. Renaming psql-pgsql would alone break more user scripts than I care to think about. change data location /var/lib/pgsql/data to /var/pgsql move .conf files from /var/lib/pgsql/data to /etc/pgsql The present sources do not have any hardwired notion of where things should go. If you care to install things in those directories, you can --- but you won't get far insisting that everyone else should do likewise. Preferred filesystem organization varies across platforms. Even if it didn't, there are situations such as running multiple postmasters (eg, setting up a test version) in which some instances *must* have a nonstandard location. You might possibly be able to talk the RPM maintainer into changing his ideas of where the RPMs should install stuff --- but I believe he thinks he's following the Linux filesystem layout standard (FHS? forget what it's called exactly). In any case, breaking backwards compatibility won't be an easy sell. Going a bit further in reorganization, if the config files always lived in an /etc/pgsql directory, then pgsqld (aka postmaster) could start with zero parameters and zero environment variables (true?), Again, see multiple-postmaster issue. AFAICT you are proposing to remove flexibility that is *necessary* for some people. (Like me ... I currently have three postmasters of different vintages running on this machine ...) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Optimizer, index use, good news for 7.2b1
We used to have to force sequential scans to be disabled because of a very non-uniform distribution of keys in an index, to actually use the index. We are a music site and a very large number of keys simply point to a catch-all of Various Artists or Soundtrack. The 7.2 beta's statistics and optimizer seems very much better than previous versions of PostgreSQL. Great job guys! The table: cdinfo=# select count(*) from zsong ; count - 3840513 (1 row) cdinfo=# select artistid, count(artistid) from zsong group by artistid order by count(artistid) desc limit 2; artistid | count ---+ 100050450 | 461727 100036031 | 54699 (2 rows) In PostgreSQL 7.1.2: cdinfo=# select version() ; version - PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) cdinfo=# explain select count(*) from zsong where artistid = 1 ; NOTICE: QUERY PLAN: Aggregate (cost=93874.21..93874.21 rows=1 width=0) - Seq Scan on zsong (cost=0.00..93769.55 rows=41863 width=0) EXPLAIN cdinfo=# explain select count(*) from zsong where artistid = 100050450; NOTICE: QUERY PLAN: Aggregate (cost=94816.11..94816.11 rows=1 width=0) - Seq Scan on zsong (cost=0.00..93769.55 rows=418625 width=0) EXPLAIN In PostgreSQL 7.2b1 cdinfo=# select version(); version - PostgreSQL 7.2b1 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) cdinfo=# explain select count(*) from zsong where artistid = 1 ; NOTICE: QUERY PLAN: Aggregate (cost=80.10..80.10 rows=1 width=0) - Index Scan using zsong_artistid on zsong (cost=0.00..80.00 rows=39 width=0) EXPLAIN cdinfo=# explain select count(*) from zsong where artistid = 100050450; NOTICE: QUERY PLAN: Aggregate (cost=94899.78..94899.78 rows=1 width=0) - Seq Scan on zsong (cost=0.00..93664.41 rows=494146 width=0) EXPLAIN ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Catalogs design question
Hello Haller!!! Your question about - pg_proc select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[0] = t.oid ; select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[1] = t.oid ; ... select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[7] = t.oid ; As far as I understand the proargtypes entries 0 means no further parameter. This oidvector type of proargtypes seems to have a start index of 0. As long as there are at maximum 8 parameters allowed, this looks practicable. There is no limit on the number of arguments. An user could create a weird function like this: howe=# CREATE FUNCTION test2(int2, int2, int2, int2, int2, int2, int2, int2, int2, int2, int2, int2, int2) RETURNS int4 AS 'SELECT 1 AS RESULT' LANGUAGE 'sql'; CREATE and it would be allowed... howe=# select proargtypes from pg_proc where proname='test'; proargtypes 21 21 21 21 21 21 21 21 21 21 21 21 21 (1 row) Again, the problem is that I can't predict (nor limit) what users will try to do... Best Regards, Steve Howe ---(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] [patch] helps fe-connect.c handle -EINTR more gracefully
David Ford [EMAIL PROTECTED] writes: I traced several calls and they run through a few functions which end up in pqFlush. These code paths haven't checked the socket to see if it is ready for RW operation yet. pqFlush calls send() [ignoring SSL]. Where? AFAICS (ignoring the USE_SSL breakage), connectDBStart will return immediately after calling connect(), and the next thing that's done is pqWait from connectDBComplete. If there's a path that does what you claim, that's a bug ... but I don't see it. 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] HISTORY file
| Fix for inherited CHECK constraints (Stephan Szabo) ditto If this is what I think it is, I think the actual fix was the following (although I don't know what a particularly good wording is) ALTER TABLE ADD CONSTRAINT now properly adds check constraints to children of the specified table, which is consistant to the behavior of check constraints in inheritance trees created at create time. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.2b1 ...
Is there some formal place to make comments on how 7.2b1 works? I'm about to run it through it's paces on OBSD. Or is this just a 'it's broked' testing time? - Brandon c: 646-456-5455h: 201-798-4983 b. palmer, [EMAIL PROTECTED] pgp:crimelabs.net/bpalmer.pgp5 ---(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] storing binary data
Lincoln Yeoh wrote: Also, FWIW, 7.2 includes bytea support for LIKE, NOT LIKE, LIKE ESCAPE, ||, trim(), substring(), position(), length(), indexing, and various comparators. Cool! Would it be practical to use substring for retrieving chunks of binary data in manageable sizes? Or would the overheads be too high? Cheerio, Link. I haven't done any performance testing, but it should be no different than the substring function used on TEXT fields. Try it out and let us know ;-) -- Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Catalogs design question
Hello Haller!! Your question about - pg_proc select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[0] = t.oid ; select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[1] = t.oid ; ... select t.typname from pg_type t , pg_proc p where p.proname = 'your_stored_procedure' and p.proargtypes[7] = t.oid ; As far as I understand the proargtypes entries 0 means no further parameter. This oidvector type of proargtypes seems to have a start index of 0. As long as there are at maximum 8 parameters allowed, this looks practicable. There is such a limit ? I didn't know. This makes your code a working way. I'll look further on this later... and even if it's not a query that I would say it's beautiful, it's a way, thanks :). Your question about - pg_group The pg_group column is more bulky, because the int4[] type does not have an upper limit. So, the only solution I can see is get the number of array elements of the group you want to query select array_dims(grolist) from pg_group where groname = 'your_group'; and then generate automatically a query like select u.usename from pg_user u , pg_group g where g.grolist[1] = u.usesysid and g.groname='your_group' union select u.usename from pg_user u , pg_group g where g.grolist[2] = u.usesysid and g.groname='your_group' union ... select u.usename from pg_user u , pg_group g where g.grolist[n] = u.usesysid and g.groname='your_group' ; This looks very much like another crude hack you've already complained about. Sorry, but I can't help. Yes, it's ugly code. I would rather write a function, but again I can't assume the user has pl/perl or pl/pgsql (or any other). Two more items I do not understand: You said, the procedures to search arrays in contrib/ are slow. Maybe that's true, but usually you do not have thousands of users in a group, don't you. Yes. I would use it if I can. You said, many users cannot compile this contrib code. Yes, and they are not supposed to do so, because it's up to a system admin to do. What do I miss here? Oh, I develop an interface for PostgreSQL called pgExpress(http://www.vitavoom.com) - it's like an ODBC driver or such. I must provide the functionality I described for the driver users; it's not for me. I would of course have compiled and used the contrib code. But the driver must work out-of-the-box, and requiring a recompile (where many times is impossible to users) is not a solution... Right now, I'm hardcoding that relation inside the driver, what's also not what I dreamed about, but I seem to have no other choice. Thanks for the ideas btw :) Best Regards, Steve Howe ---(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] consistent naming of components
change default account name postgres to pgsql change daemon name postmaster to pgsqld change client name psql to pgsql change data location /var/lib/pgsql/data to /var/pgsql move .conf files from /var/lib/pgsql/data to /etc/pgsql *coff* The more correct (ie. anything but linux) place to put conf files is /usr/local/etc/pgsql. And anyway - you can change the position of these files at compile time... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pgindent run
I recently ran pgindent, which had some fixes from the 7.1 version that were suggested by Tom Lane. Unfortunately, some of my fixes had bad side effects, and I would like to run pgindent again to correct those problems Tom has found. The changes should be minimal, mostly related to indenting of struct/enum and whitespace before single-line comments. I forgot to add the ODBC symbols to pgindent so I need to rerun ODBC anyway. JDBC will not be effected. If I don't hear any objections, I will run it in 12 hours. Thanks. -- 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
[HACKERS] bug (?) with RULEs with WHERE
I cannot use RULEs with WHERE clauses. What's wrong? Is this a bug? I also had this problem with 7.1.1. The documentation says this should work. foo=# SELECT version(); version - PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) foo=# CREATE TABLE a(foo integer); CREATE foo=# CREATE TABLE b(foo integer); CREATE foo=# CREATE VIEW c AS SELECT foo FROM a; CREATE foo=# CREATE RULE d AS ON INSERT TO c WHERE new.foo=5 DO INSTEAD SELECT foo FROM b; CREATE foo=# INSERT INTO c VALUES (5); ERROR: Cannot insert into a view without an appropriate rule foo=# INSERT INTO c VALUES (6); ERROR: Cannot insert into a view without an appropriate rule TIA, Zoltan -- Kov\'acs, Zolt\'an [EMAIL PROTECTED] http://www.math.u-szeged.hu/~kovzol ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Some suggestions.
I tried posting this a couple times, and I'm not sure why I never saw it, but I do think it is something worth thinking about. There was some discussion about pre-forking PostgreSQL, and I gathered that one of the problems would be how do you know what database to open? At our shop, we use a combination of Oracle and PostgreSQL. (BTW: Congrats guys, we have more stability issues with Oracle than we do Postgres!) One of the features of Oracle that is kind of cool, is that it separates the database and the network protocol, i.e. the oracle and listener programs. The listener deals with all the networking crap, and oracle just does the database stuff. While somewhat problematic to configure, it has its advantages. While thinking about pre-forking postgres, it occured to me that Postgres may be made to work similarly. postmaster could start up as it normally does, however, there could be an additional configuration for database listeners. Similar to postgresql.conf, pglisteners.conf, could specify databases which could be pre-forked and listening on other TCP/IP ports. I envision something like this: [sales_db] enable_seqscan = false port = 5433 hostname_lookup = false [marketing_db] port = 5434 That way postmaster monitors the state of the listener postgres, and after it accepts on its port, postmaster will fork off another postgres to wait in a socket accept(). I think it would also be cool to be able to configure the behavior of the listeners differently than the standard postmaster defaults. ---(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
