Re: [GENERAL] Data on NAS / NFS

2007-07-30 Thread Joseph Shraibman
I've had trouble with NFS files on nfs filesystems disappearing for a second and reappearing. I had to add a retry loop with a delay in my code that does file reading. I wouldn't try running a production level postgres over nfs. ---(end of broadcast)--

Re: [GENERAL] index bloat WAS: reindexing pg_shdepend

2007-08-03 Thread Joseph Shraibman
Tom Lane wrote: Joseph S <[EMAIL PROTECTED]> writes: ... and when I notice that the tuplesperpage for the indexes is low (or that the indexes are bigger then the tables themselves) I know it is time for a VACUUM FULL and REINDEX on that table. If you are taking the latter as a blind must-be-w

Re: [GENERAL] Timezones in 8.2.7

2008-03-26 Thread Joseph Shraibman
After I sent my last email, a light bulb went off. I remembered a similar problem I had a while ago with parts of postgres not having read permission. Sure enough after I [EMAIL PROTECTED] /usr/local/pgsql]# chmod -R a+r * then restart postgres everything is fine. This is a IMHO a bug in th

Re: [GENERAL] Timezones in 8.2.7

2008-03-26 Thread Joseph Shraibman
Sorry, I didn't realize what you were asking. [local]:owl=# SHOW TimeZone; TimeZone -- EST5EDT (1 row) Tom Lane wrote: Joseph S <[EMAIL PROTECTED]> writes: Tom Lane wrote: Works for me ... what have you got TimeZone set to? /etc/localtime -> /usr/share/zoneinfo/US/Eastern Yo

Re: [GENERAL] Static functions

2008-10-03 Thread Joseph Shraibman
Umm r/static/stable -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Performance degradation 8.4 -> 9.1

2011-11-17 Thread Joseph Shraibman
This query is taking much longer on 9.1 than it did on 8.4. Why is it using a seq scan? => explain verbose SELECT status,EXISTS(SELECT 1 FROM eventlog e WHERE e.uid = ml.uid AND e.jobid = ml.jobid AND type = 4),EXISTS(SELECT 1 FROM eventlog e WHERE e.uid = ml.uid AND e.jobid = ml.jobid AND type =

Re: [GENERAL] Performance degradation 8.4 -> 9.1

2011-11-17 Thread Joseph Shraibman
On 11/17/2011 03:30 PM, Michael Glaesemann wrote: > > On Nov 17, 2011, at 14:24, Joseph Shraibman wrote: > >> This query is taking much longer on 9.1 than it did on 8.4. Why is it >> using a seq scan? > > Without seeing the table definition (including indexes)

[GENERAL] pg crash shortly after 9.1.1 -> 9.1.2 upgrade

2011-12-07 Thread Joseph Shraibman
All was fine until: LOG: statement: select "_devel".cleanupEvent('10 minutes'::interval, 'false'::boolean); ERROR: could not open file "base/16406/2072097_fsm": Permission denied STATEMENT: select "_devel".cleanupEvent('10 minutes'::interval, 'false'::boolean); WARNING: AbortTransaction w

Re: [GENERAL] pg crash shortly after 9.1.1 -> 9.1.2 upgrade

2011-12-07 Thread Joseph Shraibman
On 12/08/2011 12:54 AM, Tom Lane wrote: Joseph Shraibman writes: All was fine until: LOG: statement: select "_devel".cleanupEvent('10 minutes'::interval, 'false'::boolean); ERROR: could not open file "base/16406/2072097_fsm": Permission deni

[GENERAL] What is this vacuum doing?

2012-02-09 Thread Joseph Shraibman
I have a vacuum process that is sitting around and apparently not doing anything. It's been around over 2000 seconds and is eating up no cpu. It isn't waiting on a lock. Backtrace is this: #0 0x00367aed4ff7 in semop () from /lib64/libc.so.6 #1 0x005d2a83 in PGSemaphoreLock (se

[GENERAL] restore_command is not running on my standby

2012-03-13 Thread Joseph Shraibman
I have twice set up pg hot standbys ala the docs at http://www.postgresql.org/docs/9.1/interactive/hot-standby.html The third time I'm trying this I'm running into trouble. The first two times were with actual servers. This time I'm trying to set up two pg instances on my desktop for testing

[GENERAL] table permissions

2005-03-31 Thread Joseph Shraibman
Is there a function I can call to see if the current user has permissions on a certain table? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

[GENERAL] contrib/dbsize

2005-04-05 Thread Joseph Shraibman
How come relation_size() doesn't work on an index? ---(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 mailin

Re: [GENERAL] contrib/dbsize

2005-04-05 Thread Joseph Shraibman
2-5) (1 row) [local]:o=>select relation_size('pg_am_oid_index'); ERROR: "pg_am_oid_index" is an index Michael Fuhr wrote: On Tue, Apr 05, 2005 at 12:24:02PM -0400, Joseph Shraibman wrote: How come relation_size() doesn't work on an index? Could you define "doesn&#x

