Re: [GENERAL] Display of text fields
On Fri, 10 Sep 2004, Ennio-Sr wrote: I slightly modified your queries and the result gets nearer my goals, but ... Here is what I tried: SELECT DISTINCT /* despite the DISTINCT, it shows twice each matching record: once with the memo fieldd and then without it!. Leaving out the DISTINCT, each record is shown many times (may be as many as the number of numbered fields, according to the CASE condition */ t0.n_prog, t0.autore, ., ., t0.scheda_ltr, CASE WHEN t0.scheda_ltr = 'T' AND t0.n_prog=t1.n_prog THEN t1.note ELSE 'n/a' END AS note FROM bib_lt t0, bidbt t1 where t0.n_prog0 ; As an explanation of the duplicate rows: FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join is going to give alot of rows with basically every combination (1st row of t0 with 1st row of t1, 1st row of t0 with 2nd row of t1, etc...). Some of these rows will have t0.n_prog=t1.n_prog but most will not. You then project the select list for each of those rows. The ones with 'T' are going to get (assuming no duplicates in t0.n_prog or t1.n_prog) one row with the note as the final field, and a bunch more with 'n/a' as it. When you DISTINCT those, it sees that the note and 'n/a' are distinct (well, usually) and outputs both. If you're not using any other fields from t1, I would wonder if something like: SELECT t0.n_prog, ..., t0.scheda_ltr, coalesce(t1.note, 'n/a') as note FROM bib_lt t0 left outer join t1 on (t0.scheda_ltr='T' and t0.n_prog=t1.n_prog) where t0._nprog0; would be closer to what you want from the query. The join should give output with either t0 extended by NULLs or t0 joined by t1 dependant on whether t0.scheda_ltr='T' and if it finds a matching row in t1. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Canceling Query due to user request
postgres 7.4.2 on a RedHat Enterprise Server using libpq on SCO Open Server Seems that if a command takes too long I get ERROR:Canceling query due to user request. I have ulimit=unlimited postgresql.conf has statement_timeout = 0 Any ideas on what could be causing this? Thanks, Bart ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] referential integrity preventing simultaneous insert
On Thu, Sep 09, 2004 at 12:53:57PM -0500, Edwin Grubbs wrote: I have experienced problems with postgres hanging when two inserts reference the same foreign key. It appears that the second insert is waiting for the first insert to release a lock. You can also create a deadlock situation: transaction 1: INSERT INTO car VALUES (5, 1); transaction 2: INSERT INTO car VALUES (6, 2); transaction 1: INSERT INTO car VALUES (7, 2); transaction 2: INSERT INTO car VALUES (8, 1); ERROR: deadlock detected DETAIL: Process 16919 waits for ShareLock on transaction 14686; blocked by process 16920. Process 16920 waits for ShareLock on transaction 14687; blocked by process 16919. CONTEXT: SQL query SELECT 1 FROM ONLY public.model x WHERE id = $1 FOR UPDATE OF x There was a thread about this a couple of years ago: http://archives.postgresql.org/pgsql-hackers/2002-03/msg01156.php Apparently the exclusive lock is necessary to prevent other transactions from modifying the foreign key before this transaction commits. As one of the followups to the above thread mentioned, it would be nice to have a FOR PREVENT UPDATE lock that could be shared, but we don't, so PostgreSQL uses the exclusive FOR UPDATE. If you set up your foreign key references as DEFERRABLE, you can avoid the blocking and potential deadlock by issuing SET CONSTRAINTS ALL DEFERRED at the beginning of your transactions. But then you won't detect foreign key violations until the COMMIT, which might be a problem for your application. Is this fixed in postgres 8? If it still requires work, I may be able to help fund it to get it completed sooner. PostgreSQL 8.0.0beta2 still behaves this way. Maybe one of the developers can comment on the possibility of a shared FOR PREVENT UPDATE lock. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Display of text fields
* Stephan Szabo [EMAIL PROTECTED] [100904, 07:10]: On Fri, 10 Sep 2004, Ennio-Sr wrote: I slightly modified your queries and the result gets nearer my goals, but ... Here is what I tried: [ ... ] As an explanation of the duplicate rows: FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join [ ... ] If you're not using any other fields from t1, I would wonder if something like: SELECT t0.n_prog, ..., t0.scheda_ltr, coalesce(t1.note, 'n/a') as note FROM bib_lt t0 left outer join t1 on (t0.scheda_ltr='T' and t0.n_prog=t1.n_prog) where t0._nprog0; would be closer to what you want from the query. The join should give output with either t0 extended by NULLs or t0 joined by t1 dependant on whether t0.scheda_ltr='T' and if it finds a matching row in t1. Thank you Stephen, for your contribution: I'll study it in due course ... as I'm interested to learn as much as possible ... However, in the meantime, I think I found the solution. What helped me was the construction of these two testing tables: Table foo Column | Type| Modifiers +---+--- a | integer | b | character varying | c | character varying | has_d | character(1) | # which I filled with: a | b | c| has_d ---+--++--- 1 | one | number | Y 2 | two | number | Y 3 | tree | name | Y 4 | blue | color | N 5 | john | person | N (5 rows) # and: Table foo_d Column | Type | Modifiers +-+--- a | integer | d | text| # bearing my 'would-be' memo field: a |d ---+-- 1 | is the first natural 2 | follows 1 in the seq of natural 3 | there are various qualities of - (3 rows) # Then I launched an 'nth' variant of my query: SELECT DISTINCT --- t0.a, t0.b, t0.c, t0.has_d, -- t1.d, -- t0.has_d, -- ## ok, mostr prima i due 'N' e poi due volte quelli Y -- ## se tolgo 't0.has_d', cambia ordine ma sempre 8 -- ## sono CASE WHEN t0.has_d = 'Y' AND t0.a=t1.a THEN t0.a || ' - ' || t0.b || ' - ' || t0.c || ' - ' || t1.d ELSE CASE WHEN t0.has_d = 'N' THEN t0.a || ' / ' || t0.b || ' / ' || t0.c -- || ' / ' || t0.has_d END END AS The result is: FROM foo t0, foo_d t1; # and finally: The result is: 1 - one - number - is the first natural 2 - two - number - follows 1 in the seq of natural 3 - tree - name - there are various qualities of - 4 / blue / color 5 / john / person (6 rows) # which is exaclty what I was looking for :-) # Then I re-read #9.12.1 CASE of pg 7.4 Documentation with a slight # different syntax that I'm going to try ... - As I told Richard in a previous message, I was sure the solution ought to be there: it's a question of being patient and having time to 'experiment' ;-) Thanks you all for the assistance. Best regards, Ennio. -- [Perche' usare Win$ozz (dico io) se ...anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace! (diceva Henry Miller) ] (°|°) [Why to use Win$ozz (I say) if ... even a fool can do that. )=( Do something you aren't good at! (used to say Henry Miller) ] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Display of text fields
On Fri, 10 Sep 2004, Ennio-Sr wrote: * Stephan Szabo [EMAIL PROTECTED] [100904, 07:10]: On Fri, 10 Sep 2004, Ennio-Sr wrote: I slightly modified your queries and the result gets nearer my goals, but ... Here is what I tried: [ ... ] As an explanation of the duplicate rows: FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join [ ... ] If you're not using any other fields from t1, I would wonder if something like: SELECT t0.n_prog, ..., t0.scheda_ltr, coalesce(t1.note, 'n/a') as note FROM bib_lt t0 left outer join t1 on (t0.scheda_ltr='T' and t0.n_prog=t1.n_prog) where t0._nprog0; would be closer to what you want from the query. The join should give output with either t0 extended by NULLs or t0 joined by t1 dependant on whether t0.scheda_ltr='T' and if it finds a matching row in t1. Thank you Stephen, for your contribution: I'll study it in due course ... as I'm interested to learn as much as possible ... However, in the meantime, I think I found the solution. What helped me was the construction of these two testing tables: Table foo Column | Type| Modifiers +---+--- a | integer | b | character varying | c | character varying | has_d | character(1) | # which I filled with: a | b | c| has_d ---+--++--- 1 | one | number | Y 2 | two | number | Y 3 | tree | name | Y 4 | blue | color | N 5 | john | person | N (5 rows) # and: Table foo_d Column | Type | Modifiers +-+--- a | integer | d | text| # bearing my 'would-be' memo field: a |d ---+-- 1 | is the first natural 2 | follows 1 in the seq of natural 3 | there are various qualities of - (3 rows) # Then I launched an 'nth' variant of my query: SELECT DISTINCT --- t0.a, t0.b, t0.c, t0.has_d, -- t1.d, -- t0.has_d, -- ## ok, mostr prima i due 'N' e poi due volte quelli Y -- ## se tolgo 't0.has_d', cambia ordine ma sempre 8 -- ## sono CASE WHEN t0.has_d = 'Y' AND t0.a=t1.a THEN t0.a || ' - ' || t0.b || ' - ' || t0.c || ' - ' || t1.d ELSE CASE WHEN t0.has_d = 'N' THEN t0.a || ' / ' || t0.b || ' / ' || t0.c -- || ' / ' || t0.has_d END END AS The result is: FROM foo t0, foo_d t1; Note however, that this may very well perform poorly compared to other solutions because as foo and foo_d get large, you're going to be evaluating the case clause alot. In addition, this gives an extra NULL row AFAICS (see below where you get a blank row and the rowcount is 1 higher than the meaningful number of rows. The result is: 1 - one - number - is the first natural 2 - two - number - follows 1 in the seq of natural 3 - tree - name - there are various qualities of - 4 / blue / color 5 / john / person (6 rows) # which is exaclty what I was looking for :-) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Display of text fields
* Stephan Szabo [EMAIL PROTECTED] [100904, 09:05]: On Fri, 10 Sep 2004, Ennio-Sr wrote: * Stephan Szabo [EMAIL PROTECTED] [100904, 07:10]: On Fri, 10 Sep 2004, Ennio-Sr wrote: [ big cut ] Note however, that this may very well perform poorly compared to other solutions because as foo and foo_d get large, you're going to be evaluating the case clause alot. In addition, this gives an extra NULL row AFAICS (see below where you get a blank row and the rowcount is 1 higher than the meaningful number of rows. Stephan, I just tested my query on the main tables (bibl_lt and bidbt) and it seems to work reasonably quickly (my tables are not all that large: around 10.000 rows only!). But, if it is possible to get a better result, why not? So, when you say '..compared to other solutions..' are you thinking about 'COALESCE' (which I have not studied yet) or some other type of instruction, other than psql's? TIA, Ennio -- [Perche' usare Win$ozz (dico io) se ...anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace! (diceva Henry Miller) ] (°|°) [Why to use Win$ozz (I say) if ... even a fool can do that. )=( Do something you aren't good at! (used to say Henry Miller) ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Display of text fields
On Fri, 10 Sep 2004, Ennio-Sr wrote: * Stephan Szabo [EMAIL PROTECTED] [100904, 09:05]: On Fri, 10 Sep 2004, Ennio-Sr wrote: * Stephan Szabo [EMAIL PROTECTED] [100904, 07:10]: On Fri, 10 Sep 2004, Ennio-Sr wrote: [ big cut ] Note however, that this may very well perform poorly compared to other solutions because as foo and foo_d get large, you're going to be evaluating the case clause alot. In addition, this gives an extra NULL row AFAICS (see below where you get a blank row and the rowcount is 1 higher than the meaningful number of rows. Stephan, I just tested my query on the main tables (bibl_lt and bidbt) and it seems to work reasonably quickly (my tables are not all that large: around 10.000 rows only!). But, if it is possible to get a better result, why not? So, when you say '..compared to other solutions..' are you thinking about 'COALESCE' (which I have not studied yet) or some other type of instruction, other than psql's? Well, I'd expect that for large tables the outer join type solution would tend to be faster than joining every row to every other row and then using a unique step (probably after a sort) to basically remove the ones you don't want. If you try different solutions, you can use EXPLAIN ANALYZE to compare query plans. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Display of text fields
* Stephan Szabo [EMAIL PROTECTED] [100904, 10:39]: On Fri, 10 Sep 2004, Ennio-Sr wrote: * Stephan Szabo [EMAIL PROTECTED] [100904, 07:10]: On Fri, 10 Sep 2004, Ennio-Sr wrote: [ big cut ] Well, I'd expect that for large tables the outer join type solution would tend to be faster than joining every row to every other row and then using a unique step (probably after a sort) to basically remove the ones you don't want. If you try different solutions, you can use EXPLAIN ANALYZE to compare query plans. Please forget my previous message: I've just finished trying your 'COALESCE' solution (prior to studying it ;) ) and it seems to work greatly! - SELECT t0.a, t0.b, t0.c, COALESCE(t1.d, ' ') as note from foo t0 left OUTER JOIN foo_d t1 on (t0.has_d = 'Y' AND t0.a=t1.a); # the result is: a | b | c| note ---+--++-- 1 | one | number | is the first natural 2 | two | number | follows 1 in the seq of natural 3 | tree | name | there are various qualities of - 4 | blue | color | 5 | john | person | (5 rows) ^^^ - Perfect, I would say :-) Thank you again so much indeed, Stephan. Ennio. -- [Perche' usare Win$ozz (dico io) se ...anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace! (diceva Henry Miller) ] (°|°) [Why to use Win$ozz (I say) if ... even a fool can do that. )=( Do something you aren't good at! (used to say Henry Miller) ] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] SMgrRelation hashtable corrupted
On 8 beta1 I get 'SMgrRelation hashtable corrupted' *sometimes* when executing the following, but only when there are other clients connecting to the same database. ALTER TABLE declines ALTER COLUMN comp_name TYPE varchar(128), ALTER COLUMN f_name1 TYPE varchar(48), ALTER COLUMN l_name1 TYPE varchar(48), ALTER COLUMN country TYPE varchar(48); Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] unsubscribe
Title: unsubscribe unsubscribe This message, including any attachments, contains confidential information intended for a specific individual and purpose and is protected by law. If you are not the intended recipient, please contact sender immediately by reply e-mail and destroy all copies. You are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The sender accepts no liability for any damage caused by any virus transmitted by this email. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission.
[GENERAL] Speeding up LIKE with placeholders?
Is there any good way to speed up SQL that uses like and has placeholders? Here's the scoop. I've got a system that uses a lot of pre-generated SQL with placeholders in it. At runtime these SQL statements are fired off (through the C PQexecParams function, if that matters) for execution. No prepares or anything, just bare statements with $1 and friends, with the values passed in as parameters. Straightforward, and no big deal. Unfortunately, performance is horrible. And when I mean horrible, we're talking 6 orders of magnitude (101355.884 ms vs 0.267 ms) when checked out via EXPLAIN ANALYZE. The slow version has the SQL defined as a function with the parameters passed in, while the fast way has the parameters substituted in, and the query plan for the slow version notes that it's doing a sequential scan, while the fast version uses one of the indexes. (And the field being LIKEd has a b-tree index on it) The LIKE condition always has a constant prefix -- it's 'S%' or 'S42343%' -- so it fits the index. Now, I'd not be surprised for a generic function to do this, if the plan is created when the function is created, and I can deal with that. I'd figure, though, that since the parameters are being passed into PQexecParams basically to get them out of band so I don't have to deal with escaping, quoting, and suchlike things, that the optimizer would look at things *after* the substitution was done. Is there anything I can do to speed this up, short of doing the parameter substitution myself and skipping PQexecParams here? (Which I'd rather not, since it's a pain and somewhat error-prone (for me, at least)) -- Dan --it's like this--- Dan Sugalski even samurai [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SMgrRelation hashtable corrupted
Chris Ochs [EMAIL PROTECTED] writes: On 8 beta1 I get 'SMgrRelation hashtable corrupted' *sometimes* when executing the following, but only when there are other clients connecting to the same database. Hmm. The SMgrRelation hashtable is local in each backend, so I'm not sure I believe the correlation to other backends being active, but ... What I'd suggest doing is changing the elog(ERROR) to elog(PANIC) (in smgrclose in src/backend/storage/smgr/smgr.c), rebuilding with --enable-debug if you didn't already, and then getting a debugger stack trace from the core dump next time it happens. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Speeding up LIKE with placeholders?
Dan Sugalski [EMAIL PROTECTED] writes: I'd figure, though, that since the parameters are being passed into PQexecParams basically to get them out of band so I don't have to deal with escaping, quoting, and suchlike things, that the optimizer would look at things *after* the substitution was done. You'd figure wrong :-(. The present mechanism for the LIKE-to-index optimization requires the LIKE pattern to be a pure, unadulterated constant. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Speeding up LIKE with placeholders?
At 5:19 PM -0400 9/10/04, Tom Lane wrote: Dan Sugalski [EMAIL PROTECTED] writes: I'd figure, though, that since the parameters are being passed into PQexecParams basically to get them out of band so I don't have to deal with escaping, quoting, and suchlike things, that the optimizer would look at things *after* the substitution was done. You'd figure wrong :-(. The present mechanism for the LIKE-to-index optimization requires the LIKE pattern to be a pure, unadulterated constant. Well. Darn. Would I regret it if I asked where in the source this lies so I could go fix it? -- Dan --it's like this--- Dan Sugalski even samurai [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] how to constrain a query to return 1 or 0 rows (or 1 or 0 rows)
This is probably a stupid question, but ... I'd like to be able to take an existing query and modify it to return a single row if that's what the base query returns, and 0 rows if the base query returns multiple rows. Similarly, I'd like to also modify it to return multiple rows if that's what the base query returns, and 0 rows if the base query return a single row. What's a good way to do this? Thanks, Kevin Murphy ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] 8.0.0beta2: gcc: unrecognized option `-pthreads'
Ed L. wrote: Is this pthreads warning of any concern? gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -pthreads -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -fpic -shared -Wl,-soname,libecpg.so.4 execute.o typename.o descriptor.o data.o error.o prepare.o memory.o connect.o misc.o path.o exec.o -L../../../../src/port -L../pgtypeslib -lpgtypes -L../../../../src/interfaces/libpq -lpq -lcrypt -lm -lpthread -Wl,-rpath,/opt/pgsql/installs/postgresql-8.0.0beta2/lib -o libecpg.so.4.2 gcc: unrecognized option `-pthreads' No. The problem is that the test script just tries all options and if it doesn't error out, it uses it. Ideally we could test from configure and ingnore meaningless options but we don't know how yet. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Speeding up LIKE with placeholders?
Dan Sugalski [EMAIL PROTECTED] writes:
Would I regret it if I asked where in the source this lies so I could
go fix it?
If it were easy to fix it would have been fixed before now ...
I have toyed with the notion of converting var LIKE pattern to
var LIKE pattern AND var = lowbound(pattern) AND var highbound(pattern)
where lowbound() and highbound() are actual functions that we leave in
the generated plan, rather than insisting that the planner derive these
bounds before making the plan at all. Then the pattern wouldn't have
to be a true constant. However, it falls down on this problem: what
shall those functions do if the supplied pattern isn't left-anchored at
all? highbound in particular doesn't have a valid result it can give
that's guaranteed larger than all possible values of var. Not to
mention that a full-table index scan is the very last thing you want ---
I think the planner would really be abdicating its responsibilities to
generate a plan with that kind of downside risk.
You could possibly sidestep this argument by envisioning a query like
var LIKE ('^' || $1)
but I doubt that anyone actually writes such things. In the end, LIKE
is the sort of thing that you really have to run a planning cycle for
in order to get a reasonable plan.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] What is the postgres version of mysql's ON DUPLICATE KEY
Nick wrote: I have a table with columns (product_id,related_product_id,related_counter) If product A is related to product B then a record should be created, if the record already exists then the related_counter should be incremented. This is very easy to do with MySQL using INSERT... ON DUPLICATE KEY. Standard or not, it is very usefull. Is there a way to catch the insert error. For example... INSERT INTO related_products (product_id,related_product_id) VALUES (?,?); IF (???error: duplicate key???) THEN UPDATE related_products SET related_counter = related_counter + 1; END IF; -Nick With a rule you can do it easily ( never tried ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.0.0beta2: Ownership of implicit sequences after dump/restore
Georgi Chorbadzhiyski wrote: I just experienced the same problem [1] with 8.0.0beta2. [1] http://archives.postgresql.org/pgsql-bugs/2004-08/msg00086.php Right. It is still on the open items list. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump/pg_dumpall do not correctly dump search_path
Would someone answer this report?. Looks strange to me. --- Ben Trewern wrote: All, There seems to be a bug in pg_dumpall: For one of my dbs I've done: ALTER DATABASE dbname SET search_path = mw, public; If I do a pg_dumpall I get a line like: ALTER DATABASE dbname SET search_path TO 'mw, public'; note the 's. It's also in a place in the dump before the mw schema is created. It's not a big problem but it makes dumps less automatic. BTW If I do a pg_dump dbname I get a dump which does not reference the search_path change. I'm not sure if this is by design or it is just missing. I'm using PostgreSQL 7.4.5 on linux Thanks for any help. Ben _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.0.0beta2: gcc: unrecognized option `-pthreads'
Bruce Momjian [EMAIL PROTECTED] writes: No. The problem is that the test script just tries all options and if it doesn't error out, it uses it. Ideally we could test from configure and ingnore meaningless options but we don't know how yet. Drop any options that cause the compiler to write anything on stderr. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Speeding up LIKE with placeholders?
At 5:55 PM -0400 9/10/04, Tom Lane wrote: Dan Sugalski [EMAIL PROTECTED] writes: Would I regret it if I asked where in the source this lies so I could go fix it? If it were easy to fix it would have been fixed before now ... Oh, I wasn't expecting it to be an *easy* fix... :) The question is whether it's less work to make the fix in Postgres or in my back-end library code. Worst case I fix it in my code and submit a doc patch, but I'm up for at least investigating the general fix. Since the only difference in this case is that the parameters are pulled out for transport rather than being in band (a properly-escaped string substitution could turn this case from a PQexecParams call into a PQexec call) I was thinking the thing to do would be to either teach the planner to look in the parameter list when it gets handed $xxx variables, or have the back-end do the substitution to the SQL before handing the code to the planner. I'm not sure I like either option (the pre-substitution's got some issues when handed large parameters, while teaching the planner to use a parameter list may require thumping a lot of back-end code) and it may amount to nothing, but I figured it was worth a look, if for no other reason than to find a big mass of code I can safely run screaming from. ;-) -- Dan --it's like this--- Dan Sugalski even samurai [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] What is the postgres version of mysql's ON DUPLICATE KEY
UPDATE related_products SET related_counter = related_counter WHERE . only updates if the record exists INSERT (x,y,z) SELECT ?,?,1 WHERE NOT EXISTS (SELECT 1 FROM related_products WHERE .) Inserts if the key does not exist. On Sat, 11 Sep 2004 00:02:26 +0200, Gaetano Mendola [EMAIL PROTECTED] wrote: Nick wrote: I have a table with columns (product_id,related_product_id,related_counter) If product A is related to product B then a record should be created, if the record already exists then the related_counter should be incremented. This is very easy to do with MySQL using INSERT... ON DUPLICATE KEY. Standard or not, it is very usefull. Is there a way to catch the insert error. For example... INSERT INTO related_products (product_id,related_product_id) VALUES (?,?); IF (???error: duplicate key???) THEN UPDATE related_products SET related_counter = related_counter + 1; END IF; -Nick With a rule you can do it easily ( never tried ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Speeding up LIKE with placeholders?
Dan Sugalski [EMAIL PROTECTED] writes: Since the only difference in this case is that the parameters are pulled out for transport rather than being in band (a properly-escaped string substitution could turn this case from a PQexecParams call into a PQexec call) I was thinking the thing to do would be to either teach the planner to look in the parameter list when it gets handed $xxx variables, or have the back-end do the substitution to the SQL before handing the code to the planner. This has already been considered and rejected. Oliver Jowett did the part that is safe, which is to use the parameter values for estimation purposes in other contexts, but pre-substituting a parameter value for LIKE calls the mere correctness of the plan into question. What it would take to make it workable is a change in the semantics of the v3 protocol messages, such that there is no re-use of a plan. That, no one is up for quite yet, when we just hacked the protocol last year ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Speeding up LIKE with placeholders?
At 6:33 PM -0400 9/10/04, Tom Lane wrote: Dan Sugalski [EMAIL PROTECTED] writes: Since the only difference in this case is that the parameters are pulled out for transport rather than being in band (a properly-escaped string substitution could turn this case from a PQexecParams call into a PQexec call) I was thinking the thing to do would be to either teach the planner to look in the parameter list when it gets handed $xxx variables, or have the back-end do the substitution to the SQL before handing the code to the planner. This has already been considered and rejected. Oliver Jowett did the part that is safe, which is to use the parameter values for estimation purposes in other contexts, but pre-substituting a parameter value for LIKE calls the mere correctness of the plan into question. Ouch. Okay, fair 'nuff. (I figured the parameters could be factored in before the plan was made. Wrongly, I see, now that I poke around in the code a bit :) Plan B for me it is. What it would take to make it workable is a change in the semantics of the v3 protocol messages, such that there is no re-use of a plan. That, no one is up for quite yet, when we just hacked the protocol last year ... It might be possible with a backwards-compatible protocol change, but that's more work than I'm up for, and this is the wrong list for it anyway. -- Dan --it's like this--- Dan Sugalski even samurai [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] 8.0.0beta2: gcc: unrecognized option `-pthreads'
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
No. The problem is that the test script just tries all options and if
it doesn't error out, it uses it. Ideally we could test from configure
and ingnore meaningless options but we don't know how yet.
Drop any options that cause the compiler to write anything on stderr.
OK, the configure test is:
AC_TRY_LINK([#include pthread.h],
[pthread_t th; pthread_join(th, 0);
pthread_attr_init(0); pthread_cleanup_push(0, 0);
pthread_create(0,0,0,0); pthread_cleanup_pop(0); ],
[acx_pthread_ok=yes], [acx_pthread_ok=no])
Seems we can't just jump in there and grab stderr so I added the
following code to CVS which should do the trick.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: configure
===
RCS file: /cvsroot/pgsql-server/configure,v
retrieving revision 1.391
diff -c -c -r1.391 configure
*** configure 10 Sep 2004 13:53:39 - 1.391
--- configure 10 Sep 2004 23:55:53 -
***
*** 13325,13342
fi
rm -f conftest.$ac_objext conftest$ac_exeext conftest.$ac_ext
LIBS=$save_LIBS
CFLAGS=$save_CFLAGS
echo $as_me:$LINENO: result: $acx_pthread_ok 5
echo ${ECHO_T}$acx_pthread_ok 6
- if test x$acx_pthread_ok = xyes; then
- # we continue with more flags because Linux needs -lpthread
- # for libpq builds on PostgreSQL. The test above only
- # tests for building binaries, not shared libraries.
- PTHREAD_LIBS= $tryPTHREAD_LIBS $PTHREAD_LIBS
- PTHREAD_CFLAGS=$PTHREAD_CFLAGS $tryPTHREAD_CFLAGS
- fi
done
fi
--- 13325,13356
fi
rm -f conftest.$ac_objext conftest$ac_exeext conftest.$ac_ext
+ if test x$acx_pthread_ok = xyes; then
+ # Don't use options that are ignored by the compiler.
+ # We find them by checking stderror.
+ cat conftest.$ac_ext _ACEOF
+ int
+ main ()
+ {
+ return 0;
+ }
+ _ACEOF
+ rm -f conftest.$ac_objext conftest$ac_exeext
+ if test `(eval $ac_link 21 /dev/null)` = ; then
+ # we continue with more flags because Linux needs -lpthread
+ # for libpq builds on PostgreSQL. The test above only
+ # tests for building binaries, not shared libraries.
+ PTHREAD_LIBS= $tryPTHREAD_LIBS $PTHREAD_LIBS
+ PTHREAD_CFLAGS=$PTHREAD_CFLAGS $tryPTHREAD_CFLAGS
+ else acx_pthread_ok=no
+ fi
+ fi
+
LIBS=$save_LIBS
CFLAGS=$save_CFLAGS
echo $as_me:$LINENO: result: $acx_pthread_ok 5
echo ${ECHO_T}$acx_pthread_ok 6
done
fi
Index: config/acx_pthread.m4
===
RCS file: /cvsroot/pgsql-server/config/acx_pthread.m4,v
retrieving revision 1.6
diff -c -c -r1.6 acx_pthread.m4
*** config/acx_pthread.m4 17 Aug 2004 15:19:09 - 1.6
--- config/acx_pthread.m4 10 Sep 2004 23:55:54 -
***
*** 129,145
pthread_create(0,0,0,0); pthread_cleanup_pop(0); ],
[acx_pthread_ok=yes], [acx_pthread_ok=no])
LIBS=$save_LIBS
CFLAGS=$save_CFLAGS
AC_MSG_RESULT($acx_pthread_ok)
- if test x$acx_pthread_ok = xyes; then
- # we continue with more flags because Linux needs -lpthread
- # for libpq builds on PostgreSQL. The test above only
- # tests for building binaries, not shared libraries.
- PTHREAD_LIBS= $tryPTHREAD_LIBS $PTHREAD_LIBS
- PTHREAD_CFLAGS=$PTHREAD_CFLAGS $tryPTHREAD_CFLAGS
- fi
done
fi
--- 129,159
pthread_create(0,0,0,0); pthread_cleanup_pop(0); ],
[acx_pthread_ok=yes], [acx_pthread_ok=no])
+ if test x$acx_pthread_ok = xyes; then
+ # Don't use options that are ignored by the compiler.
+ # We find them by checking stderror.
+ cat conftest.$ac_ext _ACEOF
+ int
+ main ()
+ {
+ return 0;
+ }
+ _ACEOF
+ rm -f conftest.$ac_objext conftest$ac_exeext
+ if test `(eval $ac_link 21 /dev/null)` = ; then
+ # we continue with more flags because Linux needs -lpthread
+ # for libpq builds on PostgreSQL. The test above only
+ # tests for building binaries, not shared libraries.
+ PTHREAD_LIBS= $tryPTHREAD_LIBS $PTHREAD_LIBS
+ PTHREAD_CFLAGS=$PTHREAD_CFLAGS $tryPTHREAD_CFLAGS
+ else
Re: [GENERAL] Obtaining the Julian Day from a date
On Thu, Sep 09, 2004 at 16:32:18 -0500, Karl O. Pinc [EMAIL PROTECTED] wrote: Unfortunately modulo (%) does not operate on dates so I still need to convert to Julian day. :-( I need to know where I am within a regular repeating interval. Mostly, in my case, modulo 2. (We arbitrarly decided to begin our interval on Julian Day 0.) If you keep your data in a date field you can get the Julian day by subtracting the appropiate date. You can then do mod on this difference. You could also do the subtraction before storing the data if you want to keep it internally as Julian days. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Another Security Question: User-based Roles vs. Application Business Rules
Forgive me if this is a basic and trivial (i.e., stupid) question. I haven't been using postgres very long, and I'm not an experienced database system developer. I noticed that there is a very powerful group-based security feature in postgres. Very nice - I like it alot. So one way to implement security constraints is to define appropriate groups, assign memobership of users to those groups, and then assign group-based permissions to the assorted database objects (e.g., tables). Fantastic! However, ... this requires each entity accessing the databse to be defined as a user. In the context of a web application, this paradigm doesn't necessarily make sense since there may be many unknown users. Somehow those users must be mapped to a role. I suppose you can map all unknown users into the user guest and then define guest privileges appropriately. Is this a good approach? Is there better way to do this? Is there an altnerate way to consider? -- % Randy Yates % My Shangri-la has gone away, fading like %% Fuquay-Varina, NC% the Beatles on 'Hey Jude' %%% 919-577-9882% [EMAIL PROTECTED] % 'Shangri-La', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] building postgres for windows 2000?
Has anyone successfully done this? I've got cygwin and the very first step crashes - bash-2.05$ pwd pwd /cygdrive/e bash-2.05$ cd postgresql-7.4.5 cd postgresql-7.4.5 bash-2.05$ ./configure ./configure bash: ./configure: bad interpreter: No such file or directory bash-2.05$ -- % Randy Yates % My Shangri-la has gone away, fading like %% Fuquay-Varina, NC% the Beatles on 'Hey Jude' %%% 919-577-9882% [EMAIL PROTECTED] % 'Shangri-La', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] cygserver -S crashes under Windows 2000/cygwin
$ /usr/sbin/cygserver -S Segmentation fault (core dumped) [EMAIL PROTECTED] ~ $ -- % Randy Yates % ...the answer lies within your soul %% Fuquay-Varina, NC% 'cause no one knows which side %%% 919-577-9882% the coin will fall. [EMAIL PROTECTED] % 'Big Wheels', *Out of the Blue*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] initdb crashes under Windows
Can't get the postgres server started under Win2000/cygwin. Here's what happens: $ initdb -D /Gauss/rr/data -W The files belonging to this database system will be owned by user yates. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /Gauss/rr/data... ok creating directory /Gauss/rr/data/base... ok creating directory /Gauss/rr/data/global... ok creating directory /Gauss/rr/data/pg_xlog... ok creating directory /Gauss/rr/data/pg_clog... ok selecting default max_connections... Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 10 selecting default shared_buffers... Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 50 creating configuration files... ok creating template1 database in /Gauss/rr/data/base/1... FATAL: lock file /Gauss/rr/data/postmaster.pid already exists HINT: Is another postgres (PID 1672) running in data directory /Gauss/rr/data? initdb: failed [EMAIL PROTECTED] ~ -- % Randy Yates % Rollin' and riding and slippin' and %% Fuquay-Varina, NC% sliding, it's magic. %%% 919-577-9882% [EMAIL PROTECTED] % 'Living' Thing', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Datatypes Documentation
It might be a good idea to place a reference to table 8-1 in the various subsections of the Datatypes section in the online postgresql documentation. Otherwise, when hyperjumping from the table of contents to a specific section, table 8-1 may not be in sight (as it is not for integer datatypes) and the user is left wondering why these datatypes aren't further defined. -- % Randy Yates % Remember the good old 1980's, when %% Fuquay-Varina, NC% things were so uncomplicated? %%% 919-577-9882% 'Ticket To The Moon' [EMAIL PROTECTED] % *Time*, Electric Light Orchestra http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Firewall Security Requirements for Postgresql Access
Is opening up port 5432 (R/W both directions) all that is required of a firewall in order to access a postgres database outside the firewall? -- % Randy Yates % My Shangri-la has gone away, fading like %% Fuquay-Varina, NC% the Beatles on 'Hey Jude' %%% 919-577-9882% [EMAIL PROTECTED] % 'Shangri-La', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] unicode and varchar
Hi all,
I have a problem when inserting into a varchar field via jdbc.
Using to bytes makes the varchar fields shorten.
Example: with 7.4.5. on Linux
create table test(charfield varchar(5));
insert into test(charfield) values('abcde');
insert into test(charfield) values('üö');
select bit_length(charfield), length(charfield), charfield from test
bit_length length charfield
- -
40 5 abcde
32 4 üö
insert into test(charfield) values('üäö');
Error: ERROR: value too long for type character varying(5)
Doing the same on Windows with the fresh 8.0 beta 2 shows a different
behaviour.
create table test(charfield varchar(5));
insert into test(charfield) values('abcde');
insert into test(charfield) values('üöüöä');
select bit_length(charfield), length(charfield), charfield from test
bit_length length charfield
- -
40 5 abcde
80 5 üöüöä
'length' seems to account the 16 bit character length.
Is there a way to make this work in 7.4.x too?
May problem is that we are in a rush to do a port of our application
from informix and I will not be able to wait until 8.0 will be released.
BTW, both database servers where queried with the same JDBC driver
(version).
Best regards,
Michael
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] What is the postgres version of mysql's ON DUPLICATE KEY
I have a table with columns (product_id,related_product_id,related_counter) If product A is related to product B then a record should be created, if the record already exists then the related_counter should be incremented. This is very easy to do with MySQL using INSERT... ON DUPLICATE KEY. Standard or not, it is very usefull. Is there a way to catch the insert error. For example... INSERT INTO related_products (product_id,related_product_id) VALUES (?,?); IF (???error: duplicate key???) THEN UPDATE related_products SET related_counter = related_counter + 1; END IF; -Nick ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Salt in encrypted password in pg_shadow
Greg Stark wrote: Gaetano Mendola [EMAIL PROTECTED] writes: Well, when SHA-0 was ready NSA suggested to apply some changes in order to correct some flaw discovered and SHA-1 comes out, interesting NSA never wrote which flaw was corrected! May be SHA-1 is trasparent water to NSA eyes :-) This is awfully similar to the story that's told about DES: When DES was under development the NSA told people to try a few specific constants for the sboxes stage of the cipher. As far as anyone at the time could tell they were completely random values and nearly any value would have been just as good. Then 30 years later when differential cryptanalysis was invented people found the values the NSA told them to use are particularly resistant to differential cryptanalysis attacks. Almost any other values and DES would have fallen right then. This means it's quite possible the NSA had differential cryptanalysis 30 years before anyone else. Quite a remarkable achievement. However it's unlikely that the same situation holds today. 30 years ago nobody outside the government was doing serious cryptanalysis. If you were a mathematician interested in the field you worked for the NSA or you changed fields. These days there's tons of research in universities and in the private sector in serious cryptanalysis. The NSA still employs plenty of good cryptanalysts but they no longer have the monopoly they did back then. I will invite you to repeat the same sentence in 2034 ... :-) Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Best practices for migrating a development database to a release database
I have searched the Internet... but haven't found much relating to this. I am wondering on what the best practices are for migrating a developmemnt database to a release database. Here is the simplest example of my situation (real world would be more complex). Say you have two versions of your application. A release version and a development version. After a month of developing you are ready to release a new version. There have been many changes to the development database that are not in the release database. However, the release database contains all your real information (customers, etc...). What is the best practice for migrating the development database to the release database? I have thought of the following situations: -Simply track all the changes you made to the development database and make the same changes to the release database -Back up the release database... overwrite it with the development database... then copy all your real data back into the release database (this last step is probably quite difficult) -Perhaps some combination of the two Does anybody have any recommendations? Regards, Collin Peters ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] One Database per Data File?
I'm a complete newbie to postgres so please look the other way if these questions are really stupid. Is it legitimate to have one database per data file? For organizational and backup purposes, I'd like to keep the database files for each of several projects separate. This means, e.g., that postmaster must have multiple instances going simultaneously? I'm thinking the answer is NO because, for one, the TCPIP connection seems to be to ONE instance of postmaster which then sorts out which database objects are in its container. Am I close? -- % Randy Yates % Maybe one day I'll feel her cold embrace, %% Fuquay-Varina, NC%and kiss her interface, %%% 919-577-9882%til then, I'll leave her alone. [EMAIL PROTECTED] %'Yours Truly, 2095', *Time*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] HOWTO: Get a table or database definition
I guess it would be great if Pgsql had a way to find a database definition via a system stored procedure like other database platforms have. There are two ways I've found so far: SELECT attname as name, typname as type, atttypmod - 4 as size, relhaspkey as is_primary_key, * FROM pg_class AS a LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid) LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid) where a.relname = 'names' and b.attstattarget = -1 order by attnum; ...yields great results for a table called 'names' The other way is: pg_dump -h localhost -p 5432 -U root -s -C test | grep -i CREATE -A 50 | grep -v \-\- | grep -v \\connect | grep -v SET | tr -s \n ...shows me a result for host 'localhost', port '5432', user 'root', database 'test' ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Migrating from MaxDB to postgresql
Christopher Browne wrote: Quoth John [EMAIL PROTECTED]: As per subject, i'm considering migrating a database (still in development) from MaxDB to postgresql. The main reason for this is that the stored procedures (functions) in MaxDB are unreliable and hard to debug, and that the JDBC driver is still experimental. I thought I'd post here to find out how well functions and JDBC are supported in postgresql. If anyone has info or experience on these two things, I'd be interested to see what your opinion is. Some internal hackery has occasionally taken place with JDBC drivers that have caused internal consternation (basically due to developers needing some new features that were in the beta JDBC code), although that seems to be fading with 7.4. And if you don't hack on the drivers, you won't get bitten by that sort of thing :-). The fact that there are easily multiple levels of quoting in stored functions certainly makes debugging a bit of a pain, but unreliability is not one of the problems observed. For instance, the Slony-I replication system http://slony.info/ has a considerable portion of its code that consists of pl/pgsql stored functions, and it would break with great horribleness if stored functions were at all flakey. Cheers Christopher. I'll try and resist the hackage and get started with the JDBC. The quality of the documentation seems good, so I'll have a go at translating some stored functions this afternoon. John ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Salt in encrypted password in pg_shadow
Tom Lane [EMAIL PROTECTED] writes: it's unlikely that the same situation holds today. Why would you think that? The US government may not have too many clues, but they certainly understand the importance of crypto. I cannot think of any reason to suppose that NSA et al would have stopped spending serious effort in this area. Certainly the NSA hasn't stopped spending serious effort. What's changed is that now there is serious effort outside the NSA as well. In academia and the private sector, not to mention other national governments. That wasn't the case 30 years ago partially because the money just wasn't there outside the NSA, and partially because the NSA was extremely persuasive in hiring away anyone doing research. It's hard to do get ahead in publish-or-perish academia when everything you're working on suddenly becomes classified... (Where serious effort is measured by the standard of a billion here, a billion there, pretty soon you're talking about real money.) Well there's a limit to how much you can spend on researcher salaries. There are only so many researchers available to hire. Of course we don't know what their full budget is but if it's in the billions (which it may well be) it's probably mostly spent on operational costs, not research. Quite honestly, as a US taxpayer I would not be happy if the NSA were not far ahead of public research in this field ... It's presumably ahead. But not like the situation 30 years ago when they were the only group doing this kind of research. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Auto increment/sequence on multiple columns?
Is it possible to have a sequence across two columns. For example table1 +---+---+ | a | b | +---+---+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 | | 2 | 3 | | 3 | 1 | | 3 | 2 | | 3 | 3 | +---+---+ Would I have to create a new sequence for every unique 'a' column? That seems pretty tedious. -Nick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] help turning of NOTICE messages
Hi All, I'm developing some scripts to create my schema to be run by other folks and I'd like to have them not spit out those NOTICE messages when you create a table regarding the implicit indexes that are made since it just makes it harder to see when there is a real error. I've tried running psql with the quiet argument, I've set the QUIET variable to true and the VERBOSITY level to terse but the NOTICE messages still appear. I searched the archives and found one post from a developer where he stated that this is a feature that's been asked for and would be available in 7.3 and I have 7.4.5. Other than that I haven't been able to find any documentation or mention of it. Can anyone point me in the right direction? Thanks, Alex ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] PL/pgSQL Function Problem
I am trying to create a function that creates a user and adds a row to a table. It produces no warnings or errors when I create the function but when I attempt to execute it I get a syntax error. I do not understand why this is happening. Any help would be greatly appreciated. SELECT create_author( 'name', 'username', 'password' ); ERROR: syntax error at or near $1 at character 14 CONTEXT: PL/pgSQL function create_author line 7 at SQL statement Here is the code: CREATE OR REPLACE FUNCTION create_author ( VARCHAR(32), VARCHAR(32), VARCHAR(32) ) RETURNS INTEGER AS ' DECLARE name_ ALIAS FOR $1; username_ ALIAS FOR $2; password_ ALIAS FOR $3; authorid_ INTEGER; BEGIN CREATE USER username_ WITH ENCRYPTED PASSWORD password_ IN GROUP authors; INSERT INTO Authors ( Name, Username ) VALUES ( $1, $2 ); SELECT Max( AuthorID ) INTO authorid_ FROM Authors; RETURN authorid_; END; ' LANGUAGE 'plpgsql' SECURITY INVOKER RETURNS NULL ON NULL INPUT; __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Salt in encrypted password in pg_shadow
Greg Stark [EMAIL PROTECTED] writes: This means it's quite possible the NSA had differential cryptanalysis 30 years before anyone else. s/quite possible/known fact/ Quite a remarkable achievement. However it's unlikely that the same situation holds today. Why would you think that? The US government may not have too many clues, but they certainly understand the importance of crypto. I cannot think of any reason to suppose that NSA et al would have stopped spending serious effort in this area. (Where serious effort is measured by the standard of a billion here, a billion there, pretty soon you're talking about real money.) Quite honestly, as a US taxpayer I would not be happy if the NSA were not far ahead of public research in this field ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] postgresql hanging (blocking) with smp kernel
I have a problem with postgresql runnung on smp kernel. setup: master: --- - Pentium 4 (hyperthreading) - 2 GB Memory - os: fedora core 1 - kernel: 2.4.22-1.2188.nptlsmp - postgresql: 7.4.3-1PGDG slave 1: - Pentium 4 (hyperthreading) - 2 GB Memory - os: fedora core 1 - kernel: 2.4.22-1.2115.nptlsmp - postgresql: 7.4.3-1PGDG slave 2: - Double Xeon (with hyperthreading) - 2 GB Memory - os: fedora core 1 - kernel: 2.4.22-1.2199.nptlsmp - postgresql: 7.4.3-1PGDG the replication is made by hand. reading from slave 1 or slave 2 (balanced) and wrtiting to master, slave 1 and slave 2. Our site is a high-traffic site (the biggest dating-site in switzerland: www.swissflirt.ch) with 1400 concurrent users and 40'000 visits per day. master and slave 1 (with pentium 4) are working perfectly with smp-kernel. slave 2 (with double xeon) has big problems. running p.e. with kernel 2.4.22 (non smp) works also but of course only one CPU is used. when I use the smp kernel, the connections (and queries) are hanging (blocking) after some short time. Also when I shutdown the application (using java and jdbc to connect to the databases) the postgres-processes (on slave 2) keep existing while the processes on master and slave 1 shutdown propertly. I also tried with other kernel-releases (the one on the master 2.4.22-1.2188.nptlsmp and the one on the slave 1 2.4.22-1.2115.nptlsmp) but it doesnt help neither. does anyone know what this could be? thanks alot marcel ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Migrating from MaxDB to postgresql
As per subject, i'm considering migrating a database (still in development) from MaxDB to postgresql. The main reason for this is that the stored procedures (functions) in MaxDB are unreliable and hard to debug, and that the JDBC driver is still experimental. I thought I'd post here to find out how well functions and JDBC are supported in postgresql. If anyone has info or experience on these two things, I'd be interested to see what your opinion is. John ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] How do I list the schema for a table or procedure?
Google Mike wrote: How do I list the schema for a table or procedure? Is there a command I can do in psql to list this, or do I have to join a series of tables to see that? You can do a pg_dump -s DBNAME to see the full schema of the database. To see just a table of the database issue the command pg_dump -st TABLENAME DBNAME. These are run from the command line and not within the psql shell. Within psql you could use \d TABLENAME to describe a specific table. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] HOWTO: Get a table or database definition
One other option, which I had forgotten for a long time, was: \d object name ...which can describe many things, although this doesn't give you the CREATE syntax like a pg_dump can do. Please also note that a pg_dump can dump output to the screen if you don't specify a file, so if you're only outputting the schema with -s -C, it doesn't really impact a live production database much at all. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Access MDB Schema Import Tool?
Is there a tool that allows the tables and relationships of an Access database to be moved into postgresql (7.4.5)? Sorry if this has been asked before. There also may be new tools that have recently come out. -- % Randy Yates % Watching all the days go by... %% Fuquay-Varina, NC% Who are you and who am I? %%% 919-577-9882% 'Mission (A World Record)', [EMAIL PROTECTED] % *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgres on in the internet
Hi all; Comments inline. Lincoln Yeoh wrote: I doubt it's a good idea to make your postgres server internet accessible. You'll be using postgresql in what I'd consider to be a less tested scenario. Most people don't expose their database servers to the Internet. You could use the following configuration: client (with IPSEC VPN) | Internet | Firewall #1 (VPN endpoint) | Staging network (for VPN clients) | Firewall #2 | Database server The clients with VPN access get access to whatever the staging network has access to - which may be the postgresql db port and other services, but nothing else not explicitly permitted by Firewall #2, or Firewall #1. I have to agree with this architecture. However, I would also suggest that you think about public key management so that in the event that the client key becomes corrupt while traveling, they can call in and have the situation resolved quickly. I.e. Run this tool. It will generate your keys and email your public key to me. Also, although this is likely to be the hardest environment to set up, it will probably be the most mainenance-free in the long-run. I.e. PPTP is more vulnerable to a wide variety of attacks including DoS, etc. than IPSec is, and having a good set of security barriers is critical when you are looking at business data. The SSL issue could be used as well, but I don't really know what sorts of options are available on Windows for SSL-based VPN's. Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] V8.0 beta 1 Void type
I am declaring a variaable of type void in a plpgsql function which serves to receive the value(?) returned by another function which has been declared to return a void type. This worked in 7.4 but in testing 8, we receive an error - 'ERROR: variable dbg has pseudo-type void'. Is there a change in functionality between these versions? or is this a bug? thanks, Nick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] stringToNode() for plan nodes...
Hi! I have a problem with stringToNode() function. I'm trying to convert a Plan into its ASCII representation. This seems to succeed. The problem is that when I try to convert it back with stringToNode() the following error is reported: ERROR: badly formatted node string SEQSCAN :startup_cost 0.00 :tota... In other words stringToNode(nodeToString(plan)) fails. I've read somewhere that there is no support in stringToNode() for Plan nodes.Is this true? I want it to work in order to store (in ASCII format) and then read a Plan from disc. Any suggestions...? Thanks in advance! Katsaros Kwn/nos ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] error: insert has more expressions than target column
Dino Vliet wrote: MUCH better nowI did manage to get an insert into the table lessons with these adjustments...BUT now it seems the FOR LOOP didn't work because I only get 1 record and expected that I would get 8 records due to the i variabele. What could be wrong? My code is now: CREATE FUNCTION vulalles() RETURNS trigger AS ' BEGIN FOR i in 0..7 LOOP INSERT INTO lessons (..) SELECT dayofweek,startdate + (i*7), enddate + (i*7),...; RETURN NEW; END LOOP; END; ' LANGUAGE plpgsql; Is the 'RETURN NEW' statement supposed to be _BEFORE_ end loop? To me, it looks like you are returning from the function in the first loop turn. /Njörn ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Release of 8.0.0
WOW, that is the most comprehensive, 'nearing to commercial capability' update of Postgres (and any OTHER OSS project) that I've seen in all my readings about and dealings with Postgres. I would be VERY surprised if Postgres doesn't win some major awards (and accounts) now that this has happened. If there were some way I could send all of the dev group to some remote, computer free island to relax, I would. FANTASTIC job you guys. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] One Database per Data File?
Christopher Browne [EMAIL PROTECTED] writes: Oops! Randy Yates [EMAIL PROTECTED] was seen spray-painting on a wall: I'm a complete newbie to postgres so please look the other way if these questions are really stupid. Is it legitimate to have one database per data file? For organizational and backup purposes, I'd like to keep the database files for each of several projects separate. This means, e.g., that postmaster must have multiple instances going simultaneously? I'm thinking the answer is NO because, for one, the TCPIP connection seems to be to ONE instance of postmaster which then sorts out which database objects are in its container. Am I close? Not terribly. For a given cluster (e.g. - an instance initialized using initdb), you have a set of databases, each of which is indicated by a directory under 'base/' in that cluster. That does not seem to be the case. I have three subdirectories in my base/ directory, but according to PGADMIN III, only one database. Within each database in the cluster, each table and index is indicated by one (or more, if size 1GB) files. Thus, each database will have numerous data files, essentially one per table and one per index. If you rummage around in the files, you can learn quite a lot about the structuring of things. Each file has a number; that number corresponds to the OID number in pg_class. Thus, if you find a file called 17441, then you could find out more about it by the query select * from pg_class where oid = 17441; -- (reverse (concatenate 'string gro.gultn @ enworbbc)) http://cbbrowne.com/info/internet.html I love the way Microsoft follows standards. In much the same manner that fish follow migrating caribou. -- Sinister Midget Ahh, ok. So how does this answer my question or help me achieve my goal of one database per initdb file set? You also have not answered whether or not postmaster can have multiple instances running, each pointing to a different initdb file set. -- % Randy Yates % How's life on earth? %% Fuquay-Varina, NC% ... What is it worth? %%% 919-577-9882% 'Mission (A World Record)', [EMAIL PROTECTED] % *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Clustering postgresql
Hello list, has postgresql the functionality for clustering, load balancing and failover. I have to setup two redundant web-servers with and want run a dbms cluster on this machines. My webapps should only see one dbms, but there should be two redundant dbms. I know that mysql 4.1 supports this feature, but mysql is not a ... ;-) so I want use PostgreSQL Thanks for your advice Björn Voigt ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] One Database per Data File?
Not even close. PostgreSQL uses one or more files per table/index/sequence/etc. Each database has its own directory. I think with tablespaces you can even spread a database over multiple directories. Secondly, every connection gets its very own postmaster, they can can each access any file they wish. Hope this helps, On Mon, Sep 06, 2004 at 02:47:56AM +, Randy Yates wrote: I'm a complete newbie to postgres so please look the other way if these questions are really stupid. Is it legitimate to have one database per data file? For organizational and backup purposes, I'd like to keep the database files for each of several projects separate. This means, e.g., that postmaster must have multiple instances going simultaneously? I'm thinking the answer is NO because, for one, the TCPIP connection seems to be to ONE instance of postmaster which then sorts out which database objects are in its container. Am I close? -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp7Wpnxgttzb.pgp Description: PGP signature
