Re: [GENERAL] database name aliases?
Reece Hart wrote: I'd like to be able to have several versions of a database available concurrently and one database alias that refers to the most recent of these. For example: dbname_1-1 dbname_1-2 dbname_1-3 dbname - dbname_1-3 and $ psql -d dbname would connect to dbname_1-3. Any ideas? In 8.2 (currently beta) you can store connection data and database name on an LDAP server and refer to it via a service name. See http://developer.postgresql.org/pgdocs/postgres/libpq-ldap.html You can use the service name to connect: env PGSERVICE=dbname psql This will work with all client interfaces that use libpq to connect. Yours, Laurenz Albe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] R and postgres
Reece Hart wrote: I'd like to get R to talk to postgresql, but my head's spinning among a web of broken links, way outdated web pages, project deprecation announcements and a terrible signal:link ratio. Rdbi and RdbiPgSQL seem to be the answer, despite both being apparently defunct projects. What is the Right Thing for a guy who wants R to talk to postgresql? I think you want this: http://bioconductor.org/packages/1.9/bioc/html/RdbiPgSQL.html The bioconductor project is now maintaining RdbiPgSQL. I think many people also use RODBC to connect R with Postgres. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] stored procedure / Function
Alain Roger wrote: Hi, How to retrieve the IN parameter of a function for later use ? i tried this : CREATE OR REPLACE FUNCTION public.SP_U_001 (TypeOfArticle varchar) RETURNS SETOF public.active_articles AS My advice: Don't quote your identifiers unless you really really want them to be case sensitive. Usually there's no benefit, and it can add quite a bit of confusion (What do you mean my function does not exist?!? It's right here!). $body$ DECLARE TypeArt VARCHAR := TypeOfArticle; rec RECORD; res active_articles; /**/ BEGIN SELECT articletypes.articletype_id INTO tpart FROM articletypes WHERE articletypes.articletype_type = TypeArt; but it seems that TypeArt VARCHAR := TypeOfArticle; does not work Can you help me ? I don't think that assignments in the declare block are allowed. You could make your variable an alias though: DECLARE TypeArt ALIAS FOR TypeOfArticle; You could also move the assignment into the body of the function. Although I wonder why you don't just use the IN parameter. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] FOR ... IN
Alain Roger wrote: Hi, Before (in version 8.0.1), i did the following thing and it was working well...now (in version 8.1.4) it seems that it does not work anymore... problem is with FOR rec IN loop... Could you elaborate on does not work? So how can i tell FOR all RECORDS from select * from articles, articletypes, department where ... LOOP ... ? Well, seeing as this is apparently the same function you posted about earlier, I suspect it's the same problem with the DECLARE block. I rather doubt that FOR .. IN broke between releases. CREATE OR REPLACE FUNCTION public.SP_U_001 (TypeOfArticle varchar) RETURNS SETOF public.active_articles AS $body$ DECLARE TypeArt VARCHAR := $1; rec RECORD; res active_articles; /**/ BEGIN -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Header meaning for pg_dump
On Tue, Nov 07, 2006 at 08:40:54AM +0700, Premsun Choltanwanich wrote: Dear Richard, Regarding the information you give to me, I understand that this information is a thing that normally used by PostgreSQL system. And the information seem to be placed on a comment area. So, Who need to have a clearly understanding on the header information? AFAIK nothing (in the main distribution) actually uses it. It's more a header for people while they're scanning the dump. It also makes a nice anchor point for sed scripts if you want to extract a single object from the dump. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Dump all databases to corresponding files
On Mon, 06 Nov 2006, Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2006-11-06 12:26:43 +0100: On Sun, 05 Nov 2006, CSN wrote: Anybody know of a script that dumps all databases into corresponding dump files I've written this one in bash: [snip] This would break if any database name has white space. Why don't you use while then? psql -qXtc $query template1 | while read dbname; do pg_dump -b -F t $dbname /var/lib/pgsql/backups/$dbname.dump done It won't work if a database name have white space as first or last letter... Or when database name has a newline somewhere (also possible). It's hard to do it right so I did it simply wrong :-) The way to do it right would be somehow forcing psql to output rows separated by nulls (\0) and use xargs --null -i. But I don't know how to do it. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] FOR ... IN
2006/11/6, Alain Roger [EMAIL PROTECTED]: Hi,Before (in version 8.0.1), i did the following thing and it was working well...now (in version 8.1.4) it seems that it does not work anymore...problem is with FOR rec IN loop...So how can i tell FOR all RECORDS from select * from articles, articletypes, department where ... LOOP ... ? thanks,Al.CREATE OR REPLACE FUNCTION public.SP_U_001 (TypeOfArticle varchar) RETURNS SETOF public.active_articles AS$body$DECLARE TypeArt VARCHAR := $1; rec RECORD; res active_articles;/**/BEGIN FOR rec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP res.article_type := rec.articletypes.articletype_type; res.article_author := rec.articles.author; res.department_owner := rec.department.department_name; res.department_picture := rec.department.department_picture; res.article_title := rec.articles.title; res.article_content := rec.articles.content; res.date_creation := rec.articles.creation_date; res.date_start := rec.articles.validity_period_start; res.date_end := rec.articles.validity_period_end; RETURN NEXT res; END LOOP; RETURN; END;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Never forgot of return before END procedure;-- William Leite Araújo
[GENERAL] X.50x OID representation
I need to save something very similar to X.50x IOD:s, that is, paths where the path-components are numbers. For example: 10.5.5003.24.35. Futhermore, I need to sort these in numerical path order, so that if two paths are compared according to the first path component that differs, and this path component is compared numerically, so that e.g. 100 is considered greater than 1. Is there a suitable datatype in PostgreSQL, or some other way to achieve this? Storing the paths as strings would make path-components of different length compare wrongly, e.g. 91 would be considered greater than 900, since the second digit 1 is greater than the second digit 0. One possibility is to store the paths as strings but with the digits within each path component reversed, so that e.g. 4711 becomes 1174. This would work, given that the path-separator compares less than all digits, but is rather uggly and requires a bit of nasty rewriting when updating/inserting/reading rows. Any ideas? Thanks in advance, Egil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] X.50x OID representation
On Tue, Nov 07, 2006 at 03:57:43AM -0800, redhog wrote: I need to save something very similar to X.50x IOD:s, that is, paths where the path-components are numbers. For example: 10.5.5003.24.35. Looks like something for ltree, which is a datatype for storing tree-structured data. http://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib/ltree/README.ltree Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] database name aliases?
Weuse the pg_services.conf file. http://www.postgresql.org/docs/8.1/static/libpq-pgservice.html In the file you can point the alias to whatever db you want. To connect: [bash]$ PGSERIVCE=dbname psql Woody IGLASS Networks From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Reece HartSent: Monday, November 06, 2006 6:07 PMTo: pgsql-generalSubject: [GENERAL] database name aliases? I'd like to be able to have several versions of a database available concurrently and one database alias that refers to the most recent of these. For example:dbname_1-1dbname_1-2dbname_1-3dbname - dbname_1-3and$ psql -d dbnamewould connect to dbname_1-3. Any ideas?Thanks,Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
[GENERAL] when to use pfree?
I just encountered a problem with a C function I've been working on where it broke with the error: could not find block containing chunk ... I tracked the problem down to my use of pfree. Apparently my function was not happy attempting to return a result that was built using values that had been pfree'd. Commenting out the indicated section solved my problem. I understand that one of the advantages of palloc is that allocated memory is automatically returned at some point. My question is, when does it make sense to call pfree? I wouldn't have expected the variables I free'd below to still be needed, but apparently they were. So now I'm feeling a little intimidated about using pfree at all. Should I just save a little wear and tear on my keyboard and forgo the use of pfree altogether? aim = TupleDescGetAttInMetadata( td ); ht = BuildTupleFromCStrings( aim, vals); /* make the tuple into a datum */ result = HeapTupleGetDatum( ht ); ... // pfree( rd ); // pfree( vals[0] ); // pfree( vals[1] ); // pfree( vals[2] ); // pfree( vals ); PG_RETURN_DATUM( result ); TIA -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] when to use pfree?
On Tue, Nov 07, 2006 at 08:36:45AM -0500, Ron Peterson wrote:
I just encountered a problem with a C function I've been working on
where it broke with the error:
could not find block containing chunk ...
I tracked the problem down to my use of pfree.
I narrowed the problem down a little bit more. It has nothing to do
with the value I'm returning, it's only the call to 'pfree( rd )' below
that causes me problems.
I did the following, which apparently causes problems. I wrote my own
little function which allocates rd (using palloc).
char*
tp2cp_palloc( char* stringp, const text* textp ) {
int len;
len = VARSIZE(textp) - VARHDRSZ;
stringp = (char*)palloc( len + 1 );
if( ! memcpy( stringp, VARDATA(textp), len ) ) { return NULL; }
if( ! memset( stringp + len, '\0', 1 ) ) { return NULL; }
return stringp;
}
Which I call like
otherfunc() {
char* rd;
if( ! tp2cp_palloc( rd, rand_dev ) )
...
pfree( rd );
}
Apparently pfree hates that.
Should I abandom this idiom altogether? Or is it o.k. to do this if I
avoid the call to pfree (i.e. - will the variable be deallocated
automatically)?
TIA.
--
Ron Peterson
https://www.yellowbank.com/
aim = TupleDescGetAttInMetadata( td );
ht = BuildTupleFromCStrings( aim, vals);
/* make the tuple into a datum */
result = HeapTupleGetDatum( ht );
...
// pfree( rd );
// pfree( vals[0] );
// pfree( vals[1] );
// pfree( vals[2] );
// pfree( vals );
PG_RETURN_DATUM( result );
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] when to use pfree?
Ron Peterson [EMAIL PROTECTED] writes:
char*
tp2cp_palloc( char* stringp, const text* textp ) {
int len;
len = VARSIZE(textp) - VARHDRSZ;
stringp = (char*)palloc( len + 1 );
if( ! memcpy( stringp, VARDATA(textp), len ) ) { return NULL; }
if( ! memset( stringp + len, '\0', 1 ) ) { return NULL; }
return stringp;
}
That's simply bizarre coding style. stringp should be a local in
tp2cp_palloc, not a passed parameter that you ignore the value of.
Which I call like
otherfunc() {
char* rd;
if( ! tp2cp_palloc( rd, rand_dev ) )
...
The above does not cause rd to become set in otherfunc().
Had you been using a reasonable set of compiler flags, the compiler
would have warned you that rd was uninitialized in otherfunc().
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] R and postgres
I use RODBC which is available from http://cran.r-project.org/ I'm not sure if this will do what you want, or whether it has the features of Rdbi you need, but it gets the job done for me. I can open a channel, execute a SQL statement (typically a Select) and read the results back into a R dataframe. There is a nice way to dump a dataframe back into a table which is created for you, with columns and datatypes as appropriate. Hope this helps. TJ O'Donnell http://www.gnova.com/ I'd like to get R to talk to postgresql, but my head's spinning among a web of broken links, way outdated web pages, project deprecation announcements and a terrible signal:link ratio. Rdbi and RdbiPgSQL seem to be the answer, despite both being apparently defunct projects. What is the Right Thing for a guy who wants R to talk to postgresql? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: RE : Re: [GENERAL] first steps in PhP and PostgreSQL
No, that's the access log. Check the error log.
On Tue, 7 Nov 2006, Desmond Coughlan wrote:
X-No-Archive: true
Yep...
192.168.0.254 - - [07/Nov/2006:10:12:57 +0100] GET /php_experimental/base.php HTTP/1.1 200 -
- Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
D.
Ben [EMAIL PROTECTED] a écrit :
Have you checked your webserver error logs?
On Mon, 6 Nov 2006, Desmond Coughlan wrote:
X-No-Archive: true
Hi,
I'm sure that it's a typo or something, but as I'm getting into PhP and
PostgreSQL for the first time, I can't be sure.
I have a db, called 'cdi' ..
A 'SELECT * FROM stock;' gets me this in psql ..
cdi= SELECT * from stock ;
-[ RECORD 1 ]-+---
stock_ids | 1
isbn_no | 10101010
code_livre | 23455
titre | toto goes to Hollywood
editeur | editions toto
collection | collection toto
auteur_nom | smith
auteur_prenom | john
matiere | ang
media_type | li
-[ RECORD 2 ]-+---
stock_ids | 2
isbn_no | 10536278
code_livre | 24874
titre | toto comes back from Hollywood
editeur | editions baba
collection | collection toto
auteur_nom | martin
auteur_prenom | peter
matiere | fre
media_type | dvd
So the db is populated. I now do this in a file called base.php ..
pg_connect (dbname=cdi user=cdi password=toto) or die
(Couldn't Connect: .pg_last_error());
$query=SELECT * FROM stock;
$query=pg_query($query);
// start the output
while($row=pg_fetch_array($query,NULL,PGSQL_ASSOC)) {
echo Title: .$row['isbn_no'].
;
echo blah .$row['code_livre'].
;
}
?
I copy that file to my apache server, in php_experimental/base.php and access
it via a browser.
I don't get an error message. I instead get a blank page.
Your advice would be welcome.
D.
-
Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions !
Profitez des connaissances, des opinions et des expériences des internautes sur
Yahoo! Questions/Réponses.
---(end of broadcast)---
TIP 1: 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
--
Des Coughlan
[EMAIL PROTECTED]
Un client de plus, c'est un relou de plus...
-
Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son
interface révolutionnaire.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] FOR ... IN
2006/11/7, Alain Roger [EMAIL PROTECTED]: but there is already a RETURN NEXT res;so what will be the point of this RETURN after the END LOOP; ?http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html -- William Leite Araújo
Re: [GENERAL] FOR ... IN
Hi William,i've read that RETURN should be used when function does not return a set. in my case, i return a set. so i can not write twice return.Here is my latest version of my function.-- Function: SP_U_001(typeofarticle varchar) -- DROP FUNCTION SP_U_001(typeofarticle varchar);CREATE OR REPLACE FUNCTION SP_U_001(VARCHAR) RETURNS SETOF active_articles AS$BODY$DECLARE myrec RECORD; res active_articles; /**/BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type ; res.article_author := myrec.articles.author; res.department_owner := myrec.department.department_name; res.department_picture := myrec.department.department_picture; res.article_title := myrec.articles.title; res.article_content := myrec.articles.content; res.date_creation := myrec.articles.creation_date; res.date_start := myrec.articles.validity_period_start; res.date_end := myrec.articles.validity_period_end; END IF; RETURN NEXT res; END LOOP; RETURN;END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk; GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk;and this is the error message i get :ERROR: schema myrec does not existCONTEXT: SQL statement SELECT myrec.articletypes.articletype_type PL/pgSQL function sp_u_001 line 17 at assignmentline 17 consists of WHERE close if you count comments, if not, i consists of last line of my SELECT command == AND articles.validity_period_end now()On 11/7/06, William Leite Araújo [EMAIL PROTECTED] wrote:2006/11/7, Alain Roger [EMAIL PROTECTED]: but there is already a RETURN NEXT res;so what will be the point of this RETURN after the END LOOP; ? http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html -- William Leite Araújo
Re: [GENERAL] EXECUTE INSERT BUGS?
On Tue, 2006-11-07 at 00:01 -0500, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: On Mon, 2006-11-06 at 16:40 -0800, Matthew Peter wrote: v_value text := null; -- ^^^ right here, NULL makes the querystring fail by setting cmd = null BEGIN cmd := 'INSERT INTO test ( col ) values ( ' || quote_literal(v_value) || ');'; EXECUTE cmd; Concatenation with NULL yields NULL, which is the correct behavior. Hm. I wonder whether we should redefine quote_literal as a non-strict function that delivers NULL (*without* any quotes) when fed a null input. While that would do the Right Thing in this particular example, I'm worried that it might do the wrong thing in other contexts... Comments? One potential problem is if someone is passing a statement to EXECUTE like: SELECT 'foo' 'bar'; Then they could potentially end up with a statement like: SELECT NULL NULL; If the values of two variables were NULL instead of 'foo' and 'bar'. If the author of the function uses COALESCE() before quote_literal(), he'd be fine, but if he used it afterward, his function would stop working. There are similar situations in other places where the SQL standard treats NULL differently from a string literal. For instance: SELECT INTERVAL '0 minutes'; Again, if they COALESCE() to (for example) '0 minutes' after the quote_literal, it will fail. If they COALESCE() before, it will of course work fine. Also: IF foo = bar -- fails IF quote_literal(foo) = quote_literal(bar) -- succeeds Also, it would change the bahavior when calling quote_literal() on the return from a previous quote_literal(). We could avoid potential confusion (if there is any) by making a new function with a name that better communicates what it does. Is there a name that means converts a value into a string that would evaluate to that value? I'm not arguing against changing it to non-strict, it probably avoids more confusion than it would cause. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] running external programs
Hi all, Is it possible in a PLSQL function to call an external program/script residing at /usr/bin and return the result ? are there any workarounds for this sort of a problem ? regards, KM ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] running external programs
km [EMAIL PROTECTED] schrieb: Hi all, Is it possible in a PLSQL function to call an external program/script residing at /usr/bin and return the result ? No, because plsql is a trusted language. You can't run external commands from such a language. are there any workarounds for this sort of a problem ? Yes, with untrusted languages like plperlU or plsh or other. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] database name aliases?
On Mon, 2006-11-06 at 19:58 -0800, Reece Hart wrote: On Mon, 2006-11-06 at 16:43 -0800, Jeff Davis wrote: You can use ALTER DATABASE name RENAME TO newname;. Does that help? This is what I do now to evolve from development to staging to production, as well as to deprecate versions. That indeed solves most of the problem. Aliases might solve two problems. The first is to address the oft- recurring problem of wanting to be able to refer simultaneously to an instance and more generally to a concept (e.g., HEAD in cvs, or /etc/alternatives/ for system executables, etc). That is, one could refer to a specific db version/instance as well as a name for the most recent version (or dev, stage, prod, or whatever). I see what you're trying to do, but PostgreSQL just doesn't have that capability. An extra layers of indirection may be nice, but in this case, it doesn't exist. You should probably take a look more on the application side. You can probably accomplish what you need with network software like PgPool. I don't think that can currently do what you need, but that might be a better place to implement the features you need. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] database name aliases?
Woody and Albe- I wasn't aware of pg_service -- that does solve my original problem. Thanks for the replies. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Re: [GENERAL] running external programs
In response to Andreas Kretschmer [EMAIL PROTECTED]: are there any workarounds for this sort of a problem ? Yes, with untrusted languages like plperlU or plsh or other. You can also write your own stored procedures that duplicate the functionality of the external program ... assuming you have that kind of access to the source code of the external program. -- Bill Moran Collaborative Fusion Inc. IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] running external programs
km wrote: Hi all, Is it possible in a PLSQL function to call an external program/script residing at /usr/bin and return the result ? are there any workarounds for this sort of a problem ? regards, KM You can do it with a PLperl function. You would have to use the untrusted PLperlu though. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] running external programs
Is it possible in a PLSQL function to call an external program/script residing at /usr/bin and return the result ? No, because plsql is a trusted language. You can't run external commands from such a language. Is that a deciding criteria for a language to be flagged trusted or not ? KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] running external programs
On Tuesday 07 November 2006 10:55, km wrote: Is it possible in a PLSQL function to call an external program/script residing at /usr/bin and return the result ? No, because plsql is a trusted language. You can't run external commands from such a language. Is that a deciding criteria for a language to be flagged trusted or not ? besides other reasons, yes. UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] per-row security
On Mon, Nov 06, 2006 at 01:40:18PM -0800, Marc Munro wrote: You will of course be replicating the underlying tables and not the views, so your replication user will have to have full access to the unsecured data. This is natural and should not be a concern but may be worth explicitly documenting. In Slony, the replication user has to be a superuser anyway, so it would have access to that data no matter what. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] I'm lost :-( with FOR...IN
Hi,I' still with my stored procedure :-- Function: SP_U_001(typeofarticle varchar)-- DROP FUNCTION SP_U_001(typeofarticle varchar);CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR) RETURNS SETOF active_articles AS$BODY$DECLARE myrec RECORD; res active_articles;/**/BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type; res.article_author := myrec.articles.author; res.department_owner := myrec.department.department_name ; res.department_picture := myrec.department.department_picture; res.article_title := myrec.articles.title; res.article_content := myrec.articles.content; res.date_creation := myrec.articles.creation_date ; res.date_start := myrec.articles.validity_period_start; res.date_end := myrec.articles.validity_period_end; END IF; RETURN NEXT res; END LOOP; RETURN;END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk;GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk;whatever, i do the argument VARCHAR will be stored in double quote as varchar when i check via pgAdmin GUI. Even if in command line it looks like above. I still have the same error message on myrec :ERROR: schema myrec does not existCONTEXT: SQL statement SELECT myrec.articletypes.articletype_typei do not understand as there is quite the same example in postgreSQl 8.1.4 documentation on page 623-624 about Looping Through Query Results.so where am i wrong ?Al.
[GENERAL] WAL ends before end time of backup dump
Version: 8.1.4 I am having a problem restoring one of my base backups. I took a successful backup of the production DB already since this one, and this is just a routine test, so it's fortunately not an emergency. I think that I either have a corrupted base backup or corrupted WAL segments, or maybe I hit some strange bug. When I try to restore, I point recovery.conf to the full set of archived WAL segments, and get the following result: [snip] LOG: restored log file 00010017002B.004A3CAC.backup from archive LOG: restored log file 00010017002B from archive LOG: checkpoint record is at 17/2B4CDC58 LOG: redo record is at 17/2B4A3CAC; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 41438715; next OID: 42280 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: automatic recovery in progress LOG: redo starts at 17/2B4A3CAC LOG: record with zero length at 17/2B6EACC8 LOG: redo done at 17/2B6EAC84 LOG: restored log file 00010017002B from archive PANIC: WAL ends before end time of backup dump LOG: startup process (PID 88979) was terminated by signal 6 LOG: aborting startup due to startup process failure If I restore from the earlier base backup, which grinds slowly through a week's worth of WAL segments, it stops at segment 00010017002B, like so: [ snip ] LOG: restored log file 00010017002B from archive LOG: record with zero length at 17/2B6EACC8 LOG: redo done at 17/2B6EAC84 LOG: restored log file 00010017002B from archive LOG: archive recovery complete LOG: database system is ready LOG: transaction ID wrap limit is 1094453440, limited by database postgres If I restore from a later backup, everything works fine. The thing that stands out to me about the base backup that doesn't work is that it took several WAL segments to complete. Here's the .backup file for the base backup that fails: $ cat wal/00010017002B.004A3CAC.backup START WAL LOCATION: 17/2B4A3CAC (file 00010017002B) STOP WAL LOCATION: 17/397B7D64 (file 000100170039) CHECKPOINT LOCATION: 17/2B4CDC58 START TIME: 2006-11-05 01:00:01 PST LABEL: 20061105010001.27375.tar.gz STOP TIME: 2006-11-05 01:14:03 PST I noticed the 8.2beta3 included a fix for WAL replay, is that related? Can someone link to the thread about that bug? I can't test newer versions of postgres because all my other backups seem to work. Basically, I'd just like to know what happened to prevent it in the future. I am archiving to an NFS mount, I don't know whether that carries a risk of corruption or not. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] I'm lost :-( with FOR...IN
On 11/7/06, Alain Roger [EMAIL PROTECTED] wrote: Hi, I' still with my stored procedure : -- Function: SP_U_001(typeofarticle varchar) -- DROP FUNCTION SP_U_001(typeofarticle varchar); CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR) RETURNS SETOF active_articles AS $BODY$ DECLARE myrec RECORD; res active_articles; /**/ BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type; res.article_author := myrec.articles.author; res.department_owner := myrec.department.department_name ; res.department_picture := myrec.department.department_picture; res.article_title := myrec.articles.title; res.article_content := myrec.articles.content; res.date_creation := myrec.articles.creation_date ; res.date_start := myrec.articles.validity_period_start; res.date_end := myrec.articles.validity_period_end; END IF; RETURN NEXT res; END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk; GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk; whatever, i do the argument VARCHAR will be stored in double quote as varchar when i check via pgAdmin GUI. Even if in command line it looks like above. I still have the same error message on myrec : ERROR: schema myrec does not exist CONTEXT: SQL statement SELECT myrec.articletypes.articletype_type you are using composite types right? you have to add parenthesis to disambiguate this case: http://www.postgresql.org/docs/8.1/interactive/rowtypes.html#AEN5789 res.article_title := myrec.(articles).title; sorry i missed that the first time out. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [HACKERS] Tsearch Index Size and GiST vs. GIN
On Mon, 2006-11-06 at 13:14 -0600, Richard Whidden wrote: Due to popular demand (1 person), I've compared sizes with 90 and 100 fillfactors, along with using the new GIN index. Findings were not surprising, except for the GIN indexes, which doubled in size. This is how I understand it after reading up on the subject: GIN inserts many index entries for every record inserted in the table, and each index entry points to many tuples. GiST is a one-to-one relationship, one insert causes one index entry to be inserted and that points to one record. After several ALTER/RE INDEXes, here they are: GiST 8.1 = 94990 8.2 FF90 = 106244 relpages (8k) 8.2 FF100 = 95049 GIN --- FF100 = 197702 Regards, Jeff Davis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] I'm lost :-( with FOR...IN
If i do what you wrote, i can not create the function into my DB.error on 1st ( On 11/7/06, Merlin Moncure [EMAIL PROTECTED] wrote:On 11/7/06, Alain Roger [EMAIL PROTECTED] wrote: Hi, I' still with my stored procedure : -- Function: SP_U_001(typeofarticle varchar) -- DROP FUNCTION SP_U_001(typeofarticle varchar); CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR) RETURNS SETOF active_articles AS $BODY$ DECLARE myrec RECORD; res active_articles; /**/ BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type; res.article_author := myrec.articles.author; res.department_owner := myrec.department.department_name ; res.department_picture := myrec.department.department_picture; res.article_title := myrec.articles.title; res.article_content := myrec.articles.content; res.date_creation := myrec.articles.creation_date ; res.date_start := myrec.articles.validity_period_start; res.date_end := myrec.articles.validity_period_end; END IF; RETURN NEXT res; END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk; GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk; whatever, i do the argument VARCHAR will be stored in double quote as varchar when i check via pgAdmin GUI. Even if in command line it looks like above. I still have the same error message on myrec : ERROR:schema myrec does not exist CONTEXT:SQL statement SELECT myrec.articletypes.articletype_typeyou are using composite types right? you have to add parenthesis to disambiguate this case:http://www.postgresql.org/docs/8.1/interactive/rowtypes.html#AEN5789res.article_title := myrec.(articles).title; sorry i missed that the first time out.merlin
Re: [GENERAL] I'm lost :-( with FOR...IN
On Tue, 7 Nov 2006, Alain Roger wrote: Hi, I' still with my stored procedure : -- Function: SP_U_001(typeofarticle varchar) -- DROP FUNCTION SP_U_001(typeofarticle varchar); CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR) RETURNS SETOF active_articles AS $BODY$ DECLARE myrec RECORD; res active_articles; /**/ BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type; I don't think the column names are going to keep their originating table name inside the record, so the field probably needs to be referred to as myrec.articletype_type not myrec.articletypes.articletype_type. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] I'm lost :-( with FOR...IN
i already tried this possibility and i've got :ERROR: set-valued function called in context that cannot accept a setCONTEXT: PL/pgSQL function sp_u_001 line 26 at return next:-( On 11/7/06, Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 7 Nov 2006, Alain Roger wrote: Hi, I' still with my stored procedure : -- Function: SP_U_001(typeofarticle varchar) -- DROP FUNCTION SP_U_001(typeofarticle varchar); CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR) RETURNS SETOF active_articles AS $BODY$ DECLARE myrec RECORD; res active_articles; /**/ BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id = articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP IF (myrec IS NOT NULL) THEN res.article_type := myrec.articletypes.articletype_type;I don't think the column names are going to keep their originating tablename inside the record, so the field probably needsto be referred to as myrec.articletype_type notmyrec.articletypes.articletype_type.
Re: [GENERAL] I'm lost :-( with FOR...IN
Ok guys...i found the stupid problem :-((everytime that i call my stored procedure, i did like that : select sp_u_001('action'); instead of select * from sp_u_001('action');thanks to all of you for your tips, they helped me to understand composite.
one last question : how can i test if myrec composite has some records ?i was thinking about == select count(*) from (myrec); but i'm not sure about the logic of this command.Al.
On 11/7/06, Alain Roger [EMAIL PROTECTED] wrote:
i already tried this possibility and i've got :ERROR: set-valued function called in context that cannot accept a setCONTEXT: PL/pgSQL function sp_u_001 line 26 at return next:-(
On 11/7/06, Stephan Szabo [EMAIL PROTECTED] wrote:
On Tue, 7 Nov 2006, Alain Roger wrote: Hi, I' still with my stored procedure : -- Function: SP_U_001(typeofarticle varchar) -- DROP FUNCTION SP_U_001(typeofarticle varchar);
CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR) RETURNS SETOF active_articles AS $BODY$ DECLARE myrec RECORD; res active_articles; /**/
BEGIN FOR myrec IN select * from articles, articletypes, department where articletypes.articletype_type = $1 AND articles.articletype_id =
articletypes.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now() LOOP IF (myrec IS NOT NULL) THEN
res.article_type := myrec.articletypes.articletype_type;I don't think the column names are going to keep their originating tablename inside the record, so the field probably needsto be referred to as myrec.articletype_type
notmyrec.articletypes.articletype_type.
Re: [GENERAL] X.50x OID representation
Looks like something for ltree, which is a datatype for storing tree-structured data. http://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib/ltree/README.ltree Unfourtunately, ltree seems to compare node labels in lexicographic order: have their usual meanings. Comparison is doing in the order of direct tree traversing, children of a node are sorted lexicographic. Maybe it is possible to hack it to support numeric order, but that would mean I'd introduce a new non-approoved patch that users of my application would have to apply to their database in order to run my application. Since my application is vaguely security related, that is a big non-no - I need to keep dependencies and code simple and easy to get an overview of and review... ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Out of town next week
Hello, I will be teaching another class in Kentucky next week. I will only be sporadically checking email during the day (but will be at night). Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] per-row security
[EMAIL PROTECTED] (Andrew Sullivan) wrote: On Mon, Nov 06, 2006 at 01:40:18PM -0800, Marc Munro wrote: You will of course be replicating the underlying tables and not the views, so your replication user will have to have full access to the unsecured data. This is natural and should not be a concern but may be worth explicitly documenting. In Slony, the replication user has to be a superuser anyway, so it would have access to that data no matter what. When Slony-II work was ongoing, replication was taking place from a deeper level inside the DB engine such that that took place as perhaps even a superduperuser. A replication mechanism which captured updates from transaction logs would have effectively more than superuser access, too. -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxfinances.info/info/languages.html Rules of the Evil Overlord #71. If I decide to test a lieutenant's loyalty and see if he/she should be made a trusted lieutenant, I will have a crack squad of marksmen standing by in case the answer is no. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] R and postgres
On Tue, 2006-11-07 at 00:22 -0800, Joe Conway wrote: I think you want this: http://bioconductor.org/packages/1.9/bioc/html/RdbiPgSQL.html The bioconductor project is now maintaining RdbiPgSQL. I think many people also use RODBC to connect R with Postgres. Joe- I almost sent you an off-list email before I posted because I just knew you'd know the answer. Thank you. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Re: [GENERAL] WAL ends before end time of backup dump
Jeff Davis [EMAIL PROTECTED] writes: LOG: restored log file 00010017002B from archive LOG: record with zero length at 17/2B6EACC8 LOG: redo done at 17/2B6EAC84 It looks to me like you archived this log file before it was fully written. You should take a close look at your archiving procedures. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] WAL ends before end time of backup dump
On Tue, 2006-11-07 at 11:15 -0800, Jeff Davis wrote: Version: 8.1.4 [ snip ] OG: restored log file 00010017002B from archive LOG: record with zero length at 17/2B6EACC8 LOG: redo done at 17/2B6EAC84 LOG: restored log file 00010017002B from archive LOG: archive recovery complete LOG: database system is ready LOG: transaction ID wrap limit is 1094453440, limited by database postgres From the 8.1.4 source I see that: /* * Currently, xl_len == 0 must be bad data, but that might not be true Does that mean that I must have a corrupt WAL segment file 00010017002B? Is it possible this was caused by a bug? Would an already known bug possibly cause this problem? I am sending the transaction logs to an NFS mount. I consider it to be reliable storage, so I can't see how that would have been corrupted unless it was sent that way by PostgreSQL. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] WAL ends before end time of backup dump
On Tue, 2006-11-07 at 17:20 -0500, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: LOG: restored log file 00010017002B from archive LOG: record with zero length at 17/2B6EACC8 LOG: redo done at 17/2B6EAC84 It looks to me like you archived this log file before it was fully written. You should take a close look at your archiving procedures. Hmm... the only way the file gets there is archive command. I didn't do any manual moves of the segments, certainly not last Sunday at 1am (which is when this segment was put there). From postgresql.conf: archive_command = '/usr/local/pgsql/bin/archive_command.bash %p %f' and: --- $ cat /usr/local/pgsql/bin/archive_command.bash #!/usr/local/bin/bash # $1 is the full path to the file to archive # $2 is the filename WAL=$1 WAL_NAME=$2 WAL_ARCHIVE=/path/to/archive echo test ! -f $WAL_ARCHIVE/$WAL_NAME gzip -c $WAL $WAL_ARCHIVE/$WAL_NAME.gz Any obvious holes in there? Is gzip doing something weird? It worked with all my other WAL segments, and I've tested the backups multiple times. Also, the WAL segment is exactly the right size, although I suppose there's no way for it not to be. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] converting Informix outer to Postgres
Hi all, I have been working on this Informix SQL query which has an outer join. I have attached Informix query and my supposedly solution to this query but I cannot get the same count. I appreciate for any help. Thanks. --Informix query select count(u.id) from user u, invention i, inv_contracts ic, inv_milestones im1, milestonedef mdef1, OUTER inv_milestones im2, milestonedef mdef2 where u.id = i.user_id and ic.inv_id = i.id and ic.contract_id = mdef1.contract_id and im1.inv_id = i.id and mdef1.id = im1.milestone_id and im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and ic.contract_id = mdef2.contract_id and im2.inv_id = i.id and mdef2.id = im2.milestone_id and im1.datereceived IS NULL --Postges query select count(u.id) from dbuser u, inv_contracts ic, inv_milestones im1, milestonedef mdef1, --OUTER inv_milestones im2, milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = im2.milestone_id LEFT OUTER JOIN invention i ON im2.inv_id = i.id where u.id = i.user_id and ic.inv_id = i.id and ic.contract_id = mdef1.contract_id and im1.inv_id = i.id and mdef1.id = im1.milestone_id and im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and ic.contract_id = mdef2.contract_id and --im2.inv_id = i.id and --mdef2.id = im2.milestone_id and im1.datereceived IS NULL - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Stable sort?
Is sorting in PostgreSQL stable over subqueries, that is, is select * from (select * from A order by x) as B order by y; equivalent with select * from A order by y, x; ? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Question on inserting and querying at the same time.
I have a database table that has about 90k entries, they are all straightfoward text, and there is only one ID field that I use as primary key for this table. I have two threads working on this table. One of them inserting new content constantly, (about every second) another one idles and only wakes up when I want to query(select) the table. My problem is that from the log it seems to me that since the insertion is done far frequently than query, the query thread never finishes its job. It just hangs there for a long time until I kill it by restarting postgresql. (Even statements like EXPLAIN ANALYZE will hang) My question is : Is my suspicion correct? And would upgrading to 8.0 (or any later 8.x version of postgresql) help with my specific demand? I am using PostgreSQL 7.4.13. Thanks Wei ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Stable sort?
redhog wrote: Is sorting in PostgreSQL stable over subqueries, that is, is select * from (select * from A order by x) as B order by y; equivalent with select * from A order by y, x; Seems as easy to try as to guess. If I did this query right, it seems not. select * from (select random()0.5 as a, random()0.5 as b from generate_series(1,10) order by a) as x order by b; a | b ---+--- f | t f | f f | f f | f f | t f | f t | t t | t t | t t | f (10 rows) OTOH, HEY, why isn't that result ordered by 'b' instead of by 'a' (or am I misreading my query or the results)? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] A couple more PostgreSQL C questions
I have a couple of more PostgreSQL C questions, about the following two
compiler warnings:
warning: ISO C90 forbids mixed declarations and code
This appears to be caused by the following statement:
text* rand_dev = PG_GETARG_TEXT_P(0);
in the following context
PG_FUNCTION_INFO_V1( y_somefunc );
Datum
y_somefunc ( PG_FUNCTION_ARGS )
{
if( PG_ARGISNULL(0) ||
PG_ARGISNULL(1) ||
PG_ARGISNULL(2) )
{
PG_RETURN_NULL();
}
text* rand_dev = PG_GETARG_TEXT_P(0);
...
Should I be concerned by this? What's the proper way to code this?
_
warning: passing argument 3 of 'GetAttributeByNum' from incompatible pointer
type
...caused by the following:
bool isNull;
...
n = GetAttributeByNum( tup, 0, isNull );
executor.h has:
/*
* prototypes from functions in execQual.c
*/
extern Datum GetAttributeByNum(HeapTupleHeader tuple, AttrNumber attrno,
bool *isNull);
I'm just not seeing what's wrong here...
--
Ron Peterson
https://www.yellowbank.com/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] A couple more PostgreSQL C questions
On Tue, Nov 07, 2006 at 07:50:52PM -0500, Ron Peterson wrote: I have a couple of more PostgreSQL C questions, about the following two compiler warnings: warning: ISO C90 forbids mixed declarations and code I'm thinking this is unavoidable, and unless my time machine starts working, irrelevant. I'm thinking the correct answer is just live with it until your version of gcc uses c99 as the default standard. warning: passing argument 3 of 'GetAttributeByNum' from incompatible pointer type bool isNull; src/include/c.h has typedef char bool; which was conflicting with the definition of bool from elsewhere. I just did my own typdef char pg_bool and used that. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] A couple more PostgreSQL C questions
Ron Peterson [EMAIL PROTECTED] writes:
Datum
y_somefunc ( PG_FUNCTION_ARGS )
{
if( PG_ARGISNULL(0) ||
PG_ARGISNULL(1) ||
PG_ARGISNULL(2) )
{
PG_RETURN_NULL();
}
text* rand_dev = PG_GETARG_TEXT_P(0);
...
Should I be concerned by this? What's the proper way to code this?
The proper way to code that is either
{
text* rand_dev;
if( PG_ARGISNULL(0) ||
PG_ARGISNULL(1) ||
PG_ARGISNULL(2) )
{
PG_RETURN_NULL();
}
rand_dev = PG_GETARG_TEXT_P(0);
...
or probably better, declare the function STRICT and drop the runtime
ARGISNULL tests entirely.
I'm thinking the correct answer is just live with
it until your version of gcc uses c99 as the default standard.
Declarations in the middle of a code block are C++, not C; if you
try to hold your breath until your C compiler accepts it, you will die.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Per-row security
On 11/4/06, Alexander Staubo [EMAIL PROTECTED] wrote: I am designing an application which requires fine-grained role-basedsecurity, where every logical object in the system has an ACL whichexpresses the permissions allowed by roles. Have you considered viel for Postgres ()? Here's what it says in it's introduction: Introduction Veil is a data security add-on for Postgres. It provides an API allowing you to control access to data at the row, or even column, level. Different users will be able to run the same query and see different results. Other database vendors describe this as a Virtual Private Database.Although it is in Alpha, you should give it a try, as this will also help the project in testing the functionality in real world apps like yours. Regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] [ADMIN] Is there anyway to...
Moving to -general (and please start a new thread instead of hijacking an existing one). On Thu, Nov 02, 2006 at 01:14:22PM -0500, louis gonzales wrote: Hello all, Is there an existing mechanism is postgresql that can automatically increment/decrement on a daily basis w/out user interaction? The use case I'm considering is where a student is in some type of contract with an instructor of some sort, and that contract puts a time limit on the student requiring her to pay a fee by a certain day. IF that day comes to pass - or a certain number of days elapse - and that payment requirement hasn't been met, I want to trigger a function. The one requirement I want to impose is, that no end user of the DB application, needs to do anything to set the trigger, other than the initialization of making the student of this type. An example would be: Day1 - Application user(typically the instructor) creates a profile for a new student - John Doe, which sets a 30 day time limit for John Doe to pay $100.00 Day2 - Day31 - John Doe didn't make the payment Day 31 - Trigger of event occurs when the instructor logs in. Basically on Day 1 when John Doe's profile was created, I want a decrement counter to occur daily on his profile(some attribute/timer) and nothing should happen until day 31 when he doesn't pay. While you could setup a cron job to decrement some counter every day, I think that's not the best approach. Instead, I'd run a query once a day that finds all students that are past-due and takes some kind of action. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] delete performance is extremely slow
I am using postgresql 8.0.0 i have 4 tables a, b, c, d. with foreign key constraints. between table b and a between c and b and between d and c I am doing delete from a the total number of rows in table a is around 10 table b is around 20, similarly table c has around 20. table d however has arounf 10,0 delete from a is taking so long . So i just stopped it. what is going wrong thanks, regards Surabhi
Re: [GENERAL] delete performance is extremely slow
On Tuesday 07 November 2006 22:59, surabhi.ahuja [EMAIL PROTECTED] wrote: I am using postgresql 8.0.0 i have 4 tables a, b, c, d. with foreign key constraints. between table b and a between c and b and between d and c I am doing delete from a delete from a is taking so long . So i just stopped it. what is going wrong You're most likely missing an index on one of the foreign key fields in one of the tables that the delete is cascading to. -- A democracy is a sheep and two wolves deciding on what to have for lunch. Freedom is a well armed sheep contesting the results of the decision. -- Benjamin Franklin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] R and postgres
Reece Hart wrote: On Tue, 2006-11-07 at 00:22 -0800, Joe Conway wrote: I think you want this: http://bioconductor.org/packages/1.9/bioc/html/RdbiPgSQL.html The bioconductor project is now maintaining RdbiPgSQL. I think many people also use RODBC to connect R with Postgres. I almost sent you an off-list email before I posted because I just knew you'd know the answer. Wouldn't be a problem with me, but better to post so the answer gets into the archives for others to find later. Glad I could help. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