Re: [GENERAL] contrib/dbsize

2005-04-05 Thread Joseph Shraibman
Can I take the new .c file, do a make install, and have it work in 7.4.7 ? Michael Fuhr wrote: On Tue, Apr 05, 2005 at 01:25:01PM -0400, Joseph Shraibman wrote: local]:o=>select version(); vers

[GENERAL] psql performance

2005-04-14 Thread Joseph Shraibman
How come when I paste a large query into psql it starts off fast but then slows to a crawl eating up cpu just echoing the query back to me? I'm using psql 7.4.7 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appro

Re: [GENERAL] psql performance

2005-04-14 Thread Joseph Shraibman
--hopefully your query doesn't start with rm -rf / : ) Regards, Paul Tillotson Joseph Shraibman wrote: How come when I paste a large query into psql it starts off fast but then slows to a crawl eating up cpu just echoing the query back to me? I'm using psql 7.4.7 -

Re: [GENERAL] psql performance

2005-04-14 Thread Joseph Shraibman
cutes the query. Incidentally when I did that I only got back one row. What's up with that? Dann Corbit wrote: What is the query? What is the schema for the tables in the query? What is the cardinality of the tables? What does the planner say, when you do this: explain explain analyze http://www.

[GENERAL] UNION messing up sorting WAS: psql performance

2005-04-14 Thread Joseph Shraibman
Alvaro Herrera wrote: Incidentally when I did that I only got back one row. What's up with that? Try with "union all" instead of plain union. Talk about serendipity. The problem I've been struggling with for the last few hours has been why my query wasn't producing sorted output even though I

Re: [GENERAL] psql performance

2005-04-14 Thread Joseph Shraibman
nSSH_3.4p1 server:> rpm -qa | grep readline readline-devel-4.3-3 readline-4.3-3 [EMAIL PROTECTED] ~]$ rpm -qa | grep readline readline-4.3-13 readline-devel-4.3-13 Tom Lane wrote: Joseph Shraibman writes: It doesn't matter what the query is. The problem happens before it even runs the quer

Re: [GENERAL] psql performance

2005-04-14 Thread Joseph Shraibman
Joseph Shraibman wrote: So the question what is the difference between konsole and xterm that is causing cpu to be eating up on the server? Scratch that. I wasn't using the same input for both queries. Both of them are slow. I discovered that adding newlines to the query speeds things

Re: [GENERAL] psql performance

2005-04-14 Thread Joseph Shraibman
Uwe C. Schroeder wrote: Don't see a problem pasting this one. Neither to a local nor to a remote ssh (running psql certainly). This is 7.4.7 on redhat and mandrake linux'es I'd suspect it has nothing to do with psql. Can you paste that into a normal ssh / terminal ? It is slow just pasting to the

[GENERAL] Cursor not getting all rows

2005-05-17 Thread Joseph Shraibman
I'm running: PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) I do this: BEGIN; SELECT count(*) FROM u, d WHERE u.id = d.id AND ... ; DECLARE cname CURSOR FOR SELECT u.field, d.field FROM u, d WHERE u.id = d.id AND ... ; At the end of the f

[GENERAL] TIP 9

2005-05-17 Thread Joseph Shraibman
Doesn't this need to be ammened to say "before 8.0"? ---(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 ---(end of broadcast)---

Re: [GENERAL] Cursor not getting all rows

2005-05-17 Thread Joseph Shraibman
Scott Marlowe wrote: Only if you set transaction isolation to serializable. So am I getting data that was updated up until the time of the FETCH or the DECLARE CURSOR? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unreg

[GENERAL] blocking INSERTs

