Re: [GENERAL] connecting using libpq breaks printf
Joey Morris wrote:
This is my first attempt at using libpq, and I'm running across a strange
problem. Here is my bare-bones program:
#include stdio.h
#include libpq-fe.h
int main(int argc, char **argv) {
PGconn *conn;
fprintf(stderr, connecting\n);
conn = PQconnectdb(dbname=postgres);
PQfinish(conn);
return 0;
}
I expected this program to print connecting, but in fact I get no output
whatsoever. If I comment out the PQconnectdb and PQfinish lines, I see
connecting as expected. What could be going on here?
I tried your program, and it compiles and runs on my MinGW installation
and also writes the output to stderr.
One silly question first: are you sure that you actually call your
executable? You didn't name it test.exe, did you?
If you have problems printing to stderr, you could write to a log file:
outf = fopen(logfile.txt, a); fprintf(outf, whatever); fclose(outf);
or something like that.
If that doesn't work, odds are good that your code is not executed at all.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath functions
I have executed pg_config and I have the libxml. I think the problem is that xml2 is not enabled, any idea how to enable it? 2009/2/19 Osvaldo Kussama osvaldo.kuss...@gmail.com 2009/2/19 Francisco ricke...@gmail.com: I saw it,but it says: Use of many of these functions requires the installation to have been built with configure --with-libxml When I installed postgreSQL I did not include this option, have I to reinstall postgresql? 2009/2/19 Osvaldo Kussama osvaldo.kuss...@gmail.com 2009/2/19 Francisco ricke...@gmail.com: Hi List, I have installed postgreSQL 8.3 in my Ubuntu Hardy Heron. I want to use xpath functions (like xpath_table), but I can't. An error appears xpath_table does not exists. How could I intregrate xpath funtions with my postgreSQL without reinstalling it? Any solution? Thanks ;) From manual: From PostgreSQL 8.3 on, there is XML-related functionality based on the SQL/XML standard in the core server. That functionality covers XML syntax checking and XPath queries, which is what this module does, and more, but the API is not at all compatible. http://www.postgresql.org/docs/current/interactive/xml2.html PostgreSQL 8.3 XML Functions: http://www.postgresql.org/docs/current/interactive/functions-xml.html Verify with pg_config: http://www.postgresql.org/docs/current/interactive/app-pgconfig.html Osvaldo -- Un día te encontré y al otro te perdí
Re: [GENERAL] Question about functions that return a set of records
Mike Christensen wrote:
I have the following function:
CREATE FUNCTION foo(_userid uuid)
RETURNS SETOF record AS
$BODY$
BEGIN
RETURN QUERY
select n.UserId, u.Alias, n.Date, n.Data
--Bunch of joins, etc
If I understand correctly, I have to return SETOF record since my
result set doesn't match a table and isn't a single value. However,
this means when I want to call it I have to provide a column definition
list, such as:
select * from foo as (...);
Is there any way to specify this column list within the function
itself? The problem I'm running into is I want to call this function
using Npgsql which doesn't appear to support passing in a column
definition list.
You can avoid that problem if you specify the return type in the function
definition.
There are two possibilities:
The classical way is to define a TYPE similar to this:
CREATE TYPE foo_type AS (
_userid uuid,
_alias text,
_date date,
_data text
);
or similar, depending on your select list and data types.
Then you can define the function as:
CREATE FUNCTION foo(_userid uuid) RETURNS SETOF foo_type ...
The new way is to use output parameters. This is a little harder
to understand, but you need not define a foo_type:
CREATE FUNCTION foo(INOUT _userid uuid, OUT _alias text, OUT _date date, OUT
_data text)
RETURNS SETOF RECORD ...
In both cases you can call the function like this:
SELECT * FROM foo('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11');
The OUT parameters are just a different way of specifying the output type.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about functions that return a set of records
Hey thanks for your email, this was exactly the explanation I was
looking for. I figured out the CREATE TYPE technique but I'm gonna give
the out parameters a try as well, it kinda looks cleaner especially if
the only thing that uses the type is a single stored proc..
Albe Laurenz wrote:
Mike Christensen wrote:
I have the following function:
CREATE FUNCTION foo(_userid uuid)
RETURNS SETOF record AS
$BODY$
BEGIN
RETURN QUERY
select n.UserId, u.Alias, n.Date, n.Data
--Bunch of joins, etc
If I understand correctly, I have to return SETOF record since my
result set doesn't match a table and isn't a single value. However,
this means when I want to call it I have to provide a column definition
list, such as:
select * from foo as (...);
Is there any way to specify this column list within the function
itself? The problem I'm running into is I want to call this function
using Npgsql which doesn't appear to support passing in a column
definition list.
You can avoid that problem if you specify the return type in the function
definition.
There are two possibilities:
The classical way is to define a TYPE similar to this:
CREATE TYPE foo_type AS (
_userid uuid,
_alias text,
_date date,
_data text
);
or similar, depending on your select list and data types.
Then you can define the function as:
CREATE FUNCTION foo(_userid uuid) RETURNS SETOF foo_type ...
The new way is to use output parameters. This is a little harder
to understand, but you need not define a foo_type:
CREATE FUNCTION foo(INOUT _userid uuid, OUT _alias text, OUT _date date, OUT
_data text)
RETURNS SETOF RECORD ...
In both cases you can call the function like this:
SELECT * FROM foo('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11');
The OUT parameters are just a different way of specifying the output type.
Yours,
Laurenz Albe
Re: [GENERAL] Logfile permissions
On 2009-02-10, Thomas Guettler h...@tbz-pariv.de wrote: Hi, my logfiles all have this permission: -rw--- 1 postgres postgres14841 10. Feb 08:52 postgresql-2009-02-10_00.log Is it possible that postgres creates group readable files? you could patch and recompile the source. But it may be easier to create the logfile with the permissions you want before starting postgres. (you can do that in the init.d script that launches postgres) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] password for postgres
On 2009-02-13, Kusuma Pabba kusu...@ncoretech.com wrote: i don't know y am i getting this problem when i try to start off postgres it asks me for password: what OS. what command are you using? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres wish list
On 2009-02-19, Sameer Mahajan sameer_maha...@symantec.com wrote: Thanks Craig. Comments inline. [Sameer Mahajan] I will investigate how the unix domain sockets help in my case. Why isn't it the default for postgres installations? Or it isn't believed to be generic enough / straight forward enough to configure? it is enabled by default, but if you specify a host address when connecting (eg 127.0.0.1) you'll get a TCP/IP connection instead. by the default authentication for unix domain sockets is ident which (AIUI) requires the caller have the a unix username that matches their postgres username, but you can change that to md5 (password based) or trust (no authentication) in pg_hba.conf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query with date where clause is very slow
On 2009-02-20, Mike Christensen ima...@comcast.net wrote:
Hi all -
I have a fairly simple query:
select * from subscriptions s
inner join notifications n on n.userid = s.userid
inner join users u on u.userid = s.userid
where s.subscriberid='affaa328-5b53-430e-991a-22674ede6faf'
and n.date (CURRENT_TIMESTAMP - INTERVAL '14 day')::date;
converting timestamp to date is moderately complex (lots of integer division)
try this instead of (CURRENT_TIMESTAMP - INTERVAL '14 day')::date;
('today'::date -14)
indexing notifications on (userid,date) may help significantly too,
bye.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Large object loading stalls
Tom Lane wrote: Hmm, can you attach to the stuck backend and the vacuum worker process with gdb and get stack traces from them? The pg_locks view does not indicate any locking problem, but I'm wondering if there could be a deadlock at the LWLock level. My reply seems to have been lost in the ether. Anyway, I fixed the low fsm settings and managed to replicate the problem in two separate instances and the problem does not appear to be autovacuum, as I was able to observe the process hanging long after autovacuum has been released. Perhaps the vacuuming tasks were getting stuck before because of the too low fsm setting? Anyway - the situation now is that just the loading process is hanging on the server, with an IDLE in transaction. But it is definitely the loading program that is hanging, not the Postgres server. pg_locks 2701646 | wdb | 26359 | 2701645 | wdb | IDLE in transaction | f | | 2009-02-18 23:57:59.619868+00 | 2009-02-18 23:57:58.461848+00 | | -1 Backtrace from postgress process #0 0x2ad9ed3fef15 in recv () from /lib/libc.so.6 #1 0x0053ba38 in secure_read () #2 0x00542700 in pq_comm_reset () #3 0x00542b47 in pq_getbyte () #4 0x005b648d in prepare_for_client_read () #5 0x005b6d7a in PostgresMain () #6 0x0058c34b in ClosePostmasterPorts () #7 0x0058d06e in PostmasterMain () #8 0x005444f5 in main () Backtrace from gribLoad #0 0x2b2ab43c2c8f in poll () from /lib/libc.so.6 #1 0x2b2ab47cc4af in PQmblen () from /usr/lib/libpq.so.4 #2 0x2b2ab47cc590 in pqWaitTimed () from /usr/lib/libpq.so.4 #3 0x2b2ab47cbe72 in PQgetResult () from /usr/lib/libpq.so.4 #4 0x2b2ab47cbf4e in PQgetResult () from /usr/lib/libpq.so.4 #5 0x2b2ab32a0556 in pqxx::connection_base::prepared_exec () from /usr/lib/libpqxx-2.6.8.so #6 0x2b2ab32be6ed in pqxx::transaction_base::prepared_exec () from /usr/lib/libpqxx-2.6.8.so #7 0x2b2ab32b2486 in pqxx::prepare::invocation::exec () from /usr/lib/libpqxx-2.6.8.so #8 0x2b2ab2d9b4cc in wdb::database::WriteValue::operator() () from /usr/lib/libwdbLoaderBase.so.0 #9 0x2b2ab2da27d8 in pqxx::connection_base::performwdb::database::WriteValue () from /usr/lib/libwdbLoaderBase.so.0 #10 0x2b2ab2d99ddb in wdb::database::LoaderDatabaseConnection::loadField () from /usr/lib/libwdbLoaderBase.so.0 #11 0x004182f0 in log4cpp::CategoryStream::operator char [13] () #12 0x004073e8 in ?? () #13 0x0040819f in ?? () #14 0x2b2ab431e4ca in __libc_start_main () from /lib/libc.so.6 #15 0x0040665a in ?? () #16 0x77e3d6c8 in ?? () #17 0x in ?? () Whatever weirdness happens appears to always occur at this point in the process (previous stacktraces we've done point to the same insert statement), but the timing is seemingly totally random (it can occur right away, or the loading can run dozens of times before getting stuck). I am rather at a loss to explain this. We've loaded literally millions of rows with this code, so the functionality is hardly untested. And is it something we are doing, or could we have hit upon some concurrency issue in pq or pqxx transactors? Any hints or tips to help identify the problem would be appreciated. Strangely, if one strace's into the loading process (not the postgres process), then the poll() call on which the process can have been hanging for hours will release and the process will just go on as if nothing has happened. Anyone seen stuff like this happen before? Regards, Michael A. begin:vcard fn:Michael Akinde n:Akinde;Michael org:Meteorologisk Institutt, Norge;IT adr;quoted-printable:;;Gaustadall=C3=A9en 30D;Oslo;;0313;Norge email;internet:michael.aki...@met.no tel;work:22963379 tel;cell:45885379 x-mozilla-html:FALSE url:http://www.met.no version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why I cannot call a function from within an SQL function?
Hi Ho! The following query works well: select count (*) from item_audit where audit_ts = '2008-05-30 00:00:00' and audit_ts = '2008-10-30 00:00:00' and 'wst' != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = status ) But, when I transform it into the following SQL function, the function cannot be created barking: ERROR: syntax error at or near - LINE 6:and $1 != (select split_part(category, '-', 2) create or replace function get_I(text, timestamp, timestamp) returns bigint as 'select count (*) from item_audit as ia where audit_ts = $2 and audit_ts = $3 and $1 != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) ' language sql; What's wrong? Thank you. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
Hi Ho! Sorry, let me revise the query a bit. I copied and pasted the original one from another big query. --- On Fri, 2/20/09, Eus e...@member.fsf.org wrote: The following query works well: select count (*) from item_audit as ia where audit_ts = '2008-05-30 00:00:00' and audit_ts = '2008-10-30 00:00:00' and 'wst' != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) But, when I transform it into the following SQL function, the function cannot be created barking: ERROR: syntax error at or near - LINE 6:and $1 != (select split_part(category, '-', 2) create or replace function get_I(text, timestamp, timestamp) returns bigint as 'select count (*) from item_audit as ia where audit_ts = $2 and audit_ts = $3 and $1 != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) ' language sql; What's wrong? Thank you. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
In response to Eus : Hi Ho! The following query works well: select count (*) from item_audit where audit_ts = '2008-05-30 00:00:00' and audit_ts = '2008-10-30 00:00:00' and 'wst' != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = status ) But, when I transform it into the following SQL function, the function cannot be created barking: ERROR: syntax error at or near - LINE 6:and $1 != (select split_part(category, '-', 2) create or replace function get_I(text, timestamp, timestamp) returns bigint as 'select count (*) from item_audit as ia where audit_ts = $2 and audit_ts = $3 and $1 != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) ' language sql; What's wrong? The quoting. Use $$-quoting around the function, for instance: create or replace function get_I(text, timestamp, timestamp) returns bigint as $$ select count (*) ... $$ language plpgsql; Now you can use simple ' inside the function. Other, but inferior solution, use ''' instead ' inside the function. HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
I'm no expert, but: i might say U should Escape the ` ' ´ char in (select split_part(category, '-', 2) using something like (select split_part(category, \'-\', 2) or however it should be... A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing in e-mail? http://www.brainyquote.com/quotes/authors/m/muhammad_ali.html http://www.brainyquote.com/quotes/authors/e/emma_goldman.html http://www.brainyquote.com/quotes/authors/m/michelangelo.html Bill Watterson - There is not enough time to do all the nothing we want to do. On Fri, Feb 20, 2009 at 2:02 PM, Eus e...@member.fsf.org wrote: Hi Ho! The following query works well: select count (*) from item_audit where audit_ts = '2008-05-30 00:00:00' and audit_ts = '2008-10-30 00:00:00' and 'wst' != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = status ) But, when I transform it into the following SQL function, the function cannot be created barking: ERROR: syntax error at or near - LINE 6:and $1 != (select split_part(category 2) create or replace function get_I(text, timestamp, timestamp) returns bigint as 'select count (*) from item_audit as ia where audit_ts = $2 and audit_ts = $3 and $1 != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) ' language sql; What's wrong? Thank you. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
You are using old-style function declaration where the function body is given as a string enclosed in '. You have to escape all ' inside the body by doubling them. As an alternative, you can use $$ as the begin and end markers of your function body instead of the ' then you don't need to escape. Eus wrote: Hi Ho! The following query works well: select count (*) from item_audit where audit_ts = '2008-05-30 00:00:00' and audit_ts = '2008-10-30 00:00:00' and 'wst' != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = status ) But, when I transform it into the following SQL function, the function cannot be created barking: ERROR: syntax error at or near - LINE 6:and $1 != (select split_part(category, '-', 2) create or replace function get_I(text, timestamp, timestamp) returns bigint as 'select count (*) from item_audit as ia where audit_ts = $2 and audit_ts = $3 and $1 != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) ' language sql; What's wrong? Thank you. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
Hi Ho! --- On Fri, 2/20/09, Miguel Ángel MF michelangel...@gmail.com wrote: I'm no expert, but: i might say U should Escape the ` ' ´ char in (select split_part(category, '-', 2) using something like (select split_part(category, \'-\', 2) or however it should be... Yes, you are right! Thank you for telling me this. I had been looking for this information for half an hour. A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing in e-mail? http://www.brainyquote.com/quotes/authors/m/muhammad_ali.html http://www.brainyquote.com/quotes/authors/e/emma_goldman.html http://www.brainyquote.com/quotes/authors/m/michelangelo.html Bill Watterson - There is not enough time to do all the nothing we want to do. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
Use dollar quoting around your fiction body I'd double up on the single quotes around the dash Sent from my iPhone On Feb 20, 2009, at 8:14 AM, Eus e...@member.fsf.org wrote: Hi Ho! Sorry, let me revise the query a bit. I copied and pasted the original one from another big query. --- On Fri, 2/20/09, Eus e...@member.fsf.org wrote: The following query works well: select count (*) from item_audit as ia where audit_ts = '2008-05-30 00:00:00' and audit_ts = '2008-10-30 00:00:00' and 'wst' != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) But, when I transform it into the following SQL function, the function cannot be created barking: ERROR: syntax error at or near - LINE 6:and $1 != (select split_part(category, '-', 2) create or replace function get_I(text, timestamp, timestamp) returns bigint as 'select count (*) from item_audit as ia where audit_ts = $2 and audit_ts = $3 and $1 != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) ' language sql; What's wrong? Thank you. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
Hi Ho! --- On Fri, 2/20/09, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to Eus : Hi Ho! The following query works well: select count (*) from item_audit where audit_ts = '2008-05-30 00:00:00' and audit_ts = '2008-10-30 00:00:00' and 'wst' != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = status ) But, when I transform it into the following SQL function, the function cannot be created barking: ERROR: syntax error at or near - LINE 6:and $1 != (select split_part(category, '-', 2) create or replace function get_I(text, timestamp, timestamp) returns bigint as 'select count (*) from item_audit as ia where audit_ts = $2 and audit_ts = $3 and $1 != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) ' language sql; What's wrong? The quoting. Use $$-quoting around the function, for instance: create or replace function get_I(text, timestamp, timestamp) returns bigint as $$ select count (*) ... $$ language plpgsql; Now you can use simple ' inside the function. Other, but inferior solution, use ''' instead ' inside the function. Wow, this is great! Now I know the use of `$$'. Does the doc tell this? If yes, I really have missed it. Thank you very much for telling me this. You have saved me a lot of time from quoting a bunch of text. HTH, Andreas Yes, it really helps. Thank you very much. -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
Hi Ho! --- On Fri, 2/20/09, Ketema Harris ket...@ketema.net wrote: Use dollar quoting around your fiction body I'd double up on the single quotes around the dash Yup, I got it. Thank you for your help. Sent from my iPhone Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
Hi Ho!
--- On Fri, 2/20/09, Marc Schablewski m...@clickware.de wrote:
You are using old-style function declaration where the
function body is
given as a string enclosed in '. You have to escape all
' inside the
body by doubling them.
Ah, yes, after re-reading the doc, I found:
--- 8 ---
The syntax of the CREATE FUNCTION command requires the function body to be
written as a string constant. It is usually most convenient to use dollar
quoting (see Section 4.1.2.2) for the string constant. If you choose to use
regular single-quoted string constant syntax, you must double single quote
marks (') and backslashes (\) (assuming escape string syntax) in the body of
the function (see Section 4.1.2.1).
--- 8 ---
I really missed it. Sorry for making noise.
As an alternative, you can use $$ as
the begin
and end markers of your function body instead of the '
then you don't
need to escape.
Yes, this is much better.
Thank you very much for your explanation.
Best regards,
Eus (FSF member #4445)
In this digital era, where computing technology is pervasive, your freedom
depends on the software controlling those computing devices.
Join free software movement today! It is free as in freedom, not as in free
beer!
Join: http://www.fsf.org/jf?referrer=4445
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about functions that return a set of records
On Fri, Feb 20, 2009 at 3:50 AM, Mike Christensen ima...@comcast.net wrote: I have the following function: CREATE FUNCTION foo(_userid uuid) RETURNS SETOF record AS $BODY$ BEGIN RETURN QUERY select n.UserId, u.Alias, n.Date, n.Data --Bunch of joins, etc If I understand correctly, I have to return SETOF record since my result set doesn't match a table and isn't a single value. However, this means when I want to call it I have to provide a column definition list, such as: select * from foo as (...); Is there any way to specify this column list within the function itself? The problem I'm running into is I want to call this function using Npgsql which doesn't appear to support passing in a column definition list. Hmm, Npgsql supports this syntax when your function returns a record. But I think it is easy to add support for a setof record. I'll check it out. To use the support of record in Npgsql, you just need to specify your parameters which will receive the returned values (the output list) as out parameters. Npgsql will take care of them and build the output list for you when calling your function. commandtext = function_name; command.parameters.add(first parameter)); command.parameters[0].Direction = InDirection; command.parameters.add(second parameter)); command.parameters[1].Direction = OutDirection; command.parameters.add(Third parameter)); command.parameters[2].Direction = OutDirection; And when you call your function, Npgsql will pass your first parameter and build the output list with the second and third parameters. For while, if possible, you could use Npgsql support for returning setof refcursor. You can check examples about how to do that in our user manual: http://manual.npgsql.org I hope it helps. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://fxjr.blogspot.com http://www.npgsql.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Service not starting during install
Any ideas now? Are you *sure* you have checked the permissions of the os user_id that is assigned to run the postgreSQL service ? PG is very specific about the permissions it wants and just as importantly it is very specific about the permissions it DOES NOT want. I would suggest you start with this entry which describes installing on Windows. http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows I have installed PG on a number of Win OS machines and in almost every time when I have run into issues you described above it has come back to a permissions. As an example, last time a customer's network tech changed the user_id of the database service to a 'domain admin' and on the next reboot of the box, the pg service just wouldn't start. When we changed it back to a domain user everything worked as it should ... after spending 3 hrs trying to sort out what the problem was. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where to divide application and database
On Thu, Feb 19, 2009 at 11:43:19PM +, Sam Mason wrote: I was just reading over a reply from David Fetter from a couple of days ago; the thread is archived[1] but this question doesn't really relate to it much. The a question about how to arrange tables and David make the following comments: On Tue, Feb 17, 2009 at 09:53:00AM -0800, David Fetter wrote: On Tue, Feb 17, 2009 at 04:40:58PM +, Sam Mason wrote: user_name varchar(50) NOT NULL, As a general design question; should user_name have a UNIQUE constraint on it? i.e. user_name VARCHAR(50) NOT NULL UNIQUE, Yes, it's good to have a UNIQUE constraint, but not this one. To have a sane one, it needs further constraints, and in 8.4, case-insensitive text (citext) type. Here's one that is reasonably sane until citext is available. user_name TEXT, -- unless length is an integrity constraint, use TEXT instead of VARCHAR. then later: CREATE UNIQUE INDEX unique_user_name_your_table ON your_table(LOWER(TRIM(user_name))) You might also require that whitespace be treated in some consistent way, one example of which is simply forbidding whitespace in user_name at all. This you can do via CHECK constraints or a DOMAIN. The reason behind this appears to be moving some of the checks into the database and away from the application. Since a useful database has *many* applications instead of the application, I think this is an excellent move. Single Point of Truth and all that. When I've solved similar problems before, I've tended to make the application more aware of what's going on by having something like: user_name VARCHAR(50) NOT NULL UNIQUE CHECK (user_name ~ '^[a-z][a-z0-9_]*$') My point there was that simply limiting the length isn't enough for many purposes, and when you're adding DOMAIN or other constraints on the value, that's a place to put the length checks in, too. For example, you might well want to set a lower bound on the size of a user_name, not just an upper bound. I don't think that either my nor David's is better in general, they apply to different situations. I don't even think they're *different* in general ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Large object loading stalls
Michael Akinde michael.aki...@met.no writes: Anyway - the situation now is that just the loading process is hanging on the server, with an IDLE in transaction. But it is definitely the loading program that is hanging, not the Postgres server. What the stack traces seem to show is that both the client and the server are waiting for each other to send some data. Which means somebody's bollixed the protocol. In the past we've seen this type of thing caused by multithreaded client programs in which more than one thread tried to use the same PGconn object without adequate interlocking. libpq itself does not provide any threading guards --- if you want more than one thread accessing a PGconn then it's up to you to use a mutex or something to serialize them. Is it possible this case applies here? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where to divide application and database
On Fri, 20 Feb 2009 06:50:22 -0800 David Fetter da...@fetter.org wrote: The reason behind this appears to be moving some of the checks into the database and away from the application. Since a useful database has *many* applications instead of the application, I think this is an excellent move. Single Point of Truth and all that. I generally prefer code clearness and security over presumed performance gains but I was wondering if checks may have an impact on performances and if pg does some optimisation over them. eg. suppose I'm: insert into bla (a,b,c) from select a,b,c from bhu. And bla.a and bhu.a have the same constraint/data type etc... Is postgresql going to check if bhu.a fit in bla.a every time? I may expect this kind of optimisation is done on type and domains but would be too expensive/smart to do it on checks since postgresql should understand the equivalence or inclusion of some checks. So if a lot of stuff is moving around tables... I'd use domains and user defined types rather than checks. reasonable? If postgresql does this kind of optimisation... pushing checks in the DB is going to gain more extra points compared to doing checks at the application level. What I find a bit annoying is politely deal with the error once it is reported back to the application *and* connection and *bandwidth* costs of moving clearly wrong data back and forward. If you've a good mapping between pg types and the application language/library types it becomes easier to keep in sync those checks otherwise it is a really boring job and DB checks becomes just one more security net to maintain. In some places you REALLY appreciate/need that layer... sometimes it just get in the way. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting time-dependent load statistics
Hallöchen! Yesterday I ported a web app to PG. Every 10 minutes, a cron job scanned the log files of MySQL and generated a plot showing the queries/sec for the last 24h. (Admittedly queries/sec is not the holy grail of DB statistics.) But I still like to have something like this. At the moment I just do the same with PG's log file, with log_statement_stats = on But to generate these plots is costly (e.g. I don't need all the lines starting with !), and to interpret them is equally costly. Do you have a suggestion for a better approach? Tschö, Torsten. -- Torsten Bronger, aquisgrana, europa vetus Jabber ID: torsten.bron...@jabber.rwth-aachen.de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting time-dependent load statistics
In response to Torsten Bronger bron...@physik.rwth-aachen.de: Hallöchen! Yesterday I ported a web app to PG. Every 10 minutes, a cron job scanned the log files of MySQL and generated a plot showing the queries/sec for the last 24h. (Admittedly queries/sec is not the holy grail of DB statistics.) But I still like to have something like this. At the moment I just do the same with PG's log file, with log_statement_stats = on But to generate these plots is costly (e.g. I don't need all the lines starting with !), and to interpret them is equally costly. Do you have a suggestion for a better approach? Turn on stats collection and have a look at the various pg_stat* tables. They'll have stats that you can quickly access with considerably lower overhead. Doing it the way you're doing is driving from Pittsburgh to Maine to get to Ohio. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting time-dependent load statistics
On Fri, 2009-02-20 at 17:11 +0100, Torsten Bronger wrote: Hallöchen! Yesterday I ported a web app to PG. Every 10 minutes, a cron job scanned the log files of MySQL and generated a plot showing the queries/sec for the last 24h. (Admittedly queries/sec is not the holy grail of DB statistics.) But I still like to have something like this. At the moment I just do the same with PG's log file, with log_statement_stats = on But to generate these plots is costly (e.g. I don't need all the lines starting with !), and to interpret them is equally costly. Do you have a suggestion for a better approach? Do you want queries, or transactions? If you want transactions you already have that in pg_stat_database. Just do this every 10 minutes: psql -U user -d database -c select now() as time,sum(xact_commit) as transactions from pg_stat_Database Joshua D. Drake Tschö, Torsten. -- Torsten Bronger, aquisgrana, europa vetus Jabber ID: torsten.bron...@jabber.rwth-aachen.de -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting time-dependent load statistics
On Fri, Feb 20, 2009 at 9:11 AM, Torsten Bronger bron...@physik.rwth-aachen.de wrote: Hallöchen! Yesterday I ported a web app to PG. Every 10 minutes, a cron job scanned the log files of MySQL and generated a plot showing the queries/sec for the last 24h. (Admittedly queries/sec is not the holy grail of DB statistics.) But I still like to have something like this. At the moment I just do the same with PG's log file, with log_statement_stats = on But to generate these plots is costly (e.g. I don't need all the lines starting with !), and to interpret them is equally costly. Do you have a suggestion for a better approach? You can turn on log duration, which will just log the duration of queries. That's a handy little metric to have and every so often I turn it on and chart average query run times etc with the actual queries. I also turn on logging long running queries of say 5 or 10 seconds or more. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting time-dependent load statistics
Hallöchen! Joshua D. Drake writes: On Fri, 2009-02-20 at 17:11 +0100, Torsten Bronger wrote: Yesterday I ported a web app to PG. Every 10 minutes, a cron job scanned the log files of MySQL and generated a plot showing the queries/sec for the last 24h. (Admittedly queries/sec is not the holy grail of DB statistics.) But I still like to have something like this. [...] Do you want queries, or transactions? If you want transactions you already have that in pg_stat_database. Just do this every 10 minutes: psql -U user -d database -c select now() as time,sum(xact_commit) as transactions from pg_stat_Database Well, I'm afraid that transactions are too different from each other. Currently, I experiment with SELECT tup_returned + tup_fetched + tup_inserted + tup_updated + tup_deleted FROM pg_stat_database WHERE datname='chantal'; not being sure whether this makes sense at all. ;-) For exmaple, does tup_fetched imply tup_returned? Tschö, Torsten. -- Torsten Bronger, aquisgrana, europa vetus Jabber ID: torsten.bron...@jabber.rwth-aachen.de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fixing invalid owners on pg_toast tables in 8.3.5
We're running 8.3.5 on RHEL4 x86_64. We removed a user yesterday and were greeted with warnings from pg_dump this morning. :) pg_dump: WARNING: owner of data type pg_toast_80075 appears to be invalid The usual archives and google searches produced mainly 8.0 and earlier incidents and suggested resolving this by re-creating a user with that sysid. Since you can no longer specify a SYSID when creating a user (despite what \h in psql says), we gave ALTER type/table a shot with no luck. The owner of the actual table and index is correct, only the type has an invalid owner. I have thus far avoided the temptation to try a manual update... Is there a recommended procedure for resolving this safely? Since Postgres now prevents you from dropping users owning objects, is this a bug, or does it fall into a gray area? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fixing invalid owners on pg_toast tables in 8.3.5
On Fri, 2009-02-20 at 12:01 -0700, Cott Lang wrote: We're running 8.3.5 on RHEL4 x86_64. We removed a user yesterday and were greeted with warnings from pg_dump this morning. :) pg_dump: WARNING: owner of data type pg_toast_80075 appears to be invalid The usual archives and google searches produced mainly 8.0 and earlier incidents and suggested resolving this by re-creating a user with that sysid. Since you can no longer specify a SYSID when creating a user (despite what \h in psql says), we gave ALTER type/table a shot with no luck. Something isn't right: postgres=# create user foobar superuser; CREATE ROLE postgres=# set role foobar; SET postgres=# create type typetext AS (bar text); CREATE TYPE postgres=# select current_user; current_user -- foobar (1 row) postgres=# set role postgres; SET postgres=# drop type typetest; ERROR: type typetest does not exist postgres=# drop user foobar; ERROR: role foobar cannot be dropped because some objects depend on it DETAIL: owner of type typetext The owner of the actual table and index is correct, only the type has an invalid owner. I have thus far avoided the temptation to try a manual update... Is there a recommended procedure for resolving this safely? You can use alter type to change the owner of the type to a valid user but see above. Something is wrong. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fixing invalid owners on pg_toast tables in 8.3.5
Cott Lang c...@internetstaff.com writes: The owner of the actual table and index is correct, only the type has an invalid owner. I have thus far avoided the temptation to try a manual update... That's probably your best bet. Since Postgres now prevents you from dropping users owning objects, is this a bug, or does it fall into a gray area? Can you show us how to reproduce it? We've seen occasional reports of similar things but no one ever managed to produce a test case. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fixing invalid owners on pg_toast tables in 8.3.5
On Fri, 2009-02-20 at 11:25 -0800, Joshua D. Drake wrote: You can use alter type to change the owner of the type to a valid user but see above. Something is wrong. That's what I thought too, but we tried that first with these results: # alter type pg_toast.pg_toast_80075 OWNER TO postgres; ERROR: pg_toast.pg_toast_80075 is a table's row type HINT: Use ALTER TABLE instead. # alter table pg_toast.pg_toast_80075 OWNER TO postgres; ERROR: pg_toast_80075 is not a table, view, or sequence ... that's when I decided this was post-worthy. :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where to divide application and database
On Fri, Feb 20, 2009 at 06:50:22AM -0800, David Fetter wrote: On Thu, Feb 19, 2009 at 11:43:19PM +, Sam Mason wrote: On Tue, Feb 17, 2009 at 09:53:00AM -0800, David Fetter wrote: user_name TEXT, -- unless length is an integrity constraint, use TEXT instead of VARCHAR. then later: CREATE UNIQUE INDEX unique_user_name_your_table ON your_table(LOWER(TRIM(user_name))) You might also require that whitespace be treated in some consistent way, one example of which is simply forbidding whitespace in user_name at all. This you can do via CHECK constraints or a DOMAIN. The reason behind this appears to be moving some of the checks into the database and away from the application. Since a useful database has *many* applications instead of the application, I think this is an excellent move. Single Point of Truth and all that. Oops, I think I failed to read your original message very well then. I failed to notice the forbidding whitespace comment even though I deliberately left it in. Doh! I don't think that either my nor David's is better in general, they apply to different situations. I don't even think they're *different* in general ;) No, they're not really are they. :) -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where to divide application and database
On Fri, Feb 20, 2009 at 04:51:33PM +0100, Ivan Sergio Borgonovo wrote: What I find a bit annoying is politely deal with the error once it is reported back to the application *and* connection and *bandwidth* costs of moving clearly wrong data back and forward. This sounds a bit like premature optimization to me; I don't think many people worry about optimizing the failure code paths. I know I prefer to make sure that things go quickly when they're working. If you're worried about someone performing a DOS attack on a failure then you'd want to optimize it, but surely you'd want the checks early in the application code. If you've a good mapping between pg types and the application language/library types it becomes easier to keep in sync those checks otherwise it is a really boring job and DB checks becomes just one more security net to maintain. It does, but constraints like that aren't going to be changing to regularly are they? -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Compatibilidad RH enterprise 5.3 !!!
Tenia RH enterprise 3.0 con postgresql 7.4.3 y PHP 4.3.2, migre todo a RH enterprise 5.3 con postgresql 8.1.11 y PHP 5.1.6, migre la BD y pude montarla, pero, no tengo acceso a la BD desde apache... algun ayudita al respecto !!! aastorga
Re: [GENERAL] Getting time-dependent load statistics
Hallöchen! Torsten Bronger writes: [...] Currently, I experiment with SELECT tup_returned + tup_fetched + tup_inserted + tup_updated + tup_deleted FROM pg_stat_database WHERE datname='chantal'; Stangely, the statistics coming out of it are extremely high. I just dumped my database with the built-in tool of my web framework and got approximately 50 times as many row accesses from the command above as I have objects in my database. The dump routine of my web framework may do redundant things but not at this extent ... Tschö, Torsten. -- Torsten Bronger, aquisgrana, europa vetus Jabber ID: torsten.bron...@jabber.rwth-aachen.de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fixing invalid owners on pg_toast tables in 8.3.5
Tom, Thanks for the inspiration - I've fixed them manually. I spent a few minutes trying to recreate the obvious test case, and it all works as designed. I reviewed our logs from the user removal yesterday, and the tables linked to these toast tables did not have ownership changed yesterday, so I did more digging. I found a handful of other pg_types with an unusual owner and in every case, the toast type is owned by the user that created the database via full pg_restore some months ago. pg_restore should have created the table as the user running it, and immediately done an ALTER TABLE .. OWNER TO. So does the pg_toast type take ownership from the owner of the originating table, or the user id inserting data that forces a toast table creation ? Either way, it's inconsistent - it's only a very small percentage of tables that would have had toast tables created at restore time that are affected. Seems like two possible issues - 1. Postgres isn't checking type owners before allowing a user drop. 2. Toast type ownership doesn't always change on table ownership change. I can recreate #1 by manually updating pg_type and dropping the user, but I don't know if that's a valid test. If #2 should never happen, I can see why #1 wouldn't be much of an issue. I can't recreate #2. On Fri, 2009-02-20 at 14:32 -0500, Tom Lane wrote: Cott Lang c...@internetstaff.com writes: The owner of the actual table and index is correct, only the type has an invalid owner. I have thus far avoided the temptation to try a manual update... That's probably your best bet. Since Postgres now prevents you from dropping users owning objects, is this a bug, or does it fall into a gray area? Can you show us how to reproduce it? We've seen occasional reports of similar things but no one ever managed to produce a test case. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fixing invalid owners on pg_toast tables in 8.3.5
Cott Lang c...@internetstaff.com writes: I found a handful of other pg_types with an unusual owner and in every case, the toast type is owned by the user that created the database via full pg_restore some months ago. pg_restore should have created the table as the user running it, and immediately done an ALTER TABLE .. OWNER TO. So does the pg_toast type take ownership from the owner of the originating table, or the user id inserting data that forces a toast table creation ? Either way, it's inconsistent - it's only a very small percentage of tables that would have had toast tables created at restore time that are affected. The toast table would be created immediately. ALTER TABLE OWNER is supposed to update ownership of any attached toast table, too, and that should propagate to the pg_type row as well. There isn't supposed to be any way for a toast table to have different ownership from its parent, let alone a composite-type pg_type row have different ownership from the associated pg_class row. We have seen a small number of reports that sometimes toast tables fail to track parent-table updates, fail to get dropped when the parent is, etc. Nobody knows how to reproduce that though :-(. I was hoping you might find some tidbit that would provide the missing link, but no luck yet. Can you see any pattern or common characteristic to the tables whose toast pg_type rows failed to change owner? I'm not sure what to look for exactly, but similarities in the column contents might be a possibility. Also, can you tell if the problem tables were adjacent in the dump that was restored? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Compatibilidad RH enterprise 5.3 !!!
2009/2/20 Angelo Astorga angeloasto...@gmail.com: Tenia RH enterprise 3.0 con postgresql 7.4.3 y PHP 4.3.2, migre todo a RH enterprise 5.3 con postgresql 8.1.11 y PHP 5.1.6, migre la BD y pude montarla, pero, no tengo acceso a la BD desde apache... algun ayudita al respecto !!! Demasiado impreciso; adicionalmente, mejor consulta en pgsql-es-ayuda. Suerte. Too vague; also, better try pgsql-es-ayuda. Good luck. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fixing invalid owners on pg_toast tables in 8.3.5
On Fri, 2009-02-20 at 16:35 -0500, Tom Lane wrote: Can you see any pattern or common characteristic to the tables whose toast pg_type rows failed to change owner? I'm not sure what to look for exactly, but similarities in the column contents might be a possibility. Also, can you tell if the problem tables were adjacent in the dump that was restored? I fiddled around a while, found the problem, and I can repeat it at will. It's actually related to type changes we've made over the months since the restore. Scenario: 1. Create a table as user A. create table toaster ( bread varchar(8000) ); 2. Check typowner of toast type # select typowner from pg_type join pg_class on (typname=relname) where pg_class.oid in (select reltoastrelid from pg_class where relname='toaster'); typowner -- 16388 3. Alter the field type alter table toaster ALTER bread type varchar(9000); 4. Check the typowner of toast type # select typowner from pg_type join pg_class on (typname=relname) where pg_class.oid in (select reltoastrelid from pg_class where relname='toaster'); typowner -- 3555301 The table and toast table owners are not affected: # select relowner from pg_class where relname='toaster'; relowner -- 16388 # select relowner from pg_class where oid in (select reltoastrelid from pg_class where relname='toaster'); relowner -- 16388 Hope this helps. Cott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
