Re: [GENERAL] Bulk Load Ignore/Skip Feature
Perfect - that appears to be exactly what I was looking for. Cheers Reg Me Please wrote: Il Wednesday 14 November 2007 05:50:36 Willem Buitendyk ha scritto: Will Postgresql ever implement an ignore on error feature when bulk loading data? Currently it is my understanding that any record that violates a unique constraint will cause the copy from command to halt execution instead of skipping over the violation and logging it - as is done in Oracle and DB2. Are there alternative ways of dealing with this scenario that won't consume as much time? Appreciate any help - would love to migrate away from Oracle. Cheers pgloader http://pgfoundry.org/projects/pgloader/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Using generate_series to create a unique ID in a query?
On Mon, Nov 12, 2007 at 03:11:50PM -0800, Sarah Dougherty wrote: To recap with an example, the query below works fine, but how do I add a series to it? generate_series will not help with this. try the sequence approach, or this: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ best regards, depesz -- quicksil1er: postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV! :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Client-requested cast mode to emulate Pg8.2 on v8.3
On Wed, Nov 14, 2007 at 06:56:06PM +1300, Martin Langhoff wrote: Hmmm. We'll have to test and see if we have any in Moodle. All that has happened is that the *implicit* casting is gone. They will now simply produce errors, the fix being to explicity cast it to the type you wanted, rather than the system guessing. The example you gave is not a problem, because unknown != text. It's only an issue if you're doing things like performing text operations (substr,like etc) on non-text things (like dates, numbers, etc). - Is there a way to turn it back to the old behaviour with a warning going to the logs? No. - Is there a way to get v8.2.x to warn on the dubious casts so we can tighten the application side while on v8.2? Seems to me the easiest way would be to try it out on an 8.3 installation and exercise each query once. There may be a better way but I don't know it... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
[GENERAL] autovacuum and reindex
Hello all, I'd like to know if the autovacuum feature also deals with automatically reindexing my indexes. I know Pg8 know comes with a more eficient management of indexes, but I also read in the manuals that it's still good practice to routine reindex de most critical (in terms of speed) indexes. Could someone please tell me if autovacuum does or doesnt take care of reindexing, or if reindexing is or isn't important in pg8. thx++; Joao ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] autovacuum and reindex
Joao Miguel Ferreira wrote: Hello all, I'd like to know if the autovacuum feature also deals with automatically reindexing my indexes. It doesn't. I know Pg8 know comes with a more eficient management of indexes, but I also read in the manuals that it's still good practice to routine reindex de most critical (in terms of speed) indexes. Could someone please tell me if autovacuum does or doesnt take care of reindexing, or if reindexing is or isn't important in pg8. Reindexing isn't as important as it used to be before 7.4, but it is still needed in certain cases. I think the recommendation is to avoid it if you can, and apply only to those cases where you demonstrably need it. I've seen cases on which people was doing REINDEX when they actually needed more frequent vacuuming, so just because queries are faster after a reindex it doesn't mean that it's the cure to the problem. In any case I doubt vacuum is ever going to deal with REINDEX, because that needs exclusive locks on the table which is not something that autovac wants to deal with. Moreover, tables needing reindexing are probably those most contended, so it makes even less sense to be locking them for any nontrivial length of time. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Insert statements really slow
I have found some errors in my perl script that was slowing everything down. I too am now seeing similar speed between postgresql and mysql. Sorry for the confusion. Dave -Original Message- From: Merlin Moncure [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 13, 2007 8:44 PM To: Waller, David Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Insert statements really slow On Nov 9, 2007 12:53 PM, Waller, David [EMAIL PROTECTED] wrote: I have an application that I am porting from MySQL to PostgreSQL and I am working on the import Perl script that process the data. The data is web log data and each line has a variable amount of the fields (mostly because of cookies) so I am using a lot of insert statements. In MySQL I go through a file in about 2 minutes and it is taking about 30 in PG. I have removed all but the primary key index and have done a BEGIN and COMMIT after turning off AUTOCOMMIT. can you give us a better idea of the # records/sec we are talking about here? mysql and pg are usually pretty close in insert performance. maybe there is something fishy going on. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] reserving space in a rec for future update
Hi: I have a situation where I will be inserting thousands of records into a table but leaving 2 of it's columns null. Later on, I will be updating most of those records and putting real values in place of those 2 nulls. As for the ones that do not get updated, I want to leave them null. My concern has to do with record fragmentation at the time of update because there's no room to expand them to accept the non-null data. (BTW, the columns are floating point). Is there a way to initially insert nulls, but reserve space for the future update (and avoid record fragmentation)? Is my record fragmentation concern unfounded? Thanks in Advance !
Re: [GENERAL] Using generate_series to create a unique ID in a query?
hubert depesz lubaczewski [EMAIL PROTECTED] writes:
On Mon, Nov 12, 2007 at 03:11:50PM -0800, Sarah Dougherty wrote:
To recap with an example, the query below works fine, but how do I add a
series to it?
generate_series will not help with this.
try the sequence approach, or this:
http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/
That's a fairly ugly/messy way of doing it. If you're going to need a C
function anyway, why not just do it directly? As in the attachment.
regression=# create function rownum() returns int as '/home/tgl/pgsql/rownum'
regression-# language c;
CREATE FUNCTION
One thing you have to watch out for is that per spec, ORDER BY happens
after evaluation of the SELECT's targetlist, and in fact PG will usually
do it that way if an explicit sort is needed. So for example, this
works fine:
regression=# select rownum(),* from int8_tbl;
rownum |q1|q2
+--+---
1 | 123 | 456
2 | 123 | 4567890123456789
3 | 4567890123456789 | 123
4 | 4567890123456789 | 4567890123456789
5 | 4567890123456789 | -4567890123456789
(5 rows)
but this will not give the desired results:
regression=# select rownum(),* from int8_tbl order by q2;
rownum |q1|q2
+--+---
5 | 4567890123456789 | -4567890123456789
3 | 4567890123456789 | 123
1 | 123 | 456
2 | 123 | 4567890123456789
4 | 4567890123456789 | 4567890123456789
(5 rows)
You can work around it with a subselect:
regression=# select rownum(),* from (select * from int8_tbl order by q2) ss;
rownum |q1|q2
+--+---
1 | 4567890123456789 | -4567890123456789
2 | 4567890123456789 | 123
3 | 123 | 456
4 | 123 | 4567890123456789
5 | 4567890123456789 | 4567890123456789
(5 rows)
However, that bit of ugliness is enough to dissuade me from wanting to
put this into core PG ...
regards, tom lane
#include postgres.h
#include fmgr.h
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
Datum rownum(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(rownum);
Datum
rownum(PG_FUNCTION_ARGS)
{
int32 *ptr;
ptr = (int32 *) fcinfo-flinfo-fn_extra;
if (ptr == NULL)
{
/* First time through for the current query: allocate storage */
fcinfo-flinfo-fn_extra =
MemoryContextAlloc(fcinfo-flinfo-fn_mcxt,
sizeof(int32));
ptr = (int32 *) fcinfo-flinfo-fn_extra;
/* ... and initialize counter */
*ptr = 1;
}
else
(*ptr)++;
PG_RETURN_INT32(*ptr);
}
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Using generate_series to create a unique ID in a query?
hubert depesz lubaczewski [EMAIL PROTECTED] writes: On Wed, Nov 14, 2007 at 10:26:52AM -0500, Tom Lane wrote: That's a fairly ugly/messy way of doing it. If you're going to need a C function anyway, why not just do it directly? As in the attachment. actually you dont have to do it in c. alec pointed (in comments) that there already is statement_timestamp() function, so you can remove the c code, and use statement_timestamp() instead of get_statement_timestamp(). Using statement_timestamp that way at all is pretty horrid, because it has approximately zip to do with the concept of a query. For instance your approach would fail in a query used inside a function that is called more than once in a user-issued command. Nor do I care for the idea that the user should have to assign a distinct name to each use of the function. Lastly, statement_timestamp isn't there at all before 8.2 ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Using generate_series to create a unique ID in a query?
On Wed, Nov 14, 2007 at 10:26:52AM -0500, Tom Lane wrote: That's a fairly ugly/messy way of doing it. If you're going to need a C function anyway, why not just do it directly? As in the attachment. actually you dont have to do it in c. alec pointed (in comments) that there already is statement_timestamp() function, so you can remove the c code, and use statement_timestamp() instead of get_statement_timestamp(). depesz -- quicksil1er: postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV! :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Using generate_series to create a unique ID in a query?
On 11/14/07, Tom Lane [EMAIL PROTECTED] wrote: hubert depesz lubaczewski [EMAIL PROTECTED] writes: On Mon, Nov 12, 2007 at 03:11:50PM -0800, Sarah Dougherty wrote: To recap with an example, the query below works fine, but how do I add a series to it? generate_series will not help with this. try the sequence approach, or this: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ That's a fairly ugly/messy way of doing it. If you're going to need a C function anyway, why not just do it directly? As in the attachment. regression=# create function rownum() returns int as '/home/tgl/pgsql/rownum' regression-# language c; CREATE FUNCTION Any reason why this couldn't appear in the core of some future version? I've been wanting something like this a couple of times before. Note that Oracle has it as well. jan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] reserving space in a rec for future update
On Nov 14, 2007 9:28 AM, Gauthier, Dave [EMAIL PROTECTED] wrote: Hi: I have a situation where I will be inserting thousands of records into a table but leaving 2 of it's columns null. Later on, I will be updating most of those records and putting real values in place of those 2 nulls. As for the ones that do not get updated, I want to leave them null. My concern has to do with record fragmentation at the time of update because there's no room to expand them to accept the non-null data. (BTW, the columns are floating point). I don't think you really understand how PostgreSQL storage works. every update is the exact same as a delete / insert in terms of storage. So, you're worrying about a problem that doesn't exist. Read up Read up on it here: http://www.postgresql.org/docs/8.2/static/mvcc.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reserving space in a rec for future update
On Wed, Nov 14, 2007 at 10:28:30AM -0500, Gauthier, Dave wrote: null. My concern has to do with record fragmentation at the time of update because there's no room to expand them to accept the non-null data. (BTW, the columns are floating point). You have a mistaken idea about how this works. Is there a way to initially insert nulls, but reserve space for the future update (and avoid record fragmentation)? No. Is my record fragmentation concern unfounded? Sort of. The way this will work in Postgres is that, when you UPDATE the row, the old row will be marked dead, and a _new_ row will be written out with the new data. You will need to perform VACUUM in order to keep the table from bloating. You'll want to read the manual carefully about this topic, in order to keep your table from getting so bloated that your free space map becomes useless. One of the weakest areas for PostgreSQL is its behaviour under this sort of most rows updated scenario, and it is wise to plan carefully how you will accomplish these sorts of activities without causing yourself extreme pain. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Using generate_series to create a unique ID in a query?
On 11/14/07, Tom Lane [EMAIL PROTECTED] wrote: Jan de Visser [EMAIL PROTECTED] writes: Any reason why this couldn't appear in the core of some future version? You didn't read to the end of my post ;-). If a rownum() function like this didn't have any gotchas, I'd be in favor of putting it in, but I don't really want to set the behavior in stone just yet. g That's me, the ADHD getting the better off Oh look, waffles! :) jan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Using generate_series to create a unique ID in a query?
Jan de Visser [EMAIL PROTECTED] writes: Any reason why this couldn't appear in the core of some future version? You didn't read to the end of my post ;-). If a rownum() function like this didn't have any gotchas, I'd be in favor of putting it in, but I don't really want to set the behavior in stone just yet. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] reserving space in a rec for future update
In this usage scenario, doesn't the new HOT (heap only tuples) feature of PG8.3 help, in terms of the DB requiring less VACUUM maintenance? I am similarly performing a huge number of inserts, followed by a huge number of updates to fill in a few null fields. The data is indexed by insert time. My problem is, selects using that index degrade over time as updates are performed, presumably because data is no longer ordered sequentially across pages after updates are performed. I was hoping that HOT would help here and am actually installing PG8.3 now in order to perform some testing... Mike Andrew Sullivan wrote: On Wed, Nov 14, 2007 at 10:28:30AM -0500, Gauthier, Dave wrote: null. My concern has to do with record fragmentation at the time of update because there's no room to expand them to accept the non-null data. (BTW, the columns are floating point). You have a mistaken idea about how this works. Is there a way to initially insert nulls, but reserve space for the future update (and avoid record fragmentation)? No. Is my record fragmentation concern unfounded? Sort of. The way this will work in Postgres is that, when you UPDATE the row, the old row will be marked dead, and a _new_ row will be written out with the new data. You will need to perform VACUUM in order to keep the table from bloating. You'll want to read the manual carefully about this topic, in order to keep your table from getting so bloated that your free space map becomes useless. One of the weakest areas for PostgreSQL is its behaviour under this sort of most rows updated scenario, and it is wise to plan carefully how you will accomplish these sorts of activities without causing yourself extreme pain. A ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reserving space in a rec for future update
On Nov 14, 2007, at 10:44 AM, Mike Charnoky wrote: In this usage scenario, doesn't the new HOT (heap only tuples) feature of PG8.3 help, in terms of the DB requiring less VACUUM maintenance? I am similarly performing a huge number of inserts, followed by a huge number of updates to fill in a few null fields. The data is indexed by insert time. My problem is, selects using that index degrade over time as updates are performed, presumably because data is no longer ordered sequentially across pages after updates are performed. I was hoping that HOT would help here and am actually installing PG8.3 now in order to perform some testing... Mike Some, what HOT does is keeps index rows from being updated when updates are made to column values that aren't indexed. The same insert/delete still happens in the table data. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] reserving space in a rec for future update
On Wed, Nov 14, 2007 at 11:31:11AM -0500, Gauthier, Dave wrote: Thanks for the advanced warning about problems with vaccuum ! Note this isn't a _problem_ with vacuum, exactly, it's just the set of compromises that PostgreSQL has settled on. There are other ways of cleaning up the system (defrag, of the sort you were implying is one, pay the cost during transaction is another one, c.). The piper has to be paid, and all we're doing is arguing about what currency we'll use :) A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] reserving space in a rec for future update
On Wed, Nov 14, 2007 at 11:44:55AM -0500, Mike Charnoky wrote: In this usage scenario, doesn't the new HOT (heap only tuples) feature of PG8.3 help, in terms of the DB requiring less VACUUM maintenance? It should, yes. We'll probably know more once 8.3 is in the field. For new work, though, I would certainly suggest trying this on 8.3. I know that's the _point_ of the feature. But if you've already got an application you need to field today, doing it on a beta is risky. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] reserving space in a rec for future update
OK, I didn't know Postgres did it this way. I was hoping it would retain the old rec and update in place (if the updated values could fit). I guess not. I can rewrite the DB loading algorithm to get those values in advance, load into program memory, and reference at the time of the initial load. Thanks for the advanced warning about problems with vaccuum ! -dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: Wednesday, November 14, 2007 11:13 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] reserving space in a rec for future update On Wed, Nov 14, 2007 at 10:28:30AM -0500, Gauthier, Dave wrote: null. My concern has to do with record fragmentation at the time of update because there's no room to expand them to accept the non-null data. (BTW, the columns are floating point). You have a mistaken idea about how this works. Is there a way to initially insert nulls, but reserve space for the future update (and avoid record fragmentation)? No. Is my record fragmentation concern unfounded? Sort of. The way this will work in Postgres is that, when you UPDATE the row, the old row will be marked dead, and a _new_ row will be written out with the new data. You will need to perform VACUUM in order to keep the table from bloating. You'll want to read the manual carefully about this topic, in order to keep your table from getting so bloated that your free space map becomes useless. One of the weakest areas for PostgreSQL is its behaviour under this sort of most rows updated scenario, and it is wise to plan carefully how you will accomplish these sorts of activities without causing yourself extreme pain. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] PLpgsql debugger question
Hi, Does anyone know if there is a debugger function that will return the line numbers that are executable? Also, is the debugger code available at pgfoundry the GUI client that EnterpriseDB has done or is the module that needs to be installed on the server? As I understand it the debugger functions are included by default in 8.3, but how do you install for 8.2? Thanks, Tony Caduto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PLpgsql debugger question
Tom Lane wrote: Tony Caduto [EMAIL PROTECTED] writes: As I understand it the debugger functions are included by default in 8.3, That's incorrect. regards, tom lane Ok, thanks for the info. Back in Sept the debugger was advertised as a feature of 8.3, so if it's not included how is it a feature? Is it going to be included as a contrib module or something else? I am talking about the server side stuff not the EDB GUI client. Thanks, Tony ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PITR and warm standby setup questions
I am on 8.2 production and it will be difficult to upgrade to 8.3. Is it possible to backport the %r fix from 8.3 to 8.2? Regards Dhaval On Nov 13, 2007 11:26 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote: On Mon, 12 Nov 2007, Mason Hale wrote: After the wal segment file is copied by the restore_command script, is it safe to delete it from my archive? While I believe you can toss them immediately, This is almost never possible. The last WAL file that must be kept should be sufficient to allow recovery to restart from the last restartpoint. So a variable number of WAL files needs to be kept, not 1, not 2 and certainly never 0. pg_standby with 8.2 provides a -k option to allow keeping last N files, whereas 8.3 passes the %r parameter to show the filename of the last file that must be kept. you should considering keeping those around for a bit regardless as an additional layer of disaster recovery resources. I try to avoid deleting them until a new base backup is made, because if you have the last backup and all the archived segments it gives you another potential way to rebuild the database in case of a large disaster damages both the primary and the secondary. You can never have too many ways to try and recover from such a situation. Agreed -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Dhaval Shah ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reserving space in a rec for future update
Erik Jones wrote: On Nov 14, 2007, at 10:44 AM, Mike Charnoky wrote: In this usage scenario, doesn't the new HOT (heap only tuples) feature of PG8.3 help, in terms of the DB requiring less VACUUM maintenance? I am similarly performing a huge number of inserts, followed by a huge number of updates to fill in a few null fields. The data is indexed by insert time. My problem is, selects using that index degrade over time as updates are performed, presumably because data is no longer ordered sequentially across pages after updates are performed. I was hoping that HOT would help here and am actually installing PG8.3 now in order to perform some testing... Some, what HOT does is keeps index rows from being updated when updates are made to column values that aren't indexed. The same insert/delete still happens in the table data. But another thing HOT does is make it possible to vacuum the *page* that the update is taking place on. So if there are dead tuples that nobody needs, they can be removed and the new tuple can be placed there. -- Alvaro Herrerahttp://www.advogato.org/person/alvherre Use it up, wear it out, make it do, or do without ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PLpgsql debugger question
Tony Caduto [EMAIL PROTECTED] writes: As I understand it the debugger functions are included by default in 8.3, That's incorrect. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PLpgsql debugger question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 12:46:07 -0500 brian [EMAIL PROTECTED] wrote: Tony Caduto wrote: Back in Sept the debugger was advertised as a feature of 8.3, so if it's not included how is it a feature? Advocacy of PostgreSQL includes more than just the core code. It also includes the promotion of all the very cool projects surrounding PostgreSQL. So the debugger is a feature of 8.3. It just isn't included in core. Is it going to be included as a contrib module or something else? It is a pgfoundry project which is part of PostgreSQL. A quick co of /trunk shows that it is not in contrib. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHOzgVATb/zqfZUUQRAh1TAKCeSXRXDRk8yjZ8Agy9a0efLqtdIgCfUCAB gvqXEgrOwkmKlyJGid46fOU= =F/OB -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PLpgsql debugger question
Tony Caduto wrote: Back in Sept the debugger was advertised as a feature of 8.3, so if it's not included how is it a feature? Is it going to be included as a contrib module or something else? I am talking about the server side stuff not the EDB GUI client. I don't know what you're referring to when you say it was advertised as a feature but it's not a part of the PG release. You can get it here: http://pgfoundry.org/projects/edb-debugger/ brian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pg_dump problem
Hi I try to use pg_dump to dump my database. pg_dump smrs and it gives me an error pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found What causes this problem? Thanks sharmila Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [GENERAL] pg_dump problem
On Wed, 2007-11-14 at 10:32 -0800, SHARMILA JOTHIRAJAH wrote: Hi I try to use pg_dump to dump my database. pg_dump smrs and it gives me an error pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found check out the --oids option in the manuals (man pg_dump)... could help ?! and try this: pg_dump --oids smrs Cheers jmf What causes this problem? Thanks sharmila __ Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PLpgsql debugger question
Joshua D. Drake wrote: So the debugger is a feature of 8.3. It just isn't included in core. Is it going to be included as a contrib module or something else? It is a pgfoundry project which is part of PostgreSQL. A quick co of /trunk shows that it is not in contrib. Which is probably an error IMHO. If anything makes sense as part of /contrib it's a procedural-language debugger module. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PLpgsql debugger question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 12:49:37 -0600 Tony Caduto [EMAIL PROTECTED] wrote: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375 Now you know what I am talking about :-) I see nothing incorrect in that article. Sincerely, Joshua D. Drake Later, Tony Caduto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHO0UkATb/zqfZUUQRAtJdAJ9F21oN6o793BmyjVfxewbogFUSSQCeO85X J43wWzv1c++1b8pUpSxK6iQ= =7HEG -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PLpgsql debugger question
Joshua D. Drake wrote: Which is probably an error IMHO. If anything makes sense as part of /contrib it's a procedural-language debugger module. Take it up with those who didn't submit it for inclusion :) Fair enough. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PLpgsql debugger question
brian wrote: I don't know what you're referring to when you say it was advertised as a feature but it's not a part of the PG release. You can get it here: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375 From the article: After nine months of work, the new features in 8.3 will be available at www.postgreSQL.org http://www.postgreSQL.org. They will include: A finished PL/pgSQL debugger, a tool for editing PostgreSQL's version of the standard SQL data access language in database applications. Full text search made more accessible by becoming a feature included in the system code instead of being an add-on option. Clustering code from Skype for load balancing and spreading queries to a large database across several PostgreSQL systems. Now you know what I am talking about :-) Later, Tony Caduto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PLpgsql debugger question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 18:40:26 + Richard Huxton [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: So the debugger is a feature of 8.3. It just isn't included in core. Is it going to be included as a contrib module or something else? It is a pgfoundry project which is part of PostgreSQL. A quick co of /trunk shows that it is not in contrib. Which is probably an error IMHO. If anything makes sense as part of /contrib it's a procedural-language debugger module. Take it up with those who didn't submit it for inclusion :) Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHO0KuATb/zqfZUUQRAooIAKCs9fhSFZ1BVtfwtBzgpImMafUs1gCeKbI9 r233WBvPi6UHsxcONQw4nEY= =1Ek/ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PLpgsql debugger question
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 12:49:37 -0600 Tony Caduto [EMAIL PROTECTED] wrote: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375 Now you know what I am talking about :-) I see nothing incorrect in that article. So you're saying the finished plpgsql debugger will be available from www.postgresql.org ? After nine months of work, the new features in 8.3 will be available at www.postgreSQL.org. They will include: * A finished PL/pgSQL debugger -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PLpgsql debugger question
Richard Huxton [EMAIL PROTECTED] writes: Joshua D. Drake wrote: It is a pgfoundry project which is part of PostgreSQL. A quick co of /trunk shows that it is not in contrib. Which is probably an error IMHO. If anything makes sense as part of /contrib it's a procedural-language debugger module. At some point it might get integrated, but right now it seems to need its own release schedule. We put the core hooks in for 8.2 but the thing didn't actually get published for many months after that. IIRC there are also some questions about what dependencies the GUI part of it has got ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PLpgsql debugger question
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 12:49:37 -0600 Tony Caduto [EMAIL PROTECTED] wrote: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375 I see nothing incorrect in that article. Sincerely, Joshua D. Drake Who said anything was incorrect? It's just a bit misleading (the Info Week Article). It's just after reading that MANY readers would think that if they install 8.3, the debugger hooks/whatever would be ready out of the box. Whoever is doing the release notes may want to have something in there about the debugger and the fact that it's not included and has to be manually compiled and all that. In the current release notes for 8.3 it makes NO mention of the debugger. Later, Tony Caduto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump problem
Hi, Thanks...But I still get the same error pg_dump --oids smrs pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found sharmila - Original Message From: Joao Miguel Ferreira [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 1:41:56 PM Subject: Re: [GENERAL] pg_dump problem On Wed, 2007-11-14 at 10:32 -0800, SHARMILA JOTHIRAJAH wrote: Hi I try to use pg_dump to dump my database. pg_dump smrs and it gives me an error pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found check out the --oids option in the manuals (man pg_dump)... could help ?! and try this: pg_dump --oids smrs Cheers jmf What causes this problem? Thanks sharmila __ Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
Re: [GENERAL] pg_dump problem
SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes: pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found What causes this problem? Corrupt system tables, looks like :-( What PG version is this? I would suggest checking to see if either of those OIDs appears in either the objid or refobjid columns of pg_depend. If not, the most likely theory is that this pg_rewrite entry somehow didn't get deleted when its parent table was dropped. You could just delete it manually, eg, delete from pg_rewrite where oid = 670741; If you do find other traces of the table it might be better to try to resurrect the table (actually it's most likely a view not a table). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PLpgsql debugger question
Richard Huxton wrote: So you're saying the finished plpgsql debugger will be available from www.postgresql.org ? After nine months of work, the new features in 8.3 will be available at www.postgreSQL.org. They will include: * A finished PL/pgSQL debugger There is no mention of anything debugger related in the 8.3 beta release notes either. Kind of seems like its not really a feature to me, but what do I know :-) The article is very misleading with regards to the debugger. Later, Tony Caduto ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PLpgsql debugger question
--- Original Message --- From: Richard Huxton [EMAIL PROTECTED] To: Joshua D. Drake [EMAIL PROTECTED] Sent: 14/11/07, 19:01:04 Subject: Re: [GENERAL] PLpgsql debugger question Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 12:49:37 -0600 Tony Caduto [EMAIL PROTECTED] wrote: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375 Now you know what I am talking about :-) I see nothing incorrect in that article. So you're saying the finished plpgsql debugger will be available from www.postgresql.org ? It all is, so that is correct. /D ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] psql Segmentation fault
Since we converted to PG8, we've been experiencing segfaults when the psql client exits. It doesn't have any real effect on things... or it hasn't until now. RHEL4 i686 - PostgresQL 8.2.4 (non-redhat) -bash-3.00$ psql -n Welcome to psql 8.2.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# \q Segmentation fault That's what happens every time. No core file is produced. I've done some searches and discovered that this might be related to readline. But that doesn't seem right since it crashes when I run psql -n (no readline support). I've tried removing .psql_history files and even changing perms to prevent writes. Nothing seems to work. Now it's affecting pg_dumpall -g, which we use to backup all user accounts and roles. It seems to seqfault before it finishes writing STDOUT, which means some users/roles aren't being dumped. Anyone else run into this problem? What is the solution? Thanks, Rob ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Windows x64 Port
Hello, Magnus Hagander wrote: Willem Buitendyk wrote: Is there any plan to port Postgresql to windows x64? I can currently run Postgresql as 32 bit inside Vista 64 - would I see better performance if Postgresql was running under 64 bit. My biggest concern is memory - at 32 bit is not Postgresql limited to 4GB in windows? It's something we hope will be worked on for 8.4, but there are no firm plans. It's limited to 2Gb, actually, but *per process*. Since each backend is it's own process, you can use way more than 2Gb RAM on a 64-bit system. You can't use it for shared memory, but you can use it for local backend memory (work_mem). But you'll need a lot of backends to do it, and you will see other pieces of performance get worse with loads of backend. Oh, and your RAM will still be used for disk cache, since that's managed by the kernel. I'm wondering - what kind of problems do you expect with such port? By the way, are there any benchmark results to compare 32 and 64 bit version on Linux? Thanks, Andrei. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_dump problem
SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes: looks like the OIDs are there Yeah, that makes it look more like the pg_class row went missing than that there was an intentional drop of the view. Does VACUUM VERBOSE pg_class report anything interesting? It's possible also that reindexing pg_class would fix it. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] stripping HTML, SQL injections ...
Quick question, are there any native functions in PostGreSQL 8.1.4 that will strip HTML tags, escape chars, etc? thanx:) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PLpgsql debugger question
Tony Caduto wrote: brian wrote: I don't know what you're referring to when you say it was advertised as a feature but it's not a part of the PG release. You can get it here: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375 From the article: After nine months of work, the new features in 8.3 will be available at www.postgreSQL.org http://www.postgreSQL.org. They will include: A finished PL/pgSQL debugger, a tool for editing PostgreSQL's version of the standard SQL data access language in database applications. Full text search made more accessible by becoming a feature included in the system code instead of being an add-on option. Clustering code from Skype for load balancing and spreading queries to a large database across several PostgreSQL systems. Now you know what I am talking about :-) I should have pointed out that I wasn't questioning whether or not there was an article that stated this. I was simply pointing out that the debugger was *not* included in the release (did you look at the release notes?) and also where you can find it. That said, take this up with Information Week. brian ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Windows x64 Port
Thanks Magnus. Looking forward to 8.4, but I gather that will be some time coming. Willem Magnus Hagander wrote: Willem Buitendyk wrote: Is there any plan to port Postgresql to windows x64? I can currently run Postgresql as 32 bit inside Vista 64 - would I see better performance if Postgresql was running under 64 bit. My biggest concern is memory - at 32 bit is not Postgresql limited to 4GB in windows? It's something we hope will be worked on for 8.4, but there are no firm plans. It's limited to 2Gb, actually, but *per process*. Since each backend is it's own process, you can use way more than 2Gb RAM on a 64-bit system. You can't use it for shared memory, but you can use it for local backend memory (work_mem). But you'll need a lot of backends to do it, and you will see other pieces of performance get worse with loads of backend. Oh, and your RAM will still be used for disk cache, since that's managed by the kernel. //Magnus __ NOD32 2658 (20071114) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PLpgsql debugger question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 15:44:39 -0500 brian [EMAIL PROTECTED] wrote: I should have pointed out that I wasn't questioning whether or not there was an article that stated this. I was simply pointing out that the debugger was *not* included in the release (did you look at the release notes?) and also where you can find it. That said, take this up with Information Week. Please don't. Bruce and I both have talked with them about that article and although it isn't exactly accurate, it is close enough and is a positive endorsement of our project. Sincerely, Joshua D. Drake brian ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHO19hATb/zqfZUUQRAsHIAJ9KpmbaZZFTkOsLmQhVziWWgbOWIQCfdZpN 2AhlI01p6j3Pg9SfiU3r57Q= =Q5ME -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] stripping HTML, SQL injections ...
On Nov 14, 2007 2:40 PM, madhtr [EMAIL PROTECTED] wrote: Quick question, are there any native functions in PostGreSQL 8.1.4 that will strip HTML tags, escape chars, etc? I can't think of a lot of native functions, but it's sure easy enough to roll your own with things like the regex functionality built in. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PITR and warm standby setup questions
Dhaval Shah wrote: I am on 8.2 production and it will be difficult to upgrade to 8.3. Is it possible to backport the %r fix from 8.3 to 8.2? You need to troll through the CVS archives to find that patch and try to apply it to 8.2. This feature will not be backpatched because we don't backpatch features to previous branches. --- Regards Dhaval On Nov 13, 2007 11:26 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote: On Mon, 12 Nov 2007, Mason Hale wrote: After the wal segment file is copied by the restore_command script, is it safe to delete it from my archive? While I believe you can toss them immediately, This is almost never possible. The last WAL file that must be kept should be sufficient to allow recovery to restart from the last restartpoint. So a variable number of WAL files needs to be kept, not 1, not 2 and certainly never 0. pg_standby with 8.2 provides a -k option to allow keeping last N files, whereas 8.3 passes the %r parameter to show the filename of the last file that must be kept. you should considering keeping those around for a bit regardless as an additional layer of disaster recovery resources. I try to avoid deleting them until a new base backup is made, because if you have the last backup and all the archived segments it gives you another potential way to rebuild the database in case of a large disaster damages both the primary and the secondary. You can never have too many ways to try and recover from such a situation. Agreed -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Dhaval Shah ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Windows x64 Port
Is there any plan to port Postgresql to windows x64? I can currently run Postgresql as 32 bit inside Vista 64 - would I see better performance if Postgresql was running under 64 bit. My biggest concern is memory - at 32 bit is not Postgresql limited to 4GB in windows? Thanks, Willem ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PLpgsql debugger question
Tony Caduto [EMAIL PROTECTED] writes: Who said anything was incorrect? It's just a bit misleading (the Info Week Article). Three out of the four features mentioned in your quote are not part of core Postgres, so the author was obviously taking a very wide view of what Postgres is. Or was just misinformed. Whoever is doing the release notes may want to have something in there about the debugger and the fact that it's not included and has to be manually compiled and all that. Yup, I can see it now: para Never believe anything you read in Information Week. /para Seriously, we can't be expected to worry about misstatements made by others. If we had to add a paragraph to the release notes for every incorrect thing that's ever been said about Postgres, they'd be completely unreadable rather than just mostly. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PITR and warm standby setup questions
No problem. One more question, is there a way to find out, without going through a test install, and from release notes etc. for 8.3 if the database needs migration from 8.2 to 8.3 or not. Regards Dhaval On Nov 14, 2007 10:44 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Dhaval Shah wrote: I am on 8.2 production and it will be difficult to upgrade to 8.3. Is it possible to backport the %r fix from 8.3 to 8.2? You need to troll through the CVS archives to find that patch and try to apply it to 8.2. This feature will not be backpatched because we don't backpatch features to previous branches. --- Regards Dhaval On Nov 13, 2007 11:26 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote: On Mon, 12 Nov 2007, Mason Hale wrote: After the wal segment file is copied by the restore_command script, is it safe to delete it from my archive? While I believe you can toss them immediately, This is almost never possible. The last WAL file that must be kept should be sufficient to allow recovery to restart from the last restartpoint. So a variable number of WAL files needs to be kept, not 1, not 2 and certainly never 0. pg_standby with 8.2 provides a -k option to allow keeping last N files, whereas 8.3 passes the %r parameter to show the filename of the last file that must be kept. you should considering keeping those around for a bit regardless as an additional layer of disaster recovery resources. I try to avoid deleting them until a new base backup is made, because if you have the last backup and all the archived segments it gives you another potential way to rebuild the database in case of a large disaster damages both the primary and the secondary. You can never have too many ways to try and recover from such a situation. Agreed -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Dhaval Shah ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Dhaval Shah ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Serial IDs
Hi Is there any method of clearing the serial numbering so that ID references can start afresh without rebuilding the database. Of I use postgresql as part of my application I would like to use pgdump to ensure that I have the latest version and starting the serial numbering at #1 would be a good thing. Bob ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PITR and warm standby setup questions
Dhaval Shah wrote: No problem. One more question, is there a way to find out, without going through a test install, and from release notes etc. for 8.3 if the database needs migration from 8.2 to 8.3 or not. What is migration? Application changes? The release notes pretty much tell you everything you need. --- Regards Dhaval On Nov 14, 2007 10:44 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Dhaval Shah wrote: I am on 8.2 production and it will be difficult to upgrade to 8.3. Is it possible to backport the %r fix from 8.3 to 8.2? You need to troll through the CVS archives to find that patch and try to apply it to 8.2. This feature will not be backpatched because we don't backpatch features to previous branches. --- Regards Dhaval On Nov 13, 2007 11:26 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote: On Mon, 12 Nov 2007, Mason Hale wrote: After the wal segment file is copied by the restore_command script, is it safe to delete it from my archive? While I believe you can toss them immediately, This is almost never possible. The last WAL file that must be kept should be sufficient to allow recovery to restart from the last restartpoint. So a variable number of WAL files needs to be kept, not 1, not 2 and certainly never 0. pg_standby with 8.2 provides a -k option to allow keeping last N files, whereas 8.3 passes the %r parameter to show the filename of the last file that must be kept. you should considering keeping those around for a bit regardless as an additional layer of disaster recovery resources. I try to avoid deleting them until a new base backup is made, because if you have the last backup and all the archived segments it gives you another potential way to rebuild the database in case of a large disaster damages both the primary and the secondary. You can never have too many ways to try and recover from such a situation. Agreed -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Dhaval Shah ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Dhaval Shah -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] stripping HTML, SQL injections ...
On Nov 14, 2007, at 4:23 PM, Scott Marlowe wrote: On Nov 14, 2007 2:40 PM, madhtr [EMAIL PROTECTED] wrote: Quick question, are there any native functions in PostGreSQL 8.1.4 that will strip HTML tags, escape chars, etc? I can't think of a lot of native functions, but it's sure easy enough to roll your own with things like the regex functionality built in. Please don't do that- there are corner cases where a naive regex can fail, leaving the programmer thinking he is covered when he is not. The variety of web languages include filtering modules (HTML::Scrubber)- in the case of Perl or PHP, it can even be run server-side. Furthermore, one shouldn't use an API which allows for SQL injections. Cheers, M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Serial IDs
On Nov 14, 2007 5:17 PM, Bob Pawley [EMAIL PROTECTED] wrote:
Hi
Is there any method of clearing the serial numbering so that ID references
can start afresh without rebuilding the database.
Of I use postgresql as part of my application I would like to use pgdump to
ensure that I have the latest version and starting the serial numbering at
#1 would be a good thing.
Look up setval(), currval() and nextval(), the sequence manipulation functions.
setval('seqname',1,true) will reset seqname back like new.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] PITR and warm standby setup questions
On Nov 14, 2007 5:19 PM, Dhaval Shah [EMAIL PROTECTED] wrote: No problem. One more question, is there a way to find out, without going through a test install, and from release notes etc. for 8.3 if the database needs migration from 8.2 to 8.3 or not. Well, you HAVE to do a dump from one to the other, because major versions can't read each other's data stores. Whether or not the upgrade will break your app is a question only you can answer though. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump problem
Hi Thanks PG version is 8.2.3 I queried the pg_depend using this query select * from pg_depend where objid in (670739,670741) or refobjid in (670739,670741) looks like the OIDs are there classid objid objsubid refclassid refobjid refobjsubid deptype -- --- - --- -- -- 124767074001259 670739 0 i 125967073902615 2200 0 n 261867074101259 670739 0 i 261867074101259 670645 3 n 261867074101259 670648 3 n 261867074101259 670739 0 n so what else can cause tis problem? sharmila - Original Message From: Tom Lane [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 2:21:03 PM Subject: Re: [GENERAL] pg_dump problem SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes: pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found What causes this problem? Corrupt system tables, looks like :-( What PG version is this? I would suggest checking to see if either of those OIDs appears in either the objid or refobjid columns of pg_depend. If not, the most likely theory is that this pg_rewrite entry somehow didn't get deleted when its parent table was dropped. You could just delete it manually, eg, delete from pg_rewrite where oid = 670741; If you do find other traces of the table it might be better to try to resurrect the table (actually it's most likely a view not a table). regards, tom lane Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
Re: [GENERAL] psql Segmentation fault
Robert Landrum [EMAIL PROTECTED] writes: Since we converted to PG8, we've been experiencing segfaults when the psql client exits. Hmm. We have heard that reported on OS X because of a bug in Apple's version of libedit, but not on any flavor of Linux. Your tests seem to eliminate libreadline as the cause anyway. Might be a corrupt copy of libpq --- have you tried reinstalling that? That's what happens every time. No core file is produced. That's normal on Linuxen, because ulimit -c 0 is usually the default setting. Please try ulimit -c unlimited, and then if you get a core, send a backtrace. The last few lines of an strace watching the psql run might be useful as well. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] stripping HTML, SQL injections ...
Scott- In JavaScript http://www.java2s.com/Tutorial/JavaScript/0520__Regular-Expressions/StripHTM L.htm M-- - Original Message - From: Scott Marlowe [EMAIL PROTECTED] To: A.M. [EMAIL PROTECTED] Cc: pgsql-general pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 6:16 PM Subject: Re: [GENERAL] stripping HTML, SQL injections ... On Nov 14, 2007 4:51 PM, A.M. [EMAIL PROTECTED] wrote: On Nov 14, 2007, at 4:23 PM, Scott Marlowe wrote: On Nov 14, 2007 2:40 PM, madhtr [EMAIL PROTECTED] wrote: Quick question, are there any native functions in PostGreSQL 8.1.4 that will strip HTML tags, escape chars, etc? I can't think of a lot of native functions, but it's sure easy enough to roll your own with things like the regex functionality built in. Please don't do that- there are corner cases where a naive regex can fail, leaving the programmer thinking he is covered when he is not. The variety of web languages include filtering modules (HTML::Scrubber)- in the case of Perl or PHP, it can even be run server-side. And given that pl/PHP can run that inside the database, there's a reason you can't do it there? Furthermore, one shouldn't use an API which allows for SQL injections. Oh heck, I hadn't even noticed he was asking about escaping things. I guess it really matters what he means by escaping them. If he's talking url encoding decoding, that's something you could do safely in the db (again, with something like pl/PHP or pl/perl) but SQL escaping should be done before the db ever sees the data. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] psql Segmentation fault
Tom Lane wrote: Robert Landrum [EMAIL PROTECTED] writes: Since we converted to PG8, we've been experiencing segfaults when the psql client exits. Hmm. We have heard that reported on OS X because of a bug in Apple's version of libedit, but not on any flavor of Linux. Your tests seem to eliminate libreadline as the cause anyway. Might be a corrupt copy of libpq --- have you tried reinstalling that? Actually, our build host contained some old readline libs, which ended up being statically linked into one of the libs. The fix was to remove all the dependencies from our build host, reinstall those dependencies, and then build postgres anew. Thanks for your suggestions... Rob ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] stripping HTML, SQL injections ...
Martin, 2000/11/15, Martin Gainty [EMAIL PROTECTED]: Scott- In JavaScript http://www.java2s.com/Tutorial/JavaScript/0520__Regular-Expressions/StripHTM L.htm I don't remember what the consensus was back in 2000 (your mail's timestamp), but in 2007 it's Not A Good Idea to rely on client-side validation for security-related operations ;). Regards Ian Barwick -- http://sql-info.de/index.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] stripping HTML, SQL injections ...
this is a very simple html tag strip routine I dont understand what security you had in mind .. so I take it you're not a fan of dojo or GWT? M-- - Original Message - From: Ian Barwick [EMAIL PROTECTED] Cc: Scott Marlowe [EMAIL PROTECTED]; pgsql-general pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 7:21 PM Subject: Re: [GENERAL] stripping HTML, SQL injections ... Martin, 2000/11/15, Martin Gainty [EMAIL PROTECTED]: Scott- In JavaScript http://www.java2s.com/Tutorial/JavaScript/0520__Regular-Expressions/StripHTM L.htm I don't remember what the consensus was back in 2000 (your mail's timestamp), but in 2007 it's Not A Good Idea to rely on client-side validation for security-related operations ;). Regards Ian Barwick -- http://sql-info.de/index.html ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Qeury a boolean column?(using postgresql EJB)
hello, i have a database in postgresql that have a column boolean, then when i create a query in ejb like this(SELECT e.letsaythisisboolean FROM sample e), now problem is that when i query the database in ejb, it will return all the false value in the column boolean, and the true value will not return... How can i make the true value appear in my query result? Any suggestion or help... thanks, dychalres -- View this message in context: http://www.nabble.com/Qeury-a-boolean-column-%28using-postgresql---EJB%29-tf4808294.html#a13757431 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PLpgsql debugger question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 19:01:04 + Richard Huxton [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 12:49:37 -0600 Tony Caduto [EMAIL PROTECTED] wrote: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375 Now you know what I am talking about :-) I see nothing incorrect in that article. So you're saying the finished plpgsql debugger will be available from www.postgresql.org ? http://www.postgresql.org/ftp/projects/pgFoundry/edb-debugger/ Joshua D. Drake After nine months of work, the new features in 8.3 will be available at www.postgreSQL.org. They will include: * A finished PL/pgSQL debugger - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHO0jmATb/zqfZUUQRAga0AJ9pu3u1ukHbT1KhQt2efOmh62VhVACeNnEY mCyvw/Mwg7lFOOA+cz+K+k8= =zPJA -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Variable LIMIT and OFFSET in SELECTs
Hi all. I'd need to implement a parametric windowed select over a table called atable. The idea is to have a one row table to maintain the LIMIT and the OFFSET for the selects. If I try this: create table limoff( l int, o int ); insert into limoff values ( 10,2 ); select a.* from atable a,limoff limit l offset o; I get ERROR: argument of OFFSET must not contain variables. (You get the error also on LIMIT if you put a constant as the offset). But I can do the following: create or replace function f_limoff_1( l int, o int ) returns setof atable as $$ select * from atable limit $1 offset $2 $$ language sql; create or replace function f_limoff() returns setof atable as $$ select * from f_limoff_1( (select l from limoff),(select i from limoff) ); $$ language sql; Of course, in my opinion at least, there's no real reason for the above syntax limitation, as the sematics is not. Wouldn't it be a nice enhacement to allow variable LIMIT and OFFSET in SELECTs? -- Reg me Please ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PITR and warm standby setup questions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 18:35:00 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: Dhaval Shah wrote: No problem. One more question, is there a way to find out, without going through a test install, and from release notes etc. for 8.3 if the database needs migration from 8.2 to 8.3 or not. What is migration? Application changes? The release notes pretty much tell you everything you need. http://www.postgresql.org/docs/8.3/static/release-8-3.html Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHO4kxATb/zqfZUUQRAtRaAJ4t99bQ9e+iPqJ4WbYwY0gtVDeSGgCgmPtO sW/YuUUicDUTDZy+Hzn4ug8= =t0dY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] stripping HTML, SQL injections ...
Martin Gainty escribió: this is a very simple html tag strip routine I dont understand what security you had in mind .. so I take it you're not a fan of dojo or GWT? Let's say the user disables javascript on the browser? -- Alvaro Herrerahttp://www.advogato.org/person/alvherre Aprende a avergonzarte más ante ti que ante los demás (Demócrito) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_dump problem
You are right. There are no rows in pg_class with oids 670739 and 670741. Is that the problem? How do I fix that. Will deleting those rows from pg_depend fix this problem? Also I have another question. Should the pg tables (like pg_class etc) generally be vacuumed regularly? Thanks sharmila - Original Message From: Tom Lane [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 3:33:20 PM Subject: Re: [GENERAL] pg_dump problem SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes: looks like the OIDs are there Yeah, that makes it look more like the pg_class row went missing than that there was an intentional drop of the view. Does VACUUM VERBOSE pg_class report anything interesting? It's possible also that reindexing pg_class would fix it. regards, tom lane Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
Re: [GENERAL] PLpgsql debugger question
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 15:44:39 -0500 brian [EMAIL PROTECTED] wrote: I should have pointed out that I wasn't questioning whether or not there was an article that stated this. I was simply pointing out that the debugger was *not* included in the release (did you look at the release notes?) and also where you can find it. That said, take this up with Information Week. Please don't. Bruce and I both have talked with them about that article and although it isn't exactly accurate, it is close enough and is a positive endorsement of our project. Oh, yea, that article. There was some confusion by the author over 3rd party stuff vs main project stuff. It was a stuff-is-coming article so we couldn't get it 100% accurate after the fact. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Bulk Load Ignore/Skip Feature
On Tue, Nov 13, 2007 at 08:50:36PM -0800, Willem Buitendyk wrote: Will Postgresql ever implement an ignore on error feature when bulk loading data? Currently it is my understanding that any record that violates a unique constraint will cause the copy from command to halt execution instead of skipping over the violation and logging it - as is done in Oracle and DB2. Are there alternative ways of dealing with this scenario that won't consume as much time? Appreciate any help - would love to migrate away from Oracle. You might try pgloader. :) http://pgfoundry.org/projects/pgloader/ Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] stripping HTML, SQL injections ...
On Nov 14, 2007 4:51 PM, A.M. [EMAIL PROTECTED] wrote: On Nov 14, 2007, at 4:23 PM, Scott Marlowe wrote: On Nov 14, 2007 2:40 PM, madhtr [EMAIL PROTECTED] wrote: Quick question, are there any native functions in PostGreSQL 8.1.4 that will strip HTML tags, escape chars, etc? I can't think of a lot of native functions, but it's sure easy enough to roll your own with things like the regex functionality built in. Please don't do that- there are corner cases where a naive regex can fail, leaving the programmer thinking he is covered when he is not. The variety of web languages include filtering modules (HTML::Scrubber)- in the case of Perl or PHP, it can even be run server-side. And given that pl/PHP can run that inside the database, there's a reason you can't do it there? Furthermore, one shouldn't use an API which allows for SQL injections. Oh heck, I hadn't even noticed he was asking about escaping things. I guess it really matters what he means by escaping them. If he's talking url encoding decoding, that's something you could do safely in the db (again, with something like pl/PHP or pl/perl) but SQL escaping should be done before the db ever sees the data. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Enforcing Join condition
Is there a way to force join conditions in queries i.e. When a join is
made to a table on a particular field, another column should also be
checked?
CREATE TABLE test (info_type varchar(3), info_reference integer);
(depending on info_type, info_reference will contain key values from
different tables)
INSERT INTO test (info_type, info_reference) values ('abc','111'); ---
111 from tableA
INSERT INTO test (info_type, info_reference) values ('def','101');
--- 101 from tableB
INSERT INTO test (info_type, info_reference) values ('abc','119'); ---
119 from tableA
INSERT INTO test (info_type, info_reference) values ('def','103');
--- 103 from tableB
INSERT INTO test (info_type, info_reference) values ('def','104');
--- 104 from tableB
INSERT INTO test (info_type, info_reference) values ('def','105');
--- 105 from tableB
INSERT INTO test (info_type, info_reference) values ('def','111');
--- 111 from tableB
Now when joining tableA or tableB with test, joining only
info_reference will be wrong, we should also mention the info_type
value.
1. Is this an appropriate design for this requirement?
2. Is there a way to enforce the dual condition checking on all
queries. If a join is made to info_reference, info_type should also be
specified?
Thanks.
Ma Sivakumar
--
மா சிவகுமார்
எல்லோரும் எல்லாமும் பெற வேண்டும்
http://masivakumar.blogspot.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