2005-06-07 Thread Joseph Shraibman
I want to do the following: BEGIN; SELECT ... FROM table WHERE a = 1 FOR UPDATE; UPDATE table SET ... WHERE a = 1; if that resturns zero then INSERT INTO table (...) VALUES (...); END; The problem is that I need to avoid race conditions. Sometimes I get primary key exceptions on the INSERT.

[GENERAL] table locking and SELECT FOR UPDATE

2005-07-11 Thread Joseph Shraibman
How come when a share lock is held and update can't be done on the table, but a SELECT FOR UPDATE can be done? I can't SELECT FOR UPDATE the same row in two transactions, but I can SELECT FOR UPDATE a row that I will won't be able to update because the other table is held in a SHARE lock. -

Re: [GENERAL] table locking and SELECT FOR UPDATE

2005-07-11 Thread Joseph Shraibman
em is that one transaction can lock the rows, and the other transaction locks the table, which leads to a deadlock. Tom Lane wrote: Joseph Shraibman writes: How come when a share lock is held and update can't be done on the table, but a SELECT FOR UPDATE can be done? I can't SE

[GENERAL] free space map settings

2005-07-18 Thread Joseph Shraibman
INFO: free space map: 195 relations, 96448 pages stored; 417104 total pages needed DETAIL: Allocated FSM size: 1000 relations + 9 pages = 588 kB shared memory. I'm confused, do I need to set my fsm settings to 96448 or 417104 based on this output? Are fsm settings updated during a vac

[GENERAL] Problem with text_pattern_ops

2005-07-26 Thread Joseph Shraibman
I have this index: "directory_lower_username_seg_key" unique, btree (lower(username) text_pattern_ops, seg) ... but my query refuses to use that index. [local]:owl=>explain select * from directory where lower(username) = 'jks@selectacast.net'; QUERY PLAN ---

Re: [GENERAL] Problem with text_pattern_ops

2005-07-26 Thread Joseph Shraibman
Madison Kelly wrote: Joseph Shraibman wrote: What happens if you 'SET enable_seqscan TO OFF' and try the query again? I've had a couple of instances where the planner just doesn't like my index but once it is told to use it I get a nice performance boost. It

Re: [GENERAL] Problem with text_pattern_ops

2005-07-26 Thread Joseph Shraibman
Stephan Szabo wrote: On Tue, 26 Jul 2005, Joseph Shraibman wrote: I have this index: "directory_lower_username_seg_key" unique, btree (lower(username) text_pattern_ops, seg) ... but my query refuses to use that index. text_pattern_ops is an opclass for doing LIKE queries

Re: [GENERAL] Problem with text_pattern_ops

2005-07-26 Thread Joseph Shraibman
Stephan Szabo wrote: It is for the operators ~<~, ~<=~, ~=~, ~>=~, ~>~ (for like optimization). The docs seem to say that it does a character by character comparison rather than one using the collation thus being better for pattern matching. I'd think letting it do <, <=, =, >=, > would have i

[GENERAL] What happens when wal fails?

2005-08-03 Thread Joseph Shraibman
If I put the pg_xlog directory on its own disk, then that disk fails, does that mean the postgres is hosed or does it just mean that postgres no longer safe from a power outage? Does pg detect a problem with the wal and then call fsync() on the database files if wal isn't working? ---

[GENERAL] pg_locks.transaction field type

2005-08-17 Thread Joseph Shraibman
I have a method in my rmi server that takes a query and returns an Object[][]. I had this query: SELECT (select relname from pg_catalog.pg_class where relfilenode = relation) as relname, * FROM pg_locks; After upgrading from 7.4 to 8.0 I was getting this problem: WARNING: Servlet.service() f

Re: [GENERAL] [JDBC] pg_locks.transaction field type

2005-08-17 Thread Joseph Shraibman
Oliver Jowett wrote: Joseph Shraibman wrote: Is it a jdbc bug that is returning the answer as org.postgresql.util.PGobject instead of some kind of Number? The column's type is 'xid' which the driver doesn't currently handle, so it gets put into the "wrap it in P

Re: [GENERAL] [JDBC] pg_locks.transaction field type

2005-08-17 Thread Joseph Shraibman
So basically what needs to be changed is TypeInfoCache.java & Oid.java Alvaro Herrera wrote: On Thu, Aug 18, 2005 at 03:55:43PM +1200, Oliver Jowett wrote: Joseph Shraibman wrote: The column's type is 'xid' which the driver doesn't currently handle, so it gets

