Re: [GENERAL] plpgsql functions
2008/8/15 c k <[EMAIL PROTECTED]>: > Hi, > I am getting an error for a function written in plpgsql, as - > > CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) > RETURNS SETOF uf_closingbal AS > $BODY$begin > select accgroups."accgroupid", COALESCE(sum(osc),0) as obc, > COALESCE(sum(osd),0) as obd, COALESCE(sum(csc),0) as sc, > COALESCE(sum(csd),0) as sd, (COALESCE(sum(osc),0)+COALESCE(sum(csc),0)) as > cc, (COALESCE(sum(osd),0)+COALESCE(sum(csd),0)) as cd, > accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount", > accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid", > accgroups."accobjecttype", accgroups."againstid" from (accgroups left join > (select * from uf_accgroupob($1)) as accob on > accgroups."accgroupid"=accob."accgroupid") left join (select * from > uf_accgroupcurrentbal($1, $2)) as accgcb2 on accgroups."accgroupid" = > accgcb2."accgroupid" group by accgroups."accgroupid", > accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount", > accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid", > accgroups."accobjecttype", accgroups."againstid"; > end; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100 > ROWS 1000; > giving me an error when called from another function as 'query has no > destination for result data'. > Why? Please give the details about creating functions having only SELECT > statements using plpgsql?, What points to be remembered? pl/pgsql isn't SQL/PSM - so there are not allowed one statement functions. Use SQL language instead. Regards Pavel Stehule > > Thanks > CPK > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What's size of your PostgreSQL Database?
Dear all: We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list: 1. What's size of your database? 2. What Operating System are you using? 3. What level is your RAID array? 4. How many cores and memory does your server have? 5. What about your performance of join operations? 6. What about your performance of load operations? 7. How many concurrent readers of your database, and what's the average transfer rate, suppose all readers are doing one table scaning. 8. Single instance or a cluster, what cluster software are you using if you have a cluster? Thank you in advance! -- 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] Re: pg_restore fails on Windows
I wrote: > Of course the larger issue is why it's failing --- 150MB doesn't seem > like that much for a modern machine. I suspect that PQerrorMessage() > would tell us something useful, but pg_restore isn't letting us see it. I've applied a patch for the latter issue. But the only way we can find out what's happening is if someone will build a Windows version from CVS tip for the OP... 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] How to tell if a trigger is disabled
"Ian Harding" <[EMAIL PROTECTED]> writes: > Is there any way to tell if a trigger or triggers are disabled on a > table? I was updating some data a week or two ago and must have > forgotten to re-enable the triggers. Took me a little while to figure > out. \d tablename didn't tell me, nor did \d+ tablename. > This is on 8.2.3. FWIW, 8.3's psql knows about showing this in \dt. 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
[GENERAL] How to tell if a trigger is disabled
Is there any way to tell if a trigger or triggers are disabled on a table? I was updating some data a week or two ago and must have forgotten to re-enable the triggers. Took me a little while to figure out. \d tablename didn't tell me, nor did \d+ tablename. This is on 8.2.3. Thanks, - Ian -- 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] plpgsql functions
On Aug 15, 2008, at 1:47 PM, Raymond O'Donnell wrote: For functions return SETOF any type, you need to use the following idiom: Or, you can use, RETURN QUERY -- 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] Horology test failure while compiling PostgreSQL 8.3.3 on Solaris 10 x64
CFLAGS="-R/opt/usr/local/amd64/lib -xmodel=medium - xtarget=generic64 -fast -xarch=amd64" What does "-fast" do? If it involves any reinterpretation of IEEE float arithmetic accuracy requirements, drop it. The -fast option using sun studio cc expands to include the flag - fsimple=2 which does affect floating point calculations. If you remove the -fast flag it should pass the tests. any special reason why you are using xtarget=generic64 instead of native64? -jovan -- 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] [Q] DNS(bind) ER model
Steve Atkins wrote: On Aug 15, 2008, at 12:16 PM, Andrew Sullivan wrote: On Fri, Aug 15, 2008 at 07:44:36AM -0700, Roderick A. Anderson wrote: Thanks again. This is a pretty specialized application (at this time) so the RRTYPEs used are limited. I am trying to make the model and Pg implementation as generic as possible in case it gets released into the wild later. I made the mistake in the past of not supporting the unknown type, and regretted it. The nice thing about implementing unknown is that you can automatically add another RR later, even if you're not sure what it's supposed to look like. +1 plus the company I'm doing this for gets some strange requests from their customers -- not always correct or logical. :-( We DNS geeks have seen every mistake in the book, and some of the worst ideas are still being developed. (In Dublin, I heard someone from the DKIM working group at last suggest that maybe using the TXT RRTYPE wasn't such a hot idea. I think it's now 5 years since the DNS folks pointed out that TXT was going to cause headaches later. Sigh.) The DKIM people have been pointing that out for at least as long. Guess why they still went with the TXT record? Mostly because of the number of lame self-service DNS interfaces that don't support much apart from A, MX, CNAME and TXT. (To bring it on-topic, mostly because they use very simplistic database backends, I suspect...) Back to the original problem... I'm not sure there's a generic good structure for DNS data, it'd depend a lot on what you were planning on doing with it. Serving DNS directly out of the database is a very different set of needs to basic self-service management, which is a different set of needs to enterprise intranet DNS and so on. Here is the hitch. It is for a application to be used in-house. Actually several applications with all tied together quite closely. 1. Domain information: owner, webmaster, registrar, email manager, etc. 2. IP address allocation: There are several non-contiguous blocks of addresses and they tend to be assigned to a specific system or client. 3. DNS things: This will not be a name server but be used build named.conf and the zone files. 4. And tying the above together an application to let a tech/support/sales person add a new domain and have it automagically assigned the correct IPs for web, mail, ftp, and all that other stuff. 5. Finally a application to allow the system people to add, change, reassign, and delete domain and zone file entries. Rod -- Cheers, Steve -- 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] plpgsql functions
On 15/08/2008 20:12, c k wrote: Hi, I am getting an error for a function written in plpgsql, as - CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) RETURNS SETOF uf_closingbal AS For functions return SETOF any type, you need to use the following idiom: ... declare rec yourtype; begin for rec in [your query here...] loop return next rec; end loop; return; end; ... See the docs on control structures in pl/pgsql. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] syntax error at or near "PERFORM"
On 15/08/2008 05:32, Dale wrote: On Aug 15, 2:14 pm, Dale <[EMAIL PROTECTED]> wrote: even when I try executing something basic: PERFORM (2 + 3); I found my problem. Unfortunately PERFORM can only be used within a "LANGUAGE plpgsql" script. Yes, that's it - outside a function, you just do: select (2 + 3); Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] [Q] DNS(bind) ER model
On Fri, Aug 15, 2008 at 09:54:26PM +0200, Tino Wildenhain wrote: > looks like you want to write your own "powerdns" ? :-) > http://www.powerdns.com/ Oh, right, I forgot they use a SQL back end. They do EDNS0, too :) (Note, however, that if you plan to deploy DNSSEC you're out of luck with them. Bert is hostile to it.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- 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] Regression failing on build -> ERROR: could not access file "$libdir/plpgsql": No such file or directory
Reid Thompson <[EMAIL PROTECTED]> writes: > Would plpgsql.so get built with.. > ./configure --prefix=/usr/local/pgsql833 --without-readline --disable-shared Uh, no. That probably explains why regress.so didn't get built, either. 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] [Q] DNS(bind) ER model
Roderick A. Anderson wrote: Anyone aware of an ER model for holding name server records? Working on the zone file data and I am getting close but keep running into the differences between MX records (with a priority) and the others that can hold either a domain/sub-domain/host name or an IP address depending on whether is an A, TXT, PTR, etc. or a CNAME. Much of the database will be populated and changed automagically so the controller for the application will do the right thing but humans will get involved every so often. I hope I can get the database to make the right thing easy and the wrong thing "impossible" for them. Any suggestions? looks like you want to write your own "powerdns" ? :-) http://www.powerdns.com/ Greets Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] [Q] DNS(bind) ER model
On Aug 15, 2008, at 12:16 PM, Andrew Sullivan wrote: On Fri, Aug 15, 2008 at 07:44:36AM -0700, Roderick A. Anderson wrote: Thanks again. This is a pretty specialized application (at this time) so the RRTYPEs used are limited. I am trying to make the model and Pg implementation as generic as possible in case it gets released into the wild later. I made the mistake in the past of not supporting the unknown type, and regretted it. The nice thing about implementing unknown is that you can automatically add another RR later, even if you're not sure what it's supposed to look like. +1 plus the company I'm doing this for gets some strange requests from their customers -- not always correct or logical. :-( We DNS geeks have seen every mistake in the book, and some of the worst ideas are still being developed. (In Dublin, I heard someone from the DKIM working group at last suggest that maybe using the TXT RRTYPE wasn't such a hot idea. I think it's now 5 years since the DNS folks pointed out that TXT was going to cause headaches later. Sigh.) The DKIM people have been pointing that out for at least as long. Guess why they still went with the TXT record? Mostly because of the number of lame self-service DNS interfaces that don't support much apart from A, MX, CNAME and TXT. (To bring it on-topic, mostly because they use very simplistic database backends, I suspect...) Back to the original problem... I'm not sure there's a generic good structure for DNS data, it'd depend a lot on what you were planning on doing with it. Serving DNS directly out of the database is a very different set of needs to basic self-service management, which is a different set of needs to enterprise intranet DNS and so on. Cheers, Steve -- 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] [Q] DNS(bind) ER model
On Fri, Aug 15, 2008 at 07:44:36AM -0700, Roderick A. Anderson wrote: > Thanks again. This is a pretty specialized application (at this time) so > the RRTYPEs used are limited. I am trying to make the model and Pg > implementation as generic as possible in case it gets released into the > wild later. I made the mistake in the past of not supporting the unknown type, and regretted it. The nice thing about implementing unknown is that you can automatically add another RR later, even if you're not sure what it's supposed to look like. > plus the company I'm doing this for gets some strange requests from their > customers -- not always correct or logical. :-( We DNS geeks have seen every mistake in the book, and some of the worst ideas are still being developed. (In Dublin, I heard someone from the DKIM working group at last suggest that maybe using the TXT RRTYPE wasn't such a hot idea. I think it's now 5 years since the DNS folks pointed out that TXT was going to cause headaches later. Sigh.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql functions
Hi, I am getting an error for a function written in plpgsql, as - CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) RETURNS SETOF uf_closingbal AS $BODY$begin select accgroups."accgroupid", COALESCE(sum(osc),0) as obc, COALESCE(sum(osd),0) as obd, COALESCE(sum(csc),0) as sc, COALESCE(sum(csd),0) as sd, (COALESCE(sum(osc),0)+COALESCE(sum(csc),0)) as cc, (COALESCE(sum(osd),0)+COALESCE(sum(csd),0)) as cd, accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount", accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid", accgroups."accobjecttype", accgroups."againstid" from (accgroups left join (select * from uf_accgroupob($1)) as accob on accgroups."accgroupid"=accob."accgroupid") left join (select * from uf_accgroupcurrentbal($1, $2)) as accgcb2 on accgroups."accgroupid" = accgcb2."accgroupid" group by accgroups."accgroupid", accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount", accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid", accgroups."accobjecttype", accgroups."againstid"; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 1000; giving me an error when called from another function as 'query has no destination for result data'. Why? Please give the details about creating functions having only SELECT statements using plpgsql?, What points to be remembered? Thanks CPK
Re: [GENERAL] Killing active users
> The problem is that sometimes the rename fails because > there are active > users on the available DB... DBD::Pg::db do failed: ERROR: > database > "maindb" is being accessed by other users . > Because the reload takes > place in the wee hours, I feel it's OK to kill any > existing user > connections in order to prevent this if I have to. > You could change ph_hba whilst you rename the databases. I have a similar case here, where I restore a backup of our main servers onto a "restore" machine that can be used by the developers as a staging environment, but those developers can still be connected. What I do is have a pg_hba.deny.conf and before I reload the database I rename pg_hba.conf to bg_hba.normal.conf, and pg_hba.deny.conf to pg_hba.conf and restart the server, do my work then put them back and restart the server again. Not sure but you could perhaps even do this with a reload rather than a restart. Glyn Send instant messages to your online friends http://uk.messenger.yahoo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Changing between ORDER BY DESC and ORDER BY ASC
Is there an easy way to write one single query that can alternate between ASC and DESC orders? Ex: CREATE OR REPLACE FUNCTION GetSomeStuff(_start integer, _count integer, _sortDesc boolean) RETURNS SETOF text AS $BODY$ SELECT something FROM whatever WHERE whatever ORDER BY another_column OFFSET $1 LIMIT $2 ($4 = true ? 'DESC' : 'ASC'); $BODY$ LANGUAGE 'sql' VOLATILE; I can think of a few ways, but I am hoping for something more elegant. 1) In my case another_column is numeric, so I could multiple by negative one if I want it in the other order. Not sure what this does to the optimizer if the column is indexed or not. 2) I could write the statement twice, once with ASC and once with DESC, and then use IF/ELSE structure to pick one. 3) I could generate the statement dynamically. I am hoping there is some super secret extension that can handle this. This seems like one of those foolish things in SQL, where it is too declarative. ASC and DESC should be parameters to order by, not a part of the syntax. But I digress... any other suggestions? -- 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] Regression failing on build -> ERROR: could not access file "$libdir/plpgsql": No such file or directory
On Fri, 2008-08-15 at 11:03 -0400, Tom Lane wrote: > Reid Thompson Woulwrites: > > I'm getting this failure on compilation. Could someone point me in the > > direction of a fix? > > You probably ought to inquire into the cause of these: > > gmake[2]: stat:regress.so: There are too many levels of symbolic links to > > translate a path name. > > Usually that indicates circular symlinking :-(. You didn't show us the > part of the log about building plpgsql.so, but I'll bet it had the same > issue, whatever it is. > > regards, tom lane Would plpgsql.so get built with.. Configuration params: ./configure --prefix=/usr/local/pgsql833 --without-readline --disable-shared I'll see if I can figure out the symbolic links recursion issue. thanks, reid -- 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] Custom sort
Can you define a custom sort in postgres? For instance in mysql, you
could do something like (I forget the exact syntax)
ORDER BY FIND_IN_SET(column_name, ('one','two','three'))
I don't really know this syntax but isn't it something like :
ORDER BY column_name='one', column_name='two', column='three' ?
Thanks all. I'm not sure it's the best solution but,
here's what I ended up doing
ORDER BY strpos(column_name, 'One,Two,Three')
--
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] Horology test failure while compiling PostgreSQL 8.3.3 on Solaris 10 x64
"Dot Yet" <[EMAIL PROTECTED]> writes: > Configure command: > ./configure --prefix=/opt/usr/local/amd64 --with-openssl > --with-libraries=/opt/usr/local/amd64/lib > --with-includes=/opt/usr/local/amd64/include > --libdir=/opt/usr/local/amd64/lib --includedir=/opt/usr/local/amd64/include > CFLAGS="-R/opt/usr/local/amd64/lib -xmodel=medium -xtarget=generic64 -fast > -xarch=amd64" What does "-fast" do? If it involves any reinterpretation of IEEE float arithmetic accuracy requirements, drop it. 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] Regression failing on build -> ERROR: could not access file "$libdir/plpgsql": No such file or directory
Reid Thompson <[EMAIL PROTECTED]> writes: > I'm getting this failure on compilation. Could someone point me in the > direction of a fix? You probably ought to inquire into the cause of these: > gmake[2]: stat:regress.so: There are too many levels of symbolic links to > translate a path name. Usually that indicates circular symlinking :-(. You didn't show us the part of the log about building plpgsql.so, but I'll bet it had the same issue, whatever it is. 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] Update taking forever
Hi Oliver, currently idle except for the update statement. Any suggestions why it takes so long to update a couple million rows? update characters set last_update = null Try to update only rows that not already null. update characters set last_update = null where last_update is not null; Jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Horology test failure while compiling PostgreSQL 8.3.3 on Solaris 10 x64
Hi Eveyone, I am trying to compile PostgreSQL 8.3.3 on Solaris 10 x64. The tools involved were: OpenSSL 0.9.8h 28 May 2008 PostgreSQL 8.3.3 CC: Sun C++ 5.8 2005/10/13 Configure command: ./configure --prefix=/opt/usr/local/amd64 --with-openssl --with-libraries=/opt/usr/local/amd64/lib --with-includes=/opt/usr/local/amd64/include --libdir=/opt/usr/local/amd64/lib --includedir=/opt/usr/local/amd64/include CFLAGS="-R/opt/usr/local/amd64/lib -xmodel=medium -xtarget=generic64 -fast -xarch=amd64" The error log snippet is: # parallel group (5 tests): type_sanity horology geometry oidjoins opr_sanity geometry ... ok horology ... FAILED oidjoins ... ok type_sanity ... ok opr_sanity ... ok == shutting down postmaster == server stopped 1 of 114 tests failed. The differences that caused some tests to fail can be viewed in the file "./regression.diffs". A copy of the test summary that you see above is saved in the file "./regression.out". make[2]: *** [check] Error 1 make[2]: Leaving directory `/export/home/pgadmin/postgresql-8.3.3/src/test/regress' make[1]: *** [check] Error 2 make[1]: Leaving directory `/export/home/pgadmin/postgresql-8.3.3/src/test' make: *** [check] Error 2 cat regression.diff: *** ./expected/horology.out Wed Jul 25 13:22:36 2007 --- ./results/horology.out Fri Aug 15 10:49:39 2008 *** *** 2135,2141 SELECT '' AS ten, f1 AS interval, reltime(f1) AS reltime FROM INTERVAL_TBL; ten | interval|reltime ! -+---+--- | @ 1 min | @ 1 min | @ 5 hours | @ 5 hours | @ 10 days | @ 10 days --- 2135,2141 SELECT '' AS ten, f1 AS interval, reltime(f1) AS reltime FROM INTERVAL_TBL; ten | interval| reltime ! -+---+-- | @ 1 min | @ 1 min | @ 5 hours | @ 5 hours | @ 10 days | @ 10 days *** *** 2143,2149 | @ 3 mons | @ 3 mons | @ 14 secs ago | @ 14 secs ago | @ 1 day 2 hours 3 mins 4 secs | @ 1 day 2 hours 3 mins 4 secs ! | @ 6 years | @ 6 years | @ 5 mons | @ 5 mons | @ 5 mons 12 hours | @ 5 mons 12 hours (10 rows) --- 2143,2149 | @ 3 mons | @ 3 mons | @ 14 secs ago | @ 14 secs ago | @ 1 day 2 hours 3 mins 4 secs | @ 1 day 2 hours 3 mins 4 secs ! | @ 6 years | @ 5 years 12 mons 5 days 6 hours | @ 5 mons | @ 5 mons | @ 5 mons 12 hours | @ 5 mons 12 hours (10 rows) == ### Any idea what maybe causing this issue? Thanks in advance, dotyet.
Re: [GENERAL] Re: pg_restore fails on Windows
=?us-ascii?Q?Tom=20Tom?= <[EMAIL PROTECTED]> writes: > Magnus Hagander wrote: >> Can you set up a reproducible test-case that doesn't involve your data, > - test row was inserted using the Java client code > INSERT INTO hibtableattachmentxmldata VALUES (?,?,?) > where value 1 is "" > value 2 is 25MB (i.e. 1024*1024*25) long text of char 'F' (0x46) > value 3 is 25MB (i.e. 1024*1024*25) long byte array filled with values of 5 > (0x5) Hmm. So allowing for escaping of the bytea values, this line is going to be somewhere around 150MB in text form --- and because you used --inserts rather than COPY mode, it will have to be sent in a single message. I think it's pretty obvious that what's happening is we're failing to do that and then not recovering nicely at all. A look at the pg_dump code says the error message is coming from here res = PQexec(conn, qry->data); if (!res) die_horribly(AH, modulename, "%s: no result from server\n", desc); and a look at the libpq code suggests that PQexec will return a NULL on any send failure, which isn't part of its contract either. So we've got robustness issues on both sides of that API :-( Of course the larger issue is why it's failing --- 150MB doesn't seem like that much for a modern machine. I suspect that PQerrorMessage() would tell us something useful, but pg_restore isn't letting us see it. 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] [Q] DNS(bind) ER model
David Goodenough wrote: On Friday 15 August 2008, Roderick A. Anderson wrote: Anyone aware of an ER model for holding name server records? Working on the zone file data and I am getting close but keep running into the differences between MX records (with a priority) and the others that can hold either a domain/sub-domain/host name or an IP address depending on whether is an A, TXT, PTR, etc. or a CNAME. Much of the database will be populated and changed automagically so the controller for the application will do the right thing but humans will get involved every so often. I hope I can get the database to make the right thing easy and the wrong thing "impossible" for them. Any suggestions? Rod -- Have you looked at mydns? It is a database driven DNS server - and it works just fine with Postgresql. David, I believe I looked at mydns, and there was a sqldns also, quite awhile ago thinking to use that model as a starting point but the project got side-lined so I never followed up. The project is back on so I'll look again. Thanks, Rod -- David -- 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] [Q] DNS(bind) ER model
Andrew Sullivan wrote: On Thu, Aug 14, 2008 at 04:20:14PM -0700, Roderick A. Anderson wrote: Anyone aware of an ER model for holding name server records? What about a datatype? I have reason to believe that a company I used to work for implemented such a thing. There was some talk of releasing it, but I think there was some combination of insufficient demand, worries about competitive advantage, and concern about long term support. Thanks A. I did a quick look and a custom TYPE may be useful. Because of my involvement in that project, I can't make direct recommendations about this topic. But supposing one was to go to various DNS-related lists, it seems to me it wouldn't be that hard to get information about the wire datatypes such that you'd have enough information to implement them in Postgres (assuming you know something about Postgres datatypes). More research is on the schedule this week-end. One hint: remember the "unknown" RRTYPE. If you have questions about RRTYPEs, I'm happy to answer. Thanks again. This is a pretty specialized application (at this time) so the RRTYPEs used are limited. I am trying to make the model and Pg implementation as generic as possible in case it gets released into the wild later. Thanks for the offer on the RRTYPEs. Zone files /can/ get pretty hairy plus the company I'm doing this for gets some strange requests from their customers -- not always correct or logical. :-( Rod -- -- 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] Confronting the maximum column limitation
> The > real problem lies with the columns (biological samples) in that it is > rarely the case that we'll have multiple matrices with overlap in columns Should each configuration have its own table, while inheriting from a common base table? -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Regression failing on build -> ERROR: could not access file "$libdir/plpgsql": No such file or directory
I'm getting this failure on compilation. Could someone point me in the direction of a fix? Thanks, reid System: AIX 5.3 $ uname -a AIX aix53-dev-1 3 5 000B357F4C00 Configuration params: ./configure --prefix=/usr/local/pgsql833 --without-readline --disable-shared The Error: ...SNIP... /usr/bin/gmake -C test/regress all gmake[2]: Entering directory `/home/rthompso/postgresql-8.3.3/src/test/regress' /usr/bin/gmake -C ../../../src/port all gmake[3]: Entering directory `/home/rthompso/postgresql-8.3.3/src/port' gmake[3]: Nothing to be done for `all'. gmake[3]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/port' gmake[2]: stat:regress.so: There are too many levels of symbolic links to translate a path name. rm -f regress.so ln -s regress.so gmake[2]: stat:regress.so: There are too many levels of symbolic links to translate a path name. /usr/bin/gmake -C ../../../contrib/spi refint.so autoinc.so gmake[3]: Entering directory `/home/rthompso/postgresql-8.3.3/contrib/spi' gmake[3]: `refint.so' is up to date. gmake[3]: `autoinc.so' is up to date. gmake[3]: Leaving directory `/home/rthompso/postgresql-8.3.3/contrib/spi' rm -rf ./testtablespace mkdir ./testtablespace gmake[2]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/test/regress' gmake[1]: Leaving directory `/home/rthompso/postgresql-8.3.3/src' /usr/bin/gmake -C config all gmake[1]: Entering directory `/home/rthompso/postgresql-8.3.3/config' gmake[1]: Nothing to be done for `all'. gmake[1]: Leaving directory `/home/rthompso/postgresql-8.3.3/config' All of PostgreSQL successfully made. Ready to install. /usr/bin/gmake -C src/test check gmake[1]: Entering directory `/home/rthompso/postgresql-8.3.3/src/test' /usr/bin/gmake -C regress check gmake[2]: Entering directory `/home/rthompso/postgresql-8.3.3/src/test/regress' /usr/bin/gmake -C ../../../src/port all gmake[3]: Entering directory `/home/rthompso/postgresql-8.3.3/src/port' gmake[3]: Nothing to be done for `all'. gmake[3]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/port' gmake[2]: stat:regress.so: There are too many levels of symbolic links to translate a path name. rm -f regress.so ln -s regress.so gmake[2]: stat:regress.so: There are too many levels of symbolic links to translate a path name. /usr/bin/gmake -C ../../../contrib/spi refint.so autoinc.so gmake[3]: Entering directory `/home/rthompso/postgresql-8.3.3/contrib/spi' gmake[3]: `refint.so' is up to date. gmake[3]: `autoinc.so' is up to date. gmake[3]: Leaving directory `/home/rthompso/postgresql-8.3.3/contrib/spi' rm -rf ./testtablespace mkdir ./testtablespace ./pg_regress --temp-install=./tmp_check --top-builddir=../../.. --srcdir=/home/rthompso/postgresql-8.3.3/src/test/regress --temp-port=55432 --schedule=./parallel_schedule --multibyte=SQL_ASCII --load-language=plpgsql == removing existing temp installation== == creating temporary installation== == initializing database system == == starting postmaster== running on port 55432 with pid 618520 == creating database "regression" == CREATE DATABASE ALTER DATABASE == installing plpgsql == ERROR: could not access file "$libdir/plpgsql": No such file or directory command failed: "/home/rthompso/postgresql-8.3.3/src/test/regress/./tmp_check/install//usr/local/pgsql833/bin/psql" -X -c "CREATE LANGUAGE \"plpgsql\"" "regression" server stopped gmake[2]: *** [check] Error 2 gmake[2]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/test/regress' gmake[1]: *** [check] Error 2 gmake[1]: Leaving directory `/home/rthompso/postgresql-8.3.3/src/test' gmake: *** [check] Error 2 make: 1254-004 The error code from the last command is 2. Stop. the lib is getting built, but appears that it is not getting installed as part of the regression test $ pwd /home/rthompso/postgresql-8.3.3 [rthompso@ ]/home/rthompso/postgresql-8.3.3 $ find . -name \*plpgsql\* ./doc/src/sgml/plpgsql.sgml ./src/pl/plpgsql ./src/pl/plpgsql/src/plpgsql.h ./src/pl/plpgsql/src/libplpgsql.a ./src/test/regress/expected/plpgsql.out ./src/test/regress/sql/plpgsql.sql ./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql.html ./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-overview.html ./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-structure.html ./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-declarations.html ./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-expressions.html ./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-statements.html ./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-control-structures.html ./src/test/regress/tmp_check/install/usr/local/pgsql833/doc/html/plpgsql-cursors.html ./src/test/regress/tmp_ch
Re: [GENERAL] [Q] DNS(bind) ER model
On Fri, Aug 15, 2008 at 10:26:54AM +0100, David Goodenough wrote: > Have you looked at mydns? It is a database driven DNS server - and it > works just fine with Postgresql. Given that the references section of its documentation doesn't include a number of important RFCs, are you quite sure it's really a complete DNS server? (Note that I haven't tested it, so I'm asking in genuine ignorance. But the lack of a reference to RFC 2671, which defines EDNS0, sure doesn't give me warm fuzzies. If you're deploying a name server today and it doesn't support EDNS0, it's possible you're going to find that some users can't resolve your names in the near future.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Killing active users
Hi: I have a system where I have 2 DBs. The first is available to the users for general interaction. The second gets reloaded with new data once per night and is then "renamed" to the active one if/when the reload went successfully, preserving the old DB as a standby. Here's a snapshot of that process. alter database maindb rename to temp; alter database standby rename to maindb alter database temp rename to standby The problem is that sometimes the rename fails because there are active users on the available DB... DBD::Pg::db do failed: ERROR: database "maindb" is being accessed by other users . Because the reload takes place in the wee hours, I feel it's OK to kill any existing user connections in order to prevent this if I have to. Q: Is there a way to kill all active users on a DB without having to reboot the DB? Q: Or is my problem moot in that there is a way to leave those connections alone and attached to the now renamed DB? Thanks in Advance for any help -dave
Re: [GENERAL] table name length restriction
Am Friday, 15. August 2008 schrieb Thomas Finneid: > First question is, what is the rationale behind having a limit on the > table name? Is is an implementation detail. Fixed-length name fields are more efficient to process. And when you have fixed-length fields you need to choose some reasonable limit to avoid wasting too much space. > Secondly Any ideas on how to solve this problem? Is the name length > limit configurable? Yes, edit NAMEDATALEN in src/include/pg_config_manual.h and rebuild. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] table name length restriction
Hi I (well, a colleague of mine) have a problem where table names are longer than the 70 char limit. The names must be human readable, i.e. no synthetic name mapping etc. The table creation and the created name are executed automatically, so an algorithm to create a fancy short name is just more pain than its worth. First question is, what is the rationale behind having a limit on the table name? Secondly Any ideas on how to solve this problem? Is the name length limit configurable? regards thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UTF8 in commandprompt (CMD) on XP fails. Tips?
Hi group, I am trying to set up a testenvironment on my developmachine (XP Prof SP3), using PHP as a scriptinglanguage to access Postgres (8.3). I am working on a multilanguagal database, so I picked UTF8 as encoding for this database. Right now I am able to: 1) Send information (Including UTF8) from a html form to PHP. 2) Pick up the info in PHP, and insert it into Postgres 3) Get the info out of Postgres, and display it right as HTML in a browser. (Using HTML strict and encoding UTF-8 of course). So far so good. My problem started with using psql. psql complained to me it couldn't display all characters right when I started my session. So I changed the encoding to UTF8, using: \encoding UTF8 Now I can query from commandline, but many UTF8 characters are displayed wrong. (NOT if I use a webbrowser with UTF8, but only commandline) I read something on the net about using a different font and change my codepage. I did this (without succes): 1) Changed the font of the dosshell to Lucida Console 2) Gave the command chcp 65001 Now, if I give a simple SQL command, it responds with: Not Enough Memory. I am not sure if this is a Postgres problem or Microsoft (probably the latter) Anyway, Is it possible to have psql working with UTF8 in a windows commandprompt? TIA! Regards, Erwin Moller -- 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] [Q] DNS(bind) ER model
On Friday 15 August 2008, Roderick A. Anderson wrote: > Anyone aware of an ER model for holding name server records? > > Working on the zone file data and I am getting close but keep running > into the differences between MX records (with a priority) and the others > that can hold either a domain/sub-domain/host name or an IP address > depending on whether is an A, TXT, PTR, etc. or a CNAME. > > Much of the database will be populated and changed automagically so the > controller for the application will do the right thing but humans will > get involved every so often. I hope I can get the database to make the > right thing easy and the wrong thing "impossible" for them. > > Any suggestions? > > > Rod > -- Have you looked at mydns? It is a database driven DNS server - and it works just fine with Postgresql. David -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Update taking forever
The below statement is now running for 18 hours on a table with ~8 Million Rows, no triggers no fancy stuff. The database is otherwise performing very well and the server is a development server that's currently idle except for the update statement. Any suggestions why it takes so long to update a couple million rows? update characters set last_update = null Note: 'last_update' is a timestamp column but the column does not seem to influence the time it takes to complete the update. -- Oliver
[GENERAL] Re: pg_restore fails on Windows
Magnus Hagander wrote: > Tom Tom wrote: > > Magnus Hagander wrote: > >> Tom Tom wrote: > Tom Tom wrote: > > Hello, > > > > We have a very strange problem when restoring a database on Windows XP. > > The PG version is 8.1.10 > > The backup was made with the pg_dump on the same machine. > > > > pg_restore -F c -h localhost -p 5432 -U postgres -d "configV3" -v > "c:\Share\POSTGRES.backup" > > pg_restore: connecting to database for restore > > Password: > > pg_restore: creating SCHEMA public > > pg_restore: creating COMMENT SCHEMA public > > pg_restore: creating PROCEDURAL LANGUAGE plpgsql > > pg_restore: creating SEQUENCE hi_value > > pg_restore: executing SEQUENCE SET hi_value > > pg_restore: creating TABLE hibconfigelement > > pg_restore: creating TABLE hibrefconfigbase > > pg_restore: creating TABLE hibrefconfigreference > > pg_restore: creating TABLE hibtableattachment > > pg_restore: creating TABLE hibtableattachmentxmldata > > pg_restore: creating TABLE hibtableelementversion > > pg_restore: creating TABLE hibtableelementversionxmldata > > pg_restore: creating TABLE hibtablerootelement > > pg_restore: creating TABLE hibtablerootelementxmldata > > pg_restore: creating TABLE hibtableunversionedelement > > pg_restore: creating TABLE hibtableunversionedelementxmldata > > pg_restore: creating TABLE hibtableversionedelement > > pg_restore: creating TABLE hibtableversionedelementxmldata > > pg_restore: creating TABLE versionedelement_history > > pg_restore: creating TABLE versionedelement_refs > > pg_restore: restoring data for table "hibconfigelement" > > pg_restore: restoring data for table "hibrefconfigbase" > > pg_restore: restoring data for table "hibrefconfigreference" > > pg_restore: restoring data for table "hibtableattachment" > > pg_restore: restoring data for table "hibtableattachmentxmldata" > > pg_restore: [archiver (db)] could not execute query: no result from > server > > pg_restore: *** aborted because of error > > > > The restore unexpectedly fails on hibtableattachmentxmldata table, which > is > >> as > follows: > > CREATE TABLE hibtablerootelementxmldata > > ( > > xmldata_id varchar(255) NOT NULL, > > xmldata text > > ) > > WITHOUT OIDS; > > > > and contains thousands of rows with text field having even 40MB, encoded > in > UTF8. > > The database is created as follows: > > > > CREATE DATABASE "configV3" > > WITH OWNER = postgres > >ENCODING = 'UTF8' > >TABLESPACE = pg_default; > > > > > > The really strange is that the db restore runs OK on linux (tested on > >> RHEL4, > PG version 8.1.9). > > The pg_restore output is _not_ very descriptive but I suspect some > >> dependency > on OS system libraries (encoding), or maybe it is also related to the > size > >> of > the CLOB field. Anyway we are now effectively without any possibility to > >> backup > our database, which is VERY serious. > > Have you ever came across something similar to this? > Check what you have in your server logs (pg_log directory) and the > eventlog around this time. There is probably a better error message > available there. > > //Magnus > > >>> Thank you for your hint. > >>> The server logs does not display any errors, except for > >>> > >>> 2008-08-08 11:14:16 CEST LOG: checkpoints are occurring too frequently > >>> (14 > >> seconds apart) > >>> 2008-08-08 11:14:16 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:14:38 CEST LOG: checkpoints are occurring too frequently > >>> (22 > >> seconds apart) > >>> 2008-08-08 11:14:38 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:14:57 CEST LOG: checkpoints are occurring too frequently > >>> (19 > >> seconds apart) > >>> 2008-08-08 11:14:57 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:15:14 CEST LOG: checkpoints are occurring too frequently > >>> (17 > >> seconds apart) > >>> 2008-08-08 11:15:14 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:15:36 CEST LOG: checkpoints are occurring too frequently > >>> (22 > >> seconds apart) > >>> 2008-08-08 11:15:36 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:15:56 CEST LOG: checkpoints are occurring too frequently > >>> (20 > >> seconds apart) > >>> 2008-08-08 11:15:56 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:16:16 CEST LOG: checkpoints are occurring too frequently > >>> (20 > >> seconds apart) > >>> 2008-08-08 11:16:16 CEST HINT: Consid
