Re: [GENERAL] Error size varchar

[Adam Kavan]

At 05:06 PM 10/29/03 +, Edwin Quijada wrote:

wHAT IS unconstrained varchar???


Define the column as just varchar.  This allows a string of any 
length.  Then have a trigger truncate it after it is inserted.

--- Adam Kavan
--- [EMAIL PROTECTED]


---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Odd behaviour -- Index scan vs. seq. scan

[Adam Kavan]

explain delete from game where gameid = 1000;
Index Scan using game_pkey on game  (cost=0.00..3.14 rows=1 width=6)
explain delete from game where gameid  1000;
Seq Scan on game  (cost=0.00..4779.50 rows=200420 width=6)
explain delete from game where gameid between 1000 and 2000;
Index Scan using game_pkey on game  (cost=0.00..3.15 rows=1 width=6)
How's that possible?  Is it purposely done like this, or
is it a bug?  (BTW, Postgres version is 7.2.3)


Postgres thinks that for the = line there will only be 1 row so t uses an 
index scan.  Same thing for the between.  However it thinks that there are 
200420 rows below 1000 and decides a seq scan would be faster.  You can run 
EXPLAIN ANALYZE to see if its guesses are correct.  You can also try SET 
enable_seqscan = FALSE; to see if it is faster doing an index scan.  If it 
is faster to do an index scan edit your postgres.conf file and lower the 
cost for a random tuple,  etc.

--- Adam Kavan
--- [EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Rules question

[Adam Kavan]

I am starting to work with rules.  I think I have them down but the 
folowing one is giving me grief.  It tells me that it has rewritten 100 
times and probably has looped.  The columns ID and Location make up the 
primary key so I am sure that the result update should only effect one 
row.  Anyone know what I am missing?

CREATE OR REPLACE RULE set_changed AS ON UPDATE TO GameInformation 
WHERE NEW.Changed =FALSE DO UPDATE GameInformation SET Changed = TRUE 
WHERE Location = NEW.Location AND ID = NEW.ID;

--- Adam Kavan
--- [EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] pg_autovacuum

[Adam Kavan]

At 02:25 PM 9/4/03 -0400, Jan Wieck wrote:
H ... would need to take a look at the code ... but a wild guess would
be the communication between the backends and the collector daemon. I'm
not sure if the UDP crap^H^H^H^Hstuff I used in there is IPV6 safe. What
version of PostgreSQL was that?
On Matthew's advice I've posted my problem to Hackers.

--- Adam Kavan
--- [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] pg_autovacuum

[Adam Kavan]

At 09:41 PM 9/2/03 -0400, Matthew T. O'Connor wrote:
On Tue, 2003-09-02 at 20:40, Adam Kavan wrote:
 And there is the problem, all of the counts stay at 0 no matter what I do.
OK, so why is this happening... a bug in the stats system?  Ignoring
pg_autovaccu, what numbers do you get from the stats system when you do
a:
SELECT relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables
where relname = 'foobar';
both before and after your insert / update script.

Matthew
Before updates:

  relname   | n_tup_ins | n_tup_upd | n_tup_del
+---+---+---
 PointUsage | 0 | 0 | 0
(1 row)
After updates:

  relname   | n_tup_ins | n_tup_upd | n_tup_del
+---+---+---
 PointUsage | 0 | 0 | 0
(1 row)
After deleting updates:

  relname   | n_tup_ins | n_tup_upd | n_tup_del
+---+---+---
 PointUsage | 0 | 0 | 0
(1 row)
This is the problem... I just don't know how to fix it, or even what is 
causing it.  I am 100% sure that I am inserting into the same database that 
I executed the select from.  Here are the relevent lines from postgresql.conf:

stats_start_collector = true
#stats_command_string = true
#stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = false
Any ideas?

--- Adam Kavan
--- [EMAIL PROTECTED]


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] Index not being used ?

[Adam Kavan]

CREATE TABLE public.base (
  nombre varchar(255),
  calle varchar(255),
  puerta int2,
  resto varchar(255),
  lid int2,
  area varchar(4),
  telefono varchar(10)
)
CREATE INDEX base_dir ON base USING btree (lid, calle, puerta);
And trying the following select:

select * from base where lid = 457 and calle = 'MALABIA' and puerta = 10
I think its because lid and puerta are int2's and 457 and 10 are 
int4's.  Try lid = '457'::int2 and puerta = '10'::int2.  To use an index 
the variables have to match types exactly.