[GENERAL] backends and pg_stat_activity

2005-10-17 Thread Joseph Shraibman
Is there a way to get the ip address of the connections listed in pg_stat_activity? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your

[GENERAL] VACUUM and open transactions

2006-10-19 Thread Joseph Shraibman
I'm running postgres 8.0.8. I have a table that is updated very rapidly, so I vacuum it every 10 minutes. The problem is that I sometimes have transactions that hang out for a long time without doing anything. These transactions are preventing VACUUM from cleaning up tuples that were created

[GENERAL] How to crash postgres using savepoints

2006-11-15 Thread Joseph Shraibman
See example below. At the very least the documentation needs to tell users that savepoints use shared memory, and the cofusing HINT string needs to be changed to something more useful. When run on a machine running 8.2b3 version: PostgreSQL 8.2beta3 on i686-pc-linux-gnu, compiled by GCC gcc

Re: [GENERAL] Index vacuum improvements in 8.2

2007-01-08 Thread Joseph Shraibman
8.0.x has the problem that VACUUM FULL on a table does not reclaim space from the indexes, and I have to issue a separate REINDEX command. Has this been fixed in later versions? ---(end of broadcast)--- TIP 5: don't forget to increase your free s

Re: [GENERAL] daylight savings patches needed?

2007-02-12 Thread Joseph Shraibman
Robert Treat wrote: If you are running pre-8.0 versions you need to update your operating system (as you indicated). If you running an any 8.x version, you need to be on the most current corresponding 8.x.y release. So what happens if you have an old os with a new postgresql install? Will C

Re: [GENERAL] redhat debug info

2007-03-28 Thread Joseph Shraibman
Well 1) I'd like to avoid the performance penalty for including debug symbols and 2) I already built the binary and it is running on a live system, and I'd like to get debug symbols w/o restarting. Peter Eisentraut wrote: Am Mittwoch, 28. März 2007 03:00 schrieb Joseph S: I don't use rpms, I

[GENERAL] swap storm created by 8.2.3

2007-05-22 Thread Joseph Shraibman
I'm running: PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3) My memory settings are: work_mem = 64MB shared_buffers = 128MB temp_buffers = 32MB I ran a query that was "SELECT field, count(*) INTO TEMP temptable" and it grew to be 10gi

Re: [GENERAL] swap storm created by 8.2.3

2007-05-25 Thread Joseph Shraibman
Richard Huxton wrote: Joseph Shraibman wrote: I'm running: PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3) My memory settings are: work_mem = 64MB shared_buffers = 128MB temp_buffers = 32MB I ran a query that was "SE

Re: [GENERAL] swap storm created by 8.2.3

2007-05-25 Thread Joseph Shraibman
Richard Huxton wrote: Joseph Shraibman wrote: I ran a query that was "SELECT field, count(*) INTO TEMP temptable" and it grew to be 10gig (as reported by top) What was the real query? First I selected 90634 rows (3 ints) into the first temp table, then I did "select i

[GENERAL] high unicode chars

2005-01-18 Thread Joseph Shraibman
Has this error from 7.4.6 been fixed in 8.0? ERROR: Unicode characters greater than or equal to 0x1 are not supported ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

[GENERAL] SAVEPOINT performance

2005-12-21 Thread Joseph Shraibman
Is there any performance impact of releasing savepoints? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[GENERAL] Idea for vacuuming

2006-06-22 Thread Joseph Shraibman
I'm running a 8.0 database. I have a very large log table that is rarely updated or deleted from. The nightly vacuum does not know this, and spends a lot of time on it, and all its indexes. My RFE: When vacuuming a table, pg should try to vacuum the primary key first. If that results in 0 r

Re: [GENERAL] Idea for vacuuming

2006-06-23 Thread Joseph Shraibman
I like to make sure the vacuum takes place during off peak times, which is why I don't use autovacuum. Jim Nasby wrote: On Jun 22, 2006, at 7:12 PM, Joseph Shraibman wrote: I'm running a 8.0 database. I have a very large log table that is rarely updated or deleted from. The nigh

Re: [GENERAL] Idea for vacuuming

2006-06-25 Thread Joseph Shraibman
The verbose output shows the table being vacuumed last. Maybe it changed after 8.0 Greg Stark wrote: Jim Nasby <[EMAIL PROTECTED]> writes: My RFE: When vacuuming a table, pg should try to vacuum the primary key first. If that results in 0 recovered entries, then assume the table has no up

