[GENERAL] Re: diff's between creations of tables
On Thu, 26 Jul 2001, G.L. Grobe wrote:
> When creating an incremental and unique id, what are the benefits of using:
>
> CREATE TABLE tablename (colname SERIAL);
>
> instead of :
>
> CREATE SEQUENCE tablename_colname_seq;
> CREATE TABLE tablename
> (colname integer DEFAULT nextval('tablename_colname_seq');
> CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
>
> One is easier do delete as a dropdb dbname would do it, but anything else I
> should know. Or which one is the general practice, any rules of thumb to
> use, etc...
Same thing.
If you
CREATE TABLE foo (id serial);
PostgreSQL handles this by creating the sequence and index for you.
For the above statement, it does the following:
CREATE SEQUENCE "foo_id_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
CREATE TABLE "foo" (
"id" integer DEFAULT nextval('"foo_id_seq"'::text) NOT NULL
);
CREATE UNIQUE INDEX "foo_id_key" on "foo" using btree
("id" "int4_ops" );
[taken right from pg_dump]
Both are deleted the same way:
DROP table foo;
DROP sequence foo_id_seq;
DROPDB dbname will *always* delete everything in a database, assuming
you have permissions to use it.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(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: [GENERAL] Backup Postgre Windows to Linux
On Thu, 18 Oct 2001, [iso-8859-1] Flávio Brito wrote: > Hi ALL > > I imported a database from Access 2000 to Postgre for Windows but now I can't > backup my database to export to Postgre for Linux . > I tried to use pg_dumpall > db.sql, its freeze on Windows2000 for more than a > hour. My database has 6 MB. > > I can backup my database directory on Windows (using ZIP) and put it on Linux? Might want to try pg_dump (rather than dumpall), perhaps even using the -t option to pick just one table. This could let us find out if you can dump *anything* or not. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] VACUUM, 24/7 availability and 7.2
On Wed, 10 Oct 2001, Ian Barwick wrote: > I'm doing some work for a smallish company which conducts > its business largely online. Currently they have a legacy > mishmash of Oracle and MySQL databases which they wish > to unify one one platform (RDBMS with client access via > browser and custom serverside applications for employees > and customers). > > PostgreSQL would be my primary candidate. However the company's > operating requirments mean that the data needed for interaction > with customers / website users must be available on a 24/7 basis. > This is primarily a) data related to product ordering and > tables for storing order data; and b) website user authentication > and personalisation data (logins, user preferences etc). > > It is therefore not an option to have these databases offline > at regular intervals for any significant length of time for > VACUUMing. Replicating data to say MySQL databases is > technically feasible, at least in the case of b) above, but > not desirable. Are there any existing "native" PostgreSQL solutions > to this problem? > > More importantly, what is the situation on VACUUM for release 7.2? > It seems from the pgsql-hackers list that there are plans for > a none-exclusively locking VACUUM, e.g.: > > >http://groups.google.com/groups?q=vacuum&hl=en&group=comp.databases.postgresql.hackers&rnum=1&selm=12833.990140724%40sss.pgh.pa.us > > (sorry about the long URL); how far advanced are they, and is > there any kind of release schedule for 7.2? > > Any answers (or pointers thereto, haven't found any myself :-() > much appreciated There is a faster, non-exclusive-locking VACUUM in the CVS now; this should become part of 7.2. You can download the nightly snapsot and build it to test it with your application. HTH. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] {REPOST, CLARIFIED} How to recognize PG SQL files?
On Wed, Aug 06, 2003 at 12:55:52PM -0400, Joel Burton wrote:
Reposting, with some clarification to my request. Thanks to the several
responses I received originally.
Yes, I know that a perfectly vaild PGSQL SQL file could contain only
ANSI SQL and therefore not be recognized as PG-related. In that case,
though, it would be recognized by Vim's ANSI SQL coloring, and given
that's all this file contains, that's no problem. ;)
However, given that many people edit pg_dump files (which do contain
pgsql-isms in most cases), and many other people do use non-ANSI PG
features, I figure that I should be able to recognize 95% of the files,
and that's a win since it will consistent highlight PG syntax and make
it easier to scan files, catch typos, etc.
Some easy things:
* if a filename ends with ".pgsql", it will use PG syntax coloring
* if a file contains a comment with "pgsql" in it in the first few
lines, it will use PG syntax coloring
* if a file contains the comments that pg_dump puts in a file, it will
use PG syntax coloring.
I'd still like to catch other cases, and still have the following
questions: what features among our extensions are unique to us, and what
features are used by other common DBs? People that have more recent
experience with MySQL, Oracle, SQLServer, etc. can probably answet this
question.
Thanks, everyone!
- j.
> I'm writing a syntax mode for PG for Vim (posted an early version
> earlier today) and would like to have Vim recognize that this is a PG
> SQL file (rather than a MySQL file or an Oracle file or such).
>
> I'm trying to brainstorm what the unique-looking parts of PG's syntax
> are. These need to be present in PG SQL files (& hopefully not too
> obscure) but not present in other DB SQL files.
>
> The PG manual states how PG differs from SQL standards, but not how it
> differs from other popular databases. I've used MySQL and Oracle in the
> past, but not recently, and haven't use DB2 or SQLServer in ages and
> don't have docs for them anymore.
>
> I have a few possible suggestions. Can anyone:
>
> * tell me if these are used in other DB systems (& shouldn't be part of
> my syntax)
>
> or
>
> * provide other ideas for unique PG syntax
>
>
> My ideas:
>
> * \connect
>
> * template1
>
> * "from pg_" (selecting from a PG system table)
>
> * "create rule"
>
> * plpgsql, plperl, plpython, pltcl, pltclu, plruby (& now plphp, too, I
> suppose! ;) )
>
> * "nextval(", "currval("
>
>
> I'd love to find something common, like "SERIAL" or "CREATE SEQUENCE" or
> such, but I suspect that other commonly-used databases use these.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Does CREATE FUNCTION... WITH (ISCACHABLE) work?
I have a function that always returns the same answer given the same input (no database lookups, etc.). The pg Users' Manual documents the attribute 'iscachable' as allowing the database to parse the results of the function and not keep looking it up. Does this actually work yet? A simple test case: CREATE FUNCTION f() RETURNS INT AS ' BEGIN raise notice ''foo''; return 1; end; ' LANGUAGE 'plpgsql' WITH (ISCACHABLE); SELECT o(); NOTICE: foo o ___ 1 (1 row) SELECT o(); NOTICE: foo o (1 row) It might be that the parser is smart enough to copy any output (such as the RAISE NOTICE), my fear is that it is actually running the function a second time. Does anyone know if this caching actually happens yet, or is this a future feature? Thanks. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Does CREATE FUNCTION... WITH (ISCACHABLE) work?
On 19 Jul 2000, at 14:30, Tom Lane wrote: > "Joel Burton" <[EMAIL PROTECTED]> writes: > > I have a function that always returns the same answer given the same > > input (no database lookups, etc.). The pg Users' Manual documents > > the attribute 'iscachable' as allowing the database to parse the > > results of the function and not keep looking it up. > > iscachable does not mean that the system will cache the results of the > function across queries, it just means that the function needn't be > re-evaluated multiple times for the same arguments within a single > query. For example, given > > SELECT * from table1 where col = foo(42); > > If foo() is marked cachable then it's evaluated once during query > planning; if not it's evaluated again for each row scanned in table1. Sounds reasonable. But does it work as advertised? CREATE FUNCTION foo(int) RETURNS int AS ' BEGIN RAISE NOTICE ''hi''; RETURN 1; END;' LANGUAGE 'plpgsql'; CREATE FUNCTION foocache(int) RETURNS int AS ' BEGIN RAISE NOTICE ''hi''; RETURN 1; END;' LANGUAGE 'plpgsql' WITH (iscachable); SELECT foo(1),foo(1),foo(1) gives us 3 NOTICEs, as does SELECT foocache(1), foocache(1), foocache(1) So is it running the cached version a second time? Thanks, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
(Fwd) RE: [GENERAL] PostgreSQL, ODBC, Access (solution for me)
> > Here's a bothersome issue: I've got the most recent versions of > > Postgres, ODBC client for Win32, and Access 97. My client can enter > > new records fine via a linked table. However, when she goes back to > > add data to a column, she gets the following error: > > > > message box title: "Write Conflict" > > description: "This record has been changed by another user since you > > started editing it. If you save the record, you will overwrite > > the changes the other user made." buttons: "Copy to Clipboard" and > > "Drop > > Changes". It appears that *once* Access finds something unique about a record, it uses that to differentiate records. (Check out the SQL log to see) However, a new field in Access has no key *until* PostgreSQL gets it (if you're using a SERIAL field type), and the default values for other fields don't appear either. So, the trick is to have Access deposit a unique value (in this case, a timestamp) into each field. What works for me (even in datasheet view): 1. Create a table w/a timestamp field. CREATE TABLE Foo (id SERIAL PRIMARY KEY, fullname VARCHAR(30) NOT NULL, dt TIMESTAMP DEFAULT 'now' NOT NULL); Then, in Access: Don't use *table* datasheet view. Create a form w/the fields you want, and use that *form*datasheet view. Set it up so that Access has a DefaultValue property of Now() for the "ts" column. (In addition, while you're there, you might want to lock/hide the ts column, and lock the serial column, as Access will let you renumber a PostgreSQL serial field, which I think is a Bad Thing [YMMV].) Then use the datasheet view. Since the "dt" column should be different for each record *from the moment of inception*, this gives Access something really unique to hang its hat on. Works for me; let me know if it doesn't work for you. -- Has anyone ever collected a FAQ of Access-on-Postgresql? I've got a few tips (nothing heavy, just the usual use-float-instead-of- decimal-for-currency), and suspect others have a few. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Some questions on user defined types and functions.
It would seem that it wouldn't break anyone's existing setup, since you couldn't have an env variable in there anyway. (No one really has a directory called $HOME, I hope!) So, perhaps it could just be something in the documentation that has a stern warning about watching your consistency. Caveat hacker and all that. On 26 Jul 2000, at 17:50, Tom Lane wrote: > Jeffery Collins <[EMAIL PROTECTED]> writes: > >>>> like the following syntax to work: > >> > >>>> CREATE FUNCTION myfunc(mytype) RETURNS text AS > >>>> '$HOME/lib/libmyso.so' LANGUAGE 'c': > >> > >>>> and have the environment variable $HOME "lazy" evaluated. I have > >>>> looked at the fmgr code and this doesn't look too difficult to > >>>> add as long as I could get the $HOME past the parser. > > > I have made the changes necessary to allow environment variables to > > be entered and expanded in file names. Two files had to be changed > > backend/commands/define.c and backend/utils/fmgr/dfmgr.c. Assuming > > you are interested in the change, > > Well, that's a good question. Does anyone else have an opinion on > whether this would be a good/bad/indifferent feature? We've seen > problems in the past caused by depending on postmaster environment > variables (restart the postmaster with different environment than > usual, things mysteriously break). So I'm inclined to feel that > adding more dependence on them isn't such a hot idea. But I'm not > going to veto it if there's interest in the feature from other people. > > > what is the proper way to build a patch file that > > contains the changes? I have never done this before. > > "diff -c" against current sources, done so that the correct file > pathnames are visible in the diff output; that is, cd to top level of > distribution tree and do something like diff -c > src/backend/utils/fmgr/dfmgr.c.orig src/backend/utils/fmgr/dfmgr.c > Don't forget to include diffs for documentation updates, as well. > >regards, tom lane -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Problem with rules & ODBC
Have you tried this with triggers? I've never done this w/rules, but with triggers, I don't have this problem. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Error installing ODBC in NT
> Hi!, > > I'm installing the ODBC Driver, and evrything goes right in all the > clients (NT Workst.) except in one of them where I get this error at > 85% of the set up process: > > "Unable to create ODBC Core Subkey" > > Sure, It's not ODBC Driver faulty (I guess), but if someone knows how > to work out, would be nice to me, since I'm migrating from MS-SQL > Server to PostgreSQL, and that computer is an important one, so it is > delaying the process Under Win98, I had similar problems installing ODBC drivers (at some point, the ODBC manager seem to become corrupted, and I couldn't add new drivers.) Are you able to add another new ODBC driver? (You could download MySQL's, for example, & see if that works.) I solved my problem by going into the registry and deleting the ODBC managers keys and reinstalling PostgreSQL w/driver manager. I love my other ODBC datasources, but reinstalled those. It's hardly elegant, I'm sure they're are better ways, but, hey, it worked for me. Your mileage may *definitely* vary, You've been warned, etc., etc. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] convert from access
> I have to remake PHP script (which full of Access SQL implementation) > for PostgreSQL. I have a some of strings like this: > > SELECT memmod.item, memmod.oempartno, memmod.sdescr > FROM memmod WHERE (InStr(1, [item], '".$edtPartNo."'));"; There are programs to help you move Access *tables* to pgsql, but I don't know of anything that would help you move VBA functions to Access. A chart of the 'usual' conversion (ie InStr, Left$, etc.) VBA <-> pgsql SQL would be a simple project for someone to start. (Any takers?) In the PostgreSQL + Access faq (www.scw.org/pgaccess), there are tips about many Access->PostgreSQL conversion issues, including how to rewrite some queries to support things like outer joins, transform queries, etc.; but nothing about VBA functions. If you come across such a resource, could you email me (or this list, or better still, the INTERFACES list), so it can be added to the FAQ? -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Unanswered questions about Postgre
[re: question #4, speed/vacuuming] > Do > people need to vaccume their databases hourly? Can you vaccume while > a database is in use? Any discussion on this curious phenomenon would > be appreciated. It still boggles me. I vacuum twice a day, once in the dead of night, once around lunch. Yes, you can vacuum while the db is in use, but many locks (for updates, inserts, etc.) will hold up the vacuum. > 5) BLOB Support. Keep in mind the pgsql 7.1 (about to go beta tomorrow, if I heard right) will support much longer row sizes than 8k. Doesn't remove the needs for blobs for many of us, but fixed my problems. I believe the docs discussing the c-level interfaces talk about lo creation and such. Have you looked in the low-level docs in the programmer/developer manuals? I have only played w/blobs; others can speak better about their use/limitations, but if I have it correct: . blobs cannot be dumped . blobs are not normally vacuumed So, for most of us, I think the TOAST feature of 7.1 that allows >8k row sizes is much nicer. (Unless, of course, you really want to store binary data, not just long text fields.) Good luck, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Unanswered questions about Postgre
On 30 Nov 2000, at 1:24, Igor V. Rafienko wrote: > on Nov 29, 2000, 19:17, Joel Burton std::cout'ed: > > [snip] > > | > 5) BLOB Support. > | > | Keep in mind the pgsql 7.1 (about to go beta tomorrow, if I heard | > right) will support much longer row sizes than 8k. Doesn't remove | > the needs for blobs for many of us, but fixed my problems. > > > How _much_ longer? (Sorry if it's a FAQ, in that case, I'd appreciate > a pointer/URL). Dunno, but I've been using 7.1devel for ~2 months, and so far, longer rows seem to work fine. More information on the TOAST project is at http://www.postgresql.org/projects/devel-toast.html -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Table & Column descriptions
\d+ should show you the table schema with comments. If you're looking for the actual data, it's in pg_description. The objoid field matches the oid field in pg_attribute (which is the "fields" table for pgsql). On 30 Nov 2000, at 11:17, Dale Anderson wrote: >I am able to add table and column descriptions, and I am also able >to retrieve the table description. The problem is that I can not >find a way to retrieve the description comments on table >columns Any assistance would be greatly appreciated. > > Dale. > -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Unanswered questions about Postgre
On 30 Nov 2000, at 11:58, Joe Kislo wrote: > If you don't believe me, here's two fully SQL-92 > compliant databases, Oracle and interbase, which do not exhibit this > behavior: Ummm... havings lots of experience w/it, I can say many things about Oracle, but "fully SQL-92 compliant" sure isn't one of them. :-) -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Unanswered questions about Postgre
> > What's nice about PostgreSQL is that, while it hasn't always had > > every SQL92 feature (like outer joins, etc.), it seems to have less > > legacy, nonstandard stuff wired in. :-) > > Oh man, you have n idea. PostgreSQL is legacy headquarters. ;-) Yes, yes, I know about *some* of them [8k limit springs to mind!] (C hackers no doubt no *lots* more.) But, in terms of, "as comes out in our SQL syntax", compared to Oracle, we're free and clear. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] RFC: User reviews of PostgreSQL RI functionality
On 1 Dec 2000, at 12:00, Ed Loehr wrote: > How well is the recently-added PostgreSQL functionality supporting > referential integrity (RI) working. Any serious bugs? Any major > hinderances? Other impressions? > > I'd be particularly interested in hearing from people who've > implemented larger schemas using RI (say, more than 30 tables and 50 > foreign keys). We've been using a database w/85 tables, with about 60 pkey/fkey restraints in place. The db has 8 users plus supports a dynamic web site (having ~10 users at a time on the site). None of the tables are very large (avg ~400 rows) except 3, which have ~65000 rows. I haven't come across any real problems particular to RI in pgsql. The constraints always seem to work; dumping and restoring works fine (for RI), etc. You can do some things that defeat RI--most importantly, if you TRUNCATE a table, RI checks are never performed. However, this is (IMHO) a good thing, as TRUNCATE is intended solely for DBA use, and for DBAs, this means I can truncate a table, while ignoring any related records, and reimport (via COPY or INSERT) the data, all w/o disturbing any child relationships. This allows me to reconfigure a table, delete columns, add other constraints, etc., in a database w/o a full dump and restore. When pgsql has all the ALTER TABLE DROP COLUMN, ALTER TABLE ALTER COLUMN commands finished, this may be less important. The current "stable" ODBC driver for Windows doesn't work perfectly w/RI -- it doesn't report RI errors as an error. The RI rule is still obeyed, however, to the ODBC client program, no error is reported, so your user may never know that something went wrong. (This is fixed in the CVS versions of the ODBC driver, and you can download a binary compile from my site at www.scw.org/pgaccess.) Be away, though, that the way RI is handled internally by pgsql that when you dump a database and examine the dump, the RI statements are now triggers and not nice clean REFERENCES tblFoo ON fieldFOO DDL statements. It reimports perfectly, but is less self- documenting. In the tiny-wishlist department, I would like it if there were an easy way to change the referential integrity behavior (delete, block, etc.) for an existing relationship. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] server permissions for sql copy
> I'm running PostgreSQL 7.0.2 on Caldera eDesktop 2.4. > when I try to use the SQL COPY command in psql, I get > an error message informing me that the backend could > not open the file for reading. I think the error > number was 13 - Permission denied. I changed the > permissions of the directory and file to > world-readable; but I still get the same error > message. Keep in mind it's the *server* process (user postgres), trying to get to the file, not the psql user (probably you). Is the file in *your* home directory? You'll need to chmod a+rx this directory, and all directories above this directory. On our server, we've created a directory for pgsql copying and dumping that is rwx for postgres and our DBAs. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] RFC: User reviews of PostgreSQL RI functionality
> I was wondering if you could tell me where to get the CVS version of > the ODBC driver -- I have almost implemented foreign key support > (SQLForeignKeys), but I did it on the ODBC source available on the > postgres server. I browsed the postgres CVS repository via the web, > but couldn't find anything relevant It's *possible* that if you're programming directly to the ODBC API that you've always seen the RI stuff. I do my work in VBA apps (like Access), and I know that they don't see the RI errors. You can find the ODBC source in the pgsql source, at src/interfaces/odbc (or something *very* close, no source in front of me right now, sorry). If you don't want to grab the whole file just for ODBC stuff, you can use CVS to just get the contents of the src/interfaces/odbc directory. The directions say you need to use VisualC++ to compile this. Via ftp, that's in the /dev directory. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
[GENERAL] Re: [SQL] Access Permissions/Security
Start psql w/ -E option and you can see the SQL for all \xxx commands. On 27 Nov 2000, at 20:20, Rob Burne wrote: > Hi, > > I need to find the query that will return all security access > permissions. > > For example if a INSERT has been granted to a particular user, how do > I perform the query which will return this result. I know that the \z > command returns this, but I need the raw SQL to do it - any ideas > please? > > Many thanks, > > Rob. > __ > ___ Get more from the Web. FREE MSN Explorer download : > http://explorer.msn.com -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
[GENERAL] Re: [NOVICE] to_days(now())
On 28 Nov 2000, at 13:12, Brian Aker wrote: > I've been looking for a good reference for the built > in time functions and have yet to find any. Try the functions reference in the postgresql manual--there's a lot of information about Pgsql date/time functions there, but in your case, no function is needed--overloaded operators will work fine. > What I really need is something similar to MySQL's: > select to_days(now()); > > This returns the number of days since year one from > the current time in the database. > Thanks for any help in advance (please CC me directly > since I don't subscribe to this mailing list). select yourdate - '-01-01'::date from yourtable; seems to work just fine, and returns an integer. Both PostgreSQL and MySQL say there have been 730485 between 1/1/0 and 1/1/2000. Something we all can agree on? :-) Good luck w/your app, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Re: [NOVICE] Password protection?
[ answer about PG passwords deleted ] > PgSQL stores the plaintext password. Non-superusers can only look at > pg_password, where the password is starred out, but pg_shadow shows > the real passwords. Oops! I meant "pg_user", not "pg_password". The whole /etc/passwd and /etc/shadow playing with my mind! -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Modification times in records?
On 7 Dec 2000, at 1:59, Adam Haberlach wrote: > I'm looking for a generalized way to set a field to 'now' > whenever a record is updated. > > First, I started creating a plpgsql function for each table that > I needed this for, and then using a trigger. > > Later, I realized that the functions were all identical, so I > created one function, which lukily was always updating a field > with the same name. > > I'm looking for a way to specify a field name when I create the > trigger, so I can have one function and assign it to any table > with any timestamp field and have it update automagically. > I can't seem to get a parameter into the function. Any ideas? There's stuff in contrib/spi that handles this (IIRC, a C function called moddatetime or something like that.) It allows you to specify a fieldname of the datetime field, I believe. Plus, this should be (slightly?) faster that a PLSQL routine. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] UNION within VIEW workarounds?
> Given UNIONs are not implemented within views in 7.0.3, what are the > generally suggested work-arounds, if any? Ick. We just did the full SELECTs all the time. A pain. FYI, this is fixed in 7.1. Beta Real Soon Now. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] RE: Using Postgres with Access 2000
> Let me rephrase my words. :) > I have created postgresql tables with pgaccess. I am unable to add > new records to those tables using Microsoft Access 2000. How do I > remedy this situation, and also, is there a way to create tables > within Microsoft Access 2000 on the Postgresql server. Questions about Access + Pg are most appropriate to pgsql- interfaces, BTW. By default, ODBC connections to Pg are read-only. Did you turn off BOTH read-only option boxes? You can create tables by using the DDL statement as a 'Pass- through query' in Access. More complete answers can be found in the Pg+Access FAQ at www.scw.org/pgaccess. Good luck, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Access 2000 and PostgreSQL: Record Editing Problems
On 5 Jan 01, at 14:37, Scott Teglasi wrote: > When I add a record, I fill in the relevant fields, but when I proceed > to the next row, the row I just added shows "#Deleted" in all of the > columns. It continues to do this as I add records. When I close the > table, then reopen it, my data is there, and the #Deleted is gone. It > does add the data, however when adding new records, and having > #Deleted shown on my newly added rows, is a bit of a nuisance.. > Anyone have any remedies? It's a FAQ: www.scw.org/pgaccess. BTW, questions about Pg + Access are better in pgsql-interfaces, which deals with interfaces to PostgreSQL. good luck, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
RE: [GENERAL] How to see a RULE definition?
On Thu, 11 Jan 2001, Trewern, Ben wrote: > You can use: > pgdump -s > outfile > > which will dump all the schema for the database. > It should be in there somewhere ;) Or, for 7.1, use the new features of pg_dump to dump to the non-text-file formats, which will allow you instantly and selectively look at particular parts of your db dump, rather than worming your way through yourself. Better still: You can also SELECT * FROM pg_rules WHERE rulename='my_rule'; (at least in 7.1 betas; don't remember about 7.0.x) -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington
[GENERAL] Re: Case sensitivity
On Tue, 27 Feb 2001, Bruce Richardson wrote: > Discovering that text fields in Postgres are case sensitive - so "Text" > <> "text" and both can go into the same UNIQUE column - threw me a bit, > as this is different from other databases I've worked with. > > Is there a toggle to turn off case sensitivity, either for a whole > database or for a column? Or do I just have to get used to ~*? No, you can't turn it off, and yes, many people do notice that it's not the way some other databases work. (Some database make this configurable). You can use ~*, you can also index on function results (eg, you can create an index on a function. So, you could create unique index yourtable_pkey on yourtable ( upper(pkeyfield)); which would only allow one 'Joel', regardless of how you cap me. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington
[GENERAL] Re: pgsql for Python
On Wed, 28 Feb 2001, [iso-8859-2] Marek Pêtlicki wrote: > Has anybody used http://sourceforge.net/projects/pgsql ? > I maintain production system based on Python and PostgreSQL. > > I currently use PoPy, but for a few reasons it doesn't satysfy me fully. > The pgsql seems OK at first sight (especially libpq-to-Python API) but > for the production system I need Python DB API 2.0 compliant library. > Has anybody tested it yet? What is the status of PostgreSQL 7.1 > compliance for today? > > Any Python geeks out here? ;-) Partially a Python geek here. I use PoPy and think it's dandy. I also thought it *was* the DB API 2.0-- am I mistaken here? PyGreSQL is more commonly used, and has (IMHO) a simpler, more dict-like interface, but isn't (AFAIK) thread-safe, nor DB API compliant. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(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
[GENERAL] Can I get the default value for an attribute (field) ?
I'm building a GUI for a PostgreSQL database. In the DB, many fields have default values (a few are complicated, like the results of a sequence, but most are simple things like FALSE or 0 or such.) Is there a way to get what the default value for a field would be when a new record is added? For some tables, I could (behind the users back) add a row, grab the values given in each column, delete the row, then present these as 'default' values; however, in most tables, there are columns that cannot be null and do not have default values, therefore I can't just add a row using the default-only values. I've been looking around for a miraculous SELECT default_value(table.class) function, but can't seem to find it. Any ideas? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: Can I get the default value for an attribute (field) ?
> I'm building a GUI for a PostgreSQL database. In the DB, many fields have > default values (a few are complicated, like the results of a sequence, but > most are simple things like FALSE or 0 or such.) > > Is there a way to get what the default value for a field would be when a > new record is added? For some tables, I could (behind the users back) add > a row, grab the values given in each column, delete the row, then > present these as 'default' values; however, in most tables, there are > columns that cannot be null and do not have default values, therefore I > can't just add a row using the default-only values. > > I've been looking around for a miraculous SELECT > default_value(table.class) function, but can't seem to find it. Any ideas? I should have mentioned that I know the default values are stored in pg_attrdef, in 'human' mode at adsrc, and in 'pg' mode at adbin; I could look there, but don't know how to 'evaluate' these to the real-world equivalents (ie, instead of finding 'f' or FALSE in a column, I get 'f'::bool, and by the time this gets to the GUI app and back again, it looks like '''f''::bool' to PG, so it sees it as text, not as the boolean value false. Similarly, "current_user"() isn't resolved, etc. So, one solution might be: is there a way to 'resolve' these before they come to the front-end? Thanks! -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Database Name Case Sensitivity
On Thu, 5 Apr 2001, Brian T. Allen wrote: > I just ran into that too, and find it most undesirable. I don't know > whether that is part of the SQL spec or not, but it seems very odd. The > queries seem to be converted to lowercase before they ever reach the SQL > engine. Everything is postgresql is lowercased, unless system identifiers (double quotes) are wrapped around it. CREATE DATABASE foo is the same as CREATE DATABASE FOO or CREATE DATABASE Foo You must connect to it with \c foo However, if you CREATE TABLE "FOO" then it is not lowercased. You must connect as \c FOO I think this is a Good Thing. It allows most people to have case-insensitive system identifiers (at least they appear case-insensitive, since it lowercases everything). This plays well with other database systems. If you want case-sensitive identifiers, you just wrap them wiuth the double quotes. (cf to the mess in MySQL, where some things are case-sensitive, and some things aren't, and it varies based on whether the server OS is case sensitive. Ick.) -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(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
[GENERAL] diff -c pgsql mysql (Was Database Name Case Sensitivity)
On Thu, 5 Apr 2001, Brian T. Allen wrote: > I am sure it is a Good Thing, thank you for the explanation. I will just > have to change my thinking to all lowercase and get used to it. > > Sort of on the same topic, does anyone know of a PostgreSQL primer for those > making the change from MySQL. Changing religions :} is never easy, > something to ease the transition and point out the differences and gotchas > would be very helpful. Let me not add to any flame wars here, please. MySQL might be useful for the most complicated database project in the universe, and might be scalable to counting the grains on sand on every beach. However--it's support for many 'high-end' database features is scarce (to it's credit, in its place is an extremely easy-to-install, easy-to-administer, relatively speedy and painless database that works great only practically every OS you'd care to use.) I don't have a simple list of 'gotchas' -- instead, I wanted to answer the other question. What are the non-gotchas, non-simple differences between the two. [Actually I have some gotchas:] - MySQL uses nonstandard '#' as a comment line. Pg doesn't. Use '--', both understand it - MySQL uses ' or " to quote values, as in WHERE name = "John". Pg uses only single quotes for this, double quotes are used to quote system identifiers (field name, table names, etc.) - The whole case-sensitivity issue, above. - Pg and MySQL seem to differ most in handling of dates, and the names of functions that handle dates. But (for me, at least) are more than just 'how do I do this MySQL thing in PostgreSQL', but 'is there a much better way to think about this, which MySQL never even supported.' For example: Imagine you're building a dynamic web for human resources. You want to list every current senior staff member's name, some info about them, and a list of their goals. With MySQL, you'd do something like this: (this is generic pseudo-code, it would easily translate to PHP, Zope, EmbPerl, etc.) $firstname $lastname $goalinfo That's great, and it works fine. You can easily translate this to PostgreSQL. Would you want to, though? PostgreSQL has many features MySQL doesn't, like: * views * procedural languages * triggers * customizable aggregates * transactions For instance, rather than coding in the web front end the logic of is-not-fired and is-senior-staff, in PostgreSQL, I'd make a VIEW of all staff for which we want to show goals: CREATE VIEW staff_having_goals AS SELECT staffid, firstname || lastname as fullname FROM Staff WHERE datefired ISNULL and seniorstaff = TRUE ORDER BY lastname, firstname Now, my web programming doesn't have to worry about the lower level concerns. Imagine if this same list of people and goals appeared dozens of times on your site--I've moved from having this scattered in many places, to having it encapsulated in one place. PostgreSQL also allows procedural languages (perl, tcl, python [alpha], and an Oracle-alike, plpgsql). These allow you to create functions in your database (and even non-sysadmins can use them, as the functions fit in the PostgreSQL security model). [ Yes, MySQL has user functions, which last time I checked, had to be written in C, and linked into the database. A nice feature, to be sure, but VERY different from having high-level procedural languages usable w/o root privileges! ] We might use these procedural languages to create lists, handle database events (if a record is added here, automatically track this here, etc.) (You might have a function to calculate a staff member's hourly compensation from their salary, which, IMHO, *should* be a database function, not a function coded for every different web project or front-end project you had.) PostgreSQL also has transactions, which can remove some of the hairy if-an-error-happened-back-out-all-database-work code. (MySQL, to its credit, has transactions in their new MaxSQL thingie.) So: The things that are handled differently are fairly small. The real lesson is to learn about what features PostgreSQL has an figure out *why* to use them! I'd start w/the five about (views, procedural languages, triggers, customizable aggregates, transactions) and make sure that you understand exactly what they are, how to use them, and how wonderful they are. I hope this helps, and I do hope it doesn't sound condescending. I moved to PostgreSQL from using MySQL, and for several months after first playing with it, I just thought it was a bigger, more complicated database that did 'the same stuff' as MySQL. It took me a while to really realize how great the 'other' features are. Good luck! -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington -
[GENERAL] Re: Database Name Case Sensitivity
On Thu, 5 Apr 2001, ADBAAMD wrote: > Brian T. Allen wrote: > > > I just ran into that too, and find it most undesirable. I don't know > > whether that is part of the SQL spec or not, but it seems very odd. The > > queries seem to be converted to lowercase before they ever reach the SQL > > engine. > > Correct me if I'm wrong, but doesn't it look like a trend in PostgreSQL > development? Instead of adding options and keeping the standard or > previous behaviour, a new behaviour is attached to old syntax, and if > you want to keep the standard or old results you have to do something else. Hmmm. I'm not sure that's a charitable reading of the fantastic work of the Global Development Group. They seem *quite* sensitive to not breaking existing stuff. (In fact, for people like me, who work in small IT departments, and love to rebuild systems, sometimes they seem *too* concerned with not breaking old stuff! ;-) ) > The "Right Thing"(TM) to do in this case would be to be case insensitive > by default, according to the traditional (if not standard) SQL practice, > and do case sensitiveness if using quotes. PostgreSQL *does* do what you'd expect. It is 'case-insensitive-appearing'. AFAIK, it's only when using the \c dbname in psql that you have different behavior (that it cares if you specify in in the correct case.) Perhaps more consistent would be \c dbname is converted to lowercase \c "DBname" is treated, case insensitive (which is how everything else in the PG world works.) But, to substitue this now would break existing apps, wouldn't it? ;-) -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(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
[GENERAL] Re: Convert Filemaker Pro db to Postgresql db
On Fri, 6 Apr 2001, Scott Gritton wrote:
> I've been reading through the lists but have not been able to find any
> information about how to take a Filemaker Pro database and convert it into a
> Postgresql database.
> Does anyone have any information on how to do this type of conversion?
> Thanks!
(My suggestions are a bit speculative. I gave it a day to see if any real
FM users would respond; since none have, I hope this is better than
nothing... :-) )
It's been ~4 years since my fingers touched FileMaker Pro (I understand
it's much nicer now...).
Can FM generate DDL ('create table foo...') statements? If so, you can
dump your data that way.
In FM, can you access ODBC-linked tables? If so, you can copy the data
that way.
Or, do you have any scripting ability? You could write a
Perl/Python/PHP/P-whatever script to connect to FM and connect to PG. PG
has database connectors for all of these languages.
If you're stuck, perhaps it would help non-FM-users on the list for you to
give us a sense of what you can do w/your FM data.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Better Features document?
One thing that confused me when I started seriously looking at PostgreSQL was the features it had relative to other competitors. We have so many powerful features that are often underused by new users: * procedural languages * triggers * rules * views * custom aggregate functions * ... and more and so on. The documentation does a good job (& gets better all the time!) at explaining this, but many users never read that far into the documentation, and, of course, many people never get to the documentation at all -- they're evaluating software by a 10-minute glance through the web site. We have a features document at http://www.postgresql.org/features.html but this covers the architecture of the system (postgres / postmaster, etc), and very little about some of our other competitive advantages. My fear is that users & potential users come to PG w/o learning what a view is, how triggers can be helpful in designing database systems, why custom aggregates are so great, etc. (Those of us w/CS backgrounds do well to remember how many web database designers don't have that background!) Therefore, people compare us sometimes w/other database systems (mostly MySQL simply as 'MySQL seems faster and easier to install, but PostgreSQL has some features, like transactions, that may be useful to complicated databases', completely missing how many PG features are important to everyone that is designing databases, simple or large. I started writing a 'Features+' document a few months ago, but it got sat aside during a busy work time. I'd like to restart that work. I don't want to recreate the manuals -- I envision something like a 5-page 'product datasheet' that explains just enough about what a trigger is so that users have no excuse for not digging into that chapter, and that people understand how fantasic procedural languages are. Before I start digging into that, does anyone know if there exists a short- or medium- length (2-5 p) document that explains, for ordinary database mortals, about the sophisticated features of PG? Does anyone want to help put this together? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: Startup script
On Wed, 4 Apr 2001, Marcin Wasilewski wrote: > hello everybody, > > Maybe someone could help me with ODBC setup with POSTGRESQL > > thanks, > Marcin FAQ at www.scw.org/pgaccess -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: Why are quotes required around table / column names in SQLstatements?
On Mon, 2 Apr 2001, Wade Burlingame wrote: > ...and is there any way I can get rid of this requirement? I'm just getting > into PGSQL and I find the quotes very distracting. The quotes are only *needed* around tables/columns/etc if they contain characters not in the alphanumeric set, or if it conflicts with a reserved word. So, one can say CREATE TABLE foo but must use quotes for CREATE TABLE "foo is the word" or CREATE TABLE "table" or such. The only time you normally see them (if you haven't used them) is w/tools like pg_dump. And there's a command-line switch to not show them if not neccssary, IIRC. HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: Trouble with PL/pgSQL
On Tue, 3 Apr 2001, Kevin Willems wrote:
> I wrote the following test function:
>
> CREATE FUNCTION tester(text)
> RETURNS text
> AS 'DECLARE
>str text;
> BEGIN
>str := upper($1);
>RETURN str;
> END;'
> LANGUAGE'plpgsql'
>
> This runs OK. However, when I go to use it as follows:
>
> SELECT tester('sometext');
>
> I get the error:
>
> NOTICE: plpgsql: ERROR during compile of tester near line 1
> "RROR: parse error at or near "
If this is the actual function you used, then you need a space between
LANGUAGE and 'plpgsql';
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: converting from php3 to php4
On Wed, 4 Apr 2001, roy cabaniss wrote: > I am in the process of converting from php3 to php4 and at the same time > going from mysql to postgres. > > All the relevent files reside in > > ../foo/bar > > And the vast majority are in the form *.php3 with internal references to > other webpages which (of course) are also in the form *.php3. > > What I need is something that can go into that particular sub directory and > find every instance of php3 and make it php, both as a part of a file name > and internally in the file. Not really a PostgreSQL problem, but something quick in Perl could do this: perl -pi.bak -e 's/php4/php/gi;' `find -name "*.php3"` will iterate over all files in the current directory and below named *.php3. It iterates over every line in the file, subbing php for php3. Followups to a Perl list, please. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: information on users
On Tue, 3 Apr 2001, Nick T wrote: > Howdy all: > > I'm brand new to postgresql, so don't beat me too severely about the head > and shoulders over this question. > > I see that we can add users and delete or drop users. As the database > administrator, I'd like to be able to view all of the users and all of the > information about them. Couldn't find out how to do this in the > documentation. Could anyone tell me how to do this? If there is no utility > for this, is there a file that I can view that has this info? Welcome to PostgreSQL. There are 'system catalog tables' that hold information about your database. If you use the command \dS in the psql, you'll get the full list of these system tables. pg_user contains the user information, and can be viewed by everyone. pg_shadow contains user information plus sensitive password information, and can be viewed only by the superuser. HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: speed on Postgresql compared to Mysql
On Tue, 3 Apr 2001, Livio Righetti wrote: > Hi, > > Talking about insert, I know Mysql is fast than Postgresql. > > I've made the following test : > > 40'000 insert (accouting context) using Perl and dbd : > > Postgresql : > text : 4 min 53 s > varchar : 4 min 49 s > char : 4 min 49 s > > Mysql : > text : 0 min 29 s > varchar : 0 min 29 s > char : 0 min 29 s > > So we can see Mysql is about 10 times fast. > > Also we used Postgresql for Radius (authentication) et we have to make 3 > vacuum per day otherwise the first server is overload and the user go to the > backup server. > > Is it normal or my Postgresql is not well configured ? Err, yes. Did you just do 40,000 inserts in a row, one after another? Realistic speed tests often have many requests coming in together, to simulate application- and web-usage. In addition, did you wrap this in a transaction? Otherwise, you're performing one transaction for *every single* insert, which is much slower than in a a transaction. (Generally speaking, if you want to just add 40,000 rows to a table, I'd use COPY, not INSERT ;-) ) HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(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
[GENERAL] Re: [DOCS] Re: Better Features document?
n. * Online resource listings at http://techdocs.postgresql.org Commercial contracts: * Commercial contracts are available from several companies, and consulting from hundreds of companies and independent consultants. * Commercially-packaged versions of PostgreSQL are available from GreatBridge LLC, http://www.greatbridge.org. --- SERVER ADMINISTRATION: Backup and Recovery tools: * PostgreSQL includes a tool (pg_dump) to handle backup of all database data. These backups, which can be performed while the database is being used, are platform-independent, human-readable, and can be stored as tar archives or in compressed formats. * Database can be restored as a whole, or (using pg_restore) selectively to recover individual database elements. Security: * PostgreSQL offers a user/group security model that can restrict operations by user and/or group. * Privileges can be assigned for viewing and modifying data. * Access to the database server itself can be restricted based on host, username, database. * Database passwords can be sent encrypted, and database connections can be encrypted using SSH or SSL. Temporary tables * Temporary tables (which can be created by CREATE TABLE or SELECT INTO) are automatically dropped at the end of a database connection. * Easier maintenance than removing normal table manually. GUI Administration: * pgaccess: a platform-independent Tcl/Tk program for database maintenance and report-writing * pgAdmin: a comprehensive, Windows-based PostgreSQL administration program * XXXAnd some web programs I don't know about --- CLIENT INTERFACES Client programming languages/interfaces: * Perl (through Pg and DBD::Pg) * Python (through Pygres or PoPy) * PHP (support built-in to PHP) * Tcl * ODBC, and therefore many ODBC client programs, including Microsoft Access, StarOffice, Applix, etc. * JDBC (Java Database Connectivity) * C (through libpq and libpqeasy) * Can use embedded SQL statements in C * C++ (through libpq++) * Emacs LISP * R (Open Source statistical package) * Zope (Open Source web application server) * XXXand othhers, I'm sure--what's missing? ] Web Publishing Solutions: With its support for transactions, defined functions, and views, PostgreSQL offers web developers a safe and structured programming environment. Almost all web development systems that uses Perl, Python, ODBC, PHP, or Tcl can use PostgreSQL. * Zope * OpenACS, an Open Source port of ArsDigita's ACS * ColdFusion * Mason * EmbPerl * mod_perl and DBI * XXXAnd others, no doubt * Web groupware packages supporting PostgreSQL: Twig (www.screwdriver.net/twig), XXXand no doubt many others Query monitor: * Comes with psql, a featured-filled text console-based interactive query monitor. * Includes full support for history and history editing, customization, local/remote database access, and importing and exporting of data. * In addition, graphic query monitors available for many operating systems and desktops, including Windows, KDE, and GNOME. Event notification * LISTEN and NOTIFY can be used to pass messages or notify different clients of an event in the database. * Can be used to coordinate different front-end clients (even across different front-end systems). PERFORMANCE & STABILITY Performance: * Very competitive performance, especially for multi-user applications and larger databases. * Sophisticated locking models permits high scalability for concurrent writes and reads. Stability: [ any info on large dbs used by PG? ] - USERS * SourgeForge * Large open source database-backed collaborative web site * OpenACS * Fully open source version of ArsDigita's community-based web system, ACS * XXXBruce, surely you know of others. - AND MORE And More (Optional Contributions) Distributed with PostgreSQL are many optional contributions, such as SOUNDEX() functions for 'sounds-like' string matching, full-text indexing, cryptographic hash functions (including SHA1 and MD5), user-handled long-term cooperative locking, ISBN/ISSN number types, and more. In addition, there are many additional functions that can be downloaded from web sites or from the PostgreSQL discussion lists. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(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
[GENERAL] Re: speed on Postgresql compared to Mysql
On Mon, 9 Apr 2001, Lincoln Yeoh wrote: > At 05:30 AM 08-04-2001 -0400, Joel Burton wrote: > >On Tue, 3 Apr 2001, Livio Righetti wrote: > >> Also we used Postgresql for Radius (authentication) et we have to make 3 > >> vacuum per day otherwise the first server is overload and the user go to > the > >> backup server. > >> > >> Is it normal or my Postgresql is not well configured ? > > > >Err, yes. > > > >Did you just do 40,000 inserts in a row, one after another? Realistic > >speed tests often have many requests coming in together, to simulate > >application- and web-usage. > > > >In addition, did you wrap this in a transaction? Otherwise, you're > >performing one transaction for *every single* insert, which is much slower > >than in a a transaction. > > > >(Generally speaking, if you want to just add 40,000 rows to a table, I'd > >use COPY, not INSERT ;-) ) > > I don't think COPY is useful or relevant in a normal ISP authentication > logging scenario. > > Wrapping more than one insert doesn't help either. I think you would > normally want to commit each customer's transaction individually. > > >From his figures he can sustain about 136 inserts a second. Is that good > enough for peak loads at a medium to big ISP? Well, I confess I was being a bit facetious. No, COPY isn't generally useful web apps, and, in many cases, you would handle each transaction separately. However, scheduling 40,000 INSERTs, all neatly following one after another, and measuring how long that takes isn't very realistic either! :-) 136 of anything a second seems good to me -- unless one is tracking micro things, like all TCP/IP requests made by all users at an ISP. Given the inexpensive price of hardware and the expensive cost of programmer time, it usually seems better to throw some money at 512MB, 7200RPM SCSI drives and such, rather that at paying a technologist to code in lots of Perl/PHP/Python/Pwhatever to build all the stuff into your web app that MySQL can't do for you. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: store procedure
On Mon, 9 Apr 2001, Juan wrote: > hello list, > i´m new in stores procedure and i want to do one writed in pl/pqsl, that it > returns a table. > i´m accesing from odbc. > > how the store procedure returns the table to process it in the programming > languaje(vb)? what type it returns? Juan -- At present, there is no reasonable way for a procedure to return a result set (like a table). Could you write a view that returns the data that you want? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: PostgreSQL on Windows (again)
On Mon, 9 Apr 2001, Steve Jorgensen wrote: Check your path to make sure that cygipc is in it. IIRC, configure is looking for libcygipc, and probably not finding it. > $ ./configure > creating cache ./config.cache > checking host system type... i686-pc-cygwin > checking which template to use... win > checking whether to build with locale support... no > checking whether to build with recode support... no > checking whether to build with multibyte character support... no > checking whether to build with Unicode conversion support... no > checking for default port number... 5432 > checking for default soft limit on number of connections... 32 > checking for gcc... gcc > checking whether the C compiler (gcc ) works... yes > checking whether the C compiler (gcc ) is a cross-compiler... no > checking whether we are using GNU C... yes > checking whether gcc accepts -g... yes > using CFLAGS=-O2 > checking whether the C compiler (gcc -O2 ) works... no > configure: error: installation or configuration problem: C compiler > cannot create executables. > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Very long running query
On 10 Apr 2001, Konstantinos Agouros wrote: > Hi, > > I have a query running for 97hours now and I am wondering if this can be made > any faster. > The Query is: > insert into dailyreport select timestamp(date >'1-8-2001'),a.category,'Observed',sum(b.count) as count from websensebycat a, >netscapereduce b where a.url = b.url and a.action='Observed' and a.datum='1-8-2001' >and b.datum='1-8-2001' and not exists (select url from urlcounts where >urlcounts.url=b.url) group by a.category; > > The tables websensebycat and netscapereduce do have indices on it. > Netscapereduce has 60020 entries and websensebycat has around 6000 entries. > Urlcounts has 55 entries. The whole thing is running postgres 7.1RC2. > Anybody has a tip to accelerate this. The whole thing is running on a E250 with > 64Bit Solaris7. what does explain command show about your query? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: personel appliactions?
On Tue, 10 Apr 2001, Stan Brown wrote: > I have been suing PostgreSQL quite happily in projects for several years > now. > > Now, I am thinking about actually using the computers at home for something > useful (what a concept :-)). > > So, I was wandering if anyone knows wehre I could find a few small > applicationsUsing PostgreSQL as abckaends? I'm looking for. > > 1. CD/Music lirary management. > 2. Library (books) management. > 3. Household inventory/shoping list applications. > 4. Calorie intake application. Check www.freshmeat.net; apps like this would be posted there. You may find some apps w/only a MySQL version; if so, consider doing the translation to PostgreSQL -- in most cases, little/no change in code is neccessary (especially if the author is using Perl or Python, which tend to have more abstracted DB interfaces than PHP); usually all that's needed is just to edit the SQL statements to create/select/etc. from the schema. If there is a calorie intake application, please don't tell me about it ;-) Good luck! -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Converting from access to pgsql..questions...
On Tue, 10 Apr 2001, mazzo wrote: > First of all, since i'm new to this mailing listHi all..!! > My first question.. > I have to convert an access 97 database to pgsql...are there any tools to do > this?? I checked the website but the link for the tools are not working so i > tried to export the tables from access using the ODBC driver...(i'll be > using access to insert data into these tables)...everything went just fine > except one small (well not so small..) problem... > If i export a table i have problems with the primary keys if they are set to > Counterswhen i export them to pgsql they get turned into numeric and > since all the primary keys of my database are counters i have big problems > since i will have to update these manually...Is there some way to solve this > prob..?? I was thinking of using a trigger...is this a good idea?? Can it be > done..?? Or is there a better way to solve these problems..?? > Thanks in advance for your answers..and excuse me for my english and if i > made any mistakes..(i'm italian..!) You want pgAdmin, which is a great Windows admin tool for PG, and can help w/the conversion. http://www.greatbridge.org/project/pgadmin/projdisplay.php There's a useful FAQ of info on PostgreSQL + Access at http://www.scw.org Your English is *much* better than my Italian! :-) -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Speaking of Indexing... (Text indexing)
On Tue, 10 Apr 2001, Poet/Joshua Drake wrote: > I've been experimenting a bit with Full Text Indexing in PostgreSQL. I > have found several conflicting sites various places on the net pertaining > to whether or not PostgreSQL supports FTI, and I was hoping I could find > an authoritative answer here - I tried searching the website's archives, > but the search seems to be having some problems. > > At any rate, I am running a CVS snapshot of 7.1, and I have been trying to > create a full text index on a series of resumes. Some of these exceed 8k > in size, which is no longer a storage problem of course with 7.1, but I > seem to have run into the wicked 8k once again. Specifically: Joshua -- CREATE INDEX ... creates an index on a field, allowing for faster searches, *if* you're looking to match the first part of that text string. So, if I have a table of movie titles, creating an index on column title will allow for faster searches if my criteria is something like title='Toto Les Heros' (or like 'Toto%' or such), but not (AFAIK) for title ~ 'Les' or title LIKE '%Les%'. The index doesn't help here. For these long fields you have, you probably want to search for a word in the field, not match the start of the field. A regular index isn't your answer. There is a full text indexing solution in the contrib/ directory of the source. It essentially creates a new table w/every occurence of every word fragment, with a reference back to the row that contains it. Searching against this is indexed, and is speedy. The only downside is that you will have a *large* table holding the full text index. More help can be found in the README file in contrib/fulltextindex HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: newbie question - INSERT
On Tue, 10 Apr 2001, Cefull Lo wrote:
> When I type INSERT INTO friend
> VALUES ('', '', '');
> it returns
> INSERT 19748 1
>
> what means of 19748 and 1?
It's the OID, a unique idenifier for everything in the database.
Read the Momjian book on the website -- it explains this very well.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: COPY from file to table containing unique index
On Tue, 10 Apr 2001, Joe Johnson wrote: > I have a table with over 1,000,000 records in it containing names and phone > numbers, and one of the indexes on the table is a unique index on the phone > number. I am trying to copy about 100,000 more records to the table from a > text file, but I get an error on copying because of duplicate phone numbers > in the text file, which kills the COPY command without copying anything to > the table. Is there some way that I can get Postgres to copy the records > from the file and just skip records that contain duplicates to the unique > index? I found that using PHP scripts to do inserts for a file of this size > take MUCH longer than I'd like, so I'd like to avoid having to do it that > way if I can. Any help is appreciated. Thanks! There are a few options. This was discussed yesterday, in the thread 'problem with copy command' -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: Changes in PostgreSQL 7.1
On Sun, 15 Apr 2001, Nils Zonneveld wrote: > I'm impressed, even outer-joins are supported now :-). I know it's > entirely different matter, but are the problems in the Win32 ODBC > interface solved? It wasn't possible to update tables in PostgreSQL from > a MS Access client. w/7.0.3, it was possible to update tables from Access, and it is in 7.1 If you don't have that experience, you probably have some settings wrong in the ODBC driver. There's a FAQ at www.scw.org/pgaccess. HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Logical expn not shortcircuiting in trigger function?
Problem: PostgreSQL apparently isn't short-circuiting a logical expression, causing an error when it tries to evaluate OLD in an INSERT trigger. PostgreSQL normally 'short-circuits' logical expressions; that is, once it figures out that it can't resolve an expression to truth, it stops evaluating all the possibilities. For example: CREATE FUNCTION crash() RETURNS boolean AS ' BEGIN RAISE EXCEPTION ''crash()''; RETURN TRUE; -- will never get here END; ' LANGUAGE 'plpgsql'; SELECT 1 WHERE crash(); ERROR: crash() SELECT 1 WHERE 1=2 AND crash(); ?column? -- (0 rows) doesn't crash() because it realizes that, as both 1=2 and crash() must return true, that it isn't worth checking crash(). However, I have a procedure called by a trigger that is called for both INSERTs and UPDATEs. For INSERTs, we always want to check a class capacity. For UPDATEs, we only want to check the capacity if the registration status has changed: CREATE FUNCTION reg_chk_capacity() RETURNS opaque AS ' DECLARE seats int; BEGIN IF TG_OP=''INSERT'' OR (TG_OP=''UPDATE'' AND (OLD.statuscode <> NEW.statuscode)) THEN seats := Reg_SeatsLeft(NEW.InstID); IF seats < 1 THEN RAISE EXCEPTION ''reg_chk_capacity__inst_filled: InstID=%, RegID=%'', NEW.InstID, NEW.RegID; END IF; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; (Reg_SeatsLeft() is a simple SQL function w/o any references to NEW or OLD) If I try to INSERT into this table, I get ERROR: record old is unassigned yet So, why hasn't the logic short-circuited? Am I missing something? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: CAST doesn't work :-(
On Sun, 22 Apr 2001, Antonio Gennarini - Geotronix wrote:
> Hi.
>
> I read in Momjian's book that to change the CHAR length of a column in a table (from
>30 -> 40) lets say, i'm to use the CAST command (pg 93). The fact is that the Posgres
>User's manual has nothing about cast and psql doesn't understand this command
>:-(((
>
> Anyone can tell me how to ajust a CHAR length in a table column? I found out that
>some email exceed 30 digits now i can't insert them and don't want to start from
>scratch.
>
> Thanks.
CAST doesn't change table attributes, it just changes the datatype of an
expression.
For example
SELECT CAST '2001-01-01' AS DATE;
turns the string '2001-01-01' into a date.
(Most PG users instead write the above as
SELECT date('2001-01-01')
or
SELECT '2001-01-01'::date
tho' they're PostgreSQL-isms)
You can't change the datatype of an existing column. Instead, create a new
table, insert the data into that, drop the existing table, and rename the
new one.
For example, if you have the table:
CREATE TABLE Pers (
id int not null primary key,
email varchar(30)
);
with some data in it:
insert into pers values (1, '[EMAIL PROTECTED]');
and you want to change email to varchar(50):
CREATE TABLE pers_new (
id int not null primary key,
email varchar(50)
);
[pg_dump can give you the CREATE statement for your table so you don't
have to re-create it by hand]
insert into pers_new select * from pers;
drop table pers;
alter table pers_new rename to pers;
If you have SERIAL datatypes (which use sequences behind the scenes,
you'll have to create the new table to use the existing sequence, and not
create a new one. In our example, that would be:
CREATE TABLE pers_new (
id int not null default nextval('pers_id_seq'),
...
);
rather than just "id serial not null".
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(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
[GENERAL] Re: CAST doesn't work :-( (fwd)
lution for this
is: DON'T DO IT. Instead, make your changes with pg_dump and pg_restore.
(Functions aren't a problem, because functions are re-created with every
new backend. If you have functions that refer to the Pers table, and you
drop it/rename it, you can simply quit psql and restart it to regain use
of your functions that referred to it.)
Doing it with pg_dump / pg_restore:
In complicated cases, the best solution is to make a dump file, edit that,
and restore that:
$ pg_dump -S postgres > database.sql
then your your favorite text editor to edit this. Some text editors
(such as Emacs/XEmacs) have an 'SQL mode' which can color-highlight or
help you edit an SQL file.
Then, quit your database and
\c template1
DROP DATABASE ;
CREATE DATABASE WITH TEMPLATE=template0;
\c
\i database.sql
will drop your database, recreate it, and read in your (edited) dump file.
Noite the use of TEMPLATE=template0. Template0 is a database that is empty
of all user functions/tables/etc. Template1 (the default template) starts
of empty of user functions, but often, people add
languages/functions/tables, etc to template1, making it a bad choice for
using in a dump/restore cycle.
The restore process shows many NOTICE messages, and if your syntax is
wrong, ERROR messages. To ensure that I don't miss any important messages,
I actually do my dump like this:
1) in my directory, I keep a tiny text file called recreate.sql. In it
is the following:
DROP DATABASE ;
CREATE DATABASE WITH TEMPLATE=template0;
\c
2) I add "\i recreate.sql" to the top of the dump file while editing it.
3) So that I can easily find the error messages, I do my recreating
like this:
$ echo "\i database.sql" | psql template1 1>/dev/null
By piping the \i command to psql, rather than redirecting standard
input ("psql template1 < database.sql", I can see line numbers in
the dump file for any error messages (very helpful!).
By redirecting standard output to /dev/null, I now only see
NOTICE and ERROR messages, so I can be sure everything works fine.
4) After re-creating, I re-dump the file to a new name:
$ pg_dump -S postgres > database1.sql
5) I compare the lengths the two files to make sure that they are
almost exactly the same. This is just to double-check to make
sure that pg_dump didn't screw anything up. If you're really
paranoid, you can diff the two files to see if they're the
same.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: max. size for a table
On Mon, 23 Apr 2001, Albert wrote: > Hi all~~ > > In the documentation of "Limitations of PostgreSQL", the maximum size for a > table is 64TB on all operating system. > I am using Linux(the limitation size of ext2 file is 2GB). > Would anyone tell me if there is any solution to make a table large than 2GB > in Linux using PostgreSQL. > Thank you very much. There are many patches for Linux kernels that remove the 2GB limit (or alternate filesystems to ext2). If you're not sure where to start, try a google search : '2GB limitation remove', which returns a bunch of hits. Good luck, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: BETWEEN clause
On Mon, 23 Apr 2001, Paul Tomblin wrote: > Is the "BETWEEN" clause inclusive or exclusive? ie if I say "WHERE > latitude BETWEEN 45 and 55", will I get examples where the latitude equals > 45 or not? Also, is "latitude BETWEEN 45 and 55" any more efficient than > "latitude >= 45 AND latitude <= 55", or is it just a stylistic thing? yes, yes, and no: select 'exclusive' where 2 between 1 and 3; ?column? -- inclusive test=# select 'inclusive' where 1 between 1 and 3; ?column? -- inclusive test=# create view its_really_the_same_thing as select true where 1 between 1 and 3; test=# \d its_really_the_same_thing ... View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3)); HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: Re: BETWEEN clause
On Tue, 24 Apr 2001, Paul Tomblin wrote: > Quoting will trillich ([EMAIL PROTECTED]): > > > test=# create view its_really_the_same_thing as select true where 1 > > > between 1 and 3; > > > > > > test=# \d its_really_the_same_thing > > > ... > > > View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3)); > > > > > > > > > HTH, > > > -- > > > Joel Burton <[EMAIL PROTECTED]> > > > Director of Information Systems, Support Center of Washington > > > > just wanted to say -- BEAUTIFULLY executed reply. > > not only did you answer the query succinctly and completely, > > you showed, quite clearly, how to find out such answers. > > I was impressed as well. I didn't know you could use \d to find the > definition of views like that. Yep. Now what I'd love is \recreate foobar which would execute DROP VIEW foobar; CREATE VIEW foobar AS ... So that I could conveniently up-arrow in psql's readline, and edit and re-create the view. Woud save me a hundred vi fumblings or X-mouse cutting and pastings a week. ... who needs GUIs? ;-) -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Performance: sql functions v. plpgsql v. plperl
Last night, I was doing some amateurish benchmarking and found that, contrary to my (admittedly uninformed) expectation, sql functions seem *slower* than plsql functions. Even for very simple things, like 'SELECT CASE WHEN $1 RETURN $1 ELSE $2 END' were slower than the plsql begin if $1 then return $1; else return $2; end if; end; by about 15%. However, my benchmarking was the type usually dreaded: a single person, playing around in a scripting language, running the test one a time seqeuentially, and just timing the results. Is there any real data on this? I also tried plperl v plpgsql, and found that, probably not surprisingly, there was about a 15% advantage to plpgsql. Of course, many things can be written much simpler in perl (such as string handling functions). Even so, though, a find-the-first-letter-of-all-significant-words function written about 1.5 years ago in plpgsql (a pretty awful, nested, letter-by-letter parser) was only about twice as slow as the perl split() replacement. Looks like our little plpgsql is quite a speed demon. (as always, IANAPB [ I am not a professional benchmarker ], YMMV) -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: Performance: sql functions v. plpgsql v. plperl
On Wed, 25 Apr 2001, Tom Lane wrote: > Joel Burton <[EMAIL PROTECTED]> writes: > > Last night, I was doing some amateurish benchmarking and found that, > > contrary to my (admittedly uninformed) expectation, sql functions seem > > *slower* than plsql functions. > > IIRC, sql functions are re-parsed/planned on each execution, whereas > plpgsql functions cache their parse trees and execution plans. > Depending on exactly what you were doing, that might explain the > difference. Why is this? I'm just delving into the source code in earnest for the first time, so, forgive any awful errors, but it seems like we have the plumbing for this in views/rules... couldn't the parse tree be cached from this for each backend? Or are SQL functions mildly deprecated since they could always be replaced by the plpgsql function begin return ... end; ? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: help with serial type
On Thu, 26 Apr 2001, Gregory Wood wrote: > > I don't know if you can name a column "date" because I think it's a > > reserved word. > > Oddly enough, it *does* work (at least on my version of 7.1), although I > would recommend against doing it if for no other reason than it's confusing. If you wrap them in double-quotes, you can use most reserved words as system identifiers. But I wouldn't -- some cheesy client implementation might choke on them, and better to not find that out later. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: Postgres Bug (ALTER TABLE problem)
On Thu, 26 Apr 2001, Boulat Khakimov wrote: > Hi, > > I've discovered a bug in Postgres. When you rename > a table, the corresponding triggers for that table > are not updated. Yep. Use ALTER TABLE ADD CONSTRAINT to add 'em back in. More info can be found in the Ref Int tutorial I just submitted at techdocs.postgresql.org. HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: Re: Need for newbie friendly docs (was Newbie struggling...)
On Fri, 27 Apr 2001, Justin Clift wrote: > Newbies have interesting ideas sometimes too. After all, they've spent > their time learning about something OTHER than Unix. :-) something... other... than... unix ? Justin, I'm not clear on what you mean. Can you give us an example? ;-) But really: sure! Oracle, ferinstance, realizes that many people run Unix *because* they want to run Oracle in a stable server environment. People may be making the same decision about PostgreSQL. We shouldn't have to write this, though... if people could contribute the great 'basics of Unix you need to know to be a decent DBA' stuff that's already on the web, we'd have plenty. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: Re: Need for newbie friendly docs (was Newbie struggling...)
On Thu, 26 Apr 2001, Clayton Vernon wrote: > Joel- > > In all fairness, there aren't any good HTML-based Unix tutorials. I've > looked for them. In particular, Sun is worthless here, curious since their > Java tutorial is (IMO) really well done. > > I'm hoping PostgreSQL is MORE stable than Oracle in our Solaris environment. > The massive CPU/disk footprint of Oracle generates reliability errors in our > databases which I hope can be avoided in a leaner package. I remember struggling for days to install Oracle 8i on a 192MB laptop (don't ask why... fscking client requirement). Yeeks. > This marvelous mailing list really gives me confidence. Yep. Overall, I think PG offers *great* online support. Some possible places to start looking: 1) Does anyone have a copy of the O'Reilly book "Unix for Oracle DBAs Pocket Reference" (http://www.oreilly.com/catalog/unixoracledbapr/) This might be the kind of information that would be helpful. 2) Eric Raymond has "Unix and Internet Fundamentals" HOWTO at http://linuxdocs.org/HOWTOs/Unix-and-Internet-Fundamentals-HOWTO/index.html 3) >From DOS/Windows to Linux HOWTO http://linuxdocs.org/HOWTOs/DOS-Win-to-Linux-HOWTO.html 4) The Linux Reading List HOWTO http://linuxdocs.org/HOWTOs/Reading-List-HOWTO/index.html -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: I am now Linux and PostgreSQL user, have a question
On Fri, 27 Apr 2001, Isiah Thomas wrote: > I read some docunment say that to start the postgres service must type this > command > > /etc/rc.d/init.d/postgres start That's pretty specific to RedHat and RedHat-derived systems. (And then, only if you install PostgreSQL from the RPMs, which if you're using RedHat, you probably are). A less distribution-specific way of saying the same thing is $ service postgres start But, really, you probably want to do something like $ pg_ctl -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: SQL Where LIKE - Range it!
On Thu, 26 Apr 2001, Steagus wrote: > > What I'd like to do is pull a list of records where there is a range > of last names; say from A - F. > select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%' > - for example. > > The above code I've tried for this doesn't seem to work as I'd expect > it too? > I've even done > select * from table where last_name LIKE 'A%' AND LIKE 'F%' > > Can anyone provide some details or insights on how to accomplish this? LIKE A% AND LIKE F% means "must start with A *AND* must start with F", so the name "Anderson" would fail because it does start with A, but doesn't start with F. Something like LIKE "A%" OR LIKE "B%" OR LIKE "C%" ... OR LIKE "F%" would do the trick, but slowly, and it's a pain to write out. I'd use BETWEEN 'A' AND 'FZZZ' (or, to be more precise, >='A' and <'G') Keep in mind that PostgreSQL is case-sensitive, so if me name were 'Joel deBurton', you wouldn't find me. If you have lower-case starting names, you'll want to see (BETWEEN 'A' AND 'FZZZ') OR (BETWEEN 'a' AND 'fzzz') HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: running pgaccess on localhost
On Fri, 27 Apr 2001, Mike Goetz wrote: > Hello, pgsql newbie here... > > I'm trying to run pgaccess on a database I've created, and I get the > following error: > > PostgreSQL error message: Connection to database failed > PQconnectPoll() -- connect() failed: Connection refused > Is the postmaster running (with -i) at 'localhost' and accepting > connections on TCP/IP port '5432'? > > Now the postmaster is not normally set up with the -i option, and I'd like > to keep it that way since I'm intending to use my workstation for local > development only (are there any security issues by activating the -i > option?). Indeed turning the -i option on does in fact allow me to use > pgaccess without error. > > However, since I'm running pgaccess from a terminal window on my machine I > would have thought that this would constitute a local domain socket > connection (or at least a connection from the localhost), so I'm confused > by the error message -- am I missing something or do I really have to > enable TCP/IP connections if I want to initiate database access using > pgacess from a terminal window? The fact that you're running pgaccess from a term window doesn't mean that pgaccess connects to PG w/a local socket. Someone may know how to configure pgaccess to use a local socket. If not, use the -i switch, and edit $PGDATA/pg_hba.conf to block access from other machines. (This is the default setting.) -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: PHPPgAdmin or MS Access
On Mon, 30 Apr 2001, Randall Perry wrote: > Got a simple PgSQL database running on a linux server which I need the > client to access for inserts and updates from a Win box. > > What's the simplest way to do this; using PHPPgAdmin, or MS Access via ODBC? > > Where can I find detailed info on using ODBC with Access and PgSQL? FAQ at www.scw.org/pgaccess -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] RE: Joining more than 2 tables
On Wed, 2 May 2001, Jeff Meeks wrote: > What I am looking for is a query that will return a list of id's with a > sum from table b and a sum from table c like this: > > id namesum(b) sum(a) > 1 shell34 50 > 2 jeff 40 20 > > Thanks > Jeff Meeks > [EMAIL PROTECTED] SELECT id, name, (SELECT sum(b) FROM b WHERE b.id=a.id) AS sum_b, (SELECT sum(c) FROM c WHERE c.id=a.id) AS sum_c FROM a; -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: best way to implement producer/consumer in Perl
On 2 May 2001, Vivek Khera wrote:
> I've got an application that creates work and posts the work requests
> to a table. Each work request is just a single row in the table.
> I've also got two systems that do the work based on the requests in
> the table.
>
> It seems that ideally, I could use the LISTEN/NOTIFY features to tell
> the consumers when there is work. However, what I really need is a
> blocking LISTEN. That is, it just sits there until it gets a NOTIFY.
>
> Right now, the consumer checks for work every so often, and most of
> the time finds none.
>
> What's the best way to implement such a thing in Perl? Is there a
> blocking LISTEN?
I'm not a serious expert on such things, but, in Perl, LISTEN/NOTIFY is
handled through DBI/DBD, so the LISTEN/NOTIFY notification will come
only when a regular query is sent.
Could you possibly do something like:
while (1) {
execute regular, simple query (eg "SELECT 1;")
check for notification, and handle it
sleep for x minutes/seconds/msecs
}
It's still a loop w/a query, but at least the query should be extremely
speedy and have low impact on DB performance.
If you find a better way, please let me know.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: DROP TABLE wildcard
On Wed, 2 May 2001, Andy Koch wrote:
> Is it possible to drop multiple tables with SQL on the system tables:
>
> I tried this:
>
> DROP TABLE from pg_tables where tablename LIKE 'table_num_%';
>
> Which for whatever reason would delete 0 items despite it should have
> matched on several.
>
> Of course I'm not even sure pg_tables would be the smart place to make the
> delete from?
First of all, DROP TABLE doesn't use SELECT SQL syntax. There is no
DROP TABLE ... FROM ...
Second, pg_tables is a *view* of pg_class. If you want to make
changes, make them to pg_class.
That said, though, I don't think you want to drop tables by just deleting
the rows from pg_class. You might want to hear what the hackers have to
say about the subject, but I'm assuming its a Bad Idea.
No, there is no wildcharacter in the DROP TABLE syntax.
You could, though, make a plpgsql function that dropped tables,
and took a text parameter that it used as a regex. Then you could
SELECT dev_drop_table('tblfoo.*').
(or, instead of regex-able param, do a like-able param)
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(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
[GENERAL] Re: Starting the Server at Boot
On Thu, 3 May 2001, Peter Eisentraut wrote: > Hunter Hillegas writes: > > > Anyway, I want to start my Postgres server at boot (7.1). I tried the script > > that came with it and it doesn't work. It seems to start the server but it > > doesn't accept connections (yes, the -i option is on)... Is there a trick? > > Enable it in the postgresql.conf file. (The -i option should work. > Odd...) Perhaps the reason that -i didn't work is that the scripts use pg_ctl. Don't the options for that need to be wrapped in -o, as in '-o "-i"'. It's easy to get that wrong. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Cygwin PostgreSQL seems to work fine
If you've ever tried to install PostgreSQL under Windows, you know, despite the help offered by several FAQs and on these lists, it can be a pain. Mercifully, the latest cygwin downloads include PostgreSQL, and it works fine, almost out of the box. 1) Install cygwin from www.cygwin.com. 2) Donwload the binary cygipc distribtuion from http://www.neuro.gatech.edu/users/cwilson/cygutils/V1.1/cygipc/ (Tested w/cygipc 1.09-2) 3) Move the cygipc tarball to the root of your cygwin installation (by default, c:\cygwin) 4) In cygwin, unpack this with: tar xzvf /cygipc-1.09-2.tar.gz 5) Start cygipc running with /usr/local/bin/ipc-daemon & 6) initdb -D /var/pgsql 7) pg_ctl -D /var/pgsql 8) psql template1 Worked fine for me, and much easier than the tangle of things to try when working from the source install. (Tried on Win2000 professional & Win2000 server). The short story: if you've tried before and given up, try now. HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] RE: Unique or Primary Key?
> > Besides, I couldn't see much use in creating it as a primary > > key. How > > would I ever reference it from another table? If you're questioning how to use a multi-field primary key, it's easy... create table p (id1 int not null, id2 int not null, primary key(id1, id2)); create table c (id1 int, id2 int, foreign key (id1, id2) references p); insert into p values (1,2); insert into c values (1,1); insert into c values (1,0); ERROR: referential integrity violation - key referenced from c not found in p -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: mysql to Pgsql
On Thu, 3 May 2001, Bruce Momjian wrote: > [ Charset ISO-8859-1 unsupported, converting... ] > > I think I heard about a script that would convert mysql dump files to Pgsql > > dumps. Can someone remind me where they are? > > > > Saludos... :-) > > In 7.1 /contrib. And more info about it at techdocs.postgresql.org. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: cast bit to boolean?
On Thu, 3 May 2001, Vivek Khera wrote: > How might one case a BIT to a BOOLEAN? For example, I want to return > rows which have non-zero bit representation for, say, (sel_a & b'0011'). > That is, rows with the first or second bit set. > > I tried an explicit CAST, and just the query directly, but the cast > say you cant cast type 'bit' to 'bool', and the direct query says > WHERE clause must return type bool, not type bit: > > create table t1 (sel_a BIT(6),sel_b BIT(6)); > insert into t1 values (b'01',b'001000'); > select * from t1 where sel_a & b'11'; > ERROR: WHERE clause must return type bool, not type bit > > Where might I look for this? The manual is quite sparse regarding BIT > types. There might be a better way, but you can write a conversion routine: create function bool(bit) returns bool as ' begin if $1 = ''1''::bit then return true; end if; return false; end;' language 'plpgsql' with (isacachable); should do the trick, albeit more slowly than a built-in or C function. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: Technical Suggestion Pl. Clarrify.
On Wed, 2 May 2001, sathya wrote: > If possible kindly get me any alternate methods to get it done. > > My platforms are > Visual Basic, SQL server. An excellent alternative method to getting this done would be: 1) Delete SQL server from your system. 2) Install PostgreSQL, a very nice Open Source database system. for extra credit, s/Visual Basic/Python/g; ... and just think of the money you'll save! Seriously though, this is a list for the discussion of issues around PostgreSQL. Questions about SQL Server are not appropriate here. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Newbie Question
On Tue, 1 May 2001, Clay & Judi Kinney wrote: > How do I create an autoincrement field in a postgresql table??? > > What are the correct field type and parameters > > Any help would be appreciated. CREATE TABLE foo ( id SERIAL, ... ) it comes out as an int4 or integer type. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Postgresql table reloading
On Fri, 4 May 2001, David Wall wrote: > Sorry to bother you personally, but I read your "Updating columns in PostgreSQL >tables effectively" page and had a question. > > Will the temporary table scheme work if the table contains blobs (identified by >OIDs)? In my case, I don't need to preserve the actual OID number (it's not a key), >but I do need the blob that's behind the OID to be copied. Will the INSERT..SELECT >FROM syntax carry the blobs over? (cross-posted to pgsql-general, so others can find this in the archives) In the table, you're storing the OID of the large object itself, so if you copy all the contents of the table, so far as I can see, yes, you'd still have the reference to your large object. (The table row of your current table has an OID, and when you create the temporary table--which becomes the new table--it will have a different OID, but that shouldn't matter, since all you care about is the LO oid. However, I'm not a BLOB expert, and haven't used them in any real systems. Do try this out on a test table before doing it for real. HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: a primer on trigger?
On Fri, 4 May 2001 [EMAIL PROTECTED] wrote: > On Fri, May 04, 2001 at 12:47:02PM -0400, Joel Burton wrote: > > > > Hmmm... this raises an interesting question. > > > > Would it be possible to hook into (via trigger or other mechanism) so that > > we could execute a function on commit? There are PG triggers to do things > > like send email, etc., which, yes, can't be undone if the transaction > > Could you kindly point me a reference > to this 'trigger that emails'? I just > want to see how it's done and see if > I can modify it to my need. Look at the pgMail message posted yesterday on the list. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: a primer on trigger?
On Fri, 4 May 2001 [EMAIL PROTECTED] wrote: > On Fri, May 04, 2001 at 12:48:24PM -0400, Joel Burton wrote: > > > > What is the system call? Could you do this in pl/tclu, the > > untrusted version of pl/tcl? > > > > I don't know anything about pl/tcl and all > that stuff. I will take a look later. But > because I'm pressed for time I will go > with my existing stuff If you've never used TCL, you're gonna hate it, I fear. (Apologies to the tcl fans, but it's synax can be rather weird to people weaned on perl, IMHO) Hopefully, one day, pl/perl will be a full PL, with support for much more stuff. pl/python, in beta, has these features, so perhaps pl/perl, out of competition or spite, will pull ahead. :-) -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
