[GENERAL] Problem with complex outer join expression
I'm using 7.4.5 on win XP Pro SP1.
I'm getting:-
ERROR: syntax error at or near "(" at character 155
from the query:-
select dummy_records.sequence_nr,timesheets.weekending,timesheets.timesheet_id
from dummy_records
,left outer join timesheets
on (timesheets.weekending = ('2006-04-09' + (integer
dummy_records.sequence_nr-1)*7)))
where dummy_records.sequence_nr between 1 and (date '2006-04-23' -
date '2006-04-09')/7+1;
What this query is trying to achieve is:-
Find all weekending dates between 2 given weekending dates and any
corresponding timesheets for those weekending dates.
What am I doing wrong here? How do I achieve what I want?
Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL]
am 25.04.2006, um 20:33:39 -0700 mailte Mike Kim folgendes: > Hello, > > I was wondering if anybody know a tool or postgres > command which would allow me to see how much data is > written to hardrive and read from hardrive by Postgres Take a look into the information schema, there are table like: - pg_statio_all_tables - pg_statio_sys_tables - pg_statio_user_tables and many other. > in given time period. (let's say last 24h) You can reset the statitic counters. Please read: http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Anyone install 8.1 on Debian Stable?
On Tue, Apr 25, 2006 at 06:58:20PM +0100, Gavin Hamill wrote: > As per the instructions on the site - you use both :) (I meant which do you pin the specific package or use the -t option.) > > psql for both 7.x and 8.1 will use version 3 of the libpq API, so > there's no issue like with mysql 4.0 versus 4.1's new auth system. I see. I'll build DBD::Pg from source. The Dbdpg-general list recommended linking DBD::Pg against libpq4 instead of libpq3. > > And I assume I'll need to rebuild DBD::Pg -- and any tricks getting > > No rebuilding should be necessary for the same reasons above - > however if you do need to build something, grab > postgresql-server-dev-8.1 :) Great. Thanks for all the hand holding. ;) -- Bill Moseley [EMAIL PROTECTED] ---(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]
Hello, I was wondering if anybody know a tool or postgres command which would allow me to see how much data is written to hardrive and read from hardrive by Postgres in given time period. (let's say last 24h) Thank you, __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] "ERROR: out of memory" during pg_restore
Tom, You bet. I'll give it a go and report back. On Tue, Apr 25, 2006 at 08:39:46PM -0400, Tom Lane wrote: > I've applied the attached patch to 8.1.*, > but it could use more testing --- do you want to patch locally and > confirm it's OK for you? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Please comment on the following OpenFTS/tsearch2 issues!
On Apr 25, 2006, at 3:45 PM, Don Walker wrote: 2. Neither OpenFTS or tsearch2 support exact phrase matching. I've seen the workaround to support matching a single exact phrase by modifying the WHERE clause with textcolumn ~* "exact phrase". Does this give reasonable performance? It seems to work well for me, but I'm sure the results are highly data dependent. Performance will directly depend on the size and number of documents you must sequentially search for your phrase after making the initial cut on the indexed words. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] "ERROR: out of memory" during pg_restore
Wayne Conrad <[EMAIL PROTECTED]> writes: > I've got a 7.4 database that gives postgres an "out of memory" error > when restoring into a 32-bit build 8.1, yet restores into a 64-bit > build of 8.1. > Filesystem: -1367351296 total in 361 blocks; 34704 free (305 chunks); > -1367386000 used Now that I look at it, it's pretty obvious that the backend's large-object functions all risk leaking memory that won't be recovered till end of transaction. I'm not sure why this wasn't noticed before ... maybe the potential leaks were only potential, or something else changed about the usage pattern. Anyway, it clearly needs to be fixed to avoid problems when a single transaction executes a whole lot of large-object operations. I've applied the attached patch to 8.1.*, but it could use more testing --- do you want to patch locally and confirm it's OK for you? regards, tom lane bindcnsavCEeB.bin Description: large-object-leak.patch ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database Selection
[EMAIL PROTECTED] (Scott Marlowe) writes:
> About the security thing. Security is a process, and you won't get
> it from using two different database engines.
I'd argue that security is an "emergent property" which is either
supported by or undermined by particular
facts/features/configurations.
It's not something you can have; instead, conditions may either:
a) Leave you vulnerable to particular attacks, or
b) Protect you from particular attacks.
"Being secure" means that you have done an analysis of some set of
attacks and relevant vulnerabilities, and verified that your
conditions provide protection against those attacks.
Having multiple databases around would protect certain
vulnerabilities; whether they are *relevant* is a whole other
matter.
The notion of having a mental model of what security is, that's
something I'd consider vitally important. If you can't articulate
some sort of model that involves the notions of:
- Attacks, vulnerabilities, and protection against such
- Having some classification of kinds of possible attacks
then I don't think it's possible to articulate that there is any
resultant security.
You might be secure, for some definition thereof, but if you can't
articulate that definition...
--
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/security.html
Friends help you move. Real friends help you move bodies.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] SQL Rule
Bert wrote: Hi list I have a table construction like the one seen below, when i am updating or inserting i get a recurion, logical. But how to manage it that the rule is just doing it one time. Or is it possible to do the sum of a and b in an other way? Bert, i do this with triggers. There are pros and cons. One pro is that you can guarantee the correct result with code that looks like this (I'm coding from memory, there may be some syntax errors): if new.column_c <> old.column_c then raise error 'Cannot make direct assignment to calculated column *column_c*'; end if; Then you follow that up with the assignment, so that the code looks like: if new.column_c <> old.column_c then raise error 'Cannot make direct assignment to calculated column *column_c*'; end if; new.column_c = new.column_a + new.column_b The con is that these triggers go row-by-row. Shockingly I have found the degradation to be only 100% (instead of 700% or 1000%), so that updates take twice as long. In small-transaction situations this is not a problem, it is lost in the overhead of the transaction itself. On large assigment statements that would take 2 minutes you now have to wait 4 minutes, or break up the assignment. The really cool thing about it is that you can provide automation built on top of normalized tables. You get this by doing two things: 1) Derived values depend only upon normalized values or other derived values 2) never allow user writes to automated columns, raise an error when that happens Using views is fine for simple cases, but, and I know this because I've done it, if you expect to automate calculations across 100's of tables including complex and compound calculations, your views will become utterly unworkable, or destroy performance when 28 tables have to be joined together when sombody issues "SELECT Total_exposure FROM Customers" To really get the benefit, you can provide for a FETCH from parents to children, and also SUMs from children to parent. With that and the simple extension of your example you can have really powerful normalized and automated databases. CREATE TABLE test ( a int2, b int2, c int2, id int2 NOT NULL, CONSTRAINT id_test PRIMARY KEY (id) ) WITHOUT OIDS; ALTER TABLE test OWNER TO postgres; CREATE OR REPLACE RULE sum_op AS ON INSERT TO test DO UPDATE test SET c = new.a + new.b WHERE test.id = new.id; CREATE OR REPLACE RULE sum_op_up AS ON UPDATE TO test DO UPDATE test SET c = test.a + test.b WHERE test.id = new.id; ---(end of broadcast)--- TIP 6: explain analyze is your friend begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL Rule
On Tue, Apr 25, 2006 at 02:27:23PM -0700, Bert wrote: > I have a table construction like the one seen below, when i am updating > or inserting i get a recurion, logical. But how to manage it that the > rule is just doing it one time. Or is it possible to do the sum of a > and b in an other way? > ... Bert, (This is a resend to the list; I sent my reply privately by mistake). Have you considered using a view to do the sums on the fly? This avoids all kinds of denormalization troubles (the sum can never be incorrect): wayne=# create table test (a int, b int); CREATE TABLE wayne=# create view test_sum as select *, a + b as c from test; CREATE VIEW wayne=# insert into test (a, b) values (1, 2); INSERT 0 1 wayne=# insert into test (a, b) values (3, 4); INSERT 0 1 wayne=# select * from test_sum; a | b | c ---+---+--- 1 | 2 | 3 3 | 4 | 7 (2 rows) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] "ERROR: out of memory" during pg_restore
I've got a 7.4 database that gives postgres an "out of memory" error when restoring into a 32-bit build 8.1, yet restores into a 64-bit build of 8.1. I dumped a 7.4.9 database, running on Debian/testing, 32-bit, using this command: pg_dump --format=c --blobs production --verbose >production.dbarchive This results in a 37G file. The great bulk of this database is in large objects. I can restore it into 8.1.0, running on Debian testing, 64-bit, using this command: pg_restore -d production production.dbarchive However, I cannot restore it into 8.1.3, running on Debian testing, 32-bit, using the same command. I get this in the postgres log: TopMemoryContext: 45592 total in 4 blocks; 4032 free (31 chunks); 41560 used Filesystem: -1367351296 total in 361 blocks; 34704 free (305 chunks); -1367386000 used TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 used PLpgSQL function cache: 8192 total in 1 blocks; 5968 free (0 chunks); 2224 used CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks); 6328 used Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used MessageContext: 8192 total in 1 blocks; 1952 free (2 chunks); 6240 used smgr relation table: 24576 total in 2 blocks; 14080 free (3 chunks); 10496 used Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used PortalMemory: 8192 total in 1 blocks; 8176 free (1 chunks); 16 used Relcache by OID: 8192 total in 1 blocks; 2336 free (0 chunks); 5856 used CacheMemoryContext: 1040384 total in 7 blocks; 226336 free (1 chunks); 814048 used pg_largeobject_loid_pn_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_2618_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_rewrite_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_constraint_contypid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_constraint_conrelid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_constraint_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_shdepend_depender_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_attrdef_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_pltemplate_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_shdepend_reference_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_depend_depender_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used pg_depend_reference_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 69
Re: [GENERAL] SQL Rule
Could you create the table without the C column then create a view test_view with select a,b,a+b as c,id from test; Oisin Bert wrote: Hi list I have a table construction like the one seen below, when i am updating or inserting i get a recurion, logical. But how to manage it that the rule is just doing it one time. Or is it possible to do the sum of a and b in an other way? CREATE TABLE test ( a int2, b int2, c int2, id int2 NOT NULL, CONSTRAINT id_test PRIMARY KEY (id) ) WITHOUT OIDS; ALTER TABLE test OWNER TO postgres; CREATE OR REPLACE RULE sum_op AS ON INSERT TO test DO UPDATE test SET c = new.a + new.b WHERE test.id = new.id; CREATE OR REPLACE RULE sum_op_up AS ON UPDATE TO test DO UPDATE test SET c = test.a + test.b WHERE test.id = new.id; ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] SQL Rule
Hi list I have a table construction like the one seen below, when i am updating or inserting i get a recurion, logical. But how to manage it that the rule is just doing it one time. Or is it possible to do the sum of a and b in an other way? CREATE TABLE test ( a int2, b int2, c int2, id int2 NOT NULL, CONSTRAINT id_test PRIMARY KEY (id) ) WITHOUT OIDS; ALTER TABLE test OWNER TO postgres; CREATE OR REPLACE RULE sum_op AS ON INSERT TO test DO UPDATE test SET c = new.a + new.b WHERE test.id = new.id; CREATE OR REPLACE RULE sum_op_up AS ON UPDATE TO test DO UPDATE test SET c = test.a + test.b WHERE test.id = new.id; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Having problems with a 25 million row table on 8.1.3
On 2006-04-25, Tony Caduto <[EMAIL PROTECTED]> wrote: > Hi, > I have a client who has a 25 million row table that is used to keep > track of financial security info. > So far it has worked great, but today someone wanted to get all the tax > codes(there are lot's of dupes) from the table. > So we tried this: > > select DISTINCT tax_code from warehouse.sec_trans > We let this run for 1/2 hour or so and canceled it. > > Then I tried select DISTINCT ON (tax_code) tax_code from warehouse.sec_trans > > same deal, had to cancel it. select tax_code from warehouse.sec_trans group by tax_code; Both of the DISTINCT variants rely on sorting. GROUP BY, on the other hand, can use a hash aggregate, which will be much more efficient where the number of distinct values is low. Of course it will still need to scan the whole table... -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Having problems with a 25 million row table on 8.1.3
# - Memory - shared_buffers = 15000 # min 16 or max_connections*2, 8KB each #temp_buffers = 1000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 10240# min 64, size in KB maintenance_work_mem = 32768# min 1024, size in KB #max_stack_depth = 2048 # min 100, size in KB Anyone have any ideas on how to get all the unique tax codes from this table? Push it to a cursor and select only pieces at a time? Josuha D. Drkae Thanks in advance :-) Tony ---(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 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Please comment on the following OpenFTS/tsearch2 issues!
This topic was originally posted to the OpenFTS-general list on April 24,
2006. There were no replies in about 22 hours so I'm reposting to this more
active list.
I'm investigating OpenFTS and tsearch2 to see if they provide enough
full-text searching features to be used in a new application. I've run into
a number of issues that I would appreciate feedback/comments/workarounds on.
1. While tsearch2 provides fairly complete boolean search expression support
with AND - &, OR - |, NOT - !, and grouping - (), OpenFTS appears to only
have support for ANDing search terms. Is there some reason it hasn't been
extended to support full tsearch2 search expressions? Has anyone modified
OpenFTS to do this?
2. Neither OpenFTS or tsearch2 support exact phrase matching. I've seen the
workaround to support matching a single exact phrase by modifying the WHERE
clause with textcolumn ~* "exact phrase". Does this give reasonable
performance? Has anyone implemented exact phrase matching in complex search
expressions like ("exact phrase1" AND term1) OR (NOT "exact phrase2" AND
"exact phrase3") ?
3. The following summarizes what I've read about performance and scalability
of OpenFTS and/or tsearch2:
a) don't expect OpenFTS/tsearch2 to perform/scale as well as dedicated
search engines like Lucene, http://lucene.apache.org/,
http://archives.postgresql.org/pgsql-general/2002-05/msg01156.php.
b) OR queries are slower than AND queries,
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/o
ptimization.html.
c) the design trade-offs favor online indexing instead of search
performance/scalability - see Full text search engine section in
http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo
d) there are a number of things you can do to improve performance - see the
thread starting at
http://sourceforge.net/mailarchive/message.php?msg_id=11444008.
Do you agree with this summary? If you are using either OpenFTS or tsearch2
in production, has the performance been acceptable? For my application I
could be looking at several million documents averaging about 3 pages each
(I only have ballpark figures at present).
4. If you are using either OpenFTS or tsearch2 in production why did you
choose OpenFTS over tsearch2 or vice versa? One of the advantages of
tsearch2 that I can see is that, once you have setup your database and
indexed your documents, you can talk to the database directly from your
application using SQL without needing to go through Perl first. This assumes
that you're ok with tsearch2 search expression syntax so you can use
functions like to_tsquery. It also assumes that you don't need sophisticated
exact phrase matching.
5. Are there any scripts, tools, add-ons, etc. that you can recommend?
---(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] Having problems with a 25 million row table on 8.1.3
On Tue, Apr 25, 2006 at 03:10:32PM -0500, Tony Caduto wrote: > Hi, > I have a client who has a 25 million row table that is used to keep > track of financial security info. > So far it has worked great, but today someone wanted to get all the tax > codes(there are lot's of dupes) from the table. > So we tried this: > > select DISTINCT tax_code from warehouse.sec_trans > We let this run for 1/2 hour or so and canceled it. What plan did it want to use (EXPLAIN query)? What version of PostgreSQL? How many results are you expecting? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] Having problems with a 25 million row table on 8.1.3
Hi, I have a client who has a 25 million row table that is used to keep track of financial security info. So far it has worked great, but today someone wanted to get all the tax codes(there are lot's of dupes) from the table. So we tried this: select DISTINCT tax_code from warehouse.sec_trans We let this run for 1/2 hour or so and canceled it. Then I tried select DISTINCT ON (tax_code) tax_code from warehouse.sec_trans same deal, had to cancel it. The server has a mirrored raid setup on two drives(yes I know this is not a good setup, but it's what they have) with 2GB of ram. I have the kernels (Linux CentOS 4.3) shared memory size set to: kernel.shmmax = 262144000 Here is the postgresql.conf entries for memory that have been changed: # - Memory - shared_buffers = 15000 # min 16 or max_connections*2, 8KB each #temp_buffers = 1000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 10240# min 64, size in KB maintenance_work_mem = 32768# min 1024, size in KB #max_stack_depth = 2048 # min 100, size in KB Anyone have any ideas on how to get all the unique tax codes from this table? Thanks in advance :-) Tony ---(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] Anyone install 8.1 on Debian Stable?
On Tue, 25 Apr 2006 10:06:12 -0700 Bill Moseley <[EMAIL PROTECTED]> wrote > Ok. So as someone that tried to understand pinning once and gave up, > do you pin the packages or use: > > apt-get -t sarge-backports install postgresql > > method? As per the instructions on the site - you use both :) .. add the stanza to /etc/apt/preferences, add the magic line to /etc/apt/sources.list, then run a command similar to the above (more likely you'll want apt-get -t sarge-backports install postgresql-8.1 libpg-perl > I guess it's in the docs, but how do you use psql with both versions? psql for both 7.x and 8.1 will use version 3 of the libpq API, so there's no issue like with mysql 4.0 versus 4.1's new auth system. You can have 7.x and 8.1 installed and running at the same time, but is there much point in keeping 7.x running thesedays? > And I assume I'll need to rebuild DBD::Pg -- and any tricks getting No rebuilding should be necessary for the same reasons above - however if you do need to build something, grab postgresql-server-dev-8.1 :) gdh ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] how can I check the error status??
Hi everybody! I'm migrating some sybase store procedures to postgres functions, I almost have found all that I need, however I dont know how to check the error status of the last transaction. I need to find in postgres the equivalent to the sybase @@error global variable. I mean, how can I translate this code in a postgres function?: insert into table (a,b,c) values (1,2,3) if (@@error =0) return 0 else return -900 Any advices? thanks in advance! Luis Paz. -- paz, amor y comprensión(1967-1994)
Re: [GENERAL] Anyone install 8.1 on Debian Stable?
On Tue, Apr 25, 2006 at 04:57:20PM +0100, Gavin Hamill wrote: > Very simple www.backports.org :) > > They have 8.1.3 and it works perfectly.. even the -contrib package is > there for cube/earthdistance, and the -dev package is there if you want > to compile Slony, etc. > > Follow http://www.backports.org/instructions.html for > /etc/apt/preferences, and then off you go... Ok. So as someone that tried to understand pinning once and gave up, do you pin the packages or use: apt-get -t sarge-backports install postgresql method? I guess it's in the docs, but how do you use psql with both versions? And I assume I'll need to rebuild DBD::Pg -- and any tricks getting it to link with the correct client library? It's the "managed" part of the Dreamhost server that has me worried. ;) Thanks, -- Bill Moseley [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Anyone install 8.1 on Debian Stable?
Anyone installed 8.1 on Stable? Did you build from source or use a backport? I've got a managed dedicated server at Dreamhost and trying to decide if building from source or using a backport is a better approach. I've had problems in the past with using backports on other servers (problems showed up when later upgrading the server). And I'm a bit concerned about a package conflicting with Dreamhost's management setup. (I had 7.4 installed and after some maintenance and a reboot the package was uninstalled.) And IIRC, the backports tend to bring in a number of dependency packages. If I build from source I need to update /etc/ld.so.conf to point to /usr/local (for linking with the driver) and install my own init.d scripts -- both of which I worry about in the managed environment. Plus, the socket, logs, pid are all not in the standard debian locations. So, anyone that has been through this have any advice? There isn't an init.d script in the distribution, right? -- Bill Moseley [EMAIL PROTECTED] ---(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] Database Selection
IvoD wrote: real experience and real enterprise applications. And last but not least - I must run db engine on Win platform (not Linux) and all the "success stories" assume Linux platform. So does somebody here know Well, for one thing... I have some experience with MySQL in that respect, and I know that migrating MySQL on Windows to MySQL on Linux (or a UNIX) causes trouble. MySQL stores its' tables as files on the file system, which is case insensitive on Windows and case sensitive on UNIX. If you didn't take a lot of care to use the same case in your queries and your table definitions, your queries will stop working once you migrate to UNIX... With respect to PostgreSQL on Windows, utf-8 encoding isn't natively supported on that platform, so you better don't use that for your database encodings. A fix is in the works, if I understand correctly, but AFAIK it isn't there yet. Mind though that I rarely use Windows; I use it almost exclusively for playing games. I am biased ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_dumpall: does not exist database
On Tue, 2006-04-25 at 17:49, Jim Buttafuoco wrote: > just for the record the following also works > > from the psql prompt: > jim=# create database "testing > jim"# "; > CREATE DATABASE > jim=# drop database "testing > jim"# " > jim-# ; > DROP DATABASE > jim=# > > and from the unix shell: > createdb "testing > " > dropdb "testing > " > > you need the double quotes in all cases > I'm afraid the OP had a CR and from a unix shell you'll get a LF for the new line. So for him it did not work. The trick with the wildcard is very useful in other similar situations too ;-) Cheers, Csaba. ---(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] Anyone install 8.1 on Debian Stable?
Bill Moseley wrote: Anyone installed 8.1 on Stable? Did you build from source or use a backport? Very simple www.backports.org :) They have 8.1.3 and it works perfectly.. even the -contrib package is there for cube/earthdistance, and the -dev package is there if you want to compile Slony, etc. Follow http://www.backports.org/instructions.html for /etc/apt/preferences, and then off you go... Cheers, Gavin. ---(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_dumpall: does not exist database
just for the record the following also works from the psql prompt: jim=# create database "testing jim"# "; CREATE DATABASE jim=# drop database "testing jim"# " jim-# ; DROP DATABASE jim=# and from the unix shell: createdb "testing " dropdb "testing " you need the double quotes in all cases -- Original Message --- From: Ari Kahn <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: Tom Lane <[EMAIL PROTECTED]>, Stephen Frost <[EMAIL PROTECTED]>, pgsql-general@postgresql.org Sent: Tue, 25 Apr 2006 11:19:57 -0400 Subject: Re: [GENERAL] pg_dumpall: does not exist database > I would call you an genius, but ... :-) > > Anyway, that worked and is the solution! > postgres=# update pg_database set datname='foodmart' where datname > like 'foodmart%'; > UPDATE 1 > postgres=# drop database foodmart; > DROP DATABASE > > THANKS! > > On Apr 25, 2006, at 11:13 AM, Jim Buttafuoco wrote: > > > > > why not just > > > > update pg_database set datname='foodmart' where datname like > > 'foodmart%'; > > > > > > > > -- Original Message --- > > From: Ari Kahn <[EMAIL PROTECTED]> > > To: Tom Lane <[EMAIL PROTECTED]> > > Cc: Stephen Frost <[EMAIL PROTECTED]>, pgsql-general@postgresql.org > > Sent: Tue, 25 Apr 2006 11:08:09 -0400 > > Subject: Re: [GENERAL] pg_dumpall: does not exist database > > > >> On Apr 25, 2006, at 10:51 AM, Tom Lane wrote: > >> > >>> Ari Kahn <[EMAIL PROTECTED]> writes: > You'll notice the database foodmart has a carriage return or new > line. I still can't figure out how to get rid of it though. > >>> > >>> Perhaps something along the lines of > >>> > >>> drop database "foodmart > >>> "; > >>> > >>> regards, tom lane > >> > >> I tried that. Doesn't work. > >> Using "od -a" I did determine that there is a CR (carriage return) in > >> the name. > >> > >> 0001240 sp sp sp | sp nl sp f o o d m a r > >> t cr > >> > >> ---(end of > >> broadcast)--- > >> TIP 2: Don't 'kill -9' the postmaster > > --- End of Original Message --- > > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings --- End of Original Message --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Database Selection
On Tue, 2006-04-25 at 01:26, IvoD wrote: > I read many web pages about both PostgreSQL and MySQL, I read also > "case studies" at pg web, but I prefer opinions of real users :-) I > installed both db engines on my PC three weeks ago and now I test it. > But I'm sure there should be "features" that I am not able to catch > (e.g. MIN() function speed problems in previous pg versions) and that > are not fixed yet. And therefore I ask all you - real users - about > real experience and real enterprise applications. And last but not > least - I must run db engine on Win platform (not Linux) and all the > "success stories" assume Linux platform. So does somebody here know > some good experience of "enterprise app" on M$ Win platform? > My "sixth sense" tells me that PostgreSQL is better than MySQL, > therefore for main app I prefer PostgreSQL; but I am in doubt to run > only one db engine for two databases. But my "inner space" tells me to > separate newsgroups system and company data system and run two > different db engines - in light of security (although only one db > engine looks promissing). I've combined your two posts here into one for easy answering. MySQL was originally just a SQL front end to isam files. While much has been done to it over time, it's roots still show, and in ways that I personally don't really like. For instance, way back when, in order to make it easy to import schema from real databases like Oracle, MySQL swallowed but ignored column level constraint syntax. So, creating a table like: create child_table (i1 int, parent_id int references parent(id)); resulted in no error, but NO foreign key either. To me, that's the worst possible failure mode, a silent one. This philosophy still exists today. While MySQL supports foreign key constraints via innodb tables, it only supports the syntax in a table level format (i.e. (i1 int, parent_id int, foreign key )) and if you give it to mysql in a column level, it ignores it but produces no error. The philosophy of PostgreSQL is the polar opposite. If something doesn't work right, PostgreSQL throws and error and refuses to proceed, expecting you to take care of the problem NOW. Which is better? I prefer the postgresql way, because the mysql way leads to madness. Imagine thinking you've got FKs when you don't, and finding out 2 years down the road that all your data is incoherent because your database tricked you into thinking it was doing FK when it wasn't. About the security thing. Security is a process, and you won't get it from using two different database engines. There are other considerations. You can run multiple versions of PostgreSQL on the same box if that's what you need. Each needs to use a different tcp/ip port. Creating two separate databases within a single PostgreSQL server is the way I'd do it. That way they're both on the same port, and use the same shared memory, but for all intents and purposes, they are separate databases. Note that you can edit the pg_hba.conf file to allow only certain users to connect to one db or another. I wouldn't pick MySQL or PostgreSQL or both based on the security issue. You could just as easily run both on separate boxes for REAL security anyway. ---(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] Partitioning rule not behaving as expected
Oh ok, that makes sense. Thanks for the explanation. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Partitioning rule not behaving as expected
I created a master table and three partition tables as per Postgres documentation. I also created three rules that match the check constraints. When I insert the values into the master table, the rules seem to be functional since the data is routed to the appropriate partition tables. However, data is also inserted into the master table which is not what I want. Is there a reason DO INSTEAD would not be evaluated as such? Here's the setup: CREATE TABLE zone_data ( zone_id int4 NOT NULL, measurement_start timestamptz NOT NULL ) WITHOUT OIDS TABLESPACE tss_tbs; CREATE TABLE zone_data_01_01 ( CONSTRAINT zone_data_01_01_measurement_start_check CHECK (measurement_start <= '2001-01-31 23:59:59-05'::timestamp with time zone) ) INHERITS (zone_data); CREATE TABLE zone_data_02_01 ( CONSTRAINT zone_data_02_01_measurement_start_check CHECK (measurement_start >= '2001-02-01 00:00:00-05'::timestamp with time zone AND measurement_start <= '2001-02-28 23:59:59-05'::timestamp with time zone) ) INHERITS (zone_data); CREATE TABLE zone_data_03_01 ( CONSTRAINT zone_data_03_01_measurement_start_check CHECK (measurement_start >= '2001-03-01 00:00:00-05'::timestamp with time zone AND measurement_start <= '2001-03-31 23:59:59-05'::timestamp with time zone) ) INHERITS (zone_data); CREATE OR REPLACE RULE zone_data_01_01_insert AS ON INSERT TO zone_data WHERE new.measurement_start <= '2001-01-31 23:59:59-05'::timestamp with time zone DO INSTEAD INSERT INTO zone_data_01_01 (zone_id, measurement_start) VALUES (new.zone_id, new.measurement_start); CREATE OR REPLACE RULE zone_data_02_01_insert AS ON INSERT TO zone_data WHERE new.measurement_start >= '2001-02-01 00:00:00-05'::timestamp with time zone AND new.measurement_start <= '2001-02-28 23:59:59-05'::timestamp with time zone DO INSTEAD INSERT INTO zone_data_02_01 (zone_id, measurement_start) VALUES (new.zone_id, new.measurement_start); CREATE OR REPLACE RULE zone_data_03_01_insert AS ON INSERT TO zone_data WHERE new.measurement_start >= '2001-03-01 00:00:00-05'::timestamp with time zone AND new.measurement_start <= '2001-03-31 23:59:59-05'::timestamp with time zone DO INSTEAD INSERT INTO zone_data_03_01 (zone_id, measurement_start) VALUES (new.zone_id, new.measurement_start); Here is the result: INSERT INTO zone_data (zone_id, measurement_start) VALUES (81, '2001-01-13 00:00:09-04'); INSERT INTO zone_data (zone_id, measurement_start) VALUES (81, '2001-02-13 00:00:09-04'); INSERT INTO zone_data (zone_id, measurement_start) VALUES (81, '2001-03-13 00:00:09-04'); Query returned successfully: 0 rows affected, 32 ms execution time. SELECT * FROM zone_data; zone_id measurement_start -- 81 2001-01-13 00:00:09-04 81 2001-02-13 00:00:09-04 81 2001-03-13 00:00:09-04 SELECT * FROM zone_data_01_01; zone_id measurement_start -- 81 2001-01-13 00:00:09-04 SELECT * FROM zone_data_02_01; zone_id measurement_start -- 81 2001-02-13 00:00:09-04 SELECT * FROM zone_data_03_01; zone_id measurement_start -- 812001-03-13 00:00:09-04 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] "save history" problem
Hi Martijn. Thanks for the tip on there being no real difference in the two paths (i.e., I'm not a Unix guy). Unfortunately, as I said in my original post, using the .psqlrc option to set the HISTFILE to the user directory didn't work either. After changing ownership of the /usr/local/pgsql/ to the postgres user, the history file mechanism works (i.e., the history is saved in the .psql_history file), but this message is reported: could not save history to file "/usr/local/pgsql//.psql_history": Unknown error: 0 There's no .psqlrc file so it's just using the default. Since the message is benign I'll just ignore it (like Jerry Levan does :) Thanks, David On 4/24/06 1:59 PM, "Martijn van Oosterhout" wrote: > On Sun, Apr 23, 2006 at 09:33:40PM -0400, David F. Johnson wrote: >> Greetings. >> >> Any ideas on how to resolve this problem: > > > >> test=# \q >> could not save history to file "/usr/local/pgsql//.psql_history": Permission >> denied > > I don't know about the platform, but shouldn't that refer to your home > directory? > >> Other installations of stable releases of 8.x.x did not have this problem, >> though I'm just now running it under Mac OS X Tiger (10.4.6). I'm guessing >> it's a Tiger issue but I don't know what to do about it. > > Something like: \set HISTFILE 'blah' should do it, check the docs. > >> That said, shouldn't >> /usr/local/pgsql//.psql_history >> be >> /usr/local/pgsql/.psql_history > > There's no difference (semantically) between the two... > > Have a nice day, ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Commit rules or Commit trigger
I am developing a temporal database and I have the necessity to control the integrity constraints befor the commit of the transiction.I cannot use the deferrable checking because the integrity constraints is a select and i cannot use the trigger or rule because the event parameter not support the commit event.You have some idea?Thanks -- __ Claudio Tognolo[EMAIL PROTECTED]Department of Computer Science - Verona, Italy -Fight back spam! Download the Blue Frog. http://www.bluesecurity.com/register/s?user=Y2xhdWRpby50b2dub2xvMzkzMQ%3D%3D
Re: [GENERAL] Database Selection
My "sixth sense" tells me that PostgreSQL is better than MySQL, therefore for main app I prefer PostgreSQL; but I am in doubt to run only one db engine for two databases. But my "inner space" tells me to separate newsgroups system and company data system and run two different db engines - in light of security (although only one db engine looks promissing). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Partitioning rule not behaving as expected
Since partitioning is just specialized inheritance, your zone_data table doesn't actually have any rows in it, its just using the default behavior of Postgres (from the manual: http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html): "In PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendant tables. The latter behavior is the default." Use the "ONLY" keyword to only select rows in zone_data (of which there are hopefully none): SELECT * from ONLY zone_data; HTH -Mike ---(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] Database Selection
I read many web pages about both PostgreSQL and MySQL, I read also "case studies" at pg web, but I prefer opinions of real users :-) I installed both db engines on my PC three weeks ago and now I test it. But I'm sure there should be "features" that I am not able to catch (e.g. MIN() function speed problems in previous pg versions) and that are not fixed yet. And therefore I ask all you - real users - about real experience and real enterprise applications. And last but not least - I must run db engine on Win platform (not Linux) and all the "success stories" assume Linux platform. So does somebody here know some good experience of "enterprise app" on M$ Win platform? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Transactions, PostgreSQL and MS Access front end.
Try to use ADO
Dim con as ADODB.Connection
set con = new ADODB.Connection
con.Open "DRIVER={PostgreSQL};
SERVER=ipaddress; port=5432;
DATABASE=dbname;
UID=username;PWD=password;"
con.BeginTrans
con.Execute "UPDATE accounts SET balance = balance + 100.00
WHERE acctnum = 12345"
con.Execute "UPDATE accounts SET balance = balance - 100.00
WHERE acctnum = 7534"
Con.CommitTrans
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dumpall: does not exist database
I would call you an genius, but ... :-) Anyway, that worked and is the solution! postgres=# update pg_database set datname='foodmart' where datname like 'foodmart%'; UPDATE 1 postgres=# drop database foodmart; DROP DATABASE THANKS! On Apr 25, 2006, at 11:13 AM, Jim Buttafuoco wrote: why not just update pg_database set datname='foodmart' where datname like 'foodmart%'; -- Original Message --- From: Ari Kahn <[EMAIL PROTECTED]> To: Tom Lane <[EMAIL PROTECTED]> Cc: Stephen Frost <[EMAIL PROTECTED]>, pgsql-general@postgresql.org Sent: Tue, 25 Apr 2006 11:08:09 -0400 Subject: Re: [GENERAL] pg_dumpall: does not exist database On Apr 25, 2006, at 10:51 AM, Tom Lane wrote: Ari Kahn <[EMAIL PROTECTED]> writes: You'll notice the database foodmart has a carriage return or new line. I still can't figure out how to get rid of it though. Perhaps something along the lines of drop database "foodmart "; regards, tom lane I tried that. Doesn't work. Using "od -a" I did determine that there is a CR (carriage return) in the name. 0001240 sp sp sp | sp nl sp f o o d m a r t cr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster --- End of Original Message --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dumpall: does not exist database
why not just update pg_database set datname='foodmart' where datname like 'foodmart%'; -- Original Message --- From: Ari Kahn <[EMAIL PROTECTED]> To: Tom Lane <[EMAIL PROTECTED]> Cc: Stephen Frost <[EMAIL PROTECTED]>, pgsql-general@postgresql.org Sent: Tue, 25 Apr 2006 11:08:09 -0400 Subject: Re: [GENERAL] pg_dumpall: does not exist database > On Apr 25, 2006, at 10:51 AM, Tom Lane wrote: > > > Ari Kahn <[EMAIL PROTECTED]> writes: > >> You'll notice the database foodmart has a carriage return or new > >> line. I still can't figure out how to get rid of it though. > > > > Perhaps something along the lines of > > > > drop database "foodmart > > "; > > > > regards, tom lane > > I tried that. Doesn't work. > Using "od -a" I did determine that there is a CR (carriage return) in > the name. > > 0001240 sp sp sp | sp nl sp f o o d m a r t cr > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster --- End of Original Message --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgreslog-semctl(7438339, 4, SETVAL, 0) failed:
"surabhi.ahuja" <[EMAIL PROTECTED]> writes: > <2006-04-19 01:13:25 IST%startup>FATAL: semctl(7438339, 4, SETVAL, 0) > failed: Invalid argument Kinda looks like something deleted your semaphores --- does "ipcs -s" show anything? Stopping and restarting the postmaster should fix it, if so, but you'll want to find out who ipcrm'd the semaphores and rap their knuckles. 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] pg_dumpall: does not exist database
On Apr 25, 2006, at 10:51 AM, Tom Lane wrote: Ari Kahn <[EMAIL PROTECTED]> writes: You'll notice the database foodmart has a carriage return or new line. I still can't figure out how to get rid of it though. Perhaps something along the lines of drop database "foodmart "; regards, tom lane I tried that. Doesn't work. Using "od -a" I did determine that there is a CR (carriage return) in the name. 0001240 sp sp sp | sp nl sp f o o d m a r t cr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dumpall: does not exist database
Ari Kahn <[EMAIL PROTECTED]> writes: > You'll notice the database foodmart has a carriage return or new > line. I still can't figure out how to get rid of it though. Perhaps something along the lines of drop database "foodmart "; regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_dumpall: does not exist database
On Apr 25, 2006, at 8:46 AM, Stephen Frost wrote: * Ari Kahn ([EMAIL PROTECTED]) wrote: That was a good idea. But this is not the case. You might try just looking at pg_database directly: select * from pg_database; Or (as someone else suggested) pipeing the output into a file which you can then look at. That was a good idea. At least I could see the name of the DB: datname| datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig | datacl ---++--+---+-- +--+---+--+-- +---+---+ postgres | 10 |0 | f | t | -1 | 10791 | 499 | 499 | 1663 | | barry | 16387 |0 | f | t | -1 | 10791 | 575 | 575 | 1663 | | foodmart | 16384 |0 | f | t| -1 | 10791 | 576 | 576 | 1663 | | You'll notice the database foodmart has a carriage return or new line. I still can't figure out how to get rid of it though. The other weird thing is that the database name does not appear during the psql query. It only appears when I pipe it out. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dumpall: does not exist database
* Ari Kahn ([EMAIL PROTECTED]) wrote: > That was a good idea. But this is not the case. You might try just looking at pg_database directly: select * from pg_database; Or (as someone else suggested) pipeing the output into a file which you can then look at. As a side-note: I'm a graduate student at GMU and will be at the main Fairfax campus this afternoon (probably starting around 3pm) and I've got classes there tonight (4:30pm and 7:20pm). I'd be happy to help anyone at GMU with Postgres. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] How to have a blind-superuser
Tom Lane wrote: If you don't trust your DBA, You should fire him/her... -- Until later, Geoffrey Any society that would give up a little liberty to gain a little security will deserve neither and lose both. - Benjamin Franklin ---(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_dumpall: does not exist database
Ari Kahn wrote: I think the issue is something like this though. Send the output of your database listing to a pipe through 'cat -evt' and see if you've got any unusual characters in the names of your databases: echo '\l' | psql template1 |cat -evt -- Until later, Geoffrey Any society that would give up a little liberty to gain a little security will deserve neither and lose both. - Benjamin Franklin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to have a blind-superuser
Steve Atkins wrote: So... you're not going to be able to do this _at_all_ from within the database. You're going to need an external solution, probably a hideous seteuid thing, if you really want to do this. And it's a really bad idea, so you probably don't want to. Thinking out loud on this one, so feel free to shoot it full of holes folks. I'm also assuming a UNIX based system. I don't suggest this solution, but it might be closer to what you're looking for. Create a root permission id that has the permissions to backup the database. Set the id's shell in /etc/passwd so that it executes a script that performs the backup. Again, I don't recommend this, but it might be a workable solution. Now, you login to the machine and the script is executed. Personally, I still maintain that if you can't trust the person that is expected to backup your database, you should get rid of them. -- Until later, Geoffrey Any society that would give up a little liberty to gain a little security will deserve neither and lose both. - Benjamin Franklin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to close dead connections immediately
> PG *will* close the connection immediately if it receives any > notification of connection drop from the client. If it's not seeing > one, that suggests something wrong in your network infrastructure. > I'd suggest fixing the problem rather than kluging the symptom. I have W2K server running also Exchange and other applications. I havent seen any problem with other appls. In every morning 8 persons from different locations over internet each open a single TCP connection to this Postgres 8.1.3 server using ODBC. They close my appl in midnight. Every day some clients receive randomly error form ODBC driver SQL state: 08S01 Error number: 27 Message: "Error while executing the query; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request." In fewer cases error message is "Could not send query to backend;Could not send query to backend" In this case my application sends disconnect command to ODBC driver and re-connects. Re-connect is OK. After first error I see that there are 2 Postgres processes running for that user. Clients are using ADSL connection over phone line maintained by phone company and I have no control over this. Doe to design request my application checks for duplicate connections and those dupl connections prevent working. Any idea what should I change? > You can reduce the TCP timeout settings if you are using PG 8.1 and an > operating system that supports it (I have no idea if Windows does or not). > I wouldn't recommend trying to make it "immediate" since then any > network instability breaks your application. 5 minutes or so might be > reasonable though. As you've noticed, the default timeouts are usually > upwards of an hour. (You should however ask yourself if you really know > more about TCP than the authors of the TCP specifications do.) My queries do not take more time than 5 minutes. In case of connection loss application re-connects automatically. What are reasonable values of the 3 TCP_KEEPALIVE_* parameters in this case ? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] postgreslog-semctl(7438339, 4, SETVAL, 0) failed:
I am using postges, 8.0.0 on that system , it seems that there are two databases, i am not sure if the same postmaster is used to connect to both the databases, i am seeing these logs in postgreslog <2006-04-19 01:13:25 IST%>LOG: connection received: host=[local] port=<2006-04-19 01:13:25 IST%authentication>LOG: connection authorized: user=sdc database=dbexpress<2006-04-19 01:13:25 IST%startup>FATAL: semctl(7438339, 4, SETVAL, 0) failed: Invalid argument<2006-04-19 01:13:25 IST%>LOG: connection received: host=[local] port=<2006-04-19 01:13:25 IST%authentication>LOG: connection authorized: user=sdc database=dbexpress<2006-04-19 01:13:25 IST%startup>FATAL: semctl(7438339, 4, SETVAL, 0) failed: Invalid argument<2006-04-19 01:13:25 IST%>LOG: connection received: host=[local] port=<2006-04-19 01:13:25 IST%authentication>LOG: connection authorized: user=sdc database=dbexpress<2006-04-19 01:13:25 IST%startup>FATAL: semctl(7438339, 4, SETVAL, 0) failed: Invalid argument what do they mean? thanks, regards Surabhi
Re: [GENERAL] isnumeric - checking if text variable is convertable to numeric
am 25.04.2006, um 2:01:49 -0700 mailte SunWuKung folgendes:
> I tried this but couldn't find out what would be the WHEN condition for
>
> Select Cast('asdf' as numeric)
> ERROR: invalid input syntax for type numeric
create or replace function check_numeric(varchar) returns bool as $$
declare
i numeric;
begin
i := $1::numeric;
return 't'::bool;
EXCEPTION WHEN invalid_text_representation then
return 'f'::bool;
end;
$$ language plpgsql immutable strict;
HTH, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===Schollglas Unternehmensgruppe===
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Starting Postgresql as windows service
Rajarajan,please check the postgresql logs witin pg_logyour data directory defaults to [programs]\Postgresql\8.1\datawhere [programs] is ~"Programs and Files" in US Windows, and "Programme" in German Windows. Propably there is some problem with postgresql.conf or access to your datafiles; the log may tellhthHaraldOn 4/25/06, Rajarajan <[EMAIL PROTECTED]> wrote: Hi I want to start psql as a windows service manually.How to do that?i was able to register the service but able to start it..when i start it ..i got the following message..---Services ---The PostgreSQL service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service. ---OK ---thanks in Advance..-- My only Superstition is belief in facts -- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-PostgreSQL - supported by a community that does not put you on hold
[GENERAL] Starting Postgresql as windows service
Hi I want to start psql as a windows service manually.How to do that?i was able to register the service but able to start it..when i start it ..i got the following message..---Services ---The PostgreSQL service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service. ---OK ---thanks in Advance..-- My only Superstition is belief in facts
Re: [GENERAL] isnumeric - checking if text variable is convertable to numeric
I tried this but couldn't find out what would be the WHEN condition for
Select Cast('asdf' as numeric)
ERROR: invalid input syntax for type numeric
regards,
Balázs
---(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] pg_dumpall: does not exist database
Ari Kahn <[EMAIL PROTECTED]> writes: > On Apr 25, 2006, at 3:25 AM, Tom Lane wrote: >> If so, I'm wondering if you've got a database with a carriage return >> embedded in the name, or something like that. > That was a good idea. But this is not the case. > postgres=# drop database "\n"; > ERROR: database "\n" does not exist > postgres=# drop database "\r"; > ERROR: database "\r" does not exist Those tests have little to do with what I'm worried about. Backslash isn't an escape character in SQL names, and even if it were, your tests only checked for databases named exactly "one newline" or "one carriage return", not for names comprising those characters along with others. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_dumpall: does not exist database
On Apr 25, 2006, at 3:25 AM, Tom Lane wrote: Ari Kahn <[EMAIL PROTECTED]> writes: I was trying to dump all my databases: su - postgres /usr/local/bin/pg_dumpall > /Volumes/Space/postgresql_060425.dump I get: " does not exist database "foodmartto database "foodmart ", exiting: pg_dump failed on database "foodmart I guess I had a database called foodmart at one time. However, it doesn't show up in the DB list: postgres=# \l List of databases Name | Owner | Encoding ---+--+--- barry | barry| SQL_ASCII | kahn | SQL_ASCII . Are you trying to accurately reproduce the formatting of what you see? Yes If so, I'm wondering if you've got a database with a carriage return embedded in the name, or something like that. That was a good idea. But this is not the case. postgres=# drop database "\n"; ERROR: database "\n" does not exist postgres=# drop database "\r"; ERROR: database "\r" does not exist I think the issue is something like this though. What PG version is this? 8.1 Ari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dumpall: does not exist database
Ari Kahn <[EMAIL PROTECTED]> writes: > I was trying to dump all my databases: > su - postgres > /usr/local/bin/pg_dumpall > /Volumes/Space/postgresql_060425.dump > I get: > " does not exist database "foodmartto database "foodmart > ", exiting: pg_dump failed on database "foodmart > I guess I had a database called foodmart at one time. However, it > doesn't show up in the DB list: > postgres=# \l >List of databases > Name | Owner | Encoding > ---+--+--- > barry | barry| SQL_ASCII > | kahn | SQL_ASCII > . Are you trying to accurately reproduce the formatting of what you see? If so, I'm wondering if you've got a database with a carriage return embedded in the name, or something like that. What PG version is this? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] remove another version of postgreSQL
"surabhi.ahuja" <[EMAIL PROTECTED]> writes: > how can i remove that version, i think it is installed from rpms rpm -e would be the thing, then. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] invalid memory alloc request size 1684370054
Brendan Duddridge <[EMAIL PROTECTED]> writes: > Next exception:SQL State:XX000 -- error code: 0 -- msg: ERROR: > invalid memory alloc request size 1684370054 > I'm not sure which memory setting I need to change to correct this. This looks more like a "corrupt data" problem than an "insufficient memory" problem --- specifically, I'd say a field's length word got overwritten with some ASCII text. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] pg_dumpall: does not exist database
I was trying to dump all my databases: su - postgres /usr/local/bin/pg_dumpall > /Volumes/Space/postgresql_060425.dump I get: " does not exist database "foodmartto database "foodmart ", exiting: pg_dump failed on database "foodmart I guess I had a database called foodmart at one time. However, it doesn't show up in the DB list: postgres=# \l List of databases Name | Owner | Encoding ---+--+--- barry | barry| SQL_ASCII | kahn | SQL_ASCII . . . There is that one nagging line though with no DB name: | kahn | SQL_ASCII How do I drop this no-name DB? Thanks, Ari ---(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