Re: [GENERAL] RAID + PostgreSQL?

2006-06-27 Thread Joseph Shraibman
I'm running 8.0.8 on a raid 5 over 13 disks, and select performance on a query that needs to join two large tables is very bad. top shows pg using 2 to 4 percent cpu. Doing a query on one big table uses 30 to 45 percent cpu. This is RHEL 4 running kernel 2.6.9-22.ELsmp, using an LSI fiber ch

Re: [GENERAL] RAID + PostgreSQL?

2006-07-06 Thread Joseph Shraibman
Alex Turner wrote: As an aside note, I would consider a 13 disk RAID 5 a high risk solution. If you loose just two drives of 13 at the same time, your data is all gone. If you loose one drive, your array goes into degraded mode and your read and write performance goes to hell, and your machi

Re: [GENERAL] VACUUM FULL versus CLUSTER ON

2006-07-12 Thread Joseph Shraibman
NM I found the documentation. Joseph Shraibman wrote: Joshua D. Drake wrote: Secondly this sounds like a perfect time for you to consider upgrading to 8.1 and making use of table partitioning. How does that work, exactly? ---(end of broadcast

Re: [GENERAL] VACUUM FULL versus CLUSTER ON

2006-07-12 Thread Joseph Shraibman
Joshua D. Drake wrote: Secondly this sounds like a perfect time for you to consider upgrading to 8.1 and making use of table partitioning. How does that work, exactly? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ?

[GENERAL] How come index isn't being used when query by function return value?

2006-07-13 Thread Joseph Shraibman
db:db=>explain select * from elog where id = eds('2006-01-01'); QUERY PLAN --- Seq Scan on elog (cost=0.00..1894975.10 rows=1 width=204) Filter: (id = eds('2006-01-0

Re: [GENERAL] How come index isn't being used when query by function return

2006-07-13 Thread Joseph Shraibman
It is STABLE, which I finally figured out. I had to find section 31.6 of the docs, which is nowhere near the part about writing functions. Merlin Moncure wrote: On 7/13/06, Joseph Shraibman wrote: db:db=>explain select * from elog where id = eds('20

[GENERAL] shortcircuit logic in plpsql

2006-07-18 Thread Joseph Shraibman
I'm trying to do this: IF TG_OP = \'INSERT\' OR (TG_OP = \'UPDATE\' AND OLD.status <> NEW.status) THEN ..but pg is complaining: ERROR: record "old" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function "set_dir_count" lin

[GENERAL] trigger speed

2006-08-15 Thread Joseph Shraibman
I have a trigger that updates a count table, based on status. The count table looks like this: key status count a1 300 a2 400 b1 100 b2 200 The problem is that for large updates when I do "UPDATE table SET status = 1 WHERE status = 2 and key =

Re: [GENERAL] Upcoming events

2004-01-22 Thread Joseph Shraibman
Bruce Momjian wrote: I have events in the next few weeks in New York City, Copenhagen, Paris, and Atlanta. Check the News section on the web site for more information. I will also be in Amsterdam February 2-3, though I have no public events scheduled there. You mean the events section, don't you?

[GENERAL] Does SET STATISTICS lock the table?

2004-01-31 Thread Joseph Shraibman
Does ALTER TABLE ALTER SET STATISTICS 100; lock the table? I just tried to do that while a query is running and the ALTER is hanging. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

[GENERAL] performance problems: join conditions

2004-03-04 Thread Joseph Shraibman
I have a query like this: SELECT ... FROM u, d WHERE d.ukey = u.ukey AND AND (d.status = 3 OR (u.status = 3 AND d.status IN(2,5))); explain shows: -> Aggregate (cost=126787.04..126787.04 rows=1 width=4) -> Hash Join (cost=39244.00..126786.07 rows=387 width=4)

[GENERAL] dump / restore questions

2004-05-23 Thread Joseph Shraibman
I recently dumped and restored a 7.4.2 database. It took 30 minutes for the data to load (6 gig) and 45 for the indexes to be created (3 gig). Why are the primary keys created after the other indexes? That means that the table data had been evicted from the cache and has to be reloaded. What

Re: [GENERAL] VACUUM Question

2004-06-04 Thread Joseph Shraibman
Greg Stark wrote: Alex <[EMAIL PROTECTED]> writes: There won't be anything to VACUUM after the insert, but perhaps you still want to run ANALYZE. Note that a plain ANALYZE uses a statistical sample which is much faster, whereas VACUUM ANALYZE has to look at every record anyways so it's slower but

Re: [GENERAL] index with LIKE

2004-06-10 Thread Joseph Shraibman
Martijn van Oosterhout wrote: The classic issue is what encoding are the databases. Anything other than C and like won't use indexes. Unless you use text_pattern_ops. See http://www.postgresql.org/docs/7.4/static/indexes-opclass.html I think this needs to be in the faq. -

Re: [GENERAL] performance of IN (subquery)

2004-08-27 Thread Joseph Shraibman
Tom Lane wrote: Greg Stark <[EMAIL PROTECTED]> writes: I'm not about to run analyze in the middle of the data generation (which wouldn't work anyways since it's in a transaction). Since 7.3 or 7.4, you *can* run ANALYZE in the middle of a transaction. The cached-plan business is a problem, I agre

Re: [GENERAL] performance of IN (subquery)

2004-08-27 Thread Joseph Shraibman
ly be executed. Thus, the parsing, rewriting, and planning stages are only performed once, instead of every time the statement is executed. Markus Bertheau wrote: On Fri, 27 Aug 2004 11:09:26 -0400, Joseph Shraibman <[EMAIL PROTECTED]> wrote: How does EXECUTE solve the cached-plan business?

[GENERAL] deadlock with vacuum full on 7.4.5

2004-10-12 Thread Joseph Shraibman
I have a table that is usually really small (currently 316 rows) but goes through spasams of updates in a small time window. Therefore I have a vacuum full run every hour on this table. Last night one of these vacuum fulls deadlocked with a query on this table. Both were stuck doing nothing u

Re: [GENERAL] deadlock with vacuum full on 7.4.5

2004-10-12 Thread Joseph Shraibman
Why then when I did a kill -INT on the vacuuming backends did everything unfreeze? Tom Lane wrote: Joseph Shraibman <[EMAIL PROTECTED]> writes: Last night one of these vacuum fulls deadlocked with a query on this table. Both were stuck doing nothing until I did a kill -INT on the ba

Re: [GENERAL] deadlock with vacuum full on 7.4.5

2004-10-12 Thread Joseph Shraibman
That is what I wanted to know, how to get the evidence for next time. Tom Lane wrote: Joseph Shraibman <[EMAIL PROTECTED]> writes: Why then when I did a kill -INT on the vacuuming backends did everything unfreeze? You could have had other stuff backed up behind the VACUUM FULL lock re

Re: [GENERAL] Re: Backups WAS: 2 gig file size limit

2001-07-09 Thread Joseph Shraibman
Doug McNaught wrote: > > [HACKERS removed from CC: list] > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > > Doing a dumpall for a backup is taking a long time, the a restore from > > the dump files doesn't leave the database in its original state. C

Re: [GENERAL] Postgresql revisited. Some questions about the product

2001-07-12 Thread Joseph Shraibman
Bruce Momjian wrote: > > > > > 6. Can databases be partitioned over multiple physical files. Can > > You have to use symlinks to move to other file systems. That's not what he asked. He asked about files, and the answer is yes. -- Joseph Shraibman [EMAIL PRO

Re: [GENERAL] LARGE db dump/restore for upgrade question

2001-08-14 Thread Joseph Shraibman
Philip Crotwell wrote: > Hi > > I have a very large database of seismic data. It is about 27 Gb now, and > growing at about the rate of 1 Gb every 3-4 days. I am running Out of curiosity, how long does it take you to vacuum that? -- Joseph Shraibman [EMAIL PROTECTED] Increa

[GENERAL] dump/restore failing in 7.1.2

2001-08-14 Thread Joseph Shraibman
postgres. CREATE DATABASE You are now connected to database as user postgres. psql:/local/dumpall-8-14:22: \connect: FATAL 1: user "" does not exist -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of

Re: [GENERAL] where is PostgreSQL log ?!?!

2001-08-20 Thread Joseph Shraibman
log/pgsql which is > 0 bytes . > Am I open the right log file of postgresql ?? Thx for reading this > message !! > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[GENERAL] index naming conventions

2001-09-05 Thread Joseph Shraibman
playpen=# create table jm( playpen(# jid int NOT NULL, playpen(# mid int , playpen(# UNIQUE(jid, mid) playpen(# ); NOTICE: CREATE TABLE/UNIQUE will create implicit index 'jm_jid_key' for table 'jm' CREATE Why isn't the index created called 'jm_jid_mid_key&#x

[GENERAL] indexes on columns and functions

2003-07-02 Thread Joseph Shraibman
You can CREATE INDEX on multiple columns, and you can CREATE INDEX on a functions, but can you create an index on multiple columns and functions? For example if I want to create an index on lower(textfield), intfield ---(end of broadcast)--- TIP 1

[GENERAL] where is the list of companies that provide commercial support?

2003-07-08 Thread Joseph Shraibman
The link at the end of 1.6 in the faq does not work. ---(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 mai

[GENERAL] DISTINCT vs EXISTS performance

2003-07-08 Thread Joseph Shraibman
I have a query where I want to select the usertable records that have a matching entry in an event table. There are two ways to do this. 1) SELECT COUNT(DISTINCT u.uid) FROM usertable u, eventlog e WHERE u.uid = e.uid AND e.type = XX; 2) SELECT COUNT(u.uid) FROM usertable u WHERE EXISTS(SELECT

Re: [GENERAL] like performance w/o wildcards.

2003-08-03 Thread Joseph Shraibman
Hmm. I didn't work for me. I'll try and figure this out. Tom Lane wrote: Joseph Shraibman <[EMAIL PROTECTED]> writes: I notice in 7.3.3 the planner can't tell when a LIKE has no wildcards and is in reality an '='. Is this an easy change to make? On w

Re: [GENERAL] like performance w/o wildcards.

2003-08-04 Thread Joseph Shraibman
Richard Huxton wrote: On Monday 04 August 2003 04:29, Joseph Shraibman wrote: Hmm. I didn't work for me. I'll try and figure this out. Tom Lane wrote: Joseph Shraibman <[EMAIL PROTECTED]> writes: I notice in 7.3.3 the planner can't tell when a LIKE has no wildcards

Re: [GENERAL] FATAL: Socket command type A unknown

2003-08-28 Thread Joseph Shraibman
Natrually right after I sent this post I found the problem. The String was '[EMAIL PROTECTED] ... which is the old problem with jdbc and embedded 0 charachters. In what version of the driver was that fixed? Joseph Shraibman wrote: I'm having a wierd problem with pg 7.3.3 PostgreSQ

[GENERAL] FATAL: Socket command type A unknown

2003-08-28 Thread Joseph Shraibman
I'm having a wierd problem with pg 7.3.3 PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) I have a bunch of inserts being done by JDBC. One of them if causing a problem. LOG: query: INSERT INTO mailtextlog (mlid,tlog,cdate) VALUES(9

Re: [GENERAL] why does count take so long?

2003-09-09 Thread Joseph Shraibman
Tom Lane wrote: Something to think about for 7.5 (too late for 7.4 I fear). What about 7.4.1? This wouldn't affect how data is stored on disk, which is what would keep an upgrade out of a minor release, right? ---(end of broadcast)--- TIP 1: subsc

Re: [GENERAL] Unix domain instead of TCP socket connections with

2003-09-09 Thread Joseph Shraibman
The tomcat developers were working on a hybrid system, that would use unix sockets via JNI for local connections, but I'm not sure what happened to it. Or maybe they used a named pipe instead? I really don't know. ---(end of broadcast)--- TIP 7:

[GENERAL] selecting random rows

2003-09-12 Thread Joseph Shraibman
Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY random() is that is has to get all the rows from the table before the results are returned. ---(end of broadcast)--- TIP 9: the planner will ignore your desire t

Re: [GENERAL] State of Beta 2

2003-09-22 Thread Joseph Shraibman
Tom Lane wrote: Kaare Rasmussen <[EMAIL PROTECTED]> writes: Not sure about your position here. You claimed that it would be a good idea to freeze the on disk format for at least a couple of versions. I said it would be a good idea to freeze the format of user tables (and indexes) across multipl

Re: [GENERAL] PostgreSQL versus MySQL

2003-09-22 Thread Joseph Shraibman
Ron Johnson wrote: Who's want to build a 40-year-old rocket? You'd be surpised. Some plans for replacing the shuttle call for going back to Saturn V's. NASA went with the shuttle design in the first place because resusable was supposed to be cheaper, but it hasn't turned out that way. --

Re: [GENERAL] Data type for serial during constraint?

2000-05-30 Thread Joseph Shraibman
"Rob S." wrote: > > Hiya, > > I've never sent mail to a list before, so i hope i get it right =) > > Downloaded and installed Postgres today for Slackware, for the first time. > Kudos to the PG team; we still can't believe it went so well. We were up, > with JDBC access in about 20 minutes. O

Re: [GENERAL] Postgresql usage clip.

2000-05-30 Thread Joseph Shraibman
Jan Wieck wrote: > > Ron Chmara wrote: > > "Brett W. McCoy" wrote: > > > MySQL is great for small websites with small budgets with read-only data > > > or data that doesn't change often. It doesn't scale very well at all, and > > > for larger sites it really falls apart without anyy referential

Re: [GENERAL] Postmaster won't -HUP

2000-05-31 Thread Joseph Shraibman
In version 7.0 postgres waits for all clients to close their connections before exiting. Before it just quit. Jerry Lynde wrote: > > Hello out there, > > I'm having a problem with a production server. Actually, there are two > problems. The semi-trivial problem is that Postgres won't d

Re: [GENERAL] Postmaster won't -HUP

2000-06-01 Thread Joseph Shraibman
Jerry Lynde wrote: > > At 12:11 PM 6/1/00 -0500, Ed Loehr wrote: > >Jerry Lynde wrote: > > > > > > As for the query I'm running, it was simply select * from > > bigtable (about > > > 2-300k lines) where > > > firstname= > fname> and > > >

[GENERAL] make install for docs in 7.0.1 won't work.

2000-06-01 Thread Joseph Shraibman
I did a make install in doc in the 7.0 release, but now when I try it: make all make[1]: Entering directory `/tmp/postgresql-7.0.1/doc' make[1]: *** No rule to make target `admin', needed by `all'. Stop. make[1]: Leaving directory `/tmp/postgresql-7.0.1/doc' make: *** [install] Error 2

Re: [GENERAL] query optimiser changes 6.5->7.0

2000-06-01 Thread Joseph Shraibman
Tom Lane wrote: > > Also, 7.0.1, propagating now to an archive near you, contains some > fudge-factor twiddling to make it more willing to choose an indexscan. > We shall soon find out whether that made things better or worse for > typical uses... > > regards, tom lane O

Re: [GENERAL] raw devices

2000-07-10 Thread Joseph Shraibman
This seems to be coming up so often perhaps somebody should put it in the FAQ. The reason it doesn't support raw devices, according to a moderately long discussion not too long ago, is that with modern operating systems it is better to let the OS handle the low level stuff. Yves Dorfsman wrote:

Re: [GENERAL] Using the JDBC Driver in version 7.02

2000-07-10 Thread Joseph Shraibman
You have to format your url properly so the java runtime can figure out what driver to use for the connection. Justin Jaynes wrote: > > Hello. I am using Postgres 7.02 on my redhat linux box. I have installed > the JDK and a JSP engine. > > I have also copied the JDBC level 4 Drivers that cam

[GENERAL] select for update not locking properly.

2000-07-12 Thread Joseph Shraibman
I have a program that does this: BEGIN; SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE; UPDATE mytable SET mystring = '' WHERE x = 3 AND y = 4; END; But the locking isn't working properly. I do something that should cause 3 different threads to try and do that append, and the fir

Re: [GENERAL] select for update not locking properly.

2000-07-12 Thread Joseph Shraibman
And I forgot to mention my version is: PostgreSQL 7.0.1 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 Joseph Shraibman wrote: > > I have a program that does this: > BEGIN; > SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE; > > UPDATE mytable SET mystrin

Re: [GENERAL] How I can undelete recodes?

2000-07-31 Thread Joseph Shraibman
Herbert Liechti wrote: > > [EMAIL PROTECTED] wrote: > > > > > > > How I can recover this table. > > > > > > > > Please help me. > > > > > > > > > Put in your backup tape and restore. > > > > > > > > > > Unfortunately, I have not the backup tape. :( > > see http://www.rocksoft.com/taobackup/ >

Re: [GENERAL] Error building JDBC Driver

2000-10-16 Thread Joseph Shraibman
> > > > Any ideas? Thanks. > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your ba

  1   2   >