Re: [HACKERS] [ADMIN] how to alter sequence.
On Wed, Dec 04, 2002 at 09:33:52AM -0800, Dustin Sallings wrote:
Around 20:41 on Dec 4, 2002, Hannu Krosing said:
What's wrong with this:
dustin=# create sequence test_seq;
CREATE SEQUENCE
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
dustin=# select setval('test_seq', );
setval
(1 row)
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
Dustin --
The thread here is about how to raise the *max* value for the sequence,
not how to set the current value higher. The sequence in question was
created with a too-low maximum value (see help on CREATE SEQUENCE for
options); the user now wants to raise it.
- J.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] numeric to text (7.3)
On Mon, Dec 02, 2002 at 01:35:47PM -0500, Rod Taylor wrote:
template1=# select text(2.000::numeric);
text
---
2.000
(1 row)
The text(numeric) function doesn't round numbers. :(
This is bug or feature? :)
I'd say feature in that it doesn't reduce the precision of the number.
... and, of course, you can round with:
joel@joel=# select round('2.000'::numeric);
round
---
2
(1 row)
joel@joel=# select round('2.000'::numeric,2);
round
---
2.00
(1 row)
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] numeric to text (7.3)
On Mon, Dec 02, 2002 at 08:23:24PM +0100, Szima Gábor wrote:
OK, but:
template1=# select round('2.001'::numeric);
round
---
2
(1 row)
template1=# select round('2.001'::numeric,2);
round
---
2.00
(1 row)
The good idea (in 7.2):
template1=# select text('2.000'::numeric);
text
--
2
(1 row)
template1=# select text('2.001'::numeric);
text
---
2.001
(1 row)
This feature is missing from 7.3..
Not sure I'd call it a feature -- ISTM that text(numeric) should show
all the precision you gave it, and not shave it down to the
least-precise number that is still equal.
Anyhoo, you can get what you want with some ugly-but-straightforward
trimming:
(in 7.3):
joel@joel=# select rtrim(rtrim('2.000'::numeric, '0'),'.');
rtrim
---
2
(1 row)
joel@joel=# select rtrim(rtrim('2.001'::numeric, '0'),'.');
rtrim
---
2.001
(1 row)
Easy enough to make this into a function trim_as_much(numeric) or
somesuch.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] wierd AND condition evaluation for plpgsql
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Alessio Bragadini Sent: Thursday, May 30, 2002 9:04 AM To: PostgreSQL Hackers Subject: Re: [HACKERS] wierd AND condition evaluation for plpgsql On Tue, 2002-05-28 at 16:09, Joel Burton wrote: Actually, at least in some cases, PG does short-circuit logic: joel@joel=# select false and seeme(); joel@joel=# select true and seeme(); If seeme() returns NULL, shouldn't both SELECTs return NULL, and therefore not be short-circuit-able? Sorry, I am a little confused. In my example, seeme() returns true, not NULL. However, the short-circuiting came from the other part (the simple true or false) being evaluated first. So, regardless of the returned value of seeme(), SELECT FALSE AND seeme() would short-circuit, since FALSE AND ___ can never be true. Of course, if seemme() returns NULL, then the end result would be false. - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] wierd AND condition evaluation for plpgsql
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 30, 2002 10:44 AM To: Joel Burton Cc: Alessio Bragadini; PostgreSQL Hackers Subject: Re: [HACKERS] wierd AND condition evaluation for plpgsql Joel Burton [EMAIL PROTECTED] writes: Actually, at least in some cases, PG does short-circuit logic: joel@joel=# select false and seeme(); joel@joel=# select true and seeme(); If seeme() returns NULL, shouldn't both SELECTs return NULL, and therefore not be short-circuit-able? In my example, seeme() returns true, not NULL. However, the short-circuiting came from the other part (the simple true or false) being evaluated first. So, regardless of the returned value of seeme(), SELECT FALSE AND seeme() would short-circuit, since FALSE AND ___ can never be true. Yes. Per the SQL standard, some cases involving AND and OR can be simplified without evaluating all the arguments, and PG uses this flexibility to the hilt. You might care to read eval_const_expressions() in src/backend/optimizer/util/clauses.c. Some relevant tidbits: * Reduce any recognizably constant subexpressions of the given * expression tree, for example 2 + 2 = 4. More interestingly, * we can reduce certain boolean expressions even when they contain * non-constant subexpressions: x OR true = true no matter what * the subexpression x is. (XXX We assume that no such subexpression * will have important side-effects, which is not necessarily a good * assumption in the presence of user-defined functions; do we need a * pg_proc flag that prevents discarding the execution of a function?) * We do understand that certain functions may deliver non-constant * results even with constant inputs, nextval() being the classic * example. Functions that are not marked immutable in pg_proc * will not be pre-evaluated here, although we will reduce their * arguments as far as possible. ... Other relevant manipulations include canonicalize_qual() in src/backend/optimizer/prep/prepqual.c (tries to convert boolean WHERE expressions to normal form by application of DeMorgan's laws) and for that matter the entire planner --- the fact that we have a choice of execution plans at all really comes from the fact that we are allowed to evaluate WHERE clauses in any order. So there's not likely to be much support for any proposal that we constrain the evaluation order or guarantee the evaluation or non-evaluation of specific clauses in WHERE. (The XXX comment above is an idle aside, not something that is likely to really happen.) Thanks, Tom, for the pointers to the full story. Is there any generalizable help would could offer to people who write functions that have side effects? Don't use them in WHERE (or ON or HAVING) clauses? Evaluate the function in a earlier db call, then plug the resolved results into the SQL WHERE statement? I've lived without having this bite me; I'd think that side-effect functions would be unusual in a WHERE clause. I'm just wondering if we should work this into the docs somewhere. (Or is it? I took a look, but didn't see anything). - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] wierd AND condition evaluation for plpgsql
Actually, at least in some cases, PG does short-circuit logic: create function seeme() returns bool as ' begin raise notice ''seeme''; return true; end' language plpgsql; joel@joel=# select false and seeme(); ?column? -- f (1 row) joel@joel=# select true and seeme(); NOTICE: seeme ?column? -- t (1 row) In your case, the problem is short-circuiting a test, it's that the full statement must be parsed and prepared, and it's probably in this stage that the illegal use of old. in an insert jumps up. HTH. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Louis-David Mitterrand Sent: Tuesday, May 28, 2002 3:21 AM To: [EMAIL PROTECTED] Subject: [HACKERS] wierd AND condition evaluation for plpgsql Hi, I just noticed plpgsql evaluates all AND'ed conditions even if the first one fails. Example: elsif TG_OP = ''UPDATE'' and old.type_reponse = ''abandon'' This will break stuff if the trigger is used on INSERT as old.type_reponse will be substituted and return an error. Shouldn't plpgsql shortcut AND conditions when a previous one fails, as perl does? -- OENONE: Quoi ? PHEDRE: Je te l'ai prédit, mais tu n'as pas voulu. (Phèdre, J-B Racine, acte 3, scène 3) ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Exposed function to find table in schema search list?
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Sunday, May 19, 2002 6:40 PM To: Joel Burton Cc: Joe Conway; Pgsql-Hackers@Postgresql. Org Subject: Re: [HACKERS] Exposed function to find table in schema search list? Joel Burton [EMAIL PROTECTED] writes: Is the use of regclass going to prove to be very implementation-specific? Sure, but so would any other API for it. Well, sort of, but if we had been promoting a function tableoid(text) returns oid, we wouldn't have to make any change for the move to regclass, would we? I mean, it's specific to PG, but a simple wrapper might outlive the next under-the-hood change. On a related note: is there an easy way to use this ::regclass conversion to test if a table exists in a non-error returning way? (Can I use it in a select statement, for instance, returning a true or false value for the existence or non-existence of a table?) - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Exposed function to find table in schema search list?
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, May 24, 2002 1:33 PM To: Joel Burton Cc: Pgsql-Hackers@Postgresql. Org Subject: Re: [HACKERS] Exposed function to find table in schema search list? At the moment regclass conversion raises an error if the item isn't found; this follows the historical behavior of regproc. We could possibly have it return 0 (InvalidOid) instead, but I'm not convinced that that's better. In the case of regproc, not erroring out would lose some important error checking during initdb. Fair enough. Is there any way to handle this error and return a false? (People frequently ask how can I check to see if a table exists, and not all interfaces handle errors the same way, but everyone should know how to deal with a table result, so that we can provide a 7.3 version of SELECT 1 FROM pg_class where relname='xxx'. Thanks! - J Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Is 7.3 a good time to increase NAMEDATALEN ?
Noticed that increasing NAMEDATALEN to 128 is still on the TODO. Given that the addition of namespaces for 7.3 is going to require many client utilities to be updated anyway, is this a reaonable time to bring this increase into the standard distribution? It seems like it would be minor pain whenever we do this, and 7.3 could be as good a time as any. - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Exposed function to find table in schema search list?
I'm writing a function that accepts a table name and digs some information out about it. I'm developing on 7.3cvs w/schemas, and wanted my function to use schemas. If the user gives a full schema name (s.table), I find the table in pg_class by comparing the ns oid and relname. However, if the user doesn't give the full schema name, I have to find which table they're looking for by examining current_schemas, iterating over each schema in this path, and trying it. Is there a function already in the backend to return a class oid, given a name, by looking up the table in the current_schemas path? Would it make sense for us to expose this, or write one, so that this small wheel doesn't have to be re-invented everytime someone wants to find a table by just the name? Something like: findtable(text) returns oid findtable(foo) - oid of foo (given current search path) findtable(s.foo) - oid of s.foo I can write something in plpgsql (iterating over the array, checking each, etc.), however, it would be nice if something was already there. Any ideas? Thanks! - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Exposed function to find table in schema search list?
-Original Message- From: Joe Conway [mailto:[EMAIL PROTECTED]] Sent: Sunday, May 19, 2002 5:25 PM To: Joel Burton Cc: Pgsql-Hackers@Postgresql. Org Subject: Re: [HACKERS] Exposed function to find table in schema search list? Joel Burton wrote: Is there a function already in the backend to return a class oid, given a name, by looking up the table in the current_schemas path? Would it make sense for us to expose this, or write one, so that this small wheel doesn't have to be re-invented everytime someone wants to find a table by just the name? Something like: findtable(text) returns oid findtable(foo) - oid of foo (given current search path) findtable(s.foo) - oid of s.foo I can write something in plpgsql (iterating over the array, checking each, etc.), however, it would be nice if something was already there. I think this already exists: test=# select 'joe.foo'::regclass::oid; oid 125532 (1 row) test=# select 'public.foo'::regclass::oid; oid 125475 (1 row) test=# select 'foo'::regclass::oid; oid 125475 (1 row) test=# select current_schema(); current_schema public (1 row) Perfect! I was hoping to avoid re-creating the wheel. Thanks, Joe. Is the use of regclass going to prove to be very implementation-specific? Would it make sense for us to create a function that abstracts this? - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updated CREATE FUNCTION syntax
Tom Lane [EMAIL PROTECTED] said: Seems like the only way to do that in the backend would be to find a way of slipping the function text past the lexer/parser entirely. While I can imagine ways of doing that, I think it'd be a *whole* lot cleaner to fix things on the client side. How do you feel about a psql hack that provides a function definition mode? More generally it could be a mode to enter random text and have it be converted to an SQL literal string. Perhaps psql= create function foo (int) returns int as psql- \beginliteral psql-LIT begin psql-LIT x := $1; psql-LIT ... psql-LIT end; psql-LIT \endliteral psql- language plpgsql; Essentially, \beginliteral and \endliteral each convert to a quote mark, and everywhere in between quotes and backslashes get doubled. We might want to specify that the leading and trailing newlines get dropped, too, though for function-definition applications that would not matter. Tom -- Given that 98% of my function defining is done is psql, this would be fine for me and solve my frustrations. It wouldn't help people that build functions in scripting languages or non-psql environments, however, but I don't know how common this is. What do others think? Thanks! -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Updated CREATE FUNCTION syntax
Tom Lane [EMAIL PROTECTED] said: Joel Burton [EMAIL PROTECTED] writes: Given that 98% of my function defining is done is psql, this would be fine for me and solve my frustrations. It wouldn't help people that build functions in scripting languages or non-psql environments, however, but I don't know how common this is. True, but I'm thinking that other development environments could provide equivalent features. (I seem to recall that pgAdmin already does, for example.) ISTM the reason we've not addressed this for so long is that no one could think of a reasonable way to solve it on the backend side. Maybe we just have to shift our focus. Out of curiosity, Tom, why the preference for a solution like this rather than allowing for a much-less-common-than-' delimiter for the create function syntax? (Such as the [[ and ]] I suggested a few posts ago?) This would seem like something that wouldn't seem too difficult to do, and would work in all environments. That would have the advantage of being consistent as users switched from writing functions in psql to writing function-writing functions, to writing functions in other environments, etc. Thanks, - J. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Set-returning function syntax
For those who want to play on the bleeding edge of CVS, can someone provide the syntax for the recently-checked-in set-returning functions? I've got it figured out when I'm returning a many rows of single column, but not for many rows of several columns. If someone can do this, and no one has put together docs on this feature, I'll volunteer to write this up. Thanks! - J. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Updated CREATE FUNCTION syntax
-Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 9:37 AM To: Joel Burton Cc: PostgreSQL Development Subject: RE: [HACKERS] Updated CREATE FUNCTION syntax Joel Burton writes: Is there any standardized way of handling the single-quotes within function definition? Rather than doubling them up (which can make for very messy code when your scripting language uses single quotes!), allowing another symbol to be used, with that symbol be declared in the CREATE FUNCTION line? Interbase uses a system like this: you can set the delimiter to anything you want and use that instead of '. I think we need something like that. How exactly does Interbase set the delimiter? Keep in mind that our lexer and parser are static. Actually, now that I've thought about it for a moment, Interbase doesn't use a different delimiter, it allows a different end-of-line character. I've forgotten the exact syntax, but it's something like (Interbase doesn't allow functions like this, it uses these for stored procedures, but the basic idea is here): SELECT * FROM SOMETHING; SET EOL TO ; CREATE FUNCTION() RETURNS ... AS BEGIN; END; LANGUAGE plpgsql SET EOL TO ; SELECT * FROM SOMETHING; So that it's legal to use ; in the function, since the parser is looking for a different character to end the complete statement. I think it would be more straightforward to see something like: CREATE FUNCTION XXX() RETURNS ... AS # BEGIN; END; # LANGUAGE plpgsql DELIMITER #; But, with a static lexer/parser, that would be tricky, wouldn't it? Would it work to allow, rather than free choice of delimiters, to allow something other than single quote? Probably 95% of functions contain single quotes (and many scripting languages/development environments treat them specially), guaranteeing that you'll almost always have to double (or quad- or oct- or whatever!) your single quotes. If it's not too offensive, would something like CREATE FUNCTION XXX() RETURNS AS [[ BEGIN; END; ]] LANGUAGE plpgsql DELIMITED BY BRACES; work? Without the delimited by braces, the functions would be parsed the same (single quotes), with this, it would allow [[ and ]]. Someone who used [[ or ]] in their functions (perhaps as a custom operator or in a text string) would have to quote these (\[\[ and \]\]), but this would be __much__ less frequent than having to deal with single quotes. Nothing should break, since they have to choose to use the 'delimited by braces' option. It's not as nice as getting to choose your own delimiter, but it would solve the problem for most of us just fine and wouldn't seem too hard to implement. Functions are in SQL99, aren't they? Does the standard suggest anything here? - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Updated CREATE FUNCTION syntax
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Peter Eisentraut Sent: Thursday, May 16, 2002 1:22 PM To: PostgreSQL Development Subject: [HACKERS] Updated CREATE FUNCTION syntax As per earlier vague hint, I'm bringing the CREATE FUNCTION syntax in line with SQL99. Everything is fully backward compatible. Here is the new synopsis: CREATE [OR REPLACE] FUNCTION name (args) RETURNS type option [ option... ] [WITH (...)]; where option is any of these in any order: AS string [,string] LANGUAGE name IMMUTABLE STABLE VOLATILE CALLED ON NULL INPUT -- SQL spelling of not strict RETURNS NULL ON NULL INPUT-- SQL spelling of strict STRICT [EXTERNAL] SECURITY DEFINER -- SQL spelling of setuid [EXTERNAL] SECURITY INVOKER -- SQL spelling of not setuid IMPLICIT CAST (The SECURITY options are noops right now, but I'm planning to implement them next.) The WITH (...) options are still there, but sort of less encouraged, I guess. Is there any standardized way of handling the single-quotes within function definition? Rather than doubling them up (which can make for very messy code when your scripting language uses single quotes!), allowing another symbol to be used, with that symbol be declared in the CREATE FUNCTION line? Interbase uses a system like this: you can set the delimiter to anything you want and use that instead of '. - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TRUNCATE
I still highly recommend that it be a drop foreign key, grab data, truncate, import data, reapply foreign key (which will double check your work) as I believe data and schema integrity should be high goals of Postgresql (myself anyway). I agree that they should be high goals. However, I'd like to know what your doing. ie. Why is this method the fastest and easiest way. It's easier than dropping and recreating rules because that takes a bit of trouble. (If there were any easy way in pg_dump or in psql directly to get the text of just the rules/triggers/RI declarations for a table, that would make it a bit easier than pulling that out of the other table stuff in pg_dump output). It's easier than a full-database dump/fix/restore because sometimes (hopefully now historically :) ) pg_dump wasn't a perfect tool: for a while, it would drop RI statements, or occassionally have a hard time recreating a view, etc. Plus, of course, with a large database, it can take quite a while to process. A limited-to-that-table dump/fix/restore can be a problem because of the interrelationships of RI among tables. If there were any easier way to dump information about a table so that I could restore the RI that other tables have on it, that might be a solution. Given a dataset, how much (%age wise) do you generally modify when you clean it up? And what is the general dataset size (half million records?). More often than not, I'm working with complex tables and fairly small # of rows. Perhaps 30 fields x 10,000 records. I'm making the assumption you almost never delete data (primary key wise), otherwise foreign keyd data may no longer align. I'm also making the assumption your either the sole user of the database, or have a long period where the database is not in use (overnight?). No, I wouldn't delete things. I don't want to bypass RI, just not have to deal with removing/creating all the rules every time I need to clean up some data. In most cases, yes, I can either take db offline for an hour or ensure that there will be no writes to the db. What do you use to clean it up? Custom script for each job? Regular expressions? Simple spreadsheet like format filling in numbers? Complete dump and replace of the data? Generally, I'm doing something like pulling the data into a text file and using regexes or spreadsheet tools to clean it up. Some of which could be done (through plperl or plpython or such), but is often easier with full text manipulation/emacs/etc. Sometimes, though, I'm just cleaning out test data. For example: often, I'll create a table where records can't be deleted w/out logging information going into another table (via rule or trigger, and I usually prohibit deletions at all from the log table). I'll put some fake records in, delete a few, see the logging data, and later, when I want to delete the fake data ( the fake logging data), I'll use TRUNCATE. I could only do this w/a normal DELETE by dropping these rules/triggers, deleting, and re-creating. Which is more of a pain than I'd like to do. Given that only the owner of a table can truncate it, I'm not too worried about the security of truncate: the owner is the person who would understand the ramifications of truncate vs. delete. Having it either emit a warning that there were triggers/rules/RI or (better) requiring a FORCE parameter to truncate when there are might make others feel safe, though. - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TRUNCATE
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Tom Lane Sent: Sunday, May 12, 2002 12:30 PM To: Rod Taylor Cc: Hackers List Subject: Re: [HACKERS] TRUNCATE Rod Taylor [EMAIL PROTECTED] writes: I'm thinking it should check for an on delete rule as well as user triggers. Seems reasonable to me. Should there be a FORCE option to override these checks and do it anyway? Or is that just asking for trouble? I've relied on being able to TRUNCATE w/o having RI kick in to lots of data clean ups, forced sorts, etc. I'd find it annoying if I couldn't do this anymore (or had to do equally-annoying things, like manually drop then recreate the triggers, etc.) I'm happy w/o the FORCE option (just let TRUNCATE do it), but if enough people think that the FORCE keyword should be added to allow overriding of triggers, that could be a good compromise. But, please, don't take away the ability to TRUNCATE. Doing it when there are triggers is one the strengths of TRUNCATE, IMNSHO. - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] TRUNCATE
From my limited understanding of truncate in Oracle is it requires the user to first disable integrity constraints on the table before truncate will run. In SQL Server that truncate will not allow truncate if foreign key constraints exist, but does not execute user delete triggers. Can't remember nor confirm either of these now. But, for consistency sake we should enforce the foreign key case. But I really think it should apply to all constraints, system or user enforced (rules, user written triggers). Besides that, theres always Codds twelfth rule which I've always liked: The nonsubversion rule: If low-level access is permitted it should not bypass security or integrity rules. Dare I go against Codd, but, really, I've found it very convenient to be able to export a single table, TRUNCATE it, clean up the data in another program, and pull it back in. It's much more of a pain to have to dump the whole db (neccessary or at least sanity preserving if there are lots of complicated foreign key or trigger rules) or to drop/recreate the triggers/rules. The security issue is important, though: it's very likely that I might want to let an certain class of user DELETE a record (with all the usual rules/triggers/RI applying), but not let them bypass all that to TRUNCATE. But I still wouldn't want to see hassle-free truncation disappear in the name of security or idiot-proofing, if there are reasonable compromises. - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] TRUNCATE
-Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]] Sent: Sunday, May 12, 2002 10:17 PM To: Joel Burton; Tom Lane; Rod Taylor Cc: Hackers List Subject: RE: [HACKERS] TRUNCATE I'm happy w/o the FORCE option (just let TRUNCATE do it), but if enough people think that the FORCE keyword should be added to allow overriding of triggers, that could be a good compromise. But, please, don't take away the ability to TRUNCATE. Doing it when there are triggers is one the strengths of TRUNCATE, IMNSHO. It seems to me that there's more and more need for an 'SET CONSTRAINTS DISABLED' and 'SET CONSTRAINTS ENABLED' command that affects only foreign keys. This would basically make it ignore foreign key checks for the remainder of the transaction. This could be used before a TRUNCATE command, and would also be essential when we switch to dumping ALTER TABLE/FOREIGN KEY commands in pg_dump, and we don't want them to be checked... This would be different than SET CONSTRAINTS DEFERRED, in that DISABLED would never perform the checks, even at the end of the transaction? - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FW: Cygwin PostgreSQL Information and Suggestions
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Tom Lane Sent: Friday, May 10, 2002 12:31 PM To: Dave Page Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [HACKERS] FW: Cygwin PostgreSQL Information and Suggestions Dave Page [EMAIL PROTECTED] forwards: 4. Cygwin PostgreSQL is perceived to have poor performance. I have never done any benchmarks regarding this issue, but apparently Terry Carlin (from the defunct Great Bridge) did: http://archives.postgresql.org/pgsql-cygwin/2001-08/msg00029.php Specifically, he indicates the following: BTW, Up through 40 users, PostgreSQL under CYGWIN using the TPC-C benchmark performed very much the same as Linux PostgreSQL on the exact hardware. It should be noted that the benchmark Terry is describing fires up N concurrent backends and then measures the runtime for a specific query workload. So it's not measuring connection startup time, which is alleged by some to be Cygwin's weak spot. Nonetheless, I invite the Postgres-on-Cygwin-isn't-worth-our-time camp to produce some benchmarks supporting their position. I'm getting tired of reading unsubstantiated assertions. ... and it's worth remembering, too, that for some cases, connect time is completely unimportant: most of my work against PG is using shared, persistent connections from a web app (Zope); it could take 20 mins to make the initial connection and I'd still be happy. (Note to hackers: do not implement this 20min connect, though. :) ) - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Rather than propagating the SysV semaphore API still further, why don't we kill it now? (I'm willing to keep the shmem API, however.) Would this have the benefit of allow PostgreSQL to work properly in BSD jails, since lack of really working SysV IPC was the problem there? I have postgresql working quite happily in FreeBSD jails! (Just make sure you go sysctl jail.sysvipc_allowed=1). Yep, Alastair D'Silva helpfully pointed this out a month or two ago, and for many people, this would be a workable solution. Unfortunately, it appears that you have to run this command outside the jail, which I don't have access to. I forwarded the suggestion to my ISP (imeme, a Zope provider), who said that: This will allow you to run a single postgres in a single jail only one user would have access to it. If you try to run more then one it will try to use the same shared memory and crash. And therefore they refused to make the change. (More annoyingly, they kept trying to convince me that I should quit my whining and use MySQL since it's ACID compliant). So, I'm holding out hope that since this ISP seems unenlightened, one day PostgreSQL will simply run in BSD jails without a cooperating jailmaster, and it sounded like using the APR _might_ make this possible. (All of my other projects use PG; I'd sure love to get this one switched over!) Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
-Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 7:36 AM To: Joel Burton; Tom Lane; mlw Cc: Marc G. Fournier; [EMAIL PROTECTED] Subject: Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports I forwarded the suggestion to my ISP (imeme, a Zope provider), who said that: This will allow you to run a single postgres in a single jail only one user would have access to it. If you try to run more then one it will try to use the same shared memory and crash. Not true. But I'll avoid digging up any more on that old issue... Oh, I'm sure it's not true. But sometimes things end up on the nyah, nyah, it's my server and I say so level. Sigh. So, I guess that's where it leaves me: waiting for some solution other than ISP cluefulness. :-) - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Joel Burton [EMAIL PROTECTED] writes: Rather than propagating the SysV semaphore API still further, why don't we kill it now? (I'm willing to keep the shmem API, however.) Would this have the benefit of allow PostgreSQL to work properly in BSD jails, since lack of really working SysV IPC was the problem there? Was the problem just with semas, or was shmem an issue too? Not sure -- doesn't get far enough for me to tell. initdb dies with: creating template1 database in /usr/local/pgsql/data/base/1... IpcSemaphoreCreate: semget(key=1, num=17, 03600) failed: Function not implemented In any case, unless someone actually writes an alternative sema implementation that will work on BSD, nothing will happen... Was hoping that the discussions about the APR might let this work under BSD jails, assuming I can get the APR to compile. (For others: apparently PG will work under BSD jails if you recompile the BSD kernel w/some new settings, but my ISP for this project was unwilling to do that. Search the mailing list for messages on how to do this.) J. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Tom Lane Sent: Friday, May 03, 2002 6:07 PM To: mlw Cc: Marc G. Fournier; [EMAIL PROTECTED] Subject: Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports Rather than propagating the SysV semaphore API still further, why don't we kill it now? (I'm willing to keep the shmem API, however.) Would this have the benefit of allow PostgreSQL to work properly in BSD jails, since lack of really working SysV IPC was the problem there? - J. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg_config Makefile includes hyphens in configure arguments
Feeding `pg_config --configure` into configure no longer works, as the output of `pg_config --configure` now includes hypens (as in '--enable-cassert' '--enable-debug'), which configure rejects. This appears to come from the change in the Makefile (/src/bin/pg_config/Makefile), where diff -r1.3 -r1.4 1c1 # $Header: /projects/cvsroot/pgsql/src/bin/pg_config/Makefile,v 1.3 2001/09/16 16:11:11 petere Exp $ --- # $Header: /projects/cvsroot/pgsql/src/bin/pg_config/Makefile,v 1.4 2002/03/29 17:32:55 petere Exp $ 16c15 -e s,@configure@,$$configure,g \ --- -e s,@configure@,$(configure_args),g \ Is there a reason to keep this change if it breaks this feature, or is there an easy way to fix this? (I'm not a serious Makefile user, sorry!) Thanks! Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Bug or misunderstanding w/domains in 7.3devel?
Using latest CVS sources with Linux 2.4 i586:
Comparing using domains versus traditional explicit field types.
Here's the control test:
test=# create table t1 (f varchar(5) not null);
CREATE
test=# insert into t1 values ('2');
INSERT 16626 1
test=# select * from t1 where f='2';
f
---
2
(1 row)
If I create a VARCHAR domain, everything works as expected.
test=# create domain typ varchar(5) not null;
CREATE DOMAIN
test=# create table t2 (f typ);
CREATE
test=# insert into t2 values ('2');
INSERT 16627 1
test=# select * from t2 where f='2';
f
---
2
(1 row)
Here's a control test for the same thing, except with CHAR:
test=# create table t1 (f char(5) not null);
CREATE
test=# insert into t1 values ('2');
INSERT 16639 1
test=# select * from t1 where f='2';
f
---
2
(1 row)
However, if I create a CHAR domain, I'm unable to query the value from the
table:
test=# create domain typ char(5) not null;
CREATE DOMAIN
test=# create table t2 (f typ);
CREATE
test=# insert into t2 values ('2');
INSERT 16640 1
test=# select * from t2 where f='2';
f
---
(0 rows)
Even if I coerce the value to the correct domain:
test=# select * from t2 where f='2'::typ;
f
---
(0 rows)
However, this works:
test=# select * from t2 where f='2'::char;
f
---
2
(1 row)
Is this a bug? Is this correct behavior? Am I misunderstanding this?
Thanks!
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Knowledge Management Technology Consultant
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] initdb dies during IpcSemaphoreCreate under BSD jail
-Original Message- From: Alastair D'Silva [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 26, 2002 10:52 PM To: 'Vince Vielhaber'; 'Joel Burton' Cc: [EMAIL PROTECTED] Subject: RE: [HACKERS] initdb dies during IpcSemaphoreCreate under BSD jail You need to get your provider to set the sysctl jail.sysvipc_allowed to 1 in the host environment. If they're not willing to do this for you, we provide this feature on our servers, and also have a shared Postgres database you can use. Thanks for the tip. I'm not a *BSD guru, so I'm not familiar with this configuration change, but I've written to the Powers That Be at my ISP to see if this is something that they feel they could change. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] initdb dies during IpcSemaphoreCreate under BSD jail
You need to get your provider to set the sysctl jail.sysvipc_allowed to 1 in the host environment. If they're not willing to do this for you, we provide this feature on our servers, and also have a shared Postgres database you can use. My ISP responds to this point: In the thread on the pgsql-hackers list, someone wrote to me to say that You need to get your provider to set the sysctl jail.sysvipc_allowed to 1 in the host environment. Apparently, according to this person, this will allow the use of PG in the jailed environments. Is this something that imeme can configure? If this isn't clear, I'd be happy to find out more information for you about this configuration change and what other ramifications it might have for your servers. This will allow you to run a single postgres in a single jail only one user would have access to it. If you try to run more then one it will try to use the same shared memory and crash. Is this, in fact, the case? Thanks! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] initdb dies during IpcSemaphoreCreate under BSD jail
(posted last week to pgsql-general; no responses there, so I'm seeing if anyone here can contribute. Thanks!) I'm working on a site hosted in a BSD Jail; they have MySQL installed but, of course, I'd rather use PostgreSQL. It installs fine but can't initdb; get the following: Fixing permissions on existing directory /usr/local/pgsql/data... ok creating directory /usr/local/pgsql/data/base... ok creating directory /usr/local/pgsql/data/global... ok creating directory /usr/local/pgsql/data/pg_xlog... ok creating directory /usr/local/pgsql/data/pg_clog... ok creating template1 database in /usr/local/pgsql/data/base/1... IpcSemaphoreCreate: semget(key=1, num=17, 03600) failed: Function not implemented Earlier message traffic suggests that SYSV IPC has not been fixed to run under BSD Jails. The last time this was raised was ~1 year ago. Has there been any changes here that anyone knows of? Any hope of getting PG running in our jail? (Or, alternatively, can PG run on the real machine's processes so that the different jails can access it?) Any help would be appreciated! Thanks. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Catalogs design question
On Sat, 20 Oct 2001, Steve Howe wrote: Hello all!! I'm developer of a interface for PostgreSQL for the Borland Kylix and Delphi tools (http://www.vitavoom.com). I've run into the following problems with catalogs: - pg_group: the grolist field is an array. How can I make a query that tell me the usernames of a group ? - pg_proc: the proargtypes field is an array. How can I make a query that will link those types to the pg_types catalog ??? This catalog design seems a very crude hack to make the things working for me. Can't those relations be separated in another table ? Or maybe a function that can search for a value in array, and make a wroking reference for an array element in a relation (something like select typname from pg_type, pg_group where oid in grolist). I also quote the PotgreSQL user manual (http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/arrays.html): In the contrib/ directory are procedures to search arrays for values. This may help. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] CREATE RULE ON UPDATE/DELETE
On Sat, 20 Oct 2001, Aasmund Midttun Godal wrote: Can a rule see the where statement in a query which it has been triggered by? or is it simply ignored?? what happens? Looking over your question, I wanted to clarify the problem a bit, so: (cleaned up example a bit from Aasmund) -- set up tables drop view normal; drop view dbl; drop table raw; CREATE TABLE raw (id INT PRIMARY KEY, name TEXT ); INSERT INTO raw VALUES(1, 'a'); INSERT INTO raw VALUES(2, 'b'); INSERT INTO raw VALUES(12, 'c'); INSERT INTO raw VALUES(15, 'd'); INSERT INTO raw VALUES(14, 'e'); -- set up two views: normal, a simple view, -- and dbl, which shows id * 2 -- create basic rules to allow update to both views CREATE VIEW normal AS SELECT * FROM raw; CREATE RULE normal_update AS ON UPDATE TO normal DO INSTEAD UPDATE raw SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw; CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET id = NEW.id, name = NEW.name WHERE OLD.id = id; -- now test this UPDATE normal SET id = id + 10 where id 10; -- works fine UPDATE dbl SET id = id + 10 where id 10;-- above shows UPDATE 0 -- even though there are ids 10 UPDATE dbl SET id = id + 10; -- UPDATE 1; shows table SELECT * FROM dbl;-- inconsistencies: two as SELECT * FROM raw; The issue is that there are no IDs over 10 that have another ID that is exactly their value, so the first update to dbl does nothing. The second time, w/o the ID10 restriction, it finds 1(a), and double that, 2(b), and adds 10; getting confused about which record to edit. Is this the best way to interpret this? Is this a bug? -- 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])
Re: [HACKERS] Is there no DESCRIBE TABLE; on PGSQL? help!!!
On Fri, 19 Oct 2001, Ron de Jong wrote: Any idea to get a human readable list with column descriptions like type,size,key,default,null. It would be nice if it would look simular to the mysql variant: mysql describe employee; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | Id| int(11) | | PRI | NULL| auto_increment | | FirstName | char(30) | | MUL | || | LastName | char(30) | | | || | Infix | char(10) | YES | | NULL|| | Address1 | char(30) | YES | | NULL|| | PostCode | char(10) | YES | | NULL|| | Town | int(11) | YES | | NULL|| +---+--+--+-+-++ Easily done -- look at the \d commands in psql or \h to get help in psql. This is a FAQ -- STFW. BTW, the -hackers list is for tricky questions requiring experienced developer help, or for discussion among the gurus. Please post general questions to pgsql-general or pgsql-novice and re-post to pgsql-hackers only if you get no response w/in a week. HTH, -- 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])
Re: [HACKERS] Problem in pg_dump 7.1.2 dump order
On Wed, 10 Oct 2001, Dmitry Chernikov wrote: Hello, In dump file statement which grants permissions on view exists before statement which create view. For tables and sequences permissions dumped in correct order. --TOC Entry ID 124 (OID 150248) GRANT ALL on my_view to group sales; ... skipped --TOC Entry ID 123 (OID 194103) CREATE VIEW my_view ... Any comments? This bug was fixed in 7.1.3. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] RE: LIBPQ on Windows and large Queries
On Thu, 26 Jul 2001, Khoa Do wrote: I'm surprised you are even able to run postgres in windows. I didn't even know postgres supported windows. Could you kindly point me to instructions on how to run it and build the postgres souce on windows? If nobody will try to fix it, then maybe we should just try it ourseleves and post some patch to it. -Original Message- From: Steve Howe [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 25, 2001 9:25 PM To: [EMAIL PROTECTED] Subject: [HACKERS] LIBPQ on Windows and large Queries Hello all, Is anybody trying to solve the 8191 bytes query limit from libpq windows port ??? We've discussed this topic on Large queries - again thread and it seems like nobody got interested on fixing it. All Windows applications that rely on libpq are broken because of this issue (ODBC applications are fine btw). I can also do any kind of testing under Windows (and actually I'll do it anyway). I wonder if this limitation also applies to the unix libpq library ??? Jan, Tom, Bruce - any news on this ? Steve's question was about the Postgres library (libpq.dll). I can't confirm that is hasn't been TOAST'ed (for 8192 chars). PostgreSQL does, however, run just peachy under Windows. Has for a long time w/Windows NT and Windows 2000; recently, works with Windows 98 too. (Can't vouch for WinME, never touched the thing.) www.cygwin.com Can download it as part of the Cygwin package. You'll need to install CygIPC (easily found via google, a simple binary install). Whole thing is pretty much of a snap nowadays. Of course, would you want to run a serious database under Windows 98? -- 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
[HACKERS] Re: plpgsql: Debug function?
On 25 Jul 2001, Turbo Fredriksson wrote: Is there a way to debug a PL/pgSQL function? It's behaving very irradic! It's crude, but you can output debugging statements w/ RAISE NOTICE or catch flawed assumptions by RAISE EXCEPTION. -- 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]
[HACKERS] Re: help!
On Wed, 2 May 2001, Jeff Vainio wrote: Hello! I am a Technical Recruiter with MIS Consultants in Toronto Canada and I desperately need to find 2 POSTGRES DBA's for our Toronto client on a 3-4 month renewable contract, open $$$ based on experience. How do I go about finding these guys? Jeff -- The *-hackers list is not the appropriate forum for this. This is for discussion of developing PostgreSQl, and for kvetching about users, and things like that. :-) . There's a web page at techdocs.postgresql.org about people looking for PG consultants. . Call Great Bridge (greatbridge.com) or PostgreSQL Inc. (pgsql.com); they both are commercial companies providing PG support. They might be able to shake loose someone. PG Inc. is in Canada, so they might be a great bet. . A short message to pgsql-general would get everyone's attention. I'm not sure how people feel about these kind of notices, though -- so, keep it short, and obviously titled. help!, for instance, should become Seeking PostgreSQL DBAs in Toronto, Canada -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: what is the limit for string
On Tue, 1 May 2001, Rosie Sedghi wrote: hello Would you tell me how many characters we can have as a string field? Thanks a lot. Questions like this should be sent to pgsql-general or pgsql-novice. There is no string field. There are CHAR, VARCHAR, TEXT, and a few other unusual text-type fields. Look at the section on data types in the User's Manual for info. -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Metaphone function
I've hacked together a metaphone function from an existing metaphone implementation and the example provided by the soundex() function in contrib. I'd like to send this out to the general list, in the hopes that people will find it useful, but I wanted to wave it in front of the -hackers and -patches people first, just to make sure that I haven't done anything dreadfully, awfully terrible. :-) I'm not an expert C coder, and this is really two programs, neither of which I wrote, glued together, with some mild editing by me. There are no malloc() calls (no dynamic memory at all), nor anything terribly strange, but this is my first C function I'm turning loose on the world. If you have a chance, I'd appreciate any feedback/pointers. If it looks good / If I don't hear otherwise, I'll send it out to pgsql-announce and pgsql-general early next week. Thanks! -- 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])
[HACKERS] Re: Replication Docs..
On Thu, 3 May 2001, bpalmer wrote: I'm starting to throw together a web site relating to postgresql replication, trying to bring together the ideas we have thrown around so far. If anyone has any good docs (on replication not relating to postgresq too), please send me the links. Collaborate w/Justin -- he has information about replication up at techdocs.postgresql.org now. Thanks, -- 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
[HACKERS] Re: COPY commands could use an enhancement.
On Mon, 30 Apr 2001, Alfred Perlstein wrote: Basically: COPY webmaster FROM stdin; could become: COPY webmaster FIELDS id, name, ssn FROM stdin; We'd need some way of making field name dumping optional, because one of the nice things about not having the field names appear is that I can dump, change the field names, and re-slurp in the old dump. -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: COPY commands could use an enhancement.
On Mon, 30 Apr 2001, Tom Lane wrote: I think it'd be better to put effort into an external data translation utility that can deal with column selection, data reformatting, CR/LF conversion, and all those other silly little issues that come up when you need to move data from one DBMS to another. Sure, we could make the backend do some of this stuff, but it'd be more maintainable as a separate program ... IMHO anyway. I think that pgaccess and pgadmin already have some capability in this line, BTW. Real conversion should happen in userland. However, allowing people to COPY in a different order does prevent a userland tool from having to re-arrange a dump file. (Of course, really, with perl, re-ordering a dump file should take more than a few lines anyway.) Are there any generalized tools for re-ordering delimited columns, without having to use sed/perl/regexes, etc.? If people can point to some best practices/ideas, I'd be happy to turn them into a HOWTO. -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: unanswered: Schema Issue
On Thu, 26 Apr 2001, V. M. wrote: ok for serials, now i can extract from psql (\d tablename). But i'm not able to extract foreign keys from the schema. Yes you can. Read my tutorial on Referential Integrity in the top section at techdocs.postgresql.org. -- 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: [HACKERS] Re: unanswered: Schema Issue
On Thu, 26 Apr 2001, V. M. wrote: (moving this conversation back to pgsql-general, followups to there) perhaps adding t.tgargs to your view enable me to extract parameters that are the related fields At SCW, we use a naming convention for RI triggers, to allow us to easily extract that, and deal with error messages. We use: CREATE TABLE p (id INT); CREATE TABLE c (id INT CONSTRAINT c__ref_id REFERENCES p); This allows us at a glance to see in error messages what field of what table we were referencing. In an Access front end, we can trap this error message to a nice statement like You're trying to change a value in the table c, using information in table p, id, but...) If you don't have this, yes, you can look at in the tgargs, but, given that its a bytea field, it's hard to programmatically dig anything out of it. HTH, -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: Any optimizations to the join code in 7.1?
On Wed, 25 Apr 2001, Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: I have a particular query which performs a 15-way join; You should read http://www.postgresql.org/devel-corner/docs/postgres/explicit-joins.html I was recently poring over this page myself, as I've been working w/some larger-than-usual queries. Two questions: 1) it appears (from my tests) that SELECT * FROM CREATE VIEW joined as SELECT p.id, p.pname, c.cname FROM p LEFT OUTER JOIN c using (id) gives the same answer as SELECT * FROM CREATE VIEW nested SELECT p.id, p.pname, (select c.cname from c where c.id = p.id) FROM p However, I often am writing VIEWs that will be used by developers in a front-end system. Usually, this view might have 30 items in the select clause, but the developer using it is likely to only as for four or five items. In this case, I often prefer the subquery form because it appears that SELECT id, pname FROM joined is more complicated than SELECT id, pname FROM nested as the first has to perform the join, and the second doesn't. Is this actually correct? 2) The explicit-joins help suggests that manual structuring and experimentation might help -- has anyone written (or could anyone write) anthing about where to start in guessing what join order might be optimal? -- 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]
[HACKERS] Re: Any optimizations to the join code in 7.1?
On Wed, 25 Apr 2001, Tom Lane wrote: 2) The explicit-joins help suggests that manual structuring and experimentation might help -- has anyone written (or could anyone write) anthing about where to start in guessing what join order might be optimal? The obvious starting point is the plan produced by the planner from an unconstrained query. Even if you don't feel like trying to improve it, you could cut the time to reproduce the plan quite a bit --- just CROSS JOIN a few of the relation pairs that are joined first in the unconstrained plan. In other words, let it do the work, and steal the credit for ourselves. :-) Thanks, Tom. I appreciate your answers to my questions. In other DB systems I've used, some find that for this original query: SELECT * FROM a, b WHERE a.id=b.id AND b.name = 'foo'; that this version SELECT * FROM a JOIN b USING (id) WHERE b.name = 'foo'; has slower performance than SELECT * FROM b JOIN a USING (id) WHERE b.name = 'foo'; because it can reduce b before any join. Is it safe to assume that this is a valid optimization in PostgreSQL? If this whole thing were a view, except w/o the WHERE clause, and we were querying the view w/the b.name WHERE clause, would we still see a performance boost from the right arrangement? (ie, does our criteria get pushed down early enough in the joining process?) TIA, -- 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])
[HACKERS] Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
On Sat, 21 Apr 2001, Philip Warner wrote: At 11:29 20/04/01 -0500, Jan Wieck wrote: Philip Warner wrote: At 08:42 19/04/01 -0500, Jan Wieck wrote: and the required feature to correctly restore the tgconstrrelid is already in the backend, so pg_dump should make use of it No problem there - just tell me how... Add a "FROM opposite-relname" after the "ON relname" to the CREATE CONSTRAINT TRIGGER statements. That's it. I'll make the change ASAP. Woo-hoo! Thanks. I posted a plpgsql script yesterday that tries to restore the name if it's already been lost to a dump/restore cycle. It would be a more robust solution if, instead of relying on pgconstrname, I could get into the trigger arguments. However, these does not seem to be any way for me to do this from plpgsql, as the functions for manipulating bytea fields aren't very useful for this, an I can't coerce bytea into text or anything like that. Can anyone offer help on this? If I can get into the real args, I'll fix up the script so that it can be run once by the people w/o tgconstrrelid, and then, once Philip's done his work, we'll never lose it again! :-) -- 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]
Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
On Thu, 19 Apr 2001, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: IMHO there's nothing fundamentally wrong with having pg_dump dumping the constraints as special triggers, because they are implemented in PostgreSQL as triggers. ... The advantage of having pg_dump output these constraints as proper ALTER TABLE commands would only be readability and easier portability (from PG to another RDBMS). More to the point, it would allow easier porting to future Postgres releases that might implement constraints differently. So I agree with Philip that it's important to have these constructs dumped symbolically wherever possible. However, if that's not likely to happen right away, I think a quick hack to restore tgconstrrelid in the context of the existing approach would be a good idea. A while ago, I wrote up a small tutorial example about using RI w/Postgres. There wasn't much response to a RFC, but it might be helpful for people trying to learn what's in pg_trigger. It includes a discussion about how to disable RI, change an action, etc. It's at http://www.ca.postgresql.org/mhonarc/pgsql-docs/archive/pgsql-docs.200012 -- 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]
Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
On Thu, 19 Apr 2001, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: IMHO there's nothing fundamentally wrong with having pg_dump dumping the constraints as special triggers, because they are implemented in PostgreSQL as triggers. ... The advantage of having pg_dump output these constraints as proper ALTER TABLE commands would only be readability and easier portability (from PG to another RDBMS). More to the point, it would allow easier porting to future Postgres releases that might implement constraints differently. So I agree with Philip that it's important to have these constructs dumped symbolically wherever possible. However, if that's not likely to happen right away, I think a quick hack to restore tgconstrrelid in the context of the existing approach would be a good idea. Not having the right value was stopping me in a project, so I put together a rather fragile hack: First, a view that shows info about relationships: CREATE VIEW dev_ri_detech AS SELECT t.oid AS trigoid, c.relname AS trig_tbl, t.tgrelid, rtrunc(text(f.proname), 3) AS trigfunc, t.tgconstrname, c2.relname FROMpg_trigger t JOINpg_class c ON (t.tgrelid = c.oid) JOINpg_proc f ON (t.tgfoid = f.oid) LEFT JOIN pg_class c2 ON (t.tgconstrrelid = c2.oid) WHERE t.tgisconstraint; Then, the new part, a function that iterates over RI sets (grouped by name*). It stores the 'other' table in pgconstrrelid, knowing that the '_ins' action is for the child, and that '_del' and '_upd' are for the parent. * - It requires that your referential integrity constraints have unique names (not a bad idea anyway). eg: CREATE TABLE child (pid INT CONSTRAINT child__ref_pid REFERENCES parent) * - it completely relies on how RI is handled as of Pg7.1, including the exact names of the RI functions. After a dump/restore cycle, just select dev_ri_fix(); It does seem to work, but do try it on a backup copy of your database, please! create function dev_ri_fix() returns int as ' declare count_fixed int := 0; rec_ins record; rec_del record; upd_oid oid; begin for rec_ins in selecttrigoid, tgrelid, tgconstrname from dev_ri_detect where rtrunc(trigfunc,3)='ins' loop select trigoid, tgrelid into rec_del from dev_ri_detect where tgconstrname=rec_ins.tgconstrname and rtrunc(trigfunc,3)='del'; if not found then raise notice 'No Match: % %', rec_ins.tgconstrname, rec_ins.trigoid; else upd_oid := trigoid from dev_ri_detect where tgconstrname=rec_ins.tgconstrname and rtrunc(trigfunc,3)='upd'; update pg_trigger settgconstrrelid=rec_del.tgrelid where oid=rec_ins.trigoid; update pg_trigger settgconstrrelid=rec_ins.tgrelid where oid=rec_del.trigoid; update pg_trigger set tgconstrrelid=rec_ins.tgrelid where oid=upd_oid; count_fixed :=count_fixed + 1; end if; end loop; return count_fixed; end; ' language 'plpgsql'; (it's not terribly optimized--I normally work w/databases =300 tables) Also helpful: sometimes, after dropping, rebuilding and tinkering with a schema, I find that I'm left w/half of my referential integrity: (the parent has upd/del rules, but the child has no ins, or vice versa). The following query helps find these: SELECT tgconstrname, comma(trigfunc) as funcs, count(*) as count FROM dev_ri_detect GROUP BY tgconstrname HAVING count(*) 3; It also requires that you have named constraints. It uses a function, comma(), that just aggregates a resultset into a comma-separated list. This function (which I find generally useful) is in Roberto Mello's Cookbook, via techdocs.postgresql.org. Anyway, here's hoping that someone fixes the dumping problem (emitting as real constraints would be *much* nicer), but in the meantime, this stuff may be useful. -- 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
[HACKERS] [BUG?] tgconstrrelid doesn't survive a dump/restore
tgconstrrelid (in pg_trigger) holds table references in a RI trigger.
The value in this field is not successfully recreated after a
dump/restore.
---
If I create a simple relationship:
create table p (id int primary key);
create table c (pid int references p);
and query the system table for the RI triggers:
select tgrelid, tgname, tgconstrrelid from pg_trigger
where tgisconstraint;
I get (as expected) the trigger information:
tgrelid | tgname | tgconstrrelid
-++---
29122 | RI_ConstraintTrigger_29135 | 29096
29096 | RI_ConstraintTrigger_29137 | 29122
29096 | RI_ConstraintTrigger_29139 | 29122
(3 rows)
However, if I dump this database:
[joel@olympus joel]$ pg_dump -sN test1 | grep -v - -- test1
CREATE TABLE "p" (
"id" integer NOT NULL,
Constraint "p_pkey" Primary Key ("id")
);
CREATE TABLE "c" (
"id" integer NOT NULL
);
CREATE CONSTRAINT TRIGGER "unnamed" AFTER INSERT OR UPDATE ON
"c" NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins" ('unnamed',
'c', 'p', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "unnamed" AFTER DELETE ON "p" NOT
DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('unnamed',
'c', 'p', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "unnamed" AFTER UPDATE ON "p" NOT
DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('unnamed',
'c', 'p', 'UNSPECIFIED', 'id', 'id');
If I drop the database and recreate from the dump:
drop database test1;
create database test1 with template=template0;
\c test1
\i test1
and re-run the query on the pg_trigger table:
select tgrelid, tgname, tgconstrrelid from pg_trigger
where tgisconstraint;
PG has lost the information on which table was being referred to
(tgconstrrelid):
tgrelid | tgname | tgconstrrelid
-++---
29155 | RI_ConstraintTrigger_29168 | 0
29142 | RI_ConstraintTrigger_29170 | 0
29142 | RI_ConstraintTrigger_29172 | 0
(3 rows)
Thee referential integrity still *works* though --
test1=# insert into p values (1);
INSERT 29174 1
test1=# insert into c values (1);
INSERT 29175 1
test1=# insert into c values (2);
ERROR: unnamed referential integrity violation - key referenced from
c not found in p
test1=# update p set id=2;
ERROR: unnamed referential integrity violation - key in p still
referenced from c
test1=# delete from p;
ERROR: unnamed referential integrity violation - key in p still
referenced from c
The problem is that I've use tools that examine tgconstrrelid to figure
reverse engineer which relationships exist.
Is this a bug? Am I misunderstanding a feature?
(This was run with 7.1RC4; it's possible that this bug doesn't exist in
the release 7.1. I haven't been able to get the CVS server to work for
about 48 hours, so I haven't been able to upgrade.)
Thanks!
--
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])
[HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
On Wed, 18 Apr 2001, Tom Lane wrote: Joel Burton [EMAIL PROTECTED] writes: tgconstrrelid (in pg_trigger) holds table references in a RI trigger. The value in this field is not successfully recreated after a dump/restore. Yes, this problem was noted a couple months ago. AFAIK it was not fixed for 7.1, but I concur that it should be fixed. Jan/Philip/Tom -- Do we know if the problem is in pg_dump, or is there no way to pass the tgconstrrelid value in the CREATE CONSTRAINT TRIGGER statement? (I've read the dev docs on RI, but I haven't seen anyplace that documents what the arguments for the call are exactly, and a muddled wading through the source didn't help much.) If there are no better suggestions for the before-the-real-fix fix, I could make RI_pre_dump() and RI_post_dump() functions that would stick this information into another table so that I won't lose that info. (Or, can I always rely on digging it out of the preserved fields in pg_trig?) Thanks! -- 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])
[HACKERS] Re: Truncation of object names
On Fri, 13 Apr 2001, Tom Lane wrote: Obviously, these objections are not strong enough to keep us from increasing the standard value of NAMEDATALEN if it seems that many people are running into the limit. But AFAICT relatively few people have such problems, and I'm hesitant to make everyone deal with a change for the benefit of a few. Count me as a weak vote for leaving it where it is ... Hmm... Of course, it's Bad to break things if one doesn't have to. But (IMHO) its also bad to leave it at a setting that makes some group of people (~ 3%?) have to recompile it, and a larger group (~ 10%) wish they did/knew how to. (I, in general, share your hesistancy to break something for the benefit of the few, 'cept I'm one of the few this time. ;-) ) For some changes, one could just prewarn the world that This Is Coming, and they should anticipate it with 6 months notice or such. In this case, though, it would seem that knowing it was coming wouldn't help any -- you'd still have to recompile your client for the 32char names and the 64 (?) char names, during the 7.1 - 7.2 (or 7.5 - 8.0 or whatever) transition period. I'd like to see it longer -- is there any sane way of doing this with notice, or, as I fear, would it always be a pain, regardless of how much advance notice the world rec'd? Thanks, -- 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
[HACKERS] Re: HOLD THE PRESSES!! ... pg_dump from v7.0.3 can't import to v7.1?
On Tue, 10 Apr 2001, The Hermit Hacker wrote: all I did was use pg_dumpall from v7.0.3 to dump to a text file, and "psql template1 dumpfile" to load it back in again ... obviously this doesn't work like it has in the past? Marc -- Was there an error message during restore? I've been dumping/restoring w/7.1 since long before beta, w/o real problems, but haven't been doing this w/7.0.3 stuff. But still, psql should give you some error messages. (I'm sure you know this, but for the benefit of others on the list) In Linux, I usually use the command) psql dbname dumpfile 21 | grep ERROR so that I don't miss any errors among the all the NOTICEs -- 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
[HACKERS] Re: RC3 ...
On Sat, 7 Apr 2001, Peter Eisentraut wrote: Thomas Lockhart writes: The docs are ready for shipment. Even better ... Okay, let's let this sit as RC3 for the next week... I'll go ahead and start generating hardcopy, though I understand that it is no longer allowed into the shipping tarball :( I'm not speaking about "allowed", I'm merely talking about the state of affairs since 7.0. If people think that the postscript format should be in the main tarball, then why not, but IIRC this question was raised last time around and the decision went the other way. Having had to d/l PG many times on many different machines, I'd be delighted if it came w/o .ps docs, and w/o the doc sources (the number of people who seem to be able to turn docbook into useful stuff seems to be than people who can successful compile PG!). It sounds like the separate-tgz for docs and for Postscript makes perfect sense. Just make sure that it's *very* obvious where/how to get these, so that the mailing lists are deluged w/ 'where are the docs'? Just my .02, -- 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
[HACKERS] Re: plpython for postgres 7.1
On Mon, 2 Apr 2001, Karel Zak wrote: A couple of weeks ago I received an email from Albert Langer inquiring about the status of the python language module I had written for postgresql. I told him I could have the port to postgresql 7.1 done by the middle of this week (march 25-31). Well, it's the end of this week, but I've finished it. Besides the conversion to the new style function manager, I've implemented a complete SPI interface. (The 7.0 module couldn't execute saved plans.) If you are interested in experimenting with the module it is available at "http://users.ids.net/~bosma" download the link "tarball for postgresql 7.1" comments, bug reports and suggestions are appreciated. This is *great news* -- we use Python in our office for many things, and with Python embedded into the DB server, it makes our Zope-PostgreSQL connection ever tighter. I'm afraid I can't give much feedback about the code (I'm just not that familiar w/the PG internals), but, externally it seems to work great. I'm excited about the SD[] and GD[] dicts -- they're a nice addition for us. For those of you considering installing this, it was a very easy install (Linux-Madrake 7.2 (Linux 2.2.x) / Python 1.5.2). Run one diff against the PG sources, recompile, edit a Makefile for one- or two- library locations, and that's it. Worth playing with. I hope we will see it in 7.2 ... Indeed. For the deep gurus: what's the downside of adding PLs to our PG server? (Of course, adding alpha- or beta- quality PLs has clear problems, I mean when this becomes production quality). Does each new PL bloat the PG server? Does each new PL slow it down? Thanks! -- 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
[HACKERS] Re: Feature Request: ALTER FUNCTION (or something like that)
On Tue, 27 Mar 2001, Tom Lane wrote: Joel Burton [EMAIL PROTECTED] writes: . add a command like ALTER FUNCTION foo(text) TO foo(text) returns text as This is on the TODO list already, I believe. Yikes. I should have read it more carefully. My apologies. There are so many good things on it. Sadly, no one has claimed this item. (I still didn't see ALTER VIEW, though, which would seem just as nice.) This would seem to require that the new function would take the same parameters (and return the same?) as the old function. If it doesn't take the same parameters then it's not the same function at all, so that part is a nonissue. We'd have to disallow change of return type as well. Perhaps this is tricky, Updating pg_proc wouldn't be hard. What's missing is a notification mechanism that would cause cached copies of the function to get replaced. A crude first cut could just ignore that issue and still be extremely useful for development ... A crude first cut would be useful for development... even if I had to restart postmaster, it's still much easier than dumping/restoring the whole database, which is the usual solution around here (trying to individually fix each view/table using the function becomes so tricky as one has to track every dependency after that, etc.) Dumping/restoring can be great (three cheers especially for the new formats using the -F switch!), but in the past, I've had one or two complicated views that would dump, but couldn't be restored from the dump. (As was discussed at the time in the list; the culprit was that pg_dump produced a deeply complex expresion with a bazillion nested parentheses; if one removed some of these, pg_restore did just fine.) This has disappeared in the 7.1 code, but I'm still slightly skitting about the dump-restore so very frequently cycle. If I have your ear on the subject, tgl, is there any ugly-but-working hack to update the function by modifying the system tables directly? As always, thanks, -- 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
[HACKERS] pg_inherits: not found, but visible
Postmaster crashed on me, and on restart, pg_inherits cannot be found. I can see it in pg_class (and it shows up w/ \dS), but any attempt to modify anything fails with "pg_inherits: No such file or directory". I've reindexed the database (w/postgres -P -O). Vacuuming fails (w/error above). What could this be? Is there any hope? Thanks! -- 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
[HACKERS] pg_inherits: addt'l info
I'm sorry, I should have included: PostgreSQL 7.1beta4 Linux-Mandrake 7.1 (very simiiar RedHat 7) Intel hardware -- 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: [HACKERS] pg_inherits: not found, but visible
On Wed, 21 Mar 2001, Hiroshi Inoue wrote: Joel Burton wrote: Postmaster crashed on me, and on restart, pg_inherits cannot be found. I can see it in pg_class (and it shows up w/ \dS), but any attempt to modify anything fails with "pg_inherits: No such file or directory". I've reindexed the database (w/postgres -P -O). Vacuuming fails (w/error above). What could this be? Is there any hope? Try the following queries. 1) select oid from pg_database where datname = your_db_name; 2) select oid, relfilenode from pg_class where relname = 'pg_inherits'; For example I get the followings in my environment. 1) oid = 18720 2) relfilenode(==oid) = 16567; and I could find a $PGDATA/base/18720/16567 file. Could you find such a file ? No. I do have the db directory, and all of the other file for the existing classes, but not this. Any ideas why this would disappear? Or any ideas about how to get my existing data out? (I have a dump from about 36 hours ago; it would be nice to extract some more recent data!) -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_inherits: not found, but visible
On Tue, 20 Mar 2001, Tom Lane wrote: Joel Burton [EMAIL PROTECTED] writes: and I could find a $PGDATA/base/18720/16567 file. Could you find such a file ? No. I do have the db directory, and all of the other file for the existing classes, but not this. Hm. You could make an empty file by that name (just 'touch' it) and then you'd probably be able to dump (possibly after reindexing pg_inherit's indexes again). pg_inherits isn't a real critical table, fortunately. Any ideas why this would disappear? Interesting question, all right. Did you have a system crash? Ok, so I touched the file, and did a postgres -P -O reindex of the table with force. Going into psql then, I could select * from the table, and, not surprisingly, nothing was in it, but I can ( did) dump my data. For those watching, that's about 15 minutes from the sinking feeling of 'I just lost two days of work' to 'resolution and data restored'. Our community has *damn fine* technical support! :-) Thanks, Tom, and Hiroshi, for being so helpful so quickly. As for your questions, no, I didn't have a system crash. I was running a Zope page that queries several tables (show all classes, for each class, show all instances, for each instance, show all dates, etc.); the page normally takes about 2 minutes to pull everything together (I think that's Zope's speed issue, not PG!) Anyway, while that was chugging away, I tried to drop a view and recreate it, and that request just hung there for a few minutes. The Zope page never came up, and psql notified me that I lost my connection. I wasn't, and haven't ever, used inheritance in this database. -- 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])
Re: [HACKERS] pg_inherits: not found, but visible
On Wed, 21 Mar 2001, Hiroshi Inoue wrote: Joel Burton wrote: On Wed, 21 Mar 2001, Hiroshi Inoue wrote: Joel Burton wrote: Postmaster crashed on me, and on restart, pg_inherits cannot be found. I can see it in pg_class (and it shows up w/ \dS), but any attempt to modify anything fails with "pg_inherits: No such file or directory". I've reindexed the database (w/postgres -P -O). Vacuuming fails (w/error above). What could this be? Is there any hope? Try the following queries. 1) select oid from pg_database where datname = your_db_name; 2) select oid, relfilenode from pg_class where relname = 'pg_inherits'; For example I get the followings in my environment. 1) oid = 18720 2) relfilenode(==oid) = 16567; and I could find a $PGDATA/base/18720/16567 file. Could you find such a file ? No. I do have the db directory, and all of the other file for the existing classes, but not this. Just a confirmation. What is a result of the second query in your current environment ? I got exactly what I would expect in a working PG db: the oid and relfilenode matched, and were OIDs in the range of the other system tables in the directory. -- 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]
Re: [HACKERS] pg_inherits: not found, but visible [IT GETS WORSE]
Yikes. It gets weirder. Fixed the pg_inherits problem, went back to my Zoping, trying to optimize some views, and during another run, get an error that trelclasspq, one of my tables, couldn't open. Trying this out in psql, I get the same error message--the file doesn't exist. And, getting the oid for the file, looked in the directory--and this file is gone too! Now, I just made a good dump of the database, so I can always go back to that. But this seems to be a *serious* problem in the system. I have Zope 2.3.1b2 (most recent version of Zope) running on a Linux-Mandrake 7.2 box (server #1) It has a database adapter called ZPoPy, which is the Zope version of PoPy, a Python database adapter for PostgreSQL. PoPy is getting data from my PostgreSQL database, which is 7.1beta4, and served on a different Mandrake 7.2 box. Has anyone seen anything like this? I doubt the error is Zope *per se*, since Zope can only talk to the database adapter, and I doubt the database adapter has the intentional feature of delete-the-file-for-this-table in its protocol. It *could* be a problem w/ZPoPy or PoPy; I'll send a message to their list as well. Thanks! -- 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
[HACKERS] Re: AW: Re: MySQL and BerkleyDB (fwd)
On Mon, 22 Jan 2001, Zeugswetter Andreas SB wrote: Is anyone looking at doing this? Is this purely a MySQL-ism, or is it something that everyone else has except us? We should not only support access to all db's under one postmaster, but also remote access to other postmaster's databases. All biggie db's allow this in one way or another (synonyms, qualified object names) including 2-phase commit. Ideally this includes access to other db manufacturers, flat files, bdb ... Meaning, that this is a problem needing a generic approach. Of course, a generic, powerful approach would be great. However, a simple, limited approach would a be solution for (I suspect) 97% of the cases, which is that one software package creates a database to store mailing list names, and another creates a database to store web permissions, and you want to write a query that encompasses both, w/o semi-tedious COPY TO FILEs to temporarily move a table back and forth. And of course, a simple solution might be completed faster :-) How could this be handled? * a syntax for db-table names, such as mydb.myfield or something like that. (do we have any unused punctuation? :-) ) * aliases, so that tblFoo in dbA can be called as ToFoo in dbB * other ways? The second might be easier from a conversion view: the user wouldn't have to understand that this was a 'link', but it might prove complicated when there are many links to keep track of, etc. -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington
Re: [HACKERS] Re: AW: Re: MySQL and BerkleyDB (fwd)
On Mon, 22 Jan 2001, Ross J. Reedstrom wrote: And this case can be handled within one database by having multiple schema, one for each package. It's not there yet, but it's a simpler solution than the generic solution. The problem (as others have mentioned) is that we don't want to open the door to remote access until we have a two-phase transaction commit mechanism in place. Doing it any other way is not a 'partial solution', it's a corrupt database waiting to happen. What does '2-phase transaction commit mechanism' mean in this case? -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington
Re: [HACKERS] abstract: fix poor constant folding in 7.0.x, fixed in 7.1?
We had problem with a query taking way too long, basically
we had this:
select
date_part('hour',t_date) as hour,
transval as val
from st
where
id = 500
AND hit_date = '2000-12-07 14:27:24-08'::timestamp - '24
hours'::timespan AND hit_date = '2000-12-07 14:27:24-08'::timestamp
;
turning it into:
select
date_part('hour',t_date) as hour,
transval as val
from st
where
id = 500
AND hit_date = '2000-12-07 14:27:24-08'::timestamp
AND hit_date = '2000-12-07 14:27:24-08'::timestamp
;
Perhaps I'm being daft, but why should hit_date be both = and =
the exact same time and date? (or did you mean to subtract 24
hours from your example and forgot?)
(doing the -24 hours seperately)
The values of cost went from:
(cost=0.00..127.24 rows=11 width=12)
to:
(cost=0.00..4.94 rows=1 width=12)
By simply assigning each sql "function" a taint value for constness
one could easily reduce:
'2000-12-07 14:27:24-08'::timestamp - '24 hours'::timespan
to:
'2000-12-07 14:27:24-08'::timestamp
You mean '2000-12-06', don't you?
Each function should have a marker that explains whether when given a
const input if the output might vary, that way subexpressions can be
collapsed until an input becomes non-const.
There is "with (iscachable)".
Does
CREATE FUNCTION YESTERDAY(timestamp) RETURNS timestamp AS
'SELECT $1-''24 hours''::interval' WITH (iscachable)
work faster?
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
Re: [HACKERS] v7.1 beta 1 (ODBC driver?)
The official ODBC driver from pg7.0.x doesn't work w/7.1 (b/c of the changes in the system catalogs, IIRC). The CVS 7.1devel code works and builds easily, but I suspect 99% of the beta testers won't have Visual C++ or won't be able to compile the driver. Is there an official driver-compiler-person that could package this up for 7.1beta? (I know that a binary driver isn't part of the beta per se, and that it's not *unreleasable* to think that everyone could compile their own, but I bought VC++ just to compile this driver, and would hate to see M$ get richer for even more people. Also, I doubt we'd want to impugn the perceived quality of 7.1beta b/c people don't understand that its just the ODBC drivers that out-of-date.) If there's no one official tasked w/this, I'd be happy to submit my compiled version, at http://www.scw.org/pgaccess. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
[HACKERS] RI tutorial needs tech review
(I posted this yesterday, but it never appeared. Apologies if it's a duplicate to you.) I've written ( submitted to pgsql-docs) a tutorial on using RI features and on alter the system catalog to change RI properties for existing relationships. I needs polishing, etc., but, mostly it needs someone more familiar than I to look at the last section, on Hacking RI. All of the changes I recommend I've tried in my databases (pg7.0.2 and pg7.1-devel), and haven't noticed any problems, but if anyone has any words of warning/advice/additional tips, I'd appreciate it.) It should be in today's pgsql-docs listings. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
[HACKERS] RI tutorial hack reading needed
(apologies for posting directly to pgsql-hackers, but I'm asking for a hacker to explicitly check on the accuracy of another posting!) I've written ( submitted to pgsql-docs) a tutorial on using RI features and on alter the system catalog to change RI properties for existing relationships. I needs polishing, etc., but, mostly it needs someone more familiar than I to look at the last section, on Hacking RI. All of the changes I recommend I've tried in my databases (pg7.0.2 and pg7.1-devel), and haven't noticed any problems, but if anyone has any words of warning/advice/additional tips, I'd appreciate it.) It should be in today's pgsql-docs listings. Thanks! Joel Burton [EMAIL PROTECTED]
[HACKERS] Re: [GENERAL] Warning: Don't delete those /tmp/.PGSQL.* files
Ah, I see why the data-directory interlock file wasn't helping: it wasn't checked until *after* shared memory was set up (read clobbered :-(). This was not a very bright choice. I'm still surprised that the shared-memory reset should've trashed your database so thoroughly, though. Over the past two days I've committed changes that should make the data directory, socket file, and shared memory interlocks considerably more robust. In particular, mechanically doing "rm -f /tmp/.s.PGSQL.5432" should never be necessary anymore. That's fantastic. Thanks for the quick fix. BTW, your original message mentioned something about a recursive view definition that wasn't being recognized as such. Could you provide details on that? I can't. It's a few weeks ago, the database has been in furious development, and, of course, I didn't bother to save all those views that crashed my server. I keep trying to re-create it, but can't figure it out. I'm sorry. I think it wasn't just two views pointing at each other (it would, of course, be next to impossible to even create those, unless you hand tweaked the system tables), but I think was a view-relies-on-a- function-relies-on-a-view kind of problem. If I ever see it again, I'll save it. Thanks! -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Warning: Don't delete those /tmp/.PGSQL.* files
On 25 Nov 2000, at 17:35, Tom Lane wrote: So, I began restarting pgsql w/a line like rm -f /tmp/.PGSQL.* postmaster -i log 2log Which works great. Except that I *kept* using this for two weeks after the view problem (damn that bash up-arrow laziness!), and yesterday, used it to restart PostgreSQL except (oops!) it was already running. Results: no database at all. All classes (tables/views/etc) returned 0 records (meaning that no tables showed up in psql's \d, since pg_class returned nothing.) Ugh. The reason that removing the socket file allowed a second postmaster to start up is that we use an advisory lock on the socket file as the interlock that prevents two PMs on the same port number. Remove the socket file, poof no interlock. *However*, there is a second line of defense to prevent two postmasters in the same directory, and I don't understand why that didn't trigger. Unless you are running a version old enough to not have it. What PG version is this, anyway? 7.1devel, from about 1 week ago. Assuming you got past both interlocks, the second postmaster would have reinitialized Postgres' shared memory block for that database, which would have been a Bad Thing(tm) ... but it would not have led to any immediate damage to your on-disk files, AFAICS. Was the database still hosed after you stopped both postmasters and started a fresh one? (Did you even try that?) Yes, I stopped both, rebooted machine, restarted postmaster. Rebooted machine, used just postgres, tried to vacuum, tried to dump, etc. Always the same story. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