--- Adam Kavan
--- [EMAIL PROTECTED] 

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] pg_autovacuum

[Adam Kavan]

So you probabaly want to do somethign like this:

pg_autovacuum -d2 -s1 -S0 -v10 -V0 -a10 -A0 -L pg_autovacuum.out

this will set debug=2, sleep only 1 sec after each look, and perform
vacuums and analyzes after only 10 insert/update/delets and log it all to
pg_autovacuum.out
Run your update script

Kill pg_autovacuum

bzip the logfile and send it to the mailing list (or just to me if you
prefer).  Also a copy of your script might help.
Matthew
Ok I ran pg_autovacuum using the supplied command line and have attached 
the resulting log.  My script is just INSERT INTO PointUsage 
VALUES(DEFAULT,32,now(),1024); repeated several thousand times.  I ran 
autovac, then my script, then verified all of the rows were inserted then 
ran DELETE FROM PointUsage WHERE Value = 1024; to kill all of the new 
rows, and then killed autovac.  The results are attached.

Thank you very much for taking the time to look at this for me.

--- Adam Kavan
--- [EMAIL PROTECTED] 

pg_autovacuum.out.bz2
Description: Binary data

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] pg_autovacuum

[Adam Kavan]

First, I meant to ask for debug level 3, (-d3, not -d2) sorry  Also,
are you sure that pg_autovacuum is connecting to the same database
cluster as your insert delete script?
I have attached a new log with a debug level of 3.  And I am sure they are 
connecting to the same cluster, I only have one postgres box that is active.


In the output you should see the cur_analyze_count increase with each
insert and the curr_delete_count increase with every delete.  Also, the
debug output should tell you the threshold you have to reach for a
vacuum or analyze.
And there is the problem, all of the counts stay at 0 no matter what I do.

--- Adam Kavan
--- [EMAIL PROTECTED] 

pg_autovacuum.out.bz2
Description: Binary data

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Default Value in Table Setup Help

[Adam Kavan]

At 02:57 PM 8/18/03 -0400, Dev wrote:
Hello all,

I am working on setting up a table that will append a sequence to the end 
of the value inserted.
Example;
INSERT INTO test (test) VALUES ('abcd');
And have the data in the database be;
abcd0001

Now I do have things setup else where were the default value for the field 
is such:
default ('abcd'::text || lpad(text(nextval('test_sequence'::text)), 4, 
'0'::text))

But i want the abcd or what erver to be added in the insert?

What am I missing to make this happen?
I don't think you can do what you want to do with a column 
default.  Instead you want to look at the rewrite rules.  You can find 
information about them here:

http://www.postgresql.org/docs/7.3/interactive/sql-createrule.html

If I'm wrong I'm sure someone here will correct me :).

--- Adam Kavan
--- [EMAIL PROTECTED] 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Postgres Hanging on Inserts

[Adam Kavan]

 Alvaro Herrera Munoz [EMAIL PROTECTED] writes:
  On Wed, Jul 30, 2003 at 01:03:55PM -0500, Adam Kavan wrote:
  I have found the problem (I think) below is the list of all the locks
  pending on the relation.  The relation is a hash index on the table
that is
  being INSERT'd rapidly.  From what I can see pid 10024 and 10025 both
have
  an ExclusiveLock on the index, and they both are waiting to get an
  ExclusiveLock on the relation.

  Oh, so this is the problem.  Truth is hash indexes in Postgres are known
to
  have poor concurrency, though I didn't expect them to be subject to
  deadlocks...

 They are known to have internal deadlock problems too.  I believe what
 Adam has shown us is an internal deadlock in the index.  The locks that
 are being taken are actually page-level locks, but the pg_locks view
 doesn't show the page numbers.

 I had thought that such things would trigger a deadlock detected error
 though --- curious that it seems not to.

  you should change the hash index to a btree index

 Agreed.  Hash indexes would probably have gotten fixed by now if anyone
 could see a reason to expend effort on them, but they seem to be mostly
 an academic exercise.

 regards, tom lane

I can happily report that my system has gone through the night without any
problems.  Thanks a lot for helping me.

--- Adam Kavan
--- [EMAIL PROTECTED]


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Postgres Hanging on Inserts

[Adam Kavan]

- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, July 30, 2003 9:25 AM
Subject: Re: [GENERAL] Postgres Hanging on Inserts


 Adam Kavan [EMAIL PROTECTED] writes:
  I looked into pg_locks and they are all waiting to get an exclusive
  lock on the same relation.  Is there anyway for me to tell what this
  relation is?

 To decipher the OIDs in pg_locks, join against pg_class.oid, or just do
 select relname from pg_class where oid = ;

  Does anyone know what it could be and how I can fix this
  problem?

 Look for the process that already has a lock on the same relation, and
 find out what it's waiting for.

 regards, tom lane

I have found the problem (I think) below is the list of all the locks
pending on the relation.  The relation is a hash index on the table that is
being INSERT'd rapidly.  From what I can see pid 10024 and 10025 both have
an ExclusiveLock on the index, and they both are waiting to get an
ExclusiveLock on the relation.  Those are the only locks either pid doesn't
have so I suspect that is what is causing the deadlock.  Is there something
I've done wrong?  Both pids are just doing simple inserts.

data=# select * from pg_locks where relation = 3731653 order by granted;
 relation | database | transaction |  pid  |  mode   | granted
--+--+-+---+-+-
  3731653 |16976 | | 10091 | ShareLock   | f
  3731653 |16976 | | 10077 | ShareLock   | f
  3731653 |16976 | | 10178 | ShareLock   | f
  3731653 |16976 | | 10116 | ShareLock   | f
  3731653 |16976 | | 10108 | ShareLock   | f
  3731653 |16976 | | 10076 | ShareLock   | f
  3731653 |16976 | | 10079 | ShareLock   | f
  3731653 |16976 | | 10110 | ShareLock   | f
  3731653 |16976 | | 10023 | ExclusiveLock   | f
  3731653 |16976 | | 10177 | ShareLock   | f
  3731653 |16976 | | 10208 | ShareLock   | f
  3731653 |16976 | | 10166 | ShareLock   | f
  3731653 |16976 | | 10142 | ShareLock   | f
  3731653 |16976 | | 10160 | ShareLock   | f
  3731653 |16976 | | 10214 | ShareLock   | f
  3731653 |16976 | | 10226 | ShareLock   | f
  3731653 |16976 | | 10031 | ShareLock   | f
  3731653 |16976 | | 10237 | ShareLock   | f
  3731653 |16976 | | 10075 | ShareLock   | f
  3731653 |16976 | | 10109 | ShareLock   | f
  3731653 |16976 | | 10207 | ShareLock   | f
  3731653 |16976 | | 10190 | ShareLock   | f
  3731653 |16976 | | 10041 | ShareLock   | f
  3731653 |16976 | | 10130 | ShareLock   | f
  3731653 |16976 | | 10043 | ShareLock   | f
  3731653 |16976 | | 10026 | ShareLock   | f
  3731653 |16976 | | 10074 | ShareLock   | f
  3731653 |16976 | | 10092 | ShareLock   | f
  3731653 |16976 | | 10158 | ShareLock   | f
  3731653 |16976 | | 10024 | ExclusiveLock   | f
  3731653 |16976 | | 10141 | ShareLock   | f
  3731653 |16976 | | 10189 | ShareLock   | f
  3731653 |16976 | | 10238 | ShareLock   | f
  3731653 |16976 | | 10027 | ShareLock   | f
  3731653 |16976 | | 10078 | ShareLock   | f
  3731653 |16976 | | 10025 | ExclusiveLock   | f
  3731653 |16976 | | 10159 | ShareLock   | f
  3731653 |16976 | | 10225 | ShareLock   | f
  3731653 |16976 | |  9951 | ShareLock   | f
  3731653 |16976 | | 10029 | ShareLock   | f
  3731653 |16976 | | 10196 | ShareLock   | f
  3731653 |16976 | | 10028 | ShareLock   | f
  3731653 |16976 | | 10128 | ShareLock   | f
  3731653 |16976 | |  9951 | AccessShareLock | t
  3731653 |16976 | | 10024 | ExclusiveLock   | t
  3731653 |16976 | | 10025 | ExclusiveLock   | t
  3731653 |16976 | |  9951 | ShareLock   | t
  3731653 |16976 | | 10023 | ShareLock   | t
(48 rows)



 ---(end of broadcast)---
 TIP 2: you can get off all lists at once with the unregister command
 (send unregister YourEmailAddressHere to [EMAIL PROTECTED])


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html